XL 2016 Rang selon plusieurs critères

Michel_ja · 25 Janvier 2021

Bonjour à Tous,
je chercher une formule qui me permettra de faire le classement d'une liste obéissant à plusieurs critères choisis par un utilisateur. Les critères sont des variables qualitatives et la colonne pour le classement sont des volumes. Compte tenu que mon fichier contiendra quelques milliers de lignes, peut-être 4000 à 5000 lignes, je me demande si on peut se passer d'une formule matricielle qui utilise beaucoup de mémoire.
J'ai joint un fichier excel, je souhaiterai classé du plus grand au plus petit, depuis la Cellule C7 à C12 (pour l'année 2015), le nom du modèle (Col H) qui arrive en tête en fonction des trois variables entrées en C2, D2 et E2.
Merci Beaucoup.

danielco · 25 Janvier 2021

Bonjour,

Essaie en C7, à recopier vers la droite et le bas :

VB:

=SIERREUR(AGREGAT(14;6;M$5:M$53/(($G$5:$G$53=$C$2)*($K$5:$K$53=$D$2)*($J$5:$J$53=$E$2));LIGNE(A1));"")

Ca serait bien que tu ne mettes pas de fonctions ALEA dans tes données. Ca ne facilite pas les tests.

Pas dit que ce ne soit pas trop lourd, cependant.

Daniel

JHA · 25 Janvier 2021

Bonjour à tous,
Bonjour Danielco

Avec la table des données sous mode "Tableau" et un essai par TCD pour plus d'options avec les segments.

JHA

Michel_ja · 26 Janvier 2021

danielco à dit:
Bonjour,

Essaie en C7, à recopier vers la droite et le bas :

VB:

=SIERREUR(AGREGAT(14;6;M$5:M$53/(($G$5:$G$53=$C$2)*($K$5:$K$53=$D$2)*($J$5:$J$53=$E$2));LIGNE(A1));"")

Ca serait bien que tu ne mettes pas de fonctions ALEA dans tes données. Ca ne facilite pas les tests.

Pas dit que ce ne soit pas trop lourd, cependant.

Daniel

Bonjour Danielco. Merci beaucoup pour ta réponse. Sauf erreur de ma part, ta formule me renvoie les volumes de la colonne M par ordre décroissant. Mon besoin est un peu différent, est ce que je pourrait avoir le nom du modèle correspondant (à ces volumes) mais qui figure en colonne H ? Merci
Désolé d'avoir laissé les Alea sur le fichier joint.

Michel_ja · 26 Janvier 2021

Michel_ja à dit:
Bonjour Danielco. Merci beaucoup pour ta réponse. Sauf erreur de ma part, ta formule me renvoie les volumes de la colonne M par ordre décroissant. Mon besoin est un peu différent, est ce que je pourrait avoir le nom du modèle correspondant (à ces volumes) mais qui figure en colonne H ? Merci
Désolé d'avoir laissé les Alea sur le fichier joint.

Merci JHA pour ton retour. Je crois que j'ai oublié de préciser que mon besoin est de classer les modèles ( de ta Col A par exemple) par ordre décroissant (dans mon fichier, le modèle associé au rang 1, puis 2, de la Col B) en fonction des volumes de l'année 2015 par exemple. Avec ta proposition TCD, Je n'obtiens pas un classement par exemple en choisissant les volumes de l'année 2015 (ou autre par la suite). Je pense que pour changer l'année de référence il suffirait alors de changer le tri dans Excel !

danielco · 26 Janvier 2021

Michel_ja à dit:
Bonjour Danielco. Merci beaucoup pour ta réponse. Sauf erreur de ma part, ta formule me renvoie les volumes de la colonne M par ordre décroissant. Mon besoin est un peu différent, est ce que je pourrait avoir le nom du modèle correspondant (à ces volumes) mais qui figure en colonne H ? Merci
Désolé d'avoir laissé les Alea sur le fichier joint.

Essaie :

=SIERREUR(INDEX($H$1:$H$53;AGREGAT(14;6;LIGNE(M$5:M$53)/(($G$5:$G$53=$C$2)*($K$5:$K$53=$D$2)*($J$5:$J$53=$E$2));LIGNE(A1)));"")

Daniel

Michel_ja · 26 Janvier 2021

danielco à dit:
Essaie :

=SIERREUR(INDEX($H$1:$H$53;AGREGAT(14;6;LIGNE(M$5:M$53)/(($G$5:$G$53=$C$2)*($K$5:$K$53=$D$2)*($J$5:$J$53=$E$2));LIGNE(A1)));"")

Daniel

Merci Danielco. Ta formule me remonte bien l'ensemble des modèles qui obéissent aux trois variables exigées. Par contre, il n'y a pas de rang associé qui permettrait de faire un podium (ou liste) avec les modèles ayant le plus de succès dans l'année 2015 (par exemple), en cellule C7 pour le plus performant, donc 6us83r dans mon exemple. En ajoutant une fonction RechercheV ou grandevaleur ça serait une piste ? Merci beaucoup.
Michel

danielco · 26 Janvier 2021

Essaie :

VB:

=SIERREUR(INDEX($H$1:$H$53;SOMMEPROD(($M$5:$M$53=AGREGAT(14;6;M$5:M$53/(($G$5:$G$53=$C$2)*($K$5:$K$53=$D$2)*($J$5:$J$53=$E$2));LIGNE(A1)))*($G$5:$G$53=$C$2)*($K$5:$K$53=$D$2)*($J$5:$J$53=$E$2)*LIGNE($K$5:$K$53)));"")

Michel_ja · 26 Janvier 2021

danielco à dit:

Essaie :

VB:

=SIERREUR(INDEX($H$1:$H$53;SOMMEPROD(($M$5:$M$53=AGREGAT(14;6;M$5:M$53/(($G$5:$G$53=$C$2)*($K$5:$K$53=$D$2)*($J$5:$J$53=$E$2));LIGNE(A1)))*($G$5:$G$53=$C$2)*($K$5:$K$53=$D$2)*($J$5:$J$53=$E$2)*LIGNE($K$5:$K$53)));"")

Danielco. Ta formule fonctionne bien toutefois lorsque les volumes sont identiques dans la colonne des M, cela me renvoie un 0. Peut-on forcer à ramener un nom de modèle, quitte à prendre le premier qui apparait dans la liste par ordre alphabétique (ou autre logique) ? Je suis un peu gêné de te demander si tu as une solution à chaque petit bug. Merci

danielco · 26 Janvier 2021

Je ne vois pas comment faire autrement qu'avec une formule matricielle tarabiscotée. Je vais le faire par macro, sans doute demain.

Daniel

danielco · 27 Janvier 2021

Bonjour,

Essaie :

VB:

Sub Podium()
  Dim Plage As Range, C As Range, Result(1 To 5, 1 To 2), Res(1 To 5, 1 To 2)
  Dim Plage1 As Range
  [C7:E12] = ""
  Set Plage = Range("G4:O4").Resize(Application.CountA([G:G]))
  Plage.AutoFilter
  Plage.AutoFilter 1, Range("C2").Value
  Plage.AutoFilter 5, [D2]
  Plage.AutoFilter 4, [E2]
  Set Plage = Plage.Offset(1).Resize(Plage.Rows.Count - 1)
  Plage.Sort [M5], xlDescending, Header:=xlNo
  Set Plage = Plage.SpecialCells(xlCellTypeVisible)
  Set Plage = Plage.Resize(, 1).Offset(, 1)
  Plage.Copy
  [C7].PasteSpecial xlValues
  Set Plage = Range("G4:O4").Resize(Application.CountA([G:G]))
  Set Plage = Plage.Offset(1).Resize(Plage.Rows.Count - 1)
  Plage.Sort [N5], xlDescending, Header:=xlNo
  Set Plage = Plage.SpecialCells(xlCellTypeVisible)
  Set Plage = Plage.Resize(, 1).Offset(, 1)
  Plage.Copy
  [D7].PasteSpecial xlValues
  Set Plage = Range("G4:O4").Resize(Application.CountA([G:G]))
  Set Plage = Plage.Offset(1).Resize(Plage.Rows.Count - 1)
  Plage.Sort [O5], xlDescending, Header:=xlNo
  Set Plage = Plage.SpecialCells(xlCellTypeVisible)
  Set Plage = Plage.Resize(, 1).Offset(, 1)
  Plage.Copy
  [E7].PasteSpecial xlValues
  ActiveSheet.ShowAllData
End Sub

Note : La plage de données est triée. Si tu veux que je la remettes dans l'état initial, dis-le.

Daniel

XL 2016 Rang selon plusieurs critères

Michel_ja

XLDnaute Occasionnel

Pièces jointes

danielco

XLDnaute Accro

JHA

XLDnaute Barbatruc

Pièces jointes

Michel_ja

XLDnaute Occasionnel

Michel_ja

XLDnaute Occasionnel

danielco

XLDnaute Accro

Michel_ja

XLDnaute Occasionnel

danielco

XLDnaute Accro

Michel_ja

XLDnaute Occasionnel

danielco

XLDnaute Accro

danielco

XLDnaute Accro

Discussions similaires