[VBA] Maximum d'une colonne indexé sur une autre colonne [Fermé]

Question

[VBA] Maximum d'une colonne indexé sur une autre colonne [Fermé]

Signaler

Daiss69

Messages postés: 7
Date d'inscription: mardi 7 mars 2017
Statut: Membre
Dernière intervention: 9 mars 2017

- 7 mars 2017 à 00:25
michel_m

Messages postés: 16431
Date d'inscription: lundi 12 septembre 2005
Statut: Contributeur
Dernière intervention: 9 avril 2021

- 10 mars 2017 à 08:58

Bonjour,

Je ne sais pas si le titre est très clair (j'ai fait au mieux) je vais donc préciser un peu, j'ai des données de ce type :

id Secteur Note
145 Finance 0,2
454 Soins médicaux 3
462 Industrie 2,1
774 Finance 0.6
682 Finance -1,3
758 Industrie 1,7
285 Soins médicaux 1,5
868 Soins médicaux -0,3

Toute les lignes sont rangées "au hasard", il n'y a pas particulièrement d'ordre dans les secteurs.
Mon objectif est de prendre le maximum des notes, mais sur chaque secteur, et de sélectionner la ligne entière. Dans cette exemple, je voudrais que le résultat soit :
774 Finance 0.6
462 Industrie 2,1
454 Soins médicaux 3

J'utilise donc la fonction max, mais je ne vois pas comment ajouter cette condition par rapport à la colonne "Secteur", est-ce que quelqu'un aurait une idée?

Merci

A voir également:

[VBA] Maximum d'une colonne indexé sur une autre colonne
Vba chercher dans une colonne ✓ - Forum - VB / VBA
Méthode find dans VBA - Recherche de données sous Excel - Conseils pratiques - Visual Basic
Vba listbox additem plusieurs colonnes ✓ - Forum - VB / VBA
ListBox a 2 colonnes ✓ - Forum - Excel
Vba afficher toutes les colonnes ✓ - Forum - Excel

13 réponses

Posez votre question

michel_m · Answer 1 · 2017-03-07T08:42:18+0100

Réponse 1 / 13

michel_m

Messages postés: 16431
Date d'inscription: lundi 12 septembre 2005
Statut: Contributeur
Dernière intervention: 9 avril 2021

3 161
7 mars 2017 à 08:42

Bonjour,
Apparemment, cela pourrait s'effectuer sans VBA avec un tableau croisé dynamique...
au besoin tuto sur les TCD
https://silkyroad.developpez.com/excel/tcd/

Daiss69 · Answer 2 · 2017-03-07T09:59:48+0100

Réponse 2 / 13

Daiss69

Messages postés: 7
Date d'inscription: mardi 7 mars 2017
Statut: Membre
Dernière intervention: 9 mars 2017

7 mars 2017 à 09:59

Bonjour,
Merci pour votre réponse. Le TCD est une bonne idée mais seulement j'ai besoin d'automatiser cette tâche et de la reproduire des dizaines de fois dans mon programme ( j'ai des données pour plusieurs dates différentes ). C'est pour cela que je pense être obligé de passer par VBA.

N'est il pas possible de faire une commande du type :
Max(Range("Note")) Where Range("Secteur")=""Finance" par exemple ?

michel_m · Answer 3 · 2017-03-07T12:02:58+0100

on peut passer par du VBA bien sûr, mais il faut plus de renseignements pour pouvoir proposer une réponse

combien de lignes environ (beaucoup ne veut rien dire) ?

quelles colonnes ?
où restituer les résultats ?
j'ai des données pour plusieurs dates différentes c'est à dire ?

le mieux est de

Mettre le classeur sans données confidentielles en pièce jointe sur « mon-partage.fr »
et faire un clic droit-coller le raccourci  dans votre message

Dans l’attente

ou un exemple reflétant exactement la réalité

Daiss69 · Answer 4 · 2017-03-07T13:09:17+0100

Voici un exemple de fichier utilisé : https://mon-partage.fr/f/6dtU3uy9/

Je dispose de 650 lignes, correspondant à 650 entreprises. Je travail avec des données mensuelles sur une période de 10 ans.

A chaque itération, on passe au mois suivant et on actualise donc les secteurs d'activités et les notes attribuées aux entreprises, seule la première colonne avec les ID n'est pas modifiée.

Le but est donc, de sélectionner les notes maximales sur chaque secteur, et de les copier dans une seconde feuille, ou j'effectue ensuite d'autre traitements.

Cette manipulation est répétée pour tous les "mois" dont je dispose

Merci de votre aide

michel_m · Answer 5 · 2017-03-07T14:00:06+0100

Réponse 5 / 13

michel_m

Messages postés: 16431
Date d'inscription: lundi 12 septembre 2005
Statut: Contributeur
Dernière intervention: 9 avril 2021

3 161
7 mars 2017 à 14:00

Désolé, je suis sur Excel et non sur Ods....
Le langage de prog n'est pas le m^me que VBA

Daiss69 · Answer 6 · 2017-03-07T14:09:47+0100

Réponse 6 / 13

Daiss69

Messages postés: 7
Date d'inscription: mardi 7 mars 2017
Statut: Membre
Dernière intervention: 9 mars 2017

7 mars 2017 à 14:09

Désolé, le fichier s'est enregistré sous le mauvais format.
Voici la feuille au format Excel : https://mon-partage.fr/f/QL7BCcMs/

michel_m · Answer 7 · 2017-03-07T16:44:56+0100

Réponse 7 / 13

michel_m

Messages postés: 16431
Date d'inscription: lundi 12 septembre 2005
Statut: Contributeur
Dernière intervention: 9 avril 2021

3 161
7 mars 2017 à 16:44

Bien reçu

on cherche le maxi suivant l'id (colonne A) ou suivant le secteur(colonne B) ?

en feuille 2: 3 colonnes? id, secteur, Max ?

je comprend pas le traitement de la date

Daiss69 · Answer 8 · 2017-03-07T18:03:25+0100

Le maximum est a trouvé suivant le secteur.

On dispose de 3 colonnes : id, secteur, note

On cherche donc la note maximale sur chaque secteur.

Pour ce qui est du traitement de la date, il vaut mieux ne pas s'en occuper pour le moment. Faisons comme si nous n'avions que cette feuille et je pourrais ensuite adapter le code correspondant dans une boucle For.

On cherche donc à sélectionner à l'aide de VBA, pour chaque secteur, la note maximale obtenue, et copier les lignes correspondantes (10 lignes à copier car il y a 10 secteur différents) sur une feuille annexe

michel_m · Answer 9 · 2017-03-08T12:02:34+0100

Réponse 9 / 13

michel_m

Messages postés: 16431
Date d'inscription: lundi 12 septembre 2005
Statut: Contributeur
Dernière intervention: 9 avril 2021

3 161
Modifié par michel_m le 8/03/2017 à 12:05

Bonjour

BRAVO POUR AVOIR MIS DES NOMBRES ALEATOIRES COMME IDENTIFIANTS---> MEMES IDENTIFIANTS POUR SECTEURS DIFFERENTS IMPOSSIBLE DE TESTER DES PISTES DE TRAVAIL PAR FORMULES OU VBA !!!
J'AI PASSE 2 HEURES A DEMASQUER CETTE ABSURDITE AVEC CET EXEMPLE LIMITE FOUTAGE DE GUEULE
:((

proposition:
https://mon-partage.fr/f/Il5NqxaU/
Michel

Daiss69 · Answer 10 · 2017-03-08T17:16:58+0100

Bonjour,

Je suis vraiment désolé pour cette erreur, étant donné qu'il n'y a pas vraiment de calcul sur les identifiants, seulement du recopiage, je ne pensais pas que des id identiques poseraient de tels problèmes.

Je vous remercie pour cette proposition qui correspond exactement à ce que je cherchais à obtenir. Je ne connaissais pas l'utilisation des objets dictionnaire qui à l'air d'être très pratique.

Une dernier petite question si je peux me permettre, pensez vous que, avec ce type d'objet, il est possible de sélectionner et recopier non plus seulement la note maximal pour chaque secteur mais les 5 plus grandes valeurs ? Cela ferait apparaître plusieurs fois un même secteur dans le dictionnaire et je ne sais donc pas si cela serait faisable.

Encore merci pour le temps passé sur mon problème.

michel_m · Answer 11 · 2017-03-09T11:14:00+0100

Réponse 11 / 13

michel_m

Messages postés: 16431
Date d'inscription: lundi 12 septembre 2005
Statut: Contributeur
Dernière intervention: 9 avril 2021

3 161
9 mars 2017 à 11:14

Bonjour

Le plus simple serait d'ajouter autant de dictionary que de colonnes supplémentaires: je te laisse faire

Je teste une méthode que je viens de cogiter avec un seul dico et des variables-tableaux avec en item une var-tableau de N colonnes et une var-tableau T_out en restitution mais c'est assez abstrait
L'estimation de cette méthode: demain car j'ai pas mal de trucs à faire

michel_m · Answer 12 · 2017-03-09T15:16:12+0100

Quand des problèmes Vba me prennent la tête , pas moyen de quitter la bécane, histoire de me fatiguer encore un peu les yeux !!!!

proposition avec un seul dico et variables tableaux pour 4 colonnes(il est facile de mettre plus de colonnes (dimension de T_in et de Col)

Option Explicit
Option Base 1
'--------------------------------------------------------------------
Sub max_svt_secteur()
Dim D_max As Object
Dim Derlig As Integer, T_in, Cptr As Integer
Dim T_out, Lig As Integer, Lig_x As Integer, Col As Byte

''-------------initialisations
    Set D_max = CreateObject("scripting.dictionary")
    ReDim T_out(4, 1)
    With Sheets(1)
        'mémorisation RAM des données
        Derlig = .Columns("A").Find(what:="*", searchdirection:=xlPrevious).Row
        T_in = .Range("A2:D" & Derlig)
     End With
     'nettoyage restitution antérieure
     Sheets(2).Range("A2:C1000").Clear

'------------------mise en ordre id , secteur note maxi
        For Cptr = 1 To UBound(T_in)
            If Not D_max.exists(T_in(Cptr, 2)) Then
                'création du dictionnary clé=secteur, item =index du tablo de données
                Lig = Lig + 1
                ReDim Preserve T_out(4, Lig) 'tableau dynamique de restitition
                D_max.Add T_in(Cptr, 2), Lig  't_in(cptr,2)=secteur en cours, 1° ligne d'apparition
                For Col = 1 To 4
                    'affectation  tablo entrée vers tablo restitution
                    T_out(Col, Lig) = T_in(Cptr, Col)
                Next
            Else
                'si Note >  --->modif données
                Lig_x = D_max.Item(T_in(Cptr, 2)) 'rappel 1° ligne d'apparition du secteur
                If T_in(Cptr, 3) > T_out(3, Lig_x) Then
                    For Col = 1 To 4
                        T_out(Col, Lig_x) = T_in(Cptr, Col)
                   Next
                End If
           End If
        Next

'------------------restitution
    With Sheets(2)
        .Range("A2").Resize(D_max.Count, 4) = Application.Transpose(T_out)
        .Activate
    End With
End Sub

maquette de W
https://mon-partage.fr/f/PKbZ43XO/
tu dis...
Michel

Daiss69 · Answer 13 · 2017-03-09T15:23:51+0100

Réponse 13 / 13

Daiss69

Messages postés: 7
Date d'inscription: mardi 7 mars 2017
Statut: Membre
Dernière intervention: 9 mars 2017

9 mars 2017 à 15:23

Bonjour,

Merci pour votre proposition, entre temps je suis parti sur des boucles for et des match(large(...)) de ce type :

For j = 1 To nbConsumer_Discretionary
index = Evaluate("=MATCH(LARGE(IF(b2:b651=h3,c2:c651)," & j & "),c2:c651,0)")
refCellOptim.Offset(compteur, 0) = refCellSelec.Offset(index, 0)
refCellOptim.Offset(compteur, 1) = refCellSelec.Offset(index, 1)
refCellOptim.Offset(compteur, 2) = refCellSelec.Offset(index, 2)
compteur = compteur + 1
Next j
For j = 1 To nbConsumer_Staples
index = Evaluate("=MATCH(LARGE(IF(b2:b651=h4,c2:c651)," & j & "),c2:c651,0)")
refCellOptim.Offset(compteur, 0) = refCellSelec.Offset(index, 0)
refCellOptim.Offset(compteur, 1) = refCellSelec.Offset(index, 1)
refCellOptim.Offset(compteur, 2) = refCellSelec.Offset(index, 2)
compteur = compteur + 1
Next j

Cela fonctionne très bien mais je testerai votre méthode qui je pense est plus performante en temps de calculs !

Un grand merci pour tous ces efforts :)

Signaler

michel_m

Messages postés: 16431
Date d'inscription: lundi 12 septembre 2005
Statut: Contributeur
Dernière intervention: 9 avril 2021

3 161
Modifié par michel_m le 10/03/2017 à 09:57

Bonjour

Question rapidité, l'utilisation est efficace à partir de plusieurs milliers de lignes (2000,3000?) et les fonctions matricielles (evaluate) sont alors chronophages
Sur maquette avec env.700 lignes pas de pb avec la matricielle qui m'a servi de test

Edit: 9:55h
si il n'y a que 700 lignes, on peut résoudre par formules !

[VBA] Maximum d'une colonne indexé sur une autre colonne [Fermé]

13 réponses

Votre réponse

Newsletter