3 PowerPivot voor Excel

3.1 Waar vind je PowerPivot in Excel?

PowerPivot is een standaard onderdeel van Excel 2013. Maar als je Excel voor het eerst opent, dan vind je nergens in het lint PowerPivot terug. PowerPivot is een invoegtoepassing, net als de Oplosser. Een invoegtoepassing is een extern programma dat je toevoegt in Excel. Daarna benader je het programma door middel van het lint.

Hoewel PowerPivot een invoegtoepassing is, maakt Excel 2013 voor een aantal standaard onderdelen gebruik van de PowerPivot-database. Deze wordt echter in Excel niet PowerPivot genoemd, maar gegevensmodel. Bij het maken van een draaitabel kun je bijvoorbeeld kiezen voor Toevoegen aan gegevensmodel. Impliciet wordt dan een tabel in de PowerPivot-database toegevoegd. Om een professioneel PowerPivot-model te maken, is het nodig om met de PowerPivot-invoegtoepassing te werken.

Je kunt PowerPivot voor Excel 2010 gratis downloaden via de internetpagina www.microsoft.nl/PowerPivot of www.powerpivot.com. Let wel op: er zijn twee versies beschikbaar. Je moet eerst weten of je een 32- of 64-bitsversie van Excel hebt. Je vindt deze informatie terug in Excel. Klik de groene tab met Bestand aan. Kies dan Help.

Afbeelding 3.1: Hier vind je de informatie over Excel.

Het helpscherm zie je in afbeelding 2. Daar vind je welke versie op jouw computer is geïnstalleerd.

Afbeelding 3.2: Welke versie van Excel gebruik je?

Let meteen op welke Office-variant is geïnstalleerd. De PowerPivot-invoegtoepassing werkt namelijk niet op de studenten- en thuisversie van Excel 2010. Via de downloadpagina van de bovengenoemde websites kun je jouw versie van PowerPivot downloaden. Lees de installatie-instructies! Staat het programma op de harde schijf, zoek het dan op en dubbelklik erop. De invoegtoepassing wordt vervolgens geïnstalleerd. Als je daarna Excel opent, dan is PowerPivot klaar voor gebruik. Je herkent het aan een nieuw tabblad: PowerPivot.

Mocht je het tabblad na je installatie niet tegenkomen, volg dan de onderstaande procedure om het tabblad in te schakelen.

Om de invoegtoepassing te activeren, doe je het volgende. Kies de groene knop Bestand en vervolgens in het linker groene blok voor Opties (afbeelding 3). In de linker kolom kies je vervolgens voor de knop Invoegtoepassingen. Kies onder in het venster, bij Beheren voor de COM-invoegtoepassingen (afbeelding 4).

Afbeelding 3.3: Het venster Opties voor Excel.

Afbeelding 3.4: De COM-invoegtoepassingen activeren.

Klik nu op de Start-knop. Het venster Com-invoegtoepassingen wordt getoond (afbeelding 5). Zet nu een vinkje voor Microsoft Office PowerPivot for Excel 2013. Plaats meteen ook een vinkje voor Power View. Klik op OK.

Afbeelding 3.5: Het venster COM-invoegtoepassingen.

In Excel 2010 is alleen PowerPivot for Excel beschikbaar. Power View wordt niet ondersteund in deze Excel-versie.

Zoals je kunt zien in afbeelding 6, is het PowerPivot-tabblad nu beschikbaar in het lint.

Afbeelding 3.6: Het PowerPivot-tabblad in het lint.

Misschien had je ook een tabblad Power View in het lint verwacht, maar dat is er niet. Power View vind je terug in het tabblad Invoegen, sectie Rapporten, de knop Power View. Je ziet deze knop in afbeelding 7.

Afbeelding 3.7: In het tabblad Invoegen vind je de Power View-mogelijkheden.

Nu PowerPivot tot je beschikking staat, ga je PowerPivot nader bekijken. Power View komt in hoofdstuk 10 aan de orde.

3.2 Hoe ziet PowerPivot eruit?

PowerPivot bestaat uit twee onderdelen. Je beschikt over een extra tabblad in het lint van Excel: het eerste onderdeel. Door middel van dit tabblad kom je bij het tweede onderdeel: de PowerPivot-omgeving.

In het tweede onderdeel van PowerPivot importeer je de gegevens: hier regel je vrijwel altijd de invoer. Berekeningen maak je zowel in de Excel-draaitabel als in de PowerPivot-omgeving. Het verschil in berekeningen tussen beide omgevingen is belangrijk. In hoofdstuk 4 komt dit verder aan bod.

Heb je de PowerPivot-omgeving gestart, dan blijkt dat het PowerPivot-scherm een eigen lint heeft. In de volgende paragraaf wordt uitgelegd hoe je tussen Excel en PowerPivot ‘schakelt’.

3.3 De Excel-omgeving van PowerPivot

In afbeelding 8 zie je het PowerPivot-tabblad in het lint van Excel. Je ziet de volgende secties en mogelijkheden:

Afbeelding 3.8: Het PowerPivot-lint in Excel.

De belangrijkste knop staat (zoals gebruikelijk in Office) links in het tabblad, namelijk Beheren in de sectie Gegevensmodel. Met deze knop open je het PowerPivot-scherm: het tweede onderdeel. Heb je al een keer op de knop geklikt (en is het venster met PowerPivot al geopend), dan kun je de knop gebruiken om te schakelen vanuit de Excel- naar de PowerPivot-omgeving. Later in dit hoofdstuk leggen we uit wat er verder achter deze knop schuilt.

De volgende sectie is Berekeningen. In deze sectie kun je werken als je een draaitabel hebt gemaakt. De linkse knop heet Berekende velden. Die is verder onderverdeeld in een Nieuw berekend veld en Berekende velden beheren. Een berekend veld is een berekening in een draaitabel en voegt een draaitabelveld toe. In hoofdstuk 7 ga je werken met berekende velden. Een Excel-draaitabel kent ook berekende velden, maar de PowerPivot-variant kent meer mogelijkheden.

KPI’s staat voor Key Performance Indicators. KPI’s werken hetzelfde als een budget, waarbij je de verwachting (Budget) afzet tegen de realiteit (Actuals). Waar budgetten gekoppeld zijn aan financiële informatie, is een KPI onafhankelijk van financiën, maar gericht op het functioneren van het bedrijf. Zo definieert een organisatie KPI’s om ziekteverzuim (in dagen of aantal werknemers) of downtime (in uren) van een machinepark te volgen. Je kunt eenvoudig KPI-informatie in de vorm van een draaitabelveld aan je draaitabel toevoegen. In hoofdstuk 9 laten we zien hoe je met KPI’s werkt in PowerPivot.

De volgende knop heeft te maken met het uitlijnen van slicers. In afbeelding 9 zie je een voorbeeld van hoe je werkt met slicers. Met slicers beheer je variabelen: ze geven gebruikers de mogelijkheid om filterselecties uit te voeren op de gegevens in een draaitabel. Daarmee geef je hen een snelle en handige methode om het resultaat aan te passen aan hun wensen.

Afbeelding 3.9: Slicers in een draaitabel.

Een draaitabel is dynamisch aan de rechterzijde en aan de onderzijde. Het is dus handig de slicers aan de linkerzijde (de knop Verticaal uitlijnen) of bovenzijde (de knop Horizontaal uitlijnen) van de draaitabel uit te lijnen. Als je een slicer uitlijnt, schuift Excel de draaitabel op om ruimte te maken voor de slicer. Op deze manier kun je snel een nette opmaak van het werkblad maken. Let op: met deze knoppen maak je geen slicers. Ze werken alleen met reeds gemaakte slicers. Slicers worden behandeld in hoofdstuk 6.

In de sectie Tabellen importeer je Excel-tabellen die zich in hetzelfde Excel model bevinden in PowerPivot. Je gebruikt daarvoor de knop Toevoegen aan gegevensmodel. Staat de cursor in een geldig Excel-bereik, dan maakt PowerPivot daar een tabel van. Het resultaat van een geïmporteerde Excel-tabel heet een gekoppelde tabel. In hoofdstuk 4 leer je hoe je een gekoppelde tabel maakt.

Maak een tabel van je Excel-bereik voordat je de Excel-gegevens importeert in PowerPivot. Je hebt dan meer controle over het resultaat en je zult in staat zijn om de tabel een naam te geven die beter aansluit bij de inhoud van de gegevens.

Heb je een of meerdere gekoppelde tabellen geïmporteerd in PowerPivot, dan wordt die informatie ververst als je in PowerPivot die tabel actief maakt. Om alle gekoppelde tabellen te verversen, kun je ook de knop Alles bijwerken gebruiken.

De knop Detecteren in de sectie Relaties is ervoor bedoeld om PowerPivot zelf te laten zoeken naar mogelijke relaties tussen verschillende tabellen in je PowerPivot-model. Naar onze mening moet je deze optie niet gebruiken, omdat het beter is om goed na te denken over de structuur van je model en om bewust relaties te leggen.

De laatste knop in het Excel PowerPivot-tabblad is Instellingen. Klik je op deze knop, dan opent Opties en diagnostieken voor PowerPivot (afbeelding 10).

Afbeelding 3.10: Het venster Opties en diagnostieken voor PowerPivot.

In het tabblad Taal kun je de door jou gewenste taal instellen. Omdat PowerPivot een invoegtoepassing is, kun je in een andere taal werken dan in Excel! In dit boek gebruiken wij PowerPivot in het Nederlands.

Het lint van PowerPivot (zie afbeelding 11) in Excel 2010 ziet er op aantal punten anders uit. Het kan zijn dat dit lint afwijkt van de afbeelding. PowerPivot voor Excel 2010 kent namelijk verschillende versies. De versie van de afbeelding is PowerPivot versie 2012.

Afbeelding 3.11: Het PowerPivot-lint in Excel 2010.

De knoppen zijn in principe gelijk, maar staan op een andere plaats of worden anders genoemd. De knoppen Draaitabel en Relatiedetectie zijn in Excel 2013 opgenomen in het lint van de PowerPivot-omgeving. Deze worden later in dit hoofdstuk behandeld.

Een aantal van de hierboven genoemde knoppen werkt pas als je een model hebt. In tegenstelling tot de gebruikelijke grijze knoppen, waardoor je weet dat die bepaalde functionaliteit is uitgeschakeld, kun je zonder PowerPivot-model wel de knoppen bedienen. Je krijgt dan een melding zoals in afbeelding 12.

Afbeelding 3.12: De knop kan worden gebruikt, maar de functionaliteit is niet beschikbaar.

3.4 De PowerPivot-omgeving

Tijd om PowerPivot nader te bekijken. Klik daartoe op de knop Beheren, waarna de PowerPivot-omgeving opent. Afhankelijk van jouw computer kan dit proces enige tijd in beslag nemen. Uiteindelijk ziet het scherm eruit zoals in afbeelding 13.

Het PowerPivot-scherm heeft, als je het voor de eerste keer opent, een lint met twee tabbladen: Start en Ontwerpen. Er is nog een derde tabblad Geavanceerd, dat niet standaard zichtbaar is. Je toont en verbergt het tabblad Geavanceerd door onder Bestand te kiezen voor Geavanceerde modus of Normale modus.

Afbeelding 3.13: De PowerPivot-omgeving.

Kijk naar de mogelijkheden van het tabblad Start in afbeelding 14. Je ontdekt dat veel van deze functionaliteiten ook beschikbaar zijn in Excel. De knoppen werken hetzelfde als in Excel. Het gaat dan over de knoppen in de secties Klembord, Externe gegevens ophalen, Opmaak, Sorteren en filteren en Zoeken. Je moet wel rekening houden met de structuurverschillen tussen Excel en PowerPivot. Daar waar je in Excel de bovengenoemde handelingen verricht op een (vooraf of door Excel) gedefinieerd bereik, werkt de functionaliteit in PowerPivot op volledige kolommen. Later in het boek gebruik je de knoppen.

Afbeelding 3.14: Het tabblad Start in de PowerPivot-omgeving.

Naast de sectie Externe gegevens ophalen staat nog de knop Vernieuwen. Kenmerk van PowerPivot is dat het een kopie van de gegevens maakt en je op die kopieën werkt. Afhankelijk van de grootte van de database die je importeert en de capaciteit van de computer kan het importeren van de gegevens tijd vergen. PowerPivot importeert de door jou aangegeven databases en jij beslist wanneer de informatie binnen die databases wordt ververst. Werk je in Excel met draaitabellen, dan herken je de knop Vernieuwen: met deze knop ververs je de draaitabellen.

Naast Vernieuwen staat de knop Draaitabel. Met deze knop maak je een draaitabel of grafiek in Excel. De werking van deze knop wordt verderop uitgelegd.

De sectie Berekeningen biedt je weer de mogelijkheid om KPI’s te maken en een AutoSom te maken. Je maakt dan een berekend veld in de PowerPivot-omgeving. De sectie Weergave geeft je de mogelijkheid om de informatie in de PowerPivot-omgeving op verschillende manieren weer te geven. De indeling kent een (niet-zichtbare) hiërarchie. De twee linker knoppen in de sectie schakelen tussen de Gegevensweergave (zie afbeelding 13) en de Diagramweergave (afbeelding 15). Gebruikers van Access kennen deze weergave. In deze weergave bekijk en maak je relaties tussen tabellen.

Afbeelding 3.15: De diagramweergave van PowerPivot.

De knop Verborgen items weergeven werkt in beide weergaves. Het doet wat de naam zegt: is de knop ingedrukt, dan worden verborgen items grijs weergegeven. De knop Berekeningsgebied werkt alleen als de Gegevensweergave actief is. Hij schakelt een venster in onder de actieve tabel, waar je berekeningen kunt plaatsen (afbeelding 16).

Afbeelding 3.16: Het berekeningsgebied.

Alleen de knop Bestaande verbindingen staat niet in dit tabblad. Deze knop vind je terug in het tabblad Ontwerpen. Verder is dit tabblad in PowerPivot voor Excel 2010 hetzelfde.

Het tweede tabblad van PowerPivot is het tabblad Ontwerpen (zie afbeelding 17). Dit tabblad kent meer specifieke PowerPivot-mogelijkheden.

Afbeelding 3.17: Het tabblad Ontwerpen in de PowerPivot-omgeving.

De sectie Kolommen biedt je de mogelijkheid om te werken met de kolommen in de actieve tabel. Via de knop Toevoegen kun je een kolom in de tabel invoegen, waarna je een berekening toevoegt. Het resultaat is een berekende kolom. Je kunt ook op de header van de meest rechtse kolom met de omschrijving Kolom toevoegen klikken en vervolgens in de formulebalk klikken, of meteen een = teken intypen, wat het eerste teken is voor een berekende kolom.

De knop Verwijderen verwijdert de actieve kolom uit de tabel. Dit werkt al als je een cel van een kolom hebt geselecteerd. Je kunt ook op de knop Delete drukken om hetzelfde resultaat te krijgen.

Wil je een kolom verwijderen, klik dan eerst op de kolomheader. Je weet dan precies welke kolom wordt verwijderd. Houd je de CTRL-toets ingedrukt, dan kun je door te klikken op de gewenste kolomheaders meerdere kolommen selecteren voordat je kolommen verwijdert.

De knop Blokkeren zet de actieve kolom aan de linkerkant van de tabel en plaatst een lijn rechts naast de kolom. De kolom blijft altijd in beeld, ook als de tabel meer kolommen heeft dan er in het beeld passen. Het blokkeren van kolommen heeft geen enkele invloed op de werking van deze kolommen in jouw PowerPivot-model. In afbeelding 18 zie je twee geblokkeerde kolommen.

Afbeelding 3.18: Geblokkeerde kolommen.

Klik je op de rechterkant van de knop, dan krijg je de mogelijkheid om de blokkering op te heffen. De knop werkt vrijwel identiek als de knop Eerste kolom blokkeren in het tabblad Beeld in het lint van Excel, sectie Venster onder de knop Blokkeren.

De knop Functie invoegen lijkt op de fx-knop die in Excel te vinden is in het lint, tabblad Formules. De knop staat zowel in Excel als in PowerPivot ook links naast de formulebalk. De knop opent het venster Functie invoegen (zie afbeelding 19), waar je een DAX-functie selecteert.

Afbeelding 3.19: Het PowerPivot-venster Functie invoegen.

In PowerPivot voeg je geen Excel-functie in, maar een DAX-functie. In PowerPivot word je niet zo geholpen als in Excel. In die omgeving krijg je na het selecteren van een functie het venster Functieargumenten. Dat kent PowerPivot niet. Na het selecteren van de functie keer je terug naar de formulebalk. Hoewel het begin van de DAX wordt getoond, mis je de mogelijkheid om automatisch puntkomma’s, haakjes enzovoort te plaatsen, een functionaliteit die het venster Functieargumenten wel biedt.

Met de knop Berekeningsopties kun je de manier van berekening van het PowerPivot-model instellen.

Zowel in een Excel- als in een PowerPivot-model is het niet aan te raden de mogelijkheid tot herberekening op handmatig te zetten. Omdat je de herberekening handmatig moet activeren, loop je het risico deze handeling een keer te vergeten, waardoor de resultaten van jouw model onbedoeld onbetrouwbaar kunnen zijn.

De volgende sectie heet Relaties. De knop Relatie maken is bedoeld om relaties tussen de diverse tabellen te leggen. Excel-gebruikers kennen het begrip ‘relaties’ niet. In hoofdstuk 4 wordt het concept van relaties nader uitgelegd. De tweede knop geeft je de mogelijkheid om de gemaakte relaties aan te passen.

De knop Tabeleigenschappen geeft je een overzicht van de eigenschappen van de actieve tabel. Je stelt hier de actieve tabel (opnieuw) in. De knop zorgt er niet voor dat de actieve tabel wordt ververst, maar je kunt hier wel filters plaatsen op de geïmporteerde kolommen, of kolommen verbergen.

De knop Als datumtabel markeren geeft PowerPivot de mogelijkheid om intelligente tijdfuncties te gebruiken (zie hoofdstuk 8). Je geeft in de actieve tabel aan welke kolom de datums bevat. De datums moeten uniek in de tabel staan. In de praktijk gebruik je hiervoor meestal een gekoppelde tabel.

De laatste sectie heet Bewerken en bevat de bekende knoppen Ongedaan maken en Opnieuw. De werking van deze knoppen is gelijk aan die in Excel.

Het tabblad Ontwerpen heeft in PowerPivot voor Excel 2010 de eerder genoemde knop Bestaande verbindingen.

Het derde tabblad in het lint heet Geavanceerd. Afbeelding 20 laat dit tabblad zien.

Afbeelding 3.20: Het tabblad Geavanceerd.

PowerPivot 2010 kent dit tabblad niet.

Met de knop Maken en beheren in de sectie Perspectieven kun je binnen alle tabellen specifieke selecties en daarmee perspectieven maken. Heb je veel tabellen met veel kolommen, dan loop je het risico dat je het overzicht verliest. Door middel van een perspectief laat je alleen de kolommen zien die je op dat moment gebruikt. De andere kolommen worden niet getoond, maar zijn wel aanwezig. In hoofdstuk 9 gaan we kort in op perspectieven.

De knop Impliciet berekende velden weergeven is een AAN/UIT-knop. Het impliciet berekende veld is een extra veld in de draaitabel waar je zelf niets aan hoeft te doen. De knop bepaalt of je het berekende veld in de draaitabel ziet. Het berekeningsgebied zie je niet meer, maar de daar geplaatste berekeningen blijven uiteraard gewoon beschikbaar.

Naar de knop Samentellen op is menig draaitabelgebruiker al jarenlang op zoek. Sleep je een veld naar het Waarden-vak in de draaitabel, dan kiest Excel de samenvattingsvorm die het best past bij de inhoud van de kolom. Som is de meest gebruikte samenvatting, maar zodra Excel de inhoud van de kolom in het bereik niet ziet als een bereik met getallen, maakt Excel er Aantal van. In PowerPivot kun je jouw voorkeur voor dit veld instellen.

Via de sectie Rapportage-eigenschappen kun je verschillende instellingen maken die met name voor Power View van belang zijn. In de hoofdstukken 9 en 10 gaan we daar verder op in.

3.5 De verschillen tussen Excel en PowerPivot

Tot besluit van dit hoofdstuk zetten we nog een aantal belangrijke verschillen tussen Excel en PowerPivot op een rij.

Het werkveld

In Excel werk je altijd met een statische omgeving. Het werkveld omvat steeds 16.384 kolommen en 1.048.576 rijen. Je hebt natuurlijk meerdere bladen, maar een tabel in Excel kan nooit groter zijn dan het aantal kolommen en rijen van een werkblad.

PowerPivot werkt met een dynamische omgeving. Je importeert een tabel uit een externe bron en PowerPivot maakt een eigen tabblad voor die tabel aan, waarvan de omvang automatisch wordt bepaald op basis van de omvang van de geïmporteerde tabel. De technologie achter PowerPivot zorgt ervoor dat je een enorme hoeveelheid gegevens in een PowerPivot-model kunt inladen. Deze hoeveelheid is alleen beperkt door de geheugencapaciteit van je pc. Dit is een groot verschil met Excel! In afbeelding 21 zie je een PowerPivot-tabel met een voor Excel niet te gebruiken omvang.

Daar waar in Excel alle bladen even groot zijn, leeft in PowerPivot een tabel met vijf kolommen en twee miljoen rijen in een tabblad rustig naast een ander tabblad met bijvoorbeeld achttien kolommen en vijftigduizend rijen (je kunt in PowerPivot geen twee tabellen in een tabblad plaatsen).

Afbeelding 3.21: Een tabel in PowerPivot met meer dan een miljoen rijen.

Berekeningen

In Excel werken berekeningen op basis van gedefinieerde bereiken. Een van de populaire mogelijkheden voor berekeningen is het gebruik van functies. Bijna alle functies in Excel werken door middel van argumenten, waarbinnen jij (of Excel) het bereik specificeert. In afbeelding 22 zie je een functie die werkt met een bereik.

Afbeelding 3.22: De functie SOM gebruikt een bereik.

In PowerPivot werken berekeningen over kolommen: je importeert tabellen met gelijkvormige gegevens in de kolommen. Denk hierbij aan een tabel met bijvoorbeeld een kolom met datums. Maak je in PowerPivot een berekening, dan heeft die altijd betrekking op een of meer kolommen binnen het PowerPivot-model.

Het equivalent van Excels functies heet DAX, Dynamic Analysis eXpressions. DAX-functies hebben in PowerPivot altijd een Engelse naam, ook als je met PowerPivot in het Nederlands werkt.

Een voorbeeld om de verschillen te demonstreren. Kijk je naar de Excel-functie MAAND, dan plaats je in het argument van deze functie een verwijzing naar een cel waarin een datum staat. In PowerPivot bestaat een DAX-functie genaamd MONTH, maar gebruik je die, dan gebruik je een kolom met datums. Het resultaat binnen de tabel is een nieuwe kolom met hetzelfde aantal rijen als de tabel groot is, waarbij iedere rij wordt gevuld met de berekende maand. Afbeelding 23 toont de Excel-functie en de DAX-functie.

Afbeelding 3.23: De functie MAAND en de DAX-functie MONTH.

Selecteren

In Excel selecteer je eerst een bereik – al is dat misschien slechts één cel – en kies je daarna de gewenste handeling. Je gebruikt de muis of de cursortoetsen in combinatie met de SHIFT-toets. Wil je een Bereik selecteren, dan werkt de F8-toets (Selectie uitbreiden) in combinatie met een celverwijzing ook. Je selecteert een gehele kolom door op de letter van die kolom te klikken, maar zelfs dan selecteer je een flexibel bereik.

Afbeelding 3.24: Selecties binnen Excel en PowerPivot.

In de PowerPivot-omgeving selecteer je alleen een (of meerdere) kolom(men). Het aantal rijen van de geselecteerde kolommen speelt geen enkele rol in die selectie. Dat heeft consequenties voor bijvoorbeeld de opmaakmogelijkheden van PowerPivot. In PowerPivot klik je op de omschrijving (header) van de kolom, waarna de kolom wordt geselecteerd. Vervolgens klik je op de gewenste opmaak. In afbeelding 24 staan geselecteerde bereiken in Excel en PowerPivot.