Modèle de données dans Excel | Comment créer un modèle de données? (avec exemples)

Qu'est-ce que le modèle de données dans Excel?

Le modèle de données dans Excel est un type de tableau de données dans lequel deux ou plus de deux tableaux sont en relation les uns avec les autres via une série de données commune ou plus, dans des tableaux de modèles de données et les données de diverses autres feuilles ou sources se réunissent pour former un modèle unique table qui peut avoir accès aux données de toutes les tables.

Explication

Il permet d'intégrer les données de plusieurs tables en créant des relations basées sur une colonne commune.
Les modèles de données sont utilisés de manière transparente, fournissant des données tabulaires qui peuvent être utilisées dans le tableau croisé dynamique dans Excel et les graphiques croisés dynamiques dans Excel. Il intègre les tableaux, permettant une analyse approfondie à l'aide de tableaux croisés dynamiques, Power Pivot et Power View dans Excel.
Le modèle de données permet de charger des données dans la mémoire d'Excel.
Il est enregistré en mémoire où nous ne pouvons pas le voir directement. Ensuite, Excel peut être chargé de relier les données les unes aux autres à l'aide d'une colonne commune. La partie «Modèle» du modèle de données fait référence à la relation entre toutes les tables.
Data Model peut accéder à toutes les informations dont il a besoin, même lorsque les informations se trouvent dans plusieurs tables. Une fois le modèle de données créé, Excel a les données disponibles dans sa mémoire. Avec les données dans sa mémoire, les données peuvent être consultées de plusieurs manières.

Exemples

Vous pouvez télécharger ce modèle Excel de modèle de données ici - Modèle Excel de modèle de données

Exemple 1

Si nous avons trois ensembles de données liés au vendeur: d'abord contenant des informations sur les revenus, un second contenant les revenus du vendeur et le troisième contenant les dépenses du vendeur.

Pour connecter ces trois ensembles de données et établir une relation avec ceux-ci, nous créons un modèle de données avec les étapes suivantes:

Convertissez les jeux de données en objets Table:

Nous ne pouvons pas créer de relation avec des ensembles de données ordinaires. Le modèle de données fonctionne uniquement avec les objets Excel Tables. Pour faire ça:

Étape 1 - Cliquez n'importe où dans le jeu de données puis, cliquez sur l'onglet «Insertion» puis sur «Table» dans le groupe «Tables».

Étape 2 - Cochez ou décochez l'option: «Ma table a des en-têtes» et cliquez sur OK.

Étape 3 - Avec la nouvelle table sélectionnée, entrez le nom de la table dans le 'Nom de la table' dans le groupe 'Outils'.

Étape 4 - Nous pouvons maintenant voir que le premier ensemble de données est converti en objet «Table». En répétant ces étapes pour les deux autres ensembles de données, nous voyons qu'ils sont également convertis en objets `` Table '' comme ci-dessous:

Ajout des objets 'Table' au modèle de données: via des connexions ou des relations.

Via les connexions

Sélectionnez une table et cliquez sur l'onglet «Données», puis cliquez sur «Connexions».

Dans la boîte de dialogue qui s'affiche, il y a une icône «Ajouter». Développez la liste déroulante «Ajouter» et cliquez sur «Ajouter au modèle de données».

Cliquez sur «Tables» dans la boîte de dialogue qui s'affiche, puis sélectionnez l'une des tables et cliquez sur «Ouvrir».

Ce faisant, un modèle de données de classeur serait créé avec une table et une boîte de dialogue apparaît comme suit:

Donc, si nous répétons ces étapes pour les deux autres tables également, le modèle de données contiendra désormais les trois tables.

Nous pouvons maintenant voir que les trois tableaux apparaissent dans les connexions du classeur.

Via les relations

Créer la relation: une fois que les deux ensembles de données sont des objets Table, nous pouvons créer une relation entre eux. Pour faire ça:

Cliquez sur l'onglet «Données» puis sur «Relations».

Nous verrons une boîte de dialogue vide car il n'y a pas de connexions actuelles.

Cliquez sur «Nouveau» et une autre boîte de dialogue apparaît.

Développez les listes déroulantes «Table» et «Table associée»: la boîte de dialogue «Créer une relation» apparaît pour sélectionner les tables et les colonnes à utiliser pour une relation. Dans le développement de «Tables», sélectionnez l'ensemble de données que nous souhaitons analyser d'une manière ou d'une autre, et dans «Table associée», sélectionnez l'ensemble de données qui a des valeurs de recherche.
La table de recherche dans Excel est la plus petite table dans le cas de relations un à plusieurs et elle ne contient aucune valeur répétée dans la colonne commune. Dans le développement de «Colonne (étrangère)», sélectionnez la colonne commune dans la table principale, dans «Colonne associée (principale)», sélectionnez la colonne commune dans la table associée.

Une fois ces quatre paramètres sélectionnés, cliquez sur «OK». Une boîte de dialogue apparaît comme suit en cliquant sur «OK».

Si nous répétons ces étapes pour relier deux autres tables: Table des revenus avec la table des dépenses, elles sont également liées dans le modèle de données comme suit:

Excel crée désormais la relation dans les coulisses en combinant des données dans le modèle de données en fonction d'une colonne commune: ID du vendeur (dans ce cas).

Exemple # 2

Maintenant, disons dans l'exemple ci-dessus que nous souhaitons créer un tableau croisé dynamique qui évalue ou analyse les objets Table:

Cliquez sur «Insérer» -> «Tableau croisé dynamique».

Dans la boîte de dialogue qui s'affiche, cliquez sur l'option indiquant: «Utiliser une source de données externe», puis cliquez sur «Choisir la connexion».

Cliquez sur «Tables» dans la boîte de dialogue résultante et sélectionnez le modèle de données de classeur contenant trois tables et cliquez sur «Ouvrir».

Sélectionnez l'option «Nouvelle feuille de travail» à l'emplacement et cliquez sur «OK».

Le volet Champs de tableau croisé dynamique affichera les objets de table.

Désormais, les modifications dans le tableau croisé dynamique peuvent être effectuées en conséquence afin d'analyser les objets du tableau selon les besoins.

Par exemple, dans ce cas, si nous souhaitons trouver le revenu total ou le revenu d'un vendeur particulier, un tableau croisé dynamique est créé comme suit:

Ceci est d'une aide immense dans le cas d'un modèle / tableau contenant un grand nombre d'observations.

Ainsi, nous pouvons voir que le tableau croisé dynamique utilise instantanément le modèle de données (en le sélectionnant en choisissant la connexion) dans la mémoire Excel pour afficher les relations entre les tables.

Choses à retenir

En utilisant le modèle de données, nous pouvons analyser les données de plusieurs tables à la fois.
En créant des relations avec le modèle de données, nous surpassons le besoin d'utiliser VLOOKUP, SUMIF, la fonction INDEX et les formules MATCH car nous n'avons pas besoin d'obtenir toutes les colonnes dans une seule table.
Lorsque des ensembles de données sont importés dans Excel à partir de sources externes, les modèles sont créés implicitement.
Les relations de table peuvent être créées automatiquement si nous importons des tables liées qui ont des relations de clé primaire et étrangère.
Lors de la création de relations, les colonnes que nous connectons dans les tables doivent avoir le même type de données.
Avec les tableaux croisés dynamiques créés avec le modèle de données, nous pouvons également ajouter des segments et découper les tableaux croisés dynamiques sur n'importe quel champ souhaité.
L'avantage du modèle de données par rapport aux fonctions LOOKUP () est qu'il nécessite beaucoup moins de mémoire.
Excel 2013 ne prend en charge qu'une à une ou une à plusieurs relations, c'est-à-dire qu'une des tables ne doit pas avoir de valeurs en double sur la colonne à laquelle nous lions.