Dictionnary : Recherche iD

Bonjour,

Je touche toujours un peu mes limites avec le Dictionnary !

J'ai un grand nombre de Data comportant (entre autre une iD, un Nom et le cas échéant un Patronyme.

Je désire une fonction VBA qui me renvoie quelle que soit le nom fourni la bonne iD.

Compte tenu du grand nombre de données, le vainqueur est celui qui fournit la solution la plus rapide... (Sans passer par un des écritures intermédiaires !)

Merci

Bonsoir,

une proposition :

Function LouReeD(Val As String)
    Dim dico As Object, LaPlage As Range, Nom As String, i As Long

    LouReeD = "Nom inconnu"
    If Val = "" Then Exit Function
    Set dico = CreateObject("Scripting.Dictionary")
    Set LaPlage = Sheets("Feuil1").ListObjects(1).DataBodyRange
    For i = 1 To LaPlage.Rows.Count
        dico(LaPlage.Cells(i, 1)) = LaPlage.Cells(i, 2) & LaPlage.Cells(i, 3)
    Next i
    tablk = dico.keys
    tabli = dico.items
    Nom = Val
    For i = 0 To UBound(tabli)
        If tabli(i) = UCase(Nom) Then LouReeD = tablk(i): Exit Function
    Next i
    For i = 0 To UBound(tabli)
        If tabli(i) Like UCase(Nom) & "*" Then LouReeD = tablk(i): Exit Function
    Next i
    For i = 0 To UBound(tabli)
        If tabli(i) Like "*" & UCase(Nom) Then LouReeD = tablk(i): Exit Function
    Next i
End Function

Le fichier :

Reste à voir si elle tient la route. J'ai fait un concatener des deux colonnes nom afin d'éviter les doublons, ensuite pour la recherche je commence par le concatener, puis le "Like Nom *" et ensuite le "Like * Nom".

@ bientôt

LouReeD

Bonsoir,

Une autre proposition :

Function id(nom As String) As Integer
    Dim cell As Range, cell1 As Range
    Dim i As Long

    With Sheets(1).ListObjects(1)
        Set cell = .DataBodyRange.Find(nom)
        If cell Is Nothing Then MsgBox "pas de correspondance": Exit Function

        Set cell1 = cell
        Set cell = .DataBodyRange.FindNext(cell)
        If cell.Address <> cell1.Address Then MsgBox "plusieurs réponses - soyez plus précis": Exit Function

        i = cell.Row - .HeaderRowRange.Row
        id = .ListColumns("iD").DataBodyRange(i)

    End With

End Function

Bonsoir à tous ,

Pour le fun, un autre code de fonction avec uniquement des appels aux fonctions de feuille de calcul.

La fonction ChercheID(nom) renvoie :

• l'Id si le nom ne figure qu'une fois dans une seule des deux colonnes Nom1 ou Nom2
• l'Id si le nom ne figure qu'une fois dans chacune des colonnes Nom1 et Nom2 et sur la même ligne
• l'erreur #N/A si le nom n'est pas dans le tableau
• l'erreur #REF! si le nom figure plusieurs fois dans le tableau sur des lignes distinctes

Le code de la fonction :

Function ChercheID(ByVal nom)
Dim i&, j&
   With Worksheets("Feuil1").[a1].ListObject
      i = Application.CountIf(.ListColumns(2).DataBodyRange, nom)
      If i > 1 Then ChercheID = CVErr(xlErrRef): Exit Function
      i = Application.IfError(Application.Match(nom, .ListColumns(2).DataBodyRange, 0), 0)
      j = Application.CountIf(.ListColumns(3).DataBodyRange, nom)
      If j > 1 Then ChercheID = CVErr(xlErrRef): Exit Function
      j = Application.IfError(Application.Match(nom, .ListColumns(3).DataBodyRange, 0), 0)
      If i = 0 And j = 0 Then ChercheID = CVErr(xlErrNA): Exit Function
      If i > 0 And j > 0 Then If i <> j Then ChercheID = CVErr(xlErrRef): Exit Function
      ChercheID = IIf(i > 0, .ListColumns(1).DataBodyRange(i), .ListColumns(1).DataBodyRange(j))
   End With
End Function

bonjour Galopin01,LouReeD,lafraise,thev,

ma contribution : 0.001 sec / recherche

Sub test()
     Dim aA, i, x
     aA = Range("Tableau3").Value            'votre tableau
     t = Timer                               'start chronomètre
     For i = 1 To UBound(aA)                 'boucler les données
          x = BsAlv(aA(i, 2) & IIf(Len(aA(i, 3)), " ", "") & aA(i, 3))     'rechercher nom (et éventuellement " " & nom2)
     Next
     MsgBox " BsAlv : " & vbLf & UBound(aA) & " recherches" & vbLf & Format(Timer - t, "0.00\s")

End Sub

Function BsAlv(nom)
     aA = Evaluate("tableau3[nom]  & if(len(tableau3[nom2]),"" "","""") & tableau3[nom2]")     'matrice avec noms & noms2
     r = Application.Match(nom, aA, 0)
     If IsNumeric(r) Then
          BsAlv = Range("tableau3[ID]").Cells(r, 1).Value
     Else
          BsAlv = "erreur"
     End If
End Function

oubien ma formule

=SIERREUR(INDEX(A:A;
AGREGAT(15;6;
LIGNE(Tableau3[iD])/
(Tableau3[Nom]&SI(NBCAR(Tableau3[Nom2]);" ";"")&Tableau3[Nom2]=G1)
;1));
"erreur")

Re,

Sinon simplement par formule en G2 :

=SI(OU(NB.SI(Tableau3[Nom];G1)>1;NB.SI(Tableau3[Nom2];G1)>1);"#REF";SI(NB.SI(Tableau3[[Nom]:[Nom2]];G1)=0;NA();SI(NB.SI(Tableau3[Nom];G1)=0;INDEX(Tableau3[iD];EQUIV(G1;Tableau3[Nom2];0));SI(NB.SI(Tableau3[Nom2];G1)=0;INDEX(Tableau3[iD];EQUIV(G1;Tableau3[Nom];0));SI(EQUIV(G1;Tableau3[Nom2];0)=EQUIV(G1;Tableau3[Nom];0);INDEX(Tableau3[iD];EQUIV(G1;Tableau3[Nom2];0));"#REF")))))

Bonjour,

Pfff... C'est pas simple !

Hum... Désolé mais je n'avais pas prévu les nombreux doublons et gâteries.

Bonjour,

suis-je le seul a avoir compris qu'il fallait utiliser un "dictionnary" ?

Bonne continuation pour votre projet galopin01 !

@ bientôt

LouReeD

Bonjour à tous ,

LouReeD à dit :

suis-je le seul a avoir compris qu'il fallait utiliser un "dictionnary" ?

Rassure-toi, j'ai bien vu le mot "Dictionary" dans le titre de la question .

Mais comme tu en avais tenu compte le premier, je pense que ça a dû décourager les autres à recoder la même chose.

Il y pas mal de solutions différentes. Cependant galopin01 a aussi évoqué dans sa question le temps d'exécution :

galopin01 a dit :

qui fournit la solution la plus rapide...

J'ai tenté de créer un classeur avec une macro pour évaluer évaluer les différents temps d’exécution.

Pour cela :

• un tableau source avec 200 enregistrements sans ligne vide
• un tableau résultat avec 7100 noms à rechercher
• Parmi les noms à rechercher des noms absents, en double (multi-lignes), en double même ligne
• chacune des solutions de LouReedD, thev, mafraise (via formule ou via VBA), BsAlv.

J'ai tenté de comprendre les codes de chacun, en ai modifié certains pour afficher une valeur quand le nom cherché est absent, modifié un peu plus pour le code de thev qui affichait un message en cas d'absence ou de doublons et donnait des résultat bizarre chez moi (code modifié à vérifier pas son auteur surtout partie doublons que je n'ai pas approfondi).

Il serait bon que chacun regarde son code et rapporte ce qui ne va pas pour que je modifie en conséquence le fichier joint.

Pour celui de BsLav, j'avoue qu'il me laisse un peu perplexe avec des "faux négatifs" (mais il est rapide) ; je n'ai pas creusé plus loin.

Encore un point, les auteurs n'ont pas tous compris la même chose notamment quant à la recherche avec la prise en compte ou non de la colonne "nom2", la gestion des absents, la gestion des doublons.

Encore une fois le classeur joint est sans doute incomplet ou comporte des erreurs, excusez-moi par avance.

nota: un dictionary est très rapide en général mais ici on le reconstruit à chaque appel de la fonction. Je pense que c'est ça qui ralentit cette solution.

Rem: pour l'instant, c'est la formule qui devance toutes les fonctions. Comme quoi, les formules ont encore de l'avenir .

bonjour le fil,

@LouReeD,

il y avait 2 côtés à la question, la vitesse et le choix d'un dictionaire. Ma proposition utilisait dans une fonction personnalisée un "evaluate" qui crée un array avec le nom&nom2 immédiatement et recherche dans cet array. Cela est une réponse au côté "vitesse"

Pour vous rendre heureux, une fonction avec un dictionaire, mais cela n'est plus quasi "immédiatement", un tout, tout, tout petit delai.

J'ai utilisé 1.000 noms et j'ai crée un boucle pour rechercher chaqu'un. Résultat, le dictionaire est 2,5 fois plus lent que l'évaluate

Puis même technique, mais si je fais ce boucle dans un sub et la création du dictionaire n'est qu'une fois, c'est de nouveau immédiatement.

Donc, à mon avis, le dictionaire est vite, mais ici l'usage unique dans une fonction personnalisée n'était pas bien si on considère la vitesse.

Point d'attention : un dictionaire traite les majuscules et miniscules différents, si on ne dit rien. C'est pourquoi on a ces Lcase et Ucase dans la macro pour "tricher"

La macro "Test" utilise 5 méthodes et montre chaque fois le résultat pour 1.000 noms ( >>> "un grand nombre de Data ")

En effet, avec le fichier de mafraise, et en bricolant (contrairement à BasLv) une mise en mémoire du dictionnaire à l'ouverture du fichier, je passe de plus de 20 secondes à 0.395 ! Et moins d'erreur ?

Le fichier de BasLv montre bien la rapidité s'il n'y a pas de création de tableau à chaque appel de la fonction.

Merci @ vous, mais ma réflexion sur le dico n'avait rien de "méchant" Au moins la discussion est ouverte et vivante !

@ bientôt

LouReeD

re,

@LouReeD, 2.6 sec pour ma fonction ???

Avez-vous un fichier avec ces calculs ?

Bonjour @BsAlv ,

BsAlv a dit :

@LouReeD, 2.6 sec pour ma fonction ???

Avez-vous un fichier avec ces calculs ?

Vous pouvez essayez le fichier du message ICI (avec toutes les précautions que j'ai mentionnées dans le message).

Bonjour,

Ou j'ai à peu près compris toutes vos solutions Et je suis en mesure d'en adapter la substantifique moelle... Même si elles ne comportent pas de Dictionnary !.

Pour votre information le fichier réels peu comporter environ 3000 Noms et un peu moins de Nom2 dont une tapée de DUBOIS et MARTIN

Donc il faut que je réfléchisse à une autre stratégie pour récupérer l'info pertinente...

Je mets donc pour l'instant cette question en sommeil car je pense que la solution du Combo avec une recherche à la Google sera retenue.

Et là en principe, je sais faire...

Merci encore.

oei, mon ordinateur est lent quand je compare mes temps aux vôtres !!!

mais j'ai ajouté ma fonction dans la colonne L. Il y a environ 7.100 lignes (avec beaucoup de doublons). les fonctions "mafraise" résolvent 81% les autres 95%.

Ma fonction en colonne L est 2.5 fois plus lent que celle de mafraise en colonne G, un écart de 0.0007 sec par recherche

même idée que LouReeD, on crée les arrays une fois et on boucle 7.100 fois = 0.39 sec sur mon ordinateur (lent)

Sub M_BSALV()
     Dim aNoms, aNoms2, ID, aNom_Noms1

     t0 = Timer
     aNoms = Range("tableau3[nom]").Value
     aNoms2 = Range("tableau3[nom2]").Value
     ID = Range("tableau3[ID]").Value
     anom_nom1 = Evaluate("tableau3[nom]  & if(len(tableau3[nom2]),"" "","""") & tableau3[nom2]")     'matrice avec noms & noms2
     aa = Range("F3:F7102")
     ReDim aout(1 To UBound(aa), 1 To 1)

     t1 = Timer
     For i = 1 To UBound(aa)
          r = Application.Match(aa(i, 1), aNoms, 0)
          If IsNumeric(r) Then
               aout(i, 1) = ID(r, 1)
          Else
               r = Application.Match(aa(i, 1), aNoms2, 0)
               If IsNumeric(r) Then
                    aout(i, 1) = ID(r, 1)
               Else
                    r = Application.Match(aa(i, 1), anom_nom1, 0)
                    If IsNumeric(r) Then
                         aout(i, 1) = ID(r, 1)
                    Else
                         aout(i, 1) = "erreur"
                    End If
               End If
          End If
     Next

     t2 = Timer
     Range("M3").Resize(UBound(aout)).Value = aout

     t3 = Timer

     MsgBox "total  : " & Format(t3 - t0, "0.000") & vbLf & vbLf & "lire  : " & Format(t1 - t0, "0.000") & vbLf & "boucle : " & Format(t2 - t1, "0.000") & vbLf & "écrire : " & Format(t3 - t2, "0.000")
End Sub

Re BsAlv,

Les fonctions "mafraise" résolvent 81% les autres 95%.

Je ne comprends pas la remarque

Ma formule et ma fonction renvoient (et c'est voulu) :

• l'ID si le nom est présent une seule fois dans une seule colonne
• l'ID si le nom est présent une fois dans chaque colonne nom ET sur la même ligne (donc même ID)
• #REF! si le nom est présent plusieurs fois sur des lignes différentes (cas indécidable)
• #N/A si le nom est absent du tableau

Je ne vois pas d'autre cas qui justifierait les 19% non résolu mais je me trompe peut-être ?

Autre point :

même idée que LouReeD, on crée les arrays une fois et on boucle 7.100 fois = 0.39 sec sur mon ordinateur (lent)

On ne peut pas utiliser telles quelles les méthodes qui consistent à stocker un dictionary ou un array comme si la source ne devait plus jamais être modifiées. Que fait-on quand la source est modifiée ? Les dictionary ou array doivent être remis à jour et le calcul des fonctions relancé.

Dernier point :

Tout comme pour les fonctions : il faudrait définir Function mafraise(ByVal nom, xrg As Range) avec xrg la plage source ou le tableau source.

L'appel de la fonction serait =mafraise(F3;Tableau3). Ainsi si une valeur du tableau source est modifié, alors Excel recalcule automatiquement tous les appels de la fonction mafraise. Donc la source figure dans les arguments de la fonction pour déclencher le recalcul des fonctions quand la source est modifiée.

Re BsAlv,

Ma fonction en colonne L est 2.5 fois plus lent que celle de mafraise en colonne G, un écart de 0.0007 sec par recherche

Au niveau des durées, quand l'ordre de grandeur est autour des dixièmes de seconde on n'est plus à quelques fractions de seconde près. Le 2,5 fois plus lent n'est plus très important. Tout ça finit "dans un mouchoir de poche".

@galopin01, je ne comprend pas cette phrase :
Ou j'ai à peu près compris toutes vos solutions Et je suis en mesure d'en adapter la substantifique moelle... Même si elles ne comportent pas de Dictionnary !.

Ou bien vous avez un filtre sur mes messages et propositions Après le dictionnaire comme dit sur plusieurs message est plus lent en fonction qui se recalcule au "réel", mais en le créant à l'ouverture, il est rapide, il faut juste trouver le bon tempo pour le mettre à jour suite à des modifications dans la plage de données source.

@mafraise, sur votre fichier vous indiquez des erreurs "volontaires", serait-ce que vous cherchez en priorité un nom commençant par la valeur de la première colonne ? Car le code de thev et le mien donne les mêmes résultats, avec inconnu quand il n'y a pas le nom dans aucune des colonnes et un numéro d'ID quand il est trouvé au moins dans une colonne, non ?

@ bientôt

LouReeD

Re ,

LouReeD a dit :

@mafraise, sur votre fichier vous indiquez des erreurs "volontaires", serait-ce que vous cherchez en priorité un nom commençant par la valeur de la première colonne ? Car le code de thev et le mien donne les mêmes résultats, avec inconnu quand il n'y a pas le nom dans aucune des colonnes et un numéro d'ID quand il est trouvé au moins dans une colonne, non ?

Je ne retourne un Id que si cette Id est unique (que ce soit dans colonne nom1 ou colonne nom2). L'autre cas où je retourne un Id est le cas où le nom est présent deux fois (une fois dans chaque colonne nom1 et nom2) ET que les Id sont identiques (même ligne dans chaque colonne)

Dans tous les autres cas, je retourne une erreur volontaire car soit les Id sont multiples soit il n'y en a pas :

• #N/A si le nom n'est pas dans le tableau source
• #REF! si le nom! est présent plusieurs fois ET sur des lignes différentes

nota : je ne fais aucune distinction entre les deux colonnes nom1 et nom2. Elles sont traitées globalement. de manière identique ; aucune n'a priorité de l'une sur l'autre.

rem : si plusieurs occurrences sont présentes, je ne renvoie pas la première comme certains ont choisi de le faire (pourquoi ferai-je un choix puisque l'auteur ne nous a rien dit sur le sujet ?)