Zelfstudie: Gegevens importeren in Excel en een gegevensmodel maken

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.

In deze reeks met zelfstudies worden gegevens gebruikt die betrekking hebben op Olympische medailles, landen die de Olympische Spelen organiseren en verschillende Olympische sportevenementen. U kunt de zelfstudies het beste in de aangegeven volgorde doornemen. 

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 bestanden te downloaden die we tijdens deze reeks zelfstudies gebruiken. Download elk van de vier bestanden naar een locatie die gemakkelijk toegankelijk is, zoals Downloads of Mijn documenten, of naar een nieuwe map die u maakt:
   > OlympicMedals.accdb Access-database
   Excel-werkmap
   OlympicSports.xlsx > > Population.xlsx Excel-werkmap
   Excel-werkmapDiscImage_table.xlsx >

2. Open een lege werkmap in Excel.

3. Klik op GEGEVENS > Externe gegevens ophalen > Uit Access. Het lint wordt automatisch aangepast op basis van de breedte van uw werkmap, zodat de opdrachten op het lint er iets anders kunnen uitzien dan hieronder. Het eerste scherm is met een brede werkmap en het tweede scherm met een werkmap waarvan het formaat is aangepast om slechts een deel van het scherm in beslag te nemen.
   
   
   
   
   
    

4. Selecteer het bestand OlympicMedals.accdb dat u hebt gedownload en klik op Openen. U ziet het onderstaande venster Tabel selecteren, met de tabellen die in de database zijn gevonden. 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.
   
   

5. Het venster Gegevens importeren wordt weergegeven.

   : Onder aan het volgende scherm ziet u het selectievakje Deze gegevens toevoegen aan het gegevensmodel. Er wordt automatisch een gegevensmodel gemaakt wanneer u gelijktijdig twee of meer tabellen importeert of hiermee werkt. Een gegevensmodel integreert de tabellen, waardoor uitgebreide analyse mogelijk is met behulp van draaitabellen, Power Pivot en Power View. Wanneer u tabellen importeert uit een database, worden de bestaande databaserelaties tussen die tabellen gebruikt om het gegevensmodel te maken in Excel. Het gegevensmodel is transparant in Excel, maar u kunt het model rechtstreeks bekijken en wijzigen met de invoegtoepassing Power Pivot. Het gegevensmodel wordt verderop in deze zelfstudie uitgebreid besproken.

   
   Selecteer de optie Draaitabelrapport , waarmee de tabellen in Excel worden geïmporteerd en een draaitabel wordt voorbereid voor het analyseren van de geïmporteerde tabellen. Klik vervolgens op OK.
   
   

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

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.

Het kan enige experimenten duren om te bepalen naar welk gebied een veld moet worden gesleept. U kunt zo veel of weinig velden uit uw tabellen slepen als u wilt, totdat de draaitabel uw gegevens weergeeft zoals u deze wilt zien. U kunt het verkennen door velden naar verschillende gebieden van de draaitabel te slepen; de onderliggende gegevens worden niet beïnvloed 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 onder Draaitabelvelden de tabel Medals uit door op de bijbehorende pijl te klikken. Sleep het veld NOC_CountryRegion in de uitgevouwen tabel Medals naar het gebied KOLOMMEN. NOC staat voor Nationale Olympische Comités, de organisatie-eenheid voor een land of regio.

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 ervoor te zorgen dat de Excel-draaitabel is geselecteerd. In de lijst Draaitabelvelden , waar de tabel Disciplines is uitgevouwen, beweegt u de muisaanwijzer over het veld Discipline en wordt rechts van het veld een vervolgkeuzepijl weergegeven. Klik op de vervolgkeuzelijst, klik op (Alles selecteren) om alle selecties te verwijderen, schuif omlaag en selecteer Boogschieten, Duiken, Schermen, Kunstschaatsen en Snelschaatsen. 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. Typ 90 in het laatste veld (helemaal rechts). Klik op OK.
      

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

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

Nu gaan we gegevens importeren uit een andere bron, dit keer uit een bestaande werkmap, en vervolgens de relaties tussen onze bestaande gegevens en de nieuwe gegevens opgeven. Met relaties kunt u verzamelingen gegevens in Excel analyseren en interessante en meeslepende 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 terwijl de gegevens nog zijn gemarkeerd om de gegevens als een tabel op te maken. U kunt de gegevens ook via het lint opmaken als een tabel door START > Opmaken als tabel te selecteren. Aangezien de gegevens kopteksten bevatten, selecteert u Mijn tabel heeft veldnamen in het venster Tabel maken dat verschijnt, zoals hier wordt weergegeven.
   
   
   
   Het opmaken van de gegevens als een tabel heeft veel voordelen. U kunt een naam toewijzen aan een tabel, waardoor u deze gemakkelijk kunt identificeren. U kunt ook relaties tussen tabellen maken, waardoor u de gegevens kunt verkennen en analyseren in draaitabellen, Power Pivot en Power View.

6. Geef de tabel een naam. Zoek in HULPMIDDELEN VOOR TABELLEN> ONTWERPEN > Eigenschappen het veld Tabelnaam en typ Sports. De werkmap ziet eruit zoals in het volgende scherm.
   

7. Sla de werkmap op.

Gegevens importeren via kopiëren en plakken

Nu we gegevens uit een Excel-werkmap hebben geïmporteerd, gaan we gegevens importeren uit een tabel die we vinden op een webpagina of een andere bron van waaruit we kunnen kopiëren en plakken in Excel. In de volgende stappen voegt u de Olympische hoststeden toe vanuit 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.

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 in deze zelfstudie is beschreven, drukt u op Ctrl + T om de gegevens op te maken als een tabel of vanuit HOME > Opmaken als tabel. Omdat de gegevens kopteksten bevatten, selecteert u Mijn tabel bevat kopteksten in het venster Tabel maken dat 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.

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 opAlles om de volledige lijst met beschikbare tabellen weer te geven, zoals wordt weergegeven in het volgende scherm.
   

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

3. Vouw Sports uit en selecteer Sport om dit veld toe te voegen aan de draaitabel. U wordt nu door Excel gevraagd een relatie te maken, zoals in het onderstaande scherm.
   
    

   Deze melding wordt weergegeven omdat u velden hebt gebruikt uit een tabel die geen deel uitmaakt van het onderliggende gegevensmodel. Eén manier om een tabel aan het gegevensmodel toe te voegen, is het maken van een relatie met een tabel die al wel aanwezig is in het gegevensmodel. Om de relatie te maken, moet een van de tabellen een kolom hebben met unieke, niet-herhaalde waarden. In de voorbeeldgegevens bevat de tabel Disciplines die u hebt geïmporteerd uit de database een veld met sportcodes, met de naam SportID. Deze sportcodes zijn ook als veld aanwezig in de Excel-gegevens die we hebben geïmporteerd. Dan gaan we nu de relatie maken.

4. Klik op MAKEN... in het gemarkeerde gebied van de lijst Draaitabelvelden om het dialoogvenster Relatie maken te openen (zie het volgende scherm).
   
   

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 .

De draaitabel wordt aangepast aan de nieuwe relatie. Maar de draaitabel ziet er nog niet goed uit, vanwege de volgorde van velden in het gebied RIJEN . Discipline is een subcategorie van een bepaalde sport, maar omdat we Discipline boven Sport hebben gerangschikt in het gebied RIJEN , is deze niet goed georganiseerd. In het volgende scherm ziet u deze ongewenste volgorde.

1. Verplaats Sport in het gebied RIJEN boven Discipline. Dat is veel beter en in de draaitabel worden de gegevens weergegeven zoals u deze wilt zien, zoals wordt weergegeven in het volgende scherm.
   
   

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, Gegevensmodelrelaties uitbreiden met behulp van Excel, Power Pivoten DAX, bouwt u voort op wat u hier hebt geleerd en gaat u 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 die berekende kolom gebruikt, zodat een anders niet-gerelateerde tabel kan worden toegevoegd aan uw gegevensmodel.

Controlepunt en quiz

Overzicht van wat u hebt geleerd

U hebt nu een Excel-werkmap met een draaitabel die toegang heeft tot gegevens in meerdere tabellen, waarvan u er verschillende afzonderlijk hebt geïmporteerd. U hebt geleerd om te importeren uit een database, uit een andere Excel-werkmap en door gegevens te kopiëren en in Excel te plakken.

Om de gegevens samen te laten werken, moest u een tabelrelatie maken die in Excel is gebruikt om de rijen te correleren. U hebt ook geleerd dat het hebben van kolommen in de ene tabel die correleren met gegevens in een andere tabel essentieel is voor het maken van relaties en 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:

Zelfstudie: Relaties in gegevensmodellen uitbreiden met Excel, Power Pivot en DAX

QUIZ

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 staan 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