La fonction QUERY de Google Sheets vous permet de manipuler efficacement vos données à des fins diverses et de créer facilement des rapports, des graphiques, et plus encore. Cette fonction présente l’avantage notable de pouvoir exécuter des requêtes dans le langage de requête de l’API de visualisation Google, permettant ainsi des manipulations de données diverses.
En essence, la fonction QUERY se présente comme la fonction ultime pour la manipulation des données dans Google Sheets, et ce tutoriel couvre de manière exhaustive tout ce dont vous avez besoin pour la maîtriser.
Syntaxe et arguments de la fonction QUERY
Commençons par la syntaxe de la fonction QUERY dans Google Sheets.
Contrairement à la plupart des autres fonctions, comprendre comment l’utiliser n’est pas évident en examinant simplement les arguments. Pas de panique, cependant. Vous comprendrez parfaitement en suivant mes exemples et mes guides étape par étape.
Syntaxe :
QUERY(data, query, [headers])Arguments :
-
data: La plage de cellules (tableau) sur laquelle effectuer la requête. Il est essentiel d’éviter les données de types mixtes dans les colonnes pour garantir des résultats précis. -
query: La requête que vous souhaitez exécuter, qui doit être entre guillemets doubles. Vous pouvez également entrer le texte approprié dans une cellule et vous y référer. -
headers: Le nombre de lignes d’en-tête dans vos données (tableau). En général, il y aura une ligne d’en-tête ou aucune. Si cet argument est omis ou défini sur -1, la valeur de l’en-tête est déduite en fonction du contenu des données source.
Exemple :
=QUERY(A1:F1000, "Select A, C", 1)Dans cet exemple, A1:F1000 représente les données, « Select A, C » est la requête, et 1 indique l’en-tête.
La formule renvoie les colonnes A et C.
Pour maîtriser la fonction QUERY de Google Sheets, il est important de se concentrer sur la compréhension de l’argument de requête, qui est un sujet vaste.
Simplifier l’argument ‘Query’
Lorsque vous lisez une formule QUERY écrite par quelqu’un d’autre, la partie la plus déroutante sera le segment de requête entre guillemets. Cela est dû à la présence de clauses de langage (mots-clés), de fonctions de manipulation de données et d’éléments de langage.
Essayer de tous les apprendre en une seule fois n’est pas pratique. Par conséquent, notre approche ici est de vous guider dans l’apprentissage des techniques essentielles de manipulation de données à l’aide de la fonction QUERY de Google Sheets.
Dans le processus, je vous indiquerai où trouver des tutoriels avancés sur ce blog pour maîtriser l’une des fonctions les plus puissantes de Google Sheets. C’est parti !
Données d’exemple pour tester la fonction QUERY
Nous allons prendre en compte des données d’exemple réparties sur six colonnes et seize lignes dans la plage A1:F16. Les libellés des champs dans A1:F1 sont les suivants :
- A, B, C, D, E, F
Ces données se trouvent dans l’onglet ‘Sheet1’. Vous pouvez obtenir une copie des données complètes en cliquant sur le bouton ci-dessous.
Nous utiliserons les données de l’onglet ‘Sheet1’ pour apprendre la fonction QUERY. Avant de commencer, veuillez remonter et revoir la syntaxe.
Il y a trois arguments dans la syntaxe. Les premier et dernier sont les données et les en-têtes, respectivement.
Ainsi, selon mon exemple de feuille, les données seront A1:F (plage ouverte) ou A1:F16 (plage fermée), et les en-têtes seront 1. Ci-dessous, nous nous concentrerons sur l’argument de requête.
Sélection des colonnes dans la fonction QUERY (Clause SELECT)
Cette section explique comment sélectionner des colonnes dans la fonction QUERY.
Exemples d’utilisation basique de la fonction QUERY de Google Sheets:
Dans nos données d’exemple ci-dessus, composées de six colonnes, vous pouvez manipuler et sélectionner des colonnes spécifiques en utilisant la clause SELECT (mot clé) dans la fonction QUERY.
=QUERY(Sheet1!A1:F16, "select A, B, F, C, D, E", 1)La formule ci-dessus renvoie les colonnes dans un ordre différent.
=QUERY(Sheet1!A1:F16, "select A, E", 1)Cette formule est utilisée pour renvoyer uniquement les colonnes A et E.
Si vous remplacez « select A, E » par « select* », toutes les colonnes de la table source des données sont renvoyées dans leur ordre d’origine.
=QUERY(Sheet1!A1:F16,, 1)Ici, l’argument de requête est omis. Dans cette formule, seules les données et les en-têtes sont spécifiés.
Si vous spécifiez 16 dans les en-têtes au lieu de 1, la formule renverra une sortie de 1 ligne x 6 colonnes, avec toutes les valeurs comme une ligne d’en-tête. Pour plus d’informations, vous pouvez lire à ce sujet ici : La formule de tableau flexible pour joindre des colonnes dans Google Sheets.
Sélectionner des résultats à partir de fonctions d’agrégation, de fonctions scalaires et d’opérateurs arithmétiques
Nous avons exploré comment sélectionner des champs/colonnes existants dans le tableau sans aucune manipulation. Ci-dessous, voyons comment traiter les valeurs des colonnes et renvoyer des résultats.
Fonction scalaire Year dans la clause SELECT :
=QUERY(Sheet1!A1:F16, "select A, year(E)", 1)Cette formule renvoie les noms de la colonne A et l’année d’adhésion dans la colonne E. La colonne E dans le tableau de données source contient la date d’adhésion.
Fonction d’agrégation Avg dans la clause SELECT :
Vous pouvez utiliser cette formule lorsque vous souhaitez renvoyer la moyenne des valeurs de la colonne F, qui contient l’âge.
=QUERY(Sheet1!A1:F16, "select avg(F)", 1)Fonction scalaire Year et opérateur de soustraction dans la clause SELECT :
Pour trouver l’âge au moment de l’adhésion, nous pouvons soustraire l’année de la date de naissance de l’année d’adhésion. Voici un exemple.
=QUERY(Sheet1!A1:F16, "select A, year(E)-year(B)", 1)Note : Nous ne considérons que les années. Par conséquent, l’âge ne sera pas entièrement précis en raison de la question des mois/années complets.
Vous pouvez en apprendre plus sur les fonctions scalaires, les opérateurs arithmétiques et les fonctions d’agrégation dans une partie ultérieure de ce tutoriel.
Identifiants de colonne
Comme le montrent les exemples ci-dessus, les identifiants de colonne sont généralement des lettres majuscules d’une ou deux caractères, comme A, B, C, …. AA, AB, etc.
Cependant, dans certains cas, nous devons utiliser des identifiants tels que Col1, Col2, etc. Savez-vous exactement quand c’est nécessaire ?
Si vos données sont dans une plage physique comme A1:F16, les identifiants de colonne seront les lettres majuscules standard – A, B, C, etc.
Cependant, lorsqu’il s’agit d’une expression, comme le résultat d’autres formules, la première colonne de la plage devient Col1, la deuxième colonne devient Col2, et ainsi de suite.
Lors de la discussion sur la sélection des colonnes dans la fonction QUERY de Google Sheets, la formule suivante illustre l’insertion d’une colonne entre A et B, remplie de tirets :
=QUERY(Sheet1!A1:F16, "Select A, '-', B", 1)Filtrer les lignes à l’aide de la fonction QUERY de Google Sheets (Clause WHERE)
Nous avons exploré comment utiliser le mot-clé SELECT dans la fonction QUERY de Google Sheets pour sélectionner des colonnes.
Maintenant, que dire du filtrage des lignes ?
Pour ce faire, nous devons utiliser la clause WHERE (mot-clé).
Dans la clause WHERE, nous devons utiliser des opérateurs de comparaison (simples ou complexes) ou des opérateurs logiques (ET, OU et NON) à des fins de filtrage.
Dans nos données d’exemple, pour filtrer le genre « M », nous pouvons utiliser la formule suivante :
=QUERY(Sheet1!A1:F, "Select A, D where D='M' ", 1)La formule ci-dessous filtre les noms dont l’âge est de 12 ans :
=QUERY(Sheet1!A1:F, "Select A where F=12 ", 1)La requête suivante filtre les noms dont la date d’adhésion est le 01/01/2015 :
=QUERY(Sheet1!A1:F, "Select A where E=date '2015-01-01'", 1)Les trois exemples de formules de requête servent à deux fins :
-
Démontrer comment utiliser le mot-clé WHERE dans la fonction QUERY de Google Sheets.
-
Illustrer l’utilisation de différents types de valeurs (littéraux), c’est-à-dire du texte, des nombres et des dates, pour les comparaisons.
Bien que ces formules donnent un aperçu de l’utilisation du mot-clé WHERE dans la fonction QUERY de Google Sheets, pour une manipulation complète des données, il est fortement recommandé de consulter des tutoriels supplémentaires. Je vous invite à explorer les tutoriels ci-dessous à votre rythme.
Conseils supplémentaires pour le filtrage :
Dans les exemples ci-dessus, je n’ai pas donné d’exemples sur la façon de filtrer une colonne de valeurs booléennes TRUE ou FALSE car je n’ai pas de telle colonne dans mes données d’exemple.
Si la colonne G contient TRUE ou FALSE, vous pouvez utiliser WHERE G=TRUE ou WHERE G=FALSE.
Important :
La correspondance des chaînes est sensible à la casse dans la fonction QUERY. Utilisez les fonctions scalaires UPPER() ou LOWER() pour effectuer une correspondance de chaîne insensible à la casse.
Par exemple :
=QUERY(Sheet1!A1:F, "Select A where lower(D)='m' ", 1)Vous avez peut-être remarqué une chose dans les exemples de formules sous le mot-clé WHERE.
J’ai utilisé la plage ouverte Sheet1!A1:F plutôt que Sheet1!A1:F16 (fermée).
C’est parce que nous filtrions en fonction de conditions qui peuvent supprimer des lignes vides du résultat. Pour filtrer immédiatement les lignes vides, vous pouvez utiliser IS NOT NULL comme suit.
=QUERY(Sheet1!A1:F, "Select A, E, F where A is not null", 1)Regroupement et agrégation (Clause GROUP BY)
Nous avons appris comment sélectionner et filtrer des colonnes. Cette section explique comment regrouper et agréger des données à l’aide de la fonction QUERY.
Pour cela, nous utilisons la clause GROUP BY (combinaison de mots-clés), qui est également un sujet complet, similaire à la clause WHERE.
Explorons d’abord quelques exemples basiques. Ensuite, je vous guiderai vers des exemples d’utilisation avancée de cette fonctionnalité.
Points clés :
Voici quelques considérations importantes lors de l’utilisation de la combinaison de mots-clés GROUP BY dans la fonction QUERY de Google Sheets :
-
Elle agrège les valeurs sur les lignes.
-
Une seule ligne est créée pour chaque combinaison distincte de valeurs dans la clause GROUP BY.
-
Les données sont automatiquement triées par les colonnes de regroupement.
-
Tous les identifiants de colonne spécifiés dans la clause SELECT doivent faire partie de la clause GROUP BY. Cependant, vous pouvez inclure des identifiants de colonne non spécifiés également.
Exemples utilisant des fonctions d’agrégation :
La formule suivante groupe les données par âge et renvoie le compte.
=QUERY(Sheet1!A1:F16, "Select F, count(F) group by F", 1)Et celle-ci groupe les données par âge et date d’adhésion et renvoie le compte.
=QUERY(Sheet1!A1:F16, "Select F, E, count(F) group by F, E", 1)J’ai utilisé la fonction d’agrégation count dans les formules. Nous pouvons utiliser de nombreuses autres fonctions d’agrégation. Je couvrirai cela plus tard.
Si vous utilisez une plage ouverte, n’oubliez pas d’utiliser le mot-clé WHERE pour filtrer les lignes vides dans l’agrégation.
=QUERY(Sheet1!A1:F, "Select F, count(F) where F is not null group by F", 1)Exemples utilisant une fonction scalaire :
=QUERY(A1:F, "Select year(E), count(E) where E is not null group by year(E)", 1)Il groupe les données par année d’adhésion et renvoie le compte.
La formule utilise la fonction scalaire YEAR() pour extraire l’année de la date d’adhésion dans la colonne E.
Exemples utilisant des opérateurs arithmétiques :
=QUERY(A1:F, "Select year(now())-year(E), count(E) where E is not null group by year(now())-year(E)", 1)Vous pouvez voir l’utilisation de l’opérateur de soustraction arithmétique dans la formule ci-dessus. Il groupe par le nombre d’années depuis l’adhésion et renvoie le compte.
Nous devons utiliser les clauses du langage de requête (mots-clés ou combinaisons de mots-clés) dans un ordre particulier, et nous les apprenons dans cet ordre dans ce tutoriel. Vous pouvez en lire plus à ce sujet ici : Quel est l’ordre correct des clauses dans la fonction QUERY de Google Sheets ?
Ressources sur le regroupement et l’agrégation :
-
Comment utiliser les opérateurs arithmétiques dans une requête Google Sheets.
-
Comment calculer la somme, la moyenne, le compte, le maximum et le minimum dans une requête Google Sheets.
Habituellement, la plupart d’entre elles sont similaires dans leur utilisation. Vous pouvez rechercher des tutoriels sur les fonctions TODATE, QUARTER, UPPER, LOWER et DATEDIFF.
Pivoter les données à l’aide de la fonction QUERY (Clause PIVOT)
Je peux vous aider à comprendre la clause PIVOT avec trois exemples.
La formule QUERY suivante (dans la cellule H1 dans la capture d’écran ci-dessous) groupe les données par date d’adhésion et genre et renvoie le compte.
=QUERY(Sheet1!A1:F16, "Select E, D, count(E) group by E, D", 1)Ainsi, le résultat contient trois colonnes : Date d’adhésion, Genre et Compte de la date d’adhésion.
Maintenant, regardez la formule QUERY (dans la cellule H9 dans la capture d’écran ci-dessus) qui utilise le mot-clé PIVOT.
=QUERY(Sheet1!A1:F16, "Select E, count(E) group by E pivot D", 1)J’ai omis la colonne D dans les clauses SELECT et GROUP BY, et l’ai plutôt utilisée dans PIVOT.
Ici également, la première colonne dans la sortie sera la Date d’adhésion (colonne E). Ensuite, il y aura deux autres colonnes car la colonne pivot D (Genre) contient deux valeurs distinctes, c’est-à-dire « M » et « F ». Le compte (valeurs agrégées) sera sous ces deux colonnes.
Les rapports de pivot sont pratiques pour préparer des graphiques en barres, en ligne, en colonnes ou en camembert (visualisation des données) dans Google Sheets.
Pouvons-nous utiliser le mot-clé PIVOT sans le mot-clé GROUP BY dans la fonction QUERY ?
Absolument ! Voici un exemple.
=QUERY(Sheet1!A1:F16, "Select count(A) pivot E", 1)Veuillez le tester vous-même.
Voici quelques ressources supplémentaires pour explorer davantage la clause PIVOT dans la fonction QUERY de Google Sheets.
-
Comment utiliser la fonction QUERY de Google Sheets de manière similaire à un tableau croisé dynamique.
-
Comment formater l’en-tête de requête pivot dans Google Sheets.
-
Comment pivoter plusieurs colonnes dans une requête Google Sheets.
Trier les données (Clause SORTBY)
À l’aide de la clause ORDER BY (combinaison de mots-clés), nous pouvons trier les lignes dans les données source en fonction des valeurs des colonnes spécifiées.
Exemple n°1 (Order by item est un identifiant de colonne) :
=QUERY(Sheet1!A1:F16, "select A, B, F order by A asc", 1)Elle trie les noms de la colonne A par ordre croissant. La formule suivante trie la date de naissance de la colonne B par ordre décroissant, puis les noms de la colonne A par ordre croissant.
=QUERY(Sheet1!A1:F16, "select A, B, F order by B desc, A asc", 1)Exemple n°2 (Order by item est le résultat de la fonction d’agrégation count) :
=QUERY(Sheet1!A1:F, "Select F, count(F) where F is not null group by F order by count(F) desc", 1)Note : Vous pouvez utiliser deux types d’éléments dans la clause ORDER BY : 1) Identifiants de colonne. 2) Résultat de fonctions d’agrégation, de fonctions scalaires ou d’opérateurs.
Limiter le nombre de lignes (Clause LIMIT)
L’objectif de la clause LIMIT (mot-clé) dans la fonction QUERY de Google Sheets est de limiter le nombre de lignes renvoyées.
Dans la formule juste ci-dessus, nous pouvons utiliser LIMIT 3 pour limiter le nombre de lignes renvoyées à 3, en excluant l’en-tête.
Exemple :
=QUERY(Sheet1!A1:F, "Select F, count(F) where F is not null group by F order by count(F) desc limit 3", 1)Ignorer les lignes dans la fonction QUERY de Google Sheets (Clause OFFSET)
L’objectif de la clause OFFSET (mot-clé) est de sauter un certain nombre de premières lignes.
Exemple n°1 :
=QUERY(Sheet1!A1:F, "Select * offset 3", 1)Exemple n°2 :
=QUERY(Sheet1!A1:F, "Select F, count(F) where F is not null group by F order by count(F) desc limit 3 offset 1", 1)Dans le deuxième exemple ci-dessus, nous avons également utilisé le mot-clé LIMIT. La requête ignore d’abord les lignes, puis applique la limite.
Étiqueter et formater le résultat de la requête (Clauses LABEL et FORMAT)
Parfois, la formule QUERY peut renvoyer des en-têtes (étiquettes) « laids ». Veuillez consulter la formule H1 dans la partie supérieure de la capture d’écran ci-dessous.
La clause LABEL (mot-clé) permet de définir l’étiquette d’une ou de plusieurs colonnes. Ainsi, nous pouvons renommer les « laid » en utilisant la formule suivante.
=QUERY(Sheet1!A1:F16, "select A, year(E)-year(B) label year(E)-year(B) 'Age at the time of Joining'", 1)Cela remplacera l’étiquette difference(year(Date of Joining)year(DOB)) par Age at the time of Joining.
Référence: Query Language Reference (Version 0.7)
Merci de votre attention. Profitez-en bien.
Références :
