---

---

Uit mijn artikelen mag blijken, dat ik een draaitabel-fanaat ben. De mogelijkheden en flexibiliteit van deze Excel-optie zijn grandioos. Een voorwaarde voor het gebruik daarvan is wel dat de brongegevens op een bepaalde manier klaar staan. Dit heb ik ooit de database-structuur genoemd.
Ben je verantwoordelijk voor een rapportageproces en vraag je iemand een overzicht te maken van bijvoorbeeld per dag de aantallen per regio, dan krijg je in 90% van de gevallen een overzicht zoals hierboven links wordt getoond (in de rijen de dagen en in de kolommen de regio’s), een ‘gewoon’ Excel-overzicht. Een groot voordeel hiervan dat het voor de mens makkelijk leesbaar en begrijpelijk is. Een groot nadeel dat je niet meer flexibel bent. Hoe zorg je er voor, dat totalen toch nog kloppen als je even één regio niet mee wilt laten tellen of wanneer je soms wel en soms niet de subtotalen van de maanden wilt tonen?

In dit artikel daarom nogmaals aandacht voor draaitabellen, maar zeker ook voor een methode om een ‘gewoon’ overzicht geautomatiseerd om te zetten naar een database-structuur.

Brongegevens

In kolom B staan de werkdagen met een zodanige opmaak, dat ook de omschrijving van de dag wordt weergegeven (zie het artikel ginfo.nl/data-datums).

NB1 kolom G bevat per dag een totaal over alle regio’s. Excel toont in sommige cellen een klein waarschuwingsdriehoekje. Wanneer je met de muis daarboven ‘gaat hangen’ krijg je te zien wat de waarschuwing is.

In dit geval ‘ziet’ Excel dat naast de getallen onder de regio’s er ook nog een getal links daarvan staat; intern Excel is een datum namelijk een getal.

NB2 de werkdagen zijn als volgt gecreëerd: in cel B3 is de datum 6-1-20 ingevoerd. Daarna is de vulgreep (het kleine vierkantje rechtsonder) naar beneden doorgevoerd. Niet met de linker-muis-toets, maar met behulp van de rechter-muis-toets. Laat u die toets los, dan kunt u diverse opties kiezen; in dit geval Werkdagen doorvoeren.

Unpivot

Voordat we op basis van deze brongegevens een draaitabel (in het Engels Pivottable) kunnen gaan maken moeten we het voorbeeldbestand eerst gaan omzetten naar een database-structuur. Daar gebruiken we Power Query voor (zie ook het artikel ginfo.nl/power-query).

Het voorbeeld-overzicht lijkt op het resultaat van een draaitabel; dit overzicht moeten we dus ont-draaitabellen. Laten we toch maar de Engelse uitdrukking Unpivot gebruiken.
In het tabblad Ovz1 van het Voorbeeldbestand was een kopie van de kolommen B t/m G uit het tabblad Data opgenomen.
Hoe gaan we deze nu Unpivot-ten?

1. klik op een van de cellen in het overzicht, bijvoorbeeld cel C3
2. kies in de menutab Gegevens in het blok Ophalen en transformeren de optie Uit tabel.
   In oudere versies van Excel moet u iets vergelijkbaars zoeken in de menutab Power Query.
3. klik in het vervolgscherm op OK. Het overzicht wordt automatisch omgezet naar een Excel-tabel.
   
4. eerst een paar kleine aanpassingen: verwijder de Totaal-kolom (die maken we straks wel weer met behulp van een draaitabel) en klik links van Datum op het kalender-symbool en wijzig het type van Datum/tijd in Datum
5. zorg dat de Datum-kolom is geselecteerd en klik dan in de menutab Transformeren in het blok Alle kolommen op het kleine driehoekje naast de optie Draaitabel opheffen
   Kies de optie Draaitabel voor andere kolommen opheffen.

6. Dat is het al bijna! Verander de kop van
   de 2e kolom in Regio en die van de 3e in Aantal (via dubbelklikken).
7. Kies dan in de menutab Startpagina in het blok Sluiten het driehoekje bij Sluiten en laden. Kies Sluiten en laden naar ….
   In het vervolgscherm moet de optie Tabel aan staan, kies dan de plaats waar de nieuwe tabel moet komen en klik op Laden.

Draaitabel

Op basis van de nieuwe brongegevens gaan we nu een draaitabel maken:

En we hebben een overzicht, dat uitblinkt in gemak en flexibiliteit (zie het tabblad Ovz1 van het Voorbeeldbestand): gebruik de plus- en min-tekens vóór de maanden om deze uit- of in te klappen, klik op een driehoekje bij de kopjes om een bepaalde item wel of niet mee te nemen, bepaalde sortering door te voeren etc.

NB Ontvangt u gegevens over andere maanden, voeg die dan onderaan de bron-tabel toe. Vergeet niet daarna de met Power Query gegeneerde tabel te vernieuwen én ook de draaitabel te vernieuwen.

Brongegevens2

Voordat we op bovenstaande manier kunnen gaan ‘Unpivot-ten’ moeten de ontbrekende gegevens in die kolommen worden aangevuld.
Dat kan handmatig door de waardes te kopiëren, maar dat kan wat makkelijker:

1. selecteer in de Maand-kolom alle cellen met en zonder waardes (op het tabblad Data2 van het Voorbeeldbestand de cellen C3:C64)
2. klik Ctrl-G (of gebruik de F5-toets, dit is de optie Ga naar), kies de button Speciaal, in het vervolgscherm de optie Lege cellen en klik dan op OK
3. de eerste lege cel is nu geselecteerd. Type daarin het =-teken en tik op de cursor-omhoog-toets (we maken dus een verwijzing naar de bovenstaande cel, in dit geval =C3).
   LET OP druk dan NIET op Enter maar op Ctrl-Enter. Op die manier worden alle geselecteerde (dus lege) cellen met deze formule gevuld.
4. Selecteer alle gevulde cellen in de Maand-kolom, tik Ctrl-C en klik dan met de rechter-muisknop ergens in dit gebied en kies de tweede Plak-optie (Waarden). De formules worden door harde waarden overschreven.
   NB deze laatste stap is eigenlijk niet nodig voor het verdere Unpivot-proces.
5. Doe hetzelfde voor de Jaar-kolom.

Dit overzicht kan dan op dezelfde manier als hiervoor worden getransformeerd naar een database-structuur (zie tabblad Data2 in het Voorbeeldbestand). Met dit verschil: zorg dat in stap 5 eerst de kolommen Jaar, Maand en Dag zijn geselecteerd.

Unpivot2

Maar het bijwerken van de brongegevens kunnen we natuurlijk ook met behulp van Power Query doen (zie het tabblad Ovz2 in het Voorbeeldbestand):
NB ik kreeg een tip van Martien, dat onderstaande methode veel te omslachtig is (zie reactie hieronder); als algemene werkwijze bij het gebruik van gegevens uit andere records is het wel belangrijk om deze manier te kennen.

1. klik op een van de cellen in het overzicht, bijvoorbeeld cel C3
2. kies in de menutab Gegevens in het blok Ophalen en transformeren de optie Uit tabel.
3. In oudere versies van Excel moet u iets vergelijkbaars zoeken in de menutab Power Query.
4. klik in het vervolgscherm op OK. Het overzicht wordt automatisch omgezet naar een Excel-tabel.
   
5. in deze records moeten op diverse plaatsen dus waarden uit het record daarvoor worden opgehaald; dat kan alleen als de records een index hebben:
   kies in de menutab Kolom toevoegen de optie Indexkolom
6. nu gaan we een nieuwe jaarkolom toevoegen. Kies in de menutab Kolom toevoegen de optie Voorwaardelijke kolom.
   
   Vul het vervolgscherm in zoals hierboven.
7. Het nieuwe resultaat ziet er nu als volgt uit:
   
   In de formulebalk moeten we “test” vervangen door een formule die verwijst naar het Jaar in het vorige record:
   #”Index toegevoegd”{[Index]-1}[Jaar]
   Ofwel: zoek in het resultaat van de vorige stap (met de naam Index toegevoegd) in het record met het volgnummer Index-1 de waarde in de kolom Jaar.
8. deze formule moet wel in alle records worden doorgevoerd:
   selecteer de eerste cel in de nieuwe kolom en kies in de menutab Transformeren in het blok Alle kolommen de optie Doorvoeren omlaag
9. voor de maand wordt op een vergelijkbare manier een nieuwe kolom toegevoegd. De querystap ziet er dan als volgt uit:
   = Table.AddColumn(#”Omlaag doorgevoerd”, “Aangepast.1”, each if [Maand] = null then #”Omlaag doorgevoerd”{[Index]-1}[Maand] else [Maand])
   Vergeet niet de formule in deze kolom door te voeren.
   NB het opzoeken in andere records is voor Excel een intensief proces; het verversen van de gegevens kan even duren.
   
10. de oorspronkelijke Jaar– en Maand-kolom en ook de Index kunnen nu verwijderd worden. Verplaats de 2 aangepaste kolommen naar voren en geef die dan de namen Jaar en Maand.
11. we zijn toch lekker bezig. Laten we zorgen dat we ook een echte datum in het bestand krijgen:
    Kies via het driehoekje linksboven in de tabel (dus naast de nieuwe kolom Jaar) de optie Kolom toevoegen vanuit voorbeelden.
    Vul onder Kolom1 in het vervolgscherm in: 6 jan 2020 en druk op Enter. Uit de formule, die Excel genereert, blijkt dat hij/zij niet weet wat we willen. In de tweede regel plaatsen we 7 jan 2020. Nu begrijpen we elkaar, dus klik op OK.
    De nieuwe kolom heeft de naam Samengevoegd gekregen; verander die in Datum (via dubbelklikken).
    Het type van deze kolom is Tekst (zie de aanduiding ABC naast de naam); dit veranderen we in Datum (klik op ABC).
    Verplaats deze kolom nu zodat die op de vierde plaats komt.
12. nu nog even de unpivot-truc: selecteer de eerste 4 kolommen en kies de optie Draaitabel voor andere kolommen opheffen.
    Nog even de namen van de twee laatste kolommen wijzigen in Regio en Aantal.
13. Als laatste: kies in de menutab Startpagina in het blok Sluiten het driehoekje bij Sluiten en laden. Kies Sluiten en laden naar ….
    In het vervolgscherm moet de optie Tabel aan staan, kies dan de plaats waar de nieuwe tabel moet komen en klik op Laden.

Voor het resultaat, een draaitabel en de Power Query’s zie het tabblad Ovz2 van het Voorbeeldbestand.

NB1 een query is altijd te bekijken door in het scherm Werkmapquery’s op de betreffende query te dubbelklikken.

NB2 het kan gebeuren dat een query niet direct in Excel geladen wordt (het ‘wieltje’ bij de querynaam blijft draaien) stop het vernieuwen en probeer het opnieuw:

---