XL 2016 Affichage villes proches à partir coordonnées GPS

bobland974 · 16 Novembre 2017

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 · 17 Novembre 2017

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 · 17 Novembre 2017

Bonjour,

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

Softmama · 17 Novembre 2017

Bonjour,

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

bobland974 · 18 Novembre 2017

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 · 18 Novembre 2017

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 · 18 Novembre 2017

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

VB:

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 · 18 Novembre 2017

Bonsour®

bobland974 à dit:
@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 · 18 Novembre 2017

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 · 18 Novembre 2017

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 · 18 Novembre 2017

bobland974 à dit:
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 · 18 Novembre 2017

Dranreb à dit:
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 · 18 Novembre 2017

Je vous préviens: Ça craint !

bobland974 · 18 Novembre 2017

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 · 18 Novembre 2017

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 · 18 Novembre 2017

Bonsour®

bobland974 à dit:
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...

XL 2016 Affichage villes proches à partir coordonnées GPS

bobland974

XLDnaute Nouveau

Pièces jointes

Dranreb

XLDnaute Barbatruc

Pièces jointes

Softmama

XLDnaute Accro

Pièces jointes

Softmama

XLDnaute Accro

Pièces jointes

bobland974

XLDnaute Nouveau

Dranreb

XLDnaute Barbatruc

Dranreb

XLDnaute Barbatruc

Modeste geedee

XLDnaute Barbatruc

bobland974

XLDnaute Nouveau

bobland974

XLDnaute Nouveau

Dranreb

XLDnaute Barbatruc

bobland974

XLDnaute Nouveau

Dranreb

XLDnaute Barbatruc

bobland974

XLDnaute Nouveau

Dranreb

XLDnaute Barbatruc

Modeste geedee

XLDnaute Barbatruc

Discussions similaires