3️⃣ Comment filtrer des données selon plusieurs critères avec la formule Excel FILTRE

(Last Updated On: 18 juillet 2020)
Voici un troisième article sur les nouvelles formules de tableaux dynamiques d’Excel.

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.

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

Ce fichier contient un exemple d'utilisation de la nouvelle formule FILTRE permettant d'effectuer des filtres dynamiques de plages de valeurs avec une formule Excel.
Télécharger

Envoyer le lien de téléchargement à :

Oui, je consens à recevoir la Newsletter. Je confirme avoir lu et accepté la Politique de confidentialité.

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)

Cliquez ici pour en savoir plus

 

Souscrivez à ma newsletter :

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *