Microsoft 365 VBA code filtre auto en boucle - plusieurs critères

[tnion]

Bonjour à tous,

Je fais appel à vous car je souhaite simplifier un fichier et automatiser des filtres multiples afin de ne plus à faire à la main. Je sais faire des filtres en VBA, mais vu le nombre, je souhaite le lancer en boucle dès que j'ajoute des nouvelles données, et là c'est la limite de mon savoir faire!

J'ai des lots de production qui sont fabriqué dans plusieurs usines (colonne C - 8 options), avec des types de productions diff (colonne B - jusqu'à 27 options) pour diff dates (2017 --> aujourd'hui). Ils ont tous une valeur IP qui est un indicateur de profitabilité.

L'objectif des filtres automatique sera de déterminer le 66% meilleure selon la valeur IP (colonne D) de chaque prod, de chaque usine et de chaque trimestre (date - colonne I). Après l'application de chaque enchainement de filtres (ex. date 22-T4, usine 1, prod 1), je souhaiterais indiquer si le lot est dans les 66% meilleure dans la colonne E (1 = oui, vide = non).

Filtre #1: Date
Filtre # 2: Usine
Filtre #3: Classe de prod
Filtre #4: trier par plus grand au plus petit sur la colonne D
Indiquer par un 1 dans la colonne E les 66% meilleures (pas toujours le même nombre de lots/prod/usine/date, donc il faut déterminer combien de lots à prendre en compte chaque fois pour les 66% meilleures)

Et de faire ça en boucle afin d'avoir les 66% meilleures par IP pour chaque trimestre / Prod / Usine.

Je vous ai mis un fichier simplifié de mes données.

Tout aide ou conseil sera grandement apprécié!
Bien cordialement,

Tnion

 

[Phil69970]

Bonjour @tnion

J'ai pas compris le résultat attendu merci de mettre pour l'exemple que tu as donnée le résultat que tu attends dans la colonne E (ou une autre ) car j'avoue c'est pas trop parlant pour moi ton explication.....

@Phil69970

 

[tnion]

Bonjour @Phil69970,

Merci pour votre réponse, et mes excuses que l'attendu n'est pas trop claire Je vais essayé

J'ai fait un exemple dans le fichier, où je fait les actions dans l'ordre suivant:
1) trier par date (année - trimestre) colonne I
2) trier par usine - colonne C
3) trier par classe de prod (1 seul dans l'usine 1) - colonne B
4) trier l'IP du plus grand au plus petit (colonne D)
5) déterminer le nombre de lots restant après les tries, et le nombre de lots à prendre en compte pour le top 66% (qui peut varier en fonction de la classe de prod) afin de noter "1" dans la colonne E pour les top 66% des lots sur la valeur IP (dans l'exemple, il y a 14 lots restants suite aux tries de la classe prod 1, dans l'usine 1 pour la période du 19-T3. Les 66% meilleurs = 9 lots / 14 lots, et donc j'ai un "1" noté pour les 9 meilleures, qui correspond à 66%)

Et il faut que j'arrive à faire pour plusieurs usines, classes de prod et de périodes, car la base de données sera actualisé tous les trimestres.

J'espère que l'attendu est plus compréhensible et je vous remercie d'avance de tout aide.

Tnion

 

[Phil69970]

@tnion


Encore quelques précision :

je souhaite le lancer en boucle dès que j'ajoute des nouvelles données,

1) A chaque ajout de données il faut donc effacer la colonne E et recommencer ?

2) Tu rajoutes des données
Tous les trimestres, tous les mois, tous les jours ou bien 1 ou 10 ou x fois par jour ?

3) 66 % de 14 ==> 9.24 tu arrondi au plus prés je pense

4) Combien d'usine au total colonne C ==> 2 comme dans l'exemple ?
Combien de classe de production au total colonne colonne B ==> 4 comme dans l'exemple ?
Pour les trimestres je sais il y en a 4 par an

Si j'ai d'autres questions on verra plus tard.....

@Phil69970

 

[tnion]

@tnion


Encore quelques précision :


1) A chaque ajout de données il faut donc effacer la colonne E et recommencer ?

2) Tu rajoutes des données
Tous les trimestres, tous les mois, tous les jours ou bien 1 ou 10 ou x fois par jour ?

3) 66 % de 14 ==> 9.24 tu arrondi au plus prés je pense

4) Combien d'usine au total colonne C ==> 2 comme dans l'exemple ?
Combien de classe de production au total colonne colonne B ==> 4 comme dans l'exemple ?
Pour les trimestres je sais il y en a 4 par an

Si j'ai d'autres questions on verra plus tard.....

@Phil69970

Merci toujours! Voici les réponses

1 + 2) Nous allons ajouter des données tous les trimestres, et vu que je cherche les top 66% / trimestre, normalement nous n'avons pas à supprimer la colonne E et recommencer.

3) Oui, nous allons arrondi au plus prés

4) Aujourd'hui j'ai 8 usines, 27 classes de prod diff et mes données commence début 2017 jusqu'à fin 2022. Il y a la possibilité que nous pouvons créer d'autres classes de prod, ou d'affecter une classe de prod existant à une autre usine, donc les nombres ne sont pas figé (juste pour faciliter la tâche )

A la fin, je souhaite mettre en place des TCD avec des segments où nous pouvons sélectionner ou non les top 66% par l'IP afin de regarder les autres critères de productivité (qui ne sont pas dans le fichier d'exemple).

Je vous remercie beaucoup d'avance!
Tnion

 

[Cousinhub]

Bonjour,
Bonjour @Phil69970
En attendant la solution de Phil, voici une solution via Power Query (en natif dans ta version), et sans VBA (mais on peut en rajouter, pour automatiser, s'il le faut)
Dans le fichier joint, à droite de ton Tableau (transformé en Tableau Structuré, nommé "T_Data"), le résultat de la requête. Pour arriver à ce résultat :
- Regrouper par Date (aa-tt), Usine et Class Prod, en calculant le nombre de chaque groupe ( multiplié par 2/3 arrondi, pour avoir les 66%¨)
- Déterminer le rang par rapport à l'IP par Groupe
- Si le rang est inférieur aux 66%, on rajoute un 1
A partir de cette requête, on peut très facilement extraire un TCD
Cependant, ta dernière précision pourrait nécessiter des ajustements ("autres critères de productivité")
Si cela te semble "jouable"...
Bonne journée

 

[Phil69970]

Re

Je te propose ce fichier

Merci de ton retour

@Phil69970

 

[tnion]

Bonjour,
Bonjour @Phil69970
En attendant la solution de Phil, voici une solution via Power Query (en natif dans ta version), et sans VBA (mais on peut en rajouter, pour automatiser, s'il le faut)
Dans le fichier joint, à droite de ton Tableau (transformé en Tableau Structuré, nommé "T_Data"), le résultat de la requête. Pour arriver à ce résultat :
- Regrouper par Date (aa-tt), Usine et Class Prod, en calculant le nombre de chaque groupe ( multiplié par 2/3 arrondi, pour avoir les 66%¨)
- Déterminer le rang par rapport à l'IP par Groupe
- Si le rang est inférieur aux 66%, on rajoute un 1
A partir de cette requête, on peut très facilement extraire un TCD
Cependant, ta dernière précision pourrait nécessiter des ajustements ("autres critères de productivité")
Si cela te semble "jouable"...
Bonne journée

Bonjour @bhbh,

Merci beaucoup pour votre réponse! Effectivement, j'ai plein choses à apprendre! Je dois avouer que je n'ai jamais servi de PQ à date, mais ça m'intéresse

Pourriez-vous m'expliquer un peu plus les lignes de codes associés à chaque action? Je pense de comprendre une partie, mais pas tout.

- Pourquoi l'ordre des colonnes est si important? Il faut cité chaque nom de colonne pour changer l'ordre? Je demande car j'ai 84 colonnes dans ma base réelle.

- Dans l'étape "groupby", ce code "(Table.RowCount(_)*2/3), Int64.Type}" est pour cibler les 66%?

- "{"Tbl", each _, type table [IP=number]}" --> J'ai du mal à comprendre l'utilsiation du "Tbl" dans ce ligne de code, vs dans l'AjtRang où vous avez applez le tableau pour ajouter une colonne

Ma naïveté pour PQ est bien évident vu mes questions...

Merci beaucoup d'avance pour votre aide et votre patience.
Tnion

 

[Cousinhub]

Bonjour,
(Bravo pour les recherches personnelles!!!)
Un peu d'explications

let
    Source = Excel.CurrentWorkbook(){[Name="T_Data"]}[Content],
    PermutCol = Table.ReorderColumns(Source,{"Date (aa-tt)", "N° lot", "Classe prod", "Usine", "IP", "66% IP par usine par prod", "Trimestre", "Annéé longue", "Année court"}),
    // Cette étape ne sert à rien. Je m'en sers juste pour être sûr de l'orthographe des noms de colonne (absence d'espace...)
    // J'aurais pu la supprimer..
    GroupBy = Table.Group(PermutCol, {"Date (aa-tt)", "Classe prod", "Usine"}, {{"Nombre", each Number.Round(Table.RowCount(_)*2/3), Int64.Type},{"Tbl", each _, type table [IP=number]}}),
    //Ici, je regroupe par Trimestre, Classe et Usine. La colonne "Nombre" calcule les occurences par groupe.
    //Je multiplie par 2/3 pour avoir les 66% qui serviront à la suite - Ex, si 12 occurences, ça donne 8
    //La colonne "tbl" comporte la Table afférente à chaque Groupe. Clique dans la cellule (pas sur le nom), et dessous, tu auras la composition
    AjtRang = Table.AddColumn(GroupBy, "Rang", each Table.AddRankColumn([Tbl],"Rang",{"IP", Order.Descending},[RankKind = RankKind.Ordinal])),
    //Ici, je calcule le classement par groupe, selon l'IP, et en cas d'égalité, c'est numéroté à suivre (on ne saute pas de 1 à 3 si 2 premiers ex-aequo, mais on le pourrait, en passant en RankKing.Competition)
    ExpandRang = Table.ExpandTableColumn(AjtRang, "Rang", {"N° lot", "IP", "Rang"}, {"N° lot", "IP", "Rang"}),
    // On développe la colonne précédemment créee
    Best66 = Table.AddColumn(ExpandRang, "66% IP par usine par prod", each if [Rang] <= [Nombre] then 1 else null),
    // on recrée la colonne 66% avec calcul si le classement est <= au calcul de l'étape GroupBy
    SupprCol = Table.SelectColumns(Best66,{"N° lot", "Classe prod", "Usine", "IP", "66% IP par usine par prod", "Date (aa-tt)"}),
    // on ne garde que les colonnes qui nous intéressent (on supprime le reste, donc on s'affranchit des autres colonnes)
    Filtre = Table.SelectRows(SupprCol, each ([#"66% IP par usine par prod"] = 1))
    // Filtre sur les 66%
in
    Filtre

Et ta "naïveté", comme tu le dis, bien au contraire, pour un néophyte, je trouve que tu as bien travaillé...
PS, désolé pour le code, il faudrait que je demande à l'admin de pouvoir injecter du code "M" (les // correspondent à l'apostrophe dans le VBA (i-e des commentaires), et apparaissent en vert dans le code)

PS2, demande effectuée

 

[tnion]

Re

Je te propose ce fichier

Merci de ton retour

@Phil69970

Bonjour @Phil69970,

Merci beaucoup pour le fichier! Le code m'impression

Je vais adapter le code à mon bdd, qui contient actuellement > 16 000 lignes et nous allons ajouter tous les trimestres, mais en attendant, je souhaiterais savoir s'il y a possibilité de modifier le code afin de prendre en compte que les nouveaux lignes une fois que le macro a tourné 1 fois ?

Je demande car avec l'ensemble des filtres et les boucles, la manip est assez demandeur (qui est tout à fait normal quand on pense à la demande!), mais en connaissant mes collègues, ils vont cliquer ailleurs en attendant et bloquer le fichier

Je vous remercie d'avance!
Tnion

 

[tnion]

Bonjour,
(Bravo pour les recherches personnelles!!!)
Un peu d'explications

let
    Source = Excel.CurrentWorkbook(){[Name="T_Data"]}[Content],
    PermutCol = Table.ReorderColumns(Source,{"Date (aa-tt)", "N° lot", "Classe prod", "Usine", "IP", "66% IP par usine par prod", "Trimestre", "Annéé longue", "Année court"}),
    // Cette étape ne sert à rien. Je m'en sers juste pour être sûr de l'orthographe des noms de colonne (absence d'espace...)
    // J'aurais pu la supprimer..
    GroupBy = Table.Group(PermutCol, {"Date (aa-tt)", "Classe prod", "Usine"}, {{"Nombre", each Number.Round(Table.RowCount(_)*2/3), Int64.Type},{"Tbl", each _, type table [IP=number]}}),
    //Ici, je regroupe par Trimestre, Classe et Usine. La colonne "Nombre" calcule les occurences par groupe.
    //Je multiplie par 2/3 pour avoir les 66% qui serviront à la suite - Ex, si 12 occurences, ça donne 8
    //La colonne "tbl" comporte la Table afférente à chaque Groupe. Clique dans la cellule (pas sur le nom), et dessous, tu auras la composition
    AjtRang = Table.AddColumn(GroupBy, "Rang", each Table.AddRankColumn([Tbl],"Rang",{"IP", Order.Descending},[RankKind = RankKind.Ordinal])),
    //Ici, je calcule le classement par groupe, selon l'IP, et en cas d'égalité, c'est numéroté à suivre (on ne saute pas de 1 à 3 si 2 premiers ex-aequo, mais on le pourrait, en passant en RankKing.Competition)
    ExpandRang = Table.ExpandTableColumn(AjtRang, "Rang", {"N° lot", "IP", "Rang"}, {"N° lot", "IP", "Rang"}),
    // On développe la colonne précédemment créee
    Best66 = Table.AddColumn(ExpandRang, "66% IP par usine par prod", each if [Rang] <= [Nombre] then 1 else null),
    // on recrée la colonne 66% avec calcul si le classement est <= au calcul de l'étape GroupBy
    SupprCol = Table.SelectColumns(Best66,{"N° lot", "Classe prod", "Usine", "IP", "66% IP par usine par prod", "Date (aa-tt)"}),
    // on ne garde que les colonnes qui nous intéressent (on supprime le reste, donc on s'affranchit des autres colonnes)
    Filtre = Table.SelectRows(SupprCol, each ([#"66% IP par usine par prod"] = 1))
    // Filtre sur les 66%
in
    Filtre

Et ta "naïveté", comme tu le dis, bien au contraire, pour un néophyte, je trouve que tu as bien travaillé...
PS, désolé pour le code, il faudrait que je demande à l'admin de pouvoir injecter du code "M" (les // correspondent à l'apostrophe dans le VBA (i-e des commentaires), et apparaissent en vert dans le code)

PS2, demande effectuée

Bonjour @bhbh,

Merci pour les explications! C'est bien une autre langue à apprendre Et pas de souci pour la présentation du code dans le chat, j'ai bien suivi.

Ok, donc je n'ai pas besoin de retaper les 84 entêtes de mon bdd complet, phew!

Pour mes tableaux de bord, j'ai pensé de faire un recherchev avec le n° du lot qui est commun entre les deux tableaux afin de mettre à jour la colonne 66% meilleures dans mon tableau d'origine (j'ai déjà tous les autres tcd + graphiques liés au tbl de base). Est-ce que cela aura un impact sur l'utilisation de votre code en PQ?

Est-ce qu'il y a possibilité d'automatiser ce type de manip? L'objectif est que les personnes qui vont servir du fichier final n'ont pas de manip à faire, juste consulter les tableaux de bord qui sont maintenu à jour automatiquement (et sélectionner ou non les top 66% en tant que sous-population)

Cependant, ta dernière précision pourrait nécessiter des ajustements ("autres critères de productivité")

Dans mon fichier, j'ai plusieurs critères de productivité, dont l'IP. Nous souhaitons être capable de sélectionner via un segment les 66% meilleurs selon la critère IP (dont vous m'avez bien trouvé une solution) afin de regarder les autres critères de performance (ex. rendement, etc.). Le groupe 66% meilleures d'IP est une "sous-population" de ma bdd. Est-ce que cela nécessitera des ajustements?

Merci encore pour votre aide et votre temps, c'est beaucoup apprécié

Tnion

 

[Phil69970]

Re

je souhaiterais savoir s'il y a possibilité de modifier le code afin de prendre en compte que les nouveaux lignes une fois que le macro a tourné 1 fois ?

Pour cela il faut que je revois le code mais pas pour ce WE

ils vont cliquer ailleurs en attendant et bloquer le fichier

C'est peux être par la qu'il faut réfléchir pour empêcher le code si par exemple :
A l’exécution du code la 1ere fois inscrire dans une cellule (à définir) la date du jour et si il y a moins de x jours entre la date de la cellule (à définir) et la date du jour la macro ne se lance pas.
J'avais fait un truc similaire quand je bossait .....
Evidement on peut toujours contourner la macro soit en effaçant la date soit en modifiant la date de la cellule de comparaison.

Que penses tu de cette piste de réflexion et en plus cela évite de réécrire tout le code ?
Si c'est ton choix alors il faut me donner le X jours et quelle cellule reçoit la date.

Avantage ton code ne tourne que tous les X jours
Par contre ce jour tu peux aller boire le café tranquille .....

@Phil69970

 

[Cousinhub]

Bonjour,
Euh, je ne comprends pas tout, mais je vais essayer (de comprendre...)
Si je rajoute uniquement la colonne 66% en colonne A (plus insertion d'une colonne B, de taille 0, afin que les 2 Tableaux ne se chevauchent), et ce dans l'ordre du tableau original. (avec l'ajout d'un Index dans le code)
Ainsi, une simple formule SI dans la colonne 66% te renvoie le rang (1 ou rien), on gagnerait par rapport à un RechercheV
Un clic droit dans la requête pour actualiser
PS, cette formule n'interfère en rien dans le calcul, car je ne tiens pas compte de cette colonne dans le code
Si ça peut te convenir...

 

[Phil69970]

Re

J'ai oublier de donner quelques explications sur le code :

Dans la feuille "Parametre" tu peux rajouter le nombre d'usine, class prod et de date que tu veux (la macro se débrouillera) seule contrainte ne pas déplacer les colonnes sinon il faudra aller modifier le code ici et modifier le N° de départ des boucles.si tu changes la ligne des en têtes

Derlig2 = Ws2.Range("H" & Rows.Count).End(xlUp).Row
Dlig_Usine = Ws2.Range("B" & Rows.Count).End(xlUp).Row
Dlig_ClasProd = Ws2.Range("D" & Rows.Count).End(xlUp).Row

Et tu as le choix de voir les détails mais plus long ou de ne pas les voir ==> Gadget que l'on peut supprimer !!!

J'avais fais cela pour bien voir si mon code se passait bien

Autrement avec les quelques explications que j'ai mis dans le code tu devrais comprendre le fonctionnement....

J'attends ton retour pour modifier en conséquence mon code.
*La cellule de date X jours peut très bien être sur la feuille parametre

@Phil69970

 

[Phil69970]

Bonjour à tous

@tnion

Je te propose cette nouvelle version qui répond à toutes tes demandes du post # 10

Je demande car avec l'ensemble des filtres et les boucles, la manip est assez demandeur (qui est tout à fait normal quand on pense à la demande!), mais en connaissant mes collègues, ils vont cliquer ailleurs en attendant et bloquer le fichier

Tes collègues pourront toujours s'amuser à cliquer sur le bouton et seul une action volontaire sur le choix d'une année à mettre à jour ET après un clic sur le bouton fait démarrer la macro donc il faut 2 actions pour que la macro démarre et la MAJ ne se fait que sur 1 année donc le temps d'indisponibilité du fichier est sensiblement réduit lors d'une mise à jour.
J'ai laissé la possibilité de pouvoir tout mettre à jour et à ce moment la macro mettra forcément beaucoup plus longtemps.

Merci de ton retour

@Phil69970