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.

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.

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.

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!

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.

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

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.

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.

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.

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

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.

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

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.

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.

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 slicerlinks van de draaitabel, verberg de rasterlijnen, voeg nog een titel toe en je model is klaar voor gebruik!

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?