Создание тепловой карты в 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. Нажмите ОК, чтобы закрыть диалоговое окно, затем повторите предыдущий шаг (шаг 2), чтобы связать второй переключатель с той же ячейкой (ячейка M1).

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

5. В диалоговом окне Новое правило форматирования выберите Использовать формулу для определения форматируемых ячеек из списка Выберите тип правила, затем введите эту формулу: =ЕСЛИ($M$1=1;ЕСЛИ(B2>=НАИБОЛЬШИЙ($B$2:$I$13;15);ИСТИНА;ЛОЖЬ)) в текстовое поле Форматировать значения, где эта формула истинна, и нажмите кнопку Формат, чтобы выбрать цвет. См. скриншот:

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

7. Для выделения 15 наименьших значений сохраните выделение данных и откройте диалоговое окно Новое правило форматирования, затем введите эту формулу: =ЕСЛИ($M$1=2;ЕСЛИ(B2<=НАИМЕНЬШИЙ($B$2:$I$13;15);ИСТИНА;ЛОЖЬ)) в текстовое поле Форматировать значения, где эта формула истинна, и нажмите кнопку Формат, чтобы выбрать другой цвет. См. скриншот:

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

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

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

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

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

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

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

4. Нажмите ОК, чтобы закрыть диалоговое окно, затем выберите диапазон данных, для которого вы хотите создать тепловую карту, и нажмите Главная > Условное форматирование > Новое правило, чтобы перейти в диалоговое окно Новое правило форматирования.

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

• Выберите Форматировать все ячейки на основе их значений из списка Выберите тип правила;
• Выберите 3-цветную шкалу из выпадающего списка Стиль форматирования;
• Выберите Формула в полях Тип под выпадающими списками Минимум, Середина и Максимум соответственно;
• Затем введите следующие формулы в три текстовых поля Значение:
• Минимум: =ЕСЛИ($M$1=ИСТИНА;МИН($B$2:$I$13);ЛОЖЬ)
• Середина: =ЕСЛИ($M$1=ИСТИНА;СРЗНАЧ($B$2:$I$13);ЛОЖЬ)
• Максимум: =ЕСЛИ($M$1=ИСТИНА;МАКС($B$2:$I$13);ЛОЖЬ)
• Затем укажите цвета выделения в разделе Цвет по вашему усмотрению.

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

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

Скачать пример файла тепловой карты

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