Extraire les valeurs plus grands et sa cellule à coté

[puliexcel]

Salut à tous,

j'ai besoin d'extraire un certains nombre de valeurs d'une colonne avec sa cellule à coté.
dans une colonne il y a des string, dans l'autre un integer.
j'ai besoin d'une macro qui puisse trouver les valeurs plus grands (mettons les dix premiers) et de me l'afficher dans une autre feuille, avec son nom.
donc j'aurais une deuxième feuile avec le dis premièrs noms plus important et sa valeurs de reference.

en pièce jointe un file excel

merci d'avance!

 

[gbinforme]

Re : Extraire les valeurs plus grands et sa cellule à coté

Bonjour à tous,

Pour obtenir ton résultat tu n'as pas besoin de programmer car avec deux formules tu obtiens tes valeurs.

 

[mapomme]

Re : Extraire les valeurs plus grands et sa cellule à coté

Bonjour puliexcel,

Un exemple qui mélange formule et VBA (la macro est fondamentalement là pour trier le résultat)

3 noms ont été définis:
nom définition
NOMS =DECALER(DONNÉES!$A$1;1;0;NBVAL(DONNÉES!$A:$A)-1;1)
VALEURS =DECALER(NOMS;0;1)
SEUIL =RESULTATS!$E$1

Pour le choix du seuil d'affichage, une règle de validation a été définie:
La valeur saisie en E1 sur la feuille RESULTATS doit être un entier compris entre 1 et le nombre de valeurs dictinctes. Le nombre de valeurs dictinctes est donné par la formule =SOMMEPROD(1/NB.SI(VALEURS;VALEURS))

La macro se déclenche sur une modification de la valeur de E1. Elle place temporairement des formules matricielles sur les colonnes A et B de la feuille RESULTATS, transforme le résultat des formules en valeur puis trie le résultat sur la colonne 'valeurs' (du plus grand au plus petit).

Les formules matricielles intermédiaires en colonnes A sont de la forme:
=SIERREUR(INDEX(NOMS;PETITE.VALEUR(SI(VALEURS>=GRANDE.VALEUR(SI(FREQUENCE(VALEURS;VALEURS)=0;"";VALEURS);SEUIL);LIGNE(VALEURS)-1);LIGNES($1:1)));"")

idem pour la colonne B en remplaçant NOMS par VALEURS.

nb: si on choisit en E1 la valeur 10 (10 ième plus grande valeur), on aura 14 noms extraits car il y a des ex-aequos.

 

[mapomme]

Re : Extraire les valeurs plus grands et sa cellule à coté

(re),

Une version v2 qui montre comment faire sans VBA ( mais le résultat n'est pas trié) -> voir onglet "Juste avec les formules"

 

[R@chid]

Re : Extraire les valeurs plus grands et sa cellule à coté

Bonsoir @ tous,
Je ne sais pas si j'ai bien compris la demande..
Voir PJ
@ + +

 

[puliexcel]

Re : Extraire les valeurs plus grands et sa cellule à coté

Bonjour à tous,

Pour obtenir ton résultat tu n'as pas besoin de programmer car avec deux formules tu obtiens tes valeurs.

Salut!

merci à tous pour les réponses,

en fait, j'ai besoin d'une macro pour commodité et parce que je dois reproduire ce calcul nombre de fois, su feuille différents. Fin, une macro serait l’idéal. Quelque chose qui me dise de faire cet index que vous avez faits dans vos formules à partir de certains colonne et de les imprimer dans certains colonnes d'une autre feuille.

du genre:
vu la colonne a et b de la feuille X, extrais les première 10 valeurs les plus grands de la colonne b et imprime ces valeurs, avec les valeurs des cellules à coté (situés dans la colonne a), sur les colonnes c et d de la feuille "Y".

merci

 

[mapomme]

Re : Extraire les valeurs plus grands et sa cellule à coté

Bonjour puliexcel,

Un essai en vba.

Le code principal dans module1:

Private Sub Extraire(Source As Range, Vers As Range, ByVal Seuil As Long)
Dim Res As Range, i&

Application.ScreenUpdating = False
Vers.Parent.Range(Vers(1, 1), Vers(1, 1).End(xlDown).Resize(, 2)).Clear
Source.Copy
Vers(1, 1).PasteSpecial xlPasteValues

Set Res = Vers.Parent.Range(Vers(1, 1), Vers(1, 1).End(xlDown).Resize(, 2))
Res.Sort key1:=Res(1, 2), order1:=xlDescending, _
    key2:=Res(1, 1), order2:=xlAscending, Header:=xlYes

For i = 3 To Res.Rows.Count
  If Res(i, 2) <> Res(i - 1, 2) Then Seuil = Seuil - 1
  If Seuil = 0 And i <= Res.Rows.Count Then
    Vers.Parent.Range(Res(i, 1), Vers(Res.Rows.Count, 2)).Clear
    exit for
  End If
Next i

Application.CutCopyMode = False
Application.ScreenUpdating = False
End Sub

 

[mapomme]

Re : Extraire les valeurs plus grands et sa cellule à coté

Bonjour puliexcel,

J'ai dû me tromper quand j'ai affecté la Macro est au bouton. Voici un fichier rectifié (le code est strictement identique à la version v3)

 

[puliexcel]

Re : Extraire les valeurs plus grands et sa cellule à coté

Bonjour puliexcel,

J'ai dû me tromper quand j'ai affecté la Macro est au bouton. Voici un fichier rectifié (le code est strictement identique à la version v3)

c'est parfait! (de toute manière, j'étais capable d'affecter une macro)

c'est vraiment ce que je voulais. je vais adapter le code pour mon travail. j'espère de ne pas avoir d'autres problèmes.

Merci bcp mapomme, c'est excellent! Vraiment merci!

 

[puliexcel]

Re : Extraire les valeurs plus grands et sa cellule à coté

Bonjour puliexcel,

J'ai dû me tromper quand j'ai affecté la Macro est au bouton. Voici un fichier rectifié (le code est strictement identique à la version v3)

En fait, j'ai un dernier problème.

le code est vraiment excellent, par contre, dans mon cas, j'ai besoin qu'une colonne soit définit comme variable. Parce que l’extraction ne doit pas etre fait toujours sur la colonne a et b, mais cela peut changer.
il y a la colonne a comme constante. La colonne a contient les noms.Puis il y a 100 autre colonne à coté qui contiennent des valeurs. moi je veux extraire au fur et a mesure les dix premières noms avec le plus grand valeurs, mais je dois pouvoir choisir quelle colonne confronter, si la b ou la k, ou la l, etc.

j'ai essayé ceci. j'ai la feuille résultat comme variable, elle est la page active, et la cellule k2 variable de la colonne que je veux extraire(ensemble à la colonne). ceci en bleu.

en rouge, par contre, il y a mon problème. comment je peux sélectionner deux colonne, dont une est une variable?


Option Explicit

Sub test2()
Dim x As Integer
x = Range("k2")
Dim sh As Worksheet
Set sh = ActiveSheet

'Extraire Sheets("DONNÉES").Range("a1:b9000"), Sheets("DONNÉES").Range("g3"), 8
Extraire Sheets("DONNÉES").Columns(1,x), sh.Range("f1"), 10
Application.Goto Sheets("DONNÉES").Range("a1"), True
End Sub

Private Sub Extraire(Source As Range, Vers As Range, ByVal Seuil As Long)
etc,etc,etc



merci!

 

[puliexcel]

Re : Extraire les valeurs plus grands et sa cellule à coté

en fait, si j'ai fais ceci, donc la recherche sur la colonne x (qui est une variable), le code fonctionne, mais evidement il ne m'affiche que les valeurs, sans le noms correspondants qui se situé dans la colonne a [qui devrait être indique, dans le code, comme .columns(1) ]

Option Explicit

Sub test2()
Dim x As Integer
x = Range("k2")
Dim sh As Worksheet
Set sh = ActiveSheet

'Extraire Sheets("DONNÉES").Range("a1:b9000"), Sheets("DONNÉES").Range("g3"), 8
Extraire Sheets("DONNÉES").Columns(x), sh.Range("f1"), 10
Application.Goto Sheets("DONNÉES").Range("a1"), True
End Sub

 

[mapomme]

Re : Extraire les valeurs plus grands et sa cellule à coté

Bonsoir puliexcel,

Un essai dans le fichier joint. En fait très peu de modifications. Au lieu de copier la source en une seule fois, on copie successivement la zone Source (qui correspondra à celle des noms) puis on copie la zone correspondante des valeurs (repéré grâce au nouveau paramètre ColValeur). Ensuite le code est inchangé.

L'appel se fait désormais avec 4 paramètres:
Sub Extraire(Source As Range, ColValeur As Range, Vers As Range, ByVal Seuil As Long)

Exemple:
Extraire Sheets("DONNÉES").Range("a1:b24"), Sheets("DONNÉES").Range("f3"), Sheets("RESULTATS").Range("b2"), 10
où:

1. Source est un range qui contient les noms en 1ière colonne (avec la ligne de titre) - (seule la première colonne est prise en compte, on aurait pu mettre a1:a24 ou bien a1:t24)
2. ColValeur est n'importe quel range dont la 1ière colonne correspond à la colonne des valeurs (seule la colonne de début importe, on aurait pu mettre f1:f24 ou bien f999 )
3. Vers est la cellule de destination (coin sup. gauche) - (seule la première cellule de la zone est prise en compte)
4. SEUIL correspond à la SEUIL ième valeur plus grande de la zone des valeurs (on ne compte pas les doublons)

La partie de code modifiée (en-tête mis à part):
Avant:

Source.Copy
Vers(1, 1).PasteSpecial xlPasteValues

Après:

Source.Columns(1).Copy
Vers(1, 1).PasteSpecial xlPasteValues
Source.Columns(1).Offset(, ColValeur.Column - Source.Column).Copy
Vers(1, 2).PasteSpecial xlPasteValues

 

[puliexcel]

Re : Extraire les valeurs plus grands et sa cellule à coté

Bonsoir puliexcel,

Un essai dans le fichier joint. En fait très peu de modifications. Au lieu de copier la source en une seule fois, on copie successivement la zone Source (qui correspondra à celle des noms) puis on copie la zone correspondante des valeurs (repéré grâce au nouveau paramètre ColValeur). Ensuite le code est inchangé.

L'appel se fait désormais avec 4 paramètres:
Sub Extraire(Source As Range, ColValeur As Range, Vers As Range, ByVal Seuil As Long)

Exemple:
Extraire Sheets("DONNÉES").Range("a1:b24"), Sheets("DONNÉES").Range("f3"), Sheets("RESULTATS").Range("b2"), 10
où:

1. Source est un range qui contient les noms en 1ière colonne (avec la ligne de titre) - (seule la première colonne est prise en compte, on aurait pu mettre a1:a24 ou bien a1:t24)
2. ColValeur est n'importe quel range dont la 1ière colonne correspond à la colonne des valeurs (seule la colonne de début importe, on aurait pu mettre f1:f24 ou bien f999 )
3. Vers est la cellule de destination (coin sup. gauche) - (seule la première cellule de la zone est prise en compte)
4. SEUIL correspond à la SEUIL ième valeur plus grande de la zone des valeurs (on ne compte pas les doublons)

La partie de code modifiée (en-tête mis à part):
Avant:

Source.Copy
Vers(1, 1).PasteSpecial xlPasteValues

Après:

Source.Columns(1).Copy
Vers(1, 1).PasteSpecial xlPasteValues
Source.Columns(1).Offset(, ColValeur.Column - Source.Column).Copy
Vers(1, 2).PasteSpecial xlPasteValues

Grand travail, merci bcp.
Vous êtes vraiment bien. Surtout que l'explication me permet de mieux comprendre comment cela marche.
vraiment un gros merci.

par contre je me suis mal expliqué.
donc mon problème reste.
sauf si, j'ai mal compris la réponse.

dans le fond, ma colonne je dois la décider chaque fois à partir de ma feuille.
donc si le code présentement me copie la colonne d (pour la feuille donnés) et f (pour la feuille résultats),
dans mon cas j'aimerai que cela ne soit pas décidé d'emblée par le code, mais bien par l'usager à caque fois que la macro s’exécute.

Avec mon imagination le code serait quelque chose du genre. il y a des explication plus claires de
ce que je cherche dans la pièce jointe.

code original

Sub test()
  Extraire Sheets("DONNÉES").Range("a1:b24"), Sheets("DONNÉES").Range("d3"), Sheets("DONNÉES").Range("h12"), 5
  Extraire Sheets("DONNÉES").Range("a1:a24"), Sheets("DONNÉES").Range("f3"), Sheets("RESULTATS").Range("b2"), 10
  Application.Goto Sheets("DONNÉES").Range("a1"), True
End Sub

code imaginé par moi avec mes très peu notion.

Sub test()

Dim feuilleactive As Worksheet
Set feuilleactive = ActiveSheet


Extraire Sheets("DONNÉES").Range("a1:b24"), Sheets("DONNÉES").Rangecherche la colonne à confronter dans la cellule A2 de la feuille à partir de la quelle la macro a été executé, c'est à dire cherche la colonne a confronter dans la cellule A2 de feuilleactive , feuileactive.Range("h12"), 5
feuilleactive.Range("b2"), 10
Application.Goto Sheets("DONNÉES").Range("a1"), True
End Sub

 

[mapomme]

Re : Extraire les valeurs plus grands et sa cellule à coté

Bonjour puliexcel,

Une version avec une adaptation de la macro Test() renommée en MacroCopie(). C'est dans cette dernière macro que sont définis:
La source (liste des noms), la colonne des valeurs (par lecture de la cellule A2 de la feuille courante), la cellule de destination au sein de la feuille active et le Seuil (Nième plus grande valeur).

Pour lancer la macro:
Se positionner sur la feuille où on veut extraire les données.
Tapez les touches de raccourci: Ctrl+k.

Voir exemple sur les feuilles Resultats et Resultats2.

Le code de MacroCopie():

Sub MacroCopie()
' se placer sur la feuille du résultat
' tapez le raccourci: CTRL+k

Dim FeuilRes As Worksheet, FeuilSource As Worksheet, Masource As Range
Dim Macolonne As Range, MaDestination As Range, MonSeuil&

  Set FeuilRes = ActiveSheet                  'feuille résultat
  Set FeuilSource = Sheets("DONNÉES")         'feuille source
  Set Masource = FeuilSource.Range("a1:a24")     'Source (zone des noms)
  If IsNumeric(FeuilRes.Range("A2").Value) Then
    'si colonne est un nombre
    Set Macolonne = Range("A1").Offset(, FeuilRes.Range("A2") - 1)
  Else
    'sinon on suppose que colonne est désignée par des lettres
    Set Macolonne = Range(Trim(FeuilRes.Range("A2").Value) & 1)
  End If
  Set MaDestination = FeuilRes.Range("b12")   'cellule résultat
  MonSeuil = 8    'quelle Nième plus grande valeur ?
  
  'lancement de l'extraction
  Extraire Masource, Macolonne, MaDestination, MonSeuil
  Application.Goto FeuilRes.Range("a1"), True
End Sub

 

[puliexcel]

Re : Extraire les valeurs plus grands et sa cellule à coté

Bonjour puliexcel,

Une version avec une adaptation de la macro Test() renommée en MacroCopie(). C'est dans cette dernière macro que sont définis:
La source (liste des noms), la colonne des valeurs (par lecture de la cellule A2 de la feuille courante), la cellule de destination au sein de la feuille active et le Seuil (Nième plus grande valeur).

Pour lancer la macro:
Se positionner sur la feuille où on veut extraire les données.
Tapez les touches de raccourci: Ctrl+k.

Voir exemple sur les feuilles Resultats et Resultats2.

Le code de MacroCopie():

Sub MacroCopie()
' se placer sur la feuille du résultat
' tapez le raccourci: CTRL+k

Dim FeuilRes As Worksheet, FeuilSource As Worksheet, Masource As Range
Dim Macolonne As Range, MaDestination As Range, MonSeuil&

  Set FeuilRes = ActiveSheet                  'feuille résultat
  Set FeuilSource = Sheets("DONNÉES")         'feuille source
  Set Masource = FeuilSource.Range("a1:a24")     'Source (zone des noms)
  If IsNumeric(FeuilRes.Range("A2").Value) Then
    'si colonne est un nombre
    Set Macolonne = Range("A1").Offset(, FeuilRes.Range("A2") - 1)
  Else
    'sinon on suppose que colonne est désignée par des lettres
    Set Macolonne = Range(Trim(FeuilRes.Range("A2").Value) & 1)
  End If
  Set MaDestination = FeuilRes.Range("b12")   'cellule résultat
  MonSeuil = 8    'quelle Nième plus grande valeur ?
  
  'lancement de l'extraction
  Extraire Masource, Macolonne, MaDestination, MonSeuil
  Application.Goto FeuilRes.Range("a1"), True
End Sub

c'est excellent!

ça marche super bien, c'est tout ce que je cherchais.

Ma pomme, je vous remercie bcp. Gros travail, merci!

les explication supplémentaires du code sont super appréciés!

Bonne journée!