Exceller

Compter des 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 |_+_| Sommaire

Pour compter des valeurs de texte uniques dans une plage, vous pouvez utiliser une formule qui utilise plusieurs fonctions : LA FRÉQUENCE , RENCONTRE , LIGNE et SOMMEPRODUIT .Dans l'exemple illustré, la formule en F5 est :





= SUMPRODUCT (--( FREQUENCY ( MATCH (data,data,0), ROW (data)- ROW (data.firstcell)+1)>0))

qui renvoie 4, puisqu'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 la FRÉQUENCE pour compter les valeurs numériques uniques car FREQUENCY ne fonctionne pas avec des valeurs non numériques. En conséquence, 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 :



 
= SUMPRODUCT (--( FREQUENCY ( MATCH (B5:B14,B5:B14,0), ROW (B5:B14)- ROW (B5)+1)>0))

Le résultat de MATCH est un déployer comme ça:

 
 MATCH (B5:B14,B5:B14,0)

Parce que MATCH renvoie toujours la position du premier correspondent, 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 3 fois dans ce tableau en tant que numéro 1.

Ce tableau est introduit dans FREQUENCY comme le tableau_données argument.Les tableau_bins L'argument est construit à partir de cette partie de la formule :

utiliser Excel pour trouver l'écart type
 
{1114466699}

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

 
 ROW (B5:B14)- ROW (B5)+1)

À ce stade, la FRÉQUENCE est configurée comme ceci :

 
{12345678910}

FREQUENCY renvoie un tableau de nombres qui indiquent un nombre pour chaque nombre dans le tableau de données, organisé par bin. Lorsqu'un nombre a déjà été compté, la FRÉQUENCE retournera 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
 
 FREQUENCY ({1114466699},{12345678910})

Remarque : FREQUENCY renvoie toujours un tableau avec un élément de plus que le tableau_bins .

Nous pouvons maintenant réécrire la formule comme ceci :

 
{30020300200} // output from FREQUENCY

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 (--({30020300200}>0))

Enfin, SUMPRODUCT additionne simplement les nombres et renvoie le total, qui dans ce cas est 4.

Gestion des cellules vides

Les cellules vides de la plage entraîneront le renvoi d'une erreur #N/A par la formule. Pour gérer les cellules vides, vous pouvez utiliser une formule matricielle plus compliquée qui utilise la fonction SI pour filtrer les valeurs vides :

 
= SUMPRODUCT ({10010100100})

Remarque : l'ajout de IF en fait un formule matricielle qui nécessite contrôle-shift-enter.

Pour plus d'informations, voir cette page .

De celui de Mike Givin excellent livre sur les formules matricielles, Contrôle-Maj-Entrée.

Autres façons de compter les 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


^