Les limites de Sommeprod

[basil01]

Bonjour, j'ai découvert ce forum qui a l'air de contenir des membres assez pointu sur sommeprod...

Après des recherches sur internet je n'ai pas pu trouvé de réelles réponses à mon problème, qui doit pourtant toucher beaucoup de personnes.

En effet j'applique sur une base de donnée assez grosse (environ 10 000lignes sur 20colonnes) une multitudes de fonctions SOMMEPROD du type:

=SOMMEPROD((Feuille1$P$2:$P$50000="CritèreA")*
(MOIS(Feuille1!$Y$2:$Y$50000)=$AB6)*
(ANNEE(Feuille1!$Y$2:$Y$50000)=$AC$4)*
(Feuille1!$Z$2:$Z$50000))

J'en ai environ une centaine de ce type sur ma feuille2.

Voici mon problème: comme j'utilise ces sommeprod sur une base de donnée non terminé, j'utilise énormément les filtres automatiques pour la compléter.

Seulement dès que je me sers du filtre, Excel recalculent toutes les Sommeprod et le temps de calcul est alors très long: plusieurs minutes....


La seule solution que j'ai trouvé pour l'instant est de mettre excel en calcul manuel....

Est ce que c'est un problème récurrent ou non que je rencontre. Se mettre en calcul manuel est-il la seule solution?


Merci de votre aide

 

[CB60]

Re : Les limites de Sommeprod

Bonsoir Basil
Monique m'avait concocté il y a quelques temps un programme avec des sommeprods assez complexe et le recalcul à chaque manipulation devenais lassent et j'ai fais comme toi j'ai arreter le recalcul automatique avec une macro pour mettre en manuel ou en auto , des que je la lance sur la cellule "A1" est indiqué calcul manuel ou calcul auto (pense bête pour ne pas l'oublier).
bonne soirée
Bruno

 

[basil01]

Re : Les limites de Sommeprod

Merci, dommage que cette formule prenne autant de ressources

Cependant j'ai essayé de transposer mon classeur sur Calc de Open Office, et je dois avouer que le calcul est déjà beaucoup plus rapide.

Enfin si d'autres personnes ont d'autres astuces je suis toujours preneur.



Merci.

 

[Tibo]

Re : Les limites de Sommeprod

Salut Basil,

Il est vrai que faire un calcul avec SOMMEPROD sur des plages de 50.000 lignes prend pas mal de ressources calcul.

Essaye en nommant les différentes plages et en faisant appel aux noms dans les formules.

Ca pourrait te faire gagner "un peu" de temps

@+

 

[basil01]

Re : Les limites de Sommeprod

Salut Basil,

Essaye en nommant les différentes plages et en faisant appel aux noms dans les formules.

@+

Que signifies-tu par nommer les différentes plages et faire appel au noms dans les formules? Cela signifie-t-il éviter de faire appel à des cellules?


Merci

 

[Tibo]

Re : Les limites de Sommeprod

re,

Tu sélectionnes la colonne P de la ligne 2 à la ligne 50000

Insertion - Nom - Définir et tu indiques un nom explicite.

Tu recommences pour les colonnes Y et Z (toujours de la ligne 2 à 50000) en indiquant un nom différent.

Tu intègres ensuite les noms dans ta formule :

=SOMMEPROD((nom01="CritèreA")*(MOIS(nom02)=$AB6)*(ANNEE(nom03)=$AC$4)*(nom03))

remplace nom01, nom02 et nom03 par les noms que tu auras créé.

Cela dit, je ne garantis pas une accélération fulgurante.

@+

 

[basil01]

Re : Les limites de Sommeprod

Merci pour cette astuce, ça a déjà le mérite de rendre les formules beaucoup plus claires.


Sinon je nette une vrai amélioration.

Maintenant lorsque la formule est appliquée et que j'utilise le filtre auto, la première fois que je fais une opétarion le temps de calcul est extrêmement long (plusieurs minutes). Cependant, lorsque j'applique des filtres ensuite le temps devient très raisonnable (environ 1sec).

Cependant je me demande toujours quel est la véritable cause de ces recalculs, puisque excel ne fait que cacher les cellules en appliquant un filtre....

Qu'en pensez-vous?

 

[Monique]

Re : Les limites de Sommeprod

Bonjour,

On peut nommer les plages par formule, (par Insertion – Nom – Définir)
pour ne prendre en compte que le nb de lignes nécessaire
(tu parles de 10000 lignes, mais la formule que tu utilises va jusqu'à la ligne 50000)
Crit =DECALER(Feuil1!$P$2;;;NB(Feuil1!$Y:$Y))
Dates =DECALER(Feuil1!$Y$2;;;NB(Feuil1!$Y:$Y))
Valeur =DECALER(Feuil1!$Z$2;;;NB(Feuil1!$Y:$Y))

Une fois nommées les plages, on peut aussi donner un nom à la formule
Insertion - Nom - Définir
Et ça accélère.
Pourquoi ? Je n'en sais rien.

Si ça reste lent, suis ce lien.
Chti160 m’avait fait une macro qui copiait une ligne de formules,
copiait le résultat puis collage spécial valeurs.
http://www.excel-downloads.com/forum/48804-macro-copie-colle-supprime-lignes.html

 

[basil01]

Re : Les limites de Sommeprod

MERCI MONIQUE

L'astuce que tu m'as donné rend enfin mon fichier très utilisable.
Et merci pour le petit fichier qui m'a été de grande aide pour comprendre toutes les étapes à réaliser.


Un Grand grand merci

 

[Sylvain]

Re : Les limites de Sommeprod

Bonjour,

Je voudrais apporter un autre point de vue, il me semble en effet que la formule ne fait que du comptage ou une somme simple. Pour cela, il me semble que les fonctions bases de données du type bdsomme ou bdnb sont beaucoup plus efficaces (en particulier en terme de rapidité). Et elles ne nécessitent pas de nommer de plage.

A+

 

[basil01]

Re : Les limites de Sommeprod

Effectivement, BDSOMME marche bien dans mon cas, et est beaucoup plus efficace.

Le désavantage de cette formule reste la conception des formules qui est très longues, puisqu'elle demande un tableau de critère de même type que le filtre élaboré.

Malgré ce désavantage, je vais finalement utiliser la BDSOMME, car le calcul est vraiment instantané, même si avec l'astuce de Monique le temps était déjà très réduit.


Merci pour tous

Basil



Edit: Après avoir mis en place totalement les formules, les temps de calculs sont en fait équivalents. Donc finalement je vais choisir la solution de Monique.

Merci à tous.