Comprendre la relation entre deux tables dans Excel

Nous avons déjà souligné sur ce blogue, tous les avantages pour les usagers d’Excel, d’utiliser PowerPivot pour creér des solutions BI (Business Intelligence) autonomes. Mais pour tirer profit des nombreuses possibilités offertes par PowerPivot, il faut maîtriser quelques concepts de base au niveau de la modélisation de données. Notamment, il faut bien comprendre comment concevoir un modèle de données efficace en liant entre elles différentes tables de données. Un des modèles les plus répandus, est certainement le shéma en étoile, qui propose de placer la table de faits (mesures) au centre et les tables de dimensions, tout autour. Mais avant de parler de modèles de données, parlons d’abord de ce qu’il faut savoir concernant les relations entre les tables de données dans Excel.

Créer une relation entre deux ou plusieurs tables

D’abord, rappelons qu’il est possible, grâce à PowerPivot, d’importer des données provenant de différentes source (Excel, Access, Texte, Web, SQL server, Oracle, etc.) et de concevoir un modèle de données pour fins d’analyse financière, de reporting ou de tableau de bord, en liant ces tables de données à l’aide de clés communes. Il est possible de créer ces relations directement à partir de PowerPivot ou, dans Excel 2013, directement à partir d’Excel. Pour voir comment créer ces relations à partir d’Excel, je vous invite à lire l’article suivant: Excel: Créer un modèle de données.

Voici comment se définit une relation entre deux tables:

Table source: Table de données à partir de laquelle on veut créer une relation. Dans notre exemple, il s’agit de la table des ventes internet (FactInternetSales). Cette table source contient souvent des données transactionnnelles et dans cette table, on retrouvera normalement plusieurs doublons dans la colonne servant à créer la relation.

Colonne externe: Colonne dans la table de données source qui contient les valeurs à rechercher dans l’autre table. Si on liait les tables entre elles à l’aide d’un Recherchev (Vlookup), il s’agirait du premier paramètre du Recherchev (Vlookup).

Table liée: Table comprenant les données recherchées. Si on liait les tables entre elles à l’aide d’un Rercherchev (Vlookup), il s’agirait du deuxième paramètre du Recherchev.

Colonne associée principale: Colonne dans la table liée, qui contient les données recherchées. Si on liait les tables entre elles à l’aide d’un Recherchev (Vlookup), il s’agirait du troisième paramètre du Recherchev.

Vous avez des sources de données volumineuses à rassembler et analyser dans Excel ? Apprenez à automatiser tout le processus d’importation, de transformation et de modélisation  des données avec nos formations sur Power Query et Power Pivot dans Excel.

Le diagramme ci-bas, montre bien la relation qui part de la table source “FactInternetSales” jusqu’à la table liée “DimCustomer”. La clé commune entre les deux tables est la colonne intitulée “CustomerKey”, dans les deux tables (à noter que ces deux colonnes auraient pu porter des noms différents, pour autant que leurs données correspondent).

Tableau croisé dynamique sur des tables liées

Puisque nous avons lié les deux tables de données entre elles, nous pouvons maintenant produire des tableaux croisés dynamiques à l’aide des données croisées des deux tables. Par exemple, ci-bas, nous présentons les ventes par niveau de revenus des clients. Les ventes sont dans la table de faits (FactInternetSales), alors que les niveaux de revenus sont des précisions par rapport aux clients, comprises dans la table de données clients (DimCustomer).

Pourquoi ne pas lier les tables avec un Recherchev (Vlookup)?

Il existe plusieurs raisons pour lesquelles on choisirait d’utiliser un modèles de données (tables liées via PowerPivot) plutôt que la fonction Recherchev (Vlookup) pour lier des tables entre elles.

• Pour lier des tables de données avec plus de 1 million de lignes (limite de capacité d’Excel)
• Pour éviter de calculer plusieurs fois la même information via la fonction Recherchev (Vlookup)
  • Permet d’augmenter l’efficacité de traitement (temps de traitement)
  • Permet d’optimiser l’espace de storage
• Pour avoir accès à des fonctions DAX avancées afin de créer des mesures plus riches dans nos analyses de données croisées
• Pour lier plus facilement des tables de données provenant de diverses sources de données
• Pour avoir accès à toutes les colonnes de données d’une table liée (autrement accessibles via de multiples Recherchev (Vlookup))
• Pour avoir accès à des données d’une table secondaire (liée à une table liée), qui serait autrement accessible via de multiples Recherchev (Vlookup)

Formation complémentaire

Pour une introduction à la modélisation de données avec Power Pivot, un complément BI d’Excel, qui permet d’importer, de croiser et de bonifier diverses sources de données externes pour finalement les analyser à l’aide de tableaux croisés dynamiques, suivez la formation Excel – Introduction à Power Pivot et aux modèles de données.

Voici quelques commentaires d’apprenants ayant suivi cette formation :

La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.