Leçon 12 de 23: La fonction SOMMEPROD

IMPORTANTE NOTE: Les utilisateurs d'Excel (débutants et experts) qui ont suivi mes deux cours ou qui ont travaillé avec moi sur des centaines de projets considèrent que la découverte la plus importante qu'ils ont faite est celle de SOMMEPROD. Je tiens à aviser mes visiteurs qu'il est indispensable de découvrir les fonctionnalités de base de données avant d'aborder SOMMEPROD.

La fonction SOMMEPROD est la plus utile et la plus puissante des fonctions d'Excel. De plus, il est très facile de la maîtriser.

Imaginez une base de données de ventes comme la suivante avec des centaines ou des milliers d'enregistrements (lignes). Imaginez une base de données comptable, une base de données d'inventaire.....Ces données ont été saisies manuellement ou l'ensemble a été importé dans Excel à partir d'une base de données externe, d'un programme comptable ou d'un programme de gestion d'inventaire.

Vous voulez produire un rapport comme le suivant qui soit entièrement automatisé de sorte que quand vous ajoutez une ligne de données ou quand vous importez un nouvel ensemble de données, les totaux du rapport sont recalculés.

Vous voulez une formule simple que vous développez dans une seule cellule (coin supérieur gauche du tableau) et que vous pouvez copier/coller dans toutes les cellules du tableau.

Cette formule "magique" c'est SOMMEPROD. Avec la formule SOMMPROD vous pouvez faire la somme d'une ou de plusieurs colonnes en imposant des critères situés dans une ou plusieurs autres colonnes. Ainsi, la formule dans le coin supérieur gauche du tableau calcule la somme des nombres de la colonne "E" si le mois dans la colonne "A" est "Août", si le client dans la colonne "B" est "Acme" et si le produit dans la colonne "C" est "Casques". La formule qui suit se trouve dans la cellule B32 (en rouge) et elle se lit comme suit:

=SOMMEPROD((B2:B10=A32)*(C2:C10=B31)*(E2:E10))

En bon français cette formule dit:faire la somme du champ E3 à E10 si dans la colonne B la valeur est "Acme" et si dans la colonne C, la valeur est "Casques".

La syntaxe générale est:
=SOMMEPROD((1er critère)*(2ième critère)*(champ à additionner))
vous pouvez soumettre un nombre illimité de critères de recherche séparés par des astérisques et en gérant bien les références absolues et relatives, vous pouvez composer une seule formule SOMMEPROD dans le coin gauche supérieur du tableau et la copier partout. Avec les signes de dollar appropriés, la formule copiable se lit comme suit:
 =SOMMEPROD(($B$2:$B$10=$A32)*($C$2:$C$10=B$31)*($E$2:$E$10))

Avec SOMMEPROD vous additionnez mais vous pouvez aussi comptez, calculez des moyennes et vous pouvez même utiliser des "et" et des "ou". La formule SOMMEPROD sert aussi à rechercher des valeurs numériques dans des tableaux en utilisant des critères multiples.

Comme vous le verrez dans les fichiers suivants, la fonction SOMMEPROD permet aussi de répondre à de nombreux autres défis en matière d'analyse de données d'affaires.