Tagarchief: Grootste

Heatmap en voorwaardelijke opmaak

Excel, ,
Voorbeeldbestand: VoorwOpmaak2.xlsxDownload

Ik was deze week op Strava een zogenaamde heatmap aan het bekijken; in dit geval een overzicht van de meest (en minst) gefietste trajecten van West-Europa.
Wat direct opvalt is dat er vooral in de buurt van de grote steden (en in Zuid-Limburg) veel gefietst wordt, maar zeker ook dat Nederland en België er flink uitspringen.
Hoe zou dat komen? Wordt er in Noord-Frankrijk en in Duitsland echt minder gefietst? Of leggen de fietsers daar hun tochten niet vast in Strava? Of …

Misschien is dat ook de reden waarom je heatmaps in de praktijk niet zo veel tegenkomt: ze roepen soms meer vragen op dan ze antwoorden geven.

Maar bij mij kwam wel direct de vraag op: hoe zou je een heatmap in Excel maken? Och ja, natuurlijk: met voorwaardelijke opmaak. Daarom in dit artikel maar eens de schijnwerper op diverse mogelijkheden van de, in Excel ingebouwde, opties.

Voorwaardelijke opmaak

Deze optie vinden we in Excel binnen de menutab Start in het blok Stijlen. Wanneer u deze optie kiest opent zich een vervolgscherm met diverse mogelijkheden. Een groot gedeelte daarvan zal hieronder de revue passeren.

Markeringsregels

Kiest u voor Markeringsregels dan ziet u diverse opties: de opmaak regelen van cellen die een waarde bevatten groter dan een bepaalde target, of juist kleiner of die tussen 2 targets in liggen etc.

In het tabblad Markering van het Voorbeeldbestand staat een opbrengst-overzicht per dag. In kolom C wordt de dagopbrengst bepaald met de formule =ASELECTTUSSEN(0;10)
oftewel een willekeurig geheel getal tussen 0 en 10 (allebei de uitersten doen mee).

Om alle cellen, die een waarde groter dan 7 bevatten, te accentueren doorlopen we de volgende stappen:

  1. selecteer alle cellen, die een opmaak moeten krijgen; in het voorbeeld de cellen C3:C33
  2. kies Voorwaardelijke opmaak/Markeringsregels/Groter dan
  3. vul in het vervolgscherm de targetwaarde in (hier 7) en kies als opmaak Groene opvulling met donkergroene tekst
  4. klik dan op OK

Om de cellen, waarvan de waarde kleiner is dan 3, te benadrukken doen we iets vergelijkbaars, alleen kiezen we in stap 2 de optie Kleiner dan.

NB door op F9 te drukken, wordt de Excel-map opnieuw berekend. Alle waarden in kolom C worden dan ververst en de opmaak zal zich daaraan aanpassen.

Voorwaardelijke opmaak aanpassen

Soms voldoet de op deze manier standaard ingestelde opmaak niet (helemaal). Volg dan de volgende stappen:

  1. selecteer één van de cellen, waarvan de opmaak gewijzigd moet worden, in dit geval bijvoorbeeld C3
  2. kies Voorwaardelijke opmaak en dan de optie Regels beheren…
  3. bovenstaand scherm komt dan tevoorschijn. Standaard worden alleen de regels van de huidige selectie getoond. Dit kunt u makkelijk wijzigen in het eerste keuzevak.
  4. klik op de regel die u wilt wijzigen (of bekijken) en kies Regel bewerken
  5. afhankelijk van het soort regel kunnen nu diverse parameters ingesteld worden, inclusief de gewenste opmaak

Bij iedere regel is ook te zien voor welke cellen de regel geldt; in dit geval allebei de keren voor C3:C33.

NB Dit bereik kan hier aangepast worden. Uitermate handig als u in de eerste stap bij het instellen van opmaak vergeten bent een reeks cellen te selecteren.

Ook als er nieuwe waarden aan kolom B en C worden toegevoegd zal het bereik voor de opmaak-regels moeten worden aangepast.

Opmaak in een Excel-tabel

In de kolommen E en F van het tabblad Markering in het Voorbeeldbestand staat eenzelfde soort overzicht, maar dan als Excel-tabel (zie onder andere het artikel Kunst en Excel, ofwel de 10 voordelen van het gebruik van tabellen).
Dit heeft de volgende voordelen:

  1. selecteert u één van de cellen van de tabel en u kiest dan Regels beheren, dan zal Excel direct alle opmaakregels van die tabel tonen
  2. voegt u nieuwe gegevens onder aan de tabel toe, dan zal Excel automatisch ook de voorwaardelijke opmaak aan die cellen toevoegen

In kolom E wordt via Voorwaardelijke opmaak/Markering/Datum de datum van vandaag met een gele achtergrondkleur weergegeven.

Kolom F kent dezelfde opmaak als in het vorige overzicht.

Opmaak ten opzichte van het gemiddelde

In het tabblad Markering in het Voorbeeldbestand bevatten de kolommen H en I ook een tabel (met de naam tblOpbr2).
In cel K3 (met de naam GemOpbr) wordt de gemiddelde opbrengst bepaald.

Kolom I heeft op de volgende manier een voorwaardelijke opmaak gekregen:

  1. selecteer de cellen I3:I33
  2. kies Voorwaardelijke opmaak/Markering/Groter dan
  3. als target geven we nu niet een harde grens op, maar selecteren met de muis cel K3 of drukken op de functietoets F3 en kiezen dan de naam GemOpbr (de laatste optie is het mooiste, omdat dan duidelijker te zien is wat deze opmaakregel doet)
  4. kies de gewenste opmaak
  5. klik OK
  6. herhaal het vorige maar nu met de optie Kleiner dan

Opmaak voor unieke of dubbele waarden

Voorwaardelijke opmaak kan ook eenvoudig gebruikt worden om unieke of dubbele waarden te markeren (zie het tabblad UniekDubbel in het Voorbeeldbestand):

  1. selecteer de cellen, die een opmaak moeten krijgen (in dit geval B3:B9)
  2. kies Voorwaardelijke opmaak/Markering/Dubbele waarden
  3. kies de optie Dubbele of Unieke
  4. geef de gewenste opmaak op en klik op OK

Opmaak voor bovenste/onderste

Soms wilt u zien welke cellen de grootste of kleinste waarden bevatten. Dat gaat als volgt (zie het tabblad BovenOnder in het Voorbeeldbestand):

  1. selecteer de cellen, die een opmaak moeten krijgen
  2. kies Voorwaardelijke opmaak, dan de optie Bovenste/Onderste en vervolgens de optie Bovenste 10 items
  3. dan geeft u aan of u echt 10 items wilt opmaken; u kunt bijvoorbeeld ook voor de top-3 kiezen
  4. kies de gewenste opmaak en klik op OK

NB1 u ziet ook de optie Bovenste 10%; de 10 is weer apart in te stellen. Excel zal dan zelf, aan de hand van het geselecteerde bereik, bepalen hoeveel cellen er ‘gekleurd’ moeten worden.

NB2 uiteraard kunt u ook de cellen met de laagste waarden accentueren (Onderste in plaats van Bovenste).

Opmaak ten opzichte van het gemiddelde 2

Hiervoor hebben we via Markering een methode laten zien om cellen boven of onder het gemiddelde op te maken. Excel kent standaard een alternatief (zie tabblad BovenOnder van het Voorbeeldbestand, kolommen E en F):

  1. selecteer de cellen, die een opmaak moeten krijgen
  2. kies Voorwaardelijke opmaak, dan de optie Bovenste/Onderste en vervolgens de optie Boven gemiddelde
  3. geef een opmaak op en klik op OK

NB nadeel van deze methode is, dat u nergens zelf het gemiddelde ziet.

Bovenste/onderste interactief

Soms hebben we inzicht nodig in de hoogste 5, een andere keer willen we de laagste 5 (of een ander aantal) zien (zie tabblad BovenOnder van het Voorbeeldbestand, kolommen H en I). Hier voorziet Excel niet standaard in.

  1. leg ergens vast hoeveel cellen er ‘gekleurd’ moeten worden; in het voorbeeld gebruiken we cel L2 daarvoor met de naam BovOnderAantal
  2. plaats in een cel de tekst Boven of Onder; in het voorbeeld cel L3 met de naam BovOnder. Via gegevensvalidatie kan uit die 2 woorden worden gekozen en is de invoer beperkt. 
  3. selecteer de cellen, die een opmaak moeten krijgen (beginnend bij I3)
  4. kies Voorwaardelijke opmaak, dan de optie Nieuwe regel en daarna de optie Een formule gebruiken
  5. plaats dan de volgende formule =EN(BovOnder=”Boven”; I3>=GROOTSTE($I$3:$I$33;BovOnderAantal))
  6. geef een gewenste opmaak en klik OK

De formule levert alleen als waarde WAAR (en dan zal de opmaak worden doorgevoerd) als aan allebei de voorwaarden wordt voldaan: cel L2 moet de waarde Boven bevatten EN de waarde in cel I3 moet groter of gelijk zijn aan de grootste waarde in het bereik van geselecteerde cellen (als BovOnderAantal gelijk is aan 1, aan de één na grootste als BovOnderAantal gelijk is aan 2 etcetera).

LET OP bij het maken van een formule voor voorwaardelijke opmaak met relatieve verwijzingen.
In dit geval bevat de formule I3, omdat dat het startpunt van onze selectie was.
Selecteer cel I4 en bekijk nu de regel die bij de voorwaardelijke opmaak hoort; Excel heeft de relatieve verwijzing in de formule automatisch aangepast.

NB de andere gebruikte formule (in het geval dat we de laagste waarden zoeken):
=EN(BovOnder=”Onder”;I3<=KLEINSTE($I$3:$I$33;BovOnderAantal))

Gegevensbalken

Sommige mensen willen getallen zien in overzichten, anderen zijn meer grafisch ingesteld.

Gegevensbalken als voorwaardelijke opmaak zijn in dit geval een uitkomst. Hierboven (zie het tabblad GegBalk in het Voorbeeldbestand) zijn per kolom de waarden ‘vertaald’ naar kleine grafiekjes:

  1. selecteer de cellen van de Uit-kolom. Wijs daartoe met de muis de bovenrand van de Uit-cel aan; de cursor wordt dan een pijl-naar-beneden. Klik dan met de muis.
  2. kies Voorwaardelijke opmaak en dan de optie Gegevensbalken. In dit geval kiezen we de derde kleurovergang (Rood)
  3. om de kleuren-balk en de getallen te scheiden: kies opnieuw Voorwaardelijke opmaak, maar dan de optie Regels beheren en Regel bewerken. Klik op de knop Negatieve waarden en as en zorg dat bij Asinstellingen de optie Middelpunt is geselecteerd.
  4. voor de In-kolom geldt een vergelijkbare procedure
  5. de Result– en ResCum-kolom zijn iets ingewikkelder: in eerste instantie kiezen we voor een Groene gegevensbalk. Daarna moet er nog een aanpassing doorgevoerd worden: kies bij Regel bewerken/Negatieve waarden en as een Rode opvulkleur (ook voor de rand van de balk). Zet meteen ook Middelpunt aan.

Gegevensbalken 2

Bijna hetzelfde overzicht als hiervoor (wel heb ik de kolom met cumulatieve resultaten weggelaten; zie het tabblad GegBalk in het Voorbeeldbestand, bereik B17:E29).
Het verschil zit hem er in dat de grootte van de gegevensbalken nu relatief ten opzichte van de totale tabel bepaald zijn en niet per kolom.

LET OP zoals uit de diverse voorbeelden mag blijken, moeten de overzichten wel altijd van een goede legenda worden voorzien anders kan de lezer snel de verkeerde conclusies trekken.

Gegevensbalken 3

Wanneer de exacte details van de in- en uitgaande stromen niet van belang zijn kunt u er ook voor kiezen om alleen de gegevensbalken te laten zien (zie het tabblad GegBalk in het Voorbeeldbestand, bereik H2:K14):

  1. selecteer één van de cellen waarvan de opmaak moet worden gewijzigd
  2. kies Voorwaardelijke opmaak/Regels beheren
  3. selecteer de betreffende regel en klik op Regel bewerken
  4. plaats een vinkje voor de optie Alleen balk weergeven

Gegevensbalken 4

Gegevensbalken zijn ook toepasbaar in draaitabellen (zie het tabblad GegBalkDraai in het Voorbeeldbestand).

LET OP wanneer er aan de brongegevens regels worden toegevoegd dan dient de draaitabel te worden vernieuwd. Komt er in de draaitabel dan ook een nieuwe regel bij, dan zal de opmaak niet automatisch worden doorgevoerd. Dit kunt u als volgt oplossen:

  1. selecteer in de draaitabel één van de cellen waarvan de opmaak moet worden gewijzigd
  2. kies Voorwaardelijke opmaak/Regels beheren
  3. selecteer de betreffende regel en klik op Regel bewerken
  4. kies één van de 2 opties, die beginnen met Alle cellen

Heatmap

Dan nu nog even over Heatmaps (de aanleiding voor dit artikel).

Hiernaast worden de grootste getallen groen gekleurd en de kleinste rood (zie het tabblad Kleuren in het Voorbeeldbestand).
De getallen niet in de buurt van de extremen krijgen een tussenschakering.

We gaan als volgt te werk:

  1. selecteer alle cellen, die bij de heatmap betrokken moeten worden
  2. kies Voorwaardelijke opmaak en dan de optie Kleurenschalen. In dit geval kiezen we de eerste kleurovergang (groen-geel-rood)

Maar we kunnen de standaardinstellingen van Excel nog aanpassen.
Wanneer we alles onder de 30 te weinig vinden en alles boven 90 prima, dan passen we de regel als volgt aan (zie het tabblad Kleuren in het Voorbeeldbestand, bereik I3:L14):

Kies Voorwaardelijke opmaak/Regels beheren, selecteer de betreffende regel en klik op Regel bewerken. Stel de opties in zoals hieronder weergegeven.

Nog meer opmaak

Voor diegene die de smaak te pakken hebben gekregen: het Voorbeeldbestand bevat nog diverse andere voorbeelden van opmaak. Met het bovenstaande in het achterhoofd moeten de daarbij gemaakte keuzes duidelijk zijn.

Top-5; verschillende methodes

Excel, , , , , ,
Voorbeeldbestand: Top_5.xlsxDownload

Het komt regelmatig voor, dat je een ranking wilt aanbrengen in je gegevens: welke producten verkopen het beste, in welke maanden hebben we het beste resultaat gehaald, bij welke productiestraten is het minste uitval.
In dit artikel zal ik diverse methoden de revue laten passeren, waarmee dat mogelijk is, met hun voor- en nadelen. Het maakt dan niet uit of het over de beste 3 gaat, de hoogste 5 scores of de slechtste 10.

Methode 1: easy does it!

Stel je hebt een overzicht van verkochte aantallen per maand en je wilt weten welke maand het beste is geweest?

Sorteer op Aantal en je bent klaar!

Voordeel: heel snel resultaat.

Deze methode kent echter een paar nadelen:

  1. je past op deze manier de bron-gegevens aan en dat druist in tegen regel 1 van goed Excel-gebruik.
  2. wijzigen de gegevens of komen er maanden bij, dan moet de sortering opnieuw worden doorgevoerd
  3. resultaten moeten ‘met de hand’ overgenomen worden in een rapportage

Methode 2:  maak een grafiek

In het tabblad Top5 van het Voorbeeldbestand zijn de gegevens uitgezet in een grafiek; ik heb als type een Spreidingsgrafiek gekozen, zodat de datums op een juiste tijdschaal op de as worden weergegeven en niet ‘gewoon’ achter elkaar (wijzig de laatste datum maar eens in 1-12-18).
Ga met de muis naar de hoogste waarde en Excel zal de onderliggende gegevens van het punt van de grafiek laten zien.

Voordeel: snel resultaat, waarbij goed is te zien waar de hoogste (of laagste) resultaten zitten, wat (globaal) de verschillen zijn en of er veel vergelijkbare resultaten zijn. In het voorbeeld zijn er zes  resultaten boven de 15 en nog drie anderen er vlak bij; of een top-3 (of top-5) hier veel zegt?

Nadeel: resultaten moeten ‘met de hand’ opgezocht en overgenomen worden in een rapportage.

NB wil je kijken wat er met de grafiek gebeurt als je andere brongegevens hebt, kopieer dan de cellen uit kolom D en plak ze ‘hard’ in kolom C (via Plakken speciaal/Waarden).
In de kolom Random worden door Excel telkens nieuwe data gegenereerd mbv de formule: =ASELECTTUSSEN(1;2000)/100 ofwel een willekurig getal tussen 1 en 2000 (inclusief grenzen) en deel dat door 100, zodat een getal tussen 1 en 20 (met maximaal 2 decimalen) ontstaat.

Methode 3: gebruik een Draaitabel

  1. selecteer een willekeurige cel in de brondata; deze zijn vastgelegd in de vorm van een Excel-tabel met de naam tblData. Hoe dat moet en wat de voordelen zijn: kijk op 10 voordelen van tabellen en Tabellen (deel 2).
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel en klik op het tussenscherm op OK.
  3. sleep in Lijst met draaitabelvelden het veld Maand naar Rijlabels en het veld Aantal naar het Waardegebied
  4. klik rechts op één van de getallen in de tweede kolom en kies de optie Sorteren en dan Sorteren van hoog naar laag
  5. bijna klaar; Excel laat nu nog alle maanden zien, maar we willen alleen maar de beste 5 resultaten: klik rechts op één van de maanden, kies Filteren en dan de optie Top-tien.
    Zorg dat in het tweede veld in plaats van de standaard 10 een 5 komt, de rest is OK.

Bekijk het resultaat in het tabblad Top5 van het Voorbeeldbestand.

Voordeel: de resultaat-tabel kan zo in een standaard-rapportage worden overgenomen.

Nadeel: wijzigen de gegevens of komen er nieuwe maanden bij? Vergeet niet de draaitabel te Vernieuwen (door ergens in de tabel rechts te klikken).

NB1 doordat de gegevens in een Excel-tabel zijn vastgelegd, ‘weet’ Excel wanneer er nieuwe regels zijn toegevoegd, dus de bron van de draaitabel hoeft niet meer aangepast te worden.

NB2 het Top-10-filter kan ook ingesteld worden door op het blokje achter Maand te klikken. De Top-10 vindt u dan onder Waardefilters.

NB3 wilt u niet de 5 bovenste gegevens maar de onderste, wijzig dan Top in Onder.

Optie 2 van het Top-10-filter

Dit Top-10-filter kent nog 2 andere opties. Wanneer u in plaats van Items kiest voor Procent dan zal Excel die maanden laten zien, die er samen voor zorgen, dat het ingestelde percentage minimaal wordt bereikt.

20% van 239,72 (het totaal Aantal uit het tabblad Top5 van het Voorbeeldbestand) is 47,944, dus aug-17 is nog nodig om dit Totaal te bereiken.

Optie 3 van het Top-10-filter

De derde mogelijkheid is om een harde grens meet te geven; in dit voorbeeld willen we die maanden zien die samen minimaal 100 opleveren.

Methode 4: gebruik de functie GROOTSTE

De functie GROOTSTE kent 2 parameters:
* de Matrix (bereik), waarin het grootste getal moet worden gezocht
* K,  het volgnummer; wil je de grootste waarde dan is K=1, wil je de één na grootste dan is K=2 etc.

Dus de formule in cel K5
=GROOTSTE(tblData[Aantal];I5)
haalt uit de kolom Aantal van de tabel tblData het grootste getal op (cel I5 is gelijk aan 1).

Deze formule is naar beneden gekopieerd; zie het tabblad Top5 in het Voorbeeldbestand.

Nu moet nog kolom J met de bijbehorende maand gevuld worden. In cel J5 staat daartoe de volgende formule:
=INDEX(tblData[Maand];VERGELIJKEN(K5;tblData[Aantal];0))
De functie Index zoekt in de kolom Maand van de tabel tblData die rij op, die overeenkomt met het resultaat van de functie Vergelijken; deze functie beoordeelt op welke positie de inhoud van cel K5 staat in de kolom Aantal van de tabel tblData. De 0 geeft aan dat er een exacte match moet zijn (zie ook Alternatief voor vert.zoeken en Zoeken: Index en Vergelijken).

Voordelen: de resultaat-tabel kan zo in een standaard-rapportage worden overgenomen en de tabel past zich automatisch aan aan wijzigingen in de brongegevens en hoeft dus niet vernieuwd te worden zoals bij een draaitabel.

Nadelen: ‘ingewikkelde’ formules nodig en methode werkt niet altijd goed als getallen in de kolom Aantal gelijk  zijn.

NB1 de resultaten van de GROOTSTE-tabel kunnen toegevoegd worden aan de grafiek.

NB2 wilt u niet de top-gegevens achterhalen maar de onderste, gebruik dan de functie KLEINSTE.

Methode 4: gebruik de functie GROOTSTE (bis)

Op het tabblad Top5_2 van het Voorbeeldbestand staat een nieuw databestand, waarin ook dubbele aantallen voorkomen.

Maken we hierop een draaitabel met een Top-5, dan lost Excel het probleem voor ons simpel op: hij maakt automatisch een Top-6!

Er is natuurlijk wel een oplossing om het tweede nadeel van de vorige methode op te vangen.
Het opzoeken van het grootste aantal (en de één na grootste etc) is ook hier niet het probleem (zie kolom J), maar wel het opzoeken van de daarbij behorende maand (het Aantal 16 kan horen bij jun-16, okt-16 en jun-17).

Ieder resultaat van de functie GROOTSTE krijgt in kolom K een SubNr mee. In cel K4 staat daartoe de formule:
=AANTAL.ALS($J$4:J4;J4)
Hiermee wordt het aantal keren geturfd, dat de waarde van cel J4 (de laatste parameter) voorkomt in het bereik $J$4:J4. Tsja, dat is natuurlijk altijd 1!
Maar wat gebeurt er als we de formule naar beneden kopiëren? In cel K5 komt dan automatisch =AANTAL.ALS($J$4:J5;J5): er wordt gekeken hoe vaak J5 voorkomt in het bereik van J4 tot en met J5! Op deze manier krijgen dubbelen ieder een  eigen volgnummer.

Het opzoeken van de corresponderende maand is een uitdaging. In cel L4 staat de formule:
={INDIRECT(“B”&KLEINSTE(((tblData2[Aantal]=J4)*RIJ(tblData2[Aantal]))+((tblData2[Aantal]<>J4)*10^8);K4))}

OEPS! Met dank aan Chandoo heb ik dit alternatief gevonden. Probeer de formule te begrijpen door in de menutab Formules in het blok Formules controleren de optie Formules evalueren te kiezen:

  1. Eerst zoeken we alle aantallen, die  gelijk zijn aan J4 (tblData2[Aantal]=J4); dit levert een reeks op met Waar en Onwaar
  2. deze reeks vermenigvuldigen we met de overeenkomende rijnummers (*RIJ(tblData2[Aantal])), waardoor we een reeks overhouden met rijnummers, waarin J4 voorkomt, en nullen
  3. als J4 NIET in een rij voorkomt, dan tellen we daar een groot getal (1 met 8 nullen) bij op (+((tblData2[Aantal]<>J4)*10^8))
  4. dan nemen we de kleinste (of één na kleinste etc.; afhankelijk van K4) van die reeks (KLEINSTE)
  5. als laatste wordt met INDEX de waarde in die rij in kolom B opgehaald.

Komt u er niet uit? Neem contact op met G-Info.

LET OP de formule in L4 is ingevoerd door op Ctrl-Shift-Enter te drukken (CSE-methode); het is een zogenaamde matrix- of array-formule. De formule kan wel gewoon naar beneden gekopieerd worden.
Zie voor meer uitleg over de gehanteerde methode het artikel SOMPRODUCT: meer dan SOM en PRODUCT. Ook de voorbeelden uit de werkmap, die Ton Spies mij toestuurde, kunnen hiervoor gebruikt worden.

Voordelen: de resultaat-tabel kan zo in een standaard-rapportage worden overgenomen en de tabel past zich automatisch aan aan wijzigingen in de brongegevens en hoeft dus niet vernieuwd te worden zoals bij een draaitabel.

Nadeel: zeer ‘interessante’ formules zijn nodig.

 

EK-2016: wie wordt kampioen?

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

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

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

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

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

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

Indeling achtste finales

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

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

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

Verwerken resultaten

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

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

Temperaturen en thermometer

Excel, , , , , , ,

temp1Het 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 temp2hebben 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

temp3Voordat 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”:

  1. selecteer alle cellen met temperatuurgegevens (in het tabblad KNMI de cellen B17:B13120)
  2. temp4kies in de menutab Gegevens de optie Tekst naar kolommen
  3. er opent zich een nieuw scherm: kies daar de optie Gescheiden en klik op de knop Volgende
  4. zorg dat in het volgende scherm bij Scheidingstekens ook Komma staat aangevinkt en klik op de knop Voltooien
  5. 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:

  1. klik ergens in een lege cel de waarde 10 in
  2. kopieer deze cel (bijvoorbeeld via Ctrl-C)
  3. selecteer alle temperatuurcellen
  4. klik met de rechtermuisknop ergens in de selectie
  5. kies de optie Plakken speciaal …
  6. kies bij Bewerking de optie Delen
  7. klik op de knop OK
  8. 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).

temp5Maak 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:

  1. 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.
  2. 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
    * temp6kies 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.

temp1Het 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.
temp7Daar gaat ie (zie tabblad Thermometer in het Voorbeeldbestand):

  1. 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
  2. temp8in 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.
  3. 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.
  4. 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.
  5. temp2in 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.
  6. 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.
  7. 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??

Loterij

Excel, , , , , , , , ,

loterij2Vorige week kreeg ik de vraag, hoe je met Excel het makkelijkst de winnaars van een wedstrijd zou kunnen selecteren.
De bedoeling was om uit een (grote) hoeveelheid goede inzenders willekeurig drie personen er uit te lichten.

Een poosje geleden heb ik al iets geschreven over steekproeven; dezelfde systematiek is ook voor dit probleem toepasbaar.
Maar deze keer een iets andere benadering.

Loterij1

loterijIn het Voorbeeldbestand heb ik in het tabblad Loterij een overzicht opgenomen van alle verkochte loten, inclusief de corresponderende naam (in dit geval de lotnummers 1 t/m 100 en een fictieve naam, die daarvan is afgeleid; bijvoorbeeld bij het eerste lot hoort Naam 1).
NB in plaats van lotnummers kan dit overzicht ook alle goede inzenders van een wedstrijd voorstellen

loterij2Op het tabblad Uitslag worden 3 winnaars geselecteerd:

  1. Allereerst tellen we het aantal verkochte loten (of dus het aantal goede inzenders). In cel C2 staat daartoe de formule:
    =AANTAL(Loterij!B:B)
    Ofwel tel het aantal getallen in kolom B van het tabblad Loterij.
    LET OP zijn in kolom B geen nummers opgenomen maar teksten (bijvoorbeeld A1, A2, B1 etc) dan moet u de formule AANTALARG gebruiken en van het resultaat 1 aftrekken, omdat dan ook het woord LotNr in cel B2 wordt meegeteld
  2. uit het aantal verkochte loten worden dan 3 willekeurige getallen getrokken (cellen C5, C6 en C7):
    =ASELECTTUSSEN(1;$C$2)
    Dus neem een willekeurig getal tussen 1 en de waarde in C2 (in het voorbeeld dus 100); de grenzen doen ook mee.
  3. in cel D5 zoeken we dan de corresponderende naam op:
    =VERT.ZOEKEN(C5;Loterij!B:C;2)
    Deze formule zoekt de waarde uit cel C5 op in kolom B van Loterij en geeft als resultaat de corresponderende cel uit kolom C.
    LET OP bovenstaande formule werkt alleen goed, als de lotnummers in volgorde in kolom B staan en er geen “gaten” zijn. Beter is om de formule
    =VERT.ZOEKEN(C5;Loterij!B:C;2;ONWAAR) te gebruiken; de laatste parameter zorgt er voor, dat Excel naar een exacte match gaat zoeken.
  4. de formule in D5 kan naar beneden gekopieerd worden
  5. bij iedere wijziging in de werkmap worden de Aselect-formules opnieuw berekend; dus iedere keer zullen er andere winnaars tevoorschijn komen. Dit gebeurt ook door op de functietoets F9 te drukken: herberekenen.
    Belangrijk is om van tevoren duidelijk met de “notaris” af te spreken hoe vaak er herberekend zal worden, voordat de definitieve uitslag wordt bepaald.

LET OP met bovenstaande methode is het mogelijk dat prijswinnaar 2 en/of 3 gelijk is aan prijswinnaar 1. Dat is natuurlijk niet de bedoeling. Druk dan nog een keer op F9.

NB worden er aan de lijst in het tabblad Loterij nummers en namen toegevoegd of worden er verwijderd, dan zullen de resultaten in Uitslag direct daaraan worden aangepast; we kijken immers naar alle rijen in de kolommen B en C.

Loterij2

Het Voorbeeldbestand bevat ook een tabblad Loterij2; deze is vergelijkbaar met de eerste, maar is in de vorm van een Excel-tabel opgevoerd. Een groot voordeel hiervan is dat we niet alle cellen uit de kolommen B en C hoeven mee te nemen in de formules: wanneer de tabel wordt uitgebreid of verkleind dan zullen alle corresponderende formules zich automatisch daaraan aanpassen.

loterij3De formule in C2 ziet er anders uit:
=AANTAL(LoterijOvz2[LotNr])
Tel het aantal nummers in de kolom met als kopje LotNr uit de tabel LoterijOvz2 (de tabel uit het tabblad Loterij2).

De rest van kolom C is hetzelfde. Maar in kolom D gebruiken we geen VERT.ZOEKEN maar de functie INDEX. In cel D5 komt dan de formule =INDEX(LoterijOvz2[Naam];C5).
Haal in de kolom met als kopje Naam uit de tabel LoterijOvz2 de waarde op in die regel, die overeenkomt  met de waarde in C5.

NB aanpassingen aan de tabel in Loterij2 worden automatisch meegenomen in de resultaten.

LET OP ook hier kan het nodig zijn om een keer extra op F9 te drukken om geen dubbele prijswinnaars te krijgen.

Loterij3

loterij4In het tabblad Loterij3 van het Voorbeeldbestand is in de Excel-tabel een extra kolom opgenomen, waarin iedere regel van een willekeurig getal tussen 0 en 1 wordt voorzien dmv de formule =ASELECT().
De kans, dat hier dubbele getallen in voorkomen, is heel erg klein.

NB deze functie kent geen parameters, maar, zoals achter iedere functie, dienen er wel 2 haakjes te staan (openen en sluiten).

loterij5De bepaling van de winnaars gaat nu iets anders: in cel C3 wordt de eerste winnaar bepaald door het grootste getal (MAX) in de kolom Aselect van de tabel LoterijOvz3 op te zoeken. In D3 wordt met VERT.ZOEKEN het corresponderende lotnummer gevonden en in E3 met INDEX de naam (zoals uit de formules van kolom F blijkt kunnen we dat laatste ook met VERT.ZOEKEN in de hele tabel LoterijOvz3).

Maar hoe vinden we nu de 2e- en 3e- prijswinnaars? Dan kunnen we niet meer MAX gebruiken.
In cel C4 staat dan ook een andere formule: =GROOTSTE(LoterijOvz3[Aselect];2)
ofwel zoek de tweede in grootte in  de kolom Aselect van de tabel LoterijOvz3.
U begrijpt: in plaats van MAX in cel C3 hadden we ook de functie GROOTSTE met een parameter 1 kunnen gebruiken!

NB uiteraard hadden we in dit geval de winnaars ook kunnen selecteren met de functie KLEINSTE.