Discussion :

[jadey]

Bonjour Les gens,

Voila je dispose d'une cellule qui évalue la valeur d'une formule, disons:

Code :

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

=SOMME.SI(E12:E126;">=0")/E4

Je souhaite que cette formule se base sur une plage construite de manière dynamique :

Code :

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

=SOMME.SI($E$plageDebut:$E$plageFin;">=0")/E4

ou plageDebut et plageFin sont variables définies au niveau du classeur.
Quelle est la bonne syntaxe ?
Cela doit être relativement simple à faire mais je suis une quiche sur excel et j'ai cherché mais malheureusement pas encore tombé sur le/les post(s) qui en parlent.

D'avance merci pour vos réponses.

[Pierre Fauconnier]

Salut.

Les tableaux structurés sont faits pour cela => voir mon tuto sur le sujet. Si tu ajoutes des données à la suite des données du tableau, celui-ci grandit et ta formule s'adapte automatiquement.





NB: Depuis 2007, SOMME.SI est obsolète. Perso, je lui préfère SOMME.SI.ENS qui permet 127 critères

[jadey]

Salut Fauconnier,

Merci pour ta réponse et ta solution élégante!

[jadey]

Alors par contre je remarque que les valeurs de mes petits calculs ne se mettent pas à jour en fonction des filtres du tableau. Est ce qu'il y'a une parade ?

[Pierre Fauconnier]

Il faut utiliser la fonction SOUS.TOTAL, mais il n'est plus alors question de critères mais de reprendre une des fonctions statistiques "de base" qui tiendra compte des lignes filtrées. Les filtres remplaceront donc les critères du SOMME.SI.ENS.

[jadey]

Bon j'ai fini par faire ça :

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
 
Function nbCellulesVisiblesSi(ByVal plage As Range) As Double
 
' Remplace =NB.SI(IQ_P01_TableauDuree;">=0") sur E4 par ex
' ou =NB.SI(IQ_P01_TableauRatioB;$F$4) sur F8 par ex
 
Dim plg As Range
Dim total As Double
total = 0
For Each plg In plage
    'If plg.Rows.Hidden = False And plg.Columns.Hidden = False And plg.Cells.Value = correspondance Then
    If plg.Rows.Hidden = False And plg.Columns.Hidden = False Then
 
        For Each Cellule In plg
            If Cellule.Value >= 0 Then
                total = total + 1
            End If
        Next
 
    End If
Next
nbCellulesVisiblesSi = total
 
End Function
 
Function sommeCellulesVisiblesSi(ByVal plage As Range) As Double
 
'Remplace =SOMME.SI(IQ_P01_TableauDuree;">=0") sur E5 par ex
 
Dim plg As Range
Dim total As Double
total = 0
For Each plg In plage
    'If plg.Rows.Hidden = False And plg.Columns.Hidden = False And plg.Cells.Value = correspondance Then
    If plg.Rows.Hidden = False And plg.Columns.Hidden = False Then
 
        For Each Cellule In plg
            If Cellule.Value >= 0 Then
                total = total + Cellule.Value
            End If
        Next
 
 
    End If
Next
sommeCellulesVisiblesSi = total
 
End Function

C'est crade.

ça par contre (moins crade) mais ne marche pas. ça comptabilise toutes les lignes, même celle qui sont filtrées (et sensées êtres non visibles)

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
29
30
31
32
33
34
35
36
 
Function nbCellulesVisiblesSi2(ByRef plage As Range, ByVal correspondance As Double) As Double
 
' Remplace =NB.SI(IQ_P01_TableauDuree;">=0") sur E4 par ex
' ou =NB.SI(IQ_P01_TableauRatioB;$F$4) sur F8
 
Dim plg As Range
Dim total As Double
total = 0
Set rn = plage.SpecialCells(xlCellTypeVisible)
 
For Each Cellule In rn
    If Cellule.Value >= 0 Then
        total = total + 1
    End If
Next
nbCellulesVisiblesSi2 = total
 
End Function
Function sommeCellulesVisiblesSi2(ByRef plage As Range, ByVal correspondance As Double) As Double
 
'Remplace =SOMME.SI(IQ_P01_TableauDuree;">=0") sur E5 par ex
 
Dim plg As Range
Dim total As Double
total = 0
Set rn = plage.SpecialCells(xlCellTypeVisible)
 
For Each Cellule In rn
    If Cellule.Value >= 0 Then
        total = total + Cellule.Value
    End If
Next
sommeCellulesVisiblesSi2 = total
 
End Function