Excel Power Pivot - Gestion du modèle de données

L'utilisation principale de Power Pivot est sa capacité à gérer les tables de données et les relations entre elles, pour faciliter l'analyse des données de plusieurs tables. Vous pouvez ajouter une table Excel au modèle de données pendant que vous créez un tableau croisé dynamique ou directement à partir du ruban PowerPivot.

Vous pouvez analyser les données de plusieurs tables uniquement lorsque des relations existent entre elles. Avec Power Pivot, vous pouvez créer des relations à partir de la vue de données ou de la vue de diagramme. De plus, si vous avez choisi d'ajouter une table à Power Pivot, vous devez également ajouter une relation.

Ajout de tableaux Excel au modèle de données avec tableau croisé dynamique

Lorsque vous créez un tableau croisé dynamique dans Excel, il est basé uniquement sur une seule table / plage. Si vous souhaitez ajouter plus de tables au tableau croisé dynamique, vous pouvez le faire avec le modèle de données.

Supposons que vous ayez deux feuilles de calcul dans votre classeur -

  • Un contenant les données des vendeurs et les régions qu'ils représentent, dans une table - Vendeur.

  • Un autre contenant les données des ventes, par région et par mois, dans un tableau - Ventes.

Vous pouvez résumer les ventes - par vendeur comme indiqué ci-dessous.

  • Cliquez sur le tableau - Ventes.

  • Cliquez sur l'onglet INSÉRER sur le ruban.

  • Sélectionnez Tableau croisé dynamique dans le groupe Tables.

Un tableau croisé dynamique vide avec les champs de la table des ventes - Région, mois et montant de la commande sera créé. Comme vous pouvez le constater, il y a unMORE TABLES commande sous la liste Champs de tableau croisé dynamique.

  • Cliquez sur PLUS DE TABLES.

le Create a New PivotTablela boîte de message apparaît. Le message affiché est le suivant: Pour utiliser plusieurs tables dans votre analyse, un nouveau tableau croisé dynamique doit être créé à l'aide du modèle de données. Cliquez sur Oui

Un nouveau tableau croisé dynamique sera créé comme indiqué ci-dessous -

Sous Champs de tableau croisé dynamique, vous pouvez observer qu'il existe deux onglets - ACTIVE et ALL.

  • Cliquez sur l'onglet TOUS.

  • Deux tables - Ventes et Vendeur, avec les champs correspondants apparaissent dans la liste Champs de tableau croisé dynamique.

  • Cliquez sur le champ Salesperson dans le tableau Salesperson et faites-le glisser vers la zone ROWS.

  • Cliquez sur le champ Mois dans la table Ventes et faites-le glisser vers la zone ROWS.

  • Cliquez sur le champ Montant de la commande dans le tableau Ventes et faites-le glisser vers la zone ∑ VALEURS.

Le tableau croisé dynamique est créé. Un message apparaît dans les champs de tableau croisé dynamique -Relationships between tables may be needed.

Cliquez sur le bouton CRÉER à côté du message. leCreate Relationship la boîte de dialogue apparaît.

  • Sous Table, sélectionnez Ventes.

  • Sous Column (Foreign) , sélectionnez Région.

  • Sous Related Table, sélectionnez Vendeur.

  • Sous Related Column (Primary) , sélectionnez Région.

  • Cliquez sur OK.

Votre tableau croisé dynamique des deux tableaux sur deux feuilles de calcul est prêt.

En outre, comme Excel l'a indiqué lors de l'ajout du deuxième tableau au tableau croisé dynamique, le tableau croisé dynamique a été créé avec le modèle de données. Pour vérifier, procédez comme suit -

  • Cliquez sur l'onglet POWERPIVOT sur le ruban.

  • Cliquez sur Managedans le groupe Modèle de données. La vue des données de Power Pivot apparaît.

Vous pouvez observer que les deux tables Excel que vous avez utilisées lors de la création du tableau croisé dynamique sont converties en tables de données dans le modèle de données.

Ajout de tableaux Excel d'un autre classeur au modèle de données

Supposons que les deux tables - Vendeur et Ventes se trouvent dans deux classeurs différents.

Vous pouvez ajouter le tableau Excel d'un autre classeur au modèle de données comme suit -

  • Cliquez sur le tableau Ventes.

  • Cliquez sur l'onglet INSÉRER.

  • Cliquez sur Tableau croisé dynamique dans le groupe Tables. leCreate PivotTable la boîte de dialogue apparaît.

  • Dans la zone Table / Plage, tapez Ventes.

  • Cliquez sur Nouvelle feuille de travail.

  • Cochez la case Ajouter ces données au modèle de données.

  • Cliquez sur OK.

Vous obtiendrez un tableau croisé dynamique vide sur une nouvelle feuille de calcul avec uniquement les champs correspondant à la table Sales.

Vous avez ajouté les données de la table Sales au modèle de données. Ensuite, vous devez également obtenir les données de la table Salesperson dans le modèle de données comme suit -

  • Cliquez sur la feuille de calcul contenant le tableau des ventes.

  • 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.

Sous This Workbook Data Model, 1 tables'affiche (il s'agit de la table Sales que vous avez ajoutée précédemment). Vous trouverez également les deux classeurs affichant les tableaux qu'ils contiennent.

  • Cliquez sur Vendeur sous Salesperson.xlsx.

  • Cliquez sur Ouvrir. leImport Data la boîte de dialogue apparaît.

  • Cliquez sur Rapport de tableau croisé dynamique.

  • Cliquez sur Nouvelle feuille de calcul.

Vous pouvez voir que la boîte - Add this data to the Data Modelest coché et inactif. Cliquez sur OK.

Le tableau croisé dynamique sera créé.

Comme vous pouvez le constater, les deux tableaux se trouvent dans le modèle de données. Vous devrez peut-être créer une relation entre les deux tables comme dans la section précédente.

Ajout de tableaux Excel au modèle de données à partir du ruban PowerPivot

Une autre façon d'ajouter des tableaux Excel au modèle de données consiste à so from the PowerPivot Ribbon.

Supposons que vous ayez deux feuilles de calcul dans votre classeur -

  • L'un contenant les données des vendeurs et les régions qu'ils représentent, dans un tableau - Commercial.

  • Un autre contenant les données des ventes, par région et par mois, dans un tableau - Ventes.

Vous pouvez d'abord ajouter ces tableaux Excel au modèle de données, avant d'effectuer toute analyse.

  • Cliquez sur le tableau Excel - Ventes.

  • Cliquez sur l'onglet POWERPIVOT sur le ruban.

  • Cliquez sur Ajouter au modèle de données dans le groupe Tables.

La fenêtre Power Pivot apparaît, avec la table de données Salesperson ajoutée. En outre, un onglet - Table liée apparaît sur le ruban dans la fenêtre Power Pivot.

  • Cliquez sur l'onglet Table liée sur le ruban.

  • Cliquez sur Tableau Excel: Vendeur.

Vous pouvez constater que les noms des deux tables présentes dans votre classeur sont affichés et que le nom Vendeur est coché. Cela signifie que la table de données Vendeur est liée à la table Excel Vendeur.

Cliquez sur Go to Excel Table.

La fenêtre Excel avec la feuille de calcul contenant le tableau du vendeur apparaît.

  • Cliquez sur l'onglet Feuille de calcul Ventes.

  • Cliquez sur le tableau Ventes.

  • Cliquez sur Ajouter au modèle de données dans le groupe Tables du ruban.

Le tableau Excel Sales est également ajouté au modèle de données.

Si vous souhaitez effectuer une analyse basée sur ces deux tables, comme vous le savez, vous devez créer une relation entre les deux tables de données. Dans Power Pivot, vous pouvez le faire de deux manières:

  • Depuis la vue des données

  • Depuis la vue Diagramme

Création de relations à partir de la vue des données

Comme vous le savez, dans l'affichage des données, vous pouvez afficher les tables de données avec des enregistrements sous forme de lignes et des champs sous forme de colonnes.

  • Cliquez sur l'onglet Conception dans la fenêtre Power Pivot.

  • Cliquez sur Créer une relation dans le groupe Relations. leCreate Relationship la boîte de dialogue apparaît.

  • Cliquez sur Ventes dans la zone Tableau. C'est la table à partir de laquelle la relation commence. Comme vous le savez, Colonne doit être le champ présent dans la table associée Vendeur qui contient des valeurs uniques.

  • Cliquez sur Région dans la zone Colonne.

  • Cliquez sur Vendeur dans la zone Table liée associée.

La colonne liée associée est automatiquement remplie avec Region.

Cliquez sur le bouton Créer. La relation est créée.

Création de relations à partir de la vue Diagramme

La création de relations à partir de la vue Diagramme est relativement plus simple. Suivez les étapes indiquées.

  • Cliquez sur l'onglet Accueil dans la fenêtre Power Pivot.

  • Cliquez sur Vue de diagramme dans le groupe Vue.

La vue Diagramme du modèle de données apparaît dans la fenêtre Power Pivot.

  • Cliquez sur Région dans le tableau des ventes. La région du tableau Ventes est mise en surbrillance.

  • Faites glisser vers la région dans le tableau du vendeur. La région du tableau du vendeur est également mise en surbrillance. Une ligne apparaît dans la direction dans laquelle vous avez fait glisser.

  • Une ligne apparaît entre le tableau Ventes et le tableau Vendeur indiquant la relation.

Comme vous pouvez le voir, une ligne apparaît de la table Sales vers la table Salesperson, indiquant la relation et la direction.

Si vous souhaitez connaître le champ qui fait partie d'une relation, cliquez sur la ligne de relation. La ligne et le champ des deux tableaux sont mis en surbrillance.

Gérer les relations

Vous pouvez modifier ou supprimer une relation existante dans le modèle de données.

  • Cliquez sur l'onglet Conception dans la fenêtre Power Pivot.

  • Cliquez sur Gérer les relations dans le groupe Relations. La boîte de dialogue Gérer les relations s'affiche.

Toutes les relations qui existent dans le modèle de données sont affichées.

Pour modifier une relation

  • Cliquez sur une relation.

  • Clique le Editbouton. leEdit Relationship la boîte de dialogue apparaît.

  • Apportez les modifications nécessaires à la relation.

  • Cliquez sur OK. Les changements se reflètent dans la relation.

Pour supprimer une relation

  • Cliquez sur une relation.

  • Cliquez sur le bouton Supprimer. Un message d'avertissement apparaît, indiquant comment les tables affectées par la suppression de la relation affecteront les rapports.

  • Cliquez sur OK si vous êtes sûr de vouloir supprimer. La relation sélectionnée est supprimée.

Actualisation des données Power Pivot

Supposons que vous modifiez les données du tableau Excel. Vous pouvez ajouter / modifier / supprimer les données dans le tableau Excel.

Pour actualiser les données PowerPivot, procédez comme suit:

  • Cliquez sur l'onglet Table liée dans la fenêtre Power Pivot.

  • Cliquez sur Tout mettre à jour.

Le tableau de données est mis à jour avec les modifications apportées au tableau Excel.

Comme vous pouvez le constater, vous ne pouvez pas modifier directement les données des tables de données. Par conséquent, il est préférable de conserver vos données dans des tableaux Excel qui sont liés aux tableaux de données lorsque vous les ajoutez au modèle de données. Cela facilite la mise à jour des données dans les tableaux de données au fur et à mesure que vous mettez à jour les données dans les tableaux Excel.