XL 2013 Recherche filtrée avec un test et entre deux feuilles

FMDCC · 11 Avril 2018

Bonjour,

Je bloque sur la mise en place d'un traitement qui me permettrait de faire une recherche filtrée entre deux feuilles et avec également un test.
Je m'explique, tout d'abord, j'ai 2 bases de données contenant des adresses et pour chaque ligne un nombre de logements. La seconde possède en plus un champ renseignant son code parcelle. C'est donc ce champs que je veux ajouter à ma BD 1.

Sur ma première feuille (où se trouve la BD 1), je souhaite après avoir cliqué sur un bouton, lancer un traitement qui recherche pour chaque adresse de cette feuille, parmi les adresses en DB 2 celles strictement similaires et pour laquelle la différence en nombre de logements est la moindre. Puis renvoie le code parcelle correspondant en colonne C de la feuille BD 1.

Etant donné le grand nombre d'adresses présentent en BD 2 (dans mes données pas dans cet exemple bien sûr !), j'aimerai que le traitement identifie dans un premier temps le code INSEE pour faire la recherche de l'adresse uniquement sur les adresses de cette commune et non sur l'ensemble.

Voici un fichier exemple :
Exemple.xlsm

J'aimerai également que le traitement se fasse (si possible) sans avoir recourt à l'utilisation de nouvelle feuille.

Si je n'ai pas été clair sur un point n'hésitez pas à me demander plus de précisions.

Merci beaucoup

Nairolf · 11 Avril 2018

Salut,

Essaye avec la formule matricielle (validation avec CTRL+MAJ+ENTREE) suivante en C2 (à étirer vers le bas):

Code:

=SIERREUR(SI(ESTVIDE(A2);"";INDEX(DECALER('BD 2'!$C$2;;;NBVAL('BD 2'!A:A)-2;);MIN(SI(SI((B2=DECALER('BD 2'!$B$2;;;NBVAL('BD 2'!A:A)-2;));ABS(A2-DECALER('BD 2'!$D$2;;;NBVAL('BD 2'!A:A)-2;));"")=MIN(SI((B2=DECALER('BD 2'!$B$2;;;NBVAL('BD 2'!A:A)-2;));ABS(A2-DECALER('BD 2'!$D$2;;;NBVAL('BD 2'!A:A)-2;));""));LIGNE(DECALER('BD 2'!$A$2;;;NBVAL('BD 2'!A:A)-2;));""))-1;1));"Pas de lien")

FMDCC · 11 Avril 2018

Salut Nairolf,

Merci pour ta réponse mais je recherche une solution utilisant un bouton car j'ai déjà mis au point une fonction qui fait ce traitement

Code:

Function Recherche_CP_probable(ByVal Adresses_BD2 As Range, ByVal Adresse_a_trouver As String, ByVal Nb_logs_a_trouver As Long) As String
Dim Cellule_a_tester As Range
Dim Ecart_en_cours As Long
Recherche_CP_probable = "pas de lien"
Ecart_en_cours = 1000000
For Each Cellule_a_tester In Adresses_BD2
    If Cellule_a_tester = Adresse_a_trouver Then
        If Abs(Cellule_a_tester.Offset(0, 2) - Nb_logs_a_trouver) < Ecart_en_cours Then
            Ecart_en_cours = Abs(Cellule_a_tester.Offset(0, 2) - Nb_logs_a_trouver)
            Recherche_CP_probable = Cellule_a_tester.Offset(0, 1)
        End If
    End If
Next Cellule_a_tester
End Function

Le problème est que j'ai de nombreuses adresses en BD 2 et que j'aimerai pouvoir n'effectuer les recherches d'adresses identiques entre les deux bases que sur la commune correspondante grâce au code Insee. Tout cela dans le but d'effectuer le traitement le plus rapidement possible. De plus, avec du code vba et la mise en place d'un bouton, les résultats seront présents en dure et non par le biais d'une fonction qui est fréquemment relancée par Excel. Et qui dans mon cas demande plusieurs minutes de traitements.

Nairolf · 11 Avril 2018

Re,

Après un peu de recherche et de travail (il fallait que je me remettes la tête dans le traitement de données en vba avant), je te propose le code suivant qui doit s'effectuer bien plus rapidement:

VB:

Sub Recherche_CP_probable_2()

Dim Ecart_en_cours As Long
Dim Adresses_BD2()
Dim Adresses_NbLogs_a_trouver()

Adresses_NbLogs_a_trouver = Worksheets("BD 1").Range("A2:C6").Value
Adresses_BD2 = Worksheets("BD 2").Range("A2:D30").Value

For i = LBound(Adresses_NbLogs_a_trouver) To UBound(Adresses_NbLogs_a_trouver)
    Adresses_NbLogs_a_trouver(i, 3) = "Pas de lien"
    Ecart_en_cours = 1000000
    For j = LBound(Adresses_BD2) To UBound(Adresses_BD2)
        If Adresses_BD2(j, 2) = Adresses_NbLogs_a_trouver(i, 2) Then
            If Abs(Adresses_BD2(j, 4) - Adresses_NbLogs_a_trouver(i, 1)) < Ecart_en_cours Then
                Ecart_en_cours = Abs(Adresses_BD2(j, 4) - Adresses_NbLogs_a_trouver(i, 1))
                Adresses_NbLogs_a_trouver(i, 3) = Adresses_BD2(j, 3)
            End If
        End If
    Next j
Next i

Worksheets("BD 1").Range("A2:C6").Value = Adresses_NbLogs_a_trouver

End Sub

A noter que j'ai choisi de définir les plages de cellules "brutes", je pense que tu n'auras aucun mal à l'adapter pour des plages dynamiques.

Nairolf · 11 Avril 2018

Je ne t'avais pas répondu sur le filtre par rapport au numéro INSEE :
Je pense ne pas faire erreur en disant que le problème est que tu vas rajouter un traitement supplémentaire et donc rallonger le temps de traitement, car on ne connaît pas le numéro d'INSEE en amont est qu'on est obligé de le vérifier ligne par ligne comme tu le fait pour le reste.

J'ai testé le code dans le cas de plages de 100 000 lignes (soit 10^10 combinaisons à tester avec la plus part dont le test est faux) et ça a mis un peu moins de 20 minutes sur ma machine. On peut sans doute optimiser en faisant un contrôle avant la boucle j s'il y a fréquemment une valeur sans lien.

FMDCC · 11 Avril 2018

Merci pour cette proposition mais à moins que je ne me trompe, il n'y a pas de filtrage de la liste des adresses en BD2 en fonction du code Insee qui se trouve dans l'adresse en DB1.

A priori, faire ce filtrage devrais rendre la fonction encore plus rapide à exécuter puisque le nombre de lignes à vérifier pour chaque adresses en BD1 serait bien diminué.

Edité : Oups, je n'avais pas vu ton précédent message. Sinon concernant les codes INSEE en BD1, je peux également les ajouter en colonne 1 comme dans la feuille BD2.

FMDCC · 11 Avril 2018

Après réflexion, je me demande si le fait de découper la base BD2 en plusieurs matrices (une par commune) ne serait pas une solution. Après, il faudrait nommer ces matrices par exemple "Adresses_BD2_01001" et pouvoir mettre en place une condition qui ferait que l'on fasse la recherche sur les lignes de la matrice concernée.

Nairolf · 12 Avril 2018

Salut,

Je n'avais pas vu ton EDIT hier, et j'y ai repensé, dans tous les cas tu dois passer sur toutes les lignes, donc rajouter ce champ ne permettra pas de réduire le temps de calcul.

Séparer les données en groupes permettrait de grandement réduire le temps de la procédure car réduirait le nombre de lignes à étudier.

J'ai aussi regardé plus en détail la structure du code et ce qui prend du temps.
Ce sont les If...Then qui monopolisent le plus de temps ainsi le fait qu'il y ait des données communes dans la colonne "B" de la feuille "BD 1" (moins il y a d'adresses communes, moins le temps sera long, par exemple entre que des champs identiques et que des champs différents, j'ai environ 1/4 de temps en moins).
Je ne vois comment réduire ce temps

Combien de lignes peux-tu avoir au maximum dans chacune des feuilles ?

FMDCC · 12 Avril 2018

Salut,

Entendu pour le filtre sur le code INSEE, j'abandonne l'idée.

Par contre, j'aimerai donc mettre en place un système pour travailler avec un ensemble de matrice d'adresses pour la BD2 (une par communes).

Enfin, je dirai que grosso modo, je prévois de travailler sur 5 000 en BD1 et 50 000 adresses en BD2.

Nairolf · 12 Avril 2018

Avec ce nombre de données, mon traitement devrait durer entre 20 secondes et 1 minute, tu as essayé ?

Pour les "matrices" regroupant les adresses par commune, plusieurs solutions sont possibles, je dirais que l'une des plus simples est de faire un onglet par commune (le code chercherait d’abord sur les noms d'onglets puis sur les lignes de l'onglet correspondant).

FMDCC · 12 Avril 2018

Non pas encore. Je suis sur plusieurs travaux en même temps mais dès que je l'aurai testé en grandeur nature , je te ferai un retour.

Pour les matrices d'adresses BD2 par commune, je vais tenter de toutes les laisser sur un même onglet mais de les appeler par leur nom qui serait en autre composé par le code insee correspondant.
Ex: "mat_adr_bd2_01001"

FMDCC · 13 Avril 2018

Salut,

Je viens de commencer les expérimentations sur mon fichier de travail et je rencontre des difficultés.
Plusieurs éléments changent par rapport à l'exemple que j'avais volontairement simplifié au maximum.
Dans ma BD1 il y a bien plus de colonnes que les 3 présentent dans mon fichier exemple. Voici les différences :
- le nombre de logements de l'adresse à trouver se situe en colonne C
- l'adresse à trouver se situe en colonne AZ
- le résultat doit se situer après traitement en colonne BA
- les lignes de données ne commencent qu'à partir de la ligne 4

De ce fait, cette ligne n'est plus valide :

Code:

Adresses_NbLogs_a_trouver = Worksheets("BD 1").Range("A2:C6").Value

De plus, comme annoncé hier, j'ai découpé l'ensemble des adresses BD2 en autant de plages de valeurs nommées qu'il y a de communes.
J'ai nommé ces plages selon ce modèle "com_01001".

Voici l'état actuel de mon traitement avec très certainement de nombreuses erreurs... :

Code:

Sub Recherche_CP_probable()

Dim Ecart_en_cours, Nb_adresses_a_trouver, i, j As Long
Dim Adresses_BD2()
Dim Adresses_NbLogs_a_trouver As Range
Dim Code_INSEE, Resultat As String

Nb_adresses_a_trouver = Sheets("BD1").Cells(Rows.Count, "A").End(xlUp).Row
Set Adresses_NbLogs_a_trouver = Worksheets("BD1").Range("AZ4:AZ" & Nb_adresses_a_trouver).Value

'Parcours l'ensemble des adresses à trouver en BD1
For i = 3 To Nb_adresses_a_trouver - 3
    Resultat = "Pas de lien"
    Ecart_en_cours = 1000000

    'Rempli Adresses_BD2 avec les adresses en fonction du code INSEE de l'adresse à trouver
    Code_INSEE = "com_" & Right(Adresses_NbLogs_a_trouver(i, 2), 5)
    Adresses_BD2 = Worksheets("BD2").Range(Code_INSEE).Value

    For j = LBound(Adresses_BD2) To UBound(Adresses_BD2)
        If Adresses_BD2(j, 1) = Adresses_NbLogs_a_trouver(i) Then
            If Abs(Adresses_BD2(j, 3) - Adresses_NbLogs_a_trouver(i).Offset(0, -49)) < Ecart_en_cours Then
                Ecart_en_cours = Abs(Adresses_BD2(j, 3) - Adresses_NbLogs_a_trouver(i).Offset(0, -49))
                Resultat = Adresses_BD2(j, 2)
            End If
        End If
    Next j

    Worksheets("BD1").cell(53, i + 3) = Resultat
Next i

End Sub

Pour l'instant cela bloque sur cette ligne avec comme message d'erreur :
Erreur d’exécution '424': Objet requis

Code:

Set Adresses_NbLogs_a_trouver = Worksheets("BD1").Range("AZ4:AZ" & Nb_adresses_a_trouver).Value

FMDCC · 13 Avril 2018

J'ai pu corriger cette première erreur. J'ai remis la variable Adresses_NbLogs_a_trouver sans type défini et j'ai retiré l'instruction Set.

Ensuite, j'en ai créé une seconde pour y mettre les nombres de logements en BD1. Du coup, j'ai pu retirer tout les .offset

Code:

Sub Recherche_CP_Probable()

Dim Ecart_en_cours, Nb_adresses_a_trouver, i, j As Long
Dim Adresses_BD2()
Dim Adresses_a_trouver()
Dim Nb_logs_registre()
Dim Code_INSEE, Resultat As String

Nb_adresses_a_trouver = Sheets("Méthodo correspondance Majic").Cells(Rows.Count, "A").End(xlUp).Row
Adresses_a_trouver = Worksheets("Méthodo correspondance Majic").Range("AZ4:AZ" & Nb_adresses_a_trouver).Value
Nb_logs_BD1 = Worksheets("Méthodo correspondance Majic").Range("C4:C" & Nb_adresses_a_trouver).Value

'Parcours l'ensemble des adresses de copros à rechercher
For i = 3 To Nb_adresses_a_trouver - 3
    Resultat = "Pas de lien"
    Ecart_en_cours = 1000000

    'Rempli Adresses_BD2 avec les adresses en fonction du code INSEE de l'adresse à trouver
    Code_INSEE = "com_" & Right(Adresses_a_trouver(i, 1), 5)
    Adresses_BD2 = Worksheets("BD2").Range(Code_INSEE).Value

    For j = LBound(Adresses_BD2) To UBound(Adresses_BD2)
        If Adresses_BD2(j, 1) = Adresses_a_trouver(i, 1) Then
            If Abs(Adresses_BD2(j, 3) - Nb_logs_BD1(i, 1)) < Ecart_en_cours Then
                Ecart_en_cours = Abs(Adresses_BD2(j, 3) - Nb_logs_BD1(i, 1))
                Resultat = Adresses_BD2(j, 2)
            End If
        End If
    Next j

    Worksheets("BD1").Cells(53, i + 3) = Resultat
Next i

End Sub

Lorsque je lance le traitement, j'ai plusieurs seconde de processus en cours puis un message d'erreur :
"Erreur d'exécution '1004' : Erreur définie par l'application ou par l'objet"
sur cette ligne :

Code:

Adresses_Majic = Worksheets("mat_adr_majic").Range(Code_INSEE).Value

Nairolf · 13 Avril 2018

Salut,

Je pense que ton premier problème venait plutôt du:
Dim Adresses_NbLogs_a_trouver As Range => valeur attendue = plage
incompatible avec :
Set Adresses_NbLogs_a_trouver = Worksheets("BD1").Range("AZ4:AZ" & Nb_adresses_a_trouver).Value => valeur attribuée = valeur numérique ou texte selon le contenu des cellules concernées.

Pour le second, le code serait plutôt:

VB:

Adresses_Majic = ActiveWorkbook.Names("Code_INSEE").Value

FMDCC · 13 Avril 2018

Merci pour cette première précision.

Je viens de changer la ligne de code par celle que tu m'as indiqué mais lorsque je lance le traitement, j'ai cette fois "Erreur de compilation : Impossible d'accéder à un tableau" sur Adresses_Majic.

J'ai modifié mon fichier exemple pour qu'il tienne compte des contraintes évoquées plus haut.
A mon avis, il doit y avoir pas mal d'autres améliorations possible sur ce code.

XL 2013 Recherche filtrée avec un test et entre deux feuilles

FMDCC

XLDnaute Nouveau

Pièces jointes

Nairolf

XLDnaute Accro

FMDCC

XLDnaute Nouveau

Nairolf

XLDnaute Accro

Nairolf

XLDnaute Accro

FMDCC

XLDnaute Nouveau

FMDCC

XLDnaute Nouveau

Nairolf

XLDnaute Accro

FMDCC

XLDnaute Nouveau

Nairolf

XLDnaute Accro

FMDCC

XLDnaute Nouveau

FMDCC

XLDnaute Nouveau

FMDCC

XLDnaute Nouveau

Nairolf

XLDnaute Accro

FMDCC

XLDnaute Nouveau

Pièces jointes

Discussions similaires