Exceller

Compter des 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 |_+_| Sommaire

Pour compter des valeurs de texte uniques dans une plage avec des critères, vous pouvez utiliser une formule matricielle basée sur le LA FRÉQUENCE et RENCONTRE les fonctions. Dans l'exemple illustré, la formule en G6 est :





{= SUM (--( FREQUENCY ( IF (criteria, MATCH (vals,vals,0)), ROW (vals)- ROW (vals.first)+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 saisi avec control + shift + enter.





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 la FRÉQUENCE pour compter les valeurs numériques dérivées de 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 :

 
{= SUM (--( FREQUENCY ( IF (C5:C11=G5, MATCH (B5:B11,B5:B11,0)), ROW (B5:B11)- ROW (B5)+1)>0))}

Le résultat de MATCH est un tableau comme celui-ci :



 
 MATCH (B5:B11,B5:B11,0)

Parce que MATCH renvoie toujours la position du premier correspondent, 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 en tant que numéro 1.

En dehors de la fonction MATCH, le fonction SI est utilisé pour appliquer des critères, ce qui implique dans ce cas de tester si le projet est « oméga » (à partir de la cellule G5) :

 
{1131167}

La fonction SI 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 :

 
 IF (C5:C11=G5 // filter on 'omega'

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

 
{FALSEFALSEFALSE1167} // after filtering

Cela crée un tableau comme celui-ci :

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

qui devient le tableau_bins argument dans FILTER. A ce stade, nous avons :

 
{12345678910}

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é, la FRÉQUENCE retournera zéro. Le résultat de FREQUENCY est un tableau comme celui-ci :

 
 FREQUENCY ({FALSEFALSEFALSE1167},{1234567})

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

comment trouver des moyennes dans Excel

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

 
{20000110} // result from FREQUENCY

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

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

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

 
= SUM (--({TRUEFALSEFALSEFALSEFALSETRUETRUEFALSE}))

Finalement, le 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 de 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 ({10000110})

Avec deux critères

Si vous avez deux critères, vous pouvez étendre la logique de la formule en ajoutant un autre SI imbriqué :

 
{= SUM (--( FREQUENCY ( IF (B5:B11'', IF (C5:C11=G5, MATCH (B5:B11,B5:B11,0))), ROW (B5:B11)- ROW (B5)+1)>0))}

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

Avec la logique booléenne

Avec logique booléenne , vous pouvez réduire FI imbriqués :

 
{= SUM (--( FREQUENCY ( IF (c1, IF (c2, 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 celui de Mike Givin excellent livre sur les formules matricielles, Contrôle-Maj-Entrée. Auteur Dave Bruns


^