Как создать тепловую карту в Excel: пошаговое руководство

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

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

В этом руководстве вы создадите тепловую карту с нуля, настроите её цвета и примените тот же приём к сводным таблицам. Мы также разберём ошибки, которые чаще всего сбивают с толку новичков.

Что такое тепловая карта в Excel?

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

Это делается с помощью функции Условное форматирование, а именно опции Цветовые шкалы. 

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

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

Рассмотрим это на примере набора данных о продажах: 

Набор данных о продажах для создания тепловой карты. Изображение автора.

Как создать тепловую карту в Excel

Чтобы создать тепловую карту:

1. Выделите диапазон ячеек, который нужно раскрасить. В нашем случае выбираем только числа, без названий регионов и заголовков месяцев
2. Перейдите на вкладку Главная > Условное форматирование
3. Нажмите на раскрывающееся меню
4. Наведите курсор на Цветовые шкалы — откроется подменю с несколькими готовыми цветовыми сочетаниями. Каждое из них показывает мини-превью в виде маленькой цветной сетки, чтобы вы примерно понимали, как будет выглядеть результат, до применения

Создайте тепловую карту с помощью условного форматирования. Изображение автора.

Теперь наша таблица продаж раскрашена. Сильные месяцы на Юге — ближе к зелёному. Слабые на Востоке — ближе к красному или оранжевому, а всё остальное — где-то посередине.

Примечание: по мере добавления новых значений цвета будут подстраиваться под новые минимумы и максимумы.

Как настроить тепловую карту

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

Изменить цветовую шкалу

Чтобы изменить цветовую шкалу: 

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

Часть шкал двухцветные, часть — трёхцветные. 

• Двухцветная шкала (например, от белого к красному) идеально подходит, когда важна только одна «сторона», например, выделение лучших показателей. 
• Трёхцветная шкала лучше работает, когда важны оба края — например, обозначить и недовыполняющие, и перевыполняющие регионы в таблице продаж.

Цвета из библиотеки цветовых шкал для тепловой карты. Изображение автора.

Инвертировать цвета

Чтобы поменять цвета местами: 

1. Выделите диапазон данных 
2. Откройте Условное форматирование > Управление правилами 
3. В диалоговом окне Диспетчер правил условного форматирования выберите правило цветовой шкалы, нажмите Изменить правило — вы увидите параметры для минимального и максимального цветов. 
4. Поменяйте их местами — шкала инвертируется. То, что было синим, станет красным, и наоборот.

Это полезно, когда низкие числа — это хорошие новости, а не плохие.

Поменяйте местами цвета тепловой карты. Изображение автора.

Создать пользовательскую цветовую шкалу

Если ни один пресет не подходит, соберите собственную цветовую шкалу. Для этого: 

1. Выделите диапазон данных
2. Перейдите в Условное форматирование > Создать правило > Форматировать все ячейки на основе их значений
3. Задайте свои цвета для минимума, середины и максимума вместо выбора готовой комбинации

Так вы сможете подобрать тепловую карту под фирменную палитру.

Добавьте пользовательские цвета в тепловую карту. Изображение автора.

Настроить минимальные, средние и максимальные значения

По умолчанию Excel строит шкалу на основе самого низкого и самого высокого чисел в выбранном диапазоне. Чаще всего это нормально, но при наличии одного экстремального выброса картина может искажаться.

Чтобы это настроить: 

1. Выделите диапазон данных
2. Перейдите в Условное форматирование > Управление правилами > Изменить правила
3. В диалоговом окне Изменение правила форматирования в выпадающем списке поменяйте тип значения для минимума, середины или максимума с Самое низкое значение и Самое высокое значение на Число, Процент или Процентиль в выпадающем списке
4. Укажите выбранные минимальные, средние и максимальные значения 

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

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

Далее применим эти инструменты в нескольких ситуациях, начиная с данных о продажах.

Как создавать разные виды тепловых карт

Чтобы создать разные типы тепловых карт, каждый раз следуйте одним и тем же пяти шагам:

1. Выделите данные
2. Перейдите на вкладку Главная 
3. Откройте Условное форматирование 
4. Нажмите Цветовые шкалы
5. Выберите шкалу

Рассмотрим это на четырёх разных сценариях: 

Тепловые карты для данных о продажах

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

• Выделите таблицу
• Перейдите в Главная > Условное форматирование > Цветовые шкалы и выберите трёхцветную шкалу

После этого Восток будет «зеленеть» в каждом месяце — это ваш сильнейший регион. Запад останется красным — ему требуется внимание. 

Здесь уместна трёхцветная шкала, потому что важно видеть оба края одновременно: и лучшего, и слабейшего исполнителя рядом.

Примените тепловую карту к набору данных о продажах. Изображение автора.

Тепловые карты для панелей производительности

Теперь представьте, что вы управляете небольшой командой и вместо продаж смотрите на оценки эффективности по нескольким навыкам.

На этот раз примените двухцветную шкалу, потому что вам нужно лишь понять, кто силён, а кто отстаёт. В результате видно:  

• Lee почти везде зелёный
• Priya почти везде белая
• Sam — смешанная картина: силён в дожимах, но слаб по сделкам 

Примените тепловую карту для отслеживания показателей дашборда. Изображение автора.

Тепловые карты для результатов опросов

Допустим, вы провели клиентский опрос и получили средние оценки по шкале от 1 до 5 для нескольких вопросов и периодов.

Здесь установите минимум и максимум вручную — 1 и 5 — вместо того, чтобы Excel вычислял их из данных. 

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

Примените тепловую карту для отслеживания опроса клиентов. Изображение автора.

Тепловые карты для трекинга проектов

Предположим, вы ведёте проект с несколькими задачами и хотите с первого взгляда понять, какие отстают.

Настройте шкалу так, чтобы низкие проценты были красными, а высокие — белыми. Тогда красный всегда будет означать «отстаёт». 

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

• «Документация» остаётся красной на всех этапах — это самый низкий процент готовности в таблице на каждом шаге
• «Интеграция API» выглядит нормально на этапе планирования, а затем резко проседает

В этом ценность тепловой карты: она показывает и медленную задачу, и когда она начала отставать.

Примените тепловую карту для отслеживания задач проекта. Изображение автора. 

Как использовать тепловые карты со сводными таблицами

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

Сначала вам нужна сводная таблица, к котрой это можно применить. Чтобы вставить её:

1. Выделите вашу таблицу
2. Перейдите во вкладку Вставка > Сводная таблица 
3. Выберите Новый лист и нажмите ОК

Создайте сводную таблицу. Изображение автора.

4. Справа откроется область пустой сводной таблицы и список полей с Регион, Месяц и Продажи
5. Перетащите Регион в область Строки, Месяц — в Столбцы, а Продажи — в Значения

Настройте поля сводной таблицы. Изображение автора.

Теперь у вас есть сводная таблица для раскраски. Чтобы раскрасить её:

1. Выделите значения внутри неё, а не подписи строк или столбцов
2. Перейдите в Главная > Условное форматирование > Цветовые шкалы и выберите шкалу, как раньше

Примените условное форматирование в сводной таблице. Изображение автора.

Тепловые карты vs цветовые шкалы vs условное форматирование

Тепловые карты, цветовые шкалы и условное форматирование часто путают, но это не одно и то же:

• Условное форматирование — это общий инструмент, который меняет внешний вид ячеек в зависимости от их значений, и он охватывает не только цвет. Полосы данных, наборы значков и правила выделения — всё это входит в условное форматирование.
• Цветовые шкалы — один из вариантов условного форматирования. Он закрашивает ячейки по градиенту в зависимости от их значений. Это одна из нескольких функций в том же меню.
• Тепловая карта — это видимый результат. Так выглядят данные после применения к ним цветовой шкалы. 

Так что когда кто-то говорит «добавьте тепловую карту», на деле это означает «примените цветовую шкалу с помощью условного форматирования». 

Распространённые ошибки при работе с тепловыми картами

Большинство тепловых карт сталкиваются с одними и теми же проблемами. Вот их причины и способы исправления.

Цвета не соответствуют данным

Обычно это происходит, когда минимум и максимум цветовой шкалы оставлены как «Самое низкое значение» и «Самое высокое значение», а в наборе данных есть один экстремальный выброс. Это число «растягивает» всю шкалу, и остальные значения сжимаются в узкую полосу похожих цветов.

Как исправить: Задайте фиксированные значения минимума и максимума вместо автоматического расчёта в Excel. Откройте Условное форматирование > Управление правилами > Изменить правило и поменяйте тип значения с «Самое низкое значение» на «Число».

Пустые ячейки искажают результаты

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

Как исправить: Уберите пустые ячейки из диапазона или заполните их нулями/заглушками перед применением цветовой шкалы. Особенно внимательно проверьте диапазон, если данные пришли из отчёта с пропусками.

Форматирование не обновляется

Условное форматирование, применённое к фиксированному выделению, остаётся фиксированным. Оно не расширится только потому, что вы ввели числа в следующую строку.

Как исправить: Преобразуйте диапазон в таблицу Excel перед применением цветовой шкалы. Таблицы Excel автоматически расширяются при добавлении строк, и правила условного форматирования расширяются вместе с ними. 

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

Слишком много цветов усложняют чтение данных

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

Как исправить: Ограничьтесь одной цветовой шкалой на диапазон и используйте не более двух–трёх цветов. Если таблице нужна разная подсветка, применяйте цветовые шкалы к меньшим отдельным участкам, а не наслаивайте правила друг на друга.

Лучшие практики для тепловых карт в Excel

Тепловая карта полезна только тогда, когда её легко читать. Эти привычки помогут это обеспечить:

• Используйте единые цветовые шкалы в связанных отчётах. Переход от красно-зелёной гаммы в одном месяце к синей-белой в следующем усложняет сравнение, даже если каждый отчёт по отдельности выглядит нормально.
• Избегайте слишком ярких и отвлекающих цветов — они мешают сосредоточиться на самих данных.
• Выбирайте палитры, подходящие для дальтоников, например синий-оранжевый или синий-жёлтый, и избегайте красно-зелёной — это самая сложная для различения комбинация.
• Удаляйте лишние пустые ячейки, исправляйте несогласованные форматы чисел и проверяйте, нет ли текста в числовом диапазоне — подготовьте данные перед применением тепловой карты.
• Комбинируйте тепловые карты с фильтрами или сводными таблицами для больших наборов данных — так пользователи смогут сузить выборку ещё до того, как цвета станут полезны.

Заключение 

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

Наш курс Data Analysis in Excel раскрывает обе темы глубже, если вы хотите продолжить.