Les fichiers exemples associés à cette page (zippés) sont à télécharger ici (téléchargé 12725 fois).

Introduction

Vous avez une base de données et vous voulez en extraire des fiches suivant certains critères ? Si ceux-ci sont assez simples, le filtre automatique (menu données/filtre/filtre automatique) peut faire l'affaire. Mais si vous avez des critères complexes ou calculés suivant la valeur d'une cellule, ou que plusieurs critères doivent être vérifiés en même temps, vous atteindrez vite les limites du filtre automatique. Le filtre élaboré vous donne une plus grande puissance même s'il est plus complexe à mettre en oeuvre. Pour utiliser un filtre élaboré, vous pouvez soit trier la liste sur place (dans la même feuille), soit la trier sur une autre feuille. La première façon de faire est assez bien expliquée dans l'aide d'excel, nous nous concentrerons donc uniquement sur la seconde. Pour démarrer il nous faut une table de données toute simple avec juste en première ligne des noms de champs et des valeurs dans les colonnes. Cette structure est identique à celle utilisée pour démarrer un tableau croisé dynamique.

Utilisation en mode manuel

Un filtre élaboré a besoin de trois éléments :

Une base de données

Nous avons vu que la base de données n'a rien de particulier. Simplement pour faciliter l'utilisation du filtre, il est conseillé de nommer cette zone (menu insertion/nom/définir). Ceci facilitera les fitrages ultipes sur la base.
Une petite astuce : pour éviter de devoir renommer cette base de données à chaque ajout d'une ligne, utilisez la fonction décaler de façon à ce que le nom s'ajuste automatiquement à l'ensemble de la plage. Regardez dans le fichier exemple, dans menu insertion/ nom/définir, la formule associée à zonebdd.

Une zone de critères

Dans cette zone, on va définir les critères de sélection. Cette zone doit être positionnée au dessus de la zone d'extraction.

Le principe de base est le suivant :
  • Si les critères sont écrits sur une même ligne, seules les fiches correspondant à TOUS les critères seront sélectionnées Autrement dit les critères sont liés entre eux par le ET logique
  • Si les critères sont écrits en colonne, les fiches correspondant à l'un des critères seront sélectionnées dans ce cas, les critères sont liés par le OU logique
Une zone de critère comprend au minimum deux cellules situées l'une en dessous de l'autre. Celle du dessus reçoit le champ de critère celle du dessous la valeur du critère. Par exemple

Si vous voulez sélectionner les desserts cuisant en plus de 15 minutes mais en moins d'une heure, vous écrirez

type plat temps cuisson temps cuisson
dessert >15 <60


Si vous voulez sélectionner tous les plats qui se cuisent au four OU sans cuisson les critères seront présentés ainsi :
type cuisson
four
sans cuisson


Pour sélectionner tous les plats qui se cuisent au four OU dont le temps de préparation est inférieur à 15 min :
temps cuisson type cuisson
<15  
  four



Comment utiliser un mot comme critère ?
Si dans votre table vous avez un champ comportant du texte, vous pouvez vouloir rechercher le texte exact ou un mot parmi d'autres.
Pour rechercher une expression exacte, disons "coucou le hibou" texte correspondant à la totalité du contenu de la cellule, mettez comme critère : ="=coucou le hibou".

Pour sélectionner les fiches contenant un texte quelconque avant hibou, le critère sera : ="* hibou" et ="coucou *" pour trouver coucou suivi de n'importe quel texte.
On peut également utiliser un critère calculé :
Notre table de données comporte un champ date de type jj/mm/aa. Comment trier toutes les fiches de 2003 ?
Ici, on veut extraire les années à partir des dates de la colonne A:A de la base de données Le critère est défini à partir de la première cellule de données de la table, sans tenir compte des étiquettes de champ, donc ici bdd!A2 si les dates sont dans la colonne A.
Dans la zone de critère, on laisse vide la cellule dans laquelle est normalement écrit le nom du champ d'extraction (A1). En dessous (par exemple en A2), on entre le critère :
=ANNEE(bdd!A2)=2003
Ce lui-ci commence toujours par un signe "=".
Dans la cellule A2, vous verrez apparaître VRAI ou FAUX. Ne vous en occupez pas, c'est sans incidence sur le résultat final.
N'oubliez pas de préciser le nom de la feuille de la base de données (bdd)...

Pour rechercher une chaine de caractère dans une autre, disons le mot "hibou" , vous pouvez aussi utiliser une fonction dans votre critère : =trouve("hibou";bdd!B2) si le champ texte est dans la colonne B.

Une zone d'extraction

Un des gros intérêts des fltres élaborés est de permettre une réorganisation des tables. On peut bien sur extraire l'ensemble des infos pour une fiche, c'est à dire toutes les colonnes. Mais on peut aussi choisir de n'afficher que quelques données. On peut également présenter les colonnes dans un ordre différent de celui de la table de données initiale.

Un point important : il est indispensable qu'il y ait une ligne vide au dessus de la zone d'extraction.

La première ligne de la zone d'extraction comporte l'intitulé des champs. Ceux-ci doivent être absolument identiques à ceux de la table de données, mais pas nécessairement dans le même ordre. Un copier coller évite bien des erreurs...
Laissez vide la zone en dessous de cette ligne.

Lancer le filtre

Cliquez dans la feuille destinée à recevoir les fiches filtrées, en dehors de la zone de critères et pas juste en dessous de la première ligne de la zone d'extraction. Allez dadns le menu données/filtrer/filtre élaboré.
Remplissez la boite de dialogue comme ci-dessous. Notez que vous pouvez en prime supprimer des doublons éventuels en cochant la case prévue.



Dans la plage de données, vous pouvez soit entrer "en dur" l'adresse de la table de données soit l'appeller par son nom, ici : zonebdd.
La zone de critère doit inclure tous vos critères. Là encore si vous répétez des filtrages sur la même table, il est plus rapide d'utiliser des plages nommées. Si vous ne cochez pas la case "copier vers un autre emplacement", la table sera triée sur place. Mais attention dans ce cas, les différentes zones doivent être disposées différemment. Reportez vous à l'aide d'excel (tapez filtre élaboré dans l'index de l'aide).
Vous trouverez sur la page filtres et tris d'excelabo différentes façon de travailler sur les données filtrées.

Les filtres élaborés en VBA

Pour utiliser les filtres élaborés en VBA, le principe est très similaire à ce qui est décrit ci dessus bien sur. Vous pouvez d'ailleurs tout simplement lancer l'enregistreur de macros pour en récupérer le code.
Vous pouvez cependant avoir besoin de construire des critères complexes par macro et d'utiliser seulement ce critère composé pour filtrer votre table.
dans ce cas, la zone de critère ne comprendra que deux cellules l'une en dessous de l'autre comme A1 et A2.
Comme dans le cas d'un critère calculé, la cellule A1 doit rester vide. Le critère sera écrit en A2.

Composition du critère

Comme pour un critère calculé, le critère doit commencer par le signe "=".
Pour que les lignes filtrées répondent à deux critères en même temps, il faut multilier les critères. Il faut les additionner si les lignes doivent répondre à l'un OU l'autre des critères.
=(bdd!F2="1 toque") * (bdd!G2="Bon marché")
Il vous suffit ensuite par VBA d'écrire cette formule dans la cellule A2 :
range("filtre!A2").value= "=(bdd!F2="1 toque") * (bdd!G2="Bon marché")" ou encore : [A2] = "=(bdd!F2="1 toque") * (bdd!G2="Bon marché")"

Si le critère est écrit correctement, dans la cellule A2, vous devez voir écrit "VRAI" ou "FAUX". Le filtre élaboré lui même est lancé en VBA, après voir sélectionné la feuille de destination des fiches filtrées par l'instruction :
Range("zonebdd").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("A1:A2"), CopyToRange:=Range("A4:O4"), Unique:=False

Pour rechercher une chaine de caractère dans une autre, utilisez la fonction find ("trouve" en VBA) dans votre critère : Affectez par exemple l'expression à trouver à une variable "MaVariable" puis écrivez dans votre code [A2].Formula = "=(find(""" & MaVariable & """,bdd!B2))" si le champ texte est dans la colonne B de la page bdd.

Cette page a été vue 48871 fois.