Comment compter les valeurs uniques dans Excel : Ce que vous devez savoir

Si vous travaillez avec des données, il est indispensable de compter les valeurs uniques, car des données propres permettent d'obtenir de meilleures informations. Dans ce guide, je vous montrerai comment compter les valeurs uniques dans Excel. Je vous montrerai également des cas particuliers, tels que la gestion de la sensibilité à la casse ou le comptage de valeurs uniques en fonction d'une ou de plusieurs conditions.

Parce que les idées se confondent parfois, je prendrai le temps de dissiper tout malentendu que vous pourriez avoir sur la différence entre les valeurs uniques et distinctes dans Excel et, au cas où vous souhaiteriez plutôt effectuer un comptage distinct, je vous montrerai comment faire à la fin de cet article. 

Enfin, avant de commencer, je tiens à préciser que ce ne sont là que quelques-uns des sujets abordés dans notre cursus sur les fondamentaux d'Excel, alors inscrivez-vous dès aujourd'hui pour devenir un expert.

Note sur les valeurs uniques et les valeurs de référence Valeurs distinctes dans Excel

Les termes "valeurs uniques" et "valeurs distinctes" sont parfois utilisés de manière interchangeable, mais il ne s'agit pas de la même chose. Voici donc une comparaison rapide pour vous aider à comprendre leur différence.

Valeurs distinctes sont les différentes valeurs d'un ensemble de données, les doublons étant supprimés. Par exemple, dans la liste A, A, B, C, C, D, les valeurs distinctes sont A, B, C, D.

Valeurs uniques, en revanche, sont des valeurs qui n'apparaissent qu'une seule fois dans l'ensemble de données. En reprenant le même exemple, les valeurs uniques sont B et D (parce que A et C apparaissent plus d'une fois).

Dans la première moitié de l'article, je me concentrerai sur les différentes façons de compter les valeurs uniques dans Excel. Mais si vous avez atterri ici par hasard et que vous cherchez vraiment à compter des valeurs distinctes, je vous couvre également pour cela, si vous voulez passer à la section suivante.

Méthodes pour compter les valeurs uniques dans Excel

Il existe deux façons courantes de compter les valeurs uniques dans Excel.

Méthode 1 : Utilisez la fonction UNIQUE() avec le paramètre correct

La façon la plus simple de compter les valeurs uniques est d'utiliser les fonctions UNIQUE() et COUNTA(). La fonction UNIQUE() extrait toutes les valeurs uniques d'une plage et COUNTA() compte le nombre de valeurs uniques présentes. 

COUNTA(UNIQUE(range, false, true))

Par exemple, j'ai appliqué la formule suivante à un petit ensemble de données et j'ai obtenu des valeurs uniques.

=COUNTA(UNIQUE(A2:A8, false, true))

Comptez les valeurs uniques à l'aide des fonctions UNIQUE() et COUNTA(). Image par l'auteur.

Si vous utilisiez cette syntaxe à la place, =COUNTA(UNIQUE(A2:A8)), qui n'a pas le troisième paramètre que nous avons défini comme TRUE, nous obtiendrions comme résultat un compte distinct, et non un compte unique. L'ajout de TRUE au troisième argument indique à la fonction qu'elle doit renvoyer un compte unique. Ces deux éléments font partie de la fonction UNIQUE(), ce qui peut être un peu déroutant au début.

Méthode 2 : Utilisez SUM() avec IF() et COUNTIF()

Si vous n'êtes pas sûr des nuances de la fonction UNIQUE(), ou si vous n'avez pas Excel 365, sachez que nous pouvons également combiner SUM() avec IF() et COUNTIF() pour compter les valeurs uniques.

=SUM(IF(COUNTIF(range, range)=1,1,0))

Par exemple, j'ai des données dans la plage A2:A8 et je veux compter les valeurs uniques, j'écris donc la formule suivante :

SUM(IF(COUNTIF(A2:A8, A2:A8)=1,1,0))

Ici, COUNTIF() parcourt la liste et vérifie combien de fois chaque nom apparaît. Si un nom n'apparaît qu'une seule fois, il est comptabilisé comme 1. S'il apparaît plus d'une fois, il reçoit un numéro plus élevé. Ensuite, IF() filtre ces résultats pour conserver les 1 tels quels, mais transforme tout le reste en 0. Enfin, SUM() additionne tous les 1 et nous donne le nombre total de valeurs uniques.

Comptez les valeurs uniques à l'aide de SUM(), IF() et COUNTIF(). Image par l'auteur.

Comptage des valeurs textuelles et numériques uniques

Parfois, notre ensemble de données contient des types de données mixtes et nous devons les analyser indépendamment les uns des autres. Cela peut sembler un peu intimidant au premier abord, mais c'est possible avec Excel. Examinons deux méthodes - l'une pour compter les valeurs de texte uniques et l'autre pour les nombres uniques.

Comptez les valeurs de texte uniques

Si vous souhaitez compter les valeurs de texte uniques, combinez les fonctions ISTEXT(), COUNTIF() et SUM(). ISTEXT() vérifie si une valeur est un texte, tandis que COUNTIF() compte le nombre d'occurrences de chaque valeur et SUM() totalise les entrées de texte distinctes. 

J'ai appliqué la formule suivante à un échantillon de données et elle m'a immédiatement indiqué le nombre de valeurs de texte uniques présentes.

=SUM(IF(ISTEXT(A2:A9)*COUNTIF(A2:A9,A2:A9)=1,1,0))

Comptez les valeurs de texte uniques. Image par l'auteur.

Comptez les valeurs numériques uniques

Si vous souhaitez compter les numéros uniques au lieu du texte, utilisez la même formule mais remplacez ISTEXT() par ISNUMBER(). ISNUMBER() ne prendra en compte que les valeurs numériques, tandis que COUNTIF() et SUM() s'occuperont de l'unicité. J'ai modifié la formule ci-dessus sur les mêmes données, et elle ne m'indique plus que le nombre de valeurs numériques uniques :

=SUM(IF(ISNUMBER(A2:A9)*COUNTIF(A2:A9,A2:A9)=1,1,0))

Comptez les valeurs numériques uniques. Image par l'auteur.

Compter les valeurs uniques avec des conditions et des critères

Maintenant que nous avons abordé les méthodes de base pour compter les valeurs uniques, explorons quelques techniques avancées.

Comptez les valeurs uniques en fonction des conditions

Pour compter les valeurs uniques basées sur une condition spécifique, j'utilise cette syntaxe :

=IFERROR(ROWS(UNIQUE(FILTER(range, criteria_range=criteria))), 0)

Dans cette formule, FILTER() filtre la plage sur la base de critères spécifiques, UNIQUE() supprime les doublons, ROWS() compte les résultats et IFERROR() prévient les erreurs en renvoyant le message " Non trouvé" si aucune correspondance n'est trouvée.

Supposons que je dispose d'une liste d'employés et de leurs départements, et que je doive compter combien d'employés uniques travaillent dans un département spécifique. J'utiliserai la formule suivante :

=IFERROR(ROWS(UNIQUE(FILTER(A2:A20, B2:B20=F1))), "Not Found")

Ici, A2:A10 représente la plage de noms d'employés que je souhaite filtrer et B2:B10=F1 vérifie quels employés appartiennent au département, qui est écrit en F1.

Comptez les valeurs uniques avec une condition. Image par l'auteur.

Comptez les lignes uniques sur la base de plusieurs colonnes

Si je veux compter les employés uniques d'un département spécifique dont les salaires sont inférieurs à 50 000 $, j'utilise la formule suivante :

=IFERROR(ROWS(UNIQUE(FILTER(A2:A10, (B2:B10=F1) * (C2:C10>F2)))), "Not Found")

Ici, A2:A10 représente la plage de noms d'employés, B2:B10=F1 vérifie quels employés appartiennent au département indiqué dans F1, et C2:C10>F2 vérifie si le salaire est inférieur au montant indiqué dans F2.

Comptez les valeurs uniques avec plusieurs critères. Image par l'auteur.

Comptes uniques sensibles à la casse

Par défaut, Excel fait la distinction entre les majuscules et les minuscules. Par exemple, Apple et APPLE sont considérés comme différents. Pour repérer ces cas, je crée une colonne d'aide avec la formule suivante :

=IF(SUM((--EXACT($A$2:$A$11,A2)))=1,"Unique","Duplicate")

Pour obtenir le nombre de valeurs uniques, j'utilise la fonction COUNTIF() comme suit :

=COUNTIF(B2:B11,"Unique")

Comptez les valeurs uniques sensibles à la casse. Image par l'auteur.

Meilleures pratiques pour compter les valeurs uniques dans Excel

Voici quelques-unes de mes pratiques préférées qui vous aideront à rester précis et efficace :

• Assurez-vous qu'il n'y a pas d'espaces supplémentaires ou de formatage incohérent dans les données. Dans le cas contraire, une erreur sera générée.
• Votre plage de données ne doit pas comporter de cellules vides, car elles peuvent fausser le résultat.
• Si vos données changent souvent, utilisez des tableaux croisés dynamiques ou des fonctions de tableau dynamique pour que tout soit mis à jour en temps réel. 

Comment compter plutôt des valeurs distinctes

Enfin, pour terminer, si vous vouliez vraiment compter les distinctes je vous montrerai plusieurs méthodes pour compter les valeurs distinctes.

Méthode 1 : Utilisez les fonctions COUNTIF() et SUM()

Pour compter les valeurs distinctes dans Excel, vous pouvez combiner les fonctions COUNTIF() et SUM(). La fonction COUNTIF() vérifie combien de fois chaque valeur apparaît dans un intervalle donné. Ensuite, SUM() additionne les valeurs renvoyées par COUNTIF(), ce qui donne le nombre total d'entrées distinctes. 

=SUM(1/COUNTIF(range, range))

Supposons que je veuille trouver les valeurs distinctes dans l'intervalle A2:A8. J'introduis la formule suivante :

=SUM(1/COUNTIF(A2:A8, A2:A8))

Ici, la fonction COUNTIF() vérifie combien de fois chaque valeur apparaît dans une liste.

1/COUNTIF(A2:A8, A2:A8) divise 1 par le nombre de fois où chaque valeur apparaît. Par exemple, si un nombre apparaît deux fois, chaque occurrence devient 0,5 (1/2). S'il apparaît trois fois, il sera de 0,33 (1/3). 

Comme nous ne voulons pas de ces fractions, nous pouvons utiliser SUM() ou SUMPRODUCT() pour additionner toutes les valeurs. Les fractions de chaque duplicata se combinent en 1, et les valeurs distinctes restent 1 puisque 1/1 = 1 :

=SUMPRODUCT(1/COUNTIF(A2:A8, A2:A8))

Comptez les valeurs distinctes à l'aide des fonctions COUNTIF() et SUM(). Image par l'auteur.

Méthode 2 : Utilisez les tableaux croisés dynamiques pour les comptages distincts

Excel 2013 et Excel 2016 ont intégré une fonction permettant de compter les valeurs distinctes dans un tableau croisé dynamique. Pour l'utiliser, vous devez d'abord créer un tableau croisé dynamique en sélectionnant des données : Allez dans Insertion et sélectionnez Tableau croisé dynamique. Une boîte de dialogue apparaît - dans cette boîte de dialogue, choisissez Feuille de calcul existante et cochez la case Ajouter ces données au modèle de données.

Créez un tableau croisé dynamique. Image par l'auteur.

Ensuite, faites glisser la colonne de votre choix dans le champ Valeurs. Dans mon cas, je fais glisser la colonne Fruits parce que je veux compter ses valeurs distinctes. Cliquez ensuite sur Paramètres du champ de valeur dans le menu déroulant. Une fenêtre contextuelle apparaît - sélectionnez alors Compte des distinctions. Vous pouvez même donner à cette colonne un nom personnalisé, mais c'est facultatif.

Comptez les valeurs distinctes à l'aide du tableau croisé dynamique. Image par l'auteur.

Méthode 3 : Appliquer des filtres avancés

Si vous n'aimez pas travailler avec des formules, utilisez plutôt les filtres avancés. 

Dans cet exemple, je dispose d'un ensemble de données et je souhaite trouver des valeurs distinctes à l'aide de filtres avancés. Pour ce faire, je sélectionne la plage dans laquelle je souhaite trouver la valeur en question. Ensuite, je vais dans l'onglet Données et je clique sur Avancé. 

Maintenant, dans la boîte de dialogue Filtre avancé, je choisis Copier à un autre endroit, et dans le champ Copier vers, je saisis la cellule de destination où je veux que ma liste apparaisse. Cochez ensuite la case Enregistrements uniques uniquement et cliquez sur OK.

Une fois que j'ai une liste de valeurs uniques, je calcule le nombre de ces valeurs uniques à l'aide de la fonction ROWS():

=ROWS(D2:D5)

Même si l'option de l'image ci-dessous ne lit que les enregistrements uniques, elle extrait en réalité des valeurs distinctes. 

Comptez les valeurs distinctes à l'aide d'une option de filtre avancé. Image par l'auteur.

Méthode 4 : Mettre en œuvre des macros VBA

Si vous souhaitez compter des valeurs distinctes de manière répétée, utilisez VBA (Visual Basic for Applications), où nous écrivons le code une seule fois et où il automatise l'ensemble du processus chaque fois que nous appelons la fonction avec son nom.

Voici comment vous pouvez l'utiliser : appuyez sur Alt + F11 et accédez à l'option Module sous l'onglet Insertion. Un nouveau module apparaît et vous pouvez y coller votre code. 

Voici le code que j'ai créé pour compter les valeurs distinctes :

Function CountUnique(rng As Range) As Integer
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    Dim cell As Range
    For Each cell In rng
        If Not dict.exists(cell.Value) And cell.Value <> "" 

Then,
            dict.Add cell.Value, Nothing
        End If
    Next cell
    
    CountUnique = dict.Count
End Function

Écrivez un code dans l'éditeur VBA pour trouver des valeurs distinctes. Image par l'auteur.

Ensuite, appuyez sur Ctrl + S pour l'enregistrer et sur ALt + Q pour fermer l'éditeur VBA. Vous pouvez maintenant appeler cette fonction personnalisée n'importe où dans la feuille et spécifier la plage à partir de laquelle les valeurs distinctes doivent être recherchées. Dans mon cas, elle s'appelle CountUnique(). Chaque fois que j'appelle cette fonction avec un intervalle spécifié, elle me donne une valeur de comptage :

=CountUnique(A2:A8)

Comptez les valeurs distinctes à l'aide de l'éditeur VBA. Image par l'auteur.

Tableau de référence

À titre de référence, j'ai créé un tableau pour vous aider à comprendre les différences entre les comptes uniques et les comptes distincts. 

Problèmes courants et dépannage

Parfois, même après avoir suivi les meilleures pratiques, vous pouvez être confronté à des difficultés. Mais ce n'est pas grave, car cela arrive à la plupart des gens.

Parfois, j'obtiens une erreur et je me sens confus, avant de réaliser que les espaces cachés sont le problème. Cela se produit lorsque certaines valeurs comportent des espaces supplémentaires au début ou à la fin et qu'Excel traite ces cas différemment, même s'ils se ressemblent. Pour résoudre ce problème, je place ma formule à l'intérieur de la fonction TRIM(). Lisez mon autre tutoriel, Utilisez la fonction TRIM() dans Excel : Corrigez tout problème d'espacement si vous avez des questions persistantes sur TRIM().

Les erreurs de formule dans les anciennes versions d'Excel constituent un autre problème courant. Par exemple, UNIQUE() ne fonctionne pas dans Excel 2016 ou ses versions précédentes. Au lieu de cela, utilisez COUNTIF() ou SUMPRODUCT().

Réflexions finales

J'ai abordé les formules clés, les tableaux croisés dynamiques et les macros VBA pour le comptage des valeurs uniques et distinctes. Chaque méthode a ses avantages, mais vous devez choisir celle qui répond à vos besoins et prend en charge votre version d'Excel.

Si vous souhaitez affiner vos compétences en Excel, consultez notre parcours de compétences Excel Fundamentals et notre cursus Data Preparation in Excel.