Útvonaltervezés Excellel

Nemrég Elsőbbségi PowerPivot és Excel Dashboard workshopot tartottam és miközben együtt ebédeltünk a résztvevőkkel, az egyikük felvettette, hogy nem tudok-e valamilyen megoldást az Excelben 2 cím távolságának a meghatározására.

A probléma a következő volt. Adott egy megrendelő, akinek az igényét két raktárból is ki lehetne elégíteni. Mivel az egyik raktárban nem volt elegendő készlet ezért a megrendelést azonnal csak egy másik címről lehetett volna kielégíteni. Arra kérték az elemzőket, hogy mutassák ki - és építsék be az árazó modellbe - hogy a másik címről történő kiszolgálás milyen megterhelést (károsanyag kibocsátás többletet) jelent a környezetre.

Erre a problémára keresett megoldást az egyik résztvevő. Ahogy beszélgettünk kiderült, hogy a legjobb megoldás

  1. ingyenes lenne és
  2. Excelbe lenne integrálva

Ahogy tovább beszélgettünk mondta, hogy a Google Térképben (Goole Maps) meg tudja csinálni. Erre nekem bevillant, hogy az Excel 2013-ban megjelent a webservice-ek függvényekből történő hívásának lehetősége és össze is állt fejben a megoldás. Rá pár napra pedig jött ez a cikk: Bringing Location Information Into Excel 2013 With The WebService() Function ahol gyakorlatilag lépésről lépésre le van írva minden. A lényeg:

Google Distance Matrix API

A Google Distance Matrix API visszaadja 2 cím távolságát. Gyakorlatilag úgy működik, mint az útvonaltervező:

Google útvonaltervező
Google útvonaltervező

Meg kell adni a Google Distance Matrix API-nak, hogy

  1. Honnan
  2. Hova
  3. Mivel (Kocsi, bringa, gyalog) megyünk
  4. és hogy el akarjuk-e kerülni a fizetős szakaszokat, stb.

WEBSZOLGÁLTATÁS() Excel függvény

A WEBSZOLGÁLTATÁS() nevű (angolul WEBSERVICE() ) Excel függvény meg tudja hívni a Google Distance Matrix API-t és a lekérdezés eredményét visszaadja xml formátumban:

Google Distance Matrix API output
Budapest - Miskolc távolsága a Google Distance Matrix API outputjaként. URL: http://maps.googleapis.com/maps/api/distancematrix/xml?origins=budapest&destinations=Miskolc&mode=driving&sensor=false

XMLSZŰRÉS()

Ezek után már csak annyi a dolgunk, hogy a visszakapott xml-ből ki kell hámoznunk a távolságot. Ezt az XMLSZŰRÉS() (angolul XMLFILTER() ) Excel függvénnyel tudjuk megtenni:
XMLSZŰRÉS(Google Distance Matrix API outputja jön ide; "/DistanceMatrixResponse/row/element/duration/text")

Útvonaltervezés Excellel


Útvonaltervezés Excellel

A teljes megoldás letölthető innen: Útvonaltervező Excel

MNB árfolyamok?

Rászántam jópár órát, hogy a WEBSZOLGÁLTATÁS() Excel függvénnyel le tudjam kérdezni az MNB árfolyamokat az MNB webservice-én keresztül, de nem ment. Úgyhogy maradt a 6 évvel ezelőtt megírt Integration Service-es megoldás...

hozzászólás

Zseniális. Köszönöm az infót.

Bár technikailag frappáns megoldás, azért arra érdemes figyelni, hogy az ingyenes Google szolgáltatás használatának vannak feltételei. 1. Maximum 2500 elem lekérdezése 24 órán belül (az elemek száma = a indulási címek száma * cél címek száma), ez 50 különböző útvonal lekérdezését jelenti (50*50=2500) 2. Az eredményeket kötelező Google Map-on megjeleníteni! "Use of the service in an application that doesn't display a Google map is prohibited." Részletek itt: https://developers.google.com/maps/documentation/distancematrix/ Üdv, Gábor

Új hozzászólás