La fonction QUERY dans Google Sheets, expliquée
=QUERY(data; query; [headers]) filtre, trie et résume une plage à l'aide d'un court texte écrit dans le langage de requête de Google Visualization API. Il ressemble à une requête SQL : c'est le plus proche qu'on puisse obtenir d'un tableau croisé dynamique dans une seule formule.
QUERY n'existe que dans Google Sheets. Dans Excel, utilisez FILTRE (Anglais: FILTER), TRIER (Anglais: SORT), SOMME.SI.ENS (Anglais: SUMIFS) ou un tableau croisé dynamique pour les mêmes tâches.
Les trois arguments
data est la plage à interroger. Chaque colonne doit contenir un seul type de valeur : nombres, texte ou dates. Une colonne qui mélange les types est traitée selon son type majoritaire.
query est le texte de la requête, entre guillemets, ou une référence à une cellule qui le contient.
headers indique le nombre de lignes d'en-tête de la plage. Si vous l'omettez, Sheets devine, ce qui fait parfois traiter une ligne d'en-tête comme des données. Indiquez 1 si vous avez une seule ligne d'en-tête.
Select et where
Les colonnes sont désignées par leurs lettres dans la plage (A, B, C…). =QUERY(A1:D20;"select A, C where C > 100";1) renvoie les colonnes A et C pour les lignes où C dépasse 100. Les valeurs de texte vont entre apostrophes simples : =QUERY(A1:D20;"select A, C where B = 'East'";1).
Group by et sommes
Les fonctions d'agrégation sont sum, avg, count, min et max. =QUERY(A1:D20;"select B, sum(C) group by B";1) totalise la colonne C pour chaque valeur distincte de B : un SUMIF pour chaque région, en une seule formule. Toute colonne sélectionnée qui n'est pas agrégée doit figurer dans group by.
Order by, limit et label
=QUERY(A1:D20;"select B, sum(C) group by B order by sum(C) desc limit 3 label sum(C) 'Total'";1) trie les totaux du plus élevé au plus bas, ne garde que les trois premiers et renomme la colonne de somme en Total.
Pivot
Ajoutez pivot pour transformer les valeurs distinctes d'une colonne en colonnes : =QUERY(A1:D20;"select B, sum(C) group by B pivot D";1) affiche une colonne pour chaque valeur de D, comme un tableau croisé dynamique.
Erreurs courantes
Types de données mélangés dans une colonne. Une colonne contenant des nombres et du texte renvoie des cellules vides pour le type minoritaire.
Correction : Nettoyez la colonne pour que chaque cellule ait le même type avant de l'interroger.
Oublier les guillemets autour des valeurs de texte.
=QUERY(A1:D20;"select A where B = East")provoque une erreur.Correction : Mettez le texte entre apostrophes simples dans la requête : B = 'East'.
Omettre l'argument headers. Sheets devine et peut traiter la ligne d'en-tête comme des données.
Correction : Indiquez toujours le nombre de lignes d'en-tête, généralement 1.
Questions fréquemment posées
Quel est l'équivalent Excel de QUERY ?
Il n'y en a pas d'unique. FILTRE (Anglais: FILTER) avec TRIER (Anglais: SORT) couvre select et where, SOMME.SI.ENS (Anglais: SUMIFS) couvre les sommes par groupe, et un tableau croisé dynamique couvre pivot.
Faut-il connaître SQL ?
Non, mais cela aide : select, where, group by et order by fonctionnent à peu près comme leurs homonymes SQL. Le parcours SQL de ce site les enseigne.
Fonctions associées
- FILTERFILTER renvoie les lignes d'une plage qui remplissent une condition, et les fait se déverser dans les cellules situées en dessous.
- SORTSORT renvoie une copie triée d'une plage, qui se met à jour quand les données changent, par ordre croissant ou décroissant, selon n'importe quelle colonne.
- SUMIFSSUMIFS additionne les valeurs qui remplissent plusieurs conditions à la fois, comme les commandes payées d'une région.
- UNIQUEUNIQUE renvoie chaque valeur d'une liste une seule fois, en supprimant les doublons grâce à une formule qui se met à jour toute seule.