Tableau Croisé dynamique (Excel)

Cet exercice, mis en situation sur Excel, existe également en version OpenOffice Calc.

Récupérez le document de travail qui se trouve en document joint. Il y a un autre classeur, « Document finalisé, version Excel » mais, comme son nom l’indique, ce n’est pas sur ce dernier qu’il faut faire l’exo.

Votre document de travail devrait ressembler à ceci :

Vous remarquerez qu’on y a mis les formes. En particulier, le fond alterné une ligne sur deux devrait vous rappeler quelques souvenirs.

Bien que ce ne soit pas indispensable, nous allons commencer par effectuer un tri des données par date :

Après ceci, il est temps de rentrer dans le vif du sujet :

L’assistant s’affiche à l’écran. Cliquez sur « Suivant » :

Le tableur identifie ensuite la zone des données à traiter. Cela vous permet de vérifier et, le cas échéant, d’ajuster (ce qui n’est pas nécessaire dans notre exemple)

Cliquez à nouveau sur « Suivant ».

Sur cette troisième et dernière étape, nous allons cliquer sur le bouton « Disposition », cela permettra d’apporter quelques explications sur les tableaux croisés dynamiques :

Vous allez travailler dans un environnement qui se décompose en quatre principales zones :

– LIGNE

– COLONNE

– DONNÉES

– PAGE

Comme vous pouvez le constater, il s’agit d’une structure « tabulaire » classique. Nous verrons un peu plus loin, à partir de notre exemple, comment il est possible d’utiliser cette interface. Vous retrouverez toujours cette organisation dans l’assistant de tableau croisé dynamique, quel que soit le document à traiter.

Par contre, dans la partie droite de l’assistant, figurent les champs de notre tableau (ce qui est inscrit en première ligne).

La fonctionnalité dite de « tableau croisé dynamique » consiste à récupérer les informations stockées dans une feuille de calcul afin de reconstruire « à la volée » d’autres tableaux. C’est un outil qui permet tout à la fois de créer des « synthèses » et d’isoler certaines informations, afin d’en avoir une vue plus détaillée.

Les tableaux croisés dynamiques sont donc tout à fait adaptés pour retraiter de longues listes de données saisies « en vrac », comportant, par exemple des dates, des chiffres ainsi que des informations qui apparaissent de façon répétitive (séries ou catégories).

Il est essentiel que votre tableau ne comporte pas de lignes vides. Vous ne pouvez pas lancer l’assistant de tableau croisé dynamique si votre document affiche des sous-totaux (vous avez donc intérêt à les désactiver si vous souhaitez utiliser certaines fonctions avancées du tableur, les tableaux croisés dynamiques permettent de recréer des sous-totaux et même bien plus que cela).

Nous pourrions, dès cette étape, commencer à organiser le tableau croisé dynamique. Pour cela il suffirait de glisser-déposer les champs du tableau vers les zones prédéfinies. Sur les anciennes versions d’Excel (97), ce passage était même quasiment imposé. Nous n’en ferons rien dans le cas présent, car il est beaucoup plus pratique de traiter les opérations directement sur la feuille de calcul.

Cliquez donc sur le bouton « Annuler ».

De retour sur la troisième étape de l’assistant, cliquez sur le bouton « Options » :

Là encore, cette boîte de dialogue nous apporte quelques informations complémentaires sur la fonctionnalité de tableau croisé dynamique :

En particulier, nous constatons que, par défaut, l’assistant va nous générer des totaux, à l’aide de la fonction « somme ».

Là encore, nous laisserons en l’état cet assistant : cliquez sur OK. Puis sur « Terminer »...

Construire le tableau croisé dynamique

Le tableur affiche ensuite cette vue, dans un nouvel onglet :

Nous retrouvons l’interface, composée des quatre zones, que nous avions vue sur l’assistant.

Dans la boîte « Liste de champs... » pointez « Dates » et, tout en maintenant le bouton enfoncé, déplacez votre souris ...

… jusqu’à la zone « LIGNE », placée à gauche.

Dès que vous lâchez le bouton de la souris, vous constatez que cette colonne s’est remplie avec les informations récupérées sur le tableau.

Si vous éprouvez quelque gêne ou difficulté à effectuer le « glisser-déposer » (par exemple, à cause du pavé tactile de votre portable) voici une méthode alternative qui devrait vous donner satisfaction.

Le but du jeu consiste à placer le champ « Poste » dans la zone « COLONNE ».

Sélectionnez l’option « Zone de colonnes ».

Cliquez ensuite sur le champ « Poste » puis sur le bouton « Ajouter à » :

Les « postes », qui étaient présentées sur le document d’origine dans une seule série, s’affichent ici dans différentes colonnes :

On continue : glissez-déposez le champ « Montants » dans la zone centrale, « DONNEES » :

Le tableau commence à prendre forme mais, à première vue, l’intérêt d’afficher cette présentation, quelque peu clairsemée, ne semble pas s’imposer.

Ce n’est pas fini.

Déplacez le champ « Acheteur-euse » vers la zone « Page »

Bien.

Observez que tous les champs de la liste qui ont été insérés dans le dispositif en vue de créer un « rapport de tableau croisé dynamique » apparaisse en gras dans la palette « Liste de champs de tableau croisé ».

Nous n’avons plus besoin de cette palette. Vous pouvez la déplacer dans la partie droite de votre fenêtre, ce qui nous apportera une meilleure visibilité.

Grouper

Cliquez ensuite du bouton droit de la souris sur n’importe quelle date...

… choisissez l’option « Grouper ».

Excel reconnaît qu’il s’agit d’un format « Date » et vous propose alors de grouper vos données en différentes unités de mesure du temps :

Choisissez « Mois » et cliquez sur le bouton « OK ».

Le tableau se présente désormais sous une forme nettement plus exploitable :

Ces informations n’apparaissaient pas, en tant que telles, dans la feuille de calcul d’origine mais il a été possible de les obtenir en demandant au tableur de reconstruire un nouveau tableau en ne récupérant que certaines données.

Nous n’avons qu’une vue partielle et sélective du document courant (uniquement le champ« Postes » listé par date), ce qui permet de faire ressortir un aperçu synthétique des dépenses.

Les tableaux croisés dynamiques ne font rien d’autre que cela : construire des « synthèses sélectives » ou « filtrées » à partir d’un document source (le plus souvent, un tableau Excel). Nous verrons un peu plus loin en quoi consiste l’aspect dynamique de l’affaire.

Poursuivons avec un peu de cosmétique.

Sur cette palette, cliquez sur le bouton « Mettre en forme le rapport » :

Sélectionnez ensuite le modèle « Table 2 » (à titre d’exemple) :

Excel modifie l’apparence du tableau en appliquant une mise en forme automatique :

Les champs « pages »

Observons, à présent à quoi correspond cette zone « PAGE », dans laquelle nous avions placé le champ « Acheteur - euse ».

Cliquez sur la petite flèche placée à droite de « Tous » et sélectionnez l’une des personnes, par exemple, « Annie » :

Le tableau s’actualise immédiatement avec les informations ne concernant que les achats effectués par cette personne :

Afficher les détails

Rétablissez l’option « Tous » du champ « Acheteur-euse » et cliquez sur cette cellule « D5 », autrement dit, les dépenses de « Fourniture de bureau » effectuées pendant le mois de janvier.

Sur la palette, cliquez ensuite sur le bouton « Afficher les détails » :

Le détail s’affiche alors directement sur une troisième feuille de calcul, distincte à la fois de celle sur laquelle se trouve le tableau d’origine et de celle qui gère les rapports de tableaux croisé dynamique.

La fonctionnalité « tableau croisé dynamique » permet de générer une multitude de synthèses, chacune s’affichant à chaque fois sur une feuille différente. Pour plus de clarté, je vous recommande vivement de renommer systématiquement toutes vos feuilles de calcul (clic droit sur l’onglet et « Renommer ».)

Vous remarquerez que le tableur est allé chercher des informations (descriptions et références de factures) qui n’apparaissaient pas dans le tableau croisé dynamique.

Les graphiques croisés dynamiques

Retournez sur la feuille « Tableau croisé dynamique » et cliquez sur le bouton « assistant graphique » de la palette :

Le graphique s’affiche immédiatement.

Rapide diagnostic : du point de vue de l’organisation des données, il y a tout ce dont nous avons besoin mais, question présentation et lisibilité, le verdict est sans appel : peut mieux faire.

Après ce rapide constat, un coup d’œil s’impose vers le bas de la fenêtre : là encore, ce graphique s’est affiché sur une nouvelle feuille de calcul ; nous en sommes à notre quatrième feuille dans le classeur courant :

Essayons, à présent, d’améliorer la présentation de ce graphique. Cliquez à nouveau sur le bouton « Assistant graphique » :

Nous resterons dans la catégorie « Histogramme », mais nous prendrons cette option « 3D » :

Cliquez sur terminer :

C’est déjà beaucoup plus parlant, non ?

Déplacez les palettes et modifiez la taille d’affichage (ici, je suis passé de 130 à 100%) afin de dégager la vue et d’optimiser votre environnement de travail. Il nous reste à faire quelques petites manips sur ce graphique.

Pour l’heure, ce graphique représente bien les achats, poste par poste effectués par tous les acheteur - euse - s.

À nouveau, nous allons demander de nous fournir une vue détaillée sur l’une de ces personnes :

Tout est automatiquement réactualisé, y compris les échelles de valeur.

Passons à cette autre personne :

Autre personne, autre dépenses.

On constate juste une forte dépense « Honoraires » en février.

C’est quoi, déjà cette affaire ? se demande Émilie en observant cette incommodante barre qui écrase toutes les autres...

« Dynamique » ?

Nous accompagnerons Émilie pour cette étape de l’exercice.

Un clic sur l’onglet « Tableau croisé dynamique » permet à Émilie de quitter le graphique pour sélectionner le poste « honoraires » et demander « d’afficher les détails » :

Ah ! C’était donc çà...

Émilie clique sur l’onglet « Premier trimestre 2009 » et, sans l’ombre d’une hésitation, décide d’apporter quelques petites modifications :

Retour sur le graphique. Pour l’instant aucune modification n’est visible.

Ce n’est qu’en cliquant sur ce bouton « Actualiser les données », que..

… et hop !

Ah oui, c’est nettement plus présentable ainsi, Émilie.

Voilà en quoi consiste le «  dynamique  » des tableaux croisés dynamiques : les différentes synthèses s’actualisent, à la demande, en fonction des modifications apportées sur le document source.

Voyons, à présent, un autre exemple d’actualisation dynamique. Pour cela, nous suivrons les manipulations effectuées, cette fois-ci, par Bertrand.

L’ami Bertrand modifie, en premier lieu, le champ « pages », afin de n’afficher que son compte.

Puis, il quitte le tableau croisé dynamique afin de passer sur la feuille « Premier trimestre 2009 ».

Il lui faut ajouter une référence dans la liste. Pour cela, un clic droit de la souris afin d’ajouter une ligne...

.. puis, saisie, largement assistée, grâce au remplissage automatique (voir support)

Retour sur l’onglet « Tableau croisé dynamique ».

La modification n’est pas encore prise en compte.

Ce n’est qu’après avoir cliqué sur le bouton « Actualiser les données »...

La cellule à traiter (C7) est bien sélectionnée, il suffit de cliquer sur « Afficher les détails » pour en avoir le cœur net.

L’actualisation des données s’effectue de façon dynamique non seulement sur les valeurs (le cas Émilie) mais également sur la structure du tableau (le cas Bertrand, qui ajoute une ligne) :

Le fichier finalisé est disponible ci-dessous :