Recherche explications sur SOMMEPROD

[sebgo]

Bonjour le forum,
Pour regrouper des données de vente par trimestre à partir d'une plage sur 12 mois j'utilisais un TCD à cet effet. Mais depuis que j'ai decouvert la fonction SOMMEPROD, je ne m'en passe plus. C'est ainsi que j'ai téléchargé l'excellent tutoriel de MONIQUE et CELEDA (que je remercie) sur cette fonction. Il existe des formules qui m'ont permis d'abandonner un peu les TCD. Au titre de ces formules il Y'a celles-ci qui font bien les sommes mais j'ai du mal à les décoriquer.
=SOMME(DECALER($B$7:$D$7;;(COLONNE()-COLONNE($B12))*3))
=SOMMEPROD((ENT((MOIS($B$6:$M$6)+2)/3)=(COLONNE()-COLONNE($B12)+1))*$B$7:$M$7)
=SOMMEPROD((MOIS($B$6:$M$6)>9)*$B$7:$M$7)
=SOMMEPROD(((MOIS($B$6:$M$6))=({10;11;12}))*$B$7:$M$7)
Elles sont équivalentes. Qq'1 peut-il me donner des explications.
PS:L'aide d'excel ne pas fait avancer
Merci d'avance

 

[Charly2]

Bonsoir sebgo,

C'est vrai qu'il faut avoir une bonne habitude des formules pour en comprendre certaines. Je ne suis pas un formuliste, mais tout m'intéresse

En préliminaire, il faut savoir que pour XL une condition VRAI est égale à 1 et FAUX à 0 (ce ne sont pas à proprement parler des nombres, mais le fait de les multiplier par un nombre les convertit automatiquement).

Les 2 premières formules doivent correspondre à des totaux trimestriels, alors que les 2 dernières effectuent le total du dernier trimestre...

=SOMME(DECALER($B$7:$D$7;;(COLONNE()-COLONNE($B12))*3))

effectue la somme du contenu de 3 cellules. Quelles cellules ? C'est la fonction DECALER qui nous l'indique (les formules sont, je pense, saisies dans les colonnes B à E).

En colonne B, DECALER($B$7:$D$7;;(COLONNE()-COLONNE($B12))*3) donne

DECALER($B$7:$D$7;;(2-2)*3) - colonne() renvoit 2 comme colonne($B$12), donc la plage n'est pas décalée et la somme est faite sur $B$7:$D$7.

Avec le même raisonnement, en colonne C, on obtient

DECALER($B$7:$D$7;;(3-2)*3) i.e. DECALER($B$7:$D$7;;3), la somme est effectuée sur une plage de 3 colonnes de la ligne 7 décalée de 3 colonnes vers la droite, soit sur la plage $E$7:$G$7.

Tu suis ?

=SOMMEPROD((ENT((MOIS($B$6:$M$6)+2)/3)=(COLONNE()-COLONNE($B12)+1))*$B$7:$M$7)

Là, c'est un peu plus délicat ; la première fois que j'ai vu une formule comme ça, j'ai cru que je n'arriverais jamais à comprendre

Ici, il faut d'abord s'intéresser à la condition. Tes dates sont en B6:M6 et les totaux par trimestre seront placés dans les colonnes B à E.

1) la formule est en colonne B mais toutes les cellules de $B$6:$M$6 seront testées, voyons le résultat de la partie droite de la comparaison tout en se souvenant que les dates sont en B6:M6 :
-> ENT((MOIS($B$6)+2)/3) = ENT((01+2)/3) = 1
-> ENT((MOIS($C$6)+2)/3) = ENT((02+2)/3) = 1
-> ENT((MOIS($D$6)+2)/3) = ENT((03+2)/3) = 1

voilà pour le premier trimestre, pour le second:

-> ENT((MOIS($E$6)+2)/3) = ENT((04+2)/3) = 2
-> ENT((MOIS($F$6)+2)/3) = ENT((05+2)/3) = 2
-> ENT((MOIS($G$6)+2)/3) = ENT((06+2)/3) = 2

etc.

pour la partie droite de la comparaison :
-> la fonction étant placée en B, (COLONNE()-COLONNE($B12)+1 = 1

Je suppose que tu comprends qu'il s'agit de faire la somme des colonnes du 1er trimestre, le résultat sera la somme des cellules B7, C7 et D7...

Un raisonnement identique s'applique aux 3 autres trimestres avec la formule placée dans les colonnes C à E.

=SOMMEPROD((MOIS($B$6:$M$6)>9)*$B$7:$M$7)

Celle-ci sera plus facile à comprendre, car il s'agit de faire la somme des cellules de B7:M7 pour lesquelles les mois en ligne 6 sont supérieurs à 9.

Le résultat sera la somme de K7:M7 car seuls les 3 derniers mois renvoient la valeur VRAI (=1 pou XL), soit les mois des colonnes K, L et M.

=SOMMEPROD(((MOIS($B$6:$M$6))=({10;11;12}))*$B$7:$M$7)

Elle ressemble un peu à la précédente à ceci près que chaque cellule de B7:M7 sera comparée avec la matrice {10;11;12}, donc uniquement avec les mois d'octobre, novembre et décembre, même retour que la formule précédente...

Ouf !

Je ne sais pas si tu as lu ce post jusqu'au bout mais tu es courageux si tu l'as fait

A+

 

[sebgo]

Bonjour Charly2, bonjour le forum,
Merci Charly pour les explications limpides. J'ai lu le post jusqu'au bout mais je crois qu'il me faut le relire +sieurs fois pour piger. C'est promis; les formules, j'en ferai mon affaire.
Bonne journée et A+

 

[Monique]

Bonjour,

Dans le Zip AutresFormulesII,
tu trouveras 2 classeurs,
dont un avec des exemples assez simples de SommeProd
https://www.excel-downloads.com/resources/autres-formules-ii.104/