Google Sheets QUERY est l’une des fonctions les plus puissantes de l’univers GSuite. Elle est extrêmement polyvalente et peut être appliquée à des problèmes simples et complexes.
Mon tutoriel vous guide à travers la fonction Google Sheets QUERY, où l’utiliser, sa syntaxe de base et bien plus encore. Ne vous inquiétez pas : nous irons étape par étape, en commençant par des exemples simples jusqu’aux plus complexes.
Qu’est-ce que Google Sheets QUERY ?
La fonction Google Sheets QUERY vous aide à appliquer une requête à un tableau de données dans Google Sheets. Par exemple, je l’utilise souvent pour extraire des sous-ensembles de données à partir de jeux de données principaux. Cela me permet d’analyser des zones d’intérêt spécifiques et d’obtenir une meilleure compréhension analytique.
Une requête peut être considérée comme un filtre, un tableau croisé dynamique, et bien plus encore. Si vous avez déjà travaillé avec des bases de données en utilisant SQL, vous trouverez que le format de la fonction QUERY de Google Sheets est assez similaire. En fait, la fonction QUERY de Google Sheets implique principalement l’utilisation de requêtes de style SQL afin de les appliquer à un ensemble de données donné.
Syntaxe de la fonction Google Sheets QUERY
La syntaxe de la fonction QUERY est la suivante :
=QUERY(data, query_string, [headers])
- Data : plage de cellules contenant vos données
- Query_string : contient la requête que vous souhaitez exécuter sur vos données
- Headers : nombre de lignes d’en-tête au-dessus de votre jeu de données. Ce paramètre est facultatif.
La fonction QUERY lit la requête fournie dans query_string, l’applique aux données fournies et renvoie le tableau résultant.
Note : Le query_string ne contient rien d’autre qu’une requête.
Curieusement, le format de requête des feuilles de calcul Google est similaire au format d’une requête SQL normale. Une requête se compose généralement d’une ou plusieurs clauses et peut également contenir des fonctions d’agrégation facultatives et/ou des opérations arithmétiques.
Clauses et QUERY
Une clause est une partie d’une requête qui vous permet de filtrer les données fournies. Certaines clauses vous permettent de personnaliser la façon dont vos données seront interrogées.
Par exemple, la clause SELECT vous permet de sélectionner un sous-ensemble de colonnes de votre jeu de données. La clause WHERE des requêtes Google Sheets complète la clause SELECT en filtrant les colonnes sélectionnées selon une condition.
Voici les clauses de requête possibles pour vos données. Notez qu’elles doivent être utilisées dans cet ordre :
- Select
- Where
- Group by
- Pivot
- Order By
- Limit
- Offset
- Label
- Format
- Options
N’oubliez pas que vous ne pouvez pas spécifier « group by » avant « where » ou « select ». L’ordre ci-dessus est l’ordre que vous devrez utiliser avec la fonction Query.
Fonctions d’agrégation et QUERY
Les fonctions d’agrégation sont des fonctions qui effectuent un calcul de valeurs. Ces fonctions renvoient généralement une seule valeur après avoir effectué le calcul. Voici quelques exemples de fonctions d’agrégation :
- SUM – Additionne toutes les valeurs d’une colonne spécifique (ou d’un sous-ensemble de colonne)
- COUNT – Compte le nombre de lignes dans une colonne donnée (ou un sous-ensemble de colonne)
- AVG – Calcule la moyenne des valeurs d’une colonne donnée (ou d’un sous-ensemble de colonne)
- MIN – Trouve la valeur la plus basse d’une colonne donnée (ou d’un sous-ensemble de colonne)
- MAX – Trouve la valeur la plus élevée d’une colonne donnée (ou d’un sous-ensemble de colonne)
Les fonctions d’agrégation sont généralement utilisées en conjonction avec la clause GROUP BY (que nous examinerons un peu plus tard dans ce tutoriel).
Note : Sauf pour la fonction COUNT, toutes les fonctions d’agrégation ignorent les valeurs nulles.
Opérations arithmétiques et QUERY
Les opérations arithmétiques sont des expressions composées d’une constante, d’une variable ou d’une fonction scalaire, ainsi que d’opérateurs tels que l’addition (+), la soustraction (-), la multiplication (*), la division (/) ou le modulo (%). Elles peuvent également inclure des opérateurs de comparaison tels que >, <, =, <=, >=.
Ces opérateurs sont utilisés pour effectuer des opérations mathématiques sur des données sélectionnées à partir de votre jeu de données principal.
10 exemples de requêtes Google Sheets
Pour vous montrer comment utiliser la fonction QUERY de Google Sheets, nous allons appliquer différentes requêtes à l’échantillon de données suivant :
1. Une requête simple utilisant la fonction SELECT
Commençons par une formule de requête simple dans Google Sheets. Nous voulons sélectionner et afficher une seule colonne à partir des données.
=QUERY(A:F,"SELECT A",1)
La formule ci-dessus applique la requête « SELECT A » à la table de données A:F.
Dans SQL, cette formule serait équivalente à la requête « SELECT Name FROM Dataset » (si les colonnes de référence A:F étaient nommées Dataset).
La clause SELECT indique à la fonction quelles colonnes du jeu de données principal prendre en compte lors de l’extraction des données. L’expression de la requête est écrite dans le langage de requête de l’API de visualisation Google (qui est assez similaire aux expressions de requête utilisées dans SQL).
Voici le résultat que la formule ci-dessus renvoie :
La formule a renvoyé la colonne ‘Name’ du jeu de données car c’est à cela que la colonne A fait référence.
De même, si vous voulez seulement voir la colonne ‘Address’, alors votre formule QUERY serait :
=QUERY(A:F,"SELECT D",1)
Utilisation de plages nommées dans la fonction QUERY
Vous pouvez également utiliser des plages nommées dans votre requête. Par exemple, vous pouvez donner à la plage de données A:F le nom ‘Dataset’ en sélectionnant les colonnes A à F et en tapant le nom ‘Dataset’ dans la zone ‘Nom’ (voir ci-dessous), puis en appuyant sur la touche Entrée :
Vous pouvez ensuite utiliser le nom de la plage dans la fonction QUERY comme suit :
=QUERY(Dataset,"SELECT D",1)
Note : La formule QUERY de Google Sheets ne renvoie que le tableau de données, mais sans la mise en forme.
Accédez à la feuille d’exemple en cliquant sur le lien suivant :
2. Utilisation de la fonction QUERY Google Sheets pour sélectionner plusieurs colonnes
Vous pouvez également utiliser la clause SELECT pour afficher plus d’une colonne. Par exemple, si vous souhaitez afficher à la fois les colonnes ‘Nom’ et ‘Adresse’, votre formule QUERY ressemblerait à ceci :
=QUERY(Dataset,"SELECT A,D",1)
Le symbole astérisque (*) peut être utilisé dans votre clause SELECT pour afficher toutes les colonnes. Par exemple, vous pouvez utiliser la clause SELECT pour afficher toutes les colonnes de votre jeu de données comme suit :
=QUERY(Dataset,"SELECT *",1)
3. Filtrer et extraire des données en utilisant SELECT avec la clause WHERE
Si vous souhaitez spécifier certaines conditions qu’une ligne doit satisfaire pour être affichée dans une requête, vous pouvez ajouter une clause WHERE à votre requête comme suit :
=QUERY(Dataset,"SELECT * WHERE B='Manufacturing'",1)
Lorsqu’elle est ajoutée à la clause SELECT, la clause WHERE vous permet de filtrer les données renvoyées en utilisant une ou plusieurs conditions. Par exemple, si vous souhaitez extraire uniquement les données des employés du département ‘Manufacturing’, utilisez la fonction QUERY comme suit :
=QUERY(Dataset,"SELECT * WHERE B='Manufacturing'",1)
Vous pouvez également utiliser des « opérateurs de comparaison » dans la clause WHERE. Par exemple, pour extraire des données pour les employés qui ont travaillé plus de 30 heures, vous pouvez écrire une requête comme suit :
=QUERY(Dataset,"SELECT * WHERE E>30",1)
Vous pouvez également utiliser la fonction QUERY avec les jokers Google Sheets si vous souhaitez que les conditions soient des caractères plutôt que des mots entiers.
Utiliser SELECT WHERE avec AND et OR
Ajoutez plusieurs critères de recherche à votre requête via des opérateurs logiques tels que OR et AND. Par exemple, vous pouvez étendre la recherche de la formule ci-dessus pour inclure tous les employés à la fois de ‘NY’ et ‘NJ’ comme suit :
=QUERY(Dataset,"SELECT * WHERE D contains 'NJ' or D contains 'NY'",1)
De même, pour insérer deux critères de recherche qui doivent tous deux être satisfaits pour qu’une ligne soit prise en compte, vous pouvez utiliser l’opérateur AND. Si vous souhaitez afficher les données des employés qui vivent dans ‘NJ’ et qui sont du département ‘Manufacturing’, vous pouvez utiliser la formule suivante :
=QUERY(Dataset,"SELECT * WHERE D contains 'NJ' and B='Manufacturing'",1)
4. Utilisation de la clause ORDER BY de QUERY Google Sheets
La clause ORDER BY est généralement utilisée en plus des clauses SELECT et WHERE. Elle permet de trier les données sélectionnées par ordre croissant ou décroissant. Vous pouvez spécifier la colonne par laquelle vous souhaitez trier vos données, ainsi que sélectionner un ordre croissant ou décroissant.
Par exemple, supposons que vous souhaitiez afficher les données de tous les employés du département ‘Manufacturing’ par ordre alphabétique par nom. Vous pouvez écrire votre formule QUERY comme suit :
=QUERY(Dataset,"SELECT * WHERE B='Manufacturing' ORDER BY A ASC",1)
De la même manière, pour afficher les données dans l’ordre décroissant des ‘Heures travaillées’, votre formule QUERY serait :
=QUERY(Dataset,"SELECT * WHERE B='Manufacturing' ORDER BY E DESC",1)
La clause LIMIT
Si vous avez un ensemble de données avec plusieurs points de données identiques, vous pouvez utiliser la clause LIMIT pour afficher un certain nombre d’entrées. Par exemple :
=QUERY(Dataset,"SELECT * WHERE B='Manufacturing' ORDER BY E DESC LIMIT 5",1)
5. Utilisation de QUERY pour regrouper des données sélectionnées à l’aide de la clause GROUP BY
La clause GROUP BY est l’un des concepts de requête les plus difficiles à maîtriser. La seule façon de maîtriser cette clause est de pratiquer. La clause GROUP BY vous permet de regrouper vos données en groupes, semblable à un tableau croisé dynamique.
La clause GROUP BY est généralement combinée avec des fonctions d’agrégation comme SUM, MAX et MIN, en plus des clauses SELECT et WHERE.
Par exemple, une application courante de cette fonction est de compter le nombre de lignes correspondant à une catégorie donnée. Ici, les lignes de données sont regroupées par catégorie, puis le nombre de lignes pour chaque groupe ou chaque catégorie est compté.
Supposons que vous souhaitiez regrouper vos données sur les employés par ‘Département’ afin de voir combien d’employés sont présents dans votre jeu de données pour chaque département. Dans ce cas, votre requête ressemblerait à ceci :
=QUERY(A1:F9,"SELECT B, COUNT(B) GROUP BY B",1)
Disons que je veuille afficher la colonne B (Département) et également regrouper la colonne par Département. J’ai également spécifié que pour chaque groupe de département, je veux afficher le nombre de lignes (en utilisant la fonction d’agrégation COUNT(B)).
De même, si j’affiche le nombre total d’heures travaillées par les employés dans chaque département, je pourrais utiliser la requête suivante :
=QUERY(A1:F9,"SELECT B,SUM(E) GROUP BY B")
Pour aller plus loin
Grâce à nos exemples étape par étape, vous devriez avoir une meilleure idée de la façon d’utiliser la requête Google Sheets pour extraire des informations à partir d’un ensemble de données donné. J’espère que ce tutoriel vous a été utile.
Si vous avez des questions ou des commentaires sur l’article que vous venez de lire, laissez-moi un commentaire !
