Traiter le nombre d'occurrence d'une valeur donnée dans une colonne filtrée

NeuG

XLDnaute Nouveau
Bonjour,

Je souhaite pouvoir obtenir le nombre de fois où une valeur donnée apparaît dans une colonne en tenant compte de filtres.

LA fonction Sous.total permet de compter globalement le nbre total de mes valeurs avec filtre. JE souhaite arriver à ajouter en plus un critère pour compter (uniquement les 1 par exemple).
JE veux pouvoir afficher en direct mes résultats (nbre de 0, Nbre de 1, Nbre de 2, Nbre de 3) en fonction de mes filtres (Homme ou femme, age, etc...).

Mon excel en copie pour éclairer mon besoin,
JE cherche par exemple à obtenir le nombre de fois ou la valeur 3 apparait dans une colonne en appliquant en direct mes filtres (Homme ou femme, age, etc...)

Merci d'avance pour votre aide
 

Pièces jointes

  • help.xlsx
    29 KB · Affichages: 234
  • help.xlsx
    29 KB · Affichages: 255
  • help.xlsx
    29 KB · Affichages: 258

mth

XLDnaute Barbatruc
Re : Traiter le nombre d'occurrence d'une valeur donnée dans une colonne filtrée

Bonjour,

Une formule empruntée à David84 :)
pour la cellule J7 par exemple:

Code:
=SOMMEPROD((J$13:J$22=0)*SOUS.TOTAL(3;DECALER(J$13;LIGNE(J$13:J$22)-LIGNE(J$13);0)))

Bonne soirée,

mth
 

NeuG

XLDnaute Nouveau
Re : Traiter le nombre d'occurrence d'une valeur donnée dans une colonne filtrée

Merci ça à l'air de fonctionner je vais bine vérifier.
Tu pourrais expliquer comment fonctionne cette formule ?
le sommeprod ok, le sous.total ok.
Après la fonction DECALER et la ligne (J13);0)) me dépasse.
Je ne comprends pas à quoi cela correspond.

Merci d'avance.
 

NeuG

XLDnaute Nouveau
Re : Traiter le nombre d'occurrence d'une valeur donnée dans une colonne filtrée

Merci Mth mais j'ai un problème concret :

PAr exemple en N8 (je cherche le nbre de 1 dans la colonne) j'ai écrit :
=SOMMEPROD((N$13:N$22=1)*SOUS.TOTAL(3;DECALER(N$13;LIGNE(N$13:N$22)-LIGNE(N$13);1)))
Excel me dit 4 alors que j'ai cinq 1 dans ma colonne.
Mystère...
A mon avis cela vient diu fait que ne comprends pas tout dans la formule et que je l'adapte mal sur N8 par exemple.
 

mth

XLDnaute Barbatruc
Re : Traiter le nombre d'occurrence d'une valeur donnée dans une colonne filtrée

Bonsoir Neug,

Il est plus facile de comprendre en décomposant la formule, si par exemple je veux le nombre de 2 pour la catégorie cadre dans la colonne J la formule est:

Code:
=SOMMEPROD([B](J$13:J$22=2)[/B]*[COLOR=darkred]SOUS.TOTAL(3[/COLOR];[COLOR=darkslateblue]DECALER[/COLOR](J$13;[COLOR=seagreen]LIGNE(J$13:J$22)[/COLOR]-[COLOR=green]LIGNE(J$13);[/COLOR]0)))

avec LIGNE(J$13:J$22) qui renvoie une matrice indiquant les numéros de ligne de 13 à 22, soit ceci: {13;14;15;16;17;18;19;20;21;22}
LIGNE(J$13) renvoie simplement son numéro de ligne soit : {13}

l'ensemble LIGNE(J$13:J$22)-LIGNE(J$13) permet donc d'obtenir cette matrice:
{0;1;2;3;4;5;6;7;8;9} , on enlève 13 à chaque numéro de ligne du tableau d'otigine.

La fonction Decaler(): DECALER(J$13;LIGNE(J$13:J$22)-LIGNE(J$13);0), va donc partir de la cellule J13, se décaler en ligne de 0 1 2 etc ... selon la matrice obtenue au dessus: {0;1;2;3;4;5;6;7;8;9}, et pas de décalage en colonne (d'où le zéro en dernier argument)
Elle renvoie cette matrice : {2;1;3;1;0;2;2;2;3;2} correspondant à la valeur de chaque cellule de la plage, le résultat est le même que le tableau soit filtré ou pas.

La fonction Sous.total, utilisée avec le paramètre 3 en premier argument indique que l'on cherche le nombre de celules remplies dans la plage, cette formule renvoie une matrice avec 0 si la cellule n'est pas visible, 1 si elle est visible.
Par exemple, le tableau n'est pas filtré, la fonction sous.total() renvoie cette matrice:
{1;1;1;1;1;1;1;1;1;1}, c'est à dire que toutes les cellules de la plages sont visibles et contiennent quelque chose.
Si je filtre le tableau pour n'afficher que la catégorie cadre, toutes les lignes seront masquées sauf les lignes 1 4 et 6 du tableau (correspondant au critère "cadre" donc) , et cette fonction Sous.total renvoie cette matrice:
{1;0;0;1;0;1;0;0;0;0} soit 1 quand la ligne est affichée et contient une valeur (lignes 1 4 6 du tableau) , 0 dans le cas contraire

(Si par exemple j'avais voulu faire une somme au lieu de compter le nombre de cellules contenant une valeur, j'aurais utilisé la fonction Sous.total avec le paramètre 9, et j'aurais obtenu cette matrice:
{2;0;0;1;0;2;0;0;0;0} soit les valeurs contenues dans les cellules visibles correspondant au critère cadre)

Le premier critère de la fonction Sommeprod() est (J$13:J$22=2) et renvoie une matrice avec VRAI si la cellule contient cadre et FAUX dans le cas contraire, avec le même résultat que le tableau soit filtré ou pas, soit ceci:
{VRAI;FAUX;FAUX;FAUX;FAUX;VRAI;VRAI;VRAI;FAUX;VRAI}

Le produit de nos deux matrices va donc donner cette dernière matrice: {1;0;0;0;0;1;0;0;0;0}
dont la somme fait 2


Voilà Neug, j'espère que ces quelques commentaires t'aideront à mieux comprendre.
Pour décomposer le résultat de tes formules, n'hésite pas à utiliser la touche F9, comme indiqué ici: FAQ : myDearFriend! Excel Pages: Prévisualiser le résultat d'une partie de formule


Pour ta deuxième formule, tu as inscrit 1 en décalage, remplace par 0:

Code:
=SOMMEPROD((N$13:N$22=1)*SOUS.TOTAL(3;DECALER(N$13 ;LIGNE(N$13:N$22)-LIGNE(N$13);[B][COLOR=darkred]0[/COLOR][/B])))

Très bonne soirée à toi,

mth
 

Fred_Villeneuve

XLDnaute Nouveau
Bonjour, j'ai exactement le même besoin! Merci pour la solution proposé. Elle fonctionne partiellement pour moi : sans filtre sur mes data, le résultat est bon mais dès qu'il y a un filtre je pense que la fonction décalage a un problème (si il y a des lignes cachées avant l'apparition de la ligne où le résultat recherché est présent, la formule n'identifie plus le résultat)

Voici la formule : =SOMMEPROD((C$10:C$14=C8)*SOUS.TOTAL(3,DECALER(C$10,LIGNE(C$10:C$14)-LIGNE(C$10),0)))

Avez-vous une idée du problème?

Un grand merci pour votre aide!
 

Pièces jointes

  • Capture.JPG
    Capture.JPG
    44.4 KB · Affichages: 26
  • Capture_avec filtre.JPG
    Capture_avec filtre.JPG
    41.9 KB · Affichages: 17

Discussions similaires

Statistiques des forums

Discussions
311 720
Messages
2 081 899
Membres
101 834
dernier inscrit
Jeremy06510