Nous pouvons regrouper les données par mois et année dans Google Sheets de deux manières : en utilisant la Table Pivot ou la fonction QUERY. Ce tutoriel couvre les deux méthodes, mais je donne plus d’importance à la fonction QUERY.
Veuillez noter que le titre inclut à la fois le mois et l’année. J’ai intentionnellement inclus l’année dans le titre car regrouper les données par mois et année peut être utile dans certaines situations, par exemple lorsque vous avez des données à manipuler sur plusieurs années.
Regrouper les données par mois et année vous permet d’afficher le mois sous la forme Jan-2018, ce qui est plus informatif que de simplement afficher Janvier ou le numéro de mois 1 pour le premier mois de 2018.
Dans un rapport de vente, si les transactions de vente se produisent sur différentes années, regrouper les données par mois seul peut entraîner des résultats incorrects. Cela est dû au fait que le même mois peut se produire sur plusieurs années et que les données de ces mois seraient ajoutées ensemble.
Cependant, si toutes les transactions de vente se produisent la même année, alors regrouper par mois n’est pas un problème.
Voici donc mes conseils pour éviter de telles erreurs dans Google Sheets. Pour notre explication, je vais utiliser un exemple de rapport de consommation de carburant diesel.
Supposons que j’ai deux véhicules lourds et que j’ai enregistré la consommation de carburant diesel de ces véhicules par date. Je peux utiliser ces données pour créer un rapport récapitulatif par mois et par année.
Une fois que vous aurez appris cette technique de regroupement par mois et année dans Google Sheets, vous pourrez l’utiliser pour regrouper différents types d’ensembles de données, tels que des rapports de vente, des bons de commande et des rapprochements de matériaux.
Regrouper les données par mois et année dans Google Sheets avec les tables pivot
Nous utiliserons les données suivantes sur la consommation de carburant diesel pour notre exemple. Vous pouvez les saisir telles quelles ou les copier depuis ma feuille d’exemple à la fin de ce tutoriel.
Les données contiennent 13 numéros de véhicule dans la colonne A (non uniques), la date de remplissage de carburant dans la colonne B et la quantité en gallons dans la colonne C.
Voici les étapes pour créer un récapitulatif par mois et par année à l’aide d’une table pivot à partir de ces données dans Google Sheets :
- Sélectionnez la plage de données A1:C14.
- Allez dans le menu Insertion et cliquez sur Table Pivot.
- Dans la boîte de dialogue Table Pivot, sélectionnez l’option Feuille existante et entrez E1 dans le champ Emplacement. Cliquez sur Créer.
- Dans le volet de l’éditeur de table pivot, faites glisser le champ date_de_remplissage vers la zone Lignes.
- Faites glisser le champ numéro_de_véhicule vers la zone Colonnes.
- Faites glisser le champ quantité_en_gallon vers la zone Valeurs.
- Cliquez avec le bouton droit de la souris sur n’importe quelle valeur de la colonne E dans la table pivot, puis sélectionnez Créer un regroupement de dates pivot > Année-mois.
Et voilà !
Cela créera une table pivot qui résume les données par mois et par année.
Juste un rappel rapide : lors de la création d’une table pivot, assurez-vous de sélectionner la plage de données réelle, ici A1:C14.
Si vous sélectionnez l’ensemble des colonnes, ici A1:C, vous devrez peut-être filtrer la table pour supprimer les lignes vides. Pour ce faire, il suffit de se rendre à la section Filtres située sous Valeurs dans l’éditeur de table pivot et d’ajouter la (les) colonne(s) souhaitée(s) au filtre.
Regrouper les données par mois et année dans Google Sheets avec la fonction QUERY
Maintenant, voyons la formule pour regrouper les données par mois et année dans Google Sheets. L’explication de la formule suit juste après.
Formule principale :
=QUERY(HSTACK(A2:A14,ARRAYFORMULA(EOMONTH(B2:B14,0)),C2:C14),"SELECT Col2,SUM(Col3) GROUP BY Col2 PIVOT Col1 FORMAT Col2'MMM-YY'")
Notes :
- Dans la formule QUERY, les identifiants de colonne sont sensibles à la casse. Vous devez donc les spécifier en tant que Col au lieu de COL suivi du numéro de colonne.
- La formule ci-dessus est pour la plage A2:C14. Si vous l’utilisez pour la plage A2:C, vous devez utiliser la formule comme suit :
=QUERY(HSTACK(A2:A,ARRAYFORMULA(IFERROR(EOMONTH(DATEVALUE(B2:B),0))),C2:C),"SELECT Col2,SUM(Col3) WHERE Col1 IS NOT NULL GROUP BY Col2 PIVOT Col1 FORMAT Col2'MMM-YY'")
Explication de la formule : Regroupement par mois de données sur plusieurs années
Comme vous pouvez le voir ci-dessus, j’ai utilisé une formule QUERY à cette fin. J’ai précédemment partagé un tutoriel similaire sur la façon de faire la somme par mois dans Google Sheets. Il utilisait la fonction SOMME.SI, mais cela ne fonctionne que pour une année spécifique. Si les données s’étendent sur plusieurs années, la formule peut ne pas fonctionner.
J’ai également publié un autre tutoriel sur la création d’un récapitulatif par mois dans Google Sheets en utilisant la formule QUERY. Cependant, cette formule a également le même problème. Vous ne pouvez l’utiliser que pour les dates qui tombent dans une seule année.
Par conséquent, je voudrais attirer votre attention sur ma formule QUERY ci-dessus pour regrouper les données par mois et année dans Google Sheets. Cette formule fonctionne indépendamment du fait que les données s’étendent sur plusieurs années.
Bien que QUERY soit la fonction principale utilisée dans la formule, l’essence même de la formule réside dans la fonction EOMONTH. Je vais vous expliquer pourquoi.
Dans notre formule principale, au lieu d’utiliser la plage A2:C, j’ai utilisé une plage d’array personnalisée comme suit.
HSTACK(A2:A,ARRAYFORMULA(IFERROR(EOMONTH(DATEVALUE(B2:B),0))),C2:C)
Nous avons concaténé les plages A2:A, B2:B et C2:C en utilisant la fonction HSTACK. De plus, la plage B2:B a été remplacée par une formule de tableau, qui est la suivante.
ARRAYFORMULA(IFERROR(EOMONTH(DATEVALUE(B2:B),0)))
Si vous appliquez cette formule dans la première cellule d’une colonne vide, vous verrez qu’elle remplace les dates de la colonne B par les dates de fin de mois.
Par exemple, la date dans la cellule B2 est 1/1/2018. Cette formule convertit cette date en 31/1/2018.
Cela s’applique à toutes les dates de la colonne B. Consultez cette capture d’écran pour comprendre la conversion. Dans la cellule E2, j’ai appliqué la formule EOMONTH ci-dessus pour vous montrer le résultat.
Note : J’ai formaté la colonne E2:E en dates (Format > Nombre > Date).
L’avantage de convertir la date en date de fin de mois dans la formule Google Sheets :
Les avantages de cette conversion sont les suivants :
- Au lieu d’avoir des dates assorties dans un mois, nous avons des dates de fin de mois communes. Par exemple, n’importe quelle date en janvier 2018 sera convertie en 31/01/2018, n’importe quelle date en février 2018 sera convertie en 28/02/2018, et ainsi de suite.
- Cela nous permet de regrouper les données en fonction de ces dates de fin de mois, qui peuvent servir de regroupement par mois. Nous pouvons ensuite formater ces dates de fin de mois pour afficher le mois et l’année.
Si vous comprenez le concept d’EOMONTH ci-dessus, il vous sera facile d’apprendre le reste de la formule QUERY.
Dans la table pivot, nous avons utilisé la colonne date_de_remplissage (colonne 2) dans la section Lignes et le numéro_de_véhicule (colonne 1) dans la section Colonnes pour le regroupement. Veuillez remonter et voir la Figure 2.
L’équivalent dans la formule QUERY consiste à regrouper les données par colonne 2 et à les pivoter par colonne 1.
"SELECT Col2,SUM(Col3) WHERE Col1 IS NOT NULL GROUP BY Col2 PIVOT Col1 FORMAT Col2'MMM-YY'"
J’ai formaté les dates de fin de mois en mois et année en utilisant la clause de format dans Query.
De cette façon, vous pouvez utiliser la formule QUERY pour regrouper les données par mois et année dans Google Sheets.
Conclusion
La formule QUERY ci-dessus présente les particularités suivantes :
- Elle regroupe les données par mois et année, pas seulement par mois. Cela signifie que si une date tombe le même mois mais à différentes années, il y aura un total par mois individuel pour chaque année. Par exemple, 01/01/2017 et 01/01/2018 seront regroupés séparément en Jan-2017 et Jan-2018.
- Le récapitulatif conserve l’ordre des mois et des années. C’est l’un des grands avantages de cette formule de regroupement par mois et année. Si vous utilisez une autre formule pour regrouper par mois, où la mise en forme du texte est appliquée, elle ne conservera pas l’ordre des mois et des années. Elle peut être triée selon l’ordre alphabétique.
- Nous pouvons créer un graphique pivot à partir de la formule QUERY ci-dessus. Vous pouvez également utiliser un graphique pivot à partir de la table pivot, mais seulement avec une astuce.
