Les filtres avancés ou élaborés dans Excel

Paramétrer et utiliser les filtres avancés

Par Philippe Tulliez

Date de publication : 9 décembre 2012

Outil puissant et finalement très peu connu par les utilisateurs le filtre élaboré permet de
filtrer des données avec plus de possibilités que le filtre simple dont on atteint très vite ses
limites.

En plus de filtrer les données sur place, il permet l'exportation de celles-ci vers une autre
feuille ou un autre classeur. Son exploitation en VBA offre de belles perspectives de
développement.

J'espère que la lecture de ce tutoriel vous permettra de le découvrir ou d'en apprendre plus
sur ses possibilités.

Commentez
 Les filtres avancés ou élaborés dans Excel par Philippe Tulliez

I - Introduction..............................................................................................................................................................3
I-A - Glossaire........................................................................................................................................................ 3
II - Étape préliminaire à la préparation d'un filtre avancé........................................................................................... 3
III - Comment activer le filtre élaboré..........................................................................................................................4
IV - Quelques exemples simples.................................................................................................................................4
IV-A - Premier exemple..........................................................................................................................................4
IV-B - Deuxième exemple...................................................................................................................................... 5
IV-C - Troisième exemple...................................................................................................................................... 6
IV-D - Quatrième exemple..................................................................................................................................... 6
V - Le filtre élaboré avec plusieurs critères................................................................................................................ 7
VI - Les critères calculés............................................................................................................................................. 7
VI-A - Exemples de critère calculé........................................................................................................................ 7
VI-B - Pour bien comprendre le principe du critère calculé...................................................................................9
VII - Exporter les données filtrées...............................................................................................................................9
VII-A - Exportation de toutes les données filtrées............................................................................................... 10
VII-B - Exportation partielle des données filtrées................................................................................................ 10
VII-C - Exportation vers une autre feuille que la table de données.....................................................................11
VIII - Exportation sans doublon................................................................................................................................. 11
VIII-A - Principe.................................................................................................................................................... 11
VIII-B - Ce qu'il faut savoir dans l'exportation des doublons............................................................................... 12
IX - Quelques exemples............................................................................................................................................ 12
IX-A - L'utilisation des caractères génériques..................................................................................................... 13
IX-B - Les champs vides et non vides.................................................................................................................14
X - À savoir................................................................................................................................................................15
XI - En conclusion, nous retiendrons........................................................................................................................ 16
XI-A - Ce qu'il faut savoir.....................................................................................................................................16
XII - VBA et les filtres élaborés.................................................................................................................................16
XII-A - Syntaxe..................................................................................................................................................... 17
XII-B - En pratique............................................................................................................................................... 17
XII-C - Premier exemple - Filtrer la liste sur place.............................................................................................. 17
XII-D - Deuxième exemple – Exporter la liste filtrée................................................................................ 18
XIII - Sky is the limit.................................................................................................................................................. 18
XIV - Fichier exemple................................................................................................................................................ 19
XV - Remerciements..................................................................................................................................................19

-2-
Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée
par les droits d'auteur. Copyright ® 2012 Philippe Tulliez. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc.
sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à
Developpez LLC.
http://philippetulliez.developpez.com/tutoriels/advancedfilter/
 Les filtres avancés ou élaborés dans Excel par Philippe Tulliez

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.

1 La table de données doit avoir en première ligne les étiquettes de colonne.

2 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.

re
Déplaçons ce tableau jusqu'à la ligne 5 et copions les étiquettes de ligne de ce tableau sur la 1 ligne de la feuille
Excel.

-3-
Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée
par les droits d'auteur. Copyright ® 2012 Philippe Tulliez. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc.
sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à
Developpez LLC.
http://philippetulliez.developpez.com/tutoriels/advancedfilter/
 Les filtres avancés ou élaborés dans Excel par Philippe Tulliez

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.

-4-
Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée
par les droits d'auteur. Copyright ® 2012 Philippe Tulliez. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc.
sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à
Developpez LLC.
http://philippetulliez.developpez.com/tutoriels/advancedfilter/
 Les filtres avancés ou élaborés dans Excel par Philippe Tulliez

• 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
er
supérieure ou égale au 1 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.

-5-
Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée
par les droits d'auteur. Copyright ® 2012 Philippe Tulliez. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc.
sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à
Developpez LLC.
http://philippetulliez.developpez.com/tutoriels/advancedfilter/
 Les filtres avancés ou élaborés dans Excel par Philippe Tulliez

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).

-6-
Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée
par les droits d'auteur. Copyright ® 2012 Philippe Tulliez. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc.
sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à
Developpez LLC.
http://philippetulliez.developpez.com/tutoriels/advancedfilter/
 Les filtres avancés ou élaborés dans Excel par Philippe Tulliez

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.

-7-
Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée
par les droits d'auteur. Copyright ® 2012 Philippe Tulliez. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc.
sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à
Developpez LLC.
http://philippetulliez.developpez.com/tutoriels/advancedfilter/
 Les filtres avancés ou élaborés dans Excel par Philippe Tulliez

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.

-8-
Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée
par les droits d'auteur. Copyright ® 2012 Philippe Tulliez. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc.
sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à
Developpez LLC.
http://philippetulliez.developpez.com/tutoriels/advancedfilter/
 Les filtres avancés ou élaborés dans Excel par Philippe Tulliez

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.

-9-
Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée
par les droits d'auteur. Copyright ® 2012 Philippe Tulliez. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc.
sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à
Developpez LLC.
http://philippetulliez.developpez.com/tutoriels/advancedfilter/
 Les filtres avancés ou élaborés dans Excel par Philippe Tulliez

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.

- 10 -
Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée
par les droits d'auteur. Copyright ® 2012 Philippe Tulliez. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc.
sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à
Developpez LLC.
http://philippetulliez.developpez.com/tutoriels/advancedfilter/
 Les filtres avancés ou élaborés dans Excel par Philippe Tulliez

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.

- 11 -
Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée
par les droits d'auteur. Copyright ® 2012 Philippe Tulliez. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc.
sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à
Developpez LLC.
http://philippetulliez.developpez.com/tutoriels/advancedfilter/
 Les filtres avancés ou élaborés dans Excel par Philippe Tulliez

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.

- 12 -
Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée
par les droits d'auteur. Copyright ® 2012 Philippe Tulliez. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc.
sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à
Developpez LLC.
http://philippetulliez.developpez.com/tutoriels/advancedfilter/
 Les filtres avancés ou élaborés dans Excel par Philippe Tulliez

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.

- 13 -
Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée
par les droits d'auteur. Copyright ® 2012 Philippe Tulliez. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc.
sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à
Developpez LLC.
http://philippetulliez.developpez.com/tutoriels/advancedfilter/
 Les filtres avancés ou élaborés dans Excel par Philippe Tulliez

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.

- 14 -
Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée
par les droits d'auteur. Copyright ® 2012 Philippe Tulliez. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc.
sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à
Developpez LLC.
http://philippetulliez.developpez.com/tutoriels/advancedfilter/
 Les filtres avancés ou élaborés dans Excel par Philippe Tulliez

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é.

- 15 -
Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée
par les droits d'auteur. Copyright ® 2012 Philippe Tulliez. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc.
sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à
Developpez LLC.
http://philippetulliez.developpez.com/tutoriels/advancedfilter/
 Les filtres avancés ou élaborés dans Excel par Philippe Tulliez

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.

- 16 -
Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée
par les droits d'auteur. Copyright ® 2012 Philippe Tulliez. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc.
sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à
Developpez LLC.
http://philippetulliez.developpez.com/tutoriels/advancedfilter/
 Les filtres avancés ou élaborés dans Excel par Philippe Tulliez

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 Sub

L'illustration ci-dessous montre les arguments de la méthode AdvancedFilter en regard de la boîte de dialogue Filtre
avancé.

- 17 -
Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée
par les droits d'auteur. Copyright ® 2012 Philippe Tulliez. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc.
sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à
Developpez LLC.
http://philippetulliez.developpez.com/tutoriels/advancedfilter/
 Les filtres avancés ou élaborés dans Excel par Philippe Tulliez

XII-D - Deuxième exemple &#8211; 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.

- 18 -
Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée
par les droits d'auteur. Copyright ® 2012 Philippe Tulliez. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc.
sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à
Developpez LLC.
http://philippetulliez.developpez.com/tutoriels/advancedfilter/
 Les filtres avancés ou élaborés dans Excel par Philippe Tulliez

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&#160;: 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")&#160;: .Tab.Color = vbRed: End With
Set znExport = ActiveCell
End If
znData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=znCriteria, CopyToRange:=znExport
ExportByFilter = znExport.CurrentRegion.Rows.Count &#150; 1
End Function

Cette 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.

- 19 -
Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée
par les droits d'auteur. Copyright ® 2012 Philippe Tulliez. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc.
sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à
Developpez LLC.
http://philippetulliez.developpez.com/tutoriels/advancedfilter/