Problème temps de latence saisie Excel

Bonjour à tous,

Dans un gros fichier excel (50 Mo dans la vidéo que nous avons enregistrée pour illustrer le problème : https://youtu.be/s6vTOg8kOSo, 75Mo dans la version finale), nous observons un temps de latence de 2 à 4 secondes quand on change le contenu d'une cellule, malgré le fait que le fichier soit utilisé en recalcul manuel (donc pas de calcul automatique).

Ce temps de latence se produit lorsque l'on change le contenu d'une première cellule, voire de 2 cellules, et puis plus rien, plus de latence. Mais si on lance un recalcul, le temps de latence se produit de nouveau dès lors que l'on change le contenu d'une ou 2 cellules...

Sur le long terme, c'est très pénible et cela rend le fichier quasi inutilisable. Nous souhaitons donc supprimer ou réduire ce temps de latence.

Nous utilisons actuellement excel 2007 (prétendu + rapide que les versions ultérieures) en 32bit.

A noter qu'il n'y a pas de formule matricielle dans notre fichier, et peu voire pas de formule volatile.

Je ne sais pas si la solution peut venir des options d'excel, ou autre...

Questions :

1, Comment supprimer ou réduire ce temps de latence?

2, Quelle est la version d'excel la + rapide pour du calcul?

3, Les versions d'excel 64 bits sont prétenduement conçues pour les gros fichiers : avez-vous déjà essayé cela? Pensez-vous que cela peut solutionner notre problème?

Merci de votre aide,

Nicole

PS : désolée, mais je ne peux pas communiquer de version d'exemple du fichier en question.

Bonjour,

tu demandes à ce qu'on joue à Mme Irma quoi...

1) Ton bouton fait quoi ? Application.Calculate ?

2) Un seul classeur d'ouvert ?

3) Pas de dépendances dans un fichier fermé ?

4) Pas (ou peu) de MFC ?

5) pour éliminer windows 10 pourrais-tu tester sur un PC windows 7

Maintenant un truc à tester, mais avec des éventuelles contraintes et inconvénients :

  • dans VBE sélectionner ThisWorkbook
  • dans ses propriétés mettre ForceFullCalculation à True
  • tester
Par contre les calculs seront plus lents. L'arbre des dépendances est détruits et n'est plus utilisé, toute tes formules sont donc recalculées (comme volatiles). A voir ce qui est le plus supportable pour toi, d'un autre coté tu gagneras aussi du temps à l'ouverture puisque l'arbre n'est pas reconstruit.

Bien que ce soit changé dans le classeur c'est l'application qui est concernée, donc tous les classeurs. Fermer excel et le rouvrir une session pour annuler.

En théorie on peut le faire en VBA mais je n'ai pas réussi sur 2010 :

Workbooks("Classeur4.xlsm").ForceFullCalculation = True

eric

eric

Bonsoir Eric,

Merci pour ton aide.

eriiic a écrit :

Bonjour,

tu demandes à ce qu'on joue à Mme Irma quoi...

Je me dis juste que je ne dois pas être la première à être confrontée à cela... quelqu'un a peut-être déjà remarqué ce temps de latence et a peut-être trouvé une solution.

En tous cas, je me dis que quelqu'un connaît une solution, il doit être sur ce forum.

eriiic a écrit :

1) Ton bouton fait quoi ? Application.Calculate ?

Oui, le logiciel de capture vidéo ne permettait pas d'utiliser F9.

eriiic a écrit :

2) Un seul classeur d'ouvert ?

1 ou plusieurs. Le problème se pose dans tous les cas.

eriiic a écrit :

3) Pas de dépendances dans un fichier fermé ?

non

eriiic a écrit :

4) Pas (ou peu) de MFC ?

oui, aucune je crois.

eriiic a écrit :

5) pour éliminer windows 10 pourrais-tu tester sur un PC windows 7

je n'ai pas ça sous la main, mais tu penses que ça peut être lié à win 10?

J'envisage de changer d'excel au cas où, mais je ne pensais pas incriminer l'OS.

eriiic a écrit :

Maintenant un truc à tester, mais avec des éventuelles contraintes et inconvénients :

  • dans VBE sélectionner ThisWorkbook
  • dans ses propriétés mettre ForceFullCalculation à True
  • tester
Par contre les calculs seront plus lents. L'arbre des dépendances est détruits et n'est plus utilisé, toute tes formules sont donc recalculées (comme volatiles). A voir ce qui est le plus supportable pour toi, d'un autre coté tu gagneras aussi du temps à l'ouverture puisque l'arbre n'est pas reconstruit.

Malheureusement, c'est impossible, car il y a beaucoup de calculs, et ce serait vraiment très long.

Je pensais peut-être à une option à la *** dans les options d'excel, ou un mode de calcul, ou un type de fichier (j'ai déjà essayé .xlsb sans succès).

Bonjour,

Changer de version excel n'y changera rien je pense.

Malheureusement, c'est impossible, car il y a beaucoup de calculs, et ce serait vraiment très long.

Fait-le une fois pour confirmer ou non si la piste des dépendances est la bonne.

Et puis tu es en calcul manuel. Il en a peut-être déjà un paquet à recalculer et tu ne verras peut-être pas beaucoup la différence.

eric

edit: pour faire la même chose tu peux tester avec Application.CalculateFullRebuild

eriiic a écrit :

Fait-le une fois pour confirmer ou non si la piste des dépendances est la bonne.

Et puis tu es en calcul manuel. Il en a peut-être déjà un paquet à recalculer et tu ne verras peut-être pas beaucoup la différence.

eric

edit: pour faire la même chose tu peux tester avec Application.CalculateFullRebuild

Re-bonjour Eric,

J'ai déjà testé précédemment "Application.CalculateFullRebuild" : ça met 30-40 secondes à chaque fois.

Donc intégrer cela dans un usage quotidien, ce n'est pas possible.

Je viens de le re-tester juste pour voir si le temps de latence existe juste après avoir lancé "Application.CalculateFullRebuild", et la réponse est oui, même après cela, le temps de latence existe comme dans la vidéo.

Je ne sais pas ce que cela permet de conclure.

Je vais essayer d'uploader une version du fichier test ici.

Ca permet de conclure que ce n'est sans doute pas la reconstruction de l'arbre qui est à l'origine de cette lenteur, ou qu'en tout cas sa reconstruction forcée n'y change rien.

eriiic a écrit :

Ca permet de conclure que ce n'est sans doute pas la reconstruction de l'arbre qui est à l'origine de cette lenteur, ou qu'en tout cas sa reconstruction forcée n'y change rien.

ok, bon déjà c'est une bonne nouvelle.


Comme indiqué dans la vidéo https://youtu.be/s6vTOg8kOSo, le problème, c'est le temps de latence qui survient après recalcul quand on change le contenu d'une ou 2 cellules, notamment dans la colonne B.

La description détaillée de notre problème figure dans le premier post de cette discussion.

On m'a dit que la meilleure version excel pour notre besoin est 2010 64bit. Je n'ai pas encore testé, mais si quelqu'un peut le faire et me dire si le problème de lenteur persiste, ça m'aiderait bien

Merci de votre aide !

Nicole

PS : par ailleurs, on souhaite accélérer le temps de recalcul de ce fichier, mais ça, c'est encore une autre histoire...

Je viens de tester avec ForceFullCalculation et plus de latence du tout.

Et le temps de recalcul est plus rapide qu'avec Application.CalculateFullRebuild

Tu aurais pu tester...

Tu as combien de mémoire ? Vu la taille du fichier il en faut en conséquence. Chez moix le .Calculate prend 3-4 s.

eric

eriiic a écrit :

Je viens de tester avec ForceFullCalculation et plus de latence du tout.

Et le temps de recalcul est plus rapide qu'avec Application.CalculateFullRebuild

Tu aurais pu tester...

Tu as combien de mémoire ? Vu la taille du fichier il en faut en conséquence. Chez moix le .Calculate prend 3-4 s.

eric

Bon alors attends, j'essaye de suivre mais je ne connais pas tout ça...

Alors d'abord, je n'arrive pas à faire ça :

"- dans VBE sélectionner ThisWorkbook

- dans ses propriétés mettre ForceFullCalculation à True"

J'ai mis l'onglet Développeur, mais je ne sais pas ce que je dois faire par la suite.

Ensuite, est-ce que ça va relancer un recalcul total qui prend un temps fou à chaque fois que je lance calculate()?

Parce que si c'est le cas, malheureusement, je ne pourrais pas utiliser cela...

Merci de ton aide précieuse !


Oh, j'ai trouvé!!!!

Je crois que ça règle le problème, mais faut que je teste...

MERCI ERIC !!!!!


Une question : ça sert à quoi ce changement ForceFullCalculation = True ?

Quels sont les impacts sur le fonctionnement du fichier?

Y a-t-il des inconvénients?

Ca déconnecte le système de recalcul "intelligent" d'excel et donc plus d'arbre des dépendances à reconstruire. C'est bien cette action qui te créait la latence.

Les inconvénients je te les ai donnés : calcul plus lent et ça concerne l'application donc tout les classeurs ouverts de cette session. Il faut fermer excel après avoir travaillé avec ce classeur.

Sur ce classeur ça n'impacte pas beaucoup les temps de recalcul et tu peux privilégier le confort de saisie (à voir si ça se confirme sur le vrai classeur), mais sur d'autres il sera plus conséquent.

Tu peux voir que c'est actif au "Calculer" qui reste en permanence dans la barre d'état.

Sur classeur de test le recalcul dure également 30-40 chez toi ?

Chez moi le Calculate dure 3s.... As-tu remplacé des formules par leur résultat sur ce classeur ? Si oui je ne voit pas comment tu voudrais qu'on traque les lenteurs.

Ou bien il faudrait que tu mettes plus de données bidons.

Je n'ai pas regardé toutes tes feuilles mais pour l'instant à part améliorer tes recherchev() en feuille '3' je ne vois pas, mais il n'y en a que 120.

eriiic a écrit :

Ca déconnecte le système de recalcul "intelligent" d'excel et donc plus d'arbre des dépendances à reconstruire. C'est bien cette action qui te créait la latence.

Est-ce que néanmoins les cellules sont bien calculées dans l'ordre de dépendance?

J'imagine que c'est le cas sinon bon nombre de cellules sortiraient des résultats erronés... mais bon, je voudrais être sûre.

eriiic a écrit :

Sur classeur de test le recalcul dure également 30-40 chez toi ?

non, pas là. Il y a plusieurs mois, j'avais testé la fonction workbook.CalculateFullRebuild qui mettait un temps fou. J'ai fait l'amalgame.

eriiic a écrit :

Je n'ai pas regardé toutes tes feuilles mais pour l'instant à part améliorer tes recherchev() en feuille '3' je ne vois pas, mais il n'y en a que 120.

Qu'entends-tu par "améliorer tes recherchev()"? Parce que des recherchev, j'en ai des centaines de milliers dans le fichier, alors si je peux les améliorer, je le fais tout de suite

D'ailleurs, niveau performances, je ne connais pas excel 2010, mais on m'a parlé du Pivot : sais-tu s'il y a des fonctionnalités comme celle là qui accéléreraient le fonctionnement de mon fichier ou est-ce que c'est marginal?

Enfin une question :

avec ForceFullCalculation = True, si j'utilise plusieurs fichiers excel en même temps, peut-il y avoir des interférences entre eux? Si je lance le calcul dans l'un, est-ce que ça lance le calcul dans les autres, par exemple?

Merci Eric, tu m'as bien aidée!

Est-ce que néanmoins les cellules sont bien calculées dans l'ordre de dépendance?

Théoriquement oui.

La différence c'est qu'il n'a plus l'arbre et il est obligé de rechercher les dépendances à chaque fois.

Qu'entends-tu par "améliorer tes recherchev()"? Parce que des recherchev, j'en ai des centaines de milliers dans le fichier, alors si je peux les améliorer, je le fais tout de suite

Attaque toi en priorité aux feuilles qui ont le plus de formules (en particulier les feuilles 64, 78, 73, 77, 71, 81, 83 et 74 dans cet ordre qui sont les plus lentes) et les recherchev() qui travaillent sur une très grande plage.

Mais le gain ne sera conséquent que sur un recalcul complet du classeur. Excel n'est pas bête, il sait ce qui est nécessaire d'être ré-évalué et s'en tient là.

Déjà s'adresser à la plage strictement nécessaire, tu en as quelques unes sur des colonnes entières.

Si la taille de cette plage peut évoluer utiliser un nom dynamique pour cette plage : http://www.mdf-xlpages.com/modules/smartsection/item.php?itemid=64

Dans un 2nd temps, si tu en as sur des plages avec de très nombreuses lignes remplacer les recherchev(;;;FAUX) par un double recherchev(;;;VRAI) considérablement plus rapide.

Je t'expliquerai plus tard, je dois m'absenter. De toute façon tu as déjà du boulot

Edit : tu n'as pas de cas où ça pourrait s'appliquer pour que ça vaille le coup.

eric

Merci beaucoup Eric!

Tu m'as permis de résoudre un problème qui m'embête depuis des mois.

Rechercher des sujets similaires à "probleme temps latence saisie"