Создать диаграмму тепловой карты в Excel
В Excel диаграмма тепловой карты выглядит как таблица, которая представляет собой визуальное представление, показывающее сравнительное представление набора данных. Если на вашем листе есть большой набор данных, вам действительно сложно определить более низкие или более высокие значения с первого взгляда, но на тепловой карте значение ячейки отображается в другом цветовом шаблоне, чтобы мы могли видеть большие данные или меньшие данные быстро и легко, как показано на скриншоте ниже.
- Создайте простую диаграмму тепловой карты с условным форматированием
- Создать динамическую диаграмму тепловой карты в Excel
- Пример 1. Создание динамической тепловой карты с помощью полосы прокрутки
- Пример 2: Создание динамической тепловой карты с помощью радиокнопок
- Пример 3. Создайте динамическую тепловую карту с помощью флажка.
- Скачать образец файла тепловой карты
- Видео: создание диаграммы интенсивности в Excel
Создайте простую диаграмму тепловой карты с условным форматированием
В Excel нет утилиты для прямой диаграммы тепловых карт, но с мощным Условное форматирование вы можете быстро создать тепловую карту, выполнив следующие действия:
1. Выберите диапазон данных, к которому вы хотите применить Условное форматирование.
2, Затем нажмите Главная > Условное форматирование > Цветовые шкалы, а затем выберите нужный стиль из раскрывающегося справа раскрывающегося списка (в данном случае я выберу Зеленый - желтый - красный цветовая шкала) см. снимок экрана:
3. Теперь создается тепловая карта, которая выделяет ячейки на основе их значений, зеленый цвет представляет самые высокие значения, а красный цвет представляет самые низкие значения, а остальные значения показывают цвет градиента между зеленым и красным. Смотрите скриншот:
4. Если вы хотите скрыть числа и оставить только цвета, выберите диапазон данных и нажмите Ctrl + 1 , чтобы открыть Формат ячеек диалоговое окно.
5. В Формат ячеек диалоговое окно под Номер регистрации вкладку нажмите На заказ вариант слева Категории список, а затем введите ;;; в Тип текстовое поле, см. снимок экрана:
6, Затем нажмите OK кнопку, и все числа скрыты, как показано на скриншоте ниже:
Внимание: Чтобы выделить ячейки другим цветом, который вам нравится, выберите диапазон данных и нажмите Главная > Условное форматирование > Управление правилами , чтобы перейти к Диспетчер правил условного форматирования диалоговое окно.
Затем дважды щелкните существующее правило, чтобы открыть Изменить правило форматирования диалоговое окно, а затем сбросьте правило в соответствии с вашими потребностями, см. снимок экрана:
Создать динамическую диаграмму тепловой карты в Excel
Пример 1. Создание динамической тепловой карты с помощью полосы прокрутки
Если на вашем листе есть данные из нескольких столбцов, но вы хотите отобразить их в ограниченном пространстве, в этом случае вы можете вставить полосу прокрутки на рабочий лист, чтобы сделать тепловую карту динамически, как показано ниже.
Чтобы создать этот тип динамической тепловой карты, выполните следующие действия:
1. Вставьте новый лист, а затем скопируйте месяцы первого столбца с исходного листа на этот новый лист.
2, Затем нажмите Застройщик > Вставить > Полоса прокрутки, см. снимок экрана:
3. Затем перетащите мышь, чтобы нарисовать полосу прокрутки под скопированными данными, щелкните полосу прокрутки правой кнопкой мыши и выберите Управление форматом, см. снимок экрана:
4. В Форматировать объект диалоговое окно под Control На вкладке установите минимальное значение, максимальное значение, инкрементное изменение, изменение страницы и связанную ячейку на основе вашего диапазона данных, как показано ниже:
5. Затем нажмите OK чтобы закрыть это диалоговое окно.
6. Теперь в ячейке B1 этого нового листа введите следующую формулу и нажмите Enter ключ для получения первого результата:
Внимание: В приведенной выше формуле data1! $ B $ 1: $ I $ 13 - исходный лист с диапазоном данных без заголовка строки (месяцы), $ I $ 1 это ячейка, с которой связана полоса прокрутки, $ B $ 1: B1 это ячейка, в которую вы выводите формулу.
7. Затем перетащите эту ячейку формулы в остальные ячейки, если вы хотите, чтобы на листе отображалось только 3 года, перетащите формулу из B1 в D13, см. Снимок экрана:
8. А затем примените Цветовая шкала Условное форматирование в новый диапазон данных для создания тепловой карты, теперь, когда вы перетаскиваете полосу прокрутки, тепловая карта будет перемещаться динамически, см. снимок экрана:
Пример 2: Создание динамической тепловой карты с помощью радиокнопок
Вы также можете создать динамическую тепловую карту с помощью переключателей, выберите один переключатель, чтобы выделить самые большие значения n, а выбор другой переключателя выделит самые маленькие значения n, как показано ниже:
Чтобы завершить динамическую тепловую карту этого типа, выполните следующие действия:
1. Нажмите Застройщик > Вставить > Кнопка выбора (элемент управления формой), затем перетащите мышь, чтобы нарисовать две переключатели, и отредактируйте текст по своему усмотрению, см. снимок экрана:
 |
 |
 |
2. После вставки переключателей щелкните правой кнопкой мыши первую и выберите Управление форматом, В Управление форматом диалоговое окно под Control вкладку, выберите ячейку, которая привязана к переключателю, см. снимок экрана:
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 ячейка связана с переключателями, 2 доллара: 13 иракских долларов это диапазон данных, к которому вы хотите применить условное форматирование, B2 - первая ячейка диапазона данных, число 15 - это конкретный номер, который вы хотите выделить.
8. Нажмите OK чтобы закрыть диалоговое окно, теперь при выборе первого переключателя будут выделены 15 наибольших значений, а при выборе второго переключателя будут выделены 15 наименьших значений, как показано ниже:
Пример 3. Создайте динамическую тепловую карту с помощью флажка.
В этом разделе я представлю динамическую тепловую карту с помощью флажка, который поможет вам показать или скрыть тепловую карту в зависимости от ваших потребностей. Если вы установите флажок, тепловая карта будет отображаться, если снять флажок, она будет сразу скрыта, см. Демонстрацию ниже:
1. Во-первых, вы должны преобразовать диапазон данных в формат таблицы, который поможет вам автоматически применить условное форматирование при вставке новой строки данных. Выберите диапазон данных и нажмите Ctrl + T, , чтобы открыть Создать таблицу диалоговое окно, см. снимок экрана:
2. Нажмите OK чтобы закрыть диалоговое окно, а затем щелкните Застройщик > Вставить > Флажок (контроль формы), затем перетащите мышь, чтобы установить флажок и отредактировать текст в соответствии с вашими потребностями, как показано ниже:
 |
|
 |
3. Затем щелкните правой кнопкой мыши флажок и выберите Управление форматом, В Форматировать объект диалоговое окно под Control вкладку, выберите ячейку, которая выровнена с флажком, см. снимок экрана:
4. Нажмите OK чтобы закрыть диалоговое окно, затем выберите диапазон данных, который вы хотите создать тепловую карту, и нажмите Главная > Условное форматирование > Новое правило , чтобы перейти к Новое правило форматирования диалоговое окно.
5. В Новое правило форматирования диалоговом окне выполните следующие действия:
- Выберите Отформатируйте все ячейки на основе их значений из файла Выберите тип правила список;
- Выберите 3-х цветная шкала из Стиль формата раскрывающийся список;
- Выберите Формула в Тип коробки под минимальный, средняя точка и максимальная выпадающие списки отдельно;
- Затем введите следующие формулы в три Значение текстовые поля:
- Минимум: = ЕСЛИ ($ M $ 1 = ИСТИНА, МИН ($ B $ 2: $ I $ 13), ЛОЖЬ)
- Середина: = ЕСЛИ ($ M $ 1 = ИСТИНА, СРЕДНЕЕ ($ B $ 2: $ I $ 13), ЛОЖЬ)
- Максимум: = ЕСЛИ ($ M $ 1 = ИСТИНА, МАКС ($ B $ 2: $ I $ 13), ЛОЖЬ)
- Затем укажите цвета выделения из Цвет раздел для ваших нужд.
Внимание: В приведенных выше формулах $ M $ 1 это ячейка, которая связана с флажком, 2 доллара: 13 иракских долларов - это диапазон данных, к которому вы хотите применить условное форматирование.
6. После завершения настройки нажмите OK кнопку, чтобы закрыть диалоговое окно, теперь, когда вы поставите галочку, тепловая карта будет отображаться, в противном случае она будет скрыта. См. Демонстрацию ниже:
Скачать образец файла тепловой карты
Видео: создание диаграммы тепловой карты в Excel
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
