Discussion :

[PyRo21]

Bonjour à tous,

J'essaye de créer une fonction qui me renvoie dans la cellule où se trouve cette fonction, le nombre de cellules d'une colonne filtrée répondant à un critère existant dans une autre cellule. Soit une fonction SousTotal_DT tel que SousTotal_DT($B2;$B:$B) en C2 par exemple me renvoie le nombre de cellule contenu dans la colonne 2 et répondant à la valeur en B2 soit sommer. Ceci quelque soit le critère du filtre automatique existant.

Le code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Option Explicit
 
Function SousTotal_DT(Ref As Range, Plage As Range) As Long
 
    Dim Cell, PlageFiltree As Range
    Dim N, ColPlage, ColRef As Long
 
    N = 0
    ColPlage = Plage.Column
 
    With ThisWorkbook.ActiveSheet
        Set PlageFiltree = .AutoFilter.Range.Columns(ColPlage).SpecialCells(xlCellTypeVisible).Cells
    End With
 
    For Each Cell In PlageFiltree
        If Cell.Value = Ref.Value Then N = N + 1
    Next Cell
 
    SousTotal_DT = N
 
End Function

Il doit y avoir une sacrée erreur évidente pour vous car SpecialCells me renvoie systématiquement les cellules de la plage entière du filtre et non uniquement celles filtrée et donc visible.
J'ai essayé plusieurs méthodes semblables, Pour certaines cellules le résultat évolue bien en fonction du filtre automatique(filtre sur une ou plusieurs colonnes) et pour d'autres, le résultat n'évolue pas. Il compte systématiquement l'ensemble de la plage filtrée, le filtre automatique doit-il être uniquement sur une colonne?

Merci de bien vouloir m'éclairer!

[Theze]

Bonjour,

De cette façon peut être ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
Function SousTotal_DT(Ref As Range, Plage As Range) As Long
 
    Dim Cel As Range
    Dim N As Long
 
    Application.Volatile
 
    For Each Cel In Plage
 
        If Cel.EntireRow.Hidden = False Then If Cel.Value = Ref.Value Then N = N + 1
 
    Next Cel
 
    SousTotal_DT = N
 
End Function

Par contre, il est préférable de définir la plage plutôt que de passer une colonne entière, le code sera plus rapide.

Hervé.

[PyRo21]

Merci pour cette réponse,

Application.Volatile est pertinent.

Par contre, j'ai l'impression que çà marche moins bien. L'idéal est bien la fonction SousTotal_DT(CelluleRecherchée,ColonneDeRecherche) avec ensuite une extraction de la plage filtrée (si les résultat porte sur les lignes 4,5,10,11 obtenir l'intersection de la ColonneDeRecherche avec ces lignes et effectuée la recherche dans cette plage filtrée pour limité les temps de calculs.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Option Explicit
 
Function SousTotal_DT1(Ref As Range, Plage As Range) As Long
 
    Dim Cel, PlageFiltree As Range
    Dim N, ColPlage As Long
 
    Application.Volatile
 
    N = 0
    ColPlage = 0
    ColPlage = Plage.Column
 
    ThisWorkbook.ActiveSheet.Rows(1).Hidden = True
    Set PlageFiltree = Plage.Range(Plage.SpecialCells(xlCellTypeVisible).End(xlUp), Plage.SpecialCells(xlCellTypeVisible).End(xlDown))
    ThisWorkbook.ActiveSheet.Rows(1).Hidden = False
 
 
    For Each Cel In PlageFiltree
        If Cel.EntireRow.Hidden = False Then
            If Cel.Value = Ref.Value Then N = N + 1
            End If
 
    Next Cel
 
    SousTotal_DT1 = N
 
End Function

Merci.

[Theze]

Attention, une fonction sert à retourner une valeur ou un tableau de valeurs mais pas à exécuter des actions comme par exemple ces lignes de code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
ThisWorkbook.ActiveSheet.Rows(1).Hidden = True
...
ThisWorkbook.ActiveSheet.Rows(1).Hidden = False

tu as dû remarquer que ça ne fonctionnait pas !
La lifgne de code ci-dessous n'est pas bonne tu décale la plage :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
Set PlageFiltree = Plage.Range(Plage.SpecialCells(xlCellTypeVisible).End(xlUp), Plage.SpecialCells(xlCellTypeVisible).End(xlDown))

Un exemple, admettons que ta plage soit "B10:B30" (21 cellules), si tu fais :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
MsgBox Plage.Range("A1").Address(0, 0)
MsgBox Plage.Range("A21").Address(0, 0)

tu auras B10 et B30 et non A1 et A10 car "A1" est considéré dans ce cas comme étant la cellule la plus en haut et à gauche de la plage (comme dans une feuille) et correspond alors à B10 dans ta plage de même pour "A21", le compilateur effectue un décalage par rapport à A1.
Donc plutôt ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
Set PlageFiltree = Plage.SpecialCells(xlCellTypeVisible)

Quand tu déclare des variables de cette façon (séparées par une virgule) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
Dim Cel, PlageFiltree As Range

Sache que seule "PlageFiltree" est typée Range, Cel est typée Variant, il te faut préciser le type de cette façon :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
Dim Cel As Range, PlageFiltree As Range
Dim N As Long, ColPlage As Long

Donc ta fonction :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
Function SousTotal_DT1(Ref As Range, Plage As Range) As Long
 
    Dim Cel As Range, PlageFiltree As Range
    Dim N As Long, ColPlage As Long
 
    Application.Volatile
 
    Set PlageFiltree = Plage.SpecialCells(xlCellTypeVisible)
 
    For Each Cel In PlageFiltree
 
        If Cel.EntireRow.Hidden = False Then
 
            If Cel.Value = Ref.Value Then N = N + 1
 
        End If
 
    Next Cel
 
    SousTotal_DT1 = N
 
End Function

Hervé.

[Qwazerty]

Salut

Ben mince, encore une "bizarrerie" d'Excel

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
'On pointe les cellules visibles
'Mince ce code fonctionne en temps normal... mais pas dans le cas d'une fonction perso...
'Set PlageVisible = Plage.SpecialCells(xlCellTypeVisible)

Je vote pour la méthode de Theze

++
Qwaz

[PyRo21]

Pour la déclaration des variables et le décalage de colonne, vraiment merci beaucoup. Je manque clairement de notions.

Par contre, je ne comprends pas pourquoi PlageFiltree = Plage.SpecialCells(xlCellTypeVisible) conserve toujours l'ensemble des éléments du filtre qu'ils soient visibles ou non.
La nécessité du test Cel.EntireRow.Hidden à l'air de fonctionner pour pallier ce problème néanmoins le code est devenu encore plus lent que mes précédentes boucles FOR (30sec par cellules :s).

Est-ce que je pourrais remplacer la boucle FOR par une fonction CountIf sur les cellules filtrées et visibles(cellules dont j'aimerais connaître le moyen pour les récupérer). Exemple :
Plage = Colonne B (avec titre) => PlageFiltree (selon critères choisi) = [B100;B110;B200-202;etc.]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Application.WorksheetFunction.CountIf(PlageFiltree;Ref)

Encore Merci pour ces réponses très claires.

[Qwazerty]

Salut

Par contre, je ne comprends pas pourquoi PlageFiltree = Plage.SpecialCells(xlCellTypeVisible) conserve toujours l'ensemble des éléments du filtre

Malheureusement je crains qu'il n'y ai pas d'explication, l'utilisation de Function comme fonctions personnalisés ne permet pas toujours une flexibilité aussi importante qu'à l'accoutumé. Il faut faire avec et trouver des moyen détourné quand c'est possible.
J'ai un vague souvenir d'une bidouille mais impossible de remettre la main sur la formule à utiliser...

Countif de toute façon ne semble pas apprécier les plages multiples

Peux-tu placer ton code complet stp, je ne comprends pas une telle lenteur avec un code qui devrait être très court (en nombre de ligne et en temps d’exécution)


[Edit]
Après quelques recherches, il semblerait que la fonction sous.total ne tienne compte QUE des cellules visibles sur la plage.
Il y a peut-être d'autre conction qui ont la même spécificité ou en bricolant un peut il est peut-être possible de faire ce que tu veux avec sous.total et peut-être une formule matriciel et une colonne en plus.
[/Edit]


[Edit2]

Sinon sans code
Pour que ce soit plus facile j'utilise souvent les tableau dans excel c'est très pratique (Insertion, Tableau)
tu ajoutes une colonne (la 6 ici) qui sera masquée, dans les cellules tu mets la formule qui va bien (visible ci-dessous).
et dans ta case de sous total de ta colonne 3 (celle qui contient les valeur à sommer tu place la formule (présente ci-dessous), 2 choses :

1. c'est normal qu'elle utilise les valeur de la colonne 6 et jamais celle de la colonne 3
2. c'est une formule matriciel, il faut après l'avoir écrite, utiliser la combinaison de touche Ctrl+Shift+Enter pour la valider. Des accolades apparaissent autour de la formule (tu ne dois pas les inscrire toi-même dans la formule).

	Colonne1	Colonne3	Colonne6
	zer	2	=SOUS.TOTAL(109;Tableau1[[#Cette ligne];[Colonne3]])
	zer	5	5
	zer	1	1
	ezr	3	3
	Terminé	6	6
	sdf	2	2
	sdf	8	8
	Terminé	1	1
		={SOMME(([Colonne1]="Terminé")*[Colonne6])}	0

[/Edit2]


++
Qwaz

[PyRo21]

Bonjour,

Voilà un code qui à l'air de marcher, un poil lent mais çà passe :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Option Explicit
 
Function SousTotal_DT(Ref As Range, Plage As Range) As Long
 
    Dim Cel As Range, PlageFiltree As Range
    Dim N As Long
 
    Application.Volatile
 
    N = 0
    Set PlageFiltree = Plage.Range(Cells(1, 1), Cells(1, 1).End(xlDown)).SpecialCells(xlCellTypeVisible)
 
    For Each Cel In PlageFiltree
        If Cel.EntireRow.Hidden = False Then
            If Cel.Value = Ref.Value Then N = N + 1
        End If
    Next Cel
 
    SousTotal_DT = N
 
 
End Function

[Qwazerty]

Salut

J'ai éditer mon message précédent entre temps, jette un oeil.

Attention SpecialCell ne fonctionnera pas dans le cadre d'une macro perso, met un point d'arrêt dans la function et valide la formule (rentrer dans la cellule comme pour la modifier et juste taper sur Enter), ça va relancer le calcul et stopper sur le point d'arrêt. Tu pourras ensuite regarder ce que contient plagefiltree.address.



++
Qwaz