Avez-vous déjà créé un rapport de synthèse hebdomadaire dans Google Sheets, comme celui-ci ? Si ce n’est pas le cas, préparez-vous ! Vous pouvez apprendre ici comment créer un rapport de synthèse hebdomadaire dans Google Sheets.
Il ressemble à un résumé regroupé par semaine dans Google Sheets. Mais avec la fonction Tableau croisé dynamique de Google Sheets, vous ne pouvez pas générer un rapport comme celui-ci.
Ici, à des fins d’exemple, j’utilise un tableau des dépenses quotidiennes personnelles à trois colonnes. À partir de ce tableau, je veux trouver le résumé hebdomadaire de mes dépenses, également par description.
Si vous analysez attentivement le rapport ci-dessus, qui se remplit dans la plage E2: J8 (la plage s’ajuste automatiquement en fonction des données dans les colonnes A2: C), vous pouvez comprendre une chose. Il présente certaines similitudes avec le rapport du tableau croisé dynamique.
J’utiliserai la fonction Query de Google Sheets et sa fonction Pivot pour créer ce rapport de synthèse hebdomadaire.
Je suis sûr d’une chose. Certains d’entre vous sont peut-être familiers avec la création de rapports de synthèse mensuels dans Google Sheets.
Mais il est difficile de créer un résumé hebdomadaire dans Google Sheets. Voici cet exemple génial de formule.
Données d’exemple pour créer un rapport de synthèse hebdomadaire dans Google Sheets
Dans une feuille de calcul vierge, saisissez les données ci-dessous telles quelles. Elles se répartissent sur quelques lignes seulement, mais suffisent pour remplir un rapport de synthèse hebdomadaire dans Google Sheets.
Avant de passer aux étapes, essayez d’abord la formule principale ci-dessous dans votre feuille. Vous pourriez trouver la formule un peu compliquée, mais ce n’est pas le cas. Vous pouvez l’apprendre.
J’essaierai d’expliquer chaque partie de cette formule en détail afin que vous puissiez créer un rapport de synthèse hebdomadaire dans Google Sheets, sans soucis.
Formule de requête pour remplir le résumé hebdomadaire à partir de la colonne Date (Formule principale)
Tout d’abord, appliquez la formule suivante (pour résumer les données dans les cellules A2: C) dans la cellule E2 et observez le résultat. Ensuite, je vous expliquerai comment créer cette formule de requête pour résumer vos données par semaine.
={query({ArrayFormula(if(len(A2:A),(WEEKNUM(A2:A)),)),B2:C},"Select Col2,Sum(Col3) where Col1>0 group by Col2 Pivot Col1 Label Col2'Description'",0);transpose(query({ArrayFormula(if(len(A2:A),(WEEKNUM(A2:A)),)),B2:C},"Select Sum(Col3) where Col1>0 group by Col1 label Sum(Col3)'Grand Total'",0))}Vous avez seulement besoin de la partie en gras de la formule pour remplir le résumé hebdomadaire.
Le reste de la formule sert à remplir la ligne totale à la fin du résumé.
Vous pourriez vous demander pourquoi je vous dis cela, car je veux vous convaincre que la formule n’est pas aussi compliquée que vous pourriez le penser.
Pour vous faciliter ce tutoriel, j’ai créé une feuille de calcul d’exemple où vous pouvez voir les données d’exemple ci-dessus et la formule principale en action.
De plus, j’ai divisé la formule principale en formule_1, formule_2 et formule_3 comme décrit ci-dessous sous des sous-titres différents. Je les ai également entrées dans la feuille partagée pour vous permettre d’apprendre comment créer un rapport de synthèse hebdomadaire dans Google Sheets étape par étape.
Explication de la formule principale du rapport hebdomadaire à l’aide de la requête
À partir des données d’exemple ci-dessus, il est impossible de créer un rapport de synthèse hebdomadaire dans Google Sheets. Savez-vous pourquoi ?
La raison est l’entrée de date dans la colonne A. Tout d’abord, vous devez convertir les dates de la colonne A en semaines. Attendez ! Pas besoin de faire quoi que ce soit manuellement.
Notre formule principale s’en charge. Ici, c’est juste une explication de comment la formule principale y parvient.
Formule 1 : Conversion de la colonne de dates en numéros de semaine et conservation des autres colonnes (Données de requête)
Pour l’explication, nous pouvons diviser la formule principale qui crée un résumé hebdomadaire dans Google Sheets en trois parties. Ce sont la Formule_1, la Formule_2 et la Formule_3.
Voici l’explication de la première partie. J’ai appliqué cette partie de la formule séparément dans la cellule L2 de la feuille partagée pour vous.
={ArrayFormula(if(len(A2:A),(WEEKNUM(A2:A)),)),B2:C}Parcourez les explications ci-dessous.
La formule ARRAYFORMULA WEEKNUM pour convertir l’entrée de la colonne de dates en numéros de semaine
=ARRAYFORMULA(WEEKNUM(A2:A))Cette formule convertit les dates de la plage A2:A en numéros de semaine.
Chaque semaine de l’année a des numéros d’identification différents appelés numéros de semaine. Ici, la semaine commence le dimanche et se termine le samedi.
Si vous souhaitez un autre jour de début et de fin comme du lundi au dimanche, veuillez vérifier les fonctions de date dans mon guide des fonctions. Vous y verrez comment utiliser en détail la fonction WEEKNUM de Google Sheets.
Conservation des colonnes restantes dans la source
Une autre partie de la formule ci-dessus est la plage de cellules B2:C. Elle renvoie simplement la plage de colonnes référencées telle quelle.
=B2:CSur la capture d’écran, j’ai marqué les résultats individuels de la fonction WEEKNUM ci-dessus et de la référence de plage de cellules. De plus, vous pouvez voir que j’ai utilisé les fonctions ARRAYFORMULA, IF et LEN.
Nous devons utiliser ARRAYFORMULA avec WEEKNUM lorsque ce dernier est utilisé dans une plage. S’il est dans une seule cellule, ce n’est pas nécessaire.
Ensuite, l’utilisation de IF et LEN. Cette combinaison est la plus courante dans presque toutes les formules où des plages infinies (A2:A) sont utilisées. Elle exclura les lignes vides dans le résultat.
Si notre plage est A2:A13, vous pouvez éviter l’utilisation de cette combinaison. J’ai un tutoriel dédié sur l’utilisation combinée de IF et LEN ici. Si vous le souhaitez, vous pouvez le consulter.
Formule 2 : Tableau croisé dynamique des numéros de semaine pour créer un résumé hebdomadaire dans Sheets
Veuillez remonter et vérifier la formule principale pour la partie en gras.
Dans la feuille Google partagée, vous pouvez voir cette formule (partie en gras de la formule principale) saisie dans la cellule P2.
=query({ArrayFormula(if(len(A2:A),(WEEKNUM(A2:A)),)),B2:C},"Select Col2,Sum(Col3) where Col1>0 group by Col2 Pivot Col1 Label Col2'Description'",0)J’ai déjà expliqué une partie de cette formule, c’est-à-dire {ArrayFormula(if(len(A2:A),(WEEKNUM(A2:A)),)),B2:C}, ci-dessus, qui agit comme les DONNÉES dans Query.
Le reste est simple si vous savez comment utiliser la fonction Query dans Google Doc Spreadsheets. Elle génère le résumé hebdomadaire.
Oui ! C’est ma réponse à la question de savoir comment créer un rapport de synthèse hebdomadaire dans Google Sheets. Laissez-moi vous l’expliquer. Je sais que je devrais d’abord vous présenter la syntaxe de la fonction Query.
Syntaxe de la requête à des fins d’explication
QUERY(data, query, [headers])Si nous comparons la Formule 2 ci-dessus avec la syntaxe de Query, la partie ci-dessous correspond à nos « données » dans Query.
{ArrayFormula(if(len(A2:A),(WEEKNUM(A2:A)),)),B2:C}Nous l’avons déjà appris.
Le reste de la formule est la « requête ». Elle regroupe les données de la colonne 2 (description) et somme la colonne 3 (montant).
Dans « headers », j’ai mis 0 car nos données ne contiennent pas de ligne d’en-tête (voir la capture d’écran ci-dessus). De plus, j’ai utilisé la clause Pivot dans Query.
Vous pouvez utiliser la formule telle quelle avec n’importe quelles données en modifiant simplement les références de cellules. Si vous souhaitez apprendre cela en détail, veuillez consulter les deux tutoriels suivants.
- Apprendre la fonction Query avec des exemples dans Google Sheets.
- Comment utiliser la fonction QUERY similaire à un tableau croisé dynamique dans Google Sheets.
Le résultat serait le suivant.
Nous avons déjà créé un rapport de synthèse hebdomadaire dans Google Sheets. Ici, 1, 2, 3, 4 et 5 sont les numéros de semaine. Mais ce qui nous manque, c’est une ligne totale en bas de ces données.
Voyons comment l’obtenir.
Formule 3 : Colonne totale hebdomadaire à la fin du rapport de synthèse hebdomadaire dans Query
Comment créer une ligne totale à la fin du résumé ? Voici cette formule. Vous pouvez voir cette formule telle qu’elle est entrée dans la cellule V2 de la feuille partagée.
=transpose(query({ArrayFormula(if(len(A2:A),(WEEKNUM(A2:A)),)),B2:C},"Select Sum(Col3) where Col1>0 group by Col1 label Sum(Col3)'Grand Total'",0))Cette formule est celle qui ne figure pas en gras dans notre formule principale. Cette formule de Query alimente une ligne totale.
Elle contient tous les éléments de la Formule 2 ci-dessus, à l’exception de légères modifications dans la requête. C’est en fait une copie de la Formule 2.
Quels sont ces changements ?
Dans la formule 2, nous avons regroupé la colonne 2, c’est-à-dire la description. Nous avons ensuite utilisé la clause Pivot pour répartir le total du groupe par numéros de semaine.
Mais ici, le regroupement s’applique à la colonne 1 (numéro de semaine) car nous voulons la somme hebdomadaire et, bien sûr, pas de Pivot.
De plus, vous pouvez voir la fonction TRANSPOSER dans la formule car la formule Query de la Formule 3 renvoie le résumé (total) verticalement. Nous le voulons horizontalement.
Il n’y a pas de Formule 4. C’est notre formule finale. Cette formule est la combinaison des formules 2 et 3 ci-dessus qui sont simplement jointes par des accolades.
Ce qui précède est une formule avancée pour créer un rapport de synthèse hebdomadaire dans Google Sheets.
Avec l’aide des détails ci-dessus et en analysant la feuille Google partagée, j’espère que vous pourrez l’apprendre.
Si vous avez besoin d’aide concernant cette formule, veuillez laisser un commentaire ci-dessous. Amusez-vous bien !
