Compter les valeurs uniques dans Excel, et obtenir un nombre distinct dans un tableau croisé

Comptez les valeurs uniques dans Excel avec UNIQUE ou SOMMEPROD, ajoutez une condition et activez Nombre distinct dans un tableau croisé dynamique.

Fondateur, Glow Reports. , 3 min de lecture
  • Excel
  • Formules
  • Tableaux croisés dynamiques
Une pile de cartes colorées qui passe dans un entonnoir et ressort en une courte rangée de cartes alignées
Sur cette page
  1. Pourquoi Excel compte-t-il 8 alors qu'il y a 5 clients ?
  2. Comment compter les valeurs uniques dans Excel 365 ?
  3. Comment compter les valeurs uniques dans une ancienne version d'Excel ?
  4. Comment compter les valeurs uniques avec une condition ?
  5. Comment obtenir un nombre distinct dans un tableau croisé dynamique ?
  6. Comment compter les valeurs uniques dans Google Sheets ?
  7. Où se place Glow Reports ?
  8. Questions fréquentes

Vous voulez savoir combien de clients ont commandé le mois dernier. Vous créez un tableau croisé dynamique et vous placez la colonne des clients dans les valeurs. Il affiche 8. Vous savez qu'ils étaient 5. Nombre compte des lignes, et un client qui a commandé trois fois compte trois fois. Dans Excel 365, renvoie le nombre de valeurs uniques dans une seule cellule. Voici cette formule, celle des anciennes versions, le comptage avec une condition, l'option Nombre distinct cachée dans les tableaux croisés, et Google Sheets.

Pourquoi Excel compte-t-il 8 alors qu'il y a 5 clients ?

NBVAL et la synthèse Nombre d'un tableau croisé comptent des cellules : chaque répétition d'une valeur compte à nouveau. Pour compter chaque valeur une seule fois, il faut une formule qui retire d'abord les doublons, ou un tableau croisé construit sur le modèle de données.

Les exemples ci-dessous utilisent un export de commandes de huit lignes : le numéro de commande en colonne A, l'identifiant client en colonne B et le mois en colonne C.

CommandeClientMois
1001C01Janv
1002C02Janv
1003C01Janv
1004C03Févr
1005C02Févr
1006C01Févr
1007C04Févr
1008C05Mars

Huit commandes, cinq clients. C01 a commandé trois fois, C02 deux fois, et trois clients une seule fois. La page de Microsoft pose le problème de la même façon : dans une colonne qui contient 5, 6, 7 et 6, le résultat est de trois valeurs uniques Microsoft Support.

Comment compter les valeurs uniques dans Excel 365 ?

Placez UNIQUE dans NBVAL. UNIQUE renvoie la liste sans doublons et NBVAL compte cette liste. Avec les identifiants clients en B2 :B9, renvoie 5.

Clients uniques, 5
=NBVAL(UNIQUE(B2:B9))

La fonction UNIQUE renvoie une liste de valeurs uniques au sein d'une liste ou d'une plage Microsoft Support. Elle existe dans Excel pour Microsoft 365, Excel 2021, Excel 2024 et Excel pour le web. Excel 2019 et les versions antérieures renvoient #NOM?, utilisez alors la formule de la section suivante.

Les cellules vides demandent de l'attention. UNIQUE garde une cellule vide dans sa liste et NBVAL la compte : un export avec des clients non renseignés sort avec un client de trop. Filtrez d'abord les vides :

Clients uniques, cellules vides exclues
=NBVAL(UNIQUE(FILTRE(B2:B9;B2:B9<>"")))

UNIQUE a un troisième argument. Avec à VRAI, elle ne renvoie que les valeurs présentes une seule fois. renvoie 3 : les clients qui n'ont commandé qu'une fois. Ce chiffre ouvre une analyse du réachat, traitée dans comment calculer le taux de rétention client.

Comment compter les valeurs uniques dans une ancienne version d'Excel ?

Divisez 1 par le nombre d'apparitions de chaque valeur, puis additionnez. Avec SOMMEPROD et NB.SI, renvoie 5 dans toutes les versions d'Excel.

Clients uniques dans toutes les versions, 5
=SOMMEPROD(1/NB.SI(B2:B9;B2:B9))

NB.SI renvoie, pour chaque ligne, le nombre d'apparitions de son client : 3 sur chaque ligne de C01, 2 sur chaque ligne de C02, 1 pour les autres. 1 divisé par ces nombres donne trois fois 1/3 pour C01, deux fois 1/2 pour C02 et 1 pour chacun des autres. Chaque client totalise exactement 1, la somme donne donc le nombre de clients.

Une cellule vide fait renvoyer 0 à NB.SI, et la formule échoue avec #DIV/0!. Cette version ignore les vides :

Clients uniques dans toutes les versions, cellules vides exclues
=SOMMEPROD((B2:B9<>"")/NB.SI(B2:B9;B2:B9&""))

La formule compare chaque ligne avec toutes les autres et ralentit sur un gros export. Microsoft documente deux autres méthodes, l'une avec FREQUENCE et EQUIV, l'autre manuelle : DonnéesTrier et filtrerAvancé, puis la case « Extraction sans doublon » pour copier les valeurs distinctes ailleurs et les compter avec LIGNES Microsoft Support.

Les deux formules voient « C01 » et « C01 » suivi d'une espace comme deux clients différents. L'aide de Google sur sa propre fonction UNIQUE fait la même mise en garde sur le texte masqué comme les espaces en fin de cellule Aide Éditeurs Google Docs. Passez d'abord la colonne dans SUPPRESPACE, comme le montre comment nettoyer des données dans Excel.

Comment compter les valeurs uniques avec une condition ?

Filtrez les lignes avant qu'UNIQUE les compte. Pour février, renvoie 4. Dans les anciennes versions, renvoie le même 4.

Clients uniques en février, 4
=NBVAL(UNIQUE(FILTRE(B2:B9;C2:C9="Févr")))

FILTRE garde les lignes où le test est vrai Microsoft Support. Pour deux conditions, multipliez les tests : . Placez le mois dans une cellule, par exemple F2, et écrivez pour recopier la formule le long d'une liste de mois.

Les comptages mensuels donnent 2 clients en janvier, 4 en février et 1 en mars. Leur somme fait 7, alors que le trimestre compte 5 clients. C01 et C02 ont commandé sur plusieurs mois, ils apparaissent donc dans plusieurs comptages.

7 est la somme des trois comptages mensuels. Le trimestre compte 5 clients uniques.

N'additionnez jamais des comptages uniques entre périodes ou entre groupes pour obtenir un total. Calculez le total à part, sur toute la plage.

Comment obtenir un nombre distinct dans un tableau croisé dynamique ?

Cochez « Ajouter ces données au modèle de données » à la création du tableau croisé. La synthèse Nombre distinct apparaît alors dans les paramètres des champs de valeurs. Sans le modèle de données, Excel propose Nombre, qui compte des lignes.

  1. Sélectionnez une cellule des données et choisissez InsertionTableau croisé dynamique.
  2. Dans la boîte de dialogue, cochez « Ajouter ces données au modèle de données » et validez Microsoft Support.
  3. Faites glisser Mois dans Lignes et Client dans Valeurs.
  4. Faites un clic droit sur un nombre de la zone Valeurs et choisissez Synthétiser les valeurs parAutres options.
  5. Descendez en bas de la liste et choisissez Nombre distinct.

Une réponse sur le site d'aide de Microsoft le dit sans détour : la fonction d'agrégation Distinct Count n'est disponible que si le modèle de données Power Pivot se trouve derrière le tableau croisé Microsoft Q&A. Un tableau croisé déjà créé sans la case ne peut pas basculer. Créez-en un nouveau sur la même plage, case cochée.

Le tableau croisé affiche 2, 4 et 1 pour les trois mois, et 5 sur la ligne du total général. Le modèle de données calcule le total à part, il évite donc le piège de l'addition des mois. Pour grouper d'abord les dates de commande par mois, voyez comment faire un tableau croisé dynamique par mois. Quand les comptages ne bougent pas après l'ajout de lignes, le tableau croisé doit être actualisé, comme l'explique comment actualiser un tableau croisé dynamique.

Comment compter les valeurs uniques dans Google Sheets ?

Utilisez COUNTUNIQUE. Avec les identifiants clients en B2 :B9, renvoie 5. L'aide de Google la décrit comme une fonction qui compte le nombre de valeurs uniques dans une liste de valeurs et de plages Aide Éditeurs Google Docs.

Pour une condition, combinez-la avec FILTER : renvoie 4. Sheets accepte aussi les formules UNIQUE ci-dessus.

Où se place Glow Reports ?

Les formules ci-dessus répondent à une question sur un fichier. Un reporting mensuel en pose plusieurs : clients uniques par mois, par pays, nouveaux contre récurrents, chacun sur un nouvel export avec ses cellules vides et ses espaces en trop.

Glow Reports part de cet export. Vous importez le fichier CSV ou Excel et vous demandez, en langage courant, le nombre de clients uniques par mois. L'IA écrit le SQL, un groupé par mois, construit le graphique et rédige le texte. Le SQL se trouve sous chaque chiffre : vous vérifiez que le comptage est bien distinct, et vous le modifiez pour exclure les commandes de test ou les identifiants vides. Les rapports s'exportent en PDF. L'offre gratuite donne 300 crédits une fois, et un rapport coûte 100 crédits. Le même comptage distinct, sur les numéros de commande, forme le bas de la fraction du panier moyen.

Questions fréquentes

Quelle différence entre valeurs uniques et valeurs distinctes ?

Dans l'usage courant, les deux veulent dire compter chaque valeur une fois : C01, C02, C03, C04 et C05 font 5 clients distincts. La fonction UNIQUE d'Excel, avec à VRAI, prend « unique » au sens strict des valeurs présentes une seule fois, ce qui donne 3 dans l'exemple.

Pourquoi Nombre distinct n'apparaît-il pas dans mon tableau croisé ?

Le tableau croisé a été créé sans le modèle de données. Créez-en un nouveau et cochez « Ajouter ces données au modèle de données » dans la boîte de dialogue. Nombre distinct apparaît alors en bas de la liste des synthèses.

Pourquoi NBVAL(UNIQUE()) renvoie-t-il une valeur en trop ?

La plage contient une cellule vide, et UNIQUE garde une cellule vide dans son résultat. Utilisez pour écarter les vides.

Peut-on compter les valeurs uniques sur deux colonnes ?

Oui. UNIQUE compare des lignes entières, donc compte les couples client et mois uniques. L'exemple renvoie 7, un par client et par mois de commande.

El Mehdi Benane
Fondateur, Glow Reports

Je suis ingénieur produit et fondateur de Glow Reports. J'ai passé plus de 6 ans à construire des produits dans la tech, les données et la cybersécurité, pour des grands groupes, des startups et des PME. Dans la plupart des organisations, j'ai vu les données traitées comme un sujet technique, alors qu'elles guident les décisions. J'ai construit l'outil de reporting que je cherchais, pour que les dirigeants des petites et moyennes structures lisent leurs chiffres sans équipe data.

Importez votre export de commandes dans Glow Reports et obtenez des comptages distincts par mois ou par groupe, avec le SQL derrière chaque chiffre.

À lire aussi