Как использовать условное форматирование для визуализации данных в Excel
forest_paw06.09.202522
Как использовать условное форматирование для визуализации данных в Excel
Визуализация данных в Excel с помощью условного форматирования — это мощный инструмент для быстрого анализа и представления информации. Цветовые шкалы, гистограммы и наборы значков позволяют превратить скучные таблицы в наглядные отчеты.
Рассмотрим отчет с избыточным количеством десятичных знаков, который сложно воспринимать.
Числа в сетке показывают процент брака по часам и линиям. В столбце A слева указаны часы с 7 утра до 2 дня. Вверху — пять производственных линий. Невозможно извлечь смысл из этой таблицы, так как диапазоны брака имеют шесть знаков после запятой. Это море цифр.
Выделите числа в отчете и выберите на вкладке Главная пункт Условное форматирование → Цветовые шкалы. Затем щелкните на втором значке, где красный цвет соответствует высоким значениям, а зеленый — низким.
На вкладке 'Главная' ленты выберите выпадающее меню 'Условное форматирование'. Затем выберите 'Цветовые шкалы'. Выберите вторую плитку, где красный для высоких чисел, желтый для средних и зеленый для низких.
Всего за четыре клика вы сможете выявить тенденции в данных. Например, станет видно, что Линия 2 начала день с высоким процентом брака, но улучшила показатели, а Линия 3 работала плохо весь день.
После применения цветовой шкалы видно, что Линия 2 начала с большого количества брака и улучшилась. Линия 3 имела высокий брак весь день.
Гистограммы (Data Bars)
Следующий инструмент, Гистограмма, похож на миниатюрную столбчатую диаграмму, заполняющую ячейку. В примере ниже выделите все ячейки с выручкой, кроме итоговой суммы.
20 клиентов в A2:A21 с выручкой в B2:B21. В ячейке B22 — общая сумма всех клиентов. Выделен только диапазон B2:B21, исключая итоговую ячейку.
Выберите Условное форматирование → Гистограммы → Зеленая. Теперь каждое число получает полосу цвета: большие числа — больше цвета, маленькие — почти нет.
После применения гистограммы самые большие ячейки имеют зеленую полосу, покрывающую большую часть ячейки. Маленькие ячейки имеют едва заметную зеленую полосу слева. Легко определить крупнейших клиентов по гистограмме прямо в ячейке.
Внимание:
Будьте осторожны, не включайте итоговую сумму перед выбором гистограмм. Если включить итог, он получит весь цвет, а остальные ячейки будут выглядеть одинаково.
Контраст, если бы вы включили итоговую ячейку в диапазон гистограммы. Итог получает весь зеленый цвет, а все остальные клиенты (даже крупные) получают едва заметный зеленый, из-за чего они выглядят одинаково. Вывод: не включайте итоговые ячейки в диапазон гистограммы.
Наборы значков (Icon Sets)
С помощью третьего инструмента, Наборы значков, вы можете выбрать наборы с тремя, четырьмя или пятью разными значками.
Доступно двадцать различных наборов значков. Первое различие — количество значков в наборе: 3, 4 или 5. Некоторые используют аналогию со светофором: зеленые, желтые, красные круги. Некоторые имеют стрелки, указывающие вверх, вправо или вниз. Некоторые имеют индикаторы уровня сигнала сотового телефона.
Большинство людей выравнивают числа по правому краю ячейки. Значки всегда появляются слева. Чтобы сдвинуть число ближе к значку, используйте кнопку Увеличить отступ на вкладке Главная.
На вкладке 'Главная' ленты есть значки для выравнивания по левому краю, по центру и по правому краю. Справа от них — 'Уменьшить отступ' и 'Увеличить отступ'. Чтобы сохранить выравнивание чисел по правому краю, но сдвинуть их ближе к значку, используйте выравнивание по правому краю, а затем дважды щелкните 'Увеличить отступ'.
Настройка диапазонов для значков
Все три инструмента визуализации работают, анализируя наибольшие и наименьшие числа в диапазоне. Excel разбивает этот диапазон на три равные части при использовании набора из трех значков. Это хорошо работает в стандартном случае.
Пять торговых представителей и четыре квартала с оценками. Числа варьируются от 80 до 100. Любой в нижней трети получает белую звезду. Верхняя треть получает золотую звезду. Сейчас звезды распределены равномерно. Обратите внимание, что у Эдди в Q1 была оценка 80, прежде чем перейти к следующему изображению.
Однако если Эдди получит 30 в Q1, все остальные автоматически получат золотую звезду, потому что их результаты окажутся в верхней трети по сравнению с его низким баллом. Это несправедливо, так как их оценки не улучшились.
В основном те же данные, но теперь Эдди получил 30 в Q1 вместо 80. Все значки изменились на золотые звезды, потому что по сравнению с плохим результатом Эдди в Q1 все остальные попадают в верхнюю треть. Это показывает, почему принятие настроек по умолчанию для наборов значков — не лучший способ.
Вы можете взять под контроль диапазоны для значков. Перейдите в Условное форматирование → Управление правилами и выберите Изменить правило. В выпадающем списке Тип выберите Число. Чтобы установить золотую звезду для значений 90 и выше, используйте настройки, показанные ниже. Для остальных значков можно выбрать Нет значка ячейки.
В диалоговом окне 'Изменение описания правила' показано несколько измененных настроек. Во-первых, золотая звезда теперь назначается для Числа >=90. (Сначала нужно выбрать 'Число' вместо 'Процент' в крайнем правом выпадающем списке.) Вместо выбора числа для второго и третьего значков в самом левом выпадающем меню для второго и третьего значков выбрано 'Нет значка ячейки'.
Использование условного форматирования для визуализации данных — это простой, но чрезвычайно эффективный способ улучшить читаемость отчетов и ускорить принятие решений. Настройка правил под ваши конкретные нужды позволяет получить максимальную отдачу от этих инструментов.
