19. Excel Comment compter le nombre de valeurs uniques ?

---

1^ère solution

Pour compter le nombre de valeurs uniques en une plage de cellules A3:A17 par exemple,

1 - Si la plage ne contient pas de cellules vides, utilisez la formule :

=SOMMEPROD(1/NB.SI(A3:A17;A3:A17))

2 - Si la plage peut contenir des cellules vides, utilisez la formule :

=SOMMEPROD((A3:A17<>"")/NB.SI(A3:A17;A3:A17&""))

Explications

Nous allons expliquer petit à petit ces formules et c’est l’occasion pour rappeler un certain nombre de fonctions Excel.

Soit l’extrait suivant :

La formule en cellule B3 est :

=NB.SI($A$3:$A$17;A3)

Cette formule compte le nombre d’occurrences de la valeur en A3 dans la plage A3:A17. Les signes « $ » sont ajoutés pour la copie incrémentée.

En C3 c’est l’inverse de la valeur en B3 soit la formule :

=1/B3

Et en cellule C18, c’est la somme des valeurs à la colonne C :

=SOMME(C3:C17)

La valeur obtenue à la cellule C18 est bien le nombre de valeurs uniques de la colonne A, car chaque valeur unique est comptée par 1.

Prenons par exemple une valeur existant 3 fois, nous aurons en colonne C pour chaque occurrence :

0,3333333

En ajoutons cette valeur 3 fois, nous aurons 1.

De cette manière, nous avons calculé le nombre de valeurs uniques, mais nous pouvons le faire sans passer par une colonne intermédiaire. La formule est comme indiqué plus haut :

=SOMMEPROD(1/NB.SI(A3:A17;A3:A17))

Ici la fonction NB.SI a eu en 2^ème argument une plage de cellules. Dans ce cas la comparaison se fait avec la valeur de chaque cellule de la plage et retourne un tableau de valeurs avec autant d’élément que le nombre de cellules de la plage.

La fonction SOMMEPROD avec un seul argument nous donne la somme des éléments du tableau donné. Ce sont les mêmes valeurs que celles de la colonne C de l’extrait précédent.

Afin de vous aider à mieux comprendre, je vous montre les éléments du tableau retournée par la fonction NB.SI, ainsi que les éléments inverses, en utilisant la fonction INDEX.

En cellule C3, la formule est :

=INDEX(NB.SI($A$3:$A$17;$A$3:$A$17);B3)

En cellule D3, la formule est :

=INDEX(1/NB.SI($A$3:$A$17;$A$3:$A$17);B3)

Traitement des cellules vides

Si nous ajoutons une cellule vide à la plage A3:A17, alors l’expression

NB.SI(A3:A17;A3:A17)

Retournera un tableau avec un élément égal à 0. L’expression

1/NB.SI(A3:A17;A3:A17)

Retournera un tableau avec un élément égal à l’erreur #DIV/0! et la somme retournée par SOMMEPROD sera également l’erreur #DIV/0!

La raison est que la comparaison avec une cellule vide dans la fonction NB.SI donne FAUX. Mais, nous pouvons contourner ce problème en concaténant la valeur à comparer avec la chaîne vide "", car la comparaison de la cellule vide avec la chaîne vide donne VRAI.

La formule à utiliser est donc :

=SOMMEPROD(1/NB.SI(A3:A17;A3:A17&""))

De cette manière, nous évitons l’erreur #DIV/0!. Mais, les cellules vides seront également comptées par 1.

Si nous ne voulons pas compter les cellules vides, alors il faut utiliser la formule :

=SOMMEPROD((A3:A17<>"")/NB.SI(A3:A17;A3:A17&""))

2^ème solution

Pour compter le nombre de valeurs uniques numériques dans une plage A3:A17, utilisez la formule :

=SOMME(SI(FREQUENCE(A3:A17;A3:A17)>0,1;1;0))

Pour compter le nombre de valeurs uniques textes ou numériques en la plage A3:A17 à condition que cette plage ne contienne aucune cellule vide, utilisez la formule :

=SOMME(SI(FREQUENCE(EQUIV(A3:A17;A3:A17;0);EQUIV(A3:A17;A3:A17;0))>0,1;1;0))

Pour compter le nombre de valeurs uniques textes ou numériques en la plage A3:A17, utilisez la formule :

=SOMME(SI(FREQUENCE(SI(NBCAR(A3:A17)>0;EQUIV(A3:A17;A3:A17;0);"");SI(NBCAR(A3:A17)>0;EQUIV(A3:A17;A3:A17;0);""))>0,1;1;0))

Explications

Rappel concernant la fonction FREQUENCE :

La fonction FREQUENCE prend deux arguments et retourne un tableau d’éléments. Soit l’exemple suivant :

Le 2^ème argument sert pour définir des intervalles. En ce qui concerne l’exemple donné ci-dessus où le 2^ème argument est la plage B3:B6, les intervalles sont :

• Les valeurs inférieures ou égales à 1
• Les valeurs supérieures à 1 et inférieures ou égales à 3
• Les valeurs supérieures à 3 et inférieures ou égales à 5
• Les valeurs supérieures à 5 et inférieures ou égales à 7
• Les valeurs supérieures à 7

A chacun de ces intervalles correspond un élément du tableau retourné par la fonction FREQUENCE et dont la valeur est le nombre de valeurs du premier argument A2:A16 comprises dans l’intervalle correspondant.

Par exemple le premier élément du tableau retourné est le nombre de valeurs dans la plage A2:A16 qui sont inférieures ou égale à 1.

Le dernier élément du tableau retourné est le nombre de valeurs dans la plage A2:A16 qui sont strictement supérieures à 7 soit 0.

Dans l’exemple précédent, les valeurs données en 2^ème argument sont ordonnées. Que se passe-t-il si nous donnons les mêmes valeurs dans un ordre différent :

Remarquons que nous obtenons les même valeurs mais dans un ordre différent. D’abord le nombre de valeurs comprises entre 3 et 5, puis le nombre de valeurs comprises entre 1 et 3.

Autre question, s’il y a une valeur en double au niveau du 2^ème argument ? Dans ce cas la valeur correspondant à la 2^ème occurrence est 0 :

Finalement, si je donne en 1^er et en 2^ème argument la même plage de cellules B3 :B17

• Devant la valeur 5, nous avons le nombre d’occurrence de cette valeur.
• Devant la valeur 3, nous avons le nombre d’occurrence de cette valeur.
• Devant la valeur 4, nous avons le nombre d’occurrence de cette valeur.
• Devant la 2^ème occurrence de la valeur 5 à la ligne 6, nous obtenons 0. Et ainsi de suite.

Le résultat est que nous avons un nombre différent de 0 devant la première occurrence de chaque nombre.

La formule suivante compte le nombre d’éléments du tableau retourné par FREQUENCE qui sont différents de 0. Ces éléments sont les 1^ères occurrences de chaque nombre de la liste :

=SOMME(SI(FREQUENCE(B3:B17;B3:B17)>=1;1;0))

Comment faire pour compter le nombre de valeurs uniques numériques et également textes ?

Comme indiqué plus haut, la formule est :

=SOMME(SI(FREQUENCE(EQUIV(B3:B17;B3:B17;0);EQUIV(B3:B17;B3:B17;0))>=1;1;0))

Par rapport à la formule précédente, j’ai juste remplacé l’adresse de la plage B3:B17 par :

EQUIV(B3:B17;B3:B17;0)

La fonction EQUIV retourne la position de la valeur en 1^er argument dans la liste donnée en 2^ème argument. Si le1^er argument est une liste d’éléments, alors la fonction retourne un tableau d’éléments.

L’expression

EQUIV(B3:B17;B3:B17;0)

Retourne un tableau dont les éléments sont les positions des premières occurrences pour chaque valeur. L’extrait suivant vous montre les éléments de ce tableau :

Donc la fonction EQUIV a retourné des nombres qui seront traités par la fonction FREQUENCE de la manière indiquée plus haut.

En cas de présence de valeur vide

La fonction EQUIV retourne l’erreur #N/A si elle reçoit en premier argument une cellule vide.

Donc, si la plage a des cellules vides, alors il faut remplacer dans la formule :

EQUIV(B3:B17;B3:B17;0)

Par

SI(NBCAR(A3:A17)>0;EQUIV(A3:A17;A3:A17;0);"")

La formule finale pour compter les valeurs uniques numériques et textes est :

=SOMME(SI(FREQUENCE(SI(NBCAR(A3:A17)>0;EQUIV(A3:A17;A3:A17;0);"");SI(NBCAR(A3:A17)>0;EQUIV(A3:A17;A3:A17;0);""))>0,1;1;0))

---

---