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

Question

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
Configuration: Windows / Chrome 56.0.2924.87

michel_m · Answer

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

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

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

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

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

Daiss69 · Answer

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

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

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

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

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

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

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 ExplicitOption Base 1'--------------------------------------------------------------------Sub max_svt_secteur()Dim D_max As ObjectDim Derlig As Integer, T_in, Cptr As IntegerDim 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 WithEnd Sub
maquette de W
https://mon-partage.fr/f/PKbZ43XO/
tu dis...
 Michel

Daiss69 · Answer

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_Discretionaryindex = 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 + 1Next jFor j = 1 To nbConsumer_Staplesindex = 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 + 1Next 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 :)

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

13 réponses

Discussions similaires