Comment créer tableau croisé dynamique modèle de données dans Excel 2013
Jusqu'à présent, nous nous sommes concentrés exclusivement sur des tableaux croisés dynamiques qui sont générés à partir d'une seule table de données. fonction du modèle de données a apporté des avantages supplémentaires pour faire pivoter les cartes. Avec le modèle de données, nous pouvons utiliser plusieurs tables de données dans un tableau croisé dynamique unique. Nous devons créer un ou plusieurs « relations de table », de sorte que les données peuvent être attachées ensemble.
Table des matières
Création d'un tableau croisé dynamique à l'aide du modèle de données
La figure ci-dessous montre les parties de trois tableaux qui se trouvent dans un seul classeur. Chaque feuille de calcul est dans le même classeur, juste indiqué dans la fenêtre séparée. Les feuilles de calcul sont des ordres. Les clients. et les régions. Chaque feuille a une table et je l'ai aussi appelé ces 3 tableaux que commandes. Les clients. et les régions (pour le garder organisé). Le tableau des commandes contient des informations sur les commandes de produits. Le tableau des clients contient des informations sur les clients de l'entreprise et la table régions contient des informations régionales.
L'utilisation de ces trois tableaux nous allons créer un tableau croisé dynamique, en utilisant le modèle de données.
Si vous regardez les tables près, vous constaterez que les commandes et les clients tables ont la colonne CustomerID comme commune, et les clients et les tables Régions ont la colonne Etat commun (télécharger le fichier de travail pour voir les choses communes). Nous utiliserons ces colonnes communes pour former la relation entre les tables.
Un à plusieurs relations
Notez que les relations entre les tables sont « one-to-many ». Pour chaque ligne de la table des commandes, il y a exactement une ligne correspondante dans la table des clients, et cette ligne est déterminée par la colonne CustomerID. De même, pour chaque ligne de la table clients, il y a exactement une ligne correspondante dans le tableau des régions, et cette ligne est déterminée par la colonne Etat.
Remarque
Un tableau croisé dynamique créé à l'aide du modèle de données a certaines restrictions, par rapport à un tableau croisé dynamique créée à partir d'une table de données unique. Le plus remarquable est: vous ne pouvez pas créer des groupes. De plus, vous ne pouvez pas créer des champs calculés ou éléments calculés.
Voici le processus étape par étape, nous avons utilisé pour créer le tableau croisé dynamique:
Sélectionnez une cellule dans la table des commandes et choisissez Insérer ➪ tableaux ➪ tableaux croisés dynamiques. La boîte de dialogue Créer un tableau croisé dynamique apparaît.
Choisissez les données que vous souhaitez analyser et choisir où vous voulez que le rapport de tableau croisé dynamique à placer - ces deux options seront comme il est. Sélectionnez l'ajouter ces données à la case à cocher Modèle de données et cliquez sur OK.
Sélectionnez l'ajouter ces données à la case à cocher Modèle de données et cliquez sur OK.
Si vous regardez le volet des tâches Les champs de tableau croisé dynamique (sur le côté droit de la feuille nouvellement créée), vous constaterez qu'il est un peu différent que cette fois, nous avons choisi de travailler avec le modèle de données. Le volet des tâches contient deux onglets: actifs et tous. Les listes de l'onglet actif uniquement le tableau des commandes et l'onglet Toutes les listes toutes les tables dans le classeur. Vous pouvez prendre une table sous l'onglet Tous à l'onglet actif. Pour prendre la table Customers sous l'onglet actif, activez l'onglet Tous, cliquez droit sur la table Customers, et choisissez Afficher dans l'onglet actif dans les options. Ensuite, faites la même chose pour la table des régions.
La figure suivante montre l'onglet actif du volet des tâches Les champs de tableau croisé dynamique. Les clients et les tables Régions sont élargies pour montrer leurs en-têtes de colonne (noms des champs). J'ai également modifié la configuration du volet des tâches (tâche de mise en page du volet). Pour obtenir ce changement, cliquez sur le contrôle des outils et dans le menu déroulant, j'ai choisi Section Champs et zones Section Side-by-Side.
Le volet des tâches Les champs de tableau croisé dynamique, avec trois tables actives sous l'onglet actif.
Maintenant, nous allons mettre en place les relations entre les tables. Choisissez Outils de tableau croisé dynamique ➪ Analyse ➪ relations de calculs. La boîte de dialogue Gérer les relations apparaîtra.
Boîte de dialogue Gérer les relations.
Cliquez sur le bouton Nouveau. La boîte de dialogue Créer une relation apparaîtra.
Création d'une relation entre deux tables. Maintenant, de la table des commandes customerID est liée à customerID de la table à la clientèle.
Cliquez sur OK et vous revenez à la boîte de dialogue Gérer les relations.
Cliquez sur Nouveau à nouveau. Maintenant, nous allons créer une relation entre la table des clients et la table des régions comme la figure suivante.
Création d'une relation entre la table des clients et une table Régions.
La boîte de dialogue Gérer les relations affiche maintenant ces deux relations.
Gérer boîte de dialogue des relations montrant maintenant deux relations.
Remarque
Si vous oubliez de mettre en place les relations de table à l'avance, Excel vous invite à le faire lorsque vous essayez d'ajouter un champ à la table de pivot d'une table de données différentes.
Nous avons établi la relation de table. Maintenant, il suffit de glisser les noms de champs dans les zones appropriées du volet des tâches Les champs de tableau croisé dynamique:
■ Faites glisser le champ total à la zone Valeurs.
■ Faites glisser le champ Année de la zone Colonnes.
■ Faites glisser le champ Région à la zone Lignes.
■ Faites glisser le champ StateName à la zone Lignes.
La figure suivante montre une partie du tableau croisé dynamique. J'ai ajouté deux trancheuses (Maillist et produit) pour permettre le filtrage du tableau croisé dynamique par les clients qui sont sur la liste de diffusion, et par produit.
La table de pivotement, après l'ajout de deux trancheuses pour filtrer le tableau pivotant.
Pointe
Vous pouvez convertir le tableau croisé dynamique aux formules. Pour ce faire, sélectionnez une cellule dans le tableau croisé dynamique et sélectionnez Outils ➪ Analyser ➪ tableau croisé dynamique Outils OLAP ➪ Convertir en formules. Le tableau croisé dynamique sera remplacé par des cellules qui utilisent des formules. Ces formules sont générées avec des fonctions de CUBEMEMBER et CUBEVALUE. Bien que la nouvelle gamme de données ne sera plus un tableau croisé dynamique, les formules seront mis à jour lorsque les données changent.
Télécharger ce fichier exemple pour vous pratiquer
Obtenir une version PDF de cet article!