Mooie vraag: je hebt een heleboel gegevens, maar hoe maak je daar nu informatie van.

Zoals in het plaatje is weergegeven kun je op diverse manieren met de data gaan “stoeien”.  Eén van de meest gehanteerde methodes is het gebruik van draaitabellen. En terecht: menige beroepsgroep zou ontzettend blij zijn met zo’n krachtig, breed inzetbaar hulpmiddel!

Maar draaitabellen kennen (natuurlijk) ook nadelen. Ik kan er nu twee bedenken:

1. de mogelijkheden en inzetbaarheid zijn ontzettend uitgebreid; waar begin je als je er iets over wilt vertellen!
2. draaitabellen hebben altijd een vast stramien: als je een verdeling naar product in de rijen hebt staan kun je de volgorde van de producten nog wel makkelijk aanpassen, maar als het eerste resultaat in cel C10 komt dan komt de volgende in cel C11 etc.

Nadeel 1 kunnen we niet met één artikel door G-Info oplossen, maar aan het tweede kunnen we wel wat doen.

Draaitabel maken

Om gegevens uit een draaitabel te kunnen gebruiken moet je natuurlijk wel een draaitabel hebben.
Laten we er even eentje maken (zie Voorbeeldbestand):

1. in het tabblad Basis1 staat een fictief omzetoverzicht. Per dag ligt de omzet vast, gesplitst naar soort en product.
   De gegevens zijn willekeurig door Excel gegenereerd met de formules van het tabblad Formules en daarna ‘hard’ gemaakt door de cellen te kopiëren en op dezelfde plaats te plakken als Waarden.
2. selecteer een willekeurige cel in dit overzicht en kies dan de optie Draaitabel in de menutab Invoegen
3. Excel zal automatisch het totale gebied met gegevens selecteren (in dit geval B2 t/m E1001).
   In het voorbeeld heb ik voor de locatie van de draaitabel een bestaand werkblad gekozen (namelijk Basis1) en de draaitabel op cel H3 geplaatst …
4. … en de Lijst met draaitabelvelden ingevuld zoals hiernaast. Resultaat: een mooi overzicht van de verdeling van de omzet naar Soort en Product (zie tabblad Basis1):
   NB het gemiddelde bedrag hebben we gekregen door het veld Bedrag opnieuw naar het waarden-blok te slepen en de Waardeinstellingen te veranderen van Som naar Gemiddelde.
   

De volgende opmerkingen bij dit overzicht:

1. de kolomnamen zijn groot waardoor alle kolommen automatisch extra breed worden (nog een nadeel van draaitabellen!).
   Oplossing: klik op zo’n naam en wijzig die in bijvoorbeeld Aantal of Gemid.
   Zie de tweede draaitabel op het tabblad Basis1.
2. de sortering bij Soort is niet logisch.
   Oplossing1: versleep de diverse items naar de juiste plaats door met de cursor “de rand vast te pakken”
   Oplossing2: maak een eigen sortering aan en pas die toe (zie artikel doorvoeren-en-sorteren)
3. wanneer de brongegevens uitgebreid worden met een nieuwe regel dan zal de draaitabel niet automatisch veranderen (ook niet na het Vernieuwen daarvan).
   Oplossing1: ga binnen de menutab Hulpmiddelen voor draaitabellen naar Opties en kies dan binnen Gegevens voor Andere Gegevensbron en zorg dat alle gegevens zijn geselecteerd.
   Oplossing2: zorg dat de brongegevens als een Excel-tabel zijn opgeslagen; zie hierna (Excel-tabel en draaitabel)
4.  qua structuur is het niet ‘netjes’ om basisgegevens en afgeleide overzichten in hetzelfde tabblad weer te geven. In dit geval heb ik het wel even gedaan om snel het resultaat van de draaitabel te kunnen vergelijken met de bron.

Excel-tabel en draaitabel

Twee soorten tabellen, die niet door elkaar gehaald moeten worden!
In het tabblad Basis2 van het Voorbeeldbestand staan dezelfde gegevens als in blad Basis1. Maar ik heb daar een Excel-tabel van gemaakt:

1. selecteer een willekeurige cel in dit overzicht en kies dan de optie Tabel in de menutab Invoegen
2. in het nieuwe tussenvenster wordt automatisch het hele gegevensgebied gekozen en Excel ‘ziet’ dat er een kopregel is. Klik OK.
3. het resultaat is een tabel, waarbij Excel automatisch de regels een ´zebra´-achtergrond geeft en de koppen van filter-knoppen voorziet:
4. via de menutab Hulpmiddelen voor tabellen is de naam van de tabel gewijzigd van de standaardwaarde Tabel1 naar tblOmzet.

Wanneer we nu een draaitabel hiervan maken (zie tabblad Draai) en we vullen de brongegevens op het tabblad Basis2 aan met een nieuwe regel dan zal de draaitabel automatisch de nieuwe regel meenemen.
Wel nog Vernieuwen kiezen, bijvoorbeeld door rechts te klikken op de draaitabel!

Gegevens van een draaitabel gebruiken

Genoeg over de voorbereidingen; dit artikel zou gaan over het ophalen van gegevens uit een draaitabel.

Stel dat we om een of andere reden niet alle gegevens willen zien maar bijvoorbeeld alleen het bedrag, dat bij de combinatie Soort=Een en Product=P2 hoort, dan kunnen we in Excel natuurlijk gewoon verwijzen naar cel G6 (zie het tabblad Draai in het Voorbeeldbestand).
Maar wat gebeurt er als de draaitabel wordt gewijzigd omdat er nieuwe soorten of producten bijkomen of als de structuur van de draaitabel wordt gewijzigd (wissel Soort en Product maar eens om van Rijlabels naar Kolomlabels en vice versa)?
Dan staan de gegevens van Een/P2 waarschijnlijk niet meer in dezelfde cel en klopt de verwijzing niet meer. Dit is een veel voorkomende fout in Excel-sheets!

Gelukkig heeft Microsoft daar iets op bedacht (vanaf versie Excel 2007?). Even een voorbeeld:

1. klik in het tabblad Draai in cel C30
2. tik het =-teken in en klik dan met de muis op cel M10 en druk op Enter
3. als in cel C30 nu de formule =M10 staat, moet je nog iets aan de instellingen van de draaitabel veranderen:
   * klik ergens in de draaitabel
   * in de menutab Hulpmiddelen voor draaitabellen, die nu tevoorschijn komt, kiezen we de tab Opties. In het vak Draaitabel staat nog een keer Opties: zet daar het vinkje bij DraaitabelOphalen genereren aan. Ga dan opnieuw naar punt 1.
   Wil je deze optie niet gebruiken dan weet je nu dus ook waar je hem uit kunt zetten.
4. in cel C30 staat nu de formule =DRAAITABEL.OPHALEN(“Som”;$B$3)
   Ofwel: haal het bedrag op dat hoort bij het veld Som (in ons geval is dat eigenlijk het veld Bedrag, maar we hebben Som van Bedrag gewijzigd in Som) uit de draaitabel, die begint in cel B3.
   
5. de vorige formule haalt het totaalbedrag op. Voer de vorige procedure eens uit in cel C31 maar klik dan op cel D10 ipv M10. Excel zal dan automatich de volgende formule genereren: =DRAAITABEL.OPHALEN(“Som”;$B$3;”Product”;”P1″)
   Aan het ophalen wordt dus een voorwaarde meegegeven: haal niet alle bedragen op, maar alleen die bedragen waarbij het Product gelijk is aan P1 (let op de aanhalingstekens overal).
   NB1 Dit soort formules kun je ook met de hand intikken, maar het is lastig om dan geen fouten te maken.
   NB2 verwissel Soort en Product en kijk wat de resultaten in C30 en C31 zijn; als het goed is blijven die hetzelfde. We zijn dus niet meer afhankelijk van de structuur van de draaitabel!

Dat in cel D35 de volgende formule staat, zal dan geen verrassing meer zijn:
=DRAAITABEL.OPHALEN(“Som”;$B$3;”Soort”;”Drie”;”Product”;”P3″)

Maar we gaan nog een stapje verder en maken de formule dynamisch: in cel E35 staat de formule =DRAAITABEL.OPHALEN(“Som”;$B$3;”Soort”;E33;”Product”;E34)

Bij de voorwaarden staat geen letterlijke tekst meer maar een verwijzing naar de cellen E33 en E34.  Bij het wijzigen van de inhoud van die cellen zal de formule dus ook andere waarden ophalen.

LET OP als je in cel E33 de tekst Vijf intikt levert de formule een foutmelding op! Draaitabel.Ophalen kan alleen maar gegevens ophalen die in de draaitabel voorkomen.

Voorbeeld 2 voor Draaitabel.Ophalen

In tabblad DraaiDatum van het Voorbeeldbestand staat nog een voorbeeld van de functie Draaitabel.Ophalen.

De draaitabel geeft per dag het aantal records en de omzet weer (de automatisch gegenereerde namen Aantal van Datum en Som van Bedrag zijn overschreven door Aantal respectievelijk Subtotaal).

Willen we alleen het resultaat van een specifieke dag zien dan gebruiken we weer de functie Draaitabel.Ophalen (zie tabblad DraaiDatum):

Maar omdat de datum 2-1-2015 niet voorkomt krijgen we een foutmelding; in cel G5 staat dan ook een iets uitgebreidere formule:
=ALS.FOUT(DRAAITABEL.OPHALEN(“SubTotaal”;$A$3;”Datum”;G3);0)

Dus als de functie Draaitabel.Ophalen een fout genereert dan wordt het resultaat gelijk aan 0 (nul).

Voorbeeld 3 voor Draaitabel.Ophalen

Een van de vele mogelijkheden van een draaitabel is dat je rubrieken kunt groeperen; helemaal interessant is dit voor datums.

In het tabblad DraaiMnd van het Voorbeeldbestand is een indeling naar Jaar, kwartaal en maand te zien. Hoe is dit gemaakt?
Op basis van de gegevens in tabblad Basis3 is een draaitabel gemaakt met in de Rijlabels de Datum, aan het vak Waarden is nog een keer de Datum toegevoegd en ook het Bedrag.
Maar nu komt het: wanneer je nu in de draaitabel op één van de datums met de muis rechts klikt, krijg je de optie Groeperen: Excel ‘ziet’ dat het datums zijn en heeft al de optie Maanden geselecteerd; klik ook nog op Kwartalen en Jaren en OK.

Standaard zal Excel geen subtotalen voor de nieuwe groepen bepalen: klik rechts op het jaar 2015 en zet de optie Subtotaal Jaren aan; zo ook voor de kwartalen.

Omdat het tabblad Basis3 nog formules bevat, waarbij alle velden willekeurig worden gevuld,  zal het Vernieuwen van de draaitabel ook iedere keer andere resultaten opleveren.

Wanneer je in het tabblad Formules de Einddatum verandert in bijvoorbeeld 29-2-2016, zal het resultaat daarvan meteen zichtbaar zijn in de draaitabel (na Vernieuwen).

Bekijk nu alle formules die in het tabblad DraaiMnd in het blok G3:I13 staan.
Hopelijk spreken die voor zichzelf! Zo niet: neem contact op met G-Info.

---