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

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

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

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

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

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

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

 

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

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

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

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

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

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

Давайте приведем такой пример.

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

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

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

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

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

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

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

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

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

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

Наша последовательность действий в этой ситуации будет следующей:

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

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

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

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

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

Ну и наконец, такая база данных у нас получается в итоге.

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

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

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

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

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

   

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

   

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

   

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

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

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

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

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

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

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

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

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

С использованием формул

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

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

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

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

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

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

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

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

Также можно ознакомиться в этом окне с теми правилами, которые уже применены.

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

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

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

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

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

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

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

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

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

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

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

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

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

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