Calcolo della distanza tra i punti (Microsoft Excel)
Mike tiene traccia dei valori di latitudine e longitudine in un foglio di lavoro di Excel. Poiché si tratta essenzialmente di punti su una griglia, Mike desidera calcolare la distanza tra due punti di latitudine / longitudine dati.
Se le coppie di latitudine e longitudine fossero in realtà solo punti su una griglia, calcolare la distanza tra loro sarebbe facile. Il problema è che sono davvero punti su una sfera, il che significa che non è possibile utilizzare calcoli a griglia piatta per determinare la distanza. Inoltre, ci sono molti modi per calcolare le distanze: distanza di superficie più breve, percorso di volo ottimale (“in linea d’aria”), distanza attraverso la terra, distanza di guida, ecc.
Ovviamente questa potrebbe essere una domanda complicata. Nello spazio disponibile, esaminerò un paio di modi per determinare la distanza del cerchio grande (“in linea d’aria”), e poi fornirò alcuni riferimenti per ulteriori informazioni sugli altri tipi di calcoli.
La prima cosa che devi capire è come verranno rappresentate la latitudine e la longitudine di ogni punto in Excel. Ci sono diversi modi in cui potrebbe essere rappresentato. Ad esempio, puoi inserire gradi, minuti e secondi nelle singole celle. Oppure potresti averli in una singola cella come DD: MM: SS. In entrambi i casi è accettabile, ma dovranno essere trattate in modo diverso le tue formule. Perché? Perché se inserisci latitudine e longitudine come GG: MM: SS, Excel li convertirà internamente in un valore temporale e dovrai solo prendere in considerazione tale conversione.
Quello che dovrai fare, qualunque cosa accada, è convertire la tua latitudine e longitudine in un valore decimale in radianti. Se hai una coordinata in tre celle separate (gradi, minuti e secondi), puoi utilizzare la seguente formula per convertire un valore decimale in radianti:
=RADIANS((Degrees3600+Minutes60+Seconds)/3600)
La formula utilizza intervalli denominati per gradi, minuti e secondi. Converte questi tre valori in un unico valore che rappresenta i gradi totali, quindi utilizza la funzione RADIANTI per convertirlo in radianti.
Se inizi con un valore di 32 gradi, 48 minuti e 0 secondi, la formula finisce per avere questo aspetto:
=RADIANS((323600+4860+0)/3600) =RADIANS((115200+2880+0)/3600) =RADIANS(118080/3600) =RADIANS(32.8) =0.572467995
Se stai memorizzando le tue coordinate nel formato GG: MM: SS in una singola cella (in questo esempio, cella E12), puoi utilizzare la seguente formula per convertire in un valore decimale in radianti:
=RADIANS((DAY(E12)86400+HOUR(E12)3600+MINUTE(E12)*60+SECOND(E12))/3600)
Supponendo che la cella E12 contenga 32:48:00, la formula finisce per apparire così:
=RADIANS((186400+83600+48*60+0)/3600) =RADIANS((86400+28800+2880+0)/3600) =RADIANS(118080/3600) =RADIANS(32.8) =0.572467995
Con le tue coordinate in radianti, puoi utilizzare una formula trigonometrica per calcolare la distanza lungo la superficie di una sfera. Ci sono molte di queste formule che potrebbero essere utilizzate; la seguente formula sarà sufficiente per i nostri scopi:
=ACOS(SIN(Lat1)SIN(Lat2)+COS(Lat1)COS(Lat2)COS(Lon2-Lon1))180/PI()*60
In questa formula, ciascuna delle coordinate di latitudine (Lat1 e Lat2) e longitudine (Lon1 e Lon2) deve essere un valore decimale, in radianti, come già discusso. La formula restituisce un valore in miglia nautiche, a cui è quindi possibile applicare varie formule per convertire in altre unità di misura, se lo si desidera.
Dovresti renderti conto che i valori che ottieni usando qualsiasi formula che calcola la distanza sulla superficie di una sfera darà risultati leggermente errati. Perché? Perché la Terra non è una sfera perfetta. Pertanto, le distanze devono essere considerate solo approssimative. Se vuoi essere un po ‘più preciso, puoi usare la seguente formula per determinare le tue miglia nautiche:
=ACOS(SIN(Lat1)SIN(Lat2)+COS(Lat1)COS(Lat2)COS(Lon2-Lon1))3443.89849
Questa formula sostituisce il raggio della terra (3443,89849 miglia nautiche) con il raggio di una sfera (180 / PI () * 60 o 3437,746771). In ogni caso, la risposta dovrebbe comunque essere considerata approssimativa.
Come puoi vedere, la formula per calcolare le distanze è piuttosto lunga. Potresti trovare più facile sviluppare la tua funzione definita dall’utente che eseguirà il calcolo per te. La seguente funzione prende quattro valori (le due coppie di latitudini e longitudini, in gradi), e poi restituisce un risultato in miglia nautiche:
Function CrowFlies(dlat1, dlon1, dlat2, dlon2) Pi = Application.Pi() earthradius = 3443.89849 'nautical miles lat1 = dlat1 Pi / 180 lat2 = dlat2 Pi / 180 lon1 = dlon1 Pi / 180 lon2 = dlon2 Pi / 180 cosX = Sin(lat1) Sin(lat2) + Cos(lat1) _ Cos(lat2) * Cos(lon1 - lon2) CrowFlies = earthradius * Application.Acos(cosX) End Function
Se desideri vedere una discussione più approfondita su latitudini e longitudini e sulla matematica coinvolta, puoi trovare una buona selezione di articoli in questo sito:
http://mathforum.org/library/drmath/sets/select/dm_lat_long.html
Con la matematica sotto controllo, puoi iniziare a cercare varie formule che puoi usare. Ce n’è uno interessante in VBA in questa pagina Web:
http://www.freevbcode.com/ShowCode.asp?ID=5532
Una buona discussione generale può essere trovata anche sul sito di Chip Pearson, qui:
http://www.cpearson.com/excel/LatLong.aspx
_Nota: _
Se desideri sapere come utilizzare le macro descritte in questa pagina (o in qualsiasi altra pagina dei siti ExcelTips), ho preparato una pagina speciale che include informazioni utili.
ExcelTips è la tua fonte di formazione economica su Microsoft Excel.
Questo suggerimento (3275) si applica a Microsoft Excel 97, 2000, 2002 e 2003. Puoi trovare una versione di questo suggerimento per l’interfaccia a nastro di Excel (Excel 2007 e versioni successive) qui: