XL 2016 Max/Min Calcul reste

Function Grand(plage As Range)
Dim v1, v2, i&, a&(1 To 2)
v1 = Application.Large(plage, 1) 'GRANDE.VALEUR
v2 = Application.Large(plage, 2)
For i = 1 To plage.Count
    If a(1) = 0 And plage(i) = v1 Then
        a(1) = i
    ElseIf a(2) = 0 And plage(i) = v2 Then
        a(2) = i
    End If
    If a(1) * a(2) Then Exit For
Next
Grand = a 'vecteur horizontal
End Function

Function Petit(plage As Range)
Dim v1, v2, tbl, x$, i&, a&(1 To 2)
v1 = Application.Small(plage, 1) 'PETITE.VALEUR
v2 = Application.Small(plage, 2)
tbl = Grand(plage) 'appel de la 1ère fonction
x = " " & tbl(1) & " " & tbl(2) & " "
For i = 1 To plage.Count
    If InStr(x, " " & i & " ") = 0 Then
        If a(1) = 0 And plage(i) = v1 Then
            a(1) = i
        ElseIf a(2) = 0 And plage(i) = v2 Then
            a(2) = i
        End If
        If a(1) * a(2) Then Exit For
    End If
Next
Petit = a 'vecteur horizontal
End Function

Function Grand(plage As Range)
Dim v1, v2, i&, a&(1 To 2)
v1 = Application.Large(plage, 1) 'GRANDE.VALEUR
v2 = Application.Large(plage, 2)
For i = 1 To plage.Count
    If a(1) = 0 And plage(i) = v1 Then
        a(1) = i
    ElseIf a(2) = 0 And plage(i) = v2 Then
        a(2) = IIf(v1 = v2, [9^9], i)
    End If
    If a(1) * a(2) Then Exit For
Next
Grand = a 'vecteur horizontal
End Function

Function Petit(plage As Range)
Dim v1, v2, tbl, x$, i&, a&(1 To 2)
v1 = Application.Small(plage, 1) 'PETITE.VALEUR
v2 = Application.Small(plage, 2)
tbl = Grand(plage) 'appel de la 1ère fonction
x = " " & tbl(1) & " " & tbl(2) & " "
For i = 1 To plage.Count
    If InStr(x, " " & i & " ") = 0 Then
        If a(1) = 0 And plage(i) = v1 Then
            a(1) = i
        ElseIf a(2) = 0 And plage(i) = v2 Then
            a(2) = IIf(v1 = v2, [9^9], i)
        End If
        If a(1) * a(2) Then Exit For
    End If
Next
Petit = a 'vecteur horizontal
End Function

=SOMME(A2:A8)-INDEX(A2:A8;Grand(A2:A8))-INDEX(A2:A8;Petit(A2:A8))-SIERREUR(INDEX(A2:A8;INDEX(Grand(A2:A8);2));0)-SIERREUR(INDEX(A2:A8;INDEX(Petit(A2:A8);2));0)

Function Grand(plage As Range, Optional deduire = 0)
Dim v1, v2, i&, a&(1 To 2), ded
v1 = Application.Large(plage, 1) 'GRANDE.VALEUR
v2 = Application.Large(plage, 2)
For i = 1 To plage.Count
    If a(1) = 0 And plage(i) = v1 Then
        a(1) = i
        ded = ded + plage(i)
    ElseIf a(2) = 0 And plage(i) = v2 Then
        a(2) = IIf(v1 = v2, [9^9], i)
        If v1 <> v2 Then ded = ded + plage(i)
    End If
    If a(1) * a(2) Then Exit For
Next
Grand = IIf(deduire, ded, a) 'scalaire ou vecteur horizontal
End Function

Function Petit(plage As Range, Optional deduire = 0)
Dim v1, v2, tbl, x$, i&, a(1 To 3), ded
v1 = Application.Small(plage, 1) 'PETITE.VALEUR
v2 = Application.Small(plage, 2)
tbl = Grand(plage) 'appel de la 1ère fonction
x = " " & tbl(1) & " " & tbl(2) & " "
For i = 1 To plage.Count
    If InStr(x, " " & i & " ") = 0 Then
        If a(1) = 0 And plage(i) = v1 Then
            a(1) = i
            ded = ded + plage(i)
        ElseIf a(2) = 0 And plage(i) = v2 Then
            a(2) = IIf(v1 = v2, [9^9], i)
            If v1 <> v2 Then ded = ded + plage(i)
        End If
        If a(1) * a(2) Then Exit For
    End If
Next
Petit = IIf(deduire, ded, a) 'scalaire ou vecteur horizontal
End Function

=SOMME(A2:A8)-Grand(A2:A8;1)-Petit(A2:A8;1)