Il existe de nombreux avantages à utiliser le filtre avec les colonnes de résultat Vlookup dans Google Sheets.
Parmi eux, cela vous aidera à filtrer les cellules vides, à supprimer les valeurs indésirables d’une ou plusieurs colonnes de recherche, et à appliquer des opérateurs de comparaison.
Dans ce nouveau tutoriel Google Sheets, nous allons en discuter.
Introduction
À mon avis, Vlookup dans Google Sheets était bien meilleur que son homologue Excel. Vous êtes libre d’être d’accord ou de ne pas être d’accord.
La possibilité de personnaliser Vlookup pour répondre à nos besoins de recherche est infinie dans Google Sheets, pour trois raisons principales :
- La fonction accepte des expressions dans tous les arguments.
- La disponibilité de fonctions de tableau telles que Filtrer, Trier, Query que nous pouvons utiliser à l’intérieur ou à l’extérieur de Vlookup (pour en apprendre davantage sur ces fonctions, consultez mon guide des fonctions).
- Google Sheets dispose de la fonction ArrayFormula au lieu de la formule de tableau Ctrl+Maj+Entrée dans Excel pour travailler avec des tableaux.
En passant, nous allons tirer le meilleur parti de la deuxième caractéristique ci-dessus pour filtrer les colonnes de résultat Vlookup dans Google Sheets.
Le troisième point susmentionné est un véritable avantage.
Parce que, dans Google Sheets, nous pouvions créer des tableaux virtuels à l’aide de Crochets. Cela offrait une multitude de possibilités pour ajuster la plage de recherche.
Mais plus tard, Excel a rattrapé son retard en introduisant XLOOKUP et des fonctions de tableau dynamique telles que Filtrer et Trier (bien que pas dans toutes les versions).
Maintenant, Excel est également compétent pour répondre à tous les besoins de recherche.
Vous pourriez aimer : Comparaison de la formule Vlookup dans Excel et Google Sheets.
Données échantillon
Pour filtrer un résultat Vlookup, nous devons savoir comment obtenir les valeurs de toutes les colonnes de la ligne de recherche.
J’ai déjà expliqué la même chose ici – Comment retourner plusieurs valeurs en utilisant Vlookup dans Google Sheets ?
Vous voulez un exemple ? Alors voici !
Formule principale :
=ArrayFormula(vlookup(A1,A4:H9,column(B3:H3),0))
Recherche clé – A1, qui correspond à « Item 2 ».
Plage – A4:H9 (la plage de recherche pour la recherche clé dans la première colonne).
Index – column(B3:H3), qui correspond aux colonnes 2 à 8.
La formule ci-dessus renvoie un résultat de 7 colonnes.
Voyons maintenant comment nous pouvons filtrer les colonnes de résultat Vlookup dans Google Sheets pour répondre à différents besoins.
Exemple de filtrage des colonnes de résultat Vlookup dans Google Sheets
Voici quelques exemples de formules réparties sous trois sous-titres.
Filtrer les cellules vides des colonnes de résultat Vlookup
Si vous remplacez la clé de recherche dans la cellule A1 par « Item 3 », la formule principale renverra le résultat suivant :
| 2 | 5 | 8 |
|---|
Filtrons ces colonnes de résultat Vlookup pour supprimer les cellules vides (l’ArrayFormula a été supprimée car elle n’est pas nécessaire avec Filter).
=filter( vlookup(A1,A4:H9,column(B3:H3),0), len(vlookup(A1,A4:H9,column(B3:H3),0)) )
Cela est utile lorsque vous souhaitez obtenir le résultat des ‘N’ premières colonnes non vides de Vlookup. Pour cela, utilisez simplement Array_Constrain avec.
=array_constrain( filter( vlookup(A1,A4:H9,column(B3:H3),0), len(vlookup(A1,A4:H9,column(B3:H3),0)) ),1,N )
Remplacez ‘N’ par 1 pour obtenir la première valeur, 2 pour obtenir les deux premières valeurs, et ainsi de suite.
Auparavant, nous utilisions une formule différente qui ne pouvait retourner que la première valeur non vide après une recherche verticale. Voici ce tutoriel – Déplacer la colonne d’index si elle est vide dans Vlookup dans Google Sheets
C’était une formule Vlookup imbriquée.
Application de la comparaison
La combinaison Filtre Vlookup présente plusieurs avantages, et voici le suivant qui utilise un opérateur de comparaison.
Interprétons le contenu du tableau ci-dessus de la manière suivante :
A4:A9 – articles en vente.
B4:H9 – quantités vendues des articles de A4:A9 du dimanche au samedi.
Supposons que vous souhaitiez rechercher « Item 6 » dans la colonne A et obtenir les valeurs de ventes inférieures à 5.
=filter( vlookup(A1,A4:H9,column(B3:H3),0), vlookup(A1,A4:H9,column(B3:H3),0)<5 )
Cela le fera.
Filtrer les colonnes de résultat Vlookup pour supprimer les valeurs indésirables
En fait, dans les deux exemples ci-dessus, nous avons déjà appris à supprimer les valeurs indésirables.
Pour affiner davantage le résultat, nous pouvons utiliser d’autres opérateurs de comparaison ou des expressions régulières.
Dans les trois exemples de formules ci-dessous, veuillez prêter attention au troisième qui utilise une expression régulière.
Exemple 1
Pour filtrer les colonnes de résultat Vlookup pour une valeur différente de 5.
=filter( vlookup(A1,A4:H9,column(B3:H3),0), vlookup(A1,A4:H9,column(B3:H3),0)<>5 )
Exemple 2
Pour ne retourner que la valeur 5.
=filter( vlookup(A1,A4:H9,column(B3:H3),0), vlookup(A1,A4:H9,column(B3:H3),0)=5 )
Exemple 3
Il est plus avancé.
Nous pouvons utiliser Regexmatch pour filtrer les colonnes de résultat Vlookup en utilisant plusieurs conditions. Cette formule ne renverra que les valeurs 1 et 10.
=filter( vlookup(A1,A4:H9,column(B3:H3),0), regexmatch(vlookup(A1,A4:H9,column(B3:H3),0)&"^1$|^10$") )
Si les colonnes de résultat contiennent du texte, remplacez 1, 10 par les chaînes correspondantes.
Vous pouvez ajouter plus de conditions en les séparant par le caractère pipe, comme ^pomme$|^orange$|^banane$.
Pour renvoyer les valeurs qui ne sont pas égales à 1 et 10 après une recherche Vlookup, utilisez FALSE.
=filter( vlookup(A1,A4:H9,column(B3:H3),0), regexmatch(vlookup(A1,A4:H9,column(B3:H3),0)&"^1$|^10$")=FALSE )
Filtrage des colonnes de résultat Vlookup et gestion des erreurs #N/A
Toutes les formules ci-dessus peuvent renvoyer une erreur #N/A dans deux cas. Cela se produit lorsque la clé de recherche Vlookup n’est pas présente dans la première colonne de la plage ou lorsque le filtre ne trouve pas de correspondance.
Supposons que la valeur dans la cellule A1 soit « Gold ». Il n’y a pas d’élément de ce type dans A4:A9.
Ainsi, Vlookup renverra sept erreurs #N/A car nous utilisons plusieurs colonnes d’index (sortie) dans la formule. Si vous utilisez Filter avec Vlookup, vous ne verrez probablement qu’une seule erreur.
Lorsque vous utilisez la formule de l’exemple 2 ci-dessus et que la condition/critère dans la cellule A1 est « Item 4 », le filtre renvoie l’erreur ci-dessus. Cela est dû au fait que la condition ne correspond pas aux colonnes de résultat Vlookup.
Pour supprimer ces erreurs et obtenir une valeur personnalisée, enveloppez la formule externe avec IFNA.
=ifna(formule,"message")
C’est tout. Merci de votre attention. Amusez-vous bien !
