L'impressionnante fonction de calcul FILTRE

Cette fonction récente dans Excel recèle quelques finesses que peu d’utilisateurs ont remarquées. Partons à leur découverte !

Rôle de la fonction

Elle permet de filtrer une matrice de données en fonction de critères. Elle renvoie un tableau dynamique (Voir l’article Formules de tableaux dynamiques, une nouvelle logique de conception).

Par exemple dans un fichier du personnelle, ne renvoyer que les femmes de la comptabilité.

Syntaxe

=FILTRE(Tableau ; Inclure [ ; Si_vide])

• Tableau :  La matrice à filtrer (plage, tableau dynamique, constante matricielle).
• Inclure : Une matrice booléenne dont la hauteur ou largeur est identique à la matrice de l’argument Tableau.
  Si la matrice est verticale/ligne, le filtre s'applique aux lignes, si elle est horizontale/colonne le filtre s'applique aux colonnes.
• Si_vide : Valeur à renvoyer si le filtre ne renvoie aucune réponse (critère Inclure trop strict) sinon la fonction renverra l’erreur #CALC !.

Exemple : Classique critères ET et OU mélangés

Objectif : Les personnes nées après le 1/1/2005 de sexe féminin ainsi que les personnes n’ayant pas d’enfants (quel que soit l’âge ou le sexe).

Cette condition écrite avec les opérateurs logique :
Naissance > 1/1/2005 ET Sexe = F OU NbEnfants = 0.

Rappel : Attention en français la conjonction « et » veut parfois dire ET ou parfois OU en opérateur logique !

Exemple avec ces 2 phrases :

• Je veux les Dijonnais et les Lyonnais.
• Je veux les habitant de Dijon ou de Lyon.

Elles ont le même sens et correspondent à un OU logique.

Astuce : dans OU on a le U de Union donc si la condition ajoute des résultats c’est un OU (ET lui enlève des résultats).

De même le où (accent) correspond à un ET logique 🤪.

=FILTRE(A2:D7;(B2:B7>"1/1/2005"*1)*(C2:C7="f")+(D2:D7=0))

Tableau : A2:D7 > Prendre la ligne d’étiquettes de colonne (ligne1) ne sert à rien car elle ne sera pas sélectionnée dans le résultat du filtre.

Inclure : (B2:B7>"1/1/2005"*1)*(C2:C7="f")+(D2:D7=0)

Décomposons cette matrice :

(B2:B7>"1/1/2005"*1)

*1 est une astuce de conversion (Voir Trucs et astuces pour convertir les types de valeurs).

(C2:C7="f")

La lettre f fonctionne donc ne tient pas compte de la casse (Différence Majuscule/Minuscule).

(D2:D7=0)

Rappel :

= VRAI + 0 =>1 donc VRAI <=> 1
= FAUX + 0 =>0 donc FAUX <=> 0

Donc  (B2:B7>"1/1/2005"*1)*(C2:C7="f") correspond à une matrice ou seules les lignes correspondantes au lignes où les 2 critères sont vérifiés en même temps sont à 1 les autres sont à 0.

L'argument Inclure  (B2:B7>"1/1/2005"*1)*(C2:C7="f")+(D2:D7=0) est donc égale à cette matrice.

La fonction FILTRE de renverra que les lignes correspondantes au 1 dans le même ordre.

Remarque :

• ET est prioritaire sur OU (comme la division sur l’addition) donc en cas de doute ajouter des parenthèses en plus.
• Après validation, à vous d’ajouter des bordures et les intitulés de colonnes.

Filtrage en colonnes

Filtrer les colonnes pour ne garder que celles qui correspondent au critère Inclure.

On va donc utiliser une matrice horizontale/colonne pour inclure.

Exemple

Objectif : Filtrer les colonnes pour ne garder que la colonne Prénom.

=FILTRE(A1:E7 ; A1:E1="Prénom")
=FILTRE(A1:E7 ; A3:E3="Marie")

A1:E1="Prénom" ou A3:E3="Marie" renvoie la matrice de colonne (cf les points) { FAUX . FAUX . FAUX . FAUX . VRAI }.

Remarque : =FILTRE(A1:E7 ; A3:E3=2) renvoie les colonnes N° et NbEnfants.

Comment filtrer les lignes mais n'avoir qu'une seule colonne ?

Pour cela on voit souvent l’utilisation d’une fonction INDEX ou FILTRE en plus.

Exemple :

=INDEX(FILTRE(A2:D7;(B2:B7>"1/1/2005"*1)*(C2:C7="f")+(D2:D7=0));;1)

On utilise ici une propriété peu connue de la fonction INDEX. Quand le n° de Ligne/Colonne est à 0, la fonction ne renvoie pas une valeur mais un vecteur, c’est à dire toute la Ligne/Colonne correspondante.

Mais il y a beaucoup plus simple ! Il n’a jamais été dit que l’on devait englober toute la plage dans l’argument Tableau !

=FILTRE(A2:A7;(B2:B7>"1/1/2005"*1)*(C2:C7="f")+(D2:D7=0))

Comment filtrer en ligne mais "piocher" les colonnes que l'on veut ?

Objectif : Renvoyer la 1ere et la 4eme colonne du tableau filtré.

Solution 1

On englobe la fonction FILTRE dans une autre fonction FILTRE avec une matrice horizontal/en colonne ({1.0.0.1}) précisant ainsi les colonnes à conserver (Voir plus haut Filtrage en colonne).

=FILTRE(FILTRE(A2:D7;(B2:B7>"1/1/2005"*1)*(C2:C7="f")+(D2:D7=0));{1.0.0.1})

Solution 2

Génération de la plage de 2 colonnes à filtrer via CHOISIR.

=FILTRE(CHOISIR({1.2};A2:A7;D2:D7);(B2:B7>"1/1/2005"*1)*(C2:C7="f")+(D2:D7=0))

Solution 3

Idem à Solution2 avec ASSEMB.H (365, >2021).

=FILTRE(ASSEMB.H(A2:A7;D2:D7);(B2:B7>"1/1/2005"*1)*(C2:C7="f")+(D2:D7=0))

Comment avoir une ligne sur deux ?

Objectif : Je veux les lignes ou n° est égale à 1, 3 et 5.

=FILTRE(A2:B7;MOD(SEQUENCE(6);2))

• SEQUENCE fournie une matrice de 1 à 6 : {1;2;3;4;5;6}
• MOD la modifie en {1;0;1;0;1;0}

On voit ici que la matrice Inclure peut ne pas être liée aux données du tableau tant qu’elle possède la bonne taille (j’ai pris 2 colonnes arbitrairement).
On peut remplacer directement Tableau ou Inclure par une constante matricielle (Voir Excel et les matrices (pas celles du cours de mathématiques)).

=FILTRE(A2:B7;{1;0;1;0;1;0})

Donne le même résultat !

Quelques exemples

=FILTRE(B2:B7;MOIS(B2:B7)=11)

=FILTRE(E2:E7;GAUCHE(E2:E7;1)="P")

=FILTRE(E2:E7;ESTNUM(CHERCHE("Pi";E2:E7)))

=FILTRE(E2:E7;NBCAR(E2:E7)<=5)

=FILTRE(E2:E7;NB.SI.ENS(E2:E7;E2:E7)>1)

=FILTRE(E2:E7;NB.SI.ENS(E2:E7;E2:E7)=1)

=FILTRE(E2:E7;D2:D7>=GRANDE.VALEUR(D2:D7;3))

Comment filtrer un tableau avec une zone nommée unique ?

La plage A1:E7 est nommée Maplage. Pour faire référence à la 4eme colonne par exemple dans l’argument Inclure de la fonction FILTRE, on va utiliser la fonction INDEX.

Exemple : Filtrer tout le tableau MaPlage avec un NbEnfants>2 (NbEnfants est la 4eme colonne).

=FILTRE(MaPlage;INDEX(MaPlage;;4)>2)

Merci pour votre attention bienveillante.