Optimiser vitesse d'un fichier Excel
Bonjour,
Je suis lancée dans une vaste campagne d'optimisation de la vitesse de fonctionnement d'un gros fichier excel.
1. Voici ce que je compte faire : n'hésitez pas à compléter cette liste en me donnant vos conseils sur les pratiques qui peuvent accélérer mon fichier.
A/ Faire la chasse aux fonctions réitérées :
• max( : 295.000 occurences !!! -> abaissé à 234.000 occurences
• min(: 33.000 occurences !!!) -> 14.000 occurences
• somme(: 211.000 occurences !!!) -> 140.000 occurences *
• somme.si( : 37.000 occurences !!!) -> 34.000 occurences *
• GRANDE.VALEUR( : 37.000 occurences !!!) -> 7.000 occurences
• PETITE.VALEUR( : 4.000 occurences !!!) -> 1.300 occurences
• recherchev(: 170.000 occurences !!!)
• rechercheh( : 3.000 occurences !!!)
• rang(: 218.000 occurences !!!)
• nb.si(: 788.000 occurences !!!)
* réduire le nombre de cellules utilisées dans des fonctions telles que Somme et Somme.si. La durée de calcul est proportionnelle au nombre de cellules utilisées (les cellules inutilisées sont ignorées) ;
B/ déplacer les calculs répétés vers une ou plusieurs cellules d’assistance, puis faire référence à ces cellules à partir des formules d’origine
C/ Lookups on sorted data
D/ Remplacer les fonctions "si(esterreur(" (89.000 occurences) en utilisant la fonction "sierreur(" qui est + rapide (https://docs.microsoft.com/fr-fr/office/vba/excel/concepts/excel-performance/excel-improving-calcuation-performance)
2. J'ai une question précise :
est-ce qu'une fonction recherchev() qui parcourt de nombreuses colonnes est significativement + lente qu'une recherchev() qui s'applique à un petit nombre de colonnes?
Autrement dit, est-ce que RECHERCHEV(A1;Feuil1!A1:EZ1332;120;FAUX) est beaucoup plus lent que RECHERCHEV(A1;Feuil1!A1:B1332;2;FAUX) ?
J'ai essayé de faire un test, mais sur un petit fichier, je ne vois pas d'écart. Pourtant il me semblait avoir lu que des recherchev() sur de larges plages était à éviter.
3. Autre question précise : si j'ai une formule du type : si(a1="";"";etc...)
dans une telle formule, lorsque A1 est égal à "", est-ce que la dernière partie contenue dans la formule si() qui est résumée ici par "etc..." est calculée?
Autrement dit, est-ce que la complexité du "etc..." impacte le temps de calcul de cette cellule, même lorsque A1 est bien égal à "" ?
Merci d'avance et Bonne journée,
Nicole
Bonjour,
Je ne comprend pas la première partie et comme j'ai la flemme de chercher dans tes innombrables post s'il y a un embryon de fichier qui pourrait m'éclairer...
Pour les questions 2 et 3 je n'ai pas testé mais serais tenté de répondre sans hésiter non... Ce qui ne manquera pas de motiver les éventuels contradicteurs !
A+
Bonjour nicopat, galopin01,
Quand on travaille sur des grands fichiers excel de ce type, il faut abandonner les formules pour du Power Query ou du VBA, j'ai pas fais le calcul mais tu es presque a 1 Million de cellules avec formules, pas étonnant que le fichier soit lent.
Bonjour,
sur des volumes pareils, si tu as beaucoup de répétitions du même calcul l'option 1B va grandement améliorer les performances.
Bonjour,
Je ne comprend pas la première partie et comme j'ai la flemme de chercher dans tes innombrables post s'il y a un embryon de fichier qui pourrait m'éclairer...
Pour les questions 2 et 3 je n'ai pas testé mais serais tenté de répondre sans hésiter non... Ce qui ne manquera pas de motiver les éventuels contradicteurs !
A+
Bonjour,
La première partie présente les axes auxquels je pense pour faire la chasse aux temps perdus.
Notamment réduire le recours à certaines formules qui sont théoriquement longues, mais cela ne semble pas apporter d'amélioration très significative pour l'instant.
Bonjour,
Pour une amélioration significative, il vaut mieux supprimer purement et simplement les formules du fichier.
Des formules telles que SOMME ou SOMME.SI trouvent facilement leur équivalent via un TCD (Tableau Croisé Dynamique), le cas inverse, toutes les formules peuvent être évaluées via VBA pour n'inscrire que le résultat dans les cellules (le fichier est alors nettement plus "light").
Pour être plus précis, il faudrait transmettre un petit fichier qui illustre les éléments présents dans le tien et les calculs attendus.
Bonjour nicopat, galopin01,
Quand on travaille sur des grands fichiers excel de ce type, il faut abandonner les formules pour du Power Query ou du VBA, j'ai pas fais le calcul mais tu es presque a 1 Million de cellules avec formules, pas étonnant que le fichier soit lent.
Je crois que j'ai entre 3 et 6 millions de cellules avec formules.
Je suis en train de regarder des vidéos de présentation de Power Query pour voir ce dont il s'agit, mais travaillant actuellement sur Excel 2007, je ne pourrais pas l'utiliser à court terme.
Pour ce qui est du VBA, je comprends comment l'utiliser dans des macros actionnée ponctuellement, mais je ne sais pas comment le VBA pourrait permettre de se susbsituer à des formules classiques comme cet exemple :
=SI(ET($HW$3-$HX$3=4;NB.SI(Z4:AF4;Z4)=1;NB.SI(Z4:AF4;AB4)=1;NB.SI(Z4:AF4;AD4)=1;NB.SI(Z4:AF4;AF4)=1);"";SI(ET(HI4=0;HW4="";OU(ET(SOMME(BQ4:BU4)=4;MAX(BQ4:BU4)=1);ET(SOMME(BR4:BV4)=4;MAX(BR4:BV4)=1);ET(SOMME(BS4:BW4)=4;MAX(BS4:BW4)=1);ET(SOMME(BT4:BX4)=4;MAX(BT4:BX4)=1);ET(SOMME(BU4:BY4)=4;MAX(BU4:BY4)=1);ET(SOMME(BV4:BZ4)=4;MAX(BV4:BZ4)=1);ET(SOMME(BW4:CA4)=4;MAX(BW4:CA4)=1);ET(SOMME(BX4:CB4)=4;MAX(BX4:CB4)=1);ET(SOMME(BY4:CC4)=4;MAX(BY4:CC4)=1);ET(SOMME(CC4;BQ4:BT4)=4;MAX(CC4;BQ4:BT4)=1);ET(SOMME(BQ4:BU4)=5;MAX(BQ4:BU4)=2;NB.SI(BQ4:BU4;2)=1);ET(SOMME(BR4:BV4)=5;MAX(BR4:BV4)=2;NB.SI(BR4:BV4;2)=1);ET(SOMME(BS4:BW4)=5;MAX(BS4:BW4)=2;NB.SI(BS4:BW4;2)=1);ET(SOMME(BT4:BX4)=5;MAX(BT4:BX4)=2;NB.SI(BT4:BX4;2)=1);ET(SOMME(BU4:BY4)=5;MAX(BU4:BY4)=2;NB.SI(BU4:BY4;2)=1);ET(SOMME(BV4:BZ4)=5;MAX(BV4:BZ4)=2;NB.SI(BV4:BZ4;2)=1);ET(SOMME(BW4:CA4)=5;MAX(BW4:CA4)=2;NB.SI(BW4:CA4;2)=1);ET(SOMME(BX4:CB4)=5;MAX(BX4:CB4)=2;NB.SI(BX4:CB4;2)=1);ET(SOMME(BY4:CC4)=5;MAX(BY4:CC4)=2;NB.SI(BY4:CC4;2)=1);ET(SOMME(CC4;BQ4:BT4)=5;MAX(CC4;BQ4:BT4)=2;NB.SI(CC4;2)+NB.SI(BQ4:BT4;2)=1)));recherchev(A1;BU4:BY4;4;faux);""))Merci à tous pour vos réponses !
Ca semble très compliqué d'uploader une version light de mon fichier (données confidentielles, quasi impossible d'anonymiser, taille du fichier, requêtes vers des tables access stockées localement...).
J'ai des questions supplémentaires :
#4. il me semble que la fonction recherchev() prend du temps quand elle concerne une plage étalée sur de nombreuses lignes.
J'ai ajouté récemment à mon fichier un feuillet contenant des données (18.000 lignes x 33 colonnes) et dans d'autres feuillets j'ai des formules recherchev() qui vont chercher des valeurs dans cette table et j'observe un ralentissement.
Quelqu'un sait-il s'il y a une limite à ne pas franchir pour éviter les lenteurs?
(cette question est semblable à la question #2 mais sur le nombre de ligne au lieu du nombre de colonnes).
Ou alors est-ce le fait d'ajouter un nouveau feuillet (le 142ème) avec des données qui ralentit l'ensemble?
#5. sans changer le contenu de mon fichier, pensez-vous qu'une version ultérieure à Excel 2007 accélérerait le fonctionnement?
J'avais lu que 2007 était plus rapide que les suivantes car celles-ci comportaient des modules / fonctionnalités nouvelles qui alourdissaient l'ensemble.
#6. J'ai lu que les MFC étaient source de lenteur. Normalement, il n'y en a pas dans mon fichier, mais je souhaiterais carrément désactiver cette fonctionnalité pour ce fichier pour voir si je gagne en vitesse. Est-ce possible ?
Merci
PS : je joins l'analyse FastExcel de mon fichier : 86MO, 142 feuillets, 8.000.000 cellules non vides, 3.000.000 formules.
Le feuillet 1 semble être significativement plus lent que les autres car il comprend beaucoup d’occurrences de la fonction VBA customisée décrite ici : https://forum.excel-pratique.com/viewtopic.php?f=2&t=126254
Toutefois, si la réponse est bien Non à la question #3 de mon post initial (ce qui semble bien être le cas), cette fonction n'est active qu'à une quinzaine de reprise selon les cas.