Les fonctions NB.SI, NB.SI.ENS , NB, NBVAL et NB.VIDE dans Microsoft Excel
■ Supposons que j’ai une liste de chansons qui ont été jouées à la radio. Pour chaque chanson, je connais le chanteur, la date à laquelle la chanson a été jouée et la durée de la chanson. Comment puis-je répondre à de telles questions sur les chansons de la liste:
- • Combien ont été chantés par chaque chanteur?
- • Combien n’ont pas été chantés par ma chanteuse préférée?
- • Combien duraient au moins quatre minutes?
- • Combien étaient plus longs que la durée moyenne de toutes les chansons de la liste?
- • Combien ont été chantés par des chanteurs dont le nom de famille commence par S?
- • Combien ont été chantés par des chanteurs dont le nom de famille contient exactement six lettres?
- • Combien ont été joués après le 15 juin 2005?
- • Combien ont été joués avant 2009?
- • Combien duraient exactement quatre minutes?
- • Combien ont été chantés par ma chanteuse préférée et duraient exactement quatre minutes?
- • Combien ont été chantés par ma chanteuse préférée et duraient de trois à quatre minutes?
■ Dans un contexte plus général, comment puis-je effectuer des opérations telles que les suivantes:
- • Comptez le nombre de cellules dans une plage contenant des nombres.
- • Comptez le nombre de cellules vides dans une plage.
- • Comptez le nombre de cellules non vides dans une plage.
Vous devez souvent compter le nombre de cellules dans une plage qui répondent à un critère donné. Par exemple, si une feuille de calcul contient des informations sur les ventes de maquillage, vous souhaiterez peut-être compter le nombre de transactions de vente effectuées par la vendeuse nommée Jennifer ou le nombre de transactions de vente survenues après le 10 juin. La fonction COUNTIF vous permet de compter le nombre de cellules, dans une plage répondant à des critères définis en fonction d’une ligne ou d’une colonne de la feuille de calcul.
La syntaxe de la fonction NB.SI est NB.SI (plage, critère):
■ Plage est la plage de cellules dans laquelle vous souhaitez compter les cellules répondant à un critère donné.
■ Le critère est un nombre, une date ou une expression qui détermine s’il faut compter une cellule donnée dans la plage.
La syntaxe de NB.SI.ENS est NB.SI.ENS(plage1, critère1, plage2, critère2, …, plage_n, critère_n).NB.SI.ENS permet aux critères d’impliquer plus d’une colonne ou plusieurs conditions dans une colonne. D’autres fonctions qui permettent des critères multiples sont discutées dans le chapitre 20, «Les fonctions SOMME.SI, MOYENNE.SI, SOMME.SI.ENS et MOYENNE.SI.ENS», et les chapitres suivants, «Résumé des données avec les fonctions statistiques de la base de données».
La clé pour utiliser avec succès la fonction NB.SI (et des fonctions similaires) est de comprendre la grande variété de critères acceptés par Microsoft Excel. Les types de critères que vous pouvez utiliser sont mieux expliqués à l’aide d’exemples. Outre des exemples de la fonction NB.SI, ce chapitre fournit des exemples des fonctions NB, NBVAL et NB.VIDE:
■ La fonction NB compte le nombre de cellules dans une plage contenant des nombres.
■ La fonction NBVAL compte le nombre de cellules non vides dans une plage.
■ La fonction NB.VIDE compte le nombre de cellules vides dans une plage.
Pour illustrer l’utilisation de ces fonctions, considérons une base de données (fichier) qui donne les informations suivantes pour chaque chanson diffusée sur la station de radio WKRP:
■ Le chanteur
■ La date de lecture de la chanson
■ La longueur de la chanson
La figure montre un sous-ensemble des données.
FIGURE 1 : Il s’agit de la base de données de morceaux utilisée pour les exemples NB.SI.
Réponses aux questions de cet article
Cette section fournit les réponses aux questions répertoriées au début de cet article. Combien de chansons ont été chantées par chaque chanteur?
Pour commencer, sélectionnez la première ligne de la base de données, la gamme D6: G6. Sélectionnez toute la base de données en appuyant sur Ctrl + Maj + flèche vers le bas. Dans le groupe Noms définis de l’onglet Formules, cliquez sur Créer à partir de la sélection, puis choisissez Ligne supérieure. Il s’agit du morceau Numb de la gamme D7: D957, du chanteur de la gamme E7: E957, de la date de la gamme F7: F957 et des minutes de la gamme G7: G957. Pour déterminer combien de chansons ont été chantées par chaque chanteur, copiez la formule NB.SI (Chanteur, B5) de C5 à C6: C12. Dans la cellule C5, cette formule affiche désormais le nombre de cellules de la plage Singer qui correspondent à la valeur de B5 (Eminem). La base de données contient 114 chansons chantées par Eminem. De même, Cher a chanté 112 chansons, etc., comme vous pouvez le voir sur la figure ci-dessus. Vous auriez également pu trouver le nombre de chansons chantées par Eminem avec la formule NB.SI (Chanteur, “Eminem”). Notez que vous devez mettre du texte tel qu’Eminem entre guillemets (“”) et que les critères ne sont pas sensibles à la casse.
FIGURE 2 : Utilisez NB.SI pour déterminer le nombre de chansons chantées par chaque chanteur.
Combien de chansons n’ont pas été chantées par Eminem?
Pour résoudre ce problème, vous devez savoir qu’Excel interprète la combinaison de caractères <> comme “différente de”. La formule NB.SI (Singer, “<> Eminem”), entrée dans la cellule C15, vous indique que 837 chansons de la base de données n’ont pas été chantées par Eminem, comme vous pouvez le voir sur la figure ci -dessus. Vous devez joindre <> Eminem entre guillemets car Excel traite la combinaison de caractères non égaux à (<>) comme du texte, et Eminem est, bien sûr, du texte. Vous pouvez obtenir le même résultat en utilisant la formule NB.SI (Singer, “<>” & B5), qui utilise le symbole esperluette (&) pour concaténer la référence à la cellule B5 et l’opérateur <>.
FIGURE 3: Vous pouvez combiner la fonction NB.SI avec l’opérateur différent de (<>).
Combien de chansons duraient au moins quatre minutes?
Dans la cellule C16, vous pouvez calculer le nombre de chansons jouées qui ont duré au moins quatre minutes en utilisant la formule NB.SI (minutes, “> = 4”). Vous devez mettre> = 4 entre guillemets car la combinaison de caractères supérieure ou égale à (> =), comme <>, est traitée comme du texte. Vous pouvez voir que 477 chansons ont duré au moins quatre minutes.
Combien de chansons étaient plus longues que la durée moyenne de toutes les chansons de la liste?
Pour répondre à cette question, calculez d’abord la longueur moyenne d’une chanson avec la formule moyenne (minutes) dans la cellule G5. Ensuite, dans la cellule C17, calculez le nombre de chansons qui durent plus longtemps que la moyenne avec la formule NB.SI (Minutes, “>” & G5). Vous pouvez faire référence à une autre cellule (dans ce cas, G5) dans les critères en utilisant le caractère &. Vous pouvez voir que 477 chansons ont duré plus longtemps que la moyenne, ce qui correspond au nombre de chansons d’une durée d’au moins 4 minutes. Ces chiffres correspondent car il est supposé que la longueur de chaque chanson était un entier. Pour qu’une chanson dure au moins 3,48 minutes, elle doit durer au moins 4 minutes.
Combien de chansons ont été chantées par des chanteurs dont le nom de famille commence par S?
Pour répondre à cette question, utilisez un caractère générique, l’astérisque (*), dans les critères. Un astérisque représente n’importe quelle séquence de caractères. Ainsi, la formule NB.SI.ENS (Singer, “S *”) dans la cellule C18 reprend toute chanson chantée par un chanteur dont le nom de famille commence par S. (Les critères ne sont pas sensibles à la casse.) Deux cent trente-deux chansons ont été chantées par des chanteurs avec des noms commençant par S. Ce nombre est simplement le total des chansons chantées par Bruce Springsteen et Britney Spears (103 + 129 = 232).
Combien de chansons ont été chantées par des chanteurs dont le nom de famille contient exactement six lettres?
Cet exemple utilise le caractère générique de point d’interrogation (?). Le point d’interrogation correspond à n’importe quel caractère. Par conséquent, la saisie de la formule NB.SI (Singer, “??????”) dans la cellule C19 compte le nombre de chansons chantées par des chanteurs ayant six lettres dans leur nom de famille. Le résultat est 243. (Deux chanteurs ont les noms de famille de six personnages, Britney Spears et Eminem, qui ont ensemble chanté un total de 243 chansons – 129 + 114 = 243.)
Combien de chansons ont été jouées après le 15 juin 2005?
Les critères que vous utilisez avec les fonctions NB.SI gèrent les dates en fonction du numéro de série d’une date. (Une date ultérieure est considérée comme supérieure à une date antérieure.) La formule NB.SI (Date, “> 15/06/2005”) dans la cellule C20 vous indique que 98 chansons ont été jouées après le 15 juin 2005.
Combien de chansons ont été jouées avant 2009?
Ici, vous voulez que vos critères récupèrent toutes les dates au plus tard le 31 décembre 2008. Entrez la formule NB.SI (Date, “<= 31/12/2008”) dans la cellule C21. Vous pouvez voir que 951 chansons (qui se révèlent être toutes les chansons) ont été jouées avant le début de 2009.
Combien de chansons duraient exactement quatre minutes?
Dans la cellule C22, calculez le nombre de chansons durant exactement quatre minutes avec la formule NB.SI (Minutes, 4). Cette formule compte le nombre de cellules dans la plage G7: G957 contenant un 4. Comme vous pouvez le voir, 247 chansons ont duré exactement quatre minutes. De la même manière, la cellule C23 montre que 230 chansons ont duré exactement cinq minutes.
Combien de chansons jouées ont été chantées par Bruce Springsteen et duraient exactement quatre minutes?
Vous voulez compter chaque ligne dans laquelle une entrée dans la colonne Singer est Springsteen et une entrée dans la colonne Minutes est 4. Parce que cette question implique plus d’un critère, c’est un travail pour la merveilleuse fonction NB.SI.ENS. Entrez la formule = NB.SI.ENS (Singer, “Springsteen”, Minutes, 4) dans la cellule C24.
Cette formule compte n’importe quelle ligne dans laquelle Singer est Springsteen et Minutes est égal à 4. Bruce Springsteen a chanté 24 chansons qui duraient exactement quatre minutes. Votre chanson préférée de Springsteen pourrait être “Thunder Road”, mais cette chanson dure plus de quatre minutes. Utilisez l’assistant de fonction pour saisir des formules impliquant la fonction NB.SI.ENS. N’oubliez pas que vous pouvez coller des noms de plage dans votre formule avec la touche F3.
Combien de chansons jouées ont été chantées par Madonna et duraient de trois à quatre minutes?
Parce que vous avez affaire à plusieurs critères, c’est à nouveau un travail pour NB.SI.ENS. La saisie de la formule = NB.SI.ENS (Chanteur, “Madonna”, Minutes, “<= 4”, Minutes, “> = 3”) dans la cellule C25 compte toutes les lignes dans lesquelles Madonna a chanté une chanson d’une durée de trois à quatre minutes. Ce sont exactement les lignes que vous souhaitez compter. Il s’avère que Madonna a chanté 70 chansons de trois à quatre minutes. (Peut-être que votre préféré est “Crazy for You!”)
Comment puis-je compter le nombre de cellules dans une plage contenant des nombres?
La fonction NB compte le nombre de cellules dans une plage contenant une valeur numérique. Par exemple, la formule NB (B5: C14) dans la cellule C2 affiche 9 car neuf cellules (les cellules dans C5: C13) dans la plage B5: C14 contiennent des nombres. (Voir figure 2 .)
Comment puis-je compter le nombre de cellules vides dans une plage?
La fonction NB.VIDE compte le nombre de cellules vides dans une plage. Par exemple, la formule NB.VIDE (B5: C14) entrée dans la cellule C4 renvoie une valeur de 2 car deux cellules (B14 et C14) dans la plage B5: C14 contiennent des blancs.
Comment compter le nombre de cellules non vides dans une plage?
La fonction NB.VIDE renvoie le nombre de cellules non vides dans une plage. Par exemple, la formule NB.VAL (B5: C14) dans la cellule C3 renvoie 18 car 18 cellules de la plage B5: C14 ne sont pas vides.
Remarques
Dans le reste du livre, des méthodes alternatives sont discutées pour répondre à des questions impliquant deux critères ou plus (comme le nombre de chansons de Britney Spears jouées avant le 10 juin 2005):
■ Les fonctions statistiques de la base de données sont traitées dans les prochains chapitre .
■ Les formules matricielles sont décrites au dernier chapitre , “Formules matricielles et fonctions”.
■ Les formules matricielles sont décrites au dernier chapitre, “Formules matricielles et fonctions”.