Динамическая карта в 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.
Пример сотворения динамической карты для визуализации данных
Из наружных источников были импортированные данные на рабочий лист «Данные» для следующей их обработки и визуализации:
В данной таблице представлены количественные характеристики каждомесячных продаж 3-х категорий продуктов по 6 материкам мира в протяжении 3-х лет с 2019 по 2021 года.
Подготовка начальных данных
Для удобства зрительной обработки данных отформатируем таблицу различными цветами 3-х групп продуктов, по которым следует создать зрительный сводный анализ с интерактивными способностями:
Дальше возвращаемся на лист «Визуализация» и создаем таблицу с графиком потому что описано в статье:
В итоге обязано получиться так:
Принципиальный момент! В ячейках B1, C1 и D1 мы указываем цвета заливки, в которые будут покрашены материки соответственно популярности продуктов в их. Макрос будет считывать цвета из фонов этих ячеек. Так изготовлено для удобства юзера, который сумеет без помощи других изменять цвета для подсветки соответствующих материков по данным отчета. К примеру, сероватый цвет для товара-2 поменять на голубий либо фиолетовый.
Обработка входящих характеристик статистического отчета
Дальше создаем таблицу, по которой будет обусловятся номер продукта 1-3 более продаваемый на том либо ином материке по любому месяцу. Чтоб вычислить каждое значение для всех ячеек 2-ой таблицы рейтинга категорий продуктов по материкам, мы будем применять сложную формулу массива. В ячейку P2 вводим формулу массива и нажимаем комбинацию кнопок: CTRL+SHIFT+Enter при вводе. Опосля что растягиваем маркером формулу по всему спектру ячеек P2:U37:
На базе приобретенных данных 2-ой таблицы создаем третью таблицу, которая послужит не только лишь легендой, да и источником итоговых данных для функционирования диаграммы в виде интерактивной карты материков мира.
Вычисления для визуализации на динамической карте в Excel
Итоговые значения характеристик популярности продуктов на материках карты у нас рассчитываются в зависимости от обозначенного периода при помощи интерактивных частей управления диаграммой и графиком отчета (2 счетчика Spinner). Популярность продукта в определенный просвет времени рассчитывается при помощи функции МОДА. А полная версия формулы для третьей таблицы смотрится последующим образом:
Как видно в третей таблице также употребляется условное форматирование для подсветки цветами фаворитных продуктов на материках карты.
Сейчас осталось всего только сделать макрос чтоб окрасить соответствующыми цветами материки на интерактивной карте.
Создание динамических конфигураций на карте при помощи макросов VBA
Чтоб сделать макрос в Excel откройте редактор макросов VBA композицией кнопок ALT+F11 либо через меню: «РАЗРАБОТЧИК»-«Код»-«Visual Basic». Там же сделайте модуль через меню: «Insert»-«Module» и вставьте в него последующий код макроса:
Для удобства просто скопируйте этот код в модуль макроса:
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)
Принципиальный момент! Для корректной работы макроса следует переименовать все фигуры материков карты в:
- Северная Америка – Freeform 1.
- Южная Америка – Freeform 2.
- Европа – Freeform 3.
- Азия – Freeform 4.
- Африка – Freeform 5.
- Австралия – Freeform 6.
Нажимаем комбинацию кнопок ALT+F10 и переименовываем как показано ниже на рисунке:
И в конце концов для обоих интерактивных частей типа «Счетчик» (Spinner) присвойте один и этот же макрос «continents»:
В итоге получаем динамически изменяемую карту материков как интерактивную диаграмму с подсветкой цветами категорий продуктов по популярности продаж:
В данном примере приведено обычное решение для сотворения интерактивных карт для визуализации данных отчетов в Excel. Разобравшись со всеми принципами деяния Вы можете таковым же способом создавать свои пользовательские зрительные карты для: государств, регионов, районов, логистических маршрутов, филиалов и т.п.
