Comment utiliser la fonction Excel SUMPRODUCT

Vous pourriez penser que la SOMMEPROD fonction Excel est assez ennuyeux à première vue, mais la fonction est en réalité extrêmement utile et polyvalente. Bien que son objectif principal soit de multiplier et d'additionner des données dans des tableaux, vous pouvez également l'utiliser pour un large éventail d'applications plus complexes. Cela étant dit, je vais entrer dans les détails de cette fonction.

Syntaxe

• tableau1 est le premier tableau que vous souhaitez multiplier avec les autres tableaux, puis ajouter les résultats
• tableau2 et tous les autres tableaux par la suite sont des entrées optionnelles. La fonction multipliera les composantes de tous les tableaux, puis additionnera les résultats

Comment utiliser SOMMEPROD dans Excel

Je vais commencer par vous montrer un exemple simple de la fonction SEMIPRODUIT d'Excel. Comme je l'ai mentionné précédemment, la fonction est principalement utilisée pour multiplier des tableaux et additionner les résultats.

Regardez l'exemple ci-dessous. J'ai des données de ventes d'un stand de fruits.

Je l'ai fait dans la colonne F, mais vous pouvez voir que cette méthode nécessite une nouvelle colonne et une tonne de calculs individuels. La fonction SOMMEPRODUT est un moyen beaucoup plus rapide de résoudre ce problème !

Par dactylographie =SOMMEPROD(B2:B7;C2:C7) J'indique à la fonction de multiplier chaque quantité par le prix correspondant, et d'additionner le tout. Cela me donne le chiffre d'affaires en une seule cellule !

J'ai ajouté les calculs plus longs dans la colonne F pour que vous puissiez voir que le résultat est le même – la fonction SOMMEPROD est juste beaucoup plus rapide.

Utiliser SUMPRODUCT pour compter

Lorsque vous entrez plusieurs tableaux, SOMMEPROD les multiplie et les additionne. Mais que se passe-t-il si vous n'entrez qu'un seul tableau ? Comme vous l'avez vu dans la syntaxe, seul le premier tableau est requis pour que la fonction fonctionne.

Lorsque vous n'entrez qu'un seul tableau, la fonction additionne simplement ce tableau. Vous pouvez le voir dans la capture d'écran ci-dessous, où j'utilise la fonction =SOMMEPROD(B2:B7) pour additionner la quantité totale d'articles vendus.

Dans ce cas, SUMPRODUCT fonctionne comme la fonction SOMME !

Combiner avec d'autres fonctions

L'un des avantages de la fonction SOMMEPROD est que vous pouvez la combiner avec d'autres fonctions.

Dans l'exemple ci-dessous, je le combine avec la fonction LEN pour trouver le nombre total de caractères avec la formule =SOMMEPROD(NBCAR(A2:A7)).

Autres opérateurs

Sur la base de son nom, vous pourriez penser que la fonction SOMMEPROD peut uniquement multiplier des tableaux. Cependant, la fonction peut également effectuer des opérations arithmétiques (*, /, +, -) entre des tableaux. Le thème constant est qu'elle effectue toujours l'opération sur les tableaux, puis additionne le total.

Jetez un coup d'œil à l'exemple ci-dessous. J'utilise la fonction SOMMEPROD pour additionner les colonnes Prix et Taxe, puis je multiplie par la quantité. Je fais cela avec la formule =SOMMEPROD(B2:B7*(C2:C7+D2:D7)).>

Astuce : pour spécifier quelle opération vous souhaitez effectuer, ajoutez simplement le signe entre les tableaux au lieu d'utiliser une virgule. Il est également judicieux d'utiliser des parenthèses pour vous assurer que les opérations sont effectuées dans l'ordre souhaité.

Compter avec du texte

J'ai utilisé SOMMEPROD avec des gammes de nombres, mais maintenant je vais vous montrer comment vous pouvez l'utiliser avec du texte.

Dans cet exemple, j'utilise la colonne Cible comme mon tableau, mais je spécifie également que je ne veux que les cellules contenant la chaîne de caractères “ Banane ” (j'ai ajouté une autre ligne de bananes juste pour cet exemple).

Vous remarquerez quelque chose d'étrange dans la formule utilisée ici, =SOMMEPROD(–(A2:A8=”Banane”)). Il y a deux signes négatifs (-) avant les parenthèses. Ne vous attardez pas trop sur cela - c'est juste ce dont la formule a besoin pour calculer les résultats que je veux.

Lorsque SUMPRODUCT évalue l'expression A2:A8=”Banane”, il parcourt la colonne et attribue un VRAI ou FAUX à chaque cellule. Pour additionner ceux-ci, je dois les convertir de VRAI/FAUX en un nombre.

Le “–“ revient à multiplier par -1 deux fois : une fois pour convertir le VRAI/FAUX en un nombre, et une fois de plus pour annuler le négatif. Ainsi, VRAI devient 1, et FAUX devient 0. La fonction additionne ensuite ces valeurs et indique correctement qu'il y a 2 occurrences de Banane.

Notez que vous pourriez obtenir le même effet en utilisant 1\* au lieu de — (c'est-à-dire multiplier par 1 au lieu de par -1 deux fois), mais il est un peu plus rapide de taper les deux tirets.

SOMMEPROD avec chaîne de texte et plusieurs tableaux

Maintenant, je vais combiner certains des exemples précédents. Ici, je veux le chiffre d'affaires total de la vente de bananes. Je dois utiliser 3 tableaux différents ici.

Le premier examine les chaînes de caractères de la colonne Item et indique à la fonction de ne considérer que les bananes (en leur attribuant 1 et le reste 0). Ensuite, il multiplie cela par le prix et la quantité, et additionne le résultat pour obtenir le revenu total des bananes.

La formule est la suivante =SOMMEPROD((A2:A8=”Banane”)*B2:B8*C2:C8). Notez que contrairement au dernier exemple, je n'ai pas besoin de — avant le premier tableau. C'est parce que la VRAI/FAUX générée par A2:A8=”Banane” est convertie en un nombre lorsqu'elle est multipliée avec les autres tableaux. J'avais donc seulement besoin de cette astuce lorsqu'elle était pour un seul tableau !

Cet exemple démontre vraiment la puissance de la fonction SOMMEPROD. Elle effectue les opérations que je souhaite sur chaque entrée de mes données et filtre également sélectivement pour ne donner que les résultats des éléments que je souhaite inclure. Le tout à partir d'une seule formule !

J'espère que vous voyez maintenant l'utilité de la fonction SOMMEPROD, et comment l'utiliser pour effectuer des calculs complexes avec une seule formule simple !