XL 2016 Macro VBA pour appliquer un rang selon des filtres

[M_lisa]

Bonjour,

je dispose d'une base de données avec différents indicateurs (19 au total) pour chaque établissement (entre 238 et 241 selon les indicateurs) répartis sur 8 départements. Donc 13000 lignes.
J'aurai besoin, pour chaque indicateur, d'indiquer le rang départemental de chaque établissement selon la valeur de l'indicateur.
Pour une meilleure compréhension je vous joints un fichier d'exemple, avec un onglet avec les données originales "data" et un onglet "attendu" avec le résultat attendu.

Si quelqu'un a une solution, je suis preneuse
A la main via excel le travail est plutôt fastidieux (filtre 19 * 8 fois et fonction rang).

Merci beaucoup et bonne journée !

 

[vgendron]

Bonjour

il manque des explications. parce que la, je ne comprend pas comment tu es passé des données d'origine à l'attendu...
il y a un tri??? sur quel(s) critères..??

C'est quoi les indicateurs ?

 

[M_lisa]

Bonjour,

merci pour le retour rapide ! Désolée si la demande n'était pas claire.
Sur l'attendu, la chose en plus c'est le rang de l'établissement (colonne UAI) selon chaque indicateur par rapport aux autres établissements du département.
Dans mon fichier d'exemple j'ai mis deux indicateurs (colonne nom variable) qui sont "IPS_TOTAL_Etab" et "IPS_STD_TOTAL_Etab". Et j'ai 6 établissement (colonne UAI) qui sont répartis sur deux départements (colonne dep).
Par exemple pour le département 031, il y a trois établissements (0310000X, 0311111X et 0312222X). Je veux avoir pour chaque établissement son rang départemental pour chaque indicateur. Pour le département du 031, indicateur IPS_TOTAL_Etab, l'établissement qui a la valeur la plus important est classée 1, puis le second 2 et le dernier avec la valeur la moins importante 3. (fonction rang sur excel).
Sans macro, je filtre d'abord sur l'indicateur, puis sur le département, puis j'utilise la fonction rang (et avant tous ça je dois aussi trier mes données).

J'espère que c'est plus clair ?
Merci !

 

[vgendron]

ok, vu pour les tris..
mais.. qu'est ce qui prend du temps?? mettre le rang??
une simple formule fait le travail
en E2 = 1
puis en E3= SI(ET(B3=B2;C3=C2);E2+1;1)

et tu tires la formule jusqu'en bas

 

[M_lisa]

ce qui me prend du temps c'est de faire les filtres, dans mon tableau original j'ai 19 indicateurs différents et 8 départements, donc du coup je dois filtrer 19*8 fois soit 152 fois...
filtre sur le premier indicateur, puis sur le premier département, puis fonction rang
puis filtre sur le second département, puis fonction rang
puis filtre sur le troisième département, puis fonction rang
etc pour les 8
Puis filtre sur le second indicateur, et premier département puis fonction rang
filtre sur le second département puis fonction rang
etc

ça te semble clair ?

 

[vgendron]

plutot qu'un filtre, fais un TRI en ajoutant les 3 niveaux (col C puis B puis D)

 

[Cousinhub]

Bonjour,
Une solution Power Query, en natif sur ta version 2016
Si c'est le résultat escompté..
PS, bonjour @vgendron

 

[M_lisa]

Merci oui j'avais pensé à cette solution mais elle ne répond pas à mon besoin sur deux points
- pour les cas ou plusieurs établissements ont la même valeur sur l'indicateur, en utilisant cette méthode ses établissements ne sont pas au même rang (mais peut-être que je peux arranger ce point avec une formule plus complexe en ajoutant des si)
- certains de mes rangs doivent être calculer sur des valeurs d'indicateurs croissants et d'autres décroissants (dans certains cas avoir la plus grand valeur donne un classement 1 et dans certains cas 3)
C'est pour ces deux raisons que j'utilise la fonction rang

 

[M_lisa]

Bonjour,
Une solution Power Query, en natif sur ta version 2016
Si c'est le résultat escompté..
PS, bonjour @vgendron

Bonjour,

merci ta réponse !
je ne connais pas Power Query, je vais regarder ça =)

 

[Cousinhub]

Re-,
Si tu ne nous donnes pas toutes les données d'entrée, ça ne va pas être simple...
- Pour les égalités, on peut utiliser une autre méthode, mais dans ce cas, si 1, 2, 2 le suivant, c'est 3 ou 4?
- Pour les valeurs croissantes ou décroissantes, il faut un tableau de critères, donnant le sens voulu...
Car ma solution ne traite pas ces nouvelles données

 

[vgendron]

Je ne comprend pas ce qui ne va pas..
avec la macro qui applique les tris à ta place, ca donne les rangs attendus

voir le code que j'ai commenté pour expliquer

hello @bhbh

 

[M_lisa]

Re-,
Si tu ne nous donnes pas toutes les données d'entrée, ça ne va pas être simple...
- Pour les égalités, on peut utiliser une autre méthode, mais dans ce cas, si 1, 2, 2 le suivant, c'est 3 ou 4?
- Pour les valeurs croissantes ou décroissantes, il faut un tableau de critères, donnant le sens voulu...
Car ma solution ne traite pas ces nouvelles données

Re,
Désolée, c'est vrai que j'avais juste précisé que le travail à la main filtre et fonction rang était trop long, j'aurai du expliciter pourquoi je voulais utiliser la fonction rang.
Je cherchais comment faire des boucles via VBA pour appliquer les filtres et les rangs.
Mais je vais voir si j'arrive à gérer les égalités, et pour les valeurs croissantes ou décroissantes je pourrais diviser ma base en deux.
merci

 

[Cousinhub]

Re-,
Toujours avec Power Query...
J'ai rajouté quelques lignes avec de nouveaux indicateurs.
Dans l'onglet "Crit", j'ai mis les indicateurs devant être classés par ordre croissant ou décroissant.
Il te faut juste remplir ce tableau.
Dans l'onglet "data", la requête finale, qui prend en compte les critères.
En cas d'égalité, j'ai appliqué : 1,1,3 (on peut modifier en 1,1,2 si tu veux)
Regarde si cela correspond un peu plus à tes besoins...

 

[Cousinhub]

Bonsoir,
J'ai bien noté que vous aviez mis ma proposition comme "Solution"..
Cependant, il m'aurait été aussi agréable d'avoir votre retour d'expérience sur cette "Solution", étant moi-même novice dans cet univers..
Et comme le précise, et la charte, et ma signature, un "merci" ne coûte rien...

 

[M_lisa]

Bonsoir,
J'ai bien noté que vous aviez mis ma proposition comme "Solution"..
Cependant, il m'aurait été aussi agréable d'avoir votre retour d'expérience sur cette "Solution", étant moi-même novice dans cet univers..
Et comme le précise, et la charte, et ma signature, un "merci" ne coûte rien...

Bonsoir,
J’ai noté comme solution pour que ça puisse aider d’autres utilisateur-ices et pour ne pas laisser le fil ouvert comme indiqué dans la charte. Je n’ai pour l’instant pas eu le temps de regarder votre solution car comme indiqué je ne connais pas du tout Power Query. J’ai retenu pour ma part la solution d’utiliser des formules et de séparer ma base en deux comme indiqué dans mon dernier message.
De plus Il me semble que je vous ai remercié à plusieurs reprises sur ce fil. Et j’avais déjà trouvé un de vos messages passif-agressif … malgré ça je n’ai pas fait de remarque et je me suis en plus excusée. Mais pour ce deuxième c’est trop, être derrière un écran ne vous donne pas tous les droits. Je pense que je ne suis pas à ma place sur ce forum, vraisemblablement.
Bonne soirée, et merci surtout.