XL 2016 CALCUL DE MOYENNE

Private Sub Worksheet_Activate()
Dim d As Object, tablo, colDate%, colResu(), ncol%, resu(), nombre&(), i&, dat, n&, nn&, j%, x$
Set d = CreateObject("Scripting.Dictionary")
tablo = [Tableau1] 'tableau structuré
colDate = 6 'colonne F
colResu = Array(5, 9, 10, 11, 12) 'colonnes E I J K L
ncol = UBound(colResu) + 2
ReDim resu(1 To UBound(tablo), 1 To ncol)
ReDim nombre(1 To UBound(tablo), 1 To ncol)
For i = 1 To UBound(tablo)
    dat = tablo(i, colDate)
    If Not d.exists(dat) Then
        n = n + 1
        d(dat) = n 'mémorise la ligne
    End If
    nn = d(dat)
    resu(nn, 1) = dat
    For j = 2 To ncol
        x = CStr(tablo(i, colResu(j - 2)))
        If IsNumeric(x) Then resu(nn, j) = resu(nn, j) + CDbl(x): nombre(nn, j) = nombre(nn, j) + 1
Next j, i
For i = 1 To n
    For j = 2 To ncol
        If nombre(i, j) Then resu(i, j) = resu(i, j) / nombre(i, j)
Next j, i
'---restitution---
If FilterMode Then ShowAllData 'si la feuille est filtrée
With [A2] '1ère cellule de restitution
    With .Resize(n, ncol)
        .Value = resu
        .Interior.ColorIndex = 36 'jaune clair
        .Borders.Weight = xlHairline 'bordures
    End With
    .Offset(n).Resize(Rows.Count - n - .Row + 1, ncol).Delete xlUp 'RAZ en dessous
End With
With UsedRange: End With 'actualise la barre de défilement verticale
End Sub