Exceller

Compter des valeurs uniques avec des critères

Count Unique Values With Criteria

Formule Excel : compter les valeurs uniques avec des critèresFormule générique |_+_| Sommaire

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 dans H7 est :



= SUM (--( LEN ( UNIQUE ( FILTER (range,criteria,'')))>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 tableaux dynamiques , disponible uniquement en Excel 365 . Avec une ancienne version d'Excel, vous pouvez utiliser formules alternatives plus complexes .





Explication

À la base, 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 FILTRE est utilisé pour appliquer des critères et extraire uniquement les noms associés au projet 'Omega' :



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

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

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

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

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

ce qui donne un nouveau tableau comme celui-ci :

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

À 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 :

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

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

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

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

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

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

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

Enfin, nous additionnons les résultats avec les Fonction SOMME :

comment appliquer un style de titre dans Excel
 
--({TRUETRUETRUE}) // returns {111}

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

 
= SUM ({111}) // returns 3

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

Compte unique avec plusieurs critères

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

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

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

inférieur ou égal à Excel

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

COUNTA

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

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

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 des alternatives de tableaux dynamiques, voir : Alternatives aux formules de tableau dynamique .

Formules de tableaux dynamiques sont disponibles en Bureau 365 seul. Auteur Dave Bruns


^