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 un tableau croisé dynamique dans Excel et des 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 manière dont toutes les tables sont liées les unes aux autres.
- 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
Exemple 1
Si nous avons trois ensembles de données liés au vendeur: le premier contenant des informations sur les revenus, le 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, 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 une connexion».
- Cliquez sur «Tables» dans la boîte de dialogue qui s'affiche 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 calcul» à 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 dont il faut se rappeler
- 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 avoir aucune valeur en double sur la colonne vers laquelle nous lions.
