Google Sheets : Comment utiliser la fonction QUERY?

La fonction QUERY de Google Sheets est une fonction très puissante et polyvalente. En effet, nous pouvez l’utiliser pour tous nos besoins de logique, de recherche, de comptage, de moyenne, de filtrage et de tri (et bien plus encore…).

Cette fonction nous permet de récupérer des informations spécifiques à partir d’un ensemble de données (BDD) via une instruction de requête. Cela permet en quelque sorte de récupérer des ensembles de résultats à partir d’une base de données à l’aide d’une requête. La syntaxe du langage de requête utilisé dans cette fonction est similaire au langage SQL.

Nous allons voir en détail dans cet article (et les vidéos associés) comment bien débuter avec la fonction QUERY et apprendre à maîtriser cette fonction puissante.

// 🎓 DÉCOUVREZ LES FORMATIONS SUR GOOGLE SHEETS 🎓//

Démonstration vidéo (1/2)

But de la fonction QUERY

La fonction Quere exécute sur toutes les données sélectionnées une requête écrite dans le langage de requête de l’API Google Visualization (similaire au langage SQL).

Fonctionnement de la fonction QUERY

Syntaxe

=QUERY(données; requête; en-têtes)

Tout d’abord, il faut initialiser la fonction avec « =QUERY ». Il faut ensuite définir les options suivantes :

• Données : plage de cellules sur laquelle effectuer la requête (base de données présente dans le fichier Sheets).
• Requête : définir la requête à exécuter, écrite dans le langage de requête de l’API Google Visualization.
• En-têtes [FACULTATIF] : nombre de lignes d’en-tête dans la partie supérieure des données. Si cette valeur est ignorée ou correspond à -1 (déduite en fonction du contenu des données).

Clauses et opérateurs

Le langage de requête utilisé dans la fonction QUERY de Google Sheets est un langage textuel similaire à SQL. La requête utilise notamment des clauses pour effectuer des actions.

Voici ci-dessous une liste des clauses possibles que nous pouvons utiliser pour effectuer une grande variété d’actions sur nos données.

Avec la fonction QUERY nous pouvons également utiliser un certain nombre d’opérateurs logiques pour définir des conditions dans la fonction.

Voici ci-dessous une liste d’opérateurs logiques à notre disposition :

Fonctions QUERY de base

Tout d’abord, pour comprendre comment la fonction est utilisée, je vais utiliser une base de données très simple avec des groupes d’âge comme vous pouvez le voir ci-dessous.

Dans tous les exemples à venir, je vais entrer la fonction dans la cellule F3.

Celle-ci sera également affichée dans la barre de fonction (en dessous de la barre d’outils de Sheets) et disponible en copie dans l’article au-dessus de l’image de démonstration.

Ex. 1 : afficher les données en fonction d’une condition

Pour commencer, voyons comment récupérer la liste des personnes (nom, âge, groupe) qui font partie du groupe « Adultes ».

=QUERY(B3:D12;"select * where D='Adulte'")

Avant de continuer, voyons certaines des bases de QUERY dans ce premier exemple. Notez que les données sélectionnées incluent bien les en-têtes ce qui est très utile pour garder une trace de la signification des résultats !

Après avoir sélectionné les données du tableau (B3:D12), la partie principale de la requête est entre guillemets en tant que commande de chaîne de texte.

J’ai utilisé deux des clauses répertoriées dans le tableau ci-dessus. « select » que nous pouvons traduire par « Sélectionner » et « where » qui signifie évidemment « Où ».

• Select : La commande « select » est utilisée pour renvoyer des colonnes – dire à la fonction de « Select * » signifie afficher toutes les colonnes (similaire à B, C, D dans ce cas). Il est évidemment possible d’afficher des colonnes spécifiques en remplacer « * » par « B » pour afficher uniquement la colonne « B » ou encore « B, D » pour afficher les colonnes B et D.
• Where : La clause « Where » (où) nous permet d’ajouter une condition. Dans ce cas, pour afficher uniquement les lignes du groupe « Adulte » il nous suffit de spécifier « D=’Adulte' » .

Littéralement, nous pouvons traduire la requête comme ceci : « Sélectionner (select) toutes les colonnes (*) où (where) la colonne D est égale à adulte (D=’Adulte’) ».

Ex. 2 : sélectionner des colonnes en fonction d’une condition

Maintenant voyons un exemple pour choisir uniquement une colonne en fonction d’une autre condition. A savoir « Afficher uniquement le nom des enfants qui font partis du groupe enfant ».

=QUERY(B3:D12;"Select B Where D='Enfant'")

Ex. 3 : Sélectionner plusieurs colonnes en fonction d’une condition

Il est également possible de choisir différentes colonnes et de les afficher dans un ordre spécifique. Dans ce cas, nous allons par exemple afficher le nom et l’âge des personnes qui ont plus de 15 ans.

=QUERY(B3:D12;"Select B, C Where C>15")

Information : Pour afficher l’âge avant le nom, il suffit de placer l’intitulé de la colonne C avant la B (C, B) dans la requête.

Ex. 4 : Conditions WHERE multiple

Il est évidemment possible d’appliquer plusieurs conditions WHERE dans une requête. Par exemple nous pouvons sélectionner le groupe Senior et les personnes qui ont plus de 61 ans (dans ce cas uniquement Nadia doit évidemment s’afficher).

=QUERY(B3:D12;"Select B,C Where D='Senior' AND C>61")

Démonstration vidéo (2/2)

Fonctions QUERY avancée

Pour les fonctions QUERY plus complexes, je vais utiliser le tableau de base de données ci-dessous. Un tableau avec des informations diverses sur une entreprise fictive.

Comme précédemment, je vais renseigner la fonction QUERY associée à l’exemple en question dans la cellule I3 dans ce cas.

Ex. 5 : Référencer les dates des cellules

Que faire si nous avons besoin de référencer la date d’une cellule ? Dans ce cas, nous pouvons le faire à l’aide d’opérateurs de concaténation et d’une fonction de texte.

En faisant cela comme dans l’exemple ci-dessous, nous pouvons obtenir les noms et les dates d’embauches des salariés qui se sont inscrits après le 1er janvier 2012.

=QUERY(B3:G14;"Select B,E Where E> date '"&TEXTE(K2;"yyyy-mm-dd")&"'")

Une chose importante à noter lors de l’utilisation des dates dans notre requête est que les dates ne peuvent être qu’au format « aaaa-mm-jj » et doivent être placées entre guillemets simples et précédées de la chaîne « date ».

Donc, pour indiquer le 1er février 2012 dans notre requête, nous devons taper : date « 2012-02-01 » (ou récupérer la valeur directement dans une cellule dans l’exemple ci-dessus).

Ex. 6 : fonctions arithmétiques simples (addition, soustraction, multiplication …)

Nous pouvons également utiliser des fonctions arithmétiques simples (addition, soustraction, multiplication).

Dans cet exemple, nous j’ajoute une formation supplémentaire à tous les salariés de l’entreprise.

=QUERY(B3:G14;"Select B, (F+1)")

Comme vous pouvez le voir, la sortie n’est pas très jolie, mais nous verrons en dessous comment modifier les en-têtes et le formatage plus loin dans ce guide. 😉

Ex. 7 : Moyenne

Maintenant, voyons une opération d’agrégation – la fonction moyenne. La moyenne d’une colonne est accessible avec l’abréviation « avg ». Nous devons également indiquer à la fonction avec quels éléments faire la moyenne, c’est-à-dire comment nous souhaitons regrouper les données.

Dans ce cas, il peut-être pertinent de faire la moyenne de l’âge des salariés (avg) par service (group by). Ce qui va nous donner la requête suivante :

=QUERY(B3:G14;"Select D, avg(C) group by D";1)

Nous en maintenant terminés avec les fonctions arithmétiques de base telles que la multiplication et l’addition, ainsi que des opérations d’agrégation telles que la moyenne avec la fonction QUERY. 🙂

Montons encore la complexité d’un cran avec les cas ci-dessous.

Ex. 8 : Sélectionner, Somme et Regrouper par

Nous pouvons utiliser une combinaison de Sélectionner (Select), Somme (Sum) et Regrouper par (Group By) pour répertorier tous les services et afficher le nombre de formations suivies dans chaque service.

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 fonctionnera pas.

Voici le résultat de cette requête ci-dessous :

=QUERY(B5:G16;"Select D, sum(F) group by D";1)

Ex. 9 : Étiqueter et trier

Dans l’exemple précédent, la fonction QUERY a renvoyé la deuxième colonne avec l’en-tête « sum Formations … ».

Ce qui n’est pas très « propre » comme en-tête… Heureusement, nous pouvons améliorer cela en renommant l’en-tête comme bon nous semble. 🙂

En plus de cela, nous pouvons trier les données par ordre croissant ou décroissant. Dans ce cas, je vais reprendre l’exemple précédent, mais en ajoutant une étiquette plus « parlante » (label sum(F) ‘Nombre de formations’ et en triant par nombre de formations décroissant (desc) – pour trier en croisant, il suffit de ne rien noter ou encore remplacer « desc » par « asc ».

=QUERY(B3:G14;"Select D, sum(F) group by D order by sum(F) desc label sum(F) 'Nombre de formations'";1)

Ex. 10 : Compter

Avec QUERY nous pouvons également compter le nombre de fois qu’une instance est présente dans notre tableau. Par exemple, dans ce cas, réaliser une requête qui compte et affiche le nombre de personnes par service :

=QUERY(B3:G14;"Select D, count(F) group by D label count(F) 'Nb pers. dans le service'";1)

Ex. 11 : Limiter et Trier par

La clause « Limit » permet de limiter les résultats à un nombre spécifique.

Par exemple, dans le cas si dessous je limite la sortie à 5 lignes (limit 5). Cette clause est souvent utilisée en conjonction avec la clause « Order by », qui classe les données dans l’ordre croissant ou décroissant.

=QUERY(B3:G14;"Select B,C order by B asc limit 5")

Ex. 12 : Pivoter

La clause Pivot nous permet essentiellement de créer nos 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 l’ai pivoté par la colonne B. Le résultat est que les noms des salariés sont maintenant les en-têtes, avec les données que j’ai sélectionnées dans les lignes sous chaque nom.

=QUERY(B3:G14;"Select D, sum(F) group by D pivot B")

C’est ici un exemple très simple avec la clause Pivot. Celle-ci permet d’aller beaucoup plus loin et d’afficher des données de toutes sortes de façon. Mais il faut évidemment passer un peu de temps à la manipuler pour la maîtriser complètement.

Ex. 13 : Utiliser OR dans une requête

Jusqu’à présent, nous avons vu de nombreuses requêtes qui utilisent « et » (AND) pour ajouter des critères supplémentaires à la requête. Mais nous pouvons également utiliser « ou » (OR), comme dans d’autres fonctions de Google Sheets telles que la fonction SI.

Dans cet exemple d’utilisation, je souhaite afficher les personnes qui on moins de 30 ans « OU » qui font partie du service « Marketing ». Cette clause permet ainsi d’afficher des données avec deux conditions qui sont différentes (dans ce cas l’âge est le service). Ce qui nous donne la requête suivante :

=QUERY(B3:G14;"Select B, C, D where C <=30 OR D = 'Marketing'")

Comme vous pouvez le voir dans la copie d’écran ci-dessus, la requête renvoie les données ou l’âge des personnes et inférieur ou égal à 30 ans ou les personnes qui font partie du service « Marketing » (c’est pourquoi Sonia est présente dans cette liste 😉 ).

Ex. 14 : Plusieurs lignes d’en-têtes

Jusqu’à présent, tous les exemples ont été réalisés avec une seule ligne d’en-têtes de la fonction QUERY. Maintenant, voyons ce qu’il faut faire si nous avons des en-têtes qui couvrent plusieurs lignes.

Le paramètre « header » est une entrée facultative qui s’avère pratique lorsque nos en-têtes s’étendent sur plusieurs lignes. Dans ce genre de cas, ce paramètre nous aide à combiner facilement les en-têtes sur une seule ligne. Dans le cas ci-dessous, j’ai rajouté une ligne d’en-tête avec des numéros. Le but étant d’ajouter ces numéros devant les intitulés d’entête utilisé tout au long des exercices précédents.

Voici ci-dessous la requête et le tableau correspondant :

=QUERY(B3:G15;"Select B, C, D where G <> 'Non'";2)

Tout ce que nous avons à faire dans ce cas est de mettre un « 2 » comme paramètre d’en-tête après la requête.

Quel que soit le nombre de lignes sur lesquelles s’étendent nos en-têtes, nous n’avons qu’à simplement préciser ce nombre dans le paramètre d’en-tête.

Ex. 15 : Interroger des données présentes dans une autre feuille

Même si les données que nous devez analyser sont présentes sur une (ou plusieurs) autre(s) feuille(s) de notre fichier nous pouvons toujours utiliser la fonction QUERY.

Pour cela, il faut simplement préciser dans la requête au niveau du champ données le nom de fa feuille puis la plage de cellule séparée par un point d’exclamation. Tout cela encapsulé dans des accolades. Comme ci-dessous :

=QUERY({Data1!B4:G15};"Select Col1, Col2, Col3")

Dans ce cas, mes données sont présentes dans un feuille qui se nomme « Data1 » et le tableau de données se trouve dans la plage de cellule « B4:G15 ».

Nouvelle exemple (MAJ – 11/11/21)

Ex. 16 : Query – Résultat à partir de la référence d’un cellule

Voici un nouveau que j’ai oublié d’intégrer (merci à Julien pour sa question sur le sujet 😉 ).

Il est également possible avec la fonction QUERY d’afficher le résultat d’une requête à partir d’une source externe qui peut varier. Soit pour faire simple depuis une référence spécifique d’une cellule de notre document Sheets.

Dans ce cas, je veux par exemple afficher uniquement les noms des personnes dont le service est spécifié dans la cellule « J4 » (dans ce cas « Communication »). Pour prendre en compte la référence d’une cellule dans la fonction Query, il faut l’encapsuler dans des apostrophes, des guillemets puis le symbole « & ».

Soit la formule suivante et l’aperçu en image ci-dessous :

=QUERY(B5:G17;"Select B where D='"&J4&"'")

L’idée intéressante ici est de pouvoir modifier rapidement le résultat à afficher en fonction de la cellule « J4 ». Pour aller plus loin il peut être intéressant de faire une liste déroulante avec la cellule « J4 » pour proposer la liste des services. Pour afficher automatiquement le nom des salariés qui font parties du service sélectionnés.

ARTICLE EN LIEN : Créer une liste déroulante avec la validation des données.

Pour conclure…

Comme vous pouvez le constater, QUERY est sans aucun doute l’une des fonctions les plus complexes à maîtriser avec Google Sheets. Mais croyez-moi, il est intéressant de passer un peu de temps à s’entraîner à l’utiliser, car elle permet d’analyser encore plus finement certaines de nos données présentes dans Google Sheets.

Vous pouvez évidemment retrouver d’autres exemples directement depuis la page spécifique sur la fonction sur le support officiel de Google.

Portez-vous bien et à bientôt!

---