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

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 > classeur Excel
   OlympicSports.xlsx classeur Excel
   >Population.xlsx > classeur ExcelDiscImage_table.xlsx

2. Dans Excel, ouvrez un classeur vierge.

3. Cliquez sur Données > Obtenir des données externes > À partir d’Access. Le ruban étant ajusté de façon dynamique sur la base de la largeur de votre classeur, les commandes du ruban peuvent apparaître légèrement différentes de celles figurant dans les écrans suivants. Le premier écran montre le ruban dans le cas d’un classeur large, la deuxième image montre un classeur redimensionné pour n’afficher qu’une partie de l'écran.
   
   
   
   
   
    

4. Sélectionnez le fichier OlympicMedals.accdb que vous avez téléchargé, puis cliquez sur Ouvrir. La fenêtre suivante Sélectionner une table apparaît et affiche 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. Activez la case à cocher Activer la sélection de plusieurs tables, puis sélectionnez toutes les tables. Cliquez ensuite sur OK.
   
   

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

   : 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 importe les tableaux dans Excel et prépare un tableau croisé dynamique pour l’analyse des tableaux importés, 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 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 forme de tableau à partir du ruban en sélectionnant ACCUEIL > Format en tant que tableau. É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, les Power Pivot et Power View.

6. Nommez le tableau. Dans OUTILS DE TABLEAU > CRÉATION > Propriétés, recherchez le champ Nom du tableau, puis 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.

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 plus tôt dans ce didacticiel, appuyez sur Ctrl+T pour mettre en forme les données sous la forme d’un tableau, ou sélectionnez ACCUEIL > Mettre sous forme de 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 OUTILS DE TABLEAU > CRÉATION > Propriétés, recherchez le champ Nom du tableau, puis tapez Hosts.

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 des champs detableau 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 Disciplines dans la liste déroulante.

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

7. Dans Table liée, choisissez 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 génère un modèle de données qui peut être utilisé dans le classeur dans les tableaux croisés dynamiques, les graphiques croisés dynamiques, Power Pivot ou les rapports 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 Pivotet 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. Seules les données du modèle de données sont disponibles dans les tableaux croisés dynamiques, Power Pivot et Power View.

C : si vous convertissez les données importées en tables, elles peuvent être incluses dans le modèle de données et être disponibles dans les 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 pouvez-vous importer dans Excel et inclure 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 des champs dans les quatre zones 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 : Lors de la création d’une relation entre des tables, quels sont les éléments requis ?

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