■■ Joolas est une petite entreprise de maquillage qui suit chaque transaction de vente dans une feuille de calcul Microsoft Excel . Souvent, il souhaite répondre à des questions telles que:

• • Combien de dollars de brillant à lèvres Jen a-t-elle vendu?
• • Quel était le nombre moyen d’unités de rouge à lèvres vendues chaque fois que Jen effectuait une vente dans la région Est?
• • Quel était le montant total en dollars de tout le maquillage vendu par Émilee ou dans la région de l’Est?
• • Combien de dollars de rouge à lèvres ont été vendus par Colleen ou Zaret dans la région Est?
• • Combien de transactions de rouge à lèvres n’étaient pas dans la région de l’Est?
• • Combien de dollars de rouge à lèvres Jen a-t-elle vendu en 2004?
• • Combien d’unités de maquillage ont été vendues pour un prix d’au moins 3,20 $?
• • Quel est le montant total en dollars que chaque vendeur a vendu de chaque produit de maquillage?

■■ Quelles astuces utiles puis-je utiliser pour configurer des plages de critères?

■■ J’ai une base de données qui répertorie, pour chaque transaction de vente, les revenus, la date de vente et le code d’identification du produit. Compte tenu de la date de vente et du code ID d’une transaction, existe-t-il un moyen simple d’extraire les revenus de la transaction?

Comme vous l’avez vu à l’article 43, «Utilisation de tableaux croisés dynamiques et de segments pour décrire les données», les tableaux croisés dynamiques Excel sont un excellent outil pour résumer les données. Souvent, cependant, un tableau croisé dynamique vous donne beaucoup plus d’informations que nécessaire. Les fonctions statistiques de la base de données permettent de répondre facilement à toute question de rapport sans créer de tableau croisé dynamique.

Vous connaissez déjà des fonctions telles que SOMME, MOYENNE, NB, MAX et MIN. En préfixant un D (qui signifie base de données) sur ces fonctions et sur d’autres, vous créez des fonctions statistiques de base de données. Cependant, que fait la fonction BDSOMME, par exemple, que la fonction SOMME ne peut pas? Alors que la fonction SOMME additionne chaque cellule d’une plage de cellules, la fonction BDSOMME vous permet de spécifier (en utilisant des critères) un sous-ensemble de lignes à additionner dans une plage de cellules. Par exemple, supposons que vous ayez une base de données de vente pour une petite entreprise de maquillage qui contient les informations suivantes sur chaque transaction de vente:

■■ Nom du vendeur

■■ Date de transaction

■■ Produit vendu

■■ Unités vendues

■■ Dollars de revenus générés par transaction

■■ Région du pays où la transaction a eu lieu

Vous pouvez trouver ces données dans le fichier , qui est illustré à la figure 1.

FIGURE 1 Utilisez ces données pour décrire comment utiliser les fonctions statistiques de la base de données.

En utilisant la fonction BDSOMME avec des critères appropriés, vous pouvez, par exemple, additionner les revenus générés uniquement par les transactions impliquant des ventes de brillant à lèvres dans l’Est en 2004. Essentiellement, les critères que vous définissez marquent les lignes que vous souhaitez inclure dans le total somme. Au sein de ces lignes, la fonction BDSOMME agit comme la fonction SOMME ordinaire.

La syntaxe de la fonction BDSOMME est BDSOMME (base de données, champ, critères):

■■ La base de données est la plage de cellules qui constitue la base de données. La première ligne de la liste contient des étiquettes pour chaque colonne.

■■ Le champ est la colonne contenant les valeurs que vous souhaitez ajouter à la fonction. Vous pouvez définir en plaçant le libellé de la colonne entre guillemets. (Par exemple, vous pouvez désigner la colonne Dollars en entrant «Dollars».) Le champ peut également être spécifié en utilisant la position de la colonne dans la base de données, mesurée de gauche à droite. Par exemple, la base de données de transactions d’appoint utiliserait les colonnes H à M. (la colonne Transactions n’a pas été incluse dans la base de données.) Vous pouvez spécifier la colonne H comme champ 1 et la colonne M comme champ 6.

■■ Un critère fait référence à une plage de cellules qui spécifie les lignes sur lesquelles la fonction doit fonctionner.

Combien de dollars de gloss à lèvres Jen a-t-elle vendus?

Dans cet exemple, vous souhaitez appliquer DSUM à la colonne 5 de la base de données. La colonne 5 contient le volume en dollars pour chaque transaction. (La base de données est nommée Données, qui se compose de la plage H4: M1895.) La plage de critères dans O4: P5 marque toutes les lignes de la base de données dans lesquelles Nom est égal à Jen et Produit est égal au brillant à lèvres. Ainsi, la saisie de la formule BDSOMME (données, 5, O4: P5) dans la cellule N5 (voir figure 2) calcule la quantité totale de brillant à lèvres vendue par Jen. Vous auriez également pu saisir la formule comme DSUM (données, ”Dollars”, O4: P5). Jen a vendu pour 5 461,61 $ de brillant à lèvres. Dans la cellule N6, vous pouvez trouver la même réponse en utilisant la fonction SOMME.SI.ENS  (voir Chapitre 20, «Les fonctions SOMME.SI, MOYENNE.SI,SOMME.SI.ENS et MOYENNE.SI.ENS») avec la formule = SOMME.SI.ENS (Dollars, Nom, «Jen», Produit »,« gloss »).

FIGURE  2 Voici les fonctions statistiques de la base de données à l’œuvre.

Quel était le nombre moyen d’unités de rouge à lèvres vendues chaque fois que Jen faisait une vente dans la région Est?

Vous pouvez calculer ce nombre en entrant la formule MOYENNE (données, 4, O7: Q8) dans la cellule N8. Utilisation de 4 comme la valeur pour le champ spécifie la colonne Unités, et la plage de critères O7: Q8 marque toutes les lignes de la base de données dans lesquelles Nom est égal à Jen, Produit est égal au rouge à lèvres et Emplacement est égal à Est. L’utilisation de BD MOYENNE garantit que vous faites la moyenne des unités vendues pour les lignes marquées. Vous pouvez voir qu’en moyenne, Jen a vendu 42,25 unités de rouge à lèvres dans les transactions dans la région Est. Dans la cellule N9, vous pouvez calculer la même réponse en utilisant la formule = MOYENNE (Unités, Nom, “Jen”, Produit, “rouge à lèvres”, Emplacement, “est”).

Quel était le montant total en dollars de tout le maquillage vendu par Emilee ou dans la région de l’Est?

Dans la cellule N11, vous pouvez calculer le total des dollars (76 156,48 $) de ventes par Emilee ou dans l’Est en utilisant la formule BDSOMME(données, 5, O10: P12). Les critères en O10: les ventes de drapeaux P12 en Orient ou par Emilee, car les critères des différentes lignes sont traités comme une opération OU. Les grands programmeurs de Microsoft ont veillé à ce que cette formule ne double pas les ventes d’Emilee en Orient. Ici, vous ne pouvez pas utiliser SOMME.SI.ENS pour trouver facilement la réponse.

Combien de dollars de rouge à lèvres ont été vendus par Colleen ou Zaret dans la région Est?

La formule BDSOMME (données, 5, O13: Q15) dans la cellule N14 calcule le revenu total généré par le rouge à lèvres grâce aux ventes de Colleen et Zaret (1 073,20 $) dans l’Est. Notez que O14: Q14 spécifie des critères qui sélectionner les ventes de rouge à lèvres de Colleen en Orient et O15: Q15 spécifie les critères de sélection des ventes de rouge à lèvres de Zaret en Orient. N’oubliez pas que les critères des différentes lignes sont joints par OR. Dans la cellule N15, la réponse à cette question est calculée avec la formule suivante:

=SOMME.SI.ENS (Dollars, Name, “Colleen”, Product, “lipstick”, Location, “east”)+ SOMME.SI.ENS (Dollars, Nom, “Zaret”, Produit, “rouge à lèvres”, Emplacement, “est”)

Combien de transactions de rouge à lèvres n’étaient pas dans la région de l’Est?

Dans la cellule N17, vous pouvez calculer le nombre total de transactions de rouge à lèvres (164) en dehors de la région Est avec la formule BDND (données, 4, O16: P17). Vous utilisez BDNB dans ce problème parce que vous voulez pour spécifier les critères selon lesquels la fonction comptera le nombre de lignes impliquant des ventes de rouge à lèvres et des régions autres que dans l’Est. Excel traite l’expression <> Est dans la plage de critères comme «non Est». Pour ce problème, l’utilisation de SUMIFS nécessiterait que vous ayez une fonction SOMME.SI.ENS pour chaque région.

Étant donné que la fonction NB compte les nombres, vous devez vous référer à une colonne contenant des valeurs numériques. La colonne 4, la colonne Unités, contient des nombres, de sorte que la colonne est désignée dans la formule. La formule BDNB (données, 3, O16: P17) retournerait 0 car il n’y a pas de nombres dans la troisième colonne de la base de données (qui est la colonne J dans la feuille de calcul). Bien sûr, la formule BDNBVAL (données, 3, O16: P17) retournerait la bonne réponse car BDNBVAL compte le texte ainsi que les nombres.

Combien de dollars de rouge à lèvres Jen a-t-elle vendu en 2004?

L’astuce consiste à savoir comment signaler uniquement les ventes qui ont eu lieu en 2004. En incluant dans une ligne de votre plage de critères une référence au champ Date, en utilisant les expressions> = 1/1/2004 et <1/1/2005, capture uniquement les ventes 2004. Ainsi, la saisie de la formule DSUM (données, 5, O18: R19) dans la cellule N19 calcule le total des ventes de rouge à lèvres par Jen (1 690,79 $) après le 1er janvier 2004 et avant le 1er janvier 2005. Dans la cellule N20, la réponse à ce problème est calculé avec le = SOMME.SI.ENS (Dollars, Date, ”> = 1/1/2004”, Date, ”<= 31/12/2

004 ”, Produit,” rouge à lèvres ”, Nom,” Jen ”) formule. 

Combien d’unités de maquillage ont été vendues pour un prix d’au moins 3,20 $?

Cet exemple implique des critères calculés. Fondamentalement, les critères calculés marquent les lignes de la base de données sur la base pour savoir si une condition calculée est vraie ou fausse pour cette ligne. Pour cette question, vous voulez marquez chaque ligne contenant des dollars / unités> = 3,20 $. Lors de la configuration d’un critère calculé (voir figure 3), l’étiquette de la première ligne au-dessus des critères calculés ne doit pas être une étiquette de colonne. Par exemple, vous ne pouvez pas utiliser le nom, le produit ou une autre étiquette de la ligne 4 de cette feuille de calcul. Le critère calculé est configuré pour être une formule qui renvoie Vraie en fonction de la première ligne d’informations de la base de données. Ainsi, pour spécifier des lignes dans lesquelles le prix moyen est supérieur ou égal à 3,20 $, vous devez entrer = (L5 / K5)> = 3,2 dans votre plage de critères sous un en-tête qui n’est pas une étiquette de colonne. Si la première ligne de données ne remplit pas cette condition, vous verrez FAUX dans la feuille de calcul, mais Excel marquera toujours toutes les lignes dont le prix unitaire est supérieur ou égal à 3,20 USD. La saisie de la formule BDSOMME (données, 4, O21: O22) dans N22 calcule le nombre total d’unités de maquillage vendues (1127) dans les commandes pour lesquelles le prix unitaire était supérieur ou égal à 3,20 $. Notez que la cellule O22 contient le = (L5 /K5)>=3.2 formule.

FIGURE 3 Cette figure montre un critère calculé.

Quel est le montant total en dollars que chaque vendeur a vendu pour chaque produit de maquillage?

Pour ce problème, vous pouvez utiliser une fonction BDSOMME dont la plage de critères est basée à la fois sur les colonnes Nom et Produit. À l’aide d’un tableau de données, vous pouvez facilement parcourir toutes les combinaisons possibles de nom et de produit dans la plage de critères et calculer le revenu total pour chaque combinaison de nom et de produit.

Commencez par entrer n’importe quel nom dans la cellule X26 et tout produit dans la cellule Y26. Entrer le Formule BDSOMME (données, 5, X25: Y26) dans la cellule Q25, qui calcule le chiffre d’affaires total pour (dans ce cas) Betsy et eye-liner. Saisissez le nom de chaque vendeur dans la plage de cellules Q26: Q33 et chaque produit dans la plage de cellules R25: V25. Sélectionnez maintenant la plage du tableau de données (Q25: V33). Sous l’onglet Données, dans le groupe Outils de données, cliquez sur What-If Analysis, puis sur Table de données. Choisissez la cellule X26 comme cellule d’entrée de colonne et Y26 comme cellule d’entrée de ligne. Vous obtenez ensuite les résultats présentés dans la figure 48-4. Chaque entrée du tableau de données calcule les revenus générés pour une combinaison de noms et de produits différente car le tableau de données entraîne le placement des noms dans la cellule X26 et les produits dans la cellule Y26. Par exemple, Ashley a vendu pour 3 245,45 $ de rouge à lèvres.

FIGURE 4 Combinez des tableaux de données avec une fonction BDSOMME.

Cet exemple montre comment la combinaison de tableaux de données avec des fonctions statistiques de base de données peut générer rapidement de nombreuses statistiques intéressantes. Notez que vous auriez également pu résoudre ce problème en copiant le

= Formule SOMME.SI.ENS (Dollars, Nom, $ Q37, Produit, R $ 36) de la cellule R37 à R37: V45.

Quelles astuces utiles puis-je utiliser pour configurer des plages de critères?

Voici quelques exemples de petites astuces qui pourraient vous aider à définir une plage de critères appropriée. Supposons que l’étiquette de colonne dans la première ligne de la plage de critères se réfère à une colonne contenant du texte (par exemple, la colonne H).

■■ * Allie * marquera les enregistrements contenant la chaîne de texte Allie dans la colonne H.

■■ A? X marquera un enregistrement si l’entrée de la colonne H de l’enregistrement commence par A et contient X comme troisième

personnage. (Le deuxième personnage peut être n’importe quoi!)

■■ <> * B * marquera un enregistrement si l’entrée de la colonne H ne contient aucun Bs.

Si une colonne (par exemple, la colonne I) contient des valeurs numériques,

■■> 100 marquera un enregistrement si la colonne I contient une valeur supérieure à 100.

■■ <> 100 marquera un enregistrement si la colonne I contient une valeur différente de 100.

■■> = 1000 signalera un enregistrement si la colonne I contient une valeur supérieure ou égale à 1000.

J’ai une base de données qui répertorie, pour chaque transaction de vente, les revenus, la date de vente et le code d’identification du produit. Compte tenu de la date de vente et du code d’identification d’une transaction, existe-t-il un moyen simple de saisir les revenus de la transaction?

Le fichier  (voir Figure 5) contient une base de données qui répertorie les revenus, les dates et les codes d’identification des produits pour une série de transactions de vente. Si vous connaissez la date et le code d’identification du produit pour une transaction, comment pouvez-vous trouver les revenus de la transaction? Avec la fonction BDLIRE, c’est simple. La syntaxe

de la fonction BDLIRE est BDLIRE (base de données, champ #, critères). Étant donné une plage de cellules pour la base de données et une valeur pour le champ # dans la base de données (en comptant les colonnes de gauche à droite sur toute la plage), la fonction BDLIRE renvoie l’entrée dans le champ de colonne # de l’enregistrement de base de données satisfaisant aux critères. Si aucun enregistrement ne répond aux critères, la fonction BDLIRE renvoie le message d’erreur #VALEUR. Si plusieurs enregistrements satisfont aux critères, la fonction BDLIRE renvoie le #NOMBRE! Message d’erreur.

Supposons que vous souhaitiez connaître les revenus d’une transaction impliquant le code produit 62426 qui a eu lieu le 1/9/2006. En supposant qu’il n’y a qu’une seule transaction impliquant ce produit à la date donnée, la formule BDLIRE (B7: D28,1, G5: H6) (entrée dans la cellule G9) rapporte le revenu de la transaction de 169 $. Notez que 1 a été utilisé pour l’argument champ # car Revenue est répertorié dans la première colonne de la base de données (qui est contenue dans la plage de cellules B7: D28). La gamme de critères G5: H6 garantit que vous trouverez une transaction impliquant le produit 62426 au 1/9/2006.

FIGURE 5 Cette figure montre l’utilisation de la fonction BDLIRE.