Условное форматирование в Excel: полное руководство с примерами

Условное форматирование - одна из самых простых, но мощных функций электронных таблиц Excel.

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

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

Использование условного форматирования в Excel (примеры)

В этом руководстве я покажу вам семь удивительных примеров использования условного форматирования в Excel:

• Быстрое определение дубликатов с помощью условного форматирования в Excel.
• Выделите ячейки со значением больше / меньше числа в наборе данных.
• Выделение 10 верхних / нижних (или 10%) значений в наборе данных.
• Выделение ошибок / пробелов с помощью условного форматирования в Excel.
• Создание тепловых карт с использованием условного форматирования в Excel.
• Выделите каждую N-ю строку / столбец, используя условное форматирование.
• Поиск и выделение с помощью условного форматирования в Excel.

1. Быстрое определение дубликатов

Условное форматирование в Excel можно использовать для выявления дубликатов в наборе данных.

Вот как это можно сделать:

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

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

Смотрите также: Полное руководство по поиску и удалению дубликатов в Excel.

2. Выделите ячейки со значением больше / меньше числа.

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

Вот как это сделать:

• Выберите весь набор данных.
• Перейдите на главную страницу -> Условное форматирование -> Выделение правил ячеек -> Больше, чем… / Меньше, чем…
• В зависимости от того, какой вариант вы выберете (больше или меньше), откроется диалоговое окно. Допустим, вы выбрали вариант «Больше чем». В диалоговом окне введите число в поле слева. Цель состоит в том, чтобы выделить ячейки, число которых превышает указанное число.

• Укажите формат, который будет применяться к ячейкам, удовлетворяющим условию, с помощью раскрывающегося списка справа. Существуют некоторые существующие форматы, которые вы можете использовать или указать свой собственный формат с помощью параметра «Пользовательский формат».
• Щелкните ОК.

Это мгновенно выделит все ячейки со значениями больше 5 в наборе данных.Примечание. Если вы хотите выделить значения больше 5, вам следует снова применить условное форматирование с критерием «Равно».

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

3. Выделение верхних / нижних 10 (или 10%).

Условное форматирование в Excel позволяет быстро определить 10 самых популярных элементов или 10% лучших из набора данных. Это может быть полезно в ситуациях, когда вы хотите быстро увидеть лучших кандидатов по баллам или по максимальной стоимости сделок в данных о продажах.

Точно так же вы также можете быстро определить 10 нижних элементов или 10% нижних элементов в наборе данных.

Вот как это сделать:

• Выберите весь набор данных.
• Перейдите на главную -> Условное форматирование -> Правила сверху / снизу -> 10 первых элементов (или%) / 10 последних элементов (или%).
• В зависимости от того, что вы выберете, откроется диалоговое окно. Допустим, вы выбрали 10 лучших элементов, после чего откроется диалоговое окно, как показано ниже:
• Укажите формат, который будет применяться к ячейкам, удовлетворяющим условию, с помощью раскрывающегося списка справа. Существуют некоторые существующие форматы, которые вы можете использовать или указать свой собственный формат с помощью параметра «Пользовательский формат».
• Щелкните ОК.

Это мгновенно выделит 10 лучших элементов в выбранном наборе данных. Обратите внимание, что это работает только для ячеек, содержащих числовое значение.

Кроме того, если у вас меньше 10 ячеек в наборе данных, и вы выбираете параметры, чтобы выделить первые 10 элементов / последние 10 элементов, тогда все ячейки будут выделены.

Вот несколько примеров того, как будет работать условное форматирование:

4. Выделение ошибок / пропусков

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

Условное форматирование в Excel может помочь вам быстро определить и выделить ячейки с ошибками или пустые.

Предположим, у нас есть набор данных, как показано ниже:

В этом наборе данных есть пустая ячейка (A4) и ошибки (A5 и A6).

Вот шаги, чтобы выделить ячейки, которые пусты или содержат ошибки:

• Выберите набор данных, в котором вы хотите выделить пустые ячейки и ячейки с ошибками.
• Перейдите на главную -> Условное форматирование -> Новое правило.
• В диалоговом окне «Новое правило форматирования» выберите «Использовать формулу», чтобы определить, какие ячейки нужно форматировать.
• Введите следующую формулу в поле в разделе «Редактировать описание правила»:
  = ИЛИ (ОШИБКА (A1); ОШИБКА (A1))
  • Приведенная выше формула проверяет все ячейки на наличие двух условий - пустая она или нет и есть ли в ней ошибка. Если какое-либо из условий ИСТИНА, возвращается ИСТИНА.
• Задайте формат, который вы хотите применить к пустым ячейкам или ячейкам с ошибками. Для этого нажмите кнопку «Форматировать». Откроется диалоговое окно «Формат ячеек», в котором вы можете указать формат.
• Щелкните ОК.

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

Примечание: Необязательно использовать весь диапазон A1: A7 в формуле при условном форматировании. Вышеупомянутая формула использует только A1. Когда вы применяете эту формулу ко всему диапазону, Excel проверяет одну ячейку за раз и корректирует ссылку. Например, при проверке A1 используется формула = OR (ISBLANK (A1), ISERROR (A1)). Когда он проверяет ячейку A2, он затем использует формулу = ИЛИ (ISBLANK (A2), ISERROR (A2)). Он автоматически корректирует ссылку (поскольку это относительные ссылки) в зависимости от того, какая ячейка анализируется. Таким образом, вам не нужно писать отдельную формулу для каждой ячейки. Excel достаточно умен, чтобы самостоятельно изменить ссылку на ячейку 🙂

Смотрите также: Использование IFERROR и ISERROR для обработки ошибок в Excel.

5. Создание тепловых карт

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

Что-то вроде того, что показано ниже:

Приведенный выше набор данных имеет значения от 1 до 100. Ячейки выделяются в зависимости от значения в нем. 100 получает зеленый цвет, 1 - красный цвет.

Вот шаги для создания тепловых карт с использованием условного форматирования в Excel.

• Выберите набор данных.
• Перейдите на главную -> Условное форматирование -> Цветовые шкалы и выберите одну из цветовых схем.

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

Примечание. Аналогичным образом можно применить наборы Data Bard и Icon.

6. Выделите все остальные строки / столбцы.

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

Они называются линиями зебры и могут быть особенно полезны при печати данных.

Теперь есть два способа создать эти линии зебры. Самый быстрый способ - преобразовать табличные данные в таблицу Excel. Он автоматически применял цвет к чередующимся рядам. Вы можете прочитать больше об этом здесь.

Другой способ - условное форматирование.

Предположим, у вас есть набор данных, как показано ниже:

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

• Выберите набор данных. В приведенном выше примере выберите A2: C13 (без заголовка). Если вы хотите включить и заголовок, выберите весь набор данных.
• Откройте диалоговое окно «Условное форматирование» («Домашняя страница» -> «Условное форматирование» -> «Новое правило»). [Сочетание клавиш - Alt + O + D].
• В диалоговом окне выберите «Использовать формулу, чтобы определить, какие ячейки нужно форматировать».
• Введите следующую формулу в поле в разделе «Редактировать описание правила»:
  = ISODD (СТРОКА ())

• Вышеупомянутая формула проверяет все ячейки, и если номер СТРОКИ ячейки нечетный, она возвращает ИСТИНА. Указанный условный формат будет применяться ко всем ячейкам, возвращающим ИСТИНА.
• Задайте формат, который вы хотите применить к пустым ячейкам или ячейкам с ошибками. Для этого нажмите кнопку «Форматировать». Откроется диалоговое окно «Формат ячеек», в котором вы можете указать формат.
• Щелкните ОК.

Вот и все! Альтернативные строки в наборе данных будут выделены.

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

• Выделите чередующиеся четные строки: = ЕДИНИЦЫ (СТРОКА ())
• Выделите альтернативные строки добавления: = ISODD (ROW ())
• Выделите каждую 3-ю строку: = MOD (ROW (), 3) = 0

7. Поиск и выделение данных с помощью условного форматирования.

Это немного продвинутое использование условного форматирования. Это сделало бы вас похожим на рок-звезду Excel.

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

Вот шаги для создания этой функции поиска и выделения:

• Выберите набор данных.
• Перейдите на главную страницу -> Условное форматирование -> Новое правило. (Сочетание клавиш - Alt + O + D).
• В диалоговом окне «Новое правило форматирования» выберите параметр «Использовать формулу для определения ячеек для форматирования».
• Введите следующую формулу в поле в разделе «Редактировать описание правила»:
  = И ($ C $ 2 ””, $ C $ 2 = B5)
• Задайте формат, который вы хотите применить к пустым ячейкам или ячейкам с ошибками. Для этого нажмите кнопку «Форматировать». Откроется диалоговое окно «Формат ячеек», в котором вы можете указать формат.
• Щелкните ОК.

Вот и все! Теперь, когда вы вводите что-либо в ячейку C2 и нажимаете клавишу ВВОД, он выделяет все совпадающие ячейки.

Как это работает?

Формула, используемая в условном форматировании, оценивает все ячейки в наборе данных. Допустим, вы вводите Японию в ячейку C2. Теперь Excel будет оценивать формулу для каждой ячейки.

Формула вернет ИСТИНА для ячейки при выполнении двух условий:

• Ячейка C2 не пуста.
• Содержимое ячейки C2 точно соответствует содержимому ячейки в наборе данных.

Следовательно, все ячейки, содержащие текст Japan, будут выделены.

Загрузите файл примера

Вы можете использовать ту же логику для создания таких вариаций, как:

• Выделите всю строку вместо ячейки.
• Выделите, даже если есть частичное совпадение.
• Выделяйте ячейки / строки по мере ввода (динамически) [Вам понравится этот трюк :)].

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

После применения условное форматирование остается на месте, если вы не удалите его вручную. Рекомендуется применять условное форматирование только к тем ячейкам, где оно вам нужно.

Поскольку он нестабилен, это может привести к медленной работе книги Excel.

Чтобы удалить условное форматирование:

• Выделите ячейки, из которых вы хотите удалить условное форматирование.
• Перейдите на главную -> Условное форматирование -> Очистить правила -> Очистить правила из выбранных ячеек.
  • Если вы хотите удалить условное форматирование со всего листа, выберите «Очистить правила со всего листа».

Важные сведения об условном форматировании в Excel

• Условное форматирование в volatile. Это может привести к медленной работе книги. Используйте его только при необходимости.
• Когда вы копируете ячейки вставки, содержащие условное форматирование, условное форматирование также копируется.
• Если вы примените несколько правил к одному и тому же набору ячеек, все правила останутся активными. В случае любого совпадения предпочтение отдается правилу, примененному последним. Однако вы можете изменить порядок, изменив порядок в диалоговом окне «Управление правилами».