Formule générique
=SUMPRODUCT(1/COUNTIF(data,data))
Sommaire
Pour compter le nombre de valeurs uniques dans une plage de cellules, vous pouvez utiliser une formule basée sur les fonctions COUNTIF et SUMPRODUCT. Dans l'exemple, la formule en F6 est:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Explication
En travaillant de l'intérieur vers l'extérieur, COUNTIF est configuré sur des valeurs dans la plage B5: B14, en utilisant toutes ces mêmes valeurs comme critères:
COUNTIF(B5:B14,B5:B14)
Comme nous fournissons 10 valeurs pour les critères, nous récupérons un tableau avec 10 résultats comme celui-ci:
(3;3;3;2;2;3;3;3;2;2)
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.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Toutes les valeurs qui se produisent 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.
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&""))
En 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 inclut 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 nombre de cellules vides en rendant le numérateur nul pour les comptages 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 les valeurs numériques et une pour les valeurs textuelles.
