Ayant récemment utilisé la formule FILTRE de manière plus complexe, je vous partage ici le cas où l’on souhaite filtrer les données selon plusieurs critères, qui contiennent plusieurs valeurs, ou selon un texte spécifique.
Les formules FILTRE, UNIQUE, TRIER, TRIERPAR sont disponibles avec Microsoft 365.
Formule FILTRE avec un seul critère
Il y a 2 semaines environ, j’ai publié un premier article sur l’utilisation de la formule FILTRE avec un ou plusieurs critères simples.
Voici pour rappel la syntaxe générale de la formule FILTRE :
=FILTRE (tableau; inclure; [si_vide])
- matrice – La matrice ou plage à trier.
- inclure – Critère de filtre, matrice.
- si_vide – [facultatif] La valeur à renvoyer si toutes les valeurs dans la matrice incluse sont vides (filtre ne renvoie rien).
A l’utilisation, cela donne par exemple :
=FILTRE(A2:F10; A2:A10="G20";"")
👉 Cette formule sert à filtrer la table A1:F10 selon la première colonne A contenant le nom du magasin “G20”.
Formule FILTRE avec plusieurs critères
Dans ce cas, nous utilisons l’opérateur de multiplication (*) pour renvoyer toutes les valeurs dans notre table (A1:F10) qui possèdent « soda » et dont le client est « G20» :
=FILTRE(A2:F10;(A2:A10="G20")*(B2:B10="soda");"")
Formule FILTRE “contient plusieurs valeurs”
La syntaxe pour filtrer une table selon une liste de valeurs est la suivante :
=FILTRE(table; ESTNUM(EQUIV(colonne;liste;0));"")
Par exemple, dans l’image ci-dessous, je souhaite filtrer ma table des charges (onglet Données) par nature de compte selon une liste de natures comptables renseignée dans la plage C7:C11.
Pour cela j’utilise la formule ci-dessus que j’adapte comme suivant :
=FILTRE(TabCouts[#Données];ESTNUM(EQUIV(TabCouts[Lib GroupeNC];C7:C11;0));"")
Explication
La fonction FILTRE peut filtrer les données à l’aide d’une expression logique fournie comme argument “inclure”. Dans cet exemple, cet argument est créé avec une expression qui utilise les fonctions ESTNUM et EQUIV comme ceci :
=ESTNUM(EQUIV(colonne;liste;0))
La fonction EQUIV va rechercher pour chaque libellé de nature comptable situé en colonne Lib GroupeNC si il y a une correspondance avec la liste des libellés de nature comptable en C7:C11. La fonction EQUIV renvoie un tableau des positions des libellés trouvés dans la liste C7:C11, ou renvoie une erreur si aucune correspondance n’est trouvée.
Par exemple : {1;#N/A;#N/A;#N/A;2;#N/A}
Ensuite la fonction ESTNUM convertit ce tableau de position en valeurs VRAI si une position existe, ou en valeur FAUX si aucune position existe (erreur renvoyée par EQUIV).
Par exemple : {VRAI;FAUX;FAUX;FAUX;VRAI;FAUX}
Le tableau de valeurs VRAI ou FAUX est enfin fourni à la fonction FILTRE en tant qu’argument “inclure”, et FILTRE renvoie uniquement les lignes de la table à filtrer qui correspondent à une valeur VRAI.
Le résultat est une table filtrée démarrant en cellule B18, selon la liste de valeurs spécifiées.
Formule FILTRE “contient le texte”
La syntaxe pour filtrer la table selon un filtre “contient du texte spécifique” est la suivante :
=FILTRE(table; ESTNUM(TROUVE("texte";colonne));"")
Par exemple, dans l’image ci-dessous, je souhaite filtrer ma table des charges (onglet Données) par Centre de Profit qui contiennent le texte “HORS PROD” (cellule D7).
Pour cela j’utilise la formule ci-dessus que j’adapte comme suivant :
=FILTRE(TabCouts[#Données];ESTNUM(TROUVE(D7;TabCouts[Centre de Profit]));"")
Le résultat est une table filtrée démarrant en cellule B18, selon le texte spécifié.
👇 Téléchargez le fichier Excel en exemple pour vous entraîner !
📥Téléchargement
Téléchargez le classeur Excel pour ce tutoriel. Seuls votre adresse e-mail et nom vous seront demandés dans la prochaine étape. Vous recevrez le lien de téléchargement par e-mail.
Formule FILTRE multi-critères
Envoyer le lien de téléchargement à :
Maîtrisez l’essentiel des formules Excel
Enfin, transformez-vous en Expert d’Excel. Devenez un membre essentiel de votre équipe qui obtient des résultats rapidement. Obtenez une sécurité de l’emploi et une mobilité ascendante imbattable. Tout ce qu’il vous faut, c’est construire à partir de vos connaissances actuelles d’Excel, étape par étape. Pour cela, rejoignez le programme 👏👏 :
🥇 FORMULES EXCEL ULTIMES (2 MODULES OFFERTS)
