Excel : Fonctions de dates dans les tableaux croisés dynamiques

Publié le 03 décembre 2015
par Sophie Marchand M.Sc.
Tableaux croisés dynamiques Excel 2016

La version Excel 2016 offre de nouvelles possibilités aux utilisateurs de tableaux croisés dynamiques. L’une d’entre elles concerne le regroupement de dates. Cet article vise à expliquer en quoi consiste ces nouvelles possibilités. Mais avant d’entamer la lecture de cet article, vous voudrez sans doute relire l’article suivant Excel: Maîtriser les tableaux croisés dynamiques (1 de 5) qui explique en quoi consiste la fonctionnalité Grouper/Group dans les tableaux croisés dynamiques.

 

Base de donnée d’origine

Les tableaux croisés dynamiques de cet article ont été élaborés à partir de la base de données ci-dessous. Remarquez que la seule colonne de dates est la première colonne, qui contient les jours. On ne retrouve aucune autre colonne, comme les mois, les trimestres ou les années, par exemple.

Base de données

 

Regroupement de dates dans Excel 2016

Si vous utilisez Excel 2016 et que vous élaborez un tableau croisé dynamique à partir de la base de données ci-dessus, vous pourrez utiliser le champ Date et le placer dans les étiquettes de lignes (Rows), comme d’habitude. La nouveauté avec Excel 2016, c’est que vous voyez qu’immédiatement, Excel a reconnu ce champ de date et en a créé automatiquement une hiérarchie comprenant les années, les trimestres et les mois (le champ Date a été converti en mois). Dans les versions antérieures d’Excel, l’usager devait lui-même créer ses propres regroupements de dates.

Tableaux croisés dynamiques Excel 2016

 

Pour créer un regroupement de dates dans les versions antérieures d’Excel, il fallait placer le curseur sur un champ de date en étiquette de ligne dans le tableau croisé dynamique et appuyer sur le bouton de droite de la souris et choisir l’option Grouper/Group. Pour revoir cette technique, vous pouvez relire l’article Excel: Maîtriser les tableaux croisés dynamiques (1 de 5). Cette fonctionnalité n’est pas disparue dans Excel 2016 mais l’ajout d’une hiérarchie de dates se crée automatiquement. Cela n’empêche pas l’usager d’utiliser la fonctionnalité Grouper/Group pour modifier les éléments de la hiérarchie définie par défaut.

Tableaux croisés dynamiques Excel 2016

 

Dans notre exemple, Excel a éliminé les jours et a ajouté les champs Mois/Months, Trimestres/Quarters et Années/Years.

Tableaux croisés dynamiques Excel 2016

 

Rien n’empêche un usager de rajouter le champ Jour/Days, par le biais de ce menu, s’il le souhaite.

Tableaux croisés dynamiques Excel 2016

 

Ce champ deviendra accessible dans la liste de champs du tableau croisé dynamique, tel qu’illustré ci-dessous.

Tableaux croisés dynamiques Excel 2016

 

Base de données dans Excel vs dans Power Pivot

Dans l’exemple précédent, nos données logeaient dans un onglet d’un fichier Excel. Si nos données logeaient plutôt dans Power Pivot, la même fonctionnalité serait disponible mais avec quelques différences. Par exemple, vous pouvez constater sur l’image ci-dessous que le champ de jour n’a pas été éliminé d’emblée et que les champs additionnnels ont été nommés différemment (ils débutent tous par Date (…)).

Tableaux croisés dynamiques Excel 2016

 

Là aussi, si vous souhaitez ne pas afficher les jours dans votre tableau croisé dynamique, vous pouvez supprimer le champ de date de vos étiquettes de lignes de votre tableau croisé dynamique.

Tableaux croisés dynamiques Excel 2016

 

De même, vous pouvez bouger les champs pour afficher vos données différemment. Vous pourriez par exemple déplacer le champs Date (Year) dans les étiquettes de colonnes plutôt que les étiquettes de lignes pour voir les comparaisons des ventes année sur année, tel qu’illustré ci-dessous.

Tableaux croisés dynamiques Excel 2016

 

Options de forage supplémentaires

Je vous ai déjà parlé de l’option de forage dans les graphiques et dans les tableaux croisés dynamiques, qui devient disponible lorsque la base de données sous-jacente comprend des hiérarchies. À ce sujet, vous pouvez relire l’article Comment et pourquoi créer des hiérarchies dans Power Pivot. Les nouvelles options de groupement de dates d’Excel 2016 permettent également le forage des données puisque les champs de dates sont alors considérés comme une hiérarchie de dates. Ceci est vrai autant pour les tableaux croisés dynamiques élaborés à partir de données qui logent dans Excel que celles qui logent dans Power Pivot. Ainsi, vous pourriez présenter vos ventes par années dans un graphique croisé dynamique, tel qu’illustré ci-dessous…

Tableaux croisés dynamiques Excel 2016

 

… et ensuite, utiliser les options de forage pour voir les données à un niveau inférieur (par exemple, éclater les ventes de 2010 en ventes par mois).

Tableaux croisés dynamiques Excel 2016

 

Pour forer dans un graphique croisé dynamique qui contient des hiérarchies, il s’agit de cliquer sur l’axe de temps du graphique croisé dynamique (ou du tableau croisé dynamique lié), d’aller dans le menu Analyse/Analyze et de cliquer sur les boutons d’expansion et de regroupement (Expand Field et Collapse Field).

Tableaux croisés dynamiques Excel 2016

 

Un fait intéressant à noter est que les champs de mois ajoutés par la fonctionnalité de regroupement de dates d’Excel 2016 sont classés en ordre chronologique et non en ordre alphabétique, comme c’est le cas lorsque le modèle de données (créé dans Power Pivot) contient une table de dates avec une colonne de mois. À ce sujet, vous pouvez relire l’article Convertir des segments de mois d’un ordre alphabétique à un ordre chronologique. Vous constaterez que sans cette nouvelle fonctionnalité, il faut ajouter une colonne dans la table concernée du modèle de données pour trier les mois en ordre chronologique.

Tableaux croisés dynamiques Excel 2016

 

CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

8 réflexions sur “Excel : Fonctions de dates dans les tableaux croisés dynamiques”

  1. Bonjour,
    Est-il possible de modifier les paramètres du TCD afin que cette nouvelle fonctionnalité n’apparaissent plus ?
    En effet, cela me bloque plus qu’autre chose dans la selection de mes données…
    Merci d’avance

  2. Ping : Excel - MS Office 2013 / 2016 / 365 | Pearltrees

  3. Bonjour,

    Lorsque j’ajoute une colonne dans ma base de données qui reprends la date mais sous forme Lun, Mar, … j’ai dans mon tcd de nouveau une date sous forme 02-janv …
    Hors j’aimerais pouvoir effectuer un tri par jour sur l’ensemble de la base.
    Ex : quels ont été les événements répétitifs du lundi ou du mercredi. Hors ici, j’ai actuellement besoin de savoir que le 02-janv était un mardi en regardant le calendrier

    Je travaille en excel 2016

    BaV

    1. Bonjour Fabien,

      J’aimerais vous aider mais je ne comprends pas votre problématique. Je vous invite à poser votre question sur notre forum et à y insérer votre fichier, tout en ajoutant le plus de précision possible.

      Merci.

      Sophie

  4. Bonjour @Sophie Marchand,
    Je crois avoir le même problème que @Fabien. Mes dates dans mon TCD sont dans un format différent de la source.
    J’un TCD qui reprend en ligne des dates et fait la synthèse des opérations par jour. Dans mon tableau source, les dates sont au format personnalisé jjj jj/mm/aaaa. Dans le TCD ces mêmes dates mises en ligne sont au format jj/mm/aaaa. Je n’arrive donc pas à voir le jour de la semaine (lun, mar, etc) comme je le veux. Je n’ai pas l’option format de nombre dans les paramètres de champs de ce champs. Pour avoir le format que je veux, je suis obligé chaque fois de passer par le Format de cellules. Ce qui a pour limite de n’être valable que sur les valeurs déjà affichées. Lors que je mets à jour le TCD et qu’il y a des nouvelles dates saisies, cellesi-ci sont au format jj/mm/aaaa. Seules les anciens au format jjj jj/mm/aaaa restent dans la bonne mise en forme. Est-ce une limite d’Excel 2019 ou est-ce fait volontairement. Si oui, pourquoi cette limite?
    NB: J’ai dissocié (dégroupé) les dates.

  5. Frédéric Maillet

    Bonjour Karine,
    Je vous remercie infiniment pour le temps précieux que vous m’avez fait gagné.
    Vous m’avez apporté la solution que je recherchais en une phrase alors que j’avais cherché par moi-même pendant une heure.
    Cordialement,
    Frédéric.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut