Téléchargez l'exemple de classeur
Ce didacticiel montre comment utiliser le Fonction SOMMEPROD d'Excel dans Excel.
Présentation de la fonction SOMMEPROD
La fonction SUMPRODUCT Multiplie des tableaux de nombres et additionne le tableau résultant.
Pour utiliser la fonction de feuille de calcul Excel SOMMEPROD, sélectionnez une cellule et tapez :
(Remarquez comment les entrées de formule apparaissent)
Fonction SOMMEPROD Syntaxe et entrées :
| 1 | =SUMPRODUCT(tableau1,tableau2,tableau3) |
tableau1 - Tableaux de nombres.
Qu'est-ce que la fonction SOMMEPROD ?
La fonction SOMMEPROD est l'une des fonctions les plus puissantes d'Excel. Son nom peut vous faire croire qu'il est uniquement destiné aux calculs mathématiques de base, mais il peut être utilisé pour bien plus encore.
Tableaux
SUMPRODUCT nécessite des entrées de tableaux.
Alors d'abord, qu'entendons-nous par « tableau » ? Un tableau est un simple groupe d'éléments (par exemple des nombres) disposés dans un ordre spécifique, tout comme une plage de cellules. Donc, si vous aviez les nombres 1, 2, 3 dans les cellules A1:A3, Excel lirait cela comme un tableau {1,2,3}. En fait, vous pouvez entrer {1,2,3} directement dans les formules Excel et il reconnaîtra le tableau.
Nous parlerons plus en détail des tableaux ci-dessous, mais regardons d'abord un exemple simple.
Mathématiques de base
Regardons un exemple de base de SUMPRODUCT, en l'utilisant pour calculer les ventes totales.
Nous avons notre table de produits, et nous voulons calculer les ventes totales. Vous serez tenté d'ajouter simplement une nouvelle colonne, de prendre la quantité vendue * le prix puis de résumer la nouvelle colonne. Au lieu de cela, cependant, vous pouvez simplement utiliser la fonction SOMMEPROD. Parcourons la formule :
| 1 | =SOMMEPROD(A2:A4,B2:B4) |
La fonction chargera les plages de nombres dans des tableaux, les multipliera les unes par rapport aux autres, puis additionnera les résultats :
| 1234 | =SOMMEPROD({100, 50, 10}, {6, 7, 5})=SOMMEPROD({100 * 6, 50 * 7, 10 * 5})=SOMMEPROD({600, 350, 50}=1000 |
La fonction SUMPRODUCT a pu multiplier tous les nombres pour nous ET faire la sommation.
Moyenne pondérée
Un autre cas où il est utile d'utiliser SUMPRODUCT est lorsque vous devez calculer une moyenne pondérée. Cela se produit le plus souvent lorsqu'il s'agit de travaux scolaires, examinons donc le tableau suivant.
Nous pouvons voir combien les quiz, les tests et les devoirs valent pour la note globale, ainsi que la moyenne actuelle pour chaque élément particulier. On peut alors calculer la note globale en écrivant
| 1 | =SOMMEPROD(B2:B4, C2:C4) |
Notre fonction multiplie à nouveau chaque élément des tableaux avant d'additionner le total. ça marche comme ça
| 123 | =SOMMEPROD({30%, 50%, 20%}, {73%, 90%, 95%})=SOMMEPROD({22%, 45%, 19%})=86% |
Plusieurs colonnes
Un autre endroit où nous pourrions utiliser SUMPRODUCT est avec encore plus de colonnes qui doivent toutes être multipliées les unes par rapport aux autres. Regardons un exemple où nous devons calculer le volume en morceaux de bois.
Plutôt que de créer une colonne d'aide pour calculer la vente totale pour chaque ligne, nous pouvons le faire avec une seule formule. Notre formule sera
| 1 | =SOMMEPROD(B2:B5, C2:C5, D2:D5) |
Les premiers éléments de chaque tableau se multiplieront les uns par rapport aux autres (par exemple, 4 * 2 * 1 = 8). Ensuite, le 2ème (4 * 2 * 2 = 16), et 3rd, etc. Dans l'ensemble, cela produira le tableau de produits qui ressemble à {8, 16, 16, 32). Le volume total serait alors la somme de ce tableau, 72.
Un critère
D'accord, ajoutons une autre couche de complexité. Nous avons vu que SUMPRODUCT peut gérer des tableaux de nombres, mais qu'en est-il si nous voulons vérifier des critères ? Eh bien, vous pouvez également créer des tableaux pour les valeurs booléennes (les valeurs booléennes sont des valeurs VRAI ou FAUX).
Par exemple, prenons un tableau de base {1, 2, 3}. Créons un tableau correspondant qui indique si chaque nombre est supérieur à 1. Ce tableau ressemblerait à {FALSE, TRUE, TRUE}.
Ceci est extrêmement utile dans les formules, car nous pouvons facilement convertir VRAI / FAUX en 1 / 0. Regardons un exemple.
À l'aide du tableau ci-dessous, nous voulons calculer « Combien d'unités vendues étaient rouges ? »
On peut le faire, avec cette formule :
| 1 | =SUMPRODUCT(A2:A4, --(B2:B4="Rouge")) |
"Attendez! Qu'est-ce qu'il y a avec le double symbole moins là-bas ? » vous dites. Rappelez-vous comment j'ai dit que nous pouvions convertir de Vrai/Faux en 1/0 ? Pour ce faire, nous forçons l'ordinateur à effectuer une opération mathématique. Dans ce cas, nous disons « prenez la valeur négative, puis reprenez la valeur négative ». En écrivant cela, notre tableau va changer comme ceci :
| 123 | {Vrai, vrai, faux}{-1, -1, 0}{1, 1, 0} |
Donc, revenons à la formule SUMPRODUCT complète, elle va se charger dans nos tableaux puis se multiplier, comme ceci
| 123 | =SOMMEPROD({100, 50, 10}, {1, 1, 0})=SOMMEPROD({100, 50, 0})=150 |
Notez comment le 3rd item est devenu un 0, car tout ce qui est multiplié par 0 devient zéro.
Critères multiples
Nous pouvons charger jusqu'à 255 tableaux dans notre fonction, nous pouvons donc certainement charger plus de critères. Regardons cette plus grande table où nous avons ajouté le mois vendu.
Si on veut savoir combien d'articles vendus étaient rouges et étaient au mois de février, nous pourrions écrire notre formule comme
| 1 | =SUMPRODUCT(A2:A4, --(B2:B4="Rouge"), --(C2:C4="Feb")) |
L'ordinateur évaluerait ensuite nos tableaux et se multiplierait. Nous avons déjà expliqué comment les tableaux True/False se transforment en 1/0, je vais donc sauter cette étape pour le moment.
| 123 | =SOMMEPROD({100, 50, 10}, {1, 1, 0}, {0, 1, 1})=SOMMEPROD({0, 50, 0})=50 |
Nous n'avions qu'une seule ligne dans notre exemple qui correspondait à tous les critères, mais avec des données réelles, vous avez peut-être eu plusieurs lignes dont vous aviez besoin pour être additionnées.
Critères complexes
D'accord, jusqu'à présent, vous pourriez ne pas être impressionné car tous nos exemples auraient pu être réalisés à l'aide d'autres fonctions telles que SUMIF ou COUNTIF. Maintenant, nous allons faire quelque chose de ces autres fonctions ne peut pas faire. Auparavant, notre colonne Mois avait les noms réels des mois. Et si à la place il y avait des dates ?
Nous ne pouvons pas faire un SUMIF maintenant, car SUMIF ne peut pas gérer les critères dont nous avons besoin. SUMPRODUCT peut cependant nous permettre de manipuler le tableau et de faire un test plus approfondi. Nous avons déjà manipulé des tableaux lorsque nous avons traduit le vrai/faux en 1/0. Nous allons manipuler ce tableau avec la fonction MONTH. Voici la formule complète que nous allons utiliser
| 1 | =SOMMEPROD(A2:A4, --(B2:B4="Rouge"), --(MOIS(C2:C4)=2)) |
Regardons les 3rd tableau de plus près. Tout d'abord, notre formule va extraire le numéro du mois de chaque date dans C2:C4. Cela nous donnera {1, 2, 2}. Ensuite, nous vérifions si cette valeur est égale à 2. Maintenant, notre tableau ressemble à {False, True, True}. On refait le double moins, et on a {0, 1, 1}. Nous sommes maintenant de retour à un endroit similaire à celui de l'exemple 3, et notre formule pourra nous dire qu'il y avait 50 unités vendues en février qui étaient rouges.
Double moins vs multiplication
Si vous avez déjà vu la fonction SOMMEPROD en cours d'utilisation, vous avez peut-être vu une notation légèrement différente. Plutôt que d'utiliser un double moins, vous pouvez écrire
| 1 | =SOMMEPROD(A2:A4*(B2:B4="Rouge")*(MOIS(C2:C4)=2)) |
La formule va toujours fonctionner de la même manière, nous disons simplement manuellement à l'ordinateur que nous voulons multiplier les tableaux. SUMPRODUCT allait le faire de toute façon, il n'y a donc aucun changement dans la façon dont les mathématiques fonctionnent. L'exécution de l'opération mathématique convertit notre vrai/faux en 1/0 de la même manière. Alors pourquoi la différence ?
La plupart du temps, cela n'a pas trop d'importance, et cela dépend des préférences de l'utilisateur. Il existe au moins un cas où la multiplication est nécessaire.
Lorsque vous utilisez SUMPRODUCT, l'ordinateur s'attend à ce que tous les arguments (tableau1, tableau2, etc.) soient de la même taille. Cela signifie qu'ils ont le même nombre de lignes ou de colonnes. Cependant, vous pouvez faire ce que l'on appelle un calcul de tableau à deux dimensions avec SUMPRODUCT que nous verrons dans l'exemple suivant. Lorsque vous faites cela, les tableaux sont de tailles différentes, nous devons donc contourner cette vérification "tous de la même taille".
Deux dimensions
Tous les exemples précédents avaient nos tableaux allant dans la même direction. SUMPRODUCT peut gérer les choses dans deux directions, comme nous le verrons dans le tableau suivant.
Voici notre tableau des unités vendues, mais les données sont réorganisées là où les catégories se situent en haut. Si nous voulons savoir combien d'articles étaient rouges et dans la catégorie A, nous pouvons écrire
| 1 | =SOMMEPROD((A2:A4="Rouge")*(B1:C1="A")*B2:C4) |
Qu'est-ce qui se passe ici?? Il s'avère que nous allons nous multiplier dans deux directions différentes. Visualiser cela est plus difficile à faire avec une simple phrase écrite, nous avons donc quelques images pour nous aider. Tout d'abord, notre critère de ligne (est-ce rouge ?) va se multiplier sur chaque ligne du tableau.
| 1 | =SOMMEPROD((A2:A4="ROUGE")*B2:C4) |
Ensuite, le critère de la colonne (s'agit-il de la catégorie A ?)
| 1 | =SOMMEPROD((A2:A4="Rouge")*(B1:C1="A")*B2:C4) |
Une fois que ces deux critères ont fait leur travail, les seuls non-zéros restants sont les 5 et 10. SUMPRODUCT nous donnera alors le total général de 15 comme réponse.
Rappelez-vous comment nous avons parlé des tableaux devant être de la même taille à moins que vous ne fassiez deux dimensions ? C'était en partie exact. Regarde à nouveau les tableaux que nous avons utilisés dans notre formule. Les la taille de deux de nos tableaux est le même, et le largeur de deux de nos tableaux sont les mêmes. Donc, vous devez toujours vous assurer que les choses vont s'aligner correctement, mais vous pouvez le faire dans différentes dimensions.
Deux dimensions et complexe
Plusieurs fois, nous sommes présentés avec des données qui ne sont pas dans la meilleure mise en page adaptée à nos formules. Nous pourrions essayer de le réorganiser manuellement, ou nous pouvons être plus intelligents avec nos formules. Considérons le tableau suivant.
Ici, nous avons mélangé les données de nos articles et de nos ventes pour chaque mois. Comment ferions-nous pour savoir combien d'articles Bob a vendus pendant toute l'année ?
Pour ce faire, nous utiliserons deux fonctions supplémentaires : SEARCH et ISNUMBER. La fonction RECHERCHE va nous permettre de rechercher notre mot-clé « éléments » dans les cellules d'en-tête. La sortie de cette fonction va soit par un nombre soit par une erreur (si le mot-clé n'est pas trouvé). Ensuite, nous utiliserons le ISNUMBER pour convertir cette sortie dans nos valeurs booléennes. Notre formule va ressembler à ci-dessous.
Vous devriez être assez familier avec le premier tableau maintenant. Cela va créer une sortie comme {0, 1, 0, 1}. Le prochain tableau de critères dont nous venons de parler. Cela va créer un numéro pour toutes les cellules contenant des « éléments » et une erreur pour les autres {5, #N/A !, 5, #N/A !}. ISNUMBER le convertit ensuite en booléen {True, False, True, False}. Ensuite, lorsque nous multiplions, cela ne conservera que les valeurs de la première et de la troisième colonne. Une fois que tous les tableaux se sont multipliés les uns contre les autres, les seuls nombres non nuls que nous aurons sont ceux mis en évidence ici :
| 1 | =SUMPRODUCT((A2:A5="Bob")*(ISNUMBER(SEARCH("Items",B1:E1))*B2:E5)) |
Le SUMPRODUCT additionnera ensuite tout cela et nous obtiendrons notre résultat final de 29.
SOMMEPROD Ou
De nombreuses situations surviennent où nous aimerions pouvoir additionner des valeurs si notre colonne de critères a une valeur OU une autre valeur. Vous pouvez accomplir cela dans SUMPRODUCT en ajoutant deux tableaux de critères l'un contre l'autre.
Dans cet exemple, nous voulons additionner les unités vendues pour le rouge et le bleu.
Notre formule ressemblera à ceci
| 1 | =SUMPRODUCT(A2:A7, (B2:B7="Rouge")+(B2:B7="Bleu")) |
Regardons le tableau des critères rouges. Il produira un tableau qui ressemble à ceci : {1, 1, 0, 0, 0, 0}. Le tableau de critères bleu ressemblera à {0, 0, 1, 0, 1, 0}. Lorsque vous les additionnez, le nouveau tableau ressemblera à {1, 1, 1, 0, 1, 0}. Nous pouvons voir comment les deux tableaux se sont mélangés en un seul tableau de critères. La fonction multipliera ensuite cela par notre premier tableau, et nous obtiendrons {100, 50, 10, 0, 75, 0}. Notez que les valeurs de Vert ont été mises à zéro. La dernière étape du SUMPRODUCT consiste à additionner tous les nombres pour atteindre notre solution de 235.
Un mot d'avertissement ici. Soyez prudent lorsque les tableaux de critères ne sont pas mutuellement exclusifs. Dans notre exemple, les valeurs de la colonne B pourraient être Rouge ou Bleu, mais nous savions que cela ne pourrait jamais être les deux. Considérez si nous avions écrit cette formule :
| 1 | =SOMMEPROD(A2:A7, (A2:A7>=50)+(B2:B7="Bleu")) |
Notre intention est de trouver des articles bleus qui ont été vendus ou qui étaient en quantité supérieure à 50. Cependant, ces conditions ne sont pas exclusives, car une seule ligne peut être à la fois supérieure à 50 dans la colonne A et être bleu. Le premier tableau de critères ressemblerait alors à {1, 1, 0, 1, 1, 0}, le deuxième tableau de critères étant {0, 0, 1, 0, 1, 0}. Leur addition a produit {1, 1, 1, 1, 2, 0}. Voyez-vous comment nous avons un 2 là-dedans maintenant? S'il est laissé seul, le SUMPRODUCT finira par doubler la valeur de cette ligne, changeant le 75 en 150, et nous obtiendrions le mauvais résultat. Pour corriger cela, nous plaçons une vérification de critères externes sur notre tableau, comme ceci :
| 1 | =SOMMEPROD(A2:A7, --((A2:A7>=50)+(B2:B7="Bleu")>0)) |
Maintenant, une fois que les deux tableaux de critères internes ont été additionnés, nous allons vérifier si le résultat est supérieur à 0. Cela supprime les 2 que nous avions auparavant, et à la place nous aurons un tableau comme {1, 1, 1 , 1, 1, 0} qui produira le résultat correct.
SOMMEPROD Exact
La plupart des fonctions dans Excel ne sont pas sensibles à la casse, mais nous devons parfois pouvoir effectuer une recherche en tenant compte de la sensibilité à la casse. Lorsque le résultat souhaité est numérique, nous pouvons y parvenir en utilisant EXACT dans la fonction SOMMEPROD. Considérez le tableau suivant :
Nous voulons trouver le score pour l'article "ABC123". Normalement, la fonction EXACT comparera deux éléments et renverra une sortie booléenne indiquant si les deux éléments sont exactement le même. Cependant, puisque nous sommes à l'intérieur d'un SUMPRODUCT, notre ordinateur saura que nous avons affaire à des tableaux et pourra comparer un élément avec chaque élément d'un tableau. Notre formule ressemblera à ceci
| 1 | =SOMMEPROD(--EXACT("ABC123", A2:A5), B2:B5) |
La fonction EXACT vérifiera ensuite chaque élément dans A2:A5 pour voir s'il correspond à la valeur et à la casse. Cela produira un tableau qui ressemble à {0, 1, 0, 0}. Lorsqu'il est multiplié par B2:B5, le tableau devient {0, 2, 0, 0}. Après la sommation finale, nous obtenons notre solution de 2.
SUMPRODUCT dans Google Sheets
La fonction SOMMEPROD fonctionne exactement de la même manière dans Google Sheets que dans Excel :
SUMPRODUCT Exemples en VBA
Vous pouvez également utiliser la fonction SOMMEPROD en VBA. Tapez : application.worksheetfunction.sumproduct(array1,array2,array3)
Exécuter les instructions VBA suivantes
| 1 | Range("B10") = Application.WorksheetFunction.SumProduct(Range("A2:A7"), Range("B2:B7")) |
produira les résultats suivants
Pour les arguments de la fonction (tableau1, etc.), vous pouvez soit les entrer directement dans la fonction, soit définir des variables à utiliser à la place.
