Améliorer la vitesse de calcul d'une macro [Résolu]

Messages postés
20
Date d'inscription
mercredi 25 janvier 2012
Statut
Membre
Dernière intervention
9 janvier 2019
-
Bonjour, je cherche à améliorer le temps de calcul d'une macro.

Pour faire simple :
Dans une feuille A, j'ai un tableau qui fait dans les 2500 lignes et avec environ 150 colonnes. Les 2500 lignes augmentent au fil des mois.
Dans la 1ere colonne, j'ai une clé unique. Dans les 149 suivantes j'ai des données qui doivent arriver de la feuille B en fonction de la clé unique et parfois d'autres critères.

Dans la feuille B, j'ai un tableau avec environ 30 000 lignes et 50 colonnes. Les 30 000 lignes augmentent au fil des mois.
Dans la 1ere colonne on retrouve la clé qui était unique en feuille A mais qui peut se répéter en feuille B

Pour remplir les 149 colonnes de la feuille A, je dois faire des RecherchesV, des somme.si.ens et des nb.si.ens.

Par exemple pour remplir A2 je dois sommer toutes les valeurs de ma clé unique (A1) retrouvées dans la feuille B et qui correspondent au critère 2

Puis pour remplir A3 je dois sommer toutes les valeurs de ma clé unique (A1) retrouvées dans la feuille B et qui corresponde au critère 3

Etc. Etc. Dans l'exemple ci-dessus c'est un somme.si.ens mais comme je vous l'ai dit ça peut être d'autres formules genre rechrecheV ou nb.si.ens.

Du coup vu que j'ai testé 2 techniques.

Solution 1 je fais une boucle sous VBA:

x = 1
While Worksheets("Feuille A").cells(x,1) <>""

Worksheets("Feuille A").cells(x,2)=worksheetfunction.sumifs(Range_a_sommer, Range_1, critere_1, range_2, critere_2)

Worksheets("Feuille A").cells(x,3)=worksheetfunction.vlookup(critere1, Range_1,numero_de_colonne, 0)

etc colonne 3

etc colonne 4

etc, etc... jusqu'à 149

x = x + 1

Wend


Dans ce cas la c'est très long à coder et c'est très long a exécuter car la macro parcoure toutes les lignes colonne 2 puis colonne 3 puis 4 et jusqu'à 150 donc 2500x149= 372 500 calculs

Solution 2:

- Je créé une 1ere ligne dans mon tableau feuille A qui restera toujours en 1ere ligne.
- Dedans je met toutes les formules nécessaires aux 149 colonnes
- Dans ma macro je désactive les calculs
- J'étire les 149 colonnes jusqu'en bas des 2500 lignes
- Je réactive les formules
- Je copie/colle tout en valeur pour ne pas avoir mes 372 500 calculs qui moulinent à cahque fois que je clic quelque part.

Résultat ici aussi c'est ultra long.

Je voudrais donc savoir, n'y a-t-il pas moyen auquel je n'aurais pas penser et qui pourrait aller plus vite. Par exemple si je me base sur la solution 1. Est-ce qu''il n'y a pas moyen d'appliquer ma formule de la colonne 2 à toute la colonne 2 d'un coup plutôt que de faire du ligne à ligne.

Merci d'avance pour votre ingéniosité.
Afficher la suite 

4 réponses

Messages postés
589
Date d'inscription
mercredi 5 octobre 2016
Statut
Membre
Dernière intervention
26 juin 2019
46
0
Merci
Bonjour,

en premier lieu, tu n'en parles pas donc tu peux utiliser
 Application.ScreenUpdating = False
à mettre au début de ton code sans oublier de le rétablier à
True
à la fin
ça a pour effet d’exécuter le code sans que l'écran ne suive. Tous les calculs se font et une fois fait, l'écran s'actualise.. ça fait gagner un peu sur la rapidité d'exécution

tu en demandes beaucoup en même temps aussi, suivant les performances de ton ordi c'est sûr que ça peut être long...

as tu pensé à scinder ton tableau sur plusieurs feuilles qui se mettraient à jour uniquement sur
Worksheet_Activate()
? ça serait problèmatique pour toi?

geo0258
Messages postés
20
Date d'inscription
mercredi 25 janvier 2012
Statut
Membre
Dernière intervention
9 janvier 2019
-
Bonjour Fabien merci pour la réponse j'avais déjà mis :
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Mais c'est quand même très long.

Pour la solution qui consiste à scinder le tableau en plusieurs:
Le truc c'est que lorsqu'il y a une mise à jour en feuille B je clic sur mon bouton pour alimenter la feuille A avec les mises à jour et/ou les nouvelles lignes rajoutées en feuille B. C'est seulement une fois que mes 149 critères sont calculés que ça me donne un résultat, en l'occurrence un prix pour un produit. Du coup même si je scinde en 3 ou 4 tableaux, le temps que chacun se remplisse les uns après les autres sera aussi long que de remplir une fois le gros tableau.

Tu ne sais pas si c'est possible de dire à toute une range que dans chaque cellule elle doit faire une rechercheV en fonction d'un critère qui est sur sa propre ligne? Plutôt que de dire a une cellule de faire une rechecheV, puis de passer à la cellule suivante, puis la suivante etc etc?
fabien25000
Messages postés
589
Date d'inscription
mercredi 5 octobre 2016
Statut
Membre
Dernière intervention
26 juin 2019
46 -
Mais c'est quand même très long oui je m'en doute, c'est pour ça que j'ai bien précisé "un peu"

J'ai déjà vu un code du type
Range("A1", "B1", "C1").Value=toto
(pas sûr de la synthaxe) mais à part économiser des lignes de code, le programme passera toujuors dans toutes les cases

Désolé je n'ai pas trop de solutions à part changer d'ordi ou scinder ton fichier pour essayer de fluidifier un peu l'ensemble...
Je laisse la main (mais je continue à suivre pour ma culture)
geo0258
Messages postés
20
Date d'inscription
mercredi 25 janvier 2012
Statut
Membre
Dernière intervention
9 janvier 2019
-
Ok merci quand même :)
Commenter la réponse de fabien25000
Messages postés
1938
Date d'inscription
mercredi 27 juillet 2005
Statut
Membre
Dernière intervention
16 août 2019
765
0
Merci
Bonsoir,

Tu devrais travailler en mémoire soit dans des tableaux, soit dans des dictionaries.
Tu charges tes données en mémoire, tu les travailles dans la structure la plus adaptée (tableau ou dictionary) et tu les écris par bloc dans ta feuille.
Tu peux gagner en vitesse dans un rapport surprenant par rapport à ton approche.
Il faudrait en savoir plus sur les calculs que tu fais pour t’aider plus efficacement.

Cordialement
Commenter la réponse de JvDo
Messages postés
15934
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
16 octobre 2019
2811
0
Merci
Bonjour tout le monde,

Pour appuyer Fabien et Jvdo que je salue, ci joint une démo de rapidité pour trouver une occurrence ("clé") dans une colonne de 16000 lignes (clé située en ligne 15000)

https://mon-partage.fr/f/O27iFrdo/

En ce qui te concerne avec plusieurs clés,avec la méthode Find, il te faut boucler que sur le nombre de clés et transporter les données de la ligne dans l'autre feuille.... fais signe si tu coinces.

Ou bien tenter avec un filtre...


Commenter la réponse de michel_m
Messages postés
20
Date d'inscription
mercredi 25 janvier 2012
Statut
Membre
Dernière intervention
9 janvier 2019
0
Merci
Bonjour à tous. Avec beaucoup de retard je vous répond. J'ai réussi à améliorer la vitesse de ma macro grâce à un tableau croisé dynamique intermédiaire. Ce dernier me réalise tous les somme.si et nb.si et a l'avantage de se rafraîchir en 1sec maxi. Je fais ensuite des recherchesV depuis mon tableau B vers le TCD et je colle le tout en valeur pour limiter le nombre de calcul actif dans mon fichier.

Toutes ces recherchesV, même s'il y en a plus de 300k prennent un fraction de seconde, alors que les somme.si et nb.si sont très très gourmand en puissance de calcul nécessaire et mettent plusieurs minutes. Ce qui est bien c'est que le TCD, lui, fait tout ça en 1 seconde, je ne sais pas comment.

En tout cas ça marche maintenant et ma macro ne fait pluq que d'etirer des rechercheV puis de les copier coller en valeur.

Voilà voilà merci à ceux qui auront essayé de m'aider.
Commenter la réponse de geo0258