Tutoriel importer des données dans Excel, et créer un modèle de données
Résumé: Voici le premier tutoriel d'une série conçue pour vous familiariser et à l'aise avec Excel et son intégré mash-up de données et fonctionnalités d'analyse. Ces tutoriels construire et affinent un classeur Excel à partir de zéro, construire un modèle de données, puis créer des rapports interactifs étonnants à l'aide de Power View. Les tutoriels sont conçus pour démontrer les caractéristiques Microsoft Business Intelligence et fonctionnalités dans Excel, tableaux croisés dynamiques, Power Pivot et Power View.
Dans ces didacticiels, vous apprendrez comment importer et d'explorer les données dans Excel, construire et affiner un modèle de données à l'aide Power Pivot, et créer des rapports interactifs avec Power View que vous pouvez publier, protéger et partager.
Les tutoriels de cette série sont les suivantes:
Dans ce tutoriel, vous commencez avec un classeur Excel vierge.
A la fin de ce tutoriel est un jeu-questionnaire que vous pouvez prendre pour tester votre apprentissage.
Importer des données à partir d'une base de données
Nous commençons ce tutoriel avec un classeur vide. L'objectif de cette section est de se connecter à une source de données externe et importer ces données dans Excel pour une analyse ultérieure.
Commençons par le téléchargement des données sur Internet. Les données décrit médailles olympiques, et est une base de données Microsoft Access.
Cliquez sur les liens suivants pour télécharger les fichiers que nous utilisons au cours de cette série de tutoriel. Télécharger chacun des quatre fichiers à un endroit qui est facilement accessible, comme Téléchargements ou Mes documents. ou à un nouveau dossier que vous créez:
> OlympicMedals.accdb base de données Access
> Classeur Excel OlympicSports.xlsx
> Classeur Excel Population.xlsx
> Classeur Excel DiscImage_table.xlsx
Sélectionnez le fichier que vous avez téléchargé OlympicMedals.accdb et cliquez sur Ouvrir. La fenêtre Sélectionner le tableau suivant apparaît, l'affichage des tableaux trouvés dans la base de données. Les tableaux dans une base de données sont semblables à des feuilles de calcul ou de tableaux dans Excel. Cochez la case Activer la sélection de plusieurs tables de boîte, et sélectionnez toutes les tables. Cliquez ensuite sur OK.
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. représenté sur l'écran suivant. Un modèle de données est créé automatiquement lors de l'importation ou de travail avec deux ou plusieurs tables simultanément. Un modèle de données intègre les tables, ce qui permet une analyse approfondie à l'aide de tableaux croisés dynamiques, Power Pivot et Power View. Lorsque vous importez des tables à partir d'une base de données, les relations de bases de données existantes entre ces tables est utilisé pour créer le modèle de données dans Excel. Le modèle de données est transparent dans Excel, mais vous pouvez visualiser et modifier directement en utilisant le module complémentaire Pivot Power. Le modèle de données est discuté 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 analyser les tables importées, puis cliquez sur OK.
Une fois que les données sont importées, un tableau croisé dynamique est créée en utilisant les tables importées.
Avec les données importées dans Excel, et le modèle de données automatiquement créé, vous êtes prêt à explorer les données.
Explorer les données en utilisant un tableau croisé dynamique
L'exploration des données importées est facile à l'aide d'un tableau croisé dynamique. Dans un tableau croisé dynamique, vous faites glisser les champs (similaires à colonnes dans Excel) à partir de tables (comme les tables que vous venez d'importer à partir de la base de données Access) dans différentes zones du tableau croisé dynamique pour ajuster la façon dont il présente vos données. Un tableau croisé dynamique a quatre domaines: filtres. COLONNES. RANGS. et les valeurs.
Cela peut prendre quelques expériences pour déterminer quelle zone un champ doit être traîné. Vous pouvez faire glisser autant ou quelques champs de vos tables que vous le souhaitez, jusqu'à ce que le tableau croisé dynamique présente vos données que vous voulez voir. Ne hésitez pas à explorer en faisant glisser les champs dans différentes zones du tableau croisé dynamique; les données sous-jacentes ne sont pas affectées lorsque vous arrangez champs dans un tableau croisé dynamique.
Explorons les données médailles olympiques dans le tableau croisé dynamique, en commençant par les médaillés olympiques organisés par la discipline, le type médaille, et le pays de l'athlète ou de la région.
Dans les champs de tableau croisé dynamique. élargir la table des médailles en cliquant sur la flèche à côté. Trouvez le champ NOC_CountryRegion dans le tableau des médailles élargi et faites-la glisser vers la zone COLONNES. CNO signifie Comités Nationaux Olympiques, qui est l'unité d'organisation pour un pays ou une région.
Ensuite, à partir de la table Disciplines, faites glisser la discipline dans la zone ROWS.
Faisons filtrer les disciplines pour afficher seulement cinq sports: Tir à l'arc, plongée sous-marine, Escrime, Patinage artistique, et Patinage de vitesse. Vous pouvez le faire à partir de la zone Champs de tableau croisé dynamique ou du filtre Étiquettes de ligne du tableau croisé dynamique lui-même.
Cliquez dans le tableau croisé dynamique pour assurer le tableau croisé dynamique Excel est sélectionné. Dans la liste Champs de tableau croisé dynamique, où la table disciplines est élargi, survolez son champ de discipline et une flèche déroulante apparaît à droite du champ. Cliquez sur le menu déroulant, cliquez sur (Sélectionner tout) pour supprimer toutes les sélections, puis faites défiler et sélectionnez Tir à l'arc, plongée sous-marine, Escrime, Patinage artistique, et patinage de vitesse. Cliquez sur OK.
Ou, dans la ligne Labels section du tableau croisé dynamique, cliquez sur le menu déroulant à côté de Étiquettes de ligne du tableau croisé dynamique, cliquez sur (Sélectionner tout) pour supprimer toutes les sélections, puis faites défiler et sélectionnez Tir à l'arc, plongée sous-marine, Escrime, Patinage artistique, et patinage de vitesse. Cliquez sur OK.
Dans les champs de tableau croisé dynamique. de la table des médailles, faites glisser la médaille à la zone VALUES. Étant donné que les valeurs doivent être numériques, Excel change automatiquement Médaille au comte de la Médaille.
A partir de la table des médailles, sélectionnez à nouveau la médaille et faites-la glisser dans la zone des filtres.
Nous allons filtrer le tableau croisé dynamique pour afficher uniquement les pays ou régions avec plus de 90 médailles au total. Voici comment.
Dans le tableau croisé dynamique, cliquez sur le menu déroulant à droite de la colonne des étiquettes.
Sélectionner les filtres de valeur et sélectionnez supérieur à ....
Votre tableau croisé dynamique ressemble à l'écran suivant.
Avec peu d'effort, vous avez maintenant un tableau croisé dynamique de base qui comprend des champs de trois tables différentes. Ce qui a fait cette tâche si simple étaient les relations préexistantes entre les tables. Parce que les relations de table existent dans la base de données source, et parce que vous avez importé toutes les tables en une seule opération, Excel pourrait recréer ces relations de table dans son modèle de données.
Mais si vos données proviennent de sources différentes, ou sont importées à une date ultérieure? En règle générale, vous pouvez créer des relations avec de nouvelles données basées sur des colonnes correspondantes. Dans l'étape suivante, vous importez des tables supplémentaires, et d'apprendre comment créer de nouvelles relations.
Importer des données à partir d'une feuille de calcul
Maintenant, nous allons importer les données d'une autre source, cette fois à partir d'un classeur existant, puis spécifiez les relations entre nos données existantes et les nouvelles données. Les relations permettent d'analyser les collections de données dans Excel, et créer des visualisations intéressantes et immersive à partir des données que vous importez.
Commençons par la création d'une feuille de calcul vide, puis importer des données à partir d'un classeur Excel.
Insérez une nouvelle feuille de calcul Excel, et nommez-le sport.
Accédez au dossier qui contient les fichiers de données d'échantillons téléchargés et ouvrez OlympicSports.xlsx.
Sélectionnez et copiez les données dans Sheet1. Si vous sélectionnez une cellule avec des données, telles que la cellule A1, vous pouvez appuyer sur Ctrl + A pour sélectionner toutes les données adjacentes. Fermez le classeur OlympicSports.xlsx.
Sur la feuille de sport, placez votre curseur dans la cellule A1 et collez les données.
Avec les données encore mis en évidence, appuyez sur Ctrl + T pour formater les données sous forme de tableau. Vous pouvez également formater les données dans un tableau à partir du ruban en sélectionnant HOME> sous forme de tableau. Étant donné que les données ont-têtes, sélectionnez Ma table a des en-têtes dans la fenêtre Créer un tableau qui apparaît, comme indiqué ici.
Mise en forme des données d'une table présente de nombreux avantages. Vous pouvez attribuer un nom à une table, ce qui le rend facile à identifier. Vous pouvez également établir des relations entre les tables, ce qui permet l'exploration et l'analyse de tableaux croisés dynamiques, Power Pivot et Power View.
Enregistrez le classeur.
Importer des données à l'aide de copier et coller
Maintenant que nous avons importé des données à partir d'un classeur Excel, nous allons importer des données à partir d'un tableau, on trouve sur une page Web, ou toute autre source à partir de laquelle nous pouvons copier et coller dans Excel. Dans les étapes suivantes, vous ajoutez les villes hôtes olympiques d'une table.
Insérez une nouvelle feuille de calcul Excel, et nommez-le héberge.
Sélectionnez et copiez le tableau suivant, y compris les en-têtes de table.
Dans Excel, placez votre curseur dans la cellule A1 de la feuille d'hôtes et coller les données.
Formater les données sous forme de tableau. Comme décrit plus haut dans ce tutoriel, vous appuyez sur Ctrl + T pour formater les données sous forme de tableau ou de HOME> sous forme de tableau. Étant donné que les données ont-têtes, sélectionnez Ma table a des en-têtes dans la fenêtre Créer un tableau qui apparaît.
Nom de la table. Dans OUTILS DE TABLE> DESIGN> Propriétés localiser le champ Nom de la table et des hôtes de type.
Enregistrez le classeur. Votre classeur ressemble à l'écran suivant.
Maintenant que vous avez un classeur Excel avec des tables, vous pouvez créer des relations entre eux. Créer des relations entre les tables vous permet de écrasez les données des deux tables.
Créer une relation entre les données importées
Vous pouvez immédiatement commencer à utiliser les champs dans votre tableau croisé dynamique à partir des tables importées. Si Excel ne peut pas déterminer comment intégrer 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 apprendrez à créer une relation entre les données que vous avez importé de différentes sources.
Faites défiler la liste pour voir les nouvelles tables que vous venez d'ajouter.
Cette notification se produit parce que vous avez utilisé les champs d'une table qui ne fait pas partie du modèle de données sous-jacentes. Une façon d'ajouter une table au modèle de données est de créer une relation à une table qui est déjà dans le modèle de données. Pour créer la relation, l'une des tables doivent avoir une colonne unique, de non-répétition, les valeurs. Dans les exemples de données, la table Disciplines importées à partir de la base de données contient un champ avec des codes de sport, appelé SportID. Ces mêmes codes sportifs sont présents en tant que champ dans les données Excel nous importions. Créons la relation.
Cliquez sur Créer. dans la zone de tableau croisé dynamique champs en surbrillance pour ouvrir la boîte de dialogue Créer relation, comme indiqué dans l'écran suivant.
Dans le tableau. choisissez Disciplines de la liste déroulante.
Dans la colonne (étrangères). choisissez SportID.
Dans le tableau connexe. choisissez Sport.
Dans la zone RANGS, déplacer Sport au-dessus de la discipline. C'est beaucoup mieux, et le tableau croisé dynamique affiche les données que vous voulez le voir, comme indiqué dans l'écran suivant.
Dans les coulisses, Excel est la construction d'un modèle de données qui peut être utilisé dans le classeur, dans un tableau croisé dynamique, graphique croisé dynamique, Power Pivot, ou tout rapport Voir le produit Power. relations de table sont la base d'un modèle de données, et ce qui détermine les chemins de navigation et de calcul.
Checkpoint et quiz
Revoyez ce que vous avez appris
Vous avez maintenant un classeur Excel qui comprend un tableau croisé dynamique l'accès aux données dans plusieurs tables, dont plusieurs que vous avez importés séparément. Vous avez appris à importer à partir d'une base de données, d'un autre classeur Excel et de copier des données et le coller dans Excel.
Pour faire ensemble le travail de données, il fallait créer une relation de table Excel utilisée pour établir une corrélation entre les lignes. Vous avez également appris que d'avoir des colonnes dans une table qui correspondent aux données dans une autre table est essentielle pour créer des relations, et pour rechercher des lignes liées.
Vous êtes prêt pour le prochain tutoriel dans cette série. Voici un lien:
Vous voulez voir à quel point vous vous rappelez ce que vous avez appris? Voici votre chance. Le jeu-questionnaire met en évidence les caractéristiques, les capacités ou les exigences vous avez appris dans ce tutoriel. Au bas de la page, vous trouverez les réponses. Bonne chance!
Question 1: Pourquoi est-il important de convertir les données importées dans les tables?
R: Vous n'avez pas à les convertir en tables, car toutes les données importées est automatiquement transformé en tables.
B: Si vous convertissez les données importées dans les tables, ils seront exclus du modèle de données. Seulement quand ils sont exclus du modèle de données sont-ils disponibles dans les tableaux croisés dynamiques, Power Pivot et Power View.
C: Si vous convertissez des données importées dans les tables, ils peuvent être inclus dans le modèle de données et être mis à la disposition des tableaux croisés dynamiques, Power Pivot et Power View.
D: Vous ne pouvez pas convertir les données importées dans les tables.
Question 2: Laquelle des sources de données suivantes peuvent vous importer dans Excel, et inclure dans le modèle de données?
A: Les bases de données d'accès, et bien d'autres bases de données ainsi.
B: les fichiers existants Excel.
Tout ce qui précède
Question 3: Dans un tableau croisé dynamique, ce qui se passe lorsque vous réorganisez les champs dans les quatre zones de tableau croisé dynamique Fields?
R: Rien - vous ne pouvez pas modifier l'ordre des champs une fois que vous les placez dans les zones champs de tableau croisé dynamique.
B: Le format de tableau croisé dynamique est modifié pour refléter la mise en page, mais les données sous-jacentes n'est pas affecté.
C: Le format de tableau croisé dynamique est modifié pour refléter la mise en page, et toutes les données sous-jacentes est modifié de façon permanente.
D: Les données sous-jacente est modifiée, ce qui entraîne de nouveaux ensembles de données.
Question 4: Lors de la création d'une relation entre les tables, ce qui est nécessaire?
A: Aucune table ne peut avoir une colonne qui contient des valeurs uniques, 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 de ce qui précède est correct.
Bonne réponse: C
Réponse correcte: D
Réponse correcte: B