Compter les doublons

[GillesC]

Bonjour à tous.
En recherchant une formule pour compter les doublons dans mes colonnes, j'ai trouvé sur le forum une formule de JeanMarie qui me compte les valeurs uniques :
=SOMMEPROD((1/NB.SI(A1:A100;A1:A100))*1)
J'obtiens donc mon nombre de doublons en déduisant le nombre de valeurs de ma colonne mais...
J'aimerais comprendre pourquoi "1/NB.SI"
Si quelqu'un pouvait éclairer mes 3 pauvres neurones...
Merci et bonne journée

 

[Catrice]

Re : Compter les doublons

Bonjour,

Je crois que c'est une formule de Monique ...
Comment ça marche ?
Voir le fichier joint pour comprendre.

On peut simplifier un peu : =SOMMEPROD(1/NB.SI(A1:A10;A1:A10))
Le NB.SI renvoie une matrice : {5;2;3;5;2;5;5;5;3;3}
Si on évalue, ca donne : =SOMMEPROD(1/{5;2;3;5;2;5;5;5;3;3})
Il y a 5 cinq, 3 trois et 2 deux.
Si on évalue encore :=SOMMEPROD({1/5;1/2;1/3;1/5;1/2;1/5;1/5;1/5;1/3;1/3})

On va donc faire la somme de 5 x 1/5 + 3 x 1/3 + 2 x 1/2
(ou 1/5 + 1/2 + 1/3 + 1/5 + 1/2 + 1/5 + 1/5 + 1/5 + 1/3 + 1/3)
Donc la somme de 5/5 + 3/3 + 2/2 = 3

 

[GillesC]

Re : Compter les doublons

Ok j'ai compris! merci beaucoup Catrice pour ces lumineuses explications

 

[Monique]

Re : Compter les doublons

Bonjour,

Non non, je n'y suis pour rien

Un complément : s'il y a des cellules vides, ça renvoie #DIV/0!
Dans ce cas-là :
=SOMME(SI(A1:A10<>"";1/NB.SI(A1:A10;A1:A10)))
Celle-là est à valider par ctrl, maj et entrée

On peut aussi mettre des critères dans ce style :
=SOMME(SI(A1:A10>10;1/NB.SI(A1:A10;A1:A10)))
=SOMME(SI(ESTTEXTE(A1:A10);1/NB.SI(A1:A10;A1:A10)))

 

[GillesC]

Re : Compter les doublons

Merci Monique pour ce complément d'information.
Pour rejoindre Catrice, il est vrai que quand on voit une solution ingénieuse en matière de SOMMEPROD, SOMME.SI etc on pense automatiquement :"hum y a ptêt ben du Monique là dessous!"
++

 

[JeanMarie]

Re : Compter les doublons

Bonsoir

Une autre solution, avec une formule matricielle.

=SOMME((FREQUENCE(SI(A1:A100="";"";EQUIV(A1:A100;A1:A100;0));LIGNE(A1:A100))>1)*1)

Prend en compte, les cellules vides, le texte, les valeurs numériques.

@+Jean-Marie

 

[bengourdon]

Re : Compter les doublons

Bonjour,


J'ai également besoin de faire ce type de recherche sur une matrice :

A|B|C|D
1|5|9|7
5|7|8|2
2|3|1|5
4|5|5|9

Mon but serai de compter le nombre d'occurrence dans toute la matrice sans les doublons (soit ici 8 occurrence différentes)

voici la formule que j'ai utilisé :

=SOMMEPROD(1/NB.SI(a1:d4;a1:d4))

Cela fonctionne nickel ..... malheureusement ma matrice fait 4 colonnes sur 10000 lignes environ et du coup les performances sont complètement folles!

Existe t il une fonction plus optimisée ?

Merci

Ben

 

[JeanMarie]

Re : Compter les doublons

Bonsoir Ben

Essaye la formule

=SOMME((FREQUENCE(A:D;A:D)>0)*1)

@+Jean-Marie