Как сделать тепловую карту в excel
Расширенные таблицы Excel — Тепловая карта
Тепловая карта обычно используется для обозначения цветного различия областей в двумерном массиве, причем каждый цвет связан с различными характеристиками, общими для каждой области.
В Excel тепловую карту можно применить к диапазону ячеек на основе значений, которые они содержат, используя цвета ячеек и / или цвета шрифта. Условное форматирование Excel пригодится для этой цели.
Что такое тепловая карта?
Тепловая карта — это визуальное представление данных в таблице для выделения значимых точек данных. Например, если у вас есть месячные данные о продажах продуктов за последний год, вы можете прогнозировать, в какие месяцы продажи продукта были высокими или низкими.
Тепловая карта выглядит так, как показано ниже.
Преимущества тепловых карт
Тепловая карта может использоваться для визуального отображения различных диапазонов данных с разными цветами. Это очень полезно, когда у вас большие наборы данных и вы хотите быстро визуализировать определенные черты в данных.
Тепловые карты используются для —
Подготовка данных
Расположите данные в таблице.
Как видите, данные за финансовый год, апрель — март, помесячно для каждого продукта. Вы можете создать тепловую карту, чтобы быстро определить, в какие месяцы продажи были высокими или низкими.
Создание тепловой карты
Ниже приведены шаги для создания тепловой карты —
Тепловая карта диаграммы с графиками для анализа продаж в Excel
Обычно для анализа предельного распределения двух переменных значений показателей используется точечный график. Но в данном примере будет использована тепловая карта для анализа активности продаж в Excel относительно заданного года и месяца. Плюс ко всему к тепловой карте будут добавлены две гистограммы, позволяющие более детально проанализировать два измерения с учетом их суммарных значений.
Создание тепловой карты графика с помощью сводной таблице в Excel
Маржинальные гистограммы будут связаны со сводной таблицей, в которой используется так называемые тепловая карта создана условным форматированием. Поэтому для исходных данных на втором листе «Данные» мы создаем сводную таблицу, которую помещаем отдельно на лист «Карта». Сначала выделите диапазон ячеек Данные!$A$1:$C$442 и выберите инструмент «ВСТАВКА»-«Таблицы»-«Сводная таблица».
Обратите внимание выше на рисунок! Таблицу мы создаем «на существующий лист» именно в ячейку Карта!$B$3. От этого зависит как будут работать формулы в позже созданных именных диапазонах. Упорядочиваем поля сводной таблицы так как показано ниже на рисунке:
Далее создаем тепловую карту. Перейдите в первую ячейку со значениями продаж в сводной таблице C5 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Цветовые шкалы»-«Зеленый, Желтый, Красный»:
После добавления форматирования в правом нижнем углу ячейки появится значок, который указывает нам, для какой области мы хотим применить форматирование. Из его опций мы выбираем последнюю опцию «ко всем ячейкам, содержащим значение Сумма п полю Продажи для Год и Месяц»:
Теперь мы можем дополнительно увеличить детализацию нашей тепловой карты, отображая ее в масштабе 3 цветов. Кроме того, также можно редактировать цвета. Для этого выберите инструмент: «ГЛАНВАЯ»-«Стили»-«Условное форматирование»-«Управление правилами»:
В диспетчере правил условного форматирования нажмите на кнопку «Изменить правило…» и задайте свой стиль оформления тепловой карты. В этом примере были использованы следующие коды трех оттенков красного цвета:
Преобразование формата сводной таблицы в тепловую диаграмму Excel
Теперь нам необходимо настроить внешний вид сводной таблицы и графика с тепловой картой в целом. Сначала щелкните правой кнопкой мышки по сводной таблице и из появившегося контекстного меню выберите опцию «Параметры сводной таблицы»:
В окне параметров сводной таблицы на вкладке: «Макет и формат» следует снять галочку с опции «Автоматически изменять ширину столбцов при обновлении». А на вкладке «Вывод» снимаем все галочки с опций: «Показывать кнопки развертывания и свертывания», «Показывать контекстные всплывающие подсказки», «Показывать названия полей и списки фильтров».
Также увеличиваем ширину строк и уменьшаем ширину столбцов листа со значениями показателей продаж до 40-ка пикселей.
После чего отформатируем стиль внешнего вида таблицы. Сначала перейдите на любую ячейку сводной таблицы и выберите инструмент из дополнительного меню «РАБОТА СО СВОДНЫМИ ТАБЛИЦАМИ»-«КОНСТРУКТОР»-«Стили»-«Темный 10»:
К сожалению, нельзя оставлять пустыми ячейки с надписями: «Сумма по полю Продажи» и «Общий итог», но можно их изменить, например, на пробел « ».
Чтобы удалить значения продаж из ячеек тепловой диаграммы будем использовать форматирование ячеек. Выделите диапазон всех значений в табличной части C5:N14 и нажмите комбинацию горячих клавиш CTRL+1, чтобы вызвать окно «Формат ячеек». В нем на вкладке «Число» из списка опций «Числовые форматы:» выбираем последнюю опцию «(все форматы)». После в поле ввода «Тип:» вводим код для формата ячеек из трех точек с запетыми – ;;; и нажимаем ОК.
Теперь так выглядит тепловая карта активности продаж.
Пришло время создать гистограммы, но они будут динамически изменятся в зависимости от настроек вывода данных сводной таблицы. Поэтому сначала создадим имена диапазонов с формулами, которые будут использовать гистограммы в своих параметрах. Формулы в именах для гистограмм позволят нам динамически подставлять и обновлять новые данные в графиках.
Создание дополнительных динамических графиков предельных уровней
Для создания первого имени с формулой выберите инструмент: «ФОРМУЛЫ»-«Определенные имена»-«Диспетчер имен» (CTRL+F3):
В диспетчере имен нажмите на кнопку «Создать» и в окне для указания параметров создания именного диапазона укажите имя «График1» и введите формулу в поде «Диапазон»:
Для второго имени выполните те же самые действия только задайте имя «График2» и другую формулу:
Сначала создаем График1, для этого выполните целый ряд действий:
Такие же действия следует выполнить и для второго графика учитывая всего лишь 3 исключения:
Готовые первый боковой и второй нижний перевернутые графики:
Создание интерфейса управления тепловой картой в Excel
Создаем элементы управления тепловой диаграммой в Excel. Перейдите на любую ячейку сводной таблицы и выберите инструмент: «ВСТАВКА»-«Фильтры»-«Срез». В появившемся окне «Вставка срезов» отмечаем название поля сводной таблицы «Год» и нажимаем ОК.
Снова создайте срез, на этот раз для поля «Месяц». Щелкните левой кнопкой мышки по срезу «Месяц» чтобы сделать его активным и получить доступ к его дополнительным меню: «ИНСТРУМЕНТЫ ДЛЯ СРЕЗА»-«ПАРАМЕТРЫ»-«Кнопки»-«Столбцы» задайте значение – 12 (так как у нас 12 месяцев в отчете).
С помощью данных срезов мы имеем возможность интерактивно управлять тепловой картой диаграммы управляя полями сводной таблицы как показано ниже на рисунке:
Макросы для управления положением и размерами графиков
Для идеального решения нам необходимо сделать так чтобы столбчатые графики были привязаны к краям сводной таблицы даже при ее изменении. Для решения данной задачи следует использовать VBA-макросы. Сначала создадим функцию teplokarta, которая будет автоматически определять размеры сторон сводной таблицы и под них подгонять размеры графиков.
Внимание! Чтобы получить доступ к графикам следует правильно ссылается на их имена. Чтобы узнать или изменить имя графика щелкните по нему левой кнопкой мышки и в поле имя введите «Диаграмма 1» для первого графика и «Диаграмма 2» – для второго.
Чтобы создать и использовать макрос вызовите режим редактора VBA-кода макросов в Excel. Для этого выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«VisualBasic» или нажмите комбинацию клавиш ALT+F11:
В окне редактора макросов создайте модуль выбрав инструмент: «Insert»-«Module».
В модуль необходимо вставить код функции teplokarta:
Dim karta As Worksheet
Dim grafik1 As ChartObject
Dim grafik2 As ChartObject
Dim svodtab As PivotTable
Dim svodtab_adres As Range
Dim svodtab_lev_verh
Dim svodtab_lev_niz
Dim svodtab_visota
Dim svodtab_shirina
Set karta = Sheets( «Карта» )
Set grafik1 = karta.ChartObjects( «Диаграмма 1» )
Set grafik2 = karta.ChartObjects( «Диаграмма 2» )
Set svodtab = karta.PivotTables( «СводнаяТаблица1» )
Set svodtab_adres = svodtab.TableRange1
svodtab_lev_niz = svodtab_adres.Cells(svodtab_adres.Rows.Count, 2).Address
svodtab_shirina = svodtab.PivotFields( «Месяц» ).DataRange.Address
svodtab_visota = svodtab.PivotFields( «Год» ).DataRange.Address
svodtab_lev_verh = svodtab_adres.Cells(3, svodtab_adres.Columns.Count).Address
With grafik1
.Top = Range(svodtab_lev_verh).Top
.Left = Range(svodtab_lev_verh).Left
.Height = Range(svodtab_visota).Height
.Width = 200
With grafik2
.Top = Range(svodtab_lev_niz).Top
.Left = Range(svodtab_lev_niz).Left
.Width = Range(svodtab_shirina).Width
.Height = 200
End With
End With
Теперь следует сделать двойной щелчок мышки по первому листу «Карта» в редакторе макросов чтобы вставить в него код вызова макрофункции teplokarta:
Код вызова макроса с листа:
Private Sub Worksheet_PivotTableChangeSync( ByVal Target As PivotTable)
Call teplokarta
Range(Range( «A1» ), Range( «A» & Rows.Count). End (xlUp)).RowHeight = 15
End Sub
Сохраните документ Excel в формате с поддержкой макросов *.XLSM и работайте с полноценной интерактивной тепловой картой для анализа данных по продажам за выбранный период в разрезе времени по месяцам и годам.
Как сделать тепловую карту в excel
Использование тепловой карты может помочь вам понять распределение и изменение данных в диапазоне ячеек. В этом руководстве показано, как создать тепловую карту в Excel с условным форматированием в трех случаях.
Создайте тепловую карту для диапазона ячеек в Excel
Чтобы создать тепловую карту для диапазона ячеек в Excel, сделайте следующее.
1. Выберите диапазон с данными, для которых вы установите тепловую карту, нажмите Главная > Условное форматирование > Цветовые шкалы > Желто-зеленый — красная цветовая гамма. Смотрите скриншот:
Затем создается тепловая карта с выбранными данными, как показано на скриншоте ниже.
Вы можете видеть, что выбранные данные выделены разными цветами. Зеленый цвет представляет большие значения, центральный цвет представляет средние значения, а красный цвет представляет меньшие значения.
Ноты:
Создание тепловой карты в сводной таблице Excel
Предположим, вы создали сводную таблицу, как показано на скриншоте ниже. Теперь вы хотите создать тепловую карту для числовых данных в этой сводной таблице, метод такой же, как указано выше.
Теперь тепловая карта создана в сводной таблице.
Создать динамическую тепловую карту в Excel
Во многих случаях вы не хотите всегда отображать тепловую карту на листе. Вы можете установить флажок для управления тепловой картой. Если флажок установлен, отобразить тепловую карту, а когда флажок снят, скрыть тепловую карту. Пожалуйста, сделайте следующее.
1. Выберите весь диапазон данных, нажмите Ctrl + T одновременно.
2. в Создать таблицу диалогового окна, нажмите OK кнопку.
3. Нажмите разработчик > Вставить > Флажок (контроль формы), а затем нарисуйте флажок на листе.
4. Щелкните правой кнопкой мыши флажок и выберите Управление форматом. В Управление форматом диалоговом окне введите адрес ячейки в поле Сотовая ссылка и нажмите OK кнопка. В этом случае я привязываю флажок к ячейке H1, поэтому я ввожу H1 в поле.
5. Выберите числовые ячейки, которые вы будете использовать для создания тепловой карты, нажмите Главная > Условное форматирование > Цветовые шкалы > Другие правила. Смотрите скриншот:
6. в Новое правило форматирования диалоговое окно, настройте следующим образом.
Отныне при установке флажка будет отображаться тепловая карта, в противном случае она будет все время скрыта. См. Демонстрацию ниже:
Статьи по теме
Создайте шаблон диаграммы колоколообразной кривой в Excel Диаграмма колоколообразной кривой, называемая в статистике нормальным распределением вероятностей, обычно предназначена для отображения вероятных событий, а вершина колоколообразной кривой указывает на наиболее вероятное событие. В этой статье я покажу вам, как создать диаграмму колоколообразной кривой с вашими собственными данными и сохранить книгу как шаблон в Excel.
Создание диаграммы спидометра / шкалы в Excel Вы когда-нибудь пробовали создать график спидометра в Excel? В этом руководстве представлены два метода, которые помогут вам создать диаграмму спидометра в Excel.
Создавайте динамические интерактивные диаграммы в Excel В Excel, если вы создали несколько диаграмм на основе ряда данных диапазона и хотите, чтобы диаграммы выглядели красивыми и чистыми. Для этого вы можете создать динамические интерактивные диаграммы на своем листе, когда вы выберете один вариант, соответствующая диаграмма будет отображаться как следующие снимки экрана. Здесь я представлю два типа интерактивных диаграмм: интерактивные диаграммы с использованием раскрывающегося меню и интерактивные диаграммы с использованием кнопок параметров.
Создать воронку в Excel Вы когда-нибудь хотели создать воронкообразную диаграмму, чтобы показать данные в Excel по возрастанию или убыванию? В Excel нет прямого способа создания диаграммы воронки, но сейчас я расскажу об обходном методе создания диаграммы воронки в Excel.
Как создать тепловую карту в Excel — пошаговое руководство
Тепловая карта в Excel — это визуальное представление, которое быстро показывает вам сравнительное представление набора данных.
Например, в наборе данных ниже я могу легко определить, в какие месяцы продажи были низкими (выделены красным) по сравнению с другими месяцами.
В приведенном выше наборе данных цвета назначаются в зависимости от значения в ячейке. Цветовая шкала от зеленого до желтого и красного, при этом высокие значения получают зеленый цвет, а низкие значения — красный цвет.
Создание тепловой карты в Excel
В то время как вы можете создать тепловую карту в Excel, вручную кодируя ячейки цветом. Однако вам придется переделывать её, когда значения изменятся.
Вместо ручной работы вы можете использовать условное форматирование для выделения ячеек на основе значения. Таким образом, если вы измените значения в ячейках, цвет / формат ячейки автоматически обновит тепловую карту на основе предварительно заданных правил условного форматирования.
В этом руководстве вы узнаете, как:
Создание тепловой карты в Excel с использованием условного форматирования
Если у вас есть набор данных в Excel, вы можете вручную выделить точки данных и создать тепловую карту.
Однако это будет статическая тепловая карта, поскольку цвет не изменится при изменении значения в ячейке.
Следовательно, условное форматирование — это правильный путь, поскольку он заставляет цвет в ячейке изменяться, когда вы меняете значение в ней.
Предположим, у вас есть набор данных, как показано ниже:
Вот шаги, чтобы создать тепловую карту с использованием этих данных:
Это даст вам тепловую карту, как показано ниже:
По умолчанию Excel назначает красный цвет наименьшему значению, а зеленый цвет — наибольшему значению, а все остальные значения получают цвет в зависимости от значения. Итак, есть градиент с разными оттенками трех цветов в зависимости от значения.
Теперь, что, если вам не нужен градиент, а вы хотите отображать только красный, желтый и зеленый цвета. Например, вы хотите выделить красным все значения меньше, чем, скажем, 700, независимо от значения. Итак, 500 и 650 имеют одинаковый красный цвет, так как он меньше 700.
Теперь вы получите результат, как показано ниже. Обратите внимание, что все значения ниже 700 имеют одинаковый оттенок красного цвета.
СОВЕТ: хотите отображать только цвета, а не значения в ячейках. Для этого выделите все ячейки и нажмите Control + 1. Откроется диалоговое окно «Формат ячеек». На вкладке Number выберите Custom и введите ;;;; в поле справа.
Предупреждение: условное форматирование — замечательный инструмент, но, к сожалению, оно непостоянно. Это означает, что всякий раз, когда в таблице происходят какие-либо изменения, условное форматирование пересчитывается. Хотя влияние может быть незначительным для небольших наборов данных, оно может привести к медленной работе книги Excel при работе с большими наборами данных.
Создание динамической тепловой карты в Excel
Поскольку условное форматирование зависит от значения в ячейке, как только вы меняете значение, условное форматирование пересчитывается и изменяется.
Это дает возможность составить динамическую тепловую карту.
Давайте рассмотрим два примера создания тепловых карт с помощью интерактивных элементов управления в Excel.
Пример 1: Тепловая карта с использованием полосы прокрутки
Вот пример, где тепловая карта изменяется, как только вы используете полосу прокрутки для изменения года.
Этот тип динамических тепловых карт можно использовать на информационных панелях, где у вас ограниченное пространство, но вы все же хотите, чтобы пользователь имел доступ ко всему набору данных.
Нажмите здесь, чтобы загрузить шаблон тепловой карты
Как создать эту динамическую тепловую карту?
Вот полный набор данных, который используется для создания этой динамической тепловой карты.
Вот шаги:
Кроме того, поскольку условное форматирование непостоянно, как только значение изменяется, оно также обновляется.
Пример 2: Создание динамической тепловой карты в Excel с помощью радиокнопок
Вот еще один пример, в котором вы можете изменить тепловую карту, выбрав переключатель:
В этом примере вы можете выделить 10 верхних / нижних значений на основе выбора переключателя / переключателя.
Нажмите здесь, чтобы загрузить шаблон тепловой карты
Создание тепловой карты в сводной таблице Excel
Условное форматирование в сводных таблицах работает так же, как и с любыми обычными данными.
Но вам нужно знать кое-что важное.
Позвольте мне на примере показать вам.
Предположим, у вас есть сводная таблица, как показано ниже:
Чтобы создать тепловую карту в этой сводной таблице Excel:
Это мгновенно создаст тепловую карту в сводной таблице.
Проблема с этим методом заключается в том, что если вы добавляете новые данные в бэкэнд и обновляете эту сводную таблицу, условное форматирование не будет применяться к новым данным.
Например, когда я добавил новые данные в серверную часть, скорректировал исходные данные и обновил сводную таблицу, вы можете увидеть, что условное форматирование к ней не применяется.
Это происходит, поскольку мы применили условное форматирование только к ячейкам B5: D14.
Если вы хотите, чтобы эта тепловая карта была динамической, чтобы она обновлялась при добавлении новых данных, выполните следующие действия:
Теперь условное форматирование будет обновляться при изменении данных серверной части.
Примечание. Условное форматирование исчезнет, если вы измените поля строки / столбца. Например, если вы удалите поле «Дата» и примените его снова, условное форматирование будет потеряно.