Comment utiliser la fonction Excel FILTRE

Office 365 apporte des fonctionnalités intéressantes telles que XLOOKUP, SORT et FILTER.

Lors du filtrage des données dans Excel, dans le monde pré-Office 365, nous nous appuyions principalement sur des filtres intégrés Excel ou tout au plus sur des filtres avancés ou des formules SOMMEPROD complexes.Il s'agit souvent d'une solution de contournement compliquée si vous devez filtrer une partie de l'ensemble de données.

Mais avec la nouvelle fonctionnalité FILTER, il est désormais très facile de filtrer rapidement des parties d'un ensemble de données en fonction de conditions.

Dans ce didacticiel, je vais vous montrer à quel point la nouvelle fonctionnalité FILTRE est géniale et certaines choses utiles que vous pouvez en faire.

Mais avant d'entrer dans l'exemple, examinons rapidement la syntaxe de la fonction FILTER.

Si vous souhaitez obtenir ces nouvelles fonctionnalités dans Excel, vous pouvezMise à niveau vers Office 365(Rejoignez le programme interne pour accéder à toutes les fonctionnalités/formules)

Fonction de filtre Excel – Syntaxe

Voici la syntaxe de la fonction FILTRE :

=FILTRE(tableau,include,[if_empty])

• tableau - c'est la plage de cellules où vous avez les données et souhaitez en filtrer certaines
• comprendre - C'est la condition qui indique à la fonction quels enregistrements filtrer
• [if_vide] – Il s'agit d'un paramètre facultatif dans lequel vous pouvez spécifier ce qu'il faut renvoyer si la fonction FILTER ne trouve aucun résultat.Par défaut (lorsqu'il n'est pas spécifié), il renvoie #CALC !Erreur

Examinons maintenant quelques exemples étonnants de fonctions de filtrage et ce qu'elles peuvent faire, ce qui était très compliqué sans elles.

Exemple 1 : Filtrer les données en fonction d'une condition (région)

Supposons que vous disposiez d'un ensemble de données comme celui ci-dessous et que vous souhaitiez uniquement filtrer tous les enregistrements aux États-Unis.

Voici la formule FILTER qui fait cela :

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

La formule ci-dessus prend l'ensemble de données sous forme de tableau et la condition est $B$2:$B$11=”US”

Cette condition obligera la fonction FILTER à vérifier chaque cellule de la colonne B (cellules avec la plage) et à filtrer uniquement les enregistrements qui correspondent à cette condition.

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

La fonction de filtrage renvoie un tableau dynamique de résultats (ce qui signifie qu'au lieu de renvoyer une valeur, elle renvoie un tableau qui déborde dans d'autres cellules).

Pour cela, vous devez disposer d'une zone où le résultat est vide.Il y a déjà quelque chose dans n'importe quelle cellule de la plage (E2:G5 dans cet exemple) et 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 supprimer toute la plage avec le résultat ou la cellule E2 (où la formule est entrée).Les deux supprimeront tout le tableau de résultats.Mais vous ne pouvez pas modifier une seule cellule (ou la supprimer).

Dans la formule ci-dessus, j'ai codé en dur la valeur de la plage, mais vous pouvez également la mettre dans une cellule et référencer cette cellule avec la valeur de la plage.

Par exemple, dans l'exemple ci-dessous, j'ai la valeur de plage dans la cellule I2, puis je la référence dans la formule :

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

Cela rend la formule encore plus utile, il vous suffit maintenant de modifier la valeur de la plage 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 une sélection et elle mettra instantanément à jour les données filtrées.

Exemple 2 : filtrer les données en fonction d'un critère (supérieur ou inférieur à)

Vous pouvez également utiliser des opérateurs de comparaison dans les fonctions de filtrage et extraire tous les enregistrements supérieurs ou inférieurs à une certaine valeur.

Par exemple, supposons que vous disposiez de l'ensemble de données ci-dessous et que vous souhaitiez filtrer tous les enregistrements avec des ventes supérieures à 10000 XNUMX.

La formule suivante peut le faire :

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

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

La formule vérifie chaque enregistrement pour la valeur de 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 à 10000 XNUMX, vous pouvez utiliser la formule suivante :

=FILTER($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 des ventes, vous pouvez utiliser la formule suivante :

=FILTER($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 du jeu de données.Utilisez ensuite cette valeur dans une condition de fonction FILTER pour obtenir tous les enregistrements dont les ventes sont supérieures ou égales à la troisième plus grande valeur.

Exemple 3 : Filtrage de données à l'aide de plusieurs conditions (ET)

Supposons que vous disposiez de l'ensemble de données suivant et que vous souhaitiez filtrer tous les enregistrements aux États-Unis dont la valeur des ventes est supérieure à 10000 XNUMX.

Ceci est une condition ET, vous devez vérifier deux choses - la région doit être aux États-Unis et les ventes doivent être supérieures à 10000 XNUMX.Si une seule condition est remplie, les résultats ne doivent pas être filtrés.

Voici la formule de filtre qui filtrera les enregistrements avec les États-Unis comme région et avec plus de 10000 XNUMX ventes :

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

請注意，標準（稱為包含參數）是 ($B$2:$B$11=”US”)*($C$2:$C$11>10000)

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

S'il n'y a pas d'enregistrements correspondants, la fonction renverra #CALC !Erreur.

Si vous souhaitez renvoyer quelque chose de significatif (pas une erreur), vous pouvez utiliser une formule comme celle-ci :

=FILTER($A$2:$C$11,($B$2:$B$11="USA")*($C$2:$C$11>10000),"Nothing Found")

Ici, j'ai utilisé "not found" comme troisième paramètre, qui est utilisé lorsqu'aucun enregistrement correspondant n'est trouvé.

Exemple 4 : Filtrage de données à l'aide de plusieurs critères (OU)

Vous pouvez également modifier le paramètre "contains" dans la fonction FILTER pour vérifier les conditions OR (où une condition donnée peut être vraie).

Par exemple, supposons que vous disposiez de l'ensemble de données ci-dessous et que vous souhaitiez filtrer les enregistrements dont le pays correspond aux États-Unis ou au Canada.

Voici la formule pour ce faire :

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

Notez que dans la formule ci-dessus, j'ajoute simplement les deux conditions à l'aide de l'opérateur d'addition.Étant donné que chacune de ces conditions renvoie un tableau de VRAI et FAUX, je peux ajouter un tableau combiné qui sera VRAI si l'une ou l'autre des conditions est remplie.

Un autre exemple peut être lorsque vous souhaitez filtrer tous les enregistrements dont le pays est les États-Unis ou dont la valeur des ventes est supérieure à 10000 XNUMX.

La formule suivante fera cela :

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

REMARQUE : Lors de l'utilisation des conditions AND dans la fonction FILTER, utilisez l'opérateur de multiplication (*), et lors de l'utilisation des conditions OR, utilisez l'opérateur d'addition (+).

Exemple 5 : Filtrer les données pour les enregistrements supérieurs/inférieurs à la moyenne

Vous pouvez utiliser des formules dans la fonction FILTRE pour filtrer et extraire les enregistrements avec des valeurs supérieures ou inférieures à la moyenne.

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

Vous pouvez le faire avec la formule suivante :

=FILTER($A$2:$C$11,C2:C11>AVERAGE(C2:C11))

De même, pour un résultat inférieur à la moyenne, vous pouvez utiliser la formule suivante :

=FILTER($A$2:$C$11,C2:C11<AVERAGE(C2:C11))

Exemple 6 : filtrer uniquement les enregistrements pairs (ou les enregistrements impairs)

Si vous avez besoin de filtrer et d'extraire rapidement tous les enregistrements dans des lignes paires ou impaires, vous pouvez utiliser la fonction FILTER pour le faire.

Pour ce faire, vous devez vérifier le numéro de ligne dans la fonction FILTRE et filtrer uniquement les numéros de ligne qui correspondent à la condition de numéro de ligne.

Supposons que vous disposiez d'un ensemble de données comme ci-dessous et que je souhaite uniquement extraire même des enregistrements de cet ensemble de données.

Voici la formule pour ce faire :

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

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

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 2 de la partie ROW(A11:A1) car le premier enregistrement se trouve dans la deuxième ligne, ce qui ajuste les numéros de ligne pour traiter la deuxième ligne comme le premier enregistrement.

De même, vous pouvez filtrer tous les enregistrements impairs avec la formule suivante :

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=1)

Exemple 7 : Trier des données filtrées à l'aide d'une formule

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

Par exemple, si vous utilisez la fonction FILTER pour filtrer un jeu de données, vous pouvez utiliser la fonction SORT pour obtenir des résultats triés.

Supposons que vous disposiez d'un ensemble de données comme indiqué ci-dessous et que vous souhaitiez filtrer tous les enregistrements avec des ventes supérieures à 10000 XNUMX.Vous pouvez utiliser la fonction SORT avec cette fonction pour vous assurer que les données résultantes sont triées en fonction des ventes.

La formule suivante fera cela :

=SORT(FILTER($A$2:$C$11,($C$2:$C$11>10000)),3,-1)

La fonction ci-dessus utilise la fonction FILTER pour obtenir les données de la colonne C avec des ventes supérieures à 10000 XNUMX.Utilisez ensuite le tableau renvoyé par la fonction FILTER dans la fonction SORT pour trier les données en fonction des ventes.

Le deuxième paramètre de la fonction SORT est 3, qui consiste à trier en fonction de la troisième colonne.Le quatrième paramètre est -1, qui trie les données par ordre décroissant.

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

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