Vous êtes-vous déjà demandé comment extraire des données spécifiques d’une feuille de calcul Google Sheets sans avoir à trier manuellement les informations ? Eh bien, la fonction QUERY de Google Sheets est là pour vous faciliter la tâche !
Les Clauses de la Fonction QUERY
Commençons par une introduction aux différentes clauses utilisées par la fonction QUERY. Ces clauses sont similaires à celles utilisées par SQL (Structured Query Language).
- Select – Sélectionne les colonnes à retourner et dans quel ordre. Si elle est omise, toutes les colonnes de la table sont retournées dans leur ordre par défaut.
- Where – Retourne uniquement les lignes correspondant à une condition. Si elle est omise, toutes les lignes sont renvoyées.
- Group by – Agrège les valeurs à travers les lignes.
- Pivot – Transforme les valeurs distinctes des colonnes en de nouvelles colonnes.
- Order by – Trie les lignes par valeurs dans les colonnes.
- Limit – Limite le nombre de lignes retournées.
- Offset – Saute un certain nombre de premières lignes.
- Label – Définit les libellés des colonnes.
- Format – Formate les valeurs de certaines colonnes en utilisant des motifs de formatage spécifiques.
- Options – Définit des options supplémentaires.
Ne vous inquiétez pas si vous avez des connaissances limitées ou aucune connaissance en SQL, ce n’est pas une exigence pour comprendre la fonction QUERY. Ce qui est important, c’est de respecter l’ordre dans lequel les clauses sont utilisées.
Vous n’êtes pas obligé d’utiliser toutes ces clauses à chaque fois que vous construisez une formule QUERY. La première clause SELECT est la seule clause que vous utiliserez probablement dans la plupart, sinon tous vos cas.
Si vous prévoyez d’utiliser la fonction QUERY dans votre flux de travail, je vous recommande d’étudier le Guide de référence du langage de requête pour approfondir votre compréhension des utilisations et des opérations de ces clauses.
Guide de référence du langage de requête
L’étude de ce guide de référence révélera de nombreuses fonctionnalités intéressantes, comme la possibilité d’intégrer des agrégations telles que SUM, AVERAGE, MAX, MIN et COUNT dans vos requêtes.
Vous pouvez également effectuer des fonctions scalaires telles que YEAR, MONTH et DAY, ainsi que des opérations arithmétiques (addition, soustraction, multiplication, division).
Écrire notre première requête
Nous avons nos données sur une autre feuille appelée « Data » à partir de la cellule A1.
La syntaxe de la fonction QUERY est la suivante :
Pointez vers les données
Notre premier argument est la source des données. Nous pouvons sélectionner la plage exacte occupée par nos données…
=QUERY(Data!A1:E121,
… mais cela ne permettrait pas l’inclusion de données supplémentaires. Nous allons plutôt sélectionner l’ensemble des colonnes.
=QUERY(Data!A:E,
Former la requête
C’est à ce moment que vous utilisez les clauses mentionnées ci-dessus. Les clauses de la requête doivent être comprises entre des guillemets.
Notre première requête va tout sélectionner. « Tout » peut être représenté par une astérisque (*).
=QUERY(Data!A:E, "select *",
Identifier les lignes d’en-tête
Le dernier argument définit le nombre de lignes (à partir de la première ligne) dans les données qui composent les lignes d’en-tête.
Si cet argument est omis ou défini sur -1, les en-têtes seront devinés en fonction du contenu.
Comme nos données ne comportent qu’une seule ligne d’en-tête, nous veillerons à ce que la fonction QUERY ne fasse pas une mauvaise interprétation de nos en-têtes. Nous utiliserons un « 1 » pour cet argument.
=QUERY(Data!A:E, "select *", 1)
Le résultat est le retour de toutes les colonnes et de toutes les lignes de données de la source de données.
Si nous voulons extraire des colonnes spécifiques à partir de données dans le même classeur, nous devons identifier les colonnes nécessaires par lettre de colonne (ex: A, B, C).
Si vous utilisez la fonction QUERY dans la fonction IMPORTRANGE pour importer des données depuis un autre classeur, nous devons identifier les colonnes nécessaires par numéro de colonne (ex: Col1, Col2, Col3).
Nous pouvons être créatifs en plaçant les identifiants de colonnes dans l’ordre souhaité, de cette façon nous ne sommes pas liés à l’ordre des colonnes dans les données sources.
=QUERY(Data!A:E, "select A, C, B, E", 1)
Effectuer des Agrégations de Requêtes
Dans nos données, nous avons une colonne appelée « Scénario » qui a trois valeurs possibles : « Réel », « Budget » et « Perspective1 ».
Supposons que nous souhaitons regrouper toutes les lignes pour chacun de ces trois scénarios et additionner les colonnes « Revenus » et « Trésorerie ». En substance, créer un rapport récapitulatif qui regroupe les scénarios.
Écrivons la formule suivante :
=QUERY(Data!A:E, "select A, sum(D), sum(E)")
Nous nous attendons à récupérer le contenu de la colonne « A » et à agréger les colonnes « D » et « E ». Malheureusement, les résultats ne sont pas ceux attendus.
Cela est dû au fait que nous retournons chaque ligne de la colonne « A » séparément. Il n’y a rien à agréger lorsque chaque ligne reste séparée.
La clé est de regrouper les lignes par scénario, combinant ainsi toutes les lignes « Réel », toutes les lignes « Budget » et toutes les lignes « Perspective1 ».
La clause qui est parfaite pour cette action de regroupement est (vous l’avez deviné) GROUP BY. Mettons à jour la formule pour inclure une clause GROUP BY.
=QUERY(Data!A:E, "select A, sum(D), sum(E) group by A")
Remarquez que nos valeurs ne sont pas formatées. Nous pourrions utiliser la clause FORMAT pour appliquer un formatage aux résultats, automatisant ainsi l’étape de formatage et le rendant potentiellement dynamique.
=QUERY(Data!A:E, "select A, sum(D), sum(E) group by A format sum(D) '$#,##0', sum(E) '$#,##0'")
Les codes de formatage utilisés sont presque identiques aux codes de formatage des nombres utilisés par la fonction de formatage des nombres de Sheets. Ceci peut également être utilisé pour formater les dates et les heures.
Gérer les Lignes Vides
Remarquez sur l’image ci-dessus, nous avons une ligne vide entre l’en-tête et les données. Cela est dû au fait que nous avons inclus toutes les lignes vides en-dessous de nos données. Ces lignes sont traitées comme un scénario agrégé de « rien, rien et rien ».
Pour nous assurer de ne pas inclure de lignes vides, nous allons filtrer les lignes vides à l’aide de la clause WHERE.
L’utilisation d’une « clause where » me fait penser à un homme très poilu qui distribue des cadeaux une fois par an. (blague idiote de l’année ? Peut-être.)
Nous voulons nous assurer de n’inclure que les lignes qui ont du contenu, c’est-à-dire les lignes qui ne sont pas vides (null).
Étant donné qu’une clause WHERE doit venir avant une clause GROUP BY, nous insérons l’instruction suivante dans notre formule existante.
=QUERY(Data!A:E, "select A, sum(D), sum(E) where A is not null group by A format sum(D) '$#,##0', sum(E) '$#,##0'")
Nous constatons que la ligne vide en-dessous des en-têtes a été supprimée.
Créer une Sortie Croisée
Supposons que nous souhaitons uniquement avoir la somme des « Revenus » par « Scénario », mais nous voulons également décomposer chaque « Scénario » en ses « Divisions » distinctes ?
C’est là que la clause PIVOT peut être utile. La clause PIVOT crée une série de colonnes distinctes pour lesquelles des agrégations peuvent être appliquées.
PIVOT vient après l’instruction GROUP BY, nous pouvons donc écrire une formule comme celle-ci :
=QUERY(Data!A:E, "select A, sum(D) where A is not null group by A pivot C format sum(D) '$#,##0'")
Trier les Résultats
Si vous avez besoin de trier les résultats, vous pouvez implémenter une clause ORDER BY.
Supposons que nous voulons produire une liste des « Divisions » agrégées par « Trésorerie » en utilisant la somme lorsque le « Scénario » est « Réel ».
=QUERY(Data!A:E, "select C, sum(E) where A = 'Réel' group by C format sum(E) '$#,##0'")
Les résultats sont exacts, mais nous aimerions que les « Divisions » soient classées par ordre décroissant en fonction de la somme de la « Trésorerie ». Ajoutez la clause ORDER BY suivante après la clause GROUP BY.
REMARQUE : L’ordre par défaut est croissant, mais comme nous souhaitons avoir les résultats dans l’ordre décroissant, nous plaçons le modificateur « desc » à la fin de la clause ORDER BY.
=QUERY(Data!A:E, "select C, sum(E) where A = 'Réel' group by C order by sum(E) desc format sum(E) '$#,##0'")
Personnaliser les En-têtes d’Agrégation
Il est probable que le fait d’avoir un en-tête nommé « sum Cash » ne soit pas souhaitable. Nous pouvons personnaliser les en-têtes à l’aide de la clause LABEL.
LABEL vient avant FORMAT, nous modifions donc la formule précédente comme suit.
=QUERY(Data!A:E, "select C, sum(E) where A = 'Réel' group by C order by sum(E) desc label sum(E) 'Total Cash' format sum(E) '$#,##0'")
Limiter les Résultats
Supposons que vous ne souhaitiez conserver que les 2 premières entrées en fonction des agrégations de « Trésorerie » : dans ce cas, « Productivité » et « Jeux » ?
C’est là que la clause LIMIT entre en jeu. LIMIT vient après ORDER BY, nous pouvons donc ajouter l’instruction suivante à notre requête.
=QUERY(Data!A:E, "select C, sum(E) where A = 'Réel' group by C order by sum(E) desc limit 2 label sum (E) 'Total Cash' format sum(E) '$#,##0'")
« Où puis-je en apprendre davantage ? »
Cet article n’est qu’un aperçu du fonctionnement de la fonction QUERY.
Si vous souhaitez en savoir plus sur la fonction QUERY et de nombreuses autres fonctions étonnantes de Google Sheets, consultez mon cours intitulé « Google Sheets – Le Cours Complet ».
Il y a tellement plus à faire avec la fonction QUERY. Laissez cet article faire office d’apéritif pour ce qui sera certainement un repas de quatre plats exceptionnel.
Classeur d’Exercice
N’hésitez pas à obtenir votre propre copie du fichier ICI.
