Optimiser un ( gros ) fichier Excel

[Kernoz]

Bonjour à tous,

J'utilise un fichier *.xlsm pour avoir les statistiques de tous mes clients sur plusiseurs années.

Ce fichier comporte enormement de formules sur plusieurs onglets et parfois plus de 55000 lignes par onglets.

J'ai aussi beaucoup de formules qui font reference à d'autres fichiers ex : ( "=c:\BDD\Tarif[Tarif2010.xls]Tarifs'!B50 )

Moults macro ...

Bref lorsque je lance le calcul de l'ensemble des cellules de ce fichier, je dois attendre presque 5 minutes que celui ci se fasse ( malgré un PC recent ).

Le fichier fait plus de 24Mo !!!

N'y a t-il pas un moyen d'optimiser ce fichier ? J'ai deja essayé de supprimer les colonnes et lignes vides en dessous et à droite des mes données mais je n'ai gagné que 700Ko...

Merci d'avance, nous avons besoin d'utiliser ce fichier tous les jours et ca devient penible 🙁

 

[CISCO]

Re : Optimiser un ( gros ) fichier Excel

Bonjour

Sans info supplémentaire, cela m'étonnerait que tu obtiennes une réponse efficace.

Ne peux tu découper le problème en plusieurs fichiers, ou réduire le fichier pour pouvoir poster ce résumé sur le forum ?

@ plus

 

[CBernardT]

Re : Optimiser un ( gros ) fichier Excel

Bonjour Kernoz, Cisco,

Excel est très gourmand en ressource windows. Avec autant de données à traiter, ce n'est pas étonnant que cela soit long.

Il est peut-être temps de penser à développer une application maison avec une vrai base de données tel qu'Access, etc...

 

[hoerwind]

Re : Optimiser un ( gros ) fichier Excel

Bonjour, salut CISCO et CbernardT,

Je suppose que les données antérieures à la dernière année ne se modifient plus.
Si c'est le cas, pourquoi ne pas faire un copier - collage spécial valeurs de ces feuilles sur elles-mêmes, ce qui éliminerait toutes les formules, donc accélération du temps de calcul.

De même pour les macros, ne peuvent-elles pas se limiter à la dernière feuille et non au classeur entier ?

A la limite, pourquoi ne pas créer un classeur par année ?

Voir aussi si d'éventuelles formules matricielles ne peuvent pas être remplacées par des formules normales faisant référence à une colonne intermédiaire.

Supprimer au maximum les MFC (très gourmandes !)

 

[Kernoz]

Re : Optimiser un ( gros ) fichier Excel

Merci à tous pour vos réponses.

Je ne peux pas hélas vous transmettre une copie meme simplifiée de mon fichier ( trop complexe ).

Je pense éffectivement qu'il va dans un premier temps falloir que je "bidouille" pour supprimer le maximum de données / formules pour l'alleger.

Ensuite, il faudra que je me pense sur la solution acces, même si je ne connait quasi pas ce logiciel.

Par contre pourriez vous me dire quels élements sont les plus "gourmands" sur un fichier excel ? mis à part les MEF.
Que je puisse orienter mon "ménage".

Merci. 😀

 

[Misange]

Re : Optimiser un ( gros ) fichier Excel

Bonjour

La piste qui consiste à figer les formules des feuilles qui ne doivent plus bouger (années passées) est à suivre en priorité.
Mettre les données actuelles et les archives dans des classeurs séparées en est une autre qui a le mérite de la sécurité. Avoir un si gros classeur sur excel avec des données précieuses est un très gros risque. Que ceux qui n'ont jamais eu un classeur excel qui un jour "attrape la vérole" lève la main 🙁 C'est toujours sur LE classeur le plus important que ça tombe bien sur.

Ensuite il y a une chose qui gagne énormement de temps dans les macros, c'est de travailler avec des arrays et d'éviter comme la peste de boucler sur des lignes.
On prend une plage entière, on la colle dans un array, on fait tout le boulot par macro dans l'array et on recolle le tout dans la feuille en une seule instruction. Fais les tests qui sont décrits sur ces pages
Ce lien n'existe plus
et tu verras que c'est jusqu'à 1000 fois plus rapide que de faire des bouclettes.

Très important : mettre le mode de calcul sur manuel avant de lancer des macros, le remettre en auto à la fin.

TRES gros consommateur de ressources : les formules matricielles, y compris les sommeprod. Toujours les faire sur le plus petit nombre de lignes possibles (utiliser à outrance les tables d'excel 2007, à défaut nommer les plages avec la fonction decaler)

Ne pas mettre 350 graphiques dans un même classeur. Au besoin les construire par macro au moment de l'activation de la feuille.

Travailler au max par macro et coller les résultats, pas les formules dans la feuille.

ça fait déjà quelques pistes ?

 

[flyonets44]

Re : Optimiser un ( gros ) fichier Excel

Bonsoir
Comme il a été écrit + haut tu ne conserves que les valeurs pour toutes les données anciennes qui ne bougeront plus
et tu supprimes toutes les liaisons possibles pour ne garder que les indispensables
Si possible réduire le nombre de police de caractères utilisées et reduire aussi le nombre de formatage personnalisé
cordialement
flyonets

 

[CBernardT]

Re : Optimiser un ( gros ) fichier Excel

Re,

Sans voir ton code, c'est difficile de dire comment tu peux améliorer celui-ci...

ScreenUpdating est le premier choix
Peut-être que tu utilises trop de Select ?
Peut-être qu'il y a des boucles qui ne sont pas vraiment nécessaires ?
Peut-être y a-t-il trop de formules ? Elles pourraient être créées par code puis transférées en valeur ?

 

[Jam]

Re : Optimiser un ( gros ) fichier Excel

Bonsoir à tous,

Je vais pas dire autre chose que mes chers camarades ici, car il y a tant à dire sur le sujet de l'optimisation d'un classeur. Mais tu peux peut-être nous mettre ton code à dispo, car à défaut nous pouvons/pourrons peut-être te soumettre quelques améliorations importantes.

Ah, et pour moi y'a un truc qu'il ne faut pas oublier: Excel est un tableur, Access (ou mySQL, ou SQLServer...) une base de données. Le premier est spécialisé dans le calcul, le second sur le traitement des données => Excel ne sera jamais un SGBD même avec plusieurs millions de lignes. Bref, a chacun son job 😉 Reste la possibilité de mixer le meilleur des 2 mondes. Excel en FrontEnd, Access (ou tout autre gestionnaire de BDD) en BackEnd.

Bon courage.

 

[Kernoz]

Re : Optimiser un ( gros ) fichier Excel

Merci à tous.

J'ai deja de bonnes pistes !!!

J'ai effectivement beaucoup ( enormement ) de sommeprod. Je vais etudier la piste des Arrays.

Ensuite, je chercherais des tutoriaux pour acces.

🙂

 

[Jam]

Re : Optimiser un ( gros ) fichier Excel

Slt Kernoz,

N'hésite pas à jeter un oeil sur BDSOMME(). Fonction souvent mésestimée, surtout dans le cadre de BDD.
Pour tes sommeprod, veille à utiliser des plages nommées. C'est plus efficace que d'utiliser les références.

Bon courage