Filtre avancé Excel - Un guide complet avec des exemples

Regarder la vidéo - Filtre avancé Excel

Le filtre avancé Excel est l'une des fonctionnalités les plus sous-estimées et sous-utilisées que j'ai rencontrées.

Si vous travaillez avec Excel, je suis sûr que vous avez utilisé (ou du moins entendu parler du filtre Excel classique). Il filtre rapidement un ensemble de données en fonction de la sélection, du texte spécifié, du nombre ou d'autres critères de ce type.

Dans ce guide, je vais vous montrer des choses intéressantes que vous pouvez faire en utilisant le filtre avancé Excel.

Mais d'abord… Qu'est-ce qu'Excel Advanced Filter ?

Excel Advanced Filter - comme son nom l'indique - est la version avancée du filtre standard. Vous pouvez l'utiliser lorsque vous devez utiliser des critères plus complexes pour filtrer votre ensemble de données.

Voici quelques différences entre le filtre normal et le filtre avancé :

• Alors que le filtre de données standard filtrera l'ensemble de données existant, vous pouvez également utiliser le filtre avancé Excel pour extraire l'ensemble de données vers un autre emplacement.
• Excel Advanced Filter vous permet d'utiliser des critères complexes. Par exemple, si vous avez des données de ventes, vous pouvez filtrer les données sur un critère où le commercial est Bob et la région est soit Nord soit Sud (nous verrons comment faire cela dans les exemples). Le support Office a de bonnes explications à ce sujet.
• Vous pouvez utiliser le filtre avancé Excel pour extraire des enregistrements uniques de vos données (plus d'informations à ce sujet en une seconde).

FILTRE EXCEL AVANCÉ (Exemples)

Voyons maintenant un exemple d'utilisation du filtre avancé dans Excel.

Exemple 1 - Extraction d'une liste unique

Vous pouvez utiliser Excel Advanced Filter pour extraire rapidement des enregistrements uniques d'un ensemble de données (ou, en d'autres termes, supprimer les doublons).

Dans Excel 2007 et les versions ultérieures, il existe une option pour supprimer les doublons d'un ensemble de données. Mais cela modifie votre ensemble de données existant. Pour conserver les données d'origine intactes, vous devez créer une copie des données, puis utiliser l'option Supprimer les doublons. Le filtre avancé d'Excel vous permettrait de sélectionner un emplacement pour obtenir une liste unique.

Voyons comment utiliser des filtres avancés pour obtenir une liste unique.

Supposons que vous ayez un ensemble de données comme indiqué ci-dessous :

Comme vous pouvez le voir, il existe des enregistrements en double dans cet ensemble de données (surlignés en orange). Ceux-ci peuvent être dus à une erreur dans la saisie des données ou le résultat de la compilation des données.

Dans un tel cas, vous pouvez utiliser l'outil Filtre avancé Excel pour obtenir rapidement une liste de tous les enregistrements uniques dans un emplacement différent (afin que vos données d'origine restent intactes).

Voici les étapes pour obtenir tous les enregistrements uniques :

• Sélectionnez l'ensemble des données (y compris les en-têtes).
• Allez dans l'onglet Données -> Trier et filtrer -> Avancé. (Vous pouvez également utiliser le raccourci clavier - Alt + A + Q). Cela ouvrira la boîte de dialogue Filtre avancé.
• Dans la boîte de dialogue Filtre avancé, utilisez les informations suivantes :
  • Action: Sélectionnez l'option « Copier vers un autre emplacement ». Cela vous permettra de spécifier l'emplacement où vous pouvez obtenir la liste des enregistrements uniques.
  • Plage de liste : Assurez-vous qu'il fait référence à l'ensemble de données à partir duquel vous souhaitez rechercher des enregistrements uniques. Assurez-vous également que les en-têtes de l'ensemble de données sont inclus.
  • Plage de critères : Laissez ce vide.
  • Copier: Spécifiez l'adresse de la cellule où vous souhaitez obtenir la liste des enregistrements uniques.
  • Copier les enregistrements uniques uniquement : Cochez cette option.
• Cliquez sur OK.

Cela vous donnera instantanément une liste de tous les enregistrements uniques.

Avertir: Lorsque vous utilisez le filtre avancé pour obtenir la liste unique, assurez-vous d'avoir également sélectionné l'en-tête. Si vous ne le faites pas, il considérera la première cellule comme l'en-tête.

Exemple 2 - Utilisation de critères dans le filtre avancé Excel

Obtenir des enregistrements uniques est l'une des nombreuses choses que vous pouvez faire avec le filtre avancé Excel.

Son utilité première réside dans sa capacité à permettre l'utilisation de critères complexes de filtrage des données.

Voici ce que j'entends par critères complexes. Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez obtenir rapidement tous les enregistrements où les ventes sont supérieures à 5 000 et la région est les États-Unis.

Voici comment vous pouvez utiliser Excel Advanced Filter pour filtrer les enregistrements en fonction des critères spécifiés :

• La première étape lors de l'utilisation du filtre avancé Excel avec des critères complexes consiste à spécifier les critères. Pour ce faire, copiez les en-têtes et collez-les quelque part dans la feuille de calcul.
• Spécifiez les critères pour lesquels vous souhaitez filtrer les données. Dans cet exemple, puisque nous voulons obtenir tous les enregistrements pour les États-Unis avec des ventes supérieures à 5 000, saisissez « US » dans la cellule sous Région et > 5000 dans la cellule sous Ventes. Cela serait maintenant utilisé comme entrée dans le filtre avancé pour obtenir les données filtrées (comme indiqué dans les étapes suivantes).
• Sélectionnez l'ensemble des données (y compris les en-têtes).
• Allez dans l'onglet Données -> Trier et filtrer -> Avancé. Cela ouvrira la boîte de dialogue Filtre avancé.
• Dans la boîte de dialogue Filtre avancé, utilisez les informations suivantes :
  • Action: Sélectionnez l'option « Copier vers un autre emplacement ». Cela vous permettra de spécifier l'emplacement où vous pouvez obtenir la liste des enregistrements uniques.
  • Plage de liste : Assurez-vous qu'il fait référence à l'ensemble de données à partir duquel vous souhaitez rechercher des enregistrements uniques. Assurez-vous également que les en-têtes de l'ensemble de données sont inclus.
  • Plage de critères : Spécifiez les critères que nous avons construits dans les étapes ci-dessus. Dans cet exemple, ce serait F1:I3.
  • Copier: Spécifiez l'adresse de la cellule où vous souhaitez obtenir la liste des enregistrements uniques.
  • Copier les enregistrements uniques uniquement : Cochez cette option.
• Cliquez sur OK.

Cela vous donnerait instantanément tous les records où la région est les États-Unis et les ventes sont supérieures à 5 000.

L'exemple ci-dessus est un cas où le filtrage est effectué sur la base de deux critères (US et ventes supérieures à 5000).

Le filtre avancé d'Excel vous permet de créer de nombreuses combinaisons différentes de critères.

Voici quelques exemples de la façon dont vous pouvez construire ces filtres.

Utilisation des critères ET

Lorsque vous souhaitez utiliser des critères ET, vous devez le spécifier sous l'en-tête.

Par exemple:

• Pour filtrer les enregistrements lorsque la région est les États-Unis ET que le commercial est Joe.
• Pour filtrer les enregistrements lorsque la région est les États-Unis ET que la valeur des ventes est supérieure à 5 000.
• Lorsque la région est les États-Unis ET que les ventes sont enregistrées après le 31-03-2017.

Utilisation des critères OU

Lorsque vous souhaitez utiliser des critères OU, vous devez spécifier les critères dans la même colonne.

Par exemple:

• Pour filtrer les enregistrements lorsque la région est les États-Unis OU la région est l'Asie.
• Pour filtrer les enregistrements lorsque le commercial est Bob OU Martha.

A présent, vous devez avoir réalisé que lorsque nous avons les critères dans la même ligne, c'est un ET critères, et quand nous l'avons dans des rangées différentes, c'est un OU ALORS Critères.

Exemple 3 - Utilisation des caractères WILDCARD dans le filtre avancé dans Excel

Le filtre avancé Excel permet également l'utilisation de caractères génériques lors de la construction des critères.

Il existe trois caractères génériques dans Excel :

1. * (astérisque) - Il représente un nombre quelconque de caractères. Par exemple, ex* pourrait signifier excel, excels, example, expert, etc.
2. ? (point d'interrogation) - Il représente un seul caractère. Par exemple, Tr?mp pourrait signifier Trump ou Tramp.
3. ~ (tilde) - Il est utilisé pour identifier un caractère générique (~, *, ?) dans le texte.

Voyons maintenant comment utiliser ces caractères génériques pour effectuer un filtrage avancé dans Excel.

• Pour filtrer les enregistrements où le nom du représentant commercial commence par J.

Notez que * représente n'importe quel nombre de caractères. Ainsi, tout représentant dont le nom commence par J serait filtré avec ces critères.

De même, vous pouvez également utiliser les deux autres caractères génériques.

Remarque : si vous utilisez Office 365, vous devriez vérifier la fonction FILTRE. Il peut faire beaucoup de choses que le filtre avancé peut faire avec une formule simple.

REMARQUE:

1. N'oubliez pas que les en-têtes des critères doivent être exactement les mêmes que ceux de l'ensemble de données.
2. Le filtrage avancé ne peut pas être annulé lorsqu'il est copié vers d'autres emplacements.