Tagarchief: Kleinste

Heatmap en voorwaardelijke opmaak



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.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Verder zoeken



Zoeken in Excel blijft een belangrijke en veel gebruikte optie.
Ik heb daar dan ook al enkele keren een artikel met tips over geschreven.

Recent kreeg ik een opmerking op het artikel Alternatief voor Verticaal.zoeken:

Als er dubbele waarden of woorden in een reeks staan waar je de functie vergelijken op los laat dan is het resultaat de positie van de eerste waarde die hij vindt. Betekent dat dan dat je de functie vergelijken moet toepassen op een gegevensreeks met unieke waarden of tekens?

Mijn antwoord hield in, dat hij daar gelijk in had. En dat je dus altijd moet opletten met zoeken in Excel. En dat dit inspiratie was voor een artikel over het zoeken in niet-unieke gegevens.

Dus deze keer wat inspiratie voor uw speurwerk in Excel.

Basisgegevens

Uiteraard hebben we een voorbeeld nodig, waar we wat mee kunnen oefenen.
In het tabblad Data van het Voorbeeldbestand staat een overzicht van personen, die een betaling aan mij hebben gedaan. We hebben afgesproken, dat iedere werkdag iemand een bedrag stort (was het maar waar!).

Het overzicht begint op 1 september (cel C3. De dag van de week is via cel-opmaak zichtbaar gemaakt; druk Ctrl-1).
In de cel daaronder staat de formule:
=WERKDAG(C3;1)

Omdat de gegevens in een Excel-tabel staan (met de naam tblBetalingen) wordt deze formule automatisch in de rest van de kolom doorgevoerd; voeg maar eens een nieuwe persoon onderaan toe.

U ziet dat sommige personen 1 betaling hebben gedaan, anderen 2 en enkelen hebben er 3 gedaan.
Maar hoeveel heeft iedereen nu gestort en wanneer was de laatste betaling per persoon? Tijd voor nadere analyse.

Aantal-analyse

Allereerst moeten we een overzicht hebben wie er allemaal mee betalen:

  1. klik ergens in de tabel
  2. kies dan in de menutab Gegevens in het blok Sorteren en filteren de optie Geavanceerd
  3. in het vervolgscherm kunt u diverse zaken instellen:
    * we gaan niet filteren, maar kopiëren dus de 2e optie aanvinken
    * het Lijstbereik invullen; waar moeten de gegevens vandaan komen? Wijs met de muis de bovenrand van de eerste kolom aan tot de cursor verandert in een zwarte pijl naar beneden en klik dan
    * in het veld Kopiëren naar moet een verwijzing komen naar de eerste cel waar gegevens moeten worden weggeschreven
    * en als laatste aangeven dat we alleen unieke waarden willen overhouden
    * klik dan op OK

Er blijken tot nu toe 11 betalers te zijn; zie tabblad Ovz1 van het Voorbeeldbestand.

Het aantal betalingen per persoon is nu snel gevonden. In cel C3 staat de formule:
=AANTAL.ALS(tblBetalingen[Naam];B3)

Nog even naar beneden kopiëren, et voilà.

Twee personen hebben dus al 3 keer betaald, vier al 2 keer en 5 mensen nog maar 1 keer.

Bedrag-analyse

We gaan nu via Zoeken analyseren hoeveel iedereen heeft betaald (ja, ik weet het: mbv. een draaitabel is dat in een mum van tijd gebeurd!).

Zoals hierboven al opgemerkt kan Excel alleen maar naar unieke sleutels zoeken (dit in tegenstelling tot bijvoorbeeld Access, dat bij het zoeken naar Piet 2 verschillende records zou opleveren).

We moeten dus alle regels in Excel uniek maken; dat doen we door aan de namen een volgnummer te koppelen.
Aan de basistabel zijn daarom 2 hulpkolommen toegevoegd (zie tabblad Data in het Voorbeeldbestand):

  1. in kolom E (Hulp1) staat de formule
    =AANTAL.ALS(VERSCHUIVING(tblBetalingen[[#Kopteksten];[Naam]];1;0;RIJ()-RIJ(tblBetalingen[[#Kopteksten];[Naam]]));[@Naam])
    Turf het aantal keren, dat de Naam uit de betreffende rij voorkomt in het bereik dat door Verschuiving wordt bepaald.
    De Verschuiving is spannender:
    * het bereik begint in de cel waar de kop van de naam-kolom staat (klik bij het invullen van de formule op die cel en de verwijzing wordt automatisch gegenereerd)
    * maar start 1 regel lager
    * en 0 kolommen naar rechts of links
    * en de lengte van het bereik is gelijk aan de Rij waarin de formule staat minus de rij waar de kolomkop staat
    * in de eerste regel is het bereik dus maar 1 cel groot, in de tweede regel 2 cellen etc.
  2. in kolom F (Hulp2) staat:
    =[@Naam]&[@Hulp1]
    Ofwel: koppel de Naam uit de betreffende rij aan het volgnummer uit Hulp1

De gegevens in Hulp2 zijn nu altijd uniek, ook na uitbreiding van de tabel. Dus op die kolom kunnen we nu onze zoekacties uitvoeren. Aangezien Verticaal.zoeken niet ‘naar links’ kan zoeken gebruiken we de formule met de Index-Vergelijken-combinatie (zie het betreffende artikel daarover).

In het Voorbeeldbestand in het tabblad Ovz1 staat in cel D16 de formule
=ALS.FOUT(INDEX(tblBetalingen[Bedrag];VERGELIJKEN($B16&D$15;tblBetalingen[Hulp2];0));””)

Vergelijken zoekt in de Hulp2-kolom op welke plaats de combinatie van cel B16 en cel D15 (naam met volgnummer dus) staat. De nul geeft aan, dat we een exacte vergelijking willen uitvoeren.
Als we die plaats weten dan wordt met behulp van Index het bijbehorende Bedrag opgehaald.
Omdat de naam of de combinatie met het volgnummer niet hoeven voor te komen, zorgen we er voor dat, als de Index-formule een fout oplevert, er in de cel niets komt te staan (de dubbele aanhalingstekens).

LET OP in principe kan de formule uit D16 naar rechts en naar beneden gekopieerd worden. Doe dit niet door de vulgreep rechts onder van cel D16 te verslepen, maar gebruik de toets-combinaties Ctrl-C en Ctrl-V.

In kolom C nog even een Som-formule plaatsen en we weten hoeveel iedereen in totaal heeft betaald.

NB we zijn er hier van uit gegaan, dat er maximaal 5  betalingen per persoon plaats vinden. Klopt dit niet (meer) dan moeten er nog kolommen worden toegevoegd. Vergeet niet de Som-formule in kolom C aan te passen!

Datum-analyse

Op welke dagen zijn de betalingen verricht?

Op exact dezelfde manier als hiervoor halen we nu de Datum op in plaats van het Bedrag.
Deze keer plaatsen we in kolom C de Max-formule om de laatste betaaldatum per persoon te achterhalen.

Alternatieven zonder hulp-kolommen

Voor de liefhebbers staan in het tabblad Ovz2 van het Voorbeeldbestand nog 2 alternatieven, waarbij formules worden gebruikt die geen hulpkolommen nodig hebben.

In cel D3 wordt aan de hand van de naam in B3 en het volgnummer in D2 op de volgende manier het bijbehorende bedrag opgehaald:
={ALS.FOUT(
INDEX(tblBetalingen[Bedrag];
KLEINSTE(
(tblBetalingen[Naam]=$B3)*(RIJ(tblBetalingen[Naam]));
D$2 + AANTAL.ALS(tblBetalingen[Naam];”<>”&$B3)) –
RIJ(tblBetalingen[[#Kopteksten];[Naam]])
)
;””)}

Gebruik in de menutab Formules in het blok Formules controleren de optie Formules evalueren om te onderzoeken hoe de formule werkt.

NB Deze formule is ingevoerd door in plaats van op Enter op Ctrl-Shift-Enter te drukken, de zogenaamde CSE-methode.

Wil je de CSE-methode vermijden dan wordt de formule:
=ALS.FOUT(INDEX(tblBetalingen[Bedrag];INDEX(KLEINSTE((tblBetalingen[Naam]=$B9)*(RIJ(tblBetalingen[Naam]));D$2+AANTAL.ALS(tblBetalingen[Naam];”<>”&$B9))-RIJ(tblBetalingen[[#Kopteksten];[Naam]]);0));””)

Zie cel D9 in het tabblad Ovz2 van het Voorbeeldbestand.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

 

 

Loterij

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.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd: