Exceller

Compter les valeurs de texte uniques avec des critères

Count Unique Text Values With Criteria

Formule Excel: compter les valeurs de texte uniques avec des critèresFormule générique
{= SUM (--( FREQUENCY ( IF (criteria, MATCH (vals,vals,0)), ROW (vals)- ROW (vals.first)+1)>0))}
Résumé

Pour compter des valeurs de texte uniques dans une plage avec des critères, vous pouvez utiliser une formule matricielle basée sur la LA FRÉQUENCE et CORRESPONDRE les fonctions. 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'un formule matricielle et doit être entré avec Ctrl + Maj + Entrée.



Avec Excel 365 , vous pouvez utiliser un formule beaucoup plus simple basé sur Fonction UNIQUE . 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:



 
{1131167}

Parce que MATCH renvoie toujours la position du première match, les valeurs qui apparaissent plus d'une 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, le Fonction IF est utilisé pour appliquer des critères, ce qui dans ce cas implique de 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:



 
{FALSEFALSEFALSE1167} // after filtering

Le tableau filtré est livré directement à la fonction FREQUENCE en tant que data_array argument. Ensuite, le Fonction ROW est utilisé pour construire un liste séquentielle de nombres pour chaque valeur des données:

 
 ROW (B3:B12)- ROW (B3)+1

Cela crée un tableau comme celui-ci:

 
{12345678910}

qui devient le bins_array argument dans FILTER. À ce stade, nous avons:

 
 FREQUENCY ({FALSEFALSEFALSE1167},{1234567})

FREQUENCY renvoie un tableau de nombres indiquant un décompte pour chaque valeur du tableau de données, organisé par bin. Lorsqu'un nombre a déjà été compté, FREQUENCY renverra zéro. Le résultat de FREQUENCY est un tableau comme celui-ci:

 
{20000110} // result from FREQUENCY

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

comment trouver des moyennes dans Excel

À ce stade, nous pouvons réécrire la formule comme ceci:

 
= SUM (--({20000110}>0))

Nous vérifions les valeurs supérieures à zéro, ce qui convertit les nombres en TRUE ou FALSE:

 
= SUM (--({TRUEFALSEFALSEFALSEFALSETRUETRUEFALSE}))

Ensuite, nous utilisons un double négation pour forcer les valeurs logiques à 1 et 0:

 
= SUM ({10000110})

Finalement, le Fonction SUM 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))}

c1 = critère1, c2 = critère2 et valse = la plage de valeurs.

Avec logique booléenne

Avec logique booléenne , vous pouvez réduire IF imbriquées :

 
{= 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.

Adapté de Mike Givin excellent livre sur les formules matricielles, Ctrl-Maj-Entrée. Auteur Dave Bruns


^