Условное форматирование

Ещё один способ более наглядного представления информации в таблице — условное форматирование, то есть выделение ячеек разным цветом, в зависимости от показателей.

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

При анализе данных часто возникают следующие вопросы:

• Кто из студентов набрал достаточное количество баллов для автоматического зачёта?
• Каковы тенденции курсов валют?
• У кого из торговых агентов рост объёмов продаж составил более 10% в год?
• Какие показатели температуры воздуха являются исключениями в сводных отчётах за последние пять лет?
• Чьи спортивные показатели больше, меньше или соответствуют требованиям определённого спортивного разряда?
• Каково общее распределение сотрудников по возрасту?

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

Условный формат изменяет внешний вид диапазона ячеек на основе условия (или критерия). Если условие истинно, диапазон на основе этого условия; если условие ложно, диапазон ячеек не форматируется на основе этого условия.

Кнопку Условного форматирования можно увидеть в самом центре ленты Главная: щёлкнув по ней, вы можете выбрать разные стили пометки:

• Гистограммы помогают рассмотреть значение в ячейке относительно других ячеек. Длина гистограммы соответствует значению в ячейке. Чем она длиннее — тем выше значение. Гистограммы оптимальны при определении основных показателей особенно в больших объёмах данных — например, максимальный и минимальный объём продаж сувениров в отчёте о праздничных продажах.
• Цветовые шкалы — это визуальные инструкции, которые помогают понять распределение и разброс данных. Двухцветная шкала помогает сравнить диапазон ячеек путём использования градации двух цветов. Уровень яркости цвета соответствует высоким, средним или низким значениям. Например, в красно-зелёной шкале можно указать, что ячейки с более высокими значениями будут отображаться насыщенным зелёным, а ячейки с более низкими значениями будут насыщенным красным. Трёхцветная шкала помогает сравнить диапазон ячеек путём использования градации трёх цветов. Например, в красно-жёлто-зелёной шкале можно указать, что ячейки с высокими значениями будут зелёными, ячейки со средним значением будут жёлтыми, а ячейки с низкими значениями будут красными.
• Набор значков используется для аннотирования и классификации данных по трём-пяти категориям, разделённым пороговым значением. Каждый значок соответствует диапазону значений. Например, в наборе значков три стрелки: красная стрелка вверх соответствует высоким значениям, жёлтая средняя стрелка, направленная в сторону, соответствует средним значениям, а зелёная стрелка вниз соответствует низким значениям.

В Excel 2013/2016 доступны дополнительные наборы значков, включая треугольники, звёздочки и рамки. Кроме того, можно смешивать и сопоставлять значки из разных наборов и легко скрывать их из вида — например, отображать значки только для показателей высокой прибыли и не отображать их для средних и низких значений.

Для того чтобы применить условное форматирование, выполните следующие действия:

• Выделите диапазон ячеек.
• На ленте Главная в группе Стили щёлкните по кнопке Условное форматирование, а затем выберите одну из команд: Гистограммы, Цветовые шкалы или Наборы значков .
• Выберите гистограмму, шкалу или набор значков. На значке цветовой шкалы верхний цвет соответствует высоким значениям, нижний цвет — низким значениям.

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

Можно выделить цветом диапазон максимальных и минимальных значений ячеек на основе указанного порогового значения. Например, можно найти 5 самых продаваемых продуктов в региональном отчёте, нижние 15% продуктов в клиентском опросе или 25 высших зарплат в отчёте по персоналу отдела.

Для того чтобы применить форматирование с такими условиями, выполните следующие действия:

• Выделите диапазон ячеек.
• На ленте Главная в группе Стили щёлкните по кнопке Условное форматирование, а затем выберите команду Правила отбора первых и последних значений.
• Выберите нужную команду, например 10 первых элементов или Последние 10%.
• В открывшемся окне введите значения, которые будут использоваться, а затем выберите формат.

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

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

Для того чтобы задать формулу условного форматирования, выполните следующие действия:

• Выделите диапазон ячеек.
• На ленте Главная в группе Стили щёлкните по кнопке Условное форматирование, а затем выберите команду Создать правило.

• В окне Создание правила форматирования в верхней части выберите Использовать формулу для определения форматируемых ячеек.
• В нижней части окна введите формулу. Формула должна начинаться со знака равенства (=) и должна возвращать логическое значение ИСТИНА (1) или ЛОЖЬ (0).
• Щёлкните по кнопке Формат для отображения диалогового окна Форматирование ячеек.
• Выберите формат числа, шрифта, рамки или заполнения, которые необходимо применить, когда значение в ячейке удовлетворяет условию, а затем нажмите кнопку OK.

Для того чтобы снять условное форматирование, на ленте Главная в группе Стили щёлкните по кнопке Условное форматирование, выберите команду Удалить правила и нужный вариант удаления (выделенных ячеек, всего листа, таблицы или сводной таблицы).