Реклама
Этот сайт использует Яндекс.Метрику для анализа посещаемости. Подробнее
Визуализация и дашборды

Как использовать условное форматирование для визуализации данных в Excel

forest_paw 08.10.2025 486

Как использовать условное форматирование для визуализации данных в Excel

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

Рассмотрим цветовые шкалы, гистограммы в ячейках и наборы значков. Команды ниже показаны для настольного Excel для Windows; в Excel для Mac и веба расположение настроек может отличаться.

Рассмотрим отчет с избыточным количеством десятичных знаков, который сложно воспринимать.

Числа в сетке показывают процент брака по часам и линиям. В столбце A слева указаны часы с 7 утра до 2 дня. Вверху — пять производственных линий. Невозможно извлечь смысл из этой таблицы, так как диапазоны брака имеют шесть знаков после запятой. Это море цифр.
Числа в сетке показывают процент брака по часам и линиям. В столбце A слева указаны часы с 7 утра до 2 дня. Вверху — пять производственных линий. Невозможно извлечь смысл из этой таблицы, так как диапазоны брака имеют шесть знаков после запятой. Это море цифр.

Выделите числа в отчете и выберите на вкладке Главная пункт Условное форматирование → Цветовые шкалы. Выберите шкалу, где красный цвет соответствует высоким значениям брака, жёлтый — средним, а зелёный — низким; ориентируйтесь на цвета, а не на позицию плитки.

На вкладке 'Главная' ленты выберите выпадающее меню 'Условное форматирование'. Затем выберите 'Цветовые шкалы'. Выберите шкалу с красным цветом для высоких чисел, жёлтым для средних и зелёным для низких.
На вкладке 'Главная' ленты выберите выпадающее меню 'Условное форматирование'. Затем выберите 'Цветовые шкалы'. Выберите шкалу с красным цветом для высоких чисел, жёлтым для средних и зелёным для низких.

Всего за четыре клика вы сможете выявить тенденции в данных. Например, станет видно, что Линия 2 начала день с высоким процентом брака, но улучшила показатели, а Линия 3 работала плохо весь день.

После применения цветовой шкалы видно, что Линия 2 начала с большого количества брака и улучшилась. Линия 3 имела высокий брак весь день.
После применения цветовой шкалы видно, что Линия 2 начала с большого количества брака и улучшилась. Линия 3 имела высокий брак весь день.

Гистограммы (Data Bars)

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

20 клиентов в A2:A21 с выручкой в B2:B21. В ячейке B22 — общая сумма всех клиентов. Выделен только диапазон B2:B21, исключая итоговую ячейку.
20 клиентов в A2:A21 с выручкой в B2:B21. В ячейке B22 — общая сумма всех клиентов. Выделен только диапазон B2:B21, исключая итоговую ячейку.

Выберите Условное форматирование → Гистограммы → Зеленая. Теперь каждое число получает полосу цвета: большие числа — больше цвета, маленькие — почти нет.

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

Внимание:

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

Контраст, если бы вы включили итоговую ячейку в диапазон гистограммы. Итог получает весь зеленый цвет, а все остальные клиенты (даже крупные) получают едва заметный зеленый, из-за чего они выглядят одинаково. Вывод: не включайте итоговые ячейки в диапазон гистограммы.
Контраст, если бы вы включили итоговую ячейку в диапазон гистограммы. Итог получает весь зеленый цвет, а все остальные клиенты (даже крупные) получают едва заметный зеленый, из-за чего они выглядят одинаково. Вывод: не включайте итоговые ячейки в диапазон гистограммы.

Наборы значков (Icon Sets)

С помощью третьего инструмента, Наборы значков, вы можете выбрать наборы с тремя, четырьмя или пятью разными значками.

В галерее доступны разные наборы значков; их состав зависит от версии Excel. Первое различие — количество значков в наборе: 3, 4 или 5. Некоторые используют аналогию со светофором: зеленые, желтые, красные круги. Некоторые имеют стрелки, указывающие вверх, вправо или вниз. Некоторые имеют индикаторы уровня сигнала сотового телефона.
В галерее доступны разные наборы значков; их состав зависит от версии Excel. Первое различие — количество значков в наборе: 3, 4 или 5. Некоторые используют аналогию со светофором: зеленые, желтые, красные круги. Некоторые имеют стрелки, указывающие вверх, вправо или вниз. Некоторые имеют индикаторы уровня сигнала сотового телефона.

Большинство людей выравнивают числа по правому краю ячейки. Значки всегда появляются слева. Чтобы сдвинуть число ближе к значку, используйте кнопку Увеличить отступ на вкладке Главная.

На вкладке 'Главная' ленты есть значки для выравнивания по левому краю, по центру и по правому краю. Справа от них — 'Уменьшить отступ' и 'Увеличить отступ'. Чтобы сохранить выравнивание чисел по правому краю, но сдвинуть их ближе к значку, используйте выравнивание по правому краю, а затем дважды щелкните 'Увеличить отступ'.
На вкладке 'Главная' ленты есть значки для выравнивания по левому краю, по центру и по правому краю. Справа от них — 'Уменьшить отступ' и 'Увеличить отступ'. Чтобы сохранить выравнивание чисел по правому краю, но сдвинуть их ближе к значку, используйте выравнивание по правому краю, а затем дважды щелкните 'Увеличить отступ'.

Настройка диапазонов для значков

Результат зависит от границ и типа порогов в правиле. В наборе из трёх значков с порогами «Процент» 33 и 67 границы считаются относительно минимума и максимума диапазона, а не по числу ячеек. Это не означает, что каждому значку достанется треть наблюдений. Тип «Процентиль» задаёт другие границы, а «Число» — фиксированные значения.

Пять торговых представителей и четыре квартала с оценками. Числа варьируются от 80 до 100. Любой в нижней трети получает белую звезду. Верхняя треть получает золотую звезду. Количество звёзд каждого вида зависит от самих оценок, а не обязано быть одинаковым. Обратите внимание, что у Эдди в Q1 была оценка 80, прежде чем перейти к следующему изображению.
Пять торговых представителей и четыре квартала с оценками. Числа варьируются от 80 до 100. Любой в нижней трети получает белую звезду. Верхняя треть получает золотую звезду. Количество звёзд каждого вида зависит от самих оценок, а не обязано быть одинаковым. Обратите внимание, что у Эдди в Q1 была оценка 80, прежде чем перейти к следующему изображению.

Однако если Эдди получит 30 в Q1, при пороге 67% все остальные оценки от 80 до 100 получат золотую звезду, потому что их результаты окажутся в верхней трети по сравнению с его низким баллом. Это несправедливо, так как их оценки не улучшились.

В основном те же данные, но теперь Эдди получил 30 в Q1 вместо 80. Все остальные значки стали золотыми: при минимуме 30, максимуме 100 и пороге 67% верхняя граница равна 76,9. Оценки от 80 до 100 превышают её; у Эдди с оценкой 30 остаётся нижний значок. Это показывает, почему принятие настроек по умолчанию для наборов значков — не лучший способ.
В основном те же данные, но теперь Эдди получил 30 в Q1 вместо 80. Все остальные значки стали золотыми: при минимуме 30, максимуме 100 и пороге 67% верхняя граница равна 76,9. Оценки от 80 до 100 превышают её; у Эдди с оценкой 30 остаётся нижний значок. Это показывает, почему принятие настроек по умолчанию для наборов значков — не лучший способ.

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

В диалоговом окне 'Изменение описания правила' показано несколько измененных настроек. Во-первых, золотая звезда теперь назначается для Числа >=90. (Сначала нужно выбрать 'Число' вместо 'Процент' в крайнем правом выпадающем списке.) Вместо выбора числа для второго и третьего значков в самом левом выпадающем меню для второго и третьего значков выбрано 'Нет значка ячейки'.
В диалоговом окне 'Изменение описания правила' показано несколько измененных настроек. Во-первых, золотая звезда теперь назначается для Числа >=90. (Сначала нужно выбрать 'Число' вместо 'Процент' в крайнем правом выпадающем списке.) Вместо выбора числа для второго и третьего значков в самом левом выпадающем меню для второго и третьего значков выбрано 'Нет значка ячейки'.

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

Поделиться:

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