Ce post ne parle pas de la fonction de correspondance des expressions régulières dans Query. Il s’agit plutôt d’apprendre comment utiliser la fonction Match dans la clause Where de Query dans Google Sheets.
Sans aucun doute, la fonction Match est l’une des fonctions les plus utiles dans toutes les applications de tableur populaires. Elle permet de renvoyer la position d’une chaîne de caractères, d’une date ou d’une valeur numérique dans une plage à une seule ligne ou à une seule colonne.
La position (appelée position relative) renvoyée par la formule Match peut être utilisée comme une expression dans d’autres fonctions. Ici, ces « autres fonctions » font référence à Query.
Oui ! Nous allons utiliser la fonction Match pour obtenir la position d’un élément dans une seule ligne, dans Query. Et quel est le but ?
Lorsque nous utilisons la fonction Match dans la clause Where de Query dans Google Sheets, le but est de rechercher la colonne à filtrer au lieu de spécifier directement la colonne.
Veuillez consulter les données ci-dessous.
Il s’agit d’un échantillon de feuille « Présence » dans lequel la première ligne contient quelques noms (employés ou étudiants) et en dessous leur statut présent/absent/congé (présence).
Je veux filtrer les données pour « Scott », mais pas toutes les données, seulement les jours où il était absent (représentés par « A »).
Ici, je vais utiliser la fonction Match dans la clause Where de Query pour résoudre le problème.
Nous pouvons utiliser une seule fonction Match ou plusieurs fonctions Match dans la clause Where de Query dans Google Sheets en fonction du nombre de colonnes à filtrer.
Dans le cas présent, nous voulons simplement utiliser une seule fonction Match.
Utilisation d’une seule fonction Match dans la clause Where de Query dans Google Sheets
Avant d’inclure la fonction Match dans la clause Where de Query, voyons comment nous utiliserions normalement la formule dans ce cas.
En utilisant la formule Query ci-dessous, nous pouvons filtrer les données pour les jours d’absence de « Scott ».
=query( A1:J, "Select * where H='A'",1 )Ici, il n’y a pas de possibilité d’inclure la fonction Match dans la clause Where. Voyons une alternative à la formule ci-dessus.
Comptez d’abord les colonnes de gauche à droite dans les données jusqu’à atteindre « Scott » dans la ligne n° 1. Vous obtiendrez le numéro 8 (colonne H), n’est-ce pas ?
En utilisant les données comme une expression (en entourant les données de crochets), nous pouvons utiliser les numéros de colonnes dans la clause Where.
=query( {A1:J}, "Select * where Col8='A'",1 )Cela nous permet d’utiliser la fonction Match suivante. Comment ?
=match("Scott",A1:J1,0)La formule ci-dessus renverra le numéro de colonne 8 correspondant au nom « Scott ». Nous pouvons remplacer le 8 dans la formule Query ci-dessus par la formule Match juste au-dessus.
=query( {A1:J}, "Select * where Col"&match("Scott",A1:J1,0)&"='A'",1 )Note : Si vous ne voulez que la colonne de dates, remplacez « Select * » par « Select Col1 ».
Si le critère de correspondance, c’est-à-dire « Scott », est une référence de cellule, supposez dans la cellule K1, utilisez la formule suivante.
=query( {A1:J}, "Select * where Col"&match(K1,A1:J1,0)&"='A'",1 )Utilisation de plusieurs fonctions Match dans la clause Where de Query dans Google Sheets
Supposons que vous souhaitiez vérifier si deux de vos employés étaient absents le même jour. Vous voudrez alors utiliser plusieurs fonctions Match dans la clause Where de Query dans Google Sheets.
Ici, je considère les employés « Jeff » et « Scott ».
=query( {A1:J}, "Select * where Col"&match("Scott",A1:J1,0)&"='A' and Col"&match("Jeff",A1:J1,0)&"='A'",1 )J’ai utilisé deux formules Match dans la clause Where ainsi que l’opérateur logique AND de Query.
Formules alternatives (Filtrer et Query lui-même)
Si vous voulez simplement filtrer les enregistrements pour un employé, en l’occurrence « Scott », vous pouvez bien sûr envisager d’utiliser des formules alternatives. Sans aucun doute, Filtrer est en tête de liste.
=filter( A1:J8, filter( A1:J8,A1:J1="Scott" )="A" )La formule interne Filtrer filtre la 8ème colonne en fonction du nom « Scott ».
La formule externe Filtrer filtre le tableau si la 8ème colonne contient la lettre « A ».
Vous pouvez trouver plus de détails ici – Two-way Filter in Google Sheets [Dynamic Vertical and Horizontal Filter].
Pour améliorer votre compétence sur Google Sheets, je vous propose une autre formule. Il s’agit en fait d’utiliser la fonction Query avec Transposer.
=Query( {transpose(query(transpose(A1:J),"Select * where Col1='Scott'")),A1:J}, "Select Col2 where Col1='A'" )Explication (Transposer Query)
La transposition, c’est-à-dire transpose(A1:J), modifie l’orientation des données de sorte que les noms d’employés se trouvent dans la première colonne des données transposées.
La fonction Query sélectionne la ligne qui contient le nom « Scott » dans la première colonne.
query(transpose(A1:J),"Select * where Col1='Scott'")J’ai à nouveau transposé ces données. Ainsi, nous avons la 8ème colonne avec nous.
transpose(query(transpose(A1:J),"Select * where Col1='Scott'"))J’ai ajouté l’ensemble des données en tant que colonnes adjacentes à droite de la 8ème colonne.
{transpose(query(transpose(A1:J),"Select * where Col1='Scott'")),A1:J}La fonction Query externe filtre la première colonne si elle contient « A ».
En raison de cela (la première colonne contient les noms puis toutes les huit colonnes), j’ai commencé la clause Select de la Query externe par « Select Col2 » au lieu de « Select Col1 ».
Cela renverra uniquement la colonne de la date.
Pour toutes les colonnes, vous devrez spécifier la clause Where dans la Query externe comme « Select Col2, Col3, Col4…
C’est tout ce qu’il y a à savoir sur l’utilisation de la fonction Match dans la clause Where de Query dans Google Sheets.
Merci de votre attention. Amusez-vous bien !
- Dynamic Column Id in Query Importrange Using Named Ranges.
- How to Get Dynamic Column Reference in Google Sheets Query.
- Dynamic Formula to Select Every nth Column in Query in Google Sheets.
