Range dynamique pour fonctions

Version imprimable

15/11/2011, 23h13
Wistar

Range dynamique pour fonctions

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!!
16/11/2011, 11h51
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 ?
16/11/2011, 11h58
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é ?
19/11/2011, 20h35
Wistar

Une image vaut mille mots
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

http://i42.tinypic.com/2gum6at.png
http://i39.tinypic.com/2ymeuts.png

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
19/11/2011, 21h13
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])"

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

Citation:

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 ?

Citation:

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

21/11/2011, 00h44
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

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

21/11/2011, 18h46
Wistar

1 pièce(s) jointe(s)

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

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

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 :oops:.

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.

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