Zelfstudie: Gegevens importeren in Excel en een gegevensmodel maken

Zelfstudie: Gegevens importeren in Excel en een gegevensmodel maken

Opmerking:  We willen u graag zo snel mogelijk de meest recente Help-inhoud in uw eigen taal bieden. Deze pagina is automatisch vertaald en kan grammaticale fouten of onnauwkeurigheden bevatten. Wij hopen dat deze inhoud nuttig voor u is. Kunt u ons onder aan deze pagina laten weten of de informatie nuttig voor u was? Hier is het Engelstalige artikel ter referentie.

Inleiding:    Dit is de eerste zelfstudie in een reeks die is ontworpen om u vertrouwd te maken met de functies van Excel voor het samenvoegen en analyseren van gegevens. Met deze zelfstudies gaat u een compleet nieuwe werkmap maken in Excel en deze vervolgens verfijnen. Daarnaast leert u hoe u een gegevensmodel bouwt en hoe u indrukwekkende, interactieve rapporten maakt met Power View. De zelfstudies zijn ontworpen om functies en voorzieningen voor BI (Business Intelligence) van Microsoft te demonstreren die beschikbaar zijn in Excel, draaitabellen, Power Pivot en Power View.

Opmerking: In dit artikel worden de gegevensmodellen in Excel 2013. Echter de dezelfde gegevens modelleren en de Power Pivot-functies in Excel 2013 geïntroduceerd ook van toepassing op Excel-2016.

In deze zelfstudies leert u hoe u gegevens importeert en verkent in Excel, een gegevensmodel bouwt en verfijnt met Power Pivot en interactieve rapporten maakt met Power View die u kunt publiceren, beveiligen en delen.

Deze reeks bestaat uit de volgende zelfstudies:

  1. Gegevens importeren in Excel 2013 en een gegevensmodel maken

  2. Gegevensmodel relaties uitbreiden met Excel, Power Pivot en DAX

  3. Op kaarten gebaseerde Power View-rapporten maken

  4. Internetgegevens gebruiken en standaardwaarden voor Power View-rapporten instellen

  5. Indrukwekkende Power View-rapporten maken: deel 1

  6. Indrukwekkende Power View-rapporten maken: deel 2

In deze zelfstudie begint u met een lege Excel-werkmap.

Deze zelfstudie bestaat uit de volgende gedeelten:

Gegevens importeren uit een database

Gegevens importeren uit een spreadsheet

Gegevens importeren via kopiëren en plakken

Een relatie tussen geïmporteerde gegevens maken

Controlepunt en toets

Aan het einde van deze zelfstudie kunt u enkele vragen beantwoorden om te kijken of u alles goed hebt begrepen.

Deze zelfstudie reeks gebruikt gegevens met een beschrijving van Olympische medailles, landen en verschillende Olympische sportevenementen hostingprovider. U kunt doornemen tot en met elk zelfstudie in volgorde. Ook zelfstudies voor Excel 2013 gebruiken met Power Pivot ingeschakeld. Voor meer informatie over Excel 2013, klikt u op hier. Voor hulp bij het inschakelen van Power Pivot, klikt u op hier.

Gegevens importeren uit een database

We beginnen deze zelfstudie met een lege werkmap. In dit gedeelte gaat u verbinding maken met een externe gegevensbron en die gegevens vervolgens importeren in Excel voor verdere analyse.

Laten we beginnen met het downloaden van enkele gegevens van internet. De gegevens hebben betrekking op de verschillende Olympische medailles en staan in een Microsoft Access-database.

  1. Klik op de volgende koppelingen om het downloaden van bestanden die we tijdens deze zelfstudie reeks gebruiken. Download elk van de vier bestanden naar een locatie die gemakkelijk toegankelijk is, zoals Downloads of Mijn documenten, of een nieuwe map die u maakt:
    > OlympicMedals.accdb Access-database
    > OlympicSports.xlsx Excel-werkmap
    > Population.xlsx Excel-werkmap
    > DiscImage_table.xlsx Excel-werkmap

  2. Open een lege werkmap in Excel 2013.

  3. Klik op gegevens > Externe gegevens ophalen > uit Access. Het lint wordt aangepast dynamisch op basis van de breedte van de werkmap, zodat de opdrachten op het lint er iets anders dan de volgende schermen. Het eerste scherm ziet u het lint wanneer een werkmap is breed, de tweede afbeelding ziet u een werkmap die formaat is gewijzigd om alleen een gedeelte van het scherm te zetten.

    Gegevens importeren uit Access

    Gegevens importeren uit Access met klein lint

  4. Selecteer het OlympicMedals.accdb-bestand dat u hebt gedownload en op openen. Het venster van de volgende tabel selecteren wordt weergegeven, met de tabellen die zijn gevonden in de database. Tabellen in een database zijn vergelijkbaar met werkbladen of tabellen in Excel. Schakel het selectievakje selectie van meerdere tabellen inschakelen in en selecteer alle tabellen. Klik vervolgens op OK.

    Het tabelvenster selecteren

  5. Het venster gegevens importeren wordt weergegeven.

    Opmerking: U ziet u het selectievakje onderaan in het venster waarmee u kunt deze gegevens toevoegen aan het gegevensmodel, weergegeven in het volgende scherm. Een gegevensmodel wordt automatisch gemaakt wanneer u importeren of met twee of meer tabellen tegelijk werken. Een gegevensmodel integreert de tabellen, zodat de uitgebreide analyse met behulp van draaitabellen, Power Pivoten Power View. Wanneer u tabellen importeert uit een database, worden de bestaande databaserelaties tussen deze tabellen wordt gebruikt voor het maken van het gegevensmodel in Excel. Het gegevensmodel is transparant in Excel, maar u kunt weergeven en wijzigen direct met de invoegtoepassing Power Pivot . Het gegevensmodel is verderop in deze zelfstudie in detail beschreven.


    Selecteer de optie Draaitabelrapport , die de tabellen wordt geïmporteerd in Excel en een draaitabel bereidt voor het analyseren van de geïmporteerde tabellen, en klik op OK.

    Het venster Gegevens importeren

  6. Nadat de gegevens zijn geïmporteerd, wordt een draaitabel gemaakt van de geïmporteerde tabellen.

    Lege draaitabel

De gegevens zijn nu geïmporteerd in Excel en het gegevensmodel is automatisch voor u gemaakt. Dit betekent dat u de gegevens kunt gaan verkennen.

Gegevens onderzoeken met een draaitabel

Het verkennen van geïmporteerde gegevens is eenvoudig met behulp van een draaitabel. In een draaitabel sleept u velden (vergelijkbaar met kolommen in Excel) uit tabellen (zoals de tabellen die u net uit de Access-database hebt geïmporteerd) naar verschillende gebieden van de draaitabel om de weergave van de gegevens aan te passen. Een draaitabel heeft vier gebieden: FILTERS, KOLOMMEN, RIJEN en WAARDEN.

De vier gebieden van een draaitabel

Misschien moet u even experimenteren om te bepalen naar welk gebied u een veld moet slepen. U kunt zo veel of weinig velden slepen als u wilt, totdat de gegevens op de gewenste manier worden weergegeven in de draaitabel. U kunt gerust dingen uitproberen door velden naar verschillende gebieden van de draaitabel te slepen. De onderliggende gegevens blijven ongewijzigd wanneer u velden in een draaitabel rangschikt.

Laten we de gegevens van de verschillende Olympische medailles in de draaitabel verkennen, beginnend met Olympische kampioenen geordend op discipline, type medaille, en het land of de regio van de atleet.

  1. Vouw in de Draaitabelvelden, de uit de tabel Medals door te klikken op de pijl ernaast. Het veld NOC_CountryRegion vinden in de uitgevouwen tabel Medals en sleep deze naar het gebied kolommen . NOC staat voor nationale Olympische commissies, dat wil de afdeling voor een land of regio zeggen.

  2. Sleep vervolgens Discipline uit de tabel Disciplines naar het gebied RIJEN.

  3. Laten we Disciplines filteren om maar vijf sporten weer te geven: Archery, Diving, Fencing, Figure Skating en Speed Skating. U kunt dit doen binnen de lijst Draaitabelvelden of via het filter Rijlabels in de draaitabel zelf.

    1. Klik ergens in de draaitabel om er zeker van te zijn dat de Excel-draaitabel is geselecteerd. Ga in de lijst Draaitabelvelden naar de uitgevouwen tabel Disciplines en beweeg de muisaanwijzer over het veld Discipline. U ziet nu rechts van het veld een pijl-omlaag. Klik op de pijl, klik op (Alles selecteren)om alle selecties ongedaan te maken, blader omlaag en selecteer Archery, Diving, Fencing, Figure Skating en Speed Skating. Klik op OK .

    2. Ga naar het gedeelte Rijlabels van de draaitabel, klik op de pijl-omlaag naast Rijlabels in de draaitabel, klik op (Alles selecteren) om alle selecties ongedaan te maken, blader omlaag en selecteer Archery, Diving, Fencing, Figure Skating en Speed Skating. Klik op OK .

  4. Ga naar de lijst Draaitabelvelden en sleep Medal van de tabel Medals naar het gebied WAARDEN. Aangezien waarden numeriek moeten zijn, wordt Medal automatisch door Excel gewijzigd in Count of Medal.

  5. Selecteer opnieuw Medal in de tabel Medals en sleep het veld naar het gebied FILTERS.

  6. Laten we de draaitabel nu filteren om alleen die landen of regio's weer te geven met in totaal meer dan 90 medailles. U doet dit als volgt.

    1. Klik in de draaitabel op de pijl-omlaag rechts van Kolomlabels.

    2. Selecteer Waardefilters en selecteer Groter dan….

    3. 90 typt in het laatste veld (aan de rechterkant). Klik op OK.
      Het venster Waardefilter

Uw draaitabel ziet er nu uit zoals in het volgende scherm.

Bijgewerkte draaitabel

U hebt nu in enkele stappen een eenvoudige draaitabel gemaakt met velden uit drie verschillende tabellen. Deze taak is zo eenvoudig omdat de relaties tussen de tabellen al bestaan. Omdat de tabelrelaties al aanwezig zijn in de brondatabase en u alle tabellen in één bewerking hebt geïmporteerd, konden die relaties opnieuw worden gecreëerd in het gegevensmodel in Excel.

Stel echter dat de gegevens afkomstig zijn uit verschillende bronnen of later worden geïmporteerd? Meestal kunt u relaties met nieuwe gegevens maken op basis van overeenstemmende kolommen. In de volgende stap importeert u aanvullende tabellen en leert u hoe u nieuwe relaties maakt.

Gegevens importeren uit een spreadsheet

Laten we nu gegevens gaan importeren uit een andere bron, een bestaande werkmap dit keer. Daarna geven we de relaties tussen onze bestaande gegevens en de nieuwe gegevens aan. Met behulp van relaties kunt u verzamelingen gegevens analyseren in Excel, en interessante en boeiende visualisaties maken van de gegevens die u importeert.

We beginnen met het maken van een leeg werkblad en gaan dan gegevens importeren uit een Excel-werkmap.

  1. Voeg een nieuw Excel-werkblad in en geef dit de naam Sports.

  2. Blader naar de map met de gedownloade bestanden met voorbeeldgegevens en open OlympicSports.xlsx.

  3. Selecteer en kopieer de gegevens in Sheet1. Als u een cel met gegevens selecteert, zoals cel A1, kunt u met Ctrl + A alle aangrenzende gegevens selecteren. Sluit de werkmap OlympicSports.xlsx.

  4. Plaats in het werkblad Sports de cursor in cel A1 en plak de gegevens.

  5. Druk op Ctrl + T om op te maken van de gegevens op als een tabel met de gegevens die nog steeds gemarkeerd. U kunt ook de gegevens opmaken als een tabel uit het lint door in te schakelen Start > opmaken als tabel. Aangezien de gegevens kopteksten bevat, selecteer dat de tabel bevat kopteksten in het venster Tabel maken die wordt weergegeven, zoals hier wordt getoond.

    Het venster Tabel maken

    Opmaak van de gegevens op als een tabel, heeft vele voordelen. U kunt een naam toewijzen aan een tabel, waardoor u deze gemakkelijk kunt identificeren. U kunt ook relaties tussen tabellen, zodat verkennen en te analyseren in draaitabellen, Power Pivoten Power View.

  6. De naam van de tabel. In Hulpmiddelen voor tabellen > ontwerpen > Eigenschappen, zoekt u het veld Tabelnaam en typ Sports. De werkmap eruit ziet het volgende scherm.
    Een tabel een naam geven in Excel

  7. Sla de werkmap op.

Gegevens importeren via kopiëren en plakken

Nu we gegevens uit een Excel-werkmap hebben geïmporteerd, kunnen we eens proberen gegevens uit een tabel op een webpagina te importeren. Deze procedure kunt u ook gebruiken om te importeren uit een andere bron die kopiëren en plakken in Excel ondersteunt. In de volgende stappen voegt u de steden waar Olympische Spelen zijn gehouden toe uit een tabel.

  1. Voeg een nieuw Excel-werkblad in en geef dit de naam Hosts.

  2. Selecteer en kopieer de volgende tabel, inclusief de tabelkoppen.

City

NOC_CountryRegion

Alpha-2 Code

Edition

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

Berlijn

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. Plaats in Excel de cursor in cel A1 van het werkblad Hosts en plak de gegevens.

  2. Maak de gegevens op als een tabel. Zoals eerder beschreven in deze zelfstudie, drukt u op Ctrl + T om de gegevens als een tabel op te maken. U kunt ook START > Opmaken als tabel kiezen. Aangezien de gegevens kopteksten bevatten, selecteert u Mijn tabel heeft veldnamen in het venster Tabel maken dat verschijnt, zoals hier wordt weergegeven.

  3. Geef de tabel een naam. Zoek in HULPMIDDELEN VOOR TABELLEN> ONTWERPEN > Eigenschappen het veld Tabelnaam en typ Hosts.

  4. Selecteer de kolom Edition, ga naar het tabblad START en stel het gegevenstype van de kolom in op Getal met 0 decimalen.

  5. Sla de werkmap op. Uw werkmap ziet er nu uit zoals in het volgende scherm.

Hosttabel

U hebt nu een Excel-werkmap met tabellen en kunt dus relaties maken tussen deze tabellen. Op deze manier kunt u de gegevens uit de twee tabellen combineren.

Een relatie tussen geïmporteerde gegevens maken

U kunt velden uit de geïmporteerde tabellen direct gebruiken in uw draaitabel. Als Excel niet kan bepalen hoe een veld moet worden opgenomen in de draaitabel, moet er een relatie worden gelegd met het bestaande gegevensmodel. In de volgende stappen leert u hoe u een relatie maakt tussen gegevens die u uit verschillende bronnen hebt geïmporteerd.

  1. Klik op Blad1, boven aanDraaitabelvelden, klikt u opalle om weer te geven van de volledige lijst met beschikbare tabellen, zoals wordt weergegeven in het volgende scherm.
    Klik op Alle in de lijst Draaitabelvelden om alle beschikbare tabellen weer te geven

  2. Blader door de lijst om te zien van de nieuwe tabellen die u zojuist hebt toegevoegd.

  3. Vouw Sporten en selecteer Sport toe te voegen aan de draaitabel. Zoals u ziet dat wordt u gevraagd of u een relatie maken zoals gezien in het volgende scherm.
    De melding dat er waarschijnlijk een relatie moet worden toegevoegd

    Deze melding wordt weergegeven, omdat u velden uit een tabel die geen deel uitmaakt van het onderliggende gegevensmodel gebruikt. Een manier om een tabel toevoegen aan het gegevensmodel is een relatie aan een tabel die al in het gegevensmodel te maken. Als u wilt de relatie hebt gemaakt, moet een van de tabellen een kolom met unieke, niet-herhaald waarden. In de voorbeeldgegevens bevat de tabel Disciplines is geïmporteerd uit de database een veld met sporten codes, SportID genoemd. Deze dezelfde Sport-codes zijn aanwezig als veld in de Excel-gegevens die zijn geïmporteerd. Laten we de relatie maken.

  4. Klik op CREATE... in het gemarkeerde gebied in de Draaitabelvelden om het dialoogvenster Relatie maken , zoals wordt weergegeven in het volgende scherm.

    Het venster Relatie maken

  5. Selecteer in de vervolgkeuzelijst Tabel de waarde Disciplines.

  6. Selecteer in de vervolgkeuzelijst Column (Foreign) de waarde SportID.

  7. Selecteer in de vervolgkeuzelijst Gerelateerde tabel de waarde Sports.

  8. Selecteer in de vervolgkeuzelijst Gerelateerde kolom (primair) de waarde SportID.

  9. Klik op OK .

Wijzigingen in de draaitabel om de nieuwe relatie aan te geven. Maar de draaitabel wordt niet goed helemaal nog vanwege de volgorde van velden in het gebied rijen . Discipline is een subcategorie van een bepaald sport, maar omdat we Discipline hierboven Sport in het gebied rijen gerangschikt , het niet geordend behoren. Het volgende scherm ziet deze ongewenste ordening.
Draaitabel met ongewenste ordening

  1. Klik in het gebied rijen verplaatsen Sport hierboven Discipline. Dat is beter en de draaitabel de gegevens die hoe u zien, wilt zoals wordt weergegeven in het volgende scherm worden weergegeven.

    Draaitabel met gecorrigeerde ordening

In Excel wordt op de achtergrond een gegevensmodel gebouwd dat u kunt gebruiken in de werkmap, in een draaitabel of een draaigrafiek, in Power Pivot en in een Power View-rapport. Relaties tussen tabellen zijn de basis van een gegevensmodel, en bepalen welke navigatie- en berekeningspaden er beschikbaar zijn.

In de volgende zelfstudie, Relaties in gegevensmodel uitbreiden met Excel 2013, Power Pivot en DAX, gaat u wat u hier hebt geleerd verder uitwerken. U gaat dan het gegevensmodel uitbreiden met behulp van een krachtige en visuele Excel-invoegtoepassing met de naam Power Pivot. U leert ook hoe u kolommen in een tabel berekent en hoe u een berekende kolom gebruikt om een tabel toe te voegen aan uw gegevensmodel die niet op een andere manier is gerelateerd.

Samenvatting en quiz

Overzicht van wat u hebt geleerd

U hebt nu een Excel-werkmap met een draaitabel waarin gegevens uit meerdere tabellen worden gebruikt, waarvan u er een aantal afzonderlijk hebt geïmporteerd. U hebt geleerd om gegevens te importeren uit een database en uit een andere Excel-werkmap. Daarnaast hebt u ook gegevens geïmporteerd door deze te kopiëren en te plakken in Excel.

Om de gegevens te combineren, hebt u een tabelrelatie gedefinieerd die door Excel wordt gebruikt om de rijen aan elkaar te relateren. U weet nu ook dat het essentieel is voor het maken van relaties om kolommen te hebben in een tabel die zijn gerelateerd aan gegevens in een andere tabel. Dit is trouwens ook noodzakelijk voor het opzoeken van gerelateerde rijen.

U kunt nu verder met de volgende zelfstudie in deze reeks. Klik op deze koppeling om naar die zelfstudie te gaan:

Relaties in gegevensmodel uitbreiden met Excel 2013, Power Pivot en DAX

TOETS

Wilt u controleren of u alles nog weet? Dat kan. In de volgende quiz komen de functies, mogelijkheden of vereisten aan bod waaraan aandacht is besteed in deze zelfstudie. De antwoorden vindt u onder aan de pagina. Succes!

Vraag 1: Waarom is het belangrijk om geïmporteerde gegevens te converteren naar tabellen?

A: U hoeft de gegevens niet te converteren naar tabellen omdat alle geïmporteerde gegevens automatisch worden omgezet in tabellen.

B: Als u geïmporteerde gegevens converteert naar tabellen, worden de gegevens uitgesloten van het gegevensmodel. Alleen dan zijn de gegevens beschikbaar in draaitabellen, Power Pivot en Power View.

C: Als u geïmporteerde gegevens converteert naar tabellen, kunt u ze opnemen in het gegevensmodel en kunnen de gegevens worden gebruikt in draaitabellen, Power Pivot en Power View.

D: Geïmporteerde gegevens kunnen niet worden geconverteerd naar tabellen.

Vraag 2: Welke van de volgende gegevensbronnen kunt u importeren in Excel en opnemen in het gegevensmodel?

A: Access-Databases, en ook verschillende andere databases.

B: Bestaande Excel-bestanden.

C: Alles wat u kunt kopiëren en plakken in Excel en kunt opmaken als een tabel, inclusief gegevenstabellen op websites, in documenten of iets anders dat kan worden geplakt in Excel.

D: Alle bovenstaande antwoorden.

Vraag 3: Wat gebeurt er in een draaitabel wanneer u de volgorde van velden in de vier gebieden van de lijst Draaitabelvelden wijzigt?

A: Niets. U kunt de volgorde van velden niet meer wijzigen nadat u deze hebt toegevoegd aan de gebieden in de lijst Draaitabelvelden.

B: De indeling van de draaitabel wordt aangepast aan de lay-out, maar de onderliggende gegevens blijven ongewijzigd.

C: De indeling van de draaitabel wordt aangepast aan de lay-out en alle onderliggende gegevens worden blijvend gewijzigd.

D: De onderliggende gegevens worden gewijzigd, wat resulteert in nieuwe gegevensgroepen.

Vraag 4: Wat is er nodig om een relatie tussen tabellen te definiëren?

A: Geen van de tabellen mag een kolom bevatten met unieke, niet-herhaald waarden.

B: Eén tabel mag geen onderdeel zijn van de Excel-werkmap.

C: De kolommen moeten niet worden geconverteerd naar tabellen.

D: Geen van de bovenstaande beweringen is juist.

Antwoorden

  1. Juiste antwoord: C

  2. Juiste antwoord: D

  3. Juiste antwoord: B

  4. Juiste antwoord: D

Notities: Gegevens en afbeeldingen in deze reeks zelfstudies zijn gebaseerd op:

  • Olympics Dataset van Guardian News & Media Ltd.

  • Vlagafbeeldingen van CIA Factbook (cia.gov)

  • Bevolkingsgegevens van The World Bank (worldbank.org)

  • Pictogrammen voor Olympische sporten door Thadius856 en Parutakupiu

Uw Office-vaardigheden uitbreiden
Training verkennen
Als eerste nieuwe functies krijgen
Deelnemen aan Office Insiders

Was deze informatie nuttig?

Bedankt voor uw feedback.

Hartelijk dank voor uw feedback! Het lijkt ons een goed idee om u in contact te brengen met een van onze Office-ondersteuningsagents.

×