Как сделать условное форматирование в Excel

 

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

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

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

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

 

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

 

Где найти условное форматирование в Excel? Оно находится на вкладке Главная — блок СтилиУсловное форматирование.

 

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

 

В выпадающем списке на кнопке Условное форматирование можно выбрать разные виды форматирования — далее в статье мы разберемся в них.

 

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

 

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

 

Это самый простой способ сделать условное форматирование. Рассмотрим на примере.

Выделим данные, к которым нам нужно применить условное форматирование.

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

 

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

 

Выберем пункт Больше. “Больше” в данном случае означает, что будут выделены все значения в выделенном диапазоне, которые больше заданного числа.

Укажем пороговое значение (например, 250) и выберем из выпадающего списка справа один из предустановленных форматов. Также можно настроить свой формат в пункте Пользовательский формат.

 

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

 

Обратите внимание, что в примере выбран формат с зеленой заливкой, т.к. смысл этого форматирования — показать хорошо продаваемые товары. Зеленый цвет вызывает ассоциации с понятием “хорошо”. Однако, если в вашем отчете другая цель (например, на этих же данных показать “горячий спрос”, то лучше выбрать красные оттенки формата

 

Аналогично работают и другие стандартные Правила выделения ячеек в Excel:

  • Больше — выделяет значения больше указанного значения
  • Меньше — соответственно, меньше.
  • Между — выделяем значения в диапазоне указанных значений. Причем оба значения входят в этот диапазон.
  • Равно — соответственно, ячейки, значения которых равны некому значению.
  • Текст содержит — очень удобный способ подсветить ячейки, текст которых содержит определенное слово (или часть слова). Обратите внимание, здесь именно содержит, а не равно.
  • Дата — подсветить в списке определенные даты (рассмотрим ниже)
  • Повторяющиеся значения — возможность выделить дубликаты

 

Цветовые шкалы в условном форматировании excel как сделать. Правила использования

 

Условное форматирование в excel при помощи цветовой шкалы можно сделать, если перейти в меню Условное форматированиеЦветовые шкалы, и далее выбрать нужную шкалу.

 

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

 

Для примера выделим в нашей таблице столбец “Цена за ед.” и перейдем в Условное форматированиеЦветовые шкалы — далее выбрать подходящую цветовую шкалу.

 

В примере мы выбрали шкалу Красный-Белый-Синий.

 

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

 

Как видите, excel выводит подсказку — оттенок цвета зависит от значения по сравнению с другими ячейками в диапазоне. У нас самые большие значения цены выделены красным, а самые маленькие — синим.

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

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

 

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

 

Цветовые шкалы в условном форматировании excel можно настраивать самостоятельно. Для настройки цветовой шкалы нужно щелкнуть на любой ячейке с форматированием и перейти в Условное форматированиеУправление правилами. Далее нужно дважды щелкнуть на нужном правиле, чтобы “провалиться” в него.

 

как сделать условное форматирование в excel
И здесь в нижнем блоке можно выбирать из выпадающих списков нужные цвета.

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

 

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

 

Например, если вы хотите сделать акцент “кто лучше” — не выбирайте красные или красно-синие оттенки шкалы. Лучше выбрать зеленые.

 

Условное форматирование значками

 

Чтобы сделать условное форматирование значками (светофор, стрелки, флажки и т.д.), нужно выделить диапазон, перейти в меню Условное форматированиеНаборы значков — выбрать подходящий набор.

 

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

 

Как видите, возле значений появились значки выбранного набора (светофор). Чтобы понять, как работает это правило, нужно “провалиться” в него (Условное форматированиеУправление правилами — двойной щелчок на привиле).

 

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

 

Что в данном случае означают 67 и 33 процента, если процентов в нашей таблице нет?

В стандартном варианте, зеленый значок присваивается значениям, величина которых составляется больше или равно 67% от максимума.

Красный значок — значениям, величина которых составляет 33% и меньше от максимума.

Желтый — значения между ними.

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

 

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

 

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

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

 

Еще один вариант использования условного форматирования в Excel — гистограммы.

В примере выделим столбец Выручка, и перейдем в Условное форматированиеГистограммы — выбрать нужный вид гистограммы.

 

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

 

Гистограммы бывают с градиентной (полупрозрачной) и сплошной заливкой.

Наиболее удачное использование гистограмм — когда данные имеют очень большой разброс. 

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

Условное форматирование по значению другой ячейки — как использовать формулу в условном форматировании

 

Если нужно сделать условное форматирование в Excel с использованием формулы, то можно воспользоваться следующими способами:

Способ 1. При помощи Правила выделения ячеек. 

 

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

Добавим в ячейку Е1 критерий для выручки — значение 50000, и выделим все ячейки, значение которых меньше 50000.

Для этого выделим столбец Выручка, перейдем в Правила выделения ячеек, в левом поле сотрем числовое значение и, не убирая курсор с этого поля, щелкнем по ячейке Е1 (где находится критерий). 

 

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

 

Готово, выделены значения меньше 50000. 

Теперь если изменить в ячейке Е1 значение, то условное форматирование автоматически перекрасит ячейки. Укажем в Е1 = 30000 — теперь выделена только одна ячейка, значения которой меньше 30000.

 

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

 

Способ 2. Форматирование в excel — использовать более сложную формулу

 

Иногда в качестве условия форматирования нужно задать более сложную конструкцию. В этом случае можно использовать формулу для форматирования в excel.

Для форматирования ячейки в excel формулой перейдем в пункт меню Условное форматированиеСоздать правилоИспользовать формулу для определения форматируемых ячеек — в поле ввода написать формулу.

 

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

 

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

 

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

Напишем следующую формулу:

 

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

 

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

 

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

 

 

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

 

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

Для наглядности сверху таблицы выведена сегодняшняя дата (хотя в данном случае в условном форматировании она не участвует).

Выделяем столбец с датами и переходим в меню Условное форматированиеПравила выделения ячеекДата.

 

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

 

В окне из выпадающего списка выбираем критерий даты Завтра

 

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

 

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

Меню Условное форматирование Создать правилоИспользовать формулу для определения форматируемых ячеек.

Напишем следующую формулу.

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

Теперь подсвечены даты, которые больше сегодняшней даты на 3 дня и меньше.

 

Условное форматирование несколько условий

 

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

 

Рассмотрим на примере Правила выделения ячеек.

 

Допустим, нужно выделить желтым цветом цены товаров выше 100 руб, и красным — цены выше 200 руб.

Выделяем диапазон ячеек и переходим в меню Условное форматированиеПравила выделения ячеекБольше. Указываем значение 100 и выбираем формат.

 

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

 

Выделены все ячейки, т.к.все цены больше 100 руб.

Чтобы выделить цены выше 200 руб, снова идем в меню по тому же пути и указываем уже 200, выбрав красный формат.

 

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

 

Цены выше 200 руб. выделены красным.

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

Перейдем в меню Условное форматированиеУправление правилами

Правило, которое выделяет ячейки более 200 находится сверху — это значит, что у него приоритет над правилом “более 100”.

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

Но что будет, если мы поменяем очередность правил? Это можно сделать, выделив правило и нажав стрелочку сверху.

 

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

 

Как видите, правила все те же, изменился только порядок — но теперь все значения выделены только желтым цветом.

 

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

 

Причина тому — правило “более 100” теперь имеет приоритет над правилом “более 200”, и отрабатывается в первую очередь. А правило “более 200” сработает только для тех значений, которые не попали под критерий “более 100”. А так как все значения в таблице попадают под первый критерий “более 100”, до второго правила алгоритм просто не доходит.

 

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

 

Как скопировать условное форматирование

 

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

 

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

 

Если скопировать ячейки, к которым применено условное форматирование, то оно скопируется вместе с ячейками.

 

Как убрать условное форматирование из ячейки и со всего листа

 

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

 

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

 

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

 

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

 

Для примера возьмём ту же таблицу, в которой мы выделяли даты “плюс 3 дня от сегодняшней даты”. Добавим новую строку, дата которой также соответствует критерию условного форматирования. 

Но для новой строки условное форматирование не сработало — дата не выделена цветом.

 

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

 

Причина — новая строка не попадает в ранее заданный диапазон.

Чтобы исправить диапазон условного форматирования:

— щелкнем на любой другой ячейке, для которой форматирование работает  

— перейдем в меню Условное форматирование — Управление правилами

 

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

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

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

Теперь для новой ячейки условное форматирование сработало.

 

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

В этой статье мы узнали, как сделать условное форматирование в Excel и познакомились с основными его видами.