Excel formation - Page de vente TCd - 11 Découvrez la méthode complète pour analyser les données efficacement et sans effort avec les tableaux croisés dynamiques, en cliquant ici

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

 

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 simplifier les formules avec l’automatisation des plages nommées d’Excel
Comment ajouter automatiquement une ligne lors d’un changement de valeur dans une colonne Excel
Comment tirer une lettre aléatoire pour créer un mot de passe incrackable – La fonction CAR() d’Excel
Comment créer une zone d’impression dynamique et intelligente sur Excel
Comment supprimer les lignes vides ou avec des cellules vides d’un tableau sur Excel
Comment géolocaliser une adresse sur Google Maps en 1 clic avec Excel
Comment calculer le taux de rendement interne d’un projet sur Excel (fonction TRI)
Comment envoyer la sélection par mail en 1 clic avec VBA sur Excel
Comment afficher plusieurs onglets d'un même classeur grâce au multi-fenêtrage d'Excel
Comment convertir des CSV en fichier Excel en masse (tous les fichiers d’un répertoire) avec VBA
6 Astuces pour envoyer facilement un fichier Excel par mail (sans VBA)
3 méthodes pour transformer un texte en majuscule (ou en minuscule) sur Excel