Formule générique
(=SUM(--(FREQUENCY(IF(criteria,values),values)>0)))
Sommaire
Pour compter des valeurs numériques uniques dans une plage, vous pouvez utiliser une formule basée sur les fonctions FREQUENCY, SUM et IF. Dans l'exemple illustré, les numéros d'employés apparaissent dans la plage B5: B14. La formule dans G6 est:
=SUM(--(FREQUENCY(IF(C5:C14="A",B5:B14),B5:B14)>0))
qui renvoie 2, car il y a 2 identifiants d'employés uniques dans le bâtiment A.
Remarque: il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée, sauf si vous utilisez Excel 365.
Explication
Remarque: Avant Excel 365, Excel n'avait pas de fonction dédiée pour compter les valeurs uniques. Cette formule montre une façon unique de compter les valeurs uniques, à condition qu'elles soient numériques. Si vous avez des valeurs de texte ou un mélange de texte et de nombres, vous devrez utiliser une formule plus compliquée.
La fonction Excel FREQUENCY renvoie une distribution de fréquence, qui est un tableau récapitulatif qui contient la fréquence des valeurs numériques, organisées en «classes». Nous l'utilisons ici comme un moyen détourné de compter des valeurs numériques uniques. Pour appliquer des critères, nous utilisons la fonction IF.
En travaillant de l'intérieur vers l'extérieur, nous filtrons d'abord les valeurs avec la fonction IF:
IF(C5:C14="A",B5:B14) // filter on building A
Le résultat de cette opération est un tableau comme celui-ci:
(905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE)
Notez que tous les identifiants du bâtiment B sont désormais FAUX. Ce tableau est livré directement à la fonction FREQUENCY en tant que data_array . Pour le bins_array , nous fournissons les identifiants eux-mêmes:
FREQUENCY((905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE),(905;905;905;905;773;773;801;963;963;963))
Avec cette configuration, FREQUENCY renvoie le tableau ci-dessous:
(4;0;0;0;2;0;0;0;0;0;0)
Le résultat est un peu énigmatique, mais la signification est 905 apparaît quatre fois et 773 apparaît deux fois. Les valeurs FALSE sont automatiquement ignorées.
FREQUENCY a une fonction spéciale qui renvoie automatiquement zéro pour tous les nombres qui sont déjà apparus dans le tableau de données, c'est pourquoi les valeurs sont nulles une fois qu'un nombre a été rencontré. C'est la fonctionnalité qui permet à cette approche de fonctionner.
Ensuite, chacune de ces valeurs est testée comme étant supérieure à zéro:
(4;0;0;0;2;0;0;0;0;0;0)>0
Le résultat est un tableau comme celui-ci:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Chaque TRUE de la liste représente un numéro unique dans la liste, et nous devons simplement additionner les valeurs TRUE avec SUM. Cependant, SUM n'additionnera pas les valeurs logiques dans un tableau, nous devons donc d'abord forcer les valeurs à 1 ou à zéro. Cela se fait avec le double négatif (-). Le résultat est un tableau de seulement 1 ou 0:
(1;0;0;0;1;0;0;0;0;0;0)
Enfin, SUM ajoute ces valeurs et renvoie le total, qui dans ce cas est de 2.
Critères multiples
Vous pouvez étendre la formule pour gérer plusieurs critères comme celui-ci:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),values),values)>0)))
