Comment compter les valeurs uniques (dénombrement sans doublon) sur Excel
Aujourd’hui, je vous propose de répondre à la question posée par Jean-Christophe SALLOT dans l’espace des commentaires de la chaîne YouTube qui souhaite savoir comment procéder pour dénombrer des données présentes dans une colonne, sans tenir compte des répétitions. Et pour répondre à cette question, nous allons découvrir trois méthodes différentes, chacune d’entre elles disposant de ces avantages et inconvénients que nous allons également découvrir dans ce tutoriel.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Compter le nombre de lignes
Pour illustrer ce tutoriel, nous allons partir de l’exemple suivant :
Nous retrouvons dans ce tableau une liste des ventes réalisées par des commerciaux d’une entreprise, jour par jour.
Nous allons souhaitez y extraire deux types d’informations :
- Tout d’abord nous allons vouloir connaitre le nombre de ventes totales réalisées au cours de l’année 2019,
- Ensuite, nous allons souhaiter connaître le nombre de commerciaux ayant réalisés des ventes. Pour cela nous allons découvrir trois méthodes en allant de la méthode la plus simple vers la plus complexe, chacune de ces méthodes ayant des avantages et des inconvénients que nous allons également détailler.
Pour dénombrer le nombre de ventes, il suffit tout simplement d’utiliser la fonction Excel NB() qui permet de retourner le nombre de lignes contenues dans la plage de cellules passée en argument.
Nous allons par exemple dénombrer le nombre de lignes contenues dans la colonne « Montant Vente » :
=NB(C7:C74)
Ici les noms des commerciaux se trouvent dans la plage des cellules allant de la cellule C7 jusqu’à la cellule C74.
Nous pouvons sélectionner ces cellules de deux manières :
- Soit à la souris si la plage n’est pas trop importante (en sélectionnant la première cellule, puis en faisant glisser le curseur de la souris jusqu’à la dernière, en conservant le bouton enfoncé),
- Soit au clavier, en sélectionnant une fois encore la première cellule, puis en sélectionnant automatiquement toutes les cellules de la colonne en appuyant sur les touches [Ctrl]+[Majuscule]+[↓]
Attention, la fonction NB() ne permet de dénombrer que les valeurs numériques !
Si nous avions sélectionné la colonne « Commercial », la fonction nous aurait alors retournée la valeur zéro :
Note : Pour éviter ce problème, nous pourrions bien entendu utiliser la formule NBVAL()
=NBVAL(B7:B74)
En plus de cela, nous ne pouvons pas utiliser la fonction NB() pour effectuer un dénombrement de valeurs uniques car celles-ci retourner tout simplement le nombre de valeurs contenues dans une plage, peut importe la fréquence de répétitions de celles-ci.
Voyons maintenant trois méthodes qui vont nous permettre de réaliser cette opération.
2. Utiliser une colonne intermédiaire
La méthode la plus simple pour effectuer un dénombrement des valeurs uniques va consister à insérer simplement une colonne pour effectuer un calcul intermédiaire.
Dans cette colonne, nous allons commercer à calculer le nombre de répétition de chacun des noms de commerciaux.
Et pour cela, nous allons utiliser une déclinaison de la fonction NB() que nous venons de découvrir juste avant, il s’agit de la fonction NB.SI().
La différence entre ces deux fonctions vient du fait qu’ici nous allons retrouver un second argument qui va permettre d’insérer un critère de teste à appliquer au dénombrement.
Nous allons donc chercher à connaître le nombre de fois que chaque commercial se répète :
=NB.SI($B$7:$B$74;B7)
Les cellules passées en argument sont alors les suivantes :
- $B$7:$B$74 : il s’agit des coordonnées de cellules dans lesquelles nous retrouvons les noms de tous les commerciaux. Vous noterez au passage que ces coordonnées sont saisies en références absolues, avec des symboles dollars qui vont permettre de fixer celles-ci lorsque plus tard nous allons étendre la formule vers le bas. Pour en savoir plus sur l’utilisation du dollar et sur les types de références (relatives, absolues ou mixtes), je vous invite à consulter ce tutoriel.
- B7 : Correspond au nom du commercial présenté sur la ligne de la formule actuelle. Il s’agit ici d’une référence absolue, qui va devoir être adaptée lorsque nous allons étendre la formule.
Nous pouvons ensuite valider la formule en appuyant sur la touche [Entrée].
Ensuite, pour dupliquer automatiquement la formule sur les autres cellules du tableau, il suffit de double-cliquer sur la poignée de recopie (le petit carré qui se trouve en bas à droite de la cellule :
Pour vérifier les données présentées, nous pouvons classer le tableau en fonction des noms des commerciaux.
Pour cela, nous sélectionnons l’un des noms des commerciaux, nous effectuons un clic-droit > Trier > Trier de A à Z :
Nous retrouvons ainsi chaque commercial autant de fois que ce qui est spécifié dans la colonne « Répétition ».
Ensuite, l’objectif va être que la somme des répétitions d’un commercial donné soit égal à « 1 », nous allons donc simplement diviser la valeur « 1 » par le résultat obtenu :
Bien entendu, ici Amaury Labbé n’a réalisé qu’une seule vente, cela ne changera rien ici.
En revanche, lorsque nous étendons la formule sur les cellules du bas, la formule prend tout son sens :
Ne reste ensuite plus qu’à effectuer une somme des valeurs de cette colonne (soit une somme des cellules D7 à D74) :
Ce qui nous donne alors un résultat de 11 commerciaux :
Comme vous pouvez le constater, cette méthode fonctionne très bien, mais le fait de devoir créer une colonne dédiée peut parfois être contraignant.
3. Utiliser une formule matricielle
Pour réaliser l’équivalent du système que nous venons de créer dans une cellule unique, nous pouvons encore utiliser une formule matricielle.
Contrairement aux formules classiques, une formule matricielle permet de valider plusieurs formules en même temps. De plus, elle n'est pas à valider comme n'importe quelle formule avec la touche [Entrée] mais avec la combinaison [Ctrl]+[Maj]+[Entrée].
Lorsqu’Excel identifie une formule matricielle, automatiquement des accolades vont être ajoutées au début et à la fin de la formule. Ces accolades s’ajoutent et se suppriment automatiquement et ne doivent surtout pas ajoutées à la main.
Ici, nous allons encapsuler la formule matricielle dans une fonction SOMME() pour récupérer directement le résultat correspondant dans une seule formule.
Cela dit, nous allons également de voir modifier légèrement la formule, pour effectuer l’analyse sur toutes les cellules de la colonne B7 à B74 :
{=SOMME(1/NB.SI($B$7:$B$74;$B$7:$B$74))}
La référence absolue de la plage B7 à B74 vient donc remplacer la référence à la cellule unique B7.
Excel nous retourne alors un résultat identique, à savoir 11 commerciaux présents dans la base :
Note : pour les allergiques des formules matricielles, une alternative consiste à utiliser la fonction SOMMEPROD() de la même manière mais en tant que formule classique (évidemment, en validant simplement avec la touche [Entrée] du clavier :
=SOMMEPROD(1/NB.SI(A20:A200;A20:A200))
Cette méthode est donc déjà bien plus rapide à mettre en place, et ne nécessite pas de créer une colonne dédiée dans le tableau.
En revanche, il suffit que l’une des cellules de la colonne dénombrée soit vide pour que la formule retourne une erreur de division par zéro impossible :
4. L’ultime formule de dénombrement de valeurs uniques
Pour cette formule ultime, nous allons partir de la dernière formule que nous venons de voir juste avant avec l’utilisation de SOMMEPROD() :
Ici, nous allons valider la formule en tant que formule matricielle :
{=SOMMEPROD(1/NB.SI($B$7:$B$74;$B$7:$B$74))}
Une fois encore, le résultat retourné sera une erreur :
Il suffit alors d’ajouter une fonction SI() dans la fonction SOMMEPROD() pour s’assurer que les cellules vides ne seront pas prises en comptes (et éviter les erreurs #DIV/0!) :
{=SOMMEPROD(SI($B$7:$B$74<>"";1/NB.SI($B$7:$B$74;$B$7:$B$74)))}
Excel nous retourne alors la bonne valeur, à savoir les 11 commerciaux, malgré la présence d’une cellule sans donnée !
