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

 

[tnion]

Bonjour @Phil69970 , @bhbh,

Je tiens à vous remercier tous les deux pour votre réactivité et vos conseils précieux qui m'ont permis de résoudre le problème.

J'ai utilisé les deux solutions proposés: celui de @bhbh en 1er lieu pour classifier l'ensemble de mes données (solution qui a été plus rapide avec plus que 17 000 lignes de données), et j'ai utilisé la solution 2 de @Phil69970 pour maintenir le fichier à jour quand mes collègues ajoute des données.

Merci encore! Et bonne continuation.
Cdt,
Tnion

 

[tnion]

Bonjour à tous

@tnion

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


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

Bonjour @Phil69970,

J'espère que vous allez bien La solution d'ajouter un bouton pour sélectionner l'année d'intérêt (dans cecas que les nouveaux données) est top!

J'ai bien essayé d'adapter votre code du fichier d'exemple dans mon fichier de base, mais j'ai rencontré un souci que je n'arrive pas à réglé toute seule.

Après avoir choisi 2017 dans l'onglet "Parametre", je lance le macro pour déterminer les 66% meilleurs par trim / usine / prod. La colonne AS est celui ciblé pour identifier les 66% meilleurs via le code VBA, mais à la fin il y a que 12 lignes identifiés. J'ai laissé les la catégorisation avec PQ dans la colonne à côté pour vérifier.

Je suis navrée mais mon fichier est trop grand pour le mettre en PJ. Je vous ai mis une écran et le code à la suite.

Sub Filtre()
'Application.ScreenUpdating = False
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim Derlig1&, Derlig2&, Dlig_Usine&, Dlig_ClasProd&
Dim Critere1, Critere2, Critere3, Colonne1 As Byte, Colonne2 As Byte, Colonne3 As Byte
Dim i&, k&, N_Usine&, N_ClasProd&
Dim Cptr&, Nb&, Nb_Filtrer&

Set Ws1 = Worksheets("Données")
Set Ws2 = Worksheets("Parametre")

If Ws2.Range("J1") = "" Then MsgBox "Pas d'année choisie dans la feuille Paramètre", vbCritical, "Sélection année manquante"
'If Ws1.Range("CR") <> "Voir détail" Then Application.ScreenUpdating = False

Derlig1 = Ws1.Range("A" & Rows.Count).End(xlUp).Row
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

Cptr = 0
For i = 4 To Derlig2
Critere1 = Ws2.Range("H" & i).Value
Colonne1 = 81
Ws1.Range("$A$1:$CF$" & Derlig1).AutoFilter Field:=Colonne1, Criteria1:=Critere1 'Filtre les Année-Trim
Ws1.Range("CL1") = Critere1 'info Date-trim
Nb_Filtrer = Ws1.Range("A1:A" & Derlig1).SpecialCells(xlCellTypeVisible).Count - 1
If Nb_Filtrer > 1 Then '1
For N_Usine = 4 To Dlig_Usine 'Boucle Usine
Critere2 = Ws2.Range("B" & N_Usine).Value
Colonne2 = 83
Ws1.Range("$A$1:$CF$" & Derlig1).AutoFilter Field:=Colonne2, Criteria1:=Critere2 'Filtre les usines
Ws1.Range("CN1") = Critere2 'info Usine
Nb_Filtrer = Ws1.Range("A1:A" & Derlig1).SpecialCells(xlCellTypeVisible).Count - 1
If Nb_Filtrer > 1 Then '2
For N_ClasProd = 4 To Dlig_ClasProd 'Boucle Classe Prod
Critere3 = Ws2.Range("D" & N_ClasProd).Value
Colonne3 = 84
Ws1.Range("$A$1:$CF$" & Derlig1).AutoFilter Field:=Colonne3, Criteria1:=Critere3 'Filtre les Classe Prod
Ws1.Range("CP1") = Critere3 'info Classe Prod
Nb_Filtrer = Ws1.Range("A1:A" & Derlig1).SpecialCells(xlCellTypeVisible).Count - 1
If Nb_Filtrer > 1 Then '3
Nb = Round(Nb_Filtrer * 0.66)
Worksheets("Données").Sort.SortFields.Clear
Ws1.Range("$A$1:$CF$" & Derlig1).Sort Key1:=Range("AN2"), Header:=xlYes, Order1:=xlDescending ' Key range AN = la colonne avec les valeurs IP
Cptr = 0
For k = 2 To Derlig1
If Ws1.Rows(k).Hidden <> True Then
Ws1.Range("AS" & k) = 1
Cptr = Cptr + 1
If Cptr = Nb Then k = Derlig1
End If '4
Next k
End If '3
Next N_ClasProd
End If '2
Next N_Usine
End If '1
If Feuil1.FilterMode Then Feuil1.ShowAllData
Next i
If Ws1.AutoFilterMode Then Ws1.AutoFilterMode = False
Ws2.Range("J1") = ""

Set Ws1 = Nothing: Set Ws2 = Nothing
End Sub



Si jamais vous pourriez regarder le code, ça sera grandement apprécié!

Bonne journée,
Tnion

 

[Phil69970]

Bonjour @tnion

Avec si peu d'information ET surtout une image je peux difficilement me rendre compte ce qui cloche.

Une piste un peu à l'aveugle :

As tu des espaces dans la colonnes CC ce qui donnerait ceci

Je te conseille de faire un champ calculé avec une formule comme dans mon fichier

=CONCATENER(CB2;"-";BZ2)

D’où l’intérêt de mettre un fichier représentatif

C'est quoi représentatif ?

- représentatif, même organisation des lignes et des colonnes, mêmes libellés, mêmes noms de feuilles...
- anonymisé, pas de données personnelles réelles tels nom, n° sécu, adresse ...
- simplifié, une quinzaine de lignes reproduisant l'ensemble des différents cas envisageables (Avec le résultat souhaité)

@Phil69970

 

[tnion]

Bonjour @tnion

Avec si peu d'information ET surtout une image je peux difficilement me rendre compte ce qui cloche.

Une piste un peu à l'aveugle :

As tu des espaces dans la colonnes CC ce qui donnerait ceci

Regarde la pièce jointe 1166347

Je te conseille de faire un champ calculé avec une formule comme dans mon fichier


D’où l’intérêt de mettre un fichier représentatif

C'est quoi représentatif ?

- représentatif, même organisation des lignes et des colonnes, mêmes libellés, mêmes noms de feuilles...
- anonymisé, pas de données personnelles réelles tels nom, n° sécu, adresse ...
- simplifié, une quinzaine de lignes reproduisant l'ensemble des différents cas envisageables (Avec le résultat souhaité)

@Phil69970

Bonjour @Phil69970,

Tout d'abord, je m'excuse pour la qualité de l'image.

Voici une copie du fichier avec l'ensemble des colonnes (A --> CF, sans données pour allégé le fichier), avec votre code que j'ai essayé d'adapter. Le résultat souhaité se trouve dans la colonne AR, avec le résultat du VBA dans la colonne AS. Normalement, pas de colonne minimisé.

Ca semble que le code fonctionne que pour une partie des données (le résultat obtenu quand j'ai demandé l'année 2017), et là je suis clairement en dehors de mes compétences!

Auriez vous un instant pour regarder?

Je vous remercie d'avance.
Tnion

 

[Cousinhub]

Bonjour tnion
Je n'arrive pas à comprendre ce mix (VBA + PQ)...
Vous dites que les résultats obtenus via PQ sont plus rapides sur votre base, et vous voulez y associer du VBA pour un autre traitement...
Si c'est juste pour obtenir des données par rapport à un filtre (annuel/trimestriel, par usine, classe prod....), PQ peut très bien le faire également, et extraire les données voulues dans un autre onglet, pour une analyse plus fine...
Il suffit de déterminer tous les filtres que vous voulez, de donner les colonnes nécessaires à l'analyse, et ensuite, s'il le faut, appuyer sur un bouton pour mettre à jour l'extraction...(avec uniquement les colonnes nécessaires)
Dans votre tableau, pour déterminer les trimestres, années longues..., vous devez utiliser des formules qui ralentissent d'autant les calculs.
Avec la simple date (qui doit apparaître dans une colonne quelconque), tout ces calculs peuvent être fait en interne PQ)...(et ainsi déjà supprimer les colonnes inutiles...)
Bref, à vous de voir...(je n'ai aucune action en rapport avec PQ, juste une préférence...)

 

[tnion]

Bonjour tnion
Je n'arrive pas à comprendre ce mix (VBA + PQ)...
Vous dites que les résultats obtenus via PQ sont plus rapides sur votre base, et vous voulez y associer du VBA pour un autre traitement...
Si c'est juste pour obtenir des données par rapport à un filtre (annuel/trimestriel, par usine, classe prod....), PQ peut très bien le faire également, et extraire les données voulues dans un autre onglet, pour une analyse plus fine...
Il suffit de déterminer tous les filtres que vous voulez, de donner les colonnes nécessaires à l'analyse, et ensuite, s'il le faut, appuyer sur un bouton pour mettre à jour l'extraction...(avec uniquement les colonnes nécessaires)
Dans votre tableau, pour déterminer les trimestres, années longues..., vous devez utiliser des formules qui ralentissent d'autant les calculs.
Avec la simple date (qui doit apparaître dans une colonne quelconque), tout ces calculs peuvent être fait en interne PQ)...(et ainsi déjà supprimer les colonnes inutiles...)
Bref, à vous de voir...(je n'ai aucune action en rapport avec PQ, juste une préférence...)

Bonjour @bhbh,

Merci pour votre réponse à nouveau.

Selon vous, il ne faut pas mélanger PQ et VBA? J'ai trouvé votre solution avec PQ d'être vraiment efficace et rapide, mais j'ai cherché une façon de faire en automatique dès qu'il y a des données ajoutés. Et vu que pour moi je découvre PQ, j'ai pensé que ça sera plus facile en VBA... mais bon, pas évident non plus

Donc techniquement pour PQ, il prendra en compte tout nouvelle donnée, et pour actualiser la requête il suffit juste de cliquer sur "Actualiser" dans PQ?

Et oui, je triche avec mes milles colonnes pour faire des calculs d'année, de trimestre, de date (aa-tt), etc. Il y a bien des dates (jj/mm/aaaa) dans le fichier. Quand je ne sais pas faire, des colonnes supplémentaires "simplifier" les manips.

J'avoue que les filtres multiples en automatique (soit avec PQ ou VBA) est largement en dehors de mes compétences.

Bonne journée,
Tnion

 

[Cousinhub]

Re-,
Oui, pour une mise à jour, un clic sur "Actualiser tout" suffit...
Pour les calculs de trimestre, année, je travaille dessus.
Comment s'appelle la colonne contenant les dates entrant en compte dans le calcul des trimestres? (nom exact du titre de la colonne, pas son numéro ni colonne "A"...)
Et PQ et VBA ne sont nullement incompatibles, on peut très bien les marier, mais chacun son rôle (comme partout... )

 

[tnion]

Re-,
Oui, pour une mise à jour, un clic sur "Actualiser tout" suffit...
Pour les calculs de trimestre, année, je travaille dessus.
Comment s'appelle la colonne contenant les dates entrant en compte dans le calcul des trimestres? (nom exact du titre de la colonne, pas son numéro ni colonne "A"...)
Et PQ et VBA ne sont nullement incompatibles, on peut très bien les marier, mais chacun son rôle (comme partout... )

Re,

Les calculs de dates sont fait à partir de la colonne H intitulé "Date fin".

Ok, s'il faut juste "Actualiser tout" dans PQ, je pense que je pourrais expliquer comment faire à mes collègues (qui ont plus du mal avec excel que moi...).

Milles mercis pour votre aide à nouveau!
Tnion

 

[Cousinhub]

Hello,
tnion
Le projet est quasiment terminé...
Reste juste à l'adapter à ton fichier réel...
Donc, pour éviter de multiples échanges, je vais juste te demander quelques précisions :

- Quels seraient les filtres à envisager? (usine, prod, année, trimestres, autre....)

- Peux-tu fournir un fichier vierge, avec uniquement les titres de colonne de la base de donnée? (sachant que toutes les données à calculer seront effectuées dans la requête, et que je ne vais me baser que sur la colonne "Date fin" - Donc les colonnes supplémentaires seront à supprimer)

- Quelles colonnes voudrais-tu conserver dans l'extraction finale? Ces colonnes à conserver doivent-elles être modulables?

+ d'autres souhaits?

Bonne soirée

 

[tnion]

Hello,
tnion
Le projet est quasiment terminé...
Reste juste à l'adapter à ton fichier réel...
Donc, pour éviter de multiples échanges, je vais juste te demander quelques précisions :

- Quels seraient les filtres à envisager? (usine, prod, année, trimestres, autre....)

- Peux-tu fournir un fichier vierge, avec uniquement les titres de colonne de la base de donnée? (sachant que toutes les données à calculer seront effectuées dans la requête, et que je ne vais me baser que sur la colonne "Date fin" - Donc les colonnes supplémentaires seront à supprimer)

- Quelles colonnes voudrais-tu conserver dans l'extraction finale? Ces colonnes à conserver doivent-elles être modulables?

+ d'autres souhaits?

Bonne soirée

Bonjour @bhbh,

Tout d'abord, désolée pour ma réponse tardive.

On reste sur les mêmes filtres que vous avez fait dans l'autre fichier que vous avez proposé, dont :
- année
- trimestre
- usine
- prod

Où l'objectif final est de déterminer quels lots sont dans les 66% meilleurs selon leur valeur IP (colonne AH) par année/trimestre/usine/prod et de les identifier dans la colonne (AL).

Dans l'idéale, il faut garder l'ensemble des colonnes. Les utilisateurs pourront potentiellement modifier les informations (ex. valeurs aberrants).

Merci beaucoup d'avance, c'est toujours très apprécié!
Tnion

 

[Cousinhub]

Bonjour,
OK, (bien fait de demander un classeur vierge....)
Par contre, je ne trouve pas les colonnes "Usine" et "Classe prod"...
A quelles colonnes correspondent-elles dans ton fichier réel?
@ te relire

 

[tnion]

Bonjour,
OK, (bien fait de demander un classeur vierge....)
Par contre, je ne trouve pas les colonnes "Usine" et "Classe prod"...
A quelles colonnes correspondent-elles dans ton fichier réel?
@ te relire

Re @bhbh,

Oui, c'est un BDD assez conséquent à la fin...

Ah oui, excusez moi! J'avais renommé des colonnes dans le fichier d'exemple que j'avais partagé au début.

Les vrais intitulés sont:
Usine = Usine d'aliment (colonne J)
Classe prod = Libellé production (colonne E)

Merci toujours!

 

[Cousinhub]

Re-,
La V3..
Dans l'onglet Paramètres, tu choisis l'usine, la production, l'année et le trimestre
Si tu choisis "Tous", tout le tableau va être extrait. Si la cellule est vide, ça correspond à "Tous" (ou "Toutes") - Tu peux mixer les choix
Tu remplis les données d'usine ou de production dans les listes des colonnes A et C (pour les autres, pas besoin).
Quand tu vas arriver sur l'onglet "Extract", tu vas avoir un message te demandant de confirmer (ou non) la mise à jour de l'extraction (par exemple, si la base n'a pas été modifiée, il est inutile de recalculer à chaque fois)
Tu mets tes données dans l'onglet "Données" (vérifie bien que c'est bien la colonne BR la dernière dans ton copier coller...)
Et dans l'extraction, en colonne AL (66% IP /aa-tt/usine/prod), tu as les calculs 66% Best (=1)
Bonne découverte

 

[tnion]

Re-,
La V3..
Dans l'onglet Paramètres, tu choisis l'usine, la production, l'année et le trimestre
Si tu choisis "Tous", tout le tableau va être extrait. Si la cellule est vide, ça correspond à "Tous" (ou "Toutes") - Tu peux mixer les choix
Tu remplis les données d'usine ou de production dans les listes des colonnes A et C (pour les autres, pas besoin).
Quand tu vas arriver sur l'onglet "Extract", tu vas avoir un message te demandant de confirmer (ou non) la mise à jour de l'extraction (par exemple, si la base n'a pas été modifiée, il est inutile de recalculer à chaque fois)
Tu mets tes données dans l'onglet "Données" (vérifie bien que c'est bien la colonne BR la dernière dans ton copier coller...)
Et dans l'extraction, en colonne AL (66% IP /aa-tt/usine/prod), tu as les calculs 66% Best (=1)
Bonne découverte

Bonjour @bhbh,

Juste un petit mot pour vous remercier pour tout votre aide!

Le fichier est au top! J'ai hâte de partager avec mes collègues

Bonne continuation,
Tnion