Range dynamique pour fonctions

[Wistar]

Bonjour,

Je cherche à faire quelque chose de particulier. Je commence tout juste en VBA. J'ai fait le tour des forums et je n'ai rien trouvé qui puissent m'aider.

Je possède une base de données qui me permet de rappeler un certaine nombre d'entre elles et de les classer. Le hic, c'est que le nombre est variable selon les critères et donc que mes plages de sorties varient.

Voici un exemple de ce que je peux avoir. (en surligné ce que j'aimerais avoi, idéalement les moyennes serait alignées dans une colonne)

A B CDEFGH
5 1 abc Moyenne 5 Moyenne Écart-type Nb()
5 20 abcdefg Moyenne 45 Moyenne Écart-type Nb()
5 31 ab Moyenne 90 Moyenne Écart-type Nb()

45 2 a Moyenne
45 7 ab Moyenne
45 35 a Moyenne
45 41 abcd Moyenne
45 42 abc Moyenne

90 5 ab Moyenne
90 12 abcdefgh Moyenne


Je cherche à calculer la moyenne de chaque ligne à la dernière colone +1, la moyenne de la moyenne de chaque ligne, l'écart type de la moyenne de chaque ligne pour chaque bloc de données ainsi que le nb de la colonne B de chaque bloc.

Je pense qu'une manière d'y arriver est de définir le range de la colonne 3 jusqu'à la dernière colonne utilisée de la feuille jusqu'à la ligne vide et faire une boucle pour chaque bloc de données. Mettre ça en VBA après, c'est autre chose...

J'espère que c'est suffisamment clair. Merci beaucoup!!

[ZebreLoup]

Désolé mais pour ma part je n'ai rien compris à ce que tu voulais faire. J'espère que d'autres seront plus perspicaces que moi. Sinon, tu peux reformuler ou donner un exemple ?

[Daniel.C]

Bonjour,

Franchement, je ne comprends pas l'organisation de tes données. Est-ce la base de données ou le résultat souhaité ?

[Wistar]

Voici une explication plus claire.

Je cherche à pouvoir appliquer différentes fonctions (voir images) sur une série de données qui peuvent avoir un nombre de lignes et de colonnes variable mais qui sont toujours circonscrites en (3) blocs (voir images).

Je cherche à avoir sur ces plages de données

• La moyenne de chaque ligne alignée à la dernière colonne + 1
• La moyenne des moyennes de chaque ligne pour chaque bloc
• Le SEM (ecarttype()/racine(nb())) des moyennes de chaque ligne pour chaque bloc
• Le nb() des moyennes de chaque ligne pour chaque bloc

Sur les images en

noir: ce que j'ai en ce moment
vert: ce que j'aimerais obtenir




Une solution à laquelle j'avais pensée c'est de trouver le nombre de colonnes max et les lignes vides et de définir mon range à partir de ce max et de la ligne vide puis faire une boucle pour chaque bloc. Étant débutant en VBA je ne sais pas comment pouvoir appliquer ça en pratique.

Merci

[Daniel.C]

Pour tes formules "moyenne", est-ce ce que tu veux valable pour la colonne N (14) ?

Code :

Range([A1], Cells(Rows.Count, 1).End(xlUp)).Offset(, 13).FormulaR1C1 = "=AVERAGE(RC3:RC[-1])"

[Wistar]

Bonjour Daniel,

Merci de la réponse.

J'ai fait un petit ajout pour que les moyennes s'ajoutent toujours à la dernière colonne.

Deux questions demeurent:

• Comment ajouter les autres fonctions calculées sur les moyennes des blocs?
• Comment faire pour que la formule ne s'applique pas aux lignes vides?

Pour le moment j'ai ceci qui fonctionne bien outre les points mentionnés ci-hauts.

Code :

1
2
3
4
5
6
7
 
 
Dim Last As Long
    Last = Cells.Find("*", SearchOrder:=xlByColumns, _
    LookIn:=xlValues, SearchDirection:=xlPrevious).Column
 
Range([A2], Cells(Rows.Count, 1).End(xlUp)).Offset(, Last).FormulaR1C1 = "=AVERAGE(RC3:RC[-1])"

Merci

[Daniel.C]

Comment ajouter les autres fonctions calculées sur les moyennes des blocs?

Tu peux me donner un exemple sur les feuilles que tu as publiées ?

Comment faire pour que la formule ne s'applique pas aux lignes vides?

Essaie :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
Sub test()
    Dim LastCol As Long
    LastCol = Cells.Find("*", SearchOrder:=xlByColumns, _
    LookIn:=xlValues, SearchDirection:=xlPrevious).Column
    lastlig = Cells.Find("*", SearchOrder:=xlByRows, _
    LookIn:=xlValues, SearchDirection:=xlPrevious).Row
    For Each c In Range([A2], Cells(lastlig, 1)).Offset(, LastCol)
        If Application.CountA(Range(Cells(c.Row, 1), Cells(c.Row, LastCol))) > 0 Then
            c.Formula = "=AVERAGE(" & Range(Cells(c.Row, 1), Cells(c.Row, LastCol)).Address(0, 0) & ")"
        End If
    Next c
End Sub

[Wistar]

Le code évitant les lignes vides fonctionne très bien. Merci!

Pour les statistiques (moyenne, SEM et nb()) sur les blocs de donnée, sur l'image

• supérieure ça fait référence aux formules de Q2 à S4
• inférieure ça fait référence aux formules de J2 à L4.
• En P2:4 et I2:4 respectivement on retrouve le numéro du bloc dans le colonne A duquel sont issus les fonctions.

Merci encore

[Daniel.C]

Bonjour,

Pour la moyenne et la colonne P. Essaie d'adapter pour le reste (c'est quoi "SEM" ?) :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub test1()
    'Réf. 111119.xlsm
    Dim Ligne As Long, Res As String, Ligne1 As Long
    Ligne = 1
    Ligne1 = 1
    Dim c As Range
    For Each c In Range([N2], Cells(Rows.Count, 14).End(xlUp))
        If c.Offset(1).Value = "" And c.Value <> "" Then
            Ligne = Ligne + 1
            Cells(Ligne, "S").Formula = "=AVERAGE(" & Res & ":" & c.Address & ")"
        ElseIf c.Value <> "" And c.Offset(-1) = 0 Then
            Res = c.Address
            Ligne1 = Ligne1 + 1
            Cells(Ligne1, "P").Formula = "=" & c.Address(0, 0)
        End If
    Next c
End Sub

[Wistar]

Le debugger s'active à la ligne

Code :

ElseIf c.Value <> "" And c.Offset(-1) = 0 Then

SEM c'est Standard error of mean ou erreur standard de la moyenne. En fonction excel on parle de

Code :

=ecartype(X)/racine(nb(X)))

.

Ci-joint le classeur sur lequel le code a été testé.

Merci

[Daniel.C]

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Sub Moyenne()
    'Réf. 111119.xlsm
    'données en colonne G
    'moyenne en colonne H
    'NB en colonne J
    'Ecart type en colonne K
    Dim Ligne As Long, Res As String, Ligne1 As Long
    Ligne = 1
    Ligne1 = 1
    Dim c As Range
    For Each c In Range([G2], Cells(Rows.Count, 7).End(xlUp))
        If c.Offset(1).Value = "" And c.Value <> "" Then
            Ligne = Ligne + 1
            Cells(Ligne, "H").Formula = "=AVERAGE(" & Res & ":" & c.Address & ")"
            Cells(Ligne, "J").Formula = "=COUNT(" & Res & ":" & c.Address & ")"
            Cells(Ligne, "K").Formula = "=STDEV(" & Res & ":" & c.Address & ")" & _
                "/SQRT(" & Cells(Ligne, "J").Address(0, 0) & ")"
        ElseIf c.Value <> "" And c.Offset(-1) = "" Then
            Res = c.Address
            Ligne1 = Ligne1 + 1
            Cells(Ligne1, "I").Formula = "=" & c.Address(0, 0)
        End If
    Next c
End Sub

[Wistar]

Wow Daniel, je dois avouer que je trouve ça presque magique!

J'ai encore une fois changé légèrement le code pour que ce soit toujours relatif à la dernière colonne puisque celle-ci change en fonction du nombre de données.

Code :

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
Sub Macro1()
    'Trouver la dernière colonne Last
    Dim Last As Long
    Last = Cells.Find("*", SearchOrder:=xlByColumns, _
    LookIn:=xlValues, SearchDirection:=xlPrevious).Column
    'Moyenne, SEM et N
    'moyenne des lignes en colonne Last
    Dim Ligne As Long, Res As String, Ligne1 As Long
    Ligne = 1
    Ligne1 = 1
    Dim c As Range
    For Each c In Range([G2], Cells(Rows.Count, Last).End(xlUp))
        If c.Offset(1).Value = "" And c.Value <> "" Then
            Ligne = Ligne + 1
            'moyenne en colonne Last+2
            Cells(Ligne, Last + 2).Formula = "=AVERAGE(" & Res & ":" & c.Address & ")"
             'Ecart type en colonne Last + 3
            Cells(Ligne, Last + 3).Formula = "=STDEV(" & Res & ":" & c.Address & ")" & _
                "/SQRT(" & Cells(Ligne, Last + 4).Address(0, 0) & ")"
             'NB en colonne Last + 4
            Cells(Ligne, Last + 4).Formula = "=COUNT(" & Res & ":" & c.Address & ")"
        ElseIf c.Value <> "" And c.Offset(-1) = "" Then
            Res = c.Address
            Ligne1 = Ligne1 + 1
           'Numero du groupe en colonne Last + 1
            Cells(Ligne1, Last + 1).Formula = "=" & c.Address(0, 0)
        End If
    Next c
End Sub

Il y a deux lignes que je n'ai pas su ajuster.

• Je ne réussi pas à mettre [G2] par rapport à Last. Je cherche à viser la cellule dans la colonne Last à la ligne 2. Dans l'exemple c'était G mais ce peut être variable (parce que le nombre maximale de données par ligne varit). J'ai tenté Last[2] mais ma synthaxe a fait sauté le debogger .

Code :

For Each c In Range([G2], Cells(Rows.Count, Last).End(xlUp))

• Cela fait référence à la 1ere moyenne de chacune des lignes. Dans l'exemple du classeur précédent il s'agit de la colonne G alors que c'est la colonne A qui devrait toujours (ne change pas) être visée. Par contre, les lignes sont bien selectionnées.

Code :

Cells(Ligne1, Last + 1).Formula = "=" & c.Address(0, 0)

Je dois encore te remercier de ton aide inestimable Daniel. Cela fait des semaines que je cherche à faire ce macro et je n'y serais jamais arrivé sans ton aide.

[Daniel.C]

Bonjour,

Essaie :

Code :

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
Sub Moyenne()
    'Réf. 111119.xlsm
    'données en colonne G
    'moyenne en colonne H
    'NB en colonne J
    'Ecart type en colonne K
    Dim Ligne As Long, Res As String, Ligne1 As Long, DerCol As Long
    DerCol = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
    Ligne = 1
    Ligne1 = 1
    Dim c As Range
    For Each c In Range(Cells(2, DerCol), Cells(Rows.Count, DerCol).End(xlUp))
        If c.Offset(1).Value = "" And c.Value <> "" Then
            Ligne = Ligne + 1
            Cells(Ligne, "H").Formula = "=AVERAGE(" & Res & ":" & c.Address & ")"
            Cells(Ligne, "J").Formula = "=COUNT(" & Res & ":" & c.Address & ")"
            Cells(Ligne, "K").Formula = "=STDEV(" & Res & ":" & c.Address & ")" & _
                "/SQRT(" & Cells(Ligne, "J").Address(0, 0) & ")"
        ElseIf c.Value <> "" And c.Offset(-1) = "" Then
            Res = c.Address
            Ligne1 = Ligne1 + 1
            Cells(Ligne1, "I").Formula = "=" & c.Address(0, 0)
        End If
    Next c
End Sub