L'essentiel —
FILTERrépond à une autre question queRECHERCHEV. Une recherche renvoie une correspondance ;FILTERrenvoie toutes les lignes qui remplissent votre condition et les déverse dans une plage qui se met à jour seule. Syntaxe :=FILTER(matrice; inclure; [si_vide]). Trois choses font trébucher tout le monde : sans correspondance vous obtenez#CALC!sauf si vous passez le troisième argument (=FILTER(data; cond; "Aucun résultat")) ; pour combiner des conditions on multiplie les matrices pour ET et on les additionne pour OU ((A=x)*(B=y)), jamaisET()/OU(); et une plage de déversement bloquée donne#SPILL!. Disponible dans Excel 365 et 2021+.
=FILTER(Sales, Region = "West")
=FILTER(Sales, (Region = "West") * (Amount > 1000), "Aucun résultat")
Pendant vingt ans, sortir « toutes les lignes où région = Ouest » imposait une
colonne intermédiaire, le filtre automatique et un copier-coller — ou une fragile
formule matricielle. FILTER en fait une seule expression qui se recalcule dès
que les données source changent. Mais comme elle renvoie une matrice et non une
valeur unique, elle se comporte autrement qu'une recherche, et les messages
d'erreur sont eux aussi inédits.
Remarque : dans une interface Excel en français, la fonction s'appelle FILTRE. Équivalents : UNIQUE = UNIQUE, SORT = TRIER, SORTBY = TRIER.PAR. Les formules ci-dessous utilisent les noms anglais ; le comportement est identique.
Ce que vous allez apprendre
- Pourquoi
FILTERest une requête (renvoyer plusieurs) et une recherche un repérage (renvoyer une) - L'astuce ET / OU : multiplier et additionner les matrices — pas
ET()/OU() - Pourquoi un résultat vide donne
#CALC!et le seul argument qui le corrige - Ce qui provoque vraiment
#SPILL!et comment le dégager - La « formule reine »
=SORT(UNIQUE(FILTER(...)))pour des listes propres et vivantes
Le modèle mental : FILTER est une requête, pas une recherche
Si vous connaissez SQL, FILTER est la clause WHERE : donne-moi chaque ligne
où c'est vrai. RECHERCHEV et RECHERCHEX font
l'inverse — elles parcourent de haut en bas et s'arrêtent à la première
correspondance. Cette seule différence explique toutes les surprises de FILTER.
Une recherche renvoie une valeur dans une cellule. FILTER renvoie un bloc de
lignes et les déverse vers le bas (et la droite) depuis la cellule où vous
tapez la formule. Pas de recopie, pas de validation matricielle : vous l'écrivez
une fois, Excel adapte la taille de la sortie aux données.
' Une correspondance, une cellule :
=XLOOKUP("West", Region, Sales) ' la première ligne Ouest
' Chaque correspondance, déversée dans une plage :
=FILTER(Sales, Region = "West") ' toutes les lignes Ouest, autant qu'il y en a
Voyez le deuxième argument, inclure, comme une colonne de VRAI/FAUX de la même
hauteur que vos données. Region = "West" ne compare pas une cellule — il compare
toute la colonne Région d'un coup et produit une matrice de VRAI/FAUX.
FILTER garde les lignes dont la valeur est VRAI.
La règle qui débloque tout : multiplier pour ET, additionner pour OU
C'est le plus utile à savoir sur FILTER, et la plupart des tutoriels l'enterrent.
Votre instinct écrit ET(Region="West"; Amount>1000). Ça ne marche pas —
ET() réduit toute la matrice à un seul VRAI/FAUX, donc FILTER renvoie tout ou
rien. L'argument inclure doit rester une matrice. Vous faites donc l'algèbre
booléenne vous-même :
' ET — multiplier les conditions (VRAI*VRAI = 1, tout *FAUX = 0)
=FILTER(Sales, (Region = "West") * (Amount > 1000), "Aucun résultat")
' OU — additionner les conditions (FAUX+FAUX = 0, tout > 0 est gardé)
=FILTER(Sales, (Region = "West") + (Region = "East"), "Aucun résultat")
La multiplication est le ET logique car VRAI*VRAI = 1 et tout *FAUX = 0.
L'addition est le OU logique car une ligne survit dès qu'un terme vaut 1.
Entourez chaque condition de parenthèses — la comparaison doit précéder le calcul.
Une fois ce déclic fait, des critères aussi complexes soient-ils ne sont que de
l'arithmétique sur des matrices de 1 et de 0.
Pourquoi FILTER donne #CALC! — et l'argument qui l'empêche
C'est le signalement numéro un « FILTER ne fonctionne pas ». Quand aucune ligne
ne correspond, le résultat est une matrice vide, et Excel ne sait pas afficher
« rien » dans une cellule — il renvoie donc l'erreur #CALC!. Ce n'est pas un
bug de votre logique ; c'est l'allure d'une matrice vide. Le remède est le
troisième argument facultatif, si_vide :
=FILTER(Sales, Region = "North") ' -> #CALC! si aucune ligne Nord
=FILTER(Sales, Region = "North", "Aucun résultat") ' -> "Aucun résultat" (propre)
=FILTER(Sales, Region = "North", "") ' -> vide, si vous préférez
Faites de si_vide une habitude sur chaque FILTER qui alimente un tableau
de bord ou une autre formule. Un #CALC! surprise se propage — tout ce qui
référence le déversement hérite de l'erreur. Passer si_vide transforme « aucun
résultat » en une valeur que vous contrôlez, au lieu d'une erreur rouge qui casse
le reste de la feuille.
#SPILL! concerne les voisins, pas la formule
FILTER a besoin de cellules vides pour se déverser. Si quoi que ce soit — une
valeur égarée, une cellule fusionnée, un vieux libellé — occupe la plage que le
résultat veut prendre, Excel ne peut pas poser la matrice et renvoie #SPILL!.
La formule est correcte ; c'est la destination qui est bloquée. Cliquez sur la
cellule et Excel surligne la plage de déversement visée. Dégagez ce qui s'y trouve
et le résultat apparaît. Deux règles pratiques : ne placez jamais un FILTER
juste au-dessus de données existantes, et ne remplissez pas une colonne entière
(A:A) avec quelque chose qui tomberait dans une zone de déversement.
Référencer un résultat déversé avec l'opérateur
Comme la taille de sortie varie avec les données, on ne la référence pas par une
plage fixe. On pointe le déversement avec l'opérateur # : si votre FILTER
est en E2, alors E2# signifie « toute la plage déversée, à la taille qu'elle a
en ce moment ». C'est ainsi qu'on alimente un graphique, un COUNTA ou une liste
déroulante qui grandit et rétrécit seule :
=COUNTA(E2#) ' combien de lignes FILTER a renvoyées, en direct
=SUM(F2#) ' total d'une colonne de montants déversée
La formule reine : SORT, UNIQUE et FILTER ensemble
FILTER compte tant parce qu'elle se compose. Imbriquez-la dans
UNIQUE et SORT et
vous obtenez une seule formule qui s'entretient seule, là où la plupart procèdent
en trois étapes manuelles :
=SORT(UNIQUE(FILTER(Customer, Region = "West")))
Lisez de l'intérieur vers l'extérieur : FILTER extrait les clients de l'Ouest,
UNIQUE retire les doublons, SORT classe par ordre alphabétique — et le tout se
relance dès qu'une vente arrive. C'est la source canonique d'une liste déroulante
dynamique ou d'une liste de tableau de bord. Pas de colonnes intermédiaires, pas
de bouton actualiser, pas de VBA.
Le jugement : quand FILTER remplace l'ancienne méthode
Si vous vous surprenez à ajouter une colonne intermédiaire et à activer le filtre
automatique juste pour copier des lignes correspondantes, c'est le signal pour
passer à FILTER. Si vous tapez une formule matricielle {=...} pour extraire un
sous-ensemble, ce schéma a dix ans de retard sur 365. L'exception honnête : sur
des feuilles énormes, FILTER se recalcule avec tout le classeur ; pour une
extraction unique d'un million de lignes, le filtre automatique ou Power Query
restent plus légers. Pour tout ce qui doit rester vivant, FILTER l'emporte.
Comment ExcelMaster aide
Beaucoup de travail avec FILTER revient à « construis-moi la vue vivante » :
toutes les factures impayées en retard, triées par montant, sur leur propre
onglet. ExcelMaster écrit cette formule — y compris l'algèbre booléenne
ET/OU et la protection si_vide — à partir d'une description en langage naturel,
la place où vous voulez et vérifie que la plage de déversement est dégagée. Vous
gardez la logique ; il gère la mécanique des matrices qui rend FILTER capricieux
à la main.
Questions fréquentes
Pourquoi ma formule FILTER renvoie-t-elle #CALC! ?
Parce qu'aucune ligne ne correspondait, et une matrice vide ne peut pas s'afficher
dans une cellule. Ajoutez le troisième argument, si_vide : =FILTER(data; condition; "Aucun résultat"). Cela remplace l'erreur par une valeur de votre
choix.
Comment filtrer sur plusieurs critères dans Excel ?
Gardez l'argument inclure sous forme de matrice et faites de l'algèbre
booléenne : multipliez les conditions pour ET — (A=x)*(B=y) — et additionnez-les
pour OU — (A=x)+(B=y). N'utilisez pas ET()/OU() ; elles réduisent la matrice
à une seule valeur.
Qu'est-ce qui provoque l'erreur #SPILL! avec FILTER ?
Quelque chose bloque les cellules où le résultat veut se déverser — une valeur, une cellule fusionnée ou un format dans la plage de déversement. Cliquez sur la cellule de la formule pour voir la plage surlignée, dégagez ce qui s'y trouve et le résultat apparaît.
FILTER fonctionne-t-il dans Excel 2016 ou 2019 ?
Non. FILTER et les autres fonctions de matrice dynamique exigent Excel 365 ou
Excel 2021+. Sur 2016/2019, utilisez le filtre automatique, une formule
matricielle ou Power Query.
Testé dans
Testé dans : Excel 365 (Windows 11) — dernière vérification le 16/06/2026.
Guides connexes : Excel UNIQUE · Excel SORT · XLOOKUP dans Excel