Je suis surpris de constater que personne n’a encore utilisé l’Offset Match en utilisant Query dans Google Sheets. J’espère donc que ce contenu est nouveau pour vous !
Je sais que le titre est un peu maladroit. J’ai essayé de le raccourcir sans perdre le sens, mais sans succès, n’est-ce pas ?
Que veut-on dire par Offset Match en utilisant Query ?
Cela signifie simplement décaler un certain nombre de lignes en fonction d’un mot-clé correspondant.
En utilisant la fonction Query de Google Sheets, vous pouvez décaler un certain nombre de lignes en utilisant la clause Offset.
Si vous mettez « Offset 5 », la formule Query décalera de 5 lignes. Le chiffre 5 est donc le « nombre de lignes donné ».
Vous pouvez remplacer ce nombre de décalage par une formule basée sur la fonction Match. Quel est l’avantage de le faire ?
Dans un tableau à une dimension, vous pouvez utiliser une clé de recherche dans la formule Match pour renvoyer la position relative de cette clé dans le tableau/plage. Ensuite, utilisez ce nombre pour effectuer un décalage dans Query.
Exemple d’Offset Match en utilisant Query dans Google Sheets
Voici un exemple.
Ce tutoriel porte en réalité sur l’utilisation de la fonction Match dans la clause Offset de la formule Query.
La formule Query ci-dessus recherche la clé de recherche « Mercredi » dans la colonne A et effectue un décalage jusqu’à cette ligne.
En réalité, la formule Query ci-dessus est une alternative à la combinaison de formules Index et Match ! Index Match est une combinaison de formules populaire dans les feuilles de calcul pour effectuer une recherche et un décalage.
La fonction Match trouve une valeur et l’Index utilise cette valeur de correspondance pour effectuer un décalage. C’est ce qu’on appelle Index Match et c’est ce que j’ai fait avec la formule Query ci-dessus.
Pouvez-vous me montrer la formule Index Match alors ?
Voici !
=index(A2:C,Match("Mercredi",A2:A,0))
Maintenant, je vais vous montrer comment j’ai reproduit le même résultat avec la fonction Match dans la formule Query.
J’ai déjà un tutoriel très détaillé sur la fonction Index Match. Je ne vais donc pas entrer dans les détails.
Je vais plutôt vous expliquer plus en détail la formule Query que j’ai utilisée dans la capture d’écran d’exemple qui offre de nombreuses possibilités de manipulation des données.
Formule et explication de l’Offset Match en utilisant Query
Formule :
=query(A2:C,"Select * limit 1 offset "&match("Mercredi",A2:A,0)-1)
Dans les deux formules, c’est-à-dire l’Index Match et l’Offset Match en utilisant Query, j’ai utilisé la même formule Match.
Pour votre référence rapide, voici la syntaxe de la fonction Match dans Google Sheets :
MATCH(clé_recherche, plage, [type_recherche])
La clé de recherche utilisée dans Match est « Mercredi » et sa position relative dans l’ensemble de données donné est 4.
Avec Index, vous pouvez utiliser ce 4 (formule Match) tel quel (veuillez faire défiler vers le haut et voir la formule). Cela renvoie les valeurs de la 4e ligne.
Mais avec Query, vous ne devez décaler que de 3. Si vous définissez le nombre de décalage sur 4, la formule décalera de 4 lignes et renverra la valeur de la 5e ligne.
J’ai donc utilisé la formule Match dans Query comme suit :
match("Mercredi",A2:A,0)-1
Remarquez le moins 1 à la fin.
La formule Query est donc égale à :
=query(A2:C,"Select * limit 1 offset 3")
Vous avez appris un nouvel astuce dans Google Sheets, c’est comment réaliser un Offset Match en utilisant Query.
Quel est l’avantage de l’Offset Query par rapport à l’Index Match ?
Index Match vs. Offset Match en utilisant Query – Principaux avantages
Query est plus flexible. Vous pouvez jouer avec la clause Limit dans Query pour renvoyer un certain nombre de lignes après le décalage de Match.
Formule d’exemple 1 :
=query(A2:C,"Select * limit 2 offset "&match("Mercredi",A2:A,0)-1)
Résultat :
(Formule d’exemple 2) :
=query(A2:C,"Select * offset "&match("Mercredi",A2:A,0)-1)
Dans cette formule, j’ai exclu la clause limit. La formule décalera donc les 3 premières lignes et renverra les lignes restantes telles quelles.
Cela signifie que l’Offset Match en utilisant Query dans Google Sheets a un certain avantage par rapport à l’Index Match en termes de manipulation des données.
