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

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

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

Найти «Условное форматирование» можно на самой первой вкладке ленты, перейдя в раздел «Стили». 

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

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

Далее мы указали серию чисел в диапазоне A1:A11.

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

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

Далее завершите настройку кнопкой ОК.

Для чего нужно условное форматирование?

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

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

Возьмем пример.

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

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

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

И действительно, ориентироваться гораздо проще. 

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

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

По значению другой ячейки

Теперь давайте рассмотрим следующий практический пример.

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

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

Последовательность наших действий в данной ситуации будет следующая:

1. Выберите диапазон значений. 
2. Нажмите кнопку «Условное форматирование» — «Создать правило».
3. Обратите внимание, что в нашем случае нужно применить формулу в виде правила. Далее мы используем функцию IFчтобы выделить совпадающие линии. 

Далее заполните строки, как показано на этой картинке.

Важно! На строки необходимо ссылаться абсолютно. Если мы говорим о ячейке, то в данном случае она смешанная (с фиксацией столбца).

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

Вот как выглядят наши критерии.

И, наконец, в результате мы имеем вот такую ​​базу данных.

Несколько условий

Для наглядности возьмем из таблицы диапазон A1:A11, чтобы продемонстрировать, как на реальном практическом примере можно использовать несколько условий для определения форматирования ячейки.

Сами условия такие: если число в ячейке превышает значение 6, то оно выделяется красным цветом. Если он зеленый, то цвет зеленый. И, наконец, самые большие числа, более 20, будут выделены желтым цветом. 

Существует несколько способов, как осуществляется условное форматирование по нескольким правилам.

1. Способ 1. Выбираем наш диапазон, после чего в меню настроек условного форматирования выбираем такое правило выделения ячеек, как «Еще». Слева пишется цифра 6, а справа задается форматирование. В нашем случае заливка должна быть красной. После этого цикл повторяется дважды, но уже установлены другие параметры – больше 10 и зеленый цвет и больше 20 и желтый цвет соответственно. Вы получите такой результат.
   

   

2. Способ 2. Заходим в главное меню настроек инструмента Excel «Условное форматирование». Там находим меню «Создать правило» и кликаем по этому пункту левой кнопкой мыши.
   

   

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

   

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

А вот скриншот для лучшего понимания.

Например, у нас есть число 24. Оно отвечает всем трем условиям одновременно. В этом случае будет заливка, удовлетворяющая первому условию, несмотря на то, что она больше подпадает под третье. Поэтому, если важно, чтобы числа выше 20 были заполнены желтым цветом, на первое место нужно поставить третье условие. 

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

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

В этом случае вам необходимо установить правило условного форматирования, например «Дата».

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

Нам остается только выбрать подходящий и нажать кнопку «ОК».

Мы видим, что те даты, которые относятся к последней неделе на момент построения этого диапазона, выделены красным. 

Используя формулы

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

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

Строки (по значению ячейки)

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

Как создать правило

Для создания правила необходимо использовать соответствующую опцию в разделе «Условное форматирование». Давайте рассмотрим конкретную последовательность действий. 

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

Есть кнопка «Создать правило». Также вы можете нажать кнопку «Управление правилами», после чего откроется диалоговое окно, через которое вы также сможете создать правило, нажав соответствующую кнопку в левом верхнем углу. 

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

Правила выбора ячеек

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

Форматирование всех ячеек на основе их значений

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

Форматировать только уникальные или повторяющиеся ячейки

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

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

Форматировать значения в диапазоне ниже и выше среднего

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

Форматирование только первого и последнего значений

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

Форматирование только ячеек с определенным содержимым

Чтобы отформатировать только ячейки с определенным содержимым, необходимо выбрать правило форматирования «Равно» или «Текст содержит». Разница между ними в том, что в первом случае строка должна соответствовать критерию полностью, а во втором — лишь частично. 

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