Formule générique
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Sommaire
Pour compter des valeurs de texte uniques dans une plage avec des critères, vous pouvez utiliser une formule matricielle basée sur les fonctions FREQUENCY et MATCH. Dans l'exemple illustré, la formule en G6 est:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
qui renvoie 3, puisque trois personnes différentes ont travaillé sur le projet Omega.
Remarque: il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée.
Explication
Il s'agit d'une formule complexe qui utilise FREQUENCY pour compter les valeurs numériques dérivées avec la fonction MATCH. En travaillant de l'intérieur vers l'extérieur, la fonction MATCH est utilisée pour obtenir la position de chaque valeur qui apparaît dans les données:
MATCH(B5:B11,B5:B11,0)
Le résultat de MATCH est un tableau comme celui-ci:
(1;1;3;1;1;6;7)
Étant donné que MATCH renvoie toujours la position de la première correspondance, les valeurs qui apparaissent plusieurs fois dans les données renvoient la même position. Par exemple, parce que "Jim" apparaît 4 fois dans la liste, il apparaît dans ce tableau 4 fois sous le numéro 1.
En dehors de la fonction MATCH, la fonction IF est utilisée pour appliquer des critères, ce qui dans ce cas consiste à tester si le projet est "oméga" (à partir de la cellule G5):
IF(C5:C11=G5 // filter on "omega"
La fonction IF agit comme un filtre, ne laissant passer les valeurs de MATCH que si elles sont associées à "omega". Le résultat est un tableau comme celui-ci:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
Le tableau filtré est livré directement à la fonction FREQUENCY en tant qu'argument data_array . Ensuite, la fonction ROW est utilisée pour créer une liste séquentielle de nombres pour chaque valeur dans les données:
ROW(B3:B12)-ROW(B3)+1
Cela crée un tableau comme celui-ci:
(1;2;3;4;5;6;7;8;9;10)
qui devient l' argument bins_array dans FILTER. À ce stade, nous avons:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREQUENCY renvoie un tableau de nombres qui indiquent un nombre pour chaque valeur du tableau de données, organisé par bin. Lorsqu'un nombre a déjà été compté, FREQUENCY renvoie zéro. Le résultat de FREQUENCY est un tableau comme celui-ci:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Remarque: FREQUENCY renvoie toujours un tableau avec un élément de plus que le bins_array .
À ce stade, nous pouvons réécrire la formule comme ceci:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Nous vérifions les valeurs supérieures à zéro, ce qui convertit les nombres en TRUE ou FALSE:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Ensuite, nous utilisons un double négatif pour forcer les valeurs logiques à 1 et 0:
=SUM((1;0;0;0;0;1;1;0))
Enfin, la fonction SOMME renvoie 3 comme résultat final.
Remarque: il s'agit d'une formule matricielle et doit être saisie à l'aide de Ctrl + Maj + Entrée.
Gestion des cellules vides dans la plage
Si des cellules de la plage sont vides, vous devrez ajuster la formule pour empêcher les cellules vides d'être transmises à la fonction MATCH, ce qui générera une erreur. Vous pouvez le faire en ajoutant une autre fonction IF imbriquée pour vérifier les cellules vides:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
Avec deux critères
Si vous avez deux critères, vous pouvez étendre la logique de la formule en ajoutant un autre IF imbriqué:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Où c1 = critère1, c2 = critère2 et vals = la plage de valeurs.
Avec logique booléenne
Avec la logique booléenne, vous pouvez réduire les IF imbriqués:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
Cela facilite l'ajout et la gestion de critères supplémentaires.
