Fonction de filtre Excel - Expliquée avec des exemples + une vidéo

Regarder la vidéo - Exemples de fonctions Excel FILTER

Office 365 propose des fonctions impressionnantes, telles que XLOOKUP, SORT et FILTER.

En ce qui concerne le filtrage des données dans Excel, dans le monde pré-Office 365, nous étions principalement dépendants du filtre intégré à Excel ou au maximum du filtre avancé ou des formules SUMPRODUCT complexes. Au cas où vous deviez filtrer une partie d'un ensemble de données, il s'agissait généralement d'une solution de contournement complexe (quelque chose que j'ai couvert ici).

Mais avec la nouvelle fonction FILTER, il est désormais très facile de filtrer rapidement une partie de l'ensemble de données en fonction d'une condition.

Et dans ce tutoriel, je vais vous montrer à quel point la nouvelle fonction FILTER est géniale et quelques choses utiles que vous pouvez faire avec cela.

Mais avant d'entrer dans les exemples, découvrons rapidement la syntaxe de la fonction FILTER.

Si vous souhaitez obtenir ces nouvelles fonctionnalités dans Excel, vous pouvez mise à niveau vers Office 365 (rejoindre le programme d'initiés pour accéder à toutes les fonctionnalités/formules)

Fonction de filtre Excel - Syntaxe

Voici la syntaxe de la fonction FILTER :

=FILTER(tableau,inclure,[si_vide])

• déployer - c'est la plage de cellules où vous avez les données et vous souhaitez en filtrer certaines
• comprendre - c'est la condition qui indique à la fonction quels enregistrements filtrer
• [si_vide] - il s'agit d'un argument facultatif où vous pouvez spécifier ce qu'il faut retourner au cas où aucun résultat n'est trouvé par la fonction FILTER. Par défaut (lorsqu'il n'est pas spécifié), il renvoie le #CALC! Erreur

Jetons maintenant un coup d'œil à quelques exemples étonnants de fonctions de filtre et à ce qu'elle peut faire, ce qui était assez complexe en son absence.

Cliquez ici pour télécharger le fichier d'exemple et suivez

Exemple 1 : Filtrage des données en fonction d'un critère (région)

Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez filtrer tous les enregistrements pour les États-Unis uniquement.

Vous trouverez ci-dessous la formule FILTER qui fera cela :

=FILTRE($A$2:$C$11,$B$2:$B$11="US")

La formule ci-dessus utilise l'ensemble de données comme tableau et la condition est $B$2:$B$11="US"

Cette condition obligerait la fonction FILTER à vérifier chaque cellule de la colonne B (celle qui contient la région) et seuls les enregistrements correspondant à ce critère seraient filtrés.

De plus, dans cet exemple, j'ai les données d'origine et les données filtrées sur la même feuille, mais vous pouvez également les avoir dans des feuilles séparées ou même des classeurs.

Filter Function renvoie un résultat qui est un tableau dynamique (ce qui signifie qu'au lieu de renvoyer une valeur, il renvoie un tableau qui se répand dans d'autres cellules).

Pour que cela fonctionne, vous devez avoir une zone où le résultat viendrait à être vide. Dans l'une des cellules de cette zone (E2:G5 dans cet exemple) contient déjà quelque chose, la fonction vous donnera l'erreur #SPILL.

De plus, comme il s'agit d'un tableau dynamique, vous ne pouvez pas modifier une partie du résultat. Vous pouvez soit supprimer toute la plage contenant le résultat, soit la cellule E2 (où la formule a été entrée). Ces deux éléments supprimeraient l'intégralité du tableau résultant. Mais vous ne pouvez pas modifier une cellule individuelle (ou la supprimer).

Dans la formule ci-dessus, j'ai codé en dur la valeur de la région, mais vous pouvez également l'avoir dans une cellule, puis référencer cette cellule qui a la valeur de la région.

Par exemple, dans l'exemple ci-dessous, j'ai la valeur de région dans la cellule I2 et celle-ci est ensuite référencée dans la formule :

=FILTRE($A$2:$C$11,$B$2:$B$11=I1)

Cela rend la formule encore plus utile et vous pouvez maintenant simplement modifier la valeur de la région dans la cellule I2 et le filtre changera automatiquement.

Vous pouvez également avoir une liste déroulante dans la cellule I2 où vous pouvez simplement faire la sélection et cela mettrait à jour instantanément les données filtrées.

Exemple 2 : Filtrage des données en fonction d'un critère (plus ou moins que)

Vous pouvez également utiliser des opérateurs comparatifs dans la fonction de filtre et extraire tous les enregistrements qui sont supérieurs ou inférieurs à une valeur spécifique.

Par exemple, supposons que vous ayez l'ensemble de données illustré ci-dessous et que vous souhaitiez filtrer tous les enregistrements dont la valeur des ventes est supérieure à 10 000.

La formule ci-dessous peut le faire:

=FILTRE($A$2:$C$11,($C$2:$C$11>10000))

L'argument tableau fait référence à l'ensemble de données et la condition, dans ce cas, est ($C$2:$C$11>10000).

La formule vérifie chaque enregistrement pour la valeur dans la colonne C. Si la valeur est supérieure à 10000, elle est filtrée, sinon elle est ignorée.

Si vous souhaitez obtenir tous les enregistrements inférieurs à 10 000, vous pouvez utiliser la formule ci-dessous :

=FILTRE($A$2:$C$11,($C$2:$C$11<10000))

Vous pouvez également être plus créatif avec la formule FILTER. Par exemple, si vous souhaitez filtrer les trois premiers enregistrements en fonction de la valeur des ventes, vous pouvez utiliser la formule ci-dessous :

=FILTRE($A$2:$C$11,($C$2:$C$11>=LARGE(C2:C11,3)))

La formule ci-dessus utilise la fonction LARGE pour obtenir la troisième plus grande valeur de l'ensemble de données. Cette valeur est ensuite utilisée dans les critères de la fonction FILTRE pour obtenir tous les enregistrements où la valeur des ventes est supérieure ou égale à la troisième valeur la plus élevée.

Cliquez ici pour télécharger le fichier d'exemple et suivez

Exemple 3 : Filtrage de données avec plusieurs critères (AND)

Supposons que vous ayez l'ensemble de données ci-dessous et que vous souhaitiez filtrer tous les enregistrements pour les États-Unis où la valeur de vente est supérieure à 10 000.

Il s'agit d'une condition ET où vous devez vérifier deux choses : la région doit être aux États-Unis et les ventes doivent être supérieures à 10 000. Si une seule condition est remplie, les résultats ne doivent pas être filtrés.

Vous trouverez ci-dessous la formule FILTER qui filtrera les enregistrements avec les États-Unis comme région et les ventes de plus de 10 000 :

=FILTRE($A$2:$C$11,($B$2:$B$11="US")*($C$2:$C$11>10000))

Notez que le critère (appelé l'argument d'inclusion) est ($B$2:$B$11="US")*($C$2:$C$11>10000)

Comme j'utilise deux conditions et que j'ai besoin que les deux soient vraies, j'ai utilisé l'opérateur de multiplication pour combiner ces deux critères. Cela renvoie un tableau de 0 et de 1, où un 1 n'est renvoyé que lorsque les deux conditions sont remplies.

Au cas où aucun enregistrement ne répondrait aux critères, la fonction renverrait le #CALC! Erreur.

Et au cas où vous voudriez renvoyer quelque chose de significatif (au lieu de l'erreur), vous pouvez utiliser une formule comme indiqué ci-dessous :

=FILTRE($A$2:$C$11,($B$2:$B$11="USA")*($C$2:$C$11>10000),"Rien trouvé")

Ici, j'ai utilisé "Not Found" comme troisième argument, qui est utilisé lorsqu'aucun enregistrement ne correspond aux critères.

Exemple 4 : Filtrage de données avec plusieurs critères (OR)

Vous pouvez également modifier l'argument 'include' dans la fonction FILTER pour vérifier un critère OR (où l'une des conditions données peut être vraie).

Par exemple, supposons que vous ayez l'ensemble de données illustré ci-dessous et que vous souhaitiez filtrer les enregistrements dont le pays est soit les États-Unis, soit le Canada.

Vous trouverez ci-dessous la formule qui le fera :

=FILTRE($A$2:$C$11,($B$2:$B$11="US")+($B$2:$B$11="Canada"))

Notez que dans la formule ci-dessus, j'ai simplement ajouté les deux conditions en utilisant l'opérateur d'addition. Étant donné que chacune de ces conditions renvoie un tableau de VRAI et FAUX, je peux ajouter pour obtenir un tableau combiné où il est VRAI si l'une des conditions est remplie.

Un autre exemple pourrait être lorsque vous souhaitez filtrer tous les enregistrements dont le pays est les États-Unis ou la valeur de vente est supérieure à 10 000.

La formule ci-dessous fera cela:

=FILTRE($A$2:$C$11,($B$2:$B$11="US")+(C2:C11>10000))

Remarque : lors de l'utilisation des critères ET dans une fonction FILTRE, utilisez l'opérateur de multiplication (*) et lors de l'utilisation des critères OU, utilisez l'opérateur d'addition (+).

Exemple 5 : Filtrage des données pour obtenir des enregistrements supérieurs/inférieurs à la moyenne

Vous pouvez utiliser des formules dans la fonction FILTRE pour filtrer et extraire les enregistrements dont la valeur est supérieure ou inférieure à la moyenne.

Par exemple, supposons que vous ayez l'ensemble de données illustré ci-dessous et que vous souhaitiez filtrer tous les enregistrements pour lesquels la valeur de vente est supérieure à la moyenne.

Vous pouvez le faire en utilisant la formule suivante :

=FILTRE($A$2:$C$11,C2:C11>MOYENNE(C2:C11))

De même, pour en dessous de la moyenne, vous pouvez utiliser la formule ci-dessous :

=FILTRE($A$2:$C$11,C2:C11<>

Cliquez ici pour télécharger le fichier d'exemple et suivez

Exemple 6 : Filtrer uniquement les enregistrements de numéros PAIR (ou les enregistrements de numéros impairs)

Au cas où vous auriez besoin de filtrer et d'extraire rapidement tous les enregistrements des lignes de nombres pairs ou impairs, vous pouvez le faire avec la fonction FILTRE.

Pour ce faire, vous devez vérifier le numéro de ligne dans la fonction FILTER et filtrer uniquement les numéros de ligne qui répondent aux critères de numéro de ligne.

Supposons que vous ayez l'ensemble de données comme indiqué ci-dessous et que je souhaite uniquement extraire les enregistrements pairs de cet ensemble de données.

Vous trouverez ci-dessous la formule qui le fera :

=FILTRE($A$2:$C$11,MOD(LIGNE(A2:A11)-1,2)=0)

La formule ci-dessus utilise la fonction MOD pour vérifier le numéro de ligne de chaque enregistrement (qui est donné par la fonction ROW).

La formule MOD(ROW(A2:A11)-1,2)=0 renvoie TRUE lorsque le numéro de ligne est pair et FALSE lorsqu'il est impair. Notez que j'ai soustrait 1 de la partie ROW(A2:A11) car le premier enregistrement est dans la deuxième ligne, et cela ajuste le numéro de ligne pour considérer la deuxième ligne comme le premier enregistrement.

De même, vous pouvez filtrer tous les enregistrements impairs à l'aide de la formule ci-dessous :

=FILTRE($A$2:$C$11,MOD(LIGNE(A2:A11)-1,2)=1)

Exemple 7 : trier les données filtrées avec la formule

L'utilisation de la fonction FILTER avec d'autres fonctions nous permet d'en faire beaucoup plus.

Par exemple, si vous filtrez un ensemble de données à l'aide de la fonction FILTER, vous pouvez utiliser la fonction SORT avec elle pour obtenir le résultat déjà trié.

Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez filtrer tous les enregistrements dont la valeur des ventes est supérieure à 10 000. Vous pouvez utiliser la fonction SORT avec la fonction pour vous assurer que les données résultantes sont triées en fonction de la valeur des ventes.

La formule ci-dessous fera cela:

=TRI(FILTRE($A$2:$C$11,($C$2:$C$11>10000)),3,-1)

La fonction ci-dessus utilise la fonction FILTER pour obtenir les données où la valeur de vente dans la colonne C est supérieure à 10000. Ce tableau renvoyé par la fonction FILTRE est ensuite utilisé dans la fonction SORT pour trier ces données en fonction de la valeur des ventes.

Le deuxième argument de la fonction SORT est 3, qui consiste à trier en fonction de la troisième colonne. Et le quatrième argument est -1 qui consiste à trier ces données par ordre décroissant.

Cliquez ici pour télécharger le fichier exemple

Voici donc 7 exemples d'utilisation de la fonction FILTRE dans Excel.

J'espère que vous avez trouvé ce tutoriel utile!

Vous aimerez peut-être aussi les tutoriels Excel suivants :

1. Comment filtrer les cellules avec une mise en forme de police en gras dans Excel
2. Zone de recherche de filtre Excel dynamique
3. Comment filtrer les données dans un tableau croisé dynamique dans Excel