Qui n’a jamais eu besoin de créer un rapport hebdomadaire dans Google Sheets, comme celui-ci ? Si ce n’est pas le cas, préparez-vous ! Vous allez apprendre comment créer un rapport hebdomadaire dans Google Sheets.
Ce rapport ressemble à un résumé hebdomadaire regroupé par semaine dans Google Sheets. Mais avec les tableaux croisés dynamiques de Google Sheets, vous ne pouvez pas générer un rapport comme celui-ci.
Pour cet exemple, j’utilise une feuille de dépenses personnelles quotidiennes à trois colonnes. À partir de cette feuille, je souhaite obtenir un résumé hebdomadaire de mes dépenses, également par description.
Si vous analysez attentivement le rapport ci-dessus, qui est situé dans la plage E2:J8 (la plage s’ajuste automatiquement en fonction des données de la colonne A2:C), vous remarquerez une similitude avec le rapport du tableau croisé dynamique.
J’utiliserai la fonction Query de Google Sheets et sa fonctionnalité Pivot pour créer ce rapport 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 rapport hebdomadaire dans Google Sheets. Voici un exemple de cette formule impressionnante.
Données d’exemple pour créer un rapport hebdomadaire dans Google Sheets
Sur une feuille de calcul vierge, tapez les données ci-dessous. Elles s’étendent sur seulement quelques lignes, mais elles suffisent pour générer un rapport hebdomadaire dans Google Sheets.
Avant de passer aux étapes, essayez d’abord la formule maîtresse ci-dessous dans votre feuille. La formule peut sembler un peu compliquée, mais elle ne l’est pas tant que ça. Vous pouvez l’apprendre.
J’essaierai d’expliquer chaque partie de cette formule en détail afin que vous puissiez créer un rapport hebdomadaire dans Google Sheets sans tracas.
Formule de requête pour remplir le résumé hebdomadaire à partir de la colonne des dates (formule maîtresse)
Appliquez d’abord la formule suivante (pour résumer les données de 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 n’avez besoin que de la partie en gras de la formule pour générer 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. Parce que je veux vous convaincre que la formule n’est pas aussi compliquée que vous pourriez le penser.
Pour 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 maîtresse en action.
De plus, j’ai divisé la formule maîtresse en formule_1, formule_2 et formule_3, comme expliqué ci-dessous sous différents titres. J’ai également entré cela dans la feuille partagée pour vous permettre d’apprendre comment créer un rapport hebdomadaire dans Google Sheets étape par étape.
Rapport hebdomadaire en utilisant Query – Explication de la formule maîtresse
À partir des données d’exemple ci-dessus, il est impossible de créer un rapport 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 maîtresse s’en charge. Voici une explication de la façon dont la formule maîtresse procède.
Formule 1 : Conversion de la colonne de dates en numéros de semaine et conservation des autres colonnes (Données de la requête)
Pour expliquer, nous pouvons diviser la formule maîtresse qui crée un résumé hebdomadaire dans Google Sheets en trois parties. Ce sont formule_1, formule_2 et 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 ci-dessus.
={ArrayFormula(if(len(A2:A),(WEEKNUM(A2:A)),)),B2:C}Parcourez les explications ci-dessous.
La formule ARRAYFORMULA pour convertir 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 choisir un autre jour de début et de fin, comme du lundi au dimanche, veuillez consulter les fonctions de date de mon guide de fonctions. Vous y trouverez comment utiliser la fonction WEEKNUM de Google Sheets en détail.
Conservation des colonnes restantes dans la source
Une autre partie de la formule 1 ci-dessus est la plage de cellules B2:C. Elle renvoie simplement la plage de colonnes référencée telle quelle.
=B2:CSur la capture d’écran, j’ai marqué les résultats individuels de WEEKNUM et de la plage de cellules ci-dessus. 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 s’agit d’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ù les plages infinies (A2:A) sont utilisées. Elle exclut 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 : Pivot des numéros de semaine pour créer un résumé hebdomadaire dans Sheets
Veuillez remonter et vérifier la formule maîtresse pour la partie en gras.
Dans la feuille Google partagée, vous pouvez voir cette formule (partie en gras de la formule maîtresse) 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, à savoir {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 les feuilles de calcul Google. Elle génère le résumé hebdomadaire.
Oui ! C’est ma réponse à la question de savoir comment créer un rapport hebdomadaire dans Google Sheets. Permettez-moi de l’expliquer. Je sais que je devrais d’abord vous présenter la syntaxe de la fonction Query.
Syntaxe de Query à 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 est notre « data » 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 « query ». Elle regroupe la colonne de données 2 (description) et fait la somme de 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 quelle donnée en changeant simplement les références de cellules. Si vous souhaitez en savoir plus à ce sujet, veuillez consulter les deux tutoriels suivants :
- Apprenez la fonction Query avec des exemples dans Google Sheets.
- Comment utiliser la fonction QUERY de manière similaire à un tableau croisé dynamique dans Google Sheets.
Le résultat serait le suivant.
Nous avons déjà créé un résumé 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 y parvenir.
Formule 3 : Colonne totale hebdomadaire en bas du rapport hebdomadaire dans Query
Comment créer une ligne de total à 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 n’est pas en gras dans notre formule maîtresse. Cette formule de requête permet de remplir une ligne de total.
Elle contient tous les éléments de la formule 2 ci-dessus, à l’exception de quelques changements mineurs 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. Ensuite, nous avons 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 TRANSPOSE dans la formule car la formule Query dans la formule 3 renvoie le résumé (total) verticalement. Nous le voulons horizontalement.
Formule 4 : Rapport de synthèse hebdomadaire avec colonne de total général dans Sheets
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, simplement jointes par des accolades.
Ce qui précède est une formule avancée pour créer un rapport hebdomadaire dans Google Sheets.
Grâce aux informations ci-dessus et à l’analyse de 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. Profitez-en !
