Variables Tableaux pour remplacer Vlookup...

La braise

XLDnaute Occasionnel
Bonjour à Tous,

Dans une macro, je réalise de nombreux vlookup.
J'ai un fichier à mapper de 50.000 lignes et une base de mapping de 30.000 lignes.

Pour cela j'utilise des vlookup.

Un autre post ouvert sur l'optimisation de ces vlookup m'a dirigé vers l'utilisation de Variables tableaux manifestement beaucoup plus rapide que les recherches verticales....
(voir : https://www.excel-downloads.com/threads/excel-booster-les-performances-avec-de-la-ram.123800/)

J'ai joint un fichier exemple de la méthode que j'utilise comprenant :
- un onglet avec les données à mapper ("DATA")
- un onglet avec la base de mapping ("Mapping")
- la macro

Le fichier est ouvrable dans les anciennes versions d'Excel sans télécharger le pack microsoft.

J'ai simplifié le fichier à 1000 lignes à mapper et une base de mapping de 500 lignes.

Ma question est simple :
Est-ce que ma méthode actuelle est la plus efficace?
Qu'est ce que cela donnerait en utilisant des variables tableaux?


****************
PS : Dans ma macro de production, j'utilise un timmer de début et de fin pour mesurer via un datediff la durée de traitement et donc le taux de traitement en le divisant par le nb de calcul à effectuer (nb de lignes x nb de colonnes).

Mais je n'arrive pas à faire un datediff en millisecondes :)mad:) et donc cet outil de mesure d'efficacité de la méthode ne fonctionne pas et me retourne une erreur de type (impossible de diviser par zéro...).
Si quelqu'un sait comment le calculer en millisecondes, je suis client... afin de mesurer quelle est la méthode la plus efficace.
****************

D'avance merci à tous pour votre aide toujours aussi efficace.
 

Pièces jointes

  • exemple01.zip
    42.7 KB · Affichages: 91
  • exemple01.zip
    42.7 KB · Affichages: 97
  • exemple01.zip
    42.7 KB · Affichages: 106
Dernière édition:

BOISGONTIER

XLDnaute Barbatruc
Re : Variables Tableaux pour remplacer Vlookup...

Bonjour,

-Si la table est TRIEE, on peut spécifier 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), il faut écrire:

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

Lors d'une recopie des formules sur 3.000 lignes, letemps de recalcul est égal à 20s pour Faux,instantané pour Vrai


http://boisgontierjacques.free.fr/fichiers/RechercheVVrai.zip
http://boisgontierjacques.free.fr/fichiers/RechercheVFaux.zip




JB
 
Dernière édition:

Etienne2323

XLDnaute Impliqué
Re : Variables Tableaux pour remplacer Vlookup...

Salut La Braise,
voici un exemple avec des variables tableaux. Tu peux essayer et voir si ça te convient ? J'ai détaillé le code pour que tu puisses bien comprendre le principe.

Je n'ai pas encore eu le temps de regarder pour ton timer. Dès que j'ai une chance, j'y jette un oeil. En attendant, j'espère que cela te conviendra.

Bonne fin de journée !

Étienne

Cijoint.fr - Service gratuit de dépôt de fichiers
 

La braise

XLDnaute Occasionnel
Re : Variables Tableaux pour remplacer Vlookup...

Merci à vous deux pour ces deux réponses rapides très riches en enseignements... :)

Dans l'ordre :

à BOISGONTIER :
Ton test est foudroyant... :D
J'ai hâte de le tester en prod. avec mes 50.000 lignes sur 35.000 de base. Avec le temps de traitement rallongé.. mon timmer marchera et je pourrais comparer. Pour info, avec faux je plafonnais à 1.400/vlookup/s.
Je te tiens au courrant asap!

Juste une précision, au sujet du Tri de la base à effectuer :
Il faut trier la base de mapping Ok, mais également les données à traiter?

à Etienne2323 :
J'ai ouvert ton fichier, le test est également foudroyant. Comme pour la solution de BOISGONTIER je vais la tester et je pourrais en mesurer l'efficacité. Merci beaucoup pour le détaillage du code. Je vais étudier tout ça...:D Attention il se peut que je revienne vers toi pour des précisions... je suis un peu :eek: en regardant ton code!

C'est la solution vers laquelle je veux tendre. Mais je vais d'abord tester celle de BOISGONTIER en raison de la facilité à mettre en oeuvre.

Pour le timmer, du coup je vais tout tester sur ma vraie base et donc comme le traitement sera > 1s, mon timmer actuel en secondes fonctionnera! :) Donc aucune urgence sur la question pour moi!


Merci à vous deux pour ces pistes de réflexion qui vont m'agiter pendant un moment.. :)

J'ai aussi entendu parler d'une solution avec des ".Find".
Tant qu'à traiter la question autant essayer d'être exaustif ;) donc si quelqu'un à des idées autant profiter de ma base de test grandeur nature pour la présenter et en mesurer son efficacité.

Si quelqu'un à encore une solution différente... Enjoy.
 

Etienne2323

XLDnaute Impliqué
Re : Variables Tableaux pour remplacer Vlookup...

Salut La Braise,
je ne crois pas qu'une solution avec des .find serait adaptée à ta situation. Vu l'ampleur de la surface à couvrir, cela ne ferait que ralentir l'exécution de la macro en raison des incroyablement nombreux .select entre les onglets.

Pour ce qui est de ma piste de solution, elle est adaptée à un nombre de lignes dynamiques, donc je ne croirais pas que tu sois obligé de modifier le code. Si jamais tu as des questions, n'hésite pas !

Bonne continuité !

Étienne
 

BOISGONTIER

XLDnaute Barbatruc
Re : Variables Tableaux pour remplacer Vlookup...

Avec fonction personnalisée:

Table de 30.000 éléments non triée.

http://boisgontierjacques.free.fr/fichiers/RechercheVFonctionPerso2.zip

Code:
Sub essai()
 t = Timer()
 [B2:B10000] = rechv([a2:a10000], [Noms], [Prenoms])
 [c2:c10000] = rechv([a2:a10000], [Noms], [Ages])
 MsgBox Timer() - t  ' 0,78 sec
End Sub

Function rechv(champ As Range, cles As Range, valeurs As Range)
 a = cles
 b = valeurs
 c = champ
 Dim d()
 Set mondico = CreateObject("Scripting.Dictionary")
 For i = 1 To cles.Count
   mondico.Add a(i, 1), b(i, 1)
 Next i
 ReDim d(1 To champ.Count)
 For i = 1 To champ.Count
    d(i) = mondico.item(c(i, 1))
 Next i
 rechv = Application.Transpose(d)
End Function

http://boisgontierjacques.free.fr/fichiers/RechercheVFonctionPerso.zip

JB
 
Dernière édition:

La braise

XLDnaute Occasionnel
Re : Variables Tableaux pour remplacer Vlookup...

Bonjour à Tous,

Je reviens avec les résultats du comparatif Vlookup avec VRAI ou FAUX sur ma grosse base de prod.

J'ai effectué 2 types de test :

Type:__________________Avec screenupdating :________Sans screenupdating :

Vlookup( ; ; ;FAUX)__________1.464 calculs/s_______________1.546 calculs/s
Vlookup( ; ; ;VRAI)__________34.090 calculs/s______________35.714 calculs/s


Le bilan est sans appel... 23x plus rapide.

Seul problème (comme évoqué par BOISGONTIER) quand la valeur n'existe pas dans la base de mapping le calcul remonte la valeur précédente de la base.

Je vais essayer la solution de BOISGONTIER pour identifier les refs manquantes de la base de mapping.

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

Je vous tiens au courant...

PS : en vacances jusqu'au 30/07! :)
 
Haut Bas