Modèle de données Excel (Table des matières)

  • Introduction au modèle de données dans Excel
  • Comment créer un modèle de données dans Excel?

Introduction au modèle de données dans Excel

La fonction de modèle de données d'Excel permet de créer facilement des relations entre des rapports faciles et leurs ensembles de données d'arrière-plan. Cela rend l'analyse des données beaucoup plus facile. Il permet l'intégration de données à partir d'une pléthore de tableaux répartis sur plusieurs feuilles de calcul en établissant simplement des relations entre les colonnes correspondantes. Il fonctionne complètement en arrière-plan et simplifie considérablement les fonctionnalités de création de rapports telles que le tableau croisé dynamique, etc.

Dans notre article, nous tenterons de montrer comment créer un tableau croisé dynamique à partir de deux tableaux en utilisant la fonctionnalité de modèle de données, établissant ainsi une relation entre deux objets de tableau et créant ainsi un tableau croisé dynamique.

Comment créer un modèle de données dans Excel?

Voyons comment créer le modèle de données dans Excel avec quelques 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

  • Nous avons une liste de produits et nous avons un code de rayonnage pour chaque produit. Nous avons besoin d'une table où nous avons la description des étagères ainsi que les codes des étagères. Alors, comment pouvons-nous intégrer les descriptions des rayonnages à chaque code de rayonnage? Beaucoup d'entre nous auraient peut-être recours à VLOOKUP ici, mais nous supprimerons complètement la nécessité d'utiliser VLOOKUP ici à l'aide d'Excel Data Model.

  • Le tableau de gauche est le tableau de données et le tableau de droite est la table de recherche. Comme nous pouvons le voir à partir des données, il est possible de créer une relation basée sur des colonnes communes.

  • Désormais, le modèle de données n'est compatible qu'avec les objets de table. Ainsi, il peut être parfois nécessaire de convertir des ensembles de données en objets de table. Pour ce faire, suivez les étapes ci-dessous.
  1. Cliquez avec le bouton gauche n'importe où dans l'ensemble de données.
  2. Cliquez sur l'onglet Insérer et accédez à Table dans le groupe Tables ou appuyez simplement sur Ctrl + T.
  3. Décochez ou vérifiez que Ma table a l'option En-tête. Dans notre exemple, il a en effet un en-tête. Cliquez sur OK.
  4. Bien que toujours concentré sur le nouveau tableau, nous devons fournir un nom significatif dans la zone Nom (vers la gauche de la barre de formule).

Dans notre exemple, nous avons nommé la table Personnel.

  • Maintenant, nous devons faire le même processus pour la table de recherche et le nommer Shelf Code.

Créer une relation

Donc, tout d'abord, nous allons aller dans l'onglet Données, puis sélectionner Relations dans le sous-groupe Outils de données. Après avoir cliqué sur l'option Relations, au début, car il n'y a pas de relation, nous n'aurons donc rien.

Nous allons d'abord cliquer sur Nouveau pour créer une relation. Nous devrons maintenant fournir les noms de table principale et de recherche dans la liste déroulante, puis mentionner également la colonne qui est commune entre les deux tables afin que nous puissions établir la relation entre les deux tables, à partir de la liste déroulante de colonnes.

  • Maintenant, la table principale est la table qui contient les données. Il s'agit du tableau de données principal - Table5. D'un autre côté, la table Related est la table qui contient les données de recherche - c'est notre table de recherche ShelfCodesTable. La table principale est celle qui est analysée sur la base de la table de recherche qui contient des données de recherche qui rendront les données rapportées à la fin, plus significatives.

  • Ainsi, la colonne commune entre les deux tables est la colonne Shelf Code. C'est ce que nous avons utilisé pour établir la relation entre les deux tables. Pour les colonnes, la colonne (étrangère) est celle qui fait référence au tableau de données où il peut y avoir des valeurs en double. D'un autre côté, la colonne associée (principale) fait référence à la colonne de la table de recherche où nous avons des valeurs uniques. Nous configurons simplement le champ pour rechercher des valeurs à partir de la table de recherche dans la table de données.
  • Une fois que nous avons mis cela en place, Excel créerait une relation entre les deux derrière la scène. Il intègre les données et crée un modèle de données basé sur la colonne commune. Ce n'est pas seulement léger sur les besoins en mémoire, mais aussi beaucoup plus rapide que l'utilisation de RECHERCHEV dans les grands classeurs. Après avoir défini le modèle de données, Excel traiterait ces objets comme des tables de modèle de données au lieu d'une table de feuille de calcul.
  • Maintenant, pour voir ce qu'Excel a fait, nous pouvons cliquer sur Gérer les modèles de données dans Données -> Outils de données.

  • Nous pouvons également obtenir la représentation schématique du modèle de données en changeant la vue. Nous allons cliquer sur l'option Afficher. Cela ouvrira les options d'affichage. Nous sélectionnerons ensuite la vue Diagramme. Ensuite, nous verrons la représentation schématique, montrant les deux tableaux et la relation entre eux, c'est-à-dire la colonne commune - Shelf Code.

  • Le diagramme ci-dessus montre une relation un-à-plusieurs entre les valeurs uniques de la table de recherche et la table de données avec des valeurs dupliquées.
  • Nous allons maintenant devoir créer un tableau croisé dynamique. Pour ce faire, nous allons aller dans l'onglet Insertion puis cliquer sur l'option Tableau croisé dynamique.

Dans la boîte de dialogue Créer un tableau croisé dynamique du tableau croisé dynamique, nous sélectionnerons la source comme «Utiliser le modèle de données de ce classeur».

  • Cela va créer la table Pivot et nous pouvons voir que les deux tables source sont disponibles dans la section source.

  • Nous allons maintenant créer un tableau croisé dynamique montrant le nombre de chaque personne qui a des articles mis en rayon.

  • Nous sélectionnerons Personnel dans la section Lignes du Tableau 5 (tableau de données), suivi de Description (tableau de recherche).

  • Nous allons maintenant faire glisser le code d'étagère du tableau 5 dans la section Valeurs.

  • Maintenant, nous allons ajouter des mois du tableau 5 à la section Lignes.

  • Ou nous pourrions ajouter les mois en tant que filtre et l'ajouter à la section Filtres.

Exemple # 2

  • M. Basu dirige maintenant une usine appelée Basu Corporation. M. Basu tente d'estimer les revenus pour 2019 sur la base des données de 2018.
  • Nous avons un tableau où nous avons les revenus pour 2018 et les revenus ultérieurs à différents niveaux incrémentiels.

  • Ainsi, nous avons le chiffre d'affaires pour 2018 - 1, 5 M $ et la croissance minimale attendue l'année suivante est de 12%. M. Basu veut un tableau qui montrera les revenus à différents niveaux supplémentaires.
  • Nous allons créer le tableau suivant pour les projections à différents niveaux incrémentiels pour 2019.

  • Nous allons maintenant donner à la première ligne des revenus une référence au revenu minimum estimé pour 2019, soit 1, 68 million de dollars.

  • Après avoir utilisé la formule, la réponse est indiquée ci-dessous.

  • Maintenant, nous allons sélectionner la table entière, c'est-à-dire D2: E12, puis aller dans Données -> Prévisions -> Analyse par simulation -> Table de données.

  • Cela ouvrira la boîte de dialogue Table de données. Ici, nous entrerons le pourcentage d'incrément minimum de la cellule B4 dans la cellule d'entrée de colonne. La raison en est que nos pourcentages de croissance estimatifs projetés dans le tableau sont organisés en colonnes.

  • Une fois que nous avons cliqué sur OK, l'analyse de simulation remplira automatiquement le tableau avec les revenus projetés aux différents pourcentages incrémentiels.

Exemple # 3

  • Supposons maintenant que nous ayons le même scénario que ci-dessus, sauf que maintenant nous avons également un autre axe à considérer. Supposons qu'en plus d'afficher les revenus projetés en 2019 sur la base des données de 2018 et du taux de croissance minimum attendu, nous avons désormais également le taux d'actualisation estimé.

  • Tout d'abord, nous aurons un tableau ci-dessous.

  • Nous allons maintenant faire référence au revenu minimum prévu pour 2019, c'est-à-dire la cellule B5 à la cellule D8.

  • Maintenant, nous allons sélectionner la table entière, c'est-à-dire D8: J18, puis aller dans Données -> Prévisions -> Analyse par simulation -> Table de données.

  • Cela ouvrira la boîte de dialogue Table de données. Ici, nous entrerons le pourcentage d'incrément minimum de la cellule B3 dans la cellule d'entrée de colonne. La raison en est que nos pourcentages de croissance estimatifs projetés dans le tableau sont organisés en colonnes. Nous allons maintenant également saisir en plus le pourcentage de remise minimum de la cellule B4 dans la cellule d'entrée de ligne. La raison en est que nos pourcentages de remise projetés dans le tableau sont organisés en rangées.

  • Cliquez sur OK. Cela permettra à l'analyse de simulation de remplir automatiquement le tableau avec les revenus projetés aux différents pourcentages incrémentiels selon les pourcentages de remise.

Choses à retenir sur le modèle de données dans Excel

  • Après avoir réussi à calculer les valeurs de la table de données, une simple annulation c'est-à-dire Ctrl + Z ne fonctionnera pas. Il est cependant possible de supprimer manuellement les valeurs du tableau.
  • Il n'est pas possible de supprimer une seule cellule du tableau. Il est décrit comme un tableau en interne dans Excel, nous devrons donc supprimer toutes les valeurs.
  • Nous devons sélectionner correctement la cellule d'entrée de ligne et la cellule d'entrée de colonne.
  • Contrairement au tableau croisé dynamique, le tableau de données n'a pas besoin d'être actualisé à chaque fois.
  • En utilisant le modèle de données dans Excel, nous pouvons non seulement améliorer les performances, mais aussi simplifier les besoins en mémoire dans les grandes feuilles de calcul.
  • Les modèles de données rendent également notre analyse beaucoup plus simple par rapport à l'utilisation d'un certain nombre de formules complexes dans l'ensemble du classeur.

Articles recommandés

Ceci est un guide du modèle de données dans Excel. Ici, nous discutons de la façon de créer un modèle de données dans Excel avec des exemples pratiques et un modèle Excel téléchargeable. Vous pouvez également consulter nos autres articles suggérés -

  1. Barre de formule dans Excel
  2. Imprimer le quadrillage dans Excel
  3. Regarder la fenêtre dans Excel
  4. Excel SUMIFS avec dates

Catégorie:

Conseils Excel