Как сделать условное форматирование в Excel? Инструкция с примерами.
В этой статье вы найдете множество быстрых способов условного форматирования отдельных строк, столбцов и ячеек в MS Excel 2016, 2013 и 2010. Мы увидим, как вы можете применять различные форматы к данным, которые соответствуют определенным критериям. Это может помочь определить самую важную информацию в ваших электронных таблицах.
Всем известно, что изменить фон ячейки несложно. Это можно сделать, просто нажав кнопку «Цвет заливки». Но что, если вы хотите изменить внешний вид своей таблицы при выполнении некоторого условия? Кроме того, что, если вам нужно, чтобы он менялся автоматически при внесении изменений в таблицу? Условное форматирование для этого — действительно мощная и полезная функция. Далее в этой статье вы найдете ответы на эти вопросы и прочтете несколько полезных советов, которые помогут выбрать правильный метод условного форматирования для каждой конкретной задачи.
При этом изменение внешнего вида за счет содержимого текущей ячейки или какой-то другой ячейки, или других условий, часто считается одной из самых сложных и непонятных функций, особенно для новичков. Если эта функция вас тоже пугает, не бойтесь! На самом деле он очень удобен и прост в использовании, и вы в этом убедитесь уже через 5 минут после прочтения этого руководства. А теперь посмотрите, как много мы можем сделать!
Кроме того, если вы используете условное форматирование, обратите внимание, что оно имеет приоритет над обычным ручным форматированием, которое вы можете сделать через меню «Пуск» — «Формат.
Вы можете применить условное форматирование к одной или нескольким позициям, строкам, столбцам или всей таблице в зависимости от ее содержимого или при выполнении какого-либо другого условия. Это делается путем создания правил (условий), в которых вы определяете, когда и как изменять внешний вид выбранных ячеек таблицы.
Где находится форматирование по условию в Excel?
Очень просто: на вкладке «Главная», а в более ранних версиях — в группе «Стили».
Эта функция включает в себя стандартный набор предопределенных правил и инструментов. Но самое главное, у пользователя есть возможность придумать и настроить нужный алгоритм выделения и затенения по собственным формулам.
Теперь, когда вы знаете, как включить условное форматирование в Excel, давайте продолжим и посмотрим, какие параметры форматирования у вас есть и как вы можете создавать свои собственные правила.
Как автоматически изменить цвет при помощи условного форматирования?
Чтобы по-настоящему использовать возможности условного форматирования в Excel, вам нужно научиться создавать различные типы правил.
Правила условного форматирования определяют 2 ключевых момента:
- К каким ячейкам применить условное форматирование,
- Какие условия должны быть соблюдены.
Я собираюсь показать вам, как применять условное форматирование в Excel 2016, потому что в наши дни это самая популярная версия. Однако он практически идентичен формату версий 2007, 2013 и 2010. Поэтому у вас не возникнет проблем с выделением нужной информации, независимо от того, какая версия установлена на вашем компьютере.
Задача: у вас есть таблица или набор данных, и вы хотите изменить фон ячеек в зависимости от их содержимого. Кроме того, вы хотите, чтобы он менялся динамически, чтобы отражать изменения данных.
Решение: Предположим, у вас есть таблица, содержащая данные о продажах шоколада различным покупателям. Необходимо раскрасить ячейки с количеством в таблице Excel следующим образом: менее 100 единиц товара – красным, 100 и более – зеленым.
Итак, что вы делаете шаг за шагом:
Способ 1 — Используем стандартные возможности.
Проще всего использовать стандартные правила выделения ячеек. К таким заготовкам относятся самые простые и распространенные случаи. Но сначала выберите таблицу или диапазон, где вы хотите изменить фон ячейки. Берем $D$2:$D$21.
Перейдите на вкладку «Главная» и выберите (1) > «Правила выбора ячеек» (2) > «Меньше чем» (3). В старых версиях программы нужное нам меню находится в группе «Стили».
Конечно, вы можете использовать любой другой тип правила, который больше подходит для ваших данных, например:
- Значение больше, меньше или равно.
- Выберите текст, который содержит определенные слова или символы.
- Выделите дубликаты.
- Формат конкретных дат.
В диалоге укажите, что числа должны быть меньше 100, также выберите опцию выбора.
В первом поле указывается условие, а во втором поле указывается способ форматирования результата. Обратите внимание, что вы можете выбрать цвет фона и текста из предложенных в списке. Но если вы хотите применить другие оттенки, вы можете сделать это, перейдя в «Пользовательский формат».
В результате ячейки таблицы с числом меньше 100 стали красными.
Приступим к созданию второго правила. С той же областью таблицы проделайте те же операции, только выберите пункт «Еще» на третьем шаге».
В результате получаем нужную нам раскраску.
Это самый простой вариант заполнения ячеек.
С помощью «Правил выбора ячеек» мы используем»:
- найти в таблице числа, которые больше заданного;
- выбирать те, которые меньше определенного;
- указать числа, которые находятся в нужном диапазоне;
- определить равным некоторому числу;
- отметить в выделенных текстовых полях только те, которые необходимы;
- отметить столбцы и цифры на нужную дату;
- найти повторяющийся текст или цифры;
- придумать другие правила.
Способ 2 — Как самому создать правило форматирования?
Мы можем получить тот же результат немного другим способом. Если ни одно из предопределенных правил форматирования не подходит вам, вы можете создать новое с нуля. Для этого снова перейдите на вкладку Главная и выберите (1 на изображении) > Создать правило (2).
Затем выберите «Форматировать только те ячейки, которые содержат» (3). Чуть ниже укажите, что число должно быть меньше (4) числа «100» (5).
А затем указывает, как это должно выглядеть. Нажмите кнопку «Формат» (6).
В открывшейся вкладке «Заливка» выберите «красный».
Нажмите ОК».
При создании правила в окне «Формат ячеек» переключайтесь между вкладками «Шрифт», «Граница» и «Заливка», чтобы выбрать стиль шрифта, стиль границы и цвет фона соответственно. На вкладках «Шрифт» и «Тень» вы сразу же увидите предварительный просмотр своего пользовательского форматирования.
Когда вы закончите, нажмите кнопку ОК в нижней части окна.
Прямо сейчас:
Если вам нужно больше цветов фона или шрифта, чем предусмотрено в стандартной палитре, нажмите кнопку «Другие цвета…» на вкладках «Заливка» и «Шрифт»..
Если вы хотите применить градиент цвета фона, нажмите кнопку «Методы заливки» и выберите нужные параметры.
Нажмите OK, чтобы закрыть окно и проверить, правильно ли условное форматирование применяется к вашим данным.
Сделайте то же самое еще раз, только измените условие: число должно быть больше или равно 100. И новый цвет условного форматирования, конечно же, выберите теперь зеленый.
Способ 3 — Применяем собственную формулу в правиле условного форматирования.
И, наконец, третий способ — самый сложный, но самый универсальный и с большими возможностями. Чуть ранее мы создали правила форматирования, указав определенные числа, дату или текст. Однако в некоторых случаях имеет смысл основывать условие на значении конкретной ячейки. Преимущество этого подхода заключается в том, что в зависимости от того, как значение этой ячейки изменится в будущем, ее условное форматирование будет автоматически корректироваться, чтобы отразить изменение данных.
Снова перейдите на вкладку «Главная» (в старых версиях программы в группе «Стили») и выберите (1) > «Новое правило» (2).
Затем выберите «Использовать формулу для определения форматированных ячеек» (3). Теперь нам нужно указать диапазон, в котором мы хотим что-то выделить. Для этого нажмите на иконку со стрелкой вверх (4) и наведите мышку на начало диапазона — D2. Убедитесь, что ссылка не является абсолютной (для этого можно использовать F4). И в конце просто добавьте условие: «
Остается только определить новые правила форматирования. Нажмите кнопку «Формат» (6).
Выберите красный цвет на вкладке «Заливка».
Повторите создание условия еще раз, просто введите выражение D2>=100 и выберите зеленый.
Вы спросите: «Почему все так сложно, если есть вариант попроще?» Дело в том, что использование формулы является более универсальным подходом, который мы будем применять неоднократно в дальнейшем.
Итак, цель достигнута: фон выделенных ячеек меняется от их содержимого.
Совет: Вы можете использовать тот же метод не только для раскрашивания, но и для изменения внешнего вида шрифта. Для этого просто перейдите на вкладку «Шрифт» в диалоговом окне «Формат», которое мы обсуждали на шаге 6, и выберите предпочтительный вариант макета.
Условное форматирование Excel по значению ячейки.
В двух приведенных выше примерах мы создали правила форматирования, напрямую указав числа: ограничения. Но в большинстве случаев вам нужно создать критерии форматирования на основе значений ячеек. Как это сделать? Предположим, в таблице записаны ежемесячные продажи различных продуктов. Необходимо выделить цветом те декабрьские числа, которые были больше, чем январские, в начале года.
Выберите область для применения условного форматирования M2:M16, а затем выберите «Создать правило». В описании правила пишем выражение:
=М2>В2
Обратите внимание, что здесь используются относительные ссылки для того, чтобы программа могла последовательно перебирать все ячейки в заданной ей области, и при этом каждой ячейке в столбце М соответствовала ячейка в столбце В, расположенная в той же строке и относящаяся к тот же продукт.
Настройте отображение выделенных ячеек так же, как мы обсуждали ранее.
Как видно на рисунке, созданное нами правило условного форматирования работает корректно и выделяет продажи за декабрь для тех товаров, которые выросли по сравнению с январем.
Использование абсолютных и относительных ссылок в правилах.
Для того, чтобы было проще изменить условия выделения тех или иных значений в таблице Excel, пропишем некоторые параметры выделения в специально отведенных для этого ячейках.
Задача: выбрать в таблице заказы с количеством меньше 50 и больше 100 единиц.
Мы записываем наши ограничения в D1 и D2. Далее мы создаем первое правило условного форматирования для диапазона E5:E24.
=E5>$D$2
Абсолютная ссылка на D2 означает, что каждая ячейка в нашем диапазоне сравнения должна сравниваться с D2. И относительная ссылка на первую ячейку выбранной нами области E5 говорит программе начать с этой позиции и двигаться последовательно вниз по столбцу, сравнивая число с пороговым значением 100.
Как обычно, выбираем цвет заливки, если условие выполнено.
Аналогично для E5:E24 мы создаем второе правило
=E5
В результате часть столбца будет окрашена в зеленый цвет, часть в желтый, а число от 50 до 100 останется неокрашенным.
А теперь усложним задачу: будем раскрашивать не отдельные ячейки, а целые строки таблицы. Для этого нам просто нужно изменить несколько привязок в наших правилах.
Во-первых, давайте переопределим диапазон условного форматирования. Теперь это будет $A$5:$G$24.
Давайте внесем небольшое изменение в правило форматирования:
=$E5>$D$2
Как видите, у нас есть абсолютная ссылка на столбец E. А ссылка на строку по-прежнему относительная, без знака $. Для программы это означает, что она должна использовать данные всей строки и раскрашивать ее тоже, а не отдельную ячейку.
Точно так же мы меняем второе условие с E5
При этом ссылка на D2 остается абсолютной, так как в эту ячейку записывается условие. В итоге получаем «полосатую» таблицу, где цветом уже выделены целые строки. А вся хитрость заключается в грамотном использовании абсолютных ссылок в правилах.
Производство. Попробуем запомнить простые принципы использования ссылок в правилах:
- если 2 столбца сравниваются попарно, используются относительные ссылки (M2>B2).
- если значения столбца сопоставляются с определенной ячейкой, то она должна иметь абсолютную ссылку ($D$1).
- когда нужно условно закрасить всю строку, то ссылка на эту строку должна быть относительной ($E5)
- когда вам нужно раскрасить весь столбец, ссылка на него должна быть относительной (E $ 5)
Как использовать в правилах ссылку на соседние листы?
В последних версиях, начиная с 2010, в формулах условий можно смело использовать ссылки на данные с других листов. Делается это точно так же, как и в обычных формулах.
В более ранних версиях программы, 2007 и 2003, это ограничение можно было легко обойти, используя именованные диапазоны. Просто дайте определенные имена диапазонам на текущем листе или соседних листах, а затем используйте эти имена в функциях.
В частности, вместо
=ЕСЛИ(‘Формат (Лист2)’!$E$2:$E$21>5000,1,0)
можно работать с формулой
=ЕСЛИ(продажи>5000,1,0)
Как видите, диапазон «Формат (Лист2)»! $E$2: $E$21 был назван «Продажи», и теперь к нему можно получить доступ из любой точки вашей рабочей книги.
Приоритет выполнения правил — это важно!
При использовании условного форматирования в Excel вы не ограничены одним правилом для каждой ячейки. Вы можете применять столько правил, сколько требует логика вашего проекта. В том случае, если в вашей таблице используется несколько правил, важно, в каком порядке они выполняются.
Если вы выберете меню «Управление правилами» и выберите там «Текущий лист», вы увидите список доступных правил.
В этой таблице мы хотим выделить предстоящие отгрузки в ближайшем будущем желтым цветом, а те, которые должны быть отправлены сегодня или завтра, будут отмечены красным. Ведь им следует уделить повышенное внимание и срочно нужно их реализовать.
Давайте сначала создадим первое условие:
=$E5>$C$2
Как видите, здесь находятся все строки, в которых дата отгрузки больше текущей даты, записанной в ячейке C2.
Затем мы создаем второе условие, которое, так сказать, будет подмножеством первого. Мы выбираем только те ячейки, в которых либо дата отгрузки равна текущей дате $E$5=$C$2, либо дата отгрузки больше текущей даты на 1 день $E5-$C$2=1. Если хотя бы одно из этих требований выполнено, линия будет окрашена в красный цвет.
=ИЛИ($E5-$C$2=1; $E$5=$C$2)
Важно! Правила, расположенные выше в списке, имеют более высокий приоритет (1 и 2 на рисунке выше). Новые правила всегда добавляются в начало списка и поэтому имеют более высокий приоритет. Результат вашей работы не может быть изменен действием приведенных ниже правил.
Однако порядок выполнения всегда можно изменить в том же окне с помощью стрелок вверх и вниз (3).
Как редактировать условное форматирование?
Чтобы изменить ранее созданное условие, нужно сначала посмотреть, какие условия мы применили к таблице, а затем просто выбрать нужное правило. Последовательность действий такая же, как мы обсуждали выше. Но на всякий случай еще раз повторю на скриншоте: нам нужен раздел «Управление правилами», тогда указываем, что рассматриваем текущий лист.
При нажатии на иконку «Редактировать…» мы попадаем в уже знакомое нам меню создания правила. Просто все поля уже заполнены текущими значениями. Остается только изменить то, что необходимо и нажать «ОК».
А если забыл, где какие правила создавал?
Поскольку этот метод имеет приоритет над обычным макетом, вы можете получить таблицу, которая будет выглядеть не так, как вы ожидали. Особенно, если вы забыли, где и какие правила вы создали. Итак, как нам быстро найти все ячейки с условным форматированием в таблице?
Один из самых простых способов найти эти нестандартные места в таблице — использовать меню «Пуск» — «Найти и выбрать» —…. в последних версиях Excel. Или Главная — Редактировать — Найти и выбрать — . в более ранних версиях.
Но в результате вы увидите только те области таблицы, где применено условное форматирование. И не более того. Каковы именно условия смены дизайна, пока неизвестно. В любом случае, вам, скорее всего, придется копнуть глубже и узнать, какие условия там действуют.
Поэтому лучше просто выбрать раздел «Управление правилами» — текущий лист. Мы уже дважды описывали этот процесс в предыдущих разделах, так что думаю тут проблем не возникнет.
Вы увидите все созданные вами правила, а также приоритет их выполнения. Помните, что правила вверху списка имеют наивысший приоритет: чем выше, тем важнее. Также указаны области, к которым относятся созданные форматы. Я думаю, что это будет довольно легко понять здесь.
Как можно скопировать условное форматирование?
Вот несколько способов копирования правил.
Копировать формат живописец
Его можно скопировать так же, как и обычный формат.
На вкладке «Главная» в верхней части ленты есть группа «Буфер обмена». В нем вы видите иконку кисти — формат в зависимости от модели (в разных версиях выглядит по разному, но называется одинаково). При ее нажатии копируется не только форматирование выделенных ячеек, но и условия для него, если они есть. Следующим шагом является выделение ячеек, в которые следует перенести этот макет.
Обратите внимание, что описанный способ перенесет абсолютно все форматы, в том числе установленные вручную.
Копировать вставкой.
Альтернативой дублирующему форматированию является специальный метод вставки.
Скопируйте ячейки с нужным условным форматированием любым привычным для вас способом. Выберите диапазон, в который вы хотите перенести форматирование (вы также можете выбрать несмежные, удерживая CTRL), затем щелкните правой кнопкой мыши и выберите «Специальная вставка…». Затем программа выдаст окно, в котором нужно установить переключатель в положение «форматы», после чего нажать «ОК».
Управление правилами.
Вы можете использовать диспетчер правил.
Пройдите по следующему пути: -> «Управление правилами…».
В раскрывающемся списке «Показать правила…» выберите «Этот лист». Вы сможете увидеть все правила, применимые к текущему листу.
В столбце «Применимо к» списка правил указаны диапазоны, к которым применяется каждое правило. Добавьте в это поле требуемые адреса ячеек, разделенные точкой с запятой, чтобы применить к ним созданные выше условия.
Этот метод требует больше труда, чем два предыдущих. Но его прелесть в том, что он позволяет распространять только те правила, которые вам нужны. Это особенно полезно, когда к копируемым ячейкам одновременно применяется несколько условий, и нужно скопировать только одно из них.
Как убрать условное форматирование?
Эта операция так же проста, как создание правила. Выберите, а затем Удалить правила. Вам будет предложено удалить из выбранного диапазона данных или даже удалить все правила на листе. Но имейте в виду, что это приведет к удалению всего, что было создано ранее. Но, возможно, есть что-то, что вы хотели бы сохранить.
Поэтому есть более тонкий инструмент, который мы бы рекомендовали использовать как для их редактирования, так и для удаления.
Пожалуйста, используйте последний пункт в раскрывающемся меню: «Управление правилами».
Здесь вы можете увидеть все правила на текущем листе, к каким диапазонам они принадлежат и что они делают. Поэтому гораздо проще выбрать определенное правило и удалить его.
Или поменять при необходимости.
Почему не работает?
Если вы не получили ожидаемого результата, первое, что вы должны сделать, это убедиться, что созданное вами правило условного форматирования работает правильно. Для этого можно скопировать формулу правила в любую пустую ячейку и посмотреть, что получится в результате. Если вы условно форматируете целый столбец чисел, выберите пустое место справа от таблицы.
Если условная формула оценивается как ИСТИНА, необходимо применить условное форматирование. Конечно, если ЛОЖЬ, то нет. Вернемся к одной из наших задач и немного отладим правила форматирования.
В столбец I копируем формулу первого условия, в К — второго. Зацепите правый нижний угол ячейки с формулой мышкой и перетащите ее вниз на всю высоту таблицы. Давайте получим полное представление о каждой из ячеек нашего диапазона. Как видите, ИСТИНА и ЛОЖЬ точно соответствуют цвету столбца К, который мы собственно и проверяем. В I2 мы получили ИСТИНА, поэтому цвет зеленый. В J9 ответ тоже да, поэтому цвет желтый. Так далее
Если формула сложная, вы можете разделить ее и использовать тот же метод отладки.
Мы надеемся, что вы нашли ответы на свои вопросы об условном форматировании в нашем руководстве.
Однако, если что-то все еще не работает или не работает, пожалуйста, напишите в комментариях ниже. Мы постараемся ответить вам или даже сделать отдельную статью, посвященную вашей проблеме.
Удачи!
