Как нанести на карту адреса из эксель
Есть сервисы, которые сделают визуализацию лучше. Но если надо быстро, то можно сделать это средствами MS Excel 365 (еще MS Excel 2019).
0. Есть таблица с регионами и показателями
Выделяем нужные столбцы в таблице
1. Вставка – Диаграммы – Карты
2. Тихо материмся и втыкаем в пустоту на диаграмме
Еще раз материмся, если ничего не изменилось и заново переопределяем источник данных, для этого можно удалить данную диаграмму и создать новую. А можно кликнуть Конструктор диаграмм – источник данных,
Появится такое окошко
Жмем на стрелочку для "перевыбора" области исходных данных для диаграммы и выделяем нужный диапазон с двумя столбцами.
В итоге появится вот такая диаграмма в виде карты мира
3. Локализуем регион диаграммы
Если весь мир не нужен, а в нашем случае не нужен, то кликаем на карте правой кнопкой мыши и в менюшке выбираем "Формат рядов данных".
Слева (обычно там) появится вот такое окно
Выбираем в первом поле "Проекция Меркатора" (ааа, вспомнили уроки географии сразу за 6 класс, знакомое слово))))
Карта уже не кажется натянутой на глобус и приняла более привычный вид.
Во втором поле выбирайте "Только области с данными", на выбор даются несколько вариантов.
Иногда вариантов чуть больше
Тогда на карте останутся только участок карты, который вмещает имеющиеся в вашей таблице регионы-области
Не пугайтесь, часть Сибири в нашем примере инопланетяне не угнали на другую планету. Просто данных по соответствующим регионам нет в нашей таблице с данными.
Поэтому таблицу занести недостающие регионы, с нулевыми показателями. Список регионов можно скачать ниже.
! Важно, только области в нашем случае, Например, только "Московская область", никаких "Москва и область". и т.п.
! Важно - после добавления строк с недостающими регионами надо переопределить источник данных для диаграммы, чтобы добавленные строки в диаграмме показались.
В итоге у вас получится более красивая картинка (и да, я в итоге выбрал Проекцию Альберса )
4. Наводим красоту
Можно добавить подписи регионам
Можно изменить цвета карты
А вот то что видите на скрине выше -это минус данного способа. Садись, Майкрсофт, два тебе по географии! Это так система метчит регионы рф.
Минусы
1. Обзывает Тверскую область Московской, а Московскую не показывает
Частично лечится тем, что надо добавить столбец с указанием гео-таргета уровнем выше, в нашем случае добавить столбец "Страна" и везде прописать "Россия".
По слова создателя, т.к. мире может быть несколько схожих гео-таргетов одного уровня но лежащих в разных гео-тарегтах более высокого уровня (например, как вы помните, выдуманный Том Сойер жил в реальном городке Санкт-Петербурга на берегу Миссисипи, или Миссури, не помню)
Естественно исходные данные для диаграммы надо заново переопределить + обновить данные с сервера вот таким образом
При необходимости указать, что значимым для вас является карта "по регионам" а не по "стране".
Предположим, что перед нами стоит классическая задача транспортной логистики: визуализировать движение некоего объекта по заданному маршруту из нескольких промежуточных точек. Для конкретики, давайте возьмем скорый фирменный поезд "Жигули", движущийся по маршруту Москва - Самара по следующему графику (взято из Яндекс.Расписаний):
Для решения задачи нам потребуется Excel 2013-2016 с установленной надстройкой Power Map. В Excel 2016 она установлена по умолчанию, для Excel 2013 можно скачать ее бесплатную превью-версию.
Этап 1. Находим координаты
Для однозначной привязки к промежуточным пунктам маршрута лучше использовать не названия населенных пунктов (они могут повторяться либо отсутствовать в принципе в нужном месте), а нормальные географические координаты. Достаточно щелкнуть по нужному месту в Яндекс-картах или Google Maps и вы увидите широту и долготу этой точки:
Добавим найденные координаты к нашей исходной таблице расписания движения поезда:
Этап 2. Дробим перегоны
Для плавного отображения движения поезда на карте нам необходимо разделить каждый перегон на несколько участков (чем их больше, тем плавнее будет анимация). Таким образом, перед нами встает задача получить примерные координаты и время для каждой промежуточной точки. Решить проблему можно формулой либо макросом.
Например, если хотим разбить каждый перегон на шесть интервалов (т.е. пять точек), то можно реализовать все одной формулой:
Но вставлять промежуточные строки, вводить и копировать формулу на все зеленые ячейки для каждого перегона придется вручную.
Другой вариант - макрос, что гораздо удобнее при большом количестве перегонов и промежуточных точек маршрута. Откройте редактор Visual Basic на вкладке Разработчик (Developer) или нажмите сочетание клавиш Alt + F11 . Вставьте в вашу книгу новый пустой модуль через меню Insert - Module и скопируйте туда этот код:
Как легко сообразить, константа MINS_IN_ONE_STEP задает количество минут в каждом шаге - можете менять ее значение по своему усмотрению. Теперь если выделить таблицу с данными или установить в нее активную ячейку, а потом запустить наш макрос сочетанием клавиш Alt + F8 или кнопкой Макросы на вкладке Разработчик (Developer - Macros) , то наша таблица будет преобразована в следующий вид:
Как видите, каждый перегон теперь делится на несколько интервалов - по 1 минуте каждый.
Этап 3. Переходим к карте
Осталось совсем чуть-чуть. Выделите полученную таблицу и на вкладке Вставка нажмите кнопку 3D-карта (Insert - 3D-map) :
Не перепутайте ее с кнопкой Карты (которая с глобусом) или Карты Bing (желтого цвета). После нажатия должно открыться окно надстройки Power Map.
В правой части окна на панели добавьте в группе Расположение (Location) поля широты и долготы и выберите напротив каждого название соответствующего столбца из нашей таблицы. Если все сделаете правильно, то на карте тут же должен отобразиться наш маршрут:
Теперь осталось выбрать в выпадающем списке Время (Time) столбец со значениями даты-времени из нашей таблицы и можно запускать анимацию с помощью кнопки воспроизведения в нижней части окна:
Дополнительно можно поиграться настройками слоя - кнопка Параметры слоя (Layer Options) в правом нижнем углу - и установить цвет, размер, прозрачность и т.д. отображаемых точек.
Если нажать на неприметную иконку с часами рядом с выпадающим списком Время, то можно поменять режим отображения и рисовать не маршрут, а сам поезд.
При щелчке левой кнопкой мыши по любой интересующей точке маршрута мы увидим ее подробные данные - координаты и время прохождения:
Этап 4. Несколько поездов сразу
Не секрет, что на самом деле по маршруту Москва-Самара курсируют два состава - в противофазе: когда один стартует из Москвы, другой примерно в то же время начинает движение ему навстречу из Самары. Утром один из них приходит в Самару, а другой, соответственно, в Москву и вечером процесс запускается заново. Расписание второго примерно отзеркаливает первый:
Что сделать, чтобы отобразить их на карте оба сразу?
Если по маршруту одновременно движется больше одного объекта, то данные по ним можно обработать аналогичным образом (Этапы 1 и 2) и просто добавить в продолжение нашей маршутной таблицы. А чтобы отличать поезда друг от друга, добавить еще один столбец с названием объекта:
Теперь, если построить по такой таблице еще одну визуализацию, мы будем видеть движение двух составов одновременно:
Ссылки по теме
Добрый день
Николай,-очень кстати данная тема, работа связана с транспортом и маршрутами,давно ждал что то подобное.Огромное Вам спасибо
Просто прекрасная и нужная инструкция. Николай, коллеги, а подскажите, пожалуйста, какой модуль к MS Office должен быть подключен для того, чтобы было доступно:
Вставка ---> нажмите кнопку 3D-карта (Insert - 3D-map) :
"Для решения задачи нам потребуется Excel 2013-2016 с установленной надстройкой Power Map. В Excel 2016 она установлена по умолчанию, для Excel 2013 можно скачать ее бесплатную превью-версию ."
Спасибо за урок! Не сочтите за докапывание, но в конце 2 этапа "Как видите, каждый перегон теперь делится на несколько интервалов - по 1 секунде каждый." - по минуте же - не?
Э.. да, конечно! Спасибо!
Очень крутая тема .
Предлагаю развить на предмет расчета расстояния.
Подскажите пожалуйста как это возможно реализовать с помощью google map например?
У меня есть вот такой макрос, который рассчитывает расстояние, маршрут и время в пути. Мне необходима только та часть которая отвечает за измерение расстояния. Самостоятельно разобрать не хватает знаний. Буду признателен за помощь.
Option Explicit
Public ActivationMark As Boolean
Public WasRequestGoogle As Boolean
Public MyDistance As Variant
Public MyDuration As Variant
'Задаем границы допустимых координат
Public Const Lat_min = -180, Lat_max = 180
Public Const Lon_min = -180, Lon_max = 180
'Скрываем заставку
Private Sub KillTheForm()
Unload Excelminsk
End Sub
Sub GetDistanceDurationGoogle(Address1 As String, Address2 As String)
Dim XMLDoc As Object
Dim Coord1NodeList As Object, Coord2NodeList As Object
Dim DistanceNodeList As Object, DurationNodeList As Object
Dim MyRequest As String
Dim Lat1 As String, Lon1 As String, Lat2 As String, Lon2 As String
On Error Resume Next
'Обнуляем переменные
MyDistance = ""
MyDuration = ""
'Ставим задержку между запросами
If (Address1 = Range("A3";) And Address2 = Range("B3";)) Then
Else
Application.Wait (Now + TimeValue("0:00:01";))
End If
'Кодируем адрес
Address1 = RussianStringToURLEncode_New(Address1)
Address2 = RussianStringToURLEncode_New(Address2)
MyRequest = "https://maps.googleapis.com/maps/api/directions/xml?origin=" & Address1 & "&destination=" & Address2 & "&mode=driving&language=ru"
'Debug.Print MyRequest
'Загружаем XML-документ
Set XMLDoc = CreateObject("Msxml2.DOMDocument";)
XMLDoc.async = False
If Not XMLDoc.Load(MyRequest) = True Then
MyDistance = "!ДАННЫЕ НЕ ЗАГРУЖЕНЫ"
MyDuration = "!ДАННЫЕ НЕ ЗАГРУЖЕНЫ"
Exit Sub
End If
'Считываем статус ответа
Select Case XMLDoc.SelectNodes("*/status";).Item(0).text
Case "OK"
Case "NOT_FOUND"
'Не нашел адрес точки
MyDistance = "!НЕ НАШЕЛ АДРЕС"
MyDuration = "!НЕ НАШЕЛ АДРЕС"
Exit Sub
Case "ZERO_RESULTS"
'Не может проложить маршрут
MyDistance = "!НЕТ ДОРОГИ"
MyDuration = "!НЕТ ДОРОГИ"
Exit Sub
Case "OVER_QUERY_LIMIT"
If WasRequestGoogle = False Then
Application.Wait (Now + TimeValue("0:00:02";))
WasRequestGoogle = True
Call GetDistanceDurationGoogle(Address1, Address2)
Exit Sub
Else
MyDistance = "!ПРЕВЫШЕНИЕ ЛИМИТА"
MyDuration = "!ПРЕВЫШЕНИЕ ЛИМИТА"
Exit Sub
End If
Case "REQUEST_DENIED"
MyDistance = "!ЗАПРОС ОТКЛОНЕН"
MyDuration = "!ЗАПРОС ОТКЛОНЕН"
Exit Sub
Case "INVALID_REQUEST"
MyDistance = "!НЕВЕРНЫЙ ЗАПРОС"
MyDuration = "!НЕВЕРНЫЙ ЗАПРОС"
Exit Sub
Case "UNKNOWN_ERROR"
MyDistance = "!НЕИЗВЕСТНАЯ ОШИБКА"
MyDuration = "!НЕИЗВЕСТНАЯ ОШИБКА"
Exit Sub
End Select
'Получаем координаты
Set Coord1NodeList = XMLDoc.SelectNodes("*//start_location";)
Lat1 = Coord1NodeList.Item(Coord1NodeList.Length - 1).FirstChild.text
Lon1 = Coord1NodeList.Item(Coord1NodeList.Length - 1).LastChild.text
Set Coord2NodeList = XMLDoc.SelectNodes("*//end_location";)
Lat2 = Coord2NodeList.Item(Coord2NodeList.Length - 1).FirstChild.text
Lon2 = Coord2NodeList.Item(Coord2NodeList.Length - 1).LastChild.text
'Debug.Print "Coord1=" & Lat1 & ", " & Lon1
'Debug.Print "Coord2=" & Lat2 & ", " & Lon2
'Проверяем ограничения для координат
If MyValue(Lat1) < Lat_min Or MyValue(Lat1) >Lat_max Or MyValue(Lon1) < Lon_min Or MyValue(Lon1) >Lon_max Or _
MyValue(Lat2) < Lat_min Or MyValue(Lat2) >Lat_max Or MyValue(Lon2) < Lon_min Or MyValue(Lon2) >Lon_max Then
MyDistance = "!ОГРАНИЧЕНИЕ ДЕМО"
MyDuration = "!ОГРАНИЧЕНИЕ ДЕМО"
Else
'Расстояние в метрах
Set DistanceNodeList = XMLDoc.SelectNodes("*//distance";)
MyDistance = Round(DistanceNodeList.Item(DistanceNodeList.Length - 1).FirstChild.text / 1000, 0)
'Debug.Print "MyDistance *//duration";)
MyDuration = CLng(DurationNodeList.Item(DurationNodeList.Length - 1).FirstChild.text) / 3600 / 24
'Debug.Print "MyDuration %" & Hex(AscW(l) \ 64 \ 64 + 224) & "%" & Hex(AscW(l) \ 64) & "%" & Hex(8 * 16 + AscW(l) Mod 64)
Case Is > 127: t = "%" & Hex(AscW(l) \ 64 + 192) & "%" & Hex(8 * 16 + AscW(l) Mod 64)
Case 32: t = "%20"
Case Else: t = l
End Select
RussianStringToURLEncode_New = RussianStringToURLEncode_New & t
Next
End Function
'Конвертируем широту и долготу из текста в число
Function MyValue(ByVal text As String) As Double
Dim MySeparator As String
'Считываем системный разделитель
MySeparator = Application.International(xlDecimalSeparator)
MyValue = (Trim(Replace(text, ".", MySeparator)) + 0)
End Function
Для этого вам потребуется иметь аккаунт в Google и заранее подготовить файл в Excel.
Сколько времени уйдет, чтобы внести на карту 100 точек? А если адресов больше тысячи? С сервисом Google Карты вы потратите на эту работу не более пяти минут.
Сервис Google Карты позволяет за пару кликов добавить на карту большое количество адресов. Но для этого придется создать файл Excel с этими адресами.
Для примера, чтобы показать, как создавать интерактивную карту в сервисе, используем программу капитального ремонта (Липецкой области). Та таблица, которая размещена на официальном сайте регионального Фонда капремонта, не подойдет для загрузки. Поэтому:
- Создайте файл Excel.
- Выбирайте в скаченном документе нужные столбцы, выделяйте нужное количество строк, копируйте.
- Возвращайтесь в свой файл Excel, вставьте скопированные данные.
- Аналогичным образом добавьте другие столбцы.
В появившемся списке выбирайте «Мои места». Переходите во вкладку «Карты». Нажимайте «Создать карту».
Назовите карту. Кликайте «Импорт». Всплывет окно для загрузки файла. Загрузите подготовленный документ Excel. В появившемся окне выбирайте наименование столбца, где указаны адреса. Нажимайте «Продолжить».
Возможно, сервис не распознает все адреса из файла. Откройте таблицу в сервисе и поправьте адреса. Иногда для этого достаточно убрать лишние слова, например, «городской округ».
Маркеры можно выделить разным цветом, например, чтобы показать распределение адресов по году проведения ремонта или году постройки дома (применительно к карте капитального ремонта).
Нажимайте «Стандартный стиль». Выбирайте по какому критерию будете выделять маркеры. Вы можете оставить их цветными или одним цветом с градиентной разбивкой. Выделите «Диапазоны».
Каждому маркеру можно добавить ярлык — информация, которая будет отображаться рядом с маркером. Для этого кликните «Нет ярлыка» и выбирайте параметр, который будет отображаться.
Включите доступ для всех и укажите: другие пользователи могут только просматривать карту или еще и редактировать ее. Нажимайте «Сохранить» и «Готово».
Чтобы получить код для вставки на сайт, нажимайте на три точки рядом с названием карты и «Добавить на сайт». В появившемся окне копируйте код.
Результат на сайте будет выглядеть так:
Как добавить список адресов на карту (например Google maps)? Для удобства нам нужно добавлять адреса не по одному, а группой. Как это сделать быстро и с разными вариантами?
Создание карты из списка адресов. Добавить список адресов на карту
Итак, у вас есть файл excel или другая таблица с адресами. Все адреса необходимо нанести на карту. Для начала подготовьте таблицу под формат google, т.е. таблица должна состоять из 3х столбцов
- Месторасположение точки. Широта и долгота или название адреса (как правило страна, город, улица, дом, все через запятую).
- Название точки (как бы вы хотели, чтобы эта точка называлась на карте)
- Описание точки. Комментарий.
Теперь откройте google maps , войдите в свой аккаунт (в правом верхнем углу). Войдите в Меню.
Выберите свой файл на компьютере или перетащите в появившееся окно и следуйте инструкции по дополнению адресов (необходимо прописать какой столбец чему соответствует). В конце нажимаете «Готово» — все, карта создана, смотрите картинку в начале, должно получиться примерно так.
Ссылка на карту из файла Excel
Здесь все просто.
Копируйте url адрес вашей карты из браузера. Теперь в файле excel нажмите на ячейке правой кнопкой мыши, меню «Гиперссылка» в поле адрес добавьте скопированный адрес.
Теперь при нажатии на ячейку будет открываться браузер с нужной ссылкой.
Друзья, на дня с удивление узнал, что в есть еще одна интересная возможность для работы с картами, для этого необходимо подключить плагин Bing Maps. Перейдите в «Магазин приложений» MicroSoft и в окне «Вставка» установите плагин с соответствующим названием.
На вкладке Вставка — Мои приложения предварительно выделите нужные данные на карте.
Просто добавить адреса на карту в Excel 2013 и выше
В версии Excel 2013 появилась возможность добавить карты с помощью инструмента GeoFlow.
Чтобы добавить эту возможность зайдите на вкладку Разработчик и кликните по кнопке Надстройки COM.
В открывшемся окне поставьте галочку напротив Power View и нажмите ОК. После всех этих манипуляций на вкладке Вставка (Insert) у вас должна появиться кнопка Power View.
В открывшемся окне добавьте галочку напротив возможности с картами (Power Map).
Теперь на меню Вставка есть вкладка Карта:
Интересное видео по теме (рассказывает один из разработчиков этого дополнения):
Многие люди и организации используют документы Excel для хранения данных о местоположении в форме адресов, почтовых индексов, названий городов или регионов. BatchGeo даёт возможность без проблем превратить ваш файл Excel (XLS) в наглядную карту.
Независимо от того, составляете ли вы карту ваших потенциальных клиентов или прикидываете, откуда приедут родственники на семейный праздник, вы можете очень быстро визуализировать нужные места на карте и поделиться этой картой с остальными. Когда данные будут перенесены на карту, вы можете получить дополнительную информацию о интересующем вас месте, просто нажав на нужный маркер. Как вариант, вы можете сгруппировать определённые столбцы вашей таблицы, таким образом посетители вашей карты смогут выбрать какой набор данных из таблицы они хотят увидеть на карте.
Создание карты из файла Excel
Перенести табличные данные на карту при помощи BatchGeo очень легко. Мы говорим об Excel, поскольку это наиболее распространённая программа для работы с электронными таблицами, но для программ Numbers и Google Документы процесс будет точно таким же.
Данные о местоположении должны содержаться в одном столбце. Это могут быть почтовые индексы или названия городов, а могут быть и полные адреса. Чем больше у вас данных, тем лучше для геокодера.
Чтобы начать создание карты, выполните следующие шаги:
После того, как все строки таблицы будут геокодированы, нажмите «Сохранить и продолжить», затем введите дополнительную информацию о своей карте. Укажите её название, и, если хотите, краткое описание. Чтобы зарезервировать за собой статус владельца карты, что в дальнейшем позволит вам редактировать её, укажите свой адрес электронной почты.
Теперь нажмите «Сохранить карту», после чего вы будете перенаправлены на уникальную страницу своей карты.
Watch this short video
For more information on how to create a map using excel spreadsheet data, watch our short video.
Поделитесь своей картой
Помимо этого вы можете встроить карту в страницу веб-сайта, например в свой блог. BatchGeo поддерживает встраивание как полноценных интерактивных карт, так и значков-картинок, которые являются эскизом изображения на карте и ведут на её полную версию. Код для каждого варианта вставки вы можете получить при редактировании карты либо в электронном письме, которое придёт вам, если укажете свой адрес электронной почты при сохранении карты.
И наконец, вы можете открыть свою карту при помощи программы Google Планета Земля, использовав KML-файл своей карты. Любой пользователь ПО Google Планета Земля может загрузить этот файл и увидеть все места, отмеченные на вашей карте в 3D-браузере программы.
Читайте также: