Tagarchief: Groeperen

Subtotaal

Excel, , , ,
Voorbeeldbestand: Subtotaal.xlsxDownload

Subtotalen: in de dagelijkse praktijk zijn we meestal niet alleen geïnteresseerd in totalen, maar willen we ook aantallen, bedragen etc. zien per jaar of per maand, per afdeling of per soort of ….
Subtotalen dus.

Eigenlijk vind ik, dat er maar één goede methode is om subtotalen te bepalen en dat is met behulp van draaitabellen. Bij de meeste andere methodes worden namelijk de basis-gegevens aangepast en dat druist in tegen goed Excel-gebruik.

Wanneer een (sub)totaal moet wijzigen, als er regels in de bron-data worden verborgen (maar wie wil dat nou?), alleen dan zul je de functie SUBTOTAAL moeten gebruiken.

Maar omdat het altijd goed is om meerdere alternatieve methodes te kennen, volgt hieronder een uitleg van verschillende manieren om subtotalen te genereren.

Brongegevens

In het Voorbeeldbestand op het tabblad Data staat een serie bedragen (200 regels), waarbij ieder Bedrag drie kenmerken heeft: het Jaar, de Maand en een Regio.

Het totaal van de bedragen kunnen we snel vinden door de gehele kolom te selecteren (klik op de betreffende kolom-letter, in dit geval E) en kijk rechtsonder in de statusbalk:
Afhankelijk van de Excel-versie zie je tegelijkertijd ook het gemiddelde, aantal enz. of je kunt deze oproepen door op het vinkje te klikken.

Maar wanneer je alleen het totaal van 2015 wilt weten of van de regio noord dan wordt het wat ingewikkelder: eerst sorteren op de betreffende kolom, dan alle bedragen van het jaar of regio selecteren en dan onderaan het subtotaal aflezen. Maar ondertussen hebben we iets met onze brongegevens gedaan (namelijk gesorteerd) en dat willen we niet; er kan altijd iets mis gaan bij zo’n activiteit.

En willen we het totaal van een ander jaar of andere regio dan moeten we opnieuw beginnen. Dat moet dus anders kunnen.

Draaitabel

Wat te doen?

  1. selecteer één van de cellen van de brongegevens
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. kies in het vervolgscherm OK
  4. sleep het veld Jaar naar het Rijlabels-gebied
  5. sleep het veld Bedrag naar het Waardegebied

En we hebben een overzicht van het totaalbedrag en subtotalen per jaar.

Maar als we nu toch bezig zijn, dan kunnen we dit overzicht nog wel wat aanpassen:

  1. sleep het Jaar naar het Kolomslabels-gebied
  2. sleep het veld Maand naar het Rijlabels-gebied
  3. sleep het veld Bedrag voor de tweede keer naar het Waardegebied
  4. klik op de 2e Som van Bedrag en wijzig bij Waardeveldinstellingen de Som in Gemiddeld
  5. sleep Waarden naar het Rijlabels-gebied

Dus door het simpel verslepen van velden kunnen we Excel snel diverse totalen en subtotalen laten bepalen.

Filter

De eerste alternatieve methode, die me invalt, is het gebruik van filters:

  1. selecteer een cel in het bronbestand
  2. kies in de menutab Gegevens in het blok Sorteren en filteren de optie Filter
  3. maak via de ‘vinkjes’ in de koppen de gewenste selectie
  4. selecteer alle overblijvende bedragen en onder in de statusbalk komt de som (en/of gemiddelde enzovoort) tevoorschijn

Zie het tabblad Filter in het Voorbeeldbestand; zoals daar te zien is trekken de functies SOM, GEMIDDELDE etc. zich niets aan van een filtering.
Dus dit is geen structurele oplossing.

SOM.ALS

Met behulp van ALS-formules kunnen wel subtotalen bepaald worden. In cel E3 in het tabblad Als van het Voorbeeldbestand staat de volgende formule: =SOM.ALS(B7:B206;E2;E7:E206)

Dit betekent, dat als er in het bereik B7:B206 de waarde uit cel E2 staat (in dit geval 2015) dan mag de overeenkomende waarde uit kolom E meegeteld worden.

Hetzelfde idee gaat op voor Aantal, Gemiddelde etc.

LET OP ook deze formules trekken zich niets aan van een mogelijke filtering van de brongegevens.

Wil je een iets complexer subtotaal (bijvoorbeeld van 1 jaar slechts het totaal van 1 maand) dan komt de volgende formule in beeld: =SOMMEN.ALS(E7:E206;B7:B206;E2;C7:C206;F2)

NB misschien wat verwarrend, maar de volgorde van de parameters is net wat anders; zie ook het artikel Tellen-met-voorwaarden.

Excel-tabel

De vorige ALS-formules werken nog makkelijker met een Excel-tabel (zie het tabblad AlsTabel in het Voorbeeldbestand):

  1. selecteer een cel in het bronbestand
  2. kies in de menutab Invoegen in het blok Tabellen de optie Tabel
  3. zorg dat het vinkje aanstaat bij kopregel en klik op OK

De formule om het subtotaal voor 2015/feb te bepalen wordt dan:

=SOMMEN.ALS(tblData[Bedrag];tblData[Jaar];JrSel;tblData[Maand];MndSel)

Hierbij hebben de invoercellen voor het jaar en de maand de namen JrSel, respectievelijk MndSel gekregen.

Ook hier geldt weer, dat filteren in de tabel geen invloed heeft op de formules (al zou het voorbeeld hierboven anders doen vermoeden; wijzig de filtering maar eens!).

Maar de Excel-tabel kent wel een totaliseer-optie, die rekening houdt met filtering:

  1. selecteer een cel in de Excel-tabel
  2. kies in de nieuwe menutab Hulpmiddelen voor tabellen/Ontwerpen  in het blok Opties voor tabelstijlen de optie Totaalrij.

In de cel in de onderste regel in de Bedrag-kolom komt nu automatisch de formule =SUBTOTAAL(109;[Bedrag]); het totaal van de gefilterde bedragen.

In de cel daarvoor is ‘handmatig’ de formule =SUBTOTAAL(101;[Bedrag]) geplaatst; deze zorgt voor het gemiddelde van de zichtbare bedragen (zie hierna voor de betekenis van de codes 109 en 101).

NB probeer het effect uit van de dubbele vinkjes in de onderste regel van de tabel.

SUBTOTAAL

De subtotaal-functionaliteit hoeft niet beperkt te blijven tot Excel-tabellen.
Nee, hebt u een database met gegevens dan kan Excel ook op de volgende manier een subtotalen-overzicht genereren:

  1. selecteer een cel in het bronbestand
  2. kies in de menutab Gegevens in het blok Overzicht de optie Subtotaal
  3. in het vervolgscherm kan worden aangegeven voor welk veld er subtotalen moeten komen (Bij iedere wijziging in), u kunt aangeven of u de som of gemiddelde wilt zien (of nog wat anders) en van welk veld u de som wilt zien.
  4. de drie onderste vinkjes spreken voor zich
  5. en klik op OK

Het resultaat is een brij van details en totalen (zie het tabblad Subtotaal in het Voorbeeldbestand).

LET OP Bij het gebruik van deze functionaliteit is het van groot belang, dat de bron-gegevens gesorteerd staan! En wel op de kolom, waarvan de subtotalen bepaald moeten worden.

In het tabblad SubTot2 zijn de gegevens eerste gesorteerd op Jaar en pas toen zijn op bovenstaande manier subtotalen bepaald.

Excel groepeert automatisch alle bij elkaar behorende regels; gebruik de + en – knoppen om meer of minder details te zien. Ook kun je gebruik maken van de cijfers linksboven (in dit geval 1, 2 en 3; er zijn drie niveau’s: totaal, subtotaal per jaar en detail).

NB als de sortering op meer dan 1 kolom is doorgevoerd dan kunnen ook op meerdere niveaus subtotalen worden bepaald. Als bijvoorbeeld binnen het jaar ook op de maand is gesorteerd, dan kunnen eerst subtotalen voor het maandniveau bepaald worden en daarna op jaar-niveau (vergeet niet het vinkje uit te zetten bij Huidige subtotalen vervangen!).

In cel E71 heeft Excel automatisch de formule =SUBTOTAAL(9;E3:E70) geplaatst. De formules voor de andere jaren zijn vergelijkbaar. Voor het totaal in E206 staat echter de formule =SUBTOTAAL(9;E3:E204).
Excel laat tussenliggende subtotalen dus automatisch buiten de berekening!

Ook kunnen meerdere soorten subtotalen onder elkaar geplaatst worden; zie het tabblad SubTot3 in het Voorbeeldbestand.

Ook nu is het zaak om niet te vergeten het vinkje weg te halen bij Huidige subtotalen vervangen.

SUBTOTAAL 2

Maar de functie Subtotaal kunt u ook zelf overal in een sheet plaatsen.
De functie kent in principe 2 parameters: de eerste (het functiegetal) geeft aan welke bewerking moet worden uitgevoerd (som, gemiddelde, aantal) en de tweede welk bereik bij de berekening moet worden meegenomen.
Excel kent 11 soorten berekeningen (zie het tabblad SubTot4a in het Voorbeeldbestand), waarvan het bepalen van het gemiddelde (functiegetal=1), aantal (functiegetal=2) en som (functiegetal=9) de meest gebruikte zijn.

Het functiegetal kan ook 100 groter gekozen worden; het verschil zit hem er in, dat in het tweede geval verborgen regels niet meetellen!

Bekijk op het tabblad SubTot4a het effect van het wijzigen van het functiegetal in regel 40.

Filteren op een of meerdere kolommen heeft op allebei de soorten functiegetallen hetzelfde effect: uitgefilterde waarden worden niet meegeteld.
Het groeperen van regels heeft voor de subtotalen hetzelfde resultaat als het verbergen van regels.
Bekijk het effect van de 3 bewerkingen (filteren, groeperen en verbergen) in het voorbeeld op het tabblad SubTot4b.

LET OP Blijkbaar is dit zo ingewikkeld dat Excel bij het aan- en uitzetten van bewerkingen af en toe de verkeerde resultaten oplevert!!

NB het groeperen van regels heeft verreweg de voorkeur boven het verbergen van regels. In het tweede geval komt het nogal eens voor dat je niet in de gaten hebt dat er regels ‘weg zijn’.

 

Groeperen in een draaitabel

Excel, ,

Hoewel steeds meer Excel-gebruikers weten, dat draaitabellen ontzettend handig zijn bij de analyse van gegevens, het maken van overzichten etc. en deze mogelijkheden ook in hun dagelijkse werk gebruiken, is het groeperen binnen draaitabellen nog minder bekend: deze keer dus de focus op Groeperen in draaitabellen.

Oplettende lezers hebben ongetwijfeld gezien, dat ik dit in mijn vorige artikel (Top-2000 en draaitabellen) heb gebruikt zonder verdere uitleg.

Basis

Om een draaitabel te kunnen maken hebben we wat basisgegevens nodig. Ik heb in het Voorbeeldbestand op het tabblad Basis de fictieve productie van een simpele winkel gezet: een datum, het artikel en het verkochte aantal (soms dubbel).
Uiteraard (zie Kunst en Excel; 10 voordelen van Tabellen) zijn die gegevens in de vorm van een Tabel opgeslagen (met als naam tblVerkopen).
Ze waren eerst niet gesorteerd, maar om gemakkelijk de resultaten van de draaitabellen te beoordelen, heb ik dat nu wel even gedaan (klik op het vinkje achter Datum).

Overzicht per dag

Laten we snel even een overzicht maken van deze gegevens:

  1. Plaats de cursor ergens in de tabel tblVerkopen
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. in het vervolgscherm staat alles goed (Excel heeft automatisch de hele tabel als bron gekozen en het resultaat moet op een nieuw werkblad komen), dus klik op OK
  4. op het nieuwe werkblad krijgt u aan de rechterkant de mogelijkheid om de draaitabel in te delen zoals u wilt: sleep Datum naar de Rijlabels, Art naar de Kolomlabels en Aantal naar het waardengebied
  5. eigenlijk is de draaitabel klaar, maar ik vind de standaard-layout niet handig, dus heb ik in de menutab Hulpmiddelen voor draaitabellen de tab Ontwerpen gekozen en bij Rapportindeling de optie Tabelweergave.

Het resultaat staat op het tabblad Draai van het Voorbeeldbestand: een overzicht van de totale verkoopaantallen per dag en gesplitst naar artikel.

Overzicht per maand

Vaak willen we in rapportages niet alle details zien, maar bijvoorbeeld in plaats van dag-resultaten de verkopen per maand.

In dit geval zou je de basisgegevens daartoe kunnen uitbreiden met een zogenaamde hulp-kolom waarin met behulp van de functie MAAND de maand wordt vastgelegd. Als we ook nog het jaar willen weten, moeten we nog een extra hulp-kolom toevoegen (met daarin de functie JAAR).

Maar die hulpkolommen zijn niet nodig:

  1. klik met de rechtermuisknop op één van de datums in de hiervoor gemaakte draaitabel
  2. het zogenaamde context-gevoelige submenu komt dan tevoorschijn. Kies daar de optie Groeperen
  3. Excel heeft de kolom, waarin geklikt is, geanalyseerd en heeft gezien dat dit allemaal datums zijn.
    We willen een maand-overzicht dus het voorstel van Excel kunnen we overnemen; klik op OK

LET OP als in de basisgegevens ergens in de kolom Datum een cel niet is gevuld of gevuld met een niet-bestaande datum of een getal of een tekst, dan zal het Groeperen-scherm er anders uitzien. De datum-opties zijn dan niet beschikbaar.
Nog erger: ook al corrigeert u de basisgegevens, Excel zal deze kolom nooit meer als een datum ‘zien’. Er zit dan niets anders op dan het werkblad met de draaitabel te verwijderen en het overzicht opnieuw te maken!

NB In het blok Lijst met draaitabelvelden aan de rechterkant van het scherm staat bij de te kiezen velden nog steeds Datum, maar Excel zal in de draaitabel alleen nog maar de maanden laten zien.

Overzicht per maand en jaar

Het overzicht per maand is in dit geval prima omdat Basis alleen maar datums uit 2016 bevat. Voegen we echter onderaan (of tussenin) de volgende regel toe:
1-1-2017, Servetten, 10
dan komen die in het Overzicht per maand uiteraard ook in de januari-regel tevoorschijn (wel even de draaitabel Vernieuwen door ergens rechts te klikken!).

We moeten zorgen dat de draaitabel ook per jaar groepeert:

  1. klik met de rechtermuisknop op één van de maanden in de datum-kolom van de draaitabel
  2. kies opnieuw Groeperen
  3. en klik nu ook de Jaren aan en OK

NB1 in de Lijst met draaitabelvelden is een er een nieuw veld bijgekomen, Jaren. Dit veld kunt u op dezelfde manier in de draaitabel plaatsen als andere velden. Bijvoorbeeld in het Rapportfilter zodat u overzichten per jaar kunt maken.

NB2 wanneer u een draaitabel maakt, dan legt Excel de brongegevens apart op een eigen manier in het geheugen vast. Zo ook de consequenties van groeperingen. Maakt u nu met dezelfde bron (in dit geval tblVerkopen) een andere draaitabel, dan gebruikt Excel, om geheugenruimte te sparen, dezelfde interne bron. Dus ook eventuele groeperingen zijn in de tweede draaitabel direct zichtbaar.

NB3 hebt u bij een analyse/overzicht in dezelfde draaitabel (of een andere; zie NB2) de datums zelf nodig, klik dan bij het Groeperen ook de Dagen aan.
Hebt u de dagen in het huidige overzicht niet (meer) nodig, verwijder dan het vinkje voor de Datum in Lijst met draaitabelvelden.

Overzicht per kwartaal

Ongetwijfeld hebt u ondertussen ook gezien dat u op deze manier heel gemakkelijk kwartaaloverzichten kunt maken.
In het overzicht hiernaast is de groepering per kwartaal aangezet (zoals te zien is heb ik ook de 2017-regel toegevoegd en de draaitabel vernieuwd).
Nog een paar kleine aanpassingen:

  1. met de rechtermuisknop klikken op een jaar en dan Subtotaal Jaren aanvinken
  2. met de rechtermuisknop klikken op een kwartaal en dan Subtotaal Kwartalen aanvinken
  3. de eerste drie kwartalen ‘dichtklappen’ door op het min-teken vóór het betreffende kwartaal te klikken
  4. grafiek nodig? Klik ergens in de draaitabel, kies het tabblad Opties in de menutab Hulpmiddelen voor draaitabellen en binnen het blok Extra de optie Draaigrafiek. We willen een Kolomgrafiek, dus het klikken op OK volstaat.

Artikelen groeperen

Voordat we verder gaan is het het makkelijkst om de draaitabel op het tabblad Draai van het Voorbeeldbestand eerst aan te passen:

  1. klik het vinkje voor Datum weg in de Lijst met draaitabelvelden
  2. verplaats Art van Kolomlabels naar Rijlabels

Zoals we gezien hebben weet Excel bij datums hoe hij kan groeperen; bij teksten is dat natuurlijk een stuk lastiger.
Daarom gaan we als volgt te werk:

  1. selecteer met de muis, die cellen die u wilt groeperen. In dit geval liggen die niet tegen elkaar, dus moeten we de Ctrl-toets gebruiken.
    Klik op Broodrooster
  2. hou Ctrl ingedrukt en klik op Strijkijzer
  3. klik met de rechtermuisknop op één van die 2 artikelen en kies Groeperen
  4. doe hetzelfde met Servetten en Tafelkleed
  5. verander de nieuwe Groep1 in Hard door de tekst ‘er overheen’ te tikken
  6. Groep2 wordt op die manier Soft
  7. de nieuwe kolomkop Art2 wordt ArtGroep
  8. zet nog de subtotalen voor ArtGroep aan

Nu weer een kwartaaloverzicht maken:

  1. klik het vinkje voor Art weg
  2. verplaats ArtGroep van Rijlabels naar Kolomlabels
  3. plaats Kwartalen en Jaren in de juiste volgorde in Rijlabels

Getallen groeperen

Om dit toe te lichten maken we een nieuwe draaitabel:

  1. plaats de cursor ergens in de tabel tblVerkopen
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. in het vervolgscherm staat alles goed (Excel heeft automatisch de hele tabel als bron gekozen en het resultaat moet op een nieuw werkblad komen), dus klik op OK
  4. op het nieuwe werkblad krijgt u aan de rechterkant de mogelijkheid om de draaitabel in te delen zoals u wilt: sleep Aantal naar de Rijlabels, Art naar de Kolomlabels en Datum naar het waardengebied

We zien op die manier het aantal keren, dat een bepaald aantal verkopen voorkomt, ook nog gesplitst naar artikel.

LET OP Aantal van Datum is wat misleidend: we turven niet het aantal dagen, maar het aantal keer dat het veld Datum gevuld is. We hadden ook Aantal van Art of zelfs Aantal van Aantal kunnen kiezen.

NB de basis-gegevens zijn random gevuld. Je zou dus overal exact 50 verwachten. Als ik 100.000 records had aangemaakt zou de spreiding ook minder groot zijn geweest!

Maar nu groeperen:

  1. klik met de rechtermuisknop op één van de cijfers 2, 3, 4, 5 of 6 en kies Groeperen
  2. we krijgen nu een heel ander groepeer-scherm; we kunnen een eigen indeling maken.
    Verander de 2 in 1 en de 1 achter Op: in 3.
    Ofetwel begin bij 1 en groepeer in blokken van 3.
  3. klik op OK.

Als we ook nog de artikelen groeperen naar Hard en Soft dan krijgen we het volgende overzicht:

WK-voetbal voorspeller (deel 2)

Excel, , , , ,

Spanje – Nederland: 1-5!

Nederland gestegenWie had dat verwacht; niemand toch?
Mijn vrienden, met wie ik de wedstrijd samen bekeek, begonnen te twijfelen aan de WK-voetbal voorspeller van G-Info (zie mijn blog van 5 juni).
Ik kan alleen maar ter verdediging aanvoeren, dat in mijn vorige blog ook al stond aangegeven, dat de kwaliteit van ieder Excel-model afhangt van de input en van de gehanteerde systematiek. De systematiek hou ik nog even staande (hoewel er al mensen tips voor verbetering hebben aangeleverd); de input kan iedere gebruiker zelf aanpassen (ik heb een paar ‘kleine’ aanpassingen gedaan en zie hierboven het resultaat voor de verwachtingen voor Nederland!).

Maar ik had beloofd om deze week wat meer uitleg te geven over de opzet van het spreadsheet-model; dus voor diegene, die daar nu nog behoefte aan heeft ….

Werkblad Invoer

Hier valt weinig spannends te beleven; dit blad bevat 2 tabellen, die als basis dienen voor het vervolg.WK-landen-indeling

Groepsindeling
Ik neem aan, dat de opbouw van deze tabel logisch genoeg is.
Zoals vorige keer al aangegeven, leggen we de sterkte van een land (qua voetbal!) vast via een ondergrens en een bovengrens.
Hoe kleiner het verschil tussen die twee grenzen, hoe stabieler de resultaten van het land zullen zijn en omgekeerd.

Het voorspeller-bestand is zodanig opgezet, dat deze voor een volgend toernooi opnieuw bruikbaar is. Dit alles onder de condities, dat er weer 8 groepen zijn met ieder 4 landen/ploegen en dat de 2 beste van de groepen verder gaan naar de achtste finales.
In dat geval hoeven alleen de namen van de deelnemende ploegen te worden gewijzigd in het werkblad Invoer. En uiteraard de grenzen voor de ploegen!

GroepschemaGroepschema

Met behulp van dit schema is vastgelegd in welke volgorde de groepswedstrijden worden gespeeld.
In dit geval speelt eerst ploeg 1 uit de groep tegen ploeg 2, dan 3 tegen 4, 1 tegen 3 etcetera.

Binnen dit werkblad zijn 4 namen voor groepen van cellen gedefinieerd:

  • Landen (cellen C4:C35)
  • LandKop (C3)
  • Sterkte (C4:E35)
  • GroepSchema (H3:I8)

In het vervolg van de spreadsheet wordt hier naar verwezen (meer over het gebruik van namen: zie het artikel van 31 mei 2014).

Werkblad Groepsfase

Nu begint het al wat leuker te worden! In dit blad zitten her en der wat aardige Excel-mogelijkheden ‘verstopt’.

Groepswedstrijden

Groeperen
Waarschijnlijk vallen de +-tekens aan de bovenkant en links meteen op.  Hieraan is te zien, dat er (werk)-kolommen en -rijen zijn verborgen, zodat alleen de belangrijkste onderdelen van het overzicht getoond worden.

Klik op een plus-teken en je kunt zien wat er ‘ achter’ zit; klik dan weer op het min-teken en de zaak wordt weer verborgen.
LET OP: ik heb niet de optie Kolommen of Rijen verbergen gebruikt; daarbij valt het te weinig op, dat er iets verborgen is. Nee alles is uitgevoerd met behulp van de optie Groeperen: kies aaneengesloten rijen of kolommen, die je tijdelijk niet wilt zien,  door de betreffende koppen te selecteren en ga naar de menu-optie Gegevens.  In het onderdeel Overzicht zie je de optie Groeperen.
Maar makkelijker: heb je de kolommen of rijen geselecteerd, druk dan de toetscombinatie Shift-Alt-Rechts in. Degroeperen gaat op een vergelijkbare manier (Shift-ALt-Links). Met Rechts en Links bedoel ik de cursortoets naar rechts cq. links.

Verschuiving
Nu gaan we naar wat steviger kost; kijk eens naar cel F13:

erschuivinOm deze spreadsheet nog vaker te kunnen gebruiken, moet het systeem zelf afleiden welk land op die plaats moet komen staan: het moet Land2 zijn (info in cel F1) in de 4e wedstrijd (cel C13) van de 2e groep (cel B13).

GroepschemaUit het groepschema weten we, dat de 2e ploeg in de 4e wedstrijd in iedere groep het 2e land uit die groep is.
In Excel vinden we dat met de formule:  INDEX(GroepSchema;WedNr;F1)
(zoek in Groepschema de rij op die overeenkomt met WedNr en daarbinnen de kolom, zoals vermeld in cel F1; in dit geval rij 4, kolom 2; voor meer informatie over zoeken via INDEX, zie ook het artikel Alternatief zoeken).
Ik maar hier gebruik van een heel handige eigenschap van het gebruik van Namen binnen Excel: WedNr is een reeks cellen, die loopt van C4 tot en met C51; wanneer je ergens in een cel daarnaast naar deze range verwijst via de naam (in dit voorbeeld vanuit cel F13), dan haalt Excel alleen de corresponderende cel in dezelfde rij op (dus hier C13, die de waarde 4 heeft), dus niet de hele reeks!

In het werkblad Invoer staan alle landen onder elkaar: eerst Groep A (de eerste groep), dan Groep B etc.
Om het land te vinden, dat in cel F13 moet komen, is het dus niet voldoende om te weten dat het het 2e land is maar moeten we ook nog weten in welke groep.
Het 2e land in de eerste groep staat in de landenkolom op de 2e plaats, het 2e land uit de 2e groep op 6, uit groep 3 op 10 etc.; telkens 4 verder dus.
Vandaar dat bij het hiervoor gevonden landnummer nog (GroepNr-1)*4 opgeteld moet worden om op de juiste plaats in de landenkolom terecht te komen (in dit geval gaat het om groep 2, dus komt er 4 bij en wordt het landnummer 6).

Met behulp van de functie INDEX zouden we nu in de reeks met de naam Landen kunnen zoeken, want we weten in welke rij we moeten zijn.
Ik heb deze keer voor een alternatieve manier gekozen: de functie VERSCHUIVING.

Met VERSCHUIVING geef je naast een verwijzing naar een bepaalde cel ook aan, dat je wat verder naar beneden (of naar boven!) en/of naar rechts of links wilt uitkomen.
In dit geval willen we dus vanuit de LandKop gerekend x rijen naar beneden en 0 kolommen naar links of rechts zoeken, ofwel
=VERSCHUIVING (LandKop; x; 0)

Maar de x kenden we ook al; die hebben we hiervoor afgeleid, dus de functie die we nodig hebben is:

     =VERSCHUIVING(LandKop;(GroepNr-1)*4+INDEX(GroepSchema;WedNr;E$1);0)

Nu we deze formule eenmaal hebben, kan die ook naar de overige cellen in E en F gekopieerd worden.

Wie wint een wedstrijd?
In de kolommen G en H staan de grenzen voor het eerste land uit de corresponderende rij (opgezocht m.b.v. INDEX en VERGELIJKEN; zie het artikel Alternatief zoeken), in de kolommen J en K idem voor het tweede land.

AselecttussenAls we kijken naar cel I4, dan zien we de formule =ASELECTTUSSEN(G4;H4).
In dit geval wordt er voor Brazilië willekeurig een getal gekozen tussen zijn onder- en bovengrens (deze keer leverde dat 40 op; druk je op de functietoets F9 dan zal er hoogstwaarschijnlijk een ander getal komen).

In de kolommen M en N verdelen we de punten per wedstrijd via een dubbele ALS-formule; in cel M4 is dat =ALS(I4>L4;3;ALS(I4=L4;1;0)).
Als de sterkte van Brazilië groter is dan die van Kroatië krijgen ze 3 punten, als de sterktes gelijk zijn dan 1 punt, anders 0 punten.

De volgende keer ga ik verder met de uitleg van de overige gebruikte functies en de VBA.

Voortschrijdend inzicht

Nu de eerste wedstrijden zijn gespeeld, is er misschien iets meer duidelijkheid gekomen over de sterkte van de landen. In ieder geval kennen we al wat uitslagen en kent het model dus iets minder onzekerheden.

Wat kun je nu doen:

  1. download eventueel nog een keer de WK-voetbal voorspeller
  2. als je wilt kun je op het werkblad Invoer nog wat sterktes aanpassen (Nederland toch maar gelijk maken aan Brazilië??, de sterkte van Spanje flink verlagen??)
  3. vul op het werkblad Groepsfase de bekende uitslagen in:
    Brazilië – Kroatië: 3-1; in cel I4 komt een 3 en in cel L4 een 1
    Spanje – Nederland: 1-5; in cel I10 een 1 en in cel L10 een 5
  4. vul ook de andere uitslagen in
  5. wis op het werkblad MC de vorige run(s) en laat Monte Carlo ongeveer 500 keer zijn werk doen; dat is voldoende om een goed beeld te krijgen van de mogelijke resultaten (volgens het model is de kans, dat Nederland wereldkampioen wordt door de uitslagen tot nu toe, verdubbeld!).
  6. sla het spreadsheet onder een andere naam op: WK2014 Uitslagen.xlsm