Как использовать условное форматирование для визуализации данных в Excel
Визуализация данных в Excel с помощью условного форматирования — это мощный инструмент для быстрого анализа и представления информации. Цветовые шкалы, гистограммы и наборы значков позволяют превратить скучные таблицы в наглядные отчеты.
Рассмотрим цветовые шкалы, гистограммы в ячейках и наборы значков. Команды ниже показаны для настольного Excel для Windows; в Excel для Mac и веба расположение настроек может отличаться.
Рассмотрим отчет с избыточным количеством десятичных знаков, который сложно воспринимать.
Выделите числа в отчете и выберите на вкладке Главная пункт Условное форматирование → Цветовые шкалы. Выберите шкалу, где красный цвет соответствует высоким значениям брака, жёлтый — средним, а зелёный — низким; ориентируйтесь на цвета, а не на позицию плитки.
Всего за четыре клика вы сможете выявить тенденции в данных. Например, станет видно, что Линия 2 начала день с высоким процентом брака, но улучшила показатели, а Линия 3 работала плохо весь день.
Гистограммы (Data Bars)
Следующий инструмент, Гистограмма, похож на миниатюрную столбчатую диаграмму, заполняющую ячейку. В примере ниже выделите все ячейки с выручкой, кроме итоговой суммы.
Выберите Условное форматирование → Гистограммы → Зеленая. Теперь каждое число получает полосу цвета: большие числа — больше цвета, маленькие — почти нет.
Внимание:
Будьте осторожны, не включайте итоговую сумму перед выбором гистограмм. При автоматических границах итог задаст максимум шкалы и получит самую длинную полосу, а различия между полосами отдельных клиентов станут менее заметны.
Наборы значков (Icon Sets)
С помощью третьего инструмента, Наборы значков, вы можете выбрать наборы с тремя, четырьмя или пятью разными значками.
Большинство людей выравнивают числа по правому краю ячейки. Значки всегда появляются слева. Чтобы сдвинуть число ближе к значку, используйте кнопку Увеличить отступ на вкладке Главная.
Настройка диапазонов для значков
Результат зависит от границ и типа порогов в правиле. В наборе из трёх значков с порогами «Процент» 33 и 67 границы считаются относительно минимума и максимума диапазона, а не по числу ячеек. Это не означает, что каждому значку достанется треть наблюдений. Тип «Процентиль» задаёт другие границы, а «Число» — фиксированные значения.
Однако если Эдди получит 30 в Q1, при пороге 67% все остальные оценки от 80 до 100 получат золотую звезду, потому что их результаты окажутся в верхней трети по сравнению с его низким баллом. Это несправедливо, так как их оценки не улучшились.
Вы можете взять под контроль диапазоны для значков. Перейдите в Условное форматирование → Управление правилами и выберите Изменить правило. Для обоих порогов в списке Тип выберите Число и задайте границы в порядке убывания. Чтобы установить золотую звезду для значений 90 и выше, используйте настройки, показанные ниже. Для остальных значков можно выбрать Нет значка ячейки.
Использование условного форматирования для визуализации данных — это простой, но чрезвычайно эффективный способ улучшить читаемость отчетов и ускорить принятие решений. Настройка правил под ваши конкретные нужды позволяет получить максимальную отдачу от этих инструментов.