Analyse avancée des données - Modèle de données

Le modèle de données est disponible dans Excel 2013 et les versions ultérieures. Dans Excel, vous pouvez utiliser le modèle de données pour intégrer des données de plusieurs tables dans le classeur actuel et / ou des données importées et / ou des sources de données connectées au classeur via des connexions de données.

Avec un modèle de données, vous pouvez créer des relations entre les tables. Le modèle de données est utilisé de manière transparente dans les rapports de tableau croisé dynamique, graphique croisé dynamique, PowerPivot et Power View.

Création d'un modèle de données lors de l'importation de données

Lorsque vous importez des données à partir de bases de données relationnelles comme la base de données Microsoft Access qui contiennent plusieurs tables associées, le modèle de données est automatiquement créé si vous importez plusieurs tables à la fois.

Vous pouvez éventuellement ajouter des tables au modèle de données, lorsque vous importez des données à partir des sources de données suivantes:

• Bases de données relationnelles, une table à la fois
• Fichiers texte
• Classeurs Excel

Par exemple, lorsque vous importez des données à partir d'un classeur Excel, vous pouvez observer l'option Add this data to the Data Model, avec une case à cocher activée.

Si vous souhaitez ajouter les données que vous importez au modèle de données, cochez la case.

Création d'un modèle de données à partir de tableaux Excel

Vous pouvez créer le modèle de données à partir de tableaux Excel à l'aide des commandes PowerPivot. Vous apprendrez PowerPivot en détail dans les chapitres suivants.

Toutes les commandes du modèle de données sont disponibles sous l'onglet PowerPivot du ruban. Vous pouvez ajouter des tableaux Excel au modèle de données avec ces commandes.

Considérez le classeur de données de vente suivant, dans lequel vous disposez d'une feuille de calcul Catalogue de produits contenant le produit, l'ID de produit et le prix. Vous disposez de quatre feuilles de calcul pour les ventes dans 4 régions - Est, Nord, Sud et Ouest.

Chacune de ces quatre feuilles de travail contient le nombre d'unités vendues et le montant total pour chacun des produits chaque mois. Vous devez calculer le montant total pour chacun des produits dans chaque région et le montant total des ventes dans chaque région.

Les étapes suivantes vous permettent d'arriver aux résultats souhaités -

• Commencez par créer le modèle de données.
• Cliquez dans la feuille de calcul du catalogue de produits.
• Cliquez sur l'onglet POWERPIVOT sur le ruban.
• Cliquez sur Ajouter au modèle de données. La boîte de dialogue Créer une table s'affiche.
• Sélectionnez la plage de table.
• Cochez la case Mon tableau a des en-têtes. Cliquez sur OK.

Une nouvelle fenêtre - PowerPivot pour Excel - <votre nom de fichier Excel> apparaît.

Le message suivant apparaît au centre de la fenêtre vide -

La table Product Backlog que vous avez ajoutée au modèle de données apparaît sous forme de feuille dans la fenêtre PowerPivot. Chaque ligne du tableau est un enregistrement et vous pouvez faire des allers-retours entre les enregistrements à l'aide des flèches gauche et droite en bas de la fenêtre.

• Cliquez sur l'onglet Table liée dans la fenêtre PowerPivot.
• Cliquez sur Aller au tableau Excel.

La fenêtre de données Excel apparaît.

• Cliquez sur l'onglet de la feuille de calcul - Est.
• Cliquez sur l'onglet POWERPIVOT sur le ruban.
• Cliquez sur Ajouter au modèle de données.

Une autre feuille apparaît dans la fenêtre PowerPivot affichant la table Est.

Répétez pour les feuilles de calcul - Nord, Sud et Ouest. En tout, vous avez ajouté cinq tables au modèle de données. Votre fenêtre PowerPivot ressemble à ceci:

Création de relations entre les tables

Si vous souhaitez effectuer des calculs sur les tables, vous devez d'abord définir les relations entre elles.

• Cliquez sur l'onglet Accueil du ruban dans la fenêtre PowerPivot. Comme vous pouvez le constater, les tableaux sont affichés dans la vue des données.

• Cliquez sur Vue Diagramme.

Les tableaux apparaissent dans la vue Diagramme. Comme vous le constatez, certaines des tables peuvent être hors de la zone d'affichage et tous les champs des tables peuvent ne pas être visibles.

• Redimensionnez chaque table pour afficher tous les champs de cette table.
• Faites glisser et organisez les tableaux de manière à ce qu'ils s'affichent tous.

• Dans le tableau Est, cliquez sur ID produit.
• Cliquez sur l'onglet Conception sur le ruban.
• Cliquez sur Créer une relation. La boîte de dialogue Créer une relation s'affiche.

Dans la zone sous Table, l'Est est affiché. Dans la zone sous Colonne, l'ID de produit s'affiche.

• Dans la zone sous Table de recherche associée, sélectionnez Catalogue de produits.
• L'ID de produit apparaît dans la zone sous Colonne de recherche associée.
• Cliquez sur le bouton Créer.

La ligne représentant la relation entre les tables East et Product Backlog apparaît.

• Répétez les mêmes étapes pour les tables - Nord, Sud et Ouest. Les lignes de relation apparaissent.

Synthèse des données dans les tableaux du modèle de données

Désormais, vous êtes prêt à résumer les données de vente de chacun des produits de chaque région en quelques étapes seulement.

• Cliquez sur l'onglet Accueil.
• Cliquez sur Tableau croisé dynamique.
• Sélectionnez Tableau croisé dynamique dans la liste déroulante.

La boîte de dialogue Créer un tableau croisé dynamique apparaît dans la fenêtre des tableaux Excel. Sélectionnez Nouvelle feuille de calcul.

Dans une nouvelle feuille de calcul, un tableau croisé dynamique vide apparaît. Comme vous pouvez le constater, la liste des champs contient toutes les tables du modèle de données avec tous les champs affichés.

• Sélectionnez l'ID de produit dans le tableau 1 (catalogue de produits).

• Sélectionnez Montant total dans les quatre autres tableaux.

• Pour chacun des champs dans ∑ Valeurs, modifiez le nom personnalisé dans les paramètres de champ de valeur pour afficher les noms de région sous forme d'étiquettes de colonne.

La somme du montant total sera remplacée par l'étiquette que vous donnez. Le tableau croisé dynamique avec les valeurs résumées de toutes les tables de données vous montre les résultats requis.

Ajout de données au modèle de données

Vous pouvez ajouter une nouvelle table de données au modèle de données ou de nouvelles lignes de données aux tables existantes dans le modèle de données.

Ajoutez une nouvelle table de données au modèle de données en procédant comme suit.

• Cliquez sur l'onglet DONNÉES sur le ruban.

• Cliquez sur Connexions existantes dans le groupe Obtenir des données externes. La boîte de dialogue Connexions existantes s'affiche.

• Cliquez sur l'onglet Tables. Les noms de toutes les tables du classeur seront affichés.

• Cliquez sur le nom de la table que vous souhaitez ajouter au modèle de données.

Cliquez sur le bouton Ouvrir. La boîte de dialogue Importer des données s'affiche.

Comme vous le savez, lors de l'importation de la table de données, elle est automatiquement ajoutée au modèle de données. La table nouvellement ajoutée apparaît dans la fenêtre PowerPivot.

Ajoutez de nouvelles lignes de données aux tables existantes dans le modèle de données.

Actualisez la connexion de données. Les nouvelles lignes de données de la source de données sont ajoutées au modèle de données.