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

Un modèle de données vous permet d’intégrer des données de plusieurs tables, en créant efficacement une source de données relationnelles dans un classeur Excel. Dans Excel, les modèles de données sont utilisés de manière transparente, fournissant des données tabulaires utilisées dans les tableaux croisés dynamiques et les graphiques croisés dynamiques. Un modèle de données est visualisées sous la forme d’une collection de tables dans une liste de champs, et la plupart du temps, vous ne saurez même jamais qu’il est là.

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). Vous pouvez donc prendre du recul et watch une vidéo, ou suivre notre guide d’apprentissage sur Obtenir & transformer et Power Pivot.

Où se trouve Power Pivot ?

Excel 2016 & Excel pour Microsoft 365 : Power Pivot est inclus dans le ruban.
Excel 2013 - Power Pivot fait partie de l’édition Office Professionnel Plus d’Excel 2013, mais n’est pas activé par défaut. En savoir plus sur le démarrage du complément Power Pivot pour Excel 2013.
Excel 2010 - Téléchargez le complément Power Pivot, puis installez le complément Power Pivot.

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

Excel 2016 & Excel pour Microsoft 365 - Get & Transform (Power Query) a été intégré à Excel sous l’onglet Données.
Excel 2013 : Power Query est un complément inclus dans Excel, mais qui doit être activé. Accédez à Options de > de fichiers > Compléments, puis dans la liste déroulante Gérer en bas du volet, sélectionnez Compléments COM > Accéder. Cochez Microsoft Power Query pour Excel, puis OK pour l’activer. Un onglet Power Query sera ajouté au ruban.
Excel 2010 - Téléchargez et installez le complément Power Query.. Une fois activé, un onglet Power Query est ajouté au ruban.

Mise en route

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

Dans Excel 2016 et Excel pour Microsoft 365, utilisez Data > Obtenir & transformer des données > 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 contenant 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, case activée 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 la procédure. 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 : Introduction à la É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 qui 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 Tutoriel : 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 à Power Pivot > Gérer. Si vous voyez des données de type feuille de calcul, un modèle existe. Consultez : 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 id d’étudiant ou numéro de classe. Le moyen le plus simple consiste à glisser-déplacer ces champs pour les connecter dans la vue de diagramme de Power Pivot.

Accédez à Power Pivot > Gérer.
Sous l’onglet Accueil , sélectionnez Vue de diagramme.
Toutes vos tables importées s’affichent et vous souhaiterez peut-être prendre un certain temps pour les redimensionner en fonction du nombre de champs que chacune possède.
Ensuite, faites glisser le champ de clé primaire d’une table à la suivante. L’exemple suivant est la vue diagramme 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 Students vers le champ Student ID dans la table Notes.
- tbl_Semesters | ID 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 identiques pour créer une relation, mais ils doivent être du même type de données.
- Les connecteurs de la vue de 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 et que cela détermine la façon dont 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 indiquent pas réellement 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 à Relations > données.

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ù vous souhaitez placer le tableau croisé dynamique : une nouvelle feuille de calcul ou l’emplacement actuel.
Cliquez sur OK pour qu’Excel ajoute un tableau croisé dynamique vide avec le volet Liste de champs affiché à droite.

Ensuite, créez un tableau croisé dynamique ou 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 une 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 sous forme de tableau Excel sont préférables.
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 en collant, ou en mettant à jour les données sources et en actualisant le modèle Power Pivot.