Tutoriel pas à pas pour créer un tableau dynamique avec Excel et regrouper, recouper, analyser, trier et filtrer les données facilement.
Comment créer un tableau croisé dynamique Excel pour analyser les données ? Pour analyser beaucoup de données, il peut s’avérer difficile d’analyser toutes les informations de votre feuille de calcul. Surtout les tableaux volumineux avec plusieurs milliers de lignes. Utiliser ce tutoriel sur les tableaux croisés dynamiques pour mieux gérer les feuilles de calcul. Mais aussi synthétiser les données et permettre une meilleure lecture des tendances.
Table of Contents
Utiliser un tableau croisé dynamique Excel pour répondre aux questions métier
Comment répondre à la question « Combien vend chaque vendeur par année ? » en regardant les données de ventes de l’exemple qui suit. Répondre à cette question prendrait du temps et serait compliqué.
En effet, tous les vendeurs sont repris sur plusieurs lignes et il faut additionner toutes leurs ventes individuellement. Et utiliser la commande sous-total pour trouver le total des ventes de chaque personne. Soit beaucoup de données à passer en revue.
Le tableau croisé Excel permet de croiser des données facilement
Un des meilleurs aspects des tableaux croisés dynamiques est qu’on peut rapidement réorganiser les données. Ce qui permet de poser un œil nouveau sur la feuille de calcul. Manipuler des données peut aider à répondre à différentes questions fonctionnelles ou financières. Et aussi permettre de faire des expériences avec des données nouvelles ou des projections futures pour permettre de déduire de nouvelles tendances et de nouveaux modèles.
Voici une sélection des meilleurs raccourcis Excel pour gérer les tableaux croisés dynamiques.
Présentation des tableaux croisés dynamiques Excel
Les données sources du tableau croisé sont un simple tableau Excel, qui ressemble à cela:
Heureusement, les tableaux croisés dynamiques peuvent en un instant calculer et résumer des données. Ils sont à la fois facile à lire et à organiser. Après la préparation et la mise en forme des données, les tableaux croisés dynamiques ressemblent à ceci :
Après avoir créer un tableau croisé dynamique (TCD), il est possible de l’utiliser pour répondre à différentes questions en réorganisant les données. Par exemple, pour répondre maintenant à la question suivante : Quel est le total annuel des ventes ? Modifier le tableau croisé dynamique afin qu’il prenne cette apparence :
Etape 1 : Préparer les données sources à analyser avec Excel
Sélectionner le tableau ou les cellules (y compris les en-têtes de colonnes) contenant les données sources à utiliser.
Aller dans l’onglet Insertion et cliquer sur Tableau Croisé Dynamique.
La fenêtre de dialogue de création de tableaux croisés dynamiques apparaîtra. Choisir les paramètres adéquats et cliquer sur OK. Dans notre exemple, le tableau Table1 comporte les données de base. Configurer pour que le tableau croisé dynamique soit placé sur une feuille de calcul existante.
- Un tableau croisé dynamique et une liste de champs vierges apparaîtront dans une nouvelle feuille de calcul.
- Depuis le menu du tableau croisé dynamique, choisir les champs à ajouter. Chaque champ est simplement un des en-têtes de colonne des données originales. Cocher dans la liste de champs du tableau croisé dynamique la case de chaque champ à ajouter.
Etape 2 : Sélectionner les champs du tableau à croiser
Dans cet exemple, chercher à connaître le montant total des commandes de chaque vendeur par pays et par année, cocher donc les champs suivants:
- Vendeur (Salesperson)
- Pays (Country)
- Année (Year)
- Total des ventes (SalesAmount)
Ajouter les champs dans les sections
Les champs sélectionnés sont ajoutés à l’une des quatre zones qui figurent sous la liste de champs. Dans notre exemple, le champ Vendeurs a été ajouté à la zone des Lignes et le champ Total des commandes a été ajouté dans la zone des Valeurs. Si la zone n’est pas satisfaisante, déplacer un champ dans une nouvelle zone en cliquant dessus.
Le tableau croisé dynamique permet de calculer et résumer les champs choisis. Dans notre cas, le tableau croisé dynamique indique la somme des commandes de chaque vendeur, détaillé par pays et par année civile.
Trier et filtrer les données du tableau croisé dynamique Excel
- Classer les données d’un tableau croisé dynamique comme celles de n’importe quelle feuille de calcul. Pour cela, utiliser la commande Trier et filtrer dans l’onglet Accueil. Il est possible également de formater les données chiffrées de manière personnalisée.
Changer la mise en forme du tableau croisé
- Changer par exemple l’affichage des nombres pour indiquer une devise (Monétaire), comme le Dollar US ($) ou l’Euro ($). Garder cependant en mémoire que certains arrangements peuvent disparaître après une modification de la structure d’un tableau croisé dynamique.
Etape 3 : Mettre à jour le tableau croisé dynamique Excel après un changement de structure
Après avoir modifier les données dans l’ongler source, le tableau croisé dynamique ne se met pas à jour automatiquement. Pour l’éditer manuellement, sélectionner le tableau croisé dynamique et aller dans Analyse -> Actualiser.
Dans notre exemple, le tableau croisé dynamique réponds à la question suivante: Quel est le total des ventes pour chaque vendeur par pays et par année?
Maintenant, pour poser une nouvelle question : Quel est le total annuel des ventes ? Changer simplement le champ de la zone Lignes pour ne laisser uniquement les années.
Connecter un tableau croisé dynamique Excel à cube SSAS
Dans l’exemple ci-dessus, la source des données est un onglet dans un tableur Excel. Le tableau Excel permet aussi de se connecter à un cube OLAP pour analyser de grands volumes de données avec des temps de réponses performants.
Le serveur SQL et l’nstance SSAS (Mode Tabular) dans ce cas est localhost car le SDGB est installé sur la machine locale.
La connexion établie permet de sélectionner la base SSAS et cube tabulaire (Tabular Model en Anglais). Le cube sélectionné est le cube AdventureWorks for Analysis Services tabular model, disponible sur le site github.
Cliquer sur Suivant deux fois pour conserver les options par défaut et le tableau croisé dynamique s’affiche avec les dimensions et les indicateurs SSAS du modèle tabulaire.
Conclusion sur les tableaux croisés dynamiques avec Excel
Pour conclure ce tutoriel sur la création de tableaux croisés dynamiques avec Excel, il explique pas à pas comment le créer pour mieux analyser les données des feuilles de calcul. Et surtout comment synthétiser les données afin de permettre une meilleure lecture des tendances et des totaux.
Le tutoriel explique aussi comment utiliser un tableau croisé dynamique pour répondre à des questions métier en réorganisant les données et en calculant les champs choisis. Il fournit des astuces pour trier et filtrer les données et pour changer la mise en forme du tableau croisé dynamique.
Enfin, le tutoriel explique comment mettre à jour le tableau croisé dynamique Excel après un changement de structure du tableau source. Et aussi comment se connecter à un cube SSAS pour analyser de grands volumes de données avec des temps de réponse plus performants.
