REPOST : nombre de valeurs différents + tri

[S't'f]

bonjour,
n'ayant pas eu réponse à mon message, malgré l'aide de Staple1600, je me permets de reposter en essayant d'être clair.

dans la colonne lettres, je souhaite connaitre le nombre de valeurs différentes. J'y arrive grâce à la formule =SOMMEPROD(1/NB.SI($A$2:$A$5;A2:A5)).
le problème, quand je filtre la colonne COULEUR par ROUGE, je n'obtiens pas le bon résultat (ici "2").

merci de m'aider,
Stéphane

 

[Staple1600]

Re : REPOST : nombre de valeurs différents + tri

re
SOURCE: Ce site n'existe plus
à essayer

Plages filtrées & Nombre de valeurs numériques différentes

{=SOMME(N(FREQUENCE(SOUS.TOTAL(9;DECALER(Rge;LIGNE(INDIRECT("1:"&LIGNES(Rge))) -1;;1));SOUS.TOTAL(9;DECALER(Rge;LIGNE(INDIRECT("1:"&LIGNES(Rge)))-1;;1)))>0)) -(SOUS.TOTAL(3;Rge)NB(Rge))}

{=NB.DIFF(SOUS.TOTAL(9;DECALER(Rge;SUITE(Rge);;1)))-(SOUS.TOTAL(3;Rge)<NB(Rge))}

{=SUM(IF(FREQUENCY(IF(LEN(A1:A12)>0,MATCH(A1:A12,A1:A12,0),""),IF(LEN(A1:A12)>0,MATCH(A1:A12,A1:A12,0),""))>0,1))}
{=SOMME(SI(FREQUENCE(SI(NBCAR(A1:A12)>0;EQUIV(A1:A12;A1:A12;0);"");SI(NBCAR(A1:A12)>0;EQUIV(A1:A12;A1:A12;0);""))>0;1)

 

[Staple1600]

Re : REPOST : nombre de valeurs différents + tri

Bonjour (et la patience est une qualité!)


Explications de la formule non comprise
SOURCE:Ce lien n'existe plus
Comment compter le nombre de valeurs différentes dans une plage ? "=SOMME(SI(NON(ESTVIDE(données));1/NB.SI(données;données);"zaza"))
Attention, cette formule ne marche pas s'il y a des valeurs 0 dans *Données*.

Explications :

Premier cas : la plage Données ne contient aucune cellule vide.
La formule matricielle à appliquer est simplement :
{=SOMME(1/NB.SI(Données;Données))}


Prenons un exemple : la plage Données contient les valeurs
18 45 18 "zaza" 18 45
Donc, le résultat à obtenir est 3.
1er rappel sur le fonctionnement de NB.SI :
=NB.SI(plage;valeur) retourne le nombre de fois que *valeur* apparaît dans
*plage*. Dans notre cas présent, =NB.SI(Données;18) retournerait 3.
Attention : ce n'est pas une formule matricielle.

On passe maintenant à la dimension matricielle. Comment ? En remplaçant le
deuxième argument de NB.SI - normalement une simple valeur - par un tableau
de valeurs, par exemple, pourquoi pas :
=NB.SI(Données;Données) à valider matriciellement dans une plage de six
cellules comme *Données*

On génère ainsi le tableau de valeurs suivant :
3 2 3 1 3 2
Les 3 correspondent aux trois fois où on tombe sur 18, les 2 les deux fois
où on tombe sur 45, le 1 la seule fois où on tombe sur "zaza".
(Tu remarqueras que, dans la phrase précédente, ce n'est pas par hasard que
le "nombre de fois" est exactement la valeur renvoyée, et c'est l'origine de
l'astuce ci-après).

Avec ça, on va calculer la somme des inverses :
=SOMME(1/NB.SI(Données;Données)) à valider matriciellement dans une cellule
Cela donne :
1/3 +1/2+1/3+1/1+1/3+1/2=3, le nombre cherché.
Toute l'astuce est là : si on prend l'exemple de 18, il sera décompté pour
1/3+1/3+1/3=3*1/3=1, et de même pour toutes les autres valeurs.

Deuxième cas : la plage *Données* contient au moins une cellule vide.
2ème rappel sur le fonctionnement de NB.SI :
=NB.SI(plage;A1) renvoie 0 si A1 est vide. Là où c'est gênant, c'est quand
on calcule 1/NB.SI(plage;plage), à cause du message #DIV/0! que SOMME ne
sait pas bien absorber. Il faut donc remplacer ces messages d'erreur
éventuels par un truc neutre comme "misange" qui n'intervient pas dans la
somme
(mais on pourrait très bien mettre "zaza" à la place de "misange").
C'est le rôle du test matriciel NON(ESTVIDE(Données)), d'où la formule :

{=SOMME(SI(NON(ESTVIDE(données));1/NB.SI(Données;Données);"misange"))}

Remarque
On peut faire légèrement plus simple en inversant le test :
{=SOMME(SI(ESTVIDE(données);"flo";1/NB.SI(Données;Données))}