Calcule de formule somme lent

[tiow]

Calcul de formule - somme lent

Bonjour à tous,

Je me tourne vers vous pour une question d'optimisation de formules

J'utilise la formule ci dessous:

=SOMME((Critere1 = $A6)*(Critere2=$B6)*(Critere3=$C6)*(Semaine1))

un peu trop de fois dans ma feuille excel ...

J'ai lu quelques post qui m'ont permis de nommer mes plages avec la fonction décaler .. Cependant mes calculs restent tres long dans mon fichier.

Je suis donc à la recherche d'optimisation, et j'aimerai savoir si il n'y a pas une astuce pour condenser

((Critere1 = $A6)*(Critere2=$B6)*(Critere3=$C6)

qui est utilisé 9 fois par lignes (sur 9 colonnes)

Merci à tous de votre lecture

@+
Thibaut


Ps: le fichier joint n'est pas tres lent, car si je met trop de formule, je ne peut pas l'uploader

 

[GIBI]

Re : Calcul de formule - somme lent

Bonjour,


Remplace SOMME par SOMMEPROD (à valider par enter)

sinon testes =SOMME(SI(Critere1 = $A6;SI(Critere2=$B6;SI(Critere3=$C6;Semaine1;H20)))) à valider par CTRL+enter

**********Tu peux aussi créer une colonne avec les critères concaténés (=A6 & b6 & c6) et simplifier les formules



ET surtout OPTION/calcul à la demande


GIBI

 

[tiow]

Re : Calcule de formule somme lent

Salut GIBI, et merci de me répondre,

Alors remplacer somme par sommeprod, j'ai déjà essayer et ça change rien niveau vitesse de calcul ...

J'ai tenter la formule que tu me proposes et bizarrement c'est légérement plus lent ..

Sinon j'ai bien mis calcul à la demande, mais j'aimerai pouvoir le laisser en auto, c'est pour ça que je cherche à optimiser

Sinon j'ai fait Insertion > nom > definir et j'ai créé un nom "critere" et dans mes formules je peux maintenant ecrire:

=SOMME(critere*(Semaine1))

(ou sommeprod)
mais malheuresement aucune amélioration niveau vitesse de calcul ..
J'imagine qu'avec le nombre de calcul demandés c'est peut être normal ..

Est ce que cela accélererai les calculs en passant pas une macro ?? (où tt autre manipulation mystique, je ne suis pas contre)

Thibaut

 

[jeanpierre]

Re : Calcule de formule somme lent

Bonjour Thibaut, GIBI,

Ce que je pense, c'est que, plus il y a de calcul, plus c'est long, à la vitesse du processeur près.

Certes quelques Matricielles sont un peu plus gourmandes en temps, mais globalement, le problème reste entier.

Je ne pense pas qu'une macro va accélérer la chose, ou alors, si peu. Il suffit de penser au temps que cela prendrait en manuel .

Plus on demande, plus c'est long et, là, il n'y a pas trop de miracle.

Résidus d'expérience.

Bon après-midi.

Jean-Pierre

 

[tiow]

Re : Calcule de formule somme lent

Merci pour tes précisions Jean-Pierre,

Si jamais il n'y a pas de solution optimiser mes formules, je tenterai peut être l'expérience de la macro ... Car le fait d'avoir toutes ces formules me ralentie mon fichier excel en entier.

Grâce aux macros il y a possibilité d'entrer dans chaque cellules uniquement le résultat des formules, nest ce pas ? Dans ce cas je pourrais peut être repasser mon fichier en mode de calcul automatique ...

Bon après midi à tous

Thibaut.

 

[cbea]

Re : Calcule de formule somme lent

Bonjour tiow, GIBI, jeanpierre,

Pourrais-tu fournir un exemple dans un fichier ?
Merci

 

[tiow]

Re : Calcule de formule somme lent

Bonjour cbea,

Quel type d'exemple voudrais tu ? Dans mon premier post j'ai joint un fichier représentant la formule que je voudrais optimiser.

Si tu parles d'un exemple en VBA, je ne me suis pas encore penché sur le cas.

@+
Thibaut.

 

[cbea]

Re : Calcule de formule somme lent

re,

Effectivement, mille excuses.
Je vais regarder si je peux réaliser une procédure en VBA.

Merci

 

[cbea]

Re : Calcule de formule somme lent

Je viens de faire un essai par macro et cela ne va pas plus vite.

 

[tiow]

Re : Calcule de formule somme lent

re,

Arf, merci pour tes essais cbea.
Je vais donc conserver mon fichier en mode de calcul manuel

Bon week end!

Thibaut

 

[Gael]

Re : Calcule de formule somme lent

Bonsoir à tous,

Un essai avec une formule différente qui me semble un peu plus rapide mais c'est à vérifier sur une plus grande plage.

le résultat n'est juste que si l'ensemble des 3 critères est unique dans la feuille détail.

@+

Gael

 

[tiow]

Re : Calcule de formule somme lent

Salut Gael,

Merci de ta proposition, je regarderai ça demain juste pour voir si c'est plus rapide; car malheuresement les 3 conditions peuvent se retrouver plusieurs fois dans l'onglet détail.

@+

Thibaut

 

[tiow]

Re : Calcul de formule somme lent

Bonjour à tous,

J'ai enfin pu tester ta formule Gael,

Ce qui est interessant c'est le fait qu'on ne fait plus des multiplications de matrices énormes, mais juste une somme sur la ligne concernée (ce que j'ai cherché à faire en vain)

Cependant le fait d'utiliser "equiv" empêche d'avoir des doublons .. De plus j'ai testé sur une plage plus grande et lorsque l'on agrandi la taille du tableau de recherche dans "equiv" cela commence à ralentir ..

Voilà, tout ça pour dire que sur ce forum on en apprend toujours plus

Si quelqu'un sait comment faire des sommes juste d'une petite plage de cellule en fonction de criteres dans de grand tableau, il peut venir s'exprimer ici

Merci à tous et bonne journée,

@+ Thibaut

 

[Gael]

Re : Calcule de formule somme lent

Bonjour Thibaut, bonjour à tous,

Je te propose ci-joint une autre solution que j'ai déjà souvent utilisée. Lorsque les calculs sont complexes et très lents, le principe est de ne pas refaire tous les calculs alors que l'utilisateur n'a modifié qu'une cellule et que l'on peut faire un recalcul partiel.

J'ai ajouté une macro évènementielle qui se déclenche si l'on change la valeur d'une cellule de la feuille détail.

la macro va filtrer la feuille Bilan pour récupérer le(s) enregistrements correspondants puis faire un recalcul des seules lignes filtrées et non pas de toute la base de données ce qui est beucoup plus rapide.

Cela peut parfaitement fonctionner avec ton fichier initial.

Par contre je n'ai pas compris dans quel cas ni sur quelle feuille tu peux avoir des doublons.

Si c'est une saisie de données sur la feuille Détail, j'aurai rajouté un userform sur lequel tu saisis les 3 critères et la semaine choisie, la macro affiche les données correspondantes, l'utilisateur les modifie et la mise à jour et le recalcul partiel se font à la suite.

qu'en penses-tu?

@+

Gael

 

[mutzik]

Re : Calcule de formule somme lent

Bonjour,

On peut également recourir à une fonction personnalisée, mais j'avoue que je ne comprends pas bien quel est le résultat du calcul que tu cherches
- tu as trois critères dans les colonnes A, B et C que tu multiplies par semaine1 (c'est quoi, ce semaine1)

sinon on peut également par vba refaire les calculs par appui sur bouton (à la demande) ET à l'ouverture de la feuille ET quand une valeur change dans une certaine zone de cellules ...