Blog ENI : Toute la veille numérique !
En raison d'une opération de maintenance, le site Editions ENI sera inaccessible le mardi 10 décembre, en début de journée. Nous vous invitons à anticiper vos achats. Nous nous excusons pour la gêne occasionnée
En raison d'une opération de maintenance, le site Editions ENI sera inaccessible le mardi 10 décembre, en début de journée. Nous vous invitons à anticiper vos achats. Nous nous excusons pour la gêne occasionnée

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 2021 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.

 Si la plage de cellules concernée par le tableau croisé dynamique contient des en-têtes de colonnes ou est déjà organisé en tableau de données, cliquez dans l’une des cellules ; sinon, sélectionnez la plage de cellules à traiter dans le tableau croisé dynamique.

 Assurez-vous que vos données ne contiennent pas de lignes vides.

 Activez l’onglet Insertion et cliquez sur le bouton Tableaux croisés recommandésimages/12RB292101.png du groupe Tableaux.

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

images/29RBXL16-01.png

 Cliquez dans la partie gauche de la fenêtre sur l’une des dispositions de tableaux croisés dynamiques pour en obtenir un aperçu dans la partie droite.

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 et de définir vous-même le tableau croisé dynamique.

 Si le tableau croisé dynamique proposé vous convient, cliquez sur le bouton OK.

La nouvelle feuille de calcul (nommée Feuiln) créée pour ce tableau croisé dynamique affiche également le volet Champs de tableau croisé dynamique à partir duquel vous pouvez modifier le tableau croisé dynamique.

images/12RB292102.png

Si vous souhaitez modifier le tableau croisé dynamique...

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.

 Si la plage de cellules concernée par le tableau croisé dynamique contient des en-têtes de colonnes ou est déjà organisé en tableau de données, cliquez dans une des cellules du tableau ; sinon, sélectionnez la plage de cellules à traiter dans le tableau croisé dynamique.

 Assurez-vous que vos données ne contiennent pas de lignes vides.

 Activez l’onglet Insertion et cliquez sur le bouton Tableau croisé dynamiqueimages/rIC-09.png du groupe Tableaux.

Notez que la liste associée au bouton Tableau croisé dynamique propose également l’option A partir d’une source de données externes. Ce point sera abordé dans le chapitre Importation de données - Importer des données d’une base de données Access) .

images/12RB292104.png

 Vérifiez que la zone Sélectionner un tableau ou une plage contient bien les données à analyser ; sinon, utilisez le bouton images/IC-005n.png pour les sélectionner.

 Choisissez de placer le tableau croisé dynamique sur une nouvelle feuille ou sur l’une des feuilles du classeur. Pour cela, activez l’option Nouvelle feuille de calcul dans le premier cas, ou l’option Feuille de calcul existante dans le second ; si vous avez choisi cette dernière option, utilisez le bouton images/IC-005n.png pour activer la première cellule destinatrice du rapport.

 Cliquez sur le bouton OK.

Les onglets contextuels Analyse du tableau croisé dynamique et Création...

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.

 Activez l’onglet Insertion, cliquez sur la liste du bouton Tableau croisé dynamique puis choisissez l’option A partir d’une source externe.

 Cliquez sur le bouton Choisir et connexion de la boîte de dialogue Tableau croisé dynamique à partir d’une source externe.

 Activez l’onglet Tables puis cliquez sur l’élément de votre choix.

Dans notre exemple, nous avons sélectionné l’option Tables dans le modèle de données de classeur. Notre modèle de données contient dix tables préalablement importées d’une base Access.

images/29RBXL16-08.png

 Cliquez sur le bouton Ouvrir.

 Choisissez ensuite de créer le nouveau tableau croisé...

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

Ajouter/supprimer un champ

 Cliquez avec le bouton droit sur le nom du champ dans la section Champs du volet Champs de tableau croisé dynamique puis sélectionnez l’option correspondant à la zone où vous souhaitez placer le champ :

Ajouter à la zone Filtre du rapport

Pour que les valeurs de ce champ soient proposées, sous forme de liste, comme critères de filtre du tableau croisé.

Ajouter aux étiquettes de lignes

Pour que les valeurs de ce champ deviennent les en-têtes de ligne du tableau.

Ajouter aux étiquettes de colonnes

Pour que les valeurs de ce champ deviennent les en-têtes de colonnes du tableau.

Ajouter àla zone Valeurs

Pour que les valeurs de ce champ soient utilisées pour remplir les différentes cellules du tableau croisé dynamique. Par défaut, Excel utilise la fonction SOMME pour résumer les données numériques, et la fonction NB pour les données de type texte. Notez qu’il est possible de modifier cette fonction de synthèse comme il est également possible d’effectuer un calcul personnalisé sur ces données (cf. Modifier la fonction de synthèse ou le calcul personnalisé d’un champ).

Ce tableau croisé dynamique permet d’afficher la somme des cotisations par sport et par ville. Les champs placés dans le tableau croisé dynamique apparaissent dans les Zones correspondantes du volet Champs de tableau croisé dynamique.

images/29RBXL19-07.png

Vous pouvez aussi faire glisser le champ dans la zone correspondante de la section Zones visible par défaut sous la liste des champs ou, si la disposition classique du tableau croisé est active (onglet Analyse du tableau croisé dynamique - bouton Options du tableau croisé dynamique...

Insérer un champ calculé

 Pour ajouter un champ calculé au tableau croisé dynamique, cliquez dans le tableau croisé pour activer les outils spécifiques, cliquez ensuite sur le bouton Champs, éléments et jeuxde l’onglet Analyse du tableau croisé dynamique - groupe Calculs.

 Cliquez sur l’option Champ calculé.

 Dans la boîte de dialogue Insertion d’un champ calculé, saisissez le Nom du nouveau champ calculé.

 Saisissez la Formule de votre choix ; pour y intégrer un des champs existants, sélectionnez le champ concerné dans la liste Champs puis cliquez sur le bouton Insérer un champ. Complétez la formule selon le calcul à effectuer.

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

images/29RBXL16-15.png

 Cliquez sur le bouton OK pour valider.

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.

images/29RBXL19-10.png

Pour supprimer un champ calculé, ouvrez la liste Nom de la boîte de dialogue Insertion d’un champ calculé (groupe Calculs - option Champs, éléments et jeux - option Champ calculé) pour le sélectionner, puis cliquez sur le bouton Supprimer. Vous pouvez également faire un clic droit sur l’étiquette du champ calculé puis cliquer sur l’option Supprimer « nom du champ » du menu contextuel.

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 Nombre aux données de type texte.

 Pour modifier la fonction de synthèse utilisée ou effectuer un calcul personnalisé pour traiter les données d’un champ de la zone Valeurs, cliquez sur le nom du champ concerné dans cette zone, puis activez l’option Paramètres des champs de valeurs.

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 du tableau croisé dynamique - groupe Champ actif - bouton Paramètres de champs.

images/29RBXL16-17.png

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é.

 Pour modifier la fonction de synthèse, activez l’onglet Synthèse des valeurs par puis, dans le cadre Résumer le champ de valeur par, cliquez sur la fonction de synthèse souhaitée.

 Pour utiliser un calcul personnalisé, activez l’onglet Afficher les valeurs, ouvrez la liste déroulante Afficher les valeurs.

images/29RBXL16-18.png

Puis cliquez sur le calcul personnalisé de votre choix :

  • % de … : pour afficher la valeur sous forme de pourcentage de la valeur de l’Elément de base dans le Champ de base.

  • Différence par rapport ou Différence en % par rapport : pour afficher la valeur en nombre ou en pourcentage, correspondant à la différence par rapport à la valeur de l’Elément de base dans le Champ de base.

  • Résultat cumulé par : pour afficher...

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.

images/29RBXL19-11.png

 Sélectionnez un des éléments du champ de ligne ou de colonne pour lequel vous souhaitez ajouter des sous-totaux ou totaux.

Dans le groupe Champ actif, cliquez sur le bouton Paramètres de champs (onglet Analyse du tableau croisé dynamique) pour afficher la boîte de dialogue Paramètres de champ.

images/29RBXL16-23.png

 Pour calculer le sous-total de l’étiquette de ligne ou de colonne sélectionnée à l’aide de la fonction de synthèse par défaut (SOMME pour les valeurs numériques, Nombre pour les données de type texte), activez l’option Automatique du cadre Sous-totaux.

 Pour calculer des sous-totaux à l’aide d’une fonction différente, ou bien calculer plusieurs types de sous-totaux, activez l’option Personnalisé puis choisissez la ou les fonctions à utiliser à l’aide de la touche Ctrl pour sélectionner plusieurs fonctions.

 Pour modifier le mode d’affichage des sous-totaux, activez l’onglet Disposition et impression puis activez si ce n’est déjà fait l’option Afficher les étiquettes d’éléments sous forme hiérarchique puis cochez l’option Afficher les sous-totaux au-dessus de chaque groupe avant de valider par OK.

Pour en savoir plus sur la présentation/disposition d’un tableau croisé dynamique, veuillez vous référer au titre Modifier la disposition/la présentation d’un tableau croisé dynamique un peu plus loin dans ce chapitre.

 Cliquez sur le bouton OK pour valider.

Ici, nous avons ajouté...

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 images/tab01.png 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.

images/29RBXL16-27.png

 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 images/tab02.png 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.
images/29RBXL16-28.png

 Pour annuler ce filtre, cliquez sur le bouton images/tab02.png 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 images/tab01.png puis sur l’option Filtres s’appliquant aux valeurs :

images/29RBXL16-29.png

Cliquez alors sur l’option vous permettant de définir l’intervalle de valeurs sur lesquelles vous souhaitez filtrer. Par exemple, pour filtrer les valeurs comprises entre 1000 et 1500, cliquez sur Entre, précisez les bornes de l’intervalle puis cliquez sur OK.

L’option Filtres...

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.

images/COR-IE-80.png

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 du tableau croisé dynamique - groupe Groupe puis cliquez sur le bouton Grouper le champ.

images/12RB292112.PNG

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 (nombre, date ou heure) dans la zone Début et la dernière dans la zone Fin.

Les valeurs proposées par défaut correspondent aux valeurs minimale et maximale du champ.

images/12RB292113.png

 Dans la zone Par, en fonction du type de regroupement, réalisez l’une des manipulations suivantes :

  • Pour un regroupement concernant des valeurs numériques, tapez la valeur représentant l’intervalle de chaque groupe.

  • Pour un 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.

 Cliquez dans le tableau croisé dynamique.

 Cliquez sur le bouton Insérer une chronologie du groupe Filtrer de l’onglet Analyse du tableau croisé dynamique.

 Dans la boîte de dialogue Insérer une chronologie, cochez le ou les champs de type date sur lesquels vous souhaitez appliquer le filtre chronologique, puis validez par OK.

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

images/COR-IE-86.png

 Pour modifier la périodicité appliquée à ce filtre, ouvrez la liste de la périodicité actuelle (MOIS dans notre exemple), puis sélectionnez l’option ANNÉES, TRIMESTRES, MOIS ou JOURS.

 Pour vous déplacer dans le temps, vous pouvez utiliser la barre de défilement horizontal située dans la partie inférieure de la fenêtre.

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.

images/COR-IE-87.png

Le filtre chronologique se gère comme un filtre segment (cf. Filtrer un tableau croisé dynamique - Filtrer un tableau croisé dynamique à l’aide d’un filtre segment « slicer »).

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

Modifier le format du tableau croisé dynamique

 Pour modifier le format du tableau croisé, activez l’onglet Création puis cliquez sur le bouton Disposition du rapport du groupe Disposition et enfin activez l’option :

  • Afficher sous forme compactée pour maintenir les champs de gauche dans une colonne tout en les mettant en retrait afin de montrer la relation d’imbrication avec les données connexes (format activé par défaut).

  • Afficher en mode Plan pour présenter les données dans le style de tableau croisé dynamique classique.

  • Afficher sous forme tabulaire pour présenter les données dans un format de tableau traditionnel.

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 du tableau croisé dynamique.

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

 Activez l’onglet Disposition et impression.

images/29RBXL16-43.png

 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) les étiquettes du champ suivant dans la même colonne au format compact en cochant l’option Afficher les étiquettes du champ suivant dans la même colonne (forme compactée).

 Pour afficher les éléments du champ au format tabulaire, activez l’option Afficher les étiquettes d’éléments sous forme de tableau.

 Pour intégrer un saut de page au moment...

Recalculer un tableau croisé dynamique

Lorsque la plage de cellules, source du tableau croisé dynamique, est modifiée, ces changements ne sont pas reportés dans le tableau : il faut le mettre à jour.

 Cliquez si besoin dans le tableau croisé dynamique.

 Activez l’onglet Analyse du tableau croisé dynamique puis cliquez sur la partie inférieure du bouton Actualiser du groupe Données.

 Cliquez sur l’option Actualiser pour mettre à jour les informations du classeur qui proviennent de la source de données ou l’option Actualiser tout pour mettre à jour toutes les informations qui proviennent de la source de données.

Vous pouvez également utiliser l’option Actualiser du menu contextuel du tableau croisé dynamique.

Supprimer un tableau croisé dynamique

 Cliquez à l’intérieur du tableau croisé dynamique.

 Activez, si besoin, l’onglet Analyse du tableau croisé dynamique.

 Cliquez sur le bouton Effacer du groupe Actions et sur l’option Effacer tout.