Comment utiliser la fonction Excel SUMPRODUCT dans les modèles de données - mannequins

La fonction SUMPRODUCT est actuellement répertoriée dans la catégorie mathématique et trigonométrie des fonctions Excel. Parce que le but principal de SUMPRODUCT est de calculer le produit total, la plupart des gens ne savent pas que vous pouvez réellement l'utiliser pour rechercher des valeurs. En fait, vous pouvez utiliser cette fonction polyvalente assez efficacement dans la plupart des modèles de données.

Principes de base de SUMPRODUCT

La fonction SUMPRODUCT est conçue pour multiplier les valeurs de deux ou plusieurs plages de données, puis ajouter les résultats ensemble pour renvoyer la somme des produits. Jetez un oeil à la figure suivante pour voir un scénario typique dans lequel le SUMPRODUCT est utile.

SUMPRODUCT, obtenir le total des ventes implique de multiplier le pric "width =" 458 ">

Sans le SUMPRODUCT, obtenir le total des ventes implique de multiplier le prix par les unités et ensuite de sommer les résultats. Vous voyez une analyse commune dans laquelle vous avez besoin des ventes totales pour les années 2011 et 2012. Comme vous pouvez le voir, pour obtenir le total des ventes pour chaque année, vous devez d'abord multiplier le prix par le nombre d'unités pour obtenir le total pour chaque Région.Vous devez ensuite additionner ces résultats pour obtenir le total des ventes pour chaque année

Avec la fonction SUMPRODUCT, vous pouvez effectuer l'analyse en deux étapes avec une seule formule. La figure suivante montre la même analyse avec les formules SUMPRODUCT: Plutôt que d'utiliser 11 formules, vous pouvez effectuer la même analyse avec seulement 3!

La fonction SUMPRODUCT vous permet d'effectuer la même analyse avec juste "width =" 535 "> >

la fonction SUMPRODUCT vous permet d'effectuer la même analyse avec seulement 3 formules au lieu de 11.

La syntaxe de la fonction SUMPRODUCT est assez simple:

SUMPRODUCT (

Array1

, Array2 , …) Array :

Array représente une plage de données. Vous pouvez utiliser entre 2 et 255 tableaux dans une formule SUMPRODUCT. Les tableaux sont multipliés ensemble puis ajoutés. La seule règle que vous devez vous rappeler est que tous les tableaux doivent avoir le même nombre de valeurs. C'est-à-dire que vous ne pouvez pas utiliser SUMPRODUCT si la plage X a 10 valeurs et la plage Y a 11 valeurs. Sinon, vous obtenez le #VALUE! Erreur.

Une variante de la fonction SUMPRODUCT

La fonction SUMPRODUCT est intéressante car elle permet de filtrer les valeurs. Jetez un oeil à la figure suivante:

La fonction SUMPRODUCT peut être utilisée pour filtrer les données en fonction de critères. "Width =" 535 ">

La fonction SUMPRODUCT peut être utilisée pour filtrer les données en fonction de critères.

La formule dans la cellule E12 tire la somme des unités totales pour la région Nord uniquement. Pendant ce temps, la cellule E13 tire les unités enregistrées pour la région Nord en 2011.

Pour comprendre comment cela fonctionne, jetez un oeil à la formule dans la cellule E12. Cette formule lit SUMPRODUCT ((C3: C10 = "North") * (E3: E10)).

Dans Excel, TRUE évalue à 1 et FALSE évalue à 0. Chaque valeur de la colonne C égale à North est évaluée à TRUE ou à 1. Lorsque la valeur n'est pas North, elle est évaluée à FALSE ou à 0. La partie de la formule reads (C3: C10 = "North") énumère chaque valeur dans la plage C3: C10, en affectant 1 ou 0 à chaque valeur. Puis, en interne, la formule SUMPRODUCT se traduit par

(1 * E3) + (0 * E4) + (0 * E5) + (0 * E6) + (1 * E7) + (0 * E8) + (0 * E9) + (0 * E10).

Cela vous donne la réponse de 1628 parce que

(1 * 751) + (0 * 483) + (0 * 789) + (0 * 932) + (1 * 877) + (0 * 162) + (0 * 258) + (0 * 517)

= 1628.

Appliquer les formules SUMPRODUCT dans un modèle de données

Comme toujours dans Excel, vous n'avez pas besoin de coder en dur les critères dans vos formules. Plutôt que d'utiliser explicitement "North" dans la formule SUMPRODUCT, vous pouvez référencer une cellule qui contient la valeur du filtre. Vous pouvez imaginer que la cellule A3 contient le mot Nord, auquel cas vous pouvez utiliser (C3: C10 = A3) au lieu de (C3: C10 = "Nord"). De cette façon, vous pouvez modifier dynamiquement vos critères de filtre, et votre formule continue.

La figure suivante montre comment utiliser ce concept pour extraire des données dans une table de transfert basée sur plusieurs critères. Notez que chacune des formules SUMPRODUCT présentées ici référence les cellules B3 et C3 pour filtrer sur le compte et la ligne de produits. Encore une fois, vous pouvez ajouter des listes déroulantes de validation des données aux cellules B3 et C3, ce qui vous permet de modifier facilement les critères.

La fonction SUMPRODUCT peut être utilisée pour extraire des nombres résumés de d "width =" 535 ">

La fonction SUMPRODUCT peut être utilisée pour extraire des nombres résumés de la couche de données dans des tables de transfert.