зміст
Якщо ваша компанія має філії по країні або здійснює продажі не тільки в межах МКАД, то рано чи пізно перед вами постане завдання візуального відображення числових даних з Microsoft Excel (продажі, заявки, обсяги, клієнти) на географічній карті. з прив'язкою до конкретних міст і регіонів. Давайте коротко розглянемо основні способи візуалізації геоданих, які існують в Excel.
Спосіб 1: швидко та безкоштовно – компонент Bing Maps
Починаючи з версії 2013 року, Excel має вбудований магазин додатків, тобто з’явилася можливість купувати, завантажувати та встановлювати додаткові модулі та надбудови з відсутніми функціями. Один із цих компонентів якраз дозволяє візуально відображати числові дані на карті – він називається Bing Maps і, що особливо приємно, є абсолютно безкоштовним. Щоб встановити його, відкрийте вкладку Вставити – Магазин (Вставити – Офісні програми):
Після вставки компонента на аркуші повинен з'явитися динамічний контейнер з картою. Для візуалізації вашої інформації на карті необхідно вибрати діапазон із геоданими та натиснути кнопку Показати місцезнаходження:
При необхідності в налаштуваннях (значок шестерінки у верхньому правому куті компонента) ви можете змінити кольори та тип відображуваних діаграм:
Також є можливість швидко фільтрувати міста, відображаючи лише ті, які вам потрібні (іконка воронки у верхньому правому куті компонента).
Можна легко прив'язати не тільки до міст, а й до інших об'єктів: регіонів (наприклад, Тульська область), автономні області (напр. Ямало-Ненецька) і республіки (Татарстан) — тоді в центрі області буде відображено коло діаграми. Головне, щоб назва в таблиці відповідала підписам на карті.
Всього в плюси цього методу: проста безкоштовна реалізація, автоматична прив'язка до карти, два типи діаграм, зручна фільтрація.
В мінуси: потрібен Excel 2013 з доступом в Інтернет, не можна вибирати області та райони.
Спосіб 2: гнучкий і красивий – перегляд карти у звітах Power View
Деякі версії Microsoft Excel 2013 постачаються з потужною надбудовою візуалізації звітів під назвою Power View, яка дозволяє (між іншим, і вона може багато чого!) візуально відображати дані на карті. Щоб активувати надбудову, відкрийте вкладку розробник (розробник) і натисніть кнопку Надбудови COM (Додатки COM). У вікні, що відкриється, поставте прапорець біля Power View і натисніть OK. Після всіх цих маніпуляцій на вкладці Insert (Вставити) у вас повинна бути кнопка енергетичний погляд.
Тепер ви можете вибрати діапазон з вихідними даними, натисніть на цю кнопку – у вашій книзі буде створено новий аркуш (більше схожий на слайд з Power Point), де вибрані дані будуть відображені у вигляді таблиці:
Ви можете легко перетворити таблицю на географічну карту за допомогою кнопки Картка (карта) таб Конструктор (Дизайн):
Зверніть особливу увагу на праву панель Поля Power View – на ній, на відміну від примітивних Bing Maps, перетягуючи назви стовпців (полів) з вихідної таблиці мишкою і опускаючи їх в різні області, можна дуже гнучко налаштовувати результуюче геопредставлення:
- До області Місце проживання (місця) необхідно викинути стовпець з вихідної таблиці, що містить географічні назви.
- Якщо у вас немає колонки з назвою, але є колонки з координатами, то їх потрібно розмістити в області довгота (Довгота) и широта (широта), відповідно.
- Якщо в області Колір (Колір) скиньте товари, тоді кожна бульбашка буде, окрім розміру (що відображає загальний прибуток у місті), деталізована на частини за товарами.
- Додавання поля до області вертикальний or Горизонтальні множники (Роздільники) розділить одну картку на кілька за цим полем (у нашому прикладі на чверті).
Також на контекстній вкладці, яка з’являється вгорі макет (Макет) Ви можете налаштувати фон карти (колір, ч/б, контур, вигляд із супутника), мітки, заголовки, легенду тощо.
Якщо даних багато, то на вкладці енергетичний погляд можна ввімкнути спец Область фільтра (Фільтри), де за допомогою звичайних прапорців ви можете вибрати, які міста або товари ви хочете показати на карті:
Усього: в плюсах: простота використання та гнучкість налаштування, можливість розділити одну картку на кілька категорій.
У мінусах: Power View доступний не в усіх конфігураціях Excel 2013, немає інших типів діаграм, окрім бульбашкових і секторних діаграм.
Спосіб 3: Дорогий і професійний – надбудова Power Map
Це окреме COM-доповнення для найважчих випадків, коли потрібна складна, професійна, анімована візуалізація великої кількості даних на будь-якій (навіть кастомній карті) та з відео динаміки процесу в часі. . На етапі розробки вона мала робочу назву GeoFlow, а пізніше була перейменована в Power Map. На жаль, повна версія цієї надбудови доступна лише для покупців повної версії Microsoft Office 2013 Pro або корпоративних передплатників Office 365 із планом Business Intelligence (BI). Однак товариші з Microsoft дають превью цього доповнення для скачування «пограти» безкоштовно, за що їм спасибі.
Посилання для завантаження Power Map Preview із центру завантажень Microsoft (12 Mb)
Після скачування та встановлення надбудови необхідно підключити її на вкладці Розробник – надбудови COM (Розробник — надбудови COM) подібно до Power View з попереднього абзацу. Після цього на табл Insert повинна з’явитися кнопка Картка (карта). Якщо ми тепер виберемо таблицю з вихідними даними:
… і натисніть кнопку Карта, після чого ми потрапимо в окреме вікно надбудови Microsoft Power Map:
Якщо не вдаватися в подробиці (яких достатньо для окремого навчання на півдня), то загальні принципи роботи з картою такі ж, як і в описаному вище Power View:
- Розмір стовпців визначається стовпцем вихідної таблиці (Доходи), яку ми викинемо в поле висота на правій панелі. Принцип підрахунку, як і у зведених таблицях, можна змінити у випадаючому списку полів:
- Для деталізації кожної колонки продажів по окремих товарах необхідно заповнити поле Product до області Категорія (Категорія).
- Ви можете використовувати різні типи діаграм (гістограми, бульбашки, теплові карти, заповнені області) за допомогою кнопок на правій панелі:
- Якщо у вихідних даних є стовпець з датами продажу, то його можна кинути в область Time (Час) – тоді внизу з’явиться вісь часу, по якій можна перенестися в минуле-майбутнє і побачити процес у динаміці.
«Вау-момент» надбудови Power Map можна назвати надзвичайною простотою створення анімованих відеооглядів на основі зроблених карт. Досить зробити кілька копій поточної сцени під різними кутами огляду та в різних масштабах – і надбудова автоматично створить 3D-анімацію польоту навколо вашої карти, фокусуючись на вибраних місцях. Потім отримане відео легко зберігається у форматі mp4 як окремий файл для вставки, наприклад, на слайд Power Point.
Спосіб 4. Бульбашкова діаграма з «уточненням файлу»
Самий «колгоспний» метод з усіх перерахованих, але працює у всіх версіях Excel. Побудуйте бульбашкову діаграму (Bubble Chart), вимкніть її осі, сітку, легенду… тобто все, крім бульбашок. Потім вручну відрегулюйте положення бульбашок, розмістивши під діаграмою завантажене раніше зображення потрібної карти:
мінуси цього методу очевидні: довго, нудно, багато ручної роботи. Причому вихід підписів для бульбашок - окрема проблема, коли їх багато.
профі тим, що цей параметр працюватиме в будь-якій версії Excel, на відміну від наведених нижче методів, де потрібен Excel 2013. І не потрібне підключення до Інтернету.
Спосіб 5: сторонні програми та доповнення
Раніше існувало кілька надбудов і плагінів для Excel, які дозволяли з різним ступенем зручності і краси реалізувати відображення даних на карті. Зараз переважна більшість із них або залишені розробниками, або перебувають у стадії тихого відмирання – з Power Map складно конкурувати 🙂
З тих, хто вижив, варто згадати:
- MapCite – можливо, найпотужніший з усіх. Можливість прикріплення до карти за назвами населених пунктів, областей, районів та координатами. Відображає дані як точки або теплову карту. Використовує Bing Maps як основу. Автоматично знає, як перекинути створену карту в презентацію Power Point. Безкоштовна пробна версія доступна для завантаження, повна версія коштує 99 доларів на рік.
- Карти Esri – доповнення від Esri, яке також дозволяє завантажувати та аналізувати геодані з Excel на картах. Багато налаштувань, різні типи діаграм, підтримка. Є безкоштовна демо-версія. Для повної версії потрібна підписка на картографічний сервіс ArcGis.
- MapLand– одна з перших надбудов на цю тему, створена для Excel 97-2003. У комплект входить набір карт у вигляді графічних примітивів, до яких прикріплюються дані з аркуша. Необхідно придбати додаткові картки. Демо-версія для різних версій Excel доступна для завантаження, версія Pro коштує 299 доларів.