> Tous les forums > Forum Bureautique
 lenteur INDEX/MATCH avec variables tableauSujet résolu
Ajouter un message à la discussion
Page : [1] 
Page 1 sur 1
Boogie Palace
  Posté le 23/10/2013 @ 16:13 
Aller en bas de la page 
Petit astucien

bonjour le forum,

dans mes procédures VBA, j'utilise régulièrement WorksheetFunction.Index/Match pour récupérer des données dans des plages de cellules.

en observant le gain de temps obtenu de manière générale en utilisant les variables tableau plutôt que des plages de cellules, je me suis dit que je pouvais transposer mes recherches matricielles INDEX/MATCH en les faisant -non plus sur des plages de cellules- mais dans des variables tableau, avec Application.Index/Match (à la place de WorksheetFunction).

je m'attendais à un gain de temps considérable, mais patatras, l'exécution est dix fois plus longue qu'une recherche dans une plage physique (feuille de calcul) avec WorksheetFunction.

je cherche à comprendre pourquoi cette lenteur, est-ce une question de déclaration de type de mon array ?
en général je fais des déclarations implicites : MON_ARRAY = Range("A1:z100")
dans ce cas, je suppose que MON_ARRAY est de type Variant (?)
sinon je le fais aussi parfois de façon explicite quand j'ai besoin d'une portée hors procédure, en type Variant (car mon tableau peut contenir du texte, des dates, des nombres décimaux).

je ne fais pas qqch correctement ou alors cette contre performance en recherche matricielle est un défaut connu des variables tableau ?
merci de vos lumières et/ou conseils sur le sujet.

BP

Publicité
ferrand
 Posté le 24/10/2013 à 00:07 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonsoir,

Tes questions deviennent très spécialisées !

Application.Index ou Application.Match me laissent perplexes. Pas trouvé de référence à une telle syntaxe et j'aurais le sentiment que VBA applique les WorksheetFunction correspondantes, sans doute dans des conditions peu optimales...

Pour MON_ARRAY = Range("A1:Z100"), là tu affectes à la variable MON_ARRAY (variable de type Variant, et non Tableau) un tableau des valeurs contenues dans la plage indiquée (la propriété Value étant la propriété par défaut d'un objet Range).

Je crois qu'il serait mieux de déclarer une telle variable en tableau et lui affecter alors les valeurs voulues au moyen de boucles, ou bien la déclarer comme variable objet et lui affecter la plage en tant que telle.

Je testerais aussi la recherche sur un tableau ou sur une variable plage au moyen de boucles, pour pouvoir comparer à l'utilisation de fonctions de feuilles de calcul.

Je t'avoue par ailleurs que je ne me préoccupe de la durée qu'en cas de problème, ce qui reste très empirique et je ne me risquerai pas à affirmer une plus grande vitesse d'exécution pour telle ou telle méthode sans référence fiable ou expérimentation confirmée... Galopin pourrait certainement t'éclairer plus en ce domaine.

Je suis la discussion et y reviendrai si ça recoupe mon expérience (sauf les 2 prochains jours où je suis dans la nature !).

Boogie Palace
 Posté le 24/10/2013 à 12:13 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

bonjour ferrand et merci de ton aide.

en effet, Application.Index n'est pas aussi documenté que WorksheetFunction.Index, mais ça semble bien être son pendant pour les variables, car WorksheetFunction.Index ne fonctionne que sur des plages de cellules.
pour illustrer mon cas, j'ai créé un fichier exemple avec les deux méthodes de recherche (dans la feuille, et dans une variable tableau).

http://cjoint.com/?CJyl2iEStMs

la recherche est simple : chercher dans la zone orange le texte correspondant à chacun des numéros de ma zone verte.
dans les deux méthodes, je crée une variable tableau (enfin ce que j'appelais une "variable tableau", mais qui s'avère être une variant si j'ai bien compris) pour stocker les résultats, avant de les coller sur la feuille, car ça va plus vite que d'inscrire les résultats dans les cellules une à une.

la seule différence entre les deux procédures est la méthode de recherche, c'est d'ailleurs pourquoi j'ai chronométré uniquement ce segment de procédure.
sur mon poste :
recherche dans la feuille : 0,4 seconde
recherche dans la variable : 8 interminaaaaaaaables secondes (pour seulement 3000 occurrences)

tu m'apprends un truc concernant la différence entre une variable variant qui contient un tableau de valeurs et une "véritable variable tableau".
je suis très surpris de cette subtilité, car mes variables variant semblaient se manipuler comme des variables tableau, pour moi c'était pareil.

bref, je vais creuser cette notion car c'est peut-être la clé du problème, une recherche dans une vraie variable tableau serait peut-être bien plus rapide qu'une recherche dans une variable variant qui contient un tableau de valeurs.
peut-être pourras-tu (ou tout autre astucien/ne !) me montrer comment modifier mon code en conséquence pour obtenir de meilleurs performances ? (idéalement meilleur que la recherche dans la feuille)
je vais tenter des trucs de mon côté (je ne manquerai pas de vous tenir au courant si je fais avancer le schmilblik).

Boogie Palace
 Posté le 25/10/2013 à 14:54 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

bonjour,

ferrand, j'ai creusé cette histoire de variable tableau / variant.
j'ai trouvé un paragraphe sur le site www.excelabo.net dont voici un extrait :

Si à la rigueur on peut se passer de déclarer ses variables classiques dans excel (en omettant l'instruction Option Explicit en début de module), il est obligatoire de le faire pour les arrays pour dire à excel que c'est une variable contenant plusieurs données.
On peut le faire de différentes façons

Dim A() : A = Array(1, 2, 3)
Dim B : B = Array(1, 2, 3)

sont deux écritures qui fonctionnent mais attention elles ne veulent pas dire la même chose :
A est un array qui contient des variables.
B est une variable qui peut contenir n'importe quoi. Pour le moment c'est array qui contient des variables.
Et alors ? Très souvent vous ne verrez pas la différence dans vos macros entre les deux façons de déclarer vos arrays. Mais si vous utilisez une fonction qui attend un array comme argument (pour y chercher un élément par exemple), vous aurez un message d'erreur si vous lui passez B comme argument. Déclarer Dim A() vous assure que ce A est un array et rien d'autre.

bon, ce que j'en comprends, c'est que la différence est plus philosophique qu'autre chose, mais j'ai malgré tout modifié mon script de la recherche dans la variable.
en rouge les trucs ajoutés par rapport à la version d'origine :

Dim ARRAY_PLAGE_RESULTAT(), ARRAY_PLAGE_RECHERCHE()

[PLAGE_RESULTAT].Range(Cells(2, 2), Cells(4001, 2)).ClearContents
ARRAY_PLAGE_RESULTAT() = [PLAGE_RESULTAT].Value
ARRAY_PLAGE_RECHERCHE() = [PLAGE_RECHERCHE].Value

DEBUT = Timer
For i = 2 To UBound(ARRAY_PLAGE_RESULTAT, 1)
ARRAY_PLAGE_RESULTAT(i, 2) = _
Application.Index(ARRAY_PLAGE_RECHERCHE, _
Application.Match(ARRAY_PLAGE_RESULTAT(i, 1), Application.Index(ARRAY_PLAGE_RECHERCHE, , 1), 0), 2)
Next i
FIN = Timer

[PLAGE_RESULTAT].Formula = ARRAY_PLAGE_RESULTAT
MsgBox Round(FIN - DEBUT, 1) & " secondes"

résultat des courses : 8 secondes, le même temps que la version d'origine :(



Modifié par Boogie Palace le 25/10/2013 14:58
ferrand
 Posté le 25/10/2013 à 19:31 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Je rentre juste ! je regarderai tes expérimentations plus tard (là... ).

Il y a une différence entre une variable Variant contenant un tableau et un variable tableau dont on sait qu'elle peut réserver parfois quelque surprise même si la plupart du temps ça paraît fonctionner de la même façon. J'ignore cependant si il peut y avoir un écart en vitesse d'exécution...

Boogie Palace
 Posté le 13/11/2013 à 11:50 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

bonjour,

ferrand, cette histoire de différence entre variant contenant un tableau et variable tableau a dû faire des ricochets dans ma tête et j'ai finalement eu l'idée de passer ma zone de recherche en objet plutôt qu'en variable.
TADAAAA ! c'était ça l'astuce à trouver, car l'exécution est encore plus rapide que la recherche "physique" sur une feuille avec WorksheetFunction.Index/Match.
la syntaxe reste la même, c'est même plus simple car cet objet s'utilise comme une plage de cellule (voir le script ci-dessous).

j'ai refait des tests sur mon fichier joint dans mon message du 24.10 :
la recherche "physique" dans une plage de cellules prenait 0.42s (la méthode la plus rapide que j'avais testé)
la recherche dans une variant contenant un tableau prenait en moyenne 7.92s (8.03s dans une variable tableau)

et la recherche dans un objet prend 0.11s (soit 4 fois plus vite que la recherche physique)
et en utilisant WorksheetFunction au lieu de Application, on grappille un chouilla avec 0.09s (c'est peau d'zob ici, et même sur des gros volumes ça fait qq dixièmes, ça reste négligeable, mais autant grappiller si on peut).

en rouge ce qui change par rapport à l'original (j'ai retiré le chrono qui n'est pas la partie intéressante) :
(désolé pour l'indentation, je ne trouve plus l'option du style de paragraphe qui affiche comme dans VBE)

[PLAGE_RESULTAT].Range(Cells(2, 2), Cells(4001, 2)).ClearContents 'reset de la zone de résultat
ARRAY_PLAGE_RESULTAT = [PLAGE_RESULTAT] '= zone verte
Set OBJET_PLAGE_RECHERCHE = [PLAGE_RECHERCHE] '= zone orange

For i = 2 To UBound(ARRAY_PLAGE_RESULTAT, 1)
ARRAY_PLAGE_RESULTAT(i, 2) = _
Application.Index(OBJET_PLAGE_RECHERCHE, _
Application.Match(ARRAY_PLAGE_RESULTAT(i, 1), OBJET_PLAGE_RECHERCHE.Columns(1), 0), 2)
Next i

[PLAGE_RESULTAT].Formula = ARRAY_PLAGE_RESULTAT



Modifié par Boogie Palace le 13/11/2013 11:54
ferrand
 Posté le 20/11/2013 à 11:52 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Salut,

Je note ton expérience...

Page : [1] 
Page 1 sur 1

Vous devez être connecté pour poster des messages. Cliquez ici pour vous identifier.

Vous n'avez pas de compte ? Créez-en un gratuitement !


Sujets relatifs
Faire un tableau sur Excel avec des sauts de lignes
lenteur avec open office
vba : ds tableau, insérer ligne (non entière) avec reprise formule
tranposer un tableau vert./ horizontal avec sommes
Tableau avec OpenOffice.org Calc
Application.Match dans variable tableau à 2 dimens
créer tableau excel avec débit crédit et solde
Faire un tableau avec les données qu'en ligne ??
Partage de classeur excel avec tableau croisé
creer une macro avec des variables
Plus de sujets relatifs à lenteur INDEX/MATCH avec variables tableau
 > Tous les forums > Forum Bureautique