■ Je suis directeur des ventes pour une entreprise de maquillage et j’ai résumé les informations suivantes pour chaque transaction de vente: vendeur, date de vente, unités vendues (ou retournées) et prix total reçu (ou payé pour les retours). Comment puis-je répondre aux questions suivantes?

• • Quel est le montant total en dollars des marchandises vendues par chaque vendeur?
• • Combien d’unités ont été rendues?
• • Quel est le montant total des ventes en 2005 ou après?
• • Combien d’unités de brillant à lèvres ont été vendues? Quel est le chiffre d’affaires généré par les ventes de gloss?
• • Quel est le montant total des ventes par une personne autre qu’un vendeur spécifique?
• • Quel est le nombre moyen d’unités vendues dans chaque transaction par un vendeur spécifique?
• • Quel est le montant total en dollars de rouge à lèvres vendu par un vendeur en particulier?
• • Quelle est la quantité moyenne (en unités) de rouge à lèvres dans chaque vente par un vendeur spécifique?
• • Parmi les transactions impliquant au moins 50 unités, quelle est la quantité moyenne de rouge à lèvres dans chaque vente par un vendeur spécifique?
• • Parmi les transactions de plus de 100 $, quel est le montant total en dollars de rouge à lèvres vendu par un vendeur en particulier? Quel est le montant total des transactions de moins de 100 $?

Si vous souhaitez additionner toutes les entrées dans une colonne (ou ligne) qui correspondent aux critères qui dépendent d’une autre colonne (ou ligne), la fonction SOMME.SI fait le travail. La syntaxe de la fonction SOMME.SI est SOMME.SI (plage, critère, [plage_somme]):

■ Plage est la plage de cellules que vous souhaitez évaluer avec un critère.

■ Le critère est un nombre, une date ou une expression qui détermine si une cellule donnée dans la plage de somme est ajoutée.

■ SOMME ligne est la plage de cellules qui sont ajoutées. Si la plage de somme est omise, elle est supposée être la même que la plage.

Les règles des critères que vous pouvez utiliser avec la fonction SUMIF sont identiques aux règles utilisées pour la fonction NB.SI. Pour plus d’informations sur la fonction NB.SI, reportez-vous à l’article 19, «Les fonctions NB.SI, NB.SI.ENS, NB, NBVAL et NB.VIDE».

La fonction MOYENNE.SI a la syntaxe MOYENNE.SI (plage, critère, [plage_moyenne]). MOYENNE.SI fait la moyenne de la plage de cellules répondant à un critère.

Microsoft Excel 2013 comprend trois fonctions (introduites dans Excel ) que vous pouvez utiliser pour marquer des lignes qui impliquent plusieurs critères: COUNTIFS (traité au chapitre 19), SUMIFS et AVERAGEIFS. D’autres fonctions que vous pouvez utiliser pour effectuer des calculs impliquant plusieurs critères sont décrites au chapitre 48, «Récapitulation des données avec les fonctions statistiques de la base de données». Les fonctions de tableau (voir , “Fonctions et formules de tableau”) peuvent également être utilisées pour gérer des calculs impliquant plusieurs critères.

La syntaxe de SOMME.SI.ENS est SOMME.SI.ENS (plage_somme, plage1, critère1, plage2, critère2, …, plageN, critèreN). SUMIFS additionne chaque entrée de la sum_range pour laquelle critère1 (basé sur la plage1) et critère2 (basé sur la plage2),. . . , le critère N (basé sur la plage N) sont tous satisfaits. De manière similaire, la fonction MOYENNE.SI.ENS a la syntaxe MOYENNE.SI.ENS (gamme_somme, plage1, critère1, plage2, – critère2, …, plageN, critèreN). MOYENNE.SI.ENS fait la moyenne de chaque entrée de la plage de valeurs pour laquelle critère1 (basé sur la plage1) et critère2 (basé sur la plage2),. . . , le critère N (basé sur la plage N) sont tous satisfaits.

Réponses aux questions de cet article

Cette section fournit les réponses aux questions répertoriées au début de cet article.

Quel est le montant total en dollars des marchandises vendues par chaque vendeur?

Le problème rencontré dans ce chapitre se trouve dans le fichier . La figure  montre un sous-ensemble des données.

FIGURE 1 :Voici les données que vous utiliserez pour les exemples SOMME.SI.

Comme d’habitude, commencez par étiqueter les données des colonnes G à L avec les noms correspondants dans les cellules G4: L4. Par exemple, le nom de la gamme de produits correspond à la gamme J5: J1904. Pour calculer le montant total vendu par chaque vendeur (voir figure 2), copiez la formule SOMME.SI (Nom, A5, Dollars) de la cellule B5 à B6: B13. Cette formule additionne chaque entrée de la colonne Dollars qui contient le nom Emilee dans la colonne Nom et montre qu’Emilee a vendu pour 25 258,87 $ de maquillage. Bien sûr, le = SOMME.SI (Nom  , “Emilee”, Dollars) formule donnerait le même résultat.

FIGURE 2: Voici les résultats des calculs SOMME.SI.

Combien d’unités ont été rendues?

Dans la cellule B16, la formule SOMME.SI (Unités, “<0”, Unités) totalise chaque nombre inférieur à 0 dans la colonne Unités (colonne K). Le résultat est -922. L’insertion d’un signe moins devant la formule SOMME.SI indique que 922 unités ont été retournées. (Rappelez-vous que lorsque l’argument de plage de somme est omis d’une fonction SOMME.SI, Excel suppose que la plage de somme est égale à la plage. Par conséquent, la formule –SOMME.SI (Unités, “<0”) donnerait également 922.)

Quel est le montant total des ventes en 2005 ou après 2005?

Dans la cellule B17, la formule SOMME.SI (Date, “> = 1/1/2005”, Dollars) totalise chaque entrée de la colonne Dollar (colonne L) qui contient une date le 1er janvier 2005 ou après dans la colonne Date. La formule montre que 157 854,32 $ de maquillage ont été vendus en 2005 ou plus tard.

Combien d’unités de brillant à lèvres ont été vendues? Quel est le chiffre d’affaires généré par les ventes de gloss?

Dans la cellule B18, la formule SOMME.SI (Produit, “brillant à lèvres”, Unités) totalise chaque cellule de la colonne Unités qui contient le texte “brillant à lèvres” dans la colonne Produit (colonne J). Vous pouvez voir que 16 333 unités de brillant à lèvres ont été vendues. Il s’agit du montant des ventes nettes; les transactions dans lesquelles des unités de brillant à lèvres ont été retournées sont comptabilisées comme des ventes négatives.

De façon similaire, dans la cellule B19, la formule SOMME.SI (Produit, “brillant à lèvres”, Dollars) vous indique qu’un montant net de 49 834,64 $ de brillant à lèvres a été vendu. Ce calcul considère les remboursements associés aux retours comme des revenus négatifs.

Quel est le montant total des ventes de quelqu’un d’autre que Jen?

Dans la cellule B20, la formule SOMME.SI (Nom, “<> Jen”, Dollars) additionne le montant en dollars de toutes les transactions qui n’ont pas Jen dans la colonne Nom. Vous constatez que des vendeurs autres que Jen ont vendu pour 211 786,51 $ de maquillage.

Quel est le nombre moyen d’unités vendues dans chaque transaction par un vendeur spécifique?

Il s’agit d’un travail pour la fonction MOYENNE.SI. La saisie de la formule = MOYENNE (Nom, “Jen”, Unités) dans la cellule B26 fait la moyenne de chaque entrée dans la colonne Unités qui contient Jen dans la colonne Nom. La taille moyenne des transactions de Jen était de 43,548 unités. Vous pouvez vérifier cela dans la cellule C26 avec la formule = SOMME.SI (Nom, “Jen”, Unités) / NB.SI (Nom, “Jen”).

Quel est le montant total en dollars de rouge à lèvres vendu par Jen?

Ce calcul implique deux critères (Nom = “Jen” et Produit = “rouge à lèvres”). Par conséquent, vous devez calculer la quantité que vous recherchez dans la cellule B21 avec la formule = SUMIFS (Dollars, Name, “Jen”, Product, “lipstick”). Le montant total en dollars de toutes les transactions dans lesquelles Jen a vendu du rouge à lèvres est de 3 953 $.

Quelle est la quantité moyenne (en unités) de rouge à lèvres dans chaque vente par Zaret?

Ce calcul nécessite la fonction MOYENNE.SI.ENS. Calculez la quantité dans la cellule B22 avec la formule = MOYENNE (Unités, Nom, “Zaret”, Produit, “rouge à lèvres”). Pour les transactions de vente dans lesquelles Zaret a vendu du rouge à lèvres, le nombre moyen d’unités vendues est de 33.

Parmi les transactions impliquant au moins 50 unités, quelle est la quantité moyenne de rouge à lèvres dans chaque vente par Zaret?

Ici encore, utilisez MOYENNE.SI.ENS mais ajoutez un critère pour vous assurer que le nombre d’unités vendues dans chaque transaction était d’au moins 50. Dans la cellule B23, entrez le = MOYENNE.SI (Unités, Nom, “Zaret”, Produit, “rouge à lèvres”, Unités, “> = 50”) formule. Dans toutes les transactions dans lesquelles Zaret a vendu au moins 50 unités de rouge à lèvres, la taille moyenne des transactions est de 68 unités.

Parmi les transactions de plus de 100 $, quel est le montant total en dollars de rouge à lèvres vendu par Jen? Quel est le montant total des transactions de moins de 100 $?

Étant donné que les critères sont Nom = Jen, Produit = rouge à lèvres et une déclaration sur la taille en dollars de chaque commande, vous devez utiliser la fonction SUMIFS. Dans la cellule B24, calculez le montant total des transactions dans lesquelles Jen a vendu du rouge à lèvres et le montant en dollars était d’au moins 100 $ avec le = SOMME.SI.ENS (Dollars, – Name, “Jen”, Product, “lipstick”, Dollars “> = 100 “) formule. Jen a vendu pour 3 583 $ de rouge à lèvres dans de telles transactions. Dans les transactions de rouge à lèvres impliquant moins de 100 $, vous pouvez voir dans la cellule B25 que la réponse est 370 $. (La formule est = SOMME.SI.ENS (Dollars, Name, “Jen”, Product, “lipstick”, Dollars, “<100”.) Notez que 370 $ + 3 583 $ est égal au revenu total généré par Jen des ventes de rouge à lèvres (calculé dans la cellule B21).