Compter le nombre de cellules différentes de plusieurs valeurs dans Excel

Dans Excel, la fonction NB.SI permet facilement de compter les cellules différentes d’une valeur spécifique — mais avez-vous déjà essayé de compter celles qui ne correspondent à aucune des nombreuses valeurs fournies ? Par exemple, je souhaite obtenir le nombre total de produits figurant dans la colonne A, tout en excluant les articles spécifiques listés dans la plage C4:C6, comme le montre la capture d’écran ci-dessous. Dans cet article, je vous présente quelques formules efficaces pour accomplir cette tâche sous Excel.

• Compter le nombre de cellules différentes de plusieurs valeurs avec la formule 1
• Compter le nombre de cellules différentes de plusieurs valeurs avec la formule 2

Compter le nombre de cellules différentes de plusieurs valeurs avec la formule 1

Pour compter les cellules différentes de plusieurs valeurs spécifiées, combinez les fonctions RECHERCHE, ESTNA et SOMMEPROD. La syntaxe générique est la suivante :

• range: La liste des cellules que vous souhaitez compter ;
• exclude_values : Les valeurs à exclure de la plage.

1. Saisissez ou copiez la formule ci-dessous dans une cellule vide pour afficher le résultat :

2. Appuyez ensuite sur la touche Entrée pour obtenir le résultat correct, comme illustré ci-dessous :

Explication de la formule :

• RECHERCHE(A2:A13;C2:C4;0) : Cette fonction RECHERCHE renvoie la position de chaque valeur correspondante sous forme d’un nombre, et une erreur #N/A lorsque la valeur n’est pas trouvée. Autrement dit, dès qu’une valeur dans la plage A2:A13 correspond à l’une des valeurs de C2:C4, la fonction renvoie sa position ; dans le cas contraire, elle affiche une erreur #N/A. Le résultat obtenu est le suivant : {1;#N/A;#N/A;1;3;2;#N/A;#N/A;3;#N/A;#N/A;#N/A}.
• ESTNA(RECHERCHE(A2:A13;C2:C4;0)) = ESTNA({1;#N/A;#N/A;1;3;2;#N/A; #N/A;3;#N/A; #N/A; #N/A}) : Cette fonction ESTNA convertit les erreurs en VRAI et les nombres en FAUX, ce qui donne : {FAUX;VRAI;VRAI;FAUX;FAUX;FAUX;VRAI;VRAI;FAUX;VRAI;VRAI;VRAI}.
• --(ESTNA(RECHERCHE(A2:A13;C2:C4;0))) = --({FAUX;VRAI;VRAI;FAUX;FAUX;FAUX;VRAI;VRAI;FAUX;VRAI;VRAI;VRAI}) : L’opérateur double négation (--) transforme chaque VRAI en 1 et chaque FAUX en 0, ce qui donne : SOMMEPROD(--(ESTNA(RECHERCHE(A2:A13;C2:C4;0)))) = SOMMEPROD({0;1;1;0;0;0;1;1;0;1;1;1}).
• La fonction SOMMEPROD est ensuite utilisée pour additionner les nombres du tableau et obtenir le résultat suivant : 7.

Compter le nombre de cellules différentes de plusieurs valeurs avec la formule 2

Pour accomplir cette tâche dans Excel, vous pouvez également combiner les fonctions NBVAL, SOMMEPROD et NB.SI. La syntaxe générique est la suivante :

• range: La liste des cellules que vous souhaitez compter ;
• exclude_values: Les valeurs à ignorer dans la liste de plage.

1. Saisissez ou copiez la formule ci-dessous dans une cellule vide pour afficher le résultat :

2. Appuyez ensuite sur la touche Entrée pour obtenir le résultat illustré dans la capture d’écran suivante :

Explication de la formule :

• NBVAL(A2:A13) : Cette fonction NBVAL renvoie le nombre de cellules non vides dans la plage A2:A13. Le résultat obtenu est le suivant : 12.
• NB.SI(A2:A13;C2:C4) : Cette fonction NB.SI compte les cellules de la plage A2:A13 correspondant aux critères situés en C2:C4, ce qui donne le résultat suivant : {2;1;[[PH_47]} — indiquant que « Pomme » apparaît deux fois, « Raisin » une fois et « Citron » deux fois.
• SOMMEPROD(NB.SI(A2:A13;C2:C4)) = SOMMEPROD({2;1;2}) : Cette fonction SOMMEPROD additionne tous les éléments du tableau et renvoie le résultat final suivant : 5.
• NBVAL(A2:A13) - SOMMEPROD(NB.SI(A2:A13;C2:C4)) = 12-5 : Le résultat (5) renvoyé par la fonction SOMMEPRODest soustrait du nombre total de cellules non vides (12) afin d’obtenir le résultat final recherché.

Remarque : Les deux formules ci-dessus ne sont pas Sensible à la casse.

Fonctions associées utilisées :

Autres articles :