Tutoriel : Importer des données dans Excel et créer un modèle de données

Abstrait: Il s’agit du premier tutoriel d’une série conçue pour vous familiariser et vous familiariser avec Excel et ses fonctionnalités intégrées de mash-up et d’analyse des données. Ces tutoriels créent et affinent un classeur Excel à partir de zéro, créent un modèle de données, puis créent des rapports interactifs étonnants à l’aide de Power View. Les tutoriels sont conçus pour illustrer les fonctionnalités et fonctionnalités de Microsoft Business Intelligence dans Excel, les tableaux croisés dynamiques, Power Pivot et Power View. 

Dans ces tutoriels, vous allez apprendre à importer et explorer des données dans Excel, à créer et affiner un modèle de données à l’aide de Power Pivot, et à créer des rapports interactifs avec Power View que vous pouvez publier, protéger et partager.

Elle inclut les didacticiels suivants :

1. Importer des données dans Excel 2016 et créer un modèle de données
2. Étendre les relations de modèle de données à l’aide d’Excel, Power Pivot et DAX
3. Créer des rapports Power View basés sur une carte
4. Incorporer des données d’Internet et définir les valeurs par défaut des rapports Power View
5. Aide de Power Pivot
6. Créer des rapports Power View originaux - Partie 2

Dans ce didacticiel, vous commencez avec un classeur Excel vide.

Ce didacticiel inclut les sections suivantes :

• Importer des données à partir d’une base de données
• Importer des données depuis un cube
• Importer des données par copier-coller
• Créer une relation entre des données importées
• Point de contrôle et quiz

Le didacticiel inclut un quiz qui vous permet de tester vos connaissances.

Cette série de didacticiels utilise des données décrivant les médailles olympiques, les pays hôtes et les diverses épreuves sportives organisées dans le cadre des Jeux olympiques. Nous suggérons que vous consultiez chaque didacticiel dans l’ordre. 

Importer des données à partir d’une base de données

Nous commençons ce didacticiel avec un classeur vide. L’objectif de cette section est de se connecter à une source de données externe et d’importer ces données dans Excel pour une analyse ultérieure.

Nous allons commencer par télécharger des données provenant d’Internet. Les données incluses dans une base de données Microsoft Access décrivent les médailles olympiques.

1. Cliquez sur les liens suivants pour télécharger les fichiers utilisés dans le cadre de cette série de didacticiels. Téléchargez chacun des quatre fichiers vers un emplacement facilement accessible, tel que Téléchargements ou Mes documents, ou dans un dossier que vous créez :
   > Base de données Access OlympicMedals.accdb
   > OlympicSports.xlsx classeur Excel
   > Population.xlsx classeur Excel
   > DiscImage_table.xlsx classeur Excel

2. Dans Excel, ouvrez un classeur vierge.

3. Cliquez sur Données > Obtenir des données > à partir d’une base de données > à partir d’une base de données Microsoft Access. Le ruban s’ajuste dynamiquement en fonction de la largeur de votre classeur, de sorte que les commandes de votre ruban peuvent être légèrement différentes de l’écran suivant.

   

4. Sélectionnez le fichier OlympicMedals.accdb que vous avez téléchargé, puis cliquez sur Importer. La fenêtre Navigateur suivante s’affiche, affichant les tables trouvées dans la base de données. Les tables d’une base de données sont semblables aux feuilles de calcul ou tableaux dans Excel. Cochez la case Sélectionner plusieurs tables , puis sélectionnez toutes les tables. Cliquez ensuite sur Charger > le chargement dans.

   

5. La fenêtre Importer des données apparaît.

   Remarque

   Notez la case à cocher en bas de la fenêtre qui vous permet d’ajouter ces données au modèle de données, affichée dans l’écran suivant. Un modèle de données est créé automatiquement lorsque vous importez ou utilisez simultanément plusieurs tables. Un modèle de données intègre les tables, ce qui permet une analyse approfondie à l’aide de tableaux croisés dynamiques, de Power Pivot et de Power View. Lorsque vous importez des tables à partir d’une base de données, les relations de base de données existantes entre ces tables sont utilisées pour créer le modèle de données dans Excel. Le modèle de données est transparent dans Excel, mais vous pouvez l’afficher et le modifier directement à l’aide du complément Power Pivot. Le modèle de données est abordé plus en détail plus loin dans ce tutoriel.

   Sélectionnez l’option Rapport de tableau croisé dynamique qui permet d’importer les tables dans Excel et prépare un tableau croisé dynamique pour analyser les tables importées, puis cliquez sur OK.

   

6. Une fois que les données sont importées, un tableau croisé dynamique est créé en utilisant les tables importées.

   

Avec les données importées dans Excel et le modèle de données créé automatiquement, vous êtes prêt à explorer les données.

Explorer des données à l’aide d’un tableau croisé dynamique

Il est facile d’explorer des données importées à l’aide d’un tableau croisé dynamique. Dans un tableau croisé dynamique, vous devez faire glisser les champs (semblables aux colonnes dans Excel) des tables (comme celles que vous venez d’importer de la base de données Access) dans différentes zones du tableau croisé dynamique pour ajuster la présentation de vos données. Un tableau croisé dynamique inclut quatre zones : FILTRES, COLONNES, LIGNES et VALEURS.

Il peut être nécessaire d’expérimenter pour déterminer la zone vers laquelle un champ doit être déplacé. Vous pouvez faire glisser autant de champs que vous le souhaitez à partir de vos tables, jusqu’à ce que le tableau croisé dynamique présente vos données comme vous le souhaitez. N’hésitez pas à explorer en faisant glisser des champs dans différentes zones du tableau croisé dynamique ; les données sous-jacentes ne sont pas affectées lorsque vous organisez des champs dans un tableau croisé dynamique.

Nous allons explorer les données relatives aux médailles olympiques dans le tableau croisé dynamique, en commençant par les médaillés olympiques organisés par discipline, type de médaille et pays/région de l’athlète.

1. Dans Champs de tableau croisé dynamique, développez la table Medals en cliquant sur la flèche associée. Recherchez le champ NOC_CountryRegion dans la table Medals développée et faites-le glisser vers la zone COLONNES. NOC est l’acronyme de National Olympic Committee (unité d’organisation pour un pays ou une région).

2. À partir de la table Disciplines, faites glisser Discipline vers la zone LIGNES.

3. Nous allons filtrer la table Disciplines pour afficher seulement cinq sports : tir à l’arc (Archery), plongée (Diving), escrime (Fencing), patinage artistique (Figure Skating) et patinage de vitesse (Speed Skating). Vous pouvez faire ceci dans la zone Champs de tableau croisé dynamique, ou à partir du filtre Étiquettes de ligne dans le tableau croisé dynamique lui-même.

   1. Cliquez n’importe où dans le tableau croisé dynamique pour vérifier que le tableau croisé dynamique Excel est sélectionné. Dans la liste Champs de tableau croisé dynamique , où la table Disciplines est développée, pointez sur son champ Discipline et une flèche déroulante s’affiche à droite du champ. Cliquez sur la liste déroulante, cliquez sur **(Sélectionner tout)** pour supprimer toutes les sélections, puis faites défiler vers le bas et sélectionnez Tir à l’arc, Plongée, Escrime, Patinage artistique et Patinage de vitesse. Cliquez sur OK.
   2. Ou, dans la section Étiquettes de ligne du tableau croisé dynamique, cliquez sur le menu déroulant à côté d’Étiquettes de ligne dans le tableau croisé dynamique, cliquez sur (Sélectionner tout) pour supprimer toutes les sélections, puis faites défiler et sélectionnez Archery, Diving, Fencing, Figure Skating et Speed Skating. Cliquez sur OK.

4. Dans Champs de tableau croisé dynamique, à partir de la table Medals, faites glisser Medal vers la zone VALEURS. Comme les valeurs doivent être numériques, Excel modifie automatiquement Medal en Count of Medal.

5. À partir de la table Medals, sélectionnez à nouveau et faites glisser Medal dans la zone FILTRES.

6. Nous allons filtrer le tableau croisé dynamique pour afficher uniquement les pays ou régions ayant remporté plus de 90 médailles. Voici comment procéder.

   1. Dans le tableau croisé dynamique, cliquez sur le menu déroulant à droite d’Étiquettes de colonne.
   2. Sélectionnez Filtres s’appliquant aux valeurs, puis Supérieur à....
   3. Tapez 90 dans le dernier champ (à droite). Cliquez sur OK.
      

Votre tableau croisé dynamique est semblable à celui présenté dans l’écran suivant.

Avec peu d'efforts, vous disposez maintenant d'un tableau croisé dynamique de base qui comprend des champs de trois tables différentes. C’est grâce aux relations préexistantes entre les tables que cette tâche a été si simple. Comme les relations de table existaient dans la base de données source et comme vous avez importé toutes les tables en une seule opération, Excel a pu recréer ces relations de table dans son modèle de données.

Que se passerait-il si les données provenaient de sources différentes ou si elles étaient importées ultérieurement ? En règle générale, vous pouvez créer des relations avec de nouvelles données sur la base de colonnes correspondantes. Dans l’étape suivante, vous allez importer d’autres tables et découvrir comment créer des relations.

Importer des données depuis un cube

À présent, nous allons importer des données d’une autre source (cette fois-ci, un classeur existant), puis spécifier les relations entre nos données existantes et les nouvelles données. Les relations vous permettent d’analyser les collections de données dans Excel et de créer des visualisations immersives et intéressantes grâce aux données que vous importez.

Nous allons commencer par créer une feuille de calcul vide avant d’importer les données d’un classeur Excel.

1. Insérez une nouvelle feuille de calcul Excel et nommez-la Sports.

2. Accédez au dossier contenant les fichiers de données d’exemple téléchargés, puis ouvrez OlympicSports.xlsx.

3. Sélectionnez et copiez les données de la feuille Sheet1. Si vous avez sélectionné une cellule avec des données, telle que la cellule A1, vous pouvez appuyer sur Ctrl+A pour sélectionner toutes les données adjacentes. Fermez le classeur OlympicSports.xlsx.

4. Sur la feuille de calcul Sports, placez le curseur dans la cellule A1, puis collez les données.

5. Les données étant toujours mises en surbrillance, appuyez sur Ctrl + T pour mettre en forme les données sous forme de tableau. Vous pouvez également mettre en forme les données sous la forme d’un tableau à partir du ruban en sélectionnant HOME > Format as Table. Étant donné que les données ont des en-têtes, sélectionnez Ma table comporte des en-têtes dans la fenêtre Créer une table qui s’affiche, comme illustré ici.

   

   La mise en forme des données sous forme de tableau présente de nombreux avantages. Vous pouvez attribuer un nom à une table, ce qui facilite son identification. Vous pouvez également établir des relations entre les tables, ce qui permet l’exploration et l’analyse dans les tableaux croisés dynamiques, Power Pivot et Power View.

6. Nommez le tableau. Dans PROPRIÉTÉS DE LA CONCEPTION > DE TABLE, recherchez le champ Nom de la table et tapez Sports. Le classeur ressemble à celui présenté dans l’écran suivant.
   

7. Enregistrez le classeur.

Importer des données par copier-coller

À présent que nous avons importé des données d’un classeur Excel, nous allons importer les données d’un tableau trouvé sur une page web, ou toute autre source à partir de laquelle il est possible d’effectuer un copier-coller dans Excel. Dans les étapes suivantes, vous allez ajouter les villes hôtes des Jeux olympiques à partir d’un tableau.

1. Insérez une nouvelle feuille de calcul Excel et nommez-la Hosts.
2. Sélectionnez et copiez le tableau suivant, en-têtes compris.

City	NOC_CountryRegion	Alpha-2 Code	Édition	Season
Melbourne / Stockholm	AUS	AS	1956	Summer
Sydney	AUS	AS	2000	Summer
Innsbruck	AUT	AT	1964	Winter
Innsbruck	AUT	AT	1976	Winter
Antwerp	BEL	BE	1920	Summer
Antwerp	BEL	BE	1920	Winter
Montreal	CAN	CA	1976	Summer
Lake Placid	CAN	CA	1980	Winter
Calgary	CAN	CA	1988	Winter
St. Moritz	SUI	SZ	1928	Winter
St. Moritz	SUI	SZ	1948	Winter
Beijing	CHN	CH	2008	Summer
Berlin	GER	GM	1936	Summer
Garmisch-Partenkirchen	GER	GM	1936	Winter
Barcelona	ESP	SP	1992	Summer
Helsinki	FIN	FI	1952	Summer
Paris	FRA	FR	1900	Summer
Paris	FRA	FR	1924	Summer
Chamonix	FRA	FR	1924	Winter
Grenoble	FRA	FR	1968	Winter
Albertville	FRA	FR	1992	Winter
London	GBR	UK	1908	Summer
London	GBR	UK	1908	Winter
London	GBR	UK	1948	Summer
Munich	GER	DE	1972	Summer
Athens	GRC	GR	2004	Summer
Cortina d'Ampezzo	ITA	IT	1956	Winter
Rome	ITA	IT	1960	Summer
Turin	ITA	IT	2006	Winter
Tokyo	JPN	JA	1964	Summer
Sapporo	JPN	JA	1972	Winter
Nagano	JPN	JA	1998	Winter
Seoul	KOR	KS	1988	Summer
Mexico	MEX	MX	1968	Summer
Amsterdam	NED	NL	1928	Summer
Oslo	NOR	NO	1952	Winter
Lillehammer	NOR	NO	1994	Winter
Stockholm	SWE	SW	1912	Summer
St Louis	USA	US	1904	Summer
Los Angeles	USA	US	1932	Summer
Lake Placid	USA	US	1932	Winter
Squaw Valley	USA	US	1960	Winter
Moscow	URS	RU	1980	Summer
Los Angeles	USA	US	1984	Summer
Atlanta	USA	US	1996	Summer
Salt Lake City	USA	US	2002	Winter
Sarajevo	YUG	YU	1984	Winter

1. Dans Excel, placez le curseur dans la cellule A1 de la feuille de calcul Hosts et collez les données.
2. Mettez en forme les données sous la forme d’un tableau. Comme décrit précédemment dans ce tutoriel, vous appuyez sur Ctrl + T pour mettre en forme les données sous forme de tableau ou à partir du format ACCUEIL > en tant que tableau. Comme les données ont des en-têtes, sélectionnez Ma table comporte des en-têtes dans la fenêtre Créer une table qui apparaît.
3. Nommez le tableau. Dans PROPRIÉTÉS DE LA CONCEPTION > DE TABLE , recherchez le champ Nom de la table et tapez Hôtes.
4. Sélectionnez la colonne Edition, puis à partir de l’onglet ACCUEIL, mettez-la en forme comme un nombre sans décimale.
5. Enregistrez le classeur. Votre classeur est semblable à celui présenté dans l’écran suivant.

À présent que vous avez un classeur Excel avec des tables, vous pouvez créer des relations entre celles-ci. La création de relations entre des tables vous permet de combiner les données de deux tables.

Créer une relation entre des données importées

Vous pouvez commencer immédiatement à utiliser les champs dans votre tableau croisé dynamique à partir des tables importées. Si Excel ne peut pas déterminer comment incorporer un champ dans le tableau croisé dynamique, une relation doit être établie avec le modèle de données existantes. Dans les étapes suivantes, vous allez apprendre à créer une relation entre les données que vous avez importées à partir de différentes sources.

1. Dans la feuille Sheet1, en haut deschamps de tableau croisé dynamique, cliquez surTout pour afficher la liste complète des tables disponibles, comme illustré dans l’écran suivant.
   

2. Faites défiler la liste pour afficher les nouvelles tables que vous venez d’ajouter.

3. Développez Sports et sélectionnez Sport pour l’ajouter au tableau croisé dynamique. Notez qu’Excel vous invite à créer une relation, comme montré dans l’écran suivant.
   
    
   Cette notification s’affiche parce que vous avez utilisé les champs d’une table qui ne fait pas partie du modèle de données sous-jacent. Vous pouvez ajouter une table au modèle de données en créant une relation avec une table qui figure déjà dans le modèle de données. Pour créer la relation, une des tables doit avoir une colonne de valeurs uniques et non répétées. Dans les données d’exemple, la table Disciplines importée à partir de la base de données contient un champ avec des codes de sport appelés SportID. Ces mêmes codes de sport sont présents en tant que champ dans les données Excel que nous avons importées. Nous allons créer la relation.

4. Cliquez sur CRÉER... dans la zone Champs de tableau croisé dynamique pour ouvrir la boîte de dialogue Créer une relation, comme montré dans l’écran suivant.

   

5. Dans Table, choisissez Table de modèle de données : disciplines dans la liste déroulante.

6. Dans Colonne (étrangère), choisissez SportID.

7. Dans Table associée, choisissez Table de modèle de données : Sports.

8. Dans Colonne liée (principale), choisissez SportID.

9. Cliquez sur OK.

Le tableau croisé dynamique change pour refléter la nouvelle relation. Pour autant, il n’est pas encore tout à fait au point, en raison de l’ordre des champs dans la zone LIGNES. Discipline est une sous-catégorie d’un sport donné, mais comme nous avons placé Discipline au-dessus de Sport dans la zone LIGNES, cette dernière n’est pas correctement organisée. L’écran suivant montre ce classement incorrect.

1. Dans la zone LIGNES, déplacez Sport au-dessus de Discipline. Le tableau croisé dynamique affiche désormais les données comme vous souhaitez les voir, comme montré dans l’écran suivant.

   

En arrière-plan, Excel crée un modèle de données qui peut être utilisé dans tout le classeur, dans n’importe quel tableau croisé dynamique, graphique croisé dynamique, power pivot ou tout rapport Power View. Les relations entre les tables sont à la base d’un modèle de données et déterminent les chemins de navigation et de calcul.

Dans le tutoriel suivant, Étendre les relations de modèle de données à l’aide d’Excel, Power Pivot**et DAX**, vous vous appuyez sur ce que vous avez appris ici et vous allez étendre le modèle de données à l’aide d’un complément Excel puissant et visuel appelé Power Pivot. Vous apprenez également à calculer des colonnes dans une table et à utiliser cette colonne calculée afin qu’une table non liée puisse être ajoutée à votre modèle de données.

Point de contrôle et quiz

Vérification des acquis

Vous avez maintenant un classeur Excel contenant un tableau croisé dynamique qui accède aux données de plusieurs tables importées séparément. Vous avez appris à importer des données à partir d’une base de données, à partir d’un autre classeur Excel et via le copier-coller de données dans Excel.

Pour que les données fonctionnent ensemble, vous devez créer une relation de tableau qu’Excel a utilisée pour mettre en corrélation les lignes. Vous avez également appris qu’il est essentiel d’avoir des colonnes dans une table qui sont corrélées aux données d’une autre table pour créer des relations et rechercher des lignes associées.

Vous êtes prêt à passer au prochain didacticiel de cette série. Voici un lien vers celui-ci :

Didacticiel : développer les relations d’un modèle de données à l’aide d’Excel, Power Pivot et DAX

QUIZ

Vous voulez voir à quel point vous vous souvenez de ce que vous avez appris ? Voilà votre chance. Le questionnaire suivant met en évidence les fonctionnalités, les fonctionnalités ou les exigences que vous avez apprises dans ce tutoriel. En bas de la page, vous trouverez les réponses. Bonne chance !

Question 1 : pourquoi est-il important de convertir les données importées en tables ?

A : vous n’avez pas besoin de les convertir en tables, car toutes les données importées le sont automatiquement.

B : si vous convertissez les données importées en tables, celles-ci seront exclues du modèle de données. Ce n’est que lorsqu’ils sont exclus du modèle de données qu’ils sont disponibles dans les tableaux croisés dynamiques, Power Pivot et Power View.

C : Si vous convertissez des données importées en tables, elles peuvent être incluses dans le modèle de données et mises à disposition des tableaux croisés dynamiques, Power Pivot et Power View.

D : il n’est pas possible de convertir les données importées en tables.

Question 2 : parmi les sources de données suivantes, lesquelles peuvent être importées dans Excel et incluses dans le modèle de données ?

A : bases de données Access et autres bases de données diverses.

B : fichiers Excel existants.

C : n’importe quel contenu copié et collé dans Excel et mis en forme comme table, y compris les tableaux de données dans les sites web et documents, ou tout autre contenu pouvant être collé dans Excel.

D : Toutes les réponses précédentes.

Question 3 : dans un tableau croisé dynamique, que se passe-t-il lorsque vous réorganisez les champs dans les quatre zones des champs de tableau croisé dynamique ?

A : rien. Vous ne pouvez pas réorganiser les champs une fois que vous les avez placés dans les zones des champs de tableau croisé dynamique.

B : le format du tableau croisé dynamique est modifié pour refléter la mise en page, mais les données sous-jacentes ne sont pas affectées.

C : le format du tableau croisé dynamique est modifié pour refléter la mise en page, et toutes les données sous-jacentes sont définitivement modifiées.

D : les données sous-jacentes sont modifiées, ce qui crée de nouveaux jeux de données.

Question 4: quelles sont les conditions préalables requises pour la création d’une relation entre des tables ?

A : aucune table ne peut avoir de colonne contenant des valeurs uniques et non répétées.

B : une table ne doit pas faire partie du classeur Excel.

C : les colonnes ne doivent pas être converties en tables.

D : aucune des réponses précédentes n’est correcte.

Réponses du quiz

1. Réponse correcte : C
2. Réponse correcte : D
3. Réponse correcte : B
4. Réponse correcte : D