XL 2013 [RéSOLU] Compter nombre d'occurence dans une matrice ou tableau filtré

[gueststar]

Bonjour,

je sèche sur la formule à utiliser pour pouvoir compter le nombre d'occurrences identiques contenu dans les cellules d'un tableau (à plusieurs colonnes) tout en tenant comtpe du filtre (donc compter seulement les occurrences visibles à l'écran.)

J'ai testé la formule SOMME.PROD qui intègre une formule SOUS.TOTAL. mais je n'arrive pas à obtenir un résultat correct.
J'ai testé la formule NB-SI-ENS qui paraissait plus simple et plus directe sur ce que je voulais mais nativement elle ne tient pas compte des filtres.

Pouvez-vous m'aider svp ?

ci-joint un fichier pour l'exemple. La zone en jaune est la zone de recherche qui peut potentiellement être filtrée. les cellules en vert utilisent la formule NB.SI.ENS, les cellules en bleu utilisent la formule SOMME.PROD et SOUS.TOTAL.

 

[Robert]

Bonjour Gueststar, bonjour le forum,

Ma légendaire nullité en formules n'étant plus à prouver je te propose une solution VBA. Mais je ne suis pas sûr d'avoir bien compris ta requête...
Dans ton fichier modifié en pièce jointe, tape une valeur dans la ligne 2 et le code renverra le nombre de fois que celle valeur apparaît dans les cellules visibles de la colonne de la boule correspondante. Si tu filtres après avoir défini des valeurs le calcul se refait automatiquement...
Les codes :

Private Sub Worksheet_Change(ByVal Target As Range) 'au changement dans l'onglet
Dim DL As Integer 'déclare la variable DL (Dernière Ligne)
Dim COL As Byte 'déclare la variable COL (COLonne)
Dim PL As Range 'déclare la variable PL (PLage)
Dim CEL As Range 'déclare la variable CEL (CELlule)
Dim T As Integer 'déclare la variable T (Total)

If Application.Intersect(Target, Range("B2:U2")) Is Nothing Then Exit Sub 'si le changement a lieu ailleurs qu'en B2:U2, sort de la procédure
If Target.Value = "" Then Target.Offset(1, 0).Value = "": Exit Sub 'si la cellule est effacée, efface la cellule en-dessous et sort de la procédure
DL = Cells(Application.Rows.Count, "A").End(xlUp).Row 'définit la dernière ligne éditée DL de la colonne A
COL = Target.Column + 3 'définit la colonne COL (3 colonnes de plus que la colonne de la cellule modifiée)
Set PL = Range(Cells(9, COL), Cells(DL, COL)).SpecialCells(xlCellTypeVisible) 'définit la plage PL (les cellule visibles de la plage des lignes 9 à DL de colonne COL)
For Each CEL In PL 'boucle sur toutes les cellules CEL de la plage PL
    If CEL.Value = Target.Value Then T = T + 1 'si la valeur de la cellule CEL est égale à la valeur de la cellule modifiée, incrémente T de +1
Next CEL 'prochaine celule de la boucle
Target.Offset(1, 0).Value = T 'renvoie le total T dans la cellule en dessous de la cellule modifiée
End Sub

Private Sub Worksheet_Calculate() 'au recalcul dans l'onglet
Dim I As Integer 'déclare la variable I (Incrément)
For I = 2 To 21 'boucle sur les colonnes 2 à 21 (B à U)
    'si la cellule ligne 2 colonne I n'est pas vide, réecrit sa valeur (cela renvoie la procédure Change au dessus)
    If Cells(2, I).Value <> "" Then Cells(2, I).Value = Cells(2, I).Value
Next I 'prochaine ligne de la boucle
End Sub

Le fichier :

 

[gueststar]

Slt Robert,

je ne cherche pas vraiment de solution VBA, mais je suis ouvert à tout.
J'ai testé ta solution elle ne semble pas fonctionner correctement en cas de filtre.

Je te mets un aperçu que tu peux tester du coup sur le fichier que tu m'as envoyé.
J'ai saisi des valeur exemples pour les boules 1, 2, 7 et 10 et lorsque je filtre avec la dernière colonne, j'ai une valeur à 0 pour les boules 1 et 2 ce qui est normal car aucun de ces nombres n'apparait, cependant j'ai aussi la valeur 0 pour les boules 7 et 10 alors que je devrais avoir respectivement 1 et 2 comme résultat.

 

[R@chid]

Bonjour @ tous,
Salut Robert,

en J2 :

=SOMMEPROD(SOUS.TOTAL(2;DECALER(E9;LIGNE(INDIRECT("1:"&LIGNES(E10:E127)));))*(E10:X127=45))

Cordialement

 

[Robert]

Re, Bonsoir R@chid, bonsoir le forum,

Comme dit dans mon premier post :

le code renverra le nombre de fois que celle valeur apparaît dans les cellules visibles de la colonne de la boule correspondante

Visiblement, toi tu voudrais le nombre de fois que la valeur apparaît dans les cellules visible de toutes les boules. En pièce jointe la version 02 :

 

[gueststar]

Bonsoir R@chid,

J'étais si près et en même temps si loin, merci ta formule SOMME.PROD semble avoir été effectué correctement.

@Robert: c'est exact, mon titre disait bien dans une matrice ou un tableau filtré. Merci à toi également ça semble fonctionner aussi !

Vous êtes top dans l'assistance aux personnes en PLS du cerveau
Grâce à vous j'ai 2 possibilités de gérer. Merci encore !