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,

 

[Softmama]

Bonjour,

tu peux déjà te passer de la colonne G en mettant en F21 et suivantes :

=INDEX(A$2:A$12;EQUIV(PETITE.VALEUR($F$2:$F$12;E21);$F$2:$F$12;0))

 

[bobland974]

Bonjour,

tu peux déjà te passer de la colonne G en mettant en F21 et suivantes :

=INDEX(A$2:A$12;EQUIV(PETITE.VALEUR($F$2:$F$12;E21);$F$2:$F$12;0))

Bonsoir Softmama,
En effet, cela permet de se passer de la colonne G rang en faisant cela.
Par contre le fichier que je désire utiliser en définitive n'est pas celui-ci, mais l'autre (ville_plus_proche.xlsx)
Je souhaiterai donc réussir à trouver une formule qui puisse fonctionner sans utiliser le colonne distance et ainsi comparer les coordonnées GPS d'une ligne par rapport à l'ensemble des autres lignes du tableau afin de ressortir les 3 coordonnées qui ont la distance la plus courte.

Merci pour ton aide précieuse,

Belle soirée

 

[Dranreb]

Bonsoir.
Combien aurez vous de villes au maxi déjà ?
Parce que le nombre de distances différentes possible c'est N * (N - 3) \ 2 + N
Pour 1000 c'est 499500. C'est un peu un ordre de grandeur du maximum possible… Enfin on pourrait peut être allez jusqu'à 1415 (1000405 distances)
J'envisage de les calculer toutes en VBA, de les indexer par ordre croissant, puis d'en remplir 3 maxi aux villes concernées, (les 1ers trouvés par ordre croissant)…

 

[bobland974]

Bonsoir.
Combien aurez vous de villes au maxi déjà ?
Parce que le nombre de distances différentes possible c'est N * (N - 3) \ 2 + N
Pour 1000 c'est 499500. C'est un peu un ordre de grandeur du maximum possible… Enfin on pourrait peut être allez jusqu'à 1415 (1000405 distances)
J'envisage de les calculer toutes en VBA, de les indexer par ordre croissant, puis d'en remplir 3 maxi aux villes concernées, (les 1ers trouvés par ordre croissant)…

Bonsoir Dranreb,
J'ai actuellement mis un échantillon de mon fichier uniquement en effet. Je souhaiterai saisir à terme toutes les communes de france et faire ressortir à chaque fois les 3 villes qui sont les plus proches de cette ville là.

un peu sur le principe de cela http://www.lion1906.com/Php/Resulta...0.82479410566591&RadLong1=-0.0013380916358082 mais en tout automatisé sans être obligé de faire des requêtes à chaque fois.

La deuxième solution du VBA pourrait peut être être la bonne, mais de mon côté je sèche complètement. Comment puis)je y arriver par ce biais ?

Merci beaucoup pour votre aide.

 

[Dranreb]

Toutes les communes de France, ça va faire beaucoup trop à mon avis.
Il va falloir quadriller. Genre ne prendre en compte que les villes situé dans un carré de 3 × 3 pour trouver les villes les plus proches de celles du carré central seulement…

 

[bobland974]

Toutes les communes de France, ça va faire beaucoup trop à mon avis.
Il va falloir quadriller. Genre ne prendre en compte que les villes situé dans un carré de 3 × 3 pour trouver les villes les plus proches de celles du carré central seulement…

On peut également imaginer que je redécoupe mon fichier excel en plusieurs fichiers (par département) ce qui ferait tout de suite un fichier moins lourd.

Comment puis-je faire pour justement quadriller les villes ? en VBA ? directement via les coordonnées GPS ?

 

[Dranreb]

Il faudrait déterminer dans quel carré se trouve chaque ville en arrondissant leurs coordonnées GPS. Puis les classer par carrés. Examiner chaque carré central possibles, ramenez les villes des carrés voisins… Ça sera une usine à gaz, en tout cas !

 

[bobland974]

Oui je me doute, c'est difficile de le prévoir en amont. Une zone en montagne aura un carré très grand/large alors qu'une zone urbaine elle sera beaucoup plus dense.
Et une fonction VBA qui tourne pour calculer tout cela sur 500 / 1000 lignes environ (dépend de la taille du département). Ce serait plus facilement jouable ? Si oui, comment ?

D'après vous quelle solution serait le plus adéquat ?

 

[Dranreb]

De toute façon le noyau sera le même. Si on pouvait toujours se limiter à 500 villes ce serait bien. Ça fait quand même déjà 124750 distances à examiner.
Je ne peux pas vous dire comment faire. Il faut que j'y réfléchisse et que je l'écrive. Ça me parait à priori trop compliqué pour beaucoup d'autres que moi, vu que ça l'est déjà pour moi…

 

[bobland974]

De toute façon le noyau sera le même. Si on pouvait toujours se limiter à 500 villes ce serait bien. Ça fait quand même déjà 124750 distances à examiner.
Je ne peux pas vous dire comment faire. Il faut que j'y réfléchisse et que je l'écrive. Ça me parait à priori trop compliqué pour beaucoup d'autres que moi, vu que ça l'est déjà pour moi…

La majorité du temps on sera sur des départements de moins de 500 communes. Sauf car exceptionnel d'après le tableau ci-joint :

# N° dépt Nom dept Nombre communes
1 62 Pas-de-Calais 895
2 2 Aisne 816
3 80 Somme 782
4 76 Seine-Maritime 745
5 57 Moselle 730
6 14 Calvados 706
6 21 Côte-d'Or 706
8 60 Oise 693
9 27 Eure 675
10 59 Nord 650
11 51 Marne 620
12 50 Manche 601
13 25 Doubs 594
13 54 Meurthe-et-Moselle 594
15 31 Haute-Garonne 589
16 71 Saône-et-Loire 573
17 24 Dordogne 557
18 64 Pyrénées-Atlantiques 547
19 70 Haute-Saône 545
20 39 Jura 544
21 33 Gironde 542
22 38 Isère 533
23 67 Bas-Rhin 527
24 77 Seine-et-Marne 514
24 88 Vosges 514
26 61 Orne 505
27 55 Meuse 500
28 65 Hautes-Pyrénées 474
29 17 Charente-Maritime 472
30 63 Puy-de-Dôme 470
31 8 Ardennes 463
31 32 Gers 463
33 89 Yonne 455
34 11 Aude 438
34 52 Haute-Marne 437
36 10 Aube 433
37 1 Ain 419
38 16 Charente 404
39 28 Eure-et-Loir 402
40 68 Haut-Rhin 377
41 72 Sarthe 375
42 22 Côtes-d'Armor 373
43 26 Drôme 369
44 49 Maine-et-Loire 357
45 30 Gard 353
45 35 Ille-et-Vilaine 353
47 34 Hérault 343
48 46 Lot 340
49 7 Ardèche 339
50 45 Loiret 334
51 9 Ariège 332
52 40 Landes 331
53 42 Loire 327
54 81 Tarn 323
55 3 Allier 320
56 47 Lot-et-Garonne 319
57 58 Nièvre 312
58 73 Savoie 305
58 79 Deux-Sèvres 303
60 12 Aveyron 304
61 74 Haute-Savoie 294
62 41 Loir-et-Cher 291
63 18 Cher 290
64 69 Rhône 288
65 19 Corrèze 286
66 29 Finistère 283
67 85 Vendée 282
68 86 Vienne 281
69 37 Indre-et-Loire 277
70 78 Yvelines 262
71 53 Mayenne 261
71 56 Morbihan 261
73 15 Cantal 260
73 23 Creuse 260
73 43 Haute-Loire 260
76 36 Indre 247
77 20B Haute-Corse 236
78 66 Pyrénées-Orientales 226
79 44 Loire-Atlantique 221
80 87 Haute-Vienne 201
81 4 Alpes-de-Haute-Provence 200
82 91 Essonne 196
83 82 Tarn-et-Garonne 195
84 48 Lozère 185
85 95 Val-d'Oise 185
86 5 Hautes-Alpes 172
87 6 Alpes-Maritimes 163
88 83 Var 153
89 84 Vaucluse 151
90 20A Corse-du-Sud 124
91 13 Bouches-du-Rhône 119
92 90 Territoire de Belfort 102
93 94 Val-de-Marne 47
94 93 Seine-Saint-Denis 40
95 92 Hauts-de-Seine 36
96 972 Martinique 34
97 971 Guadeloupe 32
98 974 Réunion 24
99 973 Guyane 22
100 976 Mayotte4 17
101 75 Paris 1

Cela me "rassure" entre guillemet de savoir que je ne suis pas seul dans ce cas à ne pas être assez connaisseur pour trouver une solution :O
Si je peux vous aider dans la rédaction, ou pour faire quoi que ce soit, n'hésitez pas à me le demander également.

Bonne réception,

 

[Dranreb]

J'ai écrit une macro VillesProches.

Remarque: en le faisant par départements vous risqueriez de louper des communes plus proches mais dans 2 départements voisins différents…

 

[Dranreb]

Peut être pourrait-on définir arbitrairement environ 250 villes comme centres de zones géographiques. Ça permettrait de rattacher toutes les villes à sa ville centre géographique la plus proche. Pour une ville quelconque, on pourrait ensuite inclure toutes les villes des… mettons 6 centres géographiques les plus proches de son centre à elle…

 

[Dranreb]

Bonjour.
Un peu long, je trouve, pour seulement 410 communes…
C'est loin d'être anodin si on considère que dans tous les cas la durée nécessaire est proportionnelle au carré du nombre de villes. Ça grimperait donc de plus en plus vite avec des communes supplémentaires.
Voire un peu plus vite avec ma méthode, rapide jusqu'au millier environ, car elle comporte une phase (indexation par fusions) nécessitant, si je ne dis pas de bêtise, une durée proportionnelle au produit de ce carré par son logarithme…

 

[Modeste geedee]

Bonsour®

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,

une approche par double click communes dans un rayon de 11 km

un degrés d'arc correspond à 111 km ...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Ref_Lat As Double, Ref_Lon As Double, X_lat As Double, X_lon As Double, D As Double
Dim Max_Lat As Double, min_Lat As Double, Max_Lon As Double, min_Lon As Double
Dim message As String, cell As Range
If Target.Column <> 1 Then Exit Sub
Ref_Lat = Target.Offset(0, 5): Ref_Lon = Target.Offset(0, 6)
Max_Lat = Ref_Lat + 0.1: Max_Lon = Ref_Lon + 0.1
min_Lat = Ref_Lat - 0.1: min_Lon = Ref_Lon - 0.1
For Each cell In Range("villes")
If cell.Offset(0, 5) < Max_Lat And cell.Offset(0, 5) > min_Lat And cell.Offset(0, 6) < Max_Lon And cell.Offset(0, 6) > min_Lon Then
D = 111 * ((Ref_Lat - cell.Offset(0, 5)) ^ 2 + (Ref_Lon - cell.Offset(0, 6)) ^ 2) ^ 0.5
message = message & cell & "  " & Format(D, "0.000") & "km" & Chr(10)
End If
Next
MsgBox message
Cancel=True
End Sub