Comment regrouper des dates par périodes dans un tableau croisé dynamique sur Excel

Souvent lorsque l’on analyse des données financières dans un tableau croisé dynamique, il peut être intéressant de pouvoir les regrouper par période. Si le regroupement en fonction de mois calendaires (du premier au dernier jour du mois) ne pose pas de problème, l’utilisation de périodes autres peut être plus problématique (par exemple du 25 au 24 du mois suivant).

 

 

Téléchargement

 

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

 

 

 

1. Présentation du cas

 

Pour illustrer ce tutoriel, nous disposons d’une base de données qui reprend le montant des ventes quotidiennes réalisées par un restaurant entre le 01 janvier 2016 et le 31 décembre 2018. Cela représente 3 années d’analyse, soit près de 1100 jours.

 

Il est donc essentiel de pouvoir regrouper les données en période mensuelles pour comparer les mois entre eux.

Excel formation - Regrouper les dates par périodes - 01

 

2. Regroupement par périodes calendaires

 

 

Pour analyser les données, nous allons commencer par créer un tableau croisé dynamique qui va nous permettre de de retraiter celles-ci, sans toucher à l’intégrité des données d’origines.

 

Pour cela, nous sélectionnons une cellule de la base de données, puis depuis le menu Insertion, nous sélectionnons le bouton Tableau croisé dynamique :

Excel formation - Regrouper les dates par périodes - 02

Excel affiche alors une nouvelle fenêtre :

 Excel formation - Regrouper les dates par périodes - 03

Nous vérifions tout d’abord que les cellules sélectionnées par défaut correspondent effectivement à l’ensemble des cellules qui composent la base de données d’origine (Feuil1 !$A$5:$B$1101).

Puis dans le champ « Choisissez l’emplacement de votre rapport de tableau croisé dynamique » nous choisissons d’insérer le tableau croisé dynamique directement dans la feuille de travail actuelle en cochant : Feuille de calcul existante. Dans la zone Emplacement, nous choisissons les coordonnées d’une feuille de la cellule (flèche bleue sur la capture ci-dessus).

Puis nous validons la création du tableau croisé dynamique en appuyant sur le bouton [OK].

Lorsque le tableau croisé dynamique est correctement ajouté, nous pouvons faire glisser les champs :

  • Le champs CA (ou le champ qui reprend les données que vous souhaitez analyser, du type heures de travail, quantités produites, kilomètres parcourus, …) dans la zone Valeurs

Excel formation - Regrouper les dates par périodes - 04

  • Le champs Date dans la zone Lignes :

Excel formation - Regrouper les dates par périodes - 05

 

Normalement, Excel devait nous mâcher le travail en procédant de lui-même au regroupement des dates par période :

 

Excel formation - Regrouper les dates par périodes - 06

Celles-ci sont regroupées par années. Si l’on appuie sur le bouton plus (« + ») devant l’année, celle-ci va être davantage détaillée, en présentant les dates sous la forme de trimestre, puis de mois :

Excel formation - Regrouper les dates par périodes - 07

Si vous ne souhaitez pas voir le regroupement par trimestre, il suffit de décocher l’option Trimestres dans la fenêtre de sélection des champs (à droite de la feuille de calculs).

Excel formation - Regrouper les dates par périodes - 08

Si le regroupement des dates n’a pas été généré automatiquement lors de la sélection des champs (ce qui peut être le cas si vous disposez d’une ancienne version d’Excel), rendez-vous dans le menu Analyse du ruban (qui ne s’affiche que lorsqu’une des cellules du tableau croisé dynamique est sélectionnée), puis cliquez sur le menu Grouper le champ (après avoir sélectionné une des dates du tableau croisé dynamique) :

Excel formation - Regrouper les dates par périodes - 09

Une fenêtre s’affiche alors afin de personnaliser le niveau de regroupement que nous souhaitons mettre en place :

Excel formation - Regrouper les dates par périodes - 10

 

3. Regroupement par périodes arbitraire

 

 

Si le regroupement des dates en fonction de période calendaires au sein d’un tableau croisé dynamique est très simple (voir même automatique dans certain cas), l’utilisation de périodes arbitraire est en revanche plus délicate.

 

Imaginons à présent que le magasin dont nous analysons les ventes dans notre exemple dépende directement d’une maison-mère qui exige de ce dernier une remontée des informations financière le 25 de chaque mois. Il va donc être nécessaire d’analyser les données non plus du premier au dernier jours de chaque mois, mais du 25 au 24 du mois suivant.

Pour permettre un tel regroupement, il va être nécessaire d’insérer une nouvelle colonne dans la base de données qui va se charger de calculer la période d’analyse.

Pour insérer une colonne, nous sélectionnons la colonne qui se trouve juste avant, puis nous effectuons un clic-droit, afin de sélectionner l’option Insérer :

Excel formation - Regrouper les dates par périodes - 11

 

Nous pouvons donner un titre à cette nouvelle colonne, par exemple « Période » :

 

Excel formation - Regrouper les dates par périodes - 12

Puis nous ajoutons la formule suivante dans la cellule située juste en dessous :

=DATE(ANNEE(A6);SI(JOUR(A6)<25;MOIS(A6)-1;MOIS(A6));25) 

Excel formation - Regrouper les dates par périodes - 13

Cette formule consiste à reconstituer la date de début de période en fonction de la date insérée dans la cellule A6. Pour cela nous partons du 25 du mois et de l’année en cours, et si le jour est inférieur à 25, alors nous retirons un mois.

De cette manière, la période sera identifiée à partir du premier jour de celle-ci. Pour l’identifier à partir du dernier jour, il suffit de rajouter un mois, et de prendre le 24 au lieu du 25.

Excel formation - Regrouper les dates par périodes - 14

Puis, nous pouvons étendre cette formule sur les cellules située en dessous, en utilisant la poignée de recopie située en bas à droite de la cellule :

Excel formation - Regrouper les dates par périodes - 15

De cette manière, nous obtenons la période correspondante pour chacune des dates de l’étude :

Excel formation - Regrouper les dates par périodes - 16

Ensuite, nous allons modifier les coordonnées des cellules sources du tableau croisé dynamique (Sélectionner une cellule du TCD > menu Analyse du ruban > Changer la source de données) :

Excel formation - Regrouper les dates par périodes - 17

Afin de prendre en compte la nouvelle colonne que nous venons d’ajouter :

Excel formation - Regrouper les dates par périodes - 18

Un nouveau champ apparaît alors dans la liste du tableau croisé dynamique portant le nom de Période :

Excel formation - Regrouper les dates par périodes - 19

Il nous suffit de glisser ce champ dans la zone Lignes :

Excel formation - Regrouper les dates par périodes - 20

Et voilà le tableau d’analyse, avec les ventes regroupées en fonction de périodes suivant une règle arbitraire :

Excel formation - Regrouper les dates par périodes - 21

 

4. Bonus

 

 

En bonus, voici la formule qui permet d’obtenir le début et la fin de la période d’analyse dans le tableau croisé dynamique :

 

=TEXTE(DATE(ANNEE(A6);SI(JOUR(A6)<25;MOIS(A6)-1;MOIS(A6));25);"jj/mm/aaaa")&"
 - "&TEXTE(DATE(ANNEE(A6);SI(JOUR(A6)<25;MOIS(A6)-1;MOIS(A6))+1;24);"jj/mm/aaaa")

Ce qui permet d’obtenir le résultat suivant :

Excel formation - Regrouper les dates par périodes - 22

 

Article initialement publié le 15 novembre 2018, mis à jour le 11 juin 2019



Articles qui pourraient vous intéresser

Comment contrôler les données saisies dans un tableau sans macro VBA sur Excel

Comment transformer un tableau en 2 dimensions (double entrée) et un tableau en 1 dimension (liste) sur Excel

Comment récupérer des informations depuis internet sur Excel et sans macro VBA (et créer un comparateur de prix)

Comment créer un sommaire automatique et généré en 1 clic sur Excel

Commentaires

Il n'y a aucun commentaire...

Poster un commentaire

Pseudo
Commentaire

Contact - Plan du site - À propos - Contact

© Excel-Formation 2018 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.