par , 11/10/2021 à 08h39 (2172 Affichages)
Extraire des données selon des critères complexes? Les filtres avancés en VBA peuvent vous aider en une ligne de code.
Salut.
Sur le forum, on tombe régulièrement sur une question telle que celle-ci: Je dois extraire des données selon plusieurs critères, comment faire?
Réponse 2021: Power Query. C'est l'outil par excellence qui permet d'élaborer des critères par clics. Mais il faut connaître Power Query, et même si on peut paramétrer les critères de Power Query au départ d'Excel, la structure des critères sera figée dans la requête.
Lorsque les critères sont complexes ou doivent changer à la volée tout en étant formulés, le filtre avancé est un outil qui permet de faire de belles choses, tant côté Excel qu'en VBA.
Filtre avancé côté Excel
Un filtre avancé se conçoit autour de 3 plages:
- les données, normalement sous forme de tableau structuré. Mon tuto sur ce sujet vous en dit plus:
- les critères, plage d'au moins 1 colonne et 2 lignes;
- la plage de résultat.
Filtre avancé non formulé
Je ne détaille pas le fonctionnement du filtre avancé dans ce cas de figure, car je pense que Power Query remplace avantageusement le filtre avancé.
Filtre avancé formulé
Il est possible de formuler un ou plusieurs critères. Dans ce cas, une bonne pratique est de formuler tout le critère. On voit ci-dessous comment articuler les plages avec un critère formulé. On remarque que l'entête de la formule qui définit le critère ne peut pas être équivalent à un intitulé de la table source. Le critère doit être formulé par rapport à la première ligne de données, et la zone de données doit contenir la ligne d'entête. La formule doit donc renvoyer VRAI ou FAUX. Il n'est pas important qu'elle renvoie VRAI pour la deuxième ligne. Par contre, l'utilisation des références absolues et relatives prend ici tout son sens car il faut comprendre que le filtre avancé va utiliser votre formule sur chaque ligne du tableau.
Il est possible d'extraire les données sur une autre feuille, mais en Excel, il ne faut pas louper la manoeuvre.
Et en VBA?
Extraction classique en VBA
Sur nos forums comme sur d'autres, on vous parlera de dictionary, de If Then, et d'autres trucs de programmeur qui ne devraient pas concerner les utilisateurs d'Excel. Après tout, sauf si vous êtes développeur de solutions, votre métier n'est pas de coder, mais d'utiliser Excel pour vos besoins d'analyse de données, non?
Ainsi, un code tel que celui-ci devrait vous laisser de marbre, voire vous faire fuir.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
| Sub Recherche()
Dim a(), d As New Dictionary, i As Long, an1 As Long, an2 As Long, Jour As Long
Application.ScreenUpdating = False
a = Worksheets("Commande").UsedRange.Value
Worksheets("Résultat").Range("A2:A100").ClearContents
an1 = Worksheets("Résultat").Range("B2").Value: an2 = Worksheets("Résultat").Range("C2").Value
Jour = Worksheets("Résultat").Range("D2").Value
For i = 2 To UBound(a)
If Day(a(i, 1)) = Jour Then
If Year(a(i, 1)) = an1 Or Year(a(i, 1)) = an2 Then 'needed date
d(a(i, 5)) = a(i, 5)
End If
End If
Next
If d.Count > 1 Then
Worksheets("Résultat").Range("A2").Resize(d.Count) = Application.Transpose(d.Items)
Else
Worksheets("Résultat").Range("A2").Value = d.Item(1)
End If
Worksheets("Résultat").Range("A2:A" & Worksheets("Résultat").Cells(Worksheets("Résultat").Rows.Count, "A").End(xlUp).Row).Sort key1:=Worksheets("Résultat").Range("A2"), order1:=xlAscending
Application.ScreenUpdating = True
End Sub |
C'est truffé de hard-coding, ça n'utilise pas les tableaux structurés, c'est verbeux à n'en plus finir, et le pire de tout reste à venir: la moindre modification côté Excel fait péter tout le bazar et vous oblige à mettre les mains dans le cambouis. Pouah!
Filtre avancé ou advanced filter
En VBA, nous allons voir que l'extraction réclame une unique ligne de code, et que l'écriture du code sera facilitée par la création de plages nommées, tant pour le critère formulé que pour la plage de résultat.
Notre tableau structuré est nommé, t_Ventes dans le cas présent, nous devons donc uniquement nommer la zone de critères et la zone des intitulés de résultat. Il faut remarquer ici que ces zones peuvent être localisées sur d'autres feuilles du classeur. Pour l'exemple, je nomme la zone de critères Critères et la zone de résultat Résultat.
Du coup, l'unique ligne de code devient:
range("t_Ventes[#all]").AdvancedFilter xlFilterCopy,range("Critères"),range("Résultat")
Wouah!! Ca on aime, et il faut reconnaître que c'est bien plus digeste que le code donné plus haut à coup de boucle, de dictionnaire et autres machins de programmeur. On remarquera également que si les critères, aussi complexes soient-ils, venaient à être modifiés, il suffirait simplement de modifier la formule, ce qu'un utilisateur normal d'Excel devrait pouvoir réaliser, et de relancer la ligne de code.
Ainsi, si je souhaite extraire les ventes des jours pairs des mois impairs pour les vendeurs dont le prénom commence par "P", je modifie ma formule et mon code, lui, continue à fonctionner sans rien y modifier...
Conclusions
Remplacer un code plein de hard-coding par une ligne de code grâce à une bonne connaissance de vos outils Excel fait pour moi partie des bonnes pratiques, n'en déplaise aux grincheux qui viennent pleurnicher dès que j'emploie ce terme, et il serait dommage de se passer des outils natifs d'Excel en passant par des lignes de code mal construit et mal maîtrisé.
Méfiez-vous des solutions de programmeur lorsque vous travaillez avec Excel. Pensez Excel avant de penser VBA. Vous simplifierez votre approche et vous maîtriserez bien mieux vos solutions.
Et vous, comment abordez-vous vos développements en VBA pour Excel?
* 78chris me rappelle que l'on peut utiliser les noms d'étiquettes dans un filtre avancé. Ne vous préoccupez pas ici de l'erreur affichée #NOM?. Excel comprendra la formule lorsqu'il l'appliquer avec le filtre avancé:
Le tutoriel de Philippe Tulliez vous dit tout sur les filtres avancés: https://philippetulliez.developpez.c...dvancedfilter/
.
Mis à jour 12/10/2021 à 17h29 par Pierre Fauconnier
Tags:
advanced filter,
critere,
critère formulé,
critères complexes,
extraction,
extraire,
filtre,
filtre avancé,
formule,
power query,
vba
- Catégories
-
Excel
,
MS Office
,
Trucs & Astuces