Comment créer des relations entre plusieurs tables à l’aide d’un modèle de données dans Excel
Excel est un outil puissant pour l'analyse des données et l'automatisation ultérieure lors de la gestion de grands ensembles de données. Vous pourriez passer beaucoup de temps à analyser des tonnes de données à l'aide de RECHERCHEV, INDEX-MATCH, SUMIF, etc.
Grâce au modèle de données Excel, vous pouvez gagner un temps précieux grâce aux rapports de données automatiques. Découvrez avec quelle facilité vous pouvez attribuer une relation entre deux tables à l'aide du modèle de données et une illustration d'une telle relation dans un tableau croisé dynamique dans la section suivante.
Les exigences de base
Vous aurez besoin de Power Pivot et de Power Query (Get & Transform) pour accomplir plusieurs tâches lors de la création du modèle de données Excel . Voici comment vous pouvez obtenir ces fonctionnalités dans votre classeur Excel:
Comment obtenir Power Pivot
1. Excel 2010: vous devrez télécharger le complément Power Pivot de Microsoft , puis l'installer pour votre programme Excel sur votre ordinateur.
2. Excel 2013: l' édition Office Professionnel Plus d'Excel 2013 inclut Power Pivot. Mais vous devez l'activer avant la première utilisation. Voici comment:
- Cliquez sur Fichier sur le ruban d'un classeur Excel.
- Cliquez ensuite sur Options pour ouvrir les Options Excel .
- Maintenant, cliquez sur Compléments .
- Sélectionnez Compléments COM en cliquant sur le menu déroulant de la zone Gérer .
- Cliquez sur Aller , puis cochez la case Microsoft Power Pivot pour Excel .
3. Excel 2016 et versions ultérieures: vous trouverez le menu Power Pivot sur le ruban .
Comment obtenir Power Query (Obtenir et transformer)
1. Excel 2010: vous pouvez télécharger le complément Power Query à partir de Microsoft . Après l'installation, Power Query apparaîtra sur le ruban .
2. Excel 2013: vous devez activer Power Query en suivant les mêmes étapes que vous venez de faire pour rendre Power Pivot fonctionnel dans Excel 2013.
3. Excel 2016 et versions ultérieures: vous pouvez trouver Power Query (Get & Transform) en accédant à l'onglet Données du ruban Excel.
Créer un modèle de données en important des données dans le classeur Excel
Pour ce didacticiel, vous pouvez obtenir des exemples de données pré-formatées auprès de Microsoft:
Télécharger : exemples de données sur les élèves (données uniquement) | Exemple de données sur les élèves (modèle complet)
Vous pouvez importer une base de données avec plusieurs tables liées à partir de nombreuses sources telles que des classeurs Excel, Microsoft Access, des sites Web, SQL Server, etc. Ensuite, vous devez formater l'ensemble de données afin qu'Excel puisse l'utiliser. Voici les étapes que vous pouvez essayer:
1. Dans Excel 2016 et les éditions ultérieures, cliquez sur l'onglet Données et sélectionnez Nouvelle requête .
2. Vous trouverez plusieurs façons d'importer des données à partir de sources externes ou internes. Choisissez celui qui vous convient.
3. Si vous utilisez l'édition d'Excel 2013, cliquez sur Power Query sur le ruban , puis sélectionnez Obtenir des données externes pour choisir les données à importer.
4. Vous verrez la boîte de dialogue Navigateur dans laquelle vous devez choisir les tables à importer. Cliquez sur la case à cocher Sélectionner plusieurs éléments pour choisir plusieurs tables à importer.
5. Cliquez sur Charger pour terminer le processus d'importation.
6. Excel créera un modèle de données pour vous à l'aide de ces tableaux. Vous pouvez voir les en-têtes de colonne de tableau dans les listes de champs de tableau croisé dynamique .
Vous pouvez également utiliser les fonctions Power Pivot telles que les colonnes calculées, les indicateurs de performance clés, les hiérarchies, les champs calculés et les ensembles de données filtrés à partir du modèle de données Excel. Pour cela, vous devrez générer un modèle de données à partir d'une seule table. Vous pouvez essayer ces étapes:
1. Mettez en forme vos données dans un modèle tabulaire en sélectionnant toutes les cellules contenant des données, puis cliquez sur Ctrl + T.
2. Sélectionnez maintenant la table entière, puis cliquez sur l'onglet Power Pivot sur le ruban .
3. Dans la section Tables , cliquez sur Ajouter au modèle de données .
Excel créera des relations de table entre les données associées du modèle de données. Pour cela, il doit y avoir des relations de clé primaire et étrangère dans les tables importées.
Excel utilise les informations de relation de la table importée comme base pour générer des connexions entre les tables dans un modèle de données.
Créer des relations entre les tables du modèle de données
Maintenant que vous avez un modèle de données dans votre classeur Excel, vous devez définir des relations entre les tables pour créer des rapports significatifs. Vous devez attribuer un identifiant de champ unique ou une clé primaire à chaque table, comme l'ID de semestre, le numéro de classe, l'ID d'étudiant, etc.
La fonctionnalité d'affichage de diagramme de Power Pivot vous permettra de faire glisser et de déposer ces champs pour créer une relation. Suivez ces étapes pour créer des liens de table dans le modèle de données Excel:
1. Sur le ruban du classeur Excel, cliquez sur le menu Power Pivot .
2. Maintenant, cliquez sur Gérer dans la section Modèle de données . Vous verrez l'éditeur Power Pivot comme indiqué ci-dessous:
3. Cliquez sur le bouton Vue Diagramme situé dans la section Vue de l'onglet Accueil Power Pivot. Vous verrez les en-têtes de colonne du tableau regroupés en fonction du nom du tableau.
4. Vous pourrez désormais faire glisser et déposer l'identifiant de champ unique d'une table à une autre. Voici le schéma des relations entre les quatre tables du modèle de données Excel:
Ce qui suit décrit le lien entre les tables:
- Table des étudiants | Carte d'étudiant pour déposer les notes | Carte d'étudiant
- Semestres de table | Identifiant du semestre pour la table des notes | Semestre
- Classes de table | Numéro de classe à table Grades | Identifiant de classe
5. Vous pouvez créer des relations en choisissant une paire de colonnes de valeurs uniques. S'il y a des doublons, vous verrez l'erreur suivante:
6. Vous remarquerez Star (*) d'un côté et One (1) de l'autre dans la vue Diagramme des relations. Il définit qu'une relation un-à-plusieurs existe entre les tables.
7. Dans l'éditeur Power Pivot, cliquez sur l'onglet Conception , puis sélectionnez Gérer les relations pour savoir quels champs établissent les connexions.
Générer un tableau croisé dynamique à l'aide du modèle de données Excel
Vous pouvez maintenant créer un tableau croisé dynamique ou un graphique croisé dynamique pour visualiser vos données à partir du modèle de données Excel. Un classeur Excel peut contenir un seul modèle de données, mais vous pouvez continuer à mettre à jour les tables.
Étant donné que les données changent au fil du temps, vous pouvez continuer à utiliser le même modèle et gagner du temps lorsque vous travaillez avec le même ensemble de données. Vous remarquerez plus de gain de temps lorsque vous travaillez sur des données en milliers de lignes et de colonnes. Pour créer un rapport basé sur un tableau croisé dynamique, procédez comme suit:
1. Dans l'éditeur Power Pivot, cliquez sur l'onglet Accueil .
2. Sur le ruban , cliquez sur Tableau croisé dynamique .
3. Choisissez n'importe lequel entre Nouvelle feuille de calcul ou Feuille de calcul existante.
4. Sélectionnez OK . Excel ajoutera un tableau croisé dynamique qui affichera le volet Liste des champs sur la droite.
Voici une vue holistique d'un tableau croisé dynamique créé à l'aide du modèle de données Excel pour les exemples de données d'élève utilisés dans ce didacticiel. Vous pouvez également créer des tableaux croisés dynamiques ou des graphiques professionnels à partir de données volumineuses à l'aide de l'outil de modèle de données Excel.
Transformez des ensembles de données complexes en rapports simples à l'aide du modèle de données d'Excel
Le modèle de données Excel utilise les avantages de la création de relations entre les tables pour produire des tableaux croisés dynamiques ou des graphiques significatifs à des fins de rapport de données.
Vous pouvez mettre à jour en permanence le classeur existant et publier des rapports sur les données mises à jour. Vous n'avez pas à modifier les formules ni à consacrer du temps à faire défiler des milliers de colonnes et de lignes à chaque mise à jour des données source.