Formule générique
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
Sommaire
Pour compter les valeurs de texte uniques dans une plage, vous pouvez utiliser une formule qui utilise plusieurs fonctions: FREQUENCY, MATCH, ROW et SUMPRODUCT.Dans l'exemple illustré, la formule en F5 est:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
qui renvoie 4, car il y a 4 noms uniques dans B5: B14.
Remarque: une autre façon de compter les valeurs uniques consiste à utiliser la fonction COUNTIF. Il s'agit d'une formule beaucoup plus simple, mais elle peut s'exécuter lentement sur de grands ensembles de données. Avec Excel 365, vous pouvez utiliser une formule plus simple et plus rapide basée sur UNIQUE.
Explication
Cette formule est plus compliquée qu'une formule similaire qui utilise FREQUENCY pour compter des valeurs numériques uniques car FREQUENCY ne fonctionne pas avec des valeurs non numériques. Par conséquent, une grande partie de la formule transforme simplement les données non numériques en données numériques que FREQUENCY peut gérer.
En travaillant de l'intérieur vers l'extérieur, la fonction MATCH est utilisée pour obtenir la position de chaque élément qui apparaît dans les données:
MATCH(B5:B14,B5:B14,0)
Le résultat de MATCH est un tableau comme celui-ci:
(1;1;1;4;4;6;6;6;9;9)
Étant donné que MATCH renvoie toujours la position de la première correspondance, les valeurs qui apparaissent plusieurs fois dans les données renvoient la même position. Par exemple, parce que "Jim" apparaît 3 fois dans la liste, il apparaît dans ce tableau 3 fois sous le numéro 1.
Ce tableau est introduit dans FREQUENCY en tant qu'argument data_array . L' argument bins_array est construit à partir de cette partie de la formule:
ROW(B5:B14)-ROW(B5)+1)
qui construit une liste séquentielle de nombres pour chaque valeur dans les données:
(1;2;3;4;5;6;7;8;9;10)
À ce stade, la FRÉQUENCE est configurée comme ceci:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
FREQUENCY renvoie un tableau de nombres qui indiquent un nombre pour chaque nombre du tableau de données, organisé par bin. Lorsqu'un nombre a déjà été compté, FREQUENCY renvoie zéro. Il s'agit d'une caractéristique clé dans le fonctionnement de cette formule. Le résultat de FREQUENCY est un tableau comme celui-ci:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Remarque: FREQUENCY renvoie toujours un tableau avec un élément de plus que le bins_array .
Nous pouvons maintenant réécrire la formule comme ceci:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
Ensuite, nous vérifions les valeurs supérieures à zéro (> 0), ce qui convertit les nombres en VRAI ou FAUX, puis utilisons un double négatif (-) pour convertir les valeurs VRAI et FAUX en 1 et 0. Maintenant nous avons:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
Enfin, SUMPRODUCT ajoute simplement les nombres et renvoie le total, qui dans ce cas est de 4.
Gestion des cellules vides
Les cellules vides de la plage entraîneront le renvoi de la formule d'une erreur # N / A. Pour gérer les cellules vides, vous pouvez utiliser une formule matricielle plus compliquée qui utilise la fonction IF pour filtrer les valeurs vides:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Remarque: l'ajout de IF en fait une formule matricielle qui nécessite control-shift-enter.
Pour plus d'informations, consultez cette page.
Autres façons de compter des valeurs uniques
Si vous avez Excel 365, vous pouvez utiliser la fonction UNIQUE pour compter les valeurs uniques avec une formule beaucoup plus simple.
Un tableau croisé dynamique est également un excellent moyen de compter des valeurs uniques.
