Lenteur Excel

Vilain

XLDnaute Accro
Bonjour à tous,

Je suis très content de revenir aujourd'hui sur le forum et content de voir que les tauliers sont encore là ;)

Je reviens aujourd'hui avec une problématique de lenteur d'excel. J'utilise des fichiers très lourds du fait d'un grand nombre de lignes (>350 000 lignes) et de formules gourmandes (sommeprod, somme.si.ens etc...). J'utilise une machine de guerre (32 go de ram, un processeur dernière génération etc...). Lorsque je lance une formule, cela prend beaucoup, beaucoup de temps. J'ai essayé différentes choses qui améliore un peu le temps de calcul (fichier en binaire notamment). C'est un peu mieux mais loin d'être suffisant.
Mes recherches sur le forum et sur internet en général se sont avérées infructueuses pour le moment.
Vous avez d'autres idées ?

Merci d'avance :)
 

Vilain

XLDnaute Accro
Bonjour à tous les 2 et merci pour vos réponses (je me doutais que Job serait la pour apporter son aide :D),
j'ai oublié de préciser que j'ai passé le mode de calcul en manuel.
Je cherche à éviter de passer par VBA car j'ai de nombreuses formules à faire, essayer, remplacer, revenir en arrière, etc...
Il n'y a pas une solution concernant l'allocation des ressources ?

Merci d'avance
 

zebanx

XLDnaute Accro
Bonsoir à tous,

Arrivons par expérience à quelques constats :
  1. Certains fichiers contenant effectivement des formules consommatrices comme sommeprod exigeront de travailler sur plusieurs fichiers plutôt qu'un seul (saucissonage de la base volumétrique)
  2. Certaines parties de tableau contiennent peut-être des zones qui peuvent être actualisées par une macro permettant de faire un "autofill" des différentes formules (exemple : par colonne ou par groupe de colonne si les formules sont simples) puis à faire un copie coller valeur jusqu'à la prochaine intégration de donnée.
Dans les deux cas, le VBA est incontournable.
Mais quand on "travaille" sur l'exploitation de 350 000 lignes, on n'est pas étonné non plus.
C'est la conclusion à laquelle je suis parvenu pour quelques bases en tout cas (avec un manque évident de patience à voir un fichier "compilé" ramer).
Cas concret pour moi : 1 fichier de 50000 lignes (10M) fait les traitements en 45 secondes, ce qui peut paraitre long, au lieu de x minutes. Mais quand tu as connu ces x minutes, 45 secondes te paraissent acceptables.

Dernier point : tu n'indiques pas la taille de ton fichier au départ.
Il y a peut-être un nettoyage de lignes non utilisées à faire (il y a des fils sur le sujet pour les lancer par une macro).
Et passer ces bases en XLSB plutôt qu'en XLSM si tu ne l'as pas déjà fait.
 
Dernière édition:

eriiic

XLDnaute Barbatruc
Bonjour à tous,

est-ce que passer en xlsb ne serait pas une légende ?
Le fichier sera plus petit et donc chargé plus vite.
Mais au-delà de ça j'ai dans l'idée qu'en mémoire la taille sera la même. Si le soucis est l'utilisation de la mémoire virtuelle, il ne devrait pas y avoir de différence.
Quant aux formules et codes, vu qu'ils sont identiques pas de raison que ça change.
Quelqu'un a-t-il déjà fait des tests confirmant cette 'accélération' ? (hors temps de chargement)
eric
 

zebanx

XLDnaute Accro
Bonjour à tous,
est-ce que passer en xlsb ne serait pas une légende ?
Le fichier sera plus petit et donc chargé plus vite.
Mais au-delà de ça j'ai dans l'idée qu'en mémoire la taille sera la même. Si le soucis est l'utilisation de la mémoire virtuelle, il ne devrait pas y avoir de différence.
Quant aux formules et codes, vu qu'ils sont identiques pas de raison que ça change.
Quelqu'un a-t-il déjà fait des tests confirmant cette 'accélération' ? (hors temps de chargement)
eric

Bonjour Eriiiic

Sur le fichier évoqué dans mon post, un peu plus rapide à l'exécution de certaines macros (~2 secondes sur une des macros qui traite sur XLSM en 19 secondes) mais c'est surtout à l'ouverture / sauvegardant que je trouve l'exécution beaucoup plus rapide (2 secondes au lieu de 6/7 pour la sauvegarde par exemple pour un fichier de taille 20Mo en XLSM et 10 en XLSB).
Cela n'est pas significatif en soit, mais quand tu as beaucoup de fichiers à traiter (en l'espèce 18 à 20 fichiers pour l'exécution de ces code là), ce n'est pas neutre.
Donc en pratique, par défaut en xlsm s'il y a des macros mais pour certains fichiers où ça ramerait plus, ce sera en xlsb.

Bonne journée
zebanx

config : I5, 8Go de ram, ssd 480 go. C'est pas la bête de course de Vilain.
 

eriiic

XLDnaute Barbatruc
Merci pour ton retour zebank
Je suis quand même étonné des ~2 secondes sur une des macros qui traite sur XLSM en 19 secondes, ce qui ferait 10% de gain.
Tu peux confirmer en faisant 2 lancements successifs et en retenant le 2nd temps ?
eric
 

zebanx

XLDnaute Accro
Re-bonjour Eriiic,

En reprenant le code et un timer (pas pris au départ estimation à la "grosse" sur un seul test), j'ai isolé une condition "activeworkbook.save" qui venait fausser la démarche et l'objectif recherché.
Reprécisons que ce sont bien les mêmes fichiers, l'un ayant été juste été réengistré en xlsm pour le test.

En prenant la ligne de sauvegarde sur deux lancements successifs
Temps exécution fichier xlsm : 18.7s et 18.8s
Temps exécution fichier xlsb : 17.5s et 17.6s

En ne prenant pas la ligne de sauvegarde sur deux lancements successifs
Temps exécution fichier xlsm : 16.4s et 16.3s
Temps exécution fichier xlsb : 16.6s et 16.7s

Donc ma remarque en #10 n'était pas justifiée car c'est bien la sauvegarde qui "freinait" inutilement le premier code.
Entonnement même, on gratte un peu de temps en xlsm.:rolleyes:

Désolé d'avoir induit en erreur le forum et merci de m'avoir demandé de refaire le test, c'était nécessaire de le faire et avec un timer dédié au test.

@+
zebanx
 

eriiic

XLDnaute Barbatruc
Merci d'avoir pris le temps :)
J'en avais l'intuition, mais entre ce qu'on pressent et la réalité il y a des fois des écarts.
A-priori les seuls gains se limitent donc aux lectures/écritures. Et sur gros fichiers.
Les qq dixièmes en plus des .xlsb sont peut-être dus à une remise en forme après chargement.
eric
 

eriiic

XLDnaute Barbatruc
Des pages MS très riches sur l'optimisations des feuilles :


S'aider de l'outil RefTreeAnalyser, version gratuite.
Elle permet de faire un audit de temps de calcul des feuilles, qu'on peut affiner en le faisant sur une plage sélectionnée.
Ca permet de voir sur quelles zones doivent porter en priorité les efforts d'optimisation.
eric
 

Statistiques des forums

Discussions
294 211
Messages
1 936 896
Membres
188 099
dernier inscrit
dedispotter01