Динамическая карта в Excel из фигур и макросов VBA скачать

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

Как построить динамическую карту из фигур в Excel

Чтобы реализовать данную задачу в Excel стандартными средствами существует несколько методов, но все они требуют подключения дополнительных модулей:

• надстроек (Power Map, Power View);
• компонентов (Bing Maps);
• сторонних приложений (MapCite, Esri Maps, MapLand).

Более того некоторые из них платные. А главный недостаток – это ограниченность при обмене информацией. То есть чтобы открыть файл с визуальным отчетом на динамической карте необходимо подключение дополнительных модулей для исправной работы в процессе визуального анализа.

Создадим свою простую динамическую карту с помощью фигур из векторной графики. Данная диаграмма будет работать на любых версиях Excel без необходимости подключения внешних модулей и надстроек.

Как рисовать в Excel сложные фигуры из векторной графики

В программе Excel можно создавать сложные фигуры векторной графики. Еще больше возможностей для создания сложных фигур предоставляет программа Power Point, но они все еще ограниченные. Сложную фигуру, созданную в программе Power Point, можно легко скопировать через буфер обмена и вставить в Excel для дальнейшего использования. Но чтобы нарисовать такую сложную фигуру как «карта мира» средствами Power Point – на это уйдет очень много нерационально потраченного времени, не смотря на то что теоретически это возможно. Хотя и микроскопом гвозди забивать, также теоретически возможно. Существуют более эффективные решения для данной задачи – это импорт векторной графики в Excel.

Например, можно быстро нарисовать векторную карту мира в специальных графических редакторах, предназначенных для векторной графики такие как Adobe Illustrator или CorelDRAW и др. Более того можно на просторах интернета бесплатно скачать уже готовые файлы векторного формата с нарисованной картой мира. Самый популярный формат векторной графики – это *.SVG. Он представляет из семя обычный текстовый XML файл который по правилам разметки описывает все кривые созданной фигуры по примитивам.

Несмотря на это программа Excel на сегодняшний день не импортирует такие, казалось бы, весьма близкие по духу XML форматы рисунков фигур в векторной графике. В то время как программные продукты из других офисных пакетов LibreOffice аналогично работающие с электронными таблицами LibreOffice Calc прекрасно поддерживают импорт файлов в формате SVG – самого популярного типа для хранения и обмена векторной графикой по стандарту XML. Более того в программе из данного пакета LibreOffice Impress предоставляются более широкие возможности редактирования фигур, импортированных из SVG файлов: преобразование в кривые, разбивка, разделение и др.

Но в Excel не все так печально с векторной графикой. Очень радует тот факт, что программа Excel прекрасно поддерживает импорт файлов векторной графики в формате EPS (Encapsulated PostScript). Данный формат был разработан в компании Adobe для обмена графическими данными между программами. Убедимся в этом на практике импортируя векторную графику в Excel из файла continents.eps, который был предварительно создан в редакторе Adobe Illustrator.

Создайте новый рабочий лист с названием «Визуализация» и выберите инструмент: «ВСТАВКА»-«Иллюстрации»-«Рисунки» и укажите путь к файлу continents.eps после чего нажмите на кнопку «Вставить». В результате у нас появится пока-что не векторная фигура, а только лишь рисунок, но из векторной графики:

Нам всего лишь нужно щелкнуть правой кнопкой мишки по вставленном рисунку и если он из векторной графики, то будет активна и доступна опция «Группировать»-«Разгруппировать» в появившемся контекстном меню. В результате мы получим следующее предложение: «Это импортированный рисунок, а не группа. Преобразовать его в рисунок Microsoft Office?».

Жмем на кнопку «Да» а потом комбинацию клавиш ALT+F10, чтобы убедиться в желаемом результате:

Я Вас поздравляю! Теперь мы можем работать с векторным рисунком как с обычными фигурами в Excel. Только представьте себе какие теперь отрылись новые широкие возможности в областях визуализации данных и создания сложной динамической инфографики в Excel.

Пример создания динамической карты для визуализации данных

Из внешних источников были импортированные данные на рабочий лист «Данные» для последующей их обработки и визуализации:

В данной таблице представлены количественные показатели ежемесячных продаж трех категорий товаров по шести континентам мира на протяжении трех лет с 2019 по 2021 года.

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

Для удобства визуальной обработки данных отформатируем таблицу разными цветами трех групп товаров, по которым следует сделать визуальный сводный анализ с интерактивными возможностями:

Далее возвращаемся на лист «Визуализация» и создаем таблицу с графиком так как описано в статье:

В результате должно получиться так:

Важный момент! В ячейках B1, C1 и D1 мы указываем цвета заливки, в которые будут окрашены континенты соответственно популярности товаров в них. Макрос будет считывать цвета из фонов этих ячеек. Так сделано для удобства пользователя, который сможет самостоятельно изменять цвета для подсветки соответственных континентов по данным отчета. Например, серый цвет для товара-2 заменить на синий или фиолетовый.

Обработка входящих показателей статистического отчета

Далее создаем таблицу, по которой будет определятся номер товара 1-3 наиболее продаваемый на том или ином континенте по каждому месяцу. Чтобы вычислить каждое значение для всех ячеек второй таблицы рейтинга категорий товаров по континентам, мы будем использовать сложную формулу массива. В ячейку P2 вводим формулу массива и жмем комбинацию клавиш: CTRL+SHIFT+Enter при вводе. После чего растягиваем маркером формулу по всему диапазону ячеек P2:U37:

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

Вычисления для визуализации на динамической карте в Excel

Итоговые значения показателей популярности товаров на континентах карты у нас вычисляются в зависимости от указанного периода с помощью интерактивных элементов управления диаграммой и графиком отчета (2 счетчика Spinner). Популярность товара в определенный промежуток времени вычисляется с помощью функции МОДА. А полная версия формулы для третьей таблицы выглядит следующим образом:

Как видно в третей таблице также используется условное форматирование для подсветки цветами популярных товаров на континентах карты.

Теперь осталось всего лишь создать макрос чтобы окрасить соответственными цветами континенты на интерактивной карте.

Создание динамических изменений на карте с помощью макросов VBA

Чтобы создать макрос в Excel откройте редактор макросов VBA комбинацией клавиш ALT+F11 или через меню: «РАЗРАБОТЧИК»-«Код»-«Visual Basic». Там же создайте модуль через меню: «Insert»-«Module» и вставьте в него следующий код макроса:

Для удобства просто скопируйте этот код в модуль макроса:

Sub continents()
 
Dim list As Worksheet
Dim NorthAmerica As Shape
Dim SouthAmerica As Shape
Dim Europe As Shape
Dim Asia As Shape
Dim Africa As Shape
Dim Australia As Shape
Dim tovar1 As Long
Dim tovar2 As Long
Dim tovar3 As Long
tovar1 = Range("B1").Interior.Color
tovar2 = Range("C1").Interior.Color
tovar3 = Range("D1").Interior.Color
 
Dim a() As Variant
a = Array(tovar1, tovar2, tovar3)
 
Set list = Sheets("Визуализация")
Set NorthAmerica = list.Shapes("Freeform 1")
Set SouthAmerica = list.Shapes("Freeform 2")
Set Europe = list.Shapes("Freeform 3")
Set Asia = list.Shapes("Freeform 4")
Set Africa = list.Shapes("Freeform 5")
Set Australia = list.Shapes("Freeform 6")
 
NorthAmerica.Fill.ForeColor.RGB = a(Range("I39") - 1)
SouthAmerica.Fill.ForeColor.RGB = a(Range("J39") - 1)
Europe.Fill.ForeColor.RGB = a(Range("K39") - 1)
Asia.Fill.ForeColor.RGB = a(Range("L39") - 1)
Africa.Fill.ForeColor.RGB = a(Range("M39") - 1)
Australia.Fill.ForeColor.RGB = a(Range("N39") - 1)
 
End Sub

Важный момент! Для корректной работы макроса следует переименовать все фигуры континентов карты в:

1. Северная Америка – Freeform 1.
2. Южная Америка – Freeform 2.
3. Европа – Freeform 3.
4. Азия – Freeform 4.
5. Африка – Freeform 5.
6. Австралия – Freeform 6.

Жмем комбинацию клавиш ALT+F10 и переименовываем как показано ниже на рисунке:

И наконец для обоих интерактивных элементов типа «Счетчик» (Spinner) присвойте один и тот же макрос «continents»:

В результате получаем динамически изменяемую карту континентов как интерактивную диаграмму с подсветкой цветами категорий товаров по популярности продаж:

Скачать динамическую карту из фигур в Excel

В данном примере приведено простое решение для создания интерактивных карт для визуализации данных отчетов в Excel. Разобравшись со всеми принципами действия Вы сможете таким же методом создавать свои пользовательские визуальные карты для: стран, регионов, районов, логистических маршрутов, филиалов и т.п.