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

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

• Создайте простую тепловую карту с помощью Использовать условное форматирование
• Создайте динамическую тепловую карту в Excel
• Пример 1: создание динамической тепловой карты с использованием полосы прокрутки
• Пример 2: создание динамической тепловой карты с использованием Переключатель
• Пример 3: создание динамической тепловой карты с использованием Флажок
• Скачать образец файла тепловой карты
• Видео: создание тепловой карты в Excel

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

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

1. Выделите диапазон данных, к которому необходимо применить условное форматирование.

2. Затем перейдите на вкладку Главная → Условное форматирование → Цветовые шкалыи выберите нужный стиль из раскрывающегося списка справа (в данном случае выбрана)зелёно-жёлто-красная цветовая шкала). См. скриншот:

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

4. Чтобы скрыть числа и оставить только цвета, выделите диапазон данных и нажмите клавиши Ctrl + 1, чтобы открыть диалоговое окно Установить формат ячейки.

5. В диалоговом окне Установить формат ячейки перейдите на вкладку Число, выберите (все форматы) в списке Числовые форматы слева и введите ;;; в поле Тип. См. скриншот:

6. Нажмите кнопку ОК, и все числа скроются, как показано на скриншоте ниже:

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

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

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

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

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

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

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

2. Затем перейдите на вкладку Разработчик → Вставить → Полоса прокрутки. См. скриншот:

3. Перетащите курсор, чтобы нарисовать полосу прокрутки под скопированными данными, щелкните её правой кнопкой мыши и выберите Формат объекта. См. скриншот:

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

5. Нажмите кнопку ОК, чтобы закрыть это диалоговое окно.

6. Теперь введите в ячейку B1 нового листа следующую формулу и нажмите клавишу Enter, чтобы получить первый результат:

Примечание: в приведённой выше формуле data1!$B$1:$I$13 — это исходный лист с диапазоном данных без заголовков строк (месяцев), $I$1 — ячейка, связанная с полосой прокрутки, а $B$1:B1 — ячейка, в которую вводится формула.

7. Перетащите ячейку с формулой на остальные ячейки. Если вы хотите отображать на листе только 3 года, перетащите формулу из B1 в D13. См. скриншот:

8. Затем примените функцию Цветовая шкала из инструмента Использовать условное форматирование к новому диапазону данных, чтобы создать тепловую карту. Теперь при перемещении полосы прокрутки тепловая карта будет динамически обновляться. См. скриншот:

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

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

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

1. Перейдите на вкладку Разработчик → Вставить → Переключатель (элемент управления формой), затем перетащите курсор, чтобы нарисовать два переключателя, и измените подписи по своему усмотрению. См. скриншот:

2. После вставки переключателя щелкните правой кнопкой мыши по первому элементу и выберите Формат объекта. В диалоговом окне Формат объекта на вкладке Элемент управления укажите ячейку, связанную с переключателем. См. скриншот:

3. Нажмите OK, чтобы закрыть диалоговое окно, а затем повторите описанный выше шаг (шаг 2), чтобы связать второй переключатель с той же ячейкой (ячейка M1).

4. Затем примените условное форматирование к диапазону данных: выберите нужный диапазон и нажмите Главная > Условное форматирование > Создать правило (см. снимок экрана):

5. В диалоговом окне Создание правила форматирования выберите Использовать формулу для определения форматируемых ячеек в списке Выберите тип правила, а затем введите следующую формулу: =IF($M$1=1,IF(B2>,=LARGE($B$2:$I$13,15),TRUE,FALSE)) в поле Форматировать значения, для которых формула принимает значение ИСТИНА, и нажмите кнопку Формат, чтобы выбрать цвет. См. снимок экрана:

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

7. Чтобы выделить 15 наименьших значений, оставьте выделенные данные и откройте диалоговое окно Создание правила форматирования, затем введите эту формулу: =IF($M$1=2,IF(B2<,=SMALL($B$2:$I$13,15),TRUE,FALSE)) в поле Форматировать значения, для которых формула принимает значение ИСТИНА и нажмите кнопку Формат, чтобы выбрать нужный вам цвет. См. снимок экрана:

Примечание: В приведённых выше формулах $M$1 — это ячейка, связанная с переключателем, $B$2:$I$13 — диапазон данных, к которому вы хотите применить условное форматирование, B2 — первая ячейка этого диапазона, а число 15 — конкретное значение, которое вы хотите выделить.

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

Пример 3: создание динамической тепловой карты с помощью Флажок

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

1. Сначала преобразуйте диапазон данных в таблицу, чтобы условное форматирование автоматически применялось при добавлении новых строк. Выделите диапазон данных и нажмите клавиши Ctrl + T, чтобы открыть диалоговое окно Создание таблицы, см. снимок экрана:

2. Нажмите OK, чтобы закрыть диалоговое окно, затем перейдите в раздел Разработчик > Вставить > Флажок (элемент управления формой), после чего перетащите указатель мыши, чтобы нарисовать флажок, и измените текст по своему усмотрению, как показано на снимках экрана ниже:

3. Щёлкните правой кнопкой мыши элемент Флажок и выберите Формат элемента управления. В диалоговом окне Формат объекта на вкладке Элемент управления укажите ячейку, связанную с флажком (см. снимок экрана):

4. Нажмите OK, чтобы закрыть диалоговое окно. Затем выделите диапазон данных, для которого требуется создать тепловую карту, и щёлкните Главная > Использовать условное форматирование > Создать правило, чтобы открыть диалоговое окно Создание правила форматирования.

5. В диалоговом окне Создание правила форматирования выполните следующие действия:

• Выберите Форматировать все ячейки на основе их значенийв списке Выбор типа правила;
• Выберите 3-цветная шкалав раскрывающемся списке Стиль формата;
• Выберите Формулав полях Тип, расположенных под раскрывающимися списками Минимум,Среднее значениеи Максимумсоответственно;
• Затем введите следующие формулы в три поля ЗначениеТекстовое поле:
• Минимум:=IF($M$1=TRUE,MIN($B$2:$I$13),FALSE)
• Среднее значение:=IF($M$1=TRUE,AVERAGE($B$2:$I$13),FALSE)
• Максимум:=IF($M$1=TRUE,MAX($B$2:$I$13),FALSE)
• Затем укажите цвета выделения в разделе Цвет по своему усмотрению.

Примечание: В приведённых выше формулах $M$1 — это ячейка, связанная с флажком, а $B$2:$I$13 — диапазон данных, к которому вы хотите применить условное форматирование.

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

Скачать образец файла тепловой карты

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