Tri en fonction d'un Nb Maxi

[JBARBE]

Bonjour à tous,
Je voudrais un tri en fonction d'un Nb Maxi en B2 ( ici 14 sur 20 )
Merci à l'avance et bonne journée !

 

[JHA]

Bonjour à tous,

A essayer en "N6"

=SIERREUR(RANG(K6;INDIRECT("$K$6:$K$"&5+$B$2);0);"")

Copier vers le bas

Edit: pour les doublons, tu peux essayer cette formule

=SIERREUR(RANG(K6;INDIRECT("$K$6:$K$"&5+$B$2);0)+NB.SI($N$5:$N5;$N5)-1;"")

JHA

 

[JBARBE]

Bonjour JHA,
Super ça marche !
Merci et bonne soirée !

 

[job75]

Bonjour JBARBE, JHA,

Une formule un peu compliquée mais qui a l'avantage de ne pas utiliser de fonctions volatiles :

=(LIGNE()<LIGNE(K$6)+B$2)*SOMMEPROD((LIGNE(K$6:K$25)<LIGNE(K$6)+B$2)*(K$6:K$25-LIGNE(K$6:K$25)/"1E12">=K6-LIGNE()/"1E12"))

Sur le tableau source on peut dupliquer une ligne, il n'y aura pas de doublon de rang.

Edit : avec "1E12" la formule fonctionne bien jusqu'à un maximum de 1000 et un minimum de -999,999999999998 en colonne K.

S'il peut y avoir des nombres au-delà de ces limites il faut diminuer l'exposant.

A+

 

[job75]

Re,

Bonjour JHA,
Super ça marche !

Non, avec la 2ème formule de JHA si l'on copie B7:K7 sur B8:K8 il y a 2 fois le rang 5.

Par ailleurs sur un fichier .xls on n'utilise pas en général la fonction SIERREUR...

A+

 

[JHA]

Bonjour à tous,

Ci joint un second essai pour les doublons.

Pas besoin d'enregistrer en xls avec une version excel 2007

JHA

 

[JBARBE]

Bonjour à tous,

Pas besoin d'enregistrer en xls avec une version excel 2007

JHA

Bonjour à tous bonjour Job, JHA,
En effet, j'ai la version 2007 mais j'ai enregistré en xls pour ceux qui n'ont que 2003 et antérieur !
Merci une nouvelle fois et bonne journée !

 

[job75]

Bonjour JBARBE, JHA, le forum,

Comme je l'ai dit la méthode avec "1E12" (pour éliminer les doublons) a ses limites.

On peut l'éviter, le plus simple est alors d'utiliser 2 colonnes, avec et sans doublon.

Formule en N6 :

=(LIGNE()<LIGNE(K$6)+B$2)*SOMMEPROD((LIGNE(K$6:K$25)<LIGNE(K$6)+B$2)*(K$6:K$25>=K6))

Formule en O6 :

=MAX(N6-NB.SI(N6:N$25;N6)+1;)

Fichier (2).

j'ai enregistré en xls pour ceux qui n'ont que 2003 et antérieur !

Et justement pour eux les formules avec SIERREUR ne fonctionneront pas !!!

Bonne journée.

 

[JHA]

Bonjour à tous,

Pour faire plaisir à Job75 et aux possesseurs de excel en extension ".xls" ci joint la formule modifiée avec la fonction esterreur() au lieu de "sierreur()".

JHA

 

[job75]

Re,

Merci JHA mais pourquoi utiliser ESTERREUR, ceci est bien plus simple :

=SI(LIGNE()>5+$B$2;"";RANG(K6;INDIRECT("$K$6:$K$"&5+$B$2);0)+NB.SI(INDIRECT("$k$6:$k$"&5+MIN(LIGNE()-5;$B$2));K6)-1)

A+

 

[JHA]

Bonjour à tous,

Voilà pourquoi j'ai encore beaucoup à apprendre de vos solutions.

JHA

 

[job75]

Re,

Finalement la solution la meilleure utilise 3 colonnes de résultats avec des formules très simples.

Je la préfère car il n'y a pas de formules volatiles et les calculs sont les plus rapides.

Fichier (3), voyez le post suivant pour les tests.

A+

 

[job75]

Re,

Testez (en cliquant sur le bouton) les 2 fichiers joints de 10 000 lignes.

La solution de JHA est la plus rapide quand B2 < 6350.

A+

 

[job75]

Re,

Pour finir voici une solution VBA, le calcul est très rapide avec cette macro :

Private Sub Worksheet_Change(ByVal Target As Range)
With [B5].CurrentRegion.Resize(, 9)
  If Intersect(Target, Union([B2], .Cells)) Is Nothing Then Exit Sub
  Application.ScreenUpdating = False
  If FilterMode Then ShowAllData 'si la feuille est filtrée
  .Cells(2, 13).Resize(Rows.Count - .Row) = "" 'RAZ
  If Int(Val([B2])) < 1 Then Exit Sub
  With .Cells(2, 1).Resize(Int(Val([B2])))
    .Columns(12) = .Columns(1).Value 'N° en colonne M
    .Columns(13) = .Columns(10).Value 'copie les valeurs de la colonne K en colonne N
    .Columns(12).Resize(, 2).Sort .Columns(13), xlDescending, Header:=xlNo 'tri sur la colonne N
    .Columns(13) = .Columns(1).Value 'N° en colonne N indiquant le classement
    .Columns(12).Resize(, 2).Sort .Columns(12), xlAscending, Header:=xlNo 'tri dans l'ordre initial
    .Columns(12) = "" 'RAZ de la colonne M
  End With
End With
End Sub

On notera qu'il faut 2 tris.

Fichiers joints.

Edit : j'ai ajouté les MFC.

A+

 

[JBARBE]

Re, Job, JHA,
Merci beaucoup à vous deux, j'en demandais pas temps Job mais quand même merci pour la démonstration !
Bonne journée à tous !