Créer un modèle de données dans Excel
Avant de commencer à utiliser le modèle de données, vous devez obtenir des données. Pour cela, nous allons utiliser l’expérience Get & Transform (Power Query), afin que vous puissiez prendre du recul et regarder une vidéo, ou suivre notre guide d’apprentissage sur Get & Transform and Power Pivot.
Prise en main
- Dans Excel 2016 et Excel pour Microsoft 365, utilisez Données > Obtenir les données > pour importer des données à partir d’un nombre quelconque de sources de données externes, telles qu’un fichier texte, un classeur Excel, un site web, Microsoft Access, SQL Server ou une autre base de données relationnelle qui contient plusieurs tables associées.
Dans Excel 2013 et 2010, accédez à Power Query > Obtenir des données externes, puis sélectionnez votre source de données.
- Excel vous invite à sélectionner une table. Si vous souhaitez obtenir plusieurs tables à partir de la même source de données, vérifiez l’option Activer la sélection de plusieurs tables . Lorsque vous sélectionnez plusieurs tables, Excel crée automatiquement un modèle de données pour vous.
Remarque : Pour ces exemples, nous utilisons un classeur Excel avec des détails fictifs sur les classes et les notes des étudiants. Vous pouvez télécharger notre exemple de classeur Student Data Model et suivre. Vous pouvez également télécharger une version avec un modèle de données terminé..
- Sélectionnez une ou plusieurs tables, puis cliquez sur Charger.
Si vous devez modifier les données sources, vous pouvez choisir l’option Modifier . Pour plus d’informations, consultez : Présentation du Éditeur de requête (Power Query).
Vous disposez maintenant d’un modèle de données qui contient toutes les tables que vous avez importées, et elles seront affichées dans la liste des champs de tableau croisé dynamique.
Remarques :
- Les modèles sont créés implicitement lorsque vous importez deux ou plusieurs tables simultanément dans Excel.
- Les modèles sont créés explicitement lorsque vous utilisez le complément Power Pivot pour importer des données. Dans le complément, le modèle est représenté dans une disposition à onglets similaire à Excel, où chaque onglet contient des données tabulaires. Consultez Obtenir des données à l’aide du complément Power Pivotpour découvrir les principes de base de l’importation de données à l’aide d’une base de données SQL Server.
- Un modèle peut contenir une seule table. Pour créer un modèle basé sur une seule table, sélectionnez la table et cliquez sur Ajouter au modèle de données dans Power Pivot. Vous pouvez procéder de la sorte si vous souhaitez utiliser des fonctionnalités Power Pivot, comme les datasets filtrés, les colonnes calculées, les champs calculés, les indicateurs de performance clés et les hiérarchies.
- Les relations entre les tables peuvent être créées automatiquement si vous importez des tables associées ayant des relations de clé primaire et de clé étrangère. Excel peut généralement utiliser les informations des relations importées comme base pour les relations entre les tables dans le modèle de données.
- Pour obtenir des conseils sur la façon de réduire la taille d’un modèle de données, consultez Créer un modèle de données à mémoire efficace à l’aide d’Excel et de Power Pivot.
- Pour plus d’informations, consultez didacticiel : Importer des données dans Excel et créer un modèle de données.
Conseil : Comment savoir si votre classeur a un modèle de données ? Accédez à Power Pivot > Gérer. Si vous voyez des données de type feuille de calcul, un modèle existe. Voir : Découvrez les sources de données utilisées dans un modèle de données de classeur pour en savoir plus.
Créer des relations entre vos tables
L’étape suivante consiste à créer des relations entre vos tables afin de pouvoir extraire des données de n’importe laquelle d’entre elles. Chaque table doit avoir une clé primaire ou un identificateur de champ unique, comme l’ID d’étudiant ou le numéro de classe. Le moyen le plus simple consiste à faire glisser et supprimer ces champs pour les connecter dans la vue diagramme de Power Pivot.
- Accédez à Power Pivot > Gérer.
- Sous l’onglet Accueil , sélectionnez Affichage diagramme.
- Toutes vos tables importées seront affichées et vous voudrez peut-être prendre un certain temps pour les redimensionner en fonction du nombre de champs dont chacune d’elles dispose.
- Ensuite, faites glisser le champ de clé primaire d’une table vers l’autre. L’exemple suivant est la vue schématique de nos tables d’étudiants :
Nous avons créé les liens suivants :
- tbl_Students | ID d’étudiant > tbl_Grades | ID d’étudiant
En d’autres termes, faites glisser le champ ID d’étudiant de la table Étudiants vers le champ ID d’étudiant dans la table Notes.
- tbl_Semesters | ID du semestre > tbl_Grades | Semestre
- tbl_Classes | Numéro de classe > tbl_Grades | Numéro de classe
Remarques :
- Les noms de champs n’ont pas besoin d’être les mêmes pour créer une relation, mais ils doivent être du même type de données.
- Les connecteurs de la vue diagramme ont un « 1 » d’un côté et un « * » de l’autre. Cela signifie qu’il existe une relation un-à-plusieurs entre les tables, qui détermine comment les données sont utilisées dans vos tableaux croisés dynamiques. Consultez : Relations entre les tables dans un modèle de données pour en savoir plus.
- Les connecteurs indiquent uniquement qu’il existe une relation entre les tables. Ils ne vous indiqueront pas quels champs sont liés les uns aux autres. Pour afficher les liens, accédez à Power Pivot > Gérer > Conception > Relations > Gérer les relations. Dans Excel, vous pouvez accéder à Data > Relationships.
- tbl_Students | ID d’étudiant > tbl_Grades | ID d’étudiant
Utiliser un modèle de données pour créer un tableau croisé dynamique ou un graphique croisé dynamique
Un classeur Excel ne peut contenir qu’un seul modèle de données, mais ce modèle peut contenir plusieurs tables qui peuvent être utilisées à plusieurs reprises dans le classeur. Vous pouvez ajouter d’autres tables à un modèle de données existant à tout moment.
- Dans Power Pivot, accédez à Gérer.
- Sous l’onglet Accueil , sélectionnez Tableau croisé dynamique.
- Sélectionnez l’emplacement où placer le tableau croisé dynamique : une nouvelle feuille de calcul ou l’emplacement actuel.
- Cliquez sur OK et Excel ajoute un tableau croisé dynamique vide avec le volet Liste de champs affiché à droite.
Ensuite, créez un tableau croisé dynamique ou créez un graphique croisé dynamique. Si vous avez déjà créé des relations entre les tables, vous pouvez utiliser l’un de leurs champs dans le tableau croisé dynamique. Nous avons déjà créé des relations dans l’exemple de classeur Student Data Model.
Ajouter des données non liées existantes à un modèle de données
Supposons que vous avez importé ou copié un grand nombre de données que vous souhaitez utiliser dans un modèle, mais que vous ne les avez pas ajoutées au modèle de données. L’envoi de nouvelles données dans un modèle est plus simple que vous ne le pensez.
- Commencez par sélectionner n’importe quelle cellule dans les données que vous souhaitez ajouter au modèle. Il peut s’agir de n’importe quelle plage de données, mais il est préférable de mettre en forme des données sous la forme d’un tableau Excel .
- Pour ajouter vos données, utilisez l’une de ces approches :
- Cliquez sur Power Pivot > Charger dans le modèle de données.
- Cliquez sur Insérer > Tableau croisé dynamique, puis activez Ajouter ces données au modèle de données dans la boîte de dialogue Créer un tableau croisé dynamique.
La plage ou la table est désormais ajoutée au modèle en tant que table liée. Pour en savoir plus sur l’utilisation des tables liées dans un modèle, consultez Ajouter des données à l’aide de tables liées Excel dans Power Pivot.
Ajout de données à une table Power Pivot
Dans Power Pivot, vous ne pouvez pas ajouter de ligne dans une table en tapant directement dans une nouvelle ligne comme vous pouvez le faire dans une feuille de calcul Excel. Toutefois, vous pouvez ajouter des lignes en copiant et collant, ou en mettant à jour les données sources et en actualisant le modèle Power Pivot.
Voir aussi
Présentation de l’Éditeur de requête (Power Query)
Créer un modèle de données efficace en mémoire à l’aide d’Excel et de Power Pivot
Tutoriel : Importer des données dans Excel et créer un modèle de données
Identifier les sources de données utilisées dans un modèle de données de classeur