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 |
Partager