Тепловая карта диаграммы с графиками для анализа продаж в Excel
Обычно для анализа предельного распределения двух переменных значений показателей используется точечный график. Но в данном примере будет использована тепловая карта для анализа активности продаж в Excel относительно заданного года и месяца. Плюс ко всему к тепловой карте будут добавлены две гистограммы, позволяющие более детально проанализировать два измерения с учетом их суммарных значений.
Создание тепловой карты графика с помощью сводной таблице в Excel
Маржинальные гистограммы будут связаны со сводной таблицей, в которой используется так называемые тепловая карта создана условным форматированием. Поэтому для исходных данных на втором листе «Данные» мы создаем сводную таблицу, которую помещаем отдельно на лист «Карта». Сначала выделите диапазон ячеек Данные!$A$1:$C$442 и выберите инструмент «ВСТАВКА»-«Таблицы»-«Сводная таблица».
Обратите внимание выше на рисунок! Таблицу мы создаем «на существующий лист» именно в ячейку Карта!$B$3. От этого зависит как будут работать формулы в позже созданных именных диапазонах. Упорядочиваем поля сводной таблицы так как показано ниже на рисунке:
Далее создаем тепловую карту. Перейдите в первую ячейку со значениями продаж в сводной таблице C5 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Цветовые шкалы»-«Зеленый, Желтый, Красный»:
После добавления форматирования в правом нижнем углу ячейки появится значок, который указывает нам, для какой области мы хотим применить форматирование. Из его опций мы выбираем последнюю опцию «ко всем ячейкам, содержащим значение Сумма п полю Продажи для Год и Месяц»:
Теперь мы можем дополнительно увеличить детализацию нашей тепловой карты, отображая ее в масштабе 3 цветов. Кроме того, также можно редактировать цвета. Для этого выберите инструмент: «ГЛАНВАЯ»-«Стили»-«Условное форматирование»-«Управление правилами»:
В диспетчере правил условного форматирования нажмите на кнопку «Изменить правило…» и задайте свой стиль оформления тепловой карты. В этом примере были использованы следующие коды трех оттенков красного цвета:
- Минимальное значение: Red-240, Green-220, Blue-219.
- Среднее значение: Red-218, Green-150, Blue-148.
- Максимальное значение: Red-192, Green-80, Blue-77.
Преобразование формата сводной таблицы в тепловую диаграмму Excel
Теперь нам необходимо настроить внешний вид сводной таблицы и графика с тепловой картой в целом. Сначала щелкните правой кнопкой мышки по сводной таблице и из появившегося контекстного меню выберите опцию «Параметры сводной таблицы»:
В окне параметров сводной таблицы на вкладке: «Макет и формат» следует снять галочку с опции «Автоматически изменять ширину столбцов при обновлении». А на вкладке «Вывод» снимаем все галочки с опций: «Показывать кнопки развертывания и свертывания», «Показывать контекстные всплывающие подсказки», «Показывать названия полей и списки фильтров».
Также увеличиваем ширину строк и уменьшаем ширину столбцов листа со значениями показателей продаж до 40-ка пикселей.
После чего отформатируем стиль внешнего вида таблицы. Сначала перейдите на любую ячейку сводной таблицы и выберите инструмент из дополнительного меню «РАБОТА СО СВОДНЫМИ ТАБЛИЦАМИ»-«КОНСТРУКТОР»-«Стили»-«Темный 10»:
К сожалению, нельзя оставлять пустыми ячейки с надписями: «Сумма по полю Продажи» и «Общий итог», но можно их изменить, например, на пробел « ».
Чтобы удалить значения продаж из ячеек тепловой диаграммы будем использовать форматирование ячеек. Выделите диапазон всех значений в табличной части C5:N14 и нажмите комбинацию горячих клавиш CTRL+1, чтобы вызвать окно «Формат ячеек». В нем на вкладке «Число» из списка опций «Числовые форматы:» выбираем последнюю опцию «(все форматы)». После в поле ввода «Тип:» вводим код для формата ячеек из трех точек с запетыми – ;;; и нажимаем ОК.
Теперь так выглядит тепловая карта активности продаж.
Пришло время создать гистограммы, но они будут динамически изменятся в зависимости от настроек вывода данных сводной таблицы. Поэтому сначала создадим имена диапазонов с формулами, которые будут использовать гистограммы в своих параметрах. Формулы в именах для гистограмм позволят нам динамически подставлять и обновлять новые данные в графиках.
Создание дополнительных динамических графиков предельных уровней
Для создания первого имени с формулой выберите инструмент: «ФОРМУЛЫ»-«Определенные имена»-«Диспетчер имен» (CTRL+F3):
В диспетчере имен нажмите на кнопку «Создать» и в окне для указания параметров создания именного диапазона укажите имя «График1» и введите формулу в поде «Диапазон»:
Для второго имени выполните те же самые действия только задайте имя «График2» и другую формулу:
Сначала создаем График1, для этого выполните целый ряд действий:
- Перейдите на пустую ячейку в любом месте рабочего листа Excel (по дальше на 2-3 ячейки от сводной таблицы) и выберите инструмент: «ВСТАВКА»-«Диаграммы»-«Вставить гистограмму»-«Гистограмма с группировкой»:
- Несмотря на то что гистограмма пуста нам доступны дополнительные меню для работы с ней. Выберите инструмент из дополнительного меню: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Данные»-«Выбрать данные».
- В окне «Выбор источника данных» в левой секции «Элементы легенды (ряды)» нажмите на кнопку «Добавить» и в появившемся окне «Изменение ряда» правильно укажите ссылку на имя. То есть сначала знак равно, затем название текущего листа Excel, а потом после знака восклицания имя диапазона: =Карта!График1.
- Чтобы перевернуть первый график по горизонтали из дополнительного меню выберите инструмент: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Тип»-«Изменить тип диаграммы» и меняем его на «Линейчатая».
- Теперь сделайте двойной щелчок по оси X и выберите инструмент «Формат оси»-«ПАРАМЕТРЫ ОСИ»-«Цена деления»-«Тысячи».
- Теперь сделайте двойной щелчок по любому столбцу и выберите инструмент: «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«Боковой зазор»-5%.
- Из дополнительного меню выберите инструмент: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Макеты диаграмм»-«Добавить элемент диаграммы»-«Подписи данных»-«У края, внутри». Затем сделайте двойной щелчок по любой подписи данных и выберите инструмент: «Формат подписей данных»-«ПАРАМЕТРЫ ПОДПИСЕЙ»-«ЧИСЛО»-«Категория»-«Числовой» и задайте «Число десятичных знаков» — 0, а после отметьте галочкой опцию «Разделять группы разрядов».
- Выделив одним щелчком левой кнопкой мышки название диаграммы и нажав клавишу «Delete» на клавиатуре удалите: название диаграммы, область построения, оси X и Y. И разместите гистограмму у правого края табличной части сводной таблицы так как показано ниже на рисунке:
Такие же действия следует выполнить и для второго графика учитывая всего лишь 3 исключения:
- В 3-ем пункте указываем ссылку на второе имя =Карта!График2.
- Не используем 4-й пункт чтобы не менять тип гистограммы.
- В 5-ом пункте делаем такую же настройку для оси Y плюс отмечаем галочкой опцию «обратный порядок значений», чтобы перевернуть график – вертикально.
Готовые первый боковой и второй нижний перевернутые графики:
Создание интерфейса управления тепловой картой в 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 и работайте с полноценной интерактивной тепловой картой для анализа данных по продажам за выбранный период в разрезе времени по месяцам и годам.
Как сделать тепловую карту цен для недвижимости
Считается, что Data Mining — это магическое снадобье из SQL, Python, Power BI и других волшебных компонент. Мало кто знает, что при правильном подходе с Data Mining может совладать офисный планктон с помощью одного лишь Excel.
Если вы абсолютно далеки от Data Mining, но хотите причаститься его таинств, это руководство в картинках по шагам сделано для вас. Особенно полезно тем, кто никогда бы даже не подумал сделать подобное самостоятельно.
В качестве практического вопроса будем рассматривать визуализацию данных из объявлений на популярных сайтах продажи квартир. Визуальный анализ — основа основ Data Mining, а при отсутствии специальных знаний — и вовсе единственный способ для понимания смысла, содержащегося в большом количестве данных. Это настолько фундаментальный навык, что ему посвящена целая народная мудрость:
Лучше один раз увидеть, чем сто раз услышать
Тепловая карта (heat map) – обозначение какого-либо показателя цветом:
Как правило, более высокие значения обозначаются красными оттенками, более низкие – синими. Обычная цветовая шкала выглядит так:
Географическая тепловая карта – обозначение показателя цветом на географической карте. Более высокие значения температуры показаны более красными оттенками в привязке к географическим точкам:
Географическая тепловая карта цен – обозначение цветом цен в разных географических местах.
В нашем случае это будут цены на квартиры.
Цены на квартиры будем брать с общеизвестных досок объявлений А и Ц. Для сбора объявлений без программирования нужно воспользоваться готовым парсером. В данном случае выберем наиболее доступный по причине его бесплатности и наиболее удобный из-за простоты установки в три клика в Excel.
Парсеру надо дать понять какие объявления нужно скачивать. Для этого используется ссылка на доску объявлений.
Для подготовки ссылки для скачивания объявлений с доски объявления А открываем браузер, в браузере открываем сайт доски объявлений, выбираем регион (для примера → Брянск) и раздел → квартиры. В адресном поле браузера получаем ссылку: https://www.avito.ru/bryanskaya\_oblast/kvartiry. В последней части ссылки видим раздел → kvartiry, перед ней расположен регион → bryanskaya_oblast. Вместо Брянска можно указать свой регион, а вместо раздела квартир можно указать дома-дачи-коттеджи или земельные-участки. Также можно использовать фильтры (например новостройки или вторичка, количество комнат) и они отобразятся в составе ссылки. Скажем спасибо доске объявлений А за такой понятный порядок формирования ссылок.
Для подготовки ссылки с доски объявлений Ц придется сделать дополнительный шаг: после выбора региона, раздела, фильтров и нажатия кнопки «Найти» нужная ссылка еще не будет готова. Для завершения подготовки ссылки нужно перейти на вторую страницу списка объявлений. После этого ссылка в адресной строке браузера примет вид https:// cian.ru/cat.php?deal_type=sale&engine_version=2&offer_type=flat&p=2®ion=4562&room1=1&room2=1. Раздел квартир здесь будет в offer_type=flat, а регион – в region=4562. Скажем «фу» доске объявлений Ц за не самый удобный порядок формирования ссылок.
Готовые ссылки как есть копируем из адресной строки браузера (нажатием кнопок Ctrl+A и Ctrl+C) и вставляем в парсере нажатием кнопки Добавить ссылку. Для обеих ссылок можно указать один и тот же новый файл Excel, в который будут сохраняться объявления.
Чтобы код для парсинга доски объявлений А загрузился в Excel → в настройках парсера (расположены в Excel на вкладке Надстройки) ставим галочку у парсера доски объявлений А и выключаем галочки у сохранения фотографий из объявлений, у сохранения копии объявлений, у открывания номера телефона и у других ненужных опций. То же самое повторяем с настройками парсера доски объявлений Ц.
Теперь ссылки полностью готовы для загрузки объявлений. Нажимаем в меню парсера кнопку Старт и ждем около 20 секунд до загрузки первого объявления. Да, процесс совсем не быстрый и займет время. Можно уменьшить интервал запросов в настройках парсера до 10 или 5 секунд и иногда это даже прокатывает. Но обычно доски объявлений очень не любят ботов и сразу закрывают доступ к данным (бан). Конечно, эти ограничения можно обойти и загружать данные в 100 раз быстрее, но это дороже.
Тепловая карта в Excel — Как создать тепловую карту в Excel?
Тепловая карта в Excel используется для отображения данных в разных цветовых схемах. Это помогает в оценке того, достигнут ли целевой уровень данных или нет. На Тепловой карте данные отображаются другим цветным рисунком, чтобы мы могли видеть, какая точка данных находится ниже предела, а какая — выше предела. Мы можем использовать два или более двух цветов, чтобы увидеть шаблон данных.
Как создать тепловую карту в Excel?
Вместо ручной работы условное форматирование может использоваться для выделения ячеек на основе значений. Если вы измените значения в ячейках, цвет или формат ячейки автоматически обновит тепловую карту в условном форматировании на основе предопределенных правил.
Давайте разберемся, как создать тепловую карту в Excel, используя несколько примеров.
Вы можете скачать этот шаблон Тепловой карты Excel здесь — Шаблон тепловой карты Excel
Пример № 1
Здесь мы получили данные о продажах некоторых номеров деталей. Где у нас есть цифры продаж в столбцах G и H в течение двух месяцев. Теперь мы хотим применить тепловую карту в данных, показанных ниже.
Для этого сначала выберите столбцы G и H. Теперь перейдите в Главное меню и выберите Условное форматирование, как показано ниже.
Как только мы это сделаем, мы получим выпадающий список меню, как показано ниже. Теперь из цветовых шкал выберите любой цветовой узор. Здесь мы выбрали второй вариант — красный, желтый, зеленый, как показано ниже.
Как только мы это сделаем, мы увидим тепловую схему для всех выбранных цветовых шкал, как показано ниже. Это показывает, что значение, которое меньше 50, находится в оттенках зеленого цвета. Среднее значение, близкое к 50, находится в желтых тонах, а значение, близкое к 100, — в красных.
Значения, которые ближе к пределу, имеют темные оттенки, а значения, которые далеко от предела, имеют светлые оттенки. Давайте посмотрим на небольшой фрагмент данных и измерим другое значение цветовой шкалы, как показано ниже.
Так вот, как можно сделать цветовое масштабирование для Карты Тепла в любых данных. Мы можем выбрать различное цветовое масштабирование, а также в соответствии с нашим выбором цвета.
Пример № 2
Существует другой метод создания тепловой карты в Excel. Это продвинутый метод создания тепловых карт с некоторыми дополнительными функциями. Для этого выберите столбцы G и H, следуйте по тому же пути, который обсуждался в приведенном выше примере.
- Зайдите в Главное меню, нажмите на Условное форматирование . Как только мы это сделаем, мы получим выпадающий список.
- Затем нажмите « Цветовые шкалы» в боковом списке и выберите « Дополнительные правила», как показано на скриншоте ниже.
Это перенесет нас к расширенному варианту Правил форматирования для Тепловых карт через Условное форматирование. Откроется окно окна « Новое правило форматирования», как показано ниже.
Мы можем создать новое правило или продолжить работу с существующими правилами. Для выбранного набора данных давайте создадим новое правило для тепловой карты. Как мы видим, в поле Edit the Rule Description есть вкладка Format Style, доступная с выпадающей кнопкой. Выберите 3-х цветную шкалу, как показано ниже.
Как только мы это сделаем, мы увидим 3 различных цветовых шкалы с диапазоном значений, которые будут отображаться, как показано ниже. Здесь все 3 цвета имеют значение по умолчанию.
Теперь для полного диапазона, который является минимальным, средним и максимальным, выберите диапазон, нажав на иконку 
или мы можем просто написать число, с которого нам нужно увидеть цветовую шкалу Тепловой карты. Как мы можем видеть ниже, здесь мы выбрали 0 в качестве минимального диапазона процентилей, 50 в качестве среднего процентиля и 100 в качестве максимального процентиля. И нажмите на ОК.
Примечание . Мы можем изменить цвет на вкладке «Цвет» ниже и проверить, как будет выглядеть масштабирование цвета.
Как только мы нажмем OK, мы получим тепловую карту, созданную для выбранного набора данных, как показано ниже.
Как мы видим выше, с помощью выбранной цветовой шкалы мы создали тепловую карту, которая показывает более высокие данные, чем больше темный будет красного цвета и чем ниже данные, тем больше света будет зеленого цвета. И данные Midpoint также показывают изменение цвета относительно определенного или выбранного диапазона значений. Здесь для выбранного значения Midpoint 50 данные меняют цвет на и обратно.
Плюсы Тепловой Карты в Excel
- Для огромного набора данных, где применение фильтра не даст реальной картины изменений данных. Там, если мы создадим Тепловую Карту, тогда мы сможем увидеть изменение стоимости без применения фильтров.
- Для данных, в которых нам нужно увидеть площадь (Регион, Страна, Город и т. Д.), Необходимо внести разумные изменения, в этом случае использование Heat Map дает высокие и безопасные точки в выбранной цветовой шкале.
- Всегда рекомендуется использовать тепловую карту, когда размер данных огромен, а структура данных колеблется в зависимости от определенных точек.
Недостатки тепловой карты в Excel
- Не рекомендуется длительное время применять какие-либо функции условного форматирования в данных. Потому что это делает работу Excel медленной, пока мы используем фильтр для сортировки данных.
Что нужно помнить о тепловой карте в Excel
- Всегда очищайте правило условного форматирования, как только оно будет использовано. Или сохраните правило, чтобы его можно было использовать позже.
- Выбирайте те цветовые шкалы только по рисунку тепла, который можно легко показать и измерить.
- Измерьте диапазон перед выбором и зафиксируйте точки масштабирования, чтобы цвета и точки данных можно было фиксировать выше и ниже этих значений.
Рекомендуемые статьи
Это было руководство по тепловой карте в Excel. Здесь мы обсудили, как создать Heat Map вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —
Расширенные таблицы Excel – Тепловая карта
Тепловая карта обычно используется для обозначения цветного различия областей в двумерном массиве, причем каждый цвет связан с различными характеристиками, общими для каждой области.
В Excel тепловую карту можно применить к диапазону ячеек на основе значений, которые они содержат, используя цвета ячеек и / или цвета шрифта. Условное форматирование Excel пригодится для этой цели.
Что такое тепловая карта?
Тепловая карта – это визуальное представление данных в таблице для выделения значимых точек данных. Например, если у вас есть месячные данные о продажах продуктов за последний год, вы можете прогнозировать, в какие месяцы продажи продукта были высокими или низкими.
Тепловая карта выглядит так, как показано ниже.
Преимущества тепловых карт
Тепловая карта может использоваться для визуального отображения различных диапазонов данных с разными цветами. Это очень полезно, когда у вас большие наборы данных и вы хотите быстро визуализировать определенные черты в данных.
Тепловые карты используются для –
- Выделите верхние и нижние несколько значений.
- Изобразите тренд в значениях с помощью цветовых оттенков.
- Определите пустые ячейки – скажем, в листе ответов или в анкете.
- Выделите диапазоны качества продукции.
- Выделите цифры в цепочке поставок.
- Выделите отрицательные значения.
- Выделите нулевые значения.
- Выделите выбросы, определенные порогами.
Подготовка данных
Расположите данные в таблице.
Как видите, данные за финансовый год, апрель – март, помесячно для каждого продукта. Вы можете создать тепловую карту, чтобы быстро определить, в какие месяцы продажи были высокими или низкими.
Создание тепловой карты
Ниже приведены шаги для создания тепловой карты –
Шаг 1 – Выберите данные.
Шаг 2 – Нажмите Условное форматирование на ленте. Нажмите «Управление правилами» и добавьте правила, как показано ниже.
Верхние пять значений окрашены зеленым цветом (заливка), а нижние пять значений окрашены красным (заливка).
Создание тепловой карты без отображения значений
Иногда зрителям может быть просто интересна информация, и цифры могут не понадобиться. В таком случае вы можете сделать немного форматирования следующим образом:
Шаг 1 – Выберите данные и выберите цвет шрифта как белый.
Как видите, цифры не видны. Далее вам нужно выделить пять верхних и нижние пять значений без отображения чисел.
Шаг 2 – Выберите данные (которые, конечно, не видны).
Шаг 3 – Примените условное форматирование так, чтобы верхние пять значений были окрашены зеленым (как заливка, так и шрифт), а нижние пять значений – красным (как заливка, так и шрифт).
Шаг 4 – Нажмите кнопку Применить.
Это дает быструю визуализацию высоких и низких продаж в течение года и по всем продуктам. Поскольку вы выбрали один и тот же цвет для заливки и шрифта, значения не отображаются.