Je dois admettre que le titre « Retourner toutes les valeurs dans la clause GROUP BY Query » ne donne pas suffisamment d’idées sur ce tutoriel. Je vais tenter d’expliquer cela dans les deux ou trois paragraphes suivants, ainsi que dans la capture d’écran ci-dessous. Vous êtes nouveau dans Query ? Alors consultez d’abord ce guide – Apprendre la fonction Query avec des exemples dans Sheets.
La clause GROUP BY dans Query nous aide (utilisateurs de Google Sheets) à résumer les données dans Google Sheets. En d’autres termes, la clause GROUP BY dans Query nous permet d’agrégation des valeurs sur plusieurs lignes.
L’avantage de la clause GROUP BY est qu’elle peut accepter des conditions. De plus, comme mentionné précédemment, l’utilisation de fonctions d’agrégation est nécessaire en tant que partie de la clause GROUP BY.
Lorsque vous utilisez des conditions dans le regroupement Query et utilisez des fonctions d’agrégation, vous obtiendrez le résumé des éléments qui correspondent aux conditions. Mais je veux inclure les éléments non correspondants dans le résumé avec la valeur agrégée à 0. C’est ce que je veux dire par le titre ci-dessus « Retourner toutes les valeurs dans la clause GROUP BY Query dans Google Sheets ».
Tâche/Exemple : Grouper et totaliser les éléments dans la colonne B si les valeurs correspondantes dans la colonne C sont supérieures à 100.
Résultat attendu : Dans le résultat, je veux que la formule groupe et totalise selon la tâche précédente. Mais en plus, je veux que la formule retourne les éléments qui ne correspondent pas à la condition dans le résultat avec la valeur agrégée à 0.
Comment retourner toutes les valeurs dans la clause GROUP BY Query dans Google Sheets
Vous pouvez obtenir le résultat attendu ci-dessus avec une combinaison de trois formules – Deux formules Query + SORTN.
La fonction SORTN dans Google Doc Sheets peut être nouvelle pour beaucoup d’entre vous. Mais la connaissance préalable de l’utilisation de SORTN n’est pas nécessaire pour comprendre ce tutoriel.
Voici les instructions étape par étape pour retourner toutes les valeurs dans la clause GROUP BY Query dans Google Sheets. Dans cet exemple, j’utilise la fonction d’agrégation Sum. Vous pouvez utiliser Count, Max, Min, etc. selon vos besoins.
Lecture connexe : Comment additionner, faire la moyenne, compter, trouver le maximum et le minimum dans Query Google Sheets.
Étape 1 :
Voici la formule qui renvoie le résultat dans la plage F2: G5 dans la capture d’écran ci-dessus.
=query({B2:C8},"Select Col1, Sum(Col2) Where Col2 >100 Group By Col1 label Sum(Col2)''",0)By Col1 label Sum(Col2)''",0)
Cette formule ne renvoie que certaines valeurs de la colonne B car les autres valeurs ne correspondent pas aux conditions (la valeur doit être >100 dans la colonne C).
Étape 2 :
Avec une autre formule, nous pouvons résumer les valeurs qui ne correspondent pas à la condition ci-dessus.
=query({B2:C8},"Select Col1, 0/Sum(Col2) Where Col2 <=100 Group By Col1 label 0/Sum(Col2)''",0)
Résultat :
Banana 0 Grape 0 Peer 0
Dans cette Query, j’ai totalisé les éléments si les valeurs sont <=100. Mais pour obtenir 0 au lieu de la somme, j’ai opté pour la division de chaque somme de valeurs par 0.
Étape 3 :
Combinez les deux formules Query ci-dessus en utilisant les accolades. Vous obtiendrez le résultat attendu.
Vous pouvez aimer : Formule matricielle : Comment elle diffère dans Query Google Sheets et Excel.
Je veux dire que la formule ainsi combinée peut retourner toutes les valeurs dans la clause GROUP BY Query dans Google Sheets.
={query({B2:C8},"Select Col1, Sum(Col2) Where Col2 >100 Group By Col1 label Sum(Col2)''",0);query({B2:C8},"Select Col1, 0/Sum(Col2) Where Col2 <=100 Group By Col1 label 0/Sum(Col2)''",0)}
Certains d’entre vous peuvent se contenter de cette formule. Mais lors de l’utilisation de cette formule, certains d’entre vous peuvent rencontrer des problèmes avec vos données réelles. Vous pouvez souhaiter une meilleure précision.
C’est là qu’intervient l’utilisation de SORTN. Donc n’utilisez pas la formule ci-dessus comme formule finale. Continuez à lire pour obtenir celle-ci.
SORTN + Query pour supprimer les doublons
Note : Lorsque vous avez le temps, je vous suggère d’apprendre SORTN et ses modes de regroupement. Vous allez adorer cette fonction pour sa capacité à éliminer les doublons dans Google Sheets.
Voici un nouvel exemple de données.
Apple 150
Mango 150
Orange 150
Apple 150
Mango 50
Orange 150
Dans ces données d’exemple, vous obtiendrez la sortie suivante en utilisant la combinaison Query ci-dessus.
Apple 300
Mango 150
Orange 300
Mango 0
Il inclut toutes les valeurs. Mais la valeur « Mango » est en double, n’est-ce pas ? C’est là que entre en jeu la fonction SORTN.
SORTN(plage, [n], [mode_d’affichage_des_doublons], [colonne_de_triage], [est_ascending])
Utilisez la combinaison Query ci-dessus comme plage dans SORTN.
La formule finale serait la suivante.
=SORTN({query({B2:C7},"Select Col1, Sum(Col2) Where Col2 >100 Group By Col1 label Sum(Col2)''",0);query({B2:C7},"Select Col1, 0/Sum(Col2) Where Col2 <=100 Group By Col1 label 0/Sum(Col2)''",0)},9^9,2,1,1)
Mis à part la plage, la dernière partie de la formule 9^9,2,1,1) c’est-à-dire [n], [mode_d’affichage_des_doublons], [colonne_de_triage], [est_ascending] reste la même pour toute donnée à deux colonnes avec des doublons dans la première colonne.
Dans l’exemple ci-dessus, j’ai également utilisé des plages infinies (surlignées en vert).
C’est tout. Utilisez la formule ci-dessus pour retourner toutes les valeurs dans la clause GROUP BY Query dans Google Sheets. Amusez-vous bien !
