Tagarchief: SOM.ALS

Zonnepanelen

4 juli 2016ExcelCumulatief, Draaigrafiek, Draaitabel, Namen, SOM.ALS, Tabel, Teken, voorwaardelijke opmaakGijs Verbruggen

In 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

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

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

Natuurlijk 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

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:

op 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.
de 2e kWh-kolom heeft een aparte weergave:
Via 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.
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.
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.
Op eenzelfde manier is een grafiek per maand gemaakt (zie tabblad OvzMnd):

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

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.

Kunst en Excel

29 april 2016ExcelSOM.ALS, TabelGijs Verbruggen

Een tentoonstelling over het leven en het werk van Grayson Perry in het Bonnefantenmuseum in Maastricht (nog te zien tot en met 5 juni 2016; laat deze kans niet voorbij gaan!) ontroerde mij zeer.
Waarom eigenlijk? Ik weet nog steeds niet of ik zijn (of haar?) vazen nu zo mooi vind. Ook vond ik de wandkleden in eerste instantie niet echt speciaal. Het is toch alleen maar raar om met een roze motor door Europa te toeren met je lievelingsbeer achterop? Een huis bouwen met allemaal vreemde hoekjes en beelden, dat is toch niet normaal?

Maar alles kwam in perspectief te staan, toen ik de films over zijn leven en werk had bekeken: toen kwam het besef, dat iemand probeert ons een spiegel voor te houden door zijn/haar (fantasie)wereld en leven aanschouwelijk te maken. Maar ik blijf het moeilijk vinden om het gevoel, dat de tentoonstelling bij mij opriep, onder woorden te brengen.

Dat wordt dan het bruggetje naar Excel: het gaat mij makkelijker af om (een onderdeel van) Excel uit te leggen (hoop ik!).

De tentoonstelling liet me ook weer zien, dat ik zelf dit soort kunstzinnige creativiteit mis. Maar voor mij is de Kunst van het Programmeren (was een vak op de TH-Eindhoven) ook een vorm van creativiteit is: hoe los je slim (en mooi) bepaalde problemen op.

Laten we overgaan naar Excel. Deze keer ga ik mijn belofte inlossen en iets vertellen over tabellen in Excel (en waarom we die veel meer moeten gebruiken ofwel de 10 voordelen van Tabellen).

Tabel maken

Nu de tentoonstelling van Perry de wereld overgaat (na Maastricht gaat die naar Denemarken), moet alles natuurlijk geïnventariseerd worden, inclusief de grootte van de objecten.

In het Voorbeeldbestand in het tabblad Cat1 heb ik een begin gemaakt met de catalogus: in de kolommen staan de rubrieken die ik wil vastleggen en in de rijen komen dan alle objecten.

Van dit overzicht gaan we een Excel-tabel maken:

selecteer één van de cellen in het overzicht (door er op te klikken), bijvoorbeeld cel C6
kies in de menutab Invoegen binnen het blok Tabellen de optie Tabel
Excel beoordeelt zelf welke gegevens waarschijnlijk in de tabel moeten worden opgenomen. In dit geval klopt dat niet helemaal (de kopregel Vazen nemen we niet mee): $B$2 moet gewijzigd worden in $B$3. Het vinkje bij kopteksten is wel juist.

En daar is ons nieuwe overzicht: automatisch inclusief Filters bij de koppen (voordeel 1), ‘zebra-lijntjes’ voor de leesbaarheid (voordeel 2), een eigen menutab voor diverse instellingen (voordeel 3) en het gegevensgebied heeft direct een naam (hier Tabel4, te vinden in het tabblad Hulpmiddelen voor tabellen en is aanpasbaar; voordeel 4)

Tabel uitbreiden

De grootste voordelen worden pas zichtbaar als we de tabel gaan uitbreiden (zie het Voorbeeldbestand, tabblad Cat2):

de logistiek-medewerker van Perry wil niet alleen de H, L en B van de objecten weten, maar ook hoeveel ruimte deze innemen.
In cel G3 komt de tekst Inhoud (cm3).
Aan de zebra-lijntjes is te zien dat Excel automatisch de tabel uitbreidt met een extra kolom (voordeel 5).
NB selecteer de 3 achter cm, druk op Ctrl-1 (Celeigenschappen) en kies Superscript
start een formule in cel G4 door het =-teken in te tikken, klik dan met de muis in cel D4, tik het *-teken, klik in E4, weer het *-teken, klik in F4 en druk dan op Enter.
In plaats van de standaardverwijzing =D4 * E4 * F4 heeft Excel een beter leesbare formule geplaatst (voordeel 6).
Nog mooier: de formule is direct naar beneden naar alle rijen gekopieerd (voordeel 7).
Wel zelf even de opmaak regelen: kies alle cellen met de formule in kolom G en pas de lay-out aan (bijvoorbeeld via Ctrl-1).
We voegen nog 2 vazen aan de lijst toe: in het tabblad Cat2 staan de gegevens daarvan. Selecteer deze gegevens, kies Kopiëren en Plak ze in B9.
Excel zal de tabel automatisch uitbreiden (zie de zebra-lijntjes) en vult achteraan ook direct de juiste formule toe (voordeel 8).

NB de naamgeving [@Hoogte] is even wennen: de rechte haken geven aan, dat het een naam in een tabel is; door de @ ‘weet’ Excel dat hij niet zomaar een hoogte moet nemen maar die uit dezelfde rij en Hoogte is één van de kopteksten.

Analyse van tabelgegevens

Een betrokken archivaris wil vanuit het overzicht wat extra informatie ophalen (zie het Voorbeeldbestand, tabblad Cat3).

aangezien de archivaris niets kan met cm3, heeft hij een kolom met de inhoud in liters toegevoegd door een koptekst in te tikken en de formule =[@[Inhoud (cm3)]]/1000 (ja, de archivaris kan met Excel omgaan!)
aangezien hij wil weten hoeveel werken er uit de diverse jaren komen maakt hij een draaitabel:
* selecteer één van de cellen in het overzicht (door er op te klikken), bijvoorbeeld cel C6
* kies in de menutab Invoegen binnen het blok Tabellen de optie Draaitabel
* Excel kiest automatisch het juiste bereik (hier tblCat3), klik OK
* sleep Jaar naar de Rijlabels en Omschr naar het Waarden-gebied.
nu hij toch bezig is: misschien is het ook wel handig om te weten hoe hoog de stapel wordt wanneer de werken per jaar in het magazijn moeten worden bewaard:
* sleep ook de Hoogte naar het Waarden-gebied

Maar wat nu als we nog meer vazen aan onze catalogus toevoegen:

op het tabblad Cat3 van het Voorbeeldbestand staan nog 2 extra exemplaren: kopieer de gegevens en plak ze in cel B11.
De tabel wordt automatisch uitgebreid en de formules achteraan worden ook naar de nieuwe regels doorgetrokken
Jammer, de draaitabel is niet gewijzigd! Iedereen, die gebruik maakt van dit (verders) formidabele hulpmiddel weet, dat na het aanpassen/uitbreiden van de brongegevens de draaitabel ververst moet worden: klik met de rechter muisknop ergens in de draaitabel en kies Vernieuwen.
Bij ‘gewone’ Excel-overzichten is dit niet voldoende; dan moet ook nog worden aangegeven dat de draaitabel naar een groter bereik moet ‘kijken’.
Is de bron een tabel dan is alleen Vernieuwen van de draaitabel voldoende; intern weet Excel dat de tabel ‘gegroeid’ is (voordeel 9).

Verwijzingen naar tabellen

Een andere archivaris is wat minder handig met Excel en weet niet goed hoe hij met draaitabellen moet omgaan (hij vergeet altijd Vernieuwen te kiezen en komt dus met de verkeerde cijfers).

Hij maakt daarom zijn eigen ‘draaitabel’ (creatief toch?) om de totale hoogte en lengte van de vazen per jaar te krijgen:

in het Voorbeeldbestand in het tabblad Cat4 komt in cel K19 het jaar waarvan hij het overzicht wil hebben (in het voorbeeld 2000).
en daaronder de formule
=SOM.ALS(tblCat4[Jaar];K19;tblCat4[Hoogte])
Ook hier geldt weer: als de tabel wordt uitgebreid met nieuwe vazen dan zal de formule alle nieuwe regels automatisch in het resultaat meenemen.
NB1 gelukkig hoeft dit niet ‘met de hand’ zo ingevoerd te worden: tik het begin van de formule tot en met het haakje-openen in en selecteer met de muis de cellen B4 tot en met B12, tik de punt-komma etc.
NB2 in dit geval staat voor de velden Jaar en Hoogte geen @, omdat niet de gegevens uit de overeenkomende regel moeten worden meegenomen maar alles uit de betreffende kolom.
NB3 omdat de verwijzingen buiten de tabel staan wil Excel wel weten uit welke tabel de gegevens moeten worden opgehaald; vandaar de tblCat4 voor de velden.
voor de lengte in cel K21 tikken we eenzelfde soort formule in (of we kopiëren de formule van K20 en dan op de juiste plaatsen wat aanpassingen)
in J20 en J21 nog een tekst (Hoogte resp. Lengte) en het overzicht is klaar.

NB het voordeel van deze ‘eigen draaitabel’ is dat die bij iedere wijziging van de bron direct wordt aangepast; Vernieuwen is niet nodig.

LET OP In het Voorbeeldbestand staan in J20 en J21 geen harde teksten, maar verwijzingen naar de kopjes in de tabel: =tblCat4[[#Kopteksten];[Hoogte]].
Wanneer de kopjes wijzigen (bijvoorbeeld ipv Hoogte wordt H (cm) gebruikt) dan past het overzicht zich automatisch aan. De draaitabel laat ons dan in de steek!

Een van de vele opties van tabellen, die je terug vindt onder Hulpmiddelen voor tabellen, is het al dan niet zichtbaar maken van een Totaalrij (voordeel 10):

selecteer één van de cellen in de tabel van tabblad Cat4 (door er op te klikken), bijvoorbeeld cel C6
vink in de menutab Hulpmiddelen voor tabellen binnen het blok Opties voor tabelstijlen de optie Totaalrij aan
onderaan iedere kolom kan nu een bepaald soort totaal gekozen worden (Som, Aantal, Gemiddelde etcetera).

NB naar de totalen kunnen ook weer verwijzingen gemaakt worden; in cel K23 wordt de totale hoogte opgehaald:
=tblCat4[[#Totalen];[Hoogte]]

Cadeau: dit was voordeel nummer 11!

Temperaturen en thermometer

3 juli 2015ExcelAANTALLEN.ALS, Gegevensvalidatie, Grafiek, Grootste, Plakken speciaal, SOM.ALS, Tabel, VERT.ZOEKENGijs Verbruggen

Het is vandaag toch geen weer om buiten te zijn, dus maar achter de PC gekropen om een artikel voor G-Info te schrijven.

Het onderwerp lag voor de hand: waar hebben we het allemaal over de laatste dagen? Juist ja, de temperaturen!

Eens kijken of we (met behulp van Excel) nog wat informatie kunnen toevoegen. Uit KNMI-gegevens maken we een grafiek waardoor het onderwerp nog meer gaat leven.

Voor de fun: in het Voorbeeldbestand vind je ook een “live”-thermometer.

Brongegevens

Voordat we temperatuurgrafieken kunnen maken hebben we brongegevens nodig.
Even “googelen”: al snel bleek, dat de KNMI heel veel (historisch) materiaal klaar heeft staan, dat je gratis kunt downloaden (zie http://www.knmi.nl/klimatologie/uurgegevens/select_uur).

Ik heb voor dit artikel alleen de temperatuur per uur voor drie weerstations voor het lopende jaar binnengehaald (hoewel de gegevens over de duur van de neerslag ook wel eens interessant zouden kunnen zijn!)

Na het downloaden hebben we een tekstbestand op de PC staan; deze gegevens allereerst maar eens in Excel geplakt (zie het tabblad KNMI in het Voorbeeldbestand).
Na diverse regels toelichting komen de gegevens waar het ons om te doen is: het weerstationnummer, de datum, het uur en de temperatuur. Per dag zien we alle gegevens in 1 cel komen; gescheiden door een komma.
Dat wordt “knippen”:

selecteer alle cellen met temperatuurgegevens (in het tabblad KNMI de cellen B17:B13120)
kies in de menutab Gegevens de optie Tekst naar kolommen
er opent zich een nieuw scherm: kies daar de optie Gescheiden en klik op de knop Volgende
zorg dat in het volgende scherm bij Scheidingstekens ook Komma staat aangevinkt en klik op de knop Voltooien
alle gegevens zijn nu mooi over kolommen verdeeld

Wat opvalt: de datum staat in het militaire formaat (jjjjmmdd, dus 4 cijfers voor het jaar, 2 voor de maand en 2 voor de dag; in die volgorde!).
Daarnaast moeten we de temperatuur nog door 10 delen om netjes graden Celsius te krijgen.

TIP op de volgende manier gaat dat het snelst:

klik ergens in een lege cel de waarde 10 in
kopieer deze cel (bijvoorbeeld via Ctrl-C)
selecteer alle temperatuurcellen
klik met de rechtermuisknop ergens in de selectie
kies de optie Plakken speciaal …
kies bij Bewerking de optie Delen
klik op de knop OK
maak de cel uit punt 1 weer leeg

Temperatuurgrafiek1

Om op de standaardmanier een grafiek te maken moeten we de gegevens van de KNMI nog iets anders indelen: na het “knippen” zetten we de temperaturen van de drie weerstations achter elkaar in verschillende kolommen en zetten nog wat verklarende teksten boven de kolommen (zie tabblad Grafiek1).

Maak op de ‘gewone’ manier een grafiek.
Zorg dat op de x-as de Datum en het Uur zichtbaar worden door én de gegevens van kolom B én die van kolom C op te geven.

Door de grote hoeveelheid dagen en daarbinnen de temperatuur per uur wordt de grafiek moeilijk te lezen.

Temperatuurgrafiek2

Een alternatieve grafiek kunnen we creëren door middel van een draaitabel. Een groot voordeel hierbij is dat de brongegevens niet hoeven worden aangepast.

In het Voorbeeldbestand zijn in het tabblad KNMI_data de brongegevens in een Excel-tabel opgenomen. Nieuwe gegevens kunnen onderaan worden toegevoegd; zijn ze nog niet ‘geknipt’, doe dat dan hier. Excel neemt automatisch de deling van de KNMI-temperatuur door 10 voor zijn rekening en als ’toegift’ wordt de datum in een normaal formaat gezet.

In een draaitabel worden alle gegevens uit de tabel in een bruikbaar formaat gezet (zie tabblad Draai in het Voorbeeldbestand).
Hierbij zijn een paar handigheidjes verwerkt:

in de kolommen zijn de weerstations weergegeven; standaard komen hier de nummers tevoorschijn. Deze nummers heb ik overschreven met de betreffende namen. Voortaan houdt Excel deze wijziging vast.
in de rijen staan naast de uren ook de echte datums uit KNMI_data. Deze zijn echter zodanig gegroepeerd, dat er ook een onderscheid in jaren en maanden wordt gemaakt:
* klik met de rechtermuisknop op een van de datums
* kies de optie Groeperen
* in het vervolgscherm de grenzen ruim genoeg zetten, zodat toekomstige gegevens ook direct goed verwerkt worden
* klik bij de optie Op de Dagen, Maanden en Jaren aan
* klik op de knop OK
* sleep de Jaren en Maanden van de Rijlabels naar het Rapportfilter

Wanneer de draaitabel actief is (klik ergens in de draaitabel) komt er bovenaan een nieuwe set tabbladen tevoorschijn: Hulpmiddelen voor draaitabellen. Daar vinden we binnen de menutab Opties een knop Draaigrafiek.

Het resultaat staat op het tabblad Grafiek2.
Via de filters op Jaren, Maanden en Station kunnen meer of minder en andere gegevens zichtbaar gemaakt worden.

LET OP als er regels aan de tabel KNMI_data zijn toegevoegd, kies dan wel nog de optie Vernieuwen in de Draaitabel of de Draaigrafiek (hoe? Klik rechts ergens in de tabel of aan de rand van de grafiek).

Thermometer

Terwijl ik dit aan het schrijven ben, kijk ik af en toe op de thermometer om te kijken of mijn warmtegevoel overeen komt met de werkelijkheid. Nou, hierbinnen is het bijna 30 graden en zo voelt het ook! Dus nog maar wat water drinken.

Maar dit is wel aanleiding om te kijken of we in Excel ook een thermometer kunnen inbouwen.
Daar gaat ie (zie tabblad Thermometer in het Voorbeeldbestand):

om de keuze van weerstation te vergemakkelijken maken we een klein tabelletje met ‘rugnummers’. Wanneer we aan de tabel KNMI_data ook andere weerstations toevoegen dan moet dit lijstje uitgebreid worden
in de cellen F2 en F3 kan gekozen worden voor welke dag en welk station de temperatuur moet worden weergegeven.
Denk aan de datum-notatie!
Cel F3 is van een Gegevensvalidatie voorzien, zodat daar alleen nummers gekozen kunnen worden uit het lijstje van punt 1. In cel F4 komt dan automatisch de naam van het station via de formule
=VERT.ZOEKEN(F3;Stations;2;ONWAAR)
De waarde van cel F3 (in dit geval 380) wordt opgezocht in de tabel met de naam Stations (het lijstje uit punt 1). Als die gevonden wordt, dan wordt de waarde uit de 2e kolom teruggegeven (de naam dus). Door Onwaar geven we aan, dat we een exacte match willen; de lijst hoeft dus niet gesorteerd te zijn.
dan gaan we de temperatuur opzoeken, die bij die dag én dat station hoort en dat per uur:
=SOMMEN.ALS(KNMI_data[Temp];KNMI_data[Datum];$F$2;KNMI_data[Station];Thermometer!$F$3;KNMI_data[Uur];E10)
Niet schrikken! Verticaal zoeken met verschillende voorwaarden kan niet, dus daarom iets ingewikkelder: tel alle temperaturen in de kolom Temp van de tabel KNMI_data op (we zorgen, dat er maar 1 temperatuur wordt opgehaald, dus dat is een makkie voor Excel) als aan de volgende voorwaarden wordt voldaan:
* in de kolom Datum van de tabel KNMI_data staat de waarde uit cel F2
* én in de kolom Station staat de waarde uit cel F3
* én het Uur komt overeen met cel E10
Door de relatieve en absolute verwijzingen kunnen we de formule uit F10 naar beneden kopiëren.
omdat op deze manier de waarde 0 (nul) ontstaat als er geen gegevens zijn staat er nog een ALS-formule omheen, die een lege resultaat genereert als er geen temperatuur te vinden is.
in de cellen F5, F6 en F7 worden de minimum-, maximum- en laatst bekende waarde opgehaald (voor de historische data zal dit dus altijd 12 uur ’s nachts zijn).
NB de formule in cel F7, die als matrixformule is ingevoerd (zie de accolades), vergt teveel uitleg voor dit weer! Probeer zelf de logica te ontrafelen. Kom je er niet uit; stuur een berichtje via de site.
dan een grafiek maken met de 3 waardes uit de cellen F5, F6 en F7, zodat we naast de “actuele” temperatuur ook het minimum en maximum van die dag zien.
De actuele/laatste waarde van die dag als een staafdiagram, de andere twee als lijngrafiek, inclusief markering.
Omdat we onder 0 graden ook een markering willen zien, is er nog een reeks toegevoegd met de waarde -50.
Bijna klaar: het moet er nog als een echte thermometer uitzien. Dus een afbeelding van een thermometer gegoogeled en daar de grafiek overheen gelegd, zodanig dat de schalen overeen komen.
Dan in de grafiek alle overbodige zaken als assen en rasterlijnen verwijderen en het grafiek- en tekengebied transparant maken (de optie Geen opvulling).
De 2 objecten (Grafiek en Afbeelding) allebei selecteren en Groeperen kiezen, zodat ze samen verplaatst kunnen worden: daar is onze thermometer!

NB door de tabel in het tabblad KNMI_data aan te vullen met de meest recente gegevens zal de thermometer een echt actuele stand weergeven.
Helaas nog geen real-time! Iemand een idee hoe dat zou kunnen??

WK-voetbal voorspeller (deel 3, incl VBA)

22 juni 2014Excel, VBA (Visual Basic for Applications)Aselect, Grootste, Index, SOM.ALS, VergelijkenGijs Verbruggen

Nog gauw even verder met de uitleg van het model uit de blog van 5 juni voordat het WK voorbij is!
Voor diegene die onderstaande uitleg willen volgen en het model niet (meer) paraat hebben: WK-voetbal voorspeller.

Werkblad Groepsfase

Vorige keer heb ik laten zien hoe we tot de puntenverdeling in de groepsfase komen.
We kijken nu verder naar de consequenties daarvan voor het vervolg van het toernooi.

Per land in kolom P (simpele verwijzingen naar kolom E en F) wordt gekeken hoeveel punten ze volgens het model hebben verdiend in de groepswedstrijden: in bijvoorbeeld cel Q4 moet het aantal punten komen, dat het land uit P4 heeft verzameld. Maar aangezien een land ‘uit en thuis’ kan spelen kunnen de verdiende punten in kolom M en in kolom N staan.
Om de relevante punten uit kolom M op te tellen kent Excel een mooie functie SOM.ALS. De functie kent de volgende vorm: =SOM.ALS(Bereik1;Voorwaarde;Bereik2)
Wat betekent: als een cel uit het Bereik1 gelijk is aan Voorwaarde, tel dan de overeenkomstige cel uit Bereik2 bij het totaal op.
In dit geval: =SOM.ALS($E$4:$E$51;P4;$M$4:$M$51)
Door de verwijzingen van Bereik1 en Bereik2 ‘absoluut’ te maken (dus met $-tekens voor de kolom-letter en het rij-nummer) en de Voorwaarde ‘relatief’ kan deze formule eenvoudig zonder aanpassingen naar beneden gekopieerd worden.

PS laat me weten als er behoefte is aan uitleg over het verschil tussen absolute en relatieve verwijzingen.

Maar ook zijn er punten te verdienen als ‘uitspelende’ partij, dus ook de punten uit kolom N moeten worden geteld.
De formule in Q4 wordt:
=SOM.ALS($E$4:$E$51;P4;$M$4:$M$51) + SOM.ALS($F$4:$F$51;P4;$N$4:$N$51)

Wat te doen als nummer 2 en 3 evenveel punten heeft; wie gaat dan door naar de achtste-finales?
Aangezien het hier maar een simpel model betreft en we niet hebben gemodelleerd hoeveel doelpunten ieder land maakt, moeten we een beetje truken: niet alleen krijgt een land de betreffende punten uit kolom M en N, maar we geven hem ook nog een willekeurig heel klein getal extra: + ASELECT()/10000.

Aangezien de functie ASELECT() een willekeurige getal tussen 0 en 1 genereert, krijgt een land dus een getal tussen 0 en 1/10.000 extra. Net genoeg om bij een ‘gelijke stand’ toch een verschil te hebben.

Volgorde binnen de groep
Nu we de resultaten van de groepen hebben opgezocht, moeten we nog bepalen wie de nummers 1 en 2 (en ook 3 en 4) zijn geworden.
In kolom U gaan we van iedere groep eerst het hoogst aantal punten ophalen met behulp van de functie =GROOTSTE(Bereik;Volgnummer); oftewel zoek in Bereik (gesorteerd van grootste naar kleinste) naar de met Volgnummer corresponderende waarde. Dus als Volgnummer 1 is, dan krijg je als resultaat de hoogste waarde; is het 2, dan de een na hoogste enzovoort.

Nu we de volgorde van punten per groep hebben gevonden, zoeken we in kolom T het bijbehorende land op met behulp van de functies INDEX en VERGELIJKEN (zie voor een verder uitleg hiervan het artikel Alternatief voor Vert.Zoeken).

Om in het volgende werkblad makkelijk verwijzingen naar de nummers 1 en 2 per groep te kunnen maken, geven we die cellen een naam (zie het artikel Namen ipv celverwijzingen): cel T4 krijgt de naam GA_1 (afkorting voor Groep A, nr 1), cel T5 de naam GA_2 (Groep A, nummer 2), T10 wordt GB_1 etc.

LET OP: je kunt NIET de naam GA1 gebruiken, omdat dit al een echte celverwijzing is; vandaar de _ (underscore).

Werkblad Finales

In kolom B nemen we de landen, die als eerste en tweede in een groep zijn geëindigd over. Eerst de winnaar van Groep A (in cel B3 tikken we in: =GA_1; een naamverwijzing dus), dan de 2e van Groep B (die spelen tegen elkaar in de achtste finales), winnaar Groep C, 2e van Groep D etc.

De rest van dit werkblad zou ondertussen voor zichzelf moeten spreken: we zoeken de sterktes van de landen weer op (kolommen C en D), bepalen aselect wie dan wint (kolom E) en zetten de winnaar in kolom F. Deze winnaars geven we weer een naam (AF_1, AF_2 etc. AF betekent dan Achtste Finale).
Het stramien is bij de kwart-, halve- en echte finale hetzelfde. Op die manier komen we uiteindelijk in cel Y18, de Winnaar.

Werkblad MC

Op de vorige werkbladen wordt iedere keer één Monte Carlo-run weergegeven: bij iedere wedstrijd wordt een ‘dobbelsteen’ gegooid en gekeken wie er wint. Al dit gedobbel levert dan uiteindelijk een winnaar op (meestal Brazilië, omdat we die bij Invoer het sterkste hebben ingeschat).

Door op F9 te drukken (of ergens iets te wijzigen op een werkblad) gaat Excel opnieuw met de dobbelstenen gooien en komen er overal andere resultaten tevoorschijn.
Door dit 100 keer te doen en te turven hoe vaak bijvoorbeeld Nederland winnaar wordt, weten we hoe groot de kans is dat Nederland Wereldkampioen wordt (op basis van de aannames in het werkblad Invoer en het gehanteerde model!!!).

Door het “drukken op F9” te automatiseren is het bepalen van deze kansen een stuk eenvoudiger geworden.

Allereerst maken we een tabel, waarin we kunnen turven hoe vaak een land wint:

tik in cel B4 in: =Landen
Dit is een verwijzing naar een reeks cellen in het werkblad Invoer; Excel neemt dan de waarde over die in die reeks in dezelfde rij staat als waar nu de verwijzing wordt getypt.
kopieer deze formule naar beneden tot en met cel B35.
kolom C moet door de PC worden gevuld; in C36 tellen we wel alvast het aantal runs op: =SOM(C4:C35)
in D4: =C4/$C$36; ofwel welk percentage van het totaal aantal runs komt ten gunste van dit land.
de kop boven de landen (cel B3) geven we ook een naam (LandWin); die kunnen we straks bij het turven goed gebruiken.

De grafiek is een weergave van deze tabel.

Nu nog even een klein programmaatje schrijven om de PC te laten ‘dobbelen’ en we zijn klaar!
Dat programma moet natuurlijk wel weten hoe vaak er een run gedraaid moet worden; dat zetten we in cel I5, die we de naam AantalRuns geven.

VBA-programma

Uitleg over gebruik van de VBA-editor kun je vinden in de blog van 5 mei 2014.
Hier zullen we volstaan met een korte uitleg van wat het programma doet:
1: definieer een programma (subroutine) met de naam MC
2, 3, 4: definieer variabelen, die we dadelijk gaan gebruiken
6: de programmavariabele iAantRuns krijgt de waarde van de Excel-cel met de naam AantalRuns
8: het tellertje i laten we lopen van 1 tot en met iAantRuns
9: we gaan dadelijk zoeken of we de winnaar ergens in ons lijstje tegenkomen (zou gek zijn als het niet zo was!!); we beginnen met zoeken in de regel 1, daarom geven we de variabele iRegel die waarde alvast
10: we hebben het land nog niet gevonden, dat leggen we ook even vast
12: wie is eigenlijk de winnaar van de run die net heeft gedraaid? Die staat in de Excel-cel met de naam Winnaar en dat stoppen we in de variabele strWin
13: zolang nog niet gevonden EN de cel iRegel’s onder de kop LandWin (Offset = Verschuiving) niet leeg is
14: als in die cel de winnaar van deze run staat dan
15: tel bij de waarde in de cel iRegel’s onder de kop en 1 kolom naar rechts (dus in kolom C) 1 op
16: gevonden!!!
17: een screenupdate binnen de applicatie zorgt er voor dat Excel alles doorrekent; in dit geval opnieuw gaat dobbelen bij alle wedstrijden
19: als we misschien nog verder moeten gaan zoeken, waar de winnaar staat, dan wel natuurlijk 1 regel lager
20: ga weer naar 13; als daar niet aan de voorwaarde wordt voldaan gaat het programma verder met 21
21: Next hoort bij de For van regel 8, volgende run dus
23: als het aantal runs doorlopen is, stopt het programma

Dit programma kun je laten runnen door op Alt-F8 te drukken, de macro MC te selecteren en OK te klikken, maar handiger is het om die met behulp van een button/knop te starten.
Zo’n knop maak je als volgt:

kies in de menu-balk de optie Ontwikkelaars
dan Invoegen
en klik op het eerste besturingselement: Knop
’teken’ met de cursor de omtrek voor de nieuwe knop
wijs de macro MC toe aan het object
en verander de tekst op de knop

Om ook de resultaten van één of meerdere runs te wissen, heb ik een tweede macro gemaakt (MC_Opnieuw) en die aan een knop toegewezen.
De werking van de macro zou met bovenstaande toelichting duidelijk moeten zijn.