Exceller

Fonction SOMMEPROD d'Excel

Excel Sumproduct Function

Fonction SOMMEPROD d'ExcelSommaire

La fonction SOMMEPROD multiplie des plages ou des tableaux 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.



Objet Multiplier, puis sommer des tableaux Valeur de retour Le résultat des tableaux multiplexés et sommés Syntaxe =SUMPRODUCT (array1, [array2], ...) Arguments
  • tableau1 - Le premier tableau ou plage à multiplier, puis à ajouter.
  • tableau2 - [facultatif] Le deuxième tableau ou plage à multiplier, puis à ajouter.
Version Excel 2003 Notes d'utilisation

La fonction SOMMEPROD fonctionne avec des tableaux, mais elle ne nécessite pas la syntaxe normale des tableaux (Ctrl + Maj + Entrée) pour entrer. Le but de la fonction SOMMEPROD est de multiplier, puis de sommer, 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 traitera 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 :



À B
1 État Ventes
2 DEHORS 75
3 QUEL 100
4 Émission 125
5 QUEL 125
6 Émission 150

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

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

Et tu peux somme 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 en 0. Voir plus ci-dessous...

comment faire la quadrature d'un nombre dans Excel

Pour l'exemple de somme ci-dessus, voici une représentation virtuelle des deux tableaux tels qu'ils ont été 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 VRAI / FAUX qui résultent de l'expression A2:A6='TX', et le deuxième tableau est le contenu de B2:B6. Chaque élément du premier tableau sera multiplié par l'élément correspondant du deuxième tableau. Cependant, dans l'état actuel, le résultat de SUMPRODUCT sera zéro car les valeurs VRAI et FAUX seront traitées comme zéro. Nous avons besoin que les éléments de array1 soient numériques - ils doivent être 'contraints' en uns et des zéros. C'est là qu'intervient le double négatif.

En utilisant le double négatif -- (double unaire, pour vous les types techniques), nous sommes en mesure de contraindre le VRAI/FAUX dans les valeurs numériques un et zéro, comme indiqué dans la représentation virtuelle ci-dessous. La dernière colonne 'Product' 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 des accolades pour les tableaux, l'exemple ressemble à ceci après coercition :

comment utiliser une formule indirecte dans Excel
 
= SUMPRODUCT ({0,0,1,0,1},{75,100,125,125,150}) 

et comme ceci 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 telles que 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 directement sur les tableaux. Cela peut être un bon moyen d'économiser de l'espace dans une feuille de calcul, en éliminant le besoin d'une colonne « d'aide ».

Par exemple, supposons que vous ayez 10 valeurs de texte différentes dans A1:A10 et que vous vouliez compter le nombre total de caractères pour les 10 valeurs. Vous pouvez ajouter une colonne d'assistance 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 comme A1:A10, LEN renvoie un tableau de 10 valeurs. Ensuite, SUMPRODUCT additionnera simplement toutes les valeurs et renverra le résultat, sans qu'aucune colonne d'aide ne soit 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 du tableau doivent être de la même taille. Sinon, SUMPRODUCT générera une #VALUE ! valeur d'erreur.
  3. Les tests logiques à l'intérieur des tableaux créeront des valeurs VRAI et FAUX. Dans la plupart des cas, vous voudrez les forcer à des 1 et des 0.
  4. SUMPRODUCT peut souvent utiliser directement le résultat d'autres fonctions (voir les exemples de formule ci-dessous)


^