Vous utilisez un navigateur obsolète. Il se peut que ce site ou d'autres sites Web ne s'affichent pas correctement. Vous devez le mettre à jour ou utiliser un navigateur alternatif.
XL 2010Colorier les n plus grandes valeurs d'une plage EXCEL
Boostez vos compétences Excel avec notre communauté !
Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force.
Apprenez, échangez, progressez – et tout ça gratuitement !
👉 Inscrivez-vous maintenant !
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
par exemple ici on veut les 03 plus grandes valeurs dans la troisieme ligne il prends 100 100 59 et 72 cela fait plus de 03 plus grandes valeurs
il aurait du prendre 100 100 et 72
j'arrive vraiment pas a comprendre comment le modifier
Sub ValN()
Dim WSh As Worksheet, Dc As Object, Rg As Range, CellRang As Range
Set WSh = Feuil1 ' Wsh : la feuille concernée (Feuil1 c'est le CodeName de la feuille)
Rang = WSh.[AB1] ' Rang : La valeur contenue dans la cellule AB1 de la feuille WSh
Set Rg = WSh.[A1].CurrentRegion 'Rg la zone contenant la cellule A1,
'Cette zone doit être séparée du reste de la feuille par une colonne et une ligne vide
'Redéfinir Rg pour en ôter le titres des lignes et des colonnes
Set Rg = Rg.Offset(1, 1).Resize(Rg.Rows.Count - 1, Rg.Columns.Count - 1) ' Rg ne contient plus que la plage de données à traiter
'Créer un dictionnaire, il va servir à obtenir les valeur sans doublons de chaque ligne
Set Dc = CreateObject("Scripting.Dictionary")
NbCol = Rg.Columns.Count ' Nombre de colonnes de notre plage Rg
Rg.FormatConditions.Delete ' On efface les formats conditionnels de la plage Rg, avant de les redéfinir ligne par ligne
For Each Ligne In Rg.Rows ' Boucle sur chaque lignes de la plage Rg
tb = Ligne ' tb valeur contenue dans la ligne (un tableau 1 ligne NBCol colonnes)
Dc.RemoveAll ' à chaque ligne on commance par vider le dictionnaire
'Pour chaque valeur de tb, si la valeur n'est pas vide on défini une entrée du dictionnaire
'Si une clef existe déjà, elle est écrasée, donc on n'a pas de doublon :
For i = 1 To NbCol
If Not IsEmpty(tb(1, i)) Then Dc(tb(1, i)) = CDbl("0" & tb(1, i))
Next i
'si la ligne n'était pas vide, on a au moins une clef
If Dc.Count > 0 Then
'On recupère dans tb les valeurs du dictionnaire (tableau 1 ligne base 0)
tb = Dc.items
'On tri le tableau (dans l'ordre décroissant)
Call tri(tb, 0, UBound(tb))
If Rang > UBound(tb) Then
'Si le rang demandé est plus grand que la borne supérieur de tb on prend la borne supérieur de tb
Valeur = tb(UBound(tb))
Else
'Sinon on prend le la valeur correspondant au rang demandé (-1 car le 1er index vaut 0)
Valeur = tb(Rang - 1)
End If
'On crée le format conditionnel pour la ligne en cours
With Ligne.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=" & Valeur)
.Font.Bold = True 'Police grasse
.Interior.Color = 13408767 'fond rose
End With
End If
Next Ligne
End Sub
par exemple ici on veut les 03 plus grandes valeurs dans la troisieme ligne il prends 100 100 59 et 72 cela fait plus de 03 plus grandes valeurs
il aurait du prendre 100 100 et 72
Ici les trois plus grandes valeurs sont bien 100, 72 et 59, donc il y 4 cellules concernées, imagine que tu aies 5 cellules contenant 100 ou 1 contenant 100, une 72 et trois 59, si on n'en prend que 3, lesquelles prendre parmi les cinq ?
STP détaille un peu plus ton besoin, avec ce que tu veux pour ces cas tordus.
À bientôt
EDIT : il faut peut-être rechercher le rang de chaque valeur de la ligne et ne surligner que celles qui sont inférieures ou égales à ce rang ?
Bonjour à tous,
Voici une proposition sans VBA qui permet de gérer les doublons de premier et deuxième valeur.
J'ai utilisé 3 colonnes supplémentaires qui me permettent de tester et puis la MEFC.
Le 3 colonnes peuvent être masquées.
Dites moi si cela convient.
A bientôt
Chris
re
Nouvelle version avec VBA et prise en compte du rang des valeurs de chaque ligne
VB:
Sub MFC_Via_Rang()
Dim WSh As Worksheet, Dc As Object, Rg As Range, CellRang As Range
Set WSh = Feuil1 ' Wsh : la feuille concernée (Feuil1 c'est le CodeName de la feuille)
Rang = WSh.[AB1] ' Rang : La valeur contenue dans la cellule AB1 de la feuille WSh
With WSh.[A1].CurrentRegion ' la zone continue contenant la cellule A1,
' cette zone doit être séparée du reste de la feuille par une colonne et une ligne vide
Set Rg = .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1) ' Rg ne contient plus que la plage de données à traiter
End With
NbCol = Rg.Columns.Count 'Nombre de colonne dans la plage étudiée
Rg.FormatConditions.Delete ' On efface les formats conditionnels de la plage Rg, avant de les redéfinir ligne par ligne
For Each Ligne In Rg.Rows
tb = Ligne
Min = WorksheetFunction.Min(tb) - 1 'valeur inférieure à toutes les valeurs de la ligne pour placer dans les cellules vides avant le tri
For i = 1 To UBound(tb, 2)
If IsEmpty(tb(1, i)) Then tb(1, i) = Min
Next i
Call tri(tb, 1, UBound(tb, 2))
'Recherche de la valeur correspondant au rang rechercher
V = Min: r = 0: o = 1: i = 0
Do
i = i + 1
If tb(1, i) <> V And r + o <= Rang And tb(1, i) <> Min Then
V = tb(1, i)
r = r + o
o = 1
Else
o = o + 1
End If
Loop While r < Rang And i < NbCol
'On crée le format conditionnel pour la ligne en cours
adresse = Ligne.Cells(1).Address(False, False)
If V > Min Then 'On exclue les lignes entièrement vides
With Ligne.FormatConditions.Add(Type:=xlExpression, Formula1:="=ESTNUM(" & adresse & ")*(" & adresse & ">=" & V & ")")
.Font.Bold = True 'Police grasse
.Interior.Color = 13408767 'fond rose
End With
End If
Next Ligne
End Sub
Et l'événement Worksheet_Change de la feuille
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
With Me.[A1].CurrentRegion 'Rg la zone contenant la cellule A1,
Set Rg = .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1) ' Rg ne contient plus que la plage de données à traiter
End With
If Target.Address = Me.[AB1].Address Or Not Intersect(Target, Rg) Is Nothing Then MFC_Via_Rang
End Sub
Merci c'est toujours pareil peut c'est moi qui m'exprime mal
regarde par exemple ce fichier le dernier que tu ma envoyé
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
le probleme reste entier et se pose lorsque parmi les n valeurs les plus grandes on a des valeurs identiques
Comment le resoudre ca deviens un veritable casse tete
Sub ValN()
Dim WSh As Worksheet, Dc As Object, Rg As Range, CellRang As Range
Set WSh = Feuil1 ' Wsh : la feuille concernée (Feuil1 c'est le CodeName de la feuille)
Rang = WSh.[AB1] ' Rang : La valeur contenue dans la cellule AB1 de la feuille WSh
Set Rg = WSh.[A1].CurrentRegion 'Rg la zone contenant la cellule A1,
'Cette zone doit être séparée du reste de la feuille par une colonne et une ligne vide
'Redéfinir Rg pour en ôter le titres des lignes et des colonnes
Set Rg = Rg.Offset(1, 1).Resize(Rg.Rows.Count - 1, Rg.Columns.Count - 1) ' Rg ne contient plus que la plage de données à traiter
'Créer un dictionnaire, il va servir à obtenir les valeur sans doublons de chaque ligne
Set Dc = CreateObject("Scripting.Dictionary")
NbCol = Rg.Columns.Count ' Nombre de colonnes de notre plage Rg
Rg.FormatConditions.Delete ' On efface les formats conditionnels de la plage Rg, avant de les redéfinir ligne par ligne
For Each Ligne In Rg.Rows ' Boucle sur chaque lignes de la plage Rg
tb = Ligne ' tb valeur contenue dans la ligne (un tableau 1 ligne NBCol colonnes)
Dc.RemoveAll ' à chaque ligne on commance par vider le dictionnaire
'Pour chaque valeur de tb, si la valeur n'est pas vide on défini une entrée du dictionnaire
'Si une clef existe déjà, elle est écrasée, donc on n'a pas de doublon :
For i = 1 To NbCol
If Not IsEmpty(tb(1, i)) Then Dc(tb(1, i)) = CDbl("0" & tb(1, i))
Next i
'si la ligne n'était pas vide, on a au moins une clef
If Dc.Count > 0 Then
'On recupère dans tb les valeurs du dictionnaire (tableau 1 ligne base 0)
tb = Dc.items
'On tri le tableau (dans l'ordre décroissant)
Call tri(tb, 0, UBound(tb))
If Rang > UBound(tb) Then
'Si le rang demandé est plus grand que la borne supérieur de tb on prend la borne supérieur de tb
Valeur = tb(UBound(tb))
Else
'Sinon on prend le la valeur correspondant au rang demandé (-1 car le 1er index vaut 0)
Valeur = tb(Rang - 1)
End If
'On crée le format conditionnel pour la ligne en cours
With Ligne.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=" & Valeur)
.Font.Bold = True 'Police grasse
.Interior.Color = 13408767 'fond rose
End With
End If
Next Ligne
End Sub
Ici les trois plus grandes valeurs sont bien 100, 72 et 59, donc il y 4 cellules concernées, imagine que tu aies 5 cellules contenant 100 ou 1 contenant 100, une 72 et trois 59, si on n'en prend que 3, lesquelles prendre parmi les cinq ?
STP détaille un peu plus ton besoin, avec ce que tu veux pour ces cas tordus.
À bientôt
EDIT : il faut peut-être rechercher le rang de chaque valeur de la ligne et ne surligner que celles qui sont inférieures ou égales à ce rang ?
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
je ne sais pas si tu saisi la logique je continue de chercher
mais je pense quon va tres loin
moi jai pensé simplement que lon peut agir ainsi
Prendre la première ligne du tableau et celle qui contient les numero d'odre
les joindre dans un tableau en VBA qui sera un tableau de 02 lignes plusieurs colonnes
Ensuite trier ce tableau en vba en prenant comme pivot la colonne qui contient nos données
La colonne qui contient les numéro d'ordre nous permettra de savoir a quelle position sont les nombres
il ne nous reste qu'a parcourir le tableau de en prenant k allant de l'indice 1 à n (n étant ici le nombre de plus grandes valeurs)
et récupérer les positions correspondantes et ensuite sachant que cette position sera plus 1 parceque le Tableau commence à B
et simplement colorier la cellule de cette ligne et de numéro de colonne (k+1)
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
je ne sais pas si tu saisi la logique je continue de chercher
mais je pense quon va tres loin
moi jai pensé simplement que lon peut agir ainsi
Prendre la première ligne du tableau et celle qui contient les numero d'odre
les joindre dans un tableau en VBA qui sera un tableau de 02 lignes plusieurs colonnes
Ensuite trier ce tableau en vba en prenant comme pivot la colonne qui contient nos données
La colonne qui contient les numéro d'ordre nous permettra de savoir a quelle position sont les nombres
il ne nous reste qu'a parcourir le tableau de en prenant k allant de l'indice 1 à n (n étant ici le nombre de plus grandes valeurs)
et récupérer les positions correspondantes et ensuite sachant que cette position sera plus 1 parceque le Tableau commence à B
et simplement colorier la cellule de cette ligne et de numéro de colonne (k+1)
Re ! @aurelio.ewane as-tu regardé mon post#20 ?
Il me semble qu'il correspond à ta demande.
Mais dans le cas où on a 100 200 100 120 109 et que l'on veux les cellules qui contiennent les 3 plus grandes valeurs pourquoi ne pas considérer les 2 valeurs 100 et ne pas colorer 200, 120 et les deux 100 ce qui bien sûr ferait 4 cellules mais n'en éliminera pas une arbitrairement.
Pire imagine que toutes les cellules contiennent la même valeur, alors elles contiennent toutes la plus grande valeur...
Personnellement je pense qu'il faut colorer les quatre valeurs (normal c'est ce que j'ai choisi de faire 😄)
Ma démarche est de
stocker les valeurs d'une ligne dans un tableau de variables
trier dans l'ordre décroissant ce tableau
rechercher la valeur correspondant au rang demandé
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
je ne sais pas si tu saisi la logique je continue de chercher
mais je pense quon va tres loin
moi jai pensé simplement que lon peut agir ainsi
Prendre la première ligne du tableau et celle qui contient les numero d'odre
les joindre dans un tableau en VBA qui sera un tableau de 02 lignes plusieurs colonnes
Ensuite trier ce tableau en vba en prenant comme pivot la colonne qui contient nos données
La colonne qui contient les numéro d'ordre nous permettra de savoir a quelle position sont les nombres
il ne nous reste qu'a parcourir le tableau de en prenant k allant de l'indice 1 à n (n étant ici le nombre de plus grandes valeurs)
et récupérer les positions correspondantes et ensuite sachant que cette position sera plus 1 parceque le Tableau commence à B
et simplement colorier la cellule de cette ligne et de numéro de colonne (k+1)
Bonjour à tous,
Voici une proposition sans VBA qui permet de gérer les doublons de premier et deuxième valeur.
J'ai utilisé 3 colonnes supplémentaires qui me permettent de tester et puis la MEFC.
Le 3 colonnes peuvent être masquées.
Dites moi si cela convient.
A bientôt
Chris
Re ! @aurelio.ewane as-tu regardé mon post#20 ?
Il me semble qu'il correspond à ta demande.
Mais dans le cas où on a 100 200 100 120 109 et que l'on veux les cellules qui contiennent les 3 plus grandes valeurs pourquoi ne pas considérer les 2 valeurs 100 et ne pas colorer 200, 120 et les deux 100 ce qui bien sûr ferait 4 cellules mais n'en éliminera pas une arbitrairement.
Pire imagine que toutes les cellules contiennent la même valeur, alors elles contiennent toutes la plus grande valeur...
Personnellement je pense qu'il faut colorer les quatre valeurs (normal c'est ce que j'ai choisi de faire 😄)
Ma démarche est de
stocker les valeurs d'une ligne dans un tableau de variables
trier dans l'ordre décroissant ce tableau
rechercher la valeur correspondant au rang demandé
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
je ne sais pas si tu saisi la logique je continue de chercher
mais je pense quon va tres loin
moi jai pensé simplement que lon peut agir ainsi
Prendre la première ligne du tableau et celle qui contient les numero d'odre
les joindre dans un tableau en VBA qui sera un tableau de 02 lignes plusieurs colonnes
Ensuite trier ce tableau en vba en prenant comme pivot la colonne qui contient nos données
La colonne qui contient les numéro d'ordre nous permettra de savoir a quelle position sont les nombres
il ne nous reste qu'a parcourir le tableau de en prenant k allant de l'indice 1 à n (n étant ici le nombre de plus grandes valeurs)
et récupérer les positions correspondantes et ensuite sachant que cette position sera plus 1 parceque le Tableau commence à B
et simplement colorier la cellule de cette ligne et de numéro de colonne (k+1)
ensuite boucler sur toutes les lignes
Comment faire cela en VBA
je suis daccord avec toi mais da'pres mon cahieer de charge il faut le faire comme je lai mentionné
Voici en fait ce que je cherche à faire
je ne sais pas si tu saisi la logique je continue de chercher
mais je pense quon va tres loin
moi jai pensé simplement que lon peut agir ainsi
Prendre la première ligne du tableau et celle qui contient les numero d'odre
les joindre dans un tableau en VBA qui sera un tableau de 02 lignes plusieurs colonnes
Ensuite trier ce tableau en vba en prenant comme pivot la colonne qui contient nos données
La colonne qui contient les numéro d'ordre nous permettra de savoir a quelle position sont les nombres
il ne nous reste qu'a parcourir le tableau de en prenant k allant de l'indice 1 à n (n étant ici le nombre de plus grandes valeurs)
et récupérer les positions correspondantes et ensuite sachant que cette position sera plus 1 parceque le Tableau commence à B
et simplement colorier la cellule de cette ligne et de numéro de colonne (k+1)
Voici en fait ce que je cherche à faire
je ne sais pas si tu saisi la logique je continue de chercher
mais je pense quon va tres loin
moi jai pensé simplement que lon peut agir ainsi
Prendre la première ligne du tableau et celle qui contient les numero d'odre
les joindre dans un tableau en VBA qui sera un tableau de 02 lignes plusieurs colonnes
Ensuite trier ce tableau en vba en prenant comme pivot la colonne qui contient nos données
La colonne qui contient les numéro d'ordre nous permettra de savoir a quelle position sont les nombres
il ne nous reste qu'a parcourir le tableau de en prenant k allant de l'indice 1 à n (n étant ici le nombre de plus grandes valeurs)
et récupérer les positions correspondantes et ensuite sachant que cette position sera plus 1 parceque le Tableau commence à B
et simplement colorier la cellule de cette ligne et de numéro de colonne (k+1)
Bonjour à toutes & à tous, bonjour @aurelio.ewane
Une solution sans MFC et ne mettant que n cellules en évidence :
VB:
Sub ReHausserTopRang()
Dim WSh As Worksheet, Dc As Object, Rg As Range, CellRang As Range
Set WSh = Feuil1 ' Wsh : la feuille concernée (Feuil1 c'est le CodeName de la feuille)
Rang = WSh.[AB1] ' Rang : La valeur contenue dans la cellule AB1 de la feuille WSh
With WSh.[A1].CurrentRegion ' la zone continue contenant la cellule A1,
' cette zone doit être séparée du reste de la feuille par une colonne et une ligne vide
Set Rg = .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1) ' Rg ne contient plus que la plage de données à traiter
End With
Nbcol = Rg.Columns.Count 'Nombre de colonne dans la plage étudiée
Application.ScreenUpdating = False
With Rg
.Font.Bold = False
.Interior.Pattern = xlNone
End With
ReDim TbTRi(1 To 2, 1 To Nbcol)
For Each Ligne In Rg.Rows
tb = Ligne
Min = WorksheetFunction.Min(tb) - 1 'valeur inférieure à toutes les valeurs de la ligne pour placer dans les cellules vides avant le tri
For i = 1 To Nbcol
TbTRi(1, i) = i
If IsEmpty(tb(1, i)) Then
TbTRi(2, i) = Min
Else
TbTRi(2, i) = tb(1, i)
End If
Next i
Call tri(TbTRi, 1, Nbcol)
For i = 1 To Rang
If TbTRi(2, i) <> Min Then
With Ligne.Cells(TbTRi(1, i))
.Font.Bold = True
.Interior.Color = 13408767
End With
End If
Next i
Next Ligne
Application.ScreenUpdating = True
End Sub
Sub tri(a, gauc, droi) ' Quick sort dé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(2, g): a(2, g) = a(2, d): a(2, d) = Temp
Temp = a(1, g): a(1, g) = a(1, d): a(1, 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
Voir fichier Joint
À bientôt
Edit: il faut ajouter une deuxième clef de tri (sur les N°) si l'on veut s'assurer, quand il y a des doublons de prendre les premiers dans l'ordre des N°
- Navigue sans publicité - Accède à Cléa, notre assistante IA experte Excel... et pas que... - Profite de fonctionnalités exclusives Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel. Je deviens Supporter XLD
