Comment utiliser des filtres élaborés ou avancés dans Excel ?

Dans cet article de la formation Excel, vous allez découvrir comment utiliser les filtres avancés ou les filtres élaborés dans Excel, cette fonctionnalité qui englobe tous ce que nous avons vus dans les dernières leçons de filtrage des données, mais cette fois en utilisant des techniques avancées.
filtres élaborés

Pour essayer de vous éclaircir l’utilisation de ces filtres élaborés, suivez avec moi ces exemples.

Préparez d’abord vos outils de travail

Avant de créer des filtres élaborés, vous allez donc utiliser :
Tableau de données

  • L’outil filtre avancé qui se trouve dans le groupe Trier et filtrer sous l’onglet Données.
Outil Filtre avancé

  • Et une zone de critère où vous allez introduire vos critères de filtres.
Zone de critères


Au lieu d’utiliser donc la zone de recherche ou les menus de filtres comme nous l’avons déjà vu (Voir Filtres textuels, Filtres numériques et Filtres chronologiques), vous allez utiliser une zone de critères pour entrer vos critères de filtres.

Créer un filtre élaboré simple

Dans l’exemple suivant, vous avez un tableau de données que vous allez filtrer et qui a des étiquettes : Nom, Date de naissance, ville et Mt réalisé.
Au-dessus de ce tableau, laissez quelques lignes vides (par exemple 5 lignes) et copiez collez les mêmes étiquettes de votre tableau. Vous venez donc de créer une zone de critères.
C’est une étape très nécessaire avant de commencer à créer vos filtres élaborés.

Création d'une zone de critères


Note: Les noms des étiquettes dans la zone de critères doivent être pareilles à celles de votre tableau de données. 

Maintenant, vous allez créer votre premier exemple de filtre élaboré:
  • Supposons que vous voulez afficher les noms des vendeurs appartenant à la ville de Paris.
  • Sélectionnez la cellule en dessous de l’étiquette Ville dans la zone de critères puis tapez Paris.
Critère de filtre élaboré

  • Sélectionnez après, une cellule de votre tableau de données puis cliquez sur la commande Avancé dans le groupe Trier et filtrer sous l’onglet Données.
  • La boîte de dialogue Filtre Avancé s’affiche.
  • Dans la zone Plages, Excel affiche la référence de votre tableau et vous voyez que ce dernier est sélectionné.
Définir la plage de cellules filtre avancé

  • Si ce n’est pas le cas, cliquez dans la zone Plages et supprimez son contenu puis sélectionnez toutes les cellules de votre tableau.
Sélectionner la plage de cellules dans filtre avancé

  • Cliquez après dans la zone de critères puis sélectionnez la zone de critères que vous avez créée et en incluant le critère « Paris »
Sélectionnez zone de critères

  • Voilà, maintenant il ne vous reste qu’à choisir où vous voulez afficher le résultat de cette action :
    • Filtrer la liste sur place : c’est-à-dire dans votre tableau.
Filtrer sur Place

    • Copier vers un autre emplacement : vous allez copier les données filtrées vers une nouvelle plage de cellules ou vers une autre feuille de calcul, et vous remarquerez que l’option Copier dans est activée pour vous permettre de sélectionner votre emplacement souhaité.
Filtrer vers un emplacement

  • La dernière option Extraire sans doublons, vous permet d’éliminer les lignes en double après avoir appliqué le filtre des données.
  • Cliquez enfin sur OK pour afficher le résultat:
Résultat filtre élaboré

  • Pour annuler un filtre élaboré, cliquez sur Effacer sous l'onglets Données dans le groupe Trier et filtrer.


Créer un filtre avancé en combinant deux critères ou plus

  • Si vous aimez par exemple afficher les noms des vendeurs qui sont à Paris et qui ont réalisé un Montant supérieur à 1000 euros, écrivez sous l’étiquette Ville: Paris, et sous l’étiquette Mt réalisé:  >1000 sur la même ligne.
Combiner deux critères

  • Cliquez sur votre tableau puis cliquez sur Avancé dans le groupe Trier et filtrer.
  • Définissez les options Plages et Zone de critères puis cliquez sur OK.
  • Voici le résultat.
Résultat filtre élaboré avec deux critères combinés

Vous constatez donc qu’Excel a combiné les deux critères Paris et >1000 pour filtrer les données comme vous l’avez fait en choisissant l’option ET dans la fenêtre Filtre automatique personnalisé. (Reportez-vous aux dernières leçons de filtrages).

Appliquer au moins un critère de filtre

  • Dans l’exemple suivant, tapez Paris sous Ville et tapez >1000 sous Mt réalisé mais sur la deuxième ligne de la zone de critère comme suit :
La fonction OU - filtre élaboré

  • Appliquez le filtre élaboré et étendez la sélection de la zone de critère dans l’option Zone de critères puis cliquez sur OK.
  • Voici le résultat obtenu
Résultat utilisation de la fonction OU


Ceci est équivalent à l’utilisation de la fonction OU (déjà vu aussi).

Vous pouvez donc ajouter plusieurs critères de filtre, et rappelez-vous que pour combiner les critères en utilisant la fonction ET, vous devez les écrire sur la même ligne dans la zone de critères. Et pour utiliser la fonction OU, vous devez les placer sur des lignes différentes.

Regardez maintenant cet exemple :
  • Nous voulons filtrer les données pour un montant supérieur à 1000 euros (inclus) et inférieur à 2000 euros (inclus aussi). C’est la fonction ET qui sera utilisée effectivement.
  • Dans ce cas il suffit de créer une zone de critère avec deux étiquettes portant le même nom: Mt réalisé de cette façon :
deux étiquettes semblables

  • Cliquez ensuite sur le bouton Avancé et sélectionner la nouvelle zone de critères puis validez.
Résultat intervalle des montants


Voici d’autres exemples de critères que vous pouvez ajouter:
  • Date de naissance inférieur au 31/12/1988 et Montant inférieur à 500 euros.
Exemple d'utilisation de deux critères dans un filtre élaboré

  • Date de naissance inférieur au 31/12/1988 ou la ville est Nice.
Exemple d'utilisation de deux critères avec la fonction OU

Le filtre dernier sera rétrécit si vous introduisez les critères comme suit :
  • Date de naissance inférieur au 31/12/1988 ou date de naissance inférieur au 31/12/1988 et la ville est Nice.
Utiliser même critères sur deux lignes


Ajoutez aussi un critère de plus :
  • Date de naissance inférieur au 31/12/1988 et Mt réalisé supérieur à 1000 euros, ou date de naissance inférieur au 31/12/1988 et la ville est Nice.
Plusieurs critères dans un filtre élaboré



D’autres exemples de filtres élaborés seront traités dans le prochain article.

Comment créer des filtres textuels ?

Continuons avec le filtre des données dans Excel, et abordons aujourd’hui la création des filtres textuels qui consiste à filtrer les données de type texte ou des données alphanumériques.
Comment créer des filtres textuels


Nous avons déjà traité ce sujet lors de l’utilisation de la fonctionnalité de recherche pour filtrer les données dans cette formation Excel, mais dans l’article suivant nous allons parler de l’utilisation des opérateurs de comparaisons.

Créer un filtre textuel

Veuillez télécharger tout d’abord ce classeur Excel : Liste des prix des smartphones puis ouvrez-le.
  • Cliquez sur une cellule du tableau puis sous l’onglet Données cliquez sur Filtrer.
  • Cliquez ensuite sur la flèche dans l’en-tête de la colonne Smartphones.
  • Sélectionnez l’option Filtres textuels pour afficher un sous-menu des opérateurs de comparaison.
Menu Filtres textuels

  • Si vous cherchez des données relatives à un smartphone précis, par exemple Honor 7, cliquez sur Est égal à et tapez ce nom dans la zone de critères dans la fenêtre Filtre automatique personnalisé.
  • Vous pouvez aussi sélectionnez ce nom dans la liste des critères de recherche.
  • Cliquez enfin sur OK.
Filtre textuel est égal à

  • Maintenant si vous aimez masquer les données du smartphone Nokia Lumia 640, cliquez sur Est différent de
Opérateur Est différent de

  • Puis dans la fenêtre qui s’affiche tapez le nom Nokia Lumia 640 

Critère de filtre - Nokia Lumia 640
  • Et cliquez sur OK.
  • Mais si vous ne voulez pas que les données de tous les smartphones Nokia soient affichées tapez dans la zone de la condition Est différent de … Nokia* (Nokia plus un astérisque), puis validez.
Utilisation d'uncaractère générique


Les deux opérateurs Commence par et Se termine par

  • Si vous aimez afficher les noms des smartphones commençant par M, cliquez sur Commence par puis tapez M et validez.
  •  Et si vous cherchez des smartphones que leurs noms se terminent par S6, cliquez sur Se termine par et écrivez S6 puis validez.
Les opérateurs commence par et se termine par


Contient et Ne contient pas

  • L’opérateur Contient vous affiche tous les noms des smartphones qui contiennent les caractères que vous avez tapés que ce soit au début, au milieu ou à la fin du nom.
  • Et si vous utilisez l’opérateur Ne contient pas, Excel masque tous les noms des smartphones contenant les caractères saisis.
Les opérateurs Contient et Necontient pas


Le filtre personnalisé

Ce filtre vous permet et à travers la fenêtre Filtre automatique personnalisé de choisir l’opérateur que vous voulez et d’utiliser les options Et – Ou pour combiner entre deux conditions.

Filtre personnalisé



Comment créer des filtres chronologiques ?

Aujourd’hui, nous allons traiter dans cette formation Excel la création des filtres chronologiques, c’est-à-dire filtrer des données sous forme de dates ou d’heures, en utilisant des opérateurs de comparaison que nous fournisse Excel.
Créer des filtres chronologiques


Et avant de commencer, je vous propose de télécharger ce fichier Excel qui comporte deux tableaux : Liste des factures (1 et 2) et Liste des RDV, que vous pouvez télécharger ici :

Créer des filtres chronologiques

  • Ouvrez donc le fichier téléchargé et sélectionnez la feuille Liste des factures puis cliquez sur la colonne Date, ensuite cliquez sur Filtrer sous l’onglet Données comme d’habitudes.
Activer le filtre - liste des factures

  • Les flèches apparaissent dans les en-têtes des colonnes, cliquez sur celle dans l’entête de la colonne Date.
  • Pointez-vous sur l’option Filtres Chronologiques. Et comme vous l’avez vu pour les filtres numériques, Excel affiche pour cette colonne le nom Filtres chronologiques parce qu’il comprend que les données qui se trouvent dans cette colonne sont de types dates.
Option Filtres chronologiques

  • Alors, vous voyez un sous-menu qui apparaît et qui contient 21 opérateurs de comparaison plus une commande de filtre personnalisé.
Opérateurs de comparaison - filtres chronologiques


  • Essayez donc de filtrer les données de la liste des factures en suivant les exemples suivants :

Afficher la liste des factures réalisées le 22/03/2016

  • Dans le sous-menu Filtres chronologiques, cliquez sur Est égal à…
  • La fenêtre Filtre automatique personnalisé apparaît.
  • Dans l’entrée qui correspond à la condition Est égale à, écrivez la date 22/03/2016.
  • Vous pouvez aussi recourir à deux autres possibilités pour entrer cette date :
    • Sélectionnez la date dans la liste des dates.
    • Cliquez sur le bouton Calendrier et sélectionnez la date cherchée.
  • Cliquez enfin sur OK.
Filtre chronologique est égal à


Afficher la liste des factures réalisées avant le 01/04/2016

  • Dans le sous-menu Filtres chronologiques, cliquez sur Avant…
  • Dans la fenêtre Filtre automatique personnalisé vous pouvez choisir entre :
    • Antérieur au : et vous tapez la date 01/04/2016.
Filtre chronologique - antérieur au

    • Quand vous cliquez sur OK, Excel affiche toutes les factures appartenant aux dates qui précédent la date saisie 01/04/2016. Cette dernière ne sera pas incluse.
Résultat Filtre chronologique - antérieur au

    • Antérieur ou égal au : tapez la date 01/04/2016 puis cliquez sur OK.
Filtre chronologique - antérieur ou égal au

    • La différence ici, c’est que la date saisie est incluse dans le résultat du filtrage.
Résultat Filtre chronologique - antérieur ou égal au


Afficher la liste des factures réalisées après le 05/04/2016

  • Dans le sous-menu Filtres chronologiques, cliquez sur Après…
  • De la même façon, choisissez soit :
    • Postérieur au : pour ne pas inclure la date 05/04/2016 dans le résultat du filtrage.
    • Soit Postérieur ou égal au : pour qu’Excel affiche toutes les factures appartenant aux dates qui existent à partir du 05/04/2016.
Filtres chronologiques - Postérieur au


Extraire les factures enregistrées entre 01/04/2016 et le 10/04/2016

  • Dans le sous-menu Filtres chronologiques, cliquez sur Entre…
  • Et dans la fenêtre Filtre automatique personnalisé, vous devez remplir les deux entrées :
    • Postérieur au ou Postérieur ou égal au (comme s’est expliqué précédemment) : Ecrivez donc 01/04/2016.
    • Antérieur au ou Antérieur ou égal au : Tapez 10/04/2016.
  • Cliquez enfin sur OK.
Filtres chronologiques - Entre



Les filtres dynamiques :

Les filtres dynamiques se sont des filtres qui seront créés sans avoir besoin de taper la date de filtre à chaque fois. Ils contiennent les opérateurs suivants :
  • Aujourd’hui :

Lorsque vous cliquez sur Aujourd’hui dans le sous-menu Filtres chronologiques, Excel affiche les factures réalisées aujourd’hui.

Filtres dynamiques Aujourd'hui


Note : Vérifiez si la date de votre système d’exploitation est bien ajustée.

Résultat Filtres dynamiques Aujourd'hui

  • Hier :

Ce filtre vous donne la liste des factures enregistrées hier.

Vous pouvez alors tester les autres opérateurs : Semaine dernière, cette semaine, mois dernier, ce mois, ce trimestre, trimestre dernier, année dernière, cette année et Année à ce jour.

Opérateurs de comparaison dynamiques


Pour les autres opérateurs, sélectionnez la feuille Liste des RDV, puis activez le filtre en cliquant sur Filtrer sous l’onglet Données.
  • Cliquez sur la flèche dans l’en-tête Date et dans le sous-menu Filtres chronologiques, cliquez sur Demain
  • Excel affiche donc tous les noms des personnes qui ont des rendez-vous le 13/04/2016.
  • Sélectionnez les autres opérateurs : Semaine prochaine, mois prochain, année prochaine, trimestre prochain et vérifiez vos résultats.
Filtres chronologiques dynamiques date future

  • Quant au choix Toutes les dates de cette période, il vous permet de créer des filtres chronologiques d’une autre façon et précisément des filtres trimestriels ou mensuels.

Filtrer des heures 

  • Dans le tableau Liste des RDV, cliquez sur la flèche dans l’en-tête de la colonne Heure et remarquez qu’Excel affiche Filtres numériques cette fois au lieu de Filtres chronologiques.
  • Dans le sous-menu Filtres numériques, procédez à créer vos filtres comme vous l’avez vu précédemment.
Filtrer des heures

  • Par exemple, cliquez sur Est égal à et sélectionnez l’heure dans la liste des critères.
Filtres des heures égal à

  • Voici ce que vous aurez :
Résultat Filtrer des heures



Filtre personnalisé

Le filtre personnalisé vous permet de créer des filtres de votre choix, vous pouvez donc utiliser une condition de filtre ou en combiner deux en choisissant entre les deux options Et et OU.

Utiliser un style de tableau pour créer des filtres chronologiques

Un style de tableau vous permet aussi de filtrer les données de votre tableau et pour ce faire, vous allez commencer par utiliser l’outil mettre sous forme de tableau.
  • Sélectionnez la feuille Liste des factures 2 puis cliquez sur une cellule de votre tableau.
  • Sous l’onglet Accueil, cliquez sur Mettre sous forme de tableau et sélectionnez un style de votre choix.
Mettre sous forme de tableau

  • Des flèches de filtre apparaissent donc dans les en-têtes des colonnes.
  • Cliquez sur la flèche dans l’en-tête de la colonne Date et commencez à créer vos filtres comme s’est expliqué en haut.
L’utilisation d’un style de tableau s’avère très utile si vous ajoutez une ligne des totaux à votre tableau.
  • Cliquez alors sur votre tableau, puis cliquez sur l’onglet Outils de tableau-Création
  • Cochez l’option Ligne des totaux dans le groupe Options de style de tableau.
Lignes des totaux

  • Une ligne des totaux s’ajoute alors en dessous de votre tableau et un total est calculé automatiquement sous la colonne Montant TTC.
  • Pour ajouter un total sous la colonne Montant  HT, cliquez sur la cellule en dessous de cette colonne puis sélectionnez Somme.
Insérer un total

  • Créez maintenant vos filtres chronologiques et remarquez ce qui se passe dans la ligne des totaux.
Filtres chronologiques avec des totaux