XL 2016 Rang selon plusieurs critères

[Michel_ja]

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]

Bonjour,

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

=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]

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]

Bonjour,

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

=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]

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]

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]

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]

Essaie :

=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]

Essaie :

=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]

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]

Bonjour,

Essaie :

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