Créer un modèle de données dans Excel

S’applique à

Excel pour Microsoft 365 Excel 2024 Excel 2021

Obtenir de l’aide sur Copilot

Un modèle de données vous permet d’intégrer des données provenant de plusieurs tables, créant ainsi une source de données relationnelle dans un classeur Excel. Dans Excel, les modèles de données sont utilisés de manière transparente, ce qui fournit des données tabulaires utilisées dans les tableaux et graphiques croisés dynamiques. Un modèle de données est visualisé sous la forme d’une collection de tables dans une liste de champs. La plupart du temps, vous l’utilisez généralement via la liste de champs de tableau croisé dynamique et vous ne remarquerez peut-être pas sa présence.

Avant de pouvoir commencer à travailler avec le modèle de données, vous devez obtenir des données. Pour cela, nous allons utiliser l’expérience Power Query Get & Transform. Vous pouvez donc prendre du recul et regarder une vidéo, ou suivre notre guide d’apprentissage sur Get & Transform et Power Pivot. Vos données doivent se trouver dans des tableaux (pas seulement des plages de cellules) afin qu’elles puissent être chargées et associées correctement.

Configuration requise

Où se trouve Power Pivot ?

Excel pour Microsoft 365 - Power Pivot est inclus dans le ruban.

Où se trouve Get & Transform (Power Query) ?

Excel pour Microsoft 365 - Get & Transform (Power Query) a été intégré à Excel dans l’onglet Données.

Mise en route

Tout d’abord, vous devez obtenir des données.

Créez ou ouvrez-en un qui ne contient pas les données.
Dans le ruban de Excel pour Microsoft 365, sélectionnez l’onglet Données. Dans la section Obtenir & transformer les données, sélectionnez Obtenir des 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 liées.
Excel vous invite à sélectionner un ou plusieurs tableaux. Si vous souhaitez obtenir plusieurs tables à partir de la même source de données, activez la case à cocher Sélectionner plusieurs éléments dans la case activée.
1. Sélectionnez Transformer. Lorsque vous sélectionnez plusieurs tables, Excel crée automatiquement un modèle de données pour vous. Pour plus d’informations, voir : Créer, charger ou modifier une requête dans Excel (Power Query).
  
  Remarque
  
  Pour ces exemples, nous utilisons un classeur Excel contenant des détails fictifs sur les classes et les notes des étudiants. Vous pouvez télécharger notre exemple de classeur de modèle de données étudiant et le suivre. Vous pouvez également télécharger une version avec un modèle de données complet.
Vous disposez maintenant d’un modèle de données qui contient toutes les tables que vous avez importées et qui seront affichées dans la liste des champs du tableau croisé dynamique.

Remarque

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 semblable à celle d’Excel, où chaque onglet contient des données tabulaires. Voir Obtenir des données à l’aide du complément Power Pivot pour 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, puis cliquez sur Ajouter au modèle de données dans Power Pivot. Vous pouvez le faire si vous souhaitez utiliser des fonctionnalités Power Pivot, telles que des jeux de données filtrés, des colonnes calculées, des champs calculés, des indicateurs de performance clés et des 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 réduction de la taille d’un modèle de données, consultez Créer un modèle de données économe en mémoire à 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 possède un modèle de données ? Accédez àla gestion PowerPivot>. Si vous voyez des données de type feuille de calcul, un modèle existe. Pour en savoir plus, découvrez quelles sources de données sont utilisées dans un modèle de données de classeur .

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, tel que l’ID d’étudiant ou le numéro de classe. Le moyen le plus simple consiste à glisser-déplacer ces champs pour les connecter dans la vue Diagramme de Power Pivot.

Accédez àla gestion PowerPivot>.
Sous l’onglet Accueil , sélectionnez Mode de diagramme.
Toutes vos tables importées seront affichées et vous devrez peut-être prendre le temps de les redimensionner en fonction du nombre de champs de chacune d’elles.
Ensuite, faites glisser le champ de clé primaire d’une table à l’autre. L’exemple suivant est l’affichage en mode Diagramme de nos tables d’étudiants :

Nous avons créé les liens suivants :
- tbl_Students | tbl_Grades de l’étudiant > | Carte d’étudiant
  En d’autres termes, faites glisser le champ ID étudiant de la table Étudiants vers le champ ID étudiant de la table Notes.
- tbl_Semesters | ID de > semestre tbl_Grades | Semestre
- tbl_Classes | Numéro > de cours tbl_Grades | Numéro de classe
Remarque
- Les noms de champs n’ont pas besoin d’être identiques pour créer une relation, mais ils doivent être du même type de données.
- Les liens de la vue Diagramme comportent un « 1 » d’un côté et un « * » de l’autre. Cela signifie qu’il existe une relation un-à-plusieurs entre les tables, ce qui détermine la façon dont les données sont utilisées dans vos tableaux croisés dynamiques. Pour en savoir plus, consultez : Relations entre les tables dans un modèle de données .
- Les connecteurs indiquent uniquement l’existence d’une relation entre les tables. Ils ne vous montreront pas réellement quels champs sont liés les uns aux autres. Pour afficher les liens, accédez à Power Pivot>Gérer les>relations> deconception>Gérer les relations. Dans Excel, vous pouvez accéder àRelations entre les données>.

Utiliser un modèle de données pour créer un tableau 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 souhaité du tableau croisé dynamique : une nouvelle feuille de calcul ou l’emplacement actuel.
Cliquez sur OK. 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 n’importe lequel de leurs champs dans le tableau croisé dynamique. Nous avons déjà créé des relations dans l’exemple de classeur Modèle de données de l’étudiant.

Ajouter des données non liées existantes à un modèle de données

Supposons que vous ayez importé ou copié un grand nombre de données que vous souhaitez utiliser dans un modèle, mais que vous ne les ayez 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 les données mises en forme en tant que tableau Excel sont les meilleures.
Pour ajouter vos données, utilisez l'une de ces approches :
Cliquez sur Power Pivot>Ajouter au modèle de données.
Cliquez sur Insérer> untableau croisé dynamique, puis activez la case activée 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.

Ajouter des données à un tableau PowerPivot

Dans Power Pivot, vous ne pouvez pas ajouter une ligne à un tableau en tapant directement une nouvelle ligne comme vous le faites 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.