Gratis tips Excel - Het Gegevensmodel en draaitabellen in Excel 2013


Tips Excel		go to : Index

Tip 193
Deze tip werd geschreven door Excel teacher

Het Gegevensmodel en draaitabellen in Excel 2013
In vorige versies van Excel moest men om data te analyseren van verschillende bronnen deze data eerst ingeven of importeren in Excel, en om deze data aan mekaar te linken gebruik maken van allerlei functies zoals “Verticaal Zoeken”, “Som Als”, Sommen Als” enz. En er kon ook maar een beperkt aantal rijen geïmporteerd worden (maximum rijen in Excel 2010 = 1.048.576)

Nadelen van het gebruik van het gegevensmodel: Je kan de gegevens niet meer groeperen. Bepaalde berekeningen zijn niet meer mogelijk, zoals “Product” en “Aantal nummers”. De opties “Berekende velden” en “Berekende items” zijn niet beschikbaar.

In deze tip beperken we ons tot de versies van Excel 2013 die niet beschikken over de PowerPivot add-in, om gegevens te analyseren door middel van het gegevensmodel en draaitabellen.
Wens je de bestanden uit deze tip, klik hier.

Welke data kunnen we toevoegen aan het gegevensmodel?
Excel tabellen van het actieve bestand. Excel tabellen van een niet geopend bestand. C.S.V (tekst) bestanden. Access tabellen. Tabellen van een website. Tabellen van een SQL server. Data van Analysis services. Data van ODATA gegevensfeed. Data van XML-gegevens Data van Microsoft query. Data van onbekende indeling met de wizard gegevensverbinding. Data van een reeds bestaande verbinding.

In ons voorbeeld gaan we van vijf bronnen gegevens toevoegen aan het gegevensmodel en van deze gegevens een draaitabel maken, nl. Drie Excel tabellen van niet geopende bestanden, een Access tabel en een tekst C.S.V. bestand.

Data toevoegen aan het gegevensmodel.
We openen eerst een nieuw Excel bestand. Vervolgens gaan we op het lint naar Gegevens / Externe gegevens ophalen / Van Access. In het dialoogvenster “Gegevensbron selecteren” navigeer je naar de map waar de bestanden opgeslagen zijn en kies je het Access bestand (1) van waaruit je de gegevens wil importeren en klik op Openen (2), in dit voorbeeld het bestand “Verkoopcijfers”.


In het volgende dialoogvenster “Gegevens importeren” kies je voor “Draaitabellenrapport” (3) , “Bestaand werkblad” (4) en “Deze gegevens toevoegen aan het gegevensmodel” (5) en klik “Ok”


De gegevens worden nu toegevoegd aan het gegevensmodel.

Vervolgens gaan we op het lint terug naar Gegevens / Externe gegevens ophalen / Van Access In het dialoogvenster “Gegevensbron selecteren” navigeer je naar de map waar de bestanden opgeslagen zijn en kies als bestandstype “Alle bestanden” (6) en selecteer je het Excel bestand “Producten” (7) en klik op “Openen” (8).


In het dialoogvenster “Tabel selecteren” plaats je een vinkje bij “De eerste rij met gegevens bevat kolomkoppen” (9), selecteer de eerste tabel “Product” en klik op “Ok”


In het volgende dialoogvenster “Gegevens importeren” kies je voor “Alleen verbinding maken” (10) en “Deze gegevens toevoegen aan het gegevensmodel (11)” en klik “Ok”


Herhaal de stappen 6 tot en met 11 maar selecteer nu de tweede tabel “Productsubcategory” van het Excel bestand. We herhalen nogmaals de stappen 6 tot en met 11 en selecteren het Excel bestand “Kalender” en de tabel “Kalender”. Tip: gebruik steeds een kalender tabel met een kolom met opeenvolgende dagen om gegevens te filteren, vooral als je wil filteren op dagen!

Als laatste gaan we gegevens van een tekstbestand toevoegen aan het gegevensmodel.

Ga op het lint naar Gegevens / Externe gegevens ophalen / Van Tekst. In het dialoogvenster “Gegevensbron selecteren” navigeer je naar de map waar de bestanden opgeslagen zijn en kies je het bestand “Channel” (12) en klik “Importeren” (13).


In het dialoogvenster “Wizard Tekst importeren – Stap 1 van 3” kies je voor “Gescheiden” (14) als gegevenstype en zet selecteer je “Mijn gegevens bevatten kopteksten” (15) en klik op “Volgende”.


In het dialoogvenster “Wizard Tekst importeren – Stap 2 van 3” kies je voor “Tab” (16) als scheidingsteken en klik op “Volgende”.


In het dialoogvenster “Wizard Tekst importeren – Stap 3 van 3” klik je op Voltooien.


In het volgende dialoogvenster “Gegevens importeren” kies je voor “Alleen verbinding maken” (17) en “Deze gegevens toevoegen aan het gegevensmodel (18)” en klik “Ok”


Relaties maken tussen tabellen.
Het geen we nu moeten doen is relaties maken tussen de geïmporteerde tabellen. Ga op het lint naar Gegevens / Relaties In het dialoogvenster “Relaties beheren” klik op je op “Nieuw” (19).


In het dialoogvenster “Relaties maken” maken we onze eerste relatie tussen de tabellen “Verkoopcijfers” en Kalender”. We maken een relatie tussen de kolom van “DateKey” van de tabel “Verkoopcijfers” en kolom “DateKey” en de Gerelateerde tabel “Kalender” en klikken Ok. (We maken dus een relatie tussen een tabel met een kolom die niet unieke gegevens bevat, en een tabel met kolom met unieke gegevens, de kolom “DateKey” van tabel “Verkoopcijfers “(extern)” bevat meerdere dezelfde datums en de kolom “DateKey” van tabel “Kalender “(kolom primair)” bevat unieke gegevens).


We komen terug in het dialoogvenster “Relaties beheren” en klikken weer op “Nieuw” om de volgende relatie te maken nl tussen kolom “ProductSubCategoryKey” van tabel “Product” en de kolom “ProductSubCategoryKey” van tabel” ProductSubCategoryKey”. We herhalen deze stappen voor de volgende relaties
• Van kolom “channelKey” van tabel “Verkoopcijfers” naar kolom “ChannelKey” van tabel “Channel”. Van kolom “ProductKey” van tabel “Verkoopcijfers” naar kolom “ProductKey” van tabel “Product”.

Nadat alle relaties gemaakt zijn klikken we op “Sluiten”. Het dialoogvenster “Relaties beheren” nadat alle relaties aangemaakt zijn (20).


De draaitabel maken.
Je kiest voor Invoegen - Draaitabel


Kies vervolgens voor Nieuw werkblad, een Externe gegevensbron gebruiken en Verbinding kiezen. Hierna verschijnt het volgende venster, waarbij je kiest voor het tabblad Tabellen en Alle tabellen


In het venster “Draaitabelvelden” klikken we op “Alles” (21), zodat alle tabellen getoond worden (22).


Om velden toe te voegen aan het rapport klik je op het driehoekje (23) van de betreffende tabel zodat deze zich ontplooit en waaruit je dan velden kan verslepen (24) naar de gebieden. (Draaitabellen maken wordt uitvoerig besproken cursus Excel 2010 , bij het schrijven van deze tip was de cursus Excel 2013 nog niet online).




GratisCursus.be denkt aan jullie.
> Index