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.

 

Excel formation - Compter les valeurs uniques - 01

 

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

Tutoriel Vidéo

 

1. Compter le nombre de lignes

Pour illustrer ce tutoriel, nous allons partir de l’exemple suivant :

Excel formation - Compter les valeurs uniques - 02

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 » :

Excel formation - Compter les valeurs uniques - 03

 

 =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 :

Excel formation - Compter les valeurs uniques - 04

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) 

Excel formation - Compter les valeurs uniques - 05

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].

Excel formation - Compter les valeurs uniques - 06

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 :

Excel formation - Compter les valeurs uniques - 07

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 :

Excel formation - Compter les valeurs uniques - 08

Nous retrouvons ainsi chaque commercial autant de fois que ce qui est spécifié dans la colonne « Répétition ».

Excel formation - Compter les valeurs uniques - 09

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 :

Excel formation - Compter les valeurs uniques - 10

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 :

Excel formation - Compter les valeurs uniques - 11

Ne reste ensuite plus qu’à effectuer une somme des valeurs de cette colonne (soit une somme des cellules D7 à D74) :

Excel formation - Compter les valeurs uniques - 12

Ce qui nous donne alors un résultat de 11 commerciaux :

Excel formation - Compter les valeurs uniques - 13

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 :

Excel formation - Compter les valeurs uniques - 14

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 :

Excel formation - Compter les valeurs uniques - 15

 

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() :

Excel formation - Compter les valeurs uniques - 16

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 :

Excel formation - Compter les valeurs uniques - 17

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 !

Excel formation - Compter les valeurs uniques - 18

 

 

 



Articles qui pourraient vous intéresser

Comment écrire un nombre en toutes lettres avec une fonction Excel ?
Comment compter le nombre de mots contenus dans une cellule Excel ?
Comment comparer automatiquement deux feuilles de calcul sur Excel – On développe une mini-application ensemble
56 astuces pour grands débutants sur Excel !
Comment lancer les dés sur Excel ?
Comment créer une frise chronologique à partir d’un graphique Excel ?
Suivre les matchs de l’Euro 2020 sur Excel, le fichier !
Comment mélanger une liste de manière aléatoire sur Excel ?
Comment obtenir des informations sur l’utilisateur du fichier Excel avec la fonction INFORMATIONS() ?
Faut-il utiliser les macro-commandes VBA sur Excel ?
Comment exporter une feuille dans un classeur (ouvert ou fermé) sur Excel ?
Sortez du lot : comment créer un CV moderne et impactant avec Excel

Contact - Plan du site - À propos - Contact

© Excel-Formation 2018 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.