Praktijk
Draaitabellen in Excel 2010 een wereld van verschil
D
oor van Excel 97 (intern versienr 8) over te stappen op Excel 2010 (intern versienr 14) maak je niet alleen een grote sprong in de tijd en een overstap naar een compleet nieuwe interface, maar ook een grote sprong in functionaliteit. Een onderdeel dat de afgelopen 13 jaar flink is gewijzigd, is de draaitabellenfunctie.
John Spronk
De verschillen in Excel 2010 ten opzichte van Excel 2007 worden in de Help-functie gedocumenteerd. De verschillen met eerdere versies van Excel vind je daar echter niet en vooral in Excel 2007 zijn veel dingen veranderd. Vandaar dat in dit artikel getracht wordt om op hoofdlijnen de werking van de draaitabellenfunctie in Excel 2010 uit te leggen.
De eerste hobbel die je nu moet nemen is het opsporen van de draaitabellenfunctie in het Lint. Je vindt deze in het tabblad Invoegen de meest linkse knop. Als je op de knop Draaitabellen drukt dan verschijnt er een dialoogkader.
De eerste stappen
Ik gebruik als voorbeeld een bestand gebaseerd op de omzetrapportsjabloon (dit kun je vinden via Bestand | Nieuw onder de rubriek Voorbeeldsjablonen).
Je moet hierin aangeven waar de brongegevens voor de draaitabel staan. Standaard zal hierbij net als in Excel 97 het aaneengesloten gebied waar de muiscursor in staat aangegeven worden en wordt de draaitabel in een nieuw werkblad gezet. Als je op OK klikt dan doorloop je niet een aantal schermen zoals in de draaitabel wizard in Excel 97. In plaats daarvan krijg je meteen het nieuwe werkblad voor je neus met aan de rechterkant de lijst met draaitabelvelden(kolommen) uit de brongegevens. (zie afbeelding 1) Het is de bedoeling om deze velden in de juiste rubrieken onder de lijst met velden te slepen. Dit is een heel andere werkwijze dan in Excel 97. Je kunt echter een raster in beeld brengen met daarin de verschillende onderdelen van de draaitabel zodat je op een meer vertrouwde manier een draaitabel kunt maken.
18
Computerexpress 2011•3
Praktijk
Afbeelding 1
Dit doe je als volgt: In het nieuwe werkblad zijn twee extra tab- Kies in dit dialoogkader het tabblad Weergave en kies de optie bladen zichtbaar met Hulpmiddelen voor draaitabellen. In het “Klassieke draaitabelindeling”. Nu zie je weer een rasterindetabblad Opties vind je aan de linkerkant van het Lint een knop ling waar je draaitabelvelden naar toe kunt slepen. Opties. Als je hierop klikt, verschijnt er een dialoogkader.
Computerexpress 2011•3
19
Praktijk
Draaitabel opbouwen en aanpas sen
We kunnen nu de draaitabelvelden naar het raster slepen om de tabel op te bouwen. De weergave zal direct bijgewerkt worden zodra je een veld ergens in het raster neerzet. Voor dit voorbeeld zet ik het veld Klant in het Rapportfiltergedeelte (in Excel 97 noemde men dit paginavelden), het veld Product in het Rijgedeelte en de velden Kwartaal 1 t/m 4 in het Waarde gedeelte. Net als in Excel 97 wordt bij numerieke velden niet altijd automatisch voor Som gekozen. Het veranderen van de berekening is echter iets omslachtiger geworden. Je kunt niet meer dubbelklikken op het waardeveld. In plaats daarvan klik je één keer op het waardeveld en kiest voor de onderste optie in het snelmenu: Waardeveldinstellingen. We hebben de draaitabel in de basis klaar.
Uiteraard kunnen we de draaitabel verder naar onze wensen aanpassen. Ik kies ervoor om het veld Klant toch naar het rijgedeelte te verplaatsen zodat ik direct alle klanten met de producten die ze gekocht hebben in beeld heb. Standaard kiest Excel 2010 dan misschien een andere rapportindeling voor de draaitabel dan je van Excel 97 gewend bent. Wil je de indeling veranderen dan gaat dat als volgt: Kies bij de Hulpmiddelen voor draaitabellen het tabblad Ontwerp en vervolgens de knop Rapportindeling. De optie “Tabelweergave” komt overeen met die van Excel 97.
20
Computerexpress 2011•3
Je ziet dat Excel 2010 meerdere rapportindelingen heeft waaronder “Compacte weergave”, hierbij nemen de rijlabels minder ruimte in de breedte in beslag.
Excel 2010 zal nog steeds standaard subtotalen, eindtotalen en dergelijke toevoegen aan de draaitabel. Het verbergen van deze totalen kan nu makkelijker met de knoppen Subtotalen en Eindtotalen in het tabblad Opties van de Hulpmiddelen.
Praktijk
Een handige verbetering in Excel 2010 is de toevoeging van een zoekvak in de filteropties. Hier kun je (delen) van woorden intypen om snel te filteren. Wat echter nog steeds niet kan zonder een oude truc is het filteren op de waarde velden. De truc is om in de kolom net naast de draaitabel te gaan staan en dan de filteroptie uit het tabblad Start aan te zetten. Dan verschijnen er ook filterknoppen bij de waarde velden.
Met de min-knoppen voor de namen kun je per item de details verbergen. Als je in één keer voor een rijveld de details wilt verbergen selecteer je het veld in de draaitabel en gebruik je de knop Gehele veld samenvoegen in het tabblad Opties van de Hulpmiddelen.
Filteren en details verber gen
Een grote verbetering ten opzichte van Excel 97 zijn de filtermogelijkheden. In Excel 97 was er officieel maar één methode om gegevens in een draaitabel te filteren, namelijk door een veld in het rapportfiltergedeelte te zetten. Zoals je in de schermafbeeldingen ziet zijn er nu standaard filterknoppen voor de rijvelden aanwezig.
Computerexpress 2011•3
21
Praktijk
Nog meer mogelijkheden ter verfraaiing
In het tabblad Ontwerp van de Hulpmiddelen staat een hele reeks met draaitabelstijlen die je kunt gebruiken om de tabel mooier op te maken. Hierbij kun je dankbaar gebruik maken van de Live voorbeelden. Als je met de muis op een stijl gaat staan krijg je een preview van het effect te zien. Sommige stijlen kosten volgens mij wel aardig wat dure kleureninkt uit een inktjetprinter, maar het oog wil ook wat. Een nieuwe mogelijkheid van Excel 2010 is het gebruik van zogenaamde slicers. Dit is een nieuwe manier om op een meer visuele, interactieve wijze gegevens in de draaitabel te filteren. Dit is vooral een handige optie als je mensen met weinig kennis van Excel makkelijk filters wilt laten gebruiken. In het voorbeeld maak ik een slicer voor het veld klanten om makkelijk de gegevens in het overzicht per klant te filteren. We klikken hiervoor op de knop Slicer invoegen in het tabblad Opties van de Hulpmiddelen.
Als je de Slicer selecteert verschijnt er een apart tabblad Opties onder de Hulpmiddelen voor slicers met diverse mogelijkheden. Waar is de wizard draaitabellen gebleven?
In het begin van het artikel heb je gemerkt dat je bij het maken van een draaitabel niet meer in de vertrouwde wizard draaitabellen terecht komt. Een van de minpunten van de nieuwe werkwijze is dat je geen gelegenheid meer krijgt om een nieuwe draaitabel te baseren op een al bestaande om geheugen te sparen. Nu is het de vraag of je snel tegen dat probleem aan zult lopen, maar als je veel draaitabellen in één werkmap wilt maken kan dat wel voorkomen. Een tweede mogelijkheid die in de standaard werkwijze ontbreekt, is het gebruik van meervoudige samenvoegingsbereiken bij het maken van een draaitabel.
In de lijst met velden vinken we het veld klant aan om aan te geven dat we hiervan een slicer willen maken. Het resultaat is een fraai opgemaakte lijst met knoppen per klant waarmee we per klant kunnen filteren door gewoon op een knop te klikken.
22
Computerexpress 2011•3
Om deze mogelijkheden toch te kunnen gebruiken zit de draaitabellen wizard van Excel 2003 nog wel verstopt in Excel 2010. Deze kun je vinden onder de knop Opties in het tabblad Start. Klik op de rubriek Werkbalk Snelle toegang. Zoek in de categorie “Opdrachten die niet op het lint staan” en scrol helemaal naar onderen waar de opdracht “Wizard draaitabel en draaigrafiek” staat.
Praktijk
Onderhuidse verschillen
Onderhuids gaat Excel vanaf versie 2007 anders om met draaitabellen. Dit wordt veroorzaakt door het feit dat een draaitabel in deze versies veel meer gegevens moet aankunnen. Allerlei oude limieten voor draaitabellen, zoals dat een draaitabelveld niet meer dan ca. 8000 unieke waarden mag bevatten, zijn dan ook flink opgerekt. Het verschil zal je onder andere merken als je een Excel 2007-2010 werkmap met draaitabellen opslaat in het Excel 97-2003 formaat. De draaitabellen worden dan geconverteerd naar een platte tabel waar je niet meer op kunt doorklikken of de indeling kunt veranderen. Het verschil valt ook op als je macro’s gaat opnemen waarin het maken van draaitabellen voorkomt. Je zult zien Je kunt de knop nu toevoegen aan de Werkbalk Snelle toegang. dat de VBA macrocode die wordt opgenomen anders is dan de VBA-code die tot en met Excel 2003 wordt opgenomen. Het vervelende is echter dat deze macro’s een bug bevatten waardoor je een eenmaal opgenomen macro niet kunt herhalen! Dit probleem en hoe je het op kunt lossen is ook keurig gedocumenteerd in de knowledge base van Microsoft, maar is in Excel 2010 vreemd genoeg nog niet verholpen! Bij het gebruik van de wizard draaitabel en Als je op deze knop klikt dan verschijnt het volgende dialoogkader draaigrafiek wordt andere macrocode opgenomen, maar dat veel lijkt op dat van Excel 97. ook deze is zonder aanpassing niet herhaalbaar. Het voert voor dit artikel echter te ver om de noodzakelijke aanpassingen te bespreken.
Als we de wizard doorlopen en de draaitabel baseren op dezelfde brongegevens als een eerdere draaitabel dan verschijnt bij stap 2 het volgende dialoogkader .
De procedure voor het feitelijk maken van de draaitabel is overigens identiek aan de eerder besproken werkwijze.
Computerexpress 2011•3
23
