Визуализация геоданных на карте

102620 02.05.2015 Скачать пример

Если ваша компания имеет филиалы по стране или продает не только в пределах МКАД, то рано или поздно вы столкнетесь с задачей наглядно отобразить числовые данные из Microsoft Excel (продажи, заявки, объемы, клиентов) на географической карте с привязкой к конкретным городам и регионам. Давайте вкратце пробежимся по основным способам визуализации геоданных, которые существуют в Excel.

Способ 1. Быстро и бесплатно - компонент Bing Maps

Начиная с 2013 версии в Excel встроен магазин приложений, т.е. появилась возможность докупать, скачивать и устанавливать дополнительные модули и надстройки с недостающими функциями. Один из таких компонентов как раз и позволяет наглядно отображать числовые данные на карте - он называется Bing Maps и, что особенно приятно, абсолютно бесплатен. Для его установки откройте вкладку Вставка - Магазин (Insert - Office Apps):

geo2.png

После вставки компонента на листе должен появится динамический контейнер с картой. Для визуализации на карте вашей информации нужно выделить диапазон с геоданными и нажать кнопку Показать местоположения (Show Locations):

визуализация геоданных на картах Bing

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

geo4.png

Также возможно быстро отфильтровать города, отобразив только нужные (значок воронки в правом верхнем углу компонента).

Спокойно можно привязываться не только к городам, но и к другим объектам: областям (например, Тульская область), автономным округам (например, Ямало-Ненецкий АО) и республикам (Татарстан) - тогда кругляш диаграммы будет отображен в центре области. Главное, чтобы название в таблице совпадали с подписями на карте.

Итого в плюсах этого способа: легкая бесплатная реализация, автоматическая привязка к карте, два типа диаграмм, удобная фильтрация.

В минусах: нужен Excel 2013 с доступом в интернет, нельзя выделять области и районы.

Способ 2. Гибко и красиво - режим карты в отчетах Power View

Некоторые версии Microsoft Excel 2013 идут в комплекте с мощной надстройкой визуализации отчетов, которая называется Power View и позволяет (помимо всего прочего, а умеет она много!) наглядно отображать данные на карте. Для активации надстройки откройте вкладку Разработчик (Developer) и щелкните по кнопке Надстройки COM (COM Add-ins). В открывшемся окне поставьте галочку напротив Power View и нажмите ОК. После всех этих манипуляций на вкладке Вставка (Insert) у вас должна появиться кнопка Power View

Теперь можно выделить диапазон с исходными данными нажать на эту кнопку - в вашей книге будет создан новый лист (больше похожий на слайд из Power Point), где отобразятся выделенные данные в виде таблицы:

geo9.png

Превратить таблицу в географическую карту можно легко с помощью кнопки Карта (Map) на вкладке Конструктор (Design):

geo10.png

Обратите особое внимание на правую панель Поля Power View - на ней, в отличие от примитивных Bing Maps, перетаскивая мышью названия столбцов (полей) из исходной таблицы и бросая их в разные области, можно очень гибко настраивать получившееся геопредставление:

  • В область Местоположения (Locations) нужно закинуть столбец из исходной таблицы содержащий географические названия.
  • Если у вас нет столбца с названием, но есть колонки с координатами, то их нужно поместить в области Долгота (Longitude) и Широта (Latitude), соответственно.
  • Если в область Цвет (Color) закинуть товар, то каждый пузырек будет кроме размера (отображающего общую прибыль по городу) детализирован на дольки по товарам.
  • Добавление поля в области Вертикальных или Горизонтальных множителей (Dividers) разделит одно карту на несколько по этому полю (в нашем примере - по кварталам).
Также на появившейся сверху контекстной вкладке Макет (Layout) можно настроить фон карты (цветная, ч/б, контурная, вид со спутника), подписи, заголовки, легенду и т.д.

geo11.png

Если данных очень много, то на вкладке Power View можно включить специальную Область фильтров (Filters), где с помощью привычных галочек можно выбрать какие именно города или товары нужно показать на карте:

geo12.png

Итого в плюсах: легкость использования и гибкость настройки, возможность дробить одну карту на несколько по категориям.

В минусах: 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 Download Center (12 Мб)

После скачивания и установки надстройку нужно подключить на вкладке Разработчик - Надстройки COM (Developer - COM Add-ins) аналогично Power View из предыдущего пункта. После этого на вкладке Вставка должна появиться кнопка Карта (Map). Если теперь выделить таблицу с исходными данными:

geo14.png

... и нажать кнопку Карта, то мы попадем в отдельное окно надстройки Microsoft Power Map:

geo13.png

Если не вдаваться в детали (которых тут хватит на отдельный тренинг на полдня), то общие принципы работы с картой тут те же, что и в Power View, описанной выше:

  • Размер столбиков определяется столбцом исходной таблицы (Выручка), который мы закинем в поле Высота в правой панели. Принцип подсчета, как и в сводных таблицах, можно менять в раскрывающемся списке поля:
    geo15.png
  • Чтобы детализировать каждый столбик продаж на отдельные товары, нужно закинуть поле Товар в область Категория (Category).
  • Можно использовать различные типы диаграмм (гистограммы, пузыри, тепловую карту, заливку областей) с помощью кнопок на правой панели:
    geo16.png
  • Если в исходных данных есть столбец с датами продажи, то его можно закинуть в область Время (Time) - тогда внизу появится ось времени, по которой можно перемещаться в прошлое-будущее и посмотреть процесс в динамике.

"Вау-моментом" надстройки Power Map можно, пожалуй, назвать предельную легкость создания анимированных видеообзоров на основе сделанных карт. Достаточно сделать несколько копий текущей сцены с разных углов просмотра и разным масштабом - и надстройка автоматически создаст 3D-анимацию облета вашей карты с акцентированием внимания на выбранных местах. Полученное видео потом легко сохраняется в mp4-формате в виде отдельного файла для вставки, например, на слайд Power Point.

Способ 4. Пузырьковая диаграмма с "доработкой напильником"

Самый "колхозный" способ из всех перечисленных, но зато работающий во всех версиях Excel. Построить пузырьковую диаграмму (Bubble Chart), отключить у нее оси, сетку, легенду... т.е. все, кроме пузырей. Затем вручную подогнать положение пузырей, подложив под диаграмму скачанное заранее изображение нужной карты:

geo17.png

Минусы этого способа очевидны: долго, муторно, много ручной работы. Да еще и вывод подписей к пузырям представляет собой отдельную проблему, когда их много.

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

Способ 5. Сторонние приложения и надстройки 

Раньше существовало несколько надстроек и плагинов для Excel, позволяющих с той или иной степенью удобства и красоты реализовать отображение данных на карте. Сейчас подавляющее большинство из них либо заброшены разработчиками, либо в стадии тихого отмирания - с Power Map конкурировать тяжело :)

Из оставшихся в живых достойны упоминания:

  • MapCite - пожалуй, самая мощная из всех. Умеет привязываться к карте по названиям населенных пунктов, областей, округов и координатам. Выводит данные в виде точек или тепловой карты. Использует карты Bing в виде основы. Автоматом умеет кидать созданную карту в презентации Power Point. Для скачивания доступна бесплатная trial-версия, полная версия стоит 99$/год.
  • Esri Maps - надстройка от компании Esri также позволяющая подгружать и анализировать геоданные из Excel на карты. Много настроек, различные типы диаграмм, поддерживает русский язык. Есть бесплатная демо-версия. Полная версия требует подписки на картографический сервис ArcGis.
  • MapLand- одна из первых надстроек на эту тему, созданных еще для Excel 97-2003. Идет с набором карт в виде графических примитивов, к которым и привязываются данные с листа. Дополнительные карты надо докупать. Для скачивания доступна демка под разные версии Excel, Pro версия стоит 299$.



07.06.2015 18:52:59
Огромный минус Bing Maps - при сегментном делении круговой диаграммы ее размер одинаков для всех измерений (городов) при разных значениях.

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

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

ИМХО
08.06.2015 07:51:04
Николай, очень полезная информация. Спасибо!
06.04.2016 10:23:00

Николай, здравствуйте!
Такой вопрос: в excel 2016 , в Power View при введении координат в строку "местоположение" (пример: 55.755773,  37.614608  ) позиция находится, а если ввести эти координаты по отдельности в строки "долгота" и " широта" соответственно, то адрес не находит. Подскажите, как вводить данные в долготу и широту чтобы они работали?
Мне это нужно чтобы вместо координат при наведении курсора на кружок, выводились не координаты, а название объекта.
С уважением, Зураб.
28.03.2017 07:31:00
Николай, здравствуйте! Спасибо за знания!
Вопрос: а во 2 варианте можно сделать так, чтобы над диаграммами в карте отображались цифры?