I. Introduction▲
Vous voulez extraire des enregistrements d'une table de données selon certains critères : s'ils sont relativement simples, le filtre automatique répondra parfaitement à votre attente, si par contre vos critères sont plus complexes (champs calculés, suite de critères logiques ET et OU…) vous atteindrez vite les limites du filtre automatique.
I-A. Glossaire▲
Les filtres avancés sont connus aussi sous le nom de filtres élaborés. Nous emploierons donc indifféremment ces deux termes.
Table de données : la première ligne d'une table de données Excel doit contenir les noms des champs, appelés aussi étiquettes ; les lignes, de la deuxième à la dernière, contiennent toutes les données (par exemple les renseignements concernant les clients) : ces lignes sont appelées "enregistrements".
Chaque colonne contient, pour chaque enregistrement, les données correspondant à l'étiquette de la colonne.
Enregistrement : ligne contenant tous les éléments spécifiques d'un objet déterminé et unique (par exemple les données d'un client, d'un article…).
Étiquette : chaque cellule de la première ligne de la table de données, nommant le contenu de la colonne (par exemple : "Nom", "Prénom", Numéro", etc.).
II. Étape préliminaire à la préparation d'un filtre avancé▲
Pour utiliser un filtre élaboré, il faut au moins une table de données et une zone de critères.
- La table de données doit avoir en première ligne les étiquettes de colonne.
- La zone de critères doit avoir en première ligne des étiquettes de colonnes et au moins une ligne avec un ou plusieurs critères sauf pour filtrer les doublons (ce sujet sera abordé dans un chapitre ultérieur).
Partons de cette table de données qui va nous permettre de tester les filtres élaborés sans trop de difficultés.
Déplaçons ce tableau jusqu'à la ligne 5 et copions les étiquettes de ligne de ce tableau sur la 1re ligne de la feuille Excel.
les étiquettes de la zone de critères doivent avoir la même orthographe que les étiquettes de la table de données.
III. Comment activer le filtre élaboré▲
- Excel 2003 Menu [Données] - Filtrer - Filtre élaboré.
- Excel 2007 et suivants : Onglet [Données], groupe Trier et filtrer, commande Avancé.
Ensuite la boîte de dialogue apparaît, la zone Plages remplie si le curseur se trouve dans une des cellules de la table des données.
IV. Quelques exemples simples▲
IV-A. Premier exemple▲
Commençons par un cas simple : filtrer les lignes (les enregistrements) où la colonne Lieu est égale à "Bruxelles".
Dans la cellule B2 qui se trouve juste en dessous de l'étiquette Lieu, nous introduisons la valeur "Bruxelles".
Dans le groupe Trier et filtrer de l'onglet [Données], cliquons sur la commande Avancé. La boîte de dialogue Filtre avancé apparaît.
Analysons chaque option de cette boîte.
- Filtrer la liste sur place : filtre directement dans la zone de cellules d'Excel où sont placées les données de départ et qui sont déclarées par l'option Plages (dans notre exemple A5:G12).
- Copier vers un autre emplacement : permet de créer la liste filtrée vers un autre emplacement défini par l'option Copier dans.
- Plages : la zone à filtrer.
- Zone de critères : désigne la plage de cellules où nous avons inséré nos critères de filtrage (dans notre exemple A2:G2).
- Copier dans : désigne la cellule ou la plage de cellules à partir de laquelle la copie des lignes filtrées se fera. Cette option n'est accessible que si l'option Copier vers un autre emplacement est cochée.
- Extraction sans doublon : permet de ne pas afficher les données en double soit avec le filtre sur place, soit en copiant vers un autre emplacement.
Nous optons donc pour le filtre sur place en prenant comme plage A5:G12 et comme zone de critères A1:G2. Confirmons par OK et voyons le résultat.
Nous pouvons constater que les numéros des lignes filtrées sont de couleur bleue et que les lignes 7, 8, 10 et 12 sont masquées : en effet, la valeur contenue dans ces lignes est différente du critère choisi ("Bruxelles").
Ce résultat aurait bien entendu pu être obtenu par un filtre simple.
Pour afficher à nouveau les données, il suffit de cliquer sur la commande Effacer qui se trouve dans le même groupe que la commande Filtre élaboré.
IV-B. Deuxième exemple▲
Filtrons maintenant les enregistrements pour lesquels le lieu est "Bruxelles" ET dont la date de naissance est supérieure ou égale au 1er janvier 2008.
Ajoutons donc en cellule C2 la valeur >=01/01/2008. Exécutons ensuite la même commande en choisissant les mêmes options.
Nous constatons qu'il n'y a plus que deux enregistrements qui répondent à ces deux critères combinés.
IV-C. Troisième exemple▲
Appliquons ensuite un filtre en ajoutant un critère :(Lieu=Bruxelles et Date de naissance>=01/01/1998) OU (Moyenne>7,5).
Pour obtenir le résultat escompté, ajoutons en G3 (colonne Moyenne) la valeur >7,5.
Dans la boîte de dialogue, modifions la zone de critères en mettant A1:G3.
En conclusion, nous constatons que placer des critères sur une même ligne équivaut à la fonction ET ; par contre, si nous utilisons plusieurs lignes, cela équivaut à la fonction OU
IV-D. Quatrième exemple▲
Cet exemple est pratiquement le même que le précédent sauf qu'ici dans la deuxième ligne (OU), nous ajouterons Bruxelles dans le critère Lieu.
Ce qui signifie que nous filtrons les enregistrements avec comme critères :
(Lieu =Bruxelles) ET (Naissance>=01/01/1998 OU Moyenne>7,5),
ce qui peut s'écrire également
(Lieu=Bruxelles ET Date de naissance>=01/01/1998) OU (Lieu=Bruxelles ET Moyenne>7,5).
V. Le filtre élaboré avec plusieurs critères▲
Dans les exemples précédents, nous avons utilisé un seul critère de filtre par colonne; dans les suivants, nous utiliserons simultanément plusieurs critères.
Par exemple, nous cherchons à filtrer les données de personnes dont les moyennes de cotes se situent entre 7,5 et 9.
Précision importante : jusqu'à présent, nous avions dans la zone des critères, le même nombre de colonnes que la table de données. Cela n'est absolument pas indispensable, seules les colonnes et leurs étiquettes concernées par les critères doivent être présentes.
Nous allons donc utiliser deux colonnes de critères portant la même étiquette Moyenne, comme le montre l'exemple ci-dessous.
Dans la boîte de dialogue, l'option Zone de critères fera donc référence aux cellules A1:B2.
Si nous souhaitons appliquer un critère supplémentaire, par exemple, les personnes de Bruxelles ayant une moyenne entre 7 et 9, nous ajouterons une colonne de critères et dans la boîte de dialogue, l'option Zone de critères fera donc référence aux cellules A1:C2, comme l'illustre l'exemple ci-dessous.
VI. Les critères calculés▲
VI-A. Exemples de critère calculé▲
Un des gros avantages du filtre élaboré, fort méconnu, est l'utilisation de critères calculés.
Pour les utiliser, entrons une formule en lieu et place d'une constante et plaçons comme étiquette de colonne un nom que l'on ne retrouve pas comme en-tête dans la table de données.
Ainsi, si l'on souhaite filtrer les données des personnes nées en 1997, nous placerons une colonne avec comme étiquette AnneeNaiss et à la ligne suivante la formule =ANNEE(C6)=1997.
Nous constatons que la cellule A2 qui contient la formule =ANNEE(C6)=1997 et qui fait référence à la cellule C6 renvoie FAUX : en effet la valeur de la cellule en C6 est le 5 février 1998 donc année 1998.
En conclusion : lorsque nous utilisons des critères calculés dans les filtres élaborés :
l'étiquette de colonne doit porter un nom différent de celui d'une étiquette de la table de données ;
le critère doit être une formule ou une suite de formules imbriquées qui doit renvoyer VRAI ou FAUX ;
le test logique DOIT être effectué sur une ou plusieurs cellules de la première ligne de la table de données.
Nous pourrions bien entendu ajouter des critères calculés.
Par exemple, filtrons les enregistrements des personnes nées en 1997 et dont la cote en math est égale ou supérieure à la moyenne des points obtenus en math.
Ajoutons une colonne critère dont l'étiquette sera MoyMath et la formule =D6>MOYENNE($D$6:$D$12). Sachant que la moyenne obtenue en math pour l'ensemble des personnes enregistrées est de 7,8, le résultat final est de deux enregistrements filtrés.
On aurait pu bien sûr dans ce cas se limiter à une seule colonne contenant le critère calculé avec, par exemple, comme étiquette AnSupMoy et comme formule :
=ET(ANNEE(C6)=1997;D6>=MOYENNE($D$6:$D$12))
ou encore
=(ANNEE(C6)=1997)*(D6>=MOYENNE($D$6:$D$12)).
VI-B. Pour bien comprendre le principe du critère calculé▲
Petit rappel : en A2 la formule est =ANNEE(C6)=1997, en B2 nous avons =D6>MOYENNE($D$6:$D$12).
Saisissons ces mêmes formules en I6 & J6 et recopions-les vers le bas, soit la plage I6:J12.
Nous constatons que seules deux lignes renvoient VRAI dans les deux colonnes I & J.
VII. Exporter les données filtrées▲
Un grand avantage du filtre élaboré est sa capacité à exporter des données filtrées vers un autre emplacement.
L'exportation des données filtrées se prépare de la même manière que ce qui a été décrit plus haut.
Il suffit de cocher l'option Copier vers un autre emplacement et ensuite référencer dans la zone Copier dans, la cellule à partir de laquelle doivent s'exporter les enregistrements filtrés, comme le montre l'illustration.
Les éléments ainsi filtrés se retrouvent bien à partir de $J$1.
Il y a deux possibilités d'exporter les données filtrées : soit l'entièreté des champs de la table de données, soit certains champs seulement.
VII-A. Exportation de toutes les données filtrées▲
C'est l'option par défaut que l'on retrouve dans l'exemple ci-dessus : nous avons simplement indiqué la référence de départ ($J$1) dans le champ Copier dans : de la boîte de dialogue Filtre avancé.
VII-B. Exportation partielle des données filtrées▲
L'exportation partielle demande une petite préparation.
Prenons comme hypothèse que nous ne souhaitons avoir que les colonnes Prénom, Lieu, Math et Moyenne des enregistrements ainsi filtrés.
Copions les étiquettes de ces colonnes de J1 à M1 et ensuite dans la zone Copier dans de la boîte de dialogue Filtre avancé, sélectionnons les cellules J1:M1.
Après confirmation, les données filtrées sont exportées.
L'autre avantage de l'exportation partielle est la présentation dans un ordre différent des colonnes de celui de la table de données initiale. Il suffit de placer les étiquettes dans l'ordre souhaité.
VII-C. Exportation vers une autre feuille que la table de données▲
Curieusement, il est impossible de sélectionner une autre feuille dans la zone Copier dans de la boîte de dialogue Filtre avancé. Même l'utilisation d'une plage nommée ne fonctionne pas.
Pour pallier ce problème, il faut lancer le filtre élaboré depuis la feuille d'où l'on veut exporter (il faudrait plutôt dire importer), la liste filtrée d'une table se trouvant sur une autre feuille.
VIII. Exportation sans doublon▲
VIII-A. Principe▲
Cette fonctionnalité est surtout intéressante lorsqu'il s'agit de ne prendre que les éléments uniques d'une colonne, surtout depuis la version 2007 d'Excel qui intègre un outil qui permet de supprimer les doublons.
Ainsi par exemple, nous aimerions avoir la liste des villes utilisées dans la colonne B (étiquette Lieu).
Ouvrir la boîte de dialogue Filtre avancé avec les options Copier vers un autre emplacement et Extraction sans doublon cochées, Plages : $B$1:$B$8, rien dans la zone de critères, Copier dans : $I$1.
Mais bien entendu si l'on a des lignes en double, on peut parfaitement faire une exportation sans doublon.
VIII-B. Ce qu'il faut savoir dans l'exportation des doublons▲
Important : en ce qui concerne l'exportation, le filtre sur les doublons dépend des étiquettes de colonnes de la zone d'exportation.
Dans la table de données ci-dessous, nous avons trois enregistrements avec des doublons sur l'ensemble des champs à l'exception du champ voiture.
Observons le résultat de l'exportation sans doublon des femmes habitant un Studio dans les deux exemples ci-dessous.
Le premier est le résultat de l'exportation sur trois champs (H4:J4) , l'autre sur quatre (H11:K11).
IX. Quelques exemples▲
Utilisons d'autres données pour les exemples qui vont suivre.
IX-A. L'utilisation des caractères génériques▲
Comme dans les recherches d'Excel, il est possible d'utiliser les caractères génériques, astérisque (*) et point d'interrogation (?).
Petit rappel : le point d'interrogation (?) remplace un caractère quelle que soit sa valeur, l'astérisque (*) remplace 0 ou plusieurs caractères quelles que soient leurs valeurs. Si la valeur à filtrer contient un des caractères ? ou *, il faut les faire précéder du caractère ~.
Ainsi, filtrons les personnes dont le prénom contient la lettre L.
Cherchons les voitures dont la première lettre est inconnue, les deuxième et troisième lettres OL et quels que soient le nombre et la valeur des caractères qui suivent.
IX-B. Les champs vides et non vides▲
Dans la cellule contenant le filtre, saisissez :
- = ou "=" pour les enregistrements non remplis ;
- <> ou "<>" pour les enregistrements remplis.
Filtrons les personnes qui n'ont pas de voiture.
A contrario, filtrons maintenant ceux qui ont une voiture.
X. À savoir▲
Si on veut filtrer une chaîne exacte par exemple Marie, il y a lieu de faire précéder cette chaîne par l'apostrophe et l'opérateur logique d'égalité '=Marie ou ="=Marie" (exemple 2) afin de ne pas filtrer Marie-Tina comme le montre l'exemple 1.
Les filtres sur les chaînes de caractères ne sont pas sensibles à la casse. Pour pallier le problème il y a lieu d'utiliser un critère calculé.
XI. En conclusion, nous retiendrons▲
Les données peuvent être filtrées sur place ou exportées vers un autre emplacement.
Les étiquettes de la zone de critères doivent être identiques à celles de la table de données à l'exception des critères calculés.
Les critères placés sur une même ligne sont équivalents à un ET, les critères placés sur une deuxième ligne à un OU.
Ne pas englober une ligne vide dans la plage des critères.
Les étiquettes des critères calculés DOIVENT avoir un nom différent des étiquettes de la table de données.
Les critères sont des formules dont le résultat doit être VRAI ou FAUX (booléennes) et doivent concerner la première cellule de la colonne concernée.
Pour indiquer qu'on veut copier les données filtrées vers un autre emplacement, il faut cocher l'option Copier vers un autre emplacement et remplir la zone Copier dans avec :
- l'adresse de la première cellule de l'emplacement désiré si l'on exporte tous les champs (ex : $M$1) ;
- le nom des étiquettes des colonnes à exporter (avec la même orthographe !) et sélectionner ces étiquettes (ex : $M$1:$Q$1) ; les étiquettes peuvent être placées dans un ordre différent des étiquettes de la table de données.
Si on veut exporter vers une autre feuille, il faut procéder à l'envers : il faut exécuter le filtre à partir de la feuille de destination.
XI-A. Ce qu'il faut savoir▲
Dans la zone des critères, ne pas référencer la troisième ligne (la ligne OU) si celle-ci ne contient rien. Il en résulterait un filtrage erroné.
Se méfier des propositions faites par la boîte de dialogue. Il arrive que celle-ci vous propose des références erronées.
En effet l'outil Filtre élaboré crée des références nommées pour les options plage, exportation et critères qu'il utilise pour faire des propositions par l'intermédiaire de sa boîte de dialogue.
XII. VBA et les filtres élaborés▲
L'utilisation des filtres élaborés en VBA est gérée par la méthode AdvancedFilter de l'objet Range.
Cette méthode filtre ou copie des données à partir d'une liste basée sur une plage de critères.
XII-A. Syntaxe▲
expression . AdvancedFilter (Action, [CriteriaRange], [CopyToRange], [Unique])
Les arguments : à l'exception d'Action, tous les arguments sont facultatifs.
Action : Deux constantes de type xlFilterAction définissent le type d'action.
| Nom | Valeur | Description |
| xlFilterCopy | 2 | Copie les données filtrées vers un nouvel emplacement |
| xlFilterInPlace | 1 | Filtre les données en place |
CriteriaRange Zone de critères.
CopyToRangePlage de destination des lignes copiées si l'argument Action a comme valeur xlFilterCopy.
UniqueTrue pour filtrer exclusivement les enregistrements uniques et False pour filtrer tous les enregistrements répondant aux critères. La valeur par défaut est False.
XII-B. En pratique▲
Voyons quelques exemples de l'utilisation du code VBA avec le Filtre avancé.
XII-C. Premier exemple - Filtrer la liste sur place▲
Pour notre premier exemple, filtrons sur place la plage A1G16, selon les critères suivants : les dates comprises entre le 1/3/2011 et le 30/06/2011 et ayant des enfants.
Appelons cette procédure Filter.
Sub Filter()
Range("A1:G16").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("I1:L2"), _
Unique:=False
End SubL'illustration ci-dessous montre les arguments de la méthode AdvancedFilter en regard de la boîte de dialogue Filtre avancé.
XII-D. Deuxième exemple – Exporter la liste filtrée▲
Pour cet exemple, filtrons en copiant vers un autre emplacement.
Cette fois-ci nous emploierons comme critères deux cellules contenant chacune la fonction ET.
La procédure est nommée Export.
Sub Export()
Range("A1:G16").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("I1:I3"), _
CopyToRange:=Range("I15:M15"), _
Unique:=False
End Sub
XIII. Sky is the limit▲
Cette expression s'adapte bien à ce que l'on peut faire avec VBA quand on maîtrise bien Excel.
La feuille [ControlsFilters] illustre les innombrables possibilités qui s'offrent à toutes personnes voulant développer rapidement des exportations de tables de données sans modifier le code VBA, en utilisant les cellules d'Excel comme paramètres.
Pour profiter pleinement de cette procédure afin de la rendre utilisable quelle que soit la situation, il y a lieu de créer une procédure.
Les exemples présents dans cette feuille utilisent une fonction nommée ExportByFilter qui permet de passer comme arguments la table de données, la zone des critères et en option la référence à la zone d'export. Cette procédure renvoie le nombre de données filtrées et donc exportées.
Function ExportByFilter(znData As Range, znCriteria As Range, Optional znExport As Range) As Long
' Procédure d'exportation basée sur le filtre élaboré
' Author : Philippe Tulliez http://philippe.tulliez.be
' Valeur renvoyée : Nombre d'enregistrements exportés
' znData ' Table de données
' znCriteria ' Zone des critères
' [znExport] ' Zone d'exportation (si vide exporte tout en créant une feuille)
If znExport Is Nothing Then ' Création de la feuille d'export et coloration en rouge
Worksheets.Add before:=Sheets(1)
With Worksheets(1): ActiveCell = .Range("A1") : .Tab.Color = vbRed: End With
Set znExport = ActiveCell
End If
znData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=znCriteria, CopyToRange:=znExport
ExportByFilter = znExport.CurrentRegion.Rows.Count – 1
End FunctionCette procédure est présente dans le module mTutoAdvancedFilter qui accompagne le tutoriel.
Pour tester la création automatique de la feuille d'exportation, il suffit d'effacer une des données dans les cellules contenant les noms des plages d'exportation.
XIV. Fichier exemple▲
Ce tutoriel est accompagné d'un fichier exemple que vous pouvez télécharger en cliquant ici
XV. Remerciements▲
Merci à :
Magali, ma fille chérie pour son support technique ;
Mon ami Bob Ward pour sa relecture des premières versions de ce tutoriel et ses remarques toujours aussi pertinentes ;
Pierre Fauconnier et Thierry (Arkham46) pour leurs remarques bienveillantes, conseils techniques et encouragements ;
Claude Leloup pour sa relecture orthographique, typographique et ses remarques judicieuses.