Comment utiliser la fonction QUERY de Google Sheets ?

Les feuilles de calcul Google QUERY est un outil très puissant et polyvalent. À tel point que l'on peut dire qu'il s'agit d'un guichet unique pour tous vos besoins en matière de logique, de recherche, de sommation, de comptage, de calcul de moyenne, de filtrage et de tri. Cette fonction permet d'extraire des informations spécifiques d'un ensemble de données à l'aide d'une instruction de requête, de la même manière que l'on extrait des ensembles de résultats d'une base de données à l'aide de requêtes. La syntaxe de la fonction langage de requête utilisée dans cette fonction est similaire à SQL.

Syntaxe

• données - est la référence à la plage de cellules sur laquelle nous voulons effectuer une requête.
• interrogation - est le texte à l'aide duquel la fonction QUERY produit les informations que nous recherchons dans l'ensemble de données. Comme il s'agit d'une chaîne de caractères, elle doit être placée entre guillemets. Il peut également s'agir d'une référence à une cellule dans laquelle le texte de la requête est stocké.
• en-têtes - est un paramètre facultatif qui indique le nombre de lignes d'en-tête en haut des données. S'il n'est pas renseigné, Google Sheets devine la valeur en se basant sur le contenu de l'élément données.

Comment utiliser QUERY dans Google Sheets

Pour commencer avec la fonction QUERY, partons d'une requête simple qui sélectionne toutes les colonnes du jeu de données. Dans cet exemple, j'écris la fonction dans la feuille de calcul qui contient les données. Mais vous pouvez tout aussi facilement interroger une feuille d'un autre fichier. Pour en savoir plus, lisez cet article sur QUERY avec IMPORTRANGE dans Google Sheets.

1. Nommez la plage qui contient vos données pour y faire référence plus facilement.

2. Saisissez la fonction QUERY dans la cellule où vous voulez obtenir les résultats.

3. Sélectionnez la plage nommée ou les cellules contenant les données comme paramètre 'data' (données). Ici, je saisis simplement le nom de la plage.

4. Saisissez la requête que vous voulez utiliser. Ici, je sélectionne toutes les colonnes avec "SELECT *". Comme je la tape directement dans la formule, je dois mettre la requête entre guillemets.

5. Comme les en-têtes n'occupent qu'une ligne, j'ignore le dernier paramètre. Fermez la parenthèse et appuyez sur 'Entrée'.

Clauses et opérateurs

Le langage de requête utilisé dans Google Sheets La fonction QUERY est un langage textuel similaire à SQL. La requête utilise des clauses pour effectuer des actions. Vous trouverez ci-dessous une liste des clauses possibles que vous pouvez utiliser pour effectuer une grande variété d'actions sur vos données.

Si vous utilisez souvent la fonction QUERY, vous constaterez que vous utilisez également un certain nombre d'opérateurs logiques pour définir des conditions au sein de la fonction. Vous trouverez ci-dessous une liste des opérateurs logiques que vous pouvez utiliser.

Les mots-clés des clauses ne sont pas sensibles à la casse, mais les lettres de colonne doivent être en majuscules. Lorsqu'une requête utilise plusieurs clauses, elles doivent apparaître dans cet ordre : SELECT, WHERE, GROUP BY, PIVOT, ORDER BY, LIMIT, OFFSET, LABEL, FORMAT, OPTIONS.

Fonctions de base des requêtes

Tout d'abord, pour comprendre comment la fonction est utilisée, considérons l'exemple de données suivant. Il s'agit d'informations correspondant à une liste d'étudiants qui se sont inscrits à différents cours dans une université. Dans tous les exemples qui suivent, j'introduirai la fonction dans la cellule H1. Elle sera également affichée dans la barre de fonctions dans les instantanés.

Exemple #1 : Sélectionner des colonnes en fonction d'une condition

Je commencerai par une démonstration très basique, puis j'ajouterai des éléments plus complexes tout au long de l'article afin de vous aider à comprendre la fonction étape par étape. Pour commencer, j'utiliserai la fonction QUERY pour récupérer les noms des étudiants qui résident sur le campus.

Avant de poursuivre, je vais passer en revue certaines des bases de QUERY dans ce premier exemple. Pour commencer, remarquez que les données sélectionnées incluent les en-têtes - ceux-ci seront utilisés dans le cadre de la sortie de la fonction. Vous pouvez voir que la fonction renvoie non seulement les noms des étudiants résidant sur le campus, mais aussi l'en-tête Nom, ce qui est très utile pour garder une trace de la signification des résultats !

Après avoir sélectionné les données, la partie principale de la requête se trouve entre guillemets sous la forme d'une commande de chaîne de texte. J'ai utilisé deux des clauses énumérées dans le tableau ci-dessus : Select et Where. La commande ‘select’ est utilisée pour retourner des colonnes - dire à la fonction “Select A” conduit à une sortie de la colonne A entière.

La clause ‘where’ vous permet d'ajouter une condition. Ici, j'ai ajouté la condition que la colonne F soit égale à “Oui”. Pour les lignes dont la colonne F est “Non”, la fonction ignore ces données et ne les inclut pas dans le résultat.

En combinant les deux clauses en une seule requête, j'ai tapé “Select A where F = 'Yes'". Il s'agit essentiellement de demander à la fonction de renvoyer tous les noms (de la colonne A) des étudiants qui vivent sur le campus (les données de la colonne F sont 'Oui').

Conseil : si vous souhaitez sélectionner toutes les colonnes de l'ensemble de données, vous pouvez taper Select * pour y parvenir rapidement au lieu d'énumérer chaque colonne !

Exemple #2 : Sélectionner des colonnes en fonction d'une condition

Je vais maintenant montrer le même exemple, mais en trouvant les étudiants qui ne sont PAS sur le campus.

La seule différence est que j'ai changé l'opérateur logique dans la clause ‘where’ en <>, ce qui signifie “pas égal”. Je demande donc à la fonction de me donner la liste des noms des étudiants qui n'ont PAS de ‘oui’ dans la colonne F.

Exemple #3 : Sélectionner plusieurs colonnes

Maintenant, je vais chercher les noms, âges, départements des étudiants qui ont suivi plus de 7 cours.

Je commence à nouveau par la clause Select, mais cette fois, je sélectionne plusieurs colonnes en les énumérant et en les séparant par des virgules. J'ai choisi de sélectionner les colonnes A, B et C. J'ai également modifié la clause Where pour filtrer les données des étudiants ayant suivi moins de 7 cours.

Exemple #4 : Conditions multiples de type WHERE

Je vais maintenant aller plus loin. Je vais faire apparaître les noms, les départements et les dates d'inscription des étudiants âgés de 25 ans ou moins qui ont rejoint l'université entre le 25 décembre 2016 et le 20 janvier 2017. Veuillez noter que dans le texte de la requête, les dates doivent toujours être au format aaaa-mm-jj, entre guillemets simples.

Comme vous pouvez le constater, vous pouvez ajouter plusieurs conditions “Où” en les séparant simplement par "et". Cela vous permet de filtrer les données de toutes les manières possibles et imaginables !

Exemple #5 : Référencement des cellules

Que faire si vous avez besoin de référencer la date d'une cellule ? Aucun problème ! Vous pouvez le faire à l'aide des opérateurs de concaténation et d'une fonction de texte. En procédant ainsi dans l'exemple ci-dessous, vous obtiendrez les noms et les dates d'inscription des étudiants qui se sont inscrits après le 1er janvier 2017.

Une chose importante à noter lorsque vous utilisez des dates dans votre requête est que les dates ne peuvent être que dans le format aaaa-mm-jj, et doivent être placées entre guillemets simples et précédées de la chaîne de caractères “date”. Ainsi, pour indiquer le 1er janvier 2020 dans votre requête, vous devez taper : date ‘2020-01-01’.

Fonctions arithmétiques et d'agrégation

Outre les différentes clauses énumérées ci-dessus, vous pouvez utiliser des fonctions arithmétiques ordinaires dans vos requêtes. Les opérations telles que l'addition, la soustraction, la multiplication et la division, ainsi que les fonctions d'agrégation telles que la somme, la moyenne, le décompte, le maximum et le minimum sont toutes valables.

Exemple n° 6 : multiplication et addition

Je commencerai par démontrer quelques fonctions arithmétiques simples : la multiplication et l'addition. Dans cet exemple, je veux que l'âge soit exprimé en mois plutôt qu'en années (je multiplierai donc par 12) et je veux ajouter un crédit pour chaque élève.

Comme vous pouvez le constater, il vous suffit d'effectuer l'opération sur la colonne de votre choix. Le résultat n'est pas très joli, mais nous verrons comment modifier les en-têtes et le formatage plus loin dans ce guide.

Exemple #7 : Moyenne

Je vais maintenant vous montrer une opération d'agrégation - la fonction moyenne. La moyenne d'une colonne est accessible par l'abréviation ‘avg’. Vous devez également indiquer à la fonction les éléments dont il faut faire la moyenne, c'est-à-dire la manière dont vous souhaitez que les données soient regroupées. C'est pourquoi j'ai également demandé à la fonction de regrouper les données par la colonne C (dept), en utilisant la clause Group by.

Vous avez vu que la fonction QUERY utilise des fonctions arithmétiques de base telles que la multiplication et l'addition, ainsi que des opérations d'agrégation telles que la moyenne.

Fonctions d'interrogation complexes

Maintenant que vous avez vu quelques exemples de base utilisant les clauses Select et Where pour filtrer un ensemble de données, ainsi que quelques fonctions arithmétiques, je vais vous montrer des exemples plus complexes utilisant une variété de clauses et d'opérations.

Exemple #8 : Sélectionner, additionner et grouper par

Vous pouvez utiliser une combinaison de Select, Sum et Group By pour dresser la liste de tous les départements et afficher le nombre de cours suivis dans chaque département. La clause Group By est utilisée avec les fonctions d'agrégation (telles que Sum) pour indiquer à la fonction comment les données doivent être regroupées et additionnées - sinon la fonction d'agrégation ne fonctionne pas.

Exemple #9 : étiqueter et trier

Dans l'exemple précédent, vous remarquerez que la fonction QUERY a renvoyé la deuxième colonne avec l'en-tête “sum Courses”. Honnêtement, c'est un peu gênant - heureusement, vous pouvez y remédier en renommant la colonne. En outre, je vais également utiliser la deuxième colonne (rebaptisée ‘Cours suivis’) pour la trier par ordre croissant. Voici comment procéder.

Exemple #10 : Comptage

Pouvez-vous afficher le nombre de fois où un étudiant suit des cours dans chaque département ? Bien sûr ! La fonction QUERY vous couvre ici aussi - vous pouvez utiliser l'opérateur de comptage pour y parvenir.

Au lieu d'utiliser l'opérateur Sum pour additionner tous les cours suivis, j'utilise l'opérateur Count pour compter le nombre d'occurrences d'un étudiant prenant un nombre quelconque de crédits dans chaque département. J'utilise également à nouveau les clauses Select, Group by et Label pour obtenir les résultats souhaités.

Exemple #11 : Limiter et commander par

La clause Limit limite les résultats à un nombre spécifié. Par exemple, dans la capture d'écran ci-dessous, je limite la sortie à 10 lignes. Cette clause est souvent utilisée conjointement avec la clause Order by, qui place les données dans un ordre croissant ou décroissant.

Ici, j'ai utilisé la clause Order by pour trier les données de l'âge le plus bas à l'âge le plus élevé, puis j'ai utilisé la clause Limit pour limiter la sortie à 10 - ce qui me donne les 10 étudiants les plus jeunes.

Exemple #12 : Pivot

La clause Pivot vous permet essentiellement de créer vos propres tableaux croisés dynamiques à l'aide de la fonction QUERY. Dans l'exemple ci-dessous, j'ai pris un exemple précédent et je l'ai pivoté par la colonne A. Le résultat est que les noms des étudiants sont maintenant les en-têtes, avec les données que j'ai sélectionnées dans les lignes en dessous de chaque nom.

L'utilisation de la clause pivot est un usage un peu plus avancé des requêtes, mais si vous jouez avec, vous en découvrirez les nombreuses utilisations !

Utilisation de ‘OR’ dans les requêtes

Jusqu'à présent, vous avez vu de nombreuses requêtes qui utilisent ‘et’ pour ajouter des critères supplémentaires à la requête. Mais vous pouvez également utiliser ‘ou’, comme dans d'autres fonctions de Google Sheets, telles que les instructions IF.

Exemple #13 : Utilisation de OR dans une requête

Cet exemple est une réplique d'un exemple précédent, mais au lieu de spécifier que l'âge doit être de 25 ans ou moins ET que la date de jointure doit être comprise entre les dates spécifiées, je spécifie que tout point de données qui répond à L'UN OU L'AUTRE de ces critères, passera le filtre et apparaîtra dans la sortie.

En-têtes

Jusqu'à présent, tous les exemples concernaient une seule ligne d'en-têtes dans les données et un ‘1’ dans le paramètre d'en-tête de la fonction QUERY. Je vais maintenant vous montrer ce qu'il faut faire si les en-têtes s'étendent sur plusieurs lignes.

Exemple #14 : Plusieurs lignes d'en-têtes

Le en-tête est un paramètre facultatif qui s'avère utile lorsque vos en-têtes s'étendent sur plusieurs lignes. Dans ce cas, ce paramètre vous permet de combiner facilement les en-têtes en une seule ligne, comme illustré ci-dessous.

Il vous suffit de mettre un ‘2’ dans le champ en-tête après la requête ! Quel que soit le nombre de lignes sur lesquelles s'étendent vos en-têtes, il suffit d'indiquer ce nombre dans le paramètre en-tête paramètre - c'est aussi simple que cela !

Interroger plusieurs onglets ou feuilles

Que se passe-t-il lorsque vos données sont réparties sur plusieurs onglets de votre feuille de calcul, voire sur une feuille différente ? Vous pouvez toujours utiliser la fonction QUERY sur ces multiples onglets et feuilles !

Exemple #15 : Interrogation à travers des onglets ou des feuilles

Dans cet exemple, vous pouvez voir comment utiliser la fonction QUERY sur plusieurs onglets. L'essentiel est que les données soient présentées dans le même format dans toutes les feuilles - dans cet exemple, les âges doivent figurer dans la deuxième colonne des deux feuilles. Ensuite, le format change un peu - au lieu de désigner les colonnes par leur lettre (colonne A, B, etc.), vous devez les désigner par leur numéro (Col1, Col2, etc.).

Enfin, vous devez également entourer les plages de données d'accolades {} et les séparer par un point-virgule. Les différents onglets ou feuilles sont identifiés par le nom de la feuille suivi d'un point d'exclamation, puis par la plage de cellules de la feuille. Tout ceci prend tout son sens lorsque l'on regarde l'exemple ci-dessous !

Exemples de QUERY dans Google Sheets avec un jeu de données de ventes

Par souci de clarté, tous les exemples ci-dessous reposent sur le même jeu de données. Comme certaines clauses sont assez longues, je les saisis dans des cellules pour pouvoir utiliser les références de cellule dans la fonction. C'est plus simple, car vous n'avez pas à vous soucier des guillemets, et cela vous aide à éviter les erreurs.

Pour plus de cas concrets, lisez notre guide pour maîtriser la formule QUERY.

Utilisez SELECT pour des colonnes précises

Dans cet exemple, je veux que QUERY renvoie des colonnes précises plutôt que toutes. Après le mot-clé SELECT, saisissez en majuscules les lettres des colonnes voulues, séparées par des virgules.

1. Dans une cellule vide, saisissez la requête qui sélectionne les colonnes contenant le prénom, le nom et l'adresse e-mail.

2. Dans la cellule où vous voulez les résultats, saisissez la fonction QUERY et ajoutez la plage comme premier paramètre.

3. Sélectionnez la cellule contenant la requête comme deuxième paramètre.

4. Ignorez le troisième paramètre et fermez la parenthèse.

5. Appuyez sur "Entrée" pour voir les résultats.

Utilisez la clause WHERE pour filtrer par valeur

Dans cet exemple, je sélectionne le nom du client, le montant et l'agent commercial. Mais je ne veux que les enregistrements de l'agent 1 : je dois donc utiliser SELECT et WHERE. Si vous avez seulement besoin de filtrer des lignes, il existe une autre solution : Fonction FILTRE , une option plus simple.

1. Dans une cellule vide, saisissez les clauses SELECT et WHERE pour indiquer la condition à remplir. Ici, je veux filtrer les résultats sur les transactions de l'agent 1.

2. Dans la cellule où vous voulez les résultats, saisissez la fonction QUERY et ajoutez la plage comme premier paramètre.

3. Sélectionnez la cellule contenant la requête comme deuxième paramètre.

4. Ignorez le troisième paramètre et fermez la parenthèse.

5. Appuyez sur "Entrée" pour voir les résultats.

Utilisez la clause GROUP BY pour agréger les résultats

Dans cet exemple, je veux voir le total des ventes de chaque agent commercial. Autrement dit, je veux regrouper les transactions par agent et obtenir la somme pour chacun. Il faut pour cela les clauses SELECT et GROUP BY, ainsi que la fonction SUM.

1. Commencez par saisir la requête qui sélectionne avec SELECT les colonnes F et G. En réalité, je veux la somme de F : j'entoure donc la colonne F de la fonction SUM. Ajoutez la clause GROUP BY à la fin pour que les montants soient additionnés par agent commercial.

2. Dans la cellule où vous voulez les résultats, saisissez la fonction QUERY et ajoutez la plage comme premier paramètre.

3. Sélectionnez la cellule contenant la requête comme deuxième paramètre.

4. Ignorez le troisième paramètre et fermez la parenthèse.

5. Appuyez sur "Entrée" pour voir les résultats.

Utilisez la clause ORDER BY pour trier les résultats

Ajoutons une clause ORDER BY à la requête précédente pour trier les résultats par somme, dans l'ordre décroissant. Pour trier une plage sans écrire de requête, il existe une autre option : Fonction SORT.

1. Copiez la clause de la requête de l'exemple précédent et ajoutez la clause ORDER BY comme ci-dessous.

2. Dans la cellule où vous voulez les résultats, saisissez la fonction QUERY et ajoutez la plage comme premier paramètre.

3. Sélectionnez la cellule contenant la requête comme deuxième paramètre. Ignorez le troisième paramètre et fermez la parenthèse.

4. Appuyez sur "Entrée" pour voir les résultats.

Utilisez la clause LIMIT pour restreindre le nombre de résultats

Pour limiter le nombre de résultats renvoyés, vous pouvez utiliser la clause LIMIT suivie du nombre de résultats voulu. Par exemple, ajoutons une clause à la requête précédente pour limiter les résultats aux 2 meilleurs agents.

1. Copiez la requête de la section précédente et ajoutez la clause qui limite les résultats à 2.

2. Dans la cellule où vous voulez les résultats, saisissez la fonction QUERY et ajoutez la plage comme premier paramètre.

3. Sélectionnez la cellule contenant la requête comme deuxième paramètre. Ignorez le troisième paramètre et fermez la parenthèse.

4. Appuyez sur "Entrée" pour voir les résultats.

Utilisez la clause LABEL pour renommer les colonnes

Enfin, j'ajoute à la requête une autre clause qui renomme les colonnes renvoyées : LABEL.

1. Copiez la formule de la section précédente et ajoutez la clause qui renomme les colonnes "sum amount" et "sales_agent" en 'Total Sales' et 'Sales Agent'.

2. Dans la cellule où vous voulez les résultats, saisissez la fonction QUERY et ajoutez la plage comme premier paramètre.

3. Sélectionnez la cellule contenant la requête comme deuxième paramètre. Ignorez le troisième paramètre et fermez la parenthèse.

4. Appuyez sur "Entrée" pour voir les résultats.

Il s'agit sans aucun doute de l'une des fonctions les plus complexes à maîtriser dans Google Sheets. Cela vaut la peine d'y consacrer du temps, car il s'agit sans aucun doute de l'un des outils les plus puissants de Google Sheets. Nous vous encourageons à explorer plus avant la fonction QUERY fonction ici.

Ne manquez pas de consulter cet article de blog pertinent sur Comment créer des tableaux croisés dynamiques dans Google Sheets.