XL 2016 Max/Min Calcul reste

[Galaxy_2019]

Bonjour le Forum,

J'aimerais bien de trouver une solution sous VBA pour trouver automatique les deux valeurs Max et aussi les deux valeurs Min pour l'éliminer et ensuite calculer le total des colonnes qui reste dans un tableau prédéfini.

On supprime pas les deux valeurs Max et les deux valeurs Min mais on les calcul pas avec le reste des colonnes.

Par exemple :
Un tableau qui contient une colonne B avec 15 ou 20 lignes.
Valeur Max 1 = 200 sur B2
Valeur Max 2 = 150 sur B5
Valeur Min 1 = 10 sur B7
Valeur Min 2 = 15 sur B9

On calcul le total des valeurs sans calculer les deux valeurs Max et les deux valeurs Min (Sauf les lignes B2,B5,B7,B9)

Merci

 

[R@chid]

Bonjour @ tous,
une approche par MFC.


Cordialement

 

[job75]

Avec ce fichier (2) on évite le chevauchement des plages rouges et jaunes :

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

 

[Galaxy_2019]

Bonjoir à tous,

Merci infiniment pour votre réponses... 🙂 et un grand remerciement à R@chid et Job75 pour leurs propositons quant à mon souci 😉😉

Vue que je me suis très nul en excel 🙁, je suis vraiment besoin de votre aide.

Dans le cas de doublon, d'une valeur Max ou bien d'une valeur Min

Ex:
Valeur Max 1 = 200 sur B2
Valeur Max 2 = 200 sur B5
Valeur Max 3 = 150 sur B4
Valeur Min 1 = 10 sur B7
Valeur Min 2 = 10 sur B6
Valeur Min 3 = 15 sur B9

Comment fait pour excluant une suele valeur Max et une seule valeur Min (qui sont en double) et calculer le reste des valeurs, et aussi sur MFC pour définir une seule valeur Max et une seule valeure Min (qui sont en double).

Et qu'est-ce qu'on fait s'il y a deux fois la même valeur à exclure, par ex si tu as 15 dans B9 mais aussi dans une autre cellule ?

Merci

 

[R@chid]

Bonsoir @ tous,
oui c'est possible et toujours par formule et MFC.
Voir PJ


Cordialement

 

[job75]

Bonjour Galaxy_2019, R@chid, le forum,

En cas de doublons, si l'on veut exclure une seule valeur minimum ou une seule valeur maximum :

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

Fichier (3), la formule en D6 est assez trapue :

=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)

A+

 

[job75]

Pour alléger la formule en D6 introduction de l'argument deduire dans les fonctions :

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

Fichier (4) avec en D6 :

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

A+