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

Тепловая карта в Excel — это визуальное представление, которое быстро показывает вам сравнительное представление набора данных.

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

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

Создание тепловой карты в Excel

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

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

В этом руководстве вы узнаете, как:

• Быстро создавайте тепловую карту в Excel с помощью условного форматирования.
• Создайте динамическую тепловую карту в Excel.
• Создайте тепловую карту в сводных таблицах Excel.

Давайте начнем!

Создание тепловой карты в Excel с использованием условного форматирования

Если у вас есть набор данных в Excel, вы можете вручную выделить точки данных и создать тепловую карту.

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

Следовательно, условное форматирование — это правильный путь, поскольку он заставляет цвет в ячейке изменяться, когда вы меняете значение в ней.

Предположим, у вас есть набор данных, как показано ниже:

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

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

Это даст вам тепловую карту, как показано ниже:

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

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

Сделать это:

• Перейдите на главную -> Условное форматирование -> Цветовые шкалы -> Дополнительные параметры.
• В диалоговом окне «Новое правило форматирования» выберите «Трехцветная шкала» в раскрывающемся списке «Стиль формата».
• Теперь вы можете указать минимальное, среднее и максимальное значение и присвоить ему цвет. Поскольку мы хотим выделить все ячейки со значением ниже 700 красным цветом, измените тип на Number и значение на 700.
• Щелкните ОК.

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

БОНУСНЫЙ СОВЕТ: Хотите отображать только цвета, а не значения в ячейках. Для этого выделите все ячейки и нажмите Ctrl + 1. Откроется диалоговое окно «Формат ячеек». На вкладке Number выберите Custom и введите ;;;; в поле справа.

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

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

Поскольку условное форматирование зависит от значения в ячейке, как только вы меняете значение, условное форматирование пересчитывается и изменяется.

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

Давайте посмотрим на два примера создания тепловых карт с помощью интерактивных элементов управления в Excel.

Пример 1: Тепловая карта с использованием полосы прокрутки

Вот пример, в котором тепловая карта изменяется, как только вы используете полосу прокрутки для изменения года.

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

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

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

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

Вот шаги:

• На новом листе (или на том же листе) введите названия месяцев (просто скопируйте и вставьте его из исходных данных).
• Перейдите в Developer -> Ctrls -> Insert -> Scroll Bar. Теперь щелкните в любом месте листа, и появится полоса прокрутки. (нажмите здесь, если вы не можете найти вкладку разработчика).
• Щелкните полосу прокрутки правой кнопкой мыши и выберите «Управление форматом».
• В диалоговом окне «Управление форматом» внесите следующие изменения:
  • Минимальное значение: 1
  • Максимальное значение 5
  • Ссылка на ячейку: Sheet1! $ J $ 1 (вы можете щелкнуть значок справа, а затем вручную выбрать ячейку, которую вы хотите связать с полосой прокрутки).
• Щелкните ОК.
• В ячейке B1 введите формулу: = ИНДЕКС (Sheet1! $ B $ 1: $ H $ 13, ROW (), Sheet1! $ J $ 1 + COLUMNS (Sheet2! $ B $ 1: B1) -1)
• Измените размер и поместите полосу прокрутки внизу набора данных.

Теперь, когда вы меняете полосу прокрутки, значение в Sheet1! $ J $ 1 изменится, и, поскольку формулы связаны с этой ячейкой, оно будет обновлено, чтобы отображать правильные значения.

Кроме того, поскольку условное форматирование непостоянно, как только значение изменяется, оно также обновляется.

Посмотреть видео — Динамическая тепловая карта в Excel

[lyte id=’iTXul86xkG0′ /]

Пример 2: Создание динамической тепловой карты в Excel с помощью радиокнопок

Вот еще один пример, в котором вы можете изменить тепловую карту, выбрав переключатель:

В этом примере вы можете выделить 10 верхних / нижних значений на основе выбора переключателя / переключателя.

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

Создание тепловой карты в сводной таблице Excel

Условное форматирование в сводных таблицах работает так же, как и с любыми обычными данными.

Но вам нужно знать кое-что важное.

Позвольте мне на примере показать вам.

Предположим, у вас есть сводная таблица, как показано ниже:

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

• Выберите ячейки (B5: D14).
• Перейдите на главную -> Условное форматирование -> Цветовые шкалы и выберите цветовую шкалу, которую вы хотите применить. 

Это мгновенно создаст тепловую карту в сводной таблице.

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

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

Это происходит, поскольку мы применили условное форматирование только к ячейкам B5: D14.

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

• Выберите ячейки (B5: D14).
• Перейдите на главную -> Условное форматирование -> Цветовые шкалы и выберите цветовую шкалу, которую вы хотите применить.
• Снова перейдите на главную -> Условное форматирование -> Управление правилами.
• В диспетчере правил условного форматирования нажмите кнопку «Изменить».
• В диалоговом окне «Изменить правило форматирования» выберите третий вариант: все ячейки, в которых отображаются значения «Продажи» для «Дата» и «Клиент».

Теперь условное форматирование будет обновляться при изменении данных серверной части.

Примечание: Условное форматирование исчезнет, ​​если вы измените поля строки / столбца. Например, если вы удалите поле «Дата» и примените его снова, условное форматирование будет потеряно.