=SOMME.SI(B$18:B$3000;B4;A$18:A$3000)/NB.SI(B$18:B$3000;B4)
=MOYENNE(SI(B$18:B$3000=B4;A$18:A$3000))
Function MOY(txt As String) As Double
'calcule les moyennes
Dim w As Worksheet, ref As Range, n As Byte
Application.Volatile
For Each w In Worksheets
If w.Name <> "Récapitulatif" Then 'à adapter
Set ref = w.Cells.Find(txt, , xlValues, xlWhole)
If Not ref Is Nothing Then
If Val(ref.Offset(, -1).Text) Then
n = n + 1
MOY = MOY + ref.Offset(, -1)
End If
End If
End If
Next
MOY = MOY / n
End Function
=MOY(B4)
Function MOY(txt As String) As Variant
'calcule les moyennes
Dim w As Worksheet, ref As Range, test As Boolean, n As Byte
Application.Volatile
On Error Resume Next
For Each w In Worksheets
If w.Name <> "Récapitulatif" Then 'à adapter
Set ref = w.Cells.Find(txt, , xlValues, xlWhole)
If Not ref Is Nothing Then
If ref.Offset(, -1) <> "" Then
'test pour repérer les erreurs
test = ref.Offset(, -1) < 1 Or ref.Offset(, -1) > 9
If test Or Err Then MOY = "Voir " & w.Name: Exit Function
n = n + 1
MOY = MOY + ref.Offset(, -1)
End If
End If
End If
Next
MOY = MOY / n
End Function