=NBVAL(UNIQUE(A2:A9)) compte combien de valeurs différentes contient A2:A9. UNIQUE renvoie chaque valeur une fois, et NBVAL (COUNTA en anglais) compte cette liste. Il faut Excel 2021 ou Microsoft 365 ; les versions plus anciennes sont traitées plus bas. Le tableau affiche les formules sous leur forme anglaise, avec des virgules, mais vous pouvez aussi les taper comme dans un Excel français, avec des points-virgules : =NBVAL(UNIQUE(A2:A9)).
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Unique list | Count | |
| 2 | Ana | Ana | 5 | |
| 3 | Ben | Ben | ||
| 4 | Ana | Cara | ||
| 5 | Cara | Dan | ||
| 6 | Ben | Eva | ||
| 7 | Dan | |||
| 8 | Ana | |||
| 9 | Eva |
=NBVAL(UNIQUE(A2:A9))Huit commandes viennent de cinq clients. C2 déverse la liste des noms renvoyée par UNIQUE pour que vous voyiez ce qui est compté, et D2 la compte sans avoir besoin de la liste sur la feuille. Mettez Ana en A9 et le compte tombe à 4 ; tapez un nouveau nom et il augmente.
UNIQUE ignore la casse : Ana et ana comptent pour un seul client.
Compter les valeurs uniques dans un Excel plus ancien
Excel 2019 et les versions antérieures n'ont pas UNIQUE. La formule classique est :
=SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9))
Dans un Excel français : =SOMMEPROD(1/NB.SI(A2:A9;A2:A9)).
NB.SI (COUNTIF) avec toute la plage comme critère renvoie, pour chaque ligne, le nombre de fois où la valeur de cette ligne apparaît. Un nom présent 3 fois obtient 3 sur chacune de ses lignes : 1/3 est donc ajouté trois fois et le nom totalise exactement 1. La colonne B affiche le compte de chaque ligne et la colonne C la fraction.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Customer | Times | 1/Times | Count | |
| 2 | Ana | 3 | 0.33 | 5 | |
| 3 | Ben | 2 | 0.50 | 5.00 | |
| 4 | Ana | 3 | 0.33 | ||
| 5 | Cara | 1 | 1.00 | ||
| 6 | Ben | 2 | 0.50 | ||
| 7 | Dan | 1 | 1.00 | ||
| 8 | Ana | 3 | 0.33 | ||
| 9 | Eva | 1 | 1.00 |
=SOMMEPROD(1/NB.SI(A2:A9;A2:A9))Les trois lignes d'Ana ajoutent chacune 0.33, les deux lignes de Ben chacune 0.50, et les trois noms uniques ajoutent 1 chacun : 5 au total, comme la SOMME de la colonne d'aide. Sur des dizaines de milliers de lignes, cette formule est lente, car NB.SI parcourt toute la plage une fois par ligne ; UNIQUE n'a pas ce coût.
Distinctes ou uniques : les valeurs présentes une seule fois
"Unique" désigne deux comptages différents. Celui ci-dessus compte les valeurs distinctes : chaque nom une fois. L'autre compte les valeurs présentes exactement une fois, comme les clients qui n'ont commandé qu'une seule fois. UNIQUE le fait avec son troisième argument, exactly_once, mis à VRAI.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Count | Result | |
| 2 | Ana | Distinct | 5 | |
| 3 | Ben | Exactly once | 3 | |
| 4 | Ana | Exactly once, older Excel | 3 | |
| 5 | Cara | |||
| 6 | Ben | |||
| 7 | Dan | |||
| 8 | Ana | |||
| 9 | Eva |
=NBVAL(UNIQUE(A2:A9;;VRAI))Cinq clients distincts, mais seuls trois d'entre eux, Cara, Dan et Eva, ont commandé une fois. La version pour les anciens Excel compte les lignes dont le NB.SI vaut exactement 1. Si chaque valeur se répète, UNIQUE avec exactly_once renvoie #CALC! (même nom en français et en anglais) et NBVAL compte cette erreur comme 1 ; la version SOMMEPROD (SUMPRODUCT) donne 0.
Compter les valeurs uniques avec une condition
Pour compter les clients différents d'une région, filtrez d'abord les lignes, puis comptez ce qui reste. FILTRE (FILTER) garde les lignes North et UNIQUE supprime les répétitions.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Customer | Region | Region | Customers | |
| 2 | Ana | North | North | 3 | |
| 3 | Ben | South | South | 3 | |
| 4 | Ana | North | North, older Excel | 3 | |
| 5 | Cara | North | |||
| 6 | Ben | North | |||
| 7 | Dan | South | |||
| 8 | Ana | North | |||
| 9 | Eva | South |
=NBVAL(UNIQUE(FILTRE(A2:A9;B2:B9=D2)))North compte cinq commandes de trois clients : Ana, Cara et Ben. E3 compte South de la même façon. E4 est la version pour Excel 2019 et les versions antérieures : NB.SI.ENS (COUNTIFS) compte chaque paire client et région, et la condition ne garde que les fractions North.
Si aucune ligne ne correspond, FILTRE renvoie #CALC!, et NBVAL compte cette erreur comme une valeur : tapez West en D2 et E2 affiche 1, pas 0. Entourer la formule de SIERREUR n'aide pas, car NBVAL ne renvoie pas d'erreur. Comptez plutôt les lignes du résultat, ce qui transmet bien l'erreur : =SIERREUR(LIGNES(UNIQUE(FILTRE(A2:A9;B2:B9="West")));0) renvoie 0.
Compter les valeurs uniques sans les cellules vides
Une cellule vide dans la plage devient une "valeur" de plus. UNIQUE la renvoie sous forme de 0 et NBVAL compte ce 0 : pour Ana, une cellule vide, Ben, Ana, une cellule vide, Cara et Ben, Excel donne :
=COUNTA(UNIQUE(A2:A8)) 4 three names plus the 0 for the empty cells
Dans l'ancienne formule, une ligne vide fait renvoyer 0 à NB.SI, et 1/0 donne #DIV/0!. Retirez d'abord les cellules vides :
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Formula | Count | |
| 2 | Ana | Skip blanks | 3 | |
| 3 | Older Excel | 3 | ||
| 4 | Ben | |||
| 5 | Ana | |||
| 6 | ||||
| 7 | Cara | |||
| 8 | Ben |
=NBVAL(UNIQUE(FILTRE(A2:A8;A2:A8<>"")))Les deux formules comptent les trois clients. FILTRE avec A2:A8<>"" retire les cellules vides avant que UNIQUE ne les voie. Dans l'ancienne formule, A2:A8&"" transforme chaque cellule vide en chaîne vide pour que NB.SI ne renvoie jamais 0, et (A2:A8<>"") donne à ces lignes un poids de 0.
Exercice : compter les produits
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Product | Count | Result | |
| 2 | 1001 | Apple | Products | ||
| 3 | 1002 | Pear | |||
| 4 | 1003 | Apple | |||
| 5 | 1004 | Plum | |||
| 6 | 1005 | Pear | |||
| 7 | 1006 | Apple | |||
| 8 | 1007 | Plum | |||
| 9 | 1008 | Fig |
À vous : Comptez combien de produits différents apparaissent dans B2:B9. Écrivez la formule en E2.
Quelle formule pour votre Excel
| Compter | Excel 365 / 2021 | Excel 2019 et versions antérieures |
|---|---|---|
| Les valeurs distinctes | =NBVAL(UNIQUE(A2:A9)) | =SOMMEPROD(1/NB.SI(A2:A9;A2:A9)) |
| Les valeurs présentes une fois | =NBVAL(UNIQUE(A2:A9;;VRAI)) | =SOMMEPROD(--(NB.SI(A2:A9;A2:A9)=1)) |
| Les valeurs distinctes, avec une condition | =NBVAL(UNIQUE(FILTRE(A2:A9;B2:B9="North"))) | =SOMMEPROD((B2:B9="North")/NB.SI.ENS(A2:A9;A2:A9;B2:B9;B2:B9)) |
| Les valeurs distinctes, sans les cellules vides | =NBVAL(UNIQUE(FILTRE(A2:A9;A2:A9<>""))) | =SOMMEPROD((A2:A9<>"")/NB.SI(A2:A9;A2:A9&"")) |
Dans un tableau croisé dynamique, la synthèse "Nombre distinct" fait le même travail sans formule, mais seulement si le tableau croisé a été créé avec la case "Ajouter ces données au modèle de données" cochée. Pour supprimer les répétitions plutôt que les compter, voir supprimer les doublons.
Questions fréquentes
Comment compter les valeurs uniques dans Excel ?
Dans Excel 365 ou 2021, utilisez =NBVAL(UNIQUE(A2:A9)) : UNIQUE liste chaque valeur une fois et NBVAL compte la liste. Dans les versions plus anciennes, utilisez =SOMMEPROD(1/NB.SI(A2:A9;A2:A9)).
Comment compter les valeurs qui n'apparaissent qu'une fois ?
Mettez le troisième argument de UNIQUE, exactly_once, à VRAI : =NBVAL(UNIQUE(A2:A9;;VRAI)). Pour Ana, Ana, Ben, le résultat est 1, car seul Ben apparaît une fois. Dans Excel 2019 et les versions antérieures, utilisez =SOMMEPROD(--(NB.SI(A2:A9;A2:A9)=1)).
Comment compter les valeurs uniques avec une condition ?
Filtrez d'abord, puis comptez : =NBVAL(UNIQUE(FILTRE(A2:A9;B2:B9="North"))) compte les clients différents des lignes North. Si aucune ligne ne correspond, NBVAL compte l'erreur #CALC! de FILTRE comme 1 ; quand cela peut arriver, utilisez =SIERREUR(LIGNES(UNIQUE(FILTRE(A2:A9;B2:B9="North")));0).
Comment compter les valeurs uniques en ignorant les cellules vides ?
Retirez les cellules vides avant UNIQUE : =NBVAL(UNIQUE(FILTRE(A2:A9;A2:A9<>""))). Dans un Excel plus ancien, =SOMMEPROD((A2:A9<>"")/NB.SI(A2:A9;A2:A9&"")) les ignore.
