Получение гео-координат с Yandex карт


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

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


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

Наверное многие из знают, что такое карты Yandex и Google. Может быть у вас даже были (или есть) задачи, когда по списку адресов нужно получить координаты точек на карте (широту и долготу)?

Специально для данной цели нами был написан макрос в экселе для получения координат с Yandex карт.

Работать с макросом очень просто:

  1. указываете список адресов на листе,
  2. нажимаете кнопку,
  3. получаете результат.

При желании, макрос несложно доработать на обратный порядок, то есть по списку координат получать адреса.

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

Важно!
Данная версия абсолютно функциональна, но имеет ряд ограничений, описанных ниже в комментариях.
На текущий момент на сайте опубликована новая версия данного макроса, существеенно доработанная и улучшенная: ссылка на страницу.

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

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

  1. Ul 30 октября 2015, 15:02 # 0
    Яхууу, спасибо большое.
    Подлечил ваш калькулятор
    1. Oles 18 ноября 2015, 18:43 # 0
      Молодчина! Респект и уважуха!
      1. essential 19 февраля 2016, 16:52(Комментарий был изменён) # 0
        Не работает :(

        User defined type not defined?

        Function ParserXML(ByVal iAdress As String)
        Dim XMLDoc As New MSXML2.DOMDocument, nodeList As MSXML2.IXMLDOMNodeList
        Как подлечить?

        1. essential 06 марта 2016, 09:20 # 0
          Подчинил.
          Надо подключить библиотеку Microsoft XML v3.0 вместо Microsoft XML v6.0, которая включена по умолчанию на Office.
          Для этого в редакторе MS VBA выберите Tools > References и найдите в списке Microsoft XML v3.0.
          1. Maxim 08 марта 2016, 20:20(Комментарий был изменён) # 0
            essential, спасибо!
            Скорее всего, зависит от версии вашего офиса. У меня на Excel 2007 и Excel 2013 код из файла работает без каких-либо модификаций. Но подключаемые по умолчанию библиотеки, конечно, могут отличаться у разных пользователей.
            Еще, как вариант, можно
            Dim XMLDoc As New MSXML2.DOMDocument, nodeList As MSXML2.IXMLDOMNodeList
            заменить на
            Dim XMLDoc As Object, nodeList As Object
            Set XMLDoc = CreateObject("MSXML2.DOMDocument")
            Тогда в MS VBA «Tools > References» вообще можно ничего не подключать.

            На днях выложу обновленную версию макроса, без привязки к References + с возможностью выбора режима работы: Yandex или Google сервис использовать для анализа.
            1. Kos 09 марта 2016, 23:44 # 0
              круто! ждемс!
              1. Александр 16 ноября 2016, 15:20 # 0
                На днях выложу обновленную версию макроса
                А есть???
          2. Александр 15 марта 2016, 17:59 # 0
            Не работает.
            Выдает ошибку Run-Time error 13.
            Я в этом деле совсем 0.
            Подскажите как это работает?
            1. Maxim 17 марта 2016, 07:07 # 0
              Уточните, какая у вас версия ОС (Windows 7, Windows 8, Mac OC и тп), разрядность системы (Windows x64 или x32), версия Microsoft Office (2007, 2010, 2013, 2016).
              1. Владимир 29 ноября 2016, 07:54 # 0
                У меня Windows 10 64х, на которой стоит MS Office 2010.
                Сначала макрос выдавал ошибку User defined type not defined. После замены Microsoft XML v6.0 на Microsoft XML v3.0, как описано выше, появилась ошибка Run-Time error 13 и жёлтым выделяется строка GlobalArray(i, 2) = tmpArray(2).
            2. Дмитрий 21 мая 2016, 00:23 # 0
              Спасибо! Очень выручили! :)
              1. Алексей 25 мая 2016, 18:19 # 0
                Спасибо, отличная работа.
                Скажите, утилит полностью бесплатен? Может быть есть какой то срок работы или иное ограничение?
                Можно ли получить бан от Yandex?
                1. Maxim 05 июня 2016, 11:17 # 0
                  Сама утилита (макрос) бесплатная. БАН от яндекса не получите, разве что при большом количестве адресов временное (суточное) ограничение на выгрузку гео-данных. Но по опыту, данное ограничение достаточно велико, чтобы его превысить.
                2. Ленар 09 июня 2016, 09:32 # 0
                  Добрый день!
                  Подскажите пожалуйста, что в макросе необходимо доработать, чтобы произвести обратную операцию — получение адреса по введенным координатам?
                  Заранее благодарен!
                  1. Maxim 09 июня 2016, 22:22(Комментарий был изменён) # 0
                    Добрый день.
                    Первое, это изменить строку запроса (вместо адреса передавать координаты):
                    iURL = "http://geocode-maps.yandex.ru/1.x/?geocode=" & iAdress & "&key=...==&results=1"
                    И немного доработать функцию ParserXML(), чтобы она корректно извлекала полученные значения адресов.
                    1. Варвара 17 ноября 2016, 12:55 # 0
                      А есть уже работающая версия перевода координат в адреса? Из адресов прекрасно переводит в координаты, но очень нужно наоборот!
                      1. George 16 февраля 2017, 22:28 # 0
                        Приветствую, Maxim!!!

                        Можно все-таки немного про то как и что поменять в функции ParserXML(), чтобы она корректно извлекала полученные значения адресов???
                        Я не программист, а очень нужна эта обратная функция

                        Заранее благодарен!
                        1. Maxim 17 февраля 2017, 00:05 # 0
                          Для не программиста, объяснение будет сложноватое. Попробуйте так, возможно, получится: excelstore.pro/examples-of-work/internet/getting-geo-coordinates-with-yandex-maps.html#comment-76
                          Я же, в свою очередь, в ближайшее время (на этой недели) таки найду время и доработаю макрос.
                    2. Елена 21 июня 2016, 19:28(Комментарий был изменён) # 0
                      В апреле скачивала всё работало (правда после замены в соответствии с комментарием от 08 марта 2016, 20:20), а теперь не хочет…
                      Пишет run-time error '13' type mismatch
                      Выделяет жёлтым строку: GlobalArray(i, 2) = tmpArray(2) 'адрес по версии яндекса
                      Можете помочь?
                      виндоус 10, 32 разрядная опер.система процессор x64, Офис 2007

                      ПОЖАЛУЙСТА!
                      1. Симон 30 июня 2016, 10:35 # 0
                        Аналогичная проблема. Офис 2010
                      2. Станислав 01 июля 2016, 13:23 # 0
                        Посоветуете что-нибудь Елене и Симону?
                        То же самое: месяц назад всё работало, сегодня нет.

                        виндоус ХР, 32, офис 07
                        Спасибо
                        1. Владимир 03 июля 2016, 01:53 # 0
                          присоединяюсь к вопрошающим «несоответсвие типов», помогите при оказии советом
                          1. Владимир 03 июля 2016, 09:08 # 0
                            Разобрался в чем проблема — ключ разработчика невалиден, используйте либо свой уже созданный, либо получите по ссылке
                            : tech.yandex.ru/maps/keys/

                            Ключик необходимо вставлять в строку
                            iURL = "http://geocode-maps.yandex.ru/1.x/?geocode=" & iAdress & "&key=AMsJFVIBAAAA6b1EZgIARt3xxKvqDXFSuzYNLrfjJlyduEsAAAAAAAAAAABtEHh2MEhJiiNct5e0zKVyv2ggKA==&results=1"
                            — замените набор символов
                            AMsJFVIBAAAA6b1EZgIARt3xxKvqDXFSuzYNLrfjJlyduEsAAAAAAAAAAABtEHh2MEhJiiNct5e0zKVyv2ggKA==
                            на свой ключ и всё заработает!

                            Кто скачал файл впервые

                            Dim XMLDoc As New MSXML2.DOMDocument, nodeList As MSXML2.IXMLDOMNodeList
                            заменить на
                            Dim XMLDoc As Object, nodeList As Object
                            Set XMLDoc = CreateObject("MSXML2.DOMDocument")
                            1. Станислав 05 июля 2016, 11:34 # 0
                              Что-то ничего не выходит: жму кнопку «Получить координаты», выскакивает окошко «Данные успешно загружены», а на самом деле в ячейках Широта и Долгота — пустота
                              Владимир, а вы код здесь не выложите, посмотреть, может у меня чё не так.
                            2. Дмитрий 05 июля 2016, 17:42 # 0
                              После всех изменений наконец-то все заработало. Огромное спасибо. Работа сокращена с 6 месяцев до 2-х недель.
                              Бывают адреса, которые не читаются и на таких действительно остаются пустые строчки. Но такое очень редко
                              1. Дмитрий 07 июля 2016, 08:49 # 0
                                Тоже самое что и у Станислава: жму кнопку «Получить координаты», выскакивает окошко «Данные успешно загружены», а на самом деле в ячейках Широта и Долгота — пустота
                                Помогите разобраться…
                                1. Ленар 07 июля 2016, 17:22 # 0
                                  Добрый день дамы и господа!
                                  Ввод валидного API ключа действительно помог. Заметил в файле «GeoYandex, v.2.xlsb» ошибку: столбец C — должен быть «долготой», столбец D — «широтой».
                                  1. Гость 27 июля 2016, 11:09 # 0
                                    Выдает ошибку
                                    run-time error '13' type mismatch
                                    1. Валерий 05 августа 2016, 23:38 # 0
                                      И ключ обновлял и строки менял, вчера работало — сегодня уже нет
                                      run-time error '13' type mismatch
                                      Помогите пофиксить
                                      1. Валерий 07 августа 2016, 19:39 # 0
                                        Разобрался, ограничения яндекса на 25000 запросов в сутки
                                      2. Максим 02 сентября 2016, 18:45 # 0
                                        вставил адрес «Россия, Москва, Малая Грузинская улица, 38», после запуска в строке появился адрес «Россия, Московская область, Балашиха, потребительский кооператив садоводческое товарищество Металлург-1, Малая Грузинская улица»
                                        Но это совсем в другой стороне… КАК БЫТЬ?
                                        1. Алексей 29 января 2017, 18:10 # 0
                                          Аналогичная проблема, не те координаты по некоторым адресам выдает. Точнее определяется адрес не точно, но если тот же адрес через сам яндекс вбивать, все ок.
                                          Как решить эту неточность?
                                          1. Maxim 13 марта 2017, 16:04 # 0
                                            Проверьте, как будет работать на новой версии макроса: excelstore.pro/examples-of-work/internet/get-the-address-and-coordinates-of-yandex-and-google.html
                                          2. MAX 13 сентября 2016, 19:27(Комментарий был изменён) # 0
                                            может подскажет кто? от Яндекса получаю две записи по одному запросу "-found-2-/found-" и данные выводятся по последней, НО правильная первая запись… Что поменять… не могу что-то разложить эти данные.
                                            1. Diana 30 сентября 2016, 09:47 # 0
                                              А как добавить столбец не только с координатами, но и областью. А еще точность нужна (до какой точности он определяет).

                                              И 1000 строк геокодер просматривает 10-15 минут. Можно ли ускорить работу геокодера регулируя количество запросов по 1 адресу?

                                              Помогите пж.
                                              1. Maxim 17 февраля 2017, 00:01 # 0
                                                В excel-e нет многопоточности.
                                                Чтобы одномоментно делать много запросов, нужно другое решение (не vba).
                                              2. Diana 30 сентября 2016, 10:08 # 0
                                                Область, и район.
                                                1. Ленар 10 октября 2016, 22:02 # 0
                                                  Поделюсь наблюдением: для получения адреса позиции по имеющимся координатам, достаточно в поле исходного адреса ввести через запятую долготу и широту. В ячейке «Адрес Yandex» получим запрашиваемый адрес.
                                                  1. Артем 26 октября 2016, 06:01 # 0
                                                    Добрый день!
                                                    Ключ поменял,Dim XMLDoc As New MSXML2.DOMDocument, nodeList As MSXML2.IXMLDOMNodeList заменил на
                                                    Dim XMLDoc As Object, nodeList As Object
                                                    Set XMLDoc = CreateObject(«MSXML2.DOMDocument»)
                                                    Все равно желтым посвечивает:
                                                    GlobalArray(i, 2) = tmpArray(2) 'адрес по версии яндекса
                                                    1. Maxim 13 марта 2017, 16:04 # 0
                                                      Добрый день. Проверьте, как будет работать на новой версии макроса: excelstore.pro/examples-of-work/internet/get-the-address-and-coordinates-of-yandex-and-google.html
                                                      Если всё-равно не работает, уточните адрес, который пытаетесь геокодировать.
                                                    2. Алиса 28 декабря 2016, 17:03 # 0
                                                      После всех изменений пишет что данные загружены, но их нет.
                                                      Подскажите в чем дело.
                                                      1. Арсений 24 января 2017, 18:01 # 0
                                                        Спасибо!
                                                        1. Maxim 19 февраля 2017, 02:59(Комментарий был изменён) # 0
                                                          Опубликовал новую версию макроса для работы с координатами и адресами.
                                                          Версия существенно доработана и улучшена. Все комментарии в данном посте постарался максимально учесть.
                                                          Ссылка на страницу: excelstore.pro/examples-of-work/internet/get-the-address-and-coordinates-of-yandex-and-google.html
                                                          1. Роман 03 мая 2017, 12:29 # 0
                                                            Максим, добрый день!
                                                            Скачал последнюю версию файла: ошибка уже не возникает, но в результате выдаётся «нет данных».
                                                            Как это можно поправить?
                                                            Спасибо.
                                                            1. Maxim 06 мая 2017, 17:06 # 0
                                                              Добрый день, Роман.
                                                              Актуальная версия макроса здесь: excelstore.pro/examples-of-work/internet/get-the-address-and-coordinates-of-yandex-and-google.html
                                                              Об этом сказано и в последнем комментарии на данной странице и в тексте самого описания.
                                                            2. Елена 05 мая 2017, 15:47(Комментарий был изменён) # 0
                                                              Вау спасибо вам большое за такой макрос!!!
                                                              Я про версию 3.
                                                              (Этот вариант не выдает данных)
                                                              1. Maxim 06 мая 2017, 17:07 # 0
                                                                Да, актуальная версия макроса опубликована здесь: excelstore.pro/examples-of-work/internet/get-the-address-and-coordinates-of-yandex-and-google.html
                                                                Если макрос пригодился, буду признателен, если поделитесь ссылкой на наш сайт или на указанную страницу с программой (в соц. сетях, на форумах и тд.)
                                                              2. Владимир 29 мая 2017, 12:47 # 0
                                                                Просто блистательно!!!
                                                                1. Паша 02 февраля 2018, 18:25 # 0
                                                                  Спасибо автору!!! Полезнейшая вещь.
                                                                  1. Павел 28 февраля 2018, 11:08 # 0
                                                                    А можно ближайшее метро получить?
                                                                    1. Maxim 28 февраля 2018, 15:58 # 0
                                                                      В текущей реализации макроса нет, т.к. возвращаемые результаты гугла/яндекса не содержат данную информацию. Только если через другой API. Могу доработать за плату по вашему заданию. Если актуально, пишите на info@excelstore.pro
                                                                    2. Марк 10 октября 2018, 19:52 # 0
                                                                      СПАСИБО ОГРОМНОЕ!!!
                                                                      ОЧЕНЬ ВЫРУЧИЛИ!
                                                                      1. Maxim 11 октября 2018, 09:25 # 0
                                                                        Пожалуйста
                                                                      2. Евгений 30 января 2019, 10:48 # 0
                                                                        Спасибо большое за макрос, однако с ним у меня есть проблемы: широта и долгота адресов находятся похоже не верно, т.к. при загрузке в яндекс-карты показывает туркменистан, вместо центрального региона России, либо я что-то не так делаю
                                                                        1. Maxim 30 января 2019, 22:04 # 0
                                                                          Возможно, координаты надо поменять местами.
                                                                          Попробуйте более новый макрос в соседнем посте: excelstore.pro/examples-of-work/internet/get-the-address-and-coordinates-of-yandex-and-google.html
                                                                        2. Савелий 08 февраля 2019, 22:37(Комментарий был изменён) # 0
                                                                          Большое спасибо за макрос, очень помогло.
                                                                          1. Виктор 23 апреля 2019, 11:54 # 0
                                                                            Добрый день, подскажите а как можно пропарсить и вывести только километраж расстояния между двумя адресами через тот же яндекс маршруты
                                                                            1. Виктор 23 апреля 2019, 12:01(Комментарий был изменён) # 0
                                                                              GET https: //api.routing.yandex.net/v1.0.0/distancematrix
                                                                              ? apikey= & origins=<lat1,lon1|lat2,lon2|...>
                                                                              & destinations=<lat1,lon1|lat2,lon2|...>
                                                                              & [mode=]
                                                                              & [departure_time=]
                                                                              Как реализовать это в эксель помогите пожалуйста
                                                                              1. Maxim 24 апреля 2019, 00:08 # 0
                                                                                Если вы скачаете макрос из данной темы, то там очень очень наглядно показан принцип получения данных из Яндекса. Да, там в части координат, но для примера более чем достаточно. В вашем случае все также, только немного отредактировать парсинг получаемого XML.

                                                                                Но самое сложное в другом. API Яндекс.Маршрутизации платный. Если у вас таковой доступ есть, то все решается просто (как и указал выше). В ином случае сделать быстро и удобно через этот API — никак.

                                                                                Тогда, как вариант, делать отрисовку в браузере и потом из браузера выдергивать полученный результат. Но это довольно-таки сложно. Проще найти альтернативный сервис, который отдает результат сразу в подходящем нам формате (json/xml). Таких сервисов не много, но они есть. Если, конечно, у вас нет привязки конкретно к яндексу.
                                                                                1. Maxim 24 апреля 2019, 00:14 # 0
                                                                                  И добавлю также.
                                                                                  Если километраж нужен по прямой, то расчет можно сделать и исключительно математически, предварительно получив координаты двух точек макросом из этой темы.

                                                                                  Если же маршрут нужен с учетом автодорог, то тогда одной математики мало и уже надо подключать какой-либо API.
                                                                                  1. Виктор 26 апреля 2019, 10:02 # 0
                                                                                    Спасибо за ответ, да расчет нужен именно с учетом автодорог, хотелось бы обойтись без их api, 120к руб. как то много для того что бы бухгалтерия делала около 60 запросов в мес. Хотелось бы либо выдернуть через браузер либо как вы сказали сервисами, но это опять же парсеры, скорее всего платные?
                                                                                    1. Aleksey 30 мая 2019, 00:14(Комментарий был изменён) # 0
                                                                                      120k — да уж(((
                                                                                2. Виктор 26 апреля 2019, 12:32 # 0
                                                                                  {
                                                                                     "destination_addresses" : [ "Химки, Московская обл., Россия" ],
                                                                                     "origin_addresses" : [ "Москва, Россия" ],
                                                                                     "rows" : [
                                                                                        {
                                                                                           "elements" : [
                                                                                              {
                                                                                                 "distance" : {
                                                                                                    "text" : "20,5 км",
                                                                                                    "value" : 20472
                                                                                                 },
                                                                                                 "duration" : {
                                                                                                    "text" : "34 мин.",
                                                                                                    "value" : 2060
                                                                                                 },
                                                                                                 "status" : "OK"
                                                                                              }
                                                                                           ]
                                                                                        }
                                                                                     ],
                                                                                     "status" : "OK"
                                                                                  }
                                                                                  не могу что то догнать как регуляркой вывести километраж
                                                                                  regex.Pattern = "distance(?:.|\n)*?""text"".*?([0-9]+)"
                                                                                  так не работает
                                                                                  1. Maxim 07 мая 2019, 01:56 # 0
                                                                                    Здравствуйте.
                                                                                    В данном случае можно не регуляркой, а простым поиском. Например, через функции Instr и Mid.
                                                                                  2. Arty 11 июля 2019, 14:35(Комментарий был изменён) # 0
                                                                                    Сломался макрос — постоянно выдаёт ошибку [скрытый контент]
                                                                                    Утром ещё работал. Может апиха у яндекса изменилась?
                                                                                    1. Maxim 12 июля 2019, 01:28 # 0
                                                                                      Способ решения 1 — Прочитайте комментарии к этой теме, вопрос уже поднимался и я не раз отвечал.
                                                                                      Способ решения 2 — Если не помогло, вернитесь к варианту #1 еще раз.
                                                                                    2. Vinki 11 июля 2019, 15:56 # 0
                                                                                      Аналогичная ошибка, лавочка закрыта?)
                                                                                      1. Maxim 12 июля 2019, 01:31 # 0
                                                                                        Аналогичный ответ. См. мой комментарий выше.
                                                                                      2. Олег 05 августа 2019, 10:08 # 0
                                                                                        Сдается мне API ключ протух. Не пашет.
                                                                                        1. Maxim 06 августа 2019, 22:07(Комментарий был изменён) # 0
                                                                                          Сдается мне, вы не прочитали описание макроса до конца.
                                                                                          В описании сказано — «На текущий момент на сайте опубликована новая версия данного макроса, существенно доработанная и улучшенная». И ссылка там же приведена.

                                                                                          А даже если бы дело было в API ключе, вы всегда его можете заменить на свой.
                                                                                        2. Блинчик 10 октября 2019, 14:08 # 0
                                                                                          Огромное спасибо Максиму!
                                                                                          У меня сработала только альтернативная 4-тая версия, где необходимо вставить ключ Яндекс API
                                                                                          yadi.sk/d/XH8ZoGu8eijqHA
                                                                                          Но главное, что сработала =)
                                                                                          1. Maxim 11 октября 2019, 15:38 # 0
                                                                                            Да, в шапке я писал, что данная версия макроса уже устарела. 4-ая — самая актуальная и рабочая.
                                                                                            В ближайшие дни скорректирую описание в данной теме, чтобы не люди не путали версии макроса.