Quelle est la différence entre la fonction FILTER et les filtres automatiques d'Excel ?

La fonction FILTER crée une nouvelle plage de données filtrée, laissant les données originales intactes. Les filtres automatiques masquent simplement les lignes qui ne correspondent pas aux critères, modifiant ainsi la vue des données originales. De plus, FILTER est plus flexible pour des critères complexes.

Puis-je utiliser la fonction FILTER avec des données provenant de plusieurs feuilles de calcul ?

Oui, vous pouvez utiliser la fonction FILTER avec des données provenant de plusieurs feuilles de calcul en référençant les plages de cellules appropriées dans chaque feuille. Assurez-vous que les plages ont la même hauteur pour éviter les erreurs.

La fonction FILTER est-elle disponible dans toutes les versions d'Excel ?

Non, la fonction FILTER est disponible uniquement dans Excel 365 et les versions ultérieures. Si vous utilisez une version antérieure d'Excel, vous devrez utiliser les filtres automatiques ou le filtre avancé.

Comment filtrer vos données Excel avec la fonction FILTER : Guide pratique

Comprendre la fonction FILTER Excel

La fonction FILTER dans Excel est une fonction de tableau dynamique introduite dans Excel 365 et les versions ultérieures. Elle permet de filtrer une plage de données en fonction d'un ou plusieurs critères, renvoyant un nouveau tableau contenant uniquement les lignes qui correspondent à ces critères. C'est une alternative moderne et plus flexible aux filtres automatiques traditionnels.

Syntaxe de la fonction FILTER

La syntaxe de la fonction FILTER est la suivante :

=FILTER(tableau, inclusion, [si_vide])

tableau : La plage de cellules que vous souhaitez filtrer.
inclusion : Une matrice de valeurs booléennes (VRAI ou FAUX) qui déterminent quelles lignes du tableau doivent être incluses dans le résultat. Une valeur VRAI indique que la ligne doit être incluse, et une valeur FAUX indique qu'elle doit être exclue. Cette matrice doit avoir la même hauteur que le tableau.
[si_vide] (facultatif) : La valeur à renvoyer si aucun enregistrement ne correspond aux critères de filtrage. Si cet argument est omis, la fonction renvoie une erreur #CALC!.

Prérequis

Avant de commencer à utiliser la fonction FILTER, assurez-vous de disposer d'une version d'Excel qui la prend en charge. La fonction FILTER est disponible dans Excel 365 et les versions ultérieures.

Utilisation de base de la fonction FILTER

Voyons comment utiliser la fonction FILTER avec un exemple simple. Imaginez que vous avez une liste de clients avec leurs noms, villes et chiffres d'affaires dans les colonnes A, B et C respectivement. Vous souhaitez filtrer cette liste pour afficher uniquement les clients situés à Paris.

Sélectionnez une cellule vide où vous souhaitez que le résultat du filtre apparaisse. Par exemple, la cellule E1.
Entrez la formule suivante dans la cellule E1 :

excel =FILTER(A1:C10, B1:B10="Paris", "Aucun client à Paris")
- A1:C10 est la plage de données contenant les noms, villes et chiffres d'affaires des clients.
- B1:B10="Paris" est la condition de filtrage. Elle vérifie si la valeur dans la colonne B (ville) est égale à "Paris". Elle renvoie une matrice de valeurs VRAI ou FAUX.
- "Aucun client à Paris" est la valeur à renvoyer si aucun client n'est situé à Paris.
Appuyez sur Entrée. Excel affichera alors une plage de cellules contenant uniquement les clients situés à Paris. Si aucun client n'est situé à Paris, la cellule E1 affichera le texte "Aucun client à Paris".

Explication détaillée

Dans cet exemple, la fonction FILTER examine chaque ligne de la plage A1:C10. Pour chaque ligne, elle évalue la condition B1:B10="Paris". Si la condition est VRAI (c'est-à-dire, si la ville dans la colonne B est "Paris"), la ligne entière est incluse dans le résultat. Si la condition est FAUX, la ligne est exclue.

Le résultat est un nouveau tableau dynamique qui s'étend à partir de la cellule E1. Ce tableau contient uniquement les lignes qui correspondent à la condition de filtrage.

Filtrer avec plusieurs critères

La fonction FILTER permet également de filtrer les données en utilisant plusieurs critères. Vous pouvez combiner plusieurs conditions en utilisant les opérateurs logiques * (ET) et + (OU).

Utilisation de l'opérateur ET (*)

L'opérateur * est utilisé pour combiner deux conditions avec un ET logique. Cela signifie que les deux conditions doivent être VRAI pour qu'une ligne soit incluse dans le résultat.

Par exemple, si vous souhaitez filtrer la liste des clients pour afficher uniquement les clients situés à Paris et dont le chiffre d'affaires est supérieur à 10 000 €, vous pouvez utiliser la formule suivante :

=FILTER(A1:C10, (B1:B10="Paris") * (C1:C10>10000), "Aucun client correspondant")

Dans cette formule, (B1:B10="Paris") vérifie si la ville est "Paris", et (C1:C10>10000) vérifie si le chiffre d'affaires est supérieur à 10 000 €. L'opérateur * combine ces deux conditions. Une ligne est incluse dans le résultat uniquement si la ville est "Paris" ET le chiffre d'affaires est supérieur à 10 000 €.

Utilisation de l'opérateur OU (+)

L'opérateur + est utilisé pour combiner deux conditions avec un OU logique. Cela signifie qu'au moins une des deux conditions doit être VRAI pour qu'une ligne soit incluse dans le résultat.

Par exemple, si vous souhaitez filtrer la liste des clients pour afficher uniquement les clients situés à Paris OU à Lyon, vous pouvez utiliser la formule suivante :

=FILTER(A1:C10, (B1:B10="Paris") + (B1:B10="Lyon"), "Aucun client correspondant")

Dans cette formule, (B1:B10="Paris") vérifie si la ville est "Paris", et (B1:B10="Lyon") vérifie si la ville est "Lyon". L'opérateur + combine ces deux conditions. Une ligne est incluse dans le résultat si la ville est "Paris" OU si la ville est "Lyon".

Filtrer avec des critères complexes

Vous pouvez créer des critères de filtrage plus complexes en utilisant des fonctions Excel à l'intérieur de la fonction FILTER. Par exemple, vous pouvez utiliser la fonction CHERCHE pour rechercher une chaîne de caractères dans une colonne, ou la fonction DATE pour filtrer les données en fonction d'une date.

Exemple avec la fonction CHERCHE

La fonction CHERCHE recherche une chaîne de caractères à l'intérieur d'une autre chaîne de caractères et renvoie la position de la première occurrence de la chaîne recherchée. Si la chaîne recherchée n'est pas trouvée, la fonction renvoie une erreur #VALEUR!.

Par exemple, si vous souhaitez filtrer la liste des clients pour afficher uniquement les clients dont le nom contient la lettre "A", vous pouvez utiliser la formule suivante :

=FILTER(A1:C10, ISNUMBER(CHERCHE("A", A1:A10)), "Aucun client correspondant")

Dans cette formule, CHERCHE("A", A1:A10) recherche la lettre "A" dans la colonne A (noms des clients). La fonction ISNUMBER vérifie si le résultat de la fonction CHERCHE est un nombre (c'est-à-dire, si la lettre "A" a été trouvée). Si la lettre "A" est trouvée, la fonction ISNUMBER renvoie VRAI, et la ligne est incluse dans le résultat.

Exemple avec la fonction DATE

La fonction DATE renvoie une date en fonction de l'année, du mois et du jour que vous spécifiez.

Par exemple, si vous avez une colonne contenant des dates de commande et que vous souhaitez filtrer les commandes passées en janvier 2023, vous pouvez utiliser la formule suivante :

=FILTER(A1:D10, (YEAR(C1:C10)=2023) * (MONTH(C1:C10)=1), "Aucune commande en janvier 2023")

Dans cette formule, YEAR(C1:C10) extrait l'année de la colonne C (dates de commande), et MONTH(C1:C10) extrait le mois. La formule filtre ensuite les données pour afficher uniquement les commandes passées en janvier 2023.

Erreurs courantes et comment les éviter

Lors de l'utilisation de la fonction FILTER, vous pouvez rencontrer certaines erreurs courantes. Voici quelques-unes de ces erreurs et comment les éviter :

Erreur #CALC! : Cette erreur se produit si aucun enregistrement ne correspond aux critères de filtrage et que vous n'avez pas spécifié l'argument [si_vide]. Pour éviter cette erreur, spécifiez une valeur à renvoyer si aucun enregistrement ne correspond aux critères de filtrage, par exemple "Aucun résultat".
Erreur #VALEUR! : Cette erreur peut se produire si la matrice d'inclusion (le deuxième argument de la fonction FILTER) n'a pas la même hauteur que le tableau (le premier argument). Assurez-vous que la matrice d'inclusion a la même hauteur que le tableau.
Résultats inattendus : Si vous obtenez des résultats inattendus, vérifiez attentivement vos critères de filtrage. Assurez-vous que les conditions sont correctes et que vous utilisez les bons opérateurs logiques (* pour ET, + pour OU).

Astuces et bonnes pratiques

Voici quelques astuces et bonnes pratiques pour utiliser la fonction FILTER de manière efficace :

Utilisez des noms de plages : Au lieu d'utiliser des références de cellules directes (par exemple, A1:C10), utilisez des noms de plages. Cela rend vos formules plus lisibles et plus faciles à maintenir. Pour définir un nom de plage, sélectionnez la plage de cellules et entrez un nom dans la zone de nom située à gauche de la barre de formule.
Utilisez des cellules de référence pour les critères : Au lieu d'entrer les critères de filtrage directement dans la formule, utilisez des cellules de référence. Cela vous permet de modifier facilement les critères de filtrage sans avoir à modifier la formule. Par exemple, vous pouvez entrer la ville "Paris" dans la cellule E1 et utiliser la formule =FILTER(A1:C10, B1:B10=E1, "Aucun client correspondant").
Combinez la fonction FILTER avec d'autres fonctions : La fonction FILTER peut être combinée avec d'autres fonctions Excel pour effectuer des analyses plus complexes. Par exemple, vous pouvez combiner la fonction FILTER avec la fonction SOMME pour calculer la somme des chiffres d'affaires des clients situés à Paris.
Profitez des tableaux structurés : Les tableaux structurés d'Excel (Insertion > Tableau) offrent de nombreux avantages, notamment la possibilité de référencer facilement les colonnes par leur nom, ce qui rend les formules FILTER plus lisibles et robustes.

Alternatives à la fonction FILTER

Bien que la fonction FILTER soit un outil puissant, il existe d'autres façons de filtrer les données dans Excel. Voici quelques alternatives :

Filtres automatiques : Les filtres automatiques sont la méthode de filtrage la plus courante dans Excel. Pour appliquer un filtre automatique, sélectionnez une plage de cellules et cliquez sur Données > Filtrer. Vous pouvez ensuite filtrer les données en cliquant sur les flèches situées dans les en-têtes de colonnes.
Filtre avancé : Le filtre avancé permet de filtrer les données en utilisant des critères plus complexes que les filtres automatiques. Pour utiliser le filtre avancé, cliquez sur Données > Avancé. Vous devez ensuite spécifier la plage de données à filtrer, la plage de critères et la plage où vous souhaitez que le résultat du filtre apparaisse.
Tableaux croisés dynamiques : Les tableaux croisés dynamiques sont un outil puissant pour analyser et résumer les données. Vous pouvez utiliser les tableaux croisés dynamiques pour filtrer les données en fonction de différents critères.
Power Query : Power Query est un outil d'extraction, de transformation et de chargement (ETL) intégré à Excel. Power Query permet de se connecter à différentes sources de données, de transformer les données et de les charger dans Excel. Vous pouvez utiliser Power Query pour filtrer les données lors de l'importation.

En conclusion, la fonction FILTER est un outil puissant et flexible pour filtrer les données dans Excel. Elle offre de nombreux avantages par rapport aux méthodes de filtrage traditionnelles, notamment la possibilité de filtrer les données en fonction de critères complexes et de créer des tableaux dynamiques qui se mettent à jour automatiquement lorsque les données sources changent. En maîtrisant la fonction FILTER, vous pouvez gagner du temps et améliorer votre productivité lors de l'analyse de vos données Excel.