La fonction NB.SI: 16 exemples d'utilisation

Dans l’article suivant de la formation Excel, vous allez découvrir 16 exemples qui expliquent comment utiliser la fonction NB.SI, et ceci selon le nombre de critères utilisés et selon leurs types : texte, nombre, date, valeur logique ou pour identifier des doublons dans des plages de cellules.

Comme nous le mentionnons toujours dans ce blog, lorsqu’une fonction comporte dans son nom le mot SI, cela signifie qu’elle sera effectuée en répondant à un ou plusieurs critères (Fonctions conditionnelles).

La fonction NB.SI permet donc de compter le nombre de cellules en répondant à un critère qui peut être une valeur textuelle, numérique, date ou logique …

La syntaxe de la fonction NB.SI est : NB.SI(plage; critères)

Pour mieux utiliser la fonction NB.SI, suivez avec moi ces différents exemples d’utilisation de cette fonction :

Vidéo courte

Je vous propose tout d'abord de découvrir rapidement comment utiliser la fonction NB.SI à travers cette courte vidéo :

Utiliser la fonction NB.SI avec un seul critère

Utilisation d’un critère textuel

Voici un tableau de ventes réalisées par différents vendeurs :

Nous voulons par exemple compter le nombre de ventes réalisées par un vendeur sélectionné.

Choisissez par exemple le vendeur : Sergio et utilisez la fonction NB.SI pour effectuer le calcul souhaité :

• Sélectionnez une cellule vide et tapez la formule suivante : =NB.SI(A2:A14;"Sergio")
• Le nom Sergio est mis entre guillemets puisque son type de données est texte, et bien sûr la plage de critères est la colonne Vendeurs : A2:A14

• la fonction NB.SI indique donc que 3 cellules dans la plage de cellules A2:A14 qui contiennent le nom Sergio.

• Si vous remplacez Sergio par Sophie, vous allez obtenir 2.

Note : Vous pouvez soit entrer le nom Sophie directement, soit sélectionner la cellule contenant ce nom. Votre formule pourra s’écrire alors de deux façons : =NB.SI(A2:A14;"Sophie") ou =NB.SI(A2:A14;A10)

Remarquez également que dans ce cas, la référence de cellule A10 n’est pas mise entre guillemets.

Utilisation de l’astérisque

Exemple 1 : Compter le nombre de cellules contenant des noms commençant par « S »

Sélectionnez une cellule vide et tapez la formule suivante : =NB.SI(A2:A14;"S*")

Dans cet exemple l’astérisque remplace tout ce qui vient après la lettre S, et comme vous le remarquez, Excel affiche le nombre 6.

Exemple 2 : Compter le nombre de cellules contenant des noms qui se terminent par « a »

• La fonction NB.SI sera écrite de la façon suivante : =NB.SI(A2:A14;"*a")
• Ici l’astérisque remplacent tous les caractères qui se trouvent avant la lettre « a »
• Excel affiche le nombre 3.

Exemple 3 : Compter le nombre de cellules contenant un nom ou une expression…

Par exemple dans le tableau suivant, vous avez des cellules qui contiennent le nom Rico dont nous voulons compter leur nombre :

Sélectionnez une cellule vide et tapez la formule suivante : =NB.SI(A2:A14;"*rico*")

Nous avons mis donc le nom Rico entre deux astérisques parce qu’il y a des cellules où le nom complet commence par Rico et une cellule où  Rico est écrit en dernier.

Note : Que vous écrivez « Rico » ou « rico » Excel renvoie le même résultat puisqu’on n’est pas obligé à respecter la casse en entrant les arguments de la fonction NB.SI.

Utilisation des opérateurs de comparaison dans les arguments de la fonction NB.SI

Exemple 1 : Egal à un nombre exact

A partir du tableau suivant vous allez calculer le nombre de personnes âgées de 33 ans:

• Sélectionnez une cellule et entrez la fonction NB.SI comme suit : =NB.SI(B2:B14;33)

• Vous avez donc 4 personnes qui ont l’âge de 33 ans.

Note : Remarquez que le critère 33 est écrit sans guillemets parce que c’est un nombre et pas un texte.

Supérieur ou inférieur

Exemple 1 : Compter le nombre de cellules qui ont un montant supérieur à 1000 euros

• Entrez la formule suivante dans une cellule : =NB.SI(C2:C14;">1000")

• Excel affiche 13 comme résultat de cette formule.

Note : Si vous avez bien remarqué, l’argument >1000 est écrit entre guillemets. Notez donc que lorsque vous insérez des nombres accompagnés des opérateurs de comparaisons vous devez toujours les mettre entre guillemets.

Exemple 2 : Compter le nombre de cellules contenant un montant inférieur à 2500 Euros

La fonction NB.SI sera utilisée de la façon suivante: 

=NB.SI(C2:C14;"<2500")

Utilisation de la date comme critère

Exemple 1 : Compter le nombre de cellules contenant une date précise

Dans cet exemple vous allez utiliser la fonction NB.SI pour trouver le nombre de cellules qui contiennent la date 06/04/2017 :

• Vous pouvez donc écrire la formule suivante : =NB.SI(B2:B14;"06/04/2017")
• Attention : Mettez le critère date entre guillemets.

Comme vous pouvez aussi taper la formule suivante en se servant de la fonction DATE

=NB.SI(B2:B14;DATE(2017;4;6))

Les arguments de la fonction DATE sont successivement Année, Mois et Jour séparés bien sûr par des points-virgules.

Exemple 2 : compter le nombre de cellules contenant des dates inférieures ou égales à 05/04/2017

Entrez la fonction NB.SI suivante dans une cellule vide : 

=NB.SI(B2:B14;"<=05/04/2017")

Exemple 3 : compter le nombre de cellules contenant des dates supérieures ou égales à 05/04/2017

Entrez la formule suivante dans une cellule vide : =NB.SI(B2:B14;">=05/04/2017")

Exemple 4 : Compter le nombre de cellules contenant des dates inférieures à la date d’aujourd’hui de 31 jours

Utilisez donc la fonction NB.SI de cette façon en intégrant la fonction AUJOURDHUI()

=NB.SI(B2:B14;"<"&AUJOURDHUI()-31)

Exemple 5 : Compter le nombre de cellules contenant des dates inférieures à la date d’aujourd’hui de 3 mois :

• Vous pouvez tapez tout simplement : =NB.SI(B2:B14;"<"&AUJOURDHUI()-90)

AUJOURDHUI()-90 : renvoie la date d'aujourd’hui moins 90 jours (équivalent à 3 mois).

• Ou bien intégrez la fonction DATE dans la fonction NB.SI de la façon suivante et ceci en supposant que la date d’aujourd’hui est le 06/04/2017

=NB.SI(B2:B14;"<"&DATE(2017;4 ;6)-90)

• Et si vous voulez que le calcul soit effectué automatiquement et mis à jour, entrez la formule comme suit :

=NB.SI(B2:B14;"<"&DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI())-3;JOUR(AUJOURDHUI())))

Expliquons un peu ce qui se passe là !!!

Nous avons montré précédemment que la fonction DATE contient trois arguments : Année, Mois et Jour. Voici sa syntaxe : DATE(année;mois;jour)

Alors, pour renvoyer l’année qui correspond à la date d’aujourd’hui nous avons utilisé une autre fonction qui est ANNEE en y intégrant la fonction AUJOURDHUI() qui renvoie elle aussi la date d’aujourd’hui : 06/04/2017

La fonction ANNEE(AUJOURDHUI()) est pareille donc à : ANNEE("06/04/2017") et renvoie 2017.

Pour renvoyer le mois correspondant à la date d’aujourd’hui, nous avons utilisé la fonction MOIS.

La formule MOIS(AUJOURDHUI()) renvoie le mois 4 de la date actuelle 06/04/2017.

Et puisque nous cherchons les dates inférieures à la date d’aujourd’hui de 3 mois, nous avons tapé :  MOIS(AUJOURDHUI())-3 ce qui renvoie le mois 1 (Janvier).

Il nous reste enfin de déterminer le jour de la date d’aujourd’hui, alors nous avons utilisé la fonction JOUR :    JOUR(AUJOURDHUI()) renvoie bien sûr 6

Alors et lorsque vous ouvrez votre classeur, votre formule destinée à compter le nombre de cellules contenants des dates inférieures à la date d’aujourd’hui de 3 mois sera mise à jour automatiquement et prend en compte l’actualisation de la date d’aujourd’hui !

Utiliser la fonction NB.SI avec plusieurs critères

Exemple 1 : Compter le nombre de cellules contenant « Sophie » et le nombre de cellules contenant « Sergio » en une seule formule

Si vous reprenez le premier exemple en début de cet article, vous avez vu que nous avons entré la formule NB.SI(A2:A14;"Sergio") pour compter le nombre de cellules contenant « Sergio » et la formule NB.SI(A2:A14;"Sophie") pour compter le nombre de cellules contenant « Sophie ».

Maintenant si nous voulons compter le nombre de fois que ces deux vendeurs ont vendu, nous allons utiliser la fonction NB.SI de la façon suivante en additionnant les résultats des deux formules précédentes :

= NB.SI(A2:A14;"Sergio")+ NB.SI(A2:A14;"Sophie")

Dans cet exemple vous voyez que nous avons utilisés deux critères, si vous voulez en ajouter un autre tapez l’opérateur d’addition et entrez la fonction NB.SI pour le nouveau critère et ainsi de suite.

Exemple 2 : Compter le nombre de montants supérieurs à 1000 euros et inférieurs à 3000 euros

Les deux formules que nous pouvons utiliser se sont:

• =NB.SI(B2:B14;">1000") qui renvoie le nombre de montants supérieurs à 1000 Euros.
• Et =NB.SI(B2:B14;"<3000") qui renvoie le nombre de montants inférieurs à 3000 Euros.

Nous allons donc utiliser ces deux formules dans une opération de soustraction mais avec une toute petite modification pour avoir un résultat correct (Je vous laisse la découvrir ;) ) :

Voici notre formule alors :

=NB.SI(B2:B14;">1000")-NB.SI(B2:B14;">3000")

Compter le nombre de doublons

Exemple 1 : Compter le nombre de doublons dans la même colonne

Une autre possibilité que fournit l’utilisation de la fonction NB.SI est de trouver le nombre de doublons dans une plage de cellules.

Reprenons encore notre exemple des noms de vendeurs, et essayons de trouver les noms similaires dans la plage A2:A14 en utilisant NB.SI:

• Sélectionnez la cellule B2 et entrez la formule suivante : =NB.SI($A$2:$A$14;A2)>1
• Si la valeur contenue dans la cellule A2 est répétée plus d’une fois dans la plage de cellules A2 :A14, Excel affichera VRAI sinon il affichera FAUX.
• Copiez la formule dans les cellules de B2 à B14, et voici ce que vous allez obtenir :

• Pour calculer ensuite le nombre de cellules contenant les doublons, entrez la formule suivante :

=NB.SI(B2:B14;VRAI)

Note : Remarquez que la valeur logique VRAI est saisie sans guillemets.

Il y a une autre solution pour ce faire également, qui consiste à utiliser NB.SI intégrée dans la fonction SOMMEPROD.

Voici comment procéder:

• Sélectionnez une cellule vide et entrez la formule suivante (Faites attention à l'utilisation des parenthèses) :

=SOMMEPROD((NB.SI(A2:A14;A2:A14)>1)*(A2:A14<>""))

• Le résultat est le même : 5 doublons aussi en total.

Note : pour plus de détails sur l’utilisation de cette syntaxe de la fonction SOMMEPROD, visitez le lien suivant : Moyenne pondérée avec condition

Exemple 2 : Compter le nombre de doublons dans deux colonnes

Les deux colonnes A et B contiennent des noms différents, cependant il y en a quelques-uns qui sont similaires dans les deux colonnes:

• Entrez la formule suivante pour calculer le nombre de ces doublons :

=SOMMEPROD((NB.SI(A1:A13;B1:B13)>0)*(B1:B13<>""))

• NB.SI(A1:A13;B1:B13)>0: l’utilisation de NB.SI dans ce cas (avec la condition >0 et non >1) permet de calculer le nombre de noms de la plage A1:A13 qui se répètent au moins une fois dans la plage B1:B13.
• Et pour que le calcul s’effectue sans erreur, la plage B1:B13 ne doit pas être vide, c’est pourquoi que nous avons entré ce test logique B1:B13<>"".

Lire la suite »

Moyenne pondérée avec condition

Après avoir vu comment calculer une moyenne pondérée en utilisant une formule qui se compose des deux fonctions SOMMEPROD et SOMME, aujourd’hui et dans cette formation Excel, vous allez découvrir comment calculer une moyenne pondérée qui répond à certains critères en utilisant différemment la fonction SOMMEPROD.

SOMMEPROD conditionnelle

Nous allons d’abord expliquer une deuxième façon d’utiliser la fonction SOMMEPROD qui va nous permettre ensuite de créer la formule que nous allons utiliser pour calculer une moyenne pondérée avec condition.

Comme vous l’avez vu dans l’article Formule de la moyenne pondérée, la fonction SOMMEPROD permet de calculer la somme des produits des valeurs par leurs poids, en outre, vous pouvez utiliser la fonction SOMMEPROD également pour n’effectuer ce calcul que sur quelques valeurs qui répondent à certaines conditions.

Regardez l’exemple suivant, c’est un tableau qui représente une liste des accessoires des smartphones avec leurs quantités et prix de vente. Téléchargez-le ici : Accessoires smartphones

Nous voulons par exemple trouver le nombre de Housses achetées en utilisant la fonction SOMMEPROD, c’est à dire calculer la somme des quantités correspondant à l’accessoire Housse.

Si nous appliquons un filtre sur les données de ce tableau par Housse et calculer la somme de ses quantités, nous aurons cet affichage :

Le nombre de Housses achetées est donc 210.

Utilisons maintenant SOMMEPROD.

• Dans ce cas nous devons utiliser la deuxième syntaxe de la fonction SOMMEPROD (SOMMEPROD conditionnelle) que nous pouvons l’écrire de cette façon :

SOMMEPROD(TEST_LOGIQUE1 * TEST_LOGIQUE2 * PlageValeur)

• Les arguments Test_logiques doivent être des formules qui, en les effectuant, renvoient soit la valeur Vrai, soitla valeur Faux.
• PlageValeur : c’est la référence de la plage de cellules qui contient les valeurs numériques dont on veut calculer  la somme.
• * : bien sûr c’est l’opérateur de multiplication.

Note : notez bien que nous n’avons pas utiliser les points-virgules dans cette syntaxe.

Passons à la pratique :

• Sélectionnez une cellule de votre feuille de calcul.
• Tapez la formule suivante :

=SOMMEPROD((A2:A13="Housse")*B2:B13)

• Le résultat est donc : 210

Expliquons bien les choses :

• La fonction SOMMEPROD vérifie en premier l’existence des cellules qui contiennent la valeur Housse dans la plage de cellules A2:A13 et ceci en effectuant la formule A2:A13="Housse".
• Et puisqu’il en existe 3, elle renvoie la valeur logique Vrai pour chacune. Regardez l’exemple suivant dans lequel nous avons appliqué uniquement la formule =A2:A13="Housse".

• Ensuite, SOMMEPROD passe au deuxième argument B2:B13 qui est ici la plage des valeurs à sommer, et bien sûr ce sont les quantités correspondant aux 3 valeurs trouvées. (Housse)
• Enfin, la fonction SOMMEPROD calcule la somme des produits comme ça :

(Vrai*50)+(Vrai*60)*(Vrai*100)

Dans Excel, la valeur Vrai est égale à 1 et la valeur Faux est égale à 0.

• Alors, notre formule s’effectuera de cette façon : (1*50)+(1*60)+(1*100), ce qui donne le résultat 210.

Et voilà donc, nous avons montré comment calculer la somme des produits des valeurs par leurs poids en répondant à un seul critère.

Vous pouvez aussi utiliser cette fonction pour effectuer un calcul qui répond à plusieurs conditions ou critères.

Dans l’exemple suivant, nous voulons calculer le nombre total des Housses achetées du smartphone iPhone.

• Sélectionnez une cellule vide de votre feuille de calcul et tapez la formule suivante :

=SOMMEPROD((A2:A13=A2)*(B2:B13=B2)*C2:C13)

• Comme vous pouvez le remarquez, au lieu d’écrire le mot iPhone dans le premier test_logique, nous avons tapez la référence de la cellule qui le contient : A2
• Aussi, nous avons introduit la référence de la cellule B2 dans le deuxième test_logique au lieu de taper Housse.

Moyenne pondérée avec condition

Maintenant vous allez voir un exemple qui vous montrera comment calculer une moyenne pondérée en répondant à une condition donnée.

En reprenant l'exemple du tableau précédent, nous souhaitons calculer le prix de vente moyen de l’article Coque en utilisant la fonction SOMMEPROD et en appliquant ce qui est vu en haut.

Le principe alors est de calculer la somme des produits Qte*Prix de vente de l’article Coque puis de diviser cette somme par le total des quantités correspondant au même article.

• Sélectionnez la cellule qui va afficher le résultat du calcul.
• Tapez la formule suivante :

=SOMMEPROD((A2:A13="Coque")*B2:B13*C2:C13)/SOMMEPROD((A2:A13="Coque")*B2:B13)

Notre formule se compose et comme vous le remarquez de deux parties :

• SOMMEPROD((A2:A13="Coque")*B2:B13*C2:C13) qui calcule la somme des produits Qte*Prix de vente de l’article Coque.
• SOMMEPROD((A2:A13="Coque")*B2:B13) : calcule la somme des quantités correspondant à l’article Coque.

En effectuant la formule, Excel affiche le prix moyen de vente suivant: 17,39

En suivant la même procédure, calculons à présent le prix de vente moyen de l’accessoire Coque du smartphone iPhone :

• Sélectionnez une cellule vide.
• Tapez la formule qui suit :

=SOMMEPROD((A2:A13=A7)*(B2:B13=B7)*C2:C13*D2:D13)/SOMMEPROD((A2:A13=A7)*(B2:B13=B7)*C2:C13)

Lire la suite »

Formule de la moyenne pondérée

L’article présent de la Formation Excel vous explique comment créer une formule pour calculer la moyenne pondérée en vous servant de la fonction SOMMEPROD. Vous allez voir aussi comment faire pour que votre formule créée calcule automatiquement une moyenne pondérée lorsque vous insérez de nouvelles valeurs ou vous supprimez quelques-unes.

Une moyenne arithmétique simple est différente d’une moyenne pondérée. Dans l’article précédent (Calculer la moyenne des données filtrées ou non-filtrées en utilisant la fonction MOYENNE) j'ai montré comment calculer une moyenne arithmétique en utilisant la fonction Moyenne. Et pour calculer une moyenne pondérée, Excel ne fournit aucune fonction spécifique jusqu’à la publication de cet article. Cependant vous pouvez calculer cette moyenne en utilisant une fonctionne très importante qu’on appelle SOMMEPROD.

La moyenne pondérée

Une moyenne pondérée est une moyenne que l’on obtient en multipliant chaque valeur à son effectif appelé aussi poids, puis on additionne tous les produits obtenus, et enfin on divise la somme obtenue par le total des poids.

Dans l’exemple suivant, un étudiant a eu les notes suivantes pour les matières : Maths, physique et anglais où chaque matière possède son propre coefficient :

• Pour calculer la moyenne pondérée de cet étudiant, on écrit :

(15x6)+(13x5)+(17x3)/6+5+3

• Ce qui donne 206/14
• La moyenne donc est 14,71

Si vous voulez faire ce calcul dans Excel, vous pouvez procéder ainsi :

• Sélectionnez par exemple C5 dans le tableau suivant et tapez la formule suivante :

=((B2*C2)+(B3*C3)+(B4*C4))/(C2+C3+C4)

• Et vous allez obtenir alors :  14,71

Cette formule sera très gênante surtout si votre tableau contient de multiples lignes. Alors et pour réduire ce travail, utilisez les deux fonctions SOMMEPROD et SOMME.

Présentation de la fonction SOMMEPROD

La fonction SOMMEPROD (Somme des Produits) permet de calculer la somme des produits des valeurs par leurs poids. C’est-à-dire que le travail que vous avez fait de calculer le produit de chaque note par son coefficient puis de calculer le total de ces produits, vous pouvez le faire aisément avec SOMMEPROD.

La syntaxe de la fonction SOMMEPROD

SOMMEPROD(matrice1, [matrice2], [matrice3], ...)

• Vous allez donc taper à la place de l’argument matrice1 la référence de la plage de cellules B2:B4 qui contient les notes, et à la place de l’argument matrice2 tapez la référence de la plage de cellules C2:C4
• Votre fonction sera écrite de cette façon : =SOMMEPROD(B2:B4;C2:C4)

• Ce qui donne 206
• Il vous reste enfin de diviser ce total par la somme des coefficients, c'est à dire : SOMME(C2:C4)
• Alors pour calculer la moyenne pondérée, la  formule sera :

=SOMMEPROD(B2:B4;C2:C4)/SOMME(C2:C4)

• Et c’est le même résultat obtenu précédemment : 14,71

Les matrices doivent avoir les mêmes dimensions :

Oui, effectivement, les matrices de la fonction SOMMEPROD doivent avoir le même nombre de lignes et de colonnes, si non votre formule renverra une erreur de type VALEUR. (pour plus de détails sur cette erreur consultez l’article suivant : Les erreurs dans Excel)

Dans l'exemple vu en haut, la matrice B2:B4 se compose d’une colonne et de trois lignes et c'est le même nombre de colonnes et de lignes pour la matrice C2:C4.

Regardez maintenant l’exemple suivant :

• Les deux matrices contiennent chacune deux colonnes et trois lignes.
• Alors, pour trouver la somme des produits des valeurs de la première matrice par celles de la deuxième matrice, suivez la procédure illustrée dans cette image animée :

• Le travail effectué par cette fonction suit l’ordre suivant :

(A1*D1)+(A2*D2)+(A3*D3)+(B1*E1)+(B2*E2)+(B3*E3)

Note : Vous pouvez utiliser jusqu’à 255 matrices dans la fonction SOMMEPROD

Calculer automatiquement la moyenne pondérée lors d’une insertion ou suppression de valeurs :

Dans cet exemple, vous allez voir comment calculer automatiquement le prix de vente moyen lorsque de nouveaux produits se sont ajoutés au stock mais qui se sont achetés à des prix différents.

• Dans la cellule B7, tapez la formule pour calculer la moyenne pondérée comme suit :

=SOMMEPROD(A2:A6;B2:B6)/SOMME(A2:A6)

• Faites maintenant de petites modifications, en imbriquant la fonction DECALER :
• Remplacez la cellule A6 dans SOMMEPROD et SOMME par DECALER(A7;-1;0), et la cellule B6 par DECALER(B7;-1;0)

• Insérez maintenant une nouvelle ligne juste en dessus de la cellule B7 qui contient votre formule, puis tapez les nouvelles valeurs : Quantité et Prix de vente.

• Remarquez donc que la moyenne pondérée s’est calculée automatiquement.
• Essayez cette fois de supprimer une ligne de ce tableau et remarquez aussi que votre formule moyenne pondérée s’effectue aussi automatiquement.

Lire la suite »