Filtres élaborés

Les filtres élaborés pas à pas : extraire des données suivant plusieurs critères.

Vous pouvez télécharger un exemple associé à ce tutoriel.

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 des critères résultant d'un calcul, 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. Si vous utilisez la version 2003 ou plus récente d'excel, vous avez tout intérêt à mettre ces données sous forme de lkistes (2003) ou de tableau poiur les versions suivantes (onglet accueil/style/mettre sous forme de tableau).

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 la mettre sous forme de tableau ou, pour les versions antérieures à 2003, de nommer cette zone (menu insertion/nom/définir). Ceci facilitera les fitrages multipes sur la base.


Si vous utilisez un tableau (ou une liste avec excel 2003), toutes les modifications que vous faites sur les données (ajout ou suppression) sont automatiquement prises en compte. Vous n'avez donc pas besoin de définir un nom dynamique, c'est déjà fait ! En revanche, si vous utilisez une version antérieure à 2003, il est conseillé d'utiliser la fonction decaler pour définir un nom pour la base de données qui s'ajuste à votre nombre de données.

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

Comment faire un filtre élaboré avec extraction sur une autre feuille ?

Pour faire un filtre élaboré avec extraction sur une autre feuille, on peut le faire, même sans VBA.
La base de données est dans la feuille 1, on veut extraire sur la feuille 2.

Dans la feuille 2, sélectionner une cellule vide dans une zone de la feuille capable de contenur toutes les données extraites.
Depuis cette feuille, lancez la commande de filtre élaboré. IL ne faut PAS lancer cette commande en étant sur la page contenant la base de données mais obligatoirement depuis celle qui reçoit les données extraites.

Sélectionnez la plage Source de données qui est sur la feuille 1, la plage de critère, la cellule qui sera celle en haut et à gauche de votre zone d'extraction puis lancez le filtre.

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.

Auteur(s) : 

Commentaires

Mon problème avec cette option est qu'Excel ne colle pas les éléments extraits sans doublon sur la ligne correspondant à la 1ère occurrence de chaque valeur. J'ai donc dans TCD des difficultés pour la suite quelqu'un peut-il m'aider à ranger les valeurs extraites sans doublons

Merci pour aide

Bonjour,

Avec quelle option ? (et au passage quelle version d'excel)
Quand vous faites une extraction sans doublon sur une nouvelle plage, vous pouvez extraire toute la base. Il n'y a pas alors d'histoire de ligne correspondant à la première occurrence de chique valeur. Par définition si c'est sans doublon il n'y a qu'une occurrence de chaque valeur dans le tableau de sortie qui peut sans problème être utilisé comme source pour un TCD. Si cette réponse ne vous éclaire pas, je vous propose de poser cette question sur le forum d'excel-downloads.com en y joignant un classeur qui illustre votre question (sans données confidentielles surtout !)


Ce mois-ci sur Excelabo

- Pas de nouvelle page.
- 7 pages modifiées.