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:
- klik ergens in de draaitabel
- op dat moment komt er een nieuwe menu-tab bij, Hulpmiddelen voor Draaitabellen
- klik daarbinnen op de menutab Analyseren en dan in het blok Extra op Draaigrafiek
- 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:
- klik op het keuzevinkje achter Rijlabels
- kies Meer sorteeropties
- klik op het keuzerondje vóór Aflopend
- kies dan daaronder als sorteervolgorde niet voor Maand maar voor Som van Bedrag
- 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:
- klik op één van de cellen in het brongebied, bijvoorbeeld cel C27
- kies in de menutab Invoegen in het blok Grafieken voor de optie Hiërarchiegrafiek
- kies daar de optie Treemap
- 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’:
- klik op één van de cellen in het brongebied, bijvoorbeeld cel C3
- kies in de menutab Invoegen in het blok Grafieken voor de optie Waterval-, trechter-, ….
- kies daar de optie Waterval
- uiteraard zijn diverse eigenschappen, zoals legenda nog aanpasbaar
NB ook voor dit type grafiek geldt het voorbehoud, dat niet alle (standaard-)instellingen beschikbaar zijn.