remplacer SOMMEPROD ?

[thomlau]

Bonjour à toutes et à tous.
Tout d'abord, je vous présente mes meilleurs voeux pour l'année 2009.

J'ai une question à vous soumettre.
Voilà, j'ai un classeur assez conséquent, environ 5Mo, de plus, il est bourré de mormule avec la fonction SOMMEPROD.

Voici le genre de formules :

=SOMMEPROD((données!$C$2:$C$6000=$C19)*(données!$G$2:$G$6000=G$13);(données!$D$2:$D$6000))

Serait-il possible de remplacer ces formules par d'autres moins gourmandes en ressources?
D'avance merci et bonne journée.

 

[JeanMarie]

Re : remplacer SOMMEPROD ?

Bonjour Thomlau

Difficile de te répondre, cela dépend de la construction de ton tableau données, et de ce que tu veux obtenir, un TCD permet d'éviter de passer par des SOMMEPROD.

Si une de tes plages données!$C$2:$C$6000, données!$G$2:$G$6000 est triée, oui c'est possible en définissant des plages dynamiques.

Mais sans fichier c'est difficile de te répondre.

@+Jean-Marie

 

[thomlau]

Re : remplacer SOMMEPROD ?

Bonjour JeanMarie.
Merci pour ta réponse.

Je t'ai fais un tableau simplifié.
Les formules avec SOMMEPROD sont sur la feuille "accueil";

Encore merci pour ton aide.

++

 

[JCGL]

Re : remplacer SOMMEPROD ?

Bonjour à tous,

Un essai par TCD

A+

 

[thomlau]

Re : remplacer SOMMEPROD ?

Merci JCGL.
Je vais essayer de comprendre le fonctionnement du TCD.

Merci et à bientôt.

 

[JeanMarie]

Re : remplacer SOMMEPROD ?

Bonsoir ThomLau, Jean-Claude

Ci-joint la proposition au niveau formule, pour diminuer les temps de calcul.

Dans ton cas, la diminution des plages est possible, l'un des deux critères (Colonne G) est trié. Si ce n'était le cas, la seule possibilité hormis VBA est de passer par un TCD, (et même cela serait la solution à retenir).

J'ai modifié un petit peu ton tableau, en rajoutant deux lignes (13 et 14), qui calcul la position de la première occurrence du critère, et le nombre de fois que le critère est présent dans ta base. Je le répète encore, si la base n'est pas triée sur la colonne G, le tableau retournera des valeurs erronées.

A partir de ces deux calculs, on utilise la fonction DECALER ou la fonction INDIRECT
Tu trouveras les deux exemples dans la même feuille.

DECALER permet de définir une plage de cellules, en partant d'une référence de base, d'un décalage (ici vertical) et d'une longueur.

INDIRECT, transforme une référence de cellules écrite sous forme de texte, en une référence utilisable aux autres fonctions d'Excel.
En ligne (15) tu trouveras la formule pour écrire sous forme de texte la plage.

Ensuite dans ton tableau, j'ai supprimé la fonction SOMMEPROD par la fonction SOMME.SI(). Il n'y a plus qu'un critère, la plage étant limitée.
Dans cette formule, tu verras que j'ai utilisé des plages nommées. C'est au niveau de la définition des noms que la fonction DECALER intervient. (Menu Insertion/Nom/Définir...)

En dessous de ton tableau d'origine, j'ai rajouté les formules utilisant la plage écrite en texte, en Insérant INDIRECT dans la fonction SOMME.SI

Désolé je ne sais pas te dire entre DECALER et INDIRECT, quelle version utilisée.

@+Jean-Marie

 

[bcharef]

Re : remplacer SOMMEPROD ?

Bonsoir thomlau,JeanMarie,
Salut JCGL,
Bonsoir à toutes et à tous.

Après consultation du fichier transmis par notre ami JCGL et afin de, nous permettre l'achèvement du travail à réaliser par un TCD en matière de Prod.

J'ai le plaisir de vous joindre le fichier ci-joint.

[FONT=&quot]Et, à l'occasion, j'en profite pour vous présenter mes meilleurs voeux pour l'année 2009.

A+

Cordialement.

Bcharef.
[/FONT]

 

[JCGL]

Re : remplacer SOMMEPROD ?

Bonjour à tous,
Salut Jean-Marie,
Salut BCharef,

Bravo JM pour ta persévérance en formule alors que tu reconnais qu'un TCD est "suffisant". Je reconnais bien là ton esprit didactique... Ton amitié 'Team mDF' peut-être...

Bravo BCharef pour ton TCD avec plage calculée incluse (Arf et Niark, la Marie à un concurrent... C'est juste pour rire Marie... et je te réitère mes meilleurs voeux pour 2009)

A++ et Meilleurs Voeux pour 2009
A+ à tous

 

[Bruce68]

Re : remplacer SOMMEPROD ?

Bonsoir à tous et meilleurs voeux

Un autre exemple avec BDSOMME pas gourmande en ressources

 

[JeanMarie]

Re : remplacer SOMMEPROD ?

Bonjour

Merci Jean-Claude, sur le côté didactique.

Pour aider TomLau sur la création de TCD, voici deux liens sur deux fichiers, le premier, le second, un autre lien sur une discussion https://www.excel-downloads.com/threads/aide-creation-tcd.74155/

Le résultat de la représentation des données est issue de la partie "disposition" dans l'assistant. J'aurai préféré que mes acolytes aient placé "Abrév" dans la partie Colonnes, pour avoir un semblant dans la représentation du tableaux.
Je n'ai pas assez d'expérience dans les TCD, pour te donner l'équivalent, je ne sais pas si c'est réalisable. Pour bien connaître les TCD il faut les utiliser et ce n'est pas mon cas. Si Celeda passait ici ... (c'est une experte).

La solution de Bruce68 utilisant BDSOMME est malheureusement une galère dans la gestion de la plage "critères", la composition de la "zone de critère" devant être obligatoirement des cellules contiguës.

@+Jean-Marie

 

[thomlau]

Re : remplacer SOMMEPROD ?

Bonjour à toutes et à tous.

Merci pour toutes vos contributions. Elles ont chacune un intérêt indéniable, et en tout état de cause, mon processeur rame beaucoup moins, voir pas du tout grâce à vos propositions.
Je vais voir laquelle d'entre elles est la mieux adaptée à mon cas.

Encore merci et meilleurs voeux pour 2009.

Bonne fin de semaine à vous.
++

 

[JCGL]

Re : remplacer SOMMEPROD ?

Bonjour à tous,

Le TCD avec les Abréviations en colonnes

A+

 

[bcharef]

Re : remplacer SOMMEPROD ?

Bonjour à toutes et à tous.

Dans l'espérance que la solution vous conviendra .

A vous lire

Cordialement

 

[JeanMarie]

Re : remplacer SOMMEPROD ?

Re...

bcharef, superbe, tu ne vas pas tant tirer comme ça.

Comment as-tu fais ?

@+Jean-Marie

 

[thomlau]

Re : remplacer SOMMEPROD ?

Bonjour bcharef, re le forum...

Ton tableau est tout simplement génial. J'ai complété les données et le résultat est instantanné.

Je vais apprendre a concevoir des TCD, ça m'intéresse énormément;

Encore merci et à bientôt.