Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

[Excel] Creare Matrice delle Distanze

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

[Excel] Creare Matrice delle Distanze

Postdi allis88 » 17/12/13 16:14

Salve a tutti,
premetto di non essere molto esperta di Excel ma sto cercando di imparare, dovendoci lavorare molto per la tesi che sto facendo.
Ho un problema che non riesco assolutamente a risolvere in maniera veloce: ho a disposizione una tabella con una lista di clienti e le loro coordinate geografiche (latitudine e longitudine). Dovrei creare una matrice delle distanze dove viene calcolata la distanza in linea d'aria di ogni cliente con gli altri clienti, quindi una matrice delle distanze.
Allego le due immagini esempio della tabella che ho a disposizione e della matrice che dovrei creare:
Immagine

Immagine

per calcolare la distanza in linea d'aria ho trovato una formula che dovrebbe andare bene:
dist(A,B)=R*arccos(sen(lat_Pol_A)*sen(lat_Pol_B)+cos(lat_Pol_A)*cos(lat_Pol_B)*cos(lon_Pol_A-lon_Pol_B))
dove R=6372 (raggio della Terra)
e ogni latitudine e longitudine è convertita in coordinate polari: lat_Pol_A=latA*pi.greco/180

non riesco però ad applicare la formula in maniera efficace a tutta la matrice. Forse sarà un problema banale, ma non riesco a venirne a capo :(

Inoltre, se fosse possibile, vorrei trovare i km effettivi (e non in linea d'aria) tra i vari punti. So che esiste la possibilità di interfacciare per esempio il programma MapPoint con Excel, ma non so se sia possibile fare ciò che cerco

Lavoro con Excel 2010

Grazie mille della disponibilità
allis88
Newbie
 
Post: 3
Iscritto il: 17/12/13 15:40

Sponsor
 

Re: [Excel] Creare Matrice delle Distanze

Postdi wallace&gromit » 17/12/13 17:14

ciao allis, benvenuta nel forum.
Premettendo che si può probabilmente trovare una soluzione molto migliore di questa mi sono cimentato nell'impresa di creare una formula che possa essere copiata e incollata su tutta la tabella e che rimanga valida.
Per fare questo è necessario che nelle celle di definizione dei luoghi (colonna A e riga 1) i valori siano inseriti in questa forma:
nelle celle A2 e B1: lat_Pol_A;lon_Pol_A (per esempio 46.09;8.45)
nelle celle A3 e C1: lat_Pol_B;lon_Pol_B

e così via.

in B2 inserisci questa formula:
Codice: Seleziona tutto
=6372*ARCCOS(SEN(STRINGA.ESTRAI($A2;1;RICERCA(";";$A2)-1))*SEN(STRINGA.ESTRAI(B$1;1;RICERCA(";";B$1)-1))+COS(STRINGA.ESTRAI($A2;1;RICERCA(";";$A2)-1))*COS(STRINGA.ESTRAI(B$1;1;RICERCA(";";B$1)-1))+COS(STRINGA.ESTRAI($A2;RICERCA(";";$A2)+1;LUNGHEZZA($A2)-RICERCA(";";$A2))-STRINGA.ESTRAI(B$1;RICERCA(";";B$1)+1;LUNGHEZZA(B$1)-RICERCA(";";B$1))))
appena un po' complicata :lol:
in pratica estrae sempre la parte di testo che serve per il calcolo (a seconda dei casi a sinistra per lat e a destra per lon).
È l'unico modo che mi viene in mente per avere tutti i dati in una sola tabella. Prova e fai sapere.
E comunque aspetta a vedere se qualcuno sa fare di meglio (non dubito...)

Edit: le mie coordinate di esempio non erano polari, fai in modo che le tue lo siano!
stato 2014: Office2003/2013 su win7
Avatar utente
wallace&gromit
Utente Senior
 
Post: 1420
Iscritto il: 16/01/12 14:21

Re: [Excel] Creare Matrice delle Distanze

Postdi allis88 » 17/12/13 19:33

Grazie mille!!
Da quel che vedo, vista la difficoltà della formula, il problema non è per nulla semplice, per fortuna quindi non sono così imbranata :D
Grazie per la tua formula, intanto provo questa, se poi qualcun altro ha qualche altra soluzione ben venga ;)
allis88
Newbie
 
Post: 3
Iscritto il: 17/12/13 15:40

Re: [Excel] Creare Matrice delle Distanze

Postdi Anthony47 » 17/12/13 22:35

La formula che hai riportata e' traducibile in
Codice: Seleziona tutto
=6372,8*ARCCOS((SEN(RADIANTI(Lat1))*SEN(RADIANTI(Lat2))+(COS(RADIANTI(Lat1))*COS(RADIANTI(Lat2))*COS(RADIANTI(Long2-Long1)))))
Per semplificarti la vita suggerisco di riportare in colonna A e B di Foglio2 le coordiante dei clienti (che quindi saranno in colonna C, da C4 e sottostanti); idem in riga 1 e riga 2, sicche' l' elenco clienti sara' ora su Riga3 (da D3 e adiacenti).
In D4 inserirai quindi la formula
Codice: Seleziona tutto
=6372,8*ARCCOS((SEN(RADIANTI($A4))*SEN(RADIANTI(D$1))+(COS(RADIANTI($A4))*COS(RADIANTI(D$1))*COS(RADIANTI(D$2-$B4)))))

Copiala verso destra e poi verso il basso; avrai un risultato come da immagine
Immagine

Uploaded with ImageShack.us
Eventualmente nascondi Colonne A-B e riga 1-2.

Se vuoi mantenere le coordinate e i nomi clienti in Foglio1, puoi riportarle in Foglio2 colonne A-B-C con formule semplici del tipo
Codice: Seleziona tutto
=se(Foglio1!A2<>"";Foglio1!A2;"")
(riferita al nome Cliente)
e poi riportarle in orizzontale con
Codice: Seleziona tutto
=SE(SCARTO($A$2;RIF.COLONNA(A1);RIF.RIGA(A1)-1)<>""; SCARTO($A$2;RIF.COLONNA(A1);RIF.RIGA(A1)-1);"")
Questa va in C1, poi la copi verso destra per avere l' elenco delle Lat; poi la copi anche su C2 e adiacenti e C3 e adiacenti per avere le Long e i nomi Clienti.

Se vuoi complicarti la vita, allora usa la funzione GeoDist che avevo sviluppato tempo fa:
Codice: Seleziona tutto
Function GeoDist(Lat1, Long1, Lat2, Long2) As Double
'Risultato in Km
'Distanza tra due coordinate geografiche
'E' una semplificazione della formua di Vincenty
   '
Dim Raggio As Double, PiGrec As Double, X As Double, Y As Double
    Raggio = 6372.8  'Raggio Terra, Km
    PiGrec = Application.pi
'   
' gradi a radianti
    Lat1 = Lat1 * PiGrec / 180
    Lat2 = Lat2 * PiGrec / 180
    dLon = (Long2 - Long1) * PiGrec / 180 ' delta long
'
    X = Sin(Lat1) * Sin(Lat2) + Cos(Lat1) * Cos(Lat2) * Cos(dLon)
    Y = Sqr((Cos(Lat2) * Sin(dLon)) ^ 2 + (Cos(Lat1) * Sin(Lat2) - Sin(Lat1) * Cos(Lat2) * Cos(dLon)) ^ 2)
    Geodist = WorksheetFunction.Atan2(X, Y) * Raggio
'
End Function

La metti in un Modulo standard del vba (es Modulo1), poi in D4 usi la formula
Codice: Seleziona tutto
=GeoDist($A4;$B4;D$1;D$2)
che copi verso destra e verso il basso.

In ambedue i casi Latitudine e Longitudine vanno riportati in gradi (es 45,12345) e il risultato e' espresso in Km.

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13891
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel] Creare Matrice delle Distanze

Postdi allis88 » 19/12/13 09:16

Grazie davvero, ho utilizzato il tuo metodo Anthony e finalmente sono riuscita a fare un piccolo passo nel lavoro che devo fare.
Grazie ad entrambi per le risposte :)
allis88
Newbie
 
Post: 3
Iscritto il: 17/12/13 15:40

Re: [Excel] Creare Matrice delle Distanze

Postdi Ross72 » 03/03/16 11:45

Ciao Anthony47.
Ho incollato le tue istruzioni sul modulo excel e tutto funziona alla perfezione ma dovrei replicare la funzione su Access 2013...
Ho provato a riadattarlo ma con scarsi risultati.
Potresti aiutarmi?

Grazie
Ross72
Utente Junior
 
Post: 13
Iscritto il: 03/03/16 11:36

Re: [Excel] Creare Matrice delle Distanze

Postdi Anthony47 » 03/03/16 11:59

Su Access sono un emerito incompetente, quindi devi attendere suggerimenti da altri Utenti.
Se non ti arrivano allora, tra qualche giorno, apri una nuova discussione dove spieghi cosa hai cercato di fare e su quali dati, e inserisci un link a questa discussione.
Le discussioni con 0 risposte saltano pi' all'occhio di visitatori occasionali.

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13891
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel] Creare Matrice delle Distanze

Postdi Ross72 » 03/03/16 12:07

grazie
Ross72
Utente Junior
 
Post: 13
Iscritto il: 03/03/16 11:36

Re: [Excel] Creare Matrice delle Distanze

Postdi Anthony47 » 03/03/16 14:08

Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13891
Iscritto il: 21/03/06 16:03
Località: Ivrea


Torna a Applicazioni Office Windows


Topic correlati a "[Excel] Creare Matrice delle Distanze":


Chi c’è in linea

Visitano il forum: Nessuno e 5 ospiti