Categorie archief: Excel

Unpivot

Excel, ,
Voorbeeldbestand: Unpivot.xlsxDownload

Ofwel hoe kom ik van een ‘gewoon’ Excel-overzicht naar een database-structuur.

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 het tabblad Data van het Voorbeeldbestand staat een overzicht met aantallen per werkdag van het eerste kwartaal van 2020, uitgesplitst naar regio.

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.
  1. Dat is het al bijna! Verander de kop van
    de 2e kolom in Regio en die van de 3e in Aantal (via dubbelklikken).
  2. 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:

  1. klik ergens in de nieuwe tabel
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. Bepaal waar de nieuwe draaitabel moet komen en klik op OK
  4. Plaats de Datum in het blok Rijen, Regio in Kolommen en Aantal in het Waarden-blok.
    Afhankelijk van de Excel-versie zal Excel de datums ook al groeperen in maanden. Gebeurt dit bij u niet automatisch dan kunt u later de datums nog (automatisch laten) groeperen.

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

Maar dezelfde brongegevens kunnen natuurlijk ook worden aangeleverd zoals hiernaast weergegeven.

Het jaar en de maand zijn alleen gevuld wanneer er een wijziging plaats vindt.

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:

Power Query

Excel, , ,
Voorbeeldbestand: PowerQuery.zipDownload

Waarschijnlijk is deze toevoeging aan Excel de grootste sprong voorwaarts sinds de introductie van Draaitabellen.
De eerste versies zijn ongeveer 10 jaar geleden door Microsoft vrijgegeven als een gratis add-in; sinds Excel-versie 2016 is het standaard beschikbaar, maar dan onder de naam Ophalen en Transformeren.

Deze ‘nieuwe’ naam geeft ook goed aan, waar Power Query vooral voor bedoeld is: ondersteuning bij het binnenhalen van externe bestanden, inclusief het gestandaardiseerd aanpassen en verrijken van de gegevens. Met gestandaardiseerd bedoel ik, dat alle handelingen die verricht moeten worden om externe gegevens in Excel in te lezen, vastliggen in een geautomatiseerde procedure. En deze procedure wordt dan iedere keer (dag, maand, kwartaal) op precies dezelfde manier doorlopen zodat fouten, die bij handmatige acties nog wel eens willen voorkomen, worden vermeden.
Niet alleen leuk voor u, maar ook voor uw controller en interne en externe accountant!

In dit artikel komen de eerste grondbeginselen en de basis-werking van Power Query aan bod.

Extern bestand inlezen

In het Voorbeeldbestand (een zip-bestand; pak dit uit en plaats de 3 bestanden ergens op uw PC) zitten ook twee tekstbestanden (Serie1.txt en Serie2.txt). We gaan het eerste bestand met Power Query/Ophalen en transformeren (hierna altijd met PQ aangeduid) in Excel inlezen:

  1. kies in de menutab Gegevens in het blok Ophalen en Transformeren de optie Nieuwe query
  2. kies in het vervolg-menu de optie Uit bestand en daarna Uit tekstbestand
  3. zoek het bestand Serie1.txt uit het Voorbeeldbestand op en kies Importeren

4. Excel interpreteert zo goed mogelijk de gegevens; hij ‘ziet’ welk scheidingsteken er is gebruikt etc.
De gegevens zien er goed uit, inclusief een kopregel, dus we kunnen deze gaan laden in Excel: kies het ‘vinkje’ naast Laden en kies de optie Laden naar …
5. zorg dat de button bij Tabel is geselecteerd en kies Laden

Het resultaat van de query komt automatisch in een Excel-tabel (in dit geval met de naam Serie1). Rechts in het scherm ziet u dat de query 15 rijen heeft binnengehaald (zie het tabblad Tekst in de werkmap PowerQuery.xlsx in het Voorbeeldbestand).

NB het overzicht is alleen maar zichtbaar als in de menutab Gegevens in het blok Ophalen en transformeren de optie Query’s weergeven is aangeklikt.

Maar…
het zou mooier zijn, als de namen in Soort met een hoofdletter zouden beginnen en het resultaat op datum zou zijn gesorteerd. Dit kan natuurlijk makkelijk in Excel, maar we gaan er voor zorgen dat dit direct iedere keer bij het inlezen al gebeurt.

Extern bestand inlezen en transformeren

Op dezelfde manier als hiervoor gaan we Serie1.txt opnieuw inlezen maar vanaf stap 4 wordt het anders:

4. kies de optie Gegevens transformeren

5. in de Power Query-editor is te zien welke stappen in de procedure Excel al heeft uitgevoerd: een Bron gekozen, iets met de kopregel en een Type gewijzigd (voor details: klik op een van de stappen en kijk in de formulebalk van de editor).
NB Power Query gebruikt een eigen programmeertaal, M. In dit artikel gaan we daar verder niet op in; wel belangrijk om te weten is, dat deze programmeertaal hoofdlettergevoelig is.
We gaan een stap toevoegen, namelijk het sorteren op datum: klik op het vinkje naast Datum en kies de optie Oplopend sorteren.
Er komt dan een procedure-stap bij: Rijen gesorteerd.

6. nu moet Soort nog met een hoofdletter beginnen. Dit doen we door een nieuwe kolom toe te voegen.

‘Normaal’ zou je kiezen voor een Aangepaste kolom toevoegen, maar de programmeurs van Microsoft hebben een prachtige optie Kolom toevoegen vanuit voorbeelden bedacht: klik op de tabel-button links van de kolomnamen.
In de eerste regel staat (na de sortering) bij Soort de tekst een; in het invulveld onder de nieuwe kolomkop Kolom1 tikken we Een en drukken op Enter.
PQ snapt ons: alle andere soorten zijn nu ook omgezet! Zie ook bovenaan waar een nieuwe transformatie-regel is vermeld: Text.Proper([Soort]). Dus klik op OK.
7. wijzig de kolomkop in Srt door dubbelklikken
8. plaats de nieuwe kolom links van Bedrag (met de muis slepen)
9. verwijder de kolom Soort (rechts klikken op de kolomkop)
10. klik op het vinkje bij Sluiten en laden en kies de optie Sluiten en laden naar. Zorg dat de Tabel-button aan staat en dat de tabel op het bestaande werkblad naast het vorige resultaat komt te staan; klik dan op de button Laden.

NB1 wanneer het bron-bestand Serie1.txt opnieuw wordt aangeleverd (met nieuwe gegevens), dan is het voldoende om deze resultaat-tabel te vernieuwen (ergens in de tabel rechts klikken).

NB2 wanneer je in het Voorbeeldbestand Vernieuwen kiest, zul je een foutmelding krijgen: de directory-structuur op mijn PC ziet er natuurlijk anders uit dan bij u. Hoe je dit oplost, leg ik hieronder uit.

Bestaand bestand transformeren

Maar ik zie nu, dat de bedragen verkeerd worden aangeleverd: het decimale teken is weggelaten.

We gaan daarom het vorige resultaat aanpassen:

  1. klik ergens in de resultaat-tabel (die heeft van Excel de naam Serie1_2 gekregen)
  2. kies dan in de menutab Gegevens in het blok Ophalen en transformeren de optie Uit tabel
  3. kies, net als hiervoor, de optie Kolom toevoegen vanuit voorbeelden
  4. in de eerste regel staat het bedrag 146; dit wordt dan 1,46 in de nieuwe kolom (en druk op Enter)
  5. Excel begrijpt ons nog niet; in de tweede regel tikken we 1,79 (en Enter)
  6. de transformatieregel wordt [Bedrag]/100. Precies wat we wilden! Klik op OK.
  7. kolomkop wijzigen in Bedr, de kolom Bedrag verwijderen en dan Sluiten en laden (zorg dat het resultaat naast de andere 2 komt te staan).

Bestaande query aanpassen

Hey, wat is er met de Datum gebeurd? Die heeft ineens ook een tijdaanduiding. Zo slim is Excel nu ook weer niet 😉

  1. dubbelklik in het blok Werkmapquery’s op de laatste query (met de naam Serie1_2)
  2. klik op de kolomkop Datum
  3. kies dan in het blok Transformeren de optie Gegevenstype en wijzig dit in Datum
  4. ga terug via Sluiten en laden

Voor analyse-doeleinden is het handig om ook een kolom Jaar en Maand te hebben. In het vierde blok op het tabblad Tekst van het Voorbeeldbestand zijn deze kolommen toegevoegd (en ook de Dag).

Zelfde query, ander bestand of andere plaats

In het tabblad Tekst2 van het Voorbeeldbestand staat het resultaat van dezelfde query als hiervoor in de vierde tabel; deze haalt de gegevens uit Serie1.txt.

Wanneer u deze query vernieuwt (via rechtsklikken op het resultaat) zult u een foutmelding krijgen.

Dat is snel opgelost:

  1. dubbelklik op de betreffende query (deze heeft de vermelding De download is niet voltooid.)
  2. klik op Bron in de Toegepaste stappen
  3. wijzig in de formulebalk de naam achter File.Contents zodanig dat verwezen wordt naar de map op uw PC, waarin het Voorbeeldbestand is uitgepakt.
  4. ga terug via Sluiten en laden

LET OP rond de map- en de bestandsnaam moeten de aanhalingstekens blijven staan.

Dezelfde handelingen moet u uitvoeren wanneer u niet de gegevens van Serie1 wilt ophalen maar die van Serie2. Wijzig in de Bron de naam van het bestand en sluit de query-editor.

NB De omvang van de resultaat-tabel verandert automatisch met de grootte van het bronbestand.

Bestanden combineren

Hiervoor hebben we gezien hoe je een tekst-bestand geautomatiseerd kunt inlezen. Ook hoe je achter elkaar verschillende bestanden kunt binnenhalen.

Maar PQ kent een veel handiger manier om gelijksoortige bestanden in te lezen en wel naar één resultaat-tabel. Laten we dit eens gaan uitproberen met de 2 tekstbestanden uit het Voorbeeldbestand.

  1. kies in de menutab Gegevens in het blok Ophalen en Transformeren de optie Nieuwe query
  2. kies in het vervolg-menu de optie Uit bestand en daarna Uit map
  3. kies via Bladeren de map met de 2 tekstbestanden en klik op OK
  4. klik in het vervolgscherm (met een overzicht van alle bestanden in de betreffende map) de button Gegevens transformeren.
  5. We willen alleen de tekstbestanden inlezen: kies het vinkje naast Extension en zorg dat alleen de extensie txt wordt gefilterd.
  6. kies de button Bestanden combineren naast Content
  7. in het vervolgscherm kiezen we OK (alle instellingen staan goed)
  8. maak twee nieuwe kolommen aan (via de optie Voorbeeld; zie hiervoor): Bron wordt de Source.Name zonder extensie en Bedr wordt Bedrag/100
  9. verwijder de kolommen Source.Name en Bedrag en verplaats de nieuwe kolom Bron naar voren. Maak een sortering: eerst op Bron en als tweede op de Datum. Kies daarna Sluiten en laden.

Door PQ worden enkele hulp-query’s aangemaakt. Deze worden intern door het systeem gebruikt voor het combineren van de bestanden.
Zie het tabblad TekstCombi in het Voorbeeldbestand voor het resultaat.

Wanneer er nu een derde serie gegevens wordt aangeleverd dan plaatst u dat tekstbestand in dezelfde directory en kiest u Vernieuwen van deze resultaat-tabel.

LET OP het combineren van bestanden kan natuurlijk alleen maar als ALLE bron-bestanden dezelfde structuur hebben. Het is dus zaak om intern in de organisatie goede afspraken te maken over aanlevering van bestanden.

Van internet naar Excel

Soms is het handig om gegevens van een internet-pagina over te halen naar Excel (zie ook het artikel Excel en het world-wide-web).

We zullen hier laten zien hoe je de dagkoersen van de AEX kunt binnenhalen.

LET OP1 Excel kan alleen gegevens, die in een tabelvorm op internet worden gepresenteerd, inlezen.

LET OP2 In Excel zal het AEX-overzicht altijd iets achter lopen in de tijd; door de makers van de website is dit als bescherming opgenomen.

  1. kies in de menutab Gegevens in het blok Ophalen en Transformeren de optie Nieuwe query
  2. kies in het vervolg-menu de optie Uit andere bronnen en daarna Van het web
  3. tik de gewenste URL in of kopieer die uit de adresregel van uw browser als u weet welke pagina u wilt binnenhalen; in dit geval www.beurs.nl/koersen/AEX en druk op OK.
  4. in de Navigator, die dan opent, blijkt dat we Table 0 moeten hebben. Kies Gegevens transformeren.
  5. nu kunt u nog kolommen verwijderen, verplaatsen en andere gewenste aanpassingen doorvoeren
  6. kies Sluiten en Laden naar en plaats het overzicht op een nieuw werkblad (zie het tabblad AEX in het Voorbeeldbestand).

Het is wel handig als deze tabel zich automatisch vernieuwt:
klik in de menutab Gegevens in het blok Verbindingen op het vinkje bij Alles vernieuwen.
Kies de optie Eigenschappen van verbinding.
Zorg dat de optie Vernieuwen om de 1 minuten is aangevinkt.
Klik op OK.

De tabel kan wel wat opmaak gebruiken:

  1. voeg tijdens de transformatie in PQ een kolom StDal toe; probeer het uit via enkele voorbeelden. De formulebalk moet de volgende formule bevatten:
    = Table.AddColumn(#”Type gewijzigd”, “StDal”, each if [#”+/-“] > 0 then 1 else if [#”+/-“] = 0 then 0 else -1, Int64.Type)
  2. geef de nieuwe kolom in Excel een mooie Voorwaardelijke opmaak
  3. zorg via opmaak dat alle getallen de juiste opmaak en het gewenste aantal decimalen krijgen.

Het resultaat staat in het tabblad AEX2 van het Voorbeeldbestand:

Treemap en Waterval

Excel, , , , ,
Voorbeeldbestand: TreeMap.xlsxDownload

Zolang als Excel al bestaat, wordt het niet alleen als rekentool gebruikt maar ook voor rapportage-doeleinden.
Waar in de beginperiode meestal door middel van cijfers werd gerapporteerd, zijn daar later ook grafieken bij gekomen.

Microsoft heeft ons in de afgelopen jaren met diverse soorten grafieken verblijd.
In iedere nieuwe versie van Excel verschijnen er weer nieuwe; in dit artikel aandacht voor de Treemap en de waterval-grafiek.

NB in oudere versies van Excel en in menige MAC-versie werken de voorbeelden niet.

Basisgegevens

Om een grafiek te kunnen maken hebben we natuurlijk basisgegevens nodig.
In het Voorbeeldbestand staat in het tabblad DataTree een overzicht van Bedragen uitgesplitst naar Maand en Soort.

NB door middel van de functie Aselecttussen worden de data door Excel willekeurig gekozen; bij iedere wijziging in de werkmap zullen dus nieuwe gegevens gegenereerd worden.
Op deze manier kun je snel de impact op de grafieken zien.

De gegevens zijn opgeslagen in een Excel-tabel met de naam tblDataTree. Uitbreidingen aan deze tabel (of verwijderingen) zullen daardoor automatisch doorwerken in de overzichten en grafieken die daar op gebaseerd zijn.

Draaitabel en -grafiek

De gegevens uit het tabblad DataTree zijn in een draaitabel samengevat op het tabblad OvzTree. In dit geval is de Soort in het Filter-veld geplaatst, de Maand in de Rijen en het Bedrag in het Waarden-gebied.
Deze draaitabel kan direct vertaald worden naar een grafiek:

  1. klik ergens in de draaitabel
  2. op dat moment komt er een nieuwe menu-tab bij, Hulpmiddelen voor Draaitabellen
  3. klik daarbinnen op de menutab Analyseren en dan in het blok Extra op Draaigrafiek
  4. na enkele cosmetische aanpassingen ontstaat bovenstaande grafiek

NB de grafiektitel is dynamisch: wanneer in het Filter een andere Soort wordt gekozen past de titel zich automatisch aan.
In cel C19 wordt de basis daarvoor gelegd. U wijzigt een grafiektitel als volgt: klik in de bestaande titel, daarna in de formulebalk, voer het =-teken in, klik dan op cel C19 en druk op Enter. In de formulebalk verschijnt =OvzTree!$C$19.

Niet altijd is in deze grafiek duidelijk welke maanden in welke mate bijdragen tot het totaal (klik rechts in de Draaitabel en kies Vernieuwen). Aangezien de basisgegevens telkens opnieuw worden gegenereerd zal de Draaitabel (en dus ook de Draaigrafiek) dienovereenkomstig worden bijgewerkt.
Door de maanden anders te rangschikken kunnen de onderlinge verhoudingen duidelijker worden gemaakt:

  1. klik op het keuzevinkje achter Rijlabels
  2. kies Meer sorteeropties
  3. klik op het keuzerondje vóór Aflopend
  4. kies dan daaronder als sorteervolgorde niet voor Maand maar voor Som van Bedrag
  5. klik op OK

Bekijk het effect als je de draaitabel vernieuwd.

Treemap

Maar niet iedereen vind het lezen/interpreteren van bovenstaande grafiek makkelijk.
Waarschijnlijk zijn deze mensen meer gebaat bij een zogenaamde Treemap, bedoeld om hiërarchie in resultaten te verduidelijken.

Helaas is de Treemap niet beschikbaar als de bron een draaitabel is. Daarom is in het tabblad OvzTree van het Voorbeeldbestand een hulptabel gecreëerd, die de gegevens van de draaitabel repliceert.

In cel C27 wordt, afhankelijk van de corresponderende waarde in kolom B, het Bedrag opgehaald in de draaitabel rond cel B4.

Het maken van een Treemap is dan een peuleschil:

  1. klik op één van de cellen in het brongebied, bijvoorbeeld cel C27
  2. kies in de menutab Invoegen in het blok Grafieken voor de optie Hiërarchiegrafiek
  3. kies daar de optie Treemap
  4. uiteraard zijn diverse eigenschappen, zoals legenda nog aanpasbaar

NB Blijkbaar komt dit grafiektype van een andere software-maker; niet alle eigenschappen van een grafiek zijn beschikbaar.
De mooie ronde hoeken bijvoorbeeld zijn niet meer terug te vinden.
Ook een dynamische grafiektitel is niet op dezelfde manier, als hiervoor aangegeven, in te voeren. In dit geval (zie het tabblad OvzTree van het Voorbeeldbestand) heb ik er voor gekozen om een Tekstblok in te voegen en daar dan de verwijzing naar cel C19 te plaatsen.

Om het instellen van keuzemogelijkheden te vereenvoudigen zijn ook 2 Slicers toegevoegd: eentje voor het instellen van de Soort en een andere om bepaalde Maanden te kunnen selecteren.

Waterval-grafiek

Is niet zozeer de onderlinge verhouding van belang, maar wil je weten hoe ieder onderdeel bijdraagt aan het geheel, dan is een ander type grafiek meer voor de hand liggend, de Waterval-grafiek.

NB Ook deze grafiek in niet beschikbaar als een draaitabel de brongegevens bevat.

In het tabblad Waterval van het Voorbeeldbestand is daarom een nieuwe bron-tabel (met de naam tblDataWater) ingevoerd. Per Maand wordt hier een willekeurig Bedrag gegenereerd tussen -100 en +200.

Ook het maken van een Watervalgrafiek is dan ‘kinderspel’:

  1. klik op één van de cellen in het brongebied, bijvoorbeeld cel C3
  2. kies in de menutab Invoegen in het blok Grafieken voor de optie Waterval-, trechter-, ….
  3. kies daar de optie Waterval
  4. uiteraard zijn diverse eigenschappen, zoals legenda nog aanpasbaar

NB ook voor dit type grafiek geldt het voorbehoud, dat niet alle (standaard-)instellingen beschikbaar zijn.

Opslag-datum

Excel, VBA (Visual Basic for Applications)
Voorbeeldbestand: SaveDate.zipDownload

Ofwel de datum waarop een bestand is opgeslagen; in het Engels vaak aangeduid als SaveDate.

Word kent een handige manier om in de kop- en voetregel de datum, waarop het betreffende bestand voor de laatste keer is opgeslagen, weer te geven (SaveDate, een apart Veld binnen de optie Info over document).
Op de vraag, waar dat in Excel terug te vinden is, moest ik het antwoord schuldig blijven: Excel kent een zodanige optie niet en die is ook niet met een of andere info-functie na te bouwen.
Uiteraard even Google geraadpleegd: geen resultaten voor SaveDate in Excel, wel allerlei VBA-routines waarmee dit enigszins na te bootsen is.
Daarom in dit artikel enkele voorbeelden daarvan.

Bij opslaan datum toevoegen 1

De eerste methode, die de Word-functionaliteit goed benadert, is om de datum (en tijd) aan de voetregel toe te voegen op het moment dat het Excel-bestand wordt opgeslagen.
Drie regels binnen VBA volstaan:

Dit is een zogenaamde Event-SUBroutine; net voordat de werkmap wordt opgeslagen (BeforeSave) wordt deze procedure uitgevoerd.
De parameter SaveAsUI is verplicht. Deze parameter kan binnen de routine gebruikt worden; de waarde daarvan is True als de gebruiker Opslaan als heeft gekozen en bij gewoon Opslaan is de waarde False.
De tweede parameter (Cancel) is ook verplicht en heeft standaard de waarde False. Krijgt ergens in de procedure deze parameter de waarde True dan zal er daarna geen opslag-actie plaats vinden (opslaan wordt gecanceled).
De tweede regel is waar het om draait: links in de voetregel van het actieve werkblad wordt de combinatie van de huidige datum en tijd geplaatst. De datum heeft een speciale notatie gekregen (minimaal 1 positie voor de dagaanduiding, een uitgebreide omschrijving voor de maand (bijvoorbeeld oktober), 4 cijfers voor het jaar en dan nog een komma en een spatie).

LET OP de routine plaatst de datum ALLEEN in de voetregel van het actieve werkblad (ActiveSheet). Dit is een groot verschil met de Word-optie, die de datum in de voetregel van het document plaatst en daarmee op ALLE pagina’s.

NB1 deze routine moet in een speciale module van de betreffende werkmap geplaatst worden, namelijk in ThisWorkbook.

NB2 om er voor te zorgen, dat deze routine samen met de Excel-sheet wordt opgeslagen, dient de extensie xlsm te zijn (macro-enabled).

NB3 in alle voorbeelden in dit artikel wordt de datum links in de voetregel geplaatst. Uiteraard kunnen ook de opties CenterFooter, RightFooter, LeftHeader, CenterHeader en RightHeader gebruikt worden.

Bij opslaan datum toevoegen 2

In het Voorbeeldbestand is de routine wat uitgebreid:

Hoe plaats je een dergelijke routine in je eigen werkmap:

  1. ga naar de VBA-editor: kies binnen de menutab Ontwikkelaars de optie Visual Basic of druk op Alt-F11.
  2. dubbelklik op de module ThisWorkbook van de betreffende werkmap; rechts kan dan de projectcode voor deze module ingevoerd worden.
  3. tik de bovenstaande code in of, beter, kopieer deze uit het Voorbeeldbestand.
  4. verlaat de VBA-editor door Alt-F11 te drukken
  5. sla het bestand op; zorg dat het de extensie xlsm krijgt.

Een korte uitleg van de belangrijkste elementen van de routine:

  • eerst wordt een message-box getoond met de vraag of de voetregel aangepast moet worden; alleen Ja en Nee zijn toegestaan (vbYesNo)
  • als op de Nee-knop wordt geklikt, gebeurt er in deze subroutine niets meer. Wel slaat Excel het bestand dan nog op.
  • anders wordt de inhoud van de linker-voetregel in de variabele a geplaatst en via een tweede message-box op het scherm getoond.
  • wordt daar gekozen om niet door te gaan, dan krijgt u de waarschuwing te zien, dat het bestand niet wordt opgeslagen (doordat Cancel op True wordt gezet)
  • anders wordt de voetregel aangepast, waarna Excel het bestand opslaat.

NB1 de eerste 2 keren wordt MsgBox als functie gebruikt, waarvan het resultaat in de variabele a wordt geplaatst.
In het derde geval voert MsgBox alleen een opdracht uit; omdat het dan geen functie is, moeten de haakjes weggelaten worden.

NB2 de voetregel is iets uitgebreid: naast de tekst Opslagdatum wordt met behulp van &6 ook de lettergrootte aangepast.

Alternatief 1

Een groot nadeel van bovenstaande methodes is, dat de VBA-routine eerst in het betreffende bestand moet worden geplaatst en het bestand als xlsm moet worden opgeslagen.

Handige en/of veel gebruikte routines kunnen echter ook in een module van uw persoonlijke werkmap geplaatst worden.
De vraag is: waar kunt u die map vinden?

Ziet u in de VBA-editor ergens het project PERSONAL.XLSB dan kunt u een van de bestaande modules openen door daarop te dubbelklikken (of een extra module Invoegen).

Bestaat de persoonlijke werkmap nog niet, doe dan het volgende:

  1. open de menutab Ontwikkelaars (ziet u die niet? Klik met de rechter muisknop ergens in de menubalk en kies de optie Lint aanpassen en vink in het rechtergedeelte van het nieuw geopende scherm de optie Ontwikkelaars aan).
  2. kies de optie Macro opnemen.
    Een macro opnemen (en stoppen) kan ook altijd via de knop linksonder in de statusbalk
  3. in het midden van de pop-up kiest u Persoonlijke macrowerkmap, klik op de OK-knop en kies dan direct in de menubalk de optie Opname stoppen.

Als de persoonlijke werkmap bestaat, dan kunnen routines daarin geplaatst worden; deze zijn dan in iedere werkmap direct te gebruiken.

Het eerste alternatief is dan (routine vergelijkbaar met hierboven; tekst hieronder is makkelijk te kopiëren):
Sub DatumVoetregel()
Dim a
a = MsgBox("Wilt u datum en tijd links in de voetregel plaatsen?", _
vbYesNo, "Voetregel aanpassen")
If a = vbYes Then
a = ActiveSheet.PageSetup.LeftFooter
If a <> "" Then
a = MsgBox("In de voetregel staat nu: " & _
vbCrLf & a & vbCrLf & "Doorgaan?", _
vbYesNo, "Voetregel aanpassen")
If a = vbNo Then Exit Sub
End If
ActiveSheet.PageSetup.LeftFooter = _
"&6 " & Format(Date, "d mmmm yyyy, ") & Time
End If
End Sub

LET OP het woord Private is op de eerste regel weggelaten, anders is deze routine niet voor andere werkbladen dan de persoonlijke ‘zichtbaar’.

Deze procedure zal NIET automatisch starten wanneer u een bestand opslaat. Wat moet u doen:

  1. maak een nieuwe werkmap aan
  2. kies in de menutab Ontwikkelaars de optie Macro’s (of druk op Alt-F8)
  3. kies in het vervolgscherm de gewenste routine; in dit geval PERSONAL.XLSB!DatumVoetregel en klik op Uitvoeren (of dubbelklik op de gewenste routine)
  4. sla direct daarna het bestand op.
    LET OP Doet u dat met het voorbeeldbestand, dan zal de automatische routine ook nog opgestart worden

NB doordat er twee handelingen verricht moeten worden (de routine uitvoeren en het bestand opslaan) zal de tijd in de voetregel niet exact overeenkomen met de tijd van het opgeslagen bestand.

Alternatief 2

Een verkorte routine, die alleen de datum en tijd in de voetregel zet, is:
Sub DatumVoetregelKort()
ActiveSheet.PageSetup.LeftFooter = Format(Date, "d mmmm yyyy, ") & Time
End Sub

Alternatief 3

Een derde alternatief, waarbij de SaveDate uit de eigenschappen van het document wordt opgehaald:
Sub DatumVoetregelKort2()
ActiveSheet.PageSetup.LeftFooter = Format( _
ActiveWorkbook.BuiltinDocumentProperties("Last Save Time"), "d mmmm yyyy, hh:mm:ss")
End Sub

De werkwijze is dan net andersom:

  1. sla de werkmap op
  2. voer de routine uit
  3. print de pagina

Datumverschil ofwel ouderdom ofwel leeftijd

Excel, ,
Voorbeeldbestand: DatumVerschil.xlsxDownload

Ik kreeg onlangs de vraag hoe met behulp van voorwaardelijke opmaak bepaalde machines in een Excel-overzicht ‘gekleurd’ konden worden.
De achterliggende vraag was hoe aan de hand van een in-productie-name-datum de ‘ouderdom’ bepaald kon worden.

Algemener geformuleerd: hoe kunnen we de leeftijd van iets of iemand bepalen?
In dit artikel een simpele rechttoe-rechtaan-berekening, maar ook een methode waarbij gebruik gemaakt wordt van de ‘geheime’ Excel-formule Datumverschil.

Standaard leeftijd-berekening

Mijn eerste antwoord op de vraag was om het jaar van de startdatum af te trekken van het huidige jaar (zie cel C5 in het tabblad Standaard van het Voorbeeldbestand).

Om het systeem flexibeler te maken gebruiken we geen harde waarde voor het huidige jaar, maar nemen we het jaar van Vandaag() (cel C3; denk aan de ‘loze’ haakjes op het einde).

Direct kwam als reactie: Dank, maar ….
Het bekende leeftijd-probleem: het laatste jaar mag niet meetellen als de huidige dag-maand-combinatie vóór die van de startdatum ligt.

Snel opgelost (zie cel C6 in het voorbeeld):
=JAAR(C3)-JAAR(C2)-ALS(TEKST(C3;”mm-dd”)<TEKST(C2;”mm-dd”);1;0)

De berekening van cel C5 corrigeren we door te kijken of de maand-dag-combinatie van de einddatum daar aanleiding toe geeft.
Zo ja, dan trekken we 1 af van het resultaat en anders 0.

NB1 het isoleren van de dag en maand doen we in deze formule door van de datum teksten te maken, waarbij we alleen de maand en dag meenemen (zie het artikel Data (datums)).

NB2 let op de volgorde van dag en maand in de Tekst-functie.

NB3 de formule kan nog wat korter (zie cel C7):
=JAAR(C3)-JAAR(C2)-(TEKST(C3;”mm-dd”)<TEKST(C2;”mm-dd”))
We maken hierbij gebruik van het feit, dat, wanneer een vergelijking (in dit geval: kleiner dan) WAAR is, Excel intern hier de waarde 1 aan toekent en anders de waarde 0.

NB4 de werking van een formule kun je altijd bekijken met behulp van de optie Formule evalueren; te vinden in de menutab Formules in het blok Formules controleren.

Leeftijd

Maar nu we toch bezig zijn: de leeftijd kun je natuurlijk ook in dagen, weken of maanden uitdrukken. Hoe bereken je die?

  • de leeftijd in dagen is in Excel heel gemakkelijk: =C3-C2
    Hierbij maken we gebruik van de eigenschap dat in Excel een datum intern wordt vertaald naar het aantal dagen dat verstreken is sinds 1 jan 1900.
  • het aantal weken is het voorgaande resultaat gedeeld door 7.
    Alleen geïnteresseerd in hele weken? In cel D11 van het Voorbeeldbestand, tabblad Standaard, staat de formule =GEHEEL(C10/7).
  • voor het aantal maanden hanteren we een benadering: de leeftijd in dagen gedeeld door het gemiddeld aantal dagen per maand.
    In cel C12 staat de formule: =C10/DagenMnd.
    NB1 deze benadering werkt goed als er flink wat jaren tussen begin- en einddatum zitten.
    NB2 een exacte berekening staat in cel F12: =12*(JAAR(C3)-JAAR(C2)-1)+(12-MAAND(C2))+(MAAND(C3)-1)
    NB3 DagenMnd is een verwijzing naar cel C3 in het tabblad BasisGeg van het Voorbeeldbestand .
  • het aantal jaren is in cel C13 benaderd door het aantal leeftijd-dagen te delen door een gemiddeld aantal dagen per jaar (365,25 in verband met de schrikkeljaren).

Hiervoor hebben we gezien hoe we de leeftijd in jaren kunnen bepalen.

Wil je ook nog weten hoeveel maanden of dagen er ondertussen in het laatste (onvolledige) jaar respectievelijk maand zijn verstreken dan hebben we nog 2 extra berekeningen nodig:

  • Restmaanden: =D12-D13*12
    ofwel de leeftijd in maanden minus de leeftijd in jaren maal 12
  • Restdagen: =C10-D12*DagenMnd
    ofwel de leeftijd in dagen minus de leeftijd in maanden maal het gemiddeld aantal dagen per maand
    LET OP de laatste berekening levert een getal op met decimalen. Door de benadering kan het resultaat afwijken van de exacte waarde.

Niet iedereen kan het resultaat van zo’n berekening in de vorm van een tabel goed lezen.
Daarom kan het soms handig zijn om de resultaten op te nemen in een tekst.
Een voorbeeld is te vinden in cel B18:
=”Leeftijd is “&D13&” jaar, “&D15&” maanden en “&D16&” dagen”
Teksten en cel-verwijzingen worden aan elkaar gekoppeld met het &-teken.

LET OP om teksten en de waardes uit de cellen van elkaar te scheiden zijn aan de teksten op diverse plaatsen spaties toegevoegd.

Wil je geen decimalen bij de dagen weergeven:
=”Leeftijd is “&D13&” jaar, “&D15&” maanden en “&TEKST(D16;”0″)&” dagen”
LET OP met de Tekst-functie wordt het resultaat afgerond niet ‘afgehakt’.

Een iets andere vorm van deze formule:
=”Leeftijd is ” & D13 & ” jaar, ” & D15 & ” maanden en ” &
TEKST(D16;”0 “”dagen”””)
De tekst dagen is in de opmaak van cel D16 opgenomen.
LET OP Tekst binnen de opmaak moet tussen “-tekens staan; omdat dit binnen een andere set aanhalingstekens komt moeten er zelfs dubbele “-tekens gebruikt worden.

Functie Datumverschil

Waar we in het voorgaande alle berekeningen uitvoerden met eigen formules blijkt Excel ‘onder de motorkap’ een functie te hebben waarmee de resultaten sneller kunnen worden geproduceerd

(en exacter omdat er geen benaderingen nodig zijn voor de dagen per jaar en per maand).

Excel bevat namelijk de functie Datumverschil. Deze kent drie parameters/argumenten: een begindatum, een einddatum en een aanduiding welk resultaat berekend moet worden (het aantal dagen, maanden of jaren).
De functie wordt door Microsoft niet getoond in het overzicht bij Functie invoegen. Dus de functie moet handmatig ingevoerd worden: =datumverschil(arg1;arg2;arg3).

Ook zul je in het scherm Functieargumenten geen toelichting op de verschillende argumenten vinden.

NB Microsoft houdt wat slagen om de arm:

Als we nu het aantal dagen tussen een begin- en einddatum willen weten kunnen we de volgende formule gebruiken: =datumverschil(C2;C3;”d”), waarbij we er vanuit gaan dat cel C2 de begindatum bevat en C3 de einddatum.
Willen we het aantal maanden weten, dan vervangen we “d” door “m”. Om het aantal jaren te weten te komen, moet de derde parameter “y” zijn.

LET OP gebruik de y en niet de j. Hoofdletters zijn wel toegestaan. Het is niet duidelijk of dit vertaal-probleem in iedere versie van Excel optreedt. Experimenteren dus.

NB in het Voorbeeldbestand in het tabblad DatumVerschil staat overal als derde argument een celverwijzing. Dan worden de “-tekens weggelaten.

Maar … de functie kent nog meer opties voor de derde parameter: gebruik je yd, dan worden de resterende dagen berekend (dus het aantal dagen na de vorige “verjaardag”); gebruik je ym, dan is het resultaat het aantal resterende maanden en bij het invoeren van md wordt het aantal resterende dagen bepaald, die niet in een volledige maand zitten.

Dan nog een waarschuwing van Microsoft:

LET OP de oplossing die hier aangedragen wordt levert niet hetzelfde op als bedoeld met MD. Hier worden slechts de dagen geturfd in de lopende maand op het einde; de ‘losse’ dagen aan het begin worden niet meegenomen.