Dit is een onderdeel waarin veel functionaliteit is toegevoegd aan de 2010 versie (zie paragraaf 22.6)

Hoofdstuk 22: Draaitabellen *2010 22.0 Inleiding Dit hoofdstuk had ook ‘snel gegevens samenvatten’ genoemd kunnen worden. Excel biedt een heel degelijk hulpmiddel om met tabellen gegevens samen te vatten en snel de opmaak en details van de samenvatting te veranderen. De functionaliteit van de draaitabellen is erg uitgebreid en dit onderdeel kan dan ook worden gezien als erg geavanceerd in het gebruik van Excel. Echter, met kennis van een paar basisfuncties kun je al veel doen en in dit hoofdstuk leer je die. Dit is een onderdeel waarin veel functionaliteit is toegevoegd aan de 2010 versie (zie paragraaf 22.6).

22.1 Wat zijn Draaitabellen? Draaitabellen vatten gegevens samen uit een database – gegevens die opgemaakt zijn in een tabel waar de eerste rij koppen bevat en de andere rijen bevatten categorieën of waarden:

CATEGORIEËN

KOPPEN

WAARDEN

De manier waarop de gegevens worden samengevat is flexibel, maar over het algemeen bestaat de gegevenstabel die uit de Draaitabel voortkomt uit waarden opgeteld uit een

© 2010 Excel with Business

1

Hoofdstuk 22: Draaitabellen

aantal of alle categorieën in de database. Dus met de gegevens hierboven kun je bijvoorbeeld snel deze Draaitabel maken:

Dit soort van samenvatting kan gemaakt worden met de SOM.ALS functie (zie hoofdstuk 21: Gegevens Samenvatten), maar de voordelen van het gebruiken van een Draaitabel zijn: (i) (ii)

(iii)

(iv)

Snelheid De tabel hierboven is vanuit de gegevens gemaakt met slechts zeven muisklikken. Gemak De tabel is gemaakt zonder een enkele Excel-functie te gebruiken, alleen de zeven muisklikken. Flexibiliteit Met bijvoorbeeld een paar extra klikken, kun je de weergegeven gegevens veranderen in nominale uitgaven of het weergegeven aantal jaren verminderen. Toegang tot dynamische gegevens Draaitabellen kunnen direct gekoppeld worden aan externe gegevens zoals een database of boekhoudsysteem en het resultaat kan automatisch bijgewerkt worden.

22.2 Draaitabellen maken: een recept De brongegevens opzetten De brongegevens moeten worden opgemaakt als een database, zoals hierboven beschreven. Dat betekent dat er kolomkoppen moeten staan boven een tabel met kolommen die categorieën of waarden bevatten. Om de Draaitabel op te zetten, selecteer je de hele database en klik je op de knop Draaitabel op het Tabblad Invoegen op het Lint1:

1

Binnen een bereik maak je een willekeurige cel in de het bereik actief en Excel biedt vervolgens dat bereik als bron voor de draaitabel aan.


2


Een dialoogvenster verschijnt waarin je de selectie kunt bevestigen en andere opties kunt aanpassen. Je hebt de brongegevens al geselecteerd, dus klik op OK:

Als een van de kolommen in de brongegevens een kop mist, kan de Draaitabel niet gemaakt worden en zie je de volgende foutmelding:

Als de kolom zonder kopnaam belangrijk is (je wilt deze gebruiken in de Draaitabel als categorie of waarde), geef het dan een logische naam. Als je niet van plan bent om die kolom te gebruiken, dan kun je de kolom helemaal verwijderen of een standaardtekst als kop gebruiken. Het maakt dan niet zoveel uit wat dat de kop is, alleen dat zo de Draaitabel gemaakt kan worden. Het is een goed idee om te zorgen dat de koppen uniek zijn: als er


3


duplicaten zijn, dan zal de Draaitabel nog steeds gemaakt worden, maar kunnen de koppen verwarring veroorzaken.

Selecteer de gegevens die je wilt weergeven Als je door de stappen hierboven hebt gevolgd, dan zal je een scherm zien dat hierop lijkt:

Aan de linkerkant van het werkblad is een vak; dit is de lege Draaitabel. Aan de rechterkant, vind je de ‘Lijst met draaitabelvelden’ waarin je kunt selecteren welke gegevens je wilt samenvatten. De kopnamen van het bereik vindt je hier terug. Als je deze lijst niet ziet, klik dan in de Draaitabel links, het wordt dan weergegeven. Je moet de waarden selecteren die je wilt optellen en de categorieën waarmee je wilt samenvatten. De gegevens in het voorbeeld hierboven geven de overheidsuitgaven van het Verenigd Koninkrijk weer per jaar en per departement. Als je alleen de totale uitgaven per jaar wilt zien, dan moet je ‘Jaar’ en ‘Bestedingen’ selecteren in het vak Lijst met draaitabelvelden:


4


Als je dit doet, begin je automatisch met het bouwen van de Draaitabel aan de linkerkant.

Verander de lay-out De stappen hierboven hebben een Draaitabel gemaakt die er ongeveer zo uitziet:


5


De laatste stap in het proces is het veranderen van de opmaak van de tabel naar wens. Dit kan gemakkelijk gedaan worden via de Lijst met draaitabelvelden rechts. Bijvoorbeeld, het slepen van het ‘Jaar’-veld van het ‘Rijlabels’ vak rechtsonder naar het lege ‘Kolomlabels’ vak zorgt ervoor dat de ‘Jaar’-categorie als kolom in plaats van als rij verschijnt en de tabel horizontaal over de pagina wordt geplaatst:

Samenvatting De stappen hierboven zijn samengevat:   



zorg dat de brongegevens als een database zijn opgemaakt selecteer de hele database, klik op de knop Draaitabel en op OK in het volgende dialoogvenster vink de vakjes aan in de Lijst met draaitabelvelden om te selecteren welke waarden weergegeven worden (als ‘waarden’ om berekeningen te maken of ‘categorieën’ om de gegevens in te verdelen) gebruik de Lijst met draaitabelvelden opnieuw om velden te verslepen zodat de opmaak naar wens is


6


22.3 Een paar geavanceerde tips en valkuilen Veranderen waar een Draaitabel wordt ingevoegd In het voorbeeld hierboven werd de Draaitabel gemaakt in een ‘eigen’ nieuw werkblad. Misschien heb je de optie in het dialoogvenster al zien staan toen je op de knop Draaitabel klikte:

Het selecteren van ‘Bestaand werkblad’ zorgt ervoor dat de Draaitabel wordt gemaakt op een bestaand werkblad naar keuze. Je zult voorzichtig moeten zijn dat het bestaande werk door het invoegen van de Draaitabel niet overlapt wordt. De vorm en grootte van de tabel veranderen aan de rechterzijde en de onderkant van de draaitabel, afhankelijk van welke categorieën je invoert, dus laat hier ruimte voor open..

Ik kan de Lijst met draaitabelvelden niet zien Het is mogelijk om dit vak te verbergen. Als je het niet kunt zien als je op de Draaitabel klikt, klik dan met de rechtermuisknop zodat je het volgende snelmenu ziet:


7


Selecteer ‘Lijst met velden weergeven’, zoals aangegeven.

De hele tabel filteren Eén van de opties in de Lijst met draaitabelvelden zorgt ervoor dat je alle resultaten van de Draaitabel kunt filteren per categorie. Dit is het Rapportfilter (zie volgende afbeelding).

Het gebruik hiervan is het beste uit te leggen met een voorbeeld. Als in het scherm hierboven het veld ‘Functie’ wordt toegevoegd aan het rapport en het veld wordt naar het


8


Rapportfilter vak gesleept, wordt er een vervolgkeuzemenu toegevoegd aan de Draaitabel waarin alleen de uitgaven voor een bepaalde functie in de resultaten word geselecteerd:

Let op: de Draaitabel zal als resultaat alleen de totale waarde van de geselecteerde filter categorie geven (4. ‘Economische Zaken’ in dit voorbeeld).

22.4 Slicers (Excel 2010) Een Slicer is een zwevend object met knoppen waarmee je een Draaitabel kunt filteren met waarden voor een specifiek veld. Hieronder is een cel geselecteerd in de Draaitabel en vervolgens Slicer gekozen uit de Filter groep op het Tabblad Invoegen. Daarna is het veld Land gekozen uit de lijst beschikbare velden in de Draaitabel. Er verschijnt dan een Slicer met een set knoppen van landen. Als je nu op een van de knoppen klikt, dan worden de gegevens zo gefilterd dat de Draaitabel alleen de waarde voor dat land weergeeft. Tot dusver lijkt dit niet op een grote stap – je kunt het Land veld als een rapport filter toevoegen om ongeveer hetzelfde resultaat te krijgen zonder je zorgen te maken over het nieuwe Slicer hulpmiddel. Toch zijn Slicers bedoelt om makkelijker interactief te werken met Draaitabellen en beter te zien wat de Draaitabel weergeeft. Als je maar één land selecteert, dan laat het rapportfilter alleen de naam van het land zien. Echter, als je meerdere landen selecteert, geeft het ‘Meerdere items’ weer, zodat het onmogelijk is om in één oogopslag te zien wat de Draaitabel precies laat zien. Hier is de Slicer onder de Draaitabel geplaatst. Het Tabblad Hulpmiddelen voor Slicers wordt gebruikt om het aantal kolommen te veranderen zodat het beter in het venster past. Daarna


9


is Ctrl+rechtermuisknop gebruikt om meerdere landen te selecteren. In tegenstelling tot het rapportfilter laat de Slicer zien welke landen geselecteerd zijn:

Meerdere items kunnen worden geselecteerd in de Slicer met standaard Windowstechnieken zoals Ctrl+rechtermuisknop om een aantal losse cellen te selecteren, Shiftrechter muisknop om een blok te selecteren en Ctrl+slepen met in gedrukte rechtermuisknop. De opmaak van de Slicer kan aangepast worden door gebruik te maken van de verschillende stijlen voor het weergeven van geselecteerde velden of lege velden. De verbeterde zichtbaarheid en gebruiksgemak van het filteren van een Draaitabel met een Slicer zijn handig, maar misschien nog niet heel overtuigend. Het wordt indrukwekkender als er meer dan één Slicer ingevoegd wordt of een Slicer aan meer dan één Draaitabel verbonden. Onderstaand zijn drie Slicers ingevoegd voor Land, Besteldatum en Productnaam. Terwijl items worden geselecteerd in één Slicer, veranderen de andere Slicers mee en de items waarvoor geen gegevens in de Draaitabel staan, worden nu aan het einde van de lijst met velden in de Slicer weergegeven:


10


Ten slotte wordt het verbinden van een Slicer aan meer dan één Draaitabel besproken. Er zijn 3 aparte Draaitabellen gemaakt, allemaal gebaseerd op factuurgegevens. Het betreft een top 10 van verkopers met de verkoopgegevens per land, een grafiek die verkopen per medewerker weergeeft en een tweede grafiek die verkopen per besteldatum weergeeft. Daarna is er een Slicer ingevoegd gebaseerd op Productnaam. Om deze Slicer te verbinden met alle Draaitabellen, klik je op de ‘Draaitabelverbindingen’ knop in het tabblad Hulpmiddelen voor Slicers. Hiermee kun je kiezen welke Draaitabellen je wilt verbinden. Wanneer de Slicers zijn verbonden met de drie Draaitabellen worden de filters op alle Draaitabellen toegepast als de geselecteerde items veranderen. Dit betekent dat je een interactieve presentatie kunt maken van de gegevens zonder een enkele formule te gebruiken:


11


22.5 Herhaalde koppen (Excel 2010) Hoewel PowerPivot en Slicers de grootste verbeteringen op dit vlak waren in Excel 2010, is er nog iets anders wat de moeite waard is. Het gebruik van Draaitabellen om gegevens te ordenen die vervolgens opgemaakt en verwerkt moeten worden, kan veel tijd besparen. Als je meerdere velden toevoegt aan de kolommen van een Draaitabel, tonen de meest linkse kolommen alleen de koppen op de eerste rij van elke sectie. Een voorbeeld laat dit duidelijk zien:


12


Hier zijn Land en Klantnaam toegevoegd aan de kolomlabels. De waarden voor elke klant zijn gegroepeerd binnen het land, maar de namen van de landen zijn alleen aangegeven in de eerste rij van elke sectie.Vanaf Excel 2010 is er een nieuwe optie beschikbaar, in te stellen via de knop Rapportindeling op het tabblad Ontwerp binnen de Hulpmiddelen voor Daaitabellen. Het kiezen van Alle Itemlabels Herhalen voegt de namen van landen toe aan elke rij, zoals beneden aangegeven:

22.6 PowerPivot (Excel 2010) PowerPivot is een gratis hulpprogramma voor Excel en is ontworpen voor het slim werken met grote gegevensbestanden in de bedrijfsomgeving. Omdat het mogelijkheden biedt voor databasebeheer en een andere manier van werken met Excel formules, is PowerPivot een van de meest drastische verbeteringen die ooit in Excel is geïntroduceerd.

Gegevens Een van de beloftes van Microsoft voor PowerPivot is dat het gemakkelijk miljoenen (zelfs honderden miljoenen) gegevensrecords kan bijhouden. Om het goed te testen, gebruik je een tabel met meer dan 2 miljoen rijen.


13


Je kunt PowerPivot downloaden via http://www.microsoft.com/powerpivot(lees de instructies eerst goed door zodat je de juiste versie voor jouw computer kiest). Nadat je PowerPivot hebt geïnstalleerd, zul je een PowerPivot tabblad zien op het Lint. Klik in deze tab op de PowerPivot-venster knop om het hulpprogramma te starten:

Op het tabblad Start van het PowerPivot-venster staat een knop met ‘Externe gegevens gegevens ophalen’, die weer knoppen heeft om gegevens op te halen vanuit verschillende bronnen en een Vernieuwen knop. De ‘Uit een andere database’ knop geeft opties voor ‘Uit SQL Server’, ‘Uit Access’, ,Uit Access, ‘Uit Analysis Services of PowerPivot’. In het voorbeeld is er gekozen voor Access en vervolgens in de Wizard Tabel importeren de ‘Bladeren’ knop om de database te vinden met de ongeveer 2 miljoen gegevensrecords.


14


In het voorbeeld is er al een geschikte tabel met gegevensrecords om mee te werken in PowerPivot, dus gebruik de optie Selecteren in een lijst met tabellen en weergaven om de te importeren gegevens uit te kiezen. De andere optie is Query schrijven waarmee de te importeren gegevens worden opgegeven, waarmee PowerPivot een query maakt die de gegevens opvraagt waarmee je wilt werken. Je kunt meerdere tabellen selecteren, Power Pivot vragen Gerelateerde Tabellen te selecteren en voor elke tabel een Voorbeeld en Filteren optie te gebruiken:


15


De filteroptie geeft de gegevens weer net als in een Excel en met dezelfde geavanceerde filteropties via de vervolgkeuzelijsten in elke kolomkop:

Wanneer je de gegevens hebt die je wilt analyseren, klik je op de knop ‘Voltooien’. Je ziet een scherm met de voortgang van het importeren van je gegevenstabel of tabellen naar PowerPivot:

Wanneer de gegevens zijn geïmporteerd, wordt elke tabel weergegeven als een apart ‘blad’ binnen het PowerPivot-venster. De gegevens worden weergegeven zoals in een Excel 2010 tabel met gefilterde kolomkoppen. Opties op het Start tabblad van het PowerPivot Lint zorgen ervoor dat je de notatie voor individuele kolommen kunt kiezen:


16


Het Power Pivot venster werkt nu als een database-toepassing, of als een erg uitgebreide Microsoft Query. Het Power Pivot tabblad Ontwerpen bevat hulpmiddelen voor maken en beheren van relaties tussen verschillende tabellen.

Rapporten De laatste fase is het veranderen van de verzameling van miljoenen rijen met gegevens in betekenisvolle rapporten of grafieken. Vanuit het Start tabblad in het Power Pivot-venster klik je op het vervolgkeuzemenu van de Draaitabelknop in de Rapporten groep om de beschikbare rapporten en grafieken te bekijken:

Stel je kiest hier voor ‘Vier grafieken’. Daarna kun je besluiten of je er een nieuw werkblad voor aanmaakt of ze plaatst op een bestaande. Voor de ‘Vier grafieken’ optie worden vier nieuwe werkbladen aangemaakt, elk met de gegevens voor één grafiek.

Eerst zie je vier lege Draaigrafieken. Klik in elke grafiek en selecteer de velden die je in die grafiek wilt opnemen om zo je de set van grafieken te maken. Daarna kun je een Slicer invoegen door het criteriaveld naar de Verticale of Horizontale Slicers vakken te slepen en


17


om het automatisch te verbinden aan alle vier Draaitabellen die je grafiekgegevens bevatten:


18


Dit is een onderdeel waarin veel functionaliteit is toegevoegd aan de 2010 versie (zie paragraaf 22.6)

Recommend Documents