La fonction QUERY de Google Sheets vous permet de manipuler efficacement vos données à des fins diverses et en toute simplicité. Elle facilite l’extraction d’informations essentielles à partir d’ensembles de données complexes et facilite la création de rapports, de graphiques, et bien plus encore.
L’un des avantages notables de cette fonction est sa capacité à exécuter des requêtes dans le langage de requête de l’API de visualisation Google, permettant ainsi diverses manipulations de données.
En essence, la fonction QUERY est l’ultime fonction pour la manipulation de données dans Google Sheets, et ce tutoriel couvre de manière exhaustive tout ce dont vous avez besoin pour la maîtriser.
Fonction QUERY : Syntaxe et arguments
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 immédiat en examinant les arguments. Ne vous inquiétez pas, cependant. Vous aurez une compréhension claire grâce à mes exemples et mes guides étape par étape.
Syntaxe : QUERY(data, requête, [en-têtes])
Arguments :
- data : L’étendue de cellules (tableau) sur laquelle effectuer la requête. Il est essentiel d’éviter les données de types mixtes dans les colonnes pour obtenir des résultats précis.
Qu’est-ce que les données de types mixtes ?
Par exemple, dans la colonne A, si vous avez des codes d’articles comme la séquence 1111, 1112, 1113, etc., assurez-vous de les formater tous soit en tant que nombres, soit en tant que textes, mais évitez de mélanger les deux types. En substance, une colonne désignée comme un type de nombre ne doit contenir que des nombres, une colonne de type de date doit contenir uniquement des dates, et une colonne de type de texte doit contenir uniquement des textes.
-
requête : La requête que vous souhaitez exécuter doit être placée entre guillemets. Vous pouvez également saisir le texte approprié dans une cellule et y faire référence.
-
en-têtes : 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 d’en-tête est devinée 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 essentiel de se concentrer sur la compréhension de l’argument de requête, qui est un sujet vaste.
Simplification de l’argument « requête »
Lorsque vous lisez une formule QUERY écrite par quelqu’un d’autre, la partie la plus déroutante sera la section de requête entre guillemets. C’est parce qu’elle contient des clauses de langage (mots-clés), des fonctions de manipulation de données et des éléments de langage.
Essayer de tout 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. Allons-y !
Données d’exemple pour tester la fonction QUERY
Nous considérerons des données d’exemple réparties sur six colonnes et seize lignes dans la plage A1:F16. Les étiquettes de champ dans A1:F1 sont les suivantes :
A, B, C, D, E, F
Elles se trouvent dans l’onglet ‘Feuille1’. Vous pouvez obtenir une copie de l’ensemble des données en cliquant sur le bouton ci-dessous.
Nous utiliserons les données trouvées sur la feuille d’exemple pour apprendre la fonction QUERY. Avant de commencer, veuillez remonter et revoir la syntaxe.
Il y a trois arguments dans la syntaxe. Les deux premiers sont les données et les en-têtes, respectivement.
Donc, selon ma feuille d’exemple, 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 de base de la fonction QUERY de Google Sheets
Dans nos données d’exemple ci-dessus, qui comportent six colonnes, vous pouvez manipuler et sélectionner des colonnes spécifiques à l’aide de 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 de données sont renvoyées dans leur ordre d’origine.
Une alternative pour renvoyer toutes les colonnes dans leur ordre d’origine est la formule suivante :
=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 d’une ligne sur six colonnes, avec toutes les valeurs sous forme d’en-tête de ligne. Pour plus d’informations, vous pouvez lire à ce sujet ici : La formule de tableau flexible pour joindre des colonnes dans Google Sheets.
Sélection des résultats à l’aide de fonctions d’agrégation, de fonctions scalaires et d’opérateurs arithmétiques
Ci-dessus, nous avons exploré la sélection de champs/colonnes existants dans le tableau sans aucune manipulation. Ci-dessous, voyons comment traiter les valeurs dans les 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 dans 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 tenons compte que des années. Par conséquent, l’âge ne sera pas totalement précis en raison de la question des mois/années entiers.
Vous pouvez en apprendre davantage sur les fonctions scalaires, les opérateurs arithmétiques et les fonctions d’agrégation dans la suite de ce tutoriel ci-dessous.
Identifiants de colonnes
Comme le démontrent les exemples ci-dessus, les identifiants de colonnes sont généralement des lettres de colonne d’un ou deux caractères, comme A, B, C, …. AA, AB, etc.
Cependant, dans certains scénarios, nous devons utiliser des identifiants comme Col1, Col2, etc. Savez-vous quand il est nécessaire de le faire exactement ?
Si vos données se trouvent dans une plage physique comme A1:F16, les identifiants de colonnes 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, etc.
En discutant de la sélection de 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)
Filtrage des lignes à l’aide de la fonction QUERY de Google Sheets (clause WHERE)
Nous avons exploré ci-dessus comment utiliser le mot-clé SELECT dans la fonction QUERY de Google Sheets pour sélectionner des colonnes.
Et maintenant, que diriez-vous de filtrer les lignes ?
Pour cela, 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 (AND, OR et NOT) à 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 formule Query suivante filtre les noms dont les dates d’adhésion sont le 01/01/2015 :
=QUERY(Sheet1!A1:F, « Select A where E=date ‘2015-01-01′ », 1)
Les trois exemples de formule Query ci-dessus servent à deux fins :
-
Illustrer 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, des tutoriels supplémentaires sont FORTEMENT recommandés. Veuillez explorer les tutoriels ci-dessous dans votre temps libre.
Filtrage des données : Ressources
Littéraux :
-
Exemples de l’utilisation de littéraux dans une requête dans Google Sheets.
-
Comment utiliser les critères de date dans la fonction QUERY de Google Sheets.
-
Comment utiliser DateTime dans une requête dans Google Sheets.
Opérateurs de comparaison :
-
Opérateurs de comparaison simples dans la requête Query de Sheets.
-
Comment utiliser l’opérateur LIKE String dans la requête de Google Sheets.
-
Correspondance de sous-chaîne CONTAINS dans la requête de Google Sheets pour une correspondance partielle.
-
Correspondance d’expression régulière MATCH dans la requête de Google Sheets.
-
Correspondance de préfixe commençant par et ne commençant pas par dans une requête.
-
Correspondance de suffixe commençant par et ne commençant pas par dans une requête.
Opérateurs logiques :
-
Comment utiliser And, Or et Not dans la requête de Google Sheets.
-
Comment utiliser Not Equal to dans une requête dans Google Sheets.
Autres conseils de filtrage
Dans les exemples ci-dessus, je n’ai pas donné d’exemples de filtrage d’une colonne de valeur Booléenne 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, utilisez WHERE G=TRUE ou WHERE G=FALSE.
Important :
La correspondance de chaînes est sensible à la casse dans la fonction QUERY. Utilisez les fonctions scalaires UPPER() ou LOWER() pour effectuer une correspondance de chaînes insensible à la casse.
Exemple :
=QUERY(Sheet1!A1:F, « Select A where lower(D)=’m’ « , 1)
Vous avez peut-être remarqué une chose dans les exemples de formule sous la clause WHERE.
J’ai utilisé la plage ouverte Sheet1!A1:F au lieu de Sheet1!A1:F16 (fermé).
C’est parce que nous filtrions en fonction de conditions qui peuvent supprimer des lignes vides du résultat. Pour filtrer les lignes vides directement, 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 explore comment regrouper et agréger des données à l’aide de la fonction QUERY.
À cette fin, nous utilisons la clause GROUP BY (combinaison de mots-clés), qui est également un sujet complet, semblable à la clause WHERE.
Explorons d’abord quelques exemples de base. Ensuite, je vous guiderai vers les endroits où trouver 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 dans 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 colonnes spécifiés dans la clause SELECT doivent faire partie de la clause GROUP BY. Cependant, vous pouvez également inclure des identifiants de colonnes non spécifiés.
Exemples utilisant une fonction 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, puis 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, souvenez-vous 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)
Cela 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. Elle groupe par le nombre d’années depuis l’adhésion et renvoie le compte.
Nous devons utiliser les clauses (mots-clés ou combinaisons de mots-clés) du langage de requête 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 requête de Google Sheets ?
Groupement et agrégation : Ressources
-
Comment utiliser les opérateurs arithmétiques dans la requête de Google Sheets.
-
Comment sommer, moyenniser, compter, trouver le maximum et le minimum dans la requête de Google Sheets.
-
Il existe plusieurs fonctions scalaires dans la fonction QUERY de Google Sheets, et les voici :
- year()
- month()
- day()
- hour()
- minute()
- second()
- millisecond()
- quarter()
- dayOfWeek()
- now()
- dateDiff()
- toDate()
- upper()
- lower()
La plupart d’entre elles sont similaires à utiliser. Vous pouvez rechercher sur ce blog 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 (mot-clé) avec trois exemples.
La formule QUERY suivante (dans la cellule H1 dans la capture d’écran ci-dessous) groupe la date d’adhésion et le genre, et renvoie le compte.
=QUERY(Sheet1!A1:F16, « Select E, D, count(E) group by E, D », 1)
Le résultat contient donc trois colonnes : Date d’adhésion, Genre et compte 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, plutôt utilisée dans PIVOT.
Ici aussi, la première colonne dans la sortie sera la date d’adhésion (colonne E). Ensuite, il y aura deux colonnes supplémentaires 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 utiles pour préparer des graphiques à barres, à lignes, à colonnes ou à secteurs (visualisation des données) dans Google Sheets.
Pouvons-nous utiliser le mot-clé PIVOT sans le mot-clé GROUP BY dans la fonction QUERY ?
Oui ! 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 manière similaire à un tableau croisé dynamique dans Google Sheets.
-
Comment formater l’en-tête de ligne de pivot dans la fonction QUERY de Google Sheets.
-
Comment pivoter plusieurs colonnes dans une requête dans 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 des données source en fonction des valeurs des colonnes spécifiées.
Exemple 1 (Trier par élément est un identifiant de colonne) :
=QUERY(Sheet1!A1:F16, « select A, B, F order by A asc », 1)
Il trie les noms de la colonne A par ordre croissant. La formule suivante trie la date de naissance dans la colonne B par ordre décroissant, puis les noms dans la colonne A par ordre croissant.
=QUERY(Sheet1!A1:F16, « select A, B, F order by B desc, A asc », 1)
Exemple 2 (Trier par élément est le résultat de la fonction count) :
=QUERY(Sheet1!A1:F, « Select F, count(F) where F is not null group by F order by count(F) desc », 1)
Remarque : Vous pouvez utiliser deux types d’éléments dans la clause ORDER BY : 1) Identifiants de colonnes. 2) Résultat de fonctions d’agrégation, de fonctions scalaires ou d’opérateurs.
Limite du nombre de lignes (clause LIMIT)
Le but 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 au-dessus, nous pouvons utiliser LIMIT 3 pour limiter le nombre de lignes renvoyées par celle-ci à 3 lignes, 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)
Décalage des lignes dans la fonction QUERY de Google Sheets (clause OFFSET)
Le but de la clause OFFSET (mot-clé) est de sauter un certain nombre de premières lignes.
Exemple 1 :
=QUERY(Sheet1!A1:F, « Select * offset 3 », 1)
Exemple 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 décale d’abord les lignes, puis applique la limite.
Étiquetage et mise en forme du résultat de la requête (clauses LABEL et FORMAT)
Parfois, la formule Query peut renvoyer des en-têtes (‘étiquettes’) ‘moins attrayants’. Veuillez consulter le résultat de la formule H1 dans la partie supérieure de la capture d’écran ci-dessous.
La clause LABEL (mot-clé) aide à définir l’étiquette de une ou plusieurs colonnes. Nous pouvons donc renommer l’étiquette ‘laide’ comme suit.
=QUERY(Sheet1!A1:F16, « select A, year(E)-year(B) label year(E)-year(B) ‘Age at the time of Joining' », 1)
Elle remplacera l’étiquette difference(year(Date of Joining)year(DOB)) par Age at the time of Joining.
Lié : Comprendre la clause Label dans la requête de Google Sheets.
Conclusion
Cet article couvre de manière exhaustive tout ce dont vous avez besoin pour maîtriser la fonction QUERY de Google Sheets. N’hésitez pas à poser vos doutes ou vos questions dans les commentaires ci-dessous.
Référence : Référence du langage de requête (Version 0.7)
Merci d’avoir lu. Bonne utilisation.
