Dans ce didacticiel, vous apprendrez à compter des valeurs uniques dans Excel à l'aide de formules (fonctions COUNTIF et SUMPRODUCT).
Comment compter les valeurs uniques dans Excel
Disons que nous avons un ensemble de données comme indiqué ci-dessous :
Pour les besoins de ce tutoriel, je nommerai la plage A2:A10 comme NOMS. À l'avenir, nous utiliserons cette plage nommée dans les formules.
Voir aussi : Comment créer des plages nommées dans Excel.
Dans cet ensemble de données, il y a une répétition dans la plage NAMES. Pour obtenir le nombre de noms uniques à partir de cet ensemble de données (A2:A10), nous pouvons utiliser une combinaison de fonctions COUNTIF et SUMPRODUCT comme indiqué ci-dessous :
=SUMPRODUCT(1/COUNTIF(NAMES,NAMES))
Comment fonctionne cette formule ?
Décomposons cette formule pour mieux comprendre :
- COUNTIF(NAMES,NAMES)
- Cette partie de la formule renvoie un tableau. Dans l'exemple ci-dessus, ce serait {2;2;3;1;3;1;2;3;2}. Les nombres ici indiquent combien de fois une valeur apparaît dans la plage de cellules donnée.
Par exemple, le nom est Bob, qui apparaît deux fois dans la liste, il renverrait donc le numéro 2 pour Bob. De même, Steve se produit trois fois et donc 3 est retourné pour Steve.
- Cette partie de la formule renvoie un tableau. Dans l'exemple ci-dessus, ce serait {2;2;3;1;3;1;2;3;2}. Les nombres ici indiquent combien de fois une valeur apparaît dans la plage de cellules donnée.
- 1/COUNTIF(NAMES,NAMES)
- Cette partie de la formule renverrait un tableau - {0.5;0.5;0.3333333333333333;1;0.3333333333333333;1;0.5;0.3333333333333333;0.5}
Puisque nous avons divisé 1 par le tableau, il renvoie ce tableau.
Par exemple, le premier élément du tableau renvoyé ci-dessus était 2. Lorsque 1 est divisé par 2, il renvoie 0,5.
- Cette partie de la formule renverrait un tableau - {0.5;0.5;0.3333333333333333;1;0.3333333333333333;1;0.5;0.3333333333333333;0.5}
- SUMPRODUCT(1/COUNTIF(NAMES,NAMES))
- SUMPRODUCT ajoute simplement tous ces nombres. Notez que si Bob apparaît deux fois dans la liste, le tableau ci-dessus renvoie 0,5 partout où le nom de Bob apparaît dans la liste. De même, puisque Steve apparaît trois fois dans la liste, le tableau renvoie 0,3333333 chaque fois que le nom de Steve apparaît. Lorsque nous ajoutons les nombres pour chaque nom, cela renvoie toujours 1. Et si nous ajoutons tous les nombres, cela renvoie le nombre total de noms uniques dans la liste.
Cette formule fonctionne bien jusqu'à ce que vous n'ayez aucune cellule vide dans la plage. Mais si vous avez des cellules vides, cela renverrait un #DIV/0 ! Erreur.
Comment gérer les cellules BLANK ?
Comprenons d'abord pourquoi il renvoie une erreur lorsqu'il y a une cellule vide dans la plage. Supposons que nous ayons l'ensemble de données comme indiqué ci-dessous (avec la cellule A3 vide):
Maintenant, si nous utilisons la même formule que nous avons utilisée ci-dessus, la partie COUNTIF de la formule renvoie un tableau {2;0;3;1;3;1;2;3;1}. Comme il n'y a pas de texte dans la cellule A3, son nombre est renvoyé comme 0.
Et puisque nous divisons 1 par tout ce tableau, il renvoie un #DIV/0 ! Erreur.
Pour gérer cette erreur de division en cas de cellules vides, utilisez la formule ci-dessous :
=SUMPRODUCT((1/COUNTIF(NAMES,NAMES&””)))
Une modification que nous avons apportée à cette formule est la partie critère de la fonction COUNTIF. Nous avons utilisé NAMES&”” au lieu de NAMES. En faisant cela, la formule renverrait le nombre de cellules vides (auparavant, elle renvoyait 0 là où il y avait une cellule vide).
REMARQUE : cette formule compterait les cellules vides comme une valeur unique et la renverrait dans le résultat.
Dans l'exemple ci-dessus, le résultat doit être 5, mais il renvoie 6 car la cellule vide est comptée comme l'une des valeurs uniques.
Voici la formule qui prend en charge les cellules vides et ne les compte pas dans le résultat final :
=SUMPRODUCT((NAMES"")/COUNTIF(NAMES,NAMES&""))
Dans cette formule, au lieu de 1 comme numérateur, nous avons utilisé NAMES””. Cela renvoie un tableau de VRAI et FAUX. Il renvoie FALSE chaque fois qu'il y a une cellule vide. Étant donné que VRAI équivaut à 1 et FAUX équivaut à 0 dans les calculs, les cellules vides ne sont pas comptées car le numérateur est 0 (FAUX).
Maintenant que nous avons le squelette de base de la formule prêt, nous pouvons aller plus loin et compter différents types de données.
Comment compter les valeurs uniques dans Excel qui sont du texte
Nous utiliserons le même concept discuté ci-dessus pour créer la formule qui ne comptera que les valeurs de texte uniques.
Voici la formule qui comptera les valeurs de texte uniques dans Excel :
=SUMPRODUCT((ISTEXT(NAMES)/COUNTIF(NAMES,NAMES&””)))
Tout ce que nous avons fait est d'utiliser la formule ISTEXT(NAMES) comme numérateur. Il renvoie TRUE lorsque la cellule contient du texte et FALSE si ce n'est pas le cas. Il ne comptera pas les cellules vides, mais comptera les cellules qui ont une chaîne vide ("").
Comment compter les valeurs uniques dans Excel qui sont numériques
Voici la formule qui comptera les valeurs numériques uniques dans Excel
=SUMPRODUCT((ISNUMBER(NAMES))/COUNTIF(NAMES,NAMES&””))
Ici, nous utilisons ISNUMBER(NAMES) comme numérateur. Il renvoie TRUE lorsque la cellule contient un type de données numérique et FALSE si ce n'est pas le cas. Il ne compte pas les cellules vides.
