Как сделать условное форматирование в Excel? Инструкции с примерами.
В этой статье вы найдете множество быстрых способов условного форматирования строк, столбцов и отдельных ячеек в MS Excel 2016, 2013 и 2010. Мы увидим, как применять разные стили к данным, которые соответствуют определенным критериям. Это может помочь выделить наиболее важную информацию в ваших таблицах.
Всем известно, что изменить фон клетки очень просто. Это можно сделать, просто нажав кнопку «Цвет заливки». Но что, если вы захотите изменить внешний вид своей таблицы при выполнении какого-либо условия? Кроме того, что, если вам нужно, чтобы он автоматически изменялся при внесении изменений в таблицу? Условное форматирование для этого — действительно мощная и полезная функция. Далее в этой статье вы найдете ответы на эти вопросы и прочтете несколько полезных советов, которые помогут вам выбрать правильный метод условного форматирования для каждой задачи.
При этом изменение внешнего вида из-за содержимого текущей или какой-то другой ячейки или других условий часто считается одной из самых сложных и непонятных функций, особенно для новичков. Если эта функция вас тоже пугает, не бойтесь! Действительно, это очень удобно и просто в использовании, и вы убедитесь в этом уже через 5 минут после прочтения этого руководства. А теперь посмотри, сколько мы можем сделать!
Кроме того, если вы собираетесь использовать условное форматирование, имейте в виду, что оно имеет более высокий приоритет, чем обычное ручное форматирование, которое вы можете сделать через меню Home — Format.
Вы можете применить условное форматирование к одной или нескольким позициям, строкам, столбцам или всей таблице в зависимости от их содержимого или при выполнении какого-либо другого условия. Это делается путем создания правил (условий), в которых вы определяете, когда и как изменить внешний вид выбранных ячеек в таблице.
Где находится форматирование по условию в 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, а затем выберите пункт «Создать правило». В описании правила напишите выражение:
= M2> B2
Обратите внимание, что здесь используются относительные ссылки, чтобы программа могла последовательно выполнять итерацию по всем ячейкам указанной ею области, и в то же время каждая ячейка столбца M соответствует ячейке столбца B, расположенной в той же строке и связанной с ней к тому же продукту.
Настройте отображение выбранных ячеек так же, как описано выше.
Как видно на рисунке, созданное нами правило условного форматирования работает правильно и выделяет декабрьские продажи тех продуктов, которые выросли по сравнению с январем.
Использование абсолютных и относительных ссылок в правилах.
Чтобы облегчить модификацию условий выделения определенных значений в таблице Excel, запишем некоторые параметры выделения в специально отведенные ячейки.
Задача: выбрать из таблицы заказов количества менее 50 и более 100 единиц.
Отметим наши ограничения в D1 и D2. Затем давайте создадим первое правило условного форматирования для диапазона E5: E24.
= E5> $ D $ 2
Абсолютная ссылка на D2 означает, что каждая из ячеек в нашем диапазоне сравнения должна сравниваться с D2. И ссылка относительно первой ячейки нашей области выбора E5 сообщает программе начать именно с этой позиции, а затем пройти вниз по столбцу, сравнивая количество с пороговым значением 100.
Как обычно, выберите цвет заливки, если условие выполнено.
Аналогично для E5: E24 создайте второе правило
= E5
В результате часть столбца будет окрашена в зеленый цвет, частично в желтый, а число от 50 до 100 останется неокрашенным.
Теперь усложним задачу: мы заполним цветом не отдельные ячейки, а целые строки таблицы. Для этого нам просто нужно изменить некоторые ссылки в наших правилах.
Прежде всего, давайте переопределим диапазон условного форматирования. Теперь это будет 5 австралийских долларов: 24 доллара G.
Внесем небольшие изменения в правило форматирования:
= $ E5> $ D $ 2
Как видите, теперь у нас есть абсолютная ссылка на столбец E. И ссылка на строку остается относительной, без знака $. Для программы это означает, что вам нужно использовать данные из всей строки и раскрасить ее целиком, а не отдельную ячейку.
Аналогичным образом мы меняем второе условие с E5 
При этом ссылка на D2 остается абсолютной, так как условие записывается именно в этой ячейке. В результате мы получаем «полосатую» таблицу, в которой цветом выделены целые строки. И вся хитрость заключается в правильном использовании абсолютных ссылок в правилах.
Производство. Попробуем запомнить простые принципы использования ссылок в правилах:
- если 2 столбца сравниваются попарно, используются относительные ссылки (M2> B2).
- если значения в столбце связаны с определенной ячейкой, тогда должна быть абсолютная ссылка на нее ($ D $ 1).
- когда необходимо раскрасить всю строку по условию, ссылка на эту строку должна быть относительной ($ E5)
- когда необходимо раскрасить весь столбец, ссылка на него должна быть относительной (5 E)
Как использовать в правилах ссылку на соседние листы?
В последних версиях с 2010 года в формулах условий можно смело использовать ссылки на данные с других листов. Это делается так же, как и с обычными формулами.
В предыдущих версиях программы, 2007 и 2003, это ограничение можно было легко обойти, используя именованные диапазоны. Вы просто назначаете определенные имена диапазонам на текущих или соседних листах, а затем используете эти имена в функциях.
В частности, вместо
= IF («Форматирование (Лист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. Если хотя бы одно из этих требований выполняется, линия будет окрашена в красный цвет.
= O ($ E5- $ C $ 2 = 1; $ E $ 5 = $ C $ 2)
Важно! Правила, расположенные выше в списке, имеют более высокий приоритет (1 и 2 на изображении выше). Новые правила всегда добавляются в начало списка и поэтому имеют более высокий приоритет. Результат их работы не может быть изменен действием предыдущих правил ниже.
Однако порядок выполнения всегда можно изменить в том же окне с помощью стрелок вверх и вниз (3).
Как редактировать условное форматирование?
Чтобы отредактировать ранее созданное условие, вы должны сначала посмотреть, какие условия мы применяем к таблице, а затем выбрать нужное правило. Последовательность действий такая же, как мы обсуждали выше. Но на всякий случай повторю на скриншоте: нам нужен раздел «Управление правилами», поэтому указываем, что рассматриваем текущий лист.
При нажатии на значок «Редактировать…» мы попадаем в уже знакомое меню для создания правила. Только все поля уже заполнены текущими значениями. Осталось только изменить то, что нужно, и нажать «ОК».
А если забыл, где какие правила создавал?
Из-за того, что этот метод имеет приоритет над обычным макетом, таблица может выглядеть не так, как ожидалось. Особенно, если забыть, где и какие правила создавались. Итак, как нам быстро найти все условно отформатированные ячейки в таблице?
Один из самых простых способов найти такие нестандартные позиции в таблице — использовать Главное меню — Найти и выбрать —…. в последних версиях Excel. Или Главная — Редактировать — Найти и выбрать — . в предыдущих версиях.
Но в результате вы просто увидите те области таблицы, где применяется условное форматирование. И ничего более. Какие именно условия изменения конструкции пока неизвестны. В любом случае вам, скорее всего, придется копнуть глубже и понять, какие условия здесь применяются.
Поэтому лучше просто выбрать раздел «Управление правилами» — текущий лист. Мы уже дважды описывали этот процесс в предыдущих разделах, поэтому думаю, здесь не будет никаких проблем.
Вы увидите все созданные вами правила, а также приоритет их выполнения. Напомним, что правила в верхней части списка имеют наивысший приоритет: чем он выше, тем важнее. Он также указывает области, к которым применяются созданные форматы. Думаю, здесь будет довольно легко в этом разобраться.
Как можно скопировать условное форматирование?
Вот несколько способов скопировать правила.
Копировать формат для образца
Копировать можно так же, как и в обычном формате.
На вкладке «Главная» в верхней части ленты находится группа «Буфер обмена». В нем вы видите значок кисти — формат для образца (в разных версиях он выглядит по-разному, но название то же). При нажатии на нее копируется не только формат выбранных ячеек, но и условия для них, если таковые имеются. Следующим шагом будет выбор тех ячеек, в которые нужно перенести этот рисунок.
Обратите внимание, что описанным способом будут перенесены абсолютно все форматы, в том числе установленные вручную.
Скопируйте через пасту.
Альтернативой дублированному формату является особый способ вставки.
Скопируйте ячейки с условным форматом любым знакомым способом. Выберите диапазон, в который вы хотите перенести формат (вы можете выбрать, а не соседние, удерживая клавишу CTRL), затем щелкните правой кнопкой мыши и выберите «Специальная вставка…». Затем программа отобразит окно, в котором нужно установить переключатель в пункт «форматы», после чего нажать «ОК».
Управление правилами.
Вы можете использовать диспетчер правил.
Следуйте по следующему пути: -> «Управление правилами…».
В раскрывающемся списке «Показать правила…» выберите «Этот лист». Вы сможете увидеть все правила, которые применяются к текущему листу.
В столбце «Применимо к» списка правил указаны диапазоны, к которым применяется каждое правило. Добавьте в это поле необходимые адреса ячеек, разделенные точкой с запятой, чтобы применить к ним ранее созданные условия.
Этот способ более трудоемкий, чем два предыдущих. Но его прелесть в том, что он позволяет распространять только те правила, которые вам нужны. Это особенно полезно, когда к скопированным ячейкам одновременно применяются несколько условий, и нужно скопировать только одно из них.
Как убрать условное форматирование?
Это так же просто, как создать правило. Затем выберите — «Удалить правила». Вам будет предложено удалить из выбранного диапазона данных или даже всех правил на листе. Но имейте в виду, что это удалит все, что было создано ранее. Но, возможно, что-то вы бы хотели сохранить.
Поэтому существует более тонкий инструмент, который мы рекомендуем использовать как для редактирования, так и для удаления.
Используйте последний пункт выпадающего меню: «Управление правилами».
Здесь вы можете увидеть все правила текущего листа, к каким диапазонам они относятся и что они делают. Поэтому гораздо проще выбрать конкретное правило и удалить его.
Или при необходимости поменять.
Почему не работает?
Если вы не получили ожидаемого результата, первым делом убедитесь, что созданное вами правило условного форматирования работает правильно. Для этого вы можете скопировать формулу из правила в любую пустую ячейку и посмотреть, какой результат вы получите. Если вы условно форматируете весь столбец чисел, выберите пустое место справа от таблицы.
Если результатом условной формулы является ИСТИНА, необходимо применить условное форматирование. Конечно, если ЛОЖЬ, то — нет. Вернемся к одной из наших задач и проведем такую отладку правил форматирования.
В столбец I копируем формулу первого условия, в К — второго. Щелкните мышью в правом нижнем углу ячейки с формулой и перетащите ее на всю высоту таблицы. Получаем полную картину по каждой из ячеек нашего ассортимента. Как видите, ИСТИНА и ЛОЖЬ точно соответствуют затенению столбца K, которое мы действительно проверили. В I2 мы получили ИСТИНА, поэтому цвет зеленый. Также в J9 ответ положительный, поэтому цвет желтый. И т.п.
Если формула сложная, ее можно разбить и применить тот же метод отладки.
Надеемся, вы нашли ответы на свои вопросы об условном форматировании в наших инструкциях.
Однако, если что-то не работает или не работает, напишите в комментариях ниже. Мы постараемся ответить вам или даже создать отдельную статью, посвященную вашей проблеме.
Удачи!