Exploiter un tableau de données avec SOMMEPROD

L'une des applications les plus répandues d'Excel consiste à exploiter un tableau de données en bâtissant des synthèses, plus particulièrement des totaux et décomptes selon un ou plusieurs critères.

La photo ci-dessus provient de la banque photographique gratuite Freephotobank (lien : www.freephotosbank.com/3646.html)

De nombreuses méthodes permettent de parvenir à ses fins, parmi elles :

• L'emploi des fonctions SOUS.TOTAL combiné à la mise en place de filtres;
• Le recours à la fonction SOMME.SI, qui permet de ne sommer que certaines données sous condition;
• La forme matricielle des fonctions SOMME et SI, plus puissante que la précédente, mais pas toujours aisée à comprendre;
• Le détournement de la fonction SOMMEPROD, au nom rebutant et pourtant très claire en formulation, pour peu que l'on en comprenne la syntaxe.

Le vrai et le faux dans tous ses états
Rappelez-vous la constante de l'informatique : le 1 et le 0, le VRAI et le FAUX.
SOMMEPROD fonctionne sur ce principe.
Dans Excel, habituellement, les valeurs dites booléennes, le VRAI et le FAUX, sont exprimées comme telles.



Si vous écrivez dans une cellule la formule =(2>3), Excel répond : FAUX.

Dans le cadre de la fonction SOMMEPROD, en revanche, Excel est capable de revenir aux traditionnels 1 et 0, en donnant au booléen VRAI la valeur 1 et au booléen FAUX la valeur 0 (zéro).

Quel intérêt ? Eh bien d'autoriser des multiplications susceptibles d'éliminer les valeurs fausses (multiplication par zéro) ou de retenir les valeurs vraies (multiplication par 1) sans les dénaturer.

Considérons ce petit exemple de tableau :

Les données concernent des catégories de fruits associées à des poids. Pour l'exploiter, il faut totaliser le poids pour un fruit particulier.

Bien entendu, SOMME.SI y parvient aisément. Mais il est possible aussi d'utiliser SOMMEPROD.

Nommons au préalable les deux plages du titre du nom de leurs colonnes (sélection du tableau suivie de l'activation de la commande de menu Insertion > Nom > Créer - ligne du haut).

Au-dessus du tableau, prévoyons la saisie du fruit à totaliser (cellule B3) et une cellule de sommation conditionnelle. Dans cette dernière écrivons la formule ci-dessous :

=SOMMEPROD(Poids*(Fruits=B3))



La formulation est claire :

Elle multiplie le Poids par la condition (plage Fruits égale à celui qui est recherché) qui répond 1 si la condition est vraie, Zéro sinon, auquel cas elle annule le poids des autres fruits en le multipliant par le chiffre Zéro.

Et voici le résultat :
Les 7 kg de bananes correspondent bien à la sommation des lignes 7 (2kg), 11 (4kg) et 13 (1kg).

Mais c'est lorsqu'il faut combiner plusieurs critères que SOMMEPROD prend tout son intérêt.

SOMME.SI est peu adaptée à cet usage, tandis que les sommes matricielles paraissent souvent ésotériques…

Imaginons en effet que le tableau soit plus fourni, et qu'il existe une information complémentaire : le mois de la vente.

Dans ce nouvel exemple, deux critères interviennent dans le calcul du total du poids:
1. Le fruit,
2. Le mois de la vente.

La fonction SOMME.SI n'acceptant qu'un seul critère, sauf à "bricoler", on pourrait rédiger une formule de somme matricielle, du type ci-dessous.

{=SOMME(SI(Fruits=B3;SI(Mois=D3;Poids;0);0))}
(attention : la saisie de la formule doit être validée matriciellement, en pressant les touches contrôle et majuscules combinées avec Entrée).

Pour ma part, j'ai une nette préférence pour SOMMEPROD, rédigée ainsi :

=SOMMEPROD(Poids*(Fruits=B3)*(Mois=D3))

Nous effectuons la somme du Poids, en lui affectant deux critères booléens :
1. La nature du fruit (choisie en B3),
2. Le mois (saisi en D3).

La formulation est courte, les parenthèses peu nombreuses, et les critères clairement exposés. L'ajout de nouveaux critères ne complexifie pas la formule, contrairement aux imbrications de SI dans la formulation matricielle de somme, comme dans l'exemple ci-dessous, qui combine quatre critères :



La formule à base de SOMMEPROD capable de traiter 4 conditions est la suivante :

=SOMMEPROD(Poids*(Fruits=C4)*(Année=C5)*(Mois=C6)*(Ville=C7))

…à comparer à la somme conditionnelle matricielle (qu'il faut scinder sur deux lignes de ce blog) :

{=SOMME(SI(Fruits=C4;SI(Année=C5;
SI(Mois=C6;SI(Ville=C7;Poids;0);0);0);0))}

Il n'y a pas photo !

Pour en savoir plus sur la fonction SOMMEPROD, consultez les conseils suivants du livre :

• 60-Découvrez SOMMEPROD (page 211), qui examine plusieurs exemples, dont celui des intérêts d'un compte courant bancaire.
  
• 89-Expérimentez les formules complexes (page 359), qui donne un exemple de combinaison de SOMMEPROD avec des décalages de sélections…

Rappel (publicitaire) : 100 conseils pour mieux utiliser Excel est en vente sur le site des éditions AO à cette adresse.