Categorie archief: Excel

Gegevenstabel

Excel, , , ,

wa1Ik had al lang op mijn lijstje staan om iets te schrijven over Gegevens-tabellen in Excel. Een handig hulpmiddel bij scenario-analyses.
Een artikel op de site van Chandoo was aanleiding om daar daadwerkelijk invulling aan te geven!

Even verwarring wegnemen.
In Excel kennen we twee soorten tabellen: Tabellen en Gegevenstabellen. Helder? Vóór versie 2010 werden deze tabellen Lijsten, respectievelijk Tabellen genoemd.
Laten we de nieuwere terminologie maar hanteren: in Excel is een Tabel een verzameling bij elkaar horende cellen, die door de gebruiker worden gevuld (zie ook de  artikelen Kunst en Excel en Tabellen deel 2); een Gegevenstabel daarentegen wordt door Excel automatisch ingevuld.
Cryptisch? Lees hieronder verder.

Gegevenstabel met 1 variabele

Om de werking van een Gegevenstabel uit te kunnen leggen hebben we een voorbeeld nodig. In het Voorbeeldbestand heb ik een aangepaste versie van het Chandoo-voorbeeld gebruikt:

  1. Sara wil een kopieer-bedrijfje beginnen en maakt een bedrijfsanalyse in Excel
  2. wa2Ze kan copiers leasen met een capaciteit van 100.000 kopieën per jaar voor een bedrag van € 5.000 per maand.
  3. Een ruimte heeft ze ook al op het oog voor € 1.000 per maand.
  4. De kostprijs van een kopie is 2 cent.
  5. Marktonderzoek heeft uitgewezen, dat klanten 11 cent voor een kopie over hebben.
  6. Hoeveel kopietjes moeten er dagelijks gemaakt worden om break-even te spelen; uitgaande van 50 werkweken van 5 dagen?

In het Voorbeeldbestand op het tabblad WA1 zijn deze aannames in het blok Input verwerkt.

wa3In datzelfde tabblad staat ook een blok Berekeningen:

  1. eerst het aantal kopieën: in cel G4 staat de formule =C8*C9
  2. dan moeten we weten hoeveel copiers Sara daarvoor moet leasen: het aantal kopieën gedeeld door de capaciteit van een copier. Dit moet een heel getal zijn dus in cel G5 komt de formule =AFRONDEN.NAAR.BOVEN(G4/C5;0).
    De tweede parameter (0) geeft aan dat het resultaat geen decimalen mag hebben.
  3. De formules voor de kosten, opbrengsten en de winst mogen verder geen verrassingen meer met zich mee brengen.
  4. De 3 cellen in regel 16 hebben een voorwaardelijke opmaak gekregen, zodat direct duidelijk is of er sprake is van winst of verlies. 500 kopieën per dag zijn duidelijk niet voldoende om onder de gehanteerde aannames  winst te maken.

Door nu het aantal kopieën per dag in cel C9 te wijzigen kunnen we proberen het break-even-point voor dit bedrijfje te vinden.

En juist dit is het moment, dat een Gegevenstabel zijn kracht kan bewijzen:

  1. wa4plaats ergens in een lege kolom alle aantallen, die doorgerekend moeten worden (bv 100 t/m 1000)
  2. één regel hoger in de kolom rechts daarnaast plaatsen we een verwijzing naar de cel met de winst/verliescijfers (of rechtstreeks de formule =G14-G11)
  3. selecteer dan alle cellen met aantallen, inclusief de lege cel  daarboven en alle cellen daarnaast (in het voorbeeld hiernaast dus de cellen J7 tot en met K26.
    Tip selecteer cel J7 en druk op Ctrl-A
  4. wa5kies binnen de menu-tab Gegevens in het blok Hulpmiddelen voor gegevens de optie Wat-als-analyse en daarbinnen de optie Gegevenstabel
  5. we hebben een 1-dimensionale gegevenstabel: alleen in de kolom staan waardes die door Excel moeten worden doorgerekend. In het vervolgscherm wa6vullen we dan ook alleen de kolominvoer in; we verwijzen naar de cel met het aantal kopieën (cel C9).
  6. Klik OK en Excel vult alle cellen in kolom K met de winst, die bij het betreffende aantal hoort!

NB1 als je een waarde in kolom J wijzigt, zal ook het resultaat in kolom K worden aangepast

NB2 we hebben nu alleen een formule in kolom K gezet; in een Gegevenstabel met 1 variabele kunnen echter meerdere resultaten naast elkaar berekend worden.

NB3 Excel heeft een heel speciale matrix-formule in de sheet gezet (let op de {} rond de formules in kolom K). Een consequentie daarvan is, dat je niet zomaar meer cellen/regels kunt weghalen of toevoegen. Zorg dus bij het maken van een gegevenstabel dat je ruim voldoende invoercellen hebt. Moet je later iets toevoegen dan zul je de bestaande gegevenstabel moeten weghalen en een nieuwe tabel creëren.

Tot en met 1000 exemplaren per dag wordt dit bedrijf niet winstgevend. Tot en met 400 kopietjes neemt het verlies af maar daarna schiet die weer omhoog (er moet een 2e copier geleased worden). Tussen 400 en 800 loopt het verlies weer terug; na de hik bij 800 geldt hetzelfde verhaal.

wa8wa7Om het overzicht wat flexibeler te maken zijn in het Voorbeeldbestand 2 cellen opgenomen (K4 en K5) waarmee het startaantal en de stapgrootte aangepast kunnen worden.

Vullen we daar 500 resp. 100 in, dan krijgen we direct het overzicht zoals hiernaast.
Bij 1.200 kopietjes per dag speelt Sara quitte, maar daarboven gaat het weer mis (er moet nog een copier bij!). Pas rond 2.000 exemplaren begint er een stabiele winst te ontstaan.

Gegevenstabel met 2 variabelen

wa9Maar het model sluit niet goed aan bij de realiteit: wanneer het aantal kopietjes per dag ineens groter wordt kan Sara natuurlijk niet (direct) over meer of nieuwe copiers beschikken; met de leverancier moeten van tevoren goede afspraken gemaakt worden. Voor het aantal gewenste copiers moet een inschatting gemaakt worden. Die raming nemen we in de input op.

In het tabblad WA2 van het Voorbeeldbestand is die wijziging doorgevoerd.
Ook is bij de berekening daar de formule voor het aantal kopieën aangepast; hier komt het minimum van (het aantal dagen * aantal kopieën) en (aantal copiers * capaciteit): =MIN(C9*C10;C6*C5)

Ons winst-model is nu afhankelijk van 2 variabelen: het aantal copiers en het aantal kopieën per dag.
Dus willen we een scenario-analyse doen dan zal onze gegevenstabel ook 2-dimensionaal moeten zijn:

  1. plaats ergens in een lege kolom alle aantallen kopieën, die doorgerekend moeten worden (bv 100 t/m 1000)
  2. één regel hoger in de kolommen rechts daarnaast plaatsen we de aantallen copiers, die we willen doorrekenen
  3. wa10in de cel links van de aantallen copiers (en dus net boven het aantal kopieën) komt weer een verwijzing naar de cel met de winst/verliescijfers
  4. selecteer dan de cel met de formule en het hele blok cellen met links en boven de aantallen (in het voorbeeld hiernaast dus de cellen K8 tot en met R27.
  5. kies binnen de menu-tab Gegevens in het blok Hulpmiddelen voor gegevens de optie Wat-als-analyse en daarbinnen de optie Gegevenstabel
  6. we hebben nu een 2-dimensionale gegevenstabel: in de rij staan de waardes voor het aantal copiers en in de kolom het aantal kopieën, waarvoor de resultaten door Excel moeten worden doorgerekend. In het vervolgscherm wa11vullen we dan ook allebei de invoercellen in
  7. Klik OK en Excel vult alle cellen in de gegevenstabel in.

Uit de tabel volgt dat het bedrijfje pas levensvatbaar gaat worden bij 4 kopiers en dat Sara moet proberen dan 1600 kopieën per dag te produceren.

Risicoanalyse mbv Gegevenstabel

Ambitieus als Sara is, gaat ze voor 5 copiers: “Het lukt me wel om gemiddeld 2000 kopietjes per dag aan de man te brengen! Mijn winst verdubbelt dan tov 4 copiers.”

Een vriend zegt haar, dat ze dan wel een risico-analyse moet uitvoeren. Exact 2000 kopieën per dag is niet reëel: “Ik denk dat het aantal normaal verdeeld is met een gemiddelde van 2000 en een standaard-deviatie van 100.”

wa12In het tabblad WA3 van het Voorbeeldbestand is het model uitgebreid met een kans dat het betreffende aantal kopieën, op basis van het gemiddelde en SD , gehaald zal worden. Daarvoor gebruiken we in cel J11 de formule
=NORM.VERD.N(K11;$K$4;$K$5;ONWAAR)

Ofwel hoe groot is de kans dat er 1550 (cel K11) kopieën worden gemaakt onder de aanname dat de verdeling van het aantal Normaal Verdeeld is met een gemiddelde van 2000 (cel K4) en een standaard-deviatie van 100 (cel K5)? De echte kans, niet een cumulatieve (dus de laatste parameter is Onwaar).

Cel K10 bevat nu geen verwijzing naar de Winst maar naar de Winst * Kans.

Onder de gegevenstabel tellen we de, met de kansen,  gewogen winsten per kolom op. Die delen we door de som van de gebruikte kansen en krijgen dan de gemiddelde winst per kolom. De verwachte winst voor Sara wordt dan ongeveer 10% lager.

Multi-variabele Gegevenstabel

wa13Sara heeft het idee, dat ook de huurprijs nog bespreekbaar is en wil in de analyse hier rekening mee houden. Het model krijgt dan een derde input-variabele. Maar de Excel-gegevenstabel kan maar 2 dimensies aan.

Geen nood:

  1. zorg dat alle combinaties van de variabelen (dat kunnen er dus ook 10 zijn!), die doorgerekend moeten worden in een scenario-tabel zijn opgenomen
  2. wa14wijzig de input-tabel zodanig dat de input-variabelen afhankelijk zijn van het scenarionummer. Dat kan met Verticaal Zoeken in de scenariotabel (in het voorbeeld op het tabblad WA4 is dat B25:F37).
  3. maak een 1-dimensionale Gegevenstabel, waarbij in de eerste kolom de scenarionummers staan. In de kolomkoppen daarnaast maken we dan verwijzingen naar de scenario-tabel (zie hieronder de formule in cel K5) en naar de bijbehorende winst.
  4. wa15het is niet fraai, dat het resultaat van die verwijzingen altijd zichtbaar is: wijzig de opmaak van die kopjes zodanig, dat de tekstkleur gelijk is aan de achtergrondkleur.

Bel-grafiek of -diagram

Excel, VBA (Visual Basic for Applications), , , , ,

bel1In het artikel Eerbetoon aan Rosling is gebruik gemaakt van zogenaamde bel-diagrammen, ook vaak bel-grafiek genoemd.
Met behulp van dit soort grafieken is het mogelijk om 3 dimensies weer te geven: via de x- en y-as en door de grootte van de bellen.

Het artikel was aanleiding voor een vraag, die er op neer kwam of er ook nog een vierde dimensie mogelijk was door de kleur van de bellen te variëren.
Met de hand is dit mogelijk maar er is geen standaard-optie, die bij wijzigingen in de sheet de kleuren automatisch zal aanpassen (wat wel gebeurt met de grootte van de bellen).

Kleuren in Excel

Voordat we verder gaan met een mogelijke oplossing voor het probleem van Sander moeten we eerst iets meer weten over de codering van de kleuren in Excel.

Alle kleuren worden opgebouwd door een combinatie van Rood, Groen en Blauw (de zogenaamde RGB-codering). Ieder van die 3 basis-kleuren kan in 256 stappen worden toegevoegd (lopend van 0 tot 255).
bel2Zwart is dan opgebouwd uit 0 delen van iedere kleur, terwijl wit ontstaat door het mengen van alle drie de kleuren met een intensiteit van 255. Door alleen Rood en Groen te mengen (met een intensiteit van 255) ontstaat geel.

Door de blauw-code met 65536 te vermenigvuldigen, de groen-code met 256 en deze 2 dan op te tellen met de rood-code krijgen we een decimale kleurcode.
In het schema hierboven en op het tabblad Param van het Voorbeeldbestand staan enkele voorbeelden.

De decimale codes zullen we hierna gaan gebruiken.

Probleem

Als je een bellengrafiek maakt krijgen de bellen standaard allemaal dezelfde kleur. Deze kunnen gewijzigd worden door één voor één de bellen te selecteren (klik op een bel, dan wordt de hele serie geselecteerd; klik nogmaals op de bel dan wordt de selectie beperkt tot de betreffende bel) en dan via rechts-klikken de opmaak aan te passen.

Maar de vraag is: kan dit geautomatiseerd, waarbij de kleur afhankelijk is van de inhoud van bepaalde Excel-cellen?

Hieronder volgt een oplossing waarbij gebruik wordt gemaakt van VBA; een zelf-geschreven routine controleert van iedere bel wat de corresponderende kleurcode moet zijn en past die aan. Wel moet de routine na iedere wijziging handmatig aangeroepen worden; daar hebben we dan weer een button voor gecreëerd.

Oplossing

bel3Zoals voor ieder bel-diagram hebben we naast waarden voor de x- en y-as ook waarden nodig die de grootte van de bellen zullen bepalen.

NB1 een bel-grafiek is een speciale vorm van een spreiding- of xy-grafiek en kan dus op de assen alleen maar met getallen werken, geen teksten.

NB2 we kunnen niet exact de grootte van de bellen bepalen; Excel bepaalt zelf de grootte relatief ten opzichte van  elkaar.

In het tabblad Data van het Voorbeeldbestand is dit tabelletje opgenomen en is een bel-grafiek gemaakt.

Maar nu komt de vraag van Sander: kunnen de bellen een eigen kleur krijgen afhankelijk van het aantal keren, dat een combinatie van x en y voorkomt. Hij zou graag zien, dat een bel rood is als het aantal 1 tot 5 is, geel bij 5 tot 10 en groen bij 10 of meer.

Daarom eerst even een hulptabel opgezet (zie tabblad Param in het Voorbeeldbestand):

bel4

  1. een kolom voor de ondergrens voor een bepaalde kleur
  2. een kolom met de decimale code voor de gewenste kleur (zie hierboven)
  3. de 8 cellen zijn in een Excel-tabel geplaatst (via Invoegen/Tabel) en deze tabel heeft de naam tbKleur gekregen.

bel5Aan de gegevens heb ik een vierde kolom toegevoegd met een fictief aantal en nog een kolom die uit Param afleidt welke kleurcode de bel moet krijgen. De gegevens zijn in een Excel-tabel opgenomen met de naam tbData.

De formule in de kolom Kleur ziet er als volgt uit:
=VERT.ZOEKEN([@Aantal];tbKleur;2;WAAR)

Ofwel: neem de waarde uit de corresponderende kolom Aantal, zoek in de Excel-tabel tbKleur deze waarde op en geeft de waarde uit de 2e kolom terug (de kleurcode). We zoeken geen exacte match, maar een ‘benadering’ (de grootste waarde die nog voldoet); vandaar WAAR als laatste parameter in de functie VERT.ZOEKEN.

Alles staat nu klaar om de bellen de juiste kleurcodes te geven.

VBA-routine

Zoals hiervoor aangegeven, kent Excel geen automatische koppeling tussen de kleur van de bellen en de waarde in bepaalde cellen. Dus zullen we het zelf moeten doen:

bel6

  1. in de eerste regel geven we ons programma (subroutine) een naam: BelKleur
  2. in de 2e en 3e regel zorgen we er voor dat de variabelen pts en x netjes worden gedefinieerd (mbv Dim): in de eerste variabele gaan we punten bewaren, in de tweede gehele getallen.
  3.  in de 4e regel vullen we de variabele pts met de punten (Points) uit de eerste serie (SeriesCollection(1)) van de grafiek (Chart) met de naam grBel (ChartObjects(“grBel”)) in het actieve Excel-tabblad (ActiveSheet)
  4. dan komt er een For-Next-loop waarbij x loopt van 1 tot het aantal bellen in de grafiek (pts.Count). Alles tussen For en Next wordt dus net zo vaak herhaald als er bellen zijn.
  5. regel 6 en 7 horen bij elkaar (door de Underscore en een spatie op het einde van regel 6).
    De opmaak (Format) van punt x (pts(x)) wordt ingesteld; de bel wordt gevuld (Fill) met een kleurcode (ForeColor.RGB) gelijk aan de inhoud van de cel x+1 (de kop telt ook mee, vandaar +1) uit de kolom Kleur van de tabel tbData uit de actieve sheet (ActiveSheet)

Bellen kleuren

Iedere keer als er iets aan de gegevens wordt gewijzigd (nieuwe regels toegevoegd, aantallen aangepast) zal bovenstaande routine moeten worden uitgevoerd.

Dat kan op verschillende manieren:

  1. klik op de button Bellen kleuren op het tabblad Data van het Voorbeeldbestand
  2. kies op de menu-tab Ontwikkelaars de optie Macro’s, selecteer de routine BelKleur en klik op Uitvoeren
  3. Druk op Alt-F8, selecteer de routine BelKleur en klik op Uitvoeren

Zonnepanelen

Excel, , , , , , ,

ZonnepanelenIn het kader van duurzaamheid hebben wij ook zonnepanelen op het dak liggen. Vijf jaar geleden hebben we 2 (kleine) sets laten plaatsen; na dit lustrum zullen we eens kijken hoe we de resultaten inzichtelijk kunnen maken.

Een mooie definitie in dit kader:

“Duurzame ontwikkeling is de ontwikkeling die aansluit op de behoeften van het heden zonder het vermogen van de toekomstige generaties om in hun eigen behoeften te voorzien in gevaar te brengen”

Dataverzameling

zon1Een beetje jaloers kijk ik wel eens naar andere mensen, die recentelijk panelen hebben aangeschaft: de huidige stroom-omvormers communiceren via wifi met centrale servers waar alle gegevens over stroomproductie worden verzameld. Via internet kunnen deze gegevens on-the-fly worden opgevraagd; alle details zijn in te zien, maar ook allerlei grafiekjes staan de ‘stroom-leveranciers’ tot hun beschikking.

In mijn geval moet ik wekelijks een laptop aansluiten op de omvormers en dan kan ik de resultaten van die periode uitlezen.
Uiteraard sla ik die in Excel op, zodat ik ook overzichten kan maken; zie het Voorbeeldbestand, tabblad Data.

Per dag leg ik vast hoeveel kWh een set (met drie panelen) heeft opgeleverd. Voor het gemak laat ik Excel ook de dag van de week weergeven. Naast de datum staat daartoe een kolom met dezelfde inhoud maar met een andere datumopmaak namelijk “dddd” (selecteer een cel in die kolom en kies celeigenschappen m.b.v. Ctrl-1).
De kolom met de kWh’s heeft een voorwaardelijke opmaak: als het resultaat van een dag lager is dan het gemiddelde dan wordt die rood weergegeven.

Wikipedia:

Het kilowattuur (symbool kWh) is een eenheid van arbeid of, populair uitgedrukt, een hoeveelheid elektrische energie. Als men een machine met een vermogen van 1 kW één uur laat werken heeft men een energie van 1 kWh "verbruikt".
De kosten voor opwekking van 1 kWh aan elektriciteit zijn in Nederland ongeveer 4 eurocent voor een elektriciteitscentrale die wordt gestookt op aardgas of steenkool, en 5-8 eurocent, afhankelijk van de standplaats van de molen, voor windenergie (2003). 
De verbruikersprijs ligt echter aanzienlijk hoger vanwege de kosten van bijvoorbeeld service, stroomtransport en energiebelasting. In 2013 kost een kWh ca. 23 eurocent.
Een gemiddeld huishouden verbruikte in Nederland in 2010 ongeveer 3.300 kWh.

Om makkelijk analyses te kunnen uitvoeren zijn aan de lijst nog enkele kolommen toegevoegd met het jaar, de maand en de week.
zon2NB  de gegevens worden opgeslagen in een zogenaamde Excel-tabel met de naam Zon (zie het artikel over de voordelen van tabellen). Formules en lay-out worden bij het toevoegen van nieuwe resultaten automatisch doorgevoerd.

Gemiddeldes en totalen

zon3Wat hebben de 3 panelen in die 5 jaar opgeleverd? Het Voorbeeldbestand bevat een tabblad Ovz met enkele statistieken.

In cel C2 wordt het totaal aantal opgeleverde kWh’s bepaald, de SOM van alle  waardes in de kolom kWh van de Excel-tabel Zon. Die cel heeft de naam TotKWH gekregen.

NB wanneer de tabel Zon wordt uitgebreid met volgende dagen dan zal de formule direct geactualiseerd worden met de nieuwe gegevens.

In cel C3 (met de naam AantDg) staat de volgende formule om het aantal geregistreerde dagen te tellen:
=MAX(Zon[Datum])-MIN(Zon[Datum])+1

Dus de grootste datum in de tabel minus de kleinste; gecorrigeerd met 1 omdat de eerste dag ook meetelt.

NB aangezien ik zeker weet dat alle dagen gevuld zijn had ik ook kunnen gebruiken:
=AANTAL(Zon[Datum])

Het gemiddelde per dag hebben we nu ook: in cel C4 (met de naam GemDag; deze naam wordt in de voorwaardelijke opmaak van het tabblad Data gebruikt) staat de formule:
=TotKWH/AantDg

Door in C5 het gemiddeld aantal dagen per jaar in te vullen weten we het jaargemiddelde.

zon4Natuurlijk willen we ook de totalen per jaar zien. In cel F3 staat daartoe de formule:
=SOM.ALS(Zon[Jaar];E3;Zon[kWh])

Dus de som van alle kWh’s waarvoor geldt dat in de kolom Jaar de waarde uit cel E3 staat.

Kopieer de formule naar beneden en klaar is …!

Het lijkt er op, dat we in 2016 het resultaat van het topjaar (2015) niet gaan bereiken.

Om wat beter inzicht te krijgen hoe de resultaten over de tijd zijn verdeeld, maken we wat grafieken.

Grafieken

zon5

Het resultaat van de afgelopen 5 jaar in beeld: in het blauw de dagresultaten en de rode lijn geeft het totaal in de loop van de tijd weer.

Hoe is deze grafiek gemaakt:

  1. zon6op basis van de gegevens in het tabblad Data is een draaitabel gemaakt (via de menu-optie Invoegen en dan Tabellen/Draaitabel); zie het tabblad OvzDag in het Voorbeeldbestand:
    als rij-label is daar de Datum genomen en de kWh’s zijn 2 keer in het waardegebied gezet.
  2. de 2e kWh-kolom heeft een aparte weergave:
    zon7Via rechtsklikken op één van de waardes in die kolom is de optie Waarden weegeven als gekozen. Na het klikken van de optie Voorlopig totaal in … kan één van de velden in Rijlabels worden gekozen (in dit voorbeeld hebben we er maar 1, namelijk Datum).
    Op deze manier creëren we een lopend cumulatief: het resultaat is de som van alle kWh’s tot en met de betreffende datum.
  3. Kies bij Opties in het tabblad Hulpmiddelen voor Draaitabellen het onderdeel Draaigrafiek; zorg dat de dagresultaten Kolom-grafiek als type krijgen en het lopend cumulatief een lijngrafiek en plaats deze laatste op de secundaire as.
  4. Nog even een sprekende titel:
    * in cel F2 staat de formule:
    =”Zonnepanelen set 1: “&TEKEN(13)&TEKST(GemDag;”0,0″)&” kWh gem per dag en “&TEKST(TotKWH;”#.##0″)&” totaal na “&TEKST(AantDg;”#.##0″)&” dagen”
    Hier worden diverse teksten aan elkaar ‘geplakt’ met het &-teken; naast letterlijke teksten ook verwijzingen naar bepaalde cellen (inclusief opmaak mbv de functie TEKST). De functie TEKEN zorgt met de code 13 voor een overgang naar een nieuwe regel (niet direct zichtbaar in de cel F2).
    * in het tabblad Hulpmiddelen voor Draaigrafieken zorgen we via de Indeling en de optie Grafiektitel dat er een titel tevoorschijn komt; terwijl deze actief is tikken we in de formulebalk in =OvzDag!$F$2 (of we klikken met de muis op de betreffende cel)

Maar een grafiek per dag is wel heel gedetailleerd; in het Voorbeeldbestand is een tabblad OvzWk opgenomen waarin opnieuw een draaitabel is gemaakt op basis van de gegevens uit het tabblad Data.
In de rijen staan nu 2 velden: het Jaar en de Week. De 2e kolom van de kWh’s krijgt een voorlopig totaal op basis van de Week, zodat een lopend cumulatief per jaar ontstaat.
zon8Op eenzelfde manier is een grafiek per maand gemaakt (zie tabblad OvzMnd):

zon9

Om de resultaten per maand in de loop van de tijd beter te kunnen vergelijken hebben we ook nog tabblad OvzMnd2:

zon10

Duidelijk is te zien, dat de afgelopen maand (juni 2016) ver achter is gebleven bij het resultaat in dezelfde maand de jaren daarvoor; 60 in plaats van 90 kWh!

NB juni 2011 is zo laag omdat die maand slechts 2 productiedagen telde.

EK-2016: wie wordt kampioen?

Excel, VBA (Visual Basic for Applications), , , , , , , ,

ek2016Er is al veel over geschreven: Nederland is er niet bij!

Waar niet bij? Ik bedoel het EK-voetbal natuurlijk. Dus dan maar de focus op België? Of toch Duitsland? Of gastland Frankrijk? Tsja, wie zal op 10 juli aangewezen worden als de kampioen van Europa?

Daarom de WK-voorspeller van 2 jaar geleden maar eens opgepoetst; alle landen ingevoerd, schema nagekeken om tot de ontdekking te komen, dat de indeling van de achtste finales wel heel anders gaat dan bij het WK.

In dit artikel bespreek ik dan ook niet meer de werking en de nauwkeurigheid van de voorspeller (kijk daarvoor naar WK- voorspeller deel 1, deel 2 en deel 3), maar alleen naar de indeling van de ploegen na de groepswedstrijden.
ek2016aO ja, België heeft volgens dit Excel-model trouwens ongeveer 10% kans om kampioen te worden!

Kleine lettertjes: als het model klopt tenminste en alle aannames de werkelijkheid goed benaderen.

Indeling achtste finales

Bij dit EK gaan de nummers 1 en 2 automatisch over naar de knock-out-fase. Van de 6  landen, die bij de groepswedstrijden op plaats 3 eindigen, gaan de beste 4 verder.
ek2016bVoor de indeling van deze 4 is een ingewikkeld schema bedacht (zie hiernaast; bron is WIKIPEDIA)).
De indeling is dus afhankelijk van de groepen waaruit de 3e plaatsen komen (aangeduid met de letters A t/m F).

In het Voorbeeldbestand worden daarom de volgende stappen doorlopen om tot de juiste indeling van de achtste finales te komen (zie tabblad Groepsfase):

  1. in de cellen X4 t/m X9 worden de punten van de landen opgehaald die derde zijn geworden in de 6 groepen
  2. met behulp van de functie GROOTSTE (waarbij ook kan worden aangegeven of je echt de grootste wilt hebben of de een na grootste enz) worden de beste 4 resultaten opgehaald in de cellen X12 t/m X15
  3. in de cellen Y12 t/m Y15 worden dan met behulp van de functies INDEX en VERGELIJKEN de corresponderende groepsletters opgehaald
  4. nu de moeilijkste stap: in cel Y16 (met de naam Serie3) moeten de 4 groepsletters gesorteerd worden.
    Om de functie GROOTSTE te kunnen gebruiken moeten we letters eerst vertalen naar getallen met de functie CODE (een A wordt dan 65, een B 66 etc).
    Als we via GROOTSTE de getallen in de juiste volgorde hebben staan, wordt het getal weer terugvertaald naar een letter met de functie TEKEN. De letters worden aan elkaar ‘geplakt’ met behulp van het &-teken.
    We moeten Excel nog wel aangeven, dat in de formule de functie GROOTSTE op een serie cellen moet worden losgelaten; daarom is de invoer van de formule niet met Enter afgesloten maar met Ctrl-Shift-Enter (we hebben dus een zogenaamde CSE-formule ingevoerd; zie ook het artikel SOMPRODUCT: meer dan SOM en PRODUCT).
  5. Het resultaat in cel Y16 (=Serie3) wordt in het tabblad Finales gebruikt om de juiste tegenstander van de poule-winnaars op te halen; bijvoorbeeld in cel B9 moet de tegenstander van de winnaar van groep B komen:
    =VERT.ZOEKEN(Serie3;Series3;3;ONWAAR)
    Series3 is de naam van de cellen met het Wikipedia-overzicht in het tabblad Groepsfase; de tegenstander van de B-winnaar staat in de derde kolom.

Verwerken resultaten

De eerste wedstrijden zijn ondertussen gespeeld. Geef in kolom M van het tabblad Groepsfase de juiste punten aan de betreffende ploeg.

Wanneer je de resultaten op deze manier in het spreadsheet verwerkt, blijken de kansen voor Duitsland en Frankrijk dankzij hun gewonnen wedstrijden al iets te zijn toegenomen.

Tabellen (deel 2)

Excel, , ,

TabelIn mijn vorige artikel (Kunst en Excel) heb ik 11 voordelen van het gebruik van tabellen in Excel besproken.

Op een forum voor Excel-experts heb ik gevraagd of er ook nadelen zijn aan tabellen.

Daar zijn 2 reacties op gekomen, waarvan de tweede aanleiding is geweest voor dit artikel.

Nadelen Tabellen in Excel

De eerste reactie ging over de naam, die de tabel automatisch krijgt (Tabel1, de volgende Tabel2 etcetera). De naam moet dan nog aangepast worden om zodoende een zinvolle beschrijving te krijgen; tsja, hier zie ik zo gauw geen echt probleem (en ook geen oplossing).
Maar ook werd aangegeven, dat wanneer de tabel uit de werkmap wordt verwijderd de naam nog wel actief zou blijven. Zover ik kan nagaan, geldt dat wel voor cellen (of celbereiken) die een naam krijgen en daarna worden verwijderd, maar niet voor tabellen!

In de tweede reactie werd aangegeven, dat tabellen niet bij een gegevensvalidatie kunnen worden gebruikt.
Ja, daar heeft Microsoft een steek laten vallen: standaard kan alleen een celbereik als keuzelijst worden opgegeven. Wel is het mogelijk om een ‘gewone’ naam als bron daarvoor op te geven, maar dan moet je in de tabel de gewenste reeks een aparte naam geven en er voor zorgen, dat bij uitbreiding van de lijst deze naam ook wordt aangepast.
Maar voor dit probleem hebben we gelukkig wel een oplossing!

Gegevensvalidatie

TabelIn het Voorbeeldbestand heb ik een tabblad Param opgenomen, waar 2 tabellen staan:

  1. in het eerste blok staan afkortingen en omschrijvingen voor alle afdelingen die kunnen worden gebruikt.
    De tabel heeft de naam tblAfd gekregen (hoe je een tabel maakt en de naam kunt aanpassen is in het artikel Kunst en Excel besproken).
  2. daarnaast staat een overzicht van de mogelijke kostensoorten; deze tabel heeft de naam tblKst

Deze twee tabellen gaan we gebruiken voor een gegevensvalidatie:

  1. Tabelde invoer in cel B3 van het tabblad GegVal moet beperkt worden tot die drie afkortingen voor de afdelingen, die in Param zijn opgenomen (dus D, C en O).
  2. kies de menuoptie Gegevens en dan binnen het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie:
    Tabel
  3. Tabelin het vervolgscherm kiezen we bij Toestaan de optie Lijst.
  4. dan komt de optie Bron tevoorschijn; klik op Tabel en selecteer dan de cellen B3 tot en met B5 in het tabblad Param.
  5. Klik OK. Aan cel B3 in GegVal is nu een keuzepijltje toegevoegd; als je daar op klikt kun je één van de drie mogelijkheden kiezen.

Op dezelfde manier kan ook de invoer in cel C3 beperkt worden tot de drie mogelijke kostensoorten uit Param (de cellen E3 t/m E5).

Door de cellen B3 en C3 te kopiëren kan de gegevensvalidatie uitgebreid worden naar andere cellen.

Een groot nadeel is, dat als er nieuwe opties bijkomen voor de afdelingen en/of de kostensoorten de betreffende gegevensvalidaties moeten worden aangepast.

Gegevensvalidatie met Tabellen

In plaats van een verwijzing naar de cellen B3 t/m B5 in het tabblad Param (als bron geven we dan op =Param!$B$3:$B$5) zouden we de tabelverwijzing =tblAfd[Afd] willen gebruiken (dus de kolom Afd in de tabel tblAfd).
Als we dat doen krijgen we een foutmelding, maar die kunnen we omzeilen door de functie INDIRECT te gebruiken.

Deze functie zet een tekst om in een Excel-verwijzing. Als voorbeeld: INDIRECT(“B”&A1) geeft een verwijzing naar de cel B1 als A1 de waarde 1 bevat, maar verwijst naar B2 als de waarde van A1 gelijk is aan 2 etc.

Door als bron voor de gegevensvalidatie in te voeren =INDIRECT(“tblAfd[Afd]”) krijgen we toch het gewenste resultaat.

TabelBekijk in het tabblad Boekingen1 de gegevensvalidatie van de cellen in kolom C en E. Deze maken gebruik van deze methode.

Voor de opvoer van boekingen in dit tabblad is ook gebruik gemaakt van een tabel. Een voordeel is dat bij de opvoer van een nieuwe regel (record) onderaan de gegevensvalidatie ook direct wordt overgenomen.
Hetzelfde geldt voor de formules in de kolommen D en F, waar met behulp van VERT.ZOEKEN de omschrijving van de gekozen afdeling of kostensoort wordt opgehaald.

In het tabblad Boekingen2 worden alleen de omschrijvingen van de afdelingen en de kostensoorten ingevoerd. Ook de invoer hiervan is via gegevensvalidatie ´beveiligd´.

Wanneer er nu een afdeling aan het tabblad Param wordt toegevoegd (bijvoorbeeld H met als omschrijving HRM) dan zullen alle gegevensvalidaties automatisch deze mogelijkheid meenemen. Uiteraard geldt dit ook voor de uitbreiding van het aantal kostensoorten.

LET OP: in het tabblad GegVal wordt HRM niet als keuzemogelijkheid getoond, omdat daar de gegevensvalidatie ‘hard’ aan de cellen B3 t/m B5 van Param  is gekoppeld.