Samenvatting: Dit is de eerste zelfstudie in een serie die is ontworpen om u vertrouwd te maken met Excel en de ingebouwde functies voor het samenstellen en analyseren van gegevens. Met deze zelfstudies kunt u een volledig nieuwe Excel-werkmap maken en verfijnen, een gegevensmodel bouwen en vervolgens verbluffende interactieve rapporten maken met Power View. De zelfstudies zijn ontworpen om Microsoft Business Intelligence-functies en -mogelijkheden in Excel, draaitabellen, Power Pivot en Power View te demonstreren.
In deze zelfstudies leert u hoe u gegevens in Excel importeert en verkent, 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:
- Gegevens importeren in Excel 2016 en een gegevensmodel maken
- Relaties in gegevensmodellen uitbreiden met Excel, Power Pivot en DAX
- Op kaarten gebaseerde Power View-rapporten maken
- Internetgegevens gebruiken en standaardwaarden voor Power View-rapporten instellen
- Help voor Power Pivot
- 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.
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.
Klik op de volgende koppelingen om bestanden te downloaden die we in 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
> OlympicSports.xlsx Excel-werkmap
> Population.xlsx Excel-werkmap
> DiscImage_table.xlsx Excel-werkmapOpen een lege werkmap in Excel.
Klik op Gegevens > ophalen > uit database > uit Microsoft Access-database. 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 in het volgende scherm.
Selecteer het bestand OlympicMedals.accdb dat u hebt gedownload en klik op Importeren. Het volgende Navigator-venster wordt weergegeven met de tabellen die in de database zijn gevonden. Tabellen in een database zijn vergelijkbaar met werkbladen of tabellen in Excel. Schakel het vakje Meerdere tabellen selecteren in en selecteer alle tabellen. Klik vervolgens op Laden > laden naar.
Het venster Gegevens importeren wordt weergegeven.
Opmerking
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 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 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.
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.
Misschien moet u even experimenteren om te bepalen naar welk gebied een veld moet worden gesleept. 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 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.
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.
Sleep vervolgens Discipline uit de tabel Disciplines naar het gebied RIJEN.
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.
- Klik ergens in de draaitabel om ervoor te zorgen dat de Excel-draaitabel is geselecteerd. In de lijst met draaitabelvelden , waar de tabel Disciplines is uitgevouwen, beweegt u de muisaanwijzer over het veld Discipline. Er verschijnt een vervolgkeuzepijl rechts van het veld. Klik op de vervolgkeuzelijst, klik op **(Alles selecteren)**om alle selecties te verwijderen, scrol omlaag en selecteer Archery, Diving, Fencing, Figure Skating en Speed Skating. Klik op OK.
- 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 .
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.
Selecteer opnieuw Medal in de tabel Medals en sleep het veld naar het gebied FILTERS.
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.
- Klik in de draaitabel op de pijl-omlaag rechts van Kolomlabels.
- Selecteer Waardefilters en selecteer Groter dan….
- 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, ditmaal uit een bestaande werkmap, en vervolgens de relaties opgeven tussen de bestaande gegevens en de nieuwe gegevens. Met relaties kunt u gegevensverzamelingen in Excel analyseren en interessante en indrukwekkende 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.
Voeg een nieuw Excel-werkblad in en geef dit de naam Sports.
Blader naar de map met de gedownloade bestanden met voorbeeldgegevens en open OlympicSports.xlsx.
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.
Plaats in het werkblad Sports de cursor in cel A1 en plak de gegevens.
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 HOME > 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, zodat u deze kunt verkennen en analyseren in draaitabellen, Power Pivot en Power View.
Geef de tabel een naam. Zoek in Eigenschappen voor tabelontwerp >het veld Tabelnaam en typ Sport. De werkmap ziet eruit zoals in het volgende scherm.
Sla de werkmap op.
Gegevens importeren via kopiëren en plakken
Nadat we gegevens uit een Excel-werkmap hebben geïmporteerd, gaan we gegevens importeren uit een tabel op een webpagina of een andere bron waaruit we gegevens kunnen kopiëren en plakken in Excel. In de volgende stappen voegt u de Olympische gaststeden uit een tabel toe.
- Voeg een nieuw Excel-werkblad in en geef dit de naam Hosts.
- Selecteer en kopieer de volgende tabel, inclusief de tabelkoppen.
- Plaats in Excel de cursor in cel A1 van het werkblad Hosts en plak de gegevens.
- Maak de gegevens op als een tabel. Zoals eerder beschreven in deze zelfstudie, drukt u op Ctrl + T om de gegevens op te maken als een tabel of vanuit HOME > om op te maken als tabel. Aangezien de gegevens kopteksten bevatten, selecteert u Mijn tabel heeft kopteksten in het venster Tabel maken dat verschijnt.
- Geef de tabel een naam. Zoek in Eigenschappen van TABELONTWERP > het veld Tabelnaam en typ Hosts.
- Selecteer de kolom Edition, ga naar het tabblad START en stel het gegevenstype van de kolom in op Getal met 0 decimalen.
- 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.
Klik op Blad1 boven dedraaitabelvelden opAlles om de volledige lijst met beschikbare tabellen weer te geven, zoals in het volgende scherm.
Blader door de lijst om de nieuwe tabellen te zien die u net hebt toegevoegd.
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.Klik op MAKEN... in het gemarkeerde gebied van de lijst Draaitabelvelden om het dialoogvenster Relatie maken te openen (zie het volgende scherm).
Kies in Tabel de optie Gegevensmodeltabel: Disciplines in de vervolgkeuzelijst.
Selecteer in de vervolgkeuzelijst Column (Foreign) de waarde SportID.
Kies in Gerelateerde tabel de optie Gegevensmodeltabel: Sport.
Selecteer in de vervolgkeuzelijst Gerelateerde kolom (primair) de waarde SportID.
Klik op OK .
De draaitabel wordt aangepast aan de nieuwe relatie. De draaitabel ziet er echter nog niet uit zoals u dat wilt, vanwege de volgorde van velden in het gebied RIJEN . Discipline is een subcategorie van een bepaalde sport, maar aangezien we Discipline boven Sport hebben geplaatst in het gebied RIJEN , is het niet goed georganiseerd. In het volgende scherm ziet u deze ongewenste ordening.
Ga naar het gebied RIJEN en plaats Sport boven Discipline. Dat is veel beter. De draaitabel bevat nu de gegevens op de manier waarop u ze wilt zien, zoals in het volgende scherm.
In Excel wordt achter de schermen een gegevensmodel gebouwd dat kan worden gebruikt in de werkmap, in een draaitabel, 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 Excel, Power Pivot** en DAX**, bouwt u verder op wat u hier hebt geleerd en doorloopt u stap voor stap om het gegevensmodel uit te breiden met de krachtige en visuele Excel-invoegtoepassing Power Pivot. U leert ook hoe u kolommen in een tabel kunt berekenen en die berekende kolom kunt gebruiken om een anders niet-gerelateerde tabel aan uw gegevensmodel toe te voegen.
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 een aantal afzonderlijk hebt geïmporteerd. U hebt geleerd om gegevens te importeren vanuit een database, vanuit een andere Excel-werkmap en door gegevens te kopiëren en te plakken in Excel.
Als u de gegevens samen wilde gebruiken, moest u een tabelrelatie maken die door Excel wordt gebruikt om de rijen aan elkaar te relateren. U hebt ook geleerd dat kolommen in een tabel die overeenkomen met gegevens in een andere tabel, essentieel zijn 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 ze beschikbaar in draaitabellen, Power Pivot en Power View.
C: Als u geïmporteerde gegevens converteert naar tabellen, kunnen deze worden opgenomen in het gegevensmodel en beschikbaar worden gemaakt voor 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
- Juiste antwoord: C
- Juiste antwoord: D
- Juiste antwoord: B
- Juiste antwoord: D
Opmerking
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