Comment compter les valeurs uniques dans Excel
Les ensembles de données dans Excel contiennent souvent la même valeur plusieurs fois dans une colonne. Parfois, il peut être utile de savoir combien de valeurs uniques se trouvent dans une colonne. Par exemple, si vous gérez un magasin et disposez d'une feuille de calcul de toutes vos transactions, vous souhaiterez peut-être déterminer le nombre de clients uniques que vous avez, plutôt que de compter chaque transaction.
Il est possible de le faire en comptant des valeurs uniques dans Excel en utilisant les méthodes que nous allons vous expliquer ci-dessous.
Supprimer les données en double d'une colonne
Un moyen rapide et sale de compter les valeurs uniques dans Excel consiste à supprimer les doublons et à voir combien d'entrées il reste. C'est une bonne option si vous avez besoin d'une réponse rapidement et n'avez pas besoin de suivre le résultat.
Copiez les données dans une nouvelle feuille (pour ne pas supprimer accidentellement les données dont vous avez besoin). Sélectionnez les valeurs ou la colonne dont vous souhaitez supprimer les valeurs en double. Dans la section Outils de données de l' onglet Données, sélectionnez Supprimer les doublons . Cela supprime toutes les données en double et ne laisse que les valeurs uniques.
Le même processus fonctionne si les informations sont réparties sur deux colonnes. La différence est que vous devez sélectionner les deux colonnes. Dans notre exemple, nous avons une colonne pour le prénom et une seconde pour le nom de famille.
Si vous souhaitez suivre le nombre de valeurs uniques, il vaut mieux écrire une formule à la place. Nous allons vous montrer comment faire cela ci-dessous.
Compter les valeurs uniques avec une formule Excel
Pour ne compter que des valeurs uniques, nous devons combiner plusieurs fonctions Excel. Tout d'abord, nous devons vérifier si chaque valeur est un double, puis nous devons compter les entrées restantes. Nous devons également utiliser une fonction de tableau.
Si vous cherchez simplement la réponse, utilisez cette formule, en remplaçant chaque instance de A2: A13 par les cellules que vous souhaitez utiliser:
{=SUM(IF(FREQUENCY(MATCH(A2:A13, A2:A13, 0), MATCH(A2:A13, A2:A13, 0)) >0, 1))}Comment nous y sommes arrivés est un peu compliqué. Donc, si vous voulez comprendre pourquoi cette formule fonctionne, nous la décomposerons un morceau à la fois ci-dessous.
Explication d'une fonction de matrice
Commençons par expliquer ce qu'est un tableau. Un tableau est une variable unique qui contient plusieurs valeurs. C'est comme faire référence à un tas de cellules Excel à la fois au lieu de faire référence à chaque cellule individuellement.
C'est une distinction étrange de notre point de vue. Si nous disons à une formule de regarder les cellules A2: A13 normalement ou sous forme de tableau, les données nous semblent identiques. La différence réside dans la façon dont Excel traite les données en arrière-plan. C'est une différence si subtile que les dernières versions d'Excel ne font même plus la distinction entre elles, bien que les anciennes versions le fassent.
Pour nos besoins, il est plus important de savoir comment nous pouvons utiliser les tableaux. Si vous disposez de la dernière version d'Excel, il stocke automatiquement les données sous forme de tableau lorsqu'il est plus efficace de le faire. Si vous avez une version plus ancienne, lorsque vous avez terminé d'écrire votre formule, appuyez sur Ctrl + Maj + Entrée . Une fois que vous avez fait, la formule sera entourée de parenthèses pour indiquer qu'elle est en mode tableau.
Présentation de la fonction FREQUENCY
La fonction FREQUENCE nous indique combien de fois un nombre apparaît dans une liste. C'est génial si vous travaillez avec des nombres, mais notre liste est du texte. Pour utiliser cette fonction, nous devons d'abord trouver un moyen de convertir notre texte en nombres.
Si vous essayez de compter les valeurs uniques dans une liste de nombres, vous pouvez ignorer l'étape suivante.
Utilisation de la fonction MATCH
La fonction MATCH renvoie la position de la première occurrence d'une valeur. Nous pouvons l'utiliser pour convertir notre liste de noms en valeurs numériques. Il a besoin de connaître trois informations:
- Quelle valeur recherchez-vous?
- Quel ensemble de données vérifiez-vous?
- Recherchez-vous des valeurs supérieures, inférieures ou égales à la valeur cible?
Dans notre exemple, nous voulons rechercher chaque nom de nos clients dans notre feuille de calcul Exel pour voir si leur nom exact apparaît à nouveau ailleurs.
Dans l'exemple ci-dessus, nous recherchons dans notre liste (A2: A13) Tiah Gallagher (A2) et nous voulons une correspondance exacte. Le 0 dans le dernier champ indique qu'il doit s'agir d'une correspondance exacte. Notre résultat nous indique où dans la liste le nom est apparu en premier. Dans ce cas, c'était le prénom, donc le résultat est 1.
Le problème avec cela est que nous nous intéressons à tous nos clients, pas seulement à Tiah. Mais, si nous essayons de rechercher A2: A13 au lieu de seulement A2, nous obtenons une erreur. C'est là que les fonctions de tableau sont utiles. Le premier paramètre ne peut prendre qu'une seule variable ou bien il renvoie une erreur. Mais, les tableaux sont traités comme s'ils étaient une seule variable.
Maintenant, notre fonction indique à Excel de vérifier les correspondances pour l'ensemble de notre tableau. Mais attendez, notre résultat n'a pas changé! Il dit toujours 1. Que se passe-t-il ici?
Notre fonction renvoie un tableau. Il parcourt chaque élément de notre tableau et vérifie les correspondances. Les résultats de tous les noms sont enregistrés dans un tableau, qui est renvoyé comme résultat. Étant donné qu'une cellule n'affiche qu'une seule variable à la fois, elle affiche la première valeur du tableau.
Vous pouvez vérifier cela par vous-même. Si vous modifiez la première plage en A3: A13, le résultat passera à 2. Cela est dû au fait que le nom d'Eiliyah est deuxième dans la liste et que cette valeur est maintenant enregistrée en premier dans le tableau. Si vous modifiez la première plage en A7: A13, vous obtenez à nouveau 1 car le nom de Tiah apparaît en premier à la première position de l'ensemble de données que nous vérifions.
Utilisation de la fonction FREQUENCY
Maintenant que nous avons changé les noms en valeurs numériques, nous pouvons utiliser la fonction FREQUENCY. Similaire à MATCH, il nécessite une cible à rechercher et un ensemble de données à vérifier. De la même manière que MATCH, nous ne voulons pas rechercher une seule valeur, nous voulons que la fonction vérifie chaque élément de notre liste.
La cible que nous voulons que la fonction FREQUENCY vérifie est chaque élément du tableau que notre fonction MATCH a renvoyé. Et nous voulons vérifier l'ensemble de données renvoyé par la fonction MATCH. Ainsi, nous envoyons la fonction MATCH que nous avons créée ci-dessus pour les deux paramètres.
Si vous recherchez des nombres uniques et que vous avez sauté l'étape précédente, vous enverrez la plage de nombres comme deux paramètres. Pour rechercher tous les nombres de votre liste, vous devrez également utiliser une fonction de tableau, alors n'oubliez pas d'appuyer sur Ctrl + Maj + Entrée après avoir entré la formule si vous utilisez une ancienne version d'Excel.
Maintenant, notre résultat est 2. Encore une fois, notre fonction renvoie un tableau. Il renvoie un tableau du nombre de fois où chaque valeur unique est apparue. La cellule affiche la première valeur du tableau. Dans ce cas, le nom de Tiah apparaît deux fois, donc la fréquence renvoyée est 2.
Utilisation de la fonction IF
Maintenant, notre tableau a le même nombre de valeurs que nous avons des valeurs uniques. Mais nous n'avons pas tout à fait fini. Nous avons besoin d'un moyen d'additionner cela. Si nous convertissons toutes les valeurs du tableau en 1 et les additionnons, nous saurons enfin combien de valeurs uniques nous avons.
Nous pouvons créer une fonction IF qui change toutes les valeurs supérieures à zéro à 1. Ensuite, toutes les valeurs seront égales à 1.
Pour ce faire, nous voulons que notre fonction IF vérifie si les valeurs de notre tableau FREQUENCY sont supérieures à zéro. Si c'est vrai, il doit renvoyer la valeur 1. Vous remarquerez que maintenant la première valeur du tableau renvoie comme un.
Utilisation de la fonction SUM
Nous sommes dans la dernière ligne droite! La dernière étape consiste à faire la somme du tableau.
Enveloppez la fonction précédente dans une fonction SOMME. Fini! Notre formule finale est donc:
{=SUM(IF(FREQUENCY(MATCH(A2:A13, A2:A13, 0), MATCH(A2:A13, A2:A13, 0)) >0, 1))}Compter les entrées uniques dans Excel
Il s'agit d'une fonction avancée qui nécessite beaucoup de connaissances sur Excel. Cela peut être intimidant d'essayer. Mais, une fois configuré, cela peut être très utile, il peut donc être utile de parcourir notre explication pour vous assurer que vous la comprenez.
Si vous n'avez pas besoin de compter les entrées uniques souvent, le conseil rapide et sale de supprimer les valeurs en double fonctionnera à la rigueur!