Comment compter les valeurs uniques (distinctes) dans Excel ?

Compter les valeurs distinctes dans Excel : Le guide complet

Dans Excel, compter le nombre de valeurs uniques, c'est-à-dire les valeurs qui apparaissent une seule fois dans une plage de cellules, est une tâche courante. Cela permet d'analyser des données, d'identifier des tendances et de nettoyer des listes. Il existe plusieurs méthodes pour réaliser cette opération, chacune ayant ses avantages et ses inconvénients. Nous allons explorer les principales techniques.

Méthode 1 : Utiliser la formule matricielle FREQUENCE combinée à SOMME

La fonction FREQUENCE est un outil puissant pour déterminer le nombre de fois qu'une valeur apparaît dans une plage de données. Combinée à la fonction SOMME et à quelques astuces, elle permet de compter les valeurs distinctes.

Explication de la formule

La formule générale est la suivante :

=SOMME(SI(FREQUENCE(plage;plage)>0;1))

• plage : La plage de cellules contenant les données à analyser (par exemple, A1:A10).
• FREQUENCE(plage;plage) : Cette partie calcule la fréquence de chaque valeur dans la plage. Elle renvoie un tableau de fréquences.
• FREQUENCE(plage;plage)>0 : Cette condition vérifie si la fréquence de chaque valeur est supérieure à zéro. Elle renvoie un tableau de valeurs booléennes (VRAI ou FAUX).
• SI(FREQUENCE(plage;plage)>0;1) : Si la fréquence est supérieure à zéro (c'est-à-dire si la valeur apparaît au moins une fois), la fonction SI renvoie 1. Sinon, elle renvoie FAUX (ou 0, car Excel convertit VRAI en 1 et FAUX en 0 lors d'une addition).
• SOMME(...) : La fonction SOMME additionne tous les 1 renvoyés par la fonction SI, ce qui donne le nombre de valeurs distinctes.

Exemple pratique

Supposons que vous ayez une liste de noms dans la plage A1:A10 :

Pour compter le nombre de noms distincts, entrez la formule suivante dans une cellule (par exemple, B1) :

=SOMME(SI(FREQUENCE(A1:A10;A1:A10)>0;1))

Important : Cette formule est une formule matricielle. Vous devez la valider en appuyant sur Ctrl + Maj + Entrée (au lieu de simplement Entrée). Excel ajoutera automatiquement des accolades {} autour de la formule pour indiquer qu'il s'agit d'une formule matricielle.

Le résultat affiché dans la cellule B1 sera 4, car il y a quatre noms distincts (Jean, Marie, Pierre, Sophie, Paul).

Avantages et inconvénients

• Avantages :
  • Fonctionne avec toutes les versions d'Excel.
  • Ne nécessite pas de compétences avancées en VBA ou Power Query.
• Inconvénients :
  • Peut être difficile à comprendre pour les débutants.
  • Nécessite la validation matricielle (Ctrl + Maj + Entrée).
  • Peu performante sur de très grandes plages de données.

Méthode 2 : Utiliser la fonction UNIQUE (Excel 365 et versions ultérieures)

La fonction UNIQUE est une fonction native d'Excel 365 et des versions ultérieures. Elle renvoie une liste de valeurs uniques à partir d'une plage de données. On peut ensuite combiner cette fonction avec NBVAL pour compter le nombre de valeurs uniques.

Explication de la formule

La formule générale est la suivante :

=NBVAL(UNIQUE(plage))

• plage : La plage de cellules contenant les données à analyser (par exemple, A1:A10).
• UNIQUE(plage) : Cette partie extrait les valeurs uniques de la plage et les renvoie sous forme de tableau.
• NBVAL(...) : La fonction NBVAL compte le nombre de cellules non vides dans le tableau renvoyé par UNIQUE, ce qui correspond au nombre de valeurs uniques.

Exemple pratique

En reprenant l'exemple précédent avec la liste de noms dans la plage A1:A10, entrez la formule suivante dans une cellule (par exemple, B1) :

=NBVAL(UNIQUE(A1:A10))

Le résultat affiché dans la cellule B1 sera 5, car il y a cinq noms distincts (Jean, Marie, Pierre, Sophie, Paul).

Avantages et inconvénients

• Avantages :
  • Facile à comprendre et à utiliser.
  • Ne nécessite pas de validation matricielle.
  • Plus performante que la méthode FREQUENCE/SOMME sur de grandes plages de données.
• Inconvénients :
  • Uniquement disponible dans Excel 365 et versions ultérieures.

Méthode 3 : Utiliser un tableau croisé dynamique (TCD)

Les tableaux croisés dynamiques sont des outils puissants pour analyser et synthétiser des données. Ils peuvent également être utilisés pour compter les valeurs distinctes.

Étapes à suivre

1. Sélectionnez la plage de données à analyser (par exemple, A1:A10).
2. Insérez un tableau croisé dynamique : Onglet Insertion > Tableau croisé dynamique. Choisissez l'emplacement du tableau croisé dynamique (nouvelle feuille ou feuille existante).
3. Configurez le tableau croisé dynamique :
   • Faites glisser le champ contenant les valeurs à analyser (par exemple, Nom) vers la zone Lignes.
   • Faites glisser le même champ vers la zone Valeurs. Par défaut, Excel affichera la somme des valeurs. Cliquez sur le champ dans la zone Valeurs, puis choisissez Paramètres des champs de valeurs. Dans la boîte de dialogue, sélectionnez Nombre comme type de calcul et Valeurs distinctes (ou NB si Valeurs distinctes n'est pas disponible). (Capture d'écran textuellement décrite: une boîte de dialogue intitulée "Paramètres des champs de valeurs" est affichée. Dans l'onglet "Synthétiser par", l'option "Nombre" est sélectionnée. Dans l'onglet "Afficher les valeurs", l'option "Valeurs distinctes" est recherchée. Si elle n'est pas disponible, l'option "Nombre" est utilisée.)
4. Le tableau croisé dynamique affichera le nombre de valeurs distinctes dans la plage de données.

Avantages et inconvénients

• Avantages :
  • Facile à utiliser avec une interface graphique.
  • Permet d'analyser les données selon différents critères.
  • Ne nécessite pas de formules complexes.
• Inconvénients :
  • Nécessite la création d'un tableau croisé dynamique.
  • Peut être moins flexible que les formules pour des calculs spécifiques.

Méthode 4 : Utiliser Power Query (Obtenir et transformer des données)

Power Query est un outil puissant intégré à Excel qui permet de transformer et de nettoyer des données. Il peut également être utilisé pour compter les valeurs distinctes.

Étapes à suivre

1. Sélectionnez la plage de données à analyser (par exemple, A1:A10).
2. Chargez les données dans Power Query : Onglet Données > Obtenir et transformer des données > À partir d'une plage/d'un tableau.
3. Supprimez les doublons : Dans l'éditeur Power Query, sélectionnez la colonne contenant les valeurs à analyser (par exemple, Nom). Cliquez sur Supprimer les lignes > Supprimer les doublons.
4. Comptez les lignes : Cliquez sur Fermer et charger > Fermer et charger dans.... Choisissez Tableau ou Connexion uniquement. Si vous choisissez Tableau, Excel créera un nouveau tableau avec les valeurs uniques. Vous pouvez ensuite utiliser la fonction NBVAL pour compter le nombre de lignes de ce tableau. Si vous choisissez Connexion uniquement, vous pouvez utiliser la fonction NOMBRE.LIGNES pour compter les lignes de la requête Power Query.

Avantages et inconvénients

• Avantages :
  • Très puissant pour transformer et nettoyer des données.
  • Permet d'automatiser le processus de comptage des valeurs distinctes.
  • Peut gérer de très grandes quantités de données.
• Inconvénients :
  • Nécessite une certaine familiarité avec Power Query.
  • Peut être plus complexe que les autres méthodes pour des tâches simples.

Conseils et astuces supplémentaires

• Ignorer la casse : Si vous souhaitez ignorer la casse (majuscules/minuscules) lors du comptage des valeurs distinctes, vous pouvez utiliser la fonction MAJUSCULE ou MINUSCULE pour convertir toutes les valeurs en majuscules ou en minuscules avant d'appliquer les formules ou les méthodes décrites ci-dessus. Par exemple, =SOMME(SI(FREQUENCE(MAJUSCULE(A1:A10);MAJUSCULE(A1:A10))>0;1)).
• Gérer les cellules vides : Assurez-vous de bien gérer les cellules vides dans votre plage de données. Les cellules vides peuvent affecter les résultats de certaines formules. Vous pouvez utiliser la fonction SI ou ESTVIDE pour exclure les cellules vides du calcul.
• Combiner les méthodes : Vous pouvez combiner différentes méthodes pour obtenir des résultats plus précis. Par exemple, vous pouvez utiliser Power Query pour nettoyer et transformer les données, puis utiliser un tableau croisé dynamique pour analyser les résultats.

Erreurs à éviter

• Oublier la validation matricielle : Si vous utilisez la formule FREQUENCE/SOMME, n'oubliez pas de la valider en appuyant sur Ctrl + Maj + Entrée.
• Ne pas tenir compte de la casse : Si la casse est importante pour vous, assurez-vous de ne pas l'ignorer.
• Ne pas gérer les cellules vides : Les cellules vides peuvent fausser les résultats si elles ne sont pas gérées correctement.
• Utiliser la mauvaise méthode : Choisissez la méthode la plus adaptée à vos besoins et à votre niveau de compétence.

En conclusion, compter les valeurs distinctes dans Excel peut se faire de plusieurs manières. Le choix de la méthode dépendra de votre version d'Excel, de la taille de vos données et de votre niveau de compétence. N'hésitez pas à expérimenter avec les différentes techniques présentées dans cet article pour trouver celle qui vous convient le mieux.