Comprendre la fonction Excel SUMPRODUCT (SOMMEPROD)
La fonction SUMPRODUCT, traduite par SOMMEPROD en français, est une fonction matricielle qui multiplie les éléments correspondants de plusieurs matrices (plages de cellules) et renvoie la somme de ces produits. En d'autres termes, elle effectue une multiplication terme à terme entre plusieurs plages de cellules, puis additionne les résultats de ces multiplications.
Syntaxe de SUMPRODUCT
La syntaxe de la fonction est la suivante :
=SUMPRODUCT(array1, [array2], [array3], ...)
array1: La première plage de cellules ou matrice à multiplier.array2,array3, ... (optionnel): Les plages de cellules ou matrices supplémentaires à multiplier. Vous pouvez inclure jusqu'à 255 matrices.
Important: Toutes les matrices doivent avoir les mêmes dimensions (le même nombre de lignes et de colonnes). Si les dimensions ne correspondent pas, SUMPRODUCT renverra une erreur #VALUE!.
Comment SUMPRODUCT fonctionne étape par étape
Pour bien comprendre le fonctionnement de SUMPRODUCT, décomposons le processus :
- Multiplication terme à terme: La fonction prend les premières valeurs de chaque matrice et les multiplie ensemble. Elle fait de même pour les deuxièmes valeurs, les troisièmes, et ainsi de suite, jusqu'à la fin des matrices.
- Somme des produits: Une fois que toutes les multiplications sont effectuées, SUMPRODUCT additionne tous les produits résultants.
Exemple simple:
Supposons que vous ayez deux colonnes :
| Quantité | Prix unitaire |
|---|---|
| 2 | 10 |
| 3 | 5 |
| 1 | 20 |
La formule =SUMPRODUCT(A1:A3, B1:B3) effectuera les opérations suivantes :
- (2 * 10) + (3 * 5) + (1 * 20) = 20 + 15 + 20 = 55
Le résultat sera donc 55.
Utiliser SUMPRODUCT pour des calculs simples
Bien que SUMPRODUCT puisse sembler complexe au premier abord, elle est en réalité très simple à utiliser pour des calculs de base.
Calculer le chiffre d'affaires total
Reprenons l'exemple précédent. Vous pouvez utiliser SUMPRODUCT pour calculer directement le chiffre d'affaires total sans avoir besoin de créer une colonne supplémentaire pour les produits individuels.
Formule: =SUMPRODUCT(A1:A3, B1:B3)
Explication:
A1:A3contient les quantités.B1:B3contient les prix unitaires.
SUMPRODUCT multiplie chaque quantité par son prix unitaire correspondant, puis additionne les résultats pour obtenir le chiffre d'affaires total.
Calculer la somme pondérée
SUMPRODUCT est également idéale pour calculer des sommes pondérées. Une somme pondérée est une somme où chaque valeur est multipliée par un poids avant d'être additionnée.
Exemple:
Supposons que vous ayez les résultats de trois examens et leurs coefficients respectifs :
| Examen | Résultat | Coefficient |
|---|---|---|
| 1 | 80 | 0.3 |
| 2 | 90 | 0.4 |
| 3 | 75 | 0.3 |
Pour calculer la moyenne pondérée, vous pouvez utiliser la formule suivante :
=SUMPRODUCT(B1:B3, C1:C3)
Explication:
B1:B3contient les résultats des examens.C1:C3contient les coefficients.
SUMPRODUCT multiplie chaque résultat par son coefficient, puis additionne les résultats pour obtenir la moyenne pondérée.
Utiliser SUMPRODUCT avec des critères (calculs conditionnels)
La véritable puissance de SUMPRODUCT réside dans sa capacité à effectuer des calculs conditionnels. Vous pouvez utiliser des critères pour inclure ou exclure certaines valeurs du calcul.
Syntaxe pour les critères
Pour ajouter des critères à votre formule SUMPRODUCT, vous devez utiliser une syntaxe spécifique. Les critères sont généralement exprimés sous forme d'expressions logiques (VRAI ou FAUX) qui sont ensuite converties en 1 (VRAI) ou 0 (FAUX) pour le calcul.
La syntaxe générale est la suivante :
=SUMPRODUCT((condition1)*(condition2)*...*(plage_à_sommer))
Explication:
(condition1),(condition2), ... : Les conditions à vérifier. Chaque condition doit être entourée de parenthèses.plage_à_sommer: La plage de cellules à sommer, en tenant compte des conditions.
Important: Les conditions sont multipliées entre elles. Cela signifie que toutes les conditions doivent être VRAIES (égales à 1) pour que la valeur correspondante de la plage_à_sommer soit incluse dans le calcul.
Exemples de calculs conditionnels
1. Somme des ventes d'un produit spécifique:
Supposons que vous ayez un tableau de ventes avec les colonnes suivantes :
| Produit | Quantité | Prix unitaire |
|---|---|---|
| A | 2 | 10 |
| B | 3 | 5 |
| A | 1 | 20 |
| C | 4 | 8 |
| A | 2 | 12 |
Pour calculer le chiffre d'affaires total du produit A, vous pouvez utiliser la formule suivante :
=SUMPRODUCT((A1:A5="A")*(B1:B5)*(C1:C5))
Explication:
(A1:A5="A"): Cette condition vérifie si la valeur de la colonne Produit est égale à "A". Elle renvoie une matrice de VRAI/FAUX.(B1:B5): La colonne Quantité.(C1:C5): La colonne Prix unitaire.
SUMPRODUCT multiplie chaque élément de la matrice VRAI/FAUX par la quantité et le prix unitaire correspondants. Seules les lignes où le produit est "A" auront une valeur de 1 (VRAI) dans la matrice VRAI/FAUX, ce qui inclura ces lignes dans le calcul.
2. Somme des ventes supérieures à un certain montant:
En utilisant le même tableau de ventes, vous pouvez calculer le chiffre d'affaires total des ventes dont le prix unitaire est supérieur à 10 €.
=SUMPRODUCT((C1:C5>10)*(B1:B5)*(C1:C5))
Explication:
(C1:C5>10): Cette condition vérifie si le prix unitaire est supérieur à 10. Elle renvoie une matrice de VRAI/FAUX.(B1:B5): La colonne Quantité.(C1:C5): La colonne Prix unitaire.
Seules les lignes où le prix unitaire est supérieur à 10 auront une valeur de 1 (VRAI) dans la matrice VRAI/FAUX, ce qui inclura ces lignes dans le calcul.
3. Utiliser plusieurs critères:
Vous pouvez combiner plusieurs critères pour des calculs plus complexes.
Par exemple, pour calculer le chiffre d'affaires total du produit A dont le prix unitaire est supérieur à 10 € :
=SUMPRODUCT((A1:A5="A")*(C1:C5>10)*(B1:B5)*(C1:C5))
Alternatives à SUMPRODUCT
Bien que SUMPRODUCT soit une fonction très puissante, il existe d'autres fonctions Excel qui peuvent être utilisées pour obtenir des résultats similaires, parfois de manière plus intuitive.
SUMIFS (SOMME.SI.ENS)
La fonction SUMIFS est spécifiquement conçue pour effectuer des sommes conditionnelles. Elle permet de spécifier plusieurs critères pour filtrer les données à sommer.
Syntaxe:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
sum_range: La plage de cellules à sommer.criteria_range1: La plage de cellules où se trouve le premier critère.criteria1: Le premier critère.[criteria_range2, criteria2], ...: Les plages de cellules et critères supplémentaires (optionnels).
Exemple (équivalent à l'exemple 1 ci-dessus):
=SUMIFS(C1:C5, A1:A5, "A") (Ceci ne calcule que la somme des prix, pas le chiffre d'affaires. Pour le chiffre d'affaires, il faut une colonne supplémentaire qui multiplie quantité et prix, puis utiliser SUMIFS sur cette colonne.)
SUMIFS est souvent plus facile à lire et à comprendre que SUMPRODUCT avec des critères, surtout lorsque vous avez plusieurs conditions.
SUMIF (SOMME.SI)
La fonction SUMIF est similaire à SUMIFS, mais elle ne permet de spécifier qu'un seul critère. Elle est utile pour les calculs conditionnels simples.
Syntaxe:
=SUMIF(range, criteria, [sum_range])
range: La plage de cellules où se trouve le critère.criteria: Le critère.[sum_range]: La plage de cellules à sommer (optionnelle). Si omise, la plagerangeest sommée.
Tableaux croisés dynamiques
Les tableaux croisés dynamiques sont un outil puissant pour analyser et synthétiser des données. Ils permettent de regrouper et de sommer des données en fonction de différents critères, sans avoir besoin d'utiliser des formules complexes.
Pour créer un tableau croisé dynamique, sélectionnez vos données, puis cliquez sur l'onglet "Insertion" et choisissez "Tableau croisé dynamique". Vous pouvez ensuite faire glisser les champs de votre tableau vers les différentes zones du tableau croisé dynamique (lignes, colonnes, valeurs) pour obtenir les résultats souhaités.
Bonnes pratiques et erreurs à éviter
Bonnes pratiques
- Vérifiez les dimensions des matrices: Assurez-vous que toutes les matrices utilisées dans la fonction SUMPRODUCT ont les mêmes dimensions. Sinon, vous obtiendrez une erreur
#VALUE!. - Utilisez des noms de plages clairs: Pour faciliter la lecture et la compréhension de vos formules, utilisez des noms de plages descriptifs au lieu de références de cellules brutes.
- Commentez vos formules: Ajoutez des commentaires à vos formules pour expliquer leur fonctionnement et leur objectif. Cela vous aidera à vous souvenir de ce que vous avez fait plus tard, et facilitera la collaboration avec d'autres utilisateurs.
- Testez vos formules: Vérifiez toujours que vos formules renvoient les résultats attendus. Utilisez des exemples simples et vérifiez manuellement les résultats pour vous assurer que la formule fonctionne correctement.
Erreurs à éviter
- Oublier les parenthèses pour les critères: N'oubliez pas d'entourer chaque condition de parenthèses. Sinon, la formule risque de ne pas fonctionner correctement.
- Utiliser des types de données incompatibles: Assurez-vous que les types de données des matrices sont compatibles. Par exemple, vous ne pouvez pas multiplier du texte par des nombres.
- Utiliser des références de cellules incorrectes: Vérifiez attentivement les références de cellules pour vous assurer qu'elles pointent vers les bonnes plages de données.
- Complexifier inutilement la formule: Si vous pouvez obtenir le même résultat avec une fonction plus simple (comme SUMIFS), utilisez-la. Une formule simple est plus facile à lire, à comprendre et à déboguer.
Conclusion
La fonction Excel SUMPRODUCT (SOMMEPROD) est un outil puissant et polyvalent pour effectuer des calculs complexes. En comprenant sa syntaxe et son fonctionnement, vous pouvez l'utiliser pour calculer des sommes pondérées, des moyennes conditionnelles, et bien plus encore. Bien qu'il existe des alternatives comme SUMIFS et les tableaux croisés dynamiques, SUMPRODUCT reste un atout précieux dans la boîte à outils de tout utilisateur d'Excel. N'hésitez pas à expérimenter avec différents exemples et à explorer ses nombreuses possibilités pour optimiser vos feuilles de calcul et gagner en efficacité.