Comment utiliser la fonction Excel SUMPRODUCT -

Sommaire

La fonction SUMPRODUCT multiplie les plages ou les tableaux ensemble et renvoie la somme des produits. Cela semble ennuyeux, mais SUMPRODUCT est une fonction incroyablement polyvalente qui peut être utilisée pour compter et additionner comme COUNTIFS ou SUMIFS, mais avec plus de flexibilité. D'autres fonctions peuvent facilement être utilisées dans SUMPRODUCT pour étendre encore plus les fonctionnalités.

Objectif

Multiplier, puis additionner les tableaux

Valeur de retour

Le résultat de tableaux multipliés et additionnés

Syntaxe

= SOMMEPROD (tableau1, (tableau2),…)

Arguments

• array1 - Le premier tableau ou plage à multiplier, puis ajouter.
• array2 - (facultatif) Le deuxième tableau ou plage à multiplier, puis ajouter.

Version

Excel 2003

Notes d'utilisation

La fonction SOMMEPROD fonctionne avec les tableaux, mais elle ne nécessite pas la syntaxe de tableau normale (Ctrl + Maj + Entrée) pour entrer. Le but de la fonction SUMPRODUCT est de multiplier, puis de additionner, des tableaux. Si un seul tableau est fourni, SUMPRODUCT additionnera simplement les éléments du tableau. Jusqu'à 30 baies peuvent être fournies.

Lorsque vous rencontrez SUMPRODUCT pour la première fois, cela peut sembler ennuyeux, complexe et même inutile. Mais SUMPRODUCT est une fonction incroyablement polyvalente avec de nombreuses utilisations. Parce qu'il gère les tableaux avec élégance et sans plainte, vous pouvez l'utiliser pour traiter des plages de cellules de manière intelligente et élégante (voir les liens vers des exemples de formules sur cette page).

Pour illustrer le fonctionnement de SUMPRODUCT, voici quelques exemples courants.

SUMPRODUCT pour les sommes et les comptes conditionnels

Supposons que vous ayez des données de commande dans A2: B6, avec État dans la colonne A, Ventes dans la colonne B:

	UNE	B
1	Etat	Ventes
2	Utah	75
3	CO	100
4	TX	125
5	CO	125
6	TX	150

En utilisant SUMPRODUCT, vous pouvez compter les ventes totales pour le Texas ("TX") avec cette formule:

=SUMPRODUCT(--(A2:A6="TX"))

Et vous pouvez additionner les ventes totales pour le Texas ("TX") avec cette formule:

=SUMPRODUCT(--(A2:A6="TX"),B2:B6)

Remarque: ne soyez pas confus par le double négatif. Il s'agit d'une astuce courante utilisée dans les formules Excel plus avancées pour forcer les valeurs VRAI et FAUX en 1 et 0. En savoir plus ci-dessous…

Pour l'exemple de somme ci-dessus, voici une représentation virtuelle des deux tableaux traités en premier par SUMPRODUCT:

tableau1	tableau2
FAUX	75
FAUX	100
VRAI	125
FAUX	125
VRAI	150

Chaque tableau contient 5 éléments. Le premier tableau contient les valeurs TRUE / FALSE qui résultent de l'expression A2: A6 = "TX" et le second tableau est le contenu de B2: B6. Chaque élément du premier tableau sera multiplié par l'élément correspondant du deuxième tableau. Toutefois, dans l'état actuel, le résultat de SUMPRODUCT sera zéro car les valeurs TRUE et FALSE seront traitées comme zéro. Nous avons besoin que les éléments de array1 soient numériques - ils doivent être "forcés" en uns et en zéros. C'est là qu'intervient le double négatif.

En utilisant le double négatif - (double unaire, pour vos types techniques), nous sommes en mesure de contraindre TRUE / FALSE aux valeurs numériques un et zéro, comme indiqué dans la représentation virtuelle ci-dessous. La dernière colonne "Produit" représente le résultat de la multiplication des deux tableaux ensemble. Le résultat additionné, 275, est la valeur renvoyée par SUMPRODUCT.

tableau1		tableau2		Produit
0	*	75	=	0
0	*	100	=	0
1	*	125	=	125
0	*	125	=	0
1	*	150	=	150
Somme				275

En utilisant la syntaxe d'accolades pour les tableaux, l'exemple ressemble à ceci après la coercition:

=SUMPRODUCT((0,0,1,0,1),(75,100,125,125,150)) 

et comme ça après multiplication:

=SUMPRODUCT((0,0,125,0,150))

Cet exemple développe les idées ci-dessus avec plus de détails.

SUMPRODUCT avec d'autres fonctions

SUMPRODUCT peut utiliser directement d'autres fonctions. Vous pouvez voir SUMPRODUCT utilisé avec la fonction LEN pour compter le nombre total de caractères dans une plage, ou avec des fonctions comme ISBLANK, ISTEXT, etc. Ce ne sont normalement pas des fonctions de tableau, mais lorsqu'elles reçoivent une plage, elles créent un "tableau de résultats". Étant donné que SUMPRODUCT est conçu pour fonctionner avec des tableaux, il est capable d'effectuer des calculs sur les tableaux directement. Cela peut être un bon moyen d'économiser de l'espace dans une feuille de calcul, en éliminant le besoin d'une colonne «d'assistance».

Par exemple, supposons que vous ayez 10 valeurs de texte différentes dans A1: A10 et que vous souhaitiez compter le nombre total de caractères pour les 10 valeurs. Vous pouvez ajouter une colonne d'aide dans la colonne B qui utilise cette formule: LEN (A1) pour calculer les caractères dans chaque cellule. Ensuite, vous pouvez utiliser SUM pour additionner les 10 nombres. Cependant, en utilisant SUMPRODUCT, vous pouvez écrire une formule comme celle-ci:

=SUMPRODUCT(LEN(A1:A10))

Lorsqu'il est utilisé avec une plage telle que A1: A10, LEN renvoie un tableau de 10 valeurs. Ensuite, SUMPRODUCT additionnera simplement toutes les valeurs et retournera le résultat, sans aucune colonne d'assistance nécessaire.

Voir les exemples ci-dessous de nombreuses autres façons d'utiliser SUMPRODUCT.

Remarques:

1. SUMPRODUCT traite les éléments non numériques dans les tableaux comme des zéros.
2. Les arguments de tableau doivent être de la même taille. Sinon, SUMPRODUCT générera un #VALUE! valeur d'erreur.
3. Les tests logiques à l'intérieur des tableaux créeront des valeurs TRUE et FALSE. Dans la plupart des cas, vous voudrez les forcer à 1 et 0.
4. SUMPRODUCT peut souvent utiliser directement le résultat d'autres fonctions (voir les exemples de formules ci-dessous)