Retroplanning in Excel

 

 

Retroplanning met MS Excel


Voor kleine en middelgrote projecten is MS Exel een bruikbaar hulpmiddel. Voor de kleine projecten zullen we hierna een basiswerkblad ontwerpen. Daarna zullen we dit voor middelgrote projecten verder verfijnen en optimaliseren.

Basisplanning

 

Een planning moet in één oogopslag een antwoord kunnen geven op de 4 W’s (Wie, Wat, Waar en Wanneer).

Je komt dan als snel in Excel bij een layout die er als volgt uitziet:

Deze voorstelling lijkt wat op een Gantt grafiek - wat in projectplanning de meest gebruikelijke voorstelling vormt van activiteiten op een tijdslijn.

Je kunt dit bestand downloaden door hier te klikken. Het bevat 2 tabs, één waar bovenstaande automatisatie reeds ingebouws werd en één blanco tab die bedoeld is om te oefenen en zelf de automatisatie te bouwen.

 

Relatie Begin - Duur - Einde:

Eerst gaan we een aantal basiselementen inbouwen.
We beginnen met Begin (B), Duur (D) en Einde (E). Tussen deze 3 entiteiten bestaat de relatie B+D=E of E-D=B of E-B=D. Omdat onze kleinste eenheid hier 1 dag is moeten we een correctie met 1 eenheid doorvoeren en wordt dit respectievelijk B+D-1=E, E-D+1=B en E-B+1=D.
 

 

Hoe bouw je dit fragment automatisatie ?

 

 

Vergeet niet dat datum en tijd in MS Excel een getal is en men met deze grootheden dus rekenkundige bewerkingen kan doen. Vermits er een berekend resultaat is MOET één van deze entiteiten berekend worden! Doen we dit niet dan zal men vroeg of laat gegevens hebben die niet meer in overeenstemming zijn met elkaar (“redundantie leidt tot inconsistentie!”). Het gebruik van de ISLEEG() functie dient enkel voor een mooiere opmaak en is niet fundamenteel. Het zorgt er gewoon voor dat een berekende cel geen “-1” weergeeft als de andere cellen leeg zijn. De ALS() functie geeft een lege cel of een berekening naargelang het resultaat van de ISLEEG() functie.

 

De tijdsbalk dynamisch aanmaken

Voor de tijdsbalk bovenaan geven we gewoon de formule vorige cel+1 wat ons toelaat op heel eenvoudige wijze door het wijzigen van de eerste datum van de tijdsbalk de reeks automatisch aan te passen.

 

Automatisch inkleuren van de taakbalkjes

 
Het zou onefficiënt zijn zelf de taakbalkjes in te kleuren. Je riskeert al snel discrepanties tussen wat bij de kolommen als begin en einde ingevuld werd en wat je inkleurde, vooral als men achteraf nog iets aanpast. Excel biedt de mogelijkheid een formule aan te maken waarbij de uitkomst bepaalt of een bepaalde opmaak al of niet dient toegepast te worden. Die functionaliteit draagt de naam "voorwaardelijke opmaak". Hiervoor selecteren we eerst het op te maken celgebied en maken we gebruik van de voorwaardelijke opmaak (Lint/Start/Voorwaardelijke opmaak
 
 

Vervolgens kiezen we voor “nieuwe regel” met daarin de laatste keuze “Een formule gebruiken om te bepalen welke cellen worden opgemaakt”.

Het ingeven van een formule begint per definitie met het ingeven van een “=”-teken gevolgd door de formule die zal geëvalueerd worden. Afhankelijk van het resultaat zal de nog te definiëren opmaak al dan niet worden toegepast.

De formule die we hier zullen gebruiken zal nagaan of Begin en Einde uit de linkerkolommen binnen de data van de tijdsbalk vallen. Indien ja, dan wordt de opmaak toegepast. Indien neen, blijft de opmaak ongewijzigd. Voor een correcte weergave wordt ook getest of er wel een Begin is ingegeven (anders wordt mogelijk teveel ingekleurd)

Wij gaan testen of Begin groter of gelijk is aan de datum van de tijdsbalk en of Einde kleiner of gelijk is aan de datum van de tijdsbalk. Enkel indien aan beide voorwaarden voldaan is, wordt de opmaak toegepast. Vandaar ook het gebruik van de EN() functie. Bij de formule van de voorwaardelijke opmaak zal men merken dat er gewerkt wordt met absolute en relatieve adressering. De reden hiervoor is dat de cellen die moeten getest worden BUITEN het celgebied van de voorwaardelijke opmaak liggen. We moeten hierbij nagaan hoe bij het aflopen van de cellen in het op te maken celgebied de verwijzing naar de cellen buiten dit celgebied verloopt. Voor de verwijzingen starten we ALTIJD met de cel in de linkerbovenhoek van het op te maken celgebied als basis! (in ons voorbeeld is dit F3). Als we vanuit F3 naar beneden bewegen moet rij 2 van de tijdsbalk vast blijven  F$2. Als we vanuit F3 naar rechts bewegen moeten de kolommen C en E vast blijven  $C3 en $E3. 

Zo iets dus:

 

De formule wat meer in detail bekeken:

 

Formule: =EN(G$2>=$D3;G$2<=$F3;$D3<>"")
 

Betekent:
 

G$2>=$D3 : is de datum van de huidige kolom gelijk of later dan de begindatum ?
G$2<=$F3 is de datum van de huidige kolom voor of op de einddatum ?
$D3<>”” is de begindatum niet leeg ? De test op een ingevulde datum is nodig om “valse” inkleuringen te vermijden.

 

We combineren dan de 3 testen binnen een "EN()" functie. 

Enkel als de 3 testen waar zijn dan is het eindresultaat waar en kleuren we in:

EN (test1;test2;test3) -> dan inkleuren 

 

Hoe inkleuren ? Door binnen “voorwaardelijke opmaak, een formule gebruiken” vervolgens op de drukknop “Opmaak” te klikken kunnen we de voorwaardelijke

opmaak instellen door de gewenste celeigenschappen op te geven. Voor de Gantt grafiek beperken we ons normaal tot “Rand” en “Opvulling”. Rand laat toe om de betrokken cellen met een kader te omranden, Opvulling laat toe om de achtergrondkleur van de cellen aan te passen.
 

 

Hiermee hebben we een basisplanning van het type “quick and dirty” dat toch voldoet aan een aantal basisregels en bruikbaar is voor eerder eenmalige kleine projecten.