Een Project Dashboard Maken in Excel – deel 1/3

In dit blog leer je hoe je een projectplanning op een aantrekkelijke wijze visualiseert in een Excel dashboard. Je leert het dashboard zo efficiënt mogelijk op te zetten, zodat wijzigingen in de brongegevens automatisch doorstromen naar het dashboard. En alles met zo min mogelijk formules! De eerste versie van het dashboard dat je gaat maken ziet er als volgt uit.

In dit blog leggen we de basis voor het model en beantwoorden we de meest fundamentele vraag: Wanneer is een project gestart en wanneer eindigt het project? In de vervolg blogs breiden we het dashboard steeds verder uit zodat deze ook de andere vragen beantwoordt.

De Casus

Je bent een projectmanager en hebt behoefte aan een overzicht van alle projecten die je managet. Het dashboard moet de volgende vragen beantwoorden en op een overzichtelijke manier weergeven: Wanneer is een project gestart, wanneer eindigt het project, hoeveel dagen nog te gaan en is er evt. een deadline overschreden? Bovendien bestaat iedere klus uit meerder sub-taken. Je wilt ook inzicht hebben in de ontwikkeling van deze sub-taken.

Wat je gaat leren

Iedereen is wel bekend met Excel. Je kunt er gegevens in kwijt en formules mee schrijven zodat ze automatisch doorberekend worden. Maar wist je dat Excel nog veel meer kan? Je leert een aantal zeer krachtige aspecten van Excel gebruiken waar de meesten nog niet bekend mee zijn: Tabellen, Power Pivot, Draaitabellen, Slicers en Voorwaardelijke Opmaak.

Let op: de planning tool is ontwikkeld in Microsoft Excel 365 voor Windows en werkt niet voor Excel voor de Mac!

Gegevensopslag

Ieder robuust Excel model voldoet aan het drie-pijler principe:

1. Het bevat gegevens (data)
2. Die worden gescheiden van berekeningen (calc)
3. Die weer worden gescheiden van de presentatie of visualisatie van de resultaten (viz).

Praktisch betekent dit dat ieder functioneel deel van de oplossing, de gegevens, de berekeningen en de presentatie laag, in een aparte omgeving beheerd wordt. Doorgaans is dat op een apart werkblad, maar kan ook op andere manieren.

In de oplossing die je gaat bouwen wijk je hiervan af. Je gaat de gegevens opslaan op een apart werkblad, je doet alle berekeningen achter de schermen in Power Pivot, en presenteert het resultaat met een draaitabel op een tweede tabblad.

De totale oplossing zal dus uit slechts twee tabbladen bestaan.

Je begint met de gegevensopslag. Per project moet ten minste de volgende kenmerken vastleggen: Projectnaam, Taakbeschrijving, Startdatum, Einddatum. Gebruik hiervoor onderstaande data en kopieer dit op het eerste werkblad in een nieuw Excel bestand.

Projectnaam	Taakbeschrijving	Startdatum	Einddatum
Project 1	Taak 1	19-Oct-20	26-Oct-20
Project 1	Taak 2	21-Oct-20	28-Oct-20
Project 1	Taak 3	24-Oct-20	31-Oct-20
Project 2	Taak 1	28-Oct-20	04-Nov-20
Project 2	Taak 2	29-Oct-20	05-Nov-20

Het gegevensmodel

De volgende stap ga je de berekeningen toe voegen. Hiervoor ga je gebruik maken van Power Pivot, één van de sterkste onderdelen van Excel, en waar veel Excel gebruikers nooit van gehoord hebben. Power Pivot is een COM invoegtoepassing van Microsoft. Als je het tabblad niet in het Excel menu ziet, dan moet je de Power Pivot eerst activeren.

Power Pivot installeren

Ga naar Bestand > Opties > Invoegtoepassingen en kies onderaan bij Beheren uit het keuzelijstje voor COM-invoegtoepassingen. Druk vervolgens op Start.

Je krijgt nu de optie om de geïnstalleerde COM-invoegtoepassingen te activeren. Vink de volgende optie aan ‘Microsoft Power Pivot for Excel’ en druk op OK.

Je Gegevens importeren

Selecteer nu een willekeurige cel in de ‘Projecten’ tabel, ga naar tabblad Power Pivot in het Excel menu en druk op ‘Toevoegen aan gegevensmodel’. Met dit commando importeer je de gegevens in Power Pivot, die je als een apart tabblad ziet weergegeven in de Power Pivot interface.

Een kalender toevoegen

Aangezien we een projectplanning willen visualiseren, moeten we toegang hebben tot het bereik van alle start, – en einddatums die in onze ‘Onderhoud’ tabel zijn opgenomen. Een veelvoorkomende tabel in ieder Power Pivot gegevensmodel is de kalender tabel – een tabel met voor iedere dag een aparte regel. Deze tabel wordt vaak uitgebreid met extra tijdsrelevante gegevens, zoals Jaar, Maand, Weeknummer etc.

Het is heel eenvoudig om in Power Pivot een kalender tabel aan te maken. Ga in de Power Pivot interface naar tabblad Ontwerpen en druk op de knop Gegevensmodel in de groep ‘Agenda’s’. In het keuzelijstje kies je vervolgens voor Nieuw.

Je ziet een nieuw tabblad verschijnen, genaamd Agenda, met daarin alle dagen van de jaren die in de tabel zijn gevonden. In dit geval zijn dit alle dagen van 2020. Verder zie je een aantal berekende kolommen, ieder met een aparte eigenschap van de eerste datum kolom: Jaar, Maandnummer, Maand… Wanneer je zo’n berekende kolom selecteert, zie je in de formulebalk een berekening staan die verdacht veel op Excel formules lijken, toch?

Overigens zie je dat de formules in Power Pivot Engels zijn, ondanks dat de gebruikersinterface wel in het Nederlands is. Daar is niets aan te doen…

Voor je projectenoverzicht wil je alles per dag, per week kunnen overzien. Hiervoor moet de dag, – en de weekinformatie in de Agenda tabel worden toegevoegd. De dag informatie heb je al, de weekinformatie nog niet. Voeg twee nieuwe berekende kolommen toe via ‘Kolom toevoegen’:

• Weeknummer =WEEKNUM(Agenda[Date])
• Weekdag= LEFT(Agenda[Dag van de week],2)

Om de [Weekdag] straks in de juiste volgorde te kunnen tonen, moet je de kolom sorteren op basis van de numerieke waarde in [Nummer voor de dag van de week]. Hiervoor moet je eerste de formule voor kolom [Nummer voor de dag van de week] én [Weeknummer] aanpassen zodat deze maandag als de eerste dag van de week retourneert. Pas hiervoor het optionele tweede argument aan naar 2 (Week start op maandag).

Selecteer vervolgens eerst [Weekdag] en kies dan Start > Op kolom sorteren en dan de eerste optie. In het dialoogvenster kies je vervolgens [Nummer voor de dag van de week] in het lege selectievakje.

Het sorteren van tekstuele kolommen op basis van andere numerieke kolommen is specifiek aan Power Pivot, en werkelijk een krachtige functie!

De Berekeningen

Je gaat allereerst een nieuwe meting toevoegen in Power Pivot. Dit lijkt op een Excel formule maar wordt achter de schermen tegen het gegevensmodel uitgevoerd, niet tegen de gegevens zoals die in je werkbladen staan. Je berekeningen in Power Pivot uitvoeren heeft meerdere voordelen:

• Kan met meer regels overweg dan dat er in een werkblad opgeslagen kunnen worden
• Je kunt gegevens uit meerdere bronnen met elkaar combineren, door een relatie te leggen tussen de ingeladen tabellen
• Het aantal formules dat je moet onderhouden is significant minder dan een traditioneel spreadsheetmodel waarin formules per cel doorgevoerd moeten worden

Sluit de Power Pivot interface en ga in Excel naar tabblad Power Pivot. Druk op de knop Metingen en selecteer ‘Nieuwe meting…’ uit het keuzelijstje.

Drie basis berekeningen

Je start met het maken van een aantal basismetingen. In Power Pivot kun je namelijk niet zomaar naar een cel verwijzen zoals je in Excel gewend bent. Je kunt er alleen kolomverwijzingen gebruiken. Om metingen in Power pivot te maken moet je de kolommen eerst in een aggregaat functie samenvatten, zoals onder meer een SOM, MIN of MAX functie.

Als je bekend bent met draaitabellen komt dit gedrag je bekend voor als het goed is – ook daar werk je alleen met kolommen, de zogenaamde draaivelden. En voor ieder veld dat je in het Waarden gebied van de draaitabel wilt samenvatten, moet je een aggregaat functie selecteren.

Goed, neem de volgende basismetingen over. Volg daarbij de volgende stappen:

1. Kies eerst de brontabel waaraan je de berekening wilt koppelen
2. Daarna geef je de meting een nieuwe naam
3. Neem daarna de formule over
4. Kies tot slot de juiste opmaakoptie

Je ziet, Power Pivot metingen zijn vergelijkbaar met Excel formules. Basismetingen zijn simpele functies die niets anders doen dan een enkele kolom samenvatten. Het voordeel ten opzichte van functies die je in Excel schrijft, is dat je iedere meting expliciet een naam moet geven. Dit komt later goed van pas wanneer je de metingen gaat oproepen in meer complexe metingen.

Een draaitabel invoegen

Tijd om een draaitabel in te voegen om je werk te kunnen checken. Ga naar Invoegen > Draaitabel, en klik op OK. Excel ziet dat je een gegevensmodel hebt gemaakt en heeft dat automatisch als bron aangemerkt voor de nieuw aan te maken draaitabel.

Wat opvalt, wanneer je de draaitabel hebt geselecteerd, is dat in het rechtermenu niet alleen de kolomnamen van de Onderhoud tabel staan, maar ook de kolomnamen van de Agenda tabel. Inderdaad, de draaitabel die je nu voor je hebt kan overweg met meerdere gegevensbronnen!

Sleep de volgende velden in de draaitabel: Projecten[Projectnaam] en Projecten[Taakbeschrijving] in rijen en Agenda[Weeknummer] en Agenda[Weekdag] in kolommen. Om te controleren of je berekeningen werken sleep je de basis berekeningen in het Waarden bereik.

De filter context

Wat opvalt is dat iedere cel in het waarden bereik hetzelfde resultaat toont. Dit lijkt misschien raar, maar is simpel te verklaren. Iedere cel in het waarden bereik van een draaitabel heeft namelijk één of meerdere actieve filters die het resultaat van de onderliggende aggregaat functie beïnvloeden.

Je ziet dat basisberekening [Dag] voer iedere dag de juiste dag weergeeft. Dit komt omdat onderliggend de Agenda tabel is gefilterd tot alleen de regel voor de bewuste dag, en daarover het Minimum wordt berekend.

Basisberekening [Start] toont echter voor iedere dag dezelfde startdatum, maar deze is per taak weer verschillend. Dit komt doordat de draaitabel de Projecten tabel niet filtert per dag maar weel per taak.

Je kunt de filters overigens in actie zien wanneer je een waarde dubbelklikt. Excel voegt dan automatisch een werkblad toe met daarin de onderliggende tabel, gefilterd op basis van de geldende filter criteria.

Nog een Power Pivot meting

Ok, je hebt alle zaken op orde. Nu is het tijd om de laatste meting toe te voegen – een meting die voor iedere dag het cijfer 1 teruggeeft als deze binnen de start en de einddatum ligt van een bepaalde taak ligt. Neem de volgende meeting over:

Je ziet dat deze formule dezelfde IF-formule gebruikt als je gewend bent in Excel. Er zijn echter twee dingen in deze formule die alleen beschikbaar zijn in Power Pivot.

Ten eerste is er een speciaal teken (‘&&’) om een logische EN vergelijking te doen. In Excel is dit gelijk aan de logische EN() formule. Deze neemt twee of meer expressies en kijkt of ze allemaal WAAR zijn. In Power Pivot is er ook een vergelijkbare AND() formule, maar deze accepteert alleen twee expressies. Het gebruik van de ‘&&’ biedt meer flexibiliteit.

Ten tweede zie je in het laatste argument van de IF formule BLANK() staan. Deze formule retourneert een lege waarde. In Excel wordt vaak gebruik gemaakt van een lege tekstwaarde (“”) om een lege waarde te retourneren. In werkelijkheid is dit nog steeds een geldige tekst-waarde voor Excel, in tegenstelling tot de BLANK() formule, die echt als ‘niets’ wordt beschouwd.

De planning visualiseren

Tijd om alles bij elkaar te voegen. Verwijder de basisberekeningen uit de draaitabel en voeg [Taak] toe.

Je ziet dat direct dat het datum bereik is aangepast naar het bereik van de Projecten tabel. Dat is de BLANK() formule die z’n werk doet – Excel ziet letterlijk ‘niets’ buiten het datumbereik dat jij hebt gespecificeerd in de Projecten tabel, en toont dit ook niet in de draaitabel.

Verder zie je allemaal 1’tjes voor iedere dag die binnen de planning valt van een bepaalde taak. Deze waarden gaan we gebruiken om de cellen op te maken zodat het blokjes worden in de plaats van cijfertjes. Aan elkaar grenzende blokjes vormen een balkje, en daarmee visualiseer je de duur van een bepaalde taak – precies wat je nodig hebt.

Conditionele opmaak

De draaitabel met statussen staat al klaar. Echter staat de status per sub-taak voor iedere dag telkens volledig uitgeschreven – niet erg overzichtelijk. Met een simpele voorwaardelijke opmaak kunnen we de status teksten snel in een gekleurd vakje veranderen, zodat ze per regel een mooie balk vormen.

Selecteer een willekeurige cel in het waarden bereik van de draaitabel. Ga via Start > Voorwaardelijke Opmaak > Markeringsregels voor cellen en kies ‘Gelijk aan…’ uit het uitklapmenu.

In het dialoogvenster dat verschijnt zet je de eerste waarde op 1 en kies je in het tweede vakje de laatste optie uit het keuzelijstje: Aangepaste indeling…

Tot slot moet je de opmaak van deze cel nog bepalen. Via tabblad Opvulling pas je de opvulkleur aan naar groen. Op tabblad Rand kies je een witte boven en onderrand, en op tabblad Getal kies je bij Categorie voor ‘Aangepast’, en past bij Type de waarde aan naar drie puntkomma’s (;;;).

Deze getal notatie zorgt ervoor dat de waarde in een cel niet getoond wordt, zonder dat de waarde echt gewist wordt – je kunt er gewoon berekeningen mee doen, of voorwaardelijke opmaak in dit geval.

Het resultaat begint al ergens op te lijken.

Het eindtotaal kan wel verwijderd worden in de laatste rij. Selecteer de draaitabel, ga naar Ontwerpen > Eindtotalen en kies ‘Uit voor rijen en kolommen’ uit het uitklapmenu.

Gefeliciteerd, je hebt een Gant-chart gemaakt met een draaitabel! Het mooie hiervan is dat de grafiek alle eigenschappen van de draaitabel behoudt. Zo kun je bijvoorbeeld je projecten inklappen om alleen de voortgang per project te checken.

Verdere opmaak

Er is een aantal zaken dat nog aandacht behoeft. Zo is kolom B breder dan nodig. Ook is de kolombreedte van iedere eerste dag van de week breder dan de overige dagen. Het doel is om alle kolommen in het waarden bereik dezelfde breedte te geven. Anders klopt de visualisatie immers niet – iedere dag dient eenzelfde breedte in te nemen.

De reden hiervoor is dat draaitabellen automatisch de opmaak van de kolommen aanpassen wanneer je ze ververst. Dit kunnen we gelukkig uitschakelen. Selecteer de draaitabel en kies Draaitabel Analyseren > Opties. Zet het één na laatste vinkje uit en klik op OK.

Vervolgens moet je voor alle kolommen in het werkblad, vanaf kolom B de kolombreedte aanpassen. Selecteer kolom B en druk vervolgens tegelijkertijd op Control + Shift + Pijltje naar rechts. Druk op de rechtermuisknop en selecteer ‘Kolom Breedte…’ en pas de waarde aan naar 3.

Dit ziet er al beter uit, maar er klopt nog 1 ding niet. De weeknummers zijn veranderd in een hekje (#).

Dit komt omdat de labels langer zijn dan de breedte van de kolom. Dit ligt niet zozeer aan de lengte van de tekst (dit is immers maximaal twee tekens), maar aan de +/- knopjes die nog in het kolommen bereik van de draaitabel zitten. Ook dit kun je gelukkig uitschakelen via Draaitabel Analyseren op ‘+/- Knoppen’ te drukken.

Slicers toevoegen

Het mooie van deze opzet is dat de basis een draaitabel is. Deze zijn van nature heel flexibel – ze groeien mee met het gegevensbereik waarop ze gebaseerd zijn. Als er dus nieuwe projecten bijkomen, dan hoef je de gegevensbron alleen te verversen en de draaitabel reflecteert je aanpassingen automatisch.

Een ander pluspunt van een draaitabel, is dat je er Slicers aan kunt koppelen. Slicers zijn gebruiksvriendelijke filters die je aan één of meerdere draaitabellen kunt koppelen. In dit geval is het handig om een filter toe te voegen voor het weeknummer, zodat je straks gemakkelijk de tijdsreeks kunt filteren om alleen de projecten te zijn die daarbinnen vallen.

Selecteer de draaitabel en voeg een slicer toe via Invoegen > Slicer. Selecteer de kolom die je wilt gebruiken, Agenda[Weeknummer], en klik op OK.

Positioneer de slicer links van de draaitabel, verberg de rasterlijnen, voeg nog een titel toe en je model is klaar voor gebruik!

Samenvattend

Je hebt de basis gelegd voor je planning tool. Door slim gebruik te maken van verschillende Excel functies, heb je een dynamisch model gemaakt dat klaar is om met jouw toekomstige projecten mee te groeien.

• Je hebt geleerd hoe je je model in drie stukken opdeelt: de gegevensopslag, de berekeningen en de presentatie.
• Hoe je je gegevens in een Excel tabel kunt beheren en kunt koppelen aan Power Pivot.
• Hoe je berekende kolommen in Power Pivot maakt, en special berekeningen die je in een draaitabel kunt gebruiken.
• En hoe je door voorwaardelijke opmaak en een handjevol andere opmaak trucjes, een draaitabel kunt omtoveren tot een Gantt-chart.

In de volgende blog gaan we het model verder uitbreidden zodat het je andere vragen kan beantwoorden. Zoals, hoever is een project gevorderd, is het al voltooid of is de deadline al overschreden?