La fonction FILTRE dans Microsoft Excel est l'une des fonctions les plus cruciales à maîtriser. Sans cela, vous aurez du mal à trouver les données dont vous avez besoin. Voici un cours intensif sur l'utilisation de FILTER dans Excel.
Il est également utile de noter que cette fonction n'est pas le seul moyen de filtrer les données dans MS Excel. Vous disposez d'outils tels que le filtre automatique et le filtre avancé pour obtenir le même résultat, avec quelques mises en garde importantes dont nous parlerons dans ce guide.
Qu'est-ce que la fonction FILTRE ?
Fonctions ou formules Excel sont le pain quotidien d'Excel, vous permettant de faire des choses comme trouver la moyenne d'un grand ensemble de données ou création d'un graphique de courbe de Bell. Chaque fonction a sa syntaxe, que vous pouvez généralement vérifier en entrant simplement le nom de la fonction dans Excel.
La fonction Excel FILTER, comme son nom l'indique, est utilisée pour « filtrer » les valeurs d'une plage spécifiée en fonction de certaines conditions. La plage et les conditions à utiliser sont saisies avec la fonction, ce qui la rend extrêmement personnalisable.
Avec les bons paramètres, vous pouvez extraire les informations exactes dont vous avez besoin d'une feuille de calcul sans avoir à tout parcourir pour trouver manuellement les entrées correspondantes. Et comme la sortie est contenue dans une cellule, vous pouvez enchaîner plus de fonctions pour effectuer des calculs ou visualiser les résultats dans un graphique.
Pourquoi la fonction FILTER est-elle préférée à la fonction FILTER ? Filtre avancé ?
La plupart des Excel débutants s'en tiennent aux outils de filtrage de données intégrés dans Excel plutôt que d'essayer d'apprendre la syntaxe d'une fonction. Le filtre Auto est le plus simple, vous permettant d'exclure des colonnes et de définir des critères de filtrage à partir d'une interface basée sur un menu. Ensuite, il y a le filtre Avancé avec sa capacité à appliquer plusieurs critères pour mettre en œuvre des schémas de filtrage complexes.
Alors pourquoi s'embêter à utiliser la fonction FILTER ?
Le principal avantage de l'utilisation des fonctions Excel par rapport à toute opération manuelle (avec un autre outil Excel ou même tout autre programme) est que les fonctions sont dynamiques. Le filtre Auto ou le filtre Avancé vous donne des résultats ponctuels qui ne changent pas lorsque les données sources sont modifiées. La fonction FILTER, quant à elle, met à jour ses résultats en conséquence lorsque les données changent.
Syntaxe de la fonction FILTER
La syntaxe de la formule FILTER est assez simple :
=FILTER(tableau, inclusion, [if_empty])
Un tableau est un sous-ensemble rectangulaire de la feuille de calcul, indiqué en spécifiant une plage entre la cellule supérieure gauche et la cellule inférieure droite. Par exemple, A3:E10 est un tableau composé des colonnes A à E et des lignes 3 à 10..
Le paramètre suivant est simplement le critère à utiliser, ou plus techniquement un tableau booléen. Ceci est saisi sous la forme d'une expression évaluant la valeur d'une plage de cellules (généralement une colonne) qui renvoie VRAI ou FAUX. Par exemple, A3:A10=”Pass” renverra TRUE lorsque la valeur de la cellule correspond à la chaîne donnée.
Enfin, vous pouvez saisir une valeur qui sera renvoyée par la fonction FILTER lorsqu'aucune ligne ne correspond aux conditions. Il peut s'agir d'une simple chaîne telle que « Aucun enregistrement trouvé ».
Connexe : Besoin de voir avec quelle précision les données d'un échantillon s'adaptent à un ensemble de données plus vaste ? Consultez notre guide sur calculer l'erreur type dans Excel.
Utiliser la fonction FILTER
Maintenant que nous connaissons la syntaxe de la fonction FILTER, voyons comment utiliser réellement FILTER dans une feuille de calcul.
Les exemples de données que nous utilisons pour cette démonstration comportent un tableau allant de A2 à F11, regroupant les scores en biologie de dix étudiants ainsi que la distribution normale.
Écrivons une fonction pour filtrer les entrées en fonction de leurs résultats d'examen (répertoriés dans la colonne D) et renvoyons uniquement celles dont le score est inférieur à 30. Cela devrait être la syntaxe :
=FILTER(A2:F11,D2:D11<30, "Aucune correspondance trouvée")
Étant donné que les résultats filtrés constituent un sous-ensemble du tableau, utilisez la fonction dans une cellule avec suffisamment d'espace après. Nous le ferons en dessous du tableau d'origine :
Et nous obtenons les résultats escomptés. Toutes les entrées avec un score inférieur à 30 sont choisies et affichées dans le même format de tableau.
Vous n'êtes pas non plus limité à une seule condition. Utilisez l'opérateur AND (*) pour chaîner plusieurs expressions en tant que paramètre unique, créant ainsi un filtre plus complexe.
Construisons une fonction qui renvoie les entrées comprises entre 30 et 70 points. Voici la syntaxe et les résultats :
=FILTER(A2:F11,(D2:D11>30)*(D2:D11<70),« Aucune correspondance trouvée »)
Pour les critères non exclusifs, vous pouvez également utiliser l'opérateur OR (+). Cela correspond au filtre même si une seule des conditions incluses est évaluée à TRUE.
Dans la formule suivante, nous l'utilisons pour trouver les valeurs aberrantes, en filtrant les résultats inférieurs à 15 ou supérieurs à 70.
=FILTER(A2:F11,(D2:D11<30)+(D2:D11>70),,”Aucun enregistrement trouvé”)
Enfin, au lieu d'utiliser une seule valeur ou chaîne à renvoyer lorsque la fonction FILTER ne trouve rien, vous pouvez spécifier des valeurs pour chaque colonne afin de garantir que la sortie reste toujours dans un format cohérent..
Tout d'abord, essayons une condition dont nous savons qu'elle est fausse pour voir à quoi elle ressemble par défaut :
=FILTER(A2:F11,D2:D11>90, "Aucune correspondance trouvée")
Comme vous pouvez le constater, le résultat ne comporte qu'une seule chaîne, en contradiction avec le format attendu. Ce n'est généralement pas un problème, sauf si vous cherchez à insérer les résultats (ou certaines valeurs de ceux-ci) dans une autre formule.
Essayons donc de donner des valeurs par défaut dans le même format qu'une entrée du tableau. Nous pouvons le faire en spécifiant des valeurs séparées par des virgules et placées entre accolades. Comme ceci :
=FILTER(A2:F11,D2:D11>90,{"Aucun enregistrement", "Aucun enregistrement", "Aucun enregistrement", 0})
Cela nous donne des résultats plus acceptables, cohérents avec le reste du format de la feuille de calcul.
La fonction FILTER en vaut-elle la peine ?
Même si vous utilisez uniquement MS Excel pour conserver des enregistrements et que vous n'avez pas l'intention d'effectuer des calculs sophistiqués, la fonction FILTER est l'une des rares que vous devriez quand même examiner.
Car une fois que votre classeur atteint une certaine taille, la localisation manuelle des données peut être pénible. Et même si les outils de filtrage automatique et de filtrage avancé sont pratiques, l'utilisation d'une fonction est en réalité plus pratique à long terme puisque les résultats se mettent à jour automatiquement et peuvent être associés à d'autres fonctions.
.