Problème de calcul

[Julien]

Bonjour,

Est ce que vous pourriez me donner tous les moyens possibles pour que les calculs soient moins long quand il y a pas mal de lignes et pas mal de formules. J'en connais déjà certains mais voudrais tous les connaître pour choisir les plus rapides et plus faciles à mettre en place pour un stagiaire comme moi (sans avoir à tout changer) .

Merci d'avance

 

[ZON]

Salut Julien et le forum,


Cette question revient souvent sur le forum, je me rends souvent compte que ces problemes de longueurs de calcul viennent souvent de la construction du classeur et de ses feuilles de calcul, en postant un petit fichier exemple nous serons plus à mêmes de t'aider...


A+++

 

[Julien]

Je vous joint un tout petit bout de mon fichier. Il est le même avec plus de lignes. Donc avec le bout de fichier que je vous donne, le temps de calcul est court mais avec mon vrai fichier, il est très long.
Au mois de Juillet, j'ai expliqué le mode de calcul de chaque colonne.
Si vous avez des questions, n'hésitez pas.
Je vous rappelle mon problème : réduire au maximum le temps de calcul tout en gardant les formules.
Merci d'avance à ceux qui vont plancher dessus.

 

[Sylvain]

bonjour,

il me semble que la complexité des formules vient de la présentation de la page conso.
Si elle se présentait comme les autres feuilles avec une colonne référence et une autre désignation tes formules seraient beaucoup plus simples et le temps de calcul réduit d'autant.
C'est donc la procédure de mise en forme qui doit être travaillée.

A+

 

[Jean-Marie]

Bonjour, tout le monde

Julien,

Regarde dans la formule de la colonne C de la feuille Juillet, tu trouves ceci
=RECHERCHEV(A9;INDIRECT("'Conso'!$A$3:$O"&NBVAL(INDIRECT("'Conso'!A:A";VRAI))+1;VRAI);2;FAUX)

Tu demandes à Excel de calculer X fois le nombre de valeurs dans la colonne Conso!A, une seule fois suffit, tu la mettrais où, cette valeur dans une cellule de la feuille Conso ou dans la feuille juillet ?. La réponse est à la fin du post.

En plus dans cette formule pourquoi passer par un indirect, pour diminuer la plage, mais je ne crois pas que la longueur de la plage dans un rechercheV augmente le temps de calculs.

Maintenant dans la colonne E
=(D9+INDEX(INDIRECT("Conso!H:H");EQUIV(A9;INDIRECT("Conso!A:A");0);1))/2

Pourquoi le INDIRECT("Conso!H:H") tout simplement Conso!H:H" et encore
INDIRECT("Conso!A:A") même chose Conso!A:A

Julien, dans ce fil Lien supprimé, je te donnais une formule qui pouvait te rendre d'énorme service. Je vois que tu ne l'as pas appliquée.

Réponse, dans la feuille Conso, la quantité de valeur dans la colonne A, sera donc calculée 1 fois et elle sera reprise pour l'ensemble des feuilles. Bravo si tu as la bonne réponse, et si tu ne l'as pas, tu connais maintenant une autre façon de te simplifier la vie et donc les calculs.

Bonne journée, et dit moi si les calculs sont plus rapides

@+Jean-Marie

 

[Jean-Marie]

Bonsoir

Julien si tu veux il est possible encore d'améliorer l'éxécution des formules.

Il suffit de nommer une plage ayant comme définition
=INDIRECT("'Conso'!$A$3:$O"&NBVAL('Conso'!A:A)+1;VRAI)
et tu n'auras même plus besoin de mettre dans la cellule conso le nombre d'éléments.

Bonne soirée, en attente des nouvelles sur les modifications que tu as apporté.

@+Jean-Marie

 

[Julien]

Je te remercie pour ton aide.
Je n'applique pas toujours les formules que vous me donnez et cela pour plusieurs raisons. Tout d'abord parce que je ne maîtrise pas toutes les formules que vous me donnez et donc j'hésite parfois à m'en servir par peur de me retrouver bloqué par la suite ou de ne pas pouvoir les appliquer.
Ensuite, d'autres me donnent aussi des formules que j'appliquent parfois. J'ai alors peur de les changer de peur de voir bugger mon outil qui marche relativement bien malgré le temps de calcul long.
Tes formules ont cependant l'air très intéressantes et surement beaucoup plus rapides.(J'utilise par exemple la formule INDIRECT malgré tes conseils pour ne pas avoir le message #REF quand je réimporte le fichier conso par dessus) . Seulement, j'ai du mal à les cerner. Te serais t il possible de les mettre directement sur la feuille exemple que je te renvois sachant que j'ai les 12 mois de l'année comme juillet.
Merci.

 

[Jean-Marie]

Bonjour Julien

Tu as le droit de ne pas appliquer les formules que les intervenants te proposent, mais ne repose pas la même question plus tard sans avoir essayer les formules

Si tu ne comprends pas une fonction, tu as déjà l'aide d'Excel pour t'aider et puis le forum pourra toujours te répondre sur telle ou telle formule.

Concernant la modification de ton fichier, on va le faire par post, il est préférable d'apprendre à pêcher, plutôt que de donner du poisson

Active la feuille Conso, clique le menu Insertion/nom/définir, dans la boîte de dialogue, dans le champ du haut tape le ZoneConso, dans le champ du bas tape la formule :
=DECALER(indirect("Conso!$A$1:$O$1");;;LIGNES(Conso!$A$1:$A$3)-NBVAL(Conso!$A$1:$A$3)+NBVAL(Conso!$A:$A))
Cette formule détermine une plage de cellule en fonction de la plage indiquée dans le 1er argument, le 2ème argument (laissé vide) indique un décalage en ligne de la plage, le 3ème argument (laissé vide) indique un décalage en colonne de la plage, et le 4ème argument indique le nombre de lignes que doit avoir la plage.
Pour le calcul du nombre de valeurs dans la colonne A:A, tu l'obtiens à l'aide de la fonction NBVAL, il y a une différence par rapport à la fonction NB.
La partie LIGNES(Conso!$A$1:$A$3)-NBVAL(Conso!$A$1:$A$3), calcul le nombre de lignes vide pour ton entête, si un jour prochain tu veux rajouter une ligne avant la cellule A3, cette partie prendra en compte l'insertion.

Maintenant pour tes formules sur les feuilles de mois, place en C5 la valeur du mois correspondant à la feuille, pour juillet -> 8, etc. En cellule E9, met cette formule =(D9+RECHERCHEV(A9;Zoneconso;C$5;FAUX))/2. La fonction recherchev retourne la même valeur que la fonction =index(;equiv(;;, la valeur 8 correspond à la colonne H. Dans la cellule F9 remplace ta formule par =RECHERCHEV(A9;Zoneconso;C$5+1;FAUX).

Tu peux aussi remplacer la formule en H9 par
=SI(OU(G9>20%;G9<-20%);PLANCHER(((D9+F9)/2*1,25);5);"")

Si tu as déjà toutes les feuilles par mois dans ton classeur, met dans la cellule C5 la valeur correspondante, puis sélectionne à l'aide de shift les feuilles "mois", puis change les formules E9, et F9 et H9, tu verras qu'elles seront intégrées dans toutes les feuilles, mais tu devras faire un glissé manuel des formules pour toutes les feuilles.

Si tu as des questions ...

Bonne journée.
@+Jean-Marie