Šodien Power BI Meetup grupā stāstīju par kādu savu projektu, kurā izmantoju Excel Formas, VBA kodu, Power Query un citus rīkus datu vākšanai un analīzei.
Gribu padalīties ar vienu atradumu internetā - VBA funkciju, ar kuru var noteikt attālumu starp divām adresēm. Kā demonstrēju nodarbībā, samērā labi strādā Latvijas adreses un Indeksi. Piemēram, noteikt attālumu starp diviem indeksiem "LV-1001" un "LV-1013" vai starp "Rīga" un "Daugavpils". Tā kā meklētājs izmanto Google Maps, tad jārēķinās ar to, ko Google spēj un nespēj. Pamēģiniet paši!
PS komerciālai izmantošanai (lasi - tūkstošiem adrešu meklēšanas pieprasījumiem dienā), jāreģistrē Google Key.
Lai šo funkciju izmantotu, Tavā Excel jābūt iespējotām Macro komandām. Spied Alt+F11, Iekopē šo kodu Pesonal.xlsb zem Modules sadaļas.
'Calculate Google Maps distance between two addresses Public Function GetDistance(start As String, dest As String) Dim firstVal As String, secondVal As String, lastVal As String firstVal = "http://maps.googleapis.com/maps/api/distancematrix/json?origins=" secondVal = "&destinations=" lastVal = "&mode=car&language=pl&sensor=false" Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = firstVal & Replace(start, " ", "+") & secondVal & Replace(dest, " ", "+") & lastVal objHTTP.Open "GET", URL, False objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)" objHTTP.send ("") If InStr(objHTTP.responseText, """distance"" : {") = 0 Then GoTo ErrorHandl Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = """value"".*?([0-9]+)": regex.Global = False Set matches = regex.Execute(objHTTP.responseText) tmpVal = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator)) GetDistance = CDbl(tmpVal) Exit Function ErrorHandl: GetDistance = -1 End Function
Tālāk Excel šunā ievadi formulu pēc principa:
=GETDISTANCE("adrese1";"adrese2")
Tā kā pats šo neesmu izdomājis, tad lūk atsauce uz rakstu, kurā to atradu. Kā redzams rakstā, tad iespējamas vairākas variācijas - attāluma noteikšana, laika noteikšana, attālums starp koordinātām utt.
http://analystcave.com/excel-calculate-distances-between-addresses/
Priecājos par lielo atsaucību Power BI Meetup grupā. Paldies!