Exceller

Compter les valeurs uniques dans une plage avec COUNTIF

Count Unique Values Range With Countif

Formule Excel: compter les valeurs uniques dans une plage avec COUNTIFFormule générique
= SUMPRODUCT (1/ COUNTIF (data,data))
Résumé

Pour compter le nombre de valeurs uniques dans une plage de cellules, vous pouvez utiliser une formule basée sur le COUNTIF et SOMMEPROD les fonctions. Dans l'exemple, la formule en F6 est:

 
= SUMPRODUCT (1/ COUNTIF (B5:B14,B5:B14))
Avec Excel 365 , vous pouvez utiliser un formule plus simple et plus rapide basé sur UNIQUE . Explication

En travaillant de l'intérieur vers l'extérieur, COUNTIF est configuré sur des valeurs dans la plage B5: B14, en utilisant tous ces même valeurs comme critères:



 
 COUNTIF (B5:B14,B5:B14)

Parce que nous fournissons 10 valeurs pour les critères, nous récupérons un déployer avec 10 résultats comme celui-ci:



 
{3332233322}

Chaque nombre représente un décompte - «Jim» apparaît 3 fois, «Sue» apparaît 2 fois, et ainsi de suite.

Ce tableau est configuré comme un diviseur avec 1 comme numérateur. Après division, nous obtenons un autre tableau:



 
{0.3333333333333330.3333333333333330.3333333333333330.50.50.3333333333333330.3333333333333330.3333333333333330.50.5}

Toutes les valeurs qui surviennent en une seule fois dans la plage apparaîtront sous la forme de 1, mais les valeurs qui se produisent plusieurs fois apparaîtront sous forme de valeurs fractionnaires correspondant au multiple. (c'est-à-dire qu'une valeur qui apparaît 4 fois dans les données générera 4 valeurs = 0,25).

Enfin, la fonction SUMPRODUCT additionne toutes les valeurs du tableau et renvoie le résultat.

comment ajouter et soustraire des dates dans Excel

Gestion des cellules vides

Une façon de gérer les cellules vides ou vides consiste à ajuster la formule comme suit:



 
= SUMPRODUCT (1/ COUNTIF (data,data&''))

Par concaténant une chaîne vide ('') aux données, nous empêchons les zéros de se retrouver dans le tableau créé par COUNTIF lorsqu'il y a des cellules vides dans les données. Ceci est important, car un zéro dans le diviseur provoquera le renvoi d'une erreur # DIV / 0 par la formule. Cela fonctionne car l'utilisation d'une chaîne vide ('') pour les critères comptera les cellules vides.

Cependant, bien que cette version de la formule ne génère pas d'erreur # DIV / 0 avec des cellules vides, elle volonté inclure des cellules vides dans le décompte. Si vous souhaitez exclure les cellules vides du décompte, utilisez:

 
= SUMPRODUCT ((data'')/ COUNTIF (data,data&''))

Cela a pour effet d'annuler le décompte des cellules vides en rendant le numérateur nul pour les décomptes associés.

Performance lente?

Il s'agit d'une formule cool et élégante, mais elle calcule beaucoup plus lentement que les formules qui utilisent FREQUENCY pour compter les valeurs uniques. Pour des ensembles de données plus volumineux, vous souhaiterez peut-être basculer vers une formule basée sur la fonction FREQUENCE. Voici une formule pour valeurs numériques et un pour valeurs de texte .

Auteur Dave Bruns


^