Compter le nombre de valeurs distinctes

Lorsque nous disposons d’une liste de valeurs répétées un nombre aléatoire de fois, plusieurs manipulations sont possibles pour compter le nombre de valeurs différentes : suppression des doublons, création d’un TCD. Autant de solutions qui nécessitent une action de notre part à chaque fois que nous voulons ce décompte. Créons plutôt une formule pour calculer ce dénombrement.


 

Présentation du contexte

Pour illustrer notre formule, prenons l’exemple suivant : une liste de commandes contenant un à plusieurs articles, donc autant de lignes. Nous cherchons à connaître le nombre de commandes contenues dans la liste.

Liste commandes pour decompte valeurs distinctes

La liste est réduite et les numéros se suivent, il y a donc 5 commandes distinctes dans cette liste. Dans ce cas, le décompte est simple mais cela nous permettra de vérifier notre formule.

 

Présentation de la formule

Pour un décompte des valeurs uniques, on peut utiliser la formule suivante.

Formule decompte valeurs distinctes

La formule n’est pas encadrée par des accolades mais la fonction SOMMEPROD nous donne un indice, il s’agit d’une formule matricielle.
Et son résultat, comme attendu, est bien 5.

Resultat formule decompte valeurs distinctes

Décomposons cette formule et voyons comment elle marche.

 

Explications de la formule

Commençons avec la vraie fonction de dénombrement de la formule : NB.SI.
Habituellement, cette fonction prend deux arguments : d’abord une plage de cellules puis une valeur unique, qui peut être une référence à une cellule. NB.SI décompte le nombre de valeurs dans la plage correspondant à cette valeur.

Dans ce cas, nous utilisons une plage de cellules en guise de critère, la formule devient ainsi matricielle. Et le décompte est évalué pour chaque ligne de la plage.

NB.SI(A2:A16;A2:A16) : cette partie renvoie un ‘tableau’ avec, sur chaque ligne, le nombre de fois où le numéro de commande apparait dans l’intégralité de la liste. Voilà à quoi ressemble le résultat de cette première partie.

Nombre occurrences liste commandes

1/ NB.SI(...) : on prend ensuite l’inverse de chaque nombre pour obtenir une fraction.
Si une valeur n’apparait qu’une fois, on conserve le nombre 1. Si elle apparait 2 fois, on obtient 1/2, 3 fois 1/3...

Inverse nombre occurrences liste commandes

SOMMEPROD( 1/NB.SI(...) ) : quel que soit le nombre d’occurrences, l’inverse permet d’obtenir une somme égale à 1 sur chaque commande. La fonction SOMMEPROD additionne donc une série de 1.

Somme fraction 1 valeurs distinctes

Notre liste est triée, schématiquement c’est plus simple à représenter. Mais le calcul fonctionne à l’identique sur une liste non ordonnée.

Compléments d’information

On peut remplacer la fonction SOMMEPROD par SOMME. Mais dans ce cas, pour que la formule soit reconnue comme matricielle, il faut la valider avec Ctrl+Maj+Entrée. La formule devient alors simplement :

=SOMME(1/NB.SI(A2:A16;A2:A16))

Comme expliqué dans cet article, SOMMEPROD est une fonction matricielle par défaut. Ainsi, il suffit de valider la première formule avec Entrée pour le même résultat.

Dites-nous en commentaires si vous avez une autre formule ou une autre solution pour dénombrer les occurrences uniques dans vos fichiers !

 

Abonnez-vous à la newsletter

Illustration couverture ebook 100 trucs et astuces pour Excel-ler au travail

Et recevez gratuitement l'ebook 100 trucs et astuces pour Excel-ler au travail (5€ sur Amazon)

Votre adresse mail ne sera pas communiquée et vous pourrez vous désabonner à tout moment.

Taggé , , , .Mettre en favori le Permaliens.

Laisser un commentaire

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.