Fonction perso RechVM() plusrapide que rechercheV() pour tableaux taille importante

[BOISGONTIER]

Bonsoir,


Sur Excel 2002



Avec RechercheV() classique
-Tableau de recherche : 20.000 éléments
-Si on modifie les 2.600 valeurs cherchées , le temps de recalcul est de 5 s

Avec la fonction perso matricielle RechVM(), le temps n'est pas visuellement mesurable

Function RechvM(clé As Range, champ As Range, colResult)
  Application.Volatile
  Set d = CreateObject("Scripting.Dictionary")
  a = champ.Value
  b = clé.Value
  For i = LBound(a) To UBound(a)
    d(a(i, 1)) = a(i, colResult)
  Next i
  Dim temp()
  ReDim temp(LBound(b) To UBound(b))
  For i = LBound(b) To UBound(b)
    temp(i) = d( b(i, 1))
  Next i
  RechvM = Application.Transpose(temp)
End Function

JB

 

[mapomme]

Re : Fonction perso RechVM() plusrapide que rechercheV() pour tableaux taille importa

Bonjour BOISGONTIER ,

C'est rapide, très rapide!

Juste pour simuler recherchev(X, tablo, n, faux) qui arrête la recherche à la première occurence de X (et non la dernière), peut-être pourrait on remplacer la boucle For i = LBound(a) To UBound(a) par For i = UBound(a) To LBound(a) Step -1 ?

 

[BOISGONTIER]

Re : Fonction perso RechVM() plusrapide que rechercheV() pour tableaux taille importa

>Juste pour simuler recherchev(X, tablo, n, faux) qui arrête la recherche à la première occurence de X (et non la dernière), peut-être pourrait on remplacer la boucle For i = LBound(a) To UBound(a) par For i = UBound(a) To LBound(a) Step -1 ?


-Le résultat pour chaque valeur recherchée se fait sans boucle grâce au dictionnaire:

résultat=Dictionnaire(CléCherchée)

L'accès à une clé d'un dictionnaire est 100 fois + rapide qu'une recherche séquentielle dans un tableau (l'accès aux clés d'un dictionnaire doit se faire par hash-code)

-La boucle For i = LBound(b) To UBound(b) calcule toutes les valeurs recherchées

-RechercheV() classique explore la table pour chaque valeur recherchée
donc sur l'exemple, il y a 2.600 explorations de la table Matable


JB

 

[mapomme]

Re : Fonction perso RechVM() plusrapide que rechercheV() pour tableaux taille importa

(re)

Ce que je voulais dire (ça ne concernait pas le principe de la fonction qui est super):

La fonction Excel rechercheV avec paramètre Faux s'arrête à la première valeur trouvée dans le cas où cette valeur se retrouve plusieurs fois dans le tableau (du moins me semble-t il).
La fonction matricielle RechvM passe toutes les occurences pour retenir la dernière valeur (mais je me trompe peut-être). Donc en remplissant le dico depuis "le bas" du tableau vers "le haut", on retiendrait bien la 1ere occurence de la valeur cherchée comme la fonction RechercheV.

NB: en tout cas, j'ai déjà rangé votre fonction RechvM dans mon tiroir à trésor.

 

[job75]

Re : Fonction perso RechVM() plusrapide que rechercheV() pour tableaux taille importa

Bonjour JB, salut mapomme,

Bravo JB pour cette découverte, l'objet Dictionary est vraiment d'une grande richesse.

La question qu'on peut se poser : pourquoi la fonction RECHERCHEV n'est-elle pas plus rapide ?

Même si Dictionary n'existait pas quand elle a été créée Bill pourrait la mettre à jour.

Edit pour mapomme : tu as raison, et on pourrait aussi écrire :

For i = LBound(a) To UBound(a)
  If Not d.exists(a(i, 1))  Then d(a(i, 1)) = a(i, colResult)
Next

A+

 

[BOISGONTIER]

Re : Fonction perso RechVM() plusrapide que rechercheV() pour tableaux taille importa

Bonjour,

>La question qu'on peut se poser : pourquoi la fonction RECHERCHEV n'est-elle pas plus rapide ?


-Le paramètre VRAI est généralement utilisé pour une recherche sur une valeur numérique dans une table triée. Le positionnement se fait sur la valeur exacte si elle existe et sur la valeur inférieure si la valeur exacte n'existe pas

-Recherchev() sur un code peut être rapide dans le cas suivant (souvent ignoré)

La table est TRIEE, on spécifie le paramètre VRAI.
La recherche est alors faite par DICHOTOMIE et peut être x100 + RAPIDE puisqu'il suffit de quelques
accés pour retrouver le code. C'est TRES IMPORTANT lorsque la table est de taille importante et que
la formule Recherchev() est recopiée x1000 fois (Avec FAUX , Excel consulte la table SEQUENTIELLEMENT).
Pour vérifier si le code existe (on ne récupère pas #N/A mais la valeur inférieure si le code n'existe pas), il faut alors écrire:

=SI(RECHERCHEV(CodeCherché;Articles;1;VRAI)=CodeCherché;
RECHERCHEV(CodeCherché;Articles;2;VRAI);"Inconnu")

Une autre version qui retourne toutes les occurences d'une clé dans une cellule: RechvMult()

Function RechvMult(clé As Range, champ As Range, colResult)
  Application.Volatile
  Set d = CreateObject("Scripting.Dictionary")
  a = champ.Value
  b = clé.Value
  For i = LBound(a) To UBound(a)
    If d.exists(a(i, 1)) Then
      d(a(i, 1)) = d(a(i, 1)) & " : " & a(i, colResult)
    Else
      d(a(i, 1)) = a(i, colResult)
    End If
  Next i
  Dim temp()
  ReDim temp(LBound(b) To UBound(b))
  For i = LBound(b) To UBound(b)
    temp(i) = d(b(i, 1))
  Next i
  RechvMult = Application.Transpose(temp)
End Function

Une autre version qui retourne toutes les occurences d'une clé dans plusieurs colonnes: RechvMult2()

Function RechvMult2(clé As Range, champ As Range, colResult)
  '---- retour des occurences dans plusieurs colonnes
  Application.Volatile
  ncol = Application.Caller.Columns.Count
  Set d = CreateObject("Scripting.Dictionary")
  a = champ.Value
  b = clé.Value
  For i = LBound(a) To UBound(a)
    If d.exists(a(i, 1)) Then
      d(a(i, 1)) = d(a(i, 1)) & " : " & a(i, colResult)
    Else
      d(a(i, 1)) = a(i, colResult)
    End If
  Next i
  Dim temp()
  ReDim temp(LBound(b) To UBound(b), 1 To ncol)
  For i = LBound(b) To UBound(b)
    tmp = d(b(i, 1))
    tbl = Split(tmp, ":")
    For k = LBound(tbl) To UBound(tbl)
      If k <= ncol - 1 Then temp(i, k + 1) = tbl(k)
    Next k
  Next i
  If ncol > 1 Then RechvMult2 = temp Else RechvMult2 = Application.Transpose(temp)
End Function

JB

 

[job75]

Re : Fonction perso RechVM() plusrapide que rechercheV() pour tableaux taille importa

Re,

Je réponds à ma question.

1) Pour la recherche d'une seule valeur RECHERCHEV est beaucoup plus rapide car le temps d'établissement du Dictionary est loin d'être négligeable.

Voir le fichier joint et lancer cette macro sur chaque feuille :

Sub Tests()
'lancer la macro sur chacune des feuilles
t = Timer
Application.Calculation = xlCalculationManual
For i = 1 To 100
  [G2].Calculate
Next
Application.Calculation = xlCalculationAutomatic
MsgBox Timer - t
End Sub

En G2 la formule est rendue volatile par la fonction ALEA().

2) Quand il y a beaucoup de valeurs à rechercher (calcul matriciel de JB) la fonction personnalisée est plus rapide car le Dictionary est créé une seule fois pour toutes les valeurs.

A+

 

[BOISGONTIER]

Re : Fonction perso RechVM() plusrapide que rechercheV() pour tableaux taille importa

>1) Pour la recherche d'une seule valeur RECHERCHEV est beaucoup plus rapide car le temps d'établissement du Dictionary est loin d'être négligeable.
Quand il y a beaucoup de valeurs à rechercher (calcul matriciel de JB) la fonction personnalisée est plus rapide car le Dictionary est créé une seule fois pour toutes les valeurs.


Le cas no 2 est très fréquent. Le but de cette fonction était précisément de répondre à ce problème.

Prétendre que la fonction est beaucoup plus rapide pour un seul recherchev() et seulement plus rapide pour le cas no 2 relève de la mauvaise foi.

Mes mesures avec calculate

-Tableau de recherche : 20.000 éléments
-Si on modifie les 2.600 valeurs cherchées , le temps de recalcul est de 0,12 seconde contre 3,9 s avec Recherchev()
-Pour 100 valeurs cherchées, le temps de recalcul est 0,12 seconde contre 0,23s avec Recherchev()
-Pour 50 valeurs cherchées, le temps de recalcul est 0,12 seconde contre 0,16s avec Recherchev()
-Pour 20 valeurs cherchées, le temps de recalcul est 0,12 seconde contre 0,13s avec Recherchev()

Sub temps()
  t = Timer()
  Calculate
  MsgBox Timer() - t
End Sub

Sub auto()
   Application.Calculation = xlAutomatic
End Sub

Sub manuel()
   Application.Calculation = xlManual
End Sub

JB

 

[BOISGONTIER]

Re : Fonction perso RechVM() plusrapide que rechercheV() pour tableaux taille importa

Annulé

JB

 

[BOISGONTIER]

Re : Fonction perso RechVM() plusrapide que rechercheV() pour tableaux taille importa

Bonjour,

Variante de la fonction avec message d'erreur

Function RechvM(clé As Range, champ As Range, colResult, messageErreur)
  Application.Volatile
  Set d = CreateObject("Scripting.Dictionary")
  a = champ.Value
  b = clé.Value
  For i = LBound(a) To UBound(a)
    d(a(i, 1)) = a(i, colResult)
  Next i
  Dim temp()
  ReDim temp(LBound(b) To UBound(b))
  For i = LBound(b) To UBound(b)
    If d(b(i, 1)) <> "" Then temp(i) = d(b(i, 1)) Else temp(i) = messageErreur
  Next i
  RechvM = Application.Transpose(temp)
End Function

=rechvm(F2:F2673;matable;2;"Inconnu")

=SI(ESTERREUR(RECHERCHEV(F2:F2;matable;2;FAUX));"Inconnu";RECHERCHEV(F2:F2;matable;2;FAUX))

Test avec Excel 2002:
2.600 valeurs cherchées modifiées :0,12 s contre 6,2 sec (RechercheV)
10 valeurs cherchées modifiées :0,08s contre 0,09sec (Recherchev)

JB