- Создание условного форматирования в Excel
- Правила выделения ячеек в Excel при помощи условного форматирования с примерами
- Цветовые шкалы в условном форматировании excel как сделать. Правила использования
- Условное форматирование значками
- Гистограммы в условном форматировании
- Условное форматирование по значению другой ячейки — как использовать формулу в условном форматировании
- Условное форматирование по датам
- Условное форматирование несколько условий
- Как скопировать условное форматирование
- Как убрать условное форматирование из ячейки и со всего листа
- Где исправить диапазон условного форматирования
В этой статье мы узнаем, как сделать условное форматирование для ячейки, строки и столбца в ms Excel.
Условное форматирование — это автоматический способ выделения данных в зависимости от их значений. Пример условного форматирования показан на картинке ниже, где при помощи цвета сделан акцент на количестве проданных единиц товара — чем “краснее”, тем больше спрос на этот товар, и наоборот.
Условное форматирование можно использовать не только с числовыми, но и текстовыми данными и с датами. Также можно сделать условное форматирование в сводной таблице.
Создание условного форматирования в Excel
Где найти условное форматирование в Excel? Оно находится на вкладке Главная — блок Стили — Условное форматирование.
В выпадающем списке на кнопке Условное форматирование можно выбрать разные виды форматирования — далее в статье мы разберемся в них.
Правила выделения ячеек в Excel при помощи условного форматирования с примерами
Это самый простой способ сделать условное форматирование. Рассмотрим на примере.
Выделим данные, к которым нам нужно применить условное форматирование.
Перейдем в пункт меню Условное форматирование — Правила выделения ячеек. Откроется вложенное меню, в котором можно выбрать правило условного форматирования.
Выберем пункт Больше. “Больше” в данном случае означает, что будут выделены все значения в выделенном диапазоне, которые больше заданного числа.
Укажем пороговое значение (например, 250) и выберем из выпадающего списка справа один из предустановленных форматов. Также можно настроить свой формат в пункте Пользовательский формат.
Аналогично работают и другие стандартные Правила выделения ячеек в Excel:
- Больше — выделяет значения больше указанного значения
- Меньше — соответственно, меньше.
- Между — выделяем значения в диапазоне указанных значений. Причем оба значения входят в этот диапазон.
- Равно — соответственно, ячейки, значения которых равны некому значению.
- Текст содержит — очень удобный способ подсветить ячейки, текст которых содержит определенное слово (или часть слова). Обратите внимание, здесь именно содержит, а не равно.
- Дата — подсветить в списке определенные даты (рассмотрим ниже)
- Повторяющиеся значения — возможность выделить дубликаты
Цветовые шкалы в условном форматировании excel как сделать. Правила использования
Условное форматирование в excel при помощи цветовой шкалы можно сделать, если перейти в меню Условное форматирование — Цветовые шкалы, и далее выбрать нужную шкалу.
Для примера выделим в нашей таблице столбец “Цена за ед.” и перейдем в Условное форматирование — Цветовые шкалы — далее выбрать подходящую цветовую шкалу.
В примере мы выбрали шкалу Красный-Белый-Синий.
Как видите, excel выводит подсказку — оттенок цвета зависит от значения по сравнению с другими ячейками в диапазоне. У нас самые большие значения цены выделены красным, а самые маленькие — синим.
Чтобы не путаться в цветовых шкалах, смотрим на шкалу и понимаем — самые большие значения в выделенном диапазоне будут того цвета, который сверху. Самые маленькие — тем цветом, который снизу.
На примере трехцветной шкалы, самые большие значения будут зеленые, самые маленькие — красные, средние — желтые, а остальные — градиенты этих цветов.
Цветовые шкалы в условном форматировании excel можно настраивать самостоятельно. Для настройки цветовой шкалы нужно щелкнуть на любой ячейке с форматированием и перейти в Условное форматирование — Управление правилами. Далее нужно дважды щелкнуть на нужном правиле, чтобы “провалиться” в него.
И здесь в нижнем блоке можно выбирать из выпадающих списков нужные цвета.
Например, если вы хотите сделать акцент “кто лучше” — не выбирайте красные или красно-синие оттенки шкалы. Лучше выбрать зеленые.
Условное форматирование значками
Чтобы сделать условное форматирование значками (светофор, стрелки, флажки и т.д.), нужно выделить диапазон, перейти в меню Условное форматирование — Наборы значков — выбрать подходящий набор.
Как видите, возле значений появились значки выбранного набора (светофор). Чтобы понять, как работает это правило, нужно “провалиться” в него (Условное форматирование — Управление правилами — двойной щелчок на привиле).
Что в данном случае означают 67 и 33 процента, если процентов в нашей таблице нет?
В стандартном варианте, зеленый значок присваивается значениям, величина которых составляется больше или равно 67% от максимума.
Красный значок — значениям, величина которых составляет 33% и меньше от максимума.
Желтый — значения между ними.
В формулах на картинке наглядно показано, как рассчитываются пороговые значения.
Пороговые значения по умолчанию можно менять. К тому же, вместо процентов можно выбрать другие величины, например число или процентиль.
Гистограммы в условном форматировании
Еще один вариант использования условного форматирования в Excel — гистограммы.
В примере выделим столбец Выручка, и перейдем в Условное форматирование — Гистограммы — выбрать нужный вид гистограммы.
Гистограммы бывают с градиентной (полупрозрачной) и сплошной заливкой.
Наиболее удачное использование гистограмм — когда данные имеют очень большой разброс.
Если числа находятся в одном небольшом диапазоне, использование гистограмм по умолчанию будет не показательно — в этом случае нужно настраивать пороговые значения вручную внутри правила (по аналоги со значками).
Условное форматирование по значению другой ячейки — как использовать формулу в условном форматировании
Если нужно сделать условное форматирование в Excel с использованием формулы, то можно воспользоваться следующими способами:
Способ 1. При помощи Правила выделения ячеек.
В первом пункте в Правилах выделения ячеек мы рассматривали вариант с фиксированным значением в качестве критерия условного форматирования. Но точно так же критерием можно задать и ссылку на ячейку.
Добавим в ячейку Е1 критерий для выручки — значение 50000, и выделим все ячейки, значение которых меньше 50000.
Для этого выделим столбец Выручка, перейдем в Правила выделения ячеек, в левом поле сотрем числовое значение и, не убирая курсор с этого поля, щелкнем по ячейке Е1 (где находится критерий).
Готово, выделены значения меньше 50000.
Теперь если изменить в ячейке Е1 значение, то условное форматирование автоматически перекрасит ячейки. Укажем в Е1 = 30000 — теперь выделена только одна ячейка, значения которой меньше 30000.
Способ 2. Форматирование в excel — использовать более сложную формулу
Иногда в качестве условия форматирования нужно задать более сложную конструкцию. В этом случае можно использовать формулу для форматирования в excel.
Для форматирования ячейки в excel формулой перейдем в пункт меню Условное форматирование — Создать правило — Использовать формулу для определения форматируемых ячеек — в поле ввода написать формулу.
Например, нужно выделить все наименования, у которых цена выше средней, и при этом выручка более 100 тыс. Должно одновременно выполняться оба условия.
Напишем следующую формулу:
После нажатия кнопки Ок, будут выделены только те наименования, у которых выполняются оба условия.
Условное форматирование по датам
Для примера возьмем таблицу с датами поставок. Задача сделать условное форматирование даты, соответствующей определенному критерию, цветом. Например, нужно выделить цветом поставки, у которых дата наступает завтра.
Для наглядности сверху таблицы выведена сегодняшняя дата (хотя в данном случае в условном форматировании она не участвует).
Выделяем столбец с датами и переходим в меню Условное форматирование — Правила выделения ячеек — Дата.
В окне из выпадающего списка выбираем критерий даты Завтра.
Критериев, которые заданы по умолчанию, иногда недостаточно, чтобы сделать условное форматирование даты. Например, если нужно выделить даты поставок, которые наступят в ближайшие 3 дня, придется воспользоваться формулой.
Меню Условное форматирование — Создать правило — Использовать формулу для определения форматируемых ячеек.
Напишем следующую формулу.
Теперь подсвечены даты, которые больше сегодняшней даты на 3 дня и меньше.
Условное форматирование несколько условий
Чтобы сделать условное форматирование и использовать несколько условий, можно воспользоваться способом Правила выделения ячеек или способом с Условным форматирование формулой.
Рассмотрим на примере Правила выделения ячеек.
Допустим, нужно выделить желтым цветом цены товаров выше 100 руб, и красным — цены выше 200 руб.
Выделяем диапазон ячеек и переходим в меню Условное форматирование — Правила выделения ячеек — Больше. Указываем значение 100 и выбираем формат.
Выделены все ячейки, т.к.все цены больше 100 руб.
Чтобы выделить цены выше 200 руб, снова идем в меню по тому же пути и указываем уже 200, выбрав красный формат.
Цены выше 200 руб. выделены красным.
А теперь перейдем к главной хитрости условного форматирования с несколькими условиями — очередность правил (здесь делают ошибки все новички).
Перейдем в меню Условное форматирование — Управление правилами.
Правило, которое выделяет ячейки более 200 находится сверху — это значит, что у него приоритет над правилом “более 100”.
Но что будет, если мы поменяем очередность правил? Это можно сделать, выделив правило и нажав стрелочку сверху.
Как видите, правила все те же, изменился только порядок — но теперь все значения выделены только желтым цветом.
Причина тому — правило “более 100” теперь имеет приоритет над правилом “более 200”, и отрабатывается в первую очередь. А правило “более 200” сработает только для тех значений, которые не попали под критерий “более 100”. А так как все значения в таблице попадают под первый критерий “более 100”, до второго правила алгоритм просто не доходит.
Именно поэтому, если вы делаете условное форматирование в Excel с несколькими условиями, нужно обязательно следить за очередностью правил.
Как скопировать условное форматирование
Скопировать условное форматирование можно при помощи инструмента Формат по образцу, который находится на вкладке Главная.
Если скопировать ячейки, к которым применено условное форматирование, то оно скопируется вместе с ячейками.
Как убрать условное форматирование из ячейки и со всего листа
Чтобы убрать условное форматирование только из определенных ячеек, нужно выделить эти ячейки и перейти в меню Условное форматирование — Удалить правила — Удалить правила из выделенных ячеек.
Если нужно убрать условное форматирование со всего листа, в том же пункте меню выбираем Удалить правила со всего листа.
Где исправить диапазон условного форматирования
Для примера возьмём ту же таблицу, в которой мы выделяли даты “плюс 3 дня от сегодняшней даты”. Добавим новую строку, дата которой также соответствует критерию условного форматирования.
Но для новой строки условное форматирование не сработало — дата не выделена цветом.
Причина — новая строка не попадает в ранее заданный диапазон.
Чтобы исправить диапазон условного форматирования:
— щелкнем на любой другой ячейке, для которой форматирование работает
— перейдем в меню Условное форматирование — Управление правилами
Если щелкнуть в поле Применяется к напротив нужного правила, то будет выделен бегущим выделением диапазон форматирования — новая ячейка в него не попала.
Чтобы это исправить, сотрем содержимое поля Применяется к и, не выходя из него, выделим новый диапазон, захватив новую ячейку.
Теперь для новой ячейки условное форматирование сработало.
В этой статье мы узнали, как сделать условное форматирование в Excel и познакомились с основными его видами.