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

Signaler
Messages postés
7
Date d'inscription
mardi 7 mars 2017
Statut
Membre
Dernière intervention
9 mars 2017
-
Messages postés
16431
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
9 avril 2021
-
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

13 réponses

Messages postés
16431
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
9 avril 2021
3 161
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/
Messages postés
7
Date d'inscription
mardi 7 mars 2017
Statut
Membre
Dernière intervention
9 mars 2017

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 ?
Messages postés
16431
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
9 avril 2021
3 161
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é
Messages postés
7
Date d'inscription
mardi 7 mars 2017
Statut
Membre
Dernière intervention
9 mars 2017

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
Messages postés
16431
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
9 avril 2021
3 161
Désolé, je suis sur Excel et non sur Ods....
Le langage de prog n'est pas le m^me que VBA
Messages postés
7
Date d'inscription
mardi 7 mars 2017
Statut
Membre
Dernière intervention
9 mars 2017

Désolé, le fichier s'est enregistré sous le mauvais format.
Voici la feuille au format Excel : https://mon-partage.fr/f/QL7BCcMs/
Messages postés
16431
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
9 avril 2021
3 161
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
Messages postés
7
Date d'inscription
mardi 7 mars 2017
Statut
Membre
Dernière intervention
9 mars 2017

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
Messages postés
16431
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
9 avril 2021
3 161
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
Messages postés
7
Date d'inscription
mardi 7 mars 2017
Statut
Membre
Dernière intervention
9 mars 2017

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.
Messages postés
16431
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
9 avril 2021
3 161
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
Messages postés
16431
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
9 avril 2021
3 161
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
Messages postés
7
Date d'inscription
mardi 7 mars 2017
Statut
Membre
Dernière intervention
9 mars 2017

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 :)
Messages postés
16431
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
9 avril 2021
3 161
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 !