XL 2016 Faire une SOMME.Si.ENS() avec une liste critères 1

[Danybrett]

Bonjour,

J'ai pas mal réfléchit et cherché sur le net et je ne trouve pas de solution à mon problème particulier.

J'ai un fichier Excel (Excel exemple en PJ) avec 2 feuilles:
- la BDD
- les calculs

la BDD est composée de 3 colonnes
- l'année (2017,2018...)
- le mois (1,2,3,4,5...)
- un code (A,B,C...)
- une quantité (80,1,24,10...)

Dans le calcul, j'ai regroupé les codes en 2 familles (Famille 1 et Famille 2) et je cherche à faire la somme des quantités de tous les codes appartenant à la même famille par mois et par année.

Dans mon exemple la seule formule que j'ai trouvé est:
=SOMME.SI.ENS(BDD!$D:$D;BDD!$C:$C;$A$4;BDD!$B:$B;$C4;BDD!$A:$A;D$3)+
SOMME.SI.ENS(BDD!$D:$D;BDD!$C:$C;$A$5;BDD!$B:$B;$C4;BDD!$A:$A;D$3)+
SOMME.SI.ENS(BDD!$D:$D;BDD!$C:$C;$B$4;BDD!$B:$B;$C4;BDD!$A:$A;D$3)+
SOMME.SI.ENS(BDD!$D:$D;BDD!$C:$C;$B$3;BDD!$B:$B;$C4;BDD!$A:$A;D$3)+
SOMME.SI.ENS(BDD!$D:$D;BDD!$C:$C;$A$3;BDD!$B:$B;$C4;BDD!$A:$A;D$3)

Mais, le problème est que cette façon de procéder est beaucoup trop lourde à appliquer (Dans la réalité j'ai une trentaine de codes par famille et une vingtaine de familles) et beaucoup trop lourd pour l'ordi pour actualiser les calculs.

Avez-vous une solution alternative à me proposer ?

En espérant avoir bien expliqué mon problème et qu'une solution alternative existe 🙂

Cordialement,
Danybrett

 

[chris]

Bonjour

Un simple TCD à partir des données mises sous forme de tableau : clic droit dans le TCD pour actualiser

On peut séparer en 2 TCD si tu préfères

 

[Danybrett]

Bonjour,

merci pour ta réponse rapide !
je comprends ce que tu as fait, pourrais tu m'expliquer ta démarche pour que je puisse l'appliquer à mon tour ? STP

 

[chris]

RE

C'est un simple TCD :
une fois la source mise sous forme de tableau, nommé BDD au lieu du nom automatique Tableau1, depuis une cellule de ce tableau, Insertion, Tableau croisé dynamique
Placer (en utilisant le volet de droite et des glisser/déplacer des titres de colonnes)

• Code et Date en étiquette de lignes
• Qté en valeurs

Faire un clic droit sur une date, regrouper, Année et Mois
Glisser Année en étiquette de colonnes
Sélectionner tous les Codes de la famille 1 , clic droit, grouper : Groupe1 apparait : taper Famille1 dans cette cellule
faire de même pour les codes restants et Famille2
décocher CODE dans la liste des champs en haut du volet de droite pour ne garder que les familles

Le reste est du paramétrage de la mise en forme (onglet Création de l'onglet Outils de Tableaux croisés)

Pour ajouter un segment, cliquer dans un cellule du Tableau Croisé, Insertion, Segment

 

[JHA]

Bonjour à tous,

Un essai avec sommeprod

=SOMMEPROD((BDD!$A$2:$A$850=CALCUL!D$3)*(BDD!$B$2:$B$850=CALCUL!$C4)*(--NON(ESTERREUR(EQUIV(BDD!$C$2:$C$850;CALCUL!$A$3:$A$15;0)))+NON(ESTERREUR(EQUIV(BDD!$C$2:$C$850;CALCUL!$B$3:$B$15;0))));BDD!$D$2:$D$850)

JHA

 

[Danybrett]

Bonjour, Chris et JHA

(Désolé pour le délai de réponse, ce weekend je n'étais pas disponible).
SUPER ! Vos deux propositions fonctionnent et correspondent exactement à ce que je recherchai.

Merci Chris pour ton explication, je viens d'apprendre un truc sur les tableaux croisés dynamique. 🙂

Merci JHA pour ta formule, elle fonctionne très bien. Mais elle est vachement compliquée à comprendre (j'ai beau la décortiquer dans tous les sens, je ne comprends pas comment elle fonctionne). Si tu as le temps, tu pourrais me l'expliquer ? STP 🙂

 

[JHA]

Bonjour à tous,

Je ne suis pas sûr que cela va t'éclairer!

JHA

 

[Danybrett]

Bonjour à tous,

merci JHA pour tes explications ! Je pense avoir compris la logique de fonctionnement de ta formule!

Encore merci pour l'aide que vous m'avez apporté, Chris et JHA. Vous m'avez débloqué et appris des trucs sur Excel! 🙂

Danybrett,