Avez-vous déjà utilisé la fonction RECHERCHEV pour insérer une colonne d’une table dans une autre table ? Le modèle de données intégré à Excel rend la fonction RECHERCHEV obsolète. Vous pouvez créer une relation entre deux tables de données, basée sur des données correspondantes dans chaque table. Vous pouvez ensuite créer des feuilles Power View et générer des tableaux croisés dynamiques et autres rapports avec les champs de chaque table, même si les tables proviennent de différentes sources. Par exemple, si vous avez des données de ventes client, vous pouvez importer et associer des données Time Intelligence pour analyser les modèles de ventes par année et mois.
Toutes les tables d’un classeur sont répertoriées dans les listes de champs du tableau croisé dynamique et de Power View.
Lorsque vous importez des tables associées à partir d’une base de données relationnelle, Excel crée parfois ces relations dans le modèle de données qu’il génère en arrière-plan. Pour tous les autres cas, vous devez créer des relations manuellement.
-
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.
-
Effectuez l’une des opérations suivantes : Mettre en forme les données sous forme de table ou Importer des données externes en tant que table dans une nouvelle feuille de calcul.
-
Donnez à chaque table un nom significatif : Dans Outils de table, cliquez sur Conception > Nom de la table, puis entrez un nom.
-
Vérifiez que la colonne dans l’une des tables comporte des valeurs de données uniques (non dupliquées). Excel peut seulement créer la relation si une colonne contient des valeurs uniques.
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.
-
Cliquez sur Données > Relations.
Si l’option Relations est grisée, votre classeur ne contient qu’une seule table.
-
Dans la boîte de dialogue Gérer les relations, cliquez sur Nouveau.
-
Dans la boîte de dialogue Créer une relation, pour Table, cliquez sur la flèche et sélectionnez une table dans la liste. Dans une relation « un-à-plusieurs », cette table doit être du côté « plusieurs ». Dans notre exemple client et Time Intelligence, vous choisissez la table de ventes client en premier, car plusieurs ventes peuvent avoir lieu un jour donné.
-
Pour Colonne (clé étrangère), sélectionnez la colonne qui contient les données mises en rapport avec Colonne associée (clé primaire). Par exemple, si vous aviez une colonne de dates dans les deux tables, choisissez à présent cette colonne.
-
Pour Table lié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.
-
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.
-
Cliquez sur OK.
En savoir plus sur les relations entre les tables dans Excel
Remarques sur les relations
-
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.
-
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 plus d’informations, voir Relations entre les tables dans un modèle de données.
-
Les types de données dans les deux colonnes doivent être compatibles. Pour plus d’informations, voir 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, voir Créer des relations dans la vue de diagramme dans Power Pivot.
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 Microsoft 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.
-
Démarrez le complément Power Pivot dans Microsoft Excel et ouvrez la fenêtre Power Pivot.
-
Cliquez sur Obtenir des données externes > À partir d’un service de données > À partir de Microsoft Azure Marketplace. La page d’accueil de Microsoft Azure Marketplace s’ouvre dans l’Assistant Importation de table.
-
Sous Prix, cliquez sur Gratuit.
-
Sous Catégorie, cliquez sur Science et statistiques.
-
Recherchez DateStream, puis cliquez sur S’abonner.
-
Entrez votre compte Microsoft, puis cliquez sur Se connecter. Un aperçu des données doit s’afficher dans la fenêtre.
-
Faites défiler la fenêtre vers le bas, puis cliquez sur Sélectionner la requête..
-
Cliquez sur Suivant.
-
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.
-
Cliquez sur Obtenir des données externes > À partir d’un service de données > À partir de Microsoft Azure Marketplace pour importer un second jeu de données.
-
Sous Type, cliquez sur Données.
-
Sous Prix, cliquez sur Gratuit.
-
Recherchez US Air Carrier Flight Delays, puis cliquez sur Sélectionner.
-
Faites défiler la fenêtre vers le bas, puis cliquez sur Sélectionner la requête..
-
Cliquez sur Suivant.
-
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.
-
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.
-
Dans la fenêtre Power Pivot, cliquez sur Tableau croisé dynamique pour créer un tableau croisé dynamique dans une feuille de calcul existante ou nouvelle.
-
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.
-
Développez BasicCalendarUS et cliquez sur MonthInCalendar pour l’ajouter à la zone Lignes.
-
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.
-
Dans la liste des champs, dans « Des relations entre les tables sont peut-être nécessaires », cliquez sur Créer.
-
Dans Table liée, sélectionnez On_Time_Performance. Dans Colonne associée (clé primaire), choisissez FlightDate.
-
Dans Table, sélectionnez BasicCalendarUS et dans Colonne (Foreign) choisissez DateKey. Cliquez sur OK pour créer la relation.
-
Notez que la somme des minutes de retard est différente maintenant pour chaque mois.
-
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.
Conseils : Par défaut, les mois sont classés par ordre alphabétique. À l’aide du complément Power Pivot, vous pouvez modifier le tri de sorte que les mois apparaissent dans un ordre chronologique.
-
Vérifiez que la table BasicCalendarUS est ouverte dans la fenêtre Power Pivot.
-
Sous l’onglet Accueil, cliquez sur Trier par colonne.
-
Dans la zone Trier, choisissez MonthInCalendar
-
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.
« Les relations entre les tables peuvent être nécessaires »
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 Power Pivot. 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 de tableau croisé dynamique ou Power View. Supposons que toutes vos tables soient liées d’une certaine manière à d’autres tables du modèle, mais lorsque vous essayez de combiner des champs de différentes tables, vous obtenez le message « Les relations entre les tables peuvent être nécessaires ». La cause la plus probable est que vous avez eu une relation plusieurs-à-plusieurs. Si vous suivez la chaîne de relations de table qui se connectent aux tables que vous souhaitez utiliser, vous découvrirez probablement que vous avez au moins deux relations de table un-à-plusieurs. Il n’existe pas de solution de contournement simple qui fonctionne pour chaque situation, mais vous pouvez essayer de créer des colonnes calculées pour consolider les colonnes que vous souhaitez utiliser dans une seule 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, voir Relations entre les tables dans un modèle de données.
