XL 2013 RECHERCHE AGENCE LA PLUS PROCHE D'UNE COMMUNE

[CARO13]

Bonjour
J'ai besoin d'integrer dans un tableau de suivi l'agence la plus proche de chq Commune de France.
Je n'arrive pas à trouver la meilleure solution : VBA / QUERY ou formule XLS ???

Je vous joints mon tableau d'exemple avec en feuille 1 la liste des communes avec leur coordonnée GPS et en 2 la liste des agences

Je n'ai pas mis toutes les communes pour limiter la taille de mon fichier

Merci d'avance pour votre aide !

 

[JHA]

Bonjour à tous,

Je pense qu'il sera difficile de te donner une bonne réponse, ci-joint une proposition à tout hasard.

JHA

 

[sylvanu]

Bonjour Caro, JHA,
Une première approche purement trigonométrique en PJ.
J'ai rajouté une feuille Distance pour calculer que sur une Commune.
Mais ça reste du "vol d'oiseau" donc peu précis par rapport à un calcul trajet par route.

 

[CARO13]

Bonjour à tous les 2
je regarde avec mon fichier de base mais à première vue ca me parait pas mal

un grand merci

je reviens vers vous

 

[Rouge]

Bonjour,

Autre proposition

Option Explicit

Sub Recherche_Agence()
    Dim f1 As Worksheet, f2 As Worksheet
    Dim DerLig_f1 As Long, DerLig_f2 As Long, i As Long, k As Long
    Dim Dif_Lat As Long, Dif_Lon As Long
    Dim Agence As String, Result As String, Result_Inter As String
    Dim Lon_Com As String, Lat_Com As String
    Dim Lon_Ag As String, Lat_Ag As String
    Dim Deb As Double
    
    Application.ScreenUpdating = False
    Deb = Timer
    Set f1 = Sheets("COMMUNE")
    Set f2 = Sheets("AGENCES")
    DerLig_f1 = f1.Range("A" & Rows.Count).End(xlUp).Row
    DerLig_f2 = f2.Range("A" & Rows.Count).End(xlUp).Row
    f1.Range("M2:M" & DerLig_f1).ClearContents
    For i = 2 To DerLig_f1
        Lat_Com = Val(f1.Cells(i, "C"))
        Lon_Com = Val(f1.Cells(i, "D"))
        Result = 1000
        For k = 2 To DerLig_f2
            Lat_Ag = Val(f2.Cells(k, "K"))
            Lon_Ag = Val(f2.Cells(k, "L"))
            Dif_Lat = Abs(Lat_Ag - Lat_Com) * 1
            Dif_Lon = Abs(Lon_Ag - Lon_Com) * 1
            
            Result_Inter = Sqr(Dif_Lat * Dif_Lat + Dif_Lon * Dif_Lon)
            If Result_Inter < Result Then
                Result = Result_Inter
                Agence = f2.Cells(k, "A")
            End If
        Next k
        f1.Cells(i, "M") = Agence
    Next i
    
    MsgBox "Temps d'exécution: " & Round(Timer - Deb, 2) & "Sec"
    Set f1 = Nothing
    Set f2 = Nothing
End Sub

 

[Rouge]

Autre proposition, du fait qu'il ne s'agit que de distances géographiques à vol d'oiseau, qui ne tient pas du tracé de la route ni même des dénivelés, je propose l'affichage des 3 agences les plus proches (de la plus proche à la moins proche)

 

[CARO13]

Bonjour Rouge
c'est exactement ce qu'il me fallait et en effet avoir le choix de 3 agences c'est encore mieux - MERCI

par contre lorsque je copie ta macro sur mon fichier j'ai un débogage sur
Result_Inter = Sqr(Dif_Lat * Dif_Lat + Dif_Lon * Dif_Lon)

et je n'arrive pas à comprendre pourquoi

le seul changement entre le fichier envoyé et mon fichier c'est le nombre de ligne aussi bien au niveau des communes qu'au niveau des agences

Peux tu m'orienter ?

Encore merci !

 

[Rouge]

Remplacez cette ligne:
Dim Result_Inter As String, Result1 As String, Result2 As String, Result3 As String

par celle-ci:
Dim Result_Inter As Double, Result1 As Double, Result2 As Double, Result3 As Double

 

[CARO13]

Super ca fonctionne … un énorme merci ! ca m'enlève une belle épine du pied !

Encore petite demande, où je pense que tu passeras beaucoup moins de temps que moi à le faire , possible d'ajouter les KMs qui séparent les communes à l'agence ?

MERCI !

 

[Rouge]

Du fait que les distances sont évaluées à vol d'oiseau, j'ai préféré en plus du kilométrage, passer de 3 agences à 5 agences, ce qui donne un éventail plus large sur le choix à entreprendre.

 

[CARO13]

C'est génial … Un vrai GRAND MERCI !
j'ai vraiment tout ce qu'il me faut maintenant ! je vais pouvoir finaliser mon fichier global !

Merci également SYLVANU & JHA de vous êtes penché sur mon sujet