Crédit image : Crawlan.com
Créer un rapport de résumé mensuel dans Google Sheets est une tâche facile. Il existe plusieurs options disponibles, telles que le tableau croisé dynamique, la fonction SUMIF et la fonction QUERY.
Dans Excel, il existe de nombreuses options. Nous pouvons utiliser des commandes de menu telles que « Données » > « Sous-total » et « Insertion » > « Tableau croisé dynamique » et utiliser des fonctions telles que SUMIF, SUMIFS, etc.
Dans Google Sheets également, vous pouvez adopter toutes ces méthodes, à l’exception de « Sous-total ». De plus, nous pouvons utiliser la fonction QUERY, la fonction la plus polyvalente dans Google Sheets.
Alors, dans ce tutoriel, apprenons comment créer un rapport de résumé mensuel dans Google Sheets à l’aide de la fonction QUERY.
Comment créer un rapport de résumé mensuel dans Google Sheets
Que faisons-nous avec ces données ?
Nous allons préparer un résumé mensuel de chaque produit en regroupant les colonnes de date et de produit.
Veuillez suivre les instructions étape par étape ci-dessous.
Étape 1 : Regrouper par mois
Syntaxe : QUERY(data, query, [headers])
La formule QUERY suivante peut être utilisée pour regrouper une colonne de dates par mois et additionner une autre colonne dans Google Sheets :
=QUERY(A1:F,"SELECT MONTH(A)+1, SUM(F) GROUP BY MONTH(A)",1)
La formule prend trois arguments :
- La plage de données à interroger, qui est
A1:Fdans ce cas. - L’instruction de requête, qui est
SELECT MONTH(A)+1, SUM(F) GROUP BY MONTH(A)entre guillemets doubles dans ce cas. - Le nombre de lignes d’en-tête, qui est
1dans ce cas.
L’instruction de requête sélectionne les mois des dates dans la colonne A et additionne les valeurs dans la colonne F, en regroupant les résultats par mois.
Le résultat du résumé par mois ci-dessus dans Google Sheets présente trois problèmes :
- Les numéros de mois sont « incorrects » car la fonction scalaire MONTH dans QUERY renvoie des numéros de mois sous forme d’entiers basés sur zéro. Janvier est affiché comme 0, février comme 1, et ainsi de suite.
- Il y a des lignes vides dans le résultat.
- Les libellés ne sont pas ordonnés.
Réglons d’abord ces problèmes.
La formule suivante ajustera les numéros de mois :
=QUERY(A1:F,"SELECT MONTH(A)+1, SUM(F) GROUP BY MONTH(A)+1",1)
Celle-ci supprimera les lignes vides :
=QUERY(A1:F,"SELECT MONTH(A)+1, SUM(F) WHERE A IS NOT NULL GROUP BY MONTH(A)+1",1)
Il ne reste plus qu’à rendre les libellés propres. Nous pouvons utiliser la clause LABEL pour cela :
=QUERY(A1:F,"SELECT MONTH(A)+1, SUM(F) WHERE A IS NOT NULL GROUP BY MONTH(A)+1 LABEL MONTH(A)+1 'MOIS', SUM(F) 'TOTAL'",1)
Nous avons créé un rapport de résumé mensuel de base dans Google Sheets. Il ne contient que deux colonnes : une colonne de mois et une colonne de total.
Étape 2 : Regrouper par mois et par produit
Pour regrouper par produit, incluez-le à la fois dans les clauses SELECT et GROUP BY. Voici la formule finale :
=QUERY(A1:F,"SELECT MONTH(A)+1, B, SUM(F) WHERE A IS NOT NULL GROUP BY MONTH(A)+1, B LABEL MONTH(A)+1 'MOIS', SUM(F) 'TOTAL'",1)
Nous avons créé un rapport de résumé mensuel en utilisant QUERY dans Google Sheets.
Compter, moyenne, min et max dans le résumé mensuel dans Google Sheets
Lorsque vous créez un rapport de résumé mensuel en utilisant la fonction QUERY, vous pouvez utiliser des fonctions d’agrégation autres que la fonction SUM.
Par exemple, vous pouvez utiliser les fonctions d’agrégation AVG, COUNT, MAX et MIN de QUERY pour calculer la moyenne, le nombre, la valeur maximale et la valeur minimale d’un groupe de données.
Par exemple, pour créer un résumé mensuel des ventes moyennes de produits, vous pouvez utiliser la formule suivante :
`=QUERY(A1:F, »SELECT MONTH(A)+1, B, AVG(F) WHERE A IS NOT NULL GROUP BY MONTH(A)+1, B LABEL MONTH(A)+1 ‘MOIS’, AVG(F) ‘TOTAL' »,1)
Conclusion
Lorsque vous remplacez QUERY data par des données importées, telles qu’une formule IMPORTRANGE, vous devez remplacer les identifiants de colonne dans l’instruction de query par des numéros de colonne.
Vous utiliseriez Col1 pour représenter la première colonne dans les données importées, Col2 pour la deuxième colonne, et ainsi de suite.
Voici un exemple de formule :
`=QUERY(IMPORTRANGE(« URL », »Sheet1!A1:F »), »SELECT MONTH(Col1)+1, Col2, AVG(Col6) WHERE Col1 IS NOT NULL GROUP BY MONTH(Col1)+1, Col2 LABEL MONTH(Col1)+1 ‘MOIS’, AVG(Col6) ‘TOTAL' »,1)
Nous avons créé un rapport de résumé mensuel qui utilise les numéros de mois pour le regroupement. Si vous souhaitez utiliser des noms de mois à la place, vous pouvez utiliser la fonction EOMONTH dans les données QUERY.
Ces tutoriels peuvent également vous être utiles :
- Google Sheets Query : Comment convertir le mois en nombre en nom de mois en texte
- Comment grouper les données par mois et par année dans Google Sheets
Maintenant que vous savez comment créer un résumé mensuel dans Google Sheets, vous pouvez facilement analyser vos données et obtenir des informations précieuses pour vos activités professionnelles.
L’article original a été publié sur Crawlan.com.
