Tableaux croisés dynamiques

Choisir un tableau croisé dynamique recommandé

Un tableau croisé dynamique permet de résumer, d’analyser, d’explorer et de présenter des données de synthèse. Excel 2016 peut vous suggérer plusieurs tableaux croisés dynamiques particulièrement adaptés à vos données vous aidant ainsi à les synthétiser et les analyser.

Excel analyse les données sélectionnées et vous propose quelques types de tableaux adaptés.

Le bouton Tableau croisé dynamique vide situé en bas à gauche de la fenêtre permet comme son nom l’indique de ne pas tenir compte des suggestions d’Excel...

Créer un tableau croisé dynamique

Un tableau croisé dynamique permet de résumer, d’analyser, d’explorer et de présenter des données de synthèse.

Pour utiliser un des tableaux croisés dynamiques recommandés par Excel, veuillez vous reporter au titre Choisir un tableau croisé dynamique recommandé de ce chapitre. 

Notez que le bouton Choisir la connexion associé à l’option Utiliser une source de données externes permet de sélectionner des tables dans un modèle de données Excel (cf. Importation des données - Importer des données d’une base de données Access). 

Créer un tableau croisé dynamique basé sur plusieurs tables

Cette fonctionnalité permet de lister les champs de plusieurs tables dans le volet Champs de tableau croisé dynamique vous facilitant ainsi la création d’un seul et même tableau croisé dynamique.

Pour importer plusieurs tables dans une liste des champs de tableau croisé dynamique, vous pouvez les importer simultanément à partir d’une base de données relationnelle (cf. Importation de données - Importer des données d’une base de données Access), ou bien importer les tables une par une à partir de la même source de données ou de sources de données différentes, les ajouter à un modèle de données dans Excel, créer des relations, puis utiliser ce modèle de données comme base d’un tableau croisé dynamique.

Dans le cadre de cet ouvrage, nous utilisons l’exemple d’une importation de plusieurs tables d’une base de données Access avec lesquelles nous avons créé un modèle de données Excel.

Gérer les champs d’un tableau croisé dynamique

Insérer un champ calculé

Pour illustrer cette fonctionnalité, nous allons calculer le montant des cotisations augmenté de 3 %.

Les valeurs du champ calculé apparaissent dans le tableau, le nom du nouveau champ est visible dans la liste des champs. Notez également que, par défaut, la fonction SOMME a été appliquée automatiquement à ce champ numérique.

Modifier la fonction de synthèse ou le calcul personnalisé d’un champ

Rappelons que par défaut, Excel applique la fonction SOMME aux données numériques de la zone Valeurs et la fonction NB aux données de type texte.

La boîte de dialogue Paramètres des champs de valeurs s’ouvre.

Vous pouvez également accéder à cette boîte de dialogue par l’onglet Analyse - groupe Champ actif - bouton Paramètres de champs.

La zone Nom personnalisé contient le nom du champ actif ou le nom de la source lorsqu’aucun nom personnalisé n’a été défini, précédé du type de calcul effectué.

Utiliser les champs de totaux et de sous-totaux

Pour illustrer ce titre, nous allons utiliser un tableau croisé dynamique simple dans lequel sont calculées par défaut les sommes des cotisations (zone Valeurs) par Sport et par Ville.

Filtrer un tableau croisé dynamique

Pour filtrer les données d’un tableau croisé dynamique sur un ou plusieurs champs, vous pouvez utiliser un filtre ordinaire accessible à partir d’une liste déroulante ou bien utiliser les filtres segments appelés aussi « filtres slicers » qui offrent une représentation visuelle des données filtrées.

Le champ utilisé pour filtrer fait partie du tableau

 Cliquez sur le bouton situé à droite de Étiquettes de lignes ou Étiquettes de colonnes. 

Un menu apparaît ; la liste des valeurs du champ est visible, chaque valeur est cochée car affichée dans le tableau.

 Pour retirer une ou plusieurs valeurs du tableau, décochez l’option correspondante. Pour conserver une seule valeur, décochez l’option (Sélectionner tout) puis cochez la valeur à afficher.

 Cliquez sur OK.

Sur cet exemple, la colonne BAYONNE n’est plus visible dans le tableau. Le bouton visible à droite de Étiquettes de colonnes indique qu’un filtre est appliqué à ce champ. Ce symbole est visible aussi dans la liste des champs du tableau croisé dynamique.

 Pour annuler ce filtre, cliquez sur le bouton ou sur le symbole visible dans la liste des champs puis sur l’option Effacer le filtre de « champ ».

 Pour filtrer en fonction des valeurs du tableau croisé dynamique, cliquez sur le bouton puis sur l’option Filtres s’appliquant aux valeurs :

Cliquez alors sur l’option vous permettant de définir l’intervalle de valeurs...

Grouper des données d’un tableau croisé dynamique

Cette fonctionnalité vous permet de regrouper des valeurs d’un ou plusieurs champs à l’aide de regroupements par sélection ou par champ.

Depuis la version 2016, les données des champs de type Date et heure ajoutés en Colonnes ou en Lignes sont automatiquement regroupées en années, trimestres et mois. Vous pouvez dissocier ce regroupement pour réafficher les dates et appliquer le regroupement souhaité.

Prenons l’exemple d’un tableau croisé dynamique présenté par Sport et Date de compétition.

Regrouper par champ

 Pour synthétiser des éléments de type numérique ou date dans le rapport, cliquez sur une des étiquettes de lignes ou de colonnes correspondant au champ du tableau croisé dynamique que vous voulez regrouper.

Dans notre exemple, nous allons regrouper le champ Date de compétition par année.

 Activez, si besoin, l’onglet Analyse puis cliquez sur le bouton Groupe.

Cliquez sur l’option Grouper le champ.

L’option Grouper le champ serait grisée (inaccessible) si l’élément sélectionné n’était pas un nombre, une date ou une heure.

 Dans la boîte de dialogue Grouper, modifiez si besoin la première valeur à intégrer dans le regroupement...

Filtrer les dates de façon interactive (filtre chronologique)

Vous pouvez utiliser une chronologie pour afficher les données correspondant à différentes périodes.

Dans cet exemple nous avons appliqué un filtre chronologique sur le champ Date de compétition.

Si la barre de défilement ne contient pas de curseur, vous pouvez pointer l’extrémité de la barre centrale (bleue) puis faites-la glisser jusqu’à la période souhaitée.

Dans cet exemple, le tableau croisé dynamique affiche uniquement les valeurs dont la date de compétition est comprise entre le 1 et le 30 septembre 2017.

Modifier la disposition/la présentation d’un tableau croisé dynamique

Modifier le format d’un champ de ligne

 Pour modifier le format d’un champ de ligne, sélectionnez-le puis cliquez sur le bouton Paramètres de champs du groupe Champ actif de l’onglet Analyse.

Vous pouvez aussi faire un double clic sur le champ de ligne concerné en mode plan ou tabulaire.

 Activez l’onglet Disposition et impression.

 Pour afficher les éléments de champ sous forme hiérarchique, activez l’option Afficher les étiquettes d’éléments sous forme hiérarchique. Vous pouvez ensuite demander à afficher (ou masquer)...