Filtre élaboré

Avant d'utiliser les filtres élaborés, apprenez à fond le filtre automatique.
Vous pouvez créer des filtres complexes et afficher le résultat dans la liste elle-même ou l'afficher dans une nouvelle feuille. À mon point de vue, le filtre élaboré affiché dans la liste elle-même n'est intéressant que pour des filtres basés sur plus de 2 conditions pour un même champ ou contenant des champs calculés. Par contre, l'affichage dans une autre feuille est de grand intérêt, même lorsque les critères sont simples ou nuls. En effet vous pouvez ainsi, à partir d'une liste, créez une autre liste dont les champs sont réorganisés et dont vous pouvez supprimer des champs.
Le principe du filtre élaboré repose sur 3 plages, la plage contenant la liste à élaborer, la plage contenant les critères et la plage de destination.
La plage de critère comprend une ligne avec les noms de champs (sur lesquels vous établissez les critères) et au minimum 1 ligne de critère. Les noms de champ doivent être exactement les mêmes que ceux de la liste, mis à part les champs calculés, sinon votre filtre génère une erreur. Elle peut ne pas exister, dans le cas où vous ne voulez recopier que quelques colonnes de la liste sur une autre feuille, tout en conservant toutes les lignes. Les critères de comparaison sont un ensemble de conditions utilisé pour rechercher des données. Ces critères peuvent être une série de caractères pour laquelle vous recherchez des correspondances, telle que "CHAPERON" ou une expression, telle que ">300".
La plage de destination est une ligne comprenant uniquement des noms de champ, ceux-ci doivent être exactement les mêmes que ceux de la liste, sinon votre filtre génère une erreur. La plage de destination peut être la ligne de titre de la liste.

Affichage dans la liste

 Votre feuille de calcul doit contenir au moins trois lignes vides au-dessus de la liste, en effet, il faut une ligne pour les champs de critère, une ou plusieurs pour les critères et enfin une ligne vide pour séparer les critères et la liste. En général, insérez 9 lignes avant la liste, les titres de celle-ci sont donc sur la ligne 10 de la feuille.
Pour nous aider dans la compréhension, nous prendrons l'exemple suivant.

• Copiez les champs de titre de la liste (ligne 10) qui vont être filtré. Ici vous copiez le champ "Famille".
• Collez-les dans la première ligne de la plage de critères (ligne 1).
• Dans les lignes situées sous les champs de critère, tapez les critères de comparaison. Veillez à laisser au moins une ligne vide entre les valeurs des critères et la liste. Ici vous tapez le nom de 4 familles dans la ligne 2 sous le champ "Famille". Pour éviter les erreurs, vous pouvez copier les valeurs dans la liste, puis les coller dans la zone de critère.

Notre préparation de filtre est établie, nous demandons donc ainsi, les lignes dont la famille sont égales à celles de la plage de critère.
Pour afficher ces lignes :

• Cliquez sur une cellule de la liste. La cellule A11 par exemple
• Données/Filtre/Filtre élaboré. La boîte de dialogue suivante apparaît. Depuis 2007, vous trouvez cette commande dans l'onglet Données, groupe Trier et Filtrer et se nomme Avancé.
  
• Cochez l'option Filtrer la liste sur place.
• Cliquez dans la zone Zone de critères.
• Sélectionnez avec la souris la plage de critères, soit ici la plage B1:B5. Si vous ne la voyez pas, utilisez le bouton , puis pour revenir le bouton .
• Cliquez sur Ok.

Maintenant vous pouvez recommencer ces dernières étapes, pour filtrer différemment la liste, en modifiant par exemple le nom des familles à afficher. Si la plage de critère a toujours la même adresse, vous n'aurez pas à renseigner de nouveau la Zone de critères.
Dans le cas où vous effectuez régulièrement des filtres élaborés, je vous conseille d'ajouter la commande (situé dans la catégorie Données) sur une barre d'outils et lui créer une image.

Affichage sur une autre feuille.

Nous reprenons l'exemple précédent.

• La liste doit être nommée. Je vous conseille donc de la commencer sur la ligne 1 de votre feuille. Sélectionnez les colonnes correspondant à votre liste, ici les colonnes A à H. Cliquez dans la zone Nom de la barre de formules. Tapez un nom, "BaseDonnees" ou "BD" par exemple. Si votre liste ne commence pas à la ligne 1, Insertion/Nom/Définir, sinon passez à l'étape suivante. Sélectionnez le nom de votre liste. Dans la zone Fait référence à, tapez l'adresse de votre liste depuis la 1^ère ligne de celle-ci jusqu'à la ligne 65536. Par exemple, dans la figure, l'adresse serait donc au départ : ='nom de la feuille'!$A:$H et doit devenir ='nom de la feuille'!$A$10:$H$65536. cliquez sur Ajouter, puis Fermer.
• Insérez une nouvelle feuille, Insertion/Feuille, si elle n'existe pas déjà, et renommez-la.
• Copiez la ligne de titre de la liste, ici la plage A1:H1.
• Collez la dans la cellule A10 de votre nouvelle feuille.
• Supprimez les champs que vous ne désirez pas afficher et réorganisez les éventuellement. Cf. en exemple la figure.
• Copiez les champs de titre de la liste (ligne 1) qui vont être filtré. Ici vous copiez le champ "Famille".
• Collez-les dans la première ligne de la plage de critères (ligne 1 de la nouvelle feuille).
  
  Dans les lignes situées sous les champs de critère, tapez les critères de comparaison. Veillez à laisser au moins une ligne vide entre les valeurs des critères et la liste. Ici vous tapez le nom de 4 familles dans la ligne 2 sous le champ "Famille". Pour éviter les erreurs, vous pouvez copier les valeurs dans la liste, puis
• Dans les lignes situées sous les champs de critère, tapez les critères de comparaison. Veillez à laisser au moins une ligne vide entre les valeurs des critères et la liste. Ici vous tapez le nom de 4 familles dans la ligne 2 sous le champ "Famille". Pour éviter les erreurs, vous pouvez copier les valeurs dans la liste, puis les coller dans la zone de critère.

Notre préparation de filtre est établie, nous demandons donc ainsi, les lignes dont la famille est égale à celles de la plage de critère.
Pour afficher ces lignes :

• Si aucune donnée se trouve dans la 1^ère ligne sous la plage de destination de la nouvelle feuille, insérez n'importe quel caractère dans la 1^ère cellule. Ici par exemple, insérez "a" dans la cellule A11. Cela vous paraît peut-être idiot, mais Excel nous l'impose pour établir le filtre sans problème.
• Sélectionnez sur une cellule de la plage de destination. La cellule A10 par exemple
• Données/Filtre/Filtre élaboré. La boîte de dialogue de la figure apparaît. Depuis 2007, vous trouvez cette commande dans l'onglet Données, groupe Trier et Filtrer et se nomme Avancé.
• Cochez l'option Copier vers un autre remplacement
• Cliquez dans la zone Plages et sélectionnez tout.
• Appuyez sur F3.
• Dans la boîte Coller un nom, sélectionnez le nom de votre liste. "BD" dans notre exemple.
• Cliquez dans la zone Zone de critères.
• Sélectionnez avec la souris la plage de critères, soit ici la plage A1:A5. Si vous ne la voyez pas, utilisez le bouton , puis pour revenir le bouton .
• Cliquez dans la zone Destination.
• Sélectionnez avec la souris la plage de destination, soit ici la plage A10:F10. Si vous ne la voyez pas, utilisez le bouton , puis pour revenir le bouton .
• Cliquez sur Ok.

Lorsque vous effectuerez de nouveau le filtre élaboré, n'oubliez pas d'insérer le nom de votre liste dans la zone Plages de la boîte Filtre élaboré.
Pour réorganiser une liste et/ou en extraire quelques champs, il suffit de procéder comme ci-dessus, mais de ne rien remplir dans la Zone de critères.
Il est bien important de comprendre que cette nouvelle liste n'est pas liée automatiquement à votre liste de départ. Si vous ajoutez des enregistrements dans cette liste "base de données", vous devez effectuer de nouveau le filtre pour mettre à jour la liste filtrée.

Les critères de filtre élaboré peuvent comprendre plusieurs conditions appliquées à une seule colonne, plusieurs critères appliqués à plusieurs colonnes et des conditions créées par le calcul d'une formule.

• Le principe des lignes et des colonnes dans une plage de critères est le suivant :

  • Les critères se trouvent sur une même ligne, les enregistrements de la liste doivent répondre à toutes ces conditions.
  • Les critères se trouvent dans une même colonne, les enregistrements de la liste doivent répondre à au moins une de ces conditions.

 

• Utilisation de plusieurs conditions dans une seule colonne :
  Si vous voulez appliquer au moins 4 conditions à une seule colonne, tapez les critères directement les uns au-dessous des autres, comme dans la figure. Dans ces conditions, les lignes contenant l'une de ces familles seront affichées.
  
• Utilisation de critères dans 2 colonnes ou davantage :
• Pour rechercher des données qui remplissent une condition dans plusieurs colonnes, tapez tous les critères dans la même ligne de la plage de critères, comme dans la figure. Dans ces conditions, seules les lignes contenant la famille "APPUI BAIE" et le produit "APP.BAI.30x15x1.8 RGE" seront affichées.
  
• Vous pouvez également utiliser le même champ pour les 2 colonnes, cf. figure. Dans ces conditions, seules les lignes dont la date est supérieure ou égale au 01/12/1999 et dont la date est inférieure au 01/01/2000, soit les dates du mois de décembre 1999, seront affichées.
  

• Utilisation de critères dans 2 colonnes, ou davantage, et 2 ou plusieurs lignes :
  • Pour rechercher des données qui remplissent soit une condition dans une colonne, soit une condition dans une autre colonne, tapez les critères dans des lignes différentes de la plage de critères, cf. figure. Dans ces conditions, seules les lignes contenant la famille "APPUI BAIE" (même si le groupe est différent de 3) et les lignes contenant le groupe "3" (même si la famille est différente de "APPUI BAIE") seront affichées.
    
  • Pour rechercher des lignes qui remplissent une des deux conditions dans une colonne et une des deux conditions dans une autre colonne, tapez les critères dans des lignes distinctes, cf. figure. Dans ces conditions, seules les lignes contenant la famille "APPUI BAIE" et contenant le groupe "3", ainsi que les lignes contenant la famille "APPUI PLEIN" et contenant le groupe "3" seront affichées.
    
• Types de critères de comparaison
  • Pour rechercher une valeur exacte, les critères doivent être une valeur se trouvant exactement dans un des enregistrements.

  • Pour rechercher des valeurs de texte qui contiennent certains caractères mais pas d'autres, utilisez un caractère générique. Celui-ci représente un ou plusieurs caractères non spécifiés.

    Pour rechercher	Utilisez le caractère	Exemple
    Tout caractère unique qui occupe le même emplacement que le point d'interrogation	? (point d'interrogation)	p?rt trouve « port » et « part »
    Un nombre quelconque de caractères qui occupent le même emplacement que l'astérisque	* (astérisque)	*Est trouve « Nord-Est » et « Sud-Est »
    Un point d'interrogation, un astérisque ou un tilde	~ (tilde) suivi de ?, *, ou ~	fy91~? trouve « fy91? »

    Ainsi dans l'exemple suivant, le filtre affiche tous les enregistrements dont la famille commence par les lettres A, B ou C.

  • Pour afficher uniquement les lignes dont les valeurs sont comprises entre certaines limites, tapez un opérateur de comparaison (=, <>, <, <=, >, >=) suivi d'une valeur dans la cellule située en dessous de l'étiquette de critère, exemple dans la figure .
    

Vous pouvez utiliser comme critère une valeur calculée par une formule. Dans ce cas, vous devez respecter les règles suivantes :

• N'utilisez pas le nom d'un champ de la liste comme étiquette de critère. Vous pouvez soit laisser l'étiquette de critère vide, soit utiliser un nom complètement différent des champs de la liste.
• La formule doit toujours être une formule logique et donc renvoyer uniquement les valeurs VRAI ou FAUX.
• La formule doit se référer à au moins un champ de la liste.

La référence au champ peut se faire par le nom du champ ou par l'adresse de la 1^ère cellule de la colonne correspondante, préférez ce dernier cas car il vous permet de vérifier votre formule.
Pour apprendre à établir un champ calculé, nous allons utiliser l'exemple suivant :

• Dans la cellule d'étiquette du critère (B1), vous indiquez le nom "Mois à afficher".
• Nous cherchons à afficher les enregistrements d'un mois précis. Dans la cellule A2, vous tapez le numéro du mois à afficher, 2 par exemple pour le mois de février.
• Dans la cellule de critère (B2), vous tapez la formule. Celle-ci commence donc par =. La formule est ici =MOIS(A1)=A2, dans la cellule A2 vous avez le numéro de mois à rechercher et la cellule A12 correspond à l'adresse de la 1^ère cellule de la colonne de recherche. La fonction MOIS donne le numéro du mois des enregistrements.
• Validez la cellule. Elle affiche FAUX ou VRAI, selon la donnée dans A12.

Afficher un choix ou tout

Dans une cellule, j'ai une liste déroulante de choix et en fonction de ce choix je veux élaborer un critère me permettant soit d'afficher la donnée choisie, soit tout prendre si la cellule est vide. Comment faire ?

Pour l'exemple, je vais également prendre 2 champs : 1 champ texte (nommé Acheteur) et un champ numérique (nommé Type). J'ai également 2 cellules ayant une liste déroulante de choix et nommée respectivement Choix_Acheteur et Choix_Type.

Dans ma plage de critère, je tapes les noms des champs en A1 (Acheteur) et B1(Type), puis en A2 la formule suivante =SI(Choix_Acheteur="";"*";Choix_Acheteur) et B2 celle-ci :

=SI(Choix_Type="";"<>0";CNUM(Choix_Type).

Ces formules avec un SI me permettent de récupérer le choix si la cellule n'est pas vide, dans le cas contraire, elle renvoit tout ce qui est diffférent de 0 (pour les nombres) et dans l'autre cas tout ce qui est vide ou non : * (voir ci-dessus).

Afficher tout les vides ou non vides

Reprenons notre exemple, mais cette fois-ci nous voulons afficher tous les enregistrements dont le type est vide. il vous faut recourir à un critère calculé :

1. le titre du critère ne doit plus correspondre à un des champs de la source, je le renomme donc TYPE VIDE
2. la formule pour le calcul est :=ESTVIDE(F2). F2 étant le 1er enregistrement du champ Type dans la source

Maintenant je veux afficher les enregistrements non vide, j'applique les mêmes principes mais cette fois-ci la formule est :=NON(ESTVIDE(F2))

Et maintenant si je veux tout avoir en critère calculé, ma formule sera une combinaison des deux : =OU(NON(ESTVIDE(F2));ESTVIDE(F2))