XL 2010 Colorier les n plus grandes valeurs d'une plage EXCEL

[aurelio.ewane]

Bonjour les excellistes
je suis confronté à un petit soucis
jai une plage qui va de B2:Y201
je voudrais donc pour chacune des lignes de cette plage colorier les cellules qui contiennent les n valeurs les plus grandes valeurs

n dois etre mis dans la cellule AB1 par exemple

mais le rééel soucis cest que lorsque la dans une ligne il ya 02 valeurs identiques, je n'arrive pas à les colorier toutes
je joins mon fichier

cordialment

 

[AtTheOne]

re

Je rappelle que j'ai donné une solution par MFC au post #29.

Oui j'ai vu, c'est très astucieux et efficace, mais bon j'ai voulu aller au bout de ma démarche, à @aurelio.ewane de décider de ce qu'il veut

À bientôt

 

[aurelio.ewane]

Bonjour à tous,
Si j'ai bien compris, il fallait pouvoir choisir le nombre de plus grandes valeurs.
J'ai revu ma proposition et maintenant tu peux choisir en AA1 le nombre de grande valeur à surligner (0 à 7) S'il faut faire plus, cela ne pose pas de problème.
Le principe est un peu différent :
1 on a un premier tableau technique qui détermine les grandes valeurs de la ligne de 1 à 8
2 on a un deuxième tableau technique qui élimine les doublons du premier
3 on utilise la MEFC sur base du deuxième tableau technique

Ces tableaux techniques peuvent être masqués

A noter que l'on pourrait aisément mettre une couleur différente par niveau de grande valeur.

Si cela ne correspond pas à tous les cas de figure, dites le moi.

A bientôt
Chris

Waouuuuuuuuh c'est genial
tres rapide
interessant
mais lorsqu'on choisi une valeur par exemple pour la premiere colonne ca colorie deux valeurs (150 02 fois alors que l'ideal voudrait que ca coloerie juste une fois)

 

[aurelio.ewane]

Bonjour les excellistes
je suis confronté à un petit soucis
jai une plage qui va de B2:Y201
je voudrais donc pour chacune des lignes de cette plage colorier les cellules qui contiennent les n valeurs les plus grandes valeurs

n dois etre mis dans la cellule AB1 par exemple

mais le rééel soucis cest que lorsque la dans une ligne il ya 02 valeurs identiques, je n'arrive pas à les colorier toutes
je joins mon fichier

cordialment

Impossible de changer le tableaau c'est a dire d'ajouter de slignes au tableau
Sil vous palit quelle est votre version d'excel
la mienne j'utilise la version 2010

 

[mapomme]

Bonjour @aurelio.ewane🙂,

Si nous avons 5 cellules par exemple contenant
Cellule 1 100
Cellule 2 200
Cellule 3 100
cellule 4 120
Cellule 5 109

Si on veut les 02 plus grandes Valeurs on prendra 200 et 120
Si on veut les 03 plus grandes valeurs on Prendra 100 (le premier on ne prends pas le deuxième) 200 et 120
Si on veut les 04 premiers plus grandes valeurs On prendra 100 200 100 et 120

Et la valeur 109, elle compte pour du beurre ?

 

[AtTheOne]

Bonjour à toutes et à tous, bonjour @aurelio.ewane
Allez, une dernière version moins sensible à la position des données et au nombre de colonnes :
Les valeurs à traiter sont dans un tableau structuré "TS_Valeurs", ton nombre de cellules à rehausser dans la cellule nommées "NbVal_Rehaussées"

Tu peux ajouter, supprimer des lignes/colonnes à ton tableau, le déplacer dans la feuille, et déplacer la cellule où tu saisis ton nombre de plus grandes valeurs ...

À toi de voir
À bientôt

 

[mapomme]

Re,
Avec ce que j'en ai compris et juste pour le fun, une version VBA (relativement brute de décoffrage mais néanmoins un peu commentée).
On met à jour "Feuil1" à l'ouverture du classeur, aux changements de valeurs de la feuille "Feuil1" et à l'activation de la feuille "Feuil1".
Le code principal est dans le module de "Feuil1" (il y aussi une ligne de code dans le module de ThisWorkbook) :

Public Sub NgrandeValeur()                ' colorier les N grandes valeurs de gauche à droite (notez que la Sub est publique)
Dim t, i&, j&, k&, N&, valeur, nbr&, max
   With Me                                ' avec la feuille courante (Feuil1)
      Application.ScreenUpdating = False  ' on fige l'écran (plus rapide)
      t = .Range("a1").CurrentRegion: N = .Range("aa1")     ' lecture des valeurs du tableau et de N
      .Range("b2").Resize(UBound(t) - 1, UBound(t, 2) - 1).Interior.ColorIndex = xlColorIndexNone  ' sans fond de couleur
      For i = 2 To UBound(t)                                ' pour chaque ligne du tableau t
         nbr = 0                                            ' nombre de cellules de la ligne i déjà coloriées
         For k = 1 To N                ' pour chaque cellule à colorier
            max = -1E+100              ' max est mis à un nombre négatif (très très petit)
            For j = 2 To UBound(t, 2)  ' on parcourt la ligne i de t
               If t(i, j) <> "" Then If t(i, j) > max Then max = t(i, j)   ' calcul du max de la ligne i
            Next j
            For j = 2 To UBound(t, 2)  ' on parcourt la ligne i de t
               ' si t(i,j) n'est pas vide alors si t(i,j) vaut max, on incrémente le nombre de cellules colorées
               ' et on colore la cellule correspondante et on met t(i,j) à vide - t(i,j n'est plus à traité par la suite
               If t(i, j) <> "" Then If t(i, j) >= max Then nbr = nbr + 1: .Cells(i, j).Interior.Color = vbGreen: t(i, j) = ""
               If nbr = N Then Exit For   ' si nbr (nombre de cellules déjà colorées) vaut N alors on quitte la boucle des colonnes
            Next j
            If nbr = N Then Exit For      ' si nbr (nombre de cellules déjà colorées) vaut N alors on a terminé de colorier le
         Next k                           ' nombre désiré de cellules à colorier et on quitte la boucle de coloriage
      Next i
   End With
End Sub

 

[job75]

Bonjour le forum,

Voici le fichier de mon post #29 avec un tableau structuré.

On peut lui ajouter des lignes et le déplacer.

Formule de la MFC en C7 :

=(C7<>"")*(EQUIV(C7-COLONNE()/1000000;GRANDE.VALEUR($C7:$Z7-COLONNE($C7:$Z7)/1000000;COLONNE($C7:$Z7)-COLONNE($B7));0)<=$B$2)

La numérotation des lignes se fait par la formule :

=LIGNE()-LIGNE(Tableau1[#En-têtes])

A+

 

[AtTheOne]

Re

Voici le fichier de mon post #29 avec un tableau structuré.

Je me disais bien que cette version n'arriverait pas tard 😉!

---

 

[AtTheOne]

re

je precise que jen ai 27 et parfois plus
je veux les trier par ordre
les classeurs sont nommées
class
class 2
class 20
class 1
class 5
class 5
class 21
class 6
class 8
class 2
class 20
class 11
class 15
class 12
class 18
class 16
class 4

Pour répondre à cette demande restée sans réponse (Attention aux noms dupliqués, j'ai supprimé les doublons)

Cette macro trie les feuilles nommées "Class N°"

• Si pas de N° placée en 1er
• Si autre type de nom en fin

Sub tri(a, gauc, droi)          ' Quick sort croissant  de J. Boisgontier adapté
     ref = a(2, (gauc + droi) \ 2)
     g = gauc: d = droi
     Do
          Do While a(2, g) < ref: g = g + 1: Loop
          Do While ref < a(2, d): d = d - 1: Loop
          If g <= d Then
               Temp = a(1, g): a(1, g) = a(1, d): a(1, d) = Temp
               Temp = a(2, g): a(2, g) = a(2, d): a(2, d) = Temp
          g = g + 1: d = d - 1
          End If
     Loop While g <= d
     If g < droi Then Call tri(a, g, droi)
     If gauc < d Then Call tri(a, gauc, d)
End Sub


Sub OrdonneSh()
     Dim Tb()
     nb = 0
     For Each Wsh In ThisWorkbook.Worksheets
          If UCase(Wsh.Name) Like "CLASS*" Then
               nb = nb + 1
               ReDim Preserve Tb(1 To 2, 1 To nb)
               part = Split(Wsh.Name, " ")
               Tb(1, nb) = Wsh.Name
               If UBound(part) = 1 Then
                    Tb(2, nb) = CInt(part(1))
               End If
          End If
     Next
     Call tri(Tb, 1, UBound(Tb, 2))
     ThisWorkbook.Worksheets(Tb(1, 1)).Move Before:=ThisWorkbook.Worksheets(1)
     For i = 2 To UBound(Tb, 2)
          ThisWorkbook.Worksheets(Tb(1, i)).Move After:=ThisWorkbook.Worksheets(Tb(1, i - 1))
     Next
     Application.Goto ThisWorkbook.Worksheets(Tb(1, 1)).[A1]

End Sub

en attendant une amélioration de @job75 😄

À bientôt

 

[job75]

Bonjour mapomme,

Les valeurs du tableau sont des entiers > 0 donc tu peux remplacer max = -1E+100 par max = 0.

A+

 

[CHRIS1945]

Waouuuuuuuuh c'est genial
tres rapide
interessant
mais lorsqu'on choisi une valeur par exemple pour la premiere colonne ca colorie deux valeurs (150 02 fois alors que l'ideal voudrait que ca coloerie juste une fois)

Euh.... alors c'est que je n'ai pas encore compris ta demande car je pensais que c'est justement cela que tu désirais à savoir colorier tous les doublons qui répondent au critère plus grande valeur.
Donc en définitif, tu souhaites colorier la première grande valeur trouvée et laisser la deuxième voire la troisième identique sans colorier et puis colorier la deuxième plus grande valeur DIFFERENTE et ainsi de suite...
Je ne comprend pas bien le raisonnement mais après tout je ne sais pas à quoi sert ce tableau.
Pour moi il me semble important d'attirer l'attention sur les doublons car en fait ils ont la même valeur que la première trouvée et ne pas les montrer fausse le raisonnement.
Peux tu expliquer le but rechercher ? Et par la même occasion me dire comment choisir lequel montrer ?
Si tu confirmes que c'est nécessaire... alors je le ferai...
Mais dis moi d'abord si la solution proposée t'intéresse avant que je ne passe encore quelques heures sur le sujet.
Que penses tu de l'idée de mettre des couleurs différentes par niveaux de grandes valeurs ? (Par exemple rouge pour la première et vert pour la deuxième etc...)
A bientôt
Chris

 

[AtTheOne]

Bonsoir @job75

Les valeurs du tableau sont des entiers > 0

Ça c'est une déduction suite à la lecture de l'exemple (peut-être juste d'ailleurs) mais ce n'est pas dit dans l'énoncé :

jai une plage qui va de B2:Y201
je voudrais donc pour chacune des lignes de cette plage colorier les cellules qui contiennent les n valeurs les plus grandes valeurs

Alors à mon avis prudence ! 🤔

 

[TooFatBoy]

Donc en définitif, tu souhaites colorier la première grande valeur trouvée et laisser la deuxième voire la troisième identique sans colorier et puis colorier la deuxième plus grande valeur DIFFERENTE et ainsi de suite...

Moi je n'ai pas compris ça. 🙁

J'ai compris ceci : tu prends toutes les valeurs, tu les classes dans l'ordre décroissant (mais les doublons doivent rester entre eux dans leur ordre initial), puis tu prends les n premières valeurs de cette liste.
J'ai bon ?

 

[AtTheOne]

J'ai compris ceci : tu prends toutes les valeurs, tu les classes dans l'ordre décroissant (mais les doublons doivent rester entre eux dans leur ordre initial), puis tu prends les n premières valeurs de la liste.
J'ai bon ?

C'est ce que j'ai compris aussi, et @aurelio.ewane l'a repréciser dans les post# et suivants :

Lorsque l'on veut les 03 premières plus grandes valeurs, alors ca colorie 100 02 fois mais ca ne compte que une fois et du coup sur cette ligne ca a colorier 04 cellules au lieu de 03 cellules
qui devait etre les cellules contenant
1ere cellule celle qui contient 100
2e cellule celle qui contient 100
3e cellule celle qui contient 150
ca fait en tous 03 plus grandes valeurs

À plus

 

[mapomme]

Re,
Un autre version VBA v11 plus rapide et plus simple que la v1.
Une petite nouveauté : la couleur de fond des cellules sera la couleur de fond de la cellule AA1. Quand vous modifiez la couleur de fond de cette cellule, revalidez son contenu (celui de la cellule AA1) pour la prise en compte de la nouvelle couleur.
Le code de la v11 :

Public Sub NgrandeValeur()                ' colorier les N grandes valeurs de gauche à droite (notez que la Sub est publique)
Dim t, Ncell&, i&, nv&, j&, k&, ech As Boolean, x, n&, couleur
   With Me                                ' avec la feuille courante (Feuil1)
      Application.ScreenUpdating = False  ' on fige l'écran (l'affichage est plus rapide)
      t = .Range("a1").CurrentRegion: Ncell = .Range("aa1")     ' lecture des valeurs du tableau et de N
      couleur = .Range("aa1").Interior.Color                              ' couleur du fond à utiliser
      .Range("b2").Resize(UBound(t) - 1, UBound(t, 2) - 1).Interior.ColorIndex = xlColorIndexNone  ' sans fond de couleur
      ' on déclare un tableau v des valeurs d'une ligne et de leur indice au sein de la ligne
      ReDim v(1 To UBound(t, 2) - 1): ReDim a(1 To UBound(v))
      For i = 2 To UBound(t)                                ' pour chaque ligne du tableau t,on remplit les tableaux v et a
         nv = 0                                             ' nv = nombre de valeurs de la ligne (au départ 0)
         For j = 2 To UBound(t, 2)                          ' pour chaque colonne de la ligne i du tableau t
            If t(i, j) <> "" Then nv = nv + 1: v(nv) = t(i, j): a(nv) = j  ' on stocke la valeur dans v et son index dans a
         Next j
         ' Tri de v et a (du plus grand au plus petit avec un tri à bulle car c'est un tri stable)
         Do                      ' boucle infinie
            ech = False          ' initialisation de l'indicateur d'échange pour un tour (au départ aucun échange)
            For k = 1 To nv - 1  ' depuis le premier élément de v jusqu'au dernier moins 1
               ' si l'élément k est inf. à l'élément k+1, on inverse les éléments ket k+1 de v (et a) et ech est true
               If v(k) < v(k + 1) Then x = v(k): v(k) = v(k + 1): v(k + 1) = x: x = a(k): a(k) = a(k + 1): a(k + 1) = x: ech = True
            Next k
         Loop While ech          ' Tant qu'on a fait au moins un échange on refait un tour - sinon le tri est terminé
         If Ncell > nv Then n = nv Else n = Ncell   ' nbre de cellules à colorier (le max étant nv : nombre de valeurs de la ligne)
         For j = 1 To n: .Cells(i, a(j)).Interior.Color = couleur: Next      ' coloriage
      Next i
   End With
End Sub