Exceller

Compter les valeurs uniques avec des critères

Count Unique Values With Criteria

Formule Excel: compter les valeurs uniques avec des critèresFormule générique
= SUM (--( LEN ( UNIQUE ( FILTER (range,criteria,'')))>0))
Résumé

Pour compter des valeurs uniques avec une ou plusieurs conditions, vous pouvez utiliser une formule basée sur UNIQUE et FILTRE . Dans l'exemple illustré, la formule en H7 est:

 
= SUM (--( LEN ( UNIQUE ( FILTER (B6:B15,C6:C15=H6,'')))>0))

qui renvoie 3, car il y a trois noms uniques dans B6: B15 associés au projet Omega.



Remarque: cette formule nécessite Formules de matrice dynamique , disponible uniquement en Excel 365 . Avec une ancienne version d'Excel, vous pouvez utiliser formules alternatives plus complexes .



Explication

Au fond, cette formule utilise la fonction UNIQUE pour extraire des valeurs uniques, et la fonction FILTER applique des critères.

lequel des éléments suivants est utilisé pour sélectionner des feuilles adjacentes?

Travaillant de l'intérieur vers l'extérieur, le Fonction FILTER permet d'appliquer des critères et d'extraire uniquement les noms associés au projet 'Omega':



 
 FILTER (B6:B15,C6:C15=H6) // Omega names only

Le résultat de FILTER est un déployer comme ça:

 
{'Jim''Jim''Carl''Sue''Carl'}

Ensuite, le Fonction UNIQUE est utilisé pour supprimer les doublons:

 
 UNIQUE ({'Jim''Jim''Carl''Sue''Carl'})

ce qui donne un nouveau tableau comme celui-ci:



 
{'Jim''Carl''Sue'} // after UNIQUE

À ce stade, nous avons une liste unique de noms associés à Omega, et nous avons juste besoin de les compter. Pour les raisons expliquées ci-dessous, nous le faisons avec la fonction LEN et la fonction SUM. Pour clarifier les choses, nous allons d'abord réécrire la formule pour inclure la liste unique:

 
= SUM (--( LEN ({'Jim''Carl''Sue'})>0))

Le Fonction LEN obtient la longueur de chaque élément de la liste et renvoie un tableau de longueurs:

 
 LEN ({'Jim''Carl''Sue'}) // returns {343}

Ensuite, nous vérifions si les longueurs sont supérieures à zéro:

 
 LEN ({343)>0 // returns {TRUETRUETRUE}

Et utilisez un double négation pour forcer les valeurs VRAI et FAUX à 1 et 0:

 
--({TRUETRUETRUE}) // returns {111}

Enfin, nous additionnons les résultats avec le Fonction SUM :

comment appliquer un style de titre dans Excel
 
= SUM ({111}) // returns 3

Ce tableau est livré directement à la fonction COUNTA, qui renvoie un décompte final:

 
= COUNTA ({'Jim''Carl''Sue'}) // returns 3

Notez que parce que nous vérifions la longueur de chaque élément retourné par UNIQUE, les cellules vides ou vides qui répondent aux critères sont ignorées. Cette formule est dynamique et recalculera immédiatement si les données source sont modifiées.

Compter unique avec plusieurs critères

Pour compter des valeurs uniques en fonction de plusieurs critères, vous pouvez étendre la logique «inclure» à l'intérieur de FILTER. Par exemple, pour compter les noms uniques pour le projet Omega en juin uniquement, utilisez:

 
= SUM (--( LEN ( UNIQUE ( FILTER (B6:B15,(C6:C15=H6)*(D6:D15='june'))))>0))

Ceci est un exemple d'utilisation logique booléenne pour appliquer plus d'une condition. L'approche est expliqué plus en détail ici .

inférieur ou égal à Excel

Pour plus de détails, regardez cette vidéo de formation: Comment filtrer avec plusieurs critères .

COUNTA

Il est possible d'écrire une formule plus simple qui répond sur le Fonction COUNTA . Cependant, une mise en garde importante est que COUNTA renvoie 1 lorsqu'il n'y a pas de valeurs correspondantes. Cela est dû au fait que la fonction FILTER renvoie une erreur lorsqu'aucune donnée ne correspond aux critères et que cette erreur finit par être comptée par la fonction COUNTA. La formule de base COUNTA ressemble à ceci:

 
= COUNTA ( UNIQUE ( FILTER (B6:B15,C6:C15=H6)))

Encore une fois, cette formule renverra 1 lorsqu'il n'y a pas de données correspondantes. Il comprendra également des cellules vides répondant aux critères. La formule basée sur LEN et SUM est une meilleure option.

Pas de tableaux dynamiques

Si vous utilisez une ancienne version d'Excel sans prise en charge des tableaux dynamiques, vous pouvez utiliser un formule plus complexe . Pour une discussion plus générale sur les alternatives de tableau dynamique, voir: Alternatives aux formules de matrice dynamique .

Formules de matrice dynamique sont disponibles en Bureau 365 seul. Auteur Dave Bruns


^