In het tweede blog heb je de visualisatie verrijkt met de status van een project: hoeveel dagen zijn er nog te gaan in een project, is een project al voltooid of is de deadline overschreden en zo ja, hoeveel dagen al?

Als opfrisser zie je hieronder wat je hebt gemaakt.

In dit laatste blog ga je het dashboard gebruiksklaar maken. Je gaat de slicer vervangen met een tijdlijn, visuele indicators toevoegen voor taken die te laat zijn en je voegt een controle notificatie toe voor wanneer het model geüpdatet moet worden.

Met het bestand van het vorige blog (link invoegen) kun je alle stappen meedoen. Je kunt het volledige bestand van dit deel ook hier downloaden (link invoegen).

Datumbereik Aanpassen

Voor dit deel passen we de dataset weer aan. We voegen er nog meer projecten aan toe en verlengen de planning tot in februari 2021. Zie onderstaande dataset:

Projectnaam	Taakbeschrijving	Startdatum	Einddatum	Voltooid
Project 1	Taak 1	15-Dec-20	24-Dec-20	WAAR
Project 1	Taak 2	18-Dec-20	31-Dec-20	WAAR
Project 1	Taak 3	20-Dec-20	27-Dec-20	ONWAAR
Project 2	Taak 1	23-Dec-20	01-Jan-21	ONWAAR
Project 2	Taak 2	27-Dec-20	10-Jan-21	ONWAAR
Project 3	Taak 1	30-Dec-20	07-Jan-21	ONWAAR
Project 3	Taak 2	31-Dec-20	07-Jan-21	ONWAAR
Project 3	Taak 3	04-Jan-21	14-Jan-21	ONWAAR
Project 3	Taak 4	04-Jan-21	18-Jan-21	ONWAAR
Project 4	Taak 1	05-Jan-21	09-Jan-21	ONWAAR
Project 4	Taak 2	06-Jan-21	19-Jan-21	ONWAAR
Project 4	Taak 1	09-Jan-21	17-Jan-21	ONWAAR
Project 4	Taak 2	10-Jan-21	16-Jan-21	ONWAAR
Project 5	Taak 1	10-Jan-21	24-Jan-21	ONWAAR
Project 5	Taak 2	13-Jan-21	21-Jan-21	ONWAAR
Project 6	Taak 1	13-Jan-21	22-Jan-21	ONWAAR
Project 6	Taak 2	13-Jan-21	22-Jan-21	ONWAAR
Project 6	Taak 3	16-Jan-21	21-Jan-21	ONWAAR
Project 6	Taak 4	18-Jan-21	28-Jan-21	ONWAAR
Project 7	Taak 1	22-Jan-21	04-Feb-21	ONWAAR
Project 7	Taak 2	26-Jan-21	05-Feb-21	ONWAAR
Project 7	Taak 3	27-Jan-21	06-Feb-21	ONWAAR
Project 7	Taak 4	29-Jan-21	03-Feb-21	ONWAAR
Project 7	Taak 5	01-Feb-21	07-Feb-21	ONWAAR
Project 7	Taak 6	03-Feb-21	16-Feb-21	ONWAAR
Project 8	Taak 1	03-Feb-21	12-Feb-21	ONWAAR
Project 9	Taak 1	06-Feb-21	13-Feb-21	ONWAAR
Project 9	Taak 2	10-Feb-21	22-Feb-21	ONWAAR
Project 9	Taak 3	10-Feb-21	23-Feb-21	ONWAAR

Wanner je het gegevens model ververst zie je dat het dashboard niet ververst lijkt het:

Je ziet alleen de taken die in 2020 zijn gestart. Hoe zou dit kunnen?

Weet je nog dat het projectoverzicht uit een Power Pivot draaitabel bestaat? En in Power Pivot heb je een Agenda tabel gemaakt om de tijdslijn in de draaitabel te creëren. Laten we in de Power Pivot editor openen en die tabel eens inspecteren.

Agenda Tabel Updaten

Open de Power Pivot editor via Power Pivot > Beheren en ga naar de Agenda tabel. Sorteer het eerste veld van boven naar beneden zodat de grootste datum in de eerste regel komt.

Hier zie je het probleem: de Agenda tabel loopt maar tot eind 2020! Geen nood, dit kunnen we makkelijk aanpassen. In de Power Pivot editor kun je via Ontwerpen > Gegevenstabel > Bereik bijwerken:

Pas het bereik aan naar 31-Dec-2021 en druk op OK.

Draaitabel Updaten

Ga terug naar het dashboard en ververs de draaitabel. Nog steeds zie je projecten in 2021! Zie jij wat er aan de hand is (hint: zie de arcering)?

Inderdaad, de draaitabel toont in de kolommen alleen de weeknummers en niet de jaren. Je moet het jaartal dus in het kolommenbereik van de draaitabel toevoegen.

Merk op dat de jaartallen niet leesbaar zijn in het kolommenbereik. Dit komt omdat je de kolombreedte hebt aangepast. Nu zie je alleen ‘##’, maar dat maakt niet zo veel uit. Je kunt deze regel, en die erboven gewoon verbergen.

Selecteer de twee bovenste regels van de draaitabel, ga met je muis naar de rijkoppen en kies in het rechtermuisklikmenu voor Verbergen:

Een Moment van Reflectie

Voordat je verdergaat moeten we even terug naar wat we net gedaan hebben. We hebben het model aan moeten passen omdat we in het nieuwe jaar zitten. Hiervoor moest het bereik van de Agenda tabel handmatig aangepast worden.

Als het goed is, is er al een belletje bij je gaan rinkelen bij het lezen van de term ‘handmatige aangepast’. Inderdaad, je bent bezig een slim Excel model te maken en daarin hoort in principe geen handmatige update thuis.

Je hebt nu een design-keuze te maken. Je kunt de Agenda tabel vervangen met een query die afhankelijk van de Projecten tabel het juiste datumbereik teruggeeft. Maar hiervoor moet je a) de juiste kennis hebben van Power Query en b) de tijd hebben om het te bouwen.

Verder moet je nagaan of deze aanpassing het waard is. Strikt genomen is de frequentie van de update 1 X per jaar (mits je het bereik van de Agenda tabel telkens met 1 jaar verhoogt). Dat kun je prima handmatig doen.

Maar wat als iemand anders jouw model gaat gebruiken? Weet hij/zij dan wel hoe je de aanpassing moet doen? Je kunt een kleine werkinstructie schrijven. Maar je collega moet die instructie dan wel makkelijk kunnen vinden en erop gewezen worden dat er überhaupt één bestaat.

Er is ook een andere manier. Je gaat een ‘controle’ inbouwen in je model. Deze gaat vaststellen of het bereik van de Agenda tabel wel toereikend is. Zo niet, dan stelt de controle de gebruiker op de hoogte dat de Agenda tabel moet worden geüpdatet.

Datumbereik Controle Bouwen

Voeg een nieuw tabblad toe en hernoem het ‘Controles’. Voeg er een Power Pivot draaitabel aan toe en sleep het veld Date   de Agenda tabel in het waarden van de draaitabel. Je ziet nu een draaitabel met de ‘Telling van Date’. Pas de samenvatting van Date aan naar Max via het rechtermuisklikmenu:

Als het goed is zie je nu de laatste dag van 2021. Dat is de datum die we moeten controleren. Dat ga je doen via een formule. Maar eerst ga je de draaitabel converteren naar een Power Pivot formule.

Selecteer de draaitabel en ga via Draaitabel analyseren > OLAP > Converteren naar formules. Je zie dat de opmaak van je draaitabel nu is verdwenen. Ook zie je dat de kolomkop en het waarden bereik zijn vervangen voor zgn. KUBUS formules:

Je ziet dat de KUBUSWAARDE formule in cel B3 naar de cel erboven verwijst. Vervang deze verwijzing voor de formule uit B2. De formule in B2 kun je nu verwijderen:

Nu heb je in één cel de maximale datum uit de Agenda tabel uit het Power Pivot gegevensmodel. De laatste stap is om de controle te schrijven. Je wilt weten of de maximale datum uit Agenda groter of gelijk is aan de maximale Einddatum uit de Projecten tabel.

Pas de formule aan naar:

Voeg een naam aan de controle toe in de kolom ervoor, plaats er kolomkoppen boven en een beschrijving van wat er moet gebeuren als de controle faalt:

Om de controle helemaal af te maken kun je voorwaardelijke opmaak toevoegen aan de waarde van de controle (rood als de controle faalt; als de waarde ONWAAR is):

En als je helemaal ambitieus bent kun je de beschrijving van de te ondernemen actie ook dynamisch maken:

Laten we de controle controleren. Pas het bereik van de Agenda tabel weer aan naar 31-Dec-2020. Als het goed is zie je dat de controle nu ONWAAR teruggeeft:

De controle werkt, de actie is beschreven, maar de gebruiker wordt niet op de hoogte gesteld wanneer buiten het Controle tabblad. De allerlaatste stap is dus om een notificatie te bouwen in het dashboard zelf.

Gelukkig kun je dit met een simpele ALS formule. Voeg de volgende formule toe in cel B1 boven de titel van je dashboard en pas de tekstopmaak aan naar rood.

Om je notificatie te testen kun je het datumbereik van de Agenda tabel weer aanpassen in Power Pivot. En voilà, de notificatie is weg!

Je zult misschien denken: “Dit was wel heel omslachtig David. Had je me niet beter een dynamische agenda tabel kunnen maken?”. Daar zit wat in, maar bedenk dat het hebben van controle formules in je Excel model altijd goed van pas komt.

Nu weet je hoe je een controle kunt bouwen mét notificatie. Zo maak je een model een stuk gebruiksvriendelijker én is het beter te onderhouden. Als kers op de taart heb je ook geleerd hoe je een Power Pivot draaitabel naar formules kunt converteren, wat echt een gave truc is ?

Tijdlijn Invoegen

Tijd om de slicer in het dahboard te vervangen met een tijdlijn. De tijdlijn is alleen beschikbaar wanneer je een Agenda tabel in je gegevensmodel hebt. Gelukkig heb je dat al. Verwijder ten eerste de slicer van het dashboard. Let erop dat je de eventuele selectie filters verwijdert anders blijven ze ‘hangen’ in je draaitabel(len) en moet je ze daar uitschakelen.

Selecteer vervolgens je de draaitabel en ga via ‘Draaitabel analyseren’ naar ‘Tijdlijn invoegen’. Onderstaand venster verschijnt. Kies vervolgens voor het Date veld van je Agenda tabel.

Zoals je waarschijnlijk al hebt begrepen, kun je met een tijdlijn alleen filteren op een datum-veld. Anders dan een gewone slicer kan een tijdlijn alleen aaneengesloten items selecteren. Ook bevat de tijdlijn andere opties dan de gewone slicer.

Zo kun je bijvoorbeeld de tijdseenheid aanpassen. Je kunt kiezen tussen Jaar, Quarter, Maand en Dag. Jammer dat je niet voor Week kunt kiezen, maar maand is goed genoeg voor ons dashboard.

Plaats de tijdlijn tussen de titel en de draaitabel. Pas de hoogte van rij 3 aan zodat die er goed tussen past. Verplaats de draaitabel van cel D4 naar B4 zodat de titel, de tijdlijn en de draaitabel netjes onder elkaar staan:

De Stijl Aanpassen

De tijdlijn is nog iets te druk naar mijn mening. Selecteer de tijdlijn en ga via het menu ‘Tijdlijn’ helemaal naar rechts en schakel de Kop en het Tijdsniveau uit. Zo is de tijdlijn al wat netter. Merk overigens op dat de schuifbalk in onderstaande screenshot nog wel aan staat, maar wel is verdwenen als gevolg van de afmetingen die ik de tijdlijn heb gegeven.

Ik ben nog steeds van mening dat het beter kan; er zit nog steeds een lelijke rand om de tijdlijn. Om deze te verwijderen moet je de stijl van de tijdlijn aanpassen. In hetzelfde menu kun je de geselecteerde tijdlijnstijl aanpassen via het rechtermuisklikmenu.

Merk op dat de optie ‘Wijzigen’ is uitgeschakeld. De standaard stijlen van een tijdlijn kun je namelijk niet zomaar aanpassen, Dit geldt overigens ook voor slicers, draaitabellen en gewone tabellen. Wat je moet doen is de stijl dupliceren naar een ‘Aangepaste stijl’.

Kies dus voor Dupliceren. In het venster dat verschijnt selecteer je de bovenste regel ‘Volledige tijdlijn’ en druk je op Opmaak. Het standaard opmaak venster verschijnt dan. Hier ga je naar tabblad Rand en schakel je de randen helemaal uit.

Nadat je in alle open vensters op OK hebt gedrukt heeft je tijdlijn nog steeds die lelijke rand. Wat is hier aan de hand? Je hebt zojuist een nieuwe stijl gedefinieerd, alleen heeft Excel deze nieuwe stijl nog niet toegepast op de geselecteerde tijdlijn. Een puntje voor verbetering Microsoft…

Als je de ‘Aangepast stijl’ dit keer handmatig toepast zie je dat de rand weg is:

Indicators Toevoegen

Je projectoverzicht ziet er netjes uit. Je ziet welke projecten er in een zelf te selecteren periode allemaal actief zijn/waren. Je ziet welke taken er onder een project vallen en je kunt de status van de taak herleiden van de kleurcodering.

Hoewel de rode balken heel goed in het oog springen, kost het nog steeds moeite om te zien op welke taak deze betrekking heeft. Ook zie je niet in één oogopslag welke taken allemaal over de deadline zijn.

Dit ga je oplossen door rode indicator ‘lampjes’ voor de taakomschrijvingen te plaatsen met behulp van formules. Je gaat de indicator lampjes ook interactief maken, zodat de gebruiker gemakkelijk de onderliggende waarde kan aanpassen in de kolom Projecten[Afgerond] op tabblad Invoer.

Berekende Kolommen Invoegen

Voordat je de indicators kunt maken moet je de Projecten tabel verrijken met een aantal extra gegevens. Ten eerste moet je per taak afleiden of deze over de deadline is of niet. Voeg daarom een nieuwe kolom toe aan de tabel en noem die ‘Te laat’ met onderstaande formule:

Verder moet je een taak snel kunnen opzoeken in de Projecten tabel. En dat is momenteel nog niet zo makkelijk omdat er duplicaten aanwezig zijn in kolom [Taakbeschrijving]. Het is daarom handig om een unieke sleutel te berekenen. Voeg hiertoe een nieuwe kolom toe met de naam ‘ID’ en onderstaande formule:

Tot slot wil je straks in één keer naar de juiste cel springen in kolom Voltooid. Hiervoor heb je per regel het externe adres nodig. Voeg nogmaals een kolom toe, genaamd Adres, en gebruik onderstaande formule:

De Rapportindeling Aanpassen

Aangezien de sleutel een combinatie is van de projectnaam en de taakomschrijving, moet je deze ook in het dashboard voorhanden hebben. De compacte rapportindeling van de draaitabel leent zich hier niet goed voor; zowel de projectnaam als de taakomschrijving staan in dezelfde kolom. Het zou veel handiger zijn om beide in een aparte kolom te hebben.

Gelukkig is dat snel gepiept. Selecteer de draaitabel en ga via Ontwerpen naar Rapportindeling en kies voor Overzichtsweergave. Je draaitabel ziet er nu als volgt uit:

Vervolgens kun je de labels van het Project veld herhalen zodat projectnaam en taakbeschrijving naast elkaar staan. Selecteer cel B8 en ga via het rechtermuisklikmenu naar Veldinstellingen. In het venster dat zich opent ga je naar het tweede tabblad ‘Indeling & afdrukken’ en vink je ‘Itemlabels herhalen’ aan:

Nu de projectnaam en de taakomschrijving netjes naast elkaar staan kun je met behulp van de unieke sleutel alle relevante informatie ophalen uit de Projecten tabel. Voeg in kolom A de volgende formule toe. Zorg dat je de formule voldoende regels naar beneden kopieert. In principe zou 200 regels ruim voldoende moeten zijn.

De volgende stap is om op basis van deze unieke sleutel te kijken of een taak over de deadline is gegaan. Hiervoor voeg je een nieuwe kolom toe en gebruik je onderstaande formule:

Merk op dat ik hier gebruik maak van de functie X.ZOEKEN() in de plaats van VERT.ZOEKEN(). X.ZOEKEN() is één van de nieuwe functies die Microsoft afgelopen jaar heeft toegevoegd aan Excel. Het doet hetzelfde als VERT.ZOEKEN maar is veel flexibeler. Zo hoeft de kolom waarin je zoekt niet per se de eerste in het bereik te zijn, wat hier heel goed van pas komt.

Tot slot moet je nog één hulp kolom toevoegen. Hiermee haal je het adres op:

Nu is eindelijk tijd om alle puzzelstukjes aan elkaar te leggen. Je gaat een interactief indicator ‘lampje’ tonen voor iedere taak waarvan de deadline is verstreken. Dit doe je in twee stappen. Eerst voeg je de hyperlink toe, daarna maak je deze op als een indicator lampje.

Voeg weer een kolom toe en gebruik de volgende formule:

Je ziet dat de formule alleen een hyperlink teruggeeft wanneer een taak te laat is. Merk verder op dat het adres in de HYPERLINK() formule voorafgegaan wordt door een hekje ‘#’. Dit is belangrijk, anders werkt je link niet.

De HYPERLINK() formule heeft nog een tweede argument. Hiermee kun je de tekst van de link bepalen. Deze ga je gebruiken om een rood bolletje te tonen, zodat het net een indicator ‘lampje’ lijkt.

Hiervoor kun je mooi één van de nieuwe Windows icoontjes gebruiken. Met de sneltoetscombinatie Windows + ; (dus: Windowstoets + puntkomma) kun je gemakkelijk emoji’s en andere icoontjes vinden. Deze werken ook in formules, maar jammer genoeg niet in kleur.

Wanneer je kolom A:C verbergt en de kolom met de indicators wat smaller maakt en een rode tekstkleur geeft, dan begint het al aardig op een dashboard te lijken:

En als je op een indicator klikt, brengt ie je direct bij de waarde in de Projecten tabel, mocht je ‘m willen aanpassen. Handig toch? Helaas zijn de herhalende project labels wel erg lelijk. Gelukkig heb ik ook daar een fix voor.

Pas de kolombreedte van kolom E aan naar 0.01 pixels. Hiermee verberg je de kolom nét niet, waardoor de tekst van cellen waarvan de rechtsgelegen cel leeg is gewoon doorloopt. En laat dit nou net het geval zijn bij onze titel en de project titels…

Afsluitend

Gefeliciteerd! Je hebt alle stappen doorlopen van deze reeks. Het resultaat ervan zie je hieronder:

In deel één heb je de basis gelegd voor het visualiseren van projecten met gebruik van een draaitabel. In deel twee heb je dit overzicht verfijnd door de status van een taak/project een kleur toe te wijzen. En tot slot heb je de laatste hand gelegd aan de interface.

Het resultaat mag er zijn, al zeg ik het zelf. Je Excel projectenoverzicht is overzichtelijk, gebruiksvriendelijk en gemakkelijk te onderhouden.

Deze reeks heeft je hopelijk een idee gegeven wat er allemaal mogelijk is in Excel. En dit is pas het topje van de ijsberg.

Ik hoop dat je er wat van opgestoken hebt. Heb je vragen hierover, of over Excel dashboards en oplossingen in het algemeen? Voel je vrij om contact op te nemen met ExpertExcel.