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

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

Варианты правил форматирования

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

• Больше, меньше, равно. Выделяются клетки с числовыми данными, которые соответствуют или не соответствуют заданному параметру при выполнении сравнительной операции.
• Текст содержит. Программа выделяет ячейки, которые содержат заданные пользователем текстовые фрагменты.
• Дата. Форматирование ячеек с датами, которые соответствуют определенным временным промежуткам. Используя данный вариант, к примеру, можно определить количество выходных дней в году или выделить любые даты в календаре.
• Повторяющиеся значения. Поиск дубликатов или, наоборот, уникальных значений с их последующим выделением.

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

Пример 1: форматирование ячеек с числами

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

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

   Вызываем форматирование по правилу «Между»
2. Для примера, выделим значения стоимости больше 100 руб. и меньше 150 руб. Вводим соответствующие параметры и выбираем вариант заливки. Данный цвет будет использоваться, чтобы закрасить отобранные клетки в нужный цвет.
   

   Настраиваем форматирование

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

Пример 2: форматируем клетки с текстом

Похожим образом можно использовать правило для проверки текста. Для примера, нужно выделить повторяющиеся названия категорий. Это очень просто сделать с помощью встроенного правила «Текст содержит»:

1. Выделяем столбец с наименованиями категорий товаров. Далее приступаем к условному форматированию. В пункте «Правила выделения ячеек» выбираем правило «Текст содержит».
   

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

   Выделяем ячейки с нужным текстом

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

Средства для оформления ячеек

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

1. Выделяем ячейки, в которые нужно поместить диаграмму. В нашем случае это столбец «Прибыль».
   

   Выделяем столбец
2. Открываем «Условное форматирование», далее переходим в раздел «Гистограммы» и выбираем в нем подходящий по цвету вариант заливки.
   

   Добавляем гистограмму

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

Добавляем цветовые шкалы

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

Наборы значков

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

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

Для примера, отформатируем наименования товаров, в зависимости от их стоимости. Если цена продукта выше 100 руб. — заливка будет зеленой, а если ниже — красной. Такое оформление можно сделать с помощью встроенной в Excel формулы ЕСЛИ. Рассмотрим, как сделать данное правило на примере:

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

   Переходим к созданию правила
2. В поле «Тип правила» выбираем «Использовать формулу для определения форматируемых ячеек». Ниже вписываем конструкцию, которая отвечает за условное форматирование ячеек по формуле ЕСЛИ. Также задаем цвет заливки, который в нашем конкретном случае будет зеленым.
   

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

   Результат выполнения правила
4. Аналогичным образом создаем второе правило, которое выделит названия товаров дешевле 100 руб.
   

   Создаем второе правило

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

Результат выполнения

Подводим итоги

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

Видео по теме