Créer une relation entre deux tables

Une relation est une association entre deux tables de données, basée sur des données correspondantes dans chaque table. Créer une relation vous permet de générer des tableaux croisés dynamiques et d'autres rapports qui utilisent des champs de chaque table, même si elles proviennent de sources différentes. Par exemple, si vous avez importé des données sales, vous pouvez ajouter et associer données Time Intelligence data pour analyser les modèles de ventes par année et mois.

Si vous importez des tables associées à partir d'une base de données relationnelle, Excel crée parfois ces relations dans un modèle de données qu'il génère en arrière-plan. Vous savez s'il existe une relation lorsque vous faites glisser des champs depuis des tables différentes dans la liste de champs du tableau croisé dynamique. Si vous n'êtes pas invité à créer une relation, cela signifie qu'Excel contient déjà les informations de relation nécessaires pour associer les données.

Pour tous les autres cas, vous devez créer des relations manuellement.

1. Vérifiez que le classeur contient au moins deux tables, et que chaque table possède une colonne qui peut être mappée à une colonne dans une autre table.

Vous pouvez créer des tables à l’aide de données figurant déjà dans une feuille de calcul ou en important des données externes sous forme de table dans une nouvelle feuille de calcul. Attribuez à chaque table un nom significatif pour les identifier facilement lors de la création d’une relation. Dans Outils de table, cliquez sur Création > Nom de la table pour entrer un nom.

Vérifiez qu’au moins une des colonnes de l’une des tables possèdent des valeurs de données uniques non dupliquées pour qu’il n’y ait pas de confusion lors de l’association des lignes. Par exemple, pour associer les ventes client à Time Intelligence, les deux tables doivent comprendre des dates dans le même format (01/01/2012, par exemple), et une table au moins (Time Intelligence) doit répertorier chaque date une seule fois dans la colonne.

2. Cliquez sur Données Relations pour ouvrir la boîte de dialogue Gérer les relations. Si l'option Relations est grisée, cela signifie que votre classeur ne contient qu’une seule table.
3. Cliquez sur Nouveau.
4. Dans la boîte de dialogue Créer une relation, pour Table, cliquez sur la flèche vers le bas et sélectionnez une table dans la liste déroulante. Dans une relation « un-à-plusieurs », cette table doit être du côté « plusieurs ». Dans notre exemple client et Time Intelligence, vous choisissez la table customer sales en premier, car plusieurs ventes peuvent avoir lieu un jour donné.
5. Pour Colonne (clé étrangère), sélectionnez la colonne qui contient les données mises en rapport avec Colonne associée (primaire). Par exemple, si vous aviez une colonne de dates dans les deux tables, choisissez à présent cette colonne.
6. Pour Table associée, sélectionnez une table qui comporte au moins une colonne de données mise en rapport avec la table que vous venez de sélectionner pour Table.
7. Pour Colonne associée (clé primaire), sélectionnez une colonne qui comporte des valeurs uniques qui correspondent aux valeurs dans la colonne que vous avez sélectionnée pour Colonne.
8. Cliquez sur OK.

 Remarques 

• La création de relations est similaire à l'utilisation des VLOOKUP : vous avez besoin de colonnes contenant des données correspondantes pour qu'Excel puisse créer des références croisées entre les lignes d'une table et celles d'une autre table. Dans l'exemple Time Intelligence, la table Customer doit comporter des valeurs de date qui existent également dans une table Time Intelligence.
• Dans un modèle de données, les relations de table peuvent être un-à-un (chaque passager dispose d’une carte d’embarquement) ou un-à plusieurs (chaque vol comporte plusieurs passagers), mais pas plusieurs-à-plusieurs. Les relations plusieurs-à-plusieurs donnent des erreurs de dépendance circulaire comme « Une dépendance circulaire a été détectée ». Cette erreur se produit si vous établissez une connexion directe entre deux tables plusieurs-à-plusieurs ou des connexions indirectes (une chaîne de relations de table un-à-plusieurs au sein de chaque relation et plusieurs-à-plusieurs quand affichée de bout à bout). Pour en savoir plus sur les relations, consultez Relations entre les tables dans un modèle de données.
• Les types de données dans Colonne (clé étrangère) et Colonne associée (clé primaire) doivent être compatibles. Pour plus d'informations, consultez Types de données dans les modèles de données Excel.
• Il existe d'autres méthodes plus intuitives pour créer des relations, notamment lorsque vous ne savez pas quelles colonnes utiliser. Pour plus d'informations, consultez Créer des relations dans la vue de diagramme dans PowerPivot.

Exemple : association de données Time Intelligence à des données de vol de compagnie aérienne

Vous pouvez découvrir les relations de table et Time Intelligence à l’aide de données gratuites disponibles sur Windows Azure Marketplace. Certains de ces jeux de données sont très volumineux et demandent une connexion Internet rapide pour effectuer le téléchargement dans un délai raisonnable.

1. Démarrer le complément PowerPivot dans Excel 2013 et ouvrir la fenêtre PowerPivot.
2. Cliquez sur Données externes > À partir d'un service de données > Depuis Windows Azure Marketplace. La page d’accueil de Windows Azure Marketplace s’ouvre dans l’Assistant Importation de table.
3. Sous Prix, cliquez sur Gratuit.
4. Sous Catégorie, cliquez sur Science et statistiques.
5. Recherchez DateStream, puis cliquez sur Se connecter. Plus d’informations sur ce flux de données Time Intelligence.
6. Entrez votre compte Microsoft, puis cliquez sur Se connecter. Un aperçu des données doit s’afficher dans la fenêtre.
7. Faites défiler la fenêtre vers le bas, puis cliquez sur Sélectionner la requête.
8. Cliquez sur Suivant.
9. Choisissez BasicCalendarUS, puis cliquez sur Terminer pour importer les données. Avec une connexion Internet rapide, l’importation doit prendre environ une minute. Quand l’importation est terminée, vous devez voir un rapport d’état indiquant que 73 414 lignes ont été transférées. Cliquez sur Fermer.
10. Cliquez sur Données externes > À partir d'un service de données > Depuis Windows Azure Marketplace pour importer un second jeu de données.
11. Sous Type, cliquez sur Données.
12. Sous Prix, cliquez sur Gratuit.
13. Recherchez US Air Carrier Flight Delays, puis cliquez sur Sélectionner.
14. Faites défiler la fenêtre vers le bas, puis cliquez sur Sélectionner la requête..
15. Cliquez sur Suivant.
16. Cliquez sur Terminer pour importer les données. Avec une connexion Internet rapide, l’importation peut prendre 15 minutes. Quand l’importation est terminée, vous devez afficher un rapport d’état indiquant que 2 427 284 lignes ont été transférées. Cliquez sur Fermer. Le modèle de données doit comporter à présent deux tables. Pour les associer, vous devez trouver des colonnes compatibles dans chaque table.
17. Notez que la colonne DateKey dans BasicCalendarUS est au format 1/1/2012 12:00:00 AM. La table On_Time_Performance comporte également une colonne de date/heure FlightDate, dont les valeurs sont spécifiées dans le même format : 1/1/2012 12:00:00 AM. Les deux colonnes contiennent des données correspondantes, du même type de données, et une colonne au moins (DateKey) comporte seulement des valeurs uniques. Au cours des étapes suivantes, vous utilisez ces colonnes pour associer les tables.
18. Dans la fenêtre PowerPivot, cliquez sur Tableau croisé dynamique pour créer un tableau croisé dynamique dans une feuille de calcul existante ou nouvelle.
19. Dans la liste des champs, développez On_Time_Performance et cliquez sur ArrDelayMinutes pour l’ajouter à la zone Valeurs. Dans le tableau croisé dynamique, vous devez voir le nombre total de minutes de retard des vols.
20. Développez BasicCalendarUS et cliquez sur MonthInCalendar pour l’ajouter à la zone Lignes.
21. Notez que le tableau croisé dynamique répertorie maintenant les mois, mais la somme totale des minutes est identique pour chaque mois. Des valeurs identiques qui se répètent signalent la nécessité d’une relation.
22. Dans la liste des champs, dans « Des relations entre les tables sont peut-être nécessaires », cliquez sur Créer.
23. Dans Table liée, sélectionnez On_Time_Performance. Dans Colonne associée (primaire), choisissez FlightDate.
24. Dans Table, sélectionnez BasicCalendarUS. Dans Colonne (étrangère), choisissez DateKey. Cliquez sur OK pour créer la relation.
25. Notez que la somme des minutes de retard est différente maintenant pour chaque mois.
26. Dans BasicCalendarUS, faites glisser YearKey vers la zone Lignes au-dessus de MonthInCalendar.

Vous pouvez maintenant segmenter les retards par année et mois ou d’autres valeurs dans le calendrier.

 Conseil    Par défaut, les mois sont classés par ordre alphabétique. À l’aide du complément PowerPivot, vous pouvez modifier le tri de sorte que les mois apparaissent dans un ordre chronologique.

1. Vérifiez que la table BasicCalendarUS est ouverte dans la fenêtre PowerPivot.
2. Sous l’onglet Accueil, cliquez sur Trier par colonne.
3. Dans la zone Trier, choisissez MonthInCalendar
4. Dans la zone Par, choisissez MonthOfYear.

Le tableau croisé dynamique trie maintenant chaque combinaison mois-année (October 2011, November 2011) par le numéro de mois dans une année (10, 11). Modifier l’ordre de tri est simple, car le flux DateStream fournit toutes les colonnes nécessaires au fonctionnement de ce scénario. Si vous utilisez une autre table Time Intelligence, l’étape sera différente.

Comment déterminer la relation de table à créer

Quand vous ajoutez des champs à un tableau croisé dynamique, vous êtes averti si une relation de table est requise pour les champs sélectionnés dans le tableau croisé dynamique.

Bien qu’Excel puisse vous avertir lorsqu’une relation est nécessaire, le programme ne peut pas vous indiquer les tables et colonnes à utiliser ou si une relation de table est possible. Essayez de suivre les étapes ci-après pour obtenir les réponses dont vous avez besoin.

Étape 1 : déterminer les tables à spécifier dans la relation

Si votre modèle ne contient que quelques tables, il peut être très facile de déterminer celles que vous devez utiliser. Dans le cas des modèles plus volumineux, vous aurez probablement besoin d’aide. Une approche consiste à utiliser la vue de diagramme dans le complément PowerPivot. La vue de diagramme fournit une représentation visuelle de toutes les tables dans le modèle de données. Grâce à cette vue, vous pouvez rapidement déterminer les tables séparées du reste du modèle.

 Remarque    Il est possible de créer des relations ambiguës qui ne sont pas valides lorsqu’elles sont utilisées dans un rapport Power View ou de tableau croisé dynamique. Imaginez que toutes vos tables sont associées d’une certaine manière aux autres tables du modèle. Toutefois, quand vous essayez de combiner les champs des différentes tables, le message « Des relations entre les tables sont peut-être nécessaires » s’affiche. La cause la plus probable est une relation plusieurs-à-plusieurs. Si vous suivez la chaîne des relations qui se connectent aux tables que vous voulez utiliser, vous découvrirez très probablement deux relations un-à-plusieurs au minimum. Il n’y a pas de solution simple qui fonctionne pour chaque situation. Vous pouvez toutefois essayer de créer des colonnes calculées pour consolider les colonnes que vous voulez utiliser dans une table.

Étape 2 : rechercher les colonnes pouvant être utilisées pour créer un chemin d’une table à une autre

Après avoir identifié la table déconnectée du reste du modèle, examinez ces colonnes pour déterminer si une autre colonne dans le modèle contient des valeurs correspondantes.

Imaginez par exemple que vous disposez d’un modèle qui contient des ventes de produit par secteur et que vous importez des données démographiques pour déterminer s’il existe une corrélation entre les ventes et les tendances démographiques dans chaque secteur. Comme les données démographiques proviennent d’une autre source de données, ses tables sont initialement isolées du reste du modèle. Pour intégrer les données démographiques au reste du modèle, vous devez trouver une colonne dans une des tables démographiques qui correspond à une colonne que vous utilisez déjà. Par exemple, si les données démographiques sont classées par région et si les données de vente indiquent dans quelle région la vente a eu lieu, vous pouvez associer les deux jeux de données en recherchant une colonne commune, comme Département, Code postal ou région, pour effectuer la recherche.

En plus des valeurs correspondantes, il existe d’autres conditions requises pour créer une relation :

• Les valeurs des données dans la colonne de recherche doivent être uniques. En d'autres termes, la colonne ne doit pas contenir de doublons. Dans un modèle de données, les valeurs Null et les chaînes vides sont équivalentes à un espace, qui est une valeur de donnée distincte. Cela signifie que vous ne pouvez pas avoir plusieurs valeurs Null dans la colonne de recherche.
• Les types de données dans la colonne source et la colonne de recherche doivent être compatibles. Pour plus d'informations sur les types de données, voir Types de données dans les modèles de données.

Pour en savoir plus sur les relations de table, consultez Relations entre les tables dans un modèle de données.

Haut de la page