Получение адреса и координат из Yandex и Google


Краткое описание

Новая версия макроса для Excel (VBA) для быстрого и удобного получения гео-координат/адресов из Yandex и Google. В качестве исходных данных на листе указывается список адресов (для получения координат) или список координат (для обратного геокодирования). Результаты запроса будут выведены в соседние ячейки.


Подробное описание

Некоторое время назад на сайте был опубликован макрос для получения координат из Yandex.
Несмотря на небольшой размер своего кода, макрос в удобном режиме позволяет по заданному списку адресов получать их geo-координаты (широту и долготу).

В текущей статье публикуем новый (доработанный) макрос для работы с координатами и адресами.

Изменения в новом макросе:

  1. Макрос может работать с API Яндекса и Гугла.
    Для работы с сервисом Яндекса используйте лист "yandex", для работы с Гуглом - лист "google". Предыдущая версия работала только с Яндекс.
  2. Из кода макроса исключен параметр KEY.
    Это значит, что теперь не нужно получать ключ разработчика для корректной работы макроса. Теперь всё работает без KEY. Разумеется, общие ограничения сервисов по прежнему имеют место - это около 25 000 запросов в сутки с одного IP. При необходимости узнать более подробно о действующих ограничениях, следуйте по ссылкам: лимиты для яндекса, лимиты для гугл.
  3. Добавлена возможность обратного геокодирования (определение адреса по долготе и широте).
    Для работы с обратным геокодированием необходимо соблюдать несколько важных нюансов, касающихся формата записи строки с координатами:
         - для Yandex координаты необходимо указывать в формате (через запятую, без пробелов): долгота,широта
         - для Google координаты необходимо указывать в формате (через запятую, без пробелов): широта,долгота
    В приложенном файле с макросом на соответствующих листах есть примеры как для Yandex, так и для Google.
  4. Добавлена статистика по количеству обработанных строк.
    Статистика начинает отображаться после запуска макроса. Наблюдать статистику можно в строке статус бара Excel.
  5. Типы переменных для работы с XML переименованы в Object.
    В предыдущей версии макроса типы были строго заданы как MSXML2.DOMDocument и MSXML2.IXMLDOMNodeList. Как следствие, возникали ошибки в процессе работы, если на ПК пользователя не было соответствующих библиотек "Microsoft XML".

Помимо указанных изменений, новый макрос содержит ряд других улучшений кода, в той или иной степени влияющих на корректность результата. По опыту, при тестировании макроса сервис Yandex показал более точный результат определения координат. Google, в свою очередь, показал более высокую скорость работы.

Также не забывайте, что оба сервиса хоть и служат одной и той же цели, но результат, полученный в каждом из них, немного отличается. Отличие не большое, но оно есть и, возможно, его стоит учесть. Поэтому, если этот момент вам принципиально важен, обязательно вручную проверьте результат в разных поисковых системах.

Вернуться к списку примеров

Комментарии (44)

  1. Артем 26 февраля 2017, 22:59 # 0
    Спасибо за макрос. Всё работает. Отдельная благодарность за выгрузку адресов по координатам.
    1. Александр 27 февраля 2017, 16:07 # 0
      Спасибо огромное!!! Все работает без проблем.
      Подскажите пожалуйста, я работаю оценщиком и постоянно сталкиваюсь с такой проблемой: для оценки 1-ой квартиры мне нужно подобрать 5 аналогов, в конце своего отчета я всегда добавляю скрин яндекс карты расположения аналогов и оцениваемой квартиры, нанося их на карту вручную, помечая маркерами от 1 до 5, возможно ли написать код который будет считывать 6 строк с адресами в ячейках ексель и выводить их в виде интерактивной карты или статической…
      1. Maxim 27 февраля 2017, 22:33(Комментарий был изменён) # 0
        Это возможно. Более подробно ответил вам в почте.
        Если вдруг не получили письмо, связаться со мной можно по адресу info@excelstore.pro
      2. Андрей 09 марта 2017, 14:04 # 0
        Благодарю Вас.
        Столь необходимая программа для логиста.
        Подскажите а есть возможность выведения всех геокоординируемых точек на карту.Готов оплатить данную услугу т.к пользоваться приходится постоянно
        1. Maxim 13 марта 2017, 15:59 # 0
          Да, такая возможность есть.
          Если разработка актуальна, связаться со мной можно по адресу info@excelstore.pro
        2. Александр 15 марта 2017, 18:10 # 0
          Доброго времени суток,
          подскажите, как сделать, что бы долгота и широта выводились в одну ячейку без пробелов через запятую?
          1. Maxim 15 марта 2017, 23:25(Комментарий был изменён) # 0
            В процессе работы макроса все данные формируются в массиве GlobalArray. Это одинаково работает как для макроса по Yandex, так и по Google.

            Вам будет достаточно поправить этот кусок кода:
            Sub StartMacro()
            	...
            	GlobalArray(i, 2) = tmpArray(3)         'адрес
            	GlobalArray(i, 3) = tmpArray(1)         'долгота
            	GlobalArray(i, 4) = tmpArray(2)         'широта
            	...
            	Sheets(shName).Range(Cells(1, 1), Cells(iRow, 4)).Value = GlobalArray
            	...
            End Sub
            Где вместо:
            GlobalArray(i, 3) = tmpArray(1) 
            GlobalArray(i, 4) = tmpArray(2)
            написать:
            GlobalArray(i, 3) = tmpArray(1) & "," & tmpArray(2)
            1. Maxim 15 марта 2017, 23:28 # 0
              Либо можете оставить макрос без изменений, а нужные вам ячейки (с долготой и широтой) склеивать простой формулой в дополнительном столбце.
            2. Azat 24 марта 2017, 09:25 # 0
              Здравствуйте.

              Уточните пожалуйста, в чём может быть причина результата конвертации «нет данных», в т.ч. по адресам-примерам в исходном файле.

              Возможно, есть требования к формату адресов?
              1. Azat 24 марта 2017, 09:47 # 0
                Либо мне нужно что-то поправить в настройках Excel
                1. Maxim 24 марта 2017, 19:04(Комментарий был изменён) # 0
                  Добрый день.
                  Примеры в приложенном файле вполне работоспособны. Только что еще раз проверил.
                  Формат строк для своих адресов соответственно можно указывать по аналогии.
                  Если же у вас не заработало даже на тех примерах, что в файле, то причины могут быть разные:
                  1) файервол или антивирус блокируют интернет соединение с яндексом/гуглом
                  2) на вашем ПК отсутствует библиотека MSXML2.DOMDocument
                  3) возможно что-то иное, исходя из специфики настроек вашего ПК/ОС/офиса
                  1. Алексей 21 сентября 2017, 12:20 # 0
                    Спасибо за отличный инструмент, но вот почему то никак не могу его заставить работать.
                    Уже и подключал разные версии MSXML и 3 и 6, проверял дебагером как отрабатывает функция (смутило что массив получается пустой). Доступ к интернету есть, ограничений нет (пробовал другой макрос для карт Гугл, он работает).
                    Может есть еще какие-то уже найденные нюансы из-за которых макрос не работает?
                    1. Maxim 21 сентября 2017, 15:54 # 0
                      Спасибо за отзыв.
                      Проверять работу макроса рекомендую на адресах, указанных в файле. Если с ними работает, то проблем быть не должно. Макрос у меня и моих ближайших коллег работает. Некоторым людям дорабатывал макрос, никаких проблем с его работоспособностью также не возникало.
                      Попробуйте на другом ПК. Скорее всего, дело в существующих библиотеках в вашей операционке.
                2. Azat 28 марта 2017, 10:54 # 0
                  Большое спасибо, очень хорошая разработка!

                  Действительно, причина была в настройках ПК.
                  Всё работает и очень гибко в части адресов.
                  1. Elena 02 апреля 2017, 13:30 # 0
                    Автор, огромное спасибо!
                    Помогло решить задачку на работе :)
                    1. Maxim 02 апреля 2017, 16:57 # 0
                      Если программа пригодилась, буду признателен, если поделитесь ссылкой на наш сайт или на указанную страницу с программой (в соц. сетях, на форумах и тд.)
                      1. Roman 05 апреля 2017, 13:09(Комментарий был изменён) # 0
                        Как можно изменить SelectSingleNode в SelectNodes в xmlYandex?
                        Так чтобы мне вывести не text, а все компоненты name в Component
                        1. Amnesiac 11 апреля 2017, 12:43 # 0
                          Огромное спасибо за макрос! Очень помогло в обработке большого количества адресов, нераспознанных адресной базой организации!
                          1. Andrey 13 апреля 2017, 15:12 # 0
                            Спасибо, отличный макрос!
                            Подскажите, пожалуйста, как получить «адрес Yandex» и «адрес Google» по-английски?
                            1. Maxim 06 мая 2017, 17:04 # 0
                              Для Яндекса попробуйте поменять параметр «lang» в запросе. Сейчас стоит lang=ru_RU, а вы укажите lang=en_US или lang=en_RU (смотря, что больше подойдет). Подробнее здесь: tech.yandex.ru/maps/doc/geocoder/desc/concepts/input_params-docpage/
                              Для Google по аналогии, только параметр по другому называется. Детали здесь: developers.google.com/maps/documentation/geocoding/intro
                            2. Mali 14 апреля 2017, 15:45 # 0
                              Спасибо Большое!

                              Коллега поделился этой ссылкой со мной.

                              А можно ли посчитать дистанцию между двумя адресами?
                              1. Maxim 06 мая 2017, 16:54 # 0
                                Имея начальную точку координат и конечную, да — можно посчитать. Только это будет дистанция по прямой, без учета дорог, маршрутов и тд. Поищете в интернете. На тему расчета расстояния между двумя точками очень много информации.
                              2. Alexey 24 апреля 2017, 20:44(Комментарий был изменён) # 0
                                Огромное спасибо за макрос! Очень удобная вещь, всем рекомендую теперь.

                                А вы не знаете, можно ли перенастроить этот макрос, чтобы он работал с украинским языком? Сейчас он выдает все на русском и достаточно шумно ищет украинские адреса. Спасибо!
                                1. Maxim 06 мая 2017, 17:02 # 0
                                  Да, макрос ориентирован на работу с российскими адресами.
                                  Для Яндекса попробуйте поменять параметр «lang» в запросе. Сейчас стоит lang=ru_RU, а вы укажите lang=uk_UA. Подробнее здесь: tech.yandex.ru/maps/doc/geocoder/desc/concepts/input_params-docpage/
                                  Для Google по аналогии, только параметр по другому называется. Детали здесь: developers.google.com/maps/documentation/geocoding/intro
                                2. Иван 25 апреля 2017, 09:27 # 0
                                  Добрый день! Огромное спасибо за макрос.
                                  Помогите допилить код, пожалуйста.
                                  Хочу избавиться от кнопки «получить координаты» и сделать автоматическое определение координат и адреса яндекс после нажатия Enter или при переходе в другую ячейку. Количество адресов для определения не большое: 1-10.
                                  1. Иван 25 апреля 2017, 10:26 # 0
                                    Вопрос не актуален. Разобрался.
                                    1. Павел 19 июня 2017, 01:21 # 0
                                      Сначала было все ок, а потом исчезла пауза между запросами в гугл (на второй тысяче), появились пропуски и теперь загружаются только пустые строки…
                                      1. Павел 19 июня 2017, 02:22 # 0
                                        Разобрался. Нужно получить ключ и подставить его в запрос.
                                        1. Maxim 19 июня 2017, 09:58(Комментарий был изменён) # 0
                                          В первоначальной версии макроса я использовал ключи (как для гугла, так и для яндекса). В макросе из данной темы ключи уже не используются, т.к. по моему опыту и опыту других пользователей все прекрасно работает и без них. Впрочем, лично я также считаю, что на больших объемах наличие ключа все-таки будет плюсом.

                                          Если будут еще обращения с данной проблемой, возможно, доработаю макрос для возможности использования ключей. Но пока что вы первый за все время, кто написал об этом.
                                          1. Павел 19 июня 2017, 15:00 # 0
                                            Благодарю за быстрый ответ.
                                            У меня список из 14000 адресов, к сожалению, за первый раз удалось снять с ключом около 3500 координат (гугл), хотя, ограничение гугла около 100 000 запросов в сутки и 50 запросов в секунду.
                                            Также было бы хорошо проверять иногда, если это возможно, есть ли интернет и пропускать строки, для которых координаты уже найдены. (Сейчас скрипт пропускает пустые строки, вышел из положения сортировкой и удалением данных из строк с найденными координатами, предварительно сохраняя их в отдельном файле)
                                            Благодарю за скрипт. Желаю всех благ.
                                            1. Павел 19 июня 2017, 15:11(Комментарий был изменён) # 0
                                              Также интересная задача парсинга изображений с street view, чтобы, например, можно было спарсить всю улицу перпендикулярно ее направлению. Не думали о разработке такого скрипта?
                                              1. Maxim 19 июня 2017, 16:56 # 0
                                                Павел, а откуда у вас информация, что гугл отдает 100000 запросов? На сколько мне известно, в сутки лимит 25000. Инфа здесь: developers.google.com/maps/documentation/javascript/usage?hl=ru

                                                Функционал по чеку коннекта и проверки дублей, подумаю, может и вправду добавлю, т.к. многие наверняка не удаляют дубли перед запуском макроса и не следят за стабильностью сети.
                                                Кстати, если вас быстро забанили, чтобы не усложнять задачу проксями, попробуйте работу макроса через интернет мобильного телефона (разумеется, если трафик лимита позволяет).

                                                На счет парсинга street view пока не думал. Не было задачи, где бы это могло бы пригодится. Вы для себя в чем видите полезность этого парсинга?
                                          2. Елена 21 июня 2017, 16:23 # 0
                                            Спасибо большое! Чудесная программа! Просто нет слов! Одни ахи и охи :)))
                                            Подскажите, пожалуйста, как вставить в Ваш макрос еще и высоту?
                                            И как поменять формат вывода координат на следующий 50°40'46,461«N 95°48'26,533»W 123,45m
                                            1. Maxim 21 июня 2017, 17:08 # 0
                                              1. Высоту, думаю, что только отдельным запросом. Для Гугла, например, это могло бы выглядеть так:
                                              https://maps.googleapis.com/maps/api/elevation/json?locations=<широта>,<долгота>&key=<ключ API>
                                              2. Формат вывода, как вариант, написать отдельную процедуру, которая будет преобразовывать значения широты и долготы из финального массива GlobalArray в нужный вам формат.
                                              1. Елена 22 июня 2017, 08:40 # 0
                                                Спасибо!
                                                1. Maxim 22 июня 2017, 09:32 # 0
                                                  Если нужна доработка по данным требованиям — пишите, сделаю за небольшую плату.
                                                  Подробности по почте info@excelstore.pro
                                            2. Степан 12 июля 2017, 00:00 # 0
                                              Большое спасибо!
                                              1. Алексей 04 сентября 2017, 19:43 # 0
                                                Доброго времени суток. Подскажите с чего начинать? не пойму, как подключить этот макрос в екселе. Но я в этом деле чайник. только включил макросы и вывел панель «разработчик».
                                                1. Maxim 10 сентября 2017, 20:26 # 0
                                                  Здравствуйте.
                                                  Сам файл, который вы скачали, — это и есть файл с макросом. Открываете. Если эксель выводит окно с запросом, разрешить выполнение макроса или нет, нажимаете разрешить. Собственно всё. Панель разработчика в данном случае вообще без надобности.
                                                2. Sergio 05 октября 2017, 17:23 # 0
                                                  Спасибо огромное за труд! Подскажите, пожалуйста, как брать адрес из другого столбца и записывать так же в другие? Спасибо!
                                                  1. Maxim 06 октября 2017, 02:18 # 0
                                                    Исходные данные записываются в массив GlobalArray. Потом в данный массив дописываются значения, полученные из Яндекса/Гугла. И потом данный массив вставляется обратно на лист.
                                                    Соответственно, отвечая на ваш вопрос: переопределите столбцы в массиве GlobalArray.
                                                  2. Раш 10 октября 2017, 14:18 # 0
                                                    Работает, спасибо!
                                                    1. Игорь 12 ноября 2017, 11:09 # 0
                                                      Здравствуйте!
                                                      На macOS почему-то пишет, что данные загружены, но ничего не прогружается в координатах и тд :( что делать?
                                                      спасибо!
                                                      1. Maxim 13 ноября 2017, 19:38 # 0
                                                        Здравствуйте!
                                                        Макрос изначально создавался под Windows. Работа макроса в macOS не тестировалась.
                                                        Что делать? Попробовать разобраться/поправить самому либо найти программиста под данную ОС и заказать у него доработку. Я под macOS не пишу.