Tagarchief: Tekst

Voetbalcompetities

Excel, VBA (Visual Basic for Applications), , , , ,
Voorbeeldbestand: VoetbalComp.xlsmDownload

Je hoeft niet per se van voetballen te houden om dit artikel te kunnen waarderen.
Ik kreeg van Nico van der Meer een mooi Excel-systeem toegestuurd, dat hij graag met anderen wil delen: het verwerken van voetbaluitslagen tot overzichten en standen, inclusief consequenties voor nacompetities, Champions League etcetera.

In eerste instantie dacht ik: deze sheet bevat wel veel formules, daar zullen we snel wat fouten of mogelijke problemen uit kunnen halen.
Maar niets was minder waar; dus ik geef de werkmap door met maar een paar kleine aanpassingen (verbeteringen?).

Met het systeem van Nico kun je de hele competitie 2018-2019 plezier hebben; alles is al voorbereid, inclusief speeldata en zelfs tijden!
Maar, zoals uit het plaatje hierboven mag blijken, zijn dit soort competities behoorlijk onderhevig aan wijzigingen, dus voor volgend jaar zul je wel weer flink wat aanpassingen moeten doen.

Hieronder zal ik de werking van het systeem kort toelichten en ook een handreiking geven over de aanpassingen voor een volgende competitie-ronde.
Uiteraard zal ik ook de meest gebruikte Excel-functies en de gebruikte VBA-routines toelichten.

Voetbal-competitie

Voordat er zo’n mooi overzicht ontstaat moet er natuurlijk wat voorwerk verricht worden.
In het tabblad Info van het Voorbeeldbestand heeft Nico al diverse aanwijzingen gegeven.

Het systeem omvat 3 competities, Ere-, Eerste – en Tweede Divisie (de EersteDivisie heeft nu de naam KeukenKampioen).
In het tabblad Data liggen de gegevens van de competitieronde en van de teams per divisie vast. Ook kan daar aangegeven worden of de beveiliging (ter voorkoming van het overschrijven van formules) wel of niet moet worden aangezet.

Het tabblad Speelschema bevat per divisie een overzicht van alle rondes en de wedstrijden die daarbinnen gespeeld moeten worden; dit alles inclusief de betreffende datum en tijd. Deze gegevens zijn op internet terug te vinden; via de hyperlink boven iedere divisie zijn de relevante zaken te benaderen.

Klik rechts op een Hyperlink en dan Hyperlink bewerken om te zien hoe deze opgebouwd is.

LET OP om een hyperlink te bekijken moet de beveiliging van het tabblad verwijderd zijn. Dit kan via rechtsklikken op de tab onderaan of door in het tabblad Data aan te geven dat alle beveiligingen verwijderd moeten worden.

Wilt u met dit systeem de resultaten van een competitie volgen dan moeten op dit tabblad Speelschema de uitslagen handmatig ingevoerd worden (voor de Eredivisie in de kolommen C en D).

Resultaten en stand Eredivisie

In het tabblad Eredivisie van het Voorbeeldbestand worden de ingevulde wedstrijdresultaten automatisch verwerkt.

In cel G6 wordt bijvoorbeeld het aantal doelpunten opgehaald voor de thuisclub van de wedstrijd PSV – AZ Alkmaar mbv de formule
=ALS(ISLEEG(VERT.ZOEKEN(TEKST($B6;)&” – ” & TEKST($G$5;);SchemaED;2;0)); “”;VERT.ZOEKEN(TEKST($B6;)&” – “&TEKST($G$5;);SchemaED;2;0))

Via Verticaal Zoeken wordt de wedstrijd (B6, dus PSV, tegen G5, dus AZ) opgezocht in het bereik met de naam SchemaED. De inhoud van de 2e kolom wordt als resultaat teruggegeven. De derde parameter (een 0, nul) geeft aan dat een exacte zoekactie naar de wedstrijdnaam moet worden uitgevoerd.
Als de zoekactie niets oplevert (er is nog niets ingevuld) dan komt er in dit schema ook niets te staan (de dubbele aanhalingstekens), anders wordt het resultaat van de zoekactie in de cel geplaatst.

NB1 de namen van de teams worden door een spatie, een min-teken en opnieuw een spatie van elkaar gescheiden. Bij de opvoer in Speelschema moet dit ook exact zo zijn gedaan.

NB2 Nico heeft de team-namen niet rechtstreeks aan elkaar gekoppeld, maar heeft de functie Tekst gebruikt. Waarschijnlijk om er zeker van te zijn dat Excel deze betreffende cellen zeker als tekst zal zien. Naar mijn idee overbodig.

De overige formules onder en naast het schema wijzen zichzelf.

Ook de standentabel wordt automatisch ingevuld.
Via Vergelijken wordt gekeken op welke positie het betreffende team in de vorige tabel staat en dan wordt via Index het gewenste aantal winst-punten opgehaald (uit en thuis opgeteld).

De formules in de overige kolommen zijn vergelijkbaar of ‘rechttoe-rechtaan’.

In kolom BW staat een code, die gebruikt wordt om de teams in de juiste volgorde te kunnen sorteren en de Plaats in de stand te bepalen.
Wanneer namelijk nieuwe resultaten worden toegevoegd in Speelschema, dan worden wel alle berekeningen direct uitgevoerd en ook de Plaats wordt bepaald, maar de volgorde in de standenlijst verandert niet. Wil je ook de volgorde juist hebben, klik dan op de knop Standen bijwerken. De betreffende VBA-routine (zie hierna) zal dan de sortering uitvoeren.

In cel BW6 staat de formule =WAARDE( TEKST(BS6;”000″)&TEKST(100+BT6;”000″)&TEKST(BU6;”000″)&TEKST(100-BN6;”00″)&TEKST(AANTAL.ALS($BM$6:$BM$23;”>=”&BM6);”00″))

Ook hier wordt de functie Tekst gebruikt; in dit geval om zeker te weten dat het betreffende getal altijd met exact hetzelfde aantal cijfers wordt weergegeven.
Met de functie Waarde wordt van de resulterende tekst weer een getal gemaakt.

Nico heeft in een opmerking bij Code netjes vermeld hoe de code is opgebouwd.

NB bij de Eredivisie zijn na 3 speelrondes al geen gelijke standen meer; bij de KK-divisie na 2 speelrondes nog wel.

In kolom BL wordt de plaats van het team in de stand bepaald: =RANG(BW6;$BW$6:$BW$23)
Met behulp van Voorwaardelijke opmaak krijgen bepaalde Plaatsen ook nog een kleurcode; dit is dus onafhankelijk van de sortering.

Overige divisies en nacompetitie

Bij de KK-divisie heeft Nico ook nog overzichten gemaakt ten behoeve van de bepaling van de periode-kampioenen. Zover ik nu heb kunnen zien heeft hij alle lastige afwijkingen en problemen daarmee uitstekend opgelost.

Wat de nacompetitie betreft: de maker van het systeem is nog niet helemaal zeker over de juistheid daarvan.

“De nacompetitie is voor mij nog experimenteel gezien de complexe regels.
Ik had niet verwacht, dat ik tot iets zou kunnen komen, maar al werkende daaraan ontstond er toch iets”.

Nieuwe competitieronde

Wilt u dit systeem ook een volgend seizoen gebruiken, dat moet er nogal wat aangepast worden:

  1. in het tabblad Data van het Voorbeeldbestand het seizoen aanpassen
  2. ook in Data de teams in de drie divisies aanpassen (hoeven niet per se gesorteerd te zijn)
  3. op het tabblad Data de teams van de Eredivisie selecteren en Ctrl-C (kopiëren) drukken; in het tabblad Eredivisie op de cel BM6 rechts klikken en Plakken speciaal/Waarden kiezen
  4. dezelfde werkwijze hanteren voor de teams van de eerste en tweede divisie
    LET OP bij de eerste divisie moeten de teams vijf keer geplakt worden.
  5. in het tabblad Speelschema alle uitslagen wissen en de wedstrijden die gespeeld zullen gaan worden overschrijven.
    LET OP zorg dat de namen van de clubs exact gelijk zijn aan die in het tabblad Data en dat de 2 ploegen gescheiden worden door een spatie, min-teken en nog een spatie

VBA

Met behulp van de knoppen Standen bijwerken worden bijbehorende VBA-routines uitgevoerd.
Voor de Eredivisie ziet die er als volgt uit:
Het bereik met de naam StandEredivisie wordt gesorteerd op kolom BW  en wel in Aflopende volgorde (grootste getal eerst) en het bereik heeft geen Kop.

Alle andere sorteringen gaan op dezelfde manier. Bij de KK-divisie worden 5 sorteringen achter elkaar uitgevoerd; dus voor alle schema’s van het betreffende tabblad.

In het Excel-object ThisWorkbook staat de volgende routine:

De For-Next-loop loopt alle tabbladen langs en afhankelijk van de inhoud van de cel met de naam BevJN (deze staat in het tabblad Data) wordt de beveiliging aan of uit gezet (zonder wachtwoord).

Application.ScreenUpdating zorgt er voor, dat het scherm niet gaat ‘flikkeren’ bij het selecteren van een tabblad.

 

Functies grafisch weergeven

Excel, , , ,
Voorbeeldbestand: FunctiesGraf.xlsxDownload

Excel is inzetbaar op veel gebieden; ook binnen het (wiskunde-)onderwijs is het goed bruikbaar.
Ik moest daar deze week aan denken, toen ik bedacht hoe ik 20 jaar geleden met iemand tijdens bijlessen heb zitten worstelen om hem het gevoel bij te brengen hoe 1e- en 2e-graads-functies er in grafiekvorm uitzien.

Eerstegraadsfunctie

Zoals we allemaal (?) weten is de algemene vorm van een eerstegraadsfunctie zoals hiernaast weergegeven.
Door voor a en b waarden in te vullen krijg je een specifieke functie.

In het Voorbeeldbestand in het tabblad 1e graads is de algemene functie ingevoerd door in de menutab Invoegen in het blok Symbolen de optie Vergelijking te kiezen.

De cellen C5 en C6 (naast de teksten a en b) hebben een naam gekregen:

  1. kies de menutab Formules
  2. dan in het blok Gedefinieerde namen de optie Naam definiëren
  3. voer een Naam in, in dit geval _a
  4. kies een Bereik. Standaard is de naam geldig voor de hele Werkmap, maar in dit geval moet het bereik beperkt worden tot het werkblad 1e graads
  5. pas zo nodig de verwijzing nog aan en klik op OK

NB Excel accepteert niet alle letters als Naam; ik heb er in dit geval voor gekozen om alle namen door een _ (underscore) te laten voorafgaan.

In cel B8 wordt de functie tekstueel opgebouwd:
=”ƒ(x) = ” & ALS(_a=0;””;ALS(_a=1;”x”;ALS(_a=-1;”-x”;_a&”x”))) & ALS(_b=0;””;ALS(_b<0;” – “;” + “) & ABS(_b))

De diverse tekstblokken worden m.b.v. het &-teken aan elkaar gekoppeld.

In het eerste blok is de ƒ ingevoerd via Invoegen/Symbool. Kies daar allereerst rechtsonder de optie ASCII (decimaal) en blader dan door de mogelijkheden.
Wiskundige tekens kunnen ook op de volgende site gevonden worden:  Lijst_van_wiskundige_symbolen; gebruik daar kopiëren en plak dan in Excel.

Met de diverse ALS-functies wordt de opmaak geregeld, zodat er bijvoorbeeld geen 0x of 1x in het resultaat komt te staan.

De ABS-functie levert de absolute waarde van een getal; het resultaat is dan altijd groter of gelijk aan 0.

Om de grafiek te kunnen tekenen hebben we een tabel nodig met de bij de functie horende x,y-waarden. In het tabblad 1e graads van het Voorbeeldbestand worden 100 combinaties berekend in de cellen vanaf B32. In cel C32 komt dan de formule =_a*B32+_b

NB Om het effect van veranderingen in a of b goed te kunnen zien zijn de assen ‘vastgezet’: ze lopen allebei van -5 tot +5.

Bij  eerstegraadsfuncties zijn de snijpunten met de assen altijd belangrijk. Die worden in de cellen C10 en C11 berekend met de formules =”(0 , “&_b&”)”, respectievelijk =ALS(_a=0;”geen”;”(“&-_b/_a&” , 0)”)

Omdat het resultaat van de laatste formule vaak geen geheel getal oplevert, is het inzichtelijker om het resultaat zo nodig als een breuk weer te geven (zie cel C15):
=ALS(_a=0;”geen”;”(“&TEKST(-_b/_a;”# ??/??”)&”, 0)”)

M.b.v. de functie TEKST wordt een opmaak meegegeven aan het resultaat -b/a: eventueel een geheel getal (#) en als het nodig is een breuk (met maximaal 2 cijfers in de teller en noemer).

Om alle overbodige spaties te verwijderen, kan de functie SUBSTITUEREN worden gebruikt (zie bijvoorbeeld cel C19 in het tabblad 1e graads).

In de grafiek is ook de functie weergegeven. Dit is als volgt gedaan:

  1. selecteer de cel met de functie en kies kopiëren (Ctrl-C)
  2. klik ergens anders op het werkblad met de rechter muistoets
  3. kies Plakken speciaal …
  4. en in het vervolgscherm de laatste optie van Andere plakopties (Gekoppelde afbeelding)

De inhoud van de oorspronkelijke cel wordt dan dynamisch in een grafisch object weergegeven. Dit object kan via slepen overal in de sheet geplaatst worden, dus ook in de grafiek.

Tweedegraadsfunctie

De opbouw van het tabblad 2e graads van het Voorbeeldbestand is vergelijkbaar met bovenstaande.

Het bepalen van de eventuele snijpunten met de x-as is echter wat ingewikkelder. Weet u nog?

Maar dit levert alleen maar een resultaat op als het getal onder de wortel (de discriminant) niet kleiner is dan nul; dus dat wordt eerste even gecontroleerd.

In C14 staat daarom de volgende formule:
=ALS(Oplos?=”geen”;”geen”;”(“&(-_b-WORTEL(Discr))/(2*_a)&” , 0)”)

Derdegraadsfunctie

In het tabblad 3e graads van het Voorbeeldbestand wordt de grafiek van een derdegraadsfunctie weergegeven. Het bepalen van de snijpunten met de x-as wordt nu wat lastiger; dat valt buiten de scope van dit artikel.

 

Samengestelde interest

Excel, ,
Voorbeeldbestand: Rente.xlsxDownload

Samengestelde interest of rente op rente: een onderwerp, dat regelmatig weer vragen oproept. Zijn daar in Excel nu wel of niet functies voor?

De aanleiding om hier over te schrijven was de volgende vraag:

“Volgens Microsoft-informatie bestaat er geen functie voor samengestelde interest in Excel.
Vroeger, al weer 50 jaar geleden, heb ik op de middelbare school nog financiële rekenkunde gekregen.
Daarbij gebruikte je de diverse rentetabellen met de diverse percentages om een bepaald bedrag uit te kunnen rekenen. Dat waren de kleine en grote A- en S-tabellen, als ik het mij nog goed herinner.”

In dit artikel zal ik laten zien, dat Microsoft zichzelf iets tekort doet. Het hangt van de situatie af. Maar zo gauw het wat ingewikkelder wordt, zul je toch zelf een systeem in Excel moeten bouwen, een soort (complexe) rentetabellen.

Rente op rente, variant 1a

Voor samengestelde interest zijn allerlei varianten denkbaar.
De meest eenvoudige is de situatie, dat u een bedrag op een spaarrekening zet waar jaarlijks een rente wordt bijgeschreven (ja, dat bestaat nog!). U laat na het eerste jaar alles op de rekening staan en krijgt in het tweede jaar dus ook rente vergoed over de rente van het eerste jaar (vandaar de naam Rente op rente).

Nou dit is zo simpel, dat we daar ook eigenlijk geen functie voor nodig hebben.
Op het einde van het eerste jaar wordt er rente bijgeschreven; uw tegoed is dan Bedrag * (1 + Rente%).
Op het einde van het tweede jaar wordt dit resultaat weer vermenigvuldigd met (1 + Rente%). Het mag duidelijk zijn, dat op het einde van de looptijd de Toekomstige Waarde gelijk is aan het gestorte Bedrag maal (1 + Rente%) tot de macht Looptijd.

In het Voorbeeldbestand in het tabblad TW1 staat deze berekening van de Toekomstige Waarde in cel C8:
=Bedrag*(1+Rente)^Looptijd

In de formule staan geen celverwijzingen; de betreffende cellen C4 t/m C6 hebben een naam gekregen waardoor formules die daar naar verwijzen veel beter leesbaar zijn.

NB1 de eenvoudigste manier om een cel een naam te geven is door deze cel te selecteren en dan in het naamvak linksboven de gewenste naam in te tikken.

NB2 cellen met een groene achtergrond zijn invoer-cellen.

NB3 de vraag bovenaan het tabblad is dynamisch; in cel B1 staat de formule
=”Wat is “&Bedrag&” euro over “&Looptijd&” jaar waard bij “&TEKST(Rente;”0,0%”)&” rente?”
M.b.v. het &-teken worden teksten tussen aanhalingstekens samengevoegd met namen van cellen. Om te zorgen dat Rente een mooie opmaak krijgt is gebruik gemaakt van de functie TEKST.

NB4 misschien wat verwarrend: in dit geval wordt het woord Rente gebruikt waar een rentepercentage wordt bedoeld.

Rente op rente, variant 1b

Maar de berekening kan ook met de standaard Excel-functie TW uitgevoerd worden. De benodigde formule staat in cel C9 van het tabblad TW1 van het Voorbeeldbestand:
=TW(Rente;Looptijd;0;-Bedrag)

Zoals u ziet kent deze functie 4 parameters; de betekenis van de eerste 2 moge duidelijk zijn. Met de derde parameter kunt u aangeven of er nog vaste periodieke betalingen plaats vinden (in dit voorbeeld niet; hier komen we later nog op terug). De vierde parameter geeft aan wat de Huidige Waarde is; in dit voorbeeld ‘geven’ we  dit bedrag aan de bank, vandaar het minteken.

LET OP1 als het minteken wordt weggelaten wordt het resultaat van de formule negatief.

LET OP2 als na het invoeren van de TW-functie op Enter wordt gedrukt, zal Excel het resultaat van de formule weergeven. Maar dat niet alleen: ook wordt de opmaak van de cel gewijzigd in Valuta.

Rente op rente, variant 1c

In de vorige voorbeelden is gewerkt met een jaarlijkse bijschrijving van de rente. In het tabblad TW2 van het Voorbeeldbestand kunt u zien wat het effect is van de verrekening van een maandrente (jaarrente gedeeld door 12). Afhankelijk van de keuze in cel C6 (J of M is mogelijk) wordt de JrMndFactor in cel C7 gelijk aan 1 of 12.

NB als er met maandrente wordt gerekend moet de looptijd niet in jaren maar in maanden worden uitgedrukt.

Rente op rente, variant 2

Maar wat als er niet één storting plaats vindt, maar ieder jaar (aan het begin) een overboeking plaats vindt?
Over het eerste bedrag wordt het aantal keren, dat de Looptijd is, rente uitgekeerd; over de tweede storting 1 keer minder etc. Met behulp van een wiskundige afleiding kan ook hier de TW uitgedrukt worden in een formule.

NB voor de liefhebbers: TW = Bedrag*(1+r)^n + Bedrag*(1+r)^n-1 + .. + Bedrag*(1+r).
Vermenigvuldig deze vergelijking met (1+r) en trek ze van elkaar af. Allerlei termen vallen dan tegen elkaar weg. Nog wat reshuffelen en klaar is Kees.

In het Voorbeeldbestand is deze formule in cel C8 van het tabblad TW3 opgenomen.

Maar ook hier kunnen we de standaard Excel-functie TW gebruiken; zie cel C9 in het tabblad TW3:
=TW(Rente;Looptijd;-Bedrag;0;1)

Omdat het hier periodieke betalingen/stortingen betreft, wordt het bedrag in de derde parameter geplaatst (met een min-teken). De vierde parameter (de huidige of startwaarde) is in dit geval 0.

NB de functie TW kent een optionele vijfde parameter (Type). Als deze 1 is dan betreft het een storting aan het begin van iedere periode (prenumerando); wordt hier 0 ingevuld of wordt deze weggelaten dan stelt het een storting op het einde van de periodes weer (postnumerando).

Bij alle bovenstaande methodes is het nadeel, dat er telkens slechts het resultaat na één looptijd zichtbaar is.

In dit soort situaties is het vaak veel prettiger om een overzicht/systeem op te zetten, waar het verloop in de tijd zichtbaar is.

Op het tabblad TW3 in het Voorbeeldbestand staat zo’n systeem:

  1. het startbedrag van het eerste jaar is het stortingsbedrag uit cel C4: =Bedrag
  2. in de kolom daarnaast wordt de rente over jaar 1 berekend: =[@StartBedr]*Rente
    dus het startbedrag uit dezelfde regel (vandaar de @) maal de rente uit cel C5
  3. in de volgende kolom staat het resultaat op het einde van het jaar: =[@StartBedr]+[@Rente]
  4. het startbedrag voor jaar 2 wordt dan het eindresultaat van jaar 1 plus de nieuwe storting: =H5+Bedrag
  5. aangezien we alles in de vorm van een Excel-tabel hebben gezet, worden de formules in de rente- en eind-kolom automatisch ingevuld
  6. de formule uit punt 4 kan naar beneden gekopieerd worden

Bij wijziging van het stortingsbedrag en/of rente-percentage zien we nu in één oogopslag wat de consequentie daarvan is in de loop van de tijd.

Rente op rente, variant 3a

Maar wat als de stortingen en/of het rentepercentage nu niet meer constant zijn in de loop van de tijd?

In het tabblad TW4 van het Voorbeeldbestand is het systeem uit het tabblad TW3 iets aangepast: er zijn kolommen voor het stortingsbedrag en het rentepercentage bij gekomen.

NB in deze 2 nieuwe kolommen staan alleen in de eerste rij ‘harde’ waarden (namelijk 100, respectievelijk 3%); daaronder staan altijd verwijzingen naar de regel daarboven. Een wijziging in één van de jaren wordt dan automatisch naar de toekomst doorgevoerd.

Rente op rente, variant 3b

Op het tabblad TW4 staat nog een tweede systeem.
We weten natuurlijk niet exact welk bedrag we in de toekomst kunnen sparen en wat de rente zal zijn. Maar we kunnen wel schattingen maken: ieder jaar denken we 10% meer te kunnen sparen (in het voorbeeld staat deze schatting nog op 0%) en we verwachten (hopen?), dat de rente ieder jaar 2% zal stijgen (relatief dus, geen harde 2%. In dat geval hadden we gezegd, dat het percentage met 2 procent-punt zou stijgen).

Welke van bovenstaande varianten de voorkeur heeft, is natuurlijk afhankelijk van uw eigen wensen. Betreft het slechts een eenmalige berekening of is er veel flexibiliteit en inzicht gewenst etcetera.

 

Van globaal naar detail en vv

Excel, , ,
Voorbeeldbestand: GlobaalDetail.xlsxDownload

Mensen zijn verschillend en ook hun informatiewensen zijn verschillend.

Sommige mensen willen globale informatie zien, anderen zoveel mogelijk details.
Voor afdelingen die verantwoordelijk zijn voor rapportages blijft dit altijd een lastig fenomeen. Gelukkig bieden digitale dashboards tegenwoordig een uitkomst: gebruikers van zo’n dashboard kunnen vaak zelf de selectie van voor hen interessante onderwerpen en ook het gewenste detailniveau instellen.

In dit artikel een uitwerking hoe je een grafiek in een dashboard ‘dynamisch’ kunt maken (zie ook een eerder artikel over dynamische grafieken).

Voorbeelddata

Ik ben eigenaar van een energiecentrale (in de vorm van zonnepanelen) en uiteraard wil ik wel volgen wat de energieproductie in de loop van de tijd is.

Voor een rapportage is het dag-niveau wat gedetailleerd, daarom maken we een weekrapportage. Maar ook dan zien we door de hoeveelheid cijfers vaak door de bomen het bos niet meer.

In het tabblad Data van het Voorbeeldbestand vindt u onze weekproductie vanaf week 27 in 2011. De gegevens zijn opgeslagen in een Excel-tabel met de naam tblZon. In de derde kolom staat een formule die het jaar en de week combineert.
In cel C3: =[@Jaar]*100+[@Week]
Ofwel neem het getal uit de Jaar-kolom uit dezelfde regel (daar zorgt de @ voor), vermenigvuldig dat met 100 en tel dan de Week uit die regel er bij op.

Om hierna te weten hoeveel weken er gevuld zijn, wordt dit in een afzonderlijke cel bepaald: in cel I2 staat de formule =AANTAL(tblZon[JrWeek]). Deze turft het aantal numerieke velden in de kolom JrWeek van de tabel tblZon.

De cel I2 heeft de naam MaxAant gekregen; daar zullen we hierna regelmatig naar verwijzen.

NB we hadden in C3 ook de formule =[@Jaar]&[@Week] kunnen gebruiken, maar het resultaat is dan een tekst; dat is voor rapportage-doeleinden minder flexibel.

Grafiek-1

Een grafiek (zie het tabblad Data van het Voorbeeldbestand) laat het verloop in de tijd wel goed zien. De pieken en dalen hieronder komen overeen met de zomers en winters van de afgelopen jaren. In 6,5 jaar heeft de betreffende set van 3 panelen bijna 4.000 kWh opgeleverd.

Maar aangezien de mede-eigenaar van deze energie-centrale meer in details is geïnteresseerd zou het mooi zijn als we de resultaten per jaar afzonderlijk zouden kunnen bekijken.

NB1 de resultaten per week staan uitgezet volgens de linker- ofwel primaire as, de cumulatieve opbrengst  volgens de secundaire as.
Klik rechts op een gegevensreeks en kies dan de Reeks opmaken om te wisselen van as.

NB2 zorg bij het gebruik van 2 assen dat de schaalverdelingen logisch met elkaar overeen komen. In dit geval is de maximale weekproductie op 25 gezet, terwijl het maximum van van de rechter-as op 5.000 is vastgezet.
In dit geval is door de kleur van de cijfers op de assen duidelijk gemaakt welke gegevensreeks bij welke as hoort.

Grafiek-2

Om de details beter te kunnen beoordelen heb ik een nieuwe grafiek gemaakt, die slechts een selectie van het totaal laat zien.

Om snel de jaren te kunnen vergelijken kun je hierbij aangeven met welke week je wilt beginnen (zie tabblad Calc1 van het Voorbeeldbestand).

In cel H2 vul je het volgnummer van de gewenste eerste week in; alle overige cellen bevatten formules en mogen dus niet gewijzigd worden.

Op basis van de invoer in cel H2 (deze heeft de naam Start gekregen) wordt een Excel-tabel met de naam tblZonGed gevuld; er is ruimte gecreëerd voor 55 regels.
In de eerste kolom staat een volgnummer voor de te tonen week.
Daarnaast staat in de kolom JrWeek de formule:
=ALS(Start+[@Nr]-1>MaxAant;””;
VERSCHUIVING(tblZon[[#Kopteksten];[JrWeek]];Start+[@Nr]-1;0))

Ofwel: als in een regel de week boven het maximaal aantal uitkomt, dan wordt de cel leeg (de dubbele aanhalingstekens).
Anders wordt via de functie VERSCHUIVING het weeknummer opgehaald uit de tabel tblZon op het tabblad Data; gerekend vanaf de koptekst JrWeek in die tabel zoveel regels naar beneden als door Start (cel H2) plus het volgnummer uit de betreffende regel wordt aangegeven (nog even met 1 corrigeren).

NB zie voor uitleg over de functie Verschuiving onder andere het artikel Dynamische grafieken.

In de kolommen kWh en Cum staat een vergelijkbare formule; alleen wordt de cel niet leeg gemaakt als de week boven het maximum uitkomt, maar gelijk aan de functie NB(). Die laatste zorgt er voor, dat de grafiek in dat geval geen ‘vreemde’ daling vertoont.

In de kolommen H en J worden nog enkele zaken opgehaald en klaar gezet voor de ‘aankleding’ van de grafiek:

  • in cel J3: =VERSCHUIVING(tblZonGed[[#Kopteksten];[JrWeek]];Eind-Start+1;0)
    op basis van de Start- en Eind-week wordt de naam van de week opgehaald uit de tabel van het tabblad Calc1
  • in cel H4: =”(van week “&TEKST(J2;”0000-00″)&” t/m “&TEKST(J3;”0000-00″)&”)”
    diverse teksten worden aan elkaar gekoppeld door middel van het &-teken; de begin- en eind-week worden daarbij opgemaakt met een opmaakcode, zodat er een streepje tussen het jaar en de week komt
  • in cel H5: =”Opbrengst zonnepanelen  set 1″&TEKEN(13)&Tekst
    ook hier worden teksten gekoppeld, in dit geval gescheiden gescheiden door een code 13, die er voor zorgt dat het vervolg op een nieuwe regel komt

Maak een titel aan in de grafiek, klik daarin dubbel en tik dan in de Formulebalk in =Titel

Grafiek-3

In een dashboard is het niet fraai als gebruikers ergens iets moeten intypen. Daarom gaan we er voor zorgen, dat op een meer intuïtieve manier de grafiek kan worden aangepast.

Allereerst maken we weer een set aan met basis-gegevens (zie tabblad Calc2 in het Voorbeeldbestand). We kunnen daar de begin-week opgeven en het aantal te tonen weken.

In plaats van het aanmaken van een hulptabel zoals bij Grafiek-2 leggen we alle voor de grafiek benodigde gegevens vast in zelf-gedefinieerde namen:

  1. kies in de menu-tab Formules in het blok Gedefinieerde namen de optie Naam definiëren
  2. de eerste naam wordt WkCalc2
  3. en de bijbehorende verwijzing:
    =VERSCHUIVING(tblZon[[#Kopteksten];[JrWeek]];Calc2!Start;0;Aantal)
    De laatste parameter (Aantal) zorgt er voor, dat het resultaat niet 1 cel is, maar een heel bereik.
  4. op dezelfde manier voegen we nog de naam DataCalc2 toe met de verwijzing
    =VERSCHUIVING(tblZon[[#Kopteksten];[ kWh]];Calc2!Start;0;Aantal)
  5. en dan nog de naam CumCalc2 met
    =VERSCHUIVING(tblZon[[#Kopteksten];[ Cum]];Calc2!Start;0;Aantal)
  6. bij het aanmaken van de grafiek worden nu als verwijzingen naar de benodigde reeksen deze nieuwe namen gebruikt.
    LET OP: plaats ook een verwijzing naar een tabblad of werkmap voor de naam (inclusief uitroepteken), bijvoorbeeld voor de x-waarden =Calc2!WkCalc2

Door nu onder de grafiek schuifbalken te plaatsen, die gekoppeld zijn aan de Start– en Aantal-cellen van het tabblad Calc2 ontstaat er een handige, interactieve grafiek (zie het tabblad DashBoard van het Voorbeeldbestand).

  1. kies in de menu-tab Ontwikkelaars in het blok Besturingselementen de optie Invoegen.
  2. klik dan binnen het blok Formulierbesturingselementen op de optie Schuifbalk
  3. geef met de cursor in de sheet aan waar de balk moet komen.
  4. daarna kan na rechts-klikken op de schuifbalk het besturingselement opgemaakt worden
  5. maak een koppeling met de cel die gewijzigd moet worden door de schuifbalk
  6. vul een minimum- en een maximumwaarde in
  7. vul de 2 wijzigingswaarden in. De eerste is de stap waarmee de waarde in de cel moet wijzigen als op de pijltjes aan de zijkant geklikt wordt; de tweede als in de balk links of rechts van het schuifje wordt geklikt

 

Gantt-grafiek

Excel, , , ,
Voorbeeldbestand: Gantt.xlsxDownload

Henry Laurence Gantt ontwikkelde de Gantt-grafiek rond 1910.
In zijn werk als werktuigkundig ingenieur, managementconsultant en bedrijfsadviseur werd de Gantt-grafiek gebruikt als een visueel hulpmiddel om de planning en voortgang van een project te laten zien. In die tijd werd dit gezien als een opzienbarende innovatie.

Voor (complexe) Gantt-grafieken worden veelal specialistische (project-)programma’s gebruikt, maar voor het gewone werk kunnen we met Excel ook al aardig vooruit.

Aan de hand van een simpele project-opzet zullen we 2 mogelijkheden voor een Gantt-grafiek bekijken.

Project-data

Hiernaast ziet u de gegevens van een fictief project (zie het tabblad ProjData in het Voorbeeldbestand).
Per stap en substap leggen we een volgnummer vast, samen met een omschrijving, een begindatum en het aantal geplande dagen dat de stap duurt (alleen op het laagste niveau).

Uiteraard beïnvloeden de stappen elkaar. In het voorbeeld zijn dat de volgende zaken:

  1. de startdatum van het project leggen we vast in cel D3
  2. in cel D4 komt de echte startdatum van de eerste hoofd-stap, de Brainstorm-sessies. We gebruiken de functie WEEKDAG om te controleren of D3 niet in een weekend valt. Als D3 een zondag is dan is het resultaat van WEEKDAG een 1, bij maandag een 2 etc.
  3. er zijn 2 brainstormsessies, die parallel plaats vinden. De startdatums daarvan (cellen D5 en D6) zijn gelijk aan D4.
  4. In E5 en E6 ligt vast hoeveel dagen deze sessies in beslag nemen.
  5. de einddatum van deze 2 substappen wordt bepaald met de functie WERKDAG. Zo staat in F5 de formule =WERKDAG(D5;E5-1)
    Deze functie bepaalt de einddatum door bij de startdatum (D5) een aantal dagen op te tellen (E5); de functie telt dagen van het weekend daarbij niet mee.
    Omdat op de startdatum ook al gewerkt wordt, verlagen we het aantal te werken dagen met 1.
  6. De afronding van de brainstorm vindt plaats op de eerste werkdag nadat de laatste sessie klaar is; in cel D7 staat daarom de formule =WERKDAG(MAX(F5:F6);1)
  7. De ontwerpfase begint als de brainstorm voorbij is; eerst het Functioneel Ontwerp, daarna het Technisch Ontwerp en vervolgens een Eindoverleg om alle details door te nemen.
    De opzet van de cellen D8:D11 mag dan ook duidelijk zijn.
  8. De Bouw start na de afronding van het Ontwerp (cellen D12 en D13).
  9. Deel2 van de bouw kan pas beginnen na afronding van Deel1 (cel D14: =WERKDAG(F13;1)).
  10. Wel kan de bouw van de Schil parallel aan de andere bouwactiviteiten plaats vinden (cel D15 is gelijk aan D12).
  11. De daadwerkelijke oplevering vindt plaats nadat de laatste bouwactiviteit is afgerond; in D16 staat de formule =WERKDAG(MAX(F13:F15);1)

NB de functie WERKDAG kent nog een derde parameter, Vakantiedagen. Leg ergens in uw werkmap een overzicht vast van die dagen, dat er voor het project niet gewerkt kan worden en voeg deze reeks als derde parameter toe aan alle WERKDAG-functies (zie een voorbeeld op het tabblad ProjData).

Gantt-grafiek 1

Op basis van de gegevens uit het tabblad ProjData van het Voorbeeldbestand gaan we nu een grafiek maken. Wel hebben we dan nog 2 hulpkolommen nodig:

  • in kolom G komt de startdatum van de betreffende stap minus 1 (waarom dat zullen we hieronder zien; datums zijn voor Excel gewoon getallen dus in G3 komt de formule =D3-1)
  • in kolom H komt het verschil in kalenderdagen tussen de start- en einddatum van iedere stap (in H3 staat dan de formule =F3-D3+1; met 1 gecorrigeerd omdat de startdatum ook meedoet)

Dan kunnen we de grafiek gaan opbouwen:

  1. selecteer de cellen G3:G16
  2. kies in de menutab Invoegen in het blok Grafieken de optie Staaf
  3. en dan bij 2D-staaf de 2e optie (Gestapelde staaf). Het eerste gedeelte van de grafiek is klaar.
  4. selecteer de cellen H3:H16 en Kopieer deze (Ctrl-C)
  5. klik ergens in de grafiek van punt 3 en Plak (Ctrl-V)
  6. de legenda kan weg: klik er op en druk op Delete
  7. de basis-as moet nog aangepast worden:
    * klik met de rechtermuisknop ergens in de grafiek
    * kies de optie Gegevens selecteren
    * klik op de button Bewerken onder Horizontale aslabels
    * selecteer de cellen B4:C16 en klik op OK
    * nog een keer OK om bij de grafiek terug te komen
  8. eigenlijk willen we alleen de tweede staaf zien; de eerste geeft het verloop tot aan de startdatum weer (vandaar de minus 1 in kolom G):
    * klik rechts op één van de staven, die ‘weg’ moeten
    * kies de optie Gegevensreeks opmaken
    * kies onder Opvulling de optie Geen opvulling en dan de button Sluiten
  9. Nog één aanpassing. De volgorde van de stappen is (voor ons) niet logisch:
    * klik rechts op de Categorie-as
    * kies de optie As opmaken
    * vink de optie Categorieën in omgekeerde volgorde aan en klik op de button Sluiten

In het tabblad Gantt1 ziet u het resultaat van bovenstaande exercitie (met nog enkele aanpassingen, die de grafiek overzichtelijker maken).

NB kijk wat er gebeurt, als je in cel D3 van het tabblad ProjData een andere startdatum voor het project invoert.

Gantt-grafiek 2

Deze Gantt-grafiek is op een heel andere manier tot stand gekomen (het is geen echte grafiek, maar een inkleuring van Excel-cellen; zie tabblad Gantt2 van het Voorbeeldbestand):

  1. het ‘blauwe’ blok zijn allemaal verwijzingen naar het tabblad ProjData; bijvoorbeeld in cel B6 staat de formule =ProjData!B4
  2. in cel H4 staat de formule =D5-5, zodat de ‘grafiek’ niet exact op de startdatum begint, maar 5 dagen eerder.
  3. in I4 staat de volgende dag =H4+1; deze formule is zover naar rechts gekopieerd, dat de einddatum van het project in de rij voorkomt.
  4. in H3 hebben we de Maand zichtbaar gemaakt met de formule =Tekst(H4,”mmm”) en deze formule is ook naar rechts gekopieerd
  5. in regel 2 bepalen we op een vergelijkbare manier het Jaar

Alle overige effecten (verticale lijnen, gekleurde cellen en het al dat niet zichtbaar maken van het jaar en de maand) zijn met behulp van Voorwaardelijke opmaak geïmplementeerd (LET OP deze optie is niet direct beschikbaar; om ongewilde wijzigingen te voorkomen is  het werkblad Gantt2 beveiligd. Verwijder de beveiliging via de menutab Controleren en dan de optie Beveiliging blad opheffen (geen wachtwoord vereist). Vergeet niet daarna de beveiliging weer aan te brengen!):

  1. als de tekst in regel 3 verandert (H$3<>G$3), dan komt er een rand aan de linkerkant van de cellen in de regels 2 t/m 18
  2. als de inhoud van een cel in de 2e en 3e regel gelijk is aan de cel links daarvan (H2=G2) dan krijgt de tekst in die cel een witte kleur en is dus niet meer zichtbaar
  3. de cellen in de regels 4 t/m 18 krijgen een rode linker- en rechterrand als de inhoud van de 4e regel gelijk is aan de datum van vandaag (H$4=VANDAAG()).
  4. ook krijgen de cellen in de regels 4 t/m 18 een groenige achtergrond als de datum in de 4e regel een weekend voorstelt (WEEKDAG(H$4;2)>5; de parameter 2 geeft aan dat de week op een maandag begint)
  5. de cellen in de regels 5 t/m 18 krijgen een bruinige achtergrond als de datum in de 4e regel groter of gelijk is aan de startdatum in kolom D EN de datum in de 4e regel kleiner of gelijk is aan de datum in de kolom E.

Welke methode heeft de voorkeur?

Mijn persoonlijke voorkeur gaat naar de 2e methode uit. Hierbij is ieder facet van de ‘grafiek’ in te stellen.

Dit is meteen ook het grootste nadeel van deze methode. Dus heb je snel een grafiek nodig, gebruik dan methode 1.

Laat in een reactie hieronder weten, welke methode u prefereert.