Editions ENI Livres | Vidéos | e-Formations
Les tableaux croisés dynamiques
Introduction
Préliminaires
Dans les chapitres précédents, vous avez pu mettre en pratique des techniques variées pour obtenir des statistiques consolidées à partir de bases de données d’origines diverses. Ces techniques (sous-totaux, tables de données, calculs multicritères…) nécessitent de maîtriser les fonctions de calcul d’Excel mais ne vous imposent pas de limites dans la conception et la mise en forme de vos tableaux. Une autre fonctionnalité d’Excel à connaître est la technique de conception des tableaux croisés dynamiques.
Nous aborderons dans ce livre la conception de tableaux croisés dynamiques simples, un ouvrage entier étant consacré aux tableaux croisés dynamiques dans la même collection.
Comme pour les chapitres précédents, les exemples sources du livre sont téléchargeables depuis la page Informations générales. Ceci vous évitera d’avoir à saisir toutes les données et vous permettra de contrôler vos résultats. Les bases de données proposées sont variées et vont vous permettre de mettre en œuvre les techniques de conception de tableaux croisés dynamiques dans de nombreux domaines.
Si vous utilisez déjà les tableaux croisés dynamiques dans une version précédente...
Les tableaux croisés dynamiques
Définitions
|
Base de données |
Ensemble d’informations structurées sauvegardées sur un disque. Cet ensemble d’information est consultable et modifiable. |
|
Enregistrement |
Chaque enregistrement correspond à une information relative à un élément stocké dans la base de données. |
|
Champ |
Un champ représente une caractéristique précise de l’enregistrement. Pour caractériser un enregistrement, plusieurs champs sont nécessaires, par exemple pour un salarié : N° identifiant, Prénom, Nom, Sexe, Fonction, Salaire… |
|
Filtre de rapport |
Permet de filtrer les données à afficher en fonction de l’élément sélectionné dans le filtre du rapport. Un filtre de rapport vous permet de présenter rapidement un sous-ensemble de données dans un rapport de tableau croisé. Lorsque vos données sources intègrent un grand nombre de données, le filtre permet de travailler sur une partie des données sources du rapport. Vous pouvez ainsi afficher les synthèses pour une famille de produits, une période de temps ou une catégorie de personnel spécifique. |
|
Segments |
Autorise l’utilisation de boutons pour segmenter et filtrer rapidement des données. |
|
Modèle de données |
Collection de tables et de leurs relations entre elles. |
|
Étiquettes de lignes |
Permet d’afficher les champs sous forme de lignes sur le côté gauche du rapport. Une ligne de position inférieure est imbriquée dans la ligne affichée immédiatement au-dessus d’elle. |
|
Étiquettes de colonnes |
Permet d’afficher les champs sous forme de colonnes en haut du rapport. Une colonne de position inférieure est imbriquée dans la colonne affichée immédiatement au-dessus d’elle. |
|
Champs de valeurs |
Sert à afficher des données de synthèse numériques. Vous pouvez utiliser différentes fonctions de synthèse (somme, moyenne, min, max, nombre…). |
Les limites des tableaux croisés dynamiques
|
Fonctionnalité |
Limite maximale |
|
Nombre maximal de tableau croisé dynamique par feuille |
Limité par la quantité de mémoire disponible |
|
Nombre maximal... |
Notre premier tableau croisé dynamique
Les données sources
Pour construire notre premier tableau, ouvrez le fichier TCD.xlsx.
Les données présentes dans la feuille ListeVentes sont telles que ci-après. Les ventes de deux années ont été mémorisées (2015 et 2016).
Ce fichier rassemble les différentes ventes de fournitures d’une entreprise.
Les ventes sont réalisées dans cinq secteurs.
-
ADMINISTRATION
-
COMMERCE
-
INDUSTRIE
-
SANTE
-
SERVICE
Trois commerciaux, chacun chargé de plusieurs départements, assurent les ventes.
-
MENGIN, départements 38, 42 et 69
-
LAUBRAC, départements 26, 30 et 84
-
REYNIER, départements 11 et 34
Première synthèse
Le tableau de données comporte cinq colonnes, notre premier tableau croisé dynamique va être aisé à construire. Nous allons dans un premier temps calculer le montant total des ventes par commercial et par secteur.
Positionnez le curseur dans une cellule de la source, A1 par exemple.
Onglet Insérer - groupe Tableaux - cliquez sur Tableau croisé dynamique :
La fenêtre Créer un tableau croisé dynamique apparaît :
La plage des données à analyser est sélectionnée automatiquement et apparaît entourée d’une ligne de pointillés mouvants. L’emplacement Nouvelle...
Mettre en forme le tableau croisé
Modifier la disposition du tableau croisé dynamique
Il est en général préférable de présenter un rapport de tableau croisé avec en en-tête de lignes les données les plus nombreuses. Ainsi, le tableau pourra être imprimé directement en orientation portrait.
Pour illustrer cette technique, décochez dans la fenêtre des champs de tableau croisé les trois champs Date, Trimestres et Années, puis cochez SECTEUR.
Ici, nous avons plus de secteurs que de commerciaux, c’est pourquoi nous avons placé les secteurs en en-tête de lignes et les commerciaux en en-tête de colonnes.
Nous allons juste visualiser quelle aurait été la présentation du tableau si les champs avaient été inversés.
Dans la boîte de dialogue, faites glisser le champ SECTEUR dans la zone Colonnes et COMMERCIAL dans la zone Lignes.
Le tableau croisé dynamique est réorganisé :
Un des points forts d’Excel est qu’il nous permet de faire des essais de disposition. Si lors de la création de votre tableau croisé vous vous rendez compte qu’il n’est pas fonctionnel, il vous suffira de quelques secondes pour l’organiser différemment.
Lorsque l’on positionne le curseur en dehors du tableau croisé dynamique, la boîte de dialogue n’est plus affichée. Pour la faire apparaître à nouveau, il suffit de cliquer dans le tableau croisé.
Si la boîte de dialogue a été fermée par la croix située en haut à droite, faites un clic droit dans le tableau croisé puis sélectionnez l’option Afficher la liste de champs pour l’afficher.
Modifier le format des nombres
En fonction du type de valeurs que vous analysez, il peut être utile de formater les nombres au format millier, ceci afin de définir...
Statistiques sur un fichier de salariés
Fonctions standards
Ouvrez le fichier Salariés.xlsx.
La feuille Liste de ce classeur contient des informations sur les 116 employés d’une entreprise. Sont recensés les employés en contrat à durée indéterminée (CDI) et les employés en contrat à durée déterminée (CDD).
Effectif par service et par sexe
Insérez le tableau croisé, le nombre de services étant bien évidemment supérieur au nombre de sexes, il est préférable de placer les services en en-tête de lignes et les sexes en en-tête de colonnes.
La boîte de dialogue Champs de tableau croisé dynamique est présentée ci-après.
En insérant le champ Nom dans la zone ∑ Valeurs, Excel utilise automatiquement la fonction Nombre car ce champ ne comporte que des valeurs de type texte.
Nous aurions très bien pu faire glisser dans cette zone l’un des champs, Prénom, Sexe, TypeContrat… cela n’aurait pas modifié le résultat obtenu.
En insérant un champ contenant des valeurs numériques (Salaire ou Ancienneté), nous aurions été obligés de venir modifier pour ce champ les Paramètres des champs de valeurs, pour utiliser la fonction Nombre.
Le tableau inséré est représenté...
Les tableaux croisés dynamiques recommandés
Objectif
Excel 2016 peut vous proposer plusieurs méthodes pour synthétiser vos données. Il va ainsi afficher un bref aperçu des dispositions de champs pour vous permettre de choisir celle qui vous semble la plus pertinente en fonction de ce que vous souhaitez analyser.
Vous pouvez utiliser à tout moment cette méthode de conception simple et rapide pour faire des essais et ainsi explorer les différentes possibilités de présentation proposées.
Les suggestions de tableaux croisés dynamiques vont parfois permettre au débutant de créer un tableau croisé auquel il n’aurait peut-être pas pensé.
Utilisation
Afin de tester cette méthode, nous allons utiliser les données de notre feuille Liste.
Cliquez dans la liste de données.
Onglet Insérer - groupe Tableaux, cliquez sur le deuxième bouton Tableaux croisés dynamiques.
Excel affiche une fenêtre de suggestions.
En cliquant successivement sur les suggestions dans la partie de gauche de la fenêtre, vous pourrez visualiser instantanément le tableau croisé dynamique proposé. N’hésitez pas à agrandir la largeur de la fenêtre si besoin.
En cliquant sur OK, le tableau sélectionné est inséré dans une nouvelle feuille. Il ne vous reste...
