Можете ли вы преобразовать адрес в почтовый индекс в электронной таблице?

У электронных таблиц Google есть ряд функций для внешних данных . Если вы можете найти (или создать) сайт, который выполняет поиск путем передачи параметров, вы можете поместить формулу, подобную этой, в:

Cell A1 (Address): 123 Main St Cell B1 (City): Springfield Cell C1 (State): MO  Cell D1 (combined address): =concatenate(A1,B1,C1)  Cell E1 (imported zip code):  =importData(concatenate("http://zipfinder.com/search?addr=",D1)) 

Это гипотетическая идея. Конкатенация, importData и другие функции для внешних данных существуют в электронных таблицах Google. "zipfinder.com" не существует. Есть много сайтов, которые помогут вам найти почтовый индекс по адресу. Самое сложное - найти тот, который принимает адресные данные в URL и возвращает что-то достаточно простое для использования в электронных таблицах Google.

В электронную таблицу не будет встроено ничего, поскольку базы данных с проверенными адресами очень дороги.

Вы можете найти онлайн-сервис, который сделает это за вас. Например, в Великобритании веб-сайт Royal Mail позволяет вам искать почтовые индексы (в Великобритании эквивалент почтовых индексов), но вы можете ограничиться 15 поисками в день. Это для личного использования. Бизнес получит больше - но вы должны зарегистрироваться и заплатить за это.

Подобные услуги будут существовать в других странах.

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

Это трудно сделать в Excel. Вам нужно будет вызвать веб-сервис с использованием инструментария SOAP или использовать Visual Studio. Нужно задаться вопросом, что думает Microsoft.

Это гораздо проще сделать в электронной таблице Google Docs, а затем экспортировать в Excel:

Вы можете использовать веб-сервис XML GeoCoder.ca для поиска почтового индекса по адресу. В Google Docs вы бы использовали эту функцию:

=importXML("http://geocoder.ca/?geoit=xml&showpostal=1&locate=" & A2,"//postal") 

(где A2 - адрес улицы.)

Вы также можете получить широту и долготу следующим образом:

=importXML("http://geocoder.ca/?geoit=xml&showpostal=1&locate=" & A2,"//geodata") 

Обратите внимание, что GeoCoder ограничивает количество запросов в день на бесплатное обслуживание.

Да. Теперь вы можете найти почтовые индексы в своем документе Документов Google, используя следующий URL:

Шаг 1: На этой целевой странице введите адрес, который вы хотите найти.

Шаг 2: Я искал адрес «1245 5TH ST» в Вашингтоне, округ Колумбия, и URL выглядел так:

https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=0&companyName=&address1=1245+5TH+ST&address2=&city=Washington&state=DC&urbanCode=&postalCode=&zip= 

Шаг 3: Скопируйте и вставьте следующую формулу и замените CELL, CITY, STATE:

=Mid(importHTML(CONCATENATE("https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=0&companyName=&address1=",CELL,"&address2=&city=CITY&state=STATE&urbanCode=&postalCode=&zip="),"list",15),Find("DC",importHTML(CONCATENATE("https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=0&companyName=&address1=",CELL,"&address2=&city=CITY&state=STATE&urbanCode=&postalCode=&zip="),"list",15))+2,11) 

Помните, что URL-адрес шага 3 должен совпадать с URL-адресом шага 2. Ячейка будет динамической в ​​зависимости от целевого значения ячейки.

Функция на шаге 3 извлекает 11 символов после значения STATE. Следовательно, вам придется настроить MID-функцию, чтобы получить правильные значения. Это отлично сработало для меня.

И последнее: Google позволяет использовать только 50 функций импорта на листе. Таким образом, с помощью вышеуказанного метода вы можете найти только 25 почтовых индексов за один раз.

You can write a custom Apps Script function with the Google Maps service, using a similar approach to this answer. To do this, open Tools...Script editor... and paste this function in:

function getZIP(address) { var geo = Maps.newGeocoder().geocode(address); var resultComponents = geo.results[0].address_components; for (var i = 0; i < resultComponents.length; i++) { if (resultComponents[i].types.indexOf('postal_code') > -1) { return resultComponents[i].long_name; } } } 

You can then call this in a spreadsheet cell, for example, it correctly returns 20500 for the White House:

=getZIP("1600 Pennsylvania Ave. NW, Washington, DC") 

Unlike some other approaches, it also works for non-US locations, e.g. it correctly returns 75007 for the Eiffel Tower:

=getZIP("Champ de Mars, 5 Avenue Anatole France, Paris, France") 

В Канаде веб-сайт Канадской почты позволяет искать почтовые индексы по адресу (а я не знаю ни одного дневного лимита), но, к сожалению, это не поможет вам с интеграцией в вашу электронную таблицу. Я заинтересован в том, чтобы увидеть потенциальные решения для этого. Я всегда использовал ручной поиск почтового индекса для тех, кого я не знаю.

В Великобритании вам потребуется доступ к файлу почтовых индексов (PAF), в котором перечислены все адреса в стране. Прямой доступ к PAF стоит денег, а не незначительной суммы. Мы платим около 200 фунтов стерлингов за место в год за неограниченный доступ к обновленным данным PAF, но затем мы ежемесячно выбрасываем более 10 000 запросов.

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

Даже если бы вы могли найти сайт, который позволил бы RESTful запрашивать их данные, чтобы вставить zip-файл непосредственно в электронную таблицу, они, без сомнения, установили бы строгие ограничения на количество запросов, которые могли бы быть сделаны в любой установленный период.

Существует несколько онлайн-сервисов, которые обрабатывают электронную таблицу Excel (или CSV / файл с разделителями табуляции) и добавляют недостающие ZIP-коды. Цена может варьироваться от нескольких долларов до 100 долларов США + в зависимости от поставщика услуг, некоторые из которых имеют сборы за установку, сборы за обработку и сборы за срочные заказы.

Некоторые поставщики услуг могут вернуть обработанный файл вам в течение нескольких минут, в то время как другие, которые заявляют о «срочной обработке», могут занять более 3 рабочих дней. Исходя из вашего вопроса, вы не хотите инвестировать в покупку программного обеспечения и тратить тысячи долларов на программное решение на месте. Если это так, то лучшим выбором будет онлайн-провайдер.

В интересах полного раскрытия информации я являюсь основателем SmartyStreets. Мы предлагаем полностью автоматизированную онлайн- проверку адреса . Вы можете перетащить файл Excel на наш веб-сайт, и мы обработаем его и вернем вам в течение одной или двух минут в зависимости от размера.