Bonjour à tous,
Je viens de passer une demi-journée à optimiser un fichier extrêmement lent - que je n'ai pas créé :-).
Pour chaque nouvelle entrée, on doit littéralement attendre 10 secondes avant de récupérer la main !
Voici une petite synthèse des points à vérifier :
- Évidemment, on commence par vérifier les calculs et on les optimise. Si ca n'est pas possible, on peut passer en mode manuel, mais personnellement, je préfère optimiser les formules à la place. Par exemple, des Somme.Si.Ens à la place des formules matricielles.
- Présence de mises en forme conditionnelles. Celles-ci, depuis Excel 2007, ont tendance à s'accumuler dès que l'on fait des copier-coller en conservant le format.
Voir ici pour plus de détails.
- Présence de liens externes, à la fois dans des formules, mais aussi dans des noms. Il y a plusieurs écoles, mais j'évite les liens externes au maximum, surtout si on doit en mettre beaucoup. Si on n'a pas le choix en terme d'organisation des fichiers, je préfère une solution de connexion à des données externes pour rapatrier une table, et l'actualiser à l'ouverture.
- Il semblerait - mais je n'ai pas poussé l'analyse - que faire des liens sur des fichiers stockés dans une dropbox ralentisse le système.
Malheureusement, la liste est loin d'être exhaustive !
N'hésitez pas à partager vos trouvailles !
A bientôt.
Gaëtan
Merci Gaëtan pour ces conseils !
Le premier m'a bien aidé !
J'avais fait des formules avec des calculs matriciels, et chaque rafraichissement de ma page prenait environ 10 min (!).
Je ne connaissais pas la fonction SOMME.SI.ENS, qui m'a permis de gagner énormément en temps de calcul. Ma page se rafraichit maintenant en quelques secondes. 🙂
Bien content que ca ssoit utile !
Gaetan
Bonjour.
Concernant les liens externes, ils peuvent se trouver dans une Validation de données. Les listes déroulantes peuvent faire appel à des listes situées dans un autre fichier (suite à une copie d'une feuille qui contient ces listes déroulantes)
Dominique Handelsman
Salut!
Dans un chiffrier où je stocke une grande quantité de données, j'ai ajouter une feuille pour afficher chacune des lignes de ma liste, en colonne. Pour défiler les lignes, j'ai ajouté un SpinBox avec une cellule liée pour indiquer la ligne à afficher. Malheureusement, après avoir migré de Windows 7 à Windows 10, le défilement était devenu frustrant car il y avait une lenteur…
Après investigation, j'ai ajouté un bouton Avancé et un bouton Reculé. Je n'avais plus de lenteur. J'ai supprimé le SpinBox, mais l'un de mes collègues utilisait le SpinBox avec les flèches droite et gauche.
J'ai finalement résolu le problème en remettant le SpinBox, mais en retirant la cellule lié. En VBA, j'ai ajouté des fonctions évènementiels: SpinDown, SpinUp, et j'incrémente ou décrémente manuellement la cellule désirée.
Bonjour Jocelyn, merci pour le partage, c'est bon à savoir.
Bjr,
si je peux ajouter :
* éviter tant que possible de placer des formules qui font appe à des cellules précédentes qui elles aussi font appel à des calculs a posteriori ; je veux dire éviter les formules en zig-zag surtout si elles sont complexes.
* l'ordre des onglet a aussi son importance surtout quand on fait appel à des onglets volumineux style Data qui doivent être proches de l'onglet où il y a des calculs et encore une fois si ceux-ci sont complexes. La différence est peu significative même nulle avec une très très faible volumétrie mais quand votre fichier Excel comporte des onglets DATA de plus de 50 000 lignes il y a un gain sur le bon placement des onglets
* maintenant que LET est disponible, ne pas hésiter à l'utiliser surtout quand il y a des calculs intermédiaires qui doivent se répéter, autant faire le calcul une fois puis réutiliser le résultat. Oublier les RechercheV imbriqués dans des fonctions SI par exemple, un des cas classique sur la lenteur des tableaux surtout en cas de forte volumétrie
* si possible quand on peut, éviter les fonctions volatiles sur des tableaux volumineux
* cas classique avec la fonction Aujourdhui() ça ne sert à rien de la propager 1000 fois dans un tableau ! si cela est possible faire une ligne de code qui va alimenter un champ DATE_DU_JOUR par exemple à l'ouverture du classeur. Pour cela on utilisera la fonction Date avec le formatage voulu via la fonction Format en VBA.... le jour ne change pas toutes les nano-secondes.... c'est seulement à 23:59:59... 🙂
* réduire la diagonale des données => gain espace occupé sur disque et mémoire. En effet Excel calcule une matrice pour définir la place nécessaire (Ceux qui ont connu Lotus 123 ça doit leur parler)
* Attention les MFC ça rend service.... mais en abondance = prudence, toujours sur de la grosse volumétrie
* éviter si possible les procédures événementielles.... car là aussi en cas de forte volumétrie il peut y avoir des conséquences
* cas d'une procédure événementielle qui met en surbrillance la ligne et la colonne active par exemple, oui c'est chouette quand vous avez 10 000 + des calculs.... les Perf ne sont plus les mêmes
* quand vous avez des données constantes mettez dans des champs nommés. Un champ nommé peut contenir une formule, une adresse, mais aussi directement une valeur. C'est de cette manière que l'on simule une constante dans les formules.
Bjr,
si je peux ajouter :
* éviter tant que possible de placer des formules qui font appe à des cellules précédentes qui elles aussi font appel à des calculs a posteriori ; je veux dire éviter les formules en zig-zag surtout si elles sont complexes.
* l'ordre des onglet a aussi son importance surtout quand on fait appel à des onglets volumineux style Data qui doivent être proches de l'onglet où il y a des calculs et encore une fois si ceux-ci sont complexes. La différence est peu significative même nulle avec une très très faible volumétrie mais quand votre fichier Excel comporte des onglets DATA de plus de 50 000 lignes il y a un gain sur le bon placement des onglets
* maintenant que LET est disponible, ne pas hésiter à l'utiliser surtout quand il y a des calculs intermédiaires qui doivent se répéter, autant faire le calcul une fois puis réutiliser le résultat. Oublier les RechercheV imbriqués dans des fonctions SI par exemple, un des cas classique sur la lenteur des tableaux surtout en cas de forte volumétrie
* si possible quand on peut, éviter les fonctions volatiles sur des tableaux volumineux
* cas classique avec la fonction Aujourdhui() ça ne sert à rien de la propager 1000 fois dans un tableau ! si cela est possible faire une ligne de code qui va alimenter un champ DATE_DU_JOUR par exemple à l'ouverture du classeur. Pour cela on utilisera la fonction Date avec le formatage voulu via la fonction Format en VBA.... le jour ne change pas toutes les nano-secondes.... c'est seulement à 23:59:59... 🙂
* réduire la diagonale des données => gain espace occupé sur disque et mémoire. En effet Excel calcule une matrice pour définir la place nécessaire (Ceux qui ont connu Lotus 123 ça doit leur parler)
* Attention les MFC ça rend service.... mais en abondance = prudence, toujours sur de la grosse volumétrie
* éviter si possible les procédures événementielles.... car là aussi en cas de forte volumétrie il peut y avoir des conséquences
* cas d'une procédure événementielle qui met en surbrillance la ligne et la colonne active par exemple, oui c'est chouette quand vous avez 10 000 + des calculs.... les Perf ne sont plus les mêmes
* quand vous avez des données constantes mettez dans des champs nommés. Un champ nommé peut contenir une formule, une adresse, mais aussi directement une valeur. C'est de cette manière que l'on simule une constante dans les formules.
ce sont des petits truc certes mais mis bout à bout ça peut être énorme 🙂