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.

In het vorige blog heb je de eerste versie van een projectplanning dashboard gemaakt. Dat dashboard bevatte een visualisatie die antwoord gaf op de vraag: wanner start een project en hoelang duurt het?

In dit blog ga je de visualisatie verrijken 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?

Hieronder zie je wat je gaat maken. Het Excel bestand kun je hier downloaden: Project Dashboard

Gegevensopslag

Nu je de status van een project wilt weergeven, heb je per project extra informatie nodig in de gegevensbron – je moet immers weten of een project voltooid is of niet. Voeg daarom een nieuwe kolom toe aan de tabel op het eerste tabblad.

Geef kolom de volgende naam: [Voltooid] en kopieer onderstaande regels in de tabel.

Projectnaam	Taakbeschrijving	Startdatum	Einddatum	Voltooid
Project 1	Taak 1	31-Oct-20	07-Nov-20	WAAR
Project 1	Taak 2	01-Nov-20	05-Nov-20	WAAR
Project 1	Taak 3	03-Nov-20	15-Nov-20	WAAR
Project 2	Taak 1	05-Nov-20	12-Nov-20	ONWAAR
Project 2	Taak 2	07-Nov-20	14-Nov-20	ONWAAR
Project 3	Taak 1	07-Nov-20	18-Nov-20	ONWAAR
Project 3	Taak 2	07-Nov-20	15-Nov-20	ONWAAR
Project 3	Taak 3	08-Nov-20	13-Nov-20	ONWAAR
Project 3	Taak 4	09-Nov-20	13-Nov-20	ONWAAR
Project 4	Taak 1	11-Nov-20	24-Nov-20	ONWAAR
Project 4	Taak 2	11-Nov-20	20-Nov-20	ONWAAR
Project 5	Taak 1	12-Nov-20	16-Nov-20	ONWAAR
Project 5	Taak 2	13-Nov-20	20-Nov-20	ONWAAR
Project 5	Taak 3	13-Nov-20	18-Nov-20	ONWAAR

Je ziet dat de nieuwe deze alleen twee mogelijke waarden bevat:

• WAAR = een project is voltooid
• ONWAAR = een project is nog niet voltooid

Dit datatype is beter bekend als logische waarde. Omdat een logische waarde slechts twee waarden kent, WAAR of ONWAAR, fungeert het als een aan/uit schakelaar. En dit komt in heel veel scenario’s goed van pas, zoals gegevensvalidatie.

Gegevensvalidatie toepassen

Om een correcte invoer van de nieuwe kolom te garanderen kun je gegevensvalidatie aan de kolom toevoegen. Selecteer eerst alle regels in kolom [Voltooid] en ga dan via ‘Gegevens’ naar ‘Gegevensvalidatie’:

Dit brengt je in het Gegevensvalidatiescherm waar je een de regels voor data invoer kunt bepalen.

Kies onder ‘Toestaan:’ voor de laatste optie ‘Aangepast’. Nu kun je je eigen formule gebruiken voor de validatie. Let op: de formule die je schrijft moet altijd een logische waarde teruggeven:

• WAAR = validatie geslaagd
• ONWAAR = validatie mislukt

De formule die je gebruikt is =ISLOGISCH(F5). Deze formule inspecteert of de waarde in de geselecteerde cel (in dit geval F5) een logische waarde bevat.

Druk op OK om de validatieregel toe te passen.

Wanneer je nu een waarde invoert anders dan een logische waarde, dan toont Excel een waarschuwing:

Een keuzelijst maken

Hoewel deze validatieregel goed werkt is het niet zo gebruiksvriendelijk. Veel gebruikers weten niet wat logische waarden zijn, hoewel ze deze onbewust vaak toepassen. Een betere optie is om de gebruiker een keuzelijst te geven zoals je hieronder kunt zien:

Om een keuzelijst te maken moet je eerst de items van je keuzelijst in een bereik plaatsen. Pas vervolgens de gegevensvalidatie aan naar de optie ‘Lijst’ en verwijs je naar het bereik waarin de logische waarden hebt gezet:

Voordat je doorgaat is het een goed idee om te controleren of de nieuwe kolom goed is ingeladen in het datamodel. Open Power Pivot en druk op de knop Vernieuwen als je de kolom niet ziet:

Berekeningen

In het vorige deel heb je een aantal basismetingen aan het Power Pivot datamodel toegevoegd:

• Dag:= MIN( Agenda[Date] )
• Start:= MIN( Projecten[Startdatum] )
• Eind:= MAX( Projecten[Einddatum] )
• Taak: = IF( [Dag] >= [Start] && [Dag] <= [Eind], 1, BLANK() )

De laatste meting gebruikte je in de draaitabel om met voorwaardelijke opmaak de opvulkleur van de cel bepalen zodat aaneengesloten cellen een horizontale balk vormden.

Om de status van een project te visualiseren borduur je voort op hetzelfde principe.

Per status ga je een apart cijfer berekenen, en dat cijfer gebruik je in de voorwaardelijke opmaak om de status telkens een andere kleur te geven. Op die manier wordt het voor de gebruiker in één oogopslag duidelijk hoe de zaken ervoor staan:

De doelstelling is dus om een nummering te maken van de verschillende statussen, zoals hieronder weergegeven:

1. Gedaan: dagen van project die in het verleden liggen
2. Te doen: dagen in het project die in de toekomst liggen
3. Te laat: dagen in het verleden sinds de einddatum van het project liggen

Die nummering gebruik je later om te coderen naar de volgende kleuren:

1. Gedaan = Groen
2. Te doen = Lichtgroen
3. Te laat = Donkerrood

Uiteindelijk komt je overzicht er dus zo uit te zien:

Extra basis berekeningen

Om de status per project te kunnen bepalen heb je extra berekeningen nodig. Je moet kunnen bepalen of een project voltooid is of niet. Ook moet je weten of een bepaalde dag in de draaitabel tot het verleden of tot de toekomst behoort.

Neem de volgende basismetingen over. Volg daarbij weer 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
5. NIEUW: Controleer of de formule een juist resultaat teruggeeft

Met behulp van COUNTROWS() kun je het aantal regels bepalen van een bepaalde tabel. Deze functie wordt in [Aantal taken] toegepast op de Projecten tabel. Door de filtercontext ‘ziet’ iedere cel in de draaitabel een andere set regels uit de Projecten tabel en retourneert deze meting dus telkens een ander resultaat.

De meting [Aantal voltooide taken] maakt gebruik van een hele handige Power Pivot functie: FILTER(). Hiermee kun je extra filters toepassen op een tabel binnen de huidige filtercontext. FILTER() kijkt voor iedere rij in de huidige filtercontext of de expressie in het tweede argument WAAR is, en retourneert een tabel met alleen díe regels.

Om de werking van [Aantal taken] en [Aantal voltooide taken] beter te begrijpen kun je ze naast elkaar in een aparte draaitabel zetten:

Je ziet dat Project 1 uit drie taken bestaat. Twee daarvan zijn al voltooid. De filtercontext van het subtotaal van Project 1 bestaat uit dus drie regels. In de draaitabel zie je deze getallen in de kolommen waar de metingen staan, [Aantal taken] en [Aantal voltooide taken].

Vergelijk dit met Project 3. Deze bestaat uit vier taken. In de draaitabel zie je dat dit aantal overeenkomt voor [Aantal taken]. Voor [Aantal voltooide taken] zie je daarentegen een lege cel. Dit komt omdat geen van de taken voltooid is.

De laatste meting, [Voltooid_], bepaalt of een taak, of een geheel project, voltooid is. Dit wordt berekend door het aantal taken te vergelijken met het aantal voltooide taken.

In principe is dit een rare oplossing. In Excel had je gewoon de logische EN() formule kunnen gebruiken. Helaas werkt de AND() formule in Power Pivot niet zo flexibel als zijn tegenhanger in Excel. Deze verwacht namelijk twee logische waarden, ingegeven als twee aparte argumenten, en vergelijkt of beide WAAR zijn.

Omdat een project uit meer dat twee taken kan bestaan is deze functie dus niet bruikbaar. Je moet dus een andere manier gebruiken om hetzelfde resultaat te berekenen: door het aantal taken te vergelijken met het aantal voltooide taken.

De status berekenen

Nu de basismetingen klaar staan is het tijd voor het echte werk. Je gaat een formule schrijven die per dag een statusnummer teruggeeft. Hieronder staan de verschillende statussen die we willen bepalen voor onvoltooide taken:

1. Gedaan: dag binnen planning en in het verleden
2. Te doen: dag binnen de planning en in de toekomst
3. Te laat: dag na de planning en in het verleden

Je start met twee hulp metingen om de laatste twee statussen te bepalen:

Beide berekeningen resulteren in een logische waarde en maken gebruik van de speciale EN-operator in Power Pivot, de dubbele ‘&&’. Hiermee kun je toetsen of meerdere vergelijkingen allemaal WAAR zijn.

De status per taak

Met bovenstaande hulp metingen kun je een eenvoudige berekening maken om per dag de status van een bepaalde taak te bepalen:

Deze formule maakt gebruik van SWITCH(), een handige functie die van pas komt wanneer je meerdere ALS-DAN constructies nodig hebt. De functie is overigens ook als Excel formule te gebruiken.

Het eerste argument van SWITCH() heeft een logische expressie – een formule/of waarde die een WAAR/ONWAAR teruggeeft. In dit geval gebruik je simpelweg de TRUE() functie.

De volgende argumenten worden altijd in een waarde-resultaat paar opgegeven. Eerst geef je de waarde op die je wilt vergelijken met de expressie in het eerste argument, en daarna geef je het resultaat dat je de formule wilt laten retourneren als de vergelijking WAAR is.

Dit patroon van waarde-resultaat argumenten kun je net zo vaak herhalen totdat je een laatste waarde wilt opgeven voor de categorie ANDERS – vergelijkbaar met het derde argument van de ALS() formule.

Wanneer je de meting in de draaitabel vervangt met [StatusTaak], dan zie je dat deze de juiste nummering teruggeeft per taak:

Echter, wanneer je een project inklapt, dan gaat het nog niet helemaal goed:

Merk op dat er taken in Project 1 zitten die al over de deadline zijn, maar hierboven niet als een status 3 worden weergegeven.

De status per project

Om de status op zowel project, – als op taak-niveau te bepalen heb je een allerlaatste meting nodig die de maximale waarde van [StatusTaak] berekent. Helaas kun je hiervoor niet de MAX() functie gebruiken. Deze werkt alleen voor kolomverwijzingen en niet voor zelf gedefinieerde metingen:

De X-factor

Gelukkig biedt Power Pivot hier een alternatief voor: de zogenaamde X-functies. Dit is een groep functies wiens functienaam allemaal op X eindigt. Een paar voorbeelden hiervan zijn:

• SUMX
• AVERAGEX
• MINX
• MAXX
• …

De X-functies werken allemaal op dezelfde manier.

1. Het eerste argument is altijd een tabel verwijzing. Dit mag ook een berekende tabel zijn, bijvoorbeeld als resultaat van een FILTER() functie.
2. Het tweede argument is een formule. Hierin kun je als het ware een berekende kolom toevoegen aan de tabel uit het eerste argument, uitsluitend met gebruik van de kolommen die daarin voorhanden zijn.
3. De X-functie berekent voor iedere regel in de tabel in het eerste argument, de uitkomst van de formule in het tweede argument. Vervolgens aggregeert deze alle berekende waarden tot de daadwerkelijke uitkomst van de X-formule.

Het mooie van FILTER() is dat je ook een andere meting als tweede argument mag gebruiken. In dat geval worden alle relaties in het datamodel automatisch doorgerekend. Dit is een zeer krachtige feature van Power Pivot.

Met deze nieuwe kennis kun je de uiteindelijke meting schrijven:

Wanneer je deze meting in de draaitabel plaatst zie je inderdaad dat de waarde op project-niveau de juiste waarden teruggeeft.

Visualisatie

Tijd om de nummering van [Status] te coderen naar de juiste kleuren. 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 waarde op 1 en kies je in het tweede vakje de laatste optie uit de keuzelijst: Aangepaste indeling…

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

Herhaal dit voor de overige statussen: 2 = lichtgroen en 3 = donkerrood.

Het resultaat mag er zijn! In één oogopslag zie je welke projecten over de deadline zijn en waar je je bevindt ten opzichte van de planning.

Ook wanneer je een project inklapt:

Samenvattend

Je hebt de planning tool verder verrijkt met een kleurcodering voor de status waarin een project zich bevindt.

• Je hebt geleerd hoe je gegevensvalidatie toepast zodat gebruikers alleen de juiste waarden mogen invoeren
• Hoe je de gebruiker alleen gevalideerde waarden uit een keuzelijst kunt laten selecteren
• Wat logische waarden zijn en hoe je ze kunt toepassen binnen formules, validatieregels en conditionele opmaak
• Hoe de COUNTOWS(), FILTER(), SWITCH() en MAXX() functies werken in PowerPivot.

In het volgende en laatste deel van deze serie gaan je het dashboard verder opmaken zodat het een presentatiewaardig dashboard wordt, in de plaats van alleen een slicer en een simpele (maar ontzettend gave) draaitabel.

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?