Article original : Google Sheets – How to Make a Dynamic Search Bar
Ce tutoriel est pour quand CTRL + F ne suffit pas.
Je parie que j'ai utilisé le raccourci CTRL + F plus que tout autre raccourci clavier dans ma vie. CTRL + Z arrive probablement en deuxième position, mais j'utilise CTRL + F pour trouver des choses...
TOUTES
LES
FOIS
gif d'un homme disant nous avons besoin de plus
Et oui, cela fonctionnera très bien dans une feuille Google pour trouver des informations.
Mais parfois, je veux afficher une gamme de résultats basée sur un mot que je recherche. Pour cela, nous allons créer une barre de recherche dynamique dans notre feuille Google.
Vous pouvez également suivre ce tutoriel vidéo :
La barre de recherche
Notre barre de recherche n'est rien de plus qu'une cellule ou une plage de cellules. Dans l'exemple ci-dessous, elle commence en J2.
Capture d'écran d'une barre de recherche sur Google Sheets
En ajoutant une fonction =QUERY() en J5, nous pouvons regarder ce qui est tapé dans J2 (le cercle rouge) et afficher les résultats de la recherche en dessous (le rectangle bleu).
Dans mon exemple, je recherche parmi un ensemble de transactions financières personnelles (avec des montants aléatoires ) qui se trouvent dans les colonnes A:F.
Capture d'écran des données financières sur Google Sheets
La fonction Query
La fonction =QUERY() recherche dans la plage Transactions (qui est cette plage A3:F où se trouvent toutes les transactions).
Et elle récupère toutes les informations dans la colonne B ou la colonne D qui CONTIENT ce que nous tapons dans J2.
Ainsi, elle recherche dans toutes nos descriptions de transactions de la colonne B et les catégories de la colonne D ce que nous tapons dans J2. La commande LOWER transforme les informations de B et D en minuscules. Cela facilite la recherche car la commande CONTAINS est sensible à la casse.
=QUERY(Transactions,
"SELECT A,B,C,D,E WHERE LOWER(B) CONTAINS '"&J2&"' OR LOWER(D) CONTAINS '"&J2&"'")
- le seul bémol est que si vous tapez en majuscules dans la barre de recherche, cela ne fonctionnera pas correctement.
La fonction Filter
En utilisant la fonction =FILTER() en combinaison avec la fonction =SEARCH(), nous pouvons faire la même chose avec une formule un peu plus courte et sans avoir à nous soucier de la sensibilité à la casse.
=IF(ISBLANK(J2),"",FILTER(Transactions,SEARCH(J2,B3:B225)))
Le compromis ici est que lorsque nous voulons ajouter plusieurs conditions comme nous l'avons fait dans l'instruction =QUERY(), cela ne fonctionne pas. Ni =FIND() ni =SEARCH() n'ont fonctionné correctement lorsque nous avons essayé de les utiliser plus d'une fois à l'intérieur de =FILTER().
J'ai pu trouver une solution en utilisant l'opérateur plus et en construisant la formule de cette manière :
=IF(ISBLANK(J2),"",FILTER(Transactions,(B3:B225=J2)+(D3:D225=J2)))
Malheureusement, lorsque vous filtrez de cette manière, les correspondances partielles ne sont pas incluses dans les résultats de la recherche.
Dans le cas de Query, les réponses partielles sont toujours retournées.
Ainsi, lorsque nous entrons "hom", toutes les lignes contenant "home" seraient retournées. En utilisant plusieurs conditions avec filter, rien ne serait retourné à moins que vous n'entriez le mot entier "home".
Et XLOOKUP ?
Le problème avec XLOOKUP est double. Premièrement, il ne gère pas bien les correspondances partielles sauf si vous ajoutez des caractères génériques :
=XLOOKUP("*"&J2&"*",B3:B225,A3:F225,,2)
Cela augmente la complexité mais fonctionne toujours.
La différence plus importante est qu'il ne retournera qu'un seul résultat, donc cela ne fonctionnera pas du tout pour nous pour ce cas d'utilisation.
Le gagnant est Query
Query remporte la palme simplement parce qu'il n'a pas besoin de manipulation supplémentaire pour ajouter plusieurs conditions, et il retournera toutes les valeurs qui répondent à nos critères de recherche.
Il peut vous falloir une minute pour comprendre la syntaxe, mais elle est tout aussi puissante et plus polyvalente que Filter à long terme.
La seule chose à bien retenir est la sensibilité à la casse. Si vous utilisez la commande LOWER dans votre requête, n'utilisez pas de lettres de recherche en majuscules.
Rendre cela propre
Dans la formule complète, j'ai ajouté une fonction =IF() au début pour gérer la barre de recherche vide. Nous voulons ne rien retourner dans ce cas :
=IF(ISBLANK(J2),"", QUERY(Transactions "SELECT A,B,C,D,E WHERE LOWER(B) CONTAINS '"&J2&"' OR LOWER(D) CONTAINS '"&J2&"'")
Capture d'écran d'une barre de recherche vide dans Google Sheets
Suivez-moi
Venez me suivre sur YouTube alors que je crée des tutoriels chaque semaine.
Inscrivez-vous ici pour recevoir ma newsletter dans votre boîte mail chaque semaine.
Eamonn's Sheets | Coding | Education logo