Les tableaux croisés dynamiques

L'approche multidimensionnelle

Les tableaux croisés dynamiques sont l'un des outils les plus puissants d'Excel car ils permettent la synthèse de grandes masses de données, ils sont particulièrement utiles dans le cadre d'une approche multidimensionnelle puisqu'ils en partagent la logique.

Rappelons donc les principes d'une approche multidimensionnelle telle qu'elle est présentée au chapitre sur les bases de données relationnelles.

Une population, quelle que soit composée d'hommes, d'animaux, d'entreprises ou d'individus de toute autre nature, peut généralement être étudiée selon plusieurs critères, c'est-à-dire plusieurs points de vue. Par, exemple, pour un comptable national, les entreprises peuvent être étudiées du point de vue de leur branche d'activité, de leur secteur institutionnel, de leur taille et de bien d'autres points de vue encore.

En informatique, ces différents points de vue peuvent être considérés comme les dimensions d'un cube ou de ce que nous appellerons un hypercube, d'une part parce qu'il peut avoir plus de trois dimensions, d'autre part pour le distinguer des cubes OLAP.

Par exemple, un hypercube comportant les trois dimensions, activité, secteur et taille peut se représenter ainsi :

Un tableau croisé dynamique d'Excel nous permet d'avoir une coupe en deux dimensions de ce cube en pouvant croiser aussi bien l'activité et le secteur que l'activité et la taille ainsi que la taille et le secteur, cela pour toute valeur de la troisième dimension.

Le tableau croisé dynamique peut également faire de nombreux calculs sur les données de l'hypercube comme la somme, la moyenne, le maximum ou le minimum et bien d'autres encore.

En pratique, pour être exploitées efficacement par un tableau croisé dynamique, les données doivent être stockées dans des tables structurées en hypercube, c'est-à-dire des tables où chaque dimension correspond à une colonne et où les valeurs apparaissent dans une colonne unique. Par exemple, considérons le tableau suivant où les nombres correspondent au nombre d'entreprises :

Pour être utilisé dans un tableau croisé dynamique ce tableau doit être converti dans la table suivante :

Les tables structurées en hypercube peuvent être stockées dans une feuille Excel, dans un fichier CSV ou dans une base de données.

Premiers pas

La première étape consiste à se connecter aux données. Nous prendrons l'exemple de la table précédente et nous supposerons qu'elle est stockée dans le fichier CSV DonneesCSV.csv, ce fichier ayant un séparateur point-virgule et étant situé dans le dossier C:\essai.

Pour nous connecter à ce fichier nous devons d'abord créer une connexion et, pour cela, aller dans le menu Données puis Obtenir des données à partir d'un fichier texte/CSV.

Il faut alors sélectionner le fichier puis cliquer sur Importer, la fenêtre suivante s'ouvre alors :

Il faut alors cliquer sur Charger dans..., ce qui fait apparaître une nouvelle fenêtre :

On sélectionnera Rapport de tableau croisé dynamique et on décidera de son emplacement sur la feuille active ou sur une nouvelle.

Le fenêtre suivante s'ouvre alors à droite de la feuille :

C'est ici que nous allons décider de ce que devra être notre tableau croisé dynamique. En effet, en faisant glisser les champs dans les différentes parties du tableau, nous pourrons sélectionner les deux dimensions à afficher.

Par exemple, choisissons de mettre l'activité en ligne et le secteur en colonne. Le nombre va logiquement en valeurs et nous pouvons mettre la troisième dimension, c'est-à-dire les effectifs, en filtre. Nous faisons apparaître le tableau suivant :

Dans le filtre Effectifs nous pouvons sélectionner un ou plusieurs critères, par exemple 0-10 salariés. Nous obtenons :

Bien d'autres présentations sont possibles, par exemple si nous faisons glisser le champ Effectifs dans les lignes de la manière suivante :

Nous obtenons :

Nous voyons déjà l'intérêt du tableau croisé dynamique, il nous permet d'afficher deux dimensions ou plus parmi toutes celles de l'hypercube, aucune n'étant privilégiée, et il est extrêmement facile de passer d'une sélection à l'autre.

Mise en forme du tableau croisé dynamique

Le tableau croisé dynamique dispose de nombreuses options de mise en forme qui sont accessibles en cliquant sur le tableau avec le bouton droit de la souris. Tout d'abord, on peut masquer ou afficher la liste des champs située à droite de la feuille. On peut ensuite, parmi les autres options, choisir le format des cellules et celui des nombres.

L'une des possibilités les plus intéressantes réside dans le choix des calculs effectués par le tableau croisé dynamique. En allant dans le menu Synthétiser les valeurs par, on peut remplacer le calcul standard de somme notamment par celui de moyenne, maximum, minimum ou produit. Par exemple, en sélectionnant Moyenne, on obtient :

Le menu Afficher les valeurs ouvre aussi des possibilités particulièrement intéressantes pour présenter des structures. Par exemple, si l'on sélectionne % du total de la colonne on obtient :

En sélectionnant % du total de la colonne parente et en modifiant le format on obtient :

En sélectionnant Options du tableau croisé dynamique on voit s'ouvrir une fenêtre présentant de nombreuses options. Parmi elles notons dans l'onglet Totaux et filtres la possibilité d'afficher ou non les totaux des lignes et les totaux des colonnes. C'est important car si ces totaux ont un sens dans notre exemple, ce n'est pas toujours le cas.

Dans l'onglet Disposition et mise en forme, il est nécessaire de décocher l'option Ajuster automatiquement la largeur des colonnes lors de la mise jour si l'on veut conserver la largeur des colonnes.

Dans l'onglet Données, on peut désélectionner Enregistrer les données sources avec le fichier si l'on veut limiter la taille du fichier ou si l'on veut être certain d'avoir les données actualisées lors de son ouverture.

L'ordre des lignes et des colonnes peut être modifié soit manuellement, soit en cliquant avec le bouton droit sur les étiquettes de lignes ou de colonnes et en choisissant le menu Déplacer.

Les segments

Les segments facilitent les sélections dans un tableau croisé dynamique. Pour le montrer, nous prendrons l'exemple de notre tout premier tableau croisé dynamique. Pour créer un segment il faut cliquer sur le tableau croisé dynamique, ce qui fait apparaître dans le menu une rubrique Analyse du tableau croisé dynamique. En sélectionnant cette rubrique on voit apparaître un nouveau menu parmi lequel figure Insérer un segment. Si nous le choisissons nous voyons apparaître :

Il est souvent plus logique d'utiliser un segment pour le champ placé en filtre, nous cliquerons donc sur Effectifs, ce qui donne :

En cliquant, par exemple, sur Plus de 10 salariés on affichera uniquement les données se rapportant aux entreprises de plus de 10 salariés. Les segments ont pour seule utilité de faciliter la sélection en la rendant plus visible et plus facile d'accès.

Les segments disposent aussi de paramètres auxquels on peut accéder en cliquant dessus avec le bouton droit. Par exemple, il peut être intéressant de sélectionner Masquer les éléments sans données.

Calcul d'une moyenne pondérée

S'il est possible de calculer directement des moyennes simples dans un tableau croisé dynamique, ce n'est pas le cas pour les moyennes pondérées si bien qu'un travail préliminaire est nécessaire. Pour le montrer, nous partirons de notre exemple précédent et nous supposerons que les pondérations sont contenues dans la table suivante :

Pour calculer les moyennes pondérées nous devons créer une nouvelle table, par exemple à l'aide d'une requête SQL comme nous l'avons vu au chapitre sur les bases de données. Cette nouvelle table devra contenir deux colonnes de valeurs, l'une pour la pondération, l'autre pour le produit du nombre d'entreprises par son poids. Ce qui nous donne la table suivante :

Nous pouvons maintenant la charger dans un tableau croisé dynamique comme nous l'avons fait précédemment. Nous mettrons, par exemple l'activité et les effectifs en ligne et nous ferons glisser à la fois le produit et le poids en valeurs. Il nous faut maintenant introduire dans le tableau la division du produit par le poids. Pour cela, dans le menu Tableau croisé dynamique, nous devons cliquer sur Champs, éléments et jeux puis sur Champ calculé. Nous voyons apparaître la fenêtre suivante :

Nous pouvons, par exemple, donner le nom Moyenne au champ calculé et dans la formule nous devons inscrire :

=Produit/Poids

En cliquant sur OK nous obtenons le tableau suivant :

Nous pouvons vérifier que la colonne Somme de Moyenne contient bien les moyennes pondérées. En effet, pour tout groupe de valeurs, Excel calcule d'abord la somme des produits puis divise par la somme des poids, ce qui correspond bien au mode de calcul des moyennes pondérées.

Les hiérarchies

Les hiérarchies jouent un rôle particulièrement important dans une approche multidimensionnelle car elles permettent de présenter des synthèses. Pour montrer comment créer une hiérarchie dans un tableau croisé dynamique nous partirons d'un nouvel exemple et nous considérerons la table suivante :

Cette table est stockée dans le fichier CSV Activites.csv. Si nous voulons regrouper les différentes activités dans les trois secteurs primaire, secondaire et tertiaire, nous pouvons utiliser la table suivante :

Cette table sera stockée dans le fichier CSV Hactivites.csv. Pour mettre en place la hiérarchie nous devons faire une jointure entre les deux tables, le champ Activité étant commun aux deux tables. Pour cela, nous devons commencer par créer des connexions aux deux fichiers.

Dans le menu principal nous devons aller dans Obtenir des données texte/CSV puis sélectionner Activites.CSV. Dans la fenêtre qui s'ouvre il faut cliquer sur Charger dans puis, dans la nouvelle fenêtre, cocher Ne créer que la connexion et Ajouter ces données au modèle de données puis OK.

On recommence l'opération pour le fichier Hactivites.csv mais, au moment d'importer, la fenêtre suivante apparaît :

Nous constatons que le titre des colonnes ne correspond pas au titre de la table, nous devons donc cliquer sur Transformer les données, ce qui nous fait entrer dans l'éditeur PowerQuery. Dan le menu, il faut sélectionner Utiliser la première ligne pour les en-têtes puis Fermer et charger dans. Nous retrouvons la fenêtre habituelle dans laquelle nous cocherons Ne créer que la connexion et Ajouter ces données au modèle de données puis nous cliquerons sur OK.

Nous pouvons maintenant créer le tableau croisé dynamique. Plaçons nous donc sur une feuille où nous voulons placer le tableau puis, dans le menu Insertion sélectionnons Tableau dynamique à partir d'un modèle de données. Après avoir choisi l'emplacement du tableau, la fenêtre des champs de tableau dynamique apparaît avec les deux tables Activites et Hactivites.

Par exemple, dans le première table nous pouvons faire glisser Effectifs en colonnes et Nombre en valeurs. Dans la deuxième table faisons glisser Secteur en lignes. Nous voyons apparaître le message suivant :

Comme, dans notre exemple, les colonnes qui permettent la jointure des deux tables ont le même intitulé, nous pouvons cliquer sur Détecter automatiquement. Si ce n'était pas le cas, nous devrions opter pour CREER..., ce qui ferait apparaître la fenêtre suivante :

Dans la première ligne nous pourrions sélectionner la table Activités et la colonne Activité. Dans la seconde ligne, nous devrions alors sélectionner la table Hactivites et la colonne Activité puis cliquer sur OK

Dans le cas d'une détection automatique comme dans celui d'une sélection manuelle, Excel réalise une jointure entre les deux tables. Il est alors possible de poursuivre la mise en forme de notre tableau, par exemple en faisant glisser Activité en lignes après Secteur. Nous obtenons :

Si nous voulons faire apparaître le total des secteurs, nous pouvons cliquer avec le bouton droit sur Primaire et sélectionner Sous-total de «Secteur». Dans le même menu, nous pouvons également choisir de ne faire apparaître que les secteurs en sélectionnant Développer/Réduire puis Réduire le champ entier.

Nous pouvons rajouter un niveau dans la hiérarchie en ajoutant une colonne à la table, par exemple :

Après avoir refait le même processus, nous obtenons :

Actualiser le tableau croisé dynamique

Les données du fichier source peuvent changer aussi faut-il pouvoir actualiser le tableau croisé dynamique. Pour cela, la méthode la plus simple consiste à cliquer avec le bouton droit sur le tableau croisé dynamique et, dans le menu déroulant, choisir l'option Actualiser.

Si l'on veut que l'actualisation se fasse automatiquement à l'ouverture du classeur, il faut aller dans la fenêtre Options du tableau croisé dynamique et choisir Actualiser les données lors de l'ouverture du fichier.

Dans un programme, on peut actualiser les données avec l'instruction RefreshAll. Par exemple :

ThisWorkbook.RefreshAll

Si l'adresse du fichier source a changé, il faut modifier sa connexion avec le tableau croisé dynamique. Pour connaître le nom de la connexion, il faut se placer sur le tableau croisé dynamique et, dans le menu principal, choisir Données puis Propriétés. Toujours dans le menu Données il faut ensuite cliquer sur Requêtes et connexions, ce qui fera apparaître à droite de l'écran la fenêtre des connexions.

En survolant la connexion du tableau croisé dynamique, on voit une nouvelle fenêtre dans laquelle on cliquera sur Modifier, ce qui nous fait entrer dans l'éditeur Power Query. Dans le cadre ÉTAPES APPLIQUÉES situé à droite de l'écran, on cliquera sur Source, ce qui fait apparaître en haut de l'écran une ligne où apparaît l'adresse enregistrée de la source. Il faut alors modifier cette adresse puis cliquer sur Fermer et charger.

Auteur : Francis Malherbe