Google Sheets propose d'excellentes formules qui peuvent aider à filtrer et à facilement trier les données. Et même s’il est possible de rapidement créer un filtre dans Google Sheets en allant, dans le menu, dans “Données” puis “Créer un filtre”, vous disposez également de la formule FILTER (pour FILTRE en français).
A la fin de cet article, je partage une vidéo qui vous montre comment filtrer dans Google Sheets.
La formule FILTER, comme son nom l'indique en anglais, vous permet de filtrer un ensemble de données en fonction d'une condition (ou de plusieurs conditions). C’est bien plus puissant que le filtre “classique”. Je vous explique.
Par exemple, si vous disposez d’une liste de personnes avec leurs prénoms et les départements dans lesquels elles réalisent des ventes, vous pouvez utiliser la formule FILTER dans Google Sheets pour obtenir instantanément la liste des personnes qui travaillent dans un département (comme indiqué dans la copie d’écran ci-dessous).
L'un des avantages qu'il y a à utiliser la formule FILTER par rapport au filtrage standard de Google Sheets est que les résultats de la formule FILTER sont dynamiques. Si vous modifiez quoi que ce soit dans les données d'origine, les données filtrées résultantes seront automatiquement mises à jour.
Cela fait de la formule FILTER de Google Sheets un excellent choix lors de la création de rapports ou de tableaux de bord interactifs.
Dans ce tutoriel, je vais vous montrer comment fonctionne la formule FILTER dans Google Sheets et également vous montrer quelques exemples pour une utilisation judicieuse au quotidien.
Comment filtrer dans Google Sheets : le guide vidéo
Voilà le guide (au format vidéo) pour savoir comment filtrer dans Google Sheets : (regardez la vidéo ci-dessus)
La syntaxe de la formule de filtre FILTER dans Google Sheets
Commençons donc par apprendre la syntaxe de cette formule.
La formule FILTER de Google Sheets se présente comme suit :
FILTER(plage; condition1; [condition2; …]) :
- plage : il s'agit de la plage de cellules que vous souhaitez filtrer.
- condition1 : il s'agit des colonnes/lignes (correspondant à la colonne/ligne de l'ensemble de données), qui renvoient soit une réponse “vraie”, soit une réponse “faux”. La ligne ou la colonne sélectionnée pour la condition1 doit être de la même taille que la plage.
- [condition2] : il s'agit d'un argument facultatif, si vous voulez ajouter une deuxième condition pour filtrer les données. Cela peut être une nouvelle colonne ou une ligne (dans l’ensemble des données ), qui renvoie alors des réponses “vraies” ou “fausses” (comme pour la condition1). Cette sélection doit être de la même taille que la plage initiale.
Quand vous utilisez plusieurs conditions, les résultats qui renvoient une réponse “vraie” pour les deux conditions sont filtrés (et donc affichées).
Si la formule FILTER ne trouve aucun résultat correspondant à la condition, elle renverra le message d’erreur #N/A.
Pour mieux vous rendre compte de la manière dont cela fonctionne, vous pouvez regarder la vidéo ci-dessous ou parcourir les quelques exemples détaillés ci-dessous. Cela va vous aider à mieux comprendre comment utiliser la formule FILTER dans Google Sheets.
Exemple 1 : formule FILTER de Google Sheets avec une seule condition
Supposons que vous disposiez d'un ensemble de données similaire à ce que vous voyez ci-dessous et que vous souhaitez rapidement filtrer toutes les données qui concernent le département du Morbihan.
La formule que vous allez utiliser est la suivante :
=FILTER(A2:C11;B2:B11="Morbihan")
La formule ci-dessus prend la plage de données comme argument et la condition est B2:B11="Morbihan". Cette condition vérifie chaque cellule dans la plage B2: B11 et si la valeur est égale à Morbihan : si c’est le cas, cette donnée est filtrée et affichée ; si la donnée ne correspond pas à Morbihan, elle n’est pas affichée.
Dans cet exemple, j'ai codé en dur la valeur (le département du Morbihan), mais vous pouvez également avoir cette valeur dans une cellule, puis vous référer à cette cellule. Par exemple, si vous avez le texte Morbihan dans la cellule H1, vous pouvez également utiliser la formule ci-dessous :
=FILTER(A2:C11;B2:B11=H1)
Voilà quelques informations supplémentaires à savoir sur la formule FILTER.
La formule FILTER dans Google Sheets permet d’afficher un tableau de données dans une plage de cellules adjacentes (c'est ce qu'on appelle un tableau dynamique). Pour que cela fonctionne, vous devez vous assurer que la plage avec ces cellules (où les résultats vont être affichés) est vide.
Si l'une des cellules n'est pas vide, votre formule renverra un message d’erreur #REF !. Si c’est le cas, Google Sheets va vous indiquer pourquoi il génère un message d’erreur en affichant un petit triangle rouge en haut à droite de la cellule. Lorsque vous passez la souris sur ce petit triangle, un message s'affiche :
Le résultat du tableau n'a pas été affiché, parce qu’il aurait écrasé les données dans la cellule F3.
Dès que vous supprimez la cellule avec des données qui empêchent la formule FILTER de donner le résultat filtré, la formule va pouvoir faire son travail et va automatiquement remplir la plage avec le résultat attendu.
Il faut noter que le résultat de la formule FILTER est un tableau et vous ne pouvez pas modifier ou supprimer une cellule (ou quelques cellules) dans le résultat. Pour changer le résultat, vous devez soit changer la formule, soit supprimer la formule.
Exemple 2 : la formule FILTER dans Google Sheets en fonction de plusieurs conditions
Vous pouvez également utiliser la formule FILTER pour vérifier plusieurs conditions de manière à ne renvoyer que les données où les deux conditions sont remplies.
Par exemple, si vous disposez du tableau ci-dessous et que vous souhaitez filtrer toutes les données qui concernent le département du Morbihan et dont le chiffre d’affaires est supérieur à 30 000.
Vous pouvez le faire en utilisant la formule ci-dessous;
=FILTER(A2:C11;B2:B11="Morbihan";C2:C11>30000)
La formule ci-dessus vérifie deux conditions : le département est le Morbihan et le chiffre d’affaires est supérieur à 30000). Cette formule permet de filtrer toutes les données qui répondent à ces deux critères.
Si vous le souhaitez, vous pouvez utiliser plusieurs conditions dans la même formule FILTER.
Exemple 3 : filtrer les données en fonction de plusieurs conditions
Dans l'exemple ci-dessus, j'ai utilisé deux conditions et la formule a permis de filtrer les résultats pour lesquels les deux conditions sont “vraies”.
Vous pouvez également vérifier la condition “ou” dans la formule FILTER.
Par exemple, supposons que vous disposez de la plage de données ci-dessous et que vous souhaitez filtrer toutes les données qui concernent le Finistère et le Morbihan. Cela signifie que la condition doit être que le département est soit le Finistère, soit le Morbihan (ce qui en fait une condition ou).
Voilà la formule qu’il faut utiliser :
=FILTER(A2:C11;(B2:B11="Finistère")+(B2:B11="Morbihan"))
La formule ci-dessus utilise l'addition parce qu’elle permet de vérifier d'abord les deux conditions séparément, puis ajoute le résultat de chacune. Étant donné que ces deux conditions séparées renvoient un tableau de résultat qui sont vrais ou faux, il suffit de les ajouter (à noter que “vrai” représente un 1 et “faux” est un 0 dans Google Sheets).
Cela vous donnera :
- 0 (ou “faux”) lorsque les deux conditions ne sont pas remplies,
- 1 lorsque l'une des deux conditions est remplie,
- 2 lorsque les deux conditions sont remplies.
Au final, la formule FILTER va renvoyer toutes les données où les conditions renvoient une valeur supérieure à 0.
Exemple 4 : comment filtrer dans Google Sheets pour les 3 ou 5 meilleurs données
Vous pouvez également utiliser la formule FILTER pour obtenir rapidement le top 3 ou le top 5 (ou n’importe quel nombre des premières - ou dernières - données que vous choisissez).
Par exemple, supposons que j'ai l'ensemble de données comme indiqué ci-dessous et que je souhaite obtenir rapidement les données des 3 plus hauts chiffres d’affaires réalisés.
Voici la formule à utiliser :
=FILTER(A2:C11;C2:C11>=GRANDE.VALEUR(C2:C11;3))
La formule ci-dessus utilise la formule GRANDE.VALEUR pour obtenir la troisième plus grande valeur de la plage de données. Cette valeur est ensuite utilisée dans la formule pour vérifier si les valeurs de la colonne C sont supérieures ou égales à cette valeur ou non.
Cela renvoie toutes les données qui correspondent aux critères recherchés : ici, il s’agit des trois données les plus élevées.
Si vous souhaitez obtenir les trois derniers enregistrements, vous pouvez utiliser la formule FILTER de la manière suivante :
=FILTER(A2:C11;C2:C11<=PETITE.VALEUR(C2:C11;3))
Exemple 5 : comment utiliser la formule FILTER dans Google Sheets pour trier les données filtrées
Jusqu'à présent, tous les exemples de formules de filtrage que nous avons vu filtraient les données dans le même ordre dans lequel elles apparaissent dans la plage de données d’origine.
Comment procéder si vous voulez que les données filtrées soient triées ?
Par exemple, supposons que vous voulez filtrer les 3 premières données : il serait vraiment utile de les afficher (et trier) par ordre décroissant (le plus grand en haut).
Vous trouverez ci-dessous la formule qui filtrera les données et les affichera dans un ordre décroissant :
=SORT(FILTER(A2:C11;C2:C11>=GRANDE.VALEUR(C2:C11;3));3;FAUX)
La formule ci-dessus utilise la même formule FILTER que nous avons utilisée dans l'exemple précédent pour récupérer les trois premières données en fonction de la valeur du chiffre d’affaires.
Et comme nous souhaitons que le résultat final soit trié par ordre décroissant, nous avons encadré la formule “FILTER” par la formule “SORT”. La formule “SORT” utilise les résultats donnés par la formule FILTER et les trie en fonction de la troisième colonne (qui contient les chiffres d’affaires).
Le troisième argument de la formule SORT est “FAUX”, ce qui permet de préciser que nous souhaitons obtenir le résultat final dans l'ordre décroissant. Si vous le laissez vide (ou le rendez “VRAI”), le résultat sera alors affiché dans l'ordre croissant.
Exemple 6 : filtrer les données paires (ou impaires)
Ce n'est pas un cas courant, mais c'est quelque chose qui peut s’avérer nécessaire.
Il peut arriver de n’avoir besoin que des données qui figurent dans des lignes alternées (ou même toutes les troisièmes/quatrièmes/cinquièmes lignes), et vous devrez vous débarrasser des rangées inutiles afin de pouvoir rassembler les données qui vous sont utiles.
Dans ce cas, vous pouvez utiliser la formule FILTER pour filtrer rapidement et regrouper toutes les lignes paires (ou toutes les lignes impaires). Et vous pouvez également modifier la formule pour filtrer chaque troisième, quatrième ou nième ligne dans Google Sheets.
Je pars toujours de la même hypothèse : vous disposez de la plage de données ci-dessous et vous souhaitez filtrer toutes les lignes paires.
Vous trouverez ci-dessous la formule qui va vous permettre de filtrer toutes les lignes paires :
=FILTER(A2:C11;MOD(LIGNE(A2:A11)-1;2)=0)
La formule ci-dessus utilise la formule LIGNE pour obtenir le nombre de lignes de la plage de données sélectionnée. On en soustrait le chiffre 1, puisque notre plage de données commence à partir de la deuxième ligne.
Ensuite, on utilise la formule MOD :
MOD(LIGNE(A2:A11)–1;2)=0)
Cette formule a pour résultat une réponse “vraie” pour toutes les lignes paires et “fausse” pour toutes les lignes impaires. Ce sont ces réponses “vraies” et “fausses” qui sont utilisées par la formule FILTER pour extraire les données utiles.
De même, si vous ne voulez filtrer que les données numérotées impaires, vous pouvez utiliser la formule ci-dessous :
=FILTER(A2:C11;MOD(LIGNE(A2:A11)-1;2)=1)
Et si vous souhaitez filtrer toutes les trois lignes, vous pouvez utiliser la formule ci-dessous dans Google Sheets :
=FILTER(A2:C11;MOD(ligne(A2:A11)-1;3)=0)
FAQ sur la formule de filtrage de Google Sheets
Comment filtrer les 10 premières valeurs dans Google Sheets ?
Consultez l'exemple 4 dans notre guide ci-dessus, il vous montrera comment filtrer les valeurs les plus élevées, quel que soit le nombre de données que vous décidez de filtrer.
Que fait la formule FILTER ?
La formule FILTER permet de filtrer facilement les données. Cela vous permet de laisser de côté ce qui est sans importance dans votre feuille de calcul et de vous concentrer sur ce qui est important. Grâce à cette formule, vous ne voyez que ce qui vous est utile.
Puis-je faire un filtre dans un filtre ?
Techniquement oui, mais vous feriez peut-être mieux d'utiliser la formule RECHERCHEV à la place. C'est un moyen beaucoup plus efficace de faire la même chose.
Puis-je référencer une cellule de critères avec la formule de filtre dans Google Sheets ?
Oui, il vous suffit d'utiliser la référence de cellule dans la section “condition” de la syntaxe de la formule FILTER.
Aller plus loin quand on sait filtrer dans Google Sheets
Comment filtrer par couleur dans Google Sheets ?
Savoir filtrer par couleur dans Google Sheets peut s'avérer très utile. C'est très bien de savoir filtrer, mais il est toujours utile de savoir personnaliser les filtres pour être encore plus efficace.
Savoir faire une liste déroulante
Faire une liste déroulante dans Google Sheets est une autre compétence utile quand on travaille avec des feuilles de calcul.
Afficher les nombres dans des couleurs précises
Afficher les nombres négatifs en rouge dans Google Sheets permet de faciliter l’interprétation de vos données et rend aussi vos tableaux un plus esthétiques.
Importer des données d’une autre feuille de calcul
Avant de créer des filtres, il peut être utile de savoir comment importer des données d’une autre feuille de calcul. Pour cela, la fonction IMPORTRANGE de Google Sheets est très utile.
En plus de filtrer, vous devez apprendre à trier vos données
Dans cet article, je vous apprend à trier par date dans Google Sheets. Cela peut s’avérer très utile quand vous travaillez avec des données qui contiennent des dates et qu’il faut faire des tris.
Filtrer dans Google Sheets : la vidéo explicative
Voici une vidéo d'un peu plus de 5 minutes pour apprendre à filtrer dans Google Sheets :
La formule FILTER dans Google Sheets : ce qu’il faut retenir
Nous vous avons montré quelques exemples utiles où la formule FILTER de Google Sheets peut être un véritable gain de temps. Et lorsque vous la combinez avec d'autres formules, vous pouvez faire beaucoup de choses avec.
J'espère que ce tutoriel vous a été utile.
