XL 2019 Alternative à sommeprod

[Goo69]

Bonjour,

Dans le fichier en pj, j'utilise la fonction sommeprod dans l'onglet "Collectes isolées" pour consolider des données provenant de 3 onglets.
Le fichier final est bien plus gros et cette fonction est très gourmande en ressources.
Auriez-vous une alternative moins gourmande à proposer ?

Merci

 

[mapomme]

Bonjour @Goo69🙂,

Dans le classeur que vous avez joint, les N° GPC semblent uniques dans la colonne B de la feuille "Collecte 22-23".
Je suis parti sur cette hypothèse. Si cette hypothèse est fausse alors tout ce qui suit est bien sûr également faux.

On a défini le nom plageDate avec pour définition : =DECALER('Collecte 22-23'!$A$3;0;0;1;EQUIV(9E+99;'Collecte 22-23'!$3:$3))
On a défini le nom plageGPC avec pour définition : =DECALER('Collecte 22-23'!$B$1;0;0;EQUIV(9E+99;'Collecte 22-23'!$B:$B);1)
(voir menu : Formules / Gestionnaire de noms)

En C1 de la feuille "Collectes isolées", on a mis la formule suivante à recopier vers la droite et vers le bas :

=SIERREUR(DECALER('Collecte 22-23'!$A$1;EQUIV($B6;plageGPC;0)-1;EQUIV(C$1;plageDate;0)-1);"")

À vous de voir si cela améliore les choses.

nota : la formule s'adapte au nombre de lignes et de colonnes au sein de la feuille "Collecte 22-23" par l'intermédiaire des définitions des deux plages.

 

[goube]

Bonsoir,
Un essai avec INDEX EQUIV.
Cordialement

 

[Goo69]

Bonjour @Goo69🙂,

Dans le classeur que vous avez joint, les N° GPC semblent uniques dans la colonne B de la feuille "Collecte 22-23".
Je suis parti sur cette hypothèse. Si cette hypothèse est fausse alors tout ce qui suit est bien sûr également faux.

On a défini le nom plageDate avec pour définition : =DECALER('Collecte 22-23'!$A$3;0;0;1;EQUIV(9E+99;'Collecte 22-23'!$3:$3))
On a défini le nom plageGPC avec pour définition : =DECALER('Collecte 22-23'!$B$1;0;0;EQUIV(9E+99;'Collecte 22-23'!$B:$B);1)
(voir menu : Formules / Gestionnaire de noms)

En C1 de la feuille "Collectes isolées", on a mis la formule suivante à recopier vers la droite et vers le bas :

=SIERREUR(DECALER('Collecte 22-23'!$A$1;EQUIV($B6;plageGPC;0)-1;EQUIV(C$1;plageDate;0)-1);"")

À vous de voir si cela améliore les choses.

nota : la formule s'adapte au nombre de lignes et de colonnes au sein de la feuille "Collecte 22-23" par l'intermédiaire des définitions des deux plages.

Merci @mapomme , mais effectivement, la colonne N° GPC peut contenir des doublons.

 

[job75]

Bonjour Goo69, mapomme, goube,

Formule en I6 à tirer à droite et vers le bas :

=SOMME.SI(INDIRECT("'Collecte "&I$2&"'!$b:$b");$B6;DECALER(INDIRECT("'Collecte "&I$2&"'!$A:$A");;EQUIV(I$1;INDIRECT("'Collecte "&I$2&"'!$3:$3");0)-1))

Elle posera beaucoup moins de problèmes que votre formule SOMMEPROD.

A+

 

[Goo69]

Bonsoir,
Un essai avec INDEX EQUIV.
Cordialement

Merci @goube mais j'ai omis de préciser que les données de la colonne GPC peuvent être en doublons.

 

[goube]

re,
Est il possible d'avoir un fichier exemple avec des doublons et plusieurs onglets ?

 

[Goo69]

Bonjour Goo69, mapomme, goube,

Formule en I6 à tirer à droite et vers le bas :

=SOMME.SI(INDIRECT("'Collecte "&I$2&"'!$b:$b");$B6;DECALER(INDIRECT("'Collecte "&I$2&"'!$A:$A");;EQUIV(I$1;INDIRECT("'Collecte "&I$2&"'!$3:$3");0)-1))

Elle posera beaucoup moins de problèmes que votre formule SOMMEPROD.

A+

La formule fonctionne parfaitement. C'est tellement plus rapide maintenant !!
Merci @job75

 

[Goo69]

re,
Est il possible d'avoir un fichier exemple avec des doublons et plusieurs onglets ?

@goube, la solution de @job75 fonctionne parfaitement.
Merci et désolé pour le manque de précision.

 

[CHRIS1945]

Bonjour Goo69,
Il est vrai que la fonction SOMMEPROD est très gourmande, voici une solution moins gourmande POUR AUTANT QU'IL N'Y AIT PAS DE DOUBLON dans les onglets (ce qui est le cas dans ton exemple)
S'il devait y avoir des doublons, je préconise de créer dans les onglets à compiler, un tableau "technique" qui procède à la suppression des doublons grâce à la fonction UNIQUE et ensuite faire une SOMME.SI pour sommer les doublons et enfin, on utilise la première solution sur le nouveau tableau créé.
A noter que ce nouveau tableau peut être masquer car purement technique.
Cela peut sembler lourd mais c'est finalement plus léger que les SOMMEPROD.
Si tu es dans ce cas de figure et que tu ne vois pas comment faire, reviens moi et je le ferai.

Bonne continuation
Chris

 

[Goo69]

La formule fonctionne parfaitement. C'est tellement plus rapide maintenant !!
Merci @job75

@job75
Est-ce que tu pourrais m'aider à lire cette partie de ta formule stp ?

DECALER(INDIRECT("'Collecte "&I$2&"'!$A:$A");;EQUIV(I$1;INDIRECT("'Collecte "&I$2&"'!$3:$3");0)-1)

 

[job75]

Bonjour Goo69,

INDIRECT("'Collecte "&I$2&"'!$A:$A") c'est la colonne A de la feuille "Collecte 22-23"

DECALER + EQUIV décalent cette colonne vers la droite pour obtenir la colonne du mois indiqué en I$1.

A+