Les plus petite et les plus grandes valeurs

Le 11/01/2019 à 23:23

Bonjour

Je ne sais pas vraiment comment m'y prendre pour faire une mise en forme conditionnelle suivant le nombre de valeur dans une colonne, voici un exemple : dans la colonne A1 à A20 il y a 17 valeurs dans les 17 premières cellules de cette même colonne, le but est de remplir par une couleur les cellules les 7 plus grandes valeurs(bleu) et les 7 plus petites valeurs(bleu clair), ensuite s’il y a 10 valeurs dans cette même colonne alors la mise en forme serait les 4 plus grandes valeurs et les 4 plus petites valeurs.

Les références de cette mise en forme conditionnelle :

De 8 à 9 valeurs la mise en forme serait les 3 plus grandes valeurs(bleu) et 3 plus petites valeurs (bleu clair)

De 10 à 11 valeurs la mise en forme serait les 4 plus grandes valeurs(bleu) et 4 plus petites valeurs (bleu clair)

De 12 à 13 valeurs la mise en forme serait les 5 plus grandes valeurs(bleu) et 5 plus petites valeurs (bleu clair)

De 14 à 15 valeurs la mise en forme serait les 6 plus grandes valeurs(bleu) et 6 plus petites valeurs (bleu clair)

De 14 à 15 valeurs la mise en forme serait les 6 plus grandes valeurs(bleu) et 6 plus petites valeurs (bleu clair)

De 16 à 17 valeurs la mise en forme serait les 7 plus grandes valeurs(bleu) et 7 plus petites valeurs (bleu clair)

De 18, 19 à 20 valeurs la mise en forme serait les 8 plus grandes valeurs(bleu) et 8 plus petites valeurs (bleu clair)

Ci-joint un fichier Excel pour exemple

Merci le forum

Bonne année

11valeurs.xlsx (10.20 Ko)
Le 11/01/2019 à 23:25

re, Jai oublié un s dans le titre

Le 12/01/2019 à 00:43

Salut starwaso,

avec une MFC, je ne vois pas mais je ne suis pas non plus une référence dans ce domaine.

En VBA, donc..., le fichier passant en XLSM.

Private Sub Worksheet_Change(ByVal Target As Range)
'
Dim iRow%, iTCol%, iIdx%, sCol$
'
Application.EnableEvents = False
'
iTCol = Target.Column
sCol = Split(Columns(iTCol).Address(ColumnAbsolute:=False), ":")(1)
iRow = Range(sCol & Rows.Count).End(xlUp).Row
If iRow > 8 And iRow < 21 Then
    iIdx = IIf(iRow Mod 2 = 0, IIf(iRow < 20, CInt((iRow - 2) / 2), 8), CInt((iRow - 3) / 2))
    Range("AA1:AA20").Value = Range("A1:A20").Value
    Range("AB1").Resize(iRow, 1).Value = Range(sCol & 1).Resize(iRow, 1).Value
    Range("AA1:AB" & iRow).Sort key1:=Range("AB1"), order1:=xlAscending, Orientation:=xlTopToBottom
    For x = 1 To iRow
        Cells(Range("AA" & x).Value, iTCol).Interior.Color = IIf(x <= iIdx, RGB(0, 180, 240), IIf(x > iRow - iIdx, RGB(0, 110, 190), RGB(255, 255, 255)))
    Next
    Range("AA1:AB20").Delete shift:=xlToLeft
End If
'
Application.EnableEvents = True
'
End Sub

A+

3valeurs.xlsm (22.74 Ko)
Le 12/01/2019 à 01:09

Bonsoir merci curulis57 pour la macro mais j'ai un petit souci pour modifier celle-ci car l'exemple envoyé est un morceau de plusieurs tableaux est la colonne est N

Ci joint le fichier merci

5valeurs2.xlsx (41.24 Ko)
Le 12/01/2019 à 03:33

Bonjour,

peux-tu ajouter une colonne pour simplifier les calculs et éviter une macro ? cette colonne indiquerait justement le rang

Le 12/01/2019 à 03:45

Salut starwaso,

Salut Steelson,

Un truc qui m'échappe : les cellules "vides" de tes tableaux de 20 données... ne sont pas vides et pourtant, je n'y vois rien!

Je ne sais pas comment régler ce mystère sauf à y faire attention lors de la confection de tes tableaux.

Si quelqu'un peut m'expliquer, je suis toute ouïe!

Pour fonctionner correctement, et si tu ajoutes d'autres tableaux, continue à respecter la structure actuelle (3 lignes d'écart), la base des calculs étant un modulo 25.

Private Sub Worksheet_Change(ByVal Target As Range)
'
Dim iTRow%, iRow1%, iRow2%, iIdx%, iNb%
'
Application.EnableEvents = False
Application.ScreenUpdating = False
'
If Not Intersect(Target, Range("N:N")) Is Nothing Then
    iTRow = Target.Row
    iRow1 = iTRow - IIf(iTRow Mod 25 = 0, 19, ((iTRow Mod 25) - 6))
    iRow2 = Range("N" & iRow1 + 20).End(xlUp).Row
    If iRow2 >= iRow1 + 7 Then
        iNb = (iRow2 + 1) - iRow1
        iIdx = IIf(iNb Mod 2 = 0, IIf(iNb < 20, CInt((iNb - 2) / 2), 8), CInt((iNb - 3) / 2))
        Range("AAA1:AAA20").Value = 1
        Range("AAA1:AAA20").DataSeries Type:=xlChronological, Date:=xlDay
        Range("AAB1").Resize(iNb, 1).Value = Range("N" & iRow1).Resize(iNb, 1).Value
        Range("AAA1:AAB" & iNb).Sort key1:=Range("AAB1"), order1:=xlAscending, Orientation:=xlTopToBottom
        For x = 1 To 20
            Cells((iRow1 - 1) + CInt(Range("AAA" & x).Value), 14).Interior.Color = IIf(x <= iIdx, RGB(0, 180, 240), IIf(x > iNb - iIdx And x <= iNb, RGB(0, 110, 190), RGB(255, 255, 255)))
        Next
        Range("AAA1:AAB20").Delete shift:=xlToLeft
    Else
        Range("N" & iRow1).Resize(10, 1).Interior.Color = RGB(255, 255, 255)
    End If
End If
'
Application.ScreenUpdating = True
Application.EnableEvents = True
'
End Sub

A+

6valeurs.xlsm (53.55 Ko)
Le 12/01/2019 à 11:39

Bonjour,

sans macro, mais avec une colonne

j'ai fait la MFC sur le premier groupe

5valeurs2.xlsx (43.26 Ko)
Le 12/01/2019 à 12:31

Bonjour curulis57, Steelson, curulis57, starwaso

J’essaye de garder ton fichier tel qu’il est et sans trier les numéros

Le code garde tjrs deux ou trois valeur sans couleur

Sub test()
Dim vlr@, vlr2@, vlr3@, drn%, cntr%, v%

drn = Range("N" & Rows.Count).End(xlUp).Row

For i = 6 To drn Step 25
   cntr = WorksheetFunction.CountIf(Range("N" & i & ":N" & i + 19), "<1000")
   Range("N" & i & ":N" & i + 19).Interior.ColorIndex = 2
   v = Int((cntr - 2) / 2)
   If cntr >= 18 Then v = 8
   For p = 1 To v
              vlr1 = WorksheetFunction.Large(Range("N" & i & ":N" & i + 19), p)
              vlr2 = WorksheetFunction.Small(Range("N" & i & ":N" & i + 19), p)
        For l = i To i + 19
           If Range("N" & l).Value <> "" Then
              vlr = Range("N" & l).Value
              If vlr = vlr1 Then
                 Range("N" & l).Interior.ColorIndex = 42
              End If
              If vlr = vlr2 Then
              Range("N" & l).Interior.ColorIndex = 32
              End If
           End If
         Next
    Next

Next
End Sub
3valeurs2.xlsm (84.02 Ko)
Le 12/01/2019 à 13:15

Bonjour,

Merci pour vos réponses

bon week end a toutes et a tous

Cdlt

Le 12/01/2019 à 13:54

Salut

j'étais en train de travailler sur ton problème

J’essaye de garder ton fichier tel qu’il est sans trier les numéros ou ajouter une colonne

Alors que est maintenant résolut je poste comme même

Le code garde tjrs deux ou trois valeur sans couleur

Version MFC :

PETITE :

=PETITE.VALEUR($N$6:$N$25;1)

=SI(NB.SI($N$6:$N$25;"< 1000") <18;PETITE.VALEUR($N$6:$N$25;ENT((NB.SI($N$6:$N$25;"< 1000") - 2) / 2));PETITE.VALEUR($N$6:$N$25;8))

GRANDE:

=GRANDE.VALEUR($N$6:$N$25;1)

=SI(NB.SI($N$6:$N$25;"< 1000") <18;GRANDE.VALEUR($N$6:$N$25;ENT((NB.SI($N$6:$N$25;"< 1000") - 2) / 2));GRANDE.VALEUR($N$6:$N$25;8))

6valeurs2-mfc.xlsm (33.84 Ko)
Le 13/01/2019 à 00:41

Salut starwaso,

ainnsi qu'expliqué dans ma dernière réponse, les "cellules vides" dans tes tableaux en [N] ne sont pas vides.

Sélectionne-les et regarde dans la barre d'état en bas de l'écran : il y sera indiqué : NB (non vides)...

Si tu supprimes manuellement ces cellules vides (effacer le contenu) dont j'ignore la nature, ça devrait fonctionner.

D'où vient ce fichier?

A+

Rechercher des sujets similaires à "petite grandes valeurs"