Условное форматирование в Excel
Обновлено: 05.07.2024
Хотя Excel поставляется со многими «предустановками» условного форматирования, они ограничены. Более эффективным способом применения условного форматирования являются формулы, поскольку формулы позволяют применять правила на основе любой логики, которую вы хотите. В этой статье показано, как выделять строки, различия в столбцах, отсутствующие значения и как создавать диаграммы Ганта и поля поиска с условным форматированием.
Условное форматирование — это отличный способ быстро визуализировать данные в электронной таблице. С помощью условного форматирования можно выполнять такие действия, как выделение дат в ближайшие 30 дней, отмечать проблемы с вводом данных, выделять строки, содержащие основных клиентов, отображать дубликаты и т. д.
Excel поставляется с большим количеством «наборов», которые упрощают создание новых правил без формул. Однако вы также можете создавать правила со своими собственными формулами. Используя свою собственную формулу, вы берете на себя условие, которое запускает правило, и можете применить именно ту логику, которая вам нужна. Формулы дают вам максимальную мощность и гибкость.
Например, с помощью предустановки "Равно" легко выделить ячейки, равные "яблоку".
Но что, если вы хотите выделить ячейки, равные "яблоку", "киви" или "лайму"? Конечно, вы можете создать правило для каждого значения, но это очень хлопотно. Вместо этого вы можете просто использовать одно правило, основанное на формуле с функцией ИЛИ:
Вот результат применения правила к диапазону B4:F8 в этой таблице:
Вот точная используемая формула:
Быстрый старт
Вы можете создать правило условного форматирования на основе формулы, выполнив четыре простых шага:
<р>1. Выберите ячейки, которые вы хотите отформатировать.
Функция ISODD возвращает ИСТИНА только для нечетных чисел, что приводит к срабатыванию правила:
Логика формул
Формулы, применяющие условное форматирование, должны возвращать ИСТИНА или ЛОЖЬ или числовые эквиваленты. Вот несколько примеров:
Все приведенные выше формулы возвращают значение TRUE или FALSE, поэтому они отлично работают в качестве триггера для условного форматирования.
Когда условное форматирование применяется к диапазону ячеек, введите ссылки на ячейки относительно первой строки и столбца в выделенном фрагменте (т. е. верхней левой ячейки). Хитрость в понимании того, как работают формулы условного форматирования, заключается в том, чтобы представить, что одна и та же формула применяется к каждой ячейке в выделенном фрагменте, при этом ссылки на ячейки обновляются, как обычно. Представьте, что вы ввели формулу в верхнюю левую ячейку выделения, а затем скопировали формулу по всему выделению. Если вы боретесь с этим, см. раздел о фиктивных формулах ниже.
Примеры формул
Ниже приведены примеры пользовательских формул, которые можно использовать для применения условного форматирования. Некоторые из этих примеров можно создать, используя встроенные в Excel наборы настроек для выделения ячеек, но пользовательские формулы могут выходить далеко за рамки шаблонов, как вы можете видеть ниже.
Выделить заказы из Техаса
Чтобы выделить строки, представляющие заказы из Техаса (сокращенно TX), используйте формулу, которая блокирует ссылку на столбец F:
Выделить даты в ближайшие 30 дней
Чтобы выделить даты, которые произойдут в ближайшие 30 дней, нам нужна формула, которая (1) гарантирует, что даты находятся в будущем, и (2) гарантирует, что даты отстоят на 30 дней или меньше от сегодняшнего дня. Один из способов сделать это — использовать функцию И вместе с функцией СЕЙЧАС следующим образом:
С текущей датой 18 августа 2016 года условное форматирование выделяет даты следующим образом:
Функция СЕЙЧАС возвращает текущую дату и время. Подробнее о том, как работает эта формула, читайте в этой статье: Выделить даты в ближайшие N дней.
Выделить различия в столбцах
Дав два столбца с похожей информацией, вы можете использовать условное форматирование, чтобы выявить незначительные различия. Формула, используемая для запуска форматирования ниже:
Выделить отсутствующие значения
Чтобы выделить в одном списке значения, отсутствующие в другом, можно использовать формулу, основанную на функции СЧЁТЕСЛИ:
Эта формула просто сравнивает каждое значение в списке A со значениями в именованном диапазоне "список" (D5:D10). Когда счетчик равен нулю, формула возвращает значение TRUE и запускает правило, которое выделяет значения в списке A, отсутствующие в списке B.
Выделите недвижимость с 3+ спальнями стоимостью менее 350 000 долларов США
Чтобы найти в этом списке недвижимость с минимум 3 спальнями, но стоимостью менее 300 000 долларов США, можно использовать формулу, основанную на функции И:
Знаки доллара ($) блокируют ссылку на столбцы C и D, а функция И используется, чтобы убедиться, что оба условия ИСТИННЫ. В строках, где функция И возвращает ИСТИНА, применяется условное форматирование:
Выделение основных значений (динамический пример)
Хотя в Excel есть предустановки для "верхних значений", в этом примере показано, как сделать то же самое с формулой и как формулы могут быть более гибкими. Используя формулу, мы можем сделать рабочий лист интерактивным — когда значение в F2 обновляется, правило мгновенно реагирует и выделяет новые значения.
Формула, используемая для этого правила:
Где «данные» — это именованный диапазон B4:G11, а «входные данные» — это именованный диапазон F2. На этой странице есть подробная информация и полное объяснение.
Диаграммы Ганта
Хотите верьте, хотите нет, но вы даже можете использовать формулы для создания простых диаграмм Ганта с условным форматированием, например:
В этом листе используются два правила: одно для столбцов и одно для затенения выходных:
Простое окно поиска
С условным форматированием можно сделать один интересный прием — создать простое окно поиска. В этом примере правило выделяет ячейки в столбце B, содержащие текст, введенный в ячейке F2:
Используется следующая формула:
Для получения более подробной информации и полного объяснения см.:
Устранение неполадок
Если вы не можете заставить свои правила условного форматирования правильно срабатывать, скорее всего, проблема связана с вашей формулой. Во-первых, убедитесь, что вы начали формулу со знака равенства (=). Если вы забудете об этом шаге, Excel автоматически преобразует всю формулу в текст, что сделает ее бесполезной. Чтобы исправить это, просто удалите двойные кавычки, добавленные Excel с обеих сторон, и убедитесь, что формула начинается с равенства (=).
Если формула введена правильно, но правило не срабатывает, возможно, вам придется копнуть немного глубже.Обычно вы можете использовать клавишу F9 для проверки результатов в формуле или использовать функцию оценки для пошагового выполнения формулы. К сожалению, вы не можете использовать эти инструменты с формулами условного форматирования, но вы можете использовать технику, называемую "фиктивными формулами".
Фиктивные формулы
Фиктивные формулы — это способ проверить формулы условного форматирования непосредственно на листе, чтобы вы могли увидеть, что они делают на самом деле. Это может значительно сэкономить время, когда вы пытаетесь правильно настроить ссылки на ячейки.
В двух словах вы вводите одну и ту же формулу в диапазоне ячеек, который соответствует форме ваших данных. Это позволяет вам видеть значения, возвращаемые каждой формулой, и это отличный способ визуализировать и понять, как работает условное форматирование на основе формул. Подробное объяснение см. в этой статье.
Ограничения
Существуют некоторые ограничения условного форматирования на основе формул:
- Вы не можете применять значки, цветовые шкалы или гистограммы с пользовательской формулой. Вы ограничены стандартным форматированием ячеек, включая числовые форматы, шрифт, цвет заливки и параметры границ.
- Вы не можете использовать определенные конструкции формул, такие как объединения, пересечения или константы массива, для условий условного форматирования.
- Вы не можете ссылаться на другие книги в формуле условного форматирования.
Некоторые материалы в этом разделе могут быть неприменимы к некоторым языкам.
Условное форматирование позволяет легко выделить определенные значения или упростить идентификацию определенных ячеек. Это изменяет внешний вид диапазона ячеек на основе условия (или критериев). Вы можете использовать условное форматирование, чтобы выделить ячейки, содержащие значения, соответствующие определенному условию. Или вы можете отформатировать весь диапазон ячеек и изменить точный формат по мере изменения значения каждой ячейки.
Информация о температуре с примененным условным форматированием, которая показывает верхние 10 % и нижние 10 % значений
Информация о температуре с применением условного форматирования по трехцветной шкале
Применить условное форматирование
Выберите диапазон ячеек, таблицу или весь лист, к которым вы хотите применить условное форматирование.
На вкладке "Главная" нажмите "Условное форматирование".
Выполните одно из следующих действий:
Чтобы выделить
Значения в определенных ячейках. Примерами могут служить даты после этой недели, числа от 50 до 100 или последние 10 % результатов.
Наведите указатель мыши на пункт «Правила выделения ячеек» или «Верхние/нижние правила», а затем выберите соответствующий параметр.
Связь значений в диапазоне ячеек. Расширяет полосу цвета по всей ячейке. Например, сравнение цен или населения в крупнейших городах.
Наведите указатель на гистограммы и щелкните нужную заливку.
Связь значений в диапазоне ячеек. Применяет цветовую шкалу, в которой интенсивность цвета ячейки отражает размещение значения в верхней или нижней части диапазона. Примером может служить распределение продаж по регионам.
Наведите указатель на Цветовые шкалы и выберите нужную шкалу.
Диапазон ячеек, содержащий от трех до пяти групп значений, где каждая группа имеет собственный порог. Например, можно назначить набор из трех значков для выделения ячеек, отражающих продажи ниже 80 000, ниже 60 000 и ниже 40 000 долларов. Или вы можете назначить 5-балльную рейтинговую систему для автомобилей и применить набор из пяти значков.
Наведите указатель на наборы значков и щелкните набор.
Дополнительные параметры
Выберите диапазон ячеек, таблицу или весь лист, к которым вы хотите применить условное форматирование.
На вкладке "Главная" нажмите "Условное форматирование".
Наведите указатель мыши на пункт «Правила выделения ячеек» и нажмите «Содержащий текст».
Введите текст, который хотите выделить, и нажмите OK.
Выберите диапазон ячеек, таблицу или весь лист, к которым вы хотите применить условное форматирование.
На вкладке "Главная" нажмите "Условное форматирование".
Нажмите "Новое правило".
Выберите стиль, например, 3-цветная шкала, выберите нужные условия и нажмите кнопку "ОК".
Выберите диапазон ячеек, таблицу или весь лист, к которым вы хотите применить условное форматирование.
На вкладке "Главная" нажмите "Условное форматирование".
Наведите указатель на правила выделения ячеек, а затем нажмите «Дублировать значения».
Рядом со значениями в выбранном диапазоне нажмите уникальные или повторяющиеся.
Выберите ячейку с условным форматированием, которое вы хотите скопировать.
На вкладке "Главная" (или стандартной панели инструментов в Excel для Mac 2011) нажмите "Формат" и выберите ячейки, в которые вы хотите скопировать условное форматирование.
Если условное форматирование применено только к какой-то части листа, вы можете быстро найти отформатированные ячейки, чтобы скопировать, изменить или удалить форматирование этих ячеек.
Если вы хотите найти только ячейки с определенным условным форматом, начните с нажатия ячейки с таким форматом.
В меню "Правка" нажмите "Найти" > "Перейти", а затем нажмите "Специальные".
Нажмите "Условные форматы".
Если вы хотите найти только ячейки с определенным условным форматом ячейки, выбранным на шаге 1, выберите То же.
Выберите ячейки с условным форматированием, которое вы хотите удалить.
На вкладке "Главная" нажмите "Условное форматирование".
Наведите указатель на Очистить правила и выберите нужный вариант.
Совет. Чтобы удалить все условные форматы и все другие форматы ячеек для выбранных ячеек, в меню «Правка» выберите «Очистить» и нажмите «Форматы».
Вы можете настроить правила по умолчанию для условных форматов в соответствии со своими требованиями. Вы можете изменить операторы сравнения, пороговые значения, цвета и значки.
Нажмите в диапазоне, содержащем правило условного форматирования, которое вы хотите изменить.
На вкладке "Главная" нажмите "Условное форматирование".
Нажмите "Управление правилами".
Выберите правило и нажмите «Изменить правило».
Внесите нужные изменения, нажмите "ОК", а затем еще раз нажмите "ОК".
Вы можете удалить условные форматы, которые вам больше не нужны.
Нажмите в диапазоне, содержащем правило условного форматирования, которое вы хотите изменить.
Условное форматирование в Excel позволяет выделять ячейки определенным цветом в зависимости от значения ячейки.
Правила выделения ячеек
Чтобы выделить ячейки, которые больше значения, выполните следующие действия.
<р>1. Выберите диапазон A1:A10. <р>2. На вкладке "Главная" в группе "Стили" нажмите "Условное форматирование".
Результат. Excel выделяет ячейки, которые больше 80.
<р>6. Измените значение ячейки A1 на 81.Результат. Excel автоматически изменяет формат ячейки A1.
Примечание: вы также можете использовать эту категорию (см. шаг 3), чтобы выделить ячейки, которые меньше значения, между двумя значениями, равными значению, ячейки, содержащие определенный текст, даты (сегодня, на прошлой неделе, в следующем месяце). и т. д.), повторяющиеся или уникальные значения.
Очистить правила
Чтобы очистить правило условного форматирования, выполните следующие действия.
<р>1. Выберите диапазон A1:A10. <р>2. На вкладке "Главная" в группе "Стили" нажмите "Условное форматирование".
Верхние/нижние правила
Чтобы выделить ячейки с показателями выше среднего, выполните следующие действия.
<р>1. Выберите диапазон A1:A10. <р>2. На вкладке "Главная" в группе "Стили" нажмите "Условное форматирование".
Результат. Excel вычисляет среднее значение (42,5) и форматирует ячейки, превышающие это среднее значение.
Примечание. Вы также можете использовать эту категорию (см. шаг 3), чтобы выделить первые n элементов, первые n процентов, последние n элементов, последние n процентов или ячейки с показателями ниже среднего.
Условное форматирование с формулами
Поднимите свои навыки работы с Excel на новый уровень и используйте формулу, чтобы определить, какие ячейки нужно отформатировать. Формулы, применяющие условное форматирование, должны иметь значение TRUE или FALSE.
<р>1. Выберите диапазон A1:E5.
<р>5. Введите формулу =ISODD(A1)
Результат. Excel выделяет все нечетные числа.
Объяснение: всегда пишите формулу для верхней левой ячейки в выбранном диапазоне. Excel автоматически копирует формулу в другие ячейки. Таким образом, ячейка A2 содержит формулу =ISODD(A2), ячейка A3 содержит формулу =ISODD(A3) и т. д.
Вот еще один пример.
<р>7. Выберите диапазон A2:D7.
Результат. Excel выделяет все заказы в США.
Объяснение: мы исправили ссылку на столбец C, поместив символ $ перед буквой столбца ($C2). В результате ячейки B2, C2 и ячейка D2 также содержат формулу =$C2="США", ячейки A3, B3, C3 и D3 содержат формулу =$C3="США" и т. д.
Условное форматирование — одна из самых простых, но мощных функций электронных таблиц Excel.
Как следует из названия, вы можете использовать условное форматирование в Excel, когда хотите выделить ячейки, соответствующие заданному условию.
Это дает вам возможность быстро добавить слой визуального анализа к вашему набору данных. Вы можете создавать тепловые карты, отображать значки увеличения/уменьшения, пузыри Харви и многое другое, используя условное форматирование в Excel.
Использование условного форматирования в Excel (примеры)
В этом руководстве я покажу вам семь замечательных примеров использования условного форматирования в Excel:
1. Быстрое определение дубликатов
Условное форматирование в Excel можно использовать для выявления дубликатов в наборе данных.
Вот как это можно сделать:
Это позволит мгновенно выделить все ячейки, которые имеют дубликаты в выбранном наборе данных. Ваш набор данных может находиться в одном столбце, нескольких столбцах или в несмежном диапазоне ячеек.
2. Выделение ячеек со значением больше/меньше числа
Вы можете использовать условное форматирование в Excel, чтобы быстро выделить ячейки, содержащие значения больше или меньше указанного значения. Например, выделение всех ячеек с объемом продаж менее 100 млн или выделение ячеек с оценками ниже порогового значения.
Вот как это сделать:
Это позволит мгновенно выделить все ячейки со значениями больше 5 в наборе данных. Примечание. Если вы хотите выделить значения, большие или равные 5, вам следует снова применить условное форматирование с критерием "Равно".
Тот же процесс можно выполнить, чтобы выделить ячейки со значением меньше указанного значения.
3. Выделение верхних/нижних 10 (или 10%)
Условное форматирование в Excel позволяет быстро определить первые 10 элементов или первые 10 % набора данных. Это может быть полезно в ситуациях, когда вы хотите быстро просмотреть лучших кандидатов по баллам или максимальной стоимости сделки в данных о продажах.
Аналогичным образом вы также можете быстро определить последние 10 элементов или последние 10 % в наборе данных.
Вот как это сделать:
Это позволит мгновенно выделить первые 10 элементов в выбранном наборе данных. Обратите внимание, что это работает только для ячеек, в которых есть числовое значение.
Кроме того, если в наборе данных менее 10 ячеек, и вы выбираете параметры для выделения первых 10 элементов/нижних 10 элементов, то все ячейки будут выделены.
Вот несколько примеров работы условного форматирования:
4. Выделение ошибок/пробелов
Если вы работаете с большим количеством числовых данных и расчетов в Excel, вы знаете, как важно выявлять и обрабатывать ячейки с ошибками или пустые. Использование этих ячеек в дальнейших расчетах может привести к ошибочным результатам.
Условное форматирование в Excel может помочь вам быстро определить и выделить ячейки с ошибками или пустые.
Предположим, у нас есть набор данных, как показано ниже:
В этом наборе данных есть пустая ячейка (A4) и ошибки (A5 и A6).
Вот шаги, чтобы выделить ячейки, которые пусты или содержат ошибки:
Это мгновенно выделит все ячейки, которые либо пусты, либо содержат ошибки.
Примечание. Вам не нужно использовать весь диапазон A1:A7 в формуле условного форматирования. Вышеупомянутая формула использует только A1. Когда вы применяете эту формулу ко всему диапазону, Excel проверяет одну ячейку за раз и корректирует ссылку. Например, при проверке A1 используется формула =ИЛИ(ЕПУСТО(A1),ЕОШИБКА(A1)). При проверке ячейки A2 используется формула =ИЛИ(ЕПУСТО(A2),ЕОШИБКА(A2)). Он автоматически настраивает ссылку (поскольку это относительные ссылки) в зависимости от того, какая ячейка анализируется. Таким образом, вам не нужно писать отдельную формулу для каждой ячейки. Excel достаточно умен, чтобы изменить ссылку на ячейку сам по себе 🙂
5. Создание тепловых карт
Тепловая карта – это визуальное представление данных, в котором цвет соответствует значению в ячейке. Например, можно создать тепловую карту, на которой ячейка с наибольшим значением окрашена в зеленый цвет, а по мере уменьшения значения наблюдается сдвиг в сторону красного цвета.
Как показано ниже:
Приведенный выше набор данных имеет значения от 1 до 100. Ячейки выделяются в зависимости от значения в нем. 100 — зеленый цвет, 1 — красный.
Вот шаги по созданию тепловых карт с использованием условного форматирования в Excel.
Как только вы нажмете на значок тепловой карты, к набору данных будет применено форматирование. Есть несколько цветовых градиентов, которые вы можете выбрать. Если вас не устраивают существующие параметры цвета, вы можете выбрать дополнительные правила и указать нужный цвет.
Примечание. Аналогичным образом можно применять наборы панелей данных и значков.
6. Выделить каждую вторую строку/столбец
Возможно, вы захотите выделить чередующиеся строки, чтобы повысить удобочитаемость данных.
Они называются полосами зебры и могут быть особенно полезны при печати данных.
Теперь есть два способа создать эти линии зебры. Самый быстрый способ — преобразовать ваши табличные данные в таблицу Excel. Он автоматически применял цвет к чередующимся строкам.Подробнее об этом можно прочитать здесь.
Еще один способ – условное форматирование.
Предположим, у вас есть набор данных, как показано ниже:
Вот как выделить альтернативные строки с помощью условного форматирования в Excel.
Вот оно! Альтернативные строки в наборе данных будут выделены.
Вы можете использовать один и тот же метод во многих случаях. Все, что вам нужно сделать, это использовать соответствующую формулу в условном форматировании. Вот несколько примеров:
- Выделить чередующиеся четные строки: =ISEVEN(ROW())
- Выделите дополнительные строки добавления: =ISODD(ROW())
- Выделить каждую 3-ю строку: =MOD(ROW(),3)=0
7. Поиск и выделение данных с помощью условного форматирования
Это немного расширенное использование условного форматирования. Тогда вы будете выглядеть как рок-звезда Excel.
Предположим, у вас есть набор данных, как показано ниже, с названием продукта, торговым представителем и географией. Идея состоит в том, чтобы ввести строку в ячейку C2, и если она совпадает с данными в какой-либо ячейке (ячейках), то она должна быть выделена. Примерно так, как показано ниже:
Вот шаги для создания этой функции поиска и выделения:
Вот оно! Теперь, когда вы вводите что-либо в ячейку C2 и нажимаете Enter, все соответствующие ячейки будут выделены.
Как это работает?
Формула, используемая в условном форматировании, оценивает все ячейки в наборе данных. Допустим, вы вводите Японию в ячейку C2. Теперь Excel будет оценивать формулу для каждой ячейки.
Формула вернет значение TRUE для ячейки при соблюдении двух условий:
- Ячейка C2 не пуста.
- Содержимое ячейки C2 точно соответствует содержимому ячейки в наборе данных.
Следовательно, все ячейки, содержащие текст Япония, будут выделены.
Как удалить условное форматирование в Excel
После применения условное форматирование остается в силе, если вы не удалите его вручную. Рекомендуется применять условное форматирование только к тем ячейкам, где оно необходимо.
Читайте также: