Formule générique
=SUMPRODUCT(costs,--(range="x"))
Sommaire
Pour calculer la tarification de l'offre groupée de produits à l'aide d'un simple «x» pour inclure ou exclure un produit, vous pouvez utiliser une formule basée sur la fonction SUMPRODUCT. Dans l'exemple illustré, la formule en D11 est:
=SUMPRODUCT($C$5:$C$9,--(D5:D9="x"))
Explication
La fonction SUMPRODUCT multiplie les plages ou les tableaux ensemble et renvoie la somme des produits. Cela semble ennuyeux, mais SUMPRODUCT est une fonction élégante et polyvalente, que cet exemple illustre bien.
Dans cet exemple, SUMPRODUCT est configuré avec deux tableaux. Le premier tableau est la gamme qui contient les prix des produits:
$C$5:$C$9
Notez que la référence est absolue pour éviter les modifications car la formule est copiée vers la droite. Cette plage correspond au tableau suivant:
(99;69;129;119;49)
Le deuxième tableau est généré avec cette expression:
--(D5:D9="x")
Le résultat de D5: D9 = "x" est un tableau de valeurs TRUE FALSE comme ceci:
(TRUE;TRUE;FALSE;FALSE;FALSE)
Le double négatif (-) convertit ces valeurs TRUE FALSE en 1 et 0:
(1;1;0;0;0)
Donc, à l'intérieur de SUMPRODUCT, nous avons:
=SUMPRODUCT((99;69;129;119;49),(1;1;0;0;0))
La fonction SUMPRODUCT multiplie ensuite les éléments correspondants dans chaque tableau ensemble:
=SUMPRODUCT((99;69;0;0;0))
et renvoie la somme des produits, 168 dans ce cas.
En effet, le deuxième tableau agit comme un filtre pour les valeurs du premier tableau. Les zéros dans array2 annulent les éléments dans array1 et les 1s dans array2 permettent aux valeurs de array1 de passer dans le résultat final.
Avec un seul tableau
SUMPRODUCT est configuré pour accepter plusieurs tableaux, mais vous pouvez simplifier un peu cette formule en fournissant un seul tableau au début:
=SUMPRODUCT($C$5:$C$9*(D5:D9="x"))
L'opération mathématique (multiplication) contraint automatiquement les valeurs TRUE FALSE de la deuxième expression à des uns et des zéros, sans avoir besoin d'un double négatif.
