Excel: условное форматирование — что это такое?

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

Существует три основные области применения условного форматирования в Excel:

1. Визуализация значений: Все числа в определенном диапазоне ячеек рассматриваются по отношению друг к другу. При таком типе условного форматирования внешний вид ячеек зависит от размера значений. Если значения отрицательны, больше или меньше других чисел, они выделяются.
2. Проверка данных: В зависимости от объема данных оценка каждой отдельной ячейки на предмет отклонений занимает много времени. Именно здесь может быть полезно условное форматирование. Определив определенные условия, можно выделить только те ячейки, которые вы ищете.
3. Фильтр двойных и отличных значений: Если вам нужно создать список отличительных значений, вы можете использовать формулы условного форматирования в Excel и co. Вы можете отфильтровать определенные значения, маскируя двойные значения. Кроме того, можно удалить дублирующиеся значения.

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

Задание условного форматирования на основе определенных правил делает его немного более гибким. Поскольку этот тип форматирования основан на пороговых значениях, учитываются только определенные деления ячеек. В зависимости от ваших потребностей вы можете форматировать определенные или верхние/нижние значения. Сюда входят предопределенные правила форматирования для 10 верхних/нижних или 10% элементов. Нажав на «Правила выделения ячеек», вы также можете выбирать между:

• Больше, чем…
• Меньше, чем…
• Между…
• Равно…
• Текст…
• Дата…
• Двойные значения…

Если вместо работы с числами вы обычно работаете с текстовыми записями и хотите выделить ячейки, содержащие определенные термины, нажмите «Правила выделения ячеек» > «Текст» и введите текст, по которому вы хотите отфильтровать информацию. Нажмите «ОК», и информация будет выделена.

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

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

Чтобы создать персонализированное правило с использованием формул, нажмите «Новое правило…» и выберите «Использовать формулу для определения ячеек для форматирования». Введите формулу в поле ввода, выберите нужный формат и нажмите «ОК».

В следующих примерах условное форматирование применяется к прибыли компании в столбце B с помощью полос данных. Цветные стрелки в столбце C показывают прибыль и убытки по сравнению с предыдущим месяцем.

Чтобы добавить столбцы данных в столбец B, выделите ячейки с B2 по B13 и щелкните на следующем:

• «Условное форматирование»
• «Полосы данных»
• «Заполнить…» (сделать выбор).

Теперь выделите ячейки с C3 по C13 и нажмите на:

• «Условное форматирование»
• «Наборы значков»
• «Направления»

Если, например, вы хотите показать тенденцию к росту в столбце C только тогда, когда прибыль увеличивается не менее чем на 5 евро по сравнению с предыдущим месяцем, необходимо отредактировать правила. Отметьте ячейки с C3 по C13 и нажмите «Условное форматирование». Выберите «Управление правилами…» и «Редактировать правила».

Теперь измените значение «>» для зеленой стрелки с «0» на «5» и нажмите «ОК».

Снова появится диалоговое окно менеджера условного форматирования. Нажмите «OK», чтобы подтвердить выбранные изменения для выбранного диапазона ячеек. Тенденция к росту (зеленая стрелка) в ячейке C7 больше не будет отображаться, поскольку с мая по июнь не было достигнуто увеличение прибыли как минимум на $5.

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