Comment utiliser la fonction QUERY de Google Sheets ?

Les feuilles de calcul Google REQUÊTE 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.

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.

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 #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

Les 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 !

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 REQUÊTE fonction ici.

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