Почему не работает условное форматирование в excel

Почему тормозит файл excel. Приложение Excel не отвечает, зависает или прекращает работать. Убираем чрезмерное условное форматирование

Проблема возникшая с Microsoft Excel 2016 компьютере клиента, при этом сам компьютер работает «без тормозов» и зависаний.

Microsoft Excel тормозит при попытке ввода значения в ячейку, реагируя с большой задержкой (порядка 10 секунд).

При этом файл таблицы является небольшим, таблица созданной на компьютере, а не экспортированной из внешнего приложения (например 1С).

Проблема кроется в слабой конфигурации данного компьютера (при этом её достаточно для работы комфортной работы компьютера).

[Файл] ➙ [Параметры]➙ [Дополнительно]➙ [Экран]➙ [Отключить аппаратное ускорение обработки изображения]➙ [ОК]

Изображение 1. Microsoft Excel 2016 отключить аппаратное ускорение обработки изображения

После выполнения данного действия, основная проблема со скоростью ввода данных в ячейки была устранена, но было видно небольшую задержку с меню.

Дополнительно были внесены изменения в параметры быстродействия Windows 10 (данные параметры доступны в Свойствах системы).

➙ ➙ [Дополнительно]➙ [Быстродействие]➙ [Визуальные эффекты]➙ [Особые эффекты]➙ [ОК]

Были установлены следующие параметры:

Изображение 2. Windows 10 улучшение быстродействия

Данное действие позволило полностью устранить торможение при работе Microsoft Excel 2016.

В этой статье мы рассмотрим вопрос оптимального использования Excel’ем ресурсов компьютера. Ведь даже если файл оптимизирован, бывает что все равно виснет Excel. Почему? Можно сказать, что этот материал продолжение нашумевшей статьи « «, в которой мы описывали все возможные проблемы именно внутри файлов Excel. Сейчас поговорим об оптимизации работы вашего ПК при работе с таблицами.

В большинстве случае виной тормозов и зависаний Excel служит не оптимальное оформление, структура, данные файла, но бывает что и ресурс компьютера не задействован в полной мере, например процессор используется на 25%. Ниже мы предлагаем несколько вариантов, которые точно лишне проверить если у вас виснет Excel.

Современные компьютеры очень производительны, что позволяет работать в режиме многозадачности и работать в нескольких программах одновременно. Для того, чтобы Excel не занимал все ресурсы компьютера, у версий табличного редактора начиная с 2007 есть специальная настройка — Включить многопоточные вычисления. Проверьте включена ли она и задействована ли у вас полная мощность процессора.

Для этого зайдите в круглую кнопку Office (левый верхний угол) — Параметры Excel — найдите раздел Дополнительно — подраздел Формулы. См. первую картинку.

Проверьте, чтобы у вас стояла галочка «использовать все процессоры данного компьютера».

Можно так же принудительно поставить максимальное количество — в данном случае 4. Для некоторых расчетов может выручить. Я обращал внимание, что, например, при сортировке больших таблиц часто используется только 1 процессор из 4 (по умолчанию), отсюда кстати и 25% использования процессоров, о котором часто упоминается. Установив принудительно максимальное количество задействованных потоков вы уберете эту проблему.

Для включения функции многопоточных вычислений в VBA используйте команду:

Кстати отключить полное задействование тоже полезно, особенно если вы выполняете параллельно несколько задач, а табличный редактор не в приоритете.

Как задействовать все процессоры. Настройка Windows

Кстати, не только Excel может ограничить количество процессоров в работе. В Windows тоже есть такая возможность.

Для того чтобы проверить настройку Windows нажимаем Пуск — Выполнить — вводим команду msconfig — заходим на вкладку Загрузка — Дополнительные параметры..

Убедитесь, что число процессоров не задано вручную на минимальном уровне, или галочка не установлена и система по умолчанию использует максимум ресурсов.

Не помогло, все равно виснет Excel… Еще причины

• Если у вас открыты несколько книг Excel, то расчет происходит по всем измененным ячейкам во всех открытых книг. По возможности закройте ненужные книги.
• Если у вас по-настоящему большие таблицы (

В этой статье рассматриваются действия по устранению неполадок, которые могут помочь устранить наиболее распространенные проблемы при получении Excel не отвечает ошибки, Excel, зависает или перестает работать после запуска его, или откройте книгу Excel. Такие проблемы могут возникнуть по одной или нескольким из перечисленных ниже причин.

Следуйте предоставленным решений в этой статье в порядке. Если вы попытались ранее одним из таких способов не помог, перейдите к следующему проверяйте в списке.

Запустите Excel в безопасном режиме

Безопасный режим позволяет запустить Excel без возникла определенные запуска программы. Можно открыть Excel в безопасном режиме, нажав и удерживая Ctrl при запуске программы, или используя параметр/safe (/ Safe excel.exe) при запуске программы из командной строки. При запуске Excel в безопасном режиме, он пропускает функции и параметры, такие как дополнительный автозагрузки, измененных панелей инструментов, папку xlstart и надстройки Excel. Тем не менее надстройки COM будут исключены.

Если проблему удалось решить после запуска Excel в безопасном режиме, см.: Устранение неполадок, возникающих при загрузке Excel .

Если ваша проблема не устранена после запуска Excel в безопасном режиме, перейдите к следующему пункту списка.

Установка последних обновлений

При необходимости можно задать Центр обновления Windows для автоматической загрузки и установки рекомендованные обновления. Установка никаких важных рекомендаций и оптимизации обновлений часто можно Устранение проблем с заменив устаревшие файлы и корректировать уязвимостей. Установка последних обновлений для Office, выполните действия, описанные в этой статье: Обновление Office и вашего компьютера .

Если установка последних обновлений для Office не решила проблему, перейдите к следующему пункту списка.

Проверка того, что Excel не используется другим процессом

Если Excel используется другим процессом, эта информация будет отображаться в строке состояния в нижней части окна Microsoft Excel. При попытке выполнения других действий Excel во время работы, Excel может не отображаться. Подождите, пока в процессе завершить задачу свою работу, перед активацией другие действия.

Если Excel не используется другим процессом, перейдите к следующему пункту в списке.

Выявление возможных проблем с надстройками

Хотя надстроек можно расширить возможности, они могут иногда мешал или конфликтов с Excel. Попробуйте запустить Excel без надстроек, чтобы увидеть, если проблема не исчезнет.

Выполните одно из указанных ниже действий.

Если вы используете систему Windows 10, выберите Пуск > Все программы > Система Windows > выполнить > введите Excel/safe в окне " выполнить ", а затем нажмите кнопку ОК .

Если вы используете систему Windows 8, выберите команду выполнить в меню приложения > введите Excel/safe в окне " выполнить ", а затем нажмите кнопку ОК .

Если вы используете систему Windows 7, нажмите кнопку Пуск > введите Excel/safe в поле Найти программы и файлы , а затем нажмите кнопку ОК .

Если проблема устранена, щелкните Файл > Параметры > Надстройки .

Выберите надстройки COM и нажмите кнопку Перейти .

Снимите все флажки в списке и нажмите кнопку ОК .

Закройте приложение Excel и снова запустите его.

Если проблема не возникает при запуске Excel, начните Включение надстройками один во время, пока не позволяет. Это позволит выяснить, какие надстройки является причиной проблемы. Убедитесь, что и перезапустите каждый раз при включении надстройки Excel.

Если отключение надстроек не решило проблему, перейдите к следующему пункту в списке.

Проанализируйте сведения о файле Excel и содержимого

Файлы Excel могут находиться на компьютере в течение длительного времени. Они будут обновлены до версии из версии и часто будут пересылаться из одного пользователя другому пользователю. Как правило пользователь наследует файла Excel, но не знаете, что включено в файле. Следующее может привести к производительности или поврежденных вопросы:

Создание ссылок на нечетного числа элементов в аргументах формулы массива.

Сотни или возможно тысяч объектов скрытых или 0 высота и ширина.

Лишним стили, вызванные часто используемые при копировании и вставке книги.

Определенные имена лишним и недопустимые.

Если эти действия не решило проблему, перейдите к следующему пункту в списке.

Установите флажок ли ваш файл создается по сторонних разработчиков

Иногда приложением стороннего создаются файлы Microsoft Excel. В этом случае файлы могут быть созданы неправильно, а некоторые функции могут работать неправильно при открытии файлов в Microsoft Excel. В этом случае проверки возможностей в новые файлы из-за пределов приложение стороннего производителя. Если функции работают правильно, необходимо убедиться, что третьей стороне о проблеме.

Если после проверки вне приложения сторонних ваша проблема не устранена, перейдите к следующему пункту в списке.

Выполните Выборочный запуск, чтобы определить, конфликтует ли программы, процесса или службы с помощью Excel

При запуске Windows некоторыми приложениями и службами начала автоматически, а затем запустить в фоновом режиме. Эти приложения и службы может мешать другого программного обеспечения на Компьютере. Выполнение Выборочный запуск (также известную как «чистой загрузки») помогает обнаружить проблемы с приложениями конфликтующих. Выполните Выборочный запуск, выберите один из приведенных ниже ссылок в зависимости от установленной версии Windows, а затем следуйте инструкциям, приведенным в статье:

Выборочный запуск используется для выявления оскорбительного процесс, службы или приложения, конфликтующей с Excel.

Если ваша проблема не устранена после повторного создания профиля, перейдите к следующему пункту в списке.

Восстановление программ Office

Восстановление программ Office может устранить проблемы с Excel не отвечает, зависает или зависает счет автоматического исправления ошибок в файлах Office. Инструкции по таким образом, см.: Восстановление приложений Office .

Если восстановление программ Office не решило проблему, перейдите к следующему пункту в списке.

Проверка актуальности версии антивирусной программы и наличия конфликтов с Excel

Если антивирусная программа не обновлена, Excel может работать неправильно.

Для защиты от новых вирусов поставщики антивирусных программ периодически выпускают обновления, которые можно скачать из Интернета. Скачайте последние обновления, посетив сайт поставщика своей антивирусной программы.

Список поставщиков антивирусных программ см. в статье Разработчики антивирусного программного обеспечения для Windows .

Проверка наличия конфликтов с Excel антивирусной программы:

Если антивирусная программа поддерживает интеграцию с Excel, вы можете столкнуться с проблемами производительности. В таком случае можно отключить интеграцию Excel с антивирусной программой. Вы также можете отключить все надстройки антивирусной программы, установленные в Excel.

Важно: Изменение параметров антивирусной программы может привести к уязвимости компьютера для вирусных, мошеннических или вредоносных атак. Корпорация Майкрософт не рекомендует изменять параметры антивирусной программы. Используйте это решение на свой страх и риск.

Возможно, вам придется обратиться к поставщику антивирусной программы, чтобы узнать, как настроить ее таким образом, чтобы исключить интеграцию с Excel или сканирование в Excel.

Дополнительные сведения

Если методы, упомянутых ранее не решило проблему, проблема может быть либо файл определенных или окружающей среды. Ниже описывается устранение дополнительных проблему, которая может привести к сбою или повесить в Excel.

Факторов окружающей среды

При устранении неполадок сбои факторов окружающей среды менее важен, чем содержимое файла и надстройки. Помогут определить причину проблему, выполнив следующие действия:

Следуйте советам по устранению неполадок.

Проверка файлов в cleanest среде.

В следующих разделах описаны некоторые области, которые стоит узнать.

Где хранится файл

Перемещение файла на локальном компьютере помогут определить, есть ли проблема с файлом или в котором сохранен файл. Некоторые проблемы могут возникнуть при сохранении файла Excel по сети или на веб-сервере. Рекомендуется сохранить файл на локальном компьютере. Это следует делать в следующих случаях:

Перенаправление папки «Документы» на расположение сервера

Открытие файлов из SharePoint или Webfolder

Удаленный рабочий стол или Citrix

Виртуализированной среде. Дополнительные сведения о программного обеспечения корпорации Майкрософт, которая выполняется в виртуализированной среде следующей статье: Политика поддержки для запуска в программном обеспечении виртуализации оборудования сторонних программного обеспечения Майкрософт .

Файлы Excel может стать достаточно большим при добавлении большого количества форматирования и фигур. Убедитесь в том, что она имеет достаточно оперативной памяти для запуска приложения. Требования к системе для набора приложений Microsoft Office перейдите в следующих статьях Майкрософт:

Office 2010 реализована собственные 64-разрядные версии продуктов Office преимуществами большего емкости обработки. Чтобы узнать больше о 64-разрядной версии Office, перейдите в следующих статьях Майкрософт:

Принтеры и драйверы видео

При запуске Excel, она проверяет принтера по умолчанию и видео драйверы, которые будут отображаться в книгах Excel. Excel интенсивная принтер и будет работать медленнее, если при сохранении файлов Excel страничный режим. Тестирование файла, используя различные принтеры, например драйвер принтера записи XPS-документов Microsoft или видеодрайвера VGA будет определить, является ли проблема с определенным принтером или видеодрайвера.

Если вы по-прежнему красная или поврежденных после возникли проблемы в Excel при работе с помощью разрешение описанных здесь, нужно связаться службы поддержки Майкрософт для интерактивной устранения неполадок.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте

Вроде давно работаю с офисным пакетом от Microsoft, а с подобной проблемой столкнулся впервые. Присылает мне коллега файл и просит разобраться с проблемой. В файле Excel всего 100 строк, но нормально работать с ним невозможно. Ячейки выделяются с ощутимой задержкой. При открытии файла процесс Excel съедает в районе 700 мегабайт оперативной памяти и ряд других симптомов, не позволяющих нормально работать с документом.

Первое, что меня насторожило — размер файла. Для 100 строк, размер в 29 мегабайт явно многовато. Первым делом я попробовал сохранить файл в режиме совместимости с Office 2003, но положительного результата это не принесло. Файл еще больше увеличился в размере и работать с ним стало еще трудней. Тогда мне ничего не оставалось как обратиться за помощью к Google. Через несколько попыток формирования разных запросов, поиск вывел меня на какой-то англоговорящий форум, где один из участников предложил свое решение подобных проблем.

Проблема тормозов в Excel крылась в присутствии на листе рабочей книги большего количества графических фигур. Посмотреть список этих фигур вы можете в меню "Главная" -> "Найти и выделить" -> "Область выделения".

В моем случае их оказалось почти 50 тысяч. Откуда они взялись — отдельный вопрос. Создатель файла разводит руками, но мне кажется, что подобные вещи возникают из-за постоянных вставок в файл данных из других книг. Во всяком случае пользователь составивший файл занимался именно копи пастом. Хорошо, проблема выяснена, а как ее теперь решить? Удалять графические фигуры через это окно слишком сложно (не забываем, Excel по прежнему тормозит).

Вот тут на помощь к нам приходит мощный механизм макросов. Создаем новый макрос ("Вид" -> "Макросы") и копируем в него следующий код:

Dim someShape As Shape For Each someShape In ActiveSheet.Shapes someShape.Delete Next someShape

Код копируем прямо в созданную процедуру, затем запускаем его. Excel сразу же задумается и приступит у удалению всех лишних объектов. Мои пятьдесят тысяч объектов были удалены за 4,5 минуты. После завершения процедуры исходный файл похудел аж до 29 килобайт (. ).

Уверен, что эту заметку будут читать не только разработчики (моя целевая аудитория), но и обычные пользователи. В связи с этим, обращаю внимание на код. Он удаляет абсолютно все объекты с активного листа. Под категорию "все" подпадают текстовые поля, кнопки и т.д. Если в вашем документе есть кнопки и прочие элементы управления, то обязательно укажите фильтр в цикле. Проверить тип удаляемого объекта можно так:

If someShape.Type = 17 Then someShape.Delete

Выше приведенный код будет удалять только текстовые поля. Ознакомиться со всеми типами фигур вы можете на в

Добрый день уважаемый читатель!

Эту статью я хочу рассмотреть тему эффективности Excel и узнать почему тормозит Excel, а также предоставить 12 способов улучшить работу файлов Excel! Конечно, если вы используете небольшие таблицы или вам не интересно расширение границ использования Excel, тогда статья будет не интересна, а вот если всё наоборот, приступим….

Когда же возникает такие случаи, когда файлы Excel «тормозят» и их работоспособность стремится к малой величине. Возможно, присланный вам файл очень большой и тяжёлый и поэтому долго сохраняется или он маленький, но всё то же самое… Давайте выясним причины таких проблем и основные способы их победить.

Проблемы с «тормознутостью» MS Office это довольно распространённое явление в работе Excel. Проблемная работа Excel может возникать по многим причинам: излишнее , чрезмерное использование функций Excel, созданные из данных , дополнительные и в таблицах, и многое другое.

А теперь перейдем от теории к действию и рассмотрим 12 способов, которые позволит вам, избавится от «тормозов» Excel и начать комфортно работать:

1. Уменьшаем размеры картинок

Самым первым способом уменьшения размера вашего файла, это поработать над картинками в нём. Но способ действителен, если изображения вы всё же использовали.

Когда вы добавляли картинку через меню на панели управления «Вставка» в блоке «Иллюстрации» нажимали «Рисунок» , тогда в файл вшивался рисунок с полным объёмом. А вот теперь необходимо внести изменения, установите курсор на картинку, появится новая вкладка «Работа с рисунками» с под вкладкой «Формат» . В блоке «Изменения» кликаете на кнопку «Сжать рисунки» .
В диалоговом окне «Сжатие рисунков» указываете подходящий вам вариант уменьшения размера и нажимаете «Ок» .
Не стоит забывать о том, что картинки могут скрываться и в .

2. Заменим макросами большие формулы

В случаях, когда таблица большая, на десятки тысяч строк, и вмещает в себя много сложных формул, которые ссылаются на разнообразные книги и листы приводят к тому, что тормозит Excel. Для улучшения стоит применять созданные вместо написанных формул, так как они превратят формулы в скопированные как значения тексты, цифры и значения. Очень поможет в случаях множества действий с файлом, когда производится частое пересчитывание данных всего файла.

3. Убрать форматирование таблиц Excel

В случаях, когда к применено форматирование в виде специальной таблицы, а сами таблицы достигают большого размера, это может привести к значительным сложностям в работе Excel.

Определить как именно отформатированная таблица можно просто, установив курсор на абсолютно любую ячейку, появится дополнительная вкладка в панели управления «Работа с таблицами» . Выбираете вкладку «Конструктор» , ищете раздел «Сервис» , нажимаете кнопку «Преобразовать в диапазон» и подтверждаете, что вам нужно преобразовать вашу таблицу в обычную.

4. Удаляем ненужные столбцы и строки

Это достаточно распространённая проблема при работе в Excel. При обыкновении пользователь случайно перемещается с помощью Ctrl + стрелка вправо или вниз , нажатых случайно, и переносится в конец листа, да так и сохраняет рабочую книгу, при этом значительно ее утяжеляет. Особенно это происходит, когда в конец книги добавляется случайный символ, знак или заливка.

Проверить такой вариант возможно по характерному признаку, это очень маленький ползунок.

5. Заменить формат рабочей книги на *.xlsb

В случаях, когда ваша работа связана с огромными таблицами и их размер достигает больших объёмов, то следует сохранять рабочие книги в формате *.xlsb . Это расширение хранится как двоичный формат, своеобразный специальный формат для создания и хранения ваших имеющих в основе электронные таблицы.

Когда сохранение будут произведены в этом формате, любой большой файл уменьшится сразу же в 2-3 раза, кстати, скорость вычислений также увеличится на несколько порядков, что будет вас радовать.

6. Убираем чрезмерное условное форматирование

Несмотря на всю полезность условного форматирования, его чрезмерное использование может и привести и к проблемам, ваша рабочая книга может подтормаживать. Это может произойти, даже без вашего ведома, просто в процессе работы вы копируете ячейки вместе с форматами и форматированием, а это не проходит бесследно, они накапливаются незаметно и тормозят работу файла.

Для удаления всего лишнего, для начала, выбираете необходимый диапазон, а можно и весь рабочий лист. Следующим шагом в панели управления, во вкладке «Главная» в разделе «Стили» выбираете пункт с выпадающим меню «Условное форматирование» , дальше нужен пункт «Удалить правила» и выбираете нужный подпункт удаления.

7. Удаляем ненужные данных внутри структуры файла Excel

Я могу поспорить, что далеко не каждый пользователь знает, что файлы Excel представляют собой своеобразный архив и такая структура файла начала свою жизнь с 2007 выпуска. А это означает, что теперь файлы Excel можно открыть архиватором, таким как WinRar. А вот внутри файла и могут находиться файлы, которые, в некоторых случаях значительно тормозят Excel.

Для проведения процедуры по уменьшению размера, перестрахуйтесь, и сделайте резервное копирование вашего файла. Затем откройте файл с помощью контекстного меню, открываем файл, нажав на мышке правую кнопку и выбрав пункт «Открыть с помощью» и с доступных программ выбираете программу архиватор. Или, второй вариант, открываете архиватор и в меню «Файл» кликаете на пункт «Открыть внутри» . Результатом любого из вариантов будет открытие файла Excel, как архива с файлами и папками.

В архиве, скорее всего в папке «xl» , удалите папки «printerSettings» и «drawings» .
После всех мучений запускаем снова файл и на все системные возмущение соглашаемся кнопкой «Ок» , файл восстановиться и запустится.

Обращаю ваше внимание, что если файл содержит рисованные объекты типа кнопок или фигур то удалять всю папку «drawings» не стоит, вместе с ней испарятся и фигурки. Достаточно в папке удалить vmlDrawing.vml который вмещает разнообразную информацию и может достигать больших размеров.

8. Неправильно настроенная печать на принтер

Когда вы не установили принтер по умолчанию и система не может определить , необходимо чётко обозначить, куда будет происходить отправка на печать. Если принтер невозможно опознать, замените драйвер устройства.

Иногда случается так, что стирании настроек принтера из пункта 7, может привести к тому, что неправильные настройки принтера тормозят весь файл.

9. Сменить версию Excel на более позднюю

Ни что не стоит на месте, даже наша планета, так и Excel развивается, ведь совершенству нет предела. Меняется и развивается программный продукт, оптимизируется программный код, что позволяет, иногда значительно, повысить продуктивность работы с таблицами и расчёты формул, быстродействие может увеличиться до 20% в более новых версиях, например в 2016 относительно 2007.

Также частенько встречается ситуация когда Excel версии 2007 не может работать с текущим файлом, а вот в более поздних версиях никаких проблем нет и всё работает отлично.

Усовершенствуйте версию, переходите на более продуктивные продукты MS Office, желательно от 2013 версии и выше.

10. Сносим файл «Personal.*»

Основное назначения сего файла, это хранение информации о предыдущих работах в Excel. Он помогает записывать и запоминать макрос, хранить формат данных для ваших всех книг. Проще говоря он нужен для того что бы из раза в раз не делать те же самые действия по форматированию файла, а при первоначальном открытии все персональные настройки сохраняются в специальный файл и воспроизводятся при открытии.

Проблемы могут возникать, если начнет, тормозит файл персональных настроек. Для устранения проблемы, воспользуйтесь принципом, нет файла, нет проблем. Найдите С:\. \Application Data\Microsoft\Excel\XLSTART или С:\. \Microsoft Office\Office12 и где то в недрах этих папок ищете файл «Personal.*» и сносите его.

11. Производим оптимальную настройку сводных таблиц

Когда вы в сводной таблице ссылаетесь на огромные диапазоны от десятка тысяч строк, она начинает все результаты своих расчётов, которые становятся колоссальными, хранить в себе. И это становится причиной общего торможения при работе с файлом. Для устранения этой причины, установите курсор на таблице, вызовите контекстное меню правой кнопкой мыши, потом выберите пункт «Параметры сводной таблицы» в диалоговом окне на вкладке «Данные» снимите галочку в пункте «Сохранять исходные данные вместе с файлом» .

Этими действиями вы можете уменьшить файл практически в два раза.

12. Убираем ненужные объекты

Частенько когда вы переносите или копируете информацию из других рабочих файлов или сайтов, в структуре таблиц могут содержаться разнообразные скрытые объекты типа фигур, рисунков и прочее.

Для удаления таких ненужных объектов необходимо запустить макрос, кликните горячую комбинацию клавиш Alt + F11 для вызова редактора и вставьте код.

Условное форматирование в Excel

В таблицах Excel часто находятся сухие цифры и перечень других данных, которые обычному человеку очень сложны для восприятия. Однако с помощью графической визуализации данные в таблице можно сделать более читаемыми, обратив внимание на важные пункты. Специально для этого в Excel был встроен инструмент «Условное форматирование». Неопытные пользователи могут спутать его с инструментом создания диаграмм и иллюстраций, но это не совсем то, так как установка обозначений происходит непосредственно в ячейках, а не выносится в отдельный блок.

Варианты условного форматирования

Всего предусмотрено три варианта форматирования:

• Гистограмма;
• Цветовая шкала;
• Значки.

Работа с каждым из них будет рассмотрена далее. Во всех случаях вам придется выполнить некоторые действия, чтобы получить доступ к условному форматированию:

1. Для начала выделите ячейки с данными, которые собираетесь оформить для акцентирования внимания.
2. Теперь переместитесь во вкладку «Главная», если она не открыта у вас по умолчанию.
3. Здесь, в блоке «Стили» кликните по кнопке «Условное форматирование». Появится выпадающее меню, где вам нужно будет обратить внимание на пункты: «Гистограммы», «Цветовые шкалы» и «Наборы значков». О них мы писали выше.

Перейдем к рассмотрению каждого из данных пунктов подробнее.

Настройка гистограммы

Выделив ячейки с данными откройте контекстное меню «Условное форматирование» и выберите там пункт «Гистограммы». Обратите внимание, что вам предлагается несколько дизайн-вариантов оформления. Они разделены на две большие категории: с градиентной заливкой и сплошной. Выберите среди них тот вариант оформления, который вам больше нравится.

По умолчанию, чем больше числовое значение в ячейке, тем длиннее будет гистограмма. Примечательно, что если в выделенной части таблицы присутствуют отрицательные числа, то гистограмма тоже под них подстроится: появится разделение, которое продемонстрировано на скриншоте. К сожалению, Excel версии 2007 года и более ранние не способны корректно работать с отрицательными значениями.

Цветовая шкала

В отличии от гистограммы такое форматирование предполагает заливку ячеек цветом в зависимости от того, какое число расположено непосредственно в ячейке. Для этого в выпадающем меню «Условное форматирование» выберите вариант «Цветовые шкалы». Среди представленных вариантов вы можете указать наиболее подходящую вам цветовую гамму. Она применится к выделенным ячейкам. Цветовая шкала корректно работает и с отрицательными значениями в ячейках во всех версиях Excel.

Наборы значков

Это более интересный вариант внешнего оформления ячеек с данными в таблице, так как предоставляет большое количество вариантов значков и индикаторов для оформления ячеек в таблице. Всего их 4 группы:

• Направления;
• Фигуры;
• Индикаторы;
• Оценки.

Каждый выбранный пользователем вариант предполагает использование разных значков в зависимости от содержимого ячейки. Выделенная область анализируется встроенным алгоритмом, после чего к ней применяются выбранные значки. По умолчанию к наибольшим числовым значениям применяются элементы зеленого цвета, а к наименьшем красного. Ячейки с промежуточными значениями выделяются желтым цветом.

Исключения бывают в том случае, если вы выбрали вариант, где не предусмотрена градация по цвету. Тогда она происходит в соответствии с указанным шаблоном, например, частичное заполнение фигуры.

При выборе индикаторов из блока «Направления» помимо цветового оформления, используется ещё сигнализирование в виде направлений. Так, стрелка, повернутая указателем вверх, применяется к большим величинам, влево – к средним, вниз – к малым.

В новых версиях Excel при выборе в качестве индикаторов геометрических фигур в основном используются круги, но есть и другие геометрический фигуры. В таком случае будет меняться не только цветовая заливка, но и сами фигурки в зависимости от числовых значений в ячейках. Например, круги для крупных значений, треугольники для средних и четырехугольники для наименьших.

Изменение правил выделения ячеек

Вы можете не только настроить отображение какого-то цвета или фигурки в ячейках в зависимости их значений, но и задать свои правила, если вам не нравятся те, что установлены по умолчанию. Чтобы это реализовать, сделайте следующее:

1. Кликните по блоку инструментов «Условное форматирование». Из контекстного меню выберите «Правила выделения ячеек».
2. Там будет отображено семь основных правил:
3. «Больше». В случае выбора этого вариант открывается окно, где нужно установить, значения больше какого числа требуется выделять условным форматированием. Вы можете указать адрес ячейки, из которой требуется брать это минимальное число или прописать его вручную, если оно будет неизменным. В соседнем поле нужно выбрать, каким образом будет идти выделение подходящих ячеек: светло-красная заливка и темно-красный цвет (по умолчанию); желтая заливка и темно-желтый текст; красный текст, и т.д. Можно задать пользовательский формат.
4. Очень похожий принцип работы у варианта «Меньше», но только здесь происходит наоборот – выделяются ячейки с числами меньше указанных.
5. Правило «Между» создает интервал между чисел. Данные, выходящие в этот интервал выделяются по аналогии с предыдущими правилами.
6. «Равно». Здесь вы задаете какое-то конкретное число и программа выделяет все ячейки, в которых оно содержится (в выделенной области).
7. «Текст содержит». Это правило уже применяется в тех случаях, когда работа ведется с ячейками, где прописан текст, а не числа. В окне установки правила следует указать слово, часть слова, или последовательный набор слов, при нахождении которых, соответствующие ячейки будут выделяться. Выделение ячеек настраивается аналогичным образом, как и в предыдущих вариантах.
8. «Дата». Правило применяется к ячейкам, которые содержат данные в формате дат. Вы можете задать как какую-то конкретную дату, так и выбрать готовые варианты дат: сегодня, вчера, завтра, за последние 7 дней, и т.д.
9. «Повторяющиеся значения». Здесь выделение происходит по одному из критериев: повторяющиеся это данные или уникальные.

Правила отбора первых и последних значений

Это еще один пункт «Условного форматирования» в Excel. Здесь происходит настройка правил выделения только для максимальных или минимальных значений в выделенном диапазоне ячеек. Инструмент подразумевает возможность провести как отбор по порядковым величинам, так и по процентным.

Рассмотрим доступные критерии отбора подробнее:

• Первые 10 элементов. Выделяет только первые 10 элементов в указанном диапазоне;
• Первые 10%. Выделяет только первые 10% ячеек из указанного диапазона;
• Последние 10 элементов;
• Последние 10%;
• Выше среднего. Выделяет только первую половину диапазона;
• Ниже среднего. Выделяет нижнюю половину диапазона.

Примечательно, что вы можете настроить эти фильтры под себя. Например, сделать выделение не первых 10 элементов, а первых 5 или 3.

Создание пользовательских правил

Стоит обратить внимание, что в выпадающем меню «Условное форматирование» есть пункт «Создать правило». При нажатии на него открывается окошко из шести правил, которые вы можете менять на свое усмотрение:

• Форматировать все ячейки на основании их значений;
• Форматировать только ячейки, которые содержат;
• Форматировать только первые и последние значения;
• Форматировать только значения, которые находятся выше или ниже среднего;
• Форматировать только уникальные или повторяющиеся значения;
• Использовать формулу для определения форматируемых ячеек.

Выберите одно из представленных правил, чтобы провести его детальную настройку. В нижней части окна отобразятся возможные изменения: описание правил, установка величин, интервалов и других значений. Во многом это похоже на те шаблоны, которые мы уже рассматривали выше, только настройка здесь более гибкая и все параметры собраны в одном окне.

Во вкладке «Условное форматирование» так же есть и пункт «Управление правилами». С его помощью удобно применять и удалять дополнительные правила к выделенной области. Дело в том, что к ячейкам можно добавить сразу несколько правил, например, сделать гистограмму со значками и цветовыми выделениями на заднем плане. В окошке «Диспетчера правил условного форматирования» (открывается при нажатии на «Управление правилами»). Здесь можно вносить изменения в уже установленные правила, добавлять новые или удалять неактуальные.

Дополнительно правила можно менять местами – по умолчанию они занимают место в зависимости от того, какое из них было раньше добавлено. Изменение места правила происходит с помощью кнопок со стрелками в верхней части окна.

Удаление «Условного форматирования»

Если вам нужно удалить условное форматирование для ячеек, то здесь нет ничего сложного:

1. Кликните в блоке с инструментами по пункту «Условное форматирование».
2. Из контекстного меню выберите «Удалить правила».
3. Здесь вы можете выбрать два варианта:
4. «Удалить правила из выделенных ячеек». В таком случае очищается условное форматирование только в выделенных ячейках. Если на листе есть другие ячейки с ним, но они не выделены, то условное форматирование в них не пострадает.
5. «Удалить правила со всего листа». В этом случае происходит удаление всего условного форматирования на листе, где вы сейчас находитесь.

Инструмент «Условное форматирование» является отличным решением для визуализации данных в таблицах Excel. Здесь есть как уже встроенные шаблоны, так и возможность создания собственных, в том числе и на основе имеющихся по умолчанию.

Условное форматирование в Excel: он работает, вы отдыхаете

Привет, друзья. Форматирование ячеек в Эксель, наряду с форматированием диаграмм, по праву занимают львиную долю времени, которое мы проводим с отчетами. Чтобы сократить это время до минимума, читайте эту статью про условное форматирование. После прочтения, вы поймете, как много времени и усилий можно экономить, применяя такой инструментарий ежедневно.

Условное форматирование – это автоматическое форматирование ячеек в зависимости от значения, содержащегося в них. Теперь вам не нужно вручную «разукрашивать» таблицы, чтобы расставить акценты. Достаточно правильно настроить правила форматирования и незамедлительно получить результат.

Как это работает? Вкратце это делается так:

, в которых нужно сделать условное форматирование;
1. На ленте найдите группу команд: Главная – Стили – Условное форматирование . В выпадающем меню собраны все возможные опции форматирования с условием;

1. Выбираете нужное правило, вводите дополнительные параметры, нажимаете ОК. Вот и все, теперь выделенным ячейкам задан условный формат, и при изменении значения в ней – формат тоже изменится.

Давайте разберем по пунктам это меню, чтобы понимать что же можно сделать с его помощью.

Правила выделения ячеек

В этой группе команд содержатся наиболее популярные правила, применяемые пользователями Microsoft Excel. Они проверяют значение в ячейке на следующие условия:

1. Больше заданного значения
2. Меньше заданного значения
3. Между заданными значениями
4. Содержит заданный текст
5. Дата соответствует заданной (или промежутку)
6. Повторяющиеся значения
7. Другие правила – откроет окно «Создание правил форматирования», о котором я расскажу чуть ниже в этой статье

Если в этом списке есть подходящее вам правило – кликните по нему, откроется окно параметров, где можно задать условие для форматирования и формат ячеек, когда это условие выполняется.

Когда вы определились с условием для форматирования, вам предложат выбрать один из стандартных стилей форматирования, или создать свой собственный. Чтобы создать свой — выберите в списке форматов «Пользовательский формат…». Откроется уже известное нам окно «Формат ячеек», в котором и задается собственное оформление.

На картинке ниже массиву чисел задано условное форматирование «Между 50 и 70». Как видим, соответствующие ячейки выделены красной цветовой палитрой.

Правила отбора первых и последних значений

При использовании этих команд, программа в своей памяти (не на листе) сортирует массив и выводит по вашему желанию:

1. Первые N элементов (N – задаете сами)
2. Первые N процентов
3. Последние N элементов
4. Последние N процентов
5. Значения выше среднего
6. Значения ниже среднего

Меню настройки работает так же, как и в предыдущем пункте. На картинке ниже я задал форматирование пятнадцати процентов наименьших чисел массива.

Гистограммы

Гистограммы – очень удобный способ оценить соотношение данных без построения диаграммы. Ячейки будут залиты цветом в пропорции, соответствующей числу в ней. Полностью будет залита ячейка с наибольшим числом, наименьшая заливка – в ячейке с минимальным числом. Посмотрите, как это выглядит на моем примере (столбец С):

Согласитесь, неудобно сравнивать глазами цифры в большой таблице. Куда удобнее, когда прямо в таблицу встроена гистограмма.

По умолчанию, программа предлагает 6 цветовых тем со сплошной заливкой и еще шесть – с градиентной. Нажав кнопку «Другие правила», вы можете настроить свою уникальную цветовую схему.

Цветовые шкалы

Такое форматирование очень похоже на гистограммы из предыдущего пункта, но изменяется не залитый сегмент ячейки, а цвет заливки (градиентом от одного цвета к другому). Вот, как это выглядит на предыдущем примере (от красного к синему):

Очевидно, цветовые шкалы лучше будут смотреться в больших массивах данных, тогда как в малых – предпочтительнее гистограммы.

Как и в предыдущем пункте, вы можете выбрать из стандартных наборов тем, либо нажать «Другие правила» и выбрать цвета вручную.

Наборы значков

Еще одна похожая группа форматов. Здесь в ячейке отображается значок, соответствующий значению в ней:

По умолчанию программа предлагает несколько наборов значков, а более гибкие настройки можно сделать, нажав «Другие правила».

Здесь же можно настроить пороги смены значков. Можно, так же, поставить галочку «Показать только значок», тогда в ячейке вообще не будет отображаться значение.

Правильно подбирая пределы переключения значков, можно скрыть лишние данные, а нужные – отобразить в виде значков. Вот пример настройки и результата.

Очень удобно это применять при оценке персонала, когда значение показателя не имеет значение, а нужно лишь понимать – справился человек, или нет.

Создать правило

Чтобы создать правило условного форматирования с наибольшим числом настроек – используем пункт «Создать правило».

Водите в это меню и выберите тип правила:

1. Форматировать все ячейки на основании их значений. Название пункта говорит само за себя. Отформатированы будут все ячейки выделенного диапазона в зависимости от их значения.

В раскрывающемся списке «Стиль формата» можно выбрать нужный вид оформления:

• Двухцветная шкала – задайте цвет для минимального и максимального значения, получите свой уникальный формат

• Трехцветная шкала – задайте уже три цвета: минимальное значение, максимальное и промежуточное

• Гистограмма. Здесь есть множество настроек, в том числе, можно выбрать свои цвета, тип и направление заливки, способ заливки отрицательных чисел. Поставьте галку «Показывать только столбец», чтобы скрыть числовые данные

• Наборы значков – выбирайте виды значков и пороги их переключения, отключайте значки для бесполезных данных и отображение числовых данных

1. Форматировать только ячейки, которые содержат – отформатирует лишь те ячейки, которые будут соответствовать заданному условию: числовое значение, содержание текста, вхождение даты в период, только пустые ячейки, только непустые ячейки, ячейки с ошибками или без ошибок. В зависимости от выбранного типа сравнения, задаваемые параметры могут изменяться, или вообще отсутствовать

1. Форматировать только первые или последние значения. Позволяет форматировать только заданное количество наименьших или наибольших значений в массиве
2. Форматировать только значения, которые находятся выше, или ниже среднего. Вычисляет среднее значение в выделенном массиве и форматирует только те значения, которые больше или меньше среднего (на ваш выбор)
3. Форматировать только уникальные или повторяющиеся значения. Думаю, название пункта отлично характеризует его функционал
4. Использовать формулу для определения форматируемых значений. Запишите логическую формулу сами, будут отформатированы лишь те значения, для которых результат формулы – истина. В моем примере отформатированы будут лишь те ячейки, для которых первое значение столбца больше 50.

Удалить правила

Чтобы удалить условное форматирование из выделенного диапазона, или со всего листа – нажмите на ленте Главная – Стили – Условное форматирование – Удалить правила , и выберите где именно нужно удалять

Управление правилами

Одноименный пункт служит для управления правилами условного форматирования. Здесь можно создать новое правило, изменить или удалить уже существующие.

Кстати, если скопировать ячейку с условным форматированием, оно тоже будет скопировано.Если удалить содержимое ячейки — условное форматирование сохранится.

Вот, пожалуй, и все об условном форматировании. Как вы заметили, правила условного форматирования существенно ускоряют визуальную оценку и выборку данных, являются отличной альтернативой диаграммам. В своей практике, я применяю условное форматирование каждый день, ведь это удобно, быстро и практично. Рекомендую и вам пользоваться этим функционалом, как элементом профессиональной работы с Microsoft Excel.

Условное форматирование не работает

Условное форматирование
Добрый день. Как вводить в правиле форматирования диапазоны значений? Пробовал подобным способом.

Условное форматирование
Подскажите пожалуйста, как в таблице Excel сделать так, чтобы чётные значения выделялись одним.

Условное форматирование
Доброго времени суток. Есть таблица наименование товара, цена. Подскажите, как с помощью.