Как создать тепловую карту США или ЕС в Excel: скачать

В последних версиях Excel уже имеется готовый стандартный инструмент для создания Heat Map Chart. Для этого достаточно выбрать – Insert → Charts → Maps → Filled Map. Но в нем имеется существенные ограничения: обязательно подключение к сети Интернет для обновления данных, строгая структура таблицы исходных данных и т.д. Стандартными средствами Excel можно создать свой пользовательский Heat Map Chart для US или Европы, а также любой другой страны, региона, города без использования макросов и без ограничений.

Анализ эффективности внешней торговли ЕС с Heat Map Chart в Excel

На любом дашборде всегда найдется место для интерактивного MapChart. Ведь дашборд – это визуальный отчет с главной функцией ориентации при различных ситуациях. А базовые величины любого ориентира это – время и пространство. Целью пользователя дашборда всегда является получения ответов на 2 главных вопроса:

1. Где мы находимся?
2. В каком направлении следует двигаться?

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

Преимущества разработки пользовательского Heat Map Chart в Excel

Представленный в этом примере пользовательский Heat Map Chart позволит вам презентовать данные для анализа нестандартных карт. Презентации внутрикорпоративных стратегий планов охвата покрытия новых территорий с целью масштабирования сферы влияния маркетинга и логистики. А также можно создавать визуальные анализы для карт вымышленных городов из компьютерных игр, мета-вселенных и виртуальных песочниц. В этом шаблоне уже нет никаких ограничений, настраивайте любую карту легко и под свои любые потребности. Heat Map Chart всегда смотрятся презентабельно и гармонично вписываются в любую композицию визуализации данных.

Разберем пример конструирования интерактивного Heat Map Chart в Excel для Соединенных Штатов. Этот же принцип можно будет применять для карты стран Евросоюза и любых других карт территорий, даже вымышленных или из других планет.

Техническое Задание для разработки элемента визуализации данных в Excel

В первую очередь определим Техническое Задания под разработчика визуализации данных в Excel. Ведь паруснику без цели ни один ветер не будет попутным.

Согласно традициям нашего сайта – моделируем ситуацию. Региональная компания работает в 10-ти штатах США по высокому уровню жизни:

1. Коннектикут
2. Калифорния
3. Нью-Йорк
4. Пенсильвания
5. Вашингтон
6. Массачусетс
7. Нью-Гэмпшир
8. Нью-Джерси
9. Орегон
10. Род-Айленд

В каждом из этих штатов находятся отделения компании с разными показателями эффективности продаж. Они изменяются ежемесячно. Необходимо создать интерактивную тепловую карту для экспонирования Топ-3 штата по самых эффективных уровнях продаж в каждом месяце.

Пример создания пользовательского Heat Map Chart в Excel шаг за шагом

Весь процесс занимает 3 этапа, но один из них весьма трудоемкий. В любом случае конечный результат многократно оправдывает вложенные усилия и потраченное время на разработку. С каждой новой картой разработчику будет легче. Потому что с опытом будет расти ваш уровень мастерства, а само определение мастерство – это получение больших результатов с приложением меньших усилий. Другими словами, эффективность вложений ваших усилий зависит от уровня вашего мастерства, который растет пропорционально количеству повторений.

Шаг 1. Подготовка исходных данных

Таблицы с исходными данными выглядят следующем образом:

Диапазон ячеек C15:E24 содержат умную формулу:

Данная формула делает выборку 3-х штатов с наибольшими показателями в текущем месяце из верхней таблицы исходных данных и отмечает их в нижней таблице символом черного квадрата (■ – код символа: Alt+254) – соответственно.

Теперь при изменении номера месяца в ячейке G14 будут автоматически размещаться символы черных квадратов на против соответственных названий штатов в нижней таблице. Например, при выборе 1-го месяца (Январь):

• в диапазоне С15:C24 – Топ-1 (Massachusetts – 113%);
• в диапазоне D15:D24 – штат второго уровня по показателю выполнения плана (Connecticut – 106%);
• в С15:C24 – соответствующий третьему уровню (California – 86%).

У нас сконструирована механика интерактивности путем автоматизации с использованием формул. Далее нам потребуется визуализировать процессы.

Шаг 2. Моделирование векторных фигур для карты

Нам потребуются векторные фигуры всех штатов. Их можно нарисовать самостоятельно, как было продемонстрировано в предыдущих наших примерах, используя инструмент – Insert → Illustrations → Shapes → Freedom Shape:

Или используйте уже готовые фигуры из нашего шаблона, который можно скачать в конце статьи:

Шаг 3. Слияние исходных данных и графических фигур с подключением к автоматизации

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

• Уровень 0 – цвет всех штатов карты США (код #00504A).
• Уровень 3 – цвет для подсветки штатов 3-го уровня (код #00BFAC).
• Уровень 2 – цвет для второго уровня (код #0CFFE8).
• Уровень 1 – самый верхний слой для подсветки штатов Топ-1 по показателям (код #A8FFF6).

Но чтобы автоматизировать подсветку мы не можем просто поместить фигуры на разные слои. Потребуется текстовое поле со ссылкой на соответствующею ячейку штата соответствующего уровня. Например, для подсветки штата California на третьем уровне текстовое поле должно ссылаться на ячейку с черным квадратом E16. Для этого выбираем инструмент – Insert → Text → TextBox:

В TextBox мы не вписываем значение, а просто выделяем его, кликнув по рамке левой кнопкой мышки и сразу же в Formula Bar вводим абсолютную ссылку на ячейку $E$16. В результате при изменении номера месяца (значение ячейки G14) у нас будет в TextBox появляется и скрываться символ черного квадрата.

Теперь нам нужно сделать так чтобы вместо символа квадрата отображалась фигура соответственного штата (California) в соответственном уровню цвета (уровень 3, код #00BFAC). Для этого мы будем использовать в настройках формата текста объекта TextBox заливку картинкой из Clipboard. Но это позже, а перед этим мы подготовим саму картинку из группы фигур и окрасим в соответствующий цвет.

Чтобы картинка на символе черного квадрата как заливка располагалась равномерно без искажений следует изначально соблюдать правильные пропорции ее размеров и добавить фигуру прозрачного квадрата соответственных размеров для соблюдения пропорции. Для этого нам нужно нарисовать квадрат размером 6,05 cm – высота и 3,05 cm – ширина. Это примерно 12 ячеек высотой и 1,5 столбца шириной

Фигуру штата Калифорния помещаем в центр фигуры прямоугольника, но со смещением вниз. А также уменьшаем ее размеры.

Необходимо условно разделить прямоугольник на 4 части по высоте и ширине. Если разместить на листе фигуру прямоугольника так как показано на рисунке (12 ячеек – высота и ширина 1,5 столбца, но по центру целого столбца) в результате нужная нам область это будет первые 3 ячейки вниз от центра фигуры прямоугольника. Для наглядности выше на рисунке показано, как они обведены красной линией – границы диапазона ячеек R8:R10. Фигуру штат Калифорнии при уменьшении следует сохранять пропорцию размеров высоты и ширины, для этого при смещении угловых маркеров следует удерживать клавишу SHIFT. В данном примере размер Калифорнии получился 1,44 cm – высота и 0,84 cm ширина.

После правильного расположения фигур и установки их размеров их следует сгруппировать в одну группу. Для этого выделите кликом левой кнопкой мышки обе фигуры и выберите инструмент в появившемся новом пункте главного меню – Shape Format → Arrange → Group. Или кликните по предварительно выделенным двум фигурам правой кнопкой мышки для вызова контекстного меню, где нужно выбрать опцию Group:

Обязательно! Копируем созданную группу в Clipboard предварительно выделив кликнув по ней левой кнопочкой мышки и нажав комбинацию клавиш CTRL+C.

Теперь возвращаемся к нашему объекту TextBox, выделяем его и вызываем настройки Shape Format нажав комбинацию горячих клавиш CTRL+1 (число 1- следует нажимать на основной клавиатуре, не на бухгалтерской вспомогательной числовой). И выполняем целый ряд настроек:

Сначала выбрав инструмент из появившегося вспомогательного окна после нажатия комбинации CTRL+1 – Format Shape → Text Options → Text Fill → Picture or texture fill → Picture source → Clipboard. Таким образом в объекте TextBox теперь вместо символа черного квадрата отображается маленькая фигура штата Калифорния. Чтобы ее увеличить достаточно лишь повысить размер шрифта, (например до 483 пункта – введите это значение вручную в настройках размера текста – Home → Font.) и выровнять по центру.

Затем удаляем фон и границу в объекте TextBox. Выберите инструмент CTRL+1 – Format Shape → Shape Options → No fill и No Line, чтобы текстовый объект был с прозрачным фоном и свозь него была видна карта и другие фигуры на заднем плане.

Теперь при изменении номера месяца в ячейке G14 будет появляться или исчезать фигура штата Калифорния с цветом 3-го уровня, в соответствии со значением ячейки E16. Наложим объект TextBox на карту. Если размер группы фигур карты равен высоте 11,75 cm и ширине 18,79 cm, то размер шрифта в объекте TextBox с заливкой фигуры штата должен быть 483 пункта. Текст должен быть выровнен по центру. В результате размеры совпадут:

Если соблюдать размеры основной карты, созданной из группы фигур штатов с шириной 11,75 см высоты и 18,79 см ширины, ниже приведена таблица размеров шрифтов для каждого из 10-ти штатов:

Штаты в отчете	Размеры шрифта в пунктах
Connecticut	53
California	483
New York	197
Pennsylvania	140
Washington	175
Massachusetts	110
New Hampshire	117
New Jersey	103
Oregon	228
Rhode Island	29

Наиболее трудоемкий процесс – это подбор правильных размеров шрифтов. Данная таблица существенно сэкономит вам время.

По такому же принципу следует создать все фигуры для подсветки карты с разными цветами по разным уровням. 10 штатов по 3 уровня. В результате всего необходимо создать 30 таких же объектов TextBox с заливками текста фигурами и ссылками на соответствующие ячейки в нижней таблице диапазона C15:E24. У вас должна получиться интерактивная тепловая карта:

По такому же принципу можно создать карту Европы для интерактивного дашборда как важный элемент визуализации данных:

Скачать тепловую карту США и ЕС в Excel

Такие карты можно создавать для любых локализаций. Это могут быть реальные страны, города, районы. Или даже вымышленные карты из компьютерных игр, мета-вселенных. А также планируемые охваты покрытия территорий. Их границы могут быть обусловлены внутрикорпоративными стратегиями, которые нельзя создать стандартными картами. Но данный пример ничем вас не ограничивает. Он позволяет разрабатывать эксклюзивные карты для визуализации данных под конкретные потребности, которые известны только конкретным клиентам. При этом вам не нужно прибегать к использованию макросов. Всю автоматизацию интерактивных функций дашборда получилось реализовать с помощью стандартных формул Excel.