Pourquoi FILTER est meilleur pour extraire des données Excel

XLOOKUP d'Excel est idéal pour trouver une aiguille dans une botte de foin, mais que se passe-t-il si vous voulez toutes les aiguilles ? Alors que XLOOKUP s'arrête à la première correspondance, la fonction FILTER est conçue pour l'ère des tableaux dynamiques, vous permettant d'extraire des listes entières de données avec une formule unique et élégante.

Pourquoi XLOOKUP n'est pas toujours le héros

XLOOKUP, disponible dans Excel pour Microsoft 365, Excel pour le Web et dans les versions uniques de l'application publiées en 2021 ou ultérieurement, est la référence en matière de récupération de données. Il est nettement plus facile à utiliser que le combo INDEX-MATCH et beaucoup plus flexible que ses prédécesseurs, VLOOKUP et HLOOKUP. C'est aussi plus intelligent. Par exemple, XLOOKUP peut remplir plusieurs colonnes pour une seule correspondance : si vous recherchez un identifiant d'employé, il peut automatiquement remplir le nom, le service et la date de début en une seule fois.

Mais il présente une limitation fondamentale : il est conçu pour trouver un seul résultat. Lorsque vos données contiennent plusieurs enregistrements pour les mêmes critères, comme une liste de chaque vente dans la région nord ou chaque facture pour un client spécifique, XLOOKUP n'est pas l'outil idéal car il s'arrête à la première correspondance.

Comment la fonction FILTER change la donne

La fonction FILTER appartient à une classe de fonctions de tableau dynamique modernes, ce qui signifie que vous tapez la formule une seule fois et que les résultats se répartissent dans autant de cellules que nécessaire : vous n'avez pas besoin de faire glisser la poignée de recopie vers le bas de 100 lignes.

Même si elle peut effectuer des opérations complexes, la syntaxe de la fonction FILTER est basique :

=FILTER(array,include,(if_empty))

• tableau (obligatoire) est la plage de cellules ou le tableau que vous souhaitez filtrer.
• inclure (obligatoire) est le critère qui indique à Excel ce qu'il faut conserver dans le filtre.
• (si_vide) (facultatif) est l'endroit où vous spécifiez ce qu'Excel doit afficher si aucune correspondance n'est trouvée.

Contrairement à l'outil de filtrage standard trouvé dans l'onglet Données, la fonction FILTER est active. Si vous ajoutez une nouvelle entrée, elle apparaît instantanément dans vos résultats. Et contrairement à la fonction XLOOKUP, elle renvoie chaque correspondance trouvée, pas seulement la première.

Exemple 1 : extraire toutes les ventes pour une région spécifique

Supposons que vous disposiez d'un journal principal des ventes dans un tableau Excel nommé T_Sales et que vous deviez extraire chaque transaction pour la région nord.

C'est là que la différence entre une recherche et un filtre devient claire. Imaginez que vous essayiez d'abord de résoudre ce problème en utilisant la formule XLOOKUP suivante :

=XLOOKUP(F2,T_Sales(Region),T_Sales,"No records found")

Vous remarquerez immédiatement un problème : bien que XLOOKUP soit suffisamment intelligent pour diffuser horizontalement, renvoyant la date, le vendeur et le montant pour une seule correspondance, il ne trouve que la première vente, ignorant les trois autres ventes dans cette région.

Obtenir chaque vente, utilisez plutôt la fonction FILTRE dans la cellule H2 :

=FILTER(T_Sales,T_Sales(Region)=F2,"No records found")

Contrairement à XLOOKUP, la fonction FILTER analyse toute la colonne Région et chaque fois qu'elle trouve une correspondance pour la valeur dans F2, elle extrait cette ligne entière dans votre zone de résultats.

Comme il s'agit d'un tableau dynamique et que la formule fait référence à un tableau Excel, si vous ajoutez une nouvelle vente du nord à l'ensemble de données, les résultats s'agrandiront automatiquement pour l'inclure. De même, si vous remplacez la cellule F2 par « Ouest », la liste est mise à jour instantanément.

Exemple 2 : Filtrage selon plusieurs critères

Dans le premier exemple, je vous ai montré comment utiliser FILTER dans Excel pour extraire une liste basée sur une seule condition. Cependant, l’extraction de données réelles nécessite généralement plus de précision.

Supposons que vous souhaitiez extraire toutes les ventes de Miller dans la région nord,

Même si XLOOKUP a été initialement conçu pour fonctionner avec un seul critère, il peut également gérer des recherches complexes en concaténant des valeurs (à l'aide du symbole esperluette) ou en utilisant la logique booléenne. Cependant, cette flexibilité ne change rien au défaut fondamental de XLOOKUP qui consiste à renvoyer une seule correspondance.

La fonction FILTER, en revanche, gère plusieurs critères de manière native : vous pouvez lui demander d'analyser votre table à la recherche de lignes où la condition A et la condition B est vraie et renvoie chaque enregistrement correspondant :

=FILTER(T_Sales,(T_Sales(Salesperson)=F2)*(T_Sales(Region)=F5),"No matches found")

Pourquoi cet astérisque ?

Cela ressemble à un problème mathématique car, pour Excel, c'en est un. Cette méthode s'appuie sur la logique booléenne, où les critères de la formule sont évalués et traduits en valeurs numériques : VRAI devient 1 et FAUX devient 0. En plaçant un astérisque

0 x 1 = 0 (rejeter)

Par conséquent, seules les lignes évaluées à 1 (ou TRUE) sont incluses dans le résultat final. En fait, vous pouvez inclure autant d'exigences que nécessaire, comme une plage de dates spécifique ou un montant minimum : placez simplement chaque condition entre parenthèses et séparez-les par un astérisque.

Vous ne comprenez pas vraiment Excel tant que vous ne connaissez pas ses symboles.

---

Il augmente ou diminue automatiquement à mesure que votre source de données change.