IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Voir le flux RSS

Pierre Fauconnier

Extraire des données sur base de critères complexes avec les filtres avancés en Excel et en VBA

Noter ce billet
par , 11/10/2021 à 09h39 (1865 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.

Nom : 2021-10-11_071308.png
Affichages : 406
Taille : 248,2 Ko

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.

Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
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:
Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
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...

Nom : 2021-10-11_082515.png
Affichages : 473
Taille : 201,4 Ko


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é:
Nom : 2021-10-11_083522.png
Affichages : 410
Taille : 109,1 Ko


Le tutoriel de Philippe Tulliez vous dit tout sur les filtres avancés: https://philippetulliez.developpez.c...dvancedfilter/




.

Envoyer le billet « Extraire des données sur base de critères complexes avec les filtres avancés en Excel et en VBA » dans le blog Viadeo Envoyer le billet « Extraire des données sur base de critères complexes avec les filtres avancés en Excel et en VBA » dans le blog Twitter Envoyer le billet « Extraire des données sur base de critères complexes avec les filtres avancés en Excel et en VBA » dans le blog Google Envoyer le billet « Extraire des données sur base de critères complexes avec les filtres avancés en Excel et en VBA » dans le blog Facebook Envoyer le billet « Extraire des données sur base de critères complexes avec les filtres avancés en Excel et en VBA » dans le blog Digg Envoyer le billet « Extraire des données sur base de critères complexes avec les filtres avancés en Excel et en VBA » dans le blog Delicious Envoyer le billet « Extraire des données sur base de critères complexes avec les filtres avancés en Excel et en VBA » dans le blog MySpace Envoyer le billet « Extraire des données sur base de critères complexes avec les filtres avancés en Excel et en VBA » dans le blog Yahoo

Commentaires

  1. Avatar de Goube
    • |
    • permalink
    Bonsoir Pierre,

    Je viens de lire votre post, très intéressant cette manip qui permet de simplifier l'usage des filtres.

    J'ai tenté de reproduire la démonstration, mais je n'obtiens pas le résultat escompté. Toutes les lignes du tableau original sont recopiées en dessous de ma ligne nommée Résultat.

    Mon tableau (A1:G633) est nommé Commandes, mes critères =ET(JOURSEM(B2;2)=5;C2="Fast mag";D2="Rasoir") en I4 nommés Critères et ma ligne résultat, recopie des titres de mon tableau Commandes, en I6:O6 nommée Résultat.

    Macro insérée dans un module :
    Sub filtrer()
    Range("Commandes[#all]").AdvancedFilter xlFilterCopy, Range("Critères"), Range("Résultat")
    End Sub

    Testé avec références cellules ou étiquettes colonnes, même résultat. Aurais je zappé quelque chose ?

    Bonne soirée, cordialement
  2. Avatar de Pierre Fauconnier
    • |
    • permalink
    Salut.

    A mon avis, la plage Critères est mal définie et ne pointe que vers I4. Or, comme je le précise dans le billet, une plage de critères doit avoir au moins une colonne et DEUX lignes, la ligne de l'intitulé ET, juste en dessous, la ligne pour le critère qui concerne cet intitulé.

    Dans le cas d'un filtre avancé sur un seul critère formulé, il faut donc que la plage contienne deux cellules superposées, soit I3:I4. Il faut en plus que l'intitulé en I3 ne soit pas présent comme intitulé de données dans la source. Dans mon exemple, la plage de critères est donc la plage E1:E2, et Formule en E1 n'est pas un intitulé d'une colonne du tableau.

    Si tu as effectivement nommé I4 comme Critères, alors Excel ne trouve pas de critères et se dit qu'il doit tout prendre. Il faut modifier la référence utilisée par Critères via le gestionnaire de noms => Formules, Noms définis, Gestionnaire de noms.


    Merci de me faire part du résultat de tes prochains tests
  3. Avatar de Goube
    • |
    • permalink
    Bonjour,

    Rectification faite, tout fonctionne, il fallait bien prendre les deux lignes.

    Merci pour ce partage de connaissances et la découverte de nouvelles techniques pour simplifier notre pratique de ce bel outil qu'est Excel.

    Bonne continuation et bonne journée.

    Daniel