Parfois, il vous arrive d’avoir besoin de petites astuces pour faire fonctionner vos formules. Insérer une cellule vide entre deux totaux dans une ligne de total de requête dans Google Sheets est l’une de ces astuces.
Vous pouvez ajouter une rangée de total de requête horizontalement ou verticalement à une sortie de table de requête. Mes deux tutoriels précédents détaillent cela.
- Ajouter automatiquement un total à des données filtrées dans Google Sheets.
- Comment utiliser la fonction QUERY similaire à un tableau croisé dynamique dans Google Sheets.
Dans cet article, je vais vous partager une petite astuce que je n’ai pas abordée dans ces deux tutoriels. L’astuce consiste à inclure une cellule vide entre deux totaux dans une ligne de total de requête.
Cela peut ne pas être nécessaire tout le temps, mais cela peut s’avérer utile à certaines occasions (voir la capture d’écran exemple ci-dessous).
Cet article contient la formule, l’astuce de la formule et son explication.
Exemple d’insertion d’une cellule vide entre deux totaux dans une ligne de total de requête
Voici une partie des données de vente dans A1:D.
Puisque cet article contient des instructions étape par étape (vous obtiendrez la formule finale en deux étapes principales), veuillez entrer les mêmes données d’exemple dans votre feuille de calcul dans la même plage.
J’ai résumé la partie ci-dessus des données de vente (A1:D) en regroupant la colonne A (les articles « Road Base » et « Sand »).
Vous pouvez trouver le résumé dans la plage de cellules F1:I (vous obtiendrez la formule plus tard).
Dans le résumé, les colonnes de regroupement sont les colonnes B, C et D. Les colonnes B (Qté) et D (Montant) sont additionnées et la colonne C (Taux) est moyennée par requête.
Il est évident que nous ne voulons pas le total de la colonne moyenne dans la ligne de total dans le tableau de résumé. Donc, dans la ligne de total de requête, je veux sauter le total de la colonne moyenne C comme vous pouvez le voir dans l’image ci-dessus.
C’est là que l’importance d’insérer une cellule vide entre deux totaux dans une ligne de total de requête dans Google Sheets intervient.
Voyez la sortie de la requête dans la plage F1:I. Vous pouvez voir une cellule vide entre deux totaux dans la ligne de total de requête en cellule H4.
Voyons comment insérer une cellule vide entre deux totaux dans la ligne de total de requête comme précédemment dans Google Sheets.
Il y a deux étapes principales. La première consiste à regrouper et à agréger la requête, et la seconde consiste à créer la ligne de total de requête.
Résumé de la requête (Regroupement et Agrégation)
Pour le résumé selon mon explication ci-dessus, nous pouvons utiliser la formule de REQUÊTE ci-dessous qui contient les fonctions d’agrégation de requête SUM et AVG.
Formule de résumé de la requête :
=query(A1:D, "Select A, sum(B),avg(C),sum(D) where A is not null group by A label sum(B)'Quantity',avg(C)'Rate',sum(D)'Total'",1)
Explication de la formule (en bref) :
Nous voulons regrouper la colonne A. Donc, nous avons inclus la colonne A dans la clause SELECT.
"Select A,
Ensuite, nous devons inclure la clause GROUP BY pour regrouper cette colonne (toutes les colonnes de la clause SELECT doivent trouver leur place dans la clause GROUP BY).
group by A
Les colonnes B doivent être additionnées (SUM), C doit être moyennée (AVG) et à nouveau la colonne D doit être additionnée (SUM).
sum(B),avg(C),sum(D)
Les données de la requête sont A1:D et nous avons des données jusqu’à la ligne 10 (A1:D10) seulement.
J’ai donc inclus la clause WHERE suivante dans la formule de requête pour éviter les lignes vides dans la plage A1:D. Sinon, vous obtiendrez une ligne vide dans le tableau de résumé de la requête.
where A is not null
Cela signifie que si les données sont A1:D10, vous pouvez ignorer la clause ci-dessus dans la formule.
Enfin, la clause LABEL sert à personnaliser les étiquettes de la sortie du résumé de la requête. Vous pouvez l’ignorer ou l’utiliser.
label sum(B)'Quantity',avg(C)'Rate',sum(D)'Total'
Vous pouvez également aimer : Comprendre la Clause LABEL dans la Requête Google Sheets.
À l’étape suivante, vous apprendrez l’astuce géniale pour obtenir une cellule vide entre deux totaux dans une ligne de total de requête dans Google Sheets.
Nous devons maintenant utiliser une autre formule de requête pour obtenir la ligne de total.
Obtenir une Cellule (Colonne) Vide entre Deux Totaux dans une Ligne de Total de Requête
La ligne de total doit être compatible avec la formule de résumé ci-dessus. Donc, copions la même formule et modifions-la. Pour le moment, insérez la requête (formule) dans la cellule F4.
Supprimez l’élément A (colonne A) de la clause SELECT et également de la clause GROUP BY (supprimez la clause GROUP BY). C’est la première chose que nous devons faire.
=query(A1:D, "Select sum(B),avg(C),sum(D) where A is not null label sum(B)'Quantity',avg(C)'Rate',sum(D)'Total'",1)Nous allons ensuite combiner cette formule avec notre formule de requête précédente (formule de résumé).
Tout d’abord, voyons le résultat et quelques aperçus de ce que nous allons faire dans les prochaines étapes (surlignés).
Je vais commencer par l’astuce clé.
Pour obtenir une cellule vide, disons une colonne, entre deux totaux dans une ligne de total de requête, utilisez les « méthodes de division par zéro » avec la fonction d’agrégation correspondante en cours d’utilisation, ici AVG.
Dans la formule ci-dessus, remplacez avg(C) par avg(C)/0, à la fois dans la partie d’agrégation et la partie d’étiquetage.
Cela entraînerait une erreur DIV/0 ! dans la cellule de total correspondante et cela serait considéré comme une cellule vide dans la requête.
=query(A1:D, "Select sum(B),avg(C)/0,sum(D) where A is not null label sum(B)'Quantity',avg(C)/0'Rate',sum(D)'Total'",1)
Maintenant, supprimons les étiquettes. C’est assez simple.
Vous pouvez simplement supprimer les étiquettes (mais laissez les apostrophes de clause ouverte) de la clause LABEL.
=query(A1:D, "Select sum(B),avg(C)/0,sum(D) where A is not null label sum(B)'',avg(C)/0'',sum(D)''",1)Enfin, pour sauter la ligne de total d’une cellule vers la droite, utilisez la technique suivante.
Formule générique :
={"TOTAL",above_query_formula}Formule de ligne de total de requête :
={ "TOTAL", query( A1:D, "Select sum(B),avg(C)/0,sum(D) where A is not null label sum(B)'',avg(C)/0'',sum(D)''",1 ) }Vous obtiendrez le résultat suivant :
Combinons les deux formules ci-dessus (formule de résumé et formule de ligne de total de requête).
Formule générique :
{Query_Summary_Formula;Query_Total_Row_Formula}Formule finale :
={ query( A1:D, "Select A, sum(B),avg(C),sum(D) where A is not null group by A label sum(B)'Quantity',avg(C)'Rate',sum(D)'Total'",1 ); { "TOTAL", query( A1:D, "Select sum(B),avg(C)/0,sum(D) where A is not null label sum(B)'',avg(C)/0'',sum(D)''",1 ) } }De cette façon, vous pouvez insérer une cellule vide entre deux totaux dans une ligne de total de requête dans Google Sheets.
Merci pour votre attention. Profitez-en !
