Tri en fonction d'un Nb Maxi

J

JHA

XLDnaute Barbatruc

• 14 Avril 2017

• #2

Bonjour à tous,

A essayer en "N6"

Code:

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

Copier vers le bas

Edit: pour les doublons, tu peux essayer cette formule

Code:

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

JHA

 

Dernière édition: 14 Avril 2017

Vote positif 0 Vote négatif

JBARBE

XLDnaute Barbatruc

• 14 Avril 2017

• #3

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

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 14 Avril 2017

• #4

Bonjour JBARBE, JHA,

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

Code:

=(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+

 

Pièces jointes

• Nb_en_fonction_Nb_Maxi(1).xls

  64.5 KB · Affichages: 20

Dernière édition: 15 Avril 2017

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 14 Avril 2017

• #5

Re,

JBARBE à dit:

Bonjour JHA,
Super ça marche !

Cliquez pour agrandir...

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+

 

Vote positif 0 Vote négatif

J

JHA

XLDnaute Barbatruc

• 15 Avril 2017

• #6

Bonjour à tous,

Ci joint un second essai pour les doublons.

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

JHA

 

Pièces jointes

• Nb_en_fonction_Nb_Maxi Rev1.xlsx

  11.9 KB · Affichages: 17

Vote positif 0 Vote négatif

JBARBE

XLDnaute Barbatruc

• 15 Avril 2017

• #7

JHA à dit:

Bonjour à tous,

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

JHA

Cliquez pour agrandir...

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 !

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 15 Avril 2017

• #8

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 :

Code:

=(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 :

Code:

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

Fichier (2).

JBARBE à dit:

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

Cliquez pour agrandir...

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

Bonne journée.

 

Pièces jointes

• Nb_en_fonction_Nb_Maxi(2).xls

  73.5 KB · Affichages: 15

Vote positif 0 Vote négatif

J

JHA

XLDnaute Barbatruc

• 15 Avril 2017

• #9

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

 

Pièces jointes

• Nb_en_fonction_Nb_Maxi Rev2.xls

  31 KB · Affichages: 18

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 15 Avril 2017

• #10

Re,

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

Code:

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

 

Vote positif 0 Vote négatif

J

JHA

XLDnaute Barbatruc

• 15 Avril 2017

• #11

Bonjour à tous,

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

JHA

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 15 Avril 2017

• #12

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+

 

Pièces jointes

• Nb_en_fonction_Nb_Maxi(3).xls

  66.5 KB · Affichages: 16

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 15 Avril 2017

• #13

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+

 

Pièces jointes

• Test JHA(1).xls

  1.8 MB · Affichages: 17
• Test job75(1).xls

  2.8 MB · Affichages: 13

Dernière édition: 15 Avril 2017

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 15 Avril 2017

• #14

Re,

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

Code:

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+

 

Pièces jointes

• Nb_en_fonction_Nb_Maxi par VBA(1).xls

  74.5 KB · Affichages: 17
• Test VBA(1).xls

  1.6 MB · Affichages: 15

Dernière édition: 16 Avril 2017

Vote positif 0 Vote négatif

JBARBE

XLDnaute Barbatruc

• 17 Avril 2017

• #15

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 !

 

Vote positif 0 Vote négatif