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.
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 affiche alors une nouvelle fenêtre :
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
- Le champs Date dans la zone Lignes :
Normalement, Excel devait nous mâcher le travail en procédant de lui-même au regroupement des dates par période :
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 :
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).
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) :
Une fenêtre s’affiche alors afin de personnaliser le niveau de regroupement que nous souhaitons mettre en place :
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 :
Nous pouvons donner un titre à cette nouvelle colonne, par exemple « Période » :
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)
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.
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 :
De cette manière, nous obtenons la période correspondante pour chacune des dates de l’étude :
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) :
Afin de prendre en compte la nouvelle colonne que nous venons d’ajouter :
Un nouveau champ apparaît alors dans la liste du tableau croisé dynamique portant le nom de Période :
Il nous suffit de glisser ce champ dans la zone Lignes :
Et voilà le tableau d’analyse, avec les ventes regroupées en fonction de périodes suivant une règle arbitraire :
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 :
Article initialement publié le 15 novembre 2018, mis à jour le 11 juin 2019