Excel avancé - Modèle de données

Excel 2013 possède de puissantes fonctionnalités d'analyse de données. Vous pouvez créer un modèle de données, puis créer des rapports interactifs étonnants à l'aide de Power View. Vous pouvez également utiliser les fonctionnalités et capacités de Microsoft Business Intelligence dans Excel, les tableaux croisés dynamiques, Power Pivot et Power View.

Data Modelest utilisé pour créer un modèle dans lequel les données de diverses sources peuvent être combinées en créant des relations entre les sources de données. Un modèle de données intègre les tables, permettant une analyse approfondie à l'aide de tableaux croisés dynamiques, Power Pivot et Power View.

UNE Data Modelest créé automatiquement lorsque vous importez simultanément deux ou plusieurs tables à partir d'une base de données. Les relations de base de données existantes entre ces tables sont utilisées pour créer le modèle de données dans Excel.

Step 1 - Ouvrez un nouveau classeur vierge dans Excel.

Step 2 - Cliquez sur le DATA languette.

Step 3 - Dans le Get External Data groupe, cliquez sur l'option From Access. leSelect Data Source la boîte de dialogue s'ouvre.

Step 4 - Sélectionnez Events.accdb, Fichier de base de données d'accès aux événements.

Step 5 - Le Select Table fenêtre, affichant tous les tables trouvé dans la base de données, apparaît.

Step 6- Les tableaux d'une base de données sont similaires aux tableaux d'Excel. Vérifier la‘Enable selection of multiple tables’et sélectionnez toutes les tables. Puis cliqueOK.

Step 7 - Le Import Datala fenêtre apparaît. Sélectionnez lePivotTable Reportoption. Cette option importe les tableaux dans Excel et prépare un tableau croisé dynamique pour analyser les tableaux importés. Notez que la case à cocher en bas de la fenêtre -‘Add this data to the Data Model’ est sélectionné et désactivé.

Step 8 - Les données sont importées et un PivotTable est créé à l'aide des tables importées.

Vous avez importé les données dans Excel et le Data Modelest créé automatiquement. Désormais, vous pouvez explorer les données des cinq tables, qui ont des relations définies entre elles.

Explorer les données à l'aide du tableau croisé dynamique

Step 1- Vous savez comment ajouter des champs au tableau croisé dynamique et faire glisser des champs dans les zones. Même si vous n'êtes pas sûr du rapport final souhaité, vous pouvez jouer avec les données et choisir le rapport le mieux adapté.

Dans PivotTable Fields, cliquez sur la flèche à côté du tableau - Medalspour le développer pour afficher les champs de cette table. Faites glisser leNOC_CountryRegion champ dans le Medals table à la COLUMNS surface.

Step 2 - Faites glisser Discipline du Disciplines table à la ROWS surface.

Step 3 - Filtre Disciplinepour afficher seulement cinq sports: tir à l'arc, plongée, escrime, patinage artistique et patinage de vitesse. Cela peut être fait soit enPivotTable Fields région, ou du Row Labels filtre dans le tableau croisé dynamique lui-même.

Step 4 - Dans PivotTable Fields, du Medals table, faites glisser Medal vers le VALUES surface.

Step 5 - De la Medals table, sélectionnez Medal à nouveau et faites-le glisser dans le FILTERS surface.

Step 6 - Cliquez sur le bouton de la liste déroulante à droite du Column Étiquettes.

Step 7 - Sélectionnez Value Filters puis sélectionnez Greater Than…

Step 8 - Cliquez OK.

La valeur Filters dialog boîte pour le décompte des médailles is greater than apparaît.

Step 9 - Type 80 dans le Right Field.

Step 10 - Cliquez OK.

Le tableau croisé dynamique affiche uniquement ces régions, qui comptent plus de 80 médailles au total.

Vous pouvez analyser vos données à partir des différentes tables et arriver au rapport spécifique que vous souhaitez en quelques étapes seulement. Cela était possible en raison des relations préexistantes entre les tables de la base de données source. Lorsque vous avez importé simultanément toutes les tables de la base de données, Excel a recréé les relations dans son modèle de données.

Si vous n'importez pas les tables en même temps, ou si les données proviennent de sources différentes ou si vous ajoutez de nouvelles tables à votre classeur, vous devez créer le Relationships parmi les Tables par toi-même.

Créer une relation entre les tables

Les relations vous permettent d'analyser vos collections de données dans Excel et de créer des rapports intéressants et esthétiques à partir des données que vous importez.

Step 1 - Insert une nouvelle feuille de travail.

Step 2- Créez une nouvelle table avec de nouvelles données. Nommez la nouvelle table commeSports.

Step 3 - Vous pouvez maintenant créer une relation entre cette nouvelle table et les autres tables qui existent déjà dans le Data Modeldans Excel. Renommez la feuille Sheet1 enMedals et Sheet2 comme Sports.

Sur le Medals sheet, dans le PivotTable Fields List, Cliquez sur All. Une liste complète des tableaux disponibles s'affiche. La table nouvellement ajoutée -Sports sera également affiché.

Step 4 - Cliquez sur Sports. Dans la liste développée des champs, sélectionnezSports. Excel vous demande de créer une relation entre les tables.

Step 5 - Cliquez sur CREATE. leCreate Relationship la boîte de dialogue s'ouvre.

Step 6- Pour créer la relation, l'une des tables doit avoir une colonne de valeurs uniques et non répétées. dans leDisciplines table, SportIDcolonne a de telles valeurs. La tableSports que nous avons créé a également le SportIDcolonne. DansTable, sélectionnez Disciplines.

Step 7 - Dans Column (Foreign), sélectionnez SportID.

Step 8 - Dans Related Table, sélectionnez Sports.

Step 9 - Dans Related Column (Primary), SportID est sélectionné automatiquement. Cliquez surOK.

Step 10 - Le PivotTable est modifié pour refléter l'ajout du nouveau Data FieldSport. Ajustez l'ordre des champs dans la zone Lignes pour conserver leHierarchy. Dans ce cas,Sport devrait être le premier et Discipline devrait être le prochain, comme Discipline sera imbriqué dans Sport en tant que sous-catégorie.