Excel, lenteur et liaisons

Bonjour à tous,

Je travaille actuellement sur un fichier dont je ne suis pas le propriétaire.

Ce dernier est trèèèèèèèèèès lent, que ce soit à l'ouverture, et surtout à l'enregistrement car il fait tout simplement une taille de ... (n'allez pas vous pendre svp) 170Mo. Oui oui 170 Mo. Il y a de nombreuses formules pas optimisées etc mais comme je n'en suis pas propriétaire, je dois faire avec.

Je cherche cependant à comprendre d'où vient cette masse plus qu'importante car lorsque je copie les différentes feuilles (dont une de 10 000 lignes) dans un nouveau classeur, j'arrive à une taille laaargement plus raisonnable (même si tjs importante) de 6Mo qui me permet d'enregistrer rapidement sans que le fichier plante. Actuellement il me faut pas moins de 6 minutes pour enregistrer ce fichier, je met désormais une quinzaine de secondes. Or je ne peux pas me servir de ce nouveau fichier pour diverses contraintes internes à l'entreprise.

Ma question est donc : D'où pourrait venir cette différence ? Je sais que le fichier original possède 4 graphiques ayant 3 séries de 10 000 lignes, cependant, ce n'est pas ça qui constitue la majorité du poids. Je sais aussi que ces 4 même graphiques sont liés avec 100 à 200 fichiers words (là aussi contrainte interne).

J'aurai donc souhaité savoir si les liaisons s'actualisaient en arrière plan sans même avoir le fichier word d'ouvert et si elles pouvaient être à l'origine de ce poids/ralentissement ? J'en doute car lorsque je copie uniquement le fichier (et pas les feuilles), il reste à 170 Mo, pourtant les liaisons ne sont plus actives avec ce dernier, le chemin ayant changé.

Et sinon, qu'est ce qui augmente le plus le poids d'un fichier excel ? Le fichier possède quelques formules matricielles SOMMEPROD qui extrait des données d'un autre fichier Excel. Oui c'est le bordel, c'est pour ça que je sollicite votre aide, ça dépasse mes compétences ^^

Toutes les données étant confidentielles, je ne pourrais vous fournir un exemple du fichier.

En vous remerciant pour vos lumières !

Edit : Le fichier tourne sous Excel 2003.

Bonjour,

La méthode pour alléger et rajeunir un fichier vieillissant est celle que tu as utilisée.

Soit remettre le contenu dans un support neuf... Tu n'atteindras pas le même résultat sur le fichier initial lui-même...

Tu peux cependant passer chaque feuille au peigne fin pour éliminer toute mise en forme au-delà de la zone utile.

(tu vérifies l'extension de la plage utilisée de chaque feuille et tu détruis toute zone qui en fait partie dans laquelle il n'y a rien...)

Au-delà tu dis que tu ne peux y toucher, donc pas grandchose d'autre à faire.

Cordialement.

Parfait je te remercie ! Donc rien a voir avec les liaisons entre fichier ?

Je trouve ça "bizarre" qu'un fichier puisse s'alourdir de 150 et + Mo au cours du temps, alors que la taille d'informations contenue dedans ne soient que de 6Mo. Ca vous est déjà arrivé ? J'avais jamais fait attention, un espèce de cache en soit ?

Et ma 2ème question : Je suis pas un expert en Excel, mais j'ai toujours préféré les fonctions Excel au VBA. J'ai toujours lu a droite et à gauche que le VBA alourdissait les fichiers plus que de simples formules. Est-ce vrai ? Un VBA rend-il plus lent un fichier ? (je sais que ça dépend de l'optimisation, des fonctions, du pc etc ...). Faut-il que je reste dans mon optique d'utiliser à tout prit des formules "basiques" et switcher sur le VBA en dernier recours ?

Merci pour vos avis !

Les formules alourdissent un fichier, par leur poids si elles occupent beaucoup de place et par le temps de recalcul qui rend l'utilisation rapidement moins fluide...

VBA n'occupe que le poids du texte ! Et sert effectivement à alléger un fichier en limitant les formules.

Pour ce qui est des liaisons entre fichiers, je n'ai plus trop d'expérience car depuis des lustres je fuis ce mode d'utilisation en utilisant systématiquement VBA pour toute relation entre fichiers. Je ne crois pas que cela contribue plus que le poids des formules au poids du fichier, mais je pense que cela peut jouer dans le ralentissement, selon paramètres de mise à jour.

Un fichier prend toujours un peu de poids en vieillissant, tous les éléments anciens disparus ne le rétablissent pas exactement dans l'état antérieur. La meilleure cure d'assainissement est, comme tu l'avais fait, de transférer le contenu dans un classeur neuf.

L'utilisation des nouveaux formats est aussi moins gourmandes : s'il doit toujours tourner sur 2003 tu es réduit à le laisser en xls, sinon il y aura un gain à le passer en xlsx ou xlsm, ou xlsb(format conseillé pour les gros fichier) [mais d'autant plus efficace avec une rénovation telle que tu l'avais entreprise]

La façon dont j'envisage volontiers les choses (mais tout est à voir dans la situation particulière d'utilisation) consiste à : utiliser des formules dans les calculs courants dans des zones limitées, éviter les formules sur de grandes zones, VBA pour les automatismes d'utilisation et éviter les trop-plein de formules et les liaisons, notamment éviter toute formules dans ce qui est bases de données, à partir d'une certaine importance classeur dédié à part pour une base de données, fonctions spécifique d'utilisation également dans des classeurs spécifiques et un classeur principal pour piloter l'ensemble et accueillir l'essentiel du code VBA, également ne pas conserver d'éléments inutiles qui peuvent être reconstitués à la demande à partir des données (sauf éventuellement raison juridique, mais là on exporte en PDF).

En fait, si je résume de façon un peu plus ordonnée, il y a d'abord les données dont on se servira, que l'on doit conserver, mais qu'on n'a pas besoin de voir, qu'il faut stocker sans mise en forme de la façon la plus économique en poids et en les renvoyant vers un autre classeur si leur présence tend à alourdir le classeur principal ; il y a ensuite ce qu'on voit et ce qu'on traite, où là une mise en forme est nécessaire pour présenter des données de façon utile et effectuer éventuellement tel ou tel traitement. Mais on ne voit simultanément que ce qu'on peut afficher sur l'écran, donc une façon de présenter les données pour les consulter ne justifie au plus qu'une seule feuille par type de présentation que l'on souhaite, ce qui évite de multiplier des feuilles à structure identique dont on ne pourra voir qu'une à la fois ; enfin excentrer vers des classeurs satellites tout ce qui n'est pas d'utilisation permanente mais relève de traitements particuliers.

Cordialement.

Bonjour,

Pour compléter MFerrand contrôle quand même que tu n'aies pas de lignes ou colonnes inutiles sur chaque feuilles.

Ctrl+Shift+Fin pour avoir la dernière cellule et supprimer les vides inutiles.

Si ce sont des graphiques croisés dynamiques dans les options du TCD onglet Données tu peux décocher 'Enregistrer les données sources avec le fichier'.

eric

Bonjour et merci pour vos réponses.

En effet j'ai essayé de regarder si je n'avais pas des formules masquées en bas de ligne car sous Excel 2003, mes 65 000 lignes étaient constamment afficher, même après enregistrement.

Au final j'ai rafraîchi le fichier sur un nouveau classeur pour arriver à un poids beaucoup plus raisonnable de 4,5 Mo (le poids du fichier avait doublé en 1 semaine et était à présent de 352 Mo ... Inutilisable pour tous mes collaborateurs, il était temps que je m'inquiète du problème).

Ce ne sont pas des graphiques dynamiques mais merci de l'idée, ça pourra me servir sur d'autres fichiers !

Je ne savais pas que VBA ne prenait en compte que le poids du texte et non son utilisation, je vais essayer de m'y pencher de plus prêt car ce type de code pourrait avoir une utilisation bien plus large au sein de mon entreprise.

Dernière question : Vous pensez que le mode de compatibilité de 2003 vers 2007 puisse générer des erreurs à l'origine de mon problème ? Je vais surveiller étroitement l'évolution de ce dernier, j'arriverais peut être à en trouver la cause.

Merci beaucoup à vous deux, et surtout toi MFerrand pour ces explications !

Cordialement,

Je ne savais pas que VBA ne prenait en compte que le poids du texte et non son utilisation,

Hors exécution le code n'est que du texte.

A l'exécution, il occupe la place qu'il lui faut pour travailler, mais la libère ensuite...

Bonne journée.

Bonjour,

Vous pensez que le mode de compatibilité de 2003 vers 2007 puisse générer des erreurs à l'origine de mon problème ?

Un fichier .xls est considérablement plus lourd qu'un .xlsm qui est un fichier zippé.

eric

MFerrand a écrit :

Je ne savais pas que VBA ne prenait en compte que le poids du texte et non son utilisation,

Hors exécution le code n'est que du texte.

A l'exécution, il occupe la place qu'il lui faut pour travailler, mais la libère ensuite...

Bonne journée.

C'est bon à savoir. Je te remercie pour l'info !

bonjour à tous

pour la gestion de grande quantité d'info, Microsoft (qui a conçu Excel) conseille de passer à Access.

et personnellement, dès que je songe à VBA, je passe à Access (ou autre SGBD).

https://www.google.fr/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0ahUKEwi7yOzpl_vQAhUHWhoKHSp7A9sQFggcMAA&url=https%3A%2F%2Fsupport.office.com%2Ffr-fr%2Farticle%2FUtiliser-Access-ou-Excel-pour-g%25C3%25A9rer-vos-donn%25C3%25A9es-09576147-47d1-4c6f-9312-e825227fcaea&usg=AFQjCNHJac42YJqmpG0tinV4Gc7bMy6oZg

notez qu'Excel est conçu pour exploiter* des données venant de sources diverses (Excel lui-même, Access ou toute autre base).

et qu'Access est aussi sujet à gonflements injustifiés, mais beaucoup plus lentement qu'Excel.

* faire des synthèses avec calculs (TCD, graphiques...)

Bonjour,

J'ai constaté que passer un fichier en *.xlsb allège et accélère son traitement.

Rechercher des sujets similaires à "lenteur liaisons"