Excel formule : compter le nombre de valeurs différentes

Résolu/Fermé
Signaler
Messages postés
10
Date d'inscription
dimanche 23 janvier 2011
Statut
Membre
Dernière intervention
8 octobre 2014
-
 PauMS -
Bonjour,



je cherche a compter le nombre de valeurs uniques dans une colonne. et je n'arrive pas à compter cela. Quelqu'un a t'il une idée.

merci
A voir également:

4 réponses

Messages postés
2
Date d'inscription
mercredi 23 septembre 2015
Statut
Membre
Dernière intervention
24 septembre 2015
46
Mon commentaire à la réponse de 'g' du 3 déc. 2012 à 15:14 a été supprimé en justifiant que "Le message a été jugé obsolète.", mais cette conversation ne permet pas de savoir au final quelle est la meilleure solution! Je donne donc mon avis/résume:

La meilleure solution c'est la formule
[A] {
=SOMME(SI(A1:A10<>"";1/NB.SI(A1:A10;A1:A10)))
} (la dernière proposée dans le sujet, par tongtong)

Elle donne des comptes exacts de valeurs distinctes dans la plage de cellules pour tous les types de valeurs testées: chaines de caractères de l'alphabet, chiffres, et non-alphanumériques (+, /,...), mais aussi les valeurs d'erreur (#DIV/0!, #N/A,...) et de logique (VRAI, FAUX), et même donc la valeur cellule vide (qui n'est pas comptée comme valeur distincte).
Attention, elle peut être longue à calculer (9sec de calcul pour 50 cellules, 15sec pour 500, 25sec pour 5000, ... erreur pour 50000)

La formule secondairement proposée, par ccm81, [B] {
=SOMMEPROD(1/NB.SI(A1:A7;A1:A7))
} fonctionne aussi très bien, à condition qu'il n'y ait pas de cellules vides dans la plage recherchée, ou qu'on cherche justement à compter ce vide comme une valeur distincte:
Pour éviter qu'elle donne une valeur d'erreur (#DIV/0!) dès qu'une cellule est vide, le mieux est de remplacer les cellules vides par une valeur particulière, ex 'vide' (ou un point, voire un espace mais attention aux oublis plus distinguables). Le nombre de valeurs distinctes trouvées inclut alors bien sur cette valeur de "vide", ce qui peut etre désiré parfois. Même remarque sur la longueur de calcul.

Enfin la formule en premier, par g [C] {
=SOMME(SI(NB.SI(A1:A7;A1:A7)=1;1))
} est à oublier: elle donne des valeurs inexactes dès qu'une valeur est doublée... Il suffit de voir son compte évoluer étrangement en comptant une plage de cellules contenant un seul caractere (elle trouve 1), puis 2 fois ce meme caractère (elle trouve 0), puis 3 (0), ...
54
Messages postés
2501
Date d'inscription
mercredi 3 février 2010
Statut
Membre
Dernière intervention
16 janvier 2022
1 013
Bonjour,
Il faut rendre à César...
Je pense que j'ai trouvé cette formule chez Jacques Boisgontier.
0
Messages postés
16547
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
19 janvier 2022
3 258 >
Messages postés
2501
Date d'inscription
mercredi 3 février 2010
Statut
Membre
Dernière intervention
16 janvier 2022

Salut tongtong



L'origine de la formule vient des forums anglophones comme le citait Laurent Longre il y a 10 ou 15 ans:

. 
.....
"2. Plage contenant tout type de valeurs
=SOMME(1/NB.SI(Plage;Plage))

Cette formule est issue des forums anglophones sur Excel.
NB.SI(Plage;Plage)

renvoie une matrice contenant le nombre de répétitions de chaque valeur à l'intérieur de la plage indiquée. Si une valeur est répétée N fois, la matrice correspondante comporte N fois le nombre N.
En faisant la somme des inverses de ces nombres, on obtient N*(1/N), soit 1 pour chacune des valeurs dupliquées. Le résultat est le nombre de valeurs uniques de la plage.
Contrairement à la formule précédente, celle-ci compte le nombre de valeurs différentes quel que soit leur type (nombre, texte...) mais la plage utilisée ne doit pas comprendre de cellules vides, à moins de contenir également au moins un nombre 0 ou qu'on la modifie ainsi :
=SOMME(SI(Plage<>"";1/NB.SI(Plage;Plage))) "....
0
Messages postés
2501
Date d'inscription
mercredi 3 février 2010
Statut
Membre
Dernière intervention
16 janvier 2022
1 013 >
Messages postés
16547
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
19 janvier 2022

Salut michel
Et merci pour cet historique hyper-documenté.
0
Messages postés
2
Date d'inscription
mercredi 23 septembre 2015
Statut
Membre
Dernière intervention
24 septembre 2015
46
J'étais perplexe avec le 1/N dans la formule, et à présent convaincu par l'explication. Plus les ancêtres de César en prime. Merci Michel (et Tongtong, Boisgontier!).
0
Merci beaucoup, pour moi c'est la formule avec SOMMEPROD qui fonctionne (j'ai voulu compter le nombre de dates différentes dans une liste ou parfois il y a plusieurs occurrences de la même date)
0
Messages postés
1263
Date d'inscription
vendredi 23 avril 2004
Statut
Membre
Dernière intervention
15 mai 2017
576
Bonjour,

Avec la formule matricielle :

{=SOMME(SI(NB.SI(A3:A30;A3:A30)=1;1))}

Exemple pour la colonne A3 à A30 à adapter.
Les {} ne doivent pas être saisis au clavier mais par validation par Ctrl+Maj+Entrée.

Cordialement.
9
Messages postés
10197
Date d'inscription
lundi 18 octobre 2010
Statut
Membre
Dernière intervention
14 janvier 2022
2 275
Bonjour

essaies comme ça
=SOMMEPROD(1/NB.SI(plage;plage))
toujours formule matricielle

Bonne suite
4
Bonjour,

La formule =SOMMEPROD(1/NB.SI(plage;plage)) fonctionne bien si les cellules sont renseignées, mais si les cellules ne sont pas renseignées elle ne fonctionne plus, connaissez vous une modification pour l'appliquez ?

Merci
0
Messages postés
2501
Date d'inscription
mercredi 3 février 2010
Statut
Membre
Dernière intervention
16 janvier 2022
1 013
Bonjour,
Avec des cellules vides dans le champ:
=SOMME(SI(A1:A10<>"";1/NB.SI(A1:A10;A1:A10)))
Validation en matricielle CTRL+SHIFT+ENTER
0
Bonjour,
j'utilise cette formule habituellement, mais là, ma plage de donnée est =(A1:A10;A12:A16). si je tape votre formule : =SOMME(SI(A1:A10;A12:16<>"";1/NB.SI(A1:A10;A12:16;A1:A10;A12:16))) ca m'indique en résultats : //VALEUR.
Comment résoudre ce problème s'il vous plait
0
Messages postés
16547
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
19 janvier 2022
3 258 > littlemiss6
Bonjour
Il faut faire 2 fois la formule Somm(si(...
1 pour A1:A10 1 autre pour A12:16
et les additionner
0
Messages postés
24270
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
20 janvier 2022
7 029
Bonjour,

Utilise la formule 'normale'.
Si A11 est toujours vide pas de problème, si elle est toujours remplie tu t'arranges pour que ce soit par une valeur unique que tu ne retrouves pas dans A1:A16 et tu fais formule - 1

eric
0
Messages postés
10
Date d'inscription
dimanche 23 janvier 2011
Statut
Membre
Dernière intervention
8 octobre 2014
1
Merci

Ca fonctionne nickel chrome
il me manquait la notion des {]

encore merci
1