Как создать тепловую карту в Excel: статическая и динамическая

Это пошаговое руководство проведет вас через процесс создания тепловой карты в Excel с практическими примерами.

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

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

Тепловая карта (также известная как тепловая карта) — это визуальная интерпретация числовых данных, где разные значения представлены разными цветами. Как правило, используются цветовые схемы от теплого к холодному, поэтому данные представлены в виде горячих и холодных точек.

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

Вот несколько типичных примеров:

• Тепловая карта температуры воздуха – используется для визуализации данных о температуре воздуха в определенном регионе.
• Географическая тепловая карта — отображает некоторые числовые данные по географической области с использованием различных оттенков.
• Тепловая карта управления рисками — наглядно и лаконично показывает различные риски и их влияние.

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

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

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

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

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

1. Выберите свой набор данных. В нашем случае это B3:M5.
   
2. На Дом вкладка, в Стили группа, нажмите Условное форматирование > Цветовые шкалы, а затем щелкните нужную цветовую шкалу. Когда вы наведете указатель мыши на определенную цветовую шкалу, Excel покажет вам предварительный просмотр в реальном времени прямо в вашем наборе данных.

   Для этого примера мы выбрали Красный – Желтый – Зеленый цветовая гамма:
   

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

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

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

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

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

1. На Дом вкладка, в Стили группа, нажмите Условное форматирование > Цветовые шкалы > Дополнительные правила.
   
2. в Новое правило форматирования диалоговом окне выполните следующие действия:
   • Выберите 3-цветную шкалу из Стиль формата выпадающий список.
   • За Минимум и/или Максимум значение, выберите Число в Тип раскрывающемся списке и введите нужные значения в соответствующие поля.
   • За Серединавы можете установить либо Число или же Процентиль (обычно 50%).
   • Назначьте цвет каждому из трех значений.

   Для этого примера мы настроили следующие параметры:
   

   На этой пользовательской тепловой карте все температуры ниже 45 °F выделены одним и тем же оттенком зеленого, а все температуры выше 70 °F — одним и тем же оттенком красного:
   

Создать тепловую карту в Excel без цифр

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

1. Выберите тепловую карту.
2. Нажмите Ctrl + 1, чтобы открыть Формат ячеек диалог.
3. На Число вкладка, под КатегорияВыбрать Обычай.
4. в Тип поле, введите 3 точки с запятой (;;;).
5. Нажмите OK, чтобы применить пользовательский числовой формат.
   

Вот и все! Теперь ваша тепловая карта Excel отображает только цветовые коды без номеров:

Тепловая карта Excel с квадратными ячейками

Еще одно улучшение, которое вы можете внести в свою тепловую карту, — это идеально квадратные ячейки. Ниже приведен самый быстрый способ сделать это без каких-либо скриптов или кодов VBA:

1. Выровняйте заголовки столбцов по вертикали. Чтобы заголовки столбцов не обрезались, измените их выравнивание на вертикальное. Это можно сделать с помощью программы Ориентация кнопка на Дом вкладка, в Выравнивание группа:
   

   Дополнительные сведения см. в разделе Как выровнять текст в Excel.

2. Установить ширину столбца. Выберите все столбцы и перетащите край любого заголовка столбца, чтобы сделать его шире или уже. При этом появится всплывающая подсказка с точным количеством пикселей — запомните это число.
   
3. Установить высоту строки. Выберите все строки и перетащите край любого заголовка строки на то же значение пикселей, что и у столбцов (в нашем случае 26 пикселей).
   

   Сделанный! Все ячейки вашей карты шляп теперь имеют квадратную форму:
   

Как сделать тепловую карту в сводной таблице Excel

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

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

Как сделать тепловую карту сводной таблицы динамической

Чтобы заставить тепловую карту сводной таблицы Excel автоматически включать новые записи, выполните следующие действия:

1. Выберите любую ячейку на вашей текущей тепловой карте.
2. На Дом вкладка, в Стили группа, нажмите Условное форматирование > Управление правилами…
3. в Диспетчер правил условного форматированиявыберите правило и нажмите кнопку Изменить правило кнопка.
4. в Изменить правило форматирования диалоговое окно, под Применить правило к, выберите третий вариант. В нашем случае это звучит так: Все ячейки со значениями «Сумма продаж» для «Торговый посредник» и «Продукт»..
5. Нажмите OK дважды, чтобы закрыть оба диалоговых окна.
   

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

Как создать динамическую тепловую карту в Excel с флажком

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

1. Вставьте флажок. Рядом с вашим набором данных поставьте галочку (управление формой). Для этого нажмите кнопку Разработчик вкладка > Вставлять > Элементы управления формой > Флажок. Вот подробные шаги, чтобы добавить флажок в Excel.
2. Свяжите флажок с ячейкой. Чтобы связать флажок с определенной ячейкой, щелкните флажок правой кнопкой мыши, щелкните Управление форматомпереключитесь на Контроль вкладку, введите адрес ячейки в поле Сотовая ссылка поле и нажмите кнопку ОК.

   В нашем случае флажок связан с ячейкой O2. При установленном флажке в связанной ячейке отображается ИСТИНА, в противном случае – ЛОЖЬ.
   

3. Настройте условное форматирование. Выберите набор данных, нажмите Условное форматирование > Цветовые шкалы > Дополнительные правилаи настроить пользовательская цветовая шкала этим способом:
   • в Стиль формата В раскрывающемся списке выберите 3-цветная шкала.
   • Под Минимум, Середина а также Максимумвыберите Формула из Тип выпадающий список.
   • в Ценность поля, введите следующие формулы:

     Для минимума:

     =ЕСЛИ($O$2=ИСТИНА, МИН($B$3:$M$5), ЛОЖЬ)

     Для средней точки:

     =ЕСЛИ($O$2=ИСТИНА, СРЕДНЕЕ($B$3:$M$5), ЛОЖЬ)

     Для максимума:

     =ЕСЛИ($O$2=ИСТИНА, МАКС($B$3:$M$5), ЛОЖЬ)

     В этих формулах используются функции МИН, СРЗНАЧ и МАКС для определения наименьшего, среднего и наибольшего значений в наборе данных (B3:M5), когда связанная ячейка (O2) имеет значение ИСТИНА, т. е. когда установлен флажок.

   • в Цвет раскрывающихся списков выберите нужные цвета.
   • Нажмите кнопку ОК.
     

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

Кончик. Чтобы удалить значение ИСТИНА / ЛОЖЬ из представления, вы можете связать флажок с какой-либо ячейкой в ​​пустом столбце, а затем скрыть этот столбец.

Как сделать динамическую тепловую карту в Excel без цифр

Чтобы скрыть числа на динамической тепловой карте, вам нужно создать еще одно правило условного форматирования, которое применяет пользовательский формат чисел. Вот как:

1. Создайте динамическую тепловую карту, как описано в приведенном выше примере.
2. Выберите свой набор данных.
3. На Дом вкладка, в Стили группа, нажмите Новое правило > Используйте формулу, чтобы определить, какие ячейки нужно отформатировать.
4. в Форматировать значения, где эта формула верна введите эту формулу:

   =ЕСЛИ($O$2=ИСТИНА, ИСТИНА, ЛОЖЬ)

   Где O2 — ваша связанная ячейка. Формула говорит применять правило только тогда, когда установлен флажок (O2 имеет значение TRUE).
   

5. Нажмите на Формат… кнопка.
6. в Формат ячеек диалоговое окно, переключитесь на Число вкладка, выберите Обычай в Категория список, введите 3 точки с запятой (;;;) в Тип поле и нажмите кнопку ОК.
   
7. Нажмите OK, чтобы закрыть Новое правило форматирования диалоговое окно.

С этого момента установка флажка будет отображать тепловую карту и скрывать числа:

Чтобы переключаться между двумя разными типами тепловых карт (с числами и без них), вы можете вставить три переключателя. Затем настройте 3 отдельных правила условного форматирования: 1 правило для тепловой карты с числами и 2 правила для тепловой карты без чисел. Или вы можете создать общее правило цветовой шкалы для обоих типов, используя функцию ИЛИ (как это сделано в нашем образце рабочего листа ниже).

В результате вы получите вот такую ​​красивую динамическую тепловую карту:

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

Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Практическая рабочая тетрадь для скачивания

Тепловая карта в Excel — примеры (файл .xlsx)

Вас также могут заинтересовать