Préparation des données
Avant de se lancer, jetons un rapide coup d'oeil à vos données !
En effet, un tableau croisé dynamique ne peut être généré qu'à partir d'une liste de données. Celles-ci sont organisées sous forme d'un tableau (qu'on va appeler tableau source) qui doit répondre à certains critères obligatoires :
- Vos données doivent être organisées en colonnes.
- Les données d'une colonne doivent être du même type (ne mélangez pas les dates, le texte et les nombres).
- La première ligne de vos données (et celle-là uniquement) doit contenir les entêtes de colonnes (les titres), pour permettre à Excel de les retrouver.
- Evitez d'avoir de colonne ou de ligne entièrement vide dans votre liste.
- Il ne doit pas y avoir de cellules fusionnées dans votre liste.
- Enlevez les sous-totaux si vous en avez.
Voici les données sur lesquelles nous allons travailler à titre d'exemple. Il s'agit de la liste des ventes de chaque salarié, jour par jour, comprenant des informations liées à l'agence de rattachement, les références produit, les prix, la quantité :
L'ensemble des critères est satisfait ? Tout à fait. Et les données présentes vont nous permettre d'organiser les données sous différents angles de vues, afin d'en effectuer une analyse complète. Nous pouvons créer le tableau croisé dynamique !
Création du tableau croisé dynamique
Pour créer le tableau croisé dynamique, cliquez d'abord sur une cellule de votre tableau source (peu importe laquelle).
Puis allez dans l'onglet Insertion du ruban, et cliquez sur l'icône Tableau croisé dynamique :
Excel sélectionne la totalité de la plage qui correspond à votre liste de données (une bordure animée en pointillés vous indique les limites de la sélection) et une fenêtre apparaît à l'écran :
Vous trouverez dans le premier champ de cette fenêtre les références de la plage qui contient vos données. Si vous avez respecté les critères de préparation des données (notamment : pas de ligne ou de colonne entièrement vide), la sélection doit être correcte, vous n'avez pas à vous en soucier. Dans le cas contraire, vous pouvez corriger la sélection.
Vous devez indiquer à Excel l'emplacement du tableau croisé dynamique à créer : soit une nouvelle feuille de calcul (c'est le cas le plus fréquent), ou une feuille de calcul existante (vous devrez alors indiquer à partir de quelle cellule de cette feuille insérer le TCD).
Nous reviendrons plus tard sur la mention Indiquez si vous souhaitez analyser plusieurs tables (il est en effet possible de croiser les données de plusieurs sources différentes).
A ce stade, validez la boîte de dialogue en l'état en cliquant sur Ok et observez le résultat :
Vous voyez maintenant le tableau dans la feuille. Il est vide pour l'instant, et un message vous invite à choisir les champs dans le volet latéral qui apparaît à droite de l'écran.
Qu'est-ce qu'un champ ? Il s'agit juste des informations contenues dans les colonnes de votre liste de données. Chaque champ possède un titre, qui n'est autre que l'entête de la colonne de votre liste.
Ainsi, dans notre exemple, nous retrouvons dans la partie supérieure les champs Date, Référence produit, Code vendeur, etc... :
Le fonctionnement est le suivant : sélectionnez les champs que vous voulez voir apparaître dans le tableau croisé dynamique, et faites les glisser, selon le cas, dans la zone lignes, colonnes ou valeurs :
Dans notre exemple, on peut demander à Excel de faire apparaître le chiffre d'affaires HT par agence (une ligne par agence) et au mois le mois (un mois par colonne).
Il suffit de faire glisser le champ Agence dans la zone Lignes, le champ Date dans la zone Colonnes, et le champ Montant HT dans la zonne Valeurs :
Le résultat est le suivant :
Excel fait apparaître le total de chaque agence, comme prévu, et indique un total général en fin de tableau, dans la dernière colonne et sur la dernière ligne.
Vous commencez à comprendre toute la puissance des tableaux croisés dynamiques ?
Ne nous arrêtons pas en si bon chemin ! Nous pouvons, au sein de chaque agence, faire apparaître la performance de chaque vendeur. Faites glisser le champ Vendeur dans la zone Lignes, sous Agence :
Voici le résultat :
Remarquez comme Excel fait automatiquement apparaître les totaux de chaque vendeur, mais également les sous-totaux par agence.
Combien de temps pour obtenir ce résultat ? Quelques secondes, et en quelques clics de souris seulement...
Nous avons étudié les ventes sous l'angle du chiffre d'affaires qu'elles représentent. Mais comment faire pour analyser le nombre de ventes ?
C'est simple : les TCD permettent de faire apparaître la somme des informations chiffrées, mais aussi la moyenne, les valeurs minimum ou maximum, le nombre, etc...
Dans le volet latéral à droite de votre écran, faites un clic (droit ou gauche, peu importe) sur Somme de montant HT, pour faire apparaître le menu contextuel. Choisissez alors Paramètres des champs de valeurs... :
La boîte de dialogue Paramètres des champs de valeurs s'affiche à l'écran. Dans la liste Résumer le champ de valeur par, choisissez Nombre et validez :
Le TCD s'actualise, le nombre des ventes s'affiche immédiatement dans la feuille :
Nous avons déjà obtenu beaucoup avec peu d'efforts... Dans la section suivante, nous allons voir comment affiner la présentation du TCD.
Mettre à jour le tableau croisé dynamique
Que se passe-t-il si votre tableau source (vos informations de départ) doivent être modifiées, corrigées, remplacées ? Faut-il tout recommencer à zéro ?
Non, bien sûr ! Excel a prévu une fonction d'actualisation qui permet de mettre à jour votre TCD.
Faite le test ! Modifiez une information dans votre tableau source, puis allez dans l'onglet Analyse du tableau croisé dynamique, et cliquez sur le bouton Actualiser :
Votre TCD est alors actualisé.
Attention :
- la fonction n'est pas automatique, il faudra la répéter à chaque actualisation...
- Si vous ajoutez des lignes à votre tableau source, et que celui-ci n'est pas un tableau structuré, les lignes ajoutées ne seront pas prises en compte : il faudra changer la source de données (jetez un coup d'oeil à la section suivante !).
Changer la source de données
Votre tableau source n'est pas un tableau structuré ?
Si vous avez besoin d'ajouter des lignes ou des colonnes à vos données initiales, celles-ci risquent de ne pas être prises en compte. La raison est simple : Excel a mémorisé la plage initiale qui couvrait l'intégralité de vos données au moment de la constitution du TCD, mais la taille de cette plage ne s'actualise pas.
Dans ce cas, il suffit de changer la source de données en quelques clics !
Reprenons notre exemple, et ajoutons des informations à la dernière ligne de notre tableau source :
Revenez sur l'onglet contenant le tableau croisé dynamique, et cliquez sur une cellule de celui-ci (peu importe laquelle).
Puis allez dans l'onglet Analyse du tableau croisé dynamique du ruban, et cliquez sur Changer la source de données :
Dans la boîte de dialogue qui apparaît, modifiez les références de la plage pour couvrir l'intégralité du tableau source, en incluant la dernière ligne ajoutée :
Votre TCD s'actualise alors. Il inclut désormais la dernière ligne de votre tableau source :
Ces manipulations sont assez rapides en soi. Mais il est dommage d'avoir à les effectuer pour prendre en compte des ajouts dans le tableau source.
Pour vous éviter ce travail supplémentaire, pensez à convertir systématiquement votre tableau source en tableau structuré !
Pourquoi ? Parce que les références d'un tableau structuré s'actualisent automatiquement en cas d'ajout de ligne ou de colonne. Vous n'aurez plus qu'à mettre à jour votre TCD en cliquant sur l'icône Actualiser.
