Function MOY_PERSO(Col_Coe_R As Range, Cel_Moy_R As Range)
'
' Déclarations variables
'
Application.Volatile
Application.ScreenUpdating = False
With Sheets(Cel_Moy_R.Parent.Name)
Cel_Moy_A = Cel_Moy_R.Address(0, 0)
Col_Moy_A = Left$(Cel_Moy_R.Address(0, 0), (Cel_Moy_R.Column < 27) + 2)
Lig_Moy_N = Cel_Moy_R.Row
Col_Coe_A = Left$(Col_Coe_R.Address(0, 0), (Col_Coe_R.Column < 27) + 2)
Col_Coe_N = Col_Coe_R.Column
' référence à la feuille !
Lig_Vid_N = .Range('A65536').End(xlUp).Row + 1
Ligv_N = 1
Colv_N = 0
Do
If Cel_Moy_R.Offset(Ligv_N, Colv_N).Interior.PatternColor = 16711935 Then
Exit Do
Else
Ligv_N = Ligv_N + 1
End If
Loop While Ligv_N < Lig_Vid_N
Lig_DebSct_V = Lig_Moy_N + 1
Lig_FinSct_V = Ligv_N - 1 + Lig_DebSct_V - 1
' Construction des références (adresses)
Pla_Not_A = Col_Moy_A & Lig_DebSct_V & ':' & Col_Moy_A & Lig_FinSct_V
Pla_Coe_A = Col_Coe_A & Lig_DebSct_V & ':' & Col_Coe_A & Lig_FinSct_V
' référence à la feuille !
Num_N = WorksheetFunction.SumProduct(.Range(Pla_Not_A), .Range(Pla_Coe_A))
Ssi_AE_N = WorksheetFunction.SumIf(.Range(Pla_Not_A), 'AE', .Range(Pla_Coe_A))
Ssi_AR_N = WorksheetFunction.SumIf(.Range(Pla_Not_A), 'AR', .Range(Pla_Coe_A))
Ssi_NU_N = WorksheetFunction.SumIf(.Range(Pla_Not_A), '', .Range(Pla_Coe_A))
' référence à la feuille !
Som_Coe_N = .Cells(Lig_Moy_N, Col_Coe_N).Value
Den_N = Som_Coe_N - Ssi_AE_N - Ssi_AR_N - Ssi_NU_N
If Den_N = 0 Then MOY_PERSO = '--,--' Else MOY_PERSO = Num_N / Den_N
End With
Application.ScreenUpdating = True
End Function