Vous connaissez tous ou presque la possibilité de filtrer les données dans Google Sheets en utilisant le bouton Filtrer dans la barre d’outils. C’est une méthode assez pratique, mais elle est manuelle. Il faut donc faire quelques clics pour que cela fonctionne. Si vous voulez automatiser le filtrage dans Google Sheets, il vaut mieux utiliser la fonction FILTRE. Dans ce tutoriel, nous vous expliquerons comment faire cela.
Comprendre la fonction FILTRE dans Google Sheets
La fonction FILTRE de Google Sheets permet de filtrer des sous-ensembles de données à partir d’une plage de données spécifiée en fonction d’une condition donnée.
La fonction et la fonctionnalité de filtrage sont deux choses différentes. La fonction est utilisée dans les formules pour filtrer des sous-ensembles de données, tandis que la fonctionnalité est un bouton dans la barre d’outils qui filtre l’ensemble des données.
Syntaxe de la fonction FILTRE dans Google Sheets
=FILTRE(plage_de_données, condition_1, condition_2, ...)- plage_de_données : une plage de cellules à filtrer. Exemple : A2:A
- condition : une plage de cellules qui contient des valeurs TRUE ou FALSE pour les critères de filtrage. Les critères de filtrage contiennent généralement les opérateurs de comparaison (« = », « < » et « > »), par exemple A2:A>20. Cependant, vous pouvez également utiliser la fonction REGEXMATCH dans la condition de filtrage.
Si une cellule contient les critères de filtrage, vous pouvez faire référence à cette cellule dans la condition de filtrage. Exemple : A2:A>C1. Pour en savoir plus sur la manière de faire référence à une plage de données spécifique à partir d’une autre feuille, lisez notre article de blog sur la façon de lier des données entre plusieurs feuilles de calcul.
Exemple de formule FILTRE dans Google Sheets
Reprenons l’exemple présenté dans la vidéo « Google Sheets FILTER – 5 conseils pour les débutants ». La vidéo elle-même donne des informations de base sur la fonction FILTRE, nous vous recommandons donc vivement de la regarder.
Voici un ensemble de données que nous utiliserons :
Dans cet ensemble de données, nous devons filtrer nos clients qui proviennent de la région EMEA (Europe, Moyen-Orient et Afrique) uniquement. Voici la formule pour faire cela :
=FILTRE('Ensemble de données'!A2:A, 'Ensemble de données'!C2:C="EMEA")- ‘Ensemble de données’!A2:A : la colonne des clients à filtrer.
- ‘Ensemble de données’!C2:C= »EMEA » : la condition de filtrage : toutes les valeurs « EMEA » dans la colonne Région.
C’est l’exemple de formule FILTRE le plus simple avec une seule condition. Vous pouvez cependant filtrer par plusieurs conditions, combiner FILTRE avec d’autres fonctions Google Sheets, et bien plus encore.
FILTRE par plusieurs conditions (logique ET)
Remarque : La fonction FILTRE de Google Sheets ne prend pas en charge les conditions mixtes : les conditions spécifiées peuvent être pour des colonnes ou des lignes.
FILTRER en utilisant la logique ET signifie que la formule renverra le résultat si toutes les conditions spécifiées sont remplies.
Syntaxe de la formule FILTRE par plusieurs conditions (logique ET) dans Google Sheets
=FILTRE(plage_de_données, condition_1, condition_2, condition_3, ...)Exemple de formule FILTRE par plusieurs conditions (logique ET) dans Google Sheets
Supposons que vous souhaitiez filtrer les clients (‘Jeu de données d’exemple’!A2:A) selon les trois conditions suivantes :
- Type d’abonnement : Squad (‘Jeu de données d’exemple’!B2:B= »Squad »)
- Pays : Pologne (‘Jeu de données d’exemple’!C2:C= »Pologne »)
- Montant du paiement : inférieur à 100 000 $ (‘Jeu de données d’exemple’!F2:F<100000)
Voici la formule :
=FILTRE( 'Jeu de données d'exemple'!A2:A, 'Jeu de données d'exemple'!B2:B="Squad", 'Jeu de données d'exemple'!C2:C="Pologne", 'Jeu de données d'exemple'!F2:F<100000 )FILTRE par plusieurs conditions (logique OU)
FILTRER avec la logique OU signifie que la formule FILTRE renverra le résultat si l’une ou toutes les conditions spécifiées sont remplies.
Syntaxe de la formule FILTRE par plusieurs conditions (logique OU) dans Google Sheets
=FILTRE(plage_de_données, (condition_1.1)+(condition_1.2), condition_2, (condition_3.1)+(condition_3.2), ...)Exemple de formule FILTRE par plusieurs conditions (logique OU) dans Google Sheets
Supposons que vous deviez filtrer les clients (‘Jeu de données d’exemple’!A2:A) selon différentes conditions de la même colonne :
- Type d’abonnement : Squad (‘Jeu de données d’exemple’!B2:B= »Squad »)
- Pays : Pologne (‘Jeu de données d’exemple’!C2:C= »Pologne ») et/ou Suède (‘Jeu de données d’exemple’!C2:C= »Suède »)
- Montant du paiement : inférieur à 100 000 $ (‘Jeu de données d’exemple’!F2:F<100000) et/ou supérieur à 50 000 $ (‘Jeu de données d’exemple’!F2:F>50000)
Voici la formule :
=FILTRE( 'Jeu de données d'exemple'!A2:A, 'Jeu de données d'exemple'!B2:B="Squad", ('Jeu de données d'exemple'!C2:C="Pologne")+ ('Jeu de données d'exemple'!C2:C="Suède"), ('Jeu de données d'exemple'!F2:F<100000)+ ('Jeu de données d'exemple'!F2:F>50000) )Importer des données dans Google Sheets et les filtrer sans utiliser de formules
Maintenant que vous savez comment filtrer des données dans Google Sheets avec la fonction FILTRE, mais que se passe-t-il si vous devez d’abord importer des données à partir d’une autre feuille ou d’une source externe ? Il faut du temps pour charger les données dans votre feuille de calcul, puis appliquer le filtre. Cependant, vous pouvez utiliser Coupler.io pour filtrer les données en temps réel sans utiliser de formules, en les important à partir de plus de 50 sources différentes.
Essayez-le par vous-même avec le formulaire ci-dessous. Pour commencer, sélectionnez la source de données et cliquez sur « Proceed ». Vous pouvez vous inscrire gratuitement avec votre compte Google.
Voyons comment vous pouvez filtrer les données à partir de la feuille de calcul mentionnée ci-dessus.
Choisissez la fonction FILTRE comme fichier source. Ensuite, spécifiez votre feuille de calcul – Jeu de données d’exemple. Vous pouvez éventuellement sélectionner une plage particulière que vous souhaitez exporter.
À l’étape suivante, vous pouvez filtrer vos données selon un ou plusieurs critères avant de charger vos données source dans la feuille de calcul. Par exemple, cliquez sur « Filter » puis sur « AND » pour filtrer les clients actifs en provenance de Chine avec l’abonnement Professionnel :
- Sélectionnez la colonne nommée « Pays », spécifiez la condition « est », puis entrez « Chine » comme valeur.
- Cliquez sur « +AND », sélectionnez la colonne nommée « Type d’abonnement », spécifiez la condition « est », puis entrez « Professionnel » comme valeur.
- Cliquez sur « +AND », sélectionnez la colonne nommée « Statut du client », spécifiez la condition « est », puis entrez « Actif » comme valeur.
Essayons également de filtrer les clients en provenance de Chine, mais cette fois-ci avec le type d’abonnement gratuit et le statut perdu :
- Cliquez sur « OR ».
- Sélectionnez la colonne nommée « Pays », spécifiez la condition « est », puis entrez « Chine » comme valeur.
- Cliquez sur « +AND », sélectionnez la colonne nommée « Type d’abonnement », spécifiez la condition « est », puis entrez « Gratuit » comme valeur.
- Cliquez sur « +AND », sélectionnez la colonne nommée « Statut du client », spécifiez la condition « est », puis entrez « Perdu » comme valeur.
- Cliquez sur « Apply filters ».
En outre, vous pouvez masquer, ajouter et modifier des colonnes, ainsi que trier les données par ordre croissant ou décroissant.
Une fois vos données transformées, passez aux paramètres de destination. Vous devrez choisir le fichier et la feuille où importer les données demandées.
Vous pouvez également choisir une fréquence de rafraîchissement des données pour que votre feuille de calcul soit régulièrement mise à jour. De cette façon, vous ne manquerez aucune modification apportée à vos données source.
Enfin, cliquez sur « Run importer ». En un instant, vos données seront disponibles dans la feuille de calcul de destination.
Avec Coupler.io, vous pouvez gagner du temps, éviter les erreurs humaines et accéder aux dernières données dont vous avez besoin.
Erreur de formule FILTRE dans Google Sheets : aucune correspondance trouvée dans l’évaluation du FILTRE
L’erreur « Aucune correspondance trouvée » de la fonction FILTRE se produit lorsqu’il n’y a pas de valeurs qui correspondent aux conditions spécifiées. Par exemple, nous savons que nous n’avons pas de clients en provenance d’Allemagne avec un montant de paiement inférieur à 100 000 $. Ainsi, la formule suivante renverra « #N/A » :
=FILTRE('Jeu de données d'exemple'!A2:A, 'Jeu de données d'exemple'!B2:B="Squad", 'Jeu de données d'exemple'!C2:C="Allemagne", 'Jeu de données d'exemple'!F2:F<100000)Pour que votre formule FILTRE renvoie une cellule vide au lieu de l’erreur « #N/A », ajoutez la fonction SIERREUR au début :
=SIERREUR(FILTRE('Jeu de données d'exemple'!A2:A, 'Jeu de données d'exemple'!B2:B="Squad", 'Jeu de données d'exemple'!C2:C="Allemagne", 'Jeu de données d'exemple'!F2:F<100000),)FILTRE par numéro dans Google Sheets
Voici les conditions que vous pouvez appliquer pour filtrer les valeurs numériques :
Condition de filtrage dans Google Sheets : Égal à X
Supposons que vous souhaitiez filtrer tous les clients avec un montant de paiement égal à 56 298 $ :
=FILTRE('Jeu de données d'exemple'!A2:A, 'Jeu de données d'exemple'!F2:F=56298)Condition de filtrage dans Google Sheets : Supérieur à (« > ») X
Supposons que vous souhaitiez filtrer tous les clients avec un montant de paiement supérieur à 56 298 $ :
=FILTRE('Jeu de données d'exemple'!A2:A, 'Jeu de données d'exemple'!F2:F>56298)Condition de filtrage dans Google Sheets : Inférieur à (« <« ) X
Supposons que vous souhaitiez filtrer tous les clients avec un montant de paiement inférieur à 56 298 $ :
=FILTRE('Jeu de données d'exemple'!A2:A, 'Jeu de données d'exemple'!F2:F<56298)Condition de filtrage dans Google Sheets : Différent de X
Supposons que vous souhaitiez filtrer tous les clients avec un montant de paiement différent de 56 298 $ :
=FILTRE('Jeu de données d'exemple'!A2:A, 'Jeu de données d'exemple'!F2:F<>56298)Condition de filtrage dans Google Sheets : Valeurs paires
Supposons que vous souhaitiez filtrer tous les clients avec des montants de paiement pairs :
=FILTRE('Jeu de données d'exemple'!A2:A, EST.PAIR('Jeu de données d'exemple'!F2:F))Condition de filtrage dans Google Sheets : Valeurs impaires
Supposons que vous souhaitiez filtrer tous les clients avec des montants de paiement impairs :
=FILTRE('Jeu de données d'exemple'!A2:A, EST.IMPAIR('Jeu de données d'exemple'!F2:F))FILTRE par texte dans Google Sheets (correspondance/non-correspondance exacte)
Le filtrage de base par texte comprend deux conditions :
- Renvoyer les valeurs qui sont exactement égales à la chaîne de texte spécifiée
- Renvoyer les valeurs qui ne sont pas exactement égales à la chaîne de texte spécifiée
Par exemple, supposons que vous souhaitiez filtrer les clients avec le statut perdu :
=FILTRE('Jeu de données d'exemple'!A2:A, 'Jeu de données d'exemple'!E2:E="Perdu")et ceux qui ont des statuts autres que perdu :
=FILTRE('Jeu de données d'exemple'!A2:A, 'Jeu de données d'exemple'!E2:E<>"Perdu")FILTRE par texte dans Google Sheets (correspondance partielle)
Dans l’article de blog COUNTIF vs. COUNTIFS, nous avons expliqué que vous pouvez utiliser des caractères génériques (« ? » et « * ») pour vérifier la correspondance partielle de la plage de données. Les caractères génériques ne fonctionnent pas avec FILTRER. Cependant, les fonctions SEARCH, FIND et REGEXMATCH imbriquées dans la formule FILTRE peuvent faire le travail.
Utilisez la fonction SEARCH pour filtrer par le texte recherché avec une casse insensible à la casse.
=SEARCH("texte_à_rechercher", plage_de_cellules, [emplacement_de_début])Par exemple, imbriquez SEARCH dans FILTRE pour filtrer tous les clients avec un double « o » (oo) dans leur nom :
=FILTRE('Jeu de données d'exemple'!A2:A, SEARCH("oo",'Jeu de données d'exemple'!A2:A))Utilisez la fonction FIND pour filtrer par le texte recherché avec une casse sensible à la casse.
=FIND("texte_à_rechercher", plage_de_cellules, [emplacement_de_début])Par exemple, si le texte est sensible à la casse dans votre critère de filtrage, utilisez la fonction FIND. Filtrons tous les clients qui ont « Oo » dans leur nom.
=FILTRE('Jeu de données d'exemple'!A2:A, FIND("Oo",'Jeu de données d'exemple'!A2:A))Utilisez la fonction FILTRE + REGEXMATCH pour un filtrage avancé par texte dans Google Sheets.
=REGEXMATCH(plage_de_cellules,"expression_régulière")Par exemple, filtrons tous les clients qui ont « Oo » et « atz » dans leur nom :
=FILTRE('Jeu de données d'exemple'!A2:A, REGEXMATCH('Jeu de données d'exemple'!A2:A,"Oo|atz"))FILTRE par date et heure dans Google Sheets
Pour filtrer les valeurs par date (date complète, mois, année, etc.), vous devrez imbriquer FILTRE avec des fonctions supplémentaires.
Remarque : si vous essayez simplement d’utiliser une valeur de date en tant que chaîne de texte dans le critère de filtrage, vous obtiendrez l’erreur « Aucune correspondance trouvée » :
=FILTRE('Jeu de données d'exemple'!A2:A, 'Jeu de données d'exemple'!G2:G="3/9/2020")Faisons cela correctement.
FILTRE par date exacte
Appliquez la fonction DATE dans la formule FILTRE comme suit :
=FILTRE(plage_de_cellules, plage_de_cellules=DATE(YYYY,MM,DD))Par exemple, la formule pour filtrer les clients par date de transaction le 9 mars 2020 est la suivante :
=FILTRE('Jeu de données d'exemple'!A2:A, 'Jeu de données d'exemple'!I2:I>=DATE(2020,3,9), 'Jeu de données d'exemple'!I2:I<DATE(2020,3,10))Dans notre ensemble de données, nous avons des valeurs de date+heure. Divisons cette colonne en deux colonnes distinctes (pour la date et l’heure), et voyons comment filtrer ces valeurs. Voici la formule à utiliser :
={"Date","Heure"; arrayformula(split(G2:G," "))}Maintenant, nous pouvons appliquer FILTRE+DATE pour filtrer les clients par date de transaction le 9 mars 2020 :
=FILTRE('Jeu de données d'exemple'!A2:A, 'Jeu de données d'exemple'!I2:I=DATE(2020,3,9))Pour filtrer les résultats avant ou après une date spécifiée, utilisez les opérateurs de comparaison (« < » et « > »).
FILTRE par heure dans Google Sheets
Une syntaxe similaire s’applique lorsque vous devez filtrer les valeurs par heure :
=FILTRE(plage_de_cellules, plage_de_cellules=TIME(HH,MM,SS))Par exemple, la formule pour filtrer les clients qui ont effectué la transaction avant 10:46:23 est la suivante :
=FILTRE('Jeu de données d'exemple'!A2:A, 'Jeu de données d'exemple'!J2:J<TIME(10,46,23))Utilisez les opérateurs de comparaison (« < » et « > ») pour filtrer les résultats avant ou après l’heure spécifiée.
FILTRE par jour/mois/année dans Google Sheets
Vous pouvez facilement filtrer les valeurs par jour/mois/année en utilisant les fonctions respectives (DAY, MONTH et YEAR) et la syntaxe suivante :
=FILTRE(plage_de_cellules, DAY(plage_de_cellules)=valeur)Par exemple, filtrons les clients séparément par :
- Jour du mois : 4
- Mois : mai
- Année : 2020
Voici à quoi ressembleront ces critères de filtrage dans les trois formules FILTRE distinctes :
Jour
=FILTRE('Jeu de données d'exemple'!A2:A, DAY('Jeu de données d'exemple'!G2:G)=4)Mois
=FILTRE('Jeu de données d'exemple'!A2:A, MONTH('Jeu de données d'exemple'!G2:G)=5)Année
=FILTRE('Jeu de données d'exemple'!A2:A, YEAR('Jeu de données d'exemple'!G2:G)=2020)Utilisez les opérateurs de comparaison (« < » et « > ») pour filtrer les résultats avant ou après le jour/mois/année spécifié.
Maintenant, explorons quelques études de cas avancées en utilisant la fonction FILTRE.
Supprimer les doublons dans les résultats du filtrage en utilisant FILTRE+UNIQUE dans Google Sheets
Dans l’exemple avec FILTRE + REGEXMATCH, nous avons obtenu de nombreux doublons dans les résultats du filtrage. Pour supprimer les doublons, appliquez la fonction UNIQUE dans votre formule FILTRE, comme ceci :
=unique( filter( 'Jeu de données d'exemple'!A2:A, regexmatch('Jeu de données d'exemple'!A2:A,"Oo|atz") ) )Filtrer les doublons dans Google Sheets
Supposons que vous souhaitiez filtrer les doublons (s’il y en a) d’une plage de cellules. Malheureusement, il n’existe pas de fonction NOT UNIQUE dans Google Sheets, qui aurait pu résoudre cette tâche. Mais voici une solution de contournement où vous devez imbriquer FILTRE avec UNIQUE, ARRAYFORMULA et COUNTIF :
=FILTRE( UNIQUE(plage_de_cellules), ARRAYFORMULA( COUNTIF(plage_de_cellules, UNIQUE(plage_de_cellules))>1 ) )Par exemple, nous avons deux plages de cellules : l’une avec des doublons et l’autre sans doublons. Nous avons appliqué la formule FILTRE avancée pour vérifier les deux :
=iferror( filter( unique(A2:A), arrayformula( countif(A2:A, unique(A2:A))>1 ) ) )et
=iferror( filter( unique(B2:B), arrayformula( countif(B2:B, unique(B2:B))>1 ) ) )FILTRE + IMPORTRANGE pour filtrer les données importées d’une autre feuille de calcul Google
Si vous imbriquez FILTRE avec IMPORTRANGE, vous pourrez filtrer les données importées d’une autre feuille de calcul Google. Voici la syntaxe de la formule :
=FILTRE(IMPORTRANGE("feuille_de_calcul", "plage_de_cellules"), [condition_1, condition_2, ...])Lisez notre tutoriel Google IMPORTRANGE pour voir un exemple de formule et en savoir plus sur l’utilisation de IMPORTRANGE.
FILTRE + FILTRE pour filtrer les résultats filtrés dans Google Sheets
=FILTRE(FILTRE(plage_de_cellules, condition),condition)Cette combinaison vous permet de filtrer un résultat qui a déjà été filtré. Cela peut sembler compliqué, mais voici un exemple. Nous voulons filtrer les clients selon les trois conditions suivantes :
- Type d’abonnement : Squad
- Pays : Pologne
- Montant du paiement : inférieur à 100 000 $
Le résultat du filtre doit être filtré avec une correspondance partielle « at ». Voici à quoi ressemble la formule :
=FILTRE( FILTRE( 'Jeu de données d'exemple'!A2:A, 'Jeu de données d'exemple'!B2:B="Squad", 'Jeu de données d'exemple'!C2:C="Pologne", 'Jeu de données d'exemple'!F2:F<100000 ), SEARCH("at", FILTRE( 'Jeu de données d'exemple'!A2:A, 'Jeu de données d'exemple'!B2:B="Squad", 'Jeu de données d'exemple'!C2:C="Pologne", 'Jeu de données d'exemple'!F2:F<100000 ) ) )FILTRE + LARGE pour filtrer les X premiers enregistrements
=FILTRE(plage_de_cellules,plage_de_cellules>=LARGE(plage_de_cellules,classement))Supposons que vous souhaitiez filtrer les cinq premiers clients en fonction du montant du paiement. Voici la formule :
=FILTRE( 'Jeu de données d'exemple'!A2:A, 'Jeu de données d'exemple'!F2:F>=large('Jeu de données d'exemple'!F2:F,5) )Si vous devez filtrer les plus petites valeurs au lieu des plus grandes, utilisez la fonction SMALL à la place de LARGE.
SUM + FILTRE pour calculer la somme des résultats filtrés dans Google Sheets
Si vous combinez FILTRE avec SUM, la formule renverra la somme des résultats filtrés. Voici la syntaxe :
=SUM(FILTRE(plage_de_cellules, condition))Par exemple, voici la formule pour calculer le montant total des paiements des clients perdus :
=SUM( FILTER('Jeu de données d'exemple'!F2:F, 'Jeu de données d'exemple'!E2:E="Perdu" ) )Filtrer les données et ajouter une ligne de total en dessous
Supposons que nous voulions faire ce qui suit :
- Filtrer les clients selon deux conditions : statut « perdu » et date de transaction postérieure au 1er juillet 2020
- Récupérer certaines colonnes spécifiques (nom du client, type d’abonnement, pays et montant du paiement)
- Calculer le montant total des paiements des clients perdus
- Placer le total dans une ligne séparée en dessous des résultats du filtrage
Voyons pas à pas comment la formule FILTRE avancée peut faire cela :
Filtrer les clients selon deux conditions : statut « perdu » et date de transaction postérieure au 1er juillet 2020
=FILTRE( 'Jeu de données d'exemple'!A2:A, 'Jeu de données d'exemple'!E2:E="Perdu", 'Jeu de données d'exemple'!I2:I>date(2020,7,1) )Récupérer certaines colonnes spécifiques (nom du client, type d’abonnement, pays et montant du paiement)
=FILTRE( {'Jeu de données d'exemple'!A2:C, 'Jeu de données d'exemple'!F2:F}, 'Jeu de données d'exemple'!E2:E="Perdu", 'Jeu de données d'exemple'!I2:I>date(2020,7,1) )Placer une ligne séparée pour le total en dessous des résultats du filtrage
={ FILTRE( {'Jeu de données d'exemple'!A2:C,'Jeu de données d'exemple'!F2:F}, 'Jeu de données d'exemple'!E2:E="Perdu", 'Jeu de données d'exemple'!I2:I>date(2020,7,1) ); {"Total"," "," ", sum( filter('Jeu de données d'exemple'!F2:F, 'Jeu de données d'exemple'!E2:E="Perdu", 'Jeu de données d'exemple'!I2:I>date(2020,7,1) ) ) } }Calculer le montant total des paiements des clients filtrés
={ FILTRE( {'Jeu de données d'exemple'!A2:C,'Jeu de données d'exemple'!F2:F}, 'Jeu de données d'exemple'!E2:E="Perdu", 'Jeu de données d'exemple'!I2:I>date(2020,7,1) ); {"Total"," "," ", sum( filter('Jeu de données d'exemple'!F2:F, 'Jeu de données d'exemple'!E2:E="Perdu", 'Jeu de données d'exemple'!I2:I>date(2020,7,1) ) ) } }Il s’agit d’une formule assez complexe pour FILTRE. Regardez cette vidéo YouTube pour en savoir plus sur les astuces avancées avec FILTRE et d’autres fonctions Google Sheets.
FILTRE est une fonction géniale dans Google Sheets, n’est-ce pas ?
FILTRE est une fonction très flexible et populaire dans Google Sheets. De plus, c’est l’une des fonctions les plus utilisées dans les cas couverts dans notre blog. Elle a été utilisée pour créer un suivi des ventes, ainsi que pour expliquer d’autres fonctions de Google Sheets, telles que IFS par rapport aux déclarations IF imbriquées.
Pour les données provenant d’autres feuilles de calcul ou de sources externes, utilisez Coupler.io pour les importer et les filtrer en temps réel sans utiliser de formules. Lancez-vous avec un essai gratuit de 14 jours pour le découvrir par vous-même !
