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

 

[Denis132]

Bonsoir Galaxy_2019,

Je suis nul en vba mais sinon, une approche:

=SOMME(B1:B25)-PETITE.VALEUR(B1:B25;1)-PETITE.VALEUR(B1:B25;2)-GRANDE.VALEUR(B1:B25;1)-GRANDE.VALEUR(B1:B25;2)

@+

Denis

 

[excfl]

Bonjour le forum,

MAX 2 :

=MAX(SI(B1:B20=MAX(B1:B20);"";B1:B20))

Formule matricielle à valider avec CTRL MAJ ENTREE

 

[CISCO]

Bonjour

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 ?

@ plus

 

[mapomme]

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 ?

L'énoncé n'est pas clair...

S'il faut ne prendre que les valeurs strictement supérieures à la deuxième plus petite valeur distincte et strictement inférieures à la deuxième plus grande valeur distincte, on peut essayer:

=SOMMEPROD( (B1:B25) * (B1:B25>PETITE.VALEUR(B1:B25;NB.SI(B1:B25;MIN(B1:B25))+1)) * (B1:B25<GRANDE.VALEUR(B1:B25;NB.SI(B1:B25;MAX(B1:B25))+1)))

edit : @R@chid, ça faisait longtemps qu'on ne s'était pas croisé sur le même fil

 

[job75]

Bonjour à tous,

On écrète les 2 valeurs les plus petites et les 2 valeurs les plus grandes :

=SI(NB(B1:B100)<5;"";SOMMEPROD(PETITE.VALEUR(B1:B100;LIGNE(INDIRECT("3:"&NB(B1:B100)-2)))))

A+

 

[R@chid]

Bonjour @ tous,

=SOMME(B1:B100)-SOMME(GRANDE.VALEUR(B1:B100;{1;2})+PETITE.VALEUR(B1:B100;{1;2}))

Cordialement

 

[job75]

Bonjour R@chid,

D'accord mais il faut que tu ajoutes le test SI(NB(B1:B100)<5;"";...

A+

 

[R@chid]

Re,
Oui cher Job75 il faut l'ajouter impérativement, sinon ta formule reste plus courte.
@mapomme
oui cher mai ça fait longtemps que l'on s'est pas croisé, ravi de te revoir et de te croiser.

La question pour laquelle j'attendais une réponse depuis hier c'est celle qu'a posée notre ami CISCO, en cas des exæquos sur les deux plus grandes ou plus petites valeurs, on fait quoi ?

Dans cette attente

 

[job75]

en cas des exæquos sur les deux plus grandes ou plus petites valeurs, on fait quoi ?

A mon avis on ne fait rien de plus, c'est juste un écrétage...

 

[Galaxy_2019]

Bonjour Le Forum,

Merci pour votre répenses, la formule du R@chid ca me convient très bien, merci infiniment et serait-il possible avec une MFC de définir les deux valeurs Max et les deux valeurs Min avec deux couleurs déférents

Merci.

 

[R@chid]

Bonjour @ tous,
oui c'est possible avec une MFC

Voir PJ


Cordialement

 

[Galaxy_2019]

Bonjour le forum,

Merci R@chid pour votre repense.

Mon tableau contient 07 lignes, j'aimerais bien en MFC ou bien en VBA, si on a seulement 05 lignes remplis la valeur Max et la valeur Min soit en deux couleurs déférents, et si on a les 07 lignes remplis, les deux valeurs Max et les deux valeurs Min soit en deux couleurs déférents.

Merci

 

[job75]

Bonjour à tous,

Avec la MFC de R@chid les doublons sont colorés.

Si l'on veut se limiter aux 2 plus petits et aux 2 plus grands voici 2 fonctions VBA :

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, i&, a&(1 To 2)
v1 = Application.Small(plage, 1) 'PETITE.VALEUR
v2 = Application.Small(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
Petit = a 'vecteur horizontal
End Function

Fichier joint, la MFC de la colonne C corrige la MFC de la colonne A.

A+

 

[Galaxy_2019]

Bonjour à tous,

Merci Job75 pour votre retour, et pour le cas ci-dessous?

Bonjour le forum,

Merci R@chid pour votre repense.

Mon tableau contient 07 lignes, j'aimerais bien en MFC ou bien en VBA, si on a seulement 05 lignes remplis la valeur Max et la valeur Min soit en deux couleurs déférents, et si on a les 07 lignes remplis, les deux valeurs Max et les deux valeurs Min soit en deux couleurs déférents.

Merci

Cdt.