Tagarchief: Namen

Sudoku in Excel

17 januari 2018Excel, VBA (Visual Basic for Applications)Geheel, Namen, Rest, Verschuiving, voorwaardelijke opmaakGijs Verbruggen

Voorbeeldbestand: Sudoku.xlsm Download

Ik kreeg onlangs de volgende vraag: kun je geen artikel schrijven waarin je uitlegt hoe je met behulp van Excel Sudoku-puzzels op kunt lossen.
Mijn antwoord bestond uit 2 woorden ‘Ja’ en ‘maar’.

De Ja, omdat met Excel ‘alles’ mogelijk is en de maar omdat het oplossen van zo’n puzzel meer een programmeer-uitdaging is en niet zozeer iets om de mogelijkheden van Excel voor de dagelijkse praktijk te laten zien.

Maar toen ik er wat langer over nadacht, zag ik wel wat mogelijkheden.
Dus nu toch een Sudoku in Excel, maar niet een machine die de oplossing genereert (wat zou je daar ook mee moeten), wel een systeem dat het zoeken van een oplossing ondersteunt.

Met behulp van Voorwaardelijke opmaak laat Excel zien op welke plaatsen een cijfer geplaatst kan worden. Dubbele invoer in een rij, kolom of blok wordt ook door Voorwaardelijk opmaak gesignaleerd. Bij deze opmaak wordt veelvuldig gebruik gemaakt van de Namen-optie van Excel; niet alleen de standaard-toepassing daarvan maar ook enkele meer ingewikkelde en dus interessante mogelijkheden!

Basis van het G-Info/Sudoku-systeem

Allereerst hebben we natuurlijk een blok van 9 bij 9 nodig, die dan verder is onderverdeeld in kleinere blokken van 3 bij 3 en in rijen en kolommen. Daar gebruiken we een eigenschap van cellen voor; we kunnen de (dikte van de) randen aanpassen (druk op Ctrl-1). Het bereik van de 81 cellen heeft de naam Sudoku gekregen.

Om straks te weten over welke kolom of rij we het hebben, zijn in regel 3 en in kolom B de cijfers 1 t/m 9 ingevuld. De kruising van die twee, dus cel B3, heeft de naam Ref gekregen. Straks moeten we ook weten wat de inhoud van een geselecteerde cel is; die informatie komt in cel O3 met de naam Selectie.
Regel 3 en de kolommen B en O zijn ‘onzichtbaar’ gemaakt . Dit is te zien aan de +-jes; door daar op te klikken, open en sluit je een bepaalde groep cellen (zie het tabblad Sudoku in het Voorbeeldbestand).

Om het geheel wat mooier te maken is het standaard-raster weggehaald (menutab Bestand, dan Opties en daarbinnen Geavanceerd; vink dan Rasterlijnen weergeven uit).
Alle regels en kolommen, die we niet nodig hebben, zijn verborgen (selecteer ze eerst en dan rechts klikken).
En als laatste zijn de klom- en rijnamen weggelaten (menutab Bestand, dan Opties en daarbinnen Geavanceerd; vink dan Rij- en kolomkoppen weergeven uit).

Voor een soepele werking van het spel zijn nog een paar knoppen toegevoegd die bepaalde VBA-routines opstarten; hier komen we later op terug.

Controle 1

Het eerste wat we willen weten als we een Sudoku invullen, is waar overal (als voorbeeld) een 1 staat of waar een 2 etc.
Dat kunnen we met Voorwaardelijke opmaak goed zichtbaar maken; de enige voorwaarde is dat we weten welk cijfer we willen laten oplichten. Daarvoor hebben we een klein VBA-programmaatje nodig:

De subroutine Worksheet_SelectionChange is gekoppeld aan het werkblad Sudoku. Als de selectie in dit werkblad wordt gewijzigd, wordt deze routine automatisch door Excel aangeroepen. Op deze manier bevat de cel Selectie (O3) altijd de laatst geselecteerde waarde uit het bereik Sudoku.

NB bekijk de VBA-routine in het Voorbeeldbestand door bijvoorbeeld met de muis rechts te klikken op de tab Sudoku en dan te kiezen Programmacode weergeven. Eventueel nog dubbelklikken op het blad Sudoku in de Projectverkenner.

Nu kunnen we de Voorwaardelijke opmaak toevoegen:

plaats de cursor in de eerste cel van de Sudoku (D5 dus) en Fselecteer dan de 81 Sudoku-cellen
kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak en dan Nieuwe regel …
in het vervolgscherm kiezen we de 2e optie (Alleen cellen opmaken met) en geven dan aan dat de opmaak moet worden doorgevoerd als de celwaarde gelijk is aan de inhoud van de cel Selectie (denk aan het =-teken vóór Selectie)
nog even de gewenste Opmaak instellen en OK

Controle 2

De volgende controle zorgt er voor dat we weten of er geen dubbele cijfers in de kolommen, rijen of kleine blokken voorkomen.

Voordat we dit makkelijk en flexibel kunnen doen moeten we wat voorwerk verrichten.
Excel moet automatisch beoordelen bij welke rij, kolom en blok een bepaalde cel hoort. Dan heb ik gedaan door enkele nieuwe Namen in te voeren:

plaats de cursor in de eerste cel van de Sudoku
een nieuwe naam creëren: kies in de menutab Formules in het blok Gedefinieerde namen de optie Naam definiëren
in het vervolgscherm kiezen we als Naam een handige omschrijving, in dit geval RijSel en bij Verwijst naar: =Sudoku!$B5
Denk aan het dollar-teken voor de kolom-letter.
Afhankelijk waar de cursor staat (nu in regel 5) zal deze naam de waarde uit kolom B van die regel ophalen.
op gelijkwaardige manier bestaat er ook de naam KolomSel met als formule =Sudoku!D$3
Deze naam geeft dus altijd de waarde uit regel 3 van de kolom waar de cursor staat.
om te bepalen in welk blok de cursor staat is wat ingewikkelder:
=GEHEEL((Sudoku!RijSel-1)/3)*3+GEHEEL((Sudoku!KolomSel-1)/3)+1
Hier is de naam BlokSel aan gekoppeld.
nu moeten we nog de inhoud van zo’n rij, kolom en blok kunnen ophalen om de inhoud te kunnen testen. Daartoe zijn nog 3 extra namen gedefinieerd:
RijTest met de formule =VERSCHUIVING(Sudoku!Ref;Sudoku!RijSel+1;2;1;9)
KolomTest met =VERSCHUIVING(Sudoku!Ref;2;Sudoku!KolomSel+1;9;1) en
BlokTest met =VERSCHUIVING(Sudoku!Ref;GEHEEL((Sudoku!BlokSel-1)/3)*3+2;REST((Sudoku!BlokSel-1);3)*3+2;3;3)

Nu kunnen we een nieuwe Voorwaardelijke opmaak toevoegen:

plaats de cursor in de eerste cel van de Sudoku (D5 dus) en selecteer dan de 81 Sudoku-cellen
kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak en dan Nieuwe regel …
in het vervolgscherm kiezen we nu de laatste optie (Een formule gebruiken). De formule wordt
=OF(AANTAL.ALS(BlokTest;D5)>1;
OF(AANTAL.ALS(RijTest;D5)>1;
AANTAL.ALS(KolomTest;D5)>1))
Aantal.Als kijkt hoe vaak in een bereik (de eerste parameter) de waarde van de tweede parameter voorkomt.
nog even een Opmaak instellen en OK

NB1 wil je de gebruikte opmaak bekijken kies dan Regels beheren binnen de optie Voorwaardelijke opmaak

NB2 achteraf gezien was het waarschijnlijker makkelijker geweest om voor het testen de functie INDEX te gebruiken in plaats van VERSCHUIVING. Misschien een andere keer.

Controle 3

Als laatste controle voegen we nog wat opmaak toe, die aangeeft op welke plaatsen een bepaald geselecteerd cijfer nog kan worden ingevuld:

plaats de cursor in de eerste cel van de Sudoku (D5 dus) en selecteer dan de 81 Sudoku-cellen
kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak en dan Nieuwe regel …
in het vervolgscherm kiezen we nu de laatste optie (Een formule gebruiken). De formule wordt
=EN(D5<=0;
EN(Selectie>0;
EN(AANTAL.ALS(BlokTest;Selectie)=0;
EN(AANTAL.ALS(RijTest;Selectie)=0;
AANTAL.ALS(KolomTest;Selectie)=0))))
Dus als de betreffende cel, waarvan de opmaak moet worden ingesteld, een waarde kleiner of gelijk aan nul heeft (dus er is nog geen cijfer ingevuld) EN via Selectie weten we welk cijfer we onder de loep nemen EN in het betreffende blok komt het cijfer nog niet voor EN ook niet in de rin EN niet in de kolom dan moet de opmaak toegepast worden.

Buttons/knoppen

Onder de Sudoku zijn een paar knoppen opgenomen, die het opzetten van een spel kunnen vergemakkelijken (zie het Voorbeeldbestand):

Wissen: de beveiliging van het tabblad wordt verwijderd, het speelveld leeg gemaakt, lettertypes en eventuele kleuren worden gereset. Ook de Selectie en de Starttijd worden leeg gemaakt.
de volgende stap is dat een nieuw spel moet worden opgezet. Doe dit op het tabblad Makkelijk of Moeilijk door onder een bestaand spel ‘met de hand’ cijfers op de juiste plaats te zetten.
Kies dan op het tabblad Sudoku de knop Kopieer. Er komt een pop-up waarmee kan worden aangegeven welk spel uit de voorraad moet worden opgezet. Selecteer daartoe het betreffende blok met de muis en klik dan op OK.
Als je klaar bent om te gaan spelen, klik dan op de knop Start. Allereerst worden alle opgezette cijfers geblokkeerd en vet gemaakt, het tabblad beveiligd (zonder wachtwoord) en de Starttijd ingevuld.
Wil je tussentijds wat zetten gaan proberen, bewaar dan de tussenstand via de knop Save. De stand van dat moment wordt gekopieerd naar het tabblad SaveGame.

Handleiding

In het tabblad Handleiding staat de werkwijze van het systeem ook uitgelegd en ik heb enkele tips toegevoegd.

Veel speel-plezier!

VBA-routines versnellen

4 november 2017Excel, VBA (Visual Basic for Applications)Aselect, Namen, TabelGijs Verbruggen

Voorbeeldbestand: VbaSnel.xlsm Download

Wanneer u net begint met het ontwikkelen van VBA-routines om werkzaamheden te automatiseren, zult u versteld staan van de tijdwinst die dit kan opleveren.

Maar …. als dan de hoeveelheid gegevens, waarmee u werkt, gaat groeien dan kan het zo maar zijn dan zo’n routine tientallen secondes bezig is en dat u denkt: kan dat niet sneller!

Het antwoord is (hoogst waarschijnlijk): JA, dat kan zeker sneller.

In dit artikel enkele tips voor efficiëntere VBA-routines.

Gebruik van Select

Wanneer u bepaalde handelingen in Excel wilt automatiseren, is de macro-recorder van VBA een uiterst handig hulpmiddel. U neemt de toetsaanslagen op en volgende keer kunt u met één druk op een knop diezelfde toetsaanslagen herhalen. Vaak wordt zo’n recorder-opname dan gebruikt als basis voor een uitgebreidere routine.

In dit voorbeeld geldt dat ook (zie Blad1 in het Voorbeeldbestand):

we hebben een tabel (met de naam Tabel1) met 2 kolommen: in de eerste staan maanden en in de tweede worden willekeurige bedragen met 2 decimalen gegenereerd, liggend tussen 1 en 2:
=ASELECTTUSSEN(100;200)/100
het eerste bedrag (cel C3) heeft de naam Bedrag1 gekregen, terwijl het bereik van alle bedragen de naam Bedr heeft.
in kolom F worden het aantal regels, het totaalbedrag en het gemiddelde bepaald. Het tweede gemiddelde is gelijk aan het totaalbedrag gedeeld door het aantal; een simpel voorbeeld van een ingebouwde controle.
als voorbeeld heb ik een VBA-routine gemaakt (gebaseerd op een macro-opname), die ook dezelfde berekeningen uitvoert.
Deze routine selecteert cel Bedrag1 en “loopt” zolang er geen lege cel is naar beneden; ondertussen wordt het aantal geturfd en een lopend totaal bepaald:
op het einde van de VBA-routine zorgen we dat de cursor weer in cel A1 komt en worden de resultaten via een pop-up zichtbaar gemaakt.
Ook de verstreken tijd is in de pop-up opgenomen.
klik op de button Select in het Voorbeeldbestand om de VBA-routine uit te voeren.

NB1 door de rechte haken rond een naam weet VBA, dat het een naam in de Excel-sheet is, dus niet de naam van een VBA-variabele.

NB2 zo ziet een info-scherm op de MAC er uit; de PC-versie oogt anders.

NB3 mijn MAC heeft dus ruim 11 seconden nodig voor de 1.000 verplaatsingen en berekeningen! Op een PC duurt het (uiteraard afhankelijk van de soort machine) nog geen seconde.

NB4 wil je de gebruikte routine zien: klik Alt-F11 of ga via de menutab Ontwikkelaars naar Visual Basic.

Gebruik van Select-2

Het grootste gedeelte van de benodigde tijd van de vorige VBA-routine gaat zitten in het fysiek verplaatsen van de cursor (via Offset).

Met behulp van het VBA-commando Application.ScreenUpdating = False kunnen we er voor zorgen, dat het verversen van het scherm niet meer plaats vindt. Het resultaat is, dat de MAC binnen een halve seconde klaar is!

Klik op de button Select2 om de alternatieve VBA-routine uit te voeren.

NB vergeet niet op het einde van zo’n routine de scherm-verversing weer aan te zetten: Application.ScreenUpdating = True

Gebruik van Range

Veel Excel-ontwikkelaars vinden het gebruik van de Select-methode uit den boze. Bij een prima alternatief wordt het belangrijkste gedeelte van de routine:

De berekeningen worden uitgevoerd voor iedere cel in het bereik Bedr. De computer gaat nu in sneltrein-verhaal langs de inhoud van de cellen zonder dat er een verplaatsing van de cursor nodig is.

De MAC is nu binnen 1/100 van een seconde klaar! Test de routine door op de button Range te klikken.

Gebruik van een array

In het vorige voorbeeld hebben we gebruik gemaakt van een Excel-bereik (in dit geval met de naam Bedr).
Bij heel grote bestanden kan er tijdwinst geboekt worden door dit bereik eerst in een aparte array (rij, reeks, matrix) te plaatsen:

het bereik met de naam Bedr (de truc met de rechte haken werkt nu niet meer) wordt cel voor cel in een array aBedr gestopt
voor ieder element van de array wordt het lopende totaal bepaald (Ubound=UpperBound=bovengrens)

Sneller dan het geluid!? De routine heeft minder dan 1/10.000 seconde nodig.

Gebruik van Tabel

Een alternatief voor de vorige oplossing is:

de kolom Bedrag uit Tabel1 wordt in de array aBedr gestopt
de bovengrens van de array plaatsen we in de variabele N (in het vorige voorbeeld moet de bovengrens iedere keer opnieuw worden bepaald; niet efficiënt)

Gebruik van Tabel-2

In een variant van de vorige oplossing schrijven we de resultaten ook nog weg in de Excel-sheet (in cellen van kolom G met de namen Aantal, Totaal en Gem1_):

NB1 cel G5 heeft NIET de naam Gem1. Er bestaat al een cel met die naam, namelijk de cel in kolom GEM, rij 1. Dit wordt vaak opgelost door een underscore er voor of er achter te zetten.

NB2 doordat er cellen in de sheet worden aangepast gaat Excel alle formules doorrekenen; dus alle bedragen in Bedr veranderen NADAT de VBA-berekening is uitgevoerd.

Effect van groter bestand op de berekeningen

Door Tabel1 uit te breiden naar bijvoorbeeld 100.000 regels is het verschil in benodigde tijd beter te beoordelen. Trek daartoe de vulgreep van de tabel rechtsonder naar beneden.

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.

EK-2016: wie wordt kampioen?

13 juni 2016Excel, VBA (Visual Basic for Applications)Aselect, CODE, CSE, Grootste, Index, Namen, Teken, Vergelijken, VERT.ZOEKENGijs Verbruggen

Er 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.
O 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.
Voor 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):

in de cellen X4 t/m X9 worden de punten van de landen opgehaald die derde zijn geworden in de 6 groepen
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
in de cellen Y12 t/m Y15 worden dan met behulp van de functies INDEX en VERGELIJKEN de corresponderende groepsletters opgehaald
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).
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.

Eerbetoon aan Rosling cs

20 november 2015Excel, VBA (Visual Basic for Applications)Beldiagram, Grafiek, Index, Namen, Schuifbalk, Vergelijken, VERT.ZOEKENGijs Verbruggen

Rosling

Ongetwijfeld hebt u op Internet, Youtube of op TV (DWDD of bijvoorbeeld bij Zondag met Lubach) wel eens een presentatie van Hans Rosling gezien.
Iedere keer is het weer interessant en enerverend (eigenlijk ook wel spannend) om te zien hoe hij (openbare, vrij beschikbare) gegevens weet om te zetten in informatie.
Wikipedia: Rosling stichtte de Gapminder Foundation samen met zijn zoon Ola Rosling en zijn schoondochter Anna Rosling Rönnlund. Gapminder ontwikkelde de Trendalyzer-software, die internationale statistieken omzet in bewegende, interactieve en onderhoudende grafieken.
Het doel is de promotie van een wereldvisie gebaseerd op feiten, door verhoogd gebruik en begrip van gratis toegankelijke openbare statistieken. Zijn lezingen aan de hand van Gapminder-visualisaties vielen in de prijzen doordat ze grappig en toch doodernstig zijn. De interactieve animaties zijn vrij beschikbaar op de website van de stichting (zie gapminder.org).
Bij veel van de presentaties gebruikt Rosling de Trendalyzer-software, waarmee het mogelijk is om diverse items in samenhang te tonen.
Zoals hiernaast bijvoorbeeld: op de (niet zichtbare) assen is het Inkomen per inwoner tegen de Levensverwachting uitgezet. Per land wordt dit door een bolletje weergegeven, waarbij de grootte van het bolletje wordt bepaald door het aantal inwoners van dat land. Doordat de grafiek ook nog eens een reis door de tijd kan maken, waardoor we historische ontwikkelingen kunnen zien, hebben we dus te maken met een informatie-overzicht met maar liefst 5 dimensies!

Bellendiagram

Toen ik bovenstaand voorbeeld zag, vroeg ik me af in hoeverre het mogelijk zou zijn om dit in Excel na te bouwen.
Zelf gebruik ik in rapportages zogenaamde bellendiagrammen (in het Engels bubble chart) wel eens, maar ze zijn niet echt gangbaar.
Daarom leek het me wel de moeite waard om dit idee uit te werken; ook als een soort eerbetoon aan het idealistisch te noemen werk van Rosling cs.

Basis-gegevens

Voordat we een grafiek kunnen gaan maken, moeten we natuurlijk de beschikking hebben over relevante basisgegevens.
Omdat Rosling alleen gebruik maakt van openbare bronnen is dit verzamelen niet zo moeilijk; op de site gapminder.org vinden we de nodige gegevens zelf of verwijzingen naar de bronnen.
In het Voorbeeldbestand zijn die opgenomen in de tabbladen Landen (overzicht van bijna alle landen ter wereld), Inkomen (het jaarinkomen per inwoner van de diverse landen, van 1800 tot 2015), Bevolking (het aantal inwoners per land, van 1800 tot 2015) en LevVerwachting (de levensverwachting bij geboorte per land, van 1800 tot 2015).

Inkomen
Bij sommige landen ontbraken (gedeeltes van) inkomen-gegevens. Om te zorgen dat het tekenen van de grafiek hierna niet spaak zal lopen, heb ik die gegevens aangevuld met de waarde 1 (één).

Bevolking
Het aantal inwoners per land gaat in de grafiek de belgrootte bepalen. Vandaar dat ook hier de ontbrekende gegevens aangevuld zijn met de waarde 1.

Parameters

Alle gegevens, die nodig zijn voor de besturing van ons Excel-systeem, zijn vastgelegd op het tabblad Parameters:

alle landen, die we in de grafiek willen opnemen,
het beginjaar en
eindjaar (in dit systeem 1800, respectievelijk 2015),
het jaar, waarvoor we de grafiek willen zien,
een indicator voor de snelheid van de verandering van de grafiek (overgang naar een volgend jaar) en
het opschrift van een button, die we maken om de grafiek ‘af te kunnen spelen’.

Om verwijzingen in formules overzichtelijker/leesbaarder te maken zijn aan alle parameters namen gegeven. Het snelste gaat dat op de volgende manier:

selecteer de cellen, die een naam moeten krijgen (inclusief de cellen daarboven)
kies in de menutab Formules in het blok Gedefinieerde namen de optie Maken obv selectie
zorg dat in het vervolgscherm (in dit geval) alleen het vinkje bij Bovenste rij aan staat en klik OK

NB Klik op één van de parameters (bijvoorbeeld 2015) en zie dat in het Naamvak linksboven niet meer de rij en kolom wordt weergegeven, maar de naam, die we aan de cel hebben gegeven.

Op dezelfde manier heeft de reeks landen ook een naam gekregen. Klik op het pijltje naast het Naamvak en kies Landen om dit te verifiëren.

Berekeningen

Voordat we de grafiek kunnen maken, zullen we alle benodigde gegevens bij elkaar moeten verzamelen in een vorm, die handig is om als bron voor de grafiek te fungeren.

In het tabblad Berek van het Voorbeeldbestand staat in cel B3 de formule =Landen (een verwijzing naar de landen op het tabblad Parameters). Deze formule is zo vaak naar beneden gekopieerd als noodzakelijk is om alle landen te zien.

LET OP een dergelijke verwijzing naar een zelf-gedefinieerde naam voor een bereik haalt gegevens op uit de corresponderende regel, dus in dit geval regel 3. Wil je dat niet: selecteer dan eerst alle cellen, die gevuld moeten worden; tik in =Landen en druk dan op Ctrl-Shift-Enter. Op deze manier wordt een zogenaamde matrixformule ingevoerd; Excel zet automatisch accolades rond de formule.

Per land laten we Excel de corresponderende gegevens opzoeken:

in kolom C de regio:
=VERT.ZOEKEN(B3;LandenBron;2;ONWAAR)
de inhoud van cel B3 wordt in het bereik LandenBron opgezocht. Als het land gevonden wordt, dan levert de functie het corresponderende resultaat uit de 2e kolom van het blok. Met ONWAAR geven we aan, dat we alleen tevreden zijn als de inhoud van B3 ook echt gevonden is (dus niet Benaderen).
het inkomen per land komt in kolom D:
=INDEX(Inkomen;VERGELIJKEN($B3;InkLand;0);VERGELIJKEN(KeuzeJaar;InkJaar;0))
Hoewel de inkomengegevens ook met VERT.ZOEKEN zouden kunnen worden gevonden, heb ik voor de functie Index gekozen (vind ik persoonlijk beter leesbaar en is meer universeel toepasbaar).
Met behulp van Index zoeken we in het blok Inkomen de gewenste regel en kolom op en krijgen direct het resultaat.
Maar op welke regel staat het betreffende land? Met
VERGELIJKEN($B3;InkLand;0)
is dat zo geregeld: InkLand is de naam van de reeks landen op het tabblad Inkomen. De functie Vergelijken geeft de positie van B3 in deze reeks.
Op een vergelijkbare manier wordt de juiste kolom opgezocht (het gewenste jaar staat in de parameter Keuzejaar).
LET OP de 3e parameter van de functie Vergelijken moet 0 (nul) zijn: we zoeken ook weer hier een exacte waarde, geen benadering.
de Levensverwachting en de Bevolkingsgrootte worden ook mbv de functie Index gevuld.

Grafiek

Eindelijk zijn we zover; we gaan de verzamelde informatie grafisch weergeven.

Stap voor stap (op het tabblad Graf van het Voorbeeldbestand staat het uiteindelijke resultaat):

maak een nieuw tabblad aan (bijvoorbeeld via onderaan op het scherm, op het einde van de andere tabbladen)
kies in de menutab Invoegen in het blok Grafieken de optie Overige grafieken en kies de eerste optie bij Bel
in het (lege) grafiekgebied rechts klikken en de optie Gegevens selecteren … kiezen en dan Toevoegen
in het nieuwe scherm de gegevens voor de x- en y-as en de belgrootte invullen. De reeksnaam laten we leeg; heeft bij een belgrafiek weinig nut.
Klik twee keer op OK en de grafiek is klaar!
nog wat verfraaiingen: de rasterlijnen en legenda weglaten, titels bij de assen etc.
Excel past standaard de assen automatisch aan op basis van de gegevens die gepresenteerd worden. Dat willen we niet, want als het KeuzeJaar wordt veranderd, gaat de grafiek ‘springen’.
Klik rechts op één van de cijfers van de y-as en zorg dat de Levensverwachting loopt van 10 tot 100 jaar.
Ook de x-as passen we aan: het Inkomen laten we lopen van 200 naar 100.000. Maar we zijn nog niet klaar: in het gebied met lage inkomens zitten heel veel bellen heel dicht bij elkaar, de hoge inkomens zijn uitschieters. Door deze as logaritmisch weer te geven, worden de lage inkomens duidelijker onderscheiden, terwijl de hogere inkomens ‘in elkaar schuiven’.
LET OP Een dergelijke logaritmische indeling is moeilijk leesbaar, dus alleen gebruiken als de exacte getallen niet wezenlijk zijn.
alle bellen krijgen dezelfde kleur. Handmatig is dit aan te passen, maar bij deze hoeveelheid niet echt praktisch. Met een VBA-routine zou dit (op basis van de regio) wel mogelijk zijn.
In dit geval heb ik de bel van Nederland opgezocht (aan de hand van de onderliggende cijfers) en die gekleurd en een label meegegeven.
Het jaar achter de grafiek?
Maak een tekstvak aan (via de menutab Invoegen) en tik direct in de formulebalk in =Keuzejaar. Nog wat lay-outen: lettertype en -grootte etcetera en schuif het tekstvak achter de grafiek (in het Voorbeeldbestand zijn de randen bewust zichtbaar gehouden).

Schuifbalk
Om gemakkelijk het verloop in de tijd te kunnen volgen is onder aan de grafiek een schuifbalk toegevoegd:

kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen en kies de schuifbalk (rechts naast Aa onder Formulierbestruringselementen)
’teken’ met de muis ergens op het grafiektabblad de plaats waar de schuifbalk moet komen
klik rechts op de schuifbalk en vul de diverse opties in:
Huidige waarde: laten we beginnen met 1800
Minimumwaarde: in dit geval 1800
Maximumwaarde: 2015 dus
De stappen daaronder worden 1 en 10 (1 jaar verder wanneer op het pijltje wordt geklikt, 10 jaar wanneer er in het lege gebied van de schuifbalk wordt geklikt)
Koppeling met cel: hier vullen we KeuzeJaar in; een verwijzing naar het tabblad Parameters dus.
klik OK

Mbv deze schuifbalk kunnen we nu makkelijk onze grafiek laten veranderen: de reis in de tijd kan beginnen!

Reis in de tijd
Het is natuurlijk nog mooier als we de veranderingen in de tijd automatisch kunnen laten zien.
Achter de Play-button op het tabblad Graf van het Voorbeeldbestand is een kleine VBA-routine opgenomen, die dit voor zijn rekening neemt.
Klik op Alt-F11 om de routine te bekijken. Hebt u vragen hierover? Schroom niet om contact op te nemen met G-Info.
Om de snelheid aan te passen (van 1 naar 5 sec als pauze) is nog een schuifbalk toegevoegd.