Содержание:
Если ваша компания имеет филиалы по всей стране или осуществляет продажи не только в пределах МКАД, то рано или поздно перед вами встанет задача визуального отображения числовых данных из Microsoft Excel (продажи, заявки, объемы, клиенты) на географической карте. с привязкой к конкретным городам и регионам. Давайте кратко рассмотрим основные способы визуализации геоданных, существующие в Excel.
Метод 1: быстро и бесплатно — компонент Bing Maps
Начиная с версии 2013 года в Excel появился встроенный магазин приложений, т.е. появилась возможность покупать, скачивать и устанавливать дополнительные модули и дополнения с отсутствующими функциями. Один из таких компонентов как раз и позволяет визуально отображать числовые данные на карте — он называется Bing Maps и, что особенно приятно, абсолютно бесплатен. Для установки откройте вкладку Вставка – Магазин (Вставка – Приложения Office):
После вставки компонента на листе должен появиться динамический контейнер с картой. Для визуализации вашей информации на карте необходимо выбрать диапазон с геоданными и нажать кнопку Показать локации:
При необходимости в настройках (значок шестеренки в правом верхнем углу компонента) можно изменить цвета и тип отображаемых графиков:
Также есть возможность быстро фильтровать города, отображая только те, которые вам нужны (значок воронки в правом верхнем углу компонента).
Вы можете легко привязать не только к городам, но и к другим объектам: регионам (например, Тульская обл.), автономные округа (например, Ямало-Ненецкий) и республики (Татарстан) — тогда в центре области отобразится кружок диаграммы. Главное, чтобы название в таблице соответствовало подписям на карте.
Всего в плюсы данного метода: легкая бесплатная реализация, автоматическая привязка к карте, два типа графиков, удобная фильтрация.
В минусы: нужен Excel 2013 с доступом в Интернет, нельзя выбирать регионы и районы.
Метод 2. Гибкий и красивый — представление карты в отчетах Power View
Некоторые версии Microsoft Excel 2013 поставляются с мощной надстройкой визуализации отчетов под названием Power View, которая позволяет (помимо всего прочего, а умеет многое!) визуально отображать данные на карте. Для активации дополнения откройте вкладку разработчик (Разработчик) и нажмите на кнопку COM-надстройки (надстройки COM). В открывшемся окне установите флажок рядом с Power View и нажмите «ОК». После всех этих манипуляций на вкладке Вставить (Вставить) у тебя должна быть кнопка взгляд на власть.
Теперь вы можете выбрать диапазон с исходными данными, нажмите на эту кнопку — в вашей книге будет создан новый лист (больше похож на слайд из Power Point), где выбранные данные будут отображены в виде таблицы:
Вы можете легко превратить таблицу в географическую карту с помощью кнопки Card (карта) таб Конструктор (Дизайн):
Особое внимание обратите на правую панель. Поля 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 из Центра загрузки Microsoft (12 МБ)
После скачивания и установки дополнения его необходимо подключить на вкладке Разработчик – надстройки COM (Разработчик — надстройки COM) аналогично Power View из предыдущего пункта. После этого на вкладке Вставить кнопка должна появиться Card (карта). Если теперь выделить таблицу с исходными данными:
… и нажимаем кнопку «Карта», после чего мы попадем в отдельное окно надстройки Microsoft Power Map:
Не вдаваясь в подробности (которых хватит на отдельное обучение на полдня), то общие принципы работы с картой такие же, как и в описанном выше Power View:
- Размер столбцов определяется столбцом исходной таблицы (Выручка), который мы бросим в поле рост на правой панели. Принцип подсчета, как и в сводных таблицах, можно изменить в выпадающем списке полей:
- Для детализации каждой графы продаж по отдельным товарам необходимо заполнить поле Продукт в район Категории (Категория).
- Вы можете использовать различные типы диаграмм (столбчатые диаграммы, пузырьки, тепловые карты, заполненные области) с помощью кнопок на правой панели:
- Если в исходных данных есть столбец с датами продаж, то его можно закинуть в область Время (Время) — тогда внизу появится ось времени, по которой вы сможете переместиться в прошлое-будущее и увидеть процесс в динамике.
«Вау-моментом» дополнения Power Map пожалуй можно назвать предельную простоту создания анимированных видеообзоров на основе сделанных карт. Достаточно сделать несколько копий текущей сцены с разных углов обзора и в разных масштабах — и надстройка автоматически создаст 3D-анимацию полета по вашей карте, фокусируясь на выбранных местах. Полученное видео затем легко сохраняется в формате mp4 в виде отдельного файла для вставки, например, на слайд Power Point.
Способ 4. Пузырьковая диаграмма с «уточнением файла»
Самый «колхозный» метод из всех перечисленных, но работающий во всех версиях Excel. Постройте пузырьковую диаграмму (Bubble Chart), отключите ее оси, сетку, легенду… т.е. все, кроме пузырьков. Затем вручную отрегулируйте положение пузырьков, поместив под схему ранее скачанное изображение нужной карты:
Минусы Особенности этого метода очевидны: долго, муторно, много ручной работы. Причем вывод подписей для бабблов — отдельная проблема, когда их много.
Плюсы тем, что этот вариант будет работать в любой версии Excel, в отличие от следующих методов, где требуется Excel 2013. И подключение к Интернету не требуется.
Способ 5. Сторонние приложения и дополнения.
Ранее существовало несколько дополнений и плагинов для Excel, позволяющих с разной степенью удобства и красоты реализовать отображение данных на карте. Сейчас подавляющее большинство из них либо заброшены разработчиками, либо находятся в стадии молчаливого вымирания — с Power Map сложно конкурировать 🙂
Из выживших следует упомянуть:
- КартаCite – возможно, самый мощный из всех. Возможность привязки к карте по названиям населенных пунктов, областей, районов и координатам. Отображает данные в виде точек или тепловой карты. В качестве основы использует Bing Maps. Автоматически умеет перекинуть созданную карту в презентацию Power Point. Бесплатная пробная версия доступна для скачивания, полная версия стоит 99 долларов в год.
- Карты Esri – дополнение от Esri, которое также позволяет загружать и анализировать геоданные из Excel на карты. Множество настроек, различные типы графиков, поддержка . Есть бесплатная демо-версия. Для полной версии требуется подписка на картографический сервис ArcGis.
- КартаЗемля– одна из первых надстроек по данной теме, созданная для Excel 97-2003. В комплекте идет набор карт в виде графических примитивов, к которым прикреплены данные с листа. Необходимо приобрести дополнительные карты. Для скачивания доступна демо-версия различных версий Excel, версия Pro стоит 299 долларов.