W poradniku Co nowego w programie Excel 2013 opisanych zostało wiele nowych funkcjonalności programu Excel 2013. Podkreślałem tam, że większość z nowości jest związanych ze sferą wizualizacji danych (np. cały dodatek PowerView) bądź raportowania (patrz nowości dotyczące tabel przestawnych w Excelu 2013 czy PowerPivot). Wspomnieć należy, że do tego doszło niemało, bo 51, nowych funkcji, w takich kategoriach jak statystyczne, inżynieryjne czy matematyczne. Są też takie, które dotyczą sfery Internetu. Z nich wybrałem dziś 2, na bazie których pokażę proste geokodowanie w Excelu, które szczególnie może się przydać użytkownikom Mapy Polski Excel.

[grwebform url="https://app.getresponse.com/view_webform_v2.js?u=N3vX&webforms_id=9648602" css="on" center="off" center_margin="0"/]

Co to są webserwisy i język XML?

Usługa internetowa (ang. web service) jest systemem dostępnym np. w Internecie, zaprojektowanym w celu umożliwienia interakcji, takich jak wymiana danych czy wywoływanie procedur (poleceń, zapytań) pomiędzy komputerami w sieci. Oprogramowanie identyfikowane jest przez adres URL, do którego dostęp można uzyskać przy pomocy języka XML, umożliwiając pobieranie i obsługiwanie przy pomocy innego oprogramowania – w naszym przypadku będzie to właśnie Excel.

XML (ang. Extensible Markup Language, w wolnym tłumaczeniu Rozszerzalny Język Znaczników) to po prostu uniwersalny język służący do przesyłania danych.

O co więc tu chodzi? W najprostszym ujęciu wysyłamy jakieś pytanie do usługi internetowej, która nam na nie odpowiada. Odpowiedź przychodzi jednak bardzo bogata w treść i naszym zadaniem będzie wyszukać w niej to, co nas interesuje. Pytać będziemy z użyciem funkcji WEBSERVICE, a odpowiedź filtrować z użyciem funkcji FILTERXML.

Geokodowanie z API Google Maps z użyciem funkcji WEBSERVICE i FILTERXML

Rozpatrzmy najczęstszy przypadek związany z geokodowaniem – mamy adres i chcemy go nanieść na mapę. W tym celu potrzebujemy przypisać do adresu współrzędne geograficzne: szerokość i długość geograficzną. Gdy wpisujemy adres na stronie Google Maps, strona robi to automatycznie. My wykonamy dokładnie to samo z poziomu Excela.

Składnia funkcji WEBSERVICE jest bardzo prosta, bo zawiera tylko adres url, który wpisuje się w cudzysłowach. Prosto zbudowany jest też API Google Maps, który wymaga podania poniższego linku i adresu:

http://maps.googleapis.com/maps/api/geocode/xml?address=…

Adres umieszczam w komórce A1, więc finalna postać funkcji dla wygląda tak:

Funkcja zwraca mi wszystkie dane, które oferuje usługa Google Maps, mające postać drzewa XML (wartość komórki A2):

„<?xml version=””1.0″” encoding=””UTF-8″”?>
<GeocodeResponse>
<status>OK</status>
<result>
<type>street_address</type>
<formatted_address>Wiejska 4, 00-489 Warszawa, Poland</formatted_address>
<address_component>
<long_name>4</long_name>
<short_name>4</short_name>
<type>street_number</type>
</address_component>
<address_component>
<long_name>Wiejska</long_name>
<short_name>Wiejska</short_name>
<type>route</type>
</address_component>
<address_component>
<long_name>Śródmieście</long_name>
<short_name>Śródmieście</short_name>
<type>sublocality_level_1</type>
<type>sublocality</type>
<type>political</type>
</address_component>
<address_component>
<long_name>Warszawa</long_name>
<short_name>Warszawa</short_name>
<type>locality</type>
<type>political</type>
</address_component>
<address_component>
<long_name>Warszawa</long_name>
<short_name>Warszawa</short_name>
<type>administrative_area_level_2</type>
<type>political</type>
</address_component>
<address_component>
<long_name>mazowieckie</long_name>
<short_name>mazowieckie</short_name>
<type>administrative_area_level_1</type>
<type>political</type>
</address_component>
<address_component>
<long_name>Poland</long_name>
<short_name>PL</short_name>
<type>country</type>
<type>political</type>
</address_component>
<address_component>
<long_name>00-489</long_name>
<short_name>00-489</short_name>
<type>postal_code</type>
</address_component>
<geometry>
<location>
<lat>52.2254970</lat>
<lng>21.0277350</lng>
</location>
<location_type>ROOFTOP</location_type>
<viewport>
<southwest>
<lat>52.2241480</lat>
<lng>21.0263860</lng>
</southwest>
<northeast>
<lat>52.2268460</lat>
<lng>21.0290840</lng>
</northeast>
</viewport>
</geometry>
<partial_match>true</partial_match>
<place_id>ChIJ0UGLDfvMHkcR5yXnGkdZgks</place_id>
</result>
</GeocodeResponse>
”

Naszym zadaniem jest teraz odnaleźć gałęzie, na których umieszczono szerokość (lat) i długość geograficzną (lng) i podstawić je do funkcji FILTERXML. Np. element lat znajduje się na gałęzi GeocodeResponse w podgałęzi result > geometry > location > lat.

Finalnie otrzymamy w Excelu następujące wyniki:

A mając te współrzędne, możemy już nanieść punkty do programu Mapa Polski Excel wraz z dodatkowymi danymi, jak opisałem choćby w poradniku Jak przedstawić na mapie bazę adresów po geokodowaniu?

W usłudze Google Maps znaleźć można m.in. kody pocztowe, województwo czy szczegóły podziału administracyjnego, nazywane w strukturze XML address component.

Przedstawiony web service geokodowania Google Maps dla zastosowań niekomercyjnych ma obecnie następujące ograniczenia:

2500 zapytań / dobę
5 zapytań na sekundę

Więcej o tej usłudze, a także ofercie Google dla biznesu przeczytasz na stronie dla deweloperów Google Maps API.

Podsumowanie

Przestawiony przykład pokazuje tylko wycinek możliwości nowych funkcji Excela. Jest to ważny krok dla osób, które bez umiejętności programowania będą mogły komunikować się za pomocą funkcji WEBSERVICE z dostępnymi usługami internetowymi. Gdy odpowiedź wraca w postaci innej niż XML, (np. JSON – format tekstowy, bazujący na podzbiorze języka JavaScript) zawsze można wykorzystać funkcje tekstowe, żeby wyłuskać to, co nas interesuje.