Comment calculer le nombre de valeurs uniques dans Excel ?

Calculer le Nombre de Valeurs Uniques dans Excel : Guide Complet

Il existe plusieurs façons de déterminer le nombre de valeurs uniques dans une plage de cellules Excel. Le choix de la méthode dépendra de la complexité de vos données et de vos préférences personnelles. Nous allons explorer les méthodes les plus courantes, en commençant par les formules puis en passant par Power Query.

Méthode 1 : Utilisation de la fonction FREQUENCE et SOMME

Cette méthode est particulièrement utile lorsque vous travaillez avec des données numériques. La fonction FREQUENCE permet de déterminer le nombre de fois que chaque valeur apparaît dans une plage de données, et la fonction SOMME permet d'additionner les résultats.

Étapes:

1. Sélectionnez une plage de cellules contenant les valeurs que vous souhaitez analyser (par exemple, A1:A10).

2. Entrez la formule suivante dans une cellule vide :

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

   • A1:A10 est la plage de cellules contenant vos données. Remplacez cette plage par la vôtre.
   • FREQUENCE(A1:A10;A1:A10) calcule la fréquence de chaque valeur dans la plage A1:A10.
   • SI(FREQUENCE(A1:A10;A1:A10)>0;1) renvoie 1 si la fréquence est supérieure à 0 (c'est-à-dire si la valeur apparaît au moins une fois), sinon renvoie 0.
   • SOMME(...) additionne tous les 1, ce qui donne le nombre de valeurs uniques.
   • Validez la formule en appuyant sur Entrée.

Exemple:

Supposons que la plage A1:A10 contienne les valeurs suivantes : 1, 2, 2, 3, 4, 4, 4, 5, 6, 6.

La formule =SOMME(SI(FREQUENCE(A1:A10;A1:A10)>0;1)) renverra 6, car il y a 6 valeurs uniques (1, 2, 3, 4, 5, et 6).

Important: Cette formule fonctionne mieux avec des données numériques. Pour des données textuelles, vous devrez utiliser d'autres méthodes.

Méthode 2 : Utilisation de la fonction NB.SI et SOMME (pour les textes et les nombres)

Cette méthode est plus polyvalente et fonctionne aussi bien avec des données numériques qu'avec des données textuelles. Elle utilise la fonction NB.SI pour compter le nombre d'occurrences de chaque valeur et la fonction SOMME pour additionner les résultats.

Étapes:

1. Sélectionnez une plage de cellules contenant les valeurs que vous souhaitez analyser (par exemple, A1:A10).

2. Entrez la formule suivante dans une cellule vide :

   =SOMME(1/NB.SI(A1:A10;A1:A10))

   • A1:A10 est la plage de cellules contenant vos données. Remplacez cette plage par la vôtre.
   • NB.SI(A1:A10;A1:A10) compte le nombre d'occurrences de chaque valeur dans la plage A1:A10.
   • 1/NB.SI(A1:A10;A1:A10) calcule l'inverse du nombre d'occurrences. Pour chaque valeur unique, cette expression renverra une fraction dont le dénominateur est le nombre de fois que la valeur apparaît. La somme de ces fractions sera égale au nombre de valeurs uniques.
   • SOMME(...) additionne toutes les fractions, ce qui donne le nombre de valeurs uniques.
   • Validez la formule en appuyant sur Entrée.

Exemple:

Supposons que la plage A1:A10 contienne les valeurs suivantes : "Pomme", "Banane", "Pomme", "Orange", "Banane", "Kiwi", "Pomme", "Orange", "Fraise", "Kiwi".

La formule =SOMME(1/NB.SI(A1:A10;A1:A10)) renverra 5, car il y a 5 valeurs uniques ("Pomme", "Banane", "Orange", "Kiwi", et "Fraise").

Important: Cette formule est une formule matricielle. Dans certaines versions d'Excel, il peut être nécessaire de la valider en appuyant sur Ctrl+Maj+Entrée au lieu d'Entrée. Si c'est le cas, Excel ajoutera automatiquement des accolades {} autour de la formule.

Méthode 3 : Utilisation de Power Query (Obtenir et Transformer des données)

Power Query est un outil puissant intégré à Excel qui permet de nettoyer, transformer et analyser des données provenant de différentes sources. Il offre une méthode simple et efficace pour calculer le nombre de valeurs uniques.

Étapes:

1. Sélectionnez la plage de cellules contenant les valeurs que vous souhaitez analyser (par exemple, A1:A10).
2. Allez dans l'onglet "Données" du ruban Excel.
3. Cliquez sur "Du tableau/plage" dans le groupe "Obtenir et transformer des données". Cela ouvrira l'éditeur Power Query.
4. Dans l'éditeur Power Query, cliquez avec le bouton droit de la souris sur l'en-tête de la colonne contenant vos données et sélectionnez "Supprimer les doublons".
5. Cliquez sur "Fermer et charger" dans l'onglet "Accueil" du ruban Power Query. Cela créera un nouveau tableau contenant uniquement les valeurs uniques.
6. Utilisez la fonction NBVAL pour compter le nombre de valeurs dans le nouveau tableau. Par exemple, si le nouveau tableau est dans la plage C1:C5, entrez la formule =NBVAL(C1:C5) dans une cellule vide. Cette formule renverra le nombre de valeurs uniques.

Exemple:

Si la plage A1:A10 contient les valeurs suivantes : "A", "B", "A", "C", "B", "D", "A", "C", "E", "D", après avoir supprimé les doublons avec Power Query, le nouveau tableau contiendra les valeurs "A", "B", "C", "D", "E". La formule =NBVAL(C1:C5) renverra 5.

Avantages de Power Query:

• Facilité d'utilisation: L'interface graphique de Power Query rend la suppression des doublons très simple.
• Flexibilité: Power Query peut être utilisé pour nettoyer et transformer des données provenant de différentes sources (fichiers CSV, bases de données, etc.).
• Automatisation: Vous pouvez enregistrer les étapes de transformation dans Power Query et les réutiliser ultérieurement pour automatiser le processus.

Méthode 4 : Utilisation des Tableaux Croisés Dynamiques

Les tableaux croisés dynamiques sont un outil puissant d'Excel pour synthétiser et analyser des données. Ils peuvent également être utilisés pour déterminer le nombre de valeurs uniques.

Étapes:

1. Sélectionnez la plage de cellules contenant les valeurs que vous souhaitez analyser (par exemple, A1:A10).
2. Allez dans l'onglet "Insertion" du ruban Excel.
3. Cliquez sur "Tableau croisé dynamique" dans le groupe "Tableaux".
4. Dans la boîte de dialogue "Créer un tableau croisé dynamique", choisissez où vous souhaitez placer le tableau croisé dynamique (par exemple, dans une nouvelle feuille de calcul).
5. Faites glisser le champ contenant vos données (par exemple, "Colonne1") vers la zone "Lignes" du tableau croisé dynamique.
6. Faites glisser le même champ vers la zone "Valeurs" du tableau croisé dynamique. Par défaut, Excel affichera la somme des valeurs. Cliquez sur la flèche vers le bas à côté du nom du champ dans la zone "Valeurs", sélectionnez "Paramètres des champs de valeurs", puis choisissez "Nombre".

Le tableau croisé dynamique affichera maintenant le nombre de valeurs uniques dans votre plage de données.

Exemple:

Si la plage A1:A10 contient les valeurs suivantes : "X", "Y", "X", "Z", "Y", "X", "Z", "W", "W", "Y", le tableau croisé dynamique affichera les valeurs uniques ("W", "X", "Y", "Z") et leur nombre d'occurrences. Le nombre de valeurs uniques est 4.

Conseils et Astuces

• Gérer les cellules vides: Si votre plage de données contient des cellules vides, les formules FREQUENCE et NB.SI les traiteront comme des valeurs. Pour les ignorer, vous pouvez utiliser la fonction SIERREUR pour renvoyer 0 si la cellule est vide.
• Tenir compte de la casse: Par défaut, les fonctions NB.SI et FREQUENCE ne sont pas sensibles à la casse. Si vous devez faire la distinction entre les majuscules et les minuscules, vous pouvez utiliser la fonction EXACT en combinaison avec d'autres fonctions.
• Utiliser des plages nommées: Pour rendre vos formules plus lisibles et plus faciles à maintenir, vous pouvez définir des plages nommées pour vos données. Par exemple, vous pouvez nommer la plage A1:A10 "Donnees". Vos formules deviendront alors =SOMME(1/NB.SI(Donnees;Donnees)). Cela améliore grandement la lisibilité.
• Combiner les méthodes: Vous pouvez combiner différentes méthodes pour obtenir des résultats plus précis ou pour gérer des situations plus complexes. Par exemple, vous pouvez utiliser Power Query pour nettoyer vos données, puis utiliser une formule pour calculer le nombre de valeurs uniques.

Erreurs Courantes à Éviter

• Oublier de valider les formules matricielles: Si vous utilisez la formule SOMME(1/NB.SI(A1:A10;A1:A10)), assurez-vous de la valider en appuyant sur Ctrl+Maj+Entrée si votre version d'Excel le nécessite. Ne pas le faire peut entraîner des résultats incorrects.
• Ne pas adapter les plages de cellules: Vérifiez que les plages de cellules dans vos formules correspondent bien aux données que vous souhaitez analyser. Une erreur courante est de copier-coller une formule sans modifier les références de cellules.
• Ignorer les cellules vides: Soyez conscient de la présence de cellules vides dans vos données et gérez-les correctement dans vos formules.
• Ne pas tenir compte de la casse: Si la casse est importante pour votre analyse, utilisez les fonctions appropriées pour faire la distinction entre les majuscules et les minuscules.

En conclusion, Excel offre plusieurs méthodes pour calculer le "count of unique values in excel". Que vous utilisiez des formules simples comme FREQUENCE et NB.SI, ou des outils plus avancés comme Power Query et les tableaux croisés dynamiques, vous pouvez facilement déterminer le nombre de valeurs uniques dans vos données et obtenir des informations précieuses pour votre analyse. Choisissez la méthode qui convient le mieux à vos besoins et à la structure de vos données, et n'hésitez pas à combiner différentes approches pour obtenir des résultats optimaux.