XL 2016 Affichage villes proches à partir coordonnées GPS

[bobland974]

Bonjour à tous,

Voilà, cela va bientôt faire deux jours que je tourne le problème dans tous les sens et je n'arrive pas à trouver la solution, je me permets donc de venir vers vous afin de solliciter votre aide.

Je dispose d'un fichier excel avec en
- colonne (A) : nom de la ville
- colonne (F) : coordonnée GPS latitude
- colonne (G) : coordonnée GPS longitude
- colonne H, I, J : nom des 3 villes les plus proches que je désire faire ressortir du tableau
> cf. fichier ville_plus_proche.xls

Je suis parti du fichier de base pour essayer d'en découdre avecu ne formule mais rien n'y fait
(copie de ville_proche.xlsx)

Par quel moyen puis-je réussir à y arriver sans créé autrement autant de colonne (distance) qu'il y a de ligne afin de pouvoir faire ressortir une liste de choix des villes les plus proches. A terme le fichier devrait contenir plusieurs milliers de colonnes.

Y a t il pas une solution plus simple à mettre en oeuvre car vu la quantité de ville dont j'ai à ma disposition cela est compliqué ?

Merci par avance pour votre aide,

 

[Dranreb]

Je joins une nouvelle version un peu simplifiée, sans prise en compte des distances supérieures à 40 km et avec condition de fin quand tout est renseigné.
Les données sont celle du fichier de tatiak.

 

[Softmama]

Bonjour,

une proposition par formules matricielles, en pièce jointe, sans colonne intermédiaire, à partir des coordonnées GPS uniquement.

 

[Softmama]

Bonjour,

en fait, la même avec la formule barbare de COS, ACOS... toujours en matricielle

 

[bobland974]

Bonsoir à tous,

Tout d'abord je tiens vivement à vous remercier @Softmama, @Dranreb @Modeste geedee @tatiak

Je vous avoue qu'il y a des formules que je n'ai pas compris car je ne suis pas du tout assez calé pour.
En tout cas c'est top. J'ai l'impression que par rapport à ma demande le fichier correspondant le mieux est celui de @Dranreb.

Est-ce qu'on pourrait imaginer passer de 3 villes proches à 7 ou 10 villes par exemple facilement ?
J'ai essayé d'y toucher de mon côté, mais la macro me parait bien complexe à modifier.

@Modeste geedee , je n'ai pas bien compris comment utiliser ta macro par contre. Cela serait à intégrer directement dans le dossier ?

Merci par avance pour toute votre aide et votre soutien.

Belle soirée

 

[Dranreb]

Il suffit à peu près de changer dans la macro tous les " 3" en " 7" (mais pas les 3 de la ligne dans les expressions entre crochets Ni la colonne 3 de TDst(LDst, 3)) et aussi l'adresse de la 1ère ligne de la plage résultante Feuil1.[G3:I3] tout à la fin.

 

[Dranreb]

Je crois que le mieux c'est de faire comme ça :

Sub VillesProches()
Const NbV = 7
Dim TDon(), TDst() As Variant, L1 As Long, L2 As Long, D As Double, LDst As Long, TIdx() As Long, _
    TRés(), I As Long, Zkm As String, C As Long, TCMax() As Long, NbTCMax As Long
TDon = Feuil1.[A3:F3].Resize(Feuil1.Cells(1000, "A").End(xlUp).Row - 2).Value
ReDim TDst(1 To NumVsMax(UBound(TDon, 1)), 1 To 3)
For L2 = 2 To UBound(TDon): For L1 = 1 To L2 - 1
   D = Dist(TDon(L1, 5), TDon(L1, 6), TDon(L2, 5), TDon(L2, 6))
   If D <= 40 Then LDst = LDst + 1: TDst(LDst, 1) = D: TDst(LDst, 2) = L1: TDst(LDst, 3) = L2
   Next L1, L2
IndexerFus1Col TIdx(), TDst(), LMax:=LDst
ReDim TRés(1 To UBound(TDon, 1), 1 To NbV), TCMax(1 To UBound(TDon, 1))
For I = 1 To UBound(TIdx)
   LDst = TIdx(I): L1 = TDst(LDst, 2): L2 = TDst(LDst, 3)
   Zkm = " (" & Int(TDst(LDst, 1) * 100 + 0.5) / 100 & " km)"
   C = TCMax(L1) + 1: If C <= NbV Then TCMax(L1) = C: TRés(L1, C) = TDon(L2, 1) & Zkm: If C = NbV Then NbTCMax = NbTCMax + 1
   C = TCMax(L2) + 1: If C <= NbV Then TCMax(L2) = C: TRés(L2, C) = TDon(L1, 1) & Zkm: If C = NbV Then NbTCMax = NbTCMax + 1
   If NbTCMax >= UBound(TDst, 1) Then Exit For
   Next I
Feuil1.[G3].Resize(UBound(TRés, 1), NbV).Value = TRés
End Sub

 

[Modeste geedee]

Bonsour®

@Modeste geedee , je n'ai pas bien compris comment utiliser ta macro par contre.

la macro est à mettre dans le code de feuille1
https://www.excel-downloads.com/attachments/ville_plus_proche-xlsx.1000737/
la plage "Villes" est préalablement à définir en colonne A
il suffit de double clicker ensuite sur un nom de ville
la macro liste les villes dans un rayon de ~11 km
dans ce fichier des villes différentes ont des coordonnées identiques ???

dans cet autre fichier : https://www.excel-downloads.com/attachments/copie-de-ville_proche-xlsx.1000738/
les longitudes sont erronées ??? (Ouest et Est / Paris)

 

[bobland974]

Bonsoir @Dranreb, en effet je viens de tester la macro et il y a pas à dire, c'est parfait.
J'ai l'impression qu'elle se charge également beaucoup plus rapidement que celle d'hier, non ?
Quelle est la limite d'après vous à ne pas dépasser en nombre de communes à intégrer directement au niveau du fichier ?

Bonne réception,

 

[bobland974]

Bonsoir @Modeste geedee ,
En effet, maintenant que vous me le dites, je viens de me rendre compte également que certaines coordonnées GPS sont erronées. Je comprends mieux le fonctionnement. Il faut le faire à chaque fois au cas par cas en cliquant dessus par contre ?

Savez-vous ou je peux trouver le listing de toutes les coordonnées GPS fiables des communes de france ?

Bonne réception,

 

[Dranreb]

Quelle est la limite d'après vous à ne pas dépasser en nombre de communes à intégrer

Difficile à dire. Il y a déjà aussi une limite de 40 km au delà de laquelle les villes ne sont plus associées.

 

[bobland974]

Difficile à dire. Il y a déjà aussi une limite de 40 km au delà de laquelle les villes ne sont plus associées.

Ok, super, je vais donc voir et tester. On pourrait imaginer que je passe tout en une fois (toutes les communes de France?) Cela me permettrait d'avoir correctement toutes les communes avoisinantes/proches?

Bonne réception et belle soirée,

 

[Dranreb]

Je vous préviens: Ça craint !

 

[bobland974]

Argh, ça craint à cause de la lenteur de la macro qui serait lancé (temps) ou ça craint du fait que excel risque de planter ? Il n'y a pas de risque en soi ? si ?

 

[Dranreb]

648 millions de distances à examiner environ !
Sais même pas si on dispose d'un espace mémoire suffisant pour accueillir le tableau…

 

[Modeste geedee]

Bonsour®

Bonsoir @Modeste geedee ,
En effet, maintenant que vous me le dites, je viens de me rendre compte également que certaines coordonnées GPS sont erronées. Je comprends mieux le fonctionnement. Il faut le faire à chaque fois au cas par cas en cliquant dessus par contre ?
Savez-vous ou je peux trouver le listing de toutes les coordonnées GPS fiables des communes de france ?

voir galichon.com
en ce qui concerne la formule utilisée (Pythagore) celle- ci n'est fiable que pour de faible écart inférieurs à 1° d'arc (à l'échelle d'un département)
Mais j'ai des doutes quant aux formule loxodromique ou orthodromique utilisées par les autres répondeurs
des écart incompréhensibles sautent aux yeux ???
oui il faut le faire à chaque fois.
un balayage des 36400~ communes risque cependant de pénaliser les temps de réponses.
je pourrai intégrer facilement un tri dans la réponse...