Een Project Dashboard Makenin Excel

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.

mijn-protection

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:

  • Het bevatgegevens (data)
  • Die worden gescheiden van berekeningen (calc)
  • Die weer worden gescheiden van depresentatieof visualisatievan de resultaten (viz).
excel-calc

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.

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.

project

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.

project

Je Gegevensimporteren

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.

project

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, Weeknummeretc.

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.

project

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?

project

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).

project
project

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.

project

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.

project

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:

  • Kies eerst de brontabel waaraan je de berekening wilt koppelen
  • Daarna geef je de meting een nieuwe naam
  • Neem daarna de formule over

Kies tot slot de juiste opmaakoptie

project
project
project

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!

project

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.

project

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:

project

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 logischeEN() formule. Deze neemttwee 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.

project

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.

project

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…

project

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

project

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.

project

Het resultaat begint al ergens op te lijken.

project

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.

project

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.

project

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.

project

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.

project

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.

project

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

project

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.

project

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.

project

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

project

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?

Visual Basic for Applications; wat zijn de mogelijkheden?

VBA is een krachtige programmeertaal specifiek ontwikkeld voor het automatiseren van taken in Microsoft Office-toepassingen, zoals Excel, Word en PowerPoint. De taal biedt toegang tot de functies en objecten van deze toepassingen, waardoor het mogelijk is om specifieke taken te automatiseren via macro’s en scripts. Waarom VBA gebruiken? VBA gebruik je omdat je taken kan […]

Wat zijn de mogelijkheden in Excel om te zoeken in een tabel naar gegevens?

De mogelijkheden om in Excel te zoeken naar gegevens in een tabel zijn eindeloos. Een van de krachtigste methoden is de INDEX MATCH MATCH-functie (Nederlandse versie: INDEX VERGELIJKEN VERGELIJKEN). Deze functie is een combinatie van de INDEX en MATCH functies en maakt het mogelijk om gegevens te zoeken met meerdere zoekcriteria. De INDEX-functie geeft de […]

Breng leegstand in kaart met interactieve grafiek in Excel

Je bent net begonnen als adviseur ruimtelijke ordening. De eerste opdracht is het in kaart brengen van de leegstand bij het lokale winkelcentrum. Na weken wachten heb je dan eindelijk een databestand in Excel. Net op tijd, want over twee dagen mag je een eerste update geven aan de stakeholders. Het liefst wil je iets […]

Meer inzicht in je maandelijkse marges met Excel

Met je draadloze headset ben jij als oplosser van problemen de hele dag in gesprek met mensen. Detachering of werving & selectie, jij zorgt voor de juiste mensen op de juiste plaats. ’s Avonds zit je op je gemak nog even in Excel de administratie te doen. Doordat je alles strak elke dag bijwerkt, heb […]

Maak het leven makkelijker met een macro in Excel

Als geslaagd uitbater van de beste broodjeszaak, weet je inmiddels waar je omzet mist. Met de vorige blog heb je namelijk je eigen overzicht gemaakt. Een dervingsdashboard met een heldere splitsing tussen het invoerwerkblad en het werkblad waar informatie wordt bewaard en samengevoegd. Je bent alleen nog elke keer de data handmatig van het ene […]

Inzicht in derving met Excel formules en een draaitabel

Als succesvolle horeca ondernemer maak jij zulke lekkere broodjes, dat mensen helemaal van de andere kant van de stad naar jouw zaak komen. Het gaat goed, maar toch heb je het gevoel dat je af en toe wat omzet mist. Je weet alleen niet waar en wanneer. In drie eenvoudige stappen laat ik je zien […]

Meer leads uit je Telesales met Excel? Twee gouden tips

Als teamlead Telesales geef je graag je (interne) klanten inzicht in hoe effectief er gebeld wordt. Hoeveel leads levert een campagne eigenlijk op en in welke regio’s? Voor jezelf wil je weten wat er op de verschillende dagen gepresteerd wordt en door wie. Zo kan jij bepalen welke toppresteerder de minder presterende medewerker verder kan […]

In drie eenvoudige stappen je huisstijl in Excel integreren

Je hebt net een prachtige nieuwe huisstijl laten ontwerpen. Een mooie marketing investering waar je de komende jaren weer fris mee voor de dag kan komen. In Excel gebruik je een aantal documenten met formules voor je dagelijks werk. Offerte documenten bijvoorbeeld en facturen. Daar wil je ook dat nieuwe logo in doorvoeren en dat […]

Snel en foutloos factureren in Excel – Hoe doe je dat?

Facturatie is voor de meeste ZZP’ers een haat-liefde verhouding. Het is heerlijk om met je product een lach op het gezicht van je klant te toveren. Daar doe je het tenslotte voor. Aan het eind van de maand is het tijd om de facturen de deur uit te doen. De schoorsteen moet roken! Dat doe […]

HR performance verbeteren – hoe doe je dat met Excel?

Je bent een succesvolle HR manager met verschillende regio’s onder je hoede. Onder de maat presterende regio’s is jouw volgende actiepunt. Het verzuim verlagen, het verloop verminderen, het opleidingsniveau verbeteren en de wervingsefficiëntie verhogen. Waar te beginnen? Je hebt gelukkig wel de data beschikbaar. Dat is al een hele stap. Maar hoe kom je nou […]