Fonction NB.SI.ENS - Umaan

Fonction NB.SI.ENS

Fonction NB.SI.ENS

La fonction NB.SI.ENS applique les critères aux cellules de plusieurs plages et compte le nombre de fois où tous les critères sont remplis.

Durée : 5min
Débutant

Nous allons construire un tableau de bord à partir du fichier qui se trouve dans le dernier chapitre. (Préciser quel chapitre) Ce tableau de bord est construit uniquement avec la formule NB.SI.ENS.

Définition

La fonction NB.SI.ENS applique les critères aux cellules de plusieurs plages et compte le nombre de fois où tous les critères sont remplis.

Objectif

A partir du fichier de l’onglet Magasins nous allons créer un tableau de bord qui sera alimenté automatiquement.
Nous cherchons à connaitre le nombre de magasins par pays, combien sont installés, combien restent à installer et combien sont planifiés.

Tableau de bord

Entête

Dans le tableau de bord, les cellules d’entête sont construites selon le même modèle.
Nous concaténons un texte avec le résultat d’une formule. Voir le cours sur SOUS.TOTAL
Les formules sont du type :
="Texte à afficher : "&SOMME(B2:B11)

Pays

Le nom des pays de la colonne A doivent être les mêmes que ceux de l’onglet Magasin de la colonne E.

Etude de la formule

Formule de base

Syntaxe : NB.SI.ENS(plage_critères1; critères1; [plage_critères2; critères2]…)

La syntaxe de la fonction NB.SI.ENS contient les arguments suivants :

plage_critères1Requis. La première plage dans laquelle évaluer les critères associés.
critère1Requis. Critères, sous forme de nombre, d’expression, de référence de cellule ou de texte, qui déterminent les cellules à compter.
Par exemple, les critères peuvent être les suivants :
32, « >32 », B4, « pommes » ou »32″.

Cette formule peut intégrer jusqu’à 127 critères

Formule de la Colonne A

Cette formule est la plus simple car elle ne comporte qu’un seul critère.

=NB.SI.ENS(Magasins!E:E;A2)

Nous recherchons dans la colonne E combien d’éléments sont égaux au contenu de la cellule A2. En clair combien de fois la pays France se trouve dans la colonne E.

Il suffit de tirer la formule et d’obtenir le nombre de magasins pour chaque pays.

Comme nous testons l’ensemble de la colonne E, il n’est pas utile de figer les cellules par $. Attention cependant à vos tableaux.

Formule de la Colonne B

Nous considérons que les magasins installés ont envoyé une recette d’installation.

La formule est :
=NB.SI.ENS(Magasins!E:E;A2;Magasins!G:G;"*recette reçue*")

Cette formule comporte 2 plages différentes et 2 critères différents.
Le 1er critère est identique est identique à la formule de la colonne A.
Le 2e critère, nous allons le chercher en colonne G et doit contenir les mots « recette reçue », les étoiles devant et derrière correspondent à des caractères génériques. Il peut donc y avoir une phrase complète dans cette cellule tant que les 2 mots sont présents et côte à côte.

Littéralement la formule signifie combien de magasins dans tel pays ont envoyé une recette d’installation.
Il suffit de copier la formule pour avoir l’ensemble des éléments.

Votre tableau doit ressembler à :

Formule de la Colonne C

Comme dans la colonne précédente, il n’y a que 2 critères.
La différence vient du 2e critère ou les cellules à tester doivent différentes de « recette reçue ».
Nous concaténons  » <>  » avec « *recette reçue* ».
=NB.SI.ENS(Magasins!E:E;A2;Magasins!G:G;"<>"&"*recette reçue*")

Formule de la Colonne E

Dans la dernière formule je rajoute un critère qui nous permet de déterminer les magasins par pays qui sont planifiés mais qui n’ont pas été installés.
Donc les magasins qui ont pas des dates d’installations en colonne F et qui n’ont pas de recette reçue.

Donc la formule est la suivante :
=NB.SI.ENS(Magasins!E:E;A2;Magasins!G:G;"<>"&"*recette reçue*";Magasins!F:F;"<>"&"")
Comme énoncé, le dernier critère est la recherche dans la colonne F des magasins qui ont des dates d’installation donc différents de vide.

En recopiant la formule vous obtenez le tableau définitif :

Conclusion

Cette formule est vraiment très pratique et vous renvoie le même résultat que si vous utilisiez un filtre.
La formule SOMME.SI.ENS garde le même principe et fera l’objet d’un autre chapitre.

Catégories : Excel
Le coach
Autres tutoriels qui pourraient vous interesser