Exceller

Compter les valeurs de texte uniques dans une plage

Count Unique Text Values Range

Formule Excel: compter les valeurs de texte uniques dans une plageFormule générique
= SUMPRODUCT (--( FREQUENCY ( MATCH (data,data,0), ROW (data)- ROW (data.firstcell)+1)>0))
Résumé

Pour compter des valeurs de texte uniques dans une plage, vous pouvez utiliser une formule qui utilise plusieurs fonctions: LA FRÉQUENCE , CORRESPONDRE , RANGÉE et SOMMEPROD 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 un formule plus simple et plus rapide basé 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 déployer comme ça:

 
{1114466699}

Parce que MATCH renvoie toujours la position du première match, les valeurs qui apparaissent plus d'une 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 que data_array argument.Le bins_array l'argument est construit à partir de cette partie de la formule:



utiliser Excel pour trouver l'écart type
 
 ROW (B5:B14)- ROW (B5)+1)

qui construit un liste séquentielle de nombres pour chaque valeur des données:

 
{12345678910}

À ce stade, la FREQUENCE est configurée comme ceci:

 
 FREQUENCY ({1114466699},{12345678910})

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 renverra zéro. C'est une caractéristique clé dans le fonctionnement de cette formule. Le résultat de FREQUENCY est un tableau comme celui-ci:

exceller plusieurs graphiques à partir d'un tableau croisé dynamique
 
{30020300200} // 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 (--({30020300200}>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 ({10010100100})

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 un formule matricielle qui nécessite control-shift-enter.

Pour plus d'informations, voir cette page .

De Mike Givin excellent livre sur les formules matricielles, Ctrl-Maj-Entrée.

Autres façons de compter des valeurs uniques

Si vous avez Excel 365, vous pouvez utiliser le Fonction UNIQUE à compter les valeurs uniques avec une formule beaucoup plus simple.

À tableau croisé dynamique est également un excellent moyen de compter des valeurs uniques.

Auteur Dave Bruns


^