Editions ENI Livres | Vidéos | e-Formations
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 Microsoft 365 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és
du groupe Tableaux.
Excel analyse les données sélectionnées et vous propose quelques types de tableaux adaptés.
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...
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é dynamique 
du groupe Tableaux.
Vérifiez que la zone Sélectionner un tableau ou une plage contient
bien les données à analyser ; sinon, utilisez
le bouton 
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...
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 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. chapitre 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 puis cliquez sur le menu déroulant du bouton Tableau croisé dynamique.
Activez l’option À partir de External Data Source.
Cliquez sur le bouton Choisir et connexion de la boîte de dialogue 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 ventes par équipe...
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 Calculs de l’onglet Analyse du tableau croisé dynamique puis sur le bouton Champs, éléments et jeux.
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 pourcentage de 15 % sur les ventes.
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.
Pour supprimer un champ calculé, ouvrez la liste Nom de la boîte de dialogue Insertion d’un champ calculé (bouton Calculs - option Champs, éléments et jeux - bouton Champ calculé) pour...
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.
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.
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.
Puis cliquez...
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 ventes (zone Valeurs) par Equipe, Catégorie et par Région.
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.
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, NB pour les données de type texte), activez l’option Automatiques 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és 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...
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.
visible à droite de Étiquettes de colonnes indique
qu’un filtre est appliqué à ce champ. Le 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...
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 Equipe et Date.
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 Dates par année.
Activez, si besoin, l’onglet Analyse du tableau croisé dynamique 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.
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 Dates.
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 la faire glisser jusqu’à la période souhaitée.
Dans cet exemple, le tableau croisé dynamique affiche uniquement les valeurs dont la date est comprise entre le 1 et le 31 janvier 2020.
Le filtre chronologique...
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.
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...
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 vous 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 du tableau croisé dynamique 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.
Utiliser les idées d’analyse proposées par Excel
Cette fonctionnalité vous permet d’analyser vos données grâce à des modèles tendances et résumés visuels pertinents.
Sélectionnez une cellule dans la plage de données à analyser puis activez l’onglet Accueil et cliquez sur le bouton Analyser de données du groupe Analyses.
Le volet Analyse de données s’affiche à droite de la fenêtre.
Vous pouvez affiner vos idées en sélectionnant uniquement les champs que vous voulez consulter.
Dans le volet Analyse de données,
cliquez sur le bouton 
.
Dans la liste Inclure les champs, sélectionnez ceux qui doivent être utilisés et dans la liste Résumer la valeur par, définissez la fonction de calcul (Somme ou Moyenne).
L’option Pas une valeur correspond à des champs qui ne sont généralement pas calculés.
Dans notre exemple, l’analyse de données se réalise sur les champs Equipe, Région et Ventes résumées par les valeurs calculées à l’aide de la fonction Somme.
Cliquez sur le bouton Mettre à jour.
Cliquez sur le lien Insérer un tableau croisé dynamique, Insérer un graphique… en bas de la vignette correspondant à l’analyse...