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

179173 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 варианте можно сделать так, чтобы над диаграммами в карте отображались цифры?
30.05.2018 12:41:25
Николай, здравствуйте. Как можно сортировать месяца в Power View, чтоб они шли по порядку?
05.06.2019 14:16:10
Это нужно сортировать в модели данных Power Pivot. Там есть опция сортировки одного столбца (название месяца) по значениям другого столбца (номер месяца, полученный функцией MONTH).
Добрый день. Подскажите, пожалуйста, почему при открытии 3д карты глобус появляется без границ просто как синий шар?
05.06.2019 14:14:36
Обычно такое бывает, если нет доступа в интернет или доступ закрыт для Office корпоративным фаейрволом.
05.06.2019 13:36:12
Какая может быть причина в том, что Power View не появляется на вкладке Вставка? Хотя в надстройках СОМ он присуствует и галка проставлена.
Эксель перезапускал.
05.06.2019 14:14:58
А Office лицензионный? ;)
05.06.2019 14:25:15
Неа 8)
05.06.2019 14:25:39
И что, без вариантов тогда?
30.08.2019 15:13:57
Способ 4.
Вместо Х и У можно указать реальные координаты, также указать min/max осей координат - и никаких проблем с позиционированием кружков.
17.02.2020 16:49:00
А может кто-нибудь подсказать, почему невозможно добавить легенду в способе 3 (3D карта)? Кнопка "Легенда" вообще неактивна. Кстати, судя по скриншоту приведенному здесь в примере, тут такая же проблема.
17.09.2024 16:22:42
Коллеги, как добавить карту на лист Эксель, если у меня есть список адресов в одном городе?

Вид рекламыПериод размещенияАдрес
Пиллары 1,4 х 3,0 моктябрь 1–15Арбат Новый ул, дом № 11, поз. 2
Пиллары 1,4 х 3,0 моктябрь 16–31Марксистская ул, дом № 34, кор. 7
Ситиформаты 1,2 х 1,8 мноябрь 1–15Фестивальная ул. 13 (м. "Речной вокзал"
Ситиформаты 1,2 х 1,8 мсентябрь 1– 30Комсомольский пр-т 18
Сити-борды 3,7 х 2,7 мсентябрь 1– 30Тверская-Ямская 1-я ул.  7 (м. "Маяковская", (роллер)

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