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

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

Условное форматирование – это простой способ определить ячейки с ошибочными записями или значениями определённого типа. Вы можете использовать формат (например, красная заливка), чтобы легко идентифицировать определенные ячейки.

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

Когда вы нажимаете на кнопку Условное форматирование, которая находится в группе Стили вкладки Главная, вы увидите выпадающее меню со следующими опциями:

меню условное форматирование

Правила выделения ячеек открывает дополнительное меню с различными параметрами для определения правил форматирования ячеек, содержащих конкретные значения или находится в определенном диапазоне.

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

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

Цветовые шкалы позволяет задавать двух- и трехцветовые шкалы для цвета фона ячейки на основе ее значения относительно других ячеек в диапазоне

Наборы значков отображает значок в ячейке. Какой именно значок отображается, зависит от значения ячейки относительно других ячеек. Excel 2013 предоставляет 20 наборов значков на выбор (при этом вы можете смешивать и сочетать значки из разных наборов). Количество значков в наборах колеблется от трех до пяти.

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

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

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

Графическое условное форматирование

Вероятно, самое крутое (и конечно, простое) условное форматирование, которое можно применить к диапазону ячеек – это форматирование с применением графических элементов – Гистограммы, Цветовые шкалы и Наборы значков.

На рисунке изображено применение двух различных правил для форматирования для диапазона от 6 до 1 и наоборот. В первом случае применялись Цветовые шкалы, где мы видим, как изменяется формат при изменении значения от 6 до 1, во втором – 3 цветные стрелки.

графическое условное форматирование

Определение конкретных значений в диапазоне ячеек

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

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

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

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

Выделяем диапазон ячеек, к которому мы хотим применить условное форматирование. Переходим по вкладке Главная в группу Стили, щелкаем кнопку Условное форматирование -> Правила выделения ячеек -> Текст содержит.

условное форматирование текст содержит

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

текст который содержит

Щелкаем ОК, чтобы наше правило вступило в силу.

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

Скажем, вы хотите применить три различных условных форматирования к одному и тому же диапазону ячеек: первый тип формата, когда ячейка содержит целевое значение, второй – когда больше цели и третий – когда меньше. Ниже описаны шаги по заданию формата Желая заливка с темно-желтым текстом для ячеек содержащих значение 95, Зеленая заливка с темно-зеленым текстом для ячеек со значениями больше 95 и Светло-красная заливка и темно-красным текстом для ячеек меньше 95.

Выделяем диапазон ячеек, к которому мы хотим применить три различных правила условного форматирования. Начнем с создания правила для ячеек, содержащих значение равное 95. Переходим по вкладке Главная в группу Стили, щелкаем кнопку Условное форматирование -> Правила выделения ячеек -> Равно. Excel откроет диалоговое окно Равно, где в левом текстовом поле необходимо указать условие 95, а в правом выпадающем списке выбрать формат для этого условия Желая заливка с темно-желтым текстом.

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

Далее задаем условное форматирование для значений больше 95. Из меню Условное форматирование -> Правила выделения ячеек выбираем Больше, в появившемся диалоговом окне Больше указываем значение, выше которого ячейка будет закрашиваться в зеленый цвет, и сам формат.

больше условное форматирование

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

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

пример условного форматирования

Формулы в условном форматировании

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

Выделяем таблицу с данными, к которой мы хотим применить условное форматирование. Переходим по вкладке Главная в группу Стили, щелкаем кнопку Условное форматирование -> Создать правило.  В появившемся диалоговом окне Создание правила форматирования в поле Выберите тип правила выбираем  Использовать формулу для определения форматируемых ячеек.

В поле Измените описание правила задаем условия и формат для нашего правила. В нашем случае, условием будет формула =ИЛИ(ДЕНЬНЕД($A2;2)=6;ДЕНЬНЕД($A2;2)=7). В качестве формата я выбрал темно красную заливку.

Создание правила форматирования

Послесловие

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


32 комментария

  1. А можно ли сделать так чтобы значение в ячейке менялось в зависимости от цвета другой ячейки? Например если ячейка залита красным цветом то 0, если зеленым цветом то 1.

    • Эльнур, такую штуку можно реализовать с помощью создания пользовательской функции, например, такой:

      1
      2
      3
      4
      5
      6
      7
      8
      Function FormatColor(rng As Range) As Integer
      Select Case rng.Interior.Color
          Case 255
              FormatColor = 1
          Case 65280
              FormatColor = 0
      End Select
      End Function
  2. Пример с формулой можно упростить (если, конечно, не было цели продемонстрировать именно то, как работает функция ИЛИ). Формула ниже будет делать то же самое:
    =ДЕНЬНЕД($A2;2)>5

  3. Скажите пож-та,мне необходимо что бы при определенном значении,в ячейку тянулся заранее готовый текст!Уже два часа читаю функции,но к сожелению ничего подходящего!Заранее спасибо!

    • Это можно сделать формулой, только если Вы не имеете ввиду, что «уже готовый текст» будет тянуться туда же (заменяя?) имеющееся «определенное значение».

  4. Добрый день!
    Не могу разобраться какое правило выбрать.
    Условие следующее: в одной колонке указан планируемый срок реализации, в следующей — фактический. Если фактический срок превышает плановый (дата более поздняя), то выделение одним цветом, если дата более ранняя или равна дате по плану — другим.

    • Сделала сравнение по функции ЕСЛИ. Но если растягиваю формулу, то все остальные ячейки столбца «срок факт» ссылаются на первую ячейку столбца «срок план.»

  5. Помогите пожауйста. Мне нужно в одном столбце (н) выделить слова ( в моём случае это название профессии) каждую пофесию разным цветом. Ищу возможность чтоб не закрашивать каждую отдельно строку. Заранее спасибо.

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

    Файл:  .xlsx

  7. Народ, выручайте. Нужно вернуть определенное значение из ячейки и посчитать балл, т.е. например в ячейке D3 может быть значение А, Б, В, Г, надо в ячейку D4 вернуть значение в зависимости от буквы, например А=1, Б=2, В=3 и так далее. Как сделать?

  8. Здравствуйте!

    Может быть Вы знаете, как можно дополнить стандартные наборы значков Эксель 2010 своими (пользовательскими) значками? Как добраться к этим наборам значков чтобы их изменить или дополнить? К сожалению значков из стандартных наборов не хватает.

    Максим

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

      • добрый день. стояла такая же проблема — в наборе значков условного форматирования нет зеленой стрелки вниз и красной вверх — а они для Дашборда тоже нужны )

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

  9. Добрый день.
    Очень нужна помощь!
    Прописать условное форматирование: показать фамилии консультантов, у которых перевыполнен план продаж и кол-во рабочих часов более 100.
    Мозг кипит, никак не могу понять как сопоставить в форматировании несколько задач.
    Заранее спасибо!

    Файл:  .xlsx

  10. Добрый день помогите написать макрос, я что то пытался не получилось, в общем надо так, есть таблица в которой есть цифры, надо сделать так чтобы от 1-29 закрашивалось красным, от 30-69 желтым, от 70 и выше зеленым, чтобы было 3 цвета только, пытался попробовать сделать еще через условное форматирование, но там градиентно закрашивает, а мне надо именно только 3 цвета, заранее спасибо!

  11. А я сделала через условное форматирование. Управление правилами. Там можно создавать и менять и удалять правила для выбранных ячеек и цвет самому выбирать.

  12. У меня 2 столбца в одном набор цифр — план, в другом факт.
    И мне нужно оценивать факт. Сделать функцию если больше чем то что в ячейке План закрашивалась красным и цифра была тёмно красной. Если Вышло меньше или равно то ячейка закрашивалась зелёным а цифры тёмно зеленые.

    никак не скумекаю как это сделать

  13. Добрый день, подскажите как сделать, чтобы цвет ячейки менялся при отрицательных значениях, но когда мы меняем значение на положительное, ячейка свой цвет сохраняла как при отрицательных?

  14. Здравствуйте. Помогите, я новичок в формулах и форматировании. Суть: имеется таблица, верхние ячейки с права на лево- это даты от 1 до 30(31): условное форматирование( сегодня). В низ идут по два столбца, в левом числа, в правом на против каждого числа вводится текст произвольный( например А- август или С сентябрь). Теперь главное. Столбцы вниз заполнены не полностью. Нужно чтобы пустые ячейки слева окрашивались если просрачивается дата в верхней ячейке, но чтобы не окрашивались те пустые ячейки слева, справа которых в ясейках нет чисел.

  15. Здравствуйте. Помогите, я новичок в формулах и форматировании. Суть: имеется таблица, верхние ячейки с права на лево- это даты от 1 до 30(31): условное форматирование( сегодня). В низ идут по два столбца, в левом числа, в правом на против каждого числа вводится текст произвольный( например А- август или С сентябрь). Теперь главное. Столбцы вниз заполнены не полностью. Нужно чтобы пустые ячейки слева окрашивались если просрачивается дата в верхней ячейке, но чтобы не окрашивались те пустые ячейки слева, справа которых в ясейках нет чисел, до того пока справа небудут внесены числа

  16. Здравствуйте!
    Такой вопрос: как применить условное форматирование «<" к диапазону ячеек?

    Допустим у нас есть ячейки A1 — A10 с числами от 1 до 1 и так же диапазон ячеек G1 — G10 с числами от 2 до 2(НАПРИМЕР!!)
    Я понял, что можно применить форматирование к одной ячейке, но как это сделать ко всем сразу? (к 10)

  17. Здравствуйте, собственно вопрос:
    Есть список с датами, надо чтобы за 10 дней до этой даты ячейка подсвечивалась например оранжевым цветом, а на следующий день уже красным(просрочено). Как реализовать это?Заранее спасибо!

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

Добавить комментарий для Светлана Отменить ответ

Ваш e-mail не будет опубликован. Обязательные поля помечены *