Tagarchief: Vergelijken

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.

 

Excel-functies N en T

Excel, , , , , , ,
Voorbeeldbestand: Functie-N-en-T.xlsxDownload

Excel kent 2 functies waarvan het nut niet helemaal duidelijk is: N() en T().
Het lijkt er op, dat het nog restanten zijn uit het verleden. Volgens Microsoft bestaan ze nog “in verband met compatibiliteit met andere spreadsheet-programma’s“.

Allebei de functies kennen maar 1 argument/parameter; de functie N() zet de parameter zo mogelijk om in een getal (Numeriek) en de functie T() levert als resultaat een Tekst, als het argument ook een tekst is.

NB de 2 haakjes in de tekst staan er bewust omdat iedere Excel-functie deze haakjes nodig heeft; bij verreweg de meeste functies moeten tussen de haakjes één of meer argumenten opgegeven worden.

Hieronder zullen we kijken hoe deze functies zich in de ‘normale’ praktijk gedragen, maar ik zal ook laten zien hoe de functies zich goed laten gebruiken in meer creatieve/excentrieke toepassingen.

De functie N()

Zoals al aangegeven zet deze functie het argument om in een getal; tenminste als dat (makkelijk) kan, anders levert de functie de waarde 0 terug; zie het tabblad Vb1 in het Voorbeeldbestand.

Uit de eerste 2 voorbeelden blijkt, dat de N-functie een combinatie van tekst en cijfers NIET kan omzetten naar een getal.
Een geheel getal of een getal met decimalen levert wel de juiste waarde op.
Wanneer de parameter een getal met een decimale punt bevat wordt dit door Excel als tekst gezien (de inhoud van de cel is dan ook automatisch links uitgelijnd) .

In cel B8 staat een formule, die als resultaat een getal oplevert; de N-functie neem deze waarde over.
Een datum is voor Excel een getal (het aantal dagen na 1-1-1900; zie het artikel Data (datums)). De N-functie zet een harde datum (in cel B9) dan ook ‘gewoon’ om naar het betreffende getal en ook het resultaat van een functie (VANDAAG() in cel B10). Ook als de cel een uitgebreide datum-opmaak heeft gekregen (cel B11) levert dit hetzelfde resultaat.

NB Excel heeft de cellen B8:B11 automatisch rechts uitgelijnd; daaraan is al te zien dat hij ze als numerieke waarden interpreteert.

De cellen B12:B14 bevatten teksten (automatisch links uitgelijnd) en de N-functie levert dan ook de waarde 0 terug.
Cel B12 bevat een ‘optelling’ (met het &-teken) van een tekst en een datum (dus een getal) en wordt daardoor vanzelf een tekst: =”Vandaag: “&VANDAAG()
In B13 is gebruik gemaakt van de functie TEKST om de datum een opmaak mee te geven: =”Vandaag: “&TEKST(VANDAAG();”dd-mm-jjjj”)
B14 bevat alleen de TEKST-functie, waardoor het resultaat (uiteraard) direct een tekst wordt: =TEKST(VANDAAG();”dddd d mmmm jjjj”)

De teksten Waar en Onwaar worden door de N-functie vertaald naar 1, respectievelijk 0. Ook als het het resultaat is van een formule (zie cel B17 met de formule =2>1).
De resultaten van formules die een foutwaarde opleveren (bijvoorbeeld =1/0 in cel B18) worden door de N-functie overgenomen.
Wanneer N() een lege parameter meekrijgt (zie cellen B19 en C19) dan wordt het resultaat 0.

NB let op de automatische uitlijning door Excel van de cellen B15:B18.

N-functie in plaats van ALS

De N-functie kan ook goed gebruikt worden ter vervanging van de ALS-functie. Dit levert een iets kortere en beter leesbare formule op.
Stel we hebben de situatie, dat een bedrag met een bepaalde provisie wordt verhoogd wanneer dat bedrag hoger of gelijk is aan een grenswaarde (zie tabblad Vb1 in het Voorbeeldbestand).
De bijbehorende formule is dan:
=ALS(G4>=G2;G4*(1+G3);G4)
of (als we de betreffende cellen een naam hebben gegeven):
=ALS(Bedrag>=Grenswaarde;Bedrag*(1+Provisie);Bedrag)

NB een blok cellen een naam geven kan makkelijk als volgt:

  1. selecteer de cellen F2 t/m G4
  2. kies in de menutab Formules in het blok Gedefinieerde namen de optie Maken o.b.v. selectie
  3. in dit geval moet alleen Linkerkolom aangevinkt staan
  4. klik op OK
  5. controleer even wat er gebeurd is: kies in de menutab Formules in het blok Gedefinieerde namen de optie Namen beheren

Maar de formule kun je ook op een andere manier (eenvoudiger?) schrijven:
=Bedrag*(1+ALS(Bedrag>=Grenswaarde;Provisie;0))
of
=Bedrag*(1+Provisie*ALS(Bedrag>=Grenswaarde;1;0))
of door de N-functie te gebruiken:
 =Bedrag*(1+Provisie*N(Bedrag>=Grenswaarde))

N-functie in plaats van een opmerking

Er kan niet genoeg benadrukt worden hoe belangrijk het is om een spreadsheet van voldoende documentatie te voorzien (voor uzelf en/of voor anderen).

Die documentatie kan vastliggen in Word-files, in een apart tabblad en/of door cellen met formules van opmerkingen te voorzien (zie cel G5 in het tabblad Vb1 van het Voorbeeldbestand).

Maar plaats u veel opmerkingen in een spreadsheet dan wordt het daardoor ook niet overzichtelijker. Commentaar kan ook met de N-functie aan een cel worden toegevoegd. In cel G9 wordt daartoe bij de formule de N() met als parameter het commentaar (als tekst!) opgeteld; die laatste levert toch de waarde 0 op.

De functie T()

De T-functie zal nu weinig verrassingen meer opleveren: als de parameter een tekst is (of via een formule een tekst bevat) dan zal de T-functie de tekst terugleveren en anders niets (een lege tekst).

Aangezien WAAR en ONWAAR intern Excel als numeriek worden gezien (1 of 0) levert T() dus een lege waarde op.

Bij een foutmelding als parameter wordt de foutmelding overgenomen en een leeg argument voor de T-functie geeft een leeg resultaat.

T() in de praktijk

In een bepaalde toepassing wilt u teksten uit verschillende kolommen aan elkaar koppelen. Zoals we gezien hebben kan dat gemakkelijk met behulp van het &-teken. In het tabblad Vb2 van het Voorbeeldbestand hebben we dat gedaan in cel E3; daar zijn tussen de teksten ook nog extra spaties toegevoegd.

In deze toepassing mogen de cellen echter allen gekoppeld worden als het een tekst bevat; hier komt de T-tunctie om de hoek kijken (zie cel E4).

Maar nou krijgen we problemen met de hoeveelheid tussenspaties. In cel E5 is dat als volgt opgelost:
=ALS(T(B5)=””;””;T(B5)&” “)&ALS(T(C5)=””;””;T(C5)&” “)&ALS(T(D5)=””;””;T(D5))

Als T(B5) een leeg resultaat oplevert, dan hoeft er niets te gebeuren, anders komt er de tekst T(B5) met een extra spatie.
Idem voor C5 en voor D5 bijna hetzelfde; de extra spatie vervalt.

In cel E6 staat een alternatief:
=ALS(N(B6);””;T(B6)&” “)&ALS(N(C6);””;T(C6)&” “)&ALS(N(D6);””;T(D6))

De twee laatste formules hebben als probleem dat als de laatste kolom een getal bevat (en ook als de tweede en de laatste kolom getallen bevatten) er nog een spatie op het einde van het resultaat staat.
Dat lossen we als volgt op: =SPATIES.WISSEN(E6)

NB maar we hadden natuurlijk dan ook niet zo ingewilled hoeven te doen met de ALS-functie, want SPATIES.WISSEN verwijdert niet alleen alle spaties aan de voor- en achterkant, maar wijzigt meerdere, tussenliggende spaties in één spatie. Dus de formule in E4 had ook kunnen zijn: =SPATIES.WISSEN(T(B4)&” “&T(C4)&” “&T(D4))

T-functie en CSE

In de dagelijkse praktijk hebben de T- en N-functies weinig toegevoegde waarde en dus ook weinig toepassingen.
Bij meer ingewikkelde (database-) problemen zie je toch nog wel eens opduiken.

In het Voorbeeldbestand heb ik een idee van Chandoo overgenomen in het tabblad Data.
Uit een HR-systeem is een overzicht gekomen van medewerkers, maar wel in een speciale vorm: in de oneven regels (met donkere achtergrond) staat de medewerker en direct daaronder (met lichtere achtergrond) zijn of haar leidinggevende (de tabel met medewerkers heeft de naam tblData gekregen).
Ook de leidinggevende kan weer een leidinggevende hebben, dus namen kunnen dubbel voorkomen (zie bijvoorbeeld A. Niesen; leidinggevende van A. Smit (regel 4) en als medewerker (regel 7)).

De bedoeling is om uit dit bestand bij een bepaalde medewerker zijn/haar leidinggevende op te zoeken. Het mag duidelijk zijn, dat een gewone VERT.ZOEKEN hier niet volstaat; we mogen alleen maar zoeken in de cellen met een donkere achtergrond.

Wanneer we dus A. Niesen zoeken moeten we op regel 7 uitkomen; ofwel in het derde blokje van de medewerker/baas-combinatie.

NB om de invoer van een naam te vergemakkelijken heb ik aan cel E2 een Validatie-regel toegevoegd: alleen maar namen uit de reeks met de naam KeuzeLijst zijn toegestaan en kunnen via de -button gekozen worden.

De formule in cel E3 ziet er zo uit:
=VERGELIJKEN(E2;T(VERSCHUIVING(tblData[[#Kopteksten];[Medewerkers]];RIJ($A$1:$A$100)*2-1;;1;1));0)

Wanneer we dit wat overzichtelijker laten zien:
01:=VERGELIJKEN(
02:     E2;
03:     T(
04:          VERSCHUIVING(
05:               tblData[[#Kopteksten];[Medewerkers]];
06:               RIJ($A$1:$A$100)*2-1;
07:               ;
08:               1;
09:               1
10:          )
11:     );
12:     0
13:)

Met Vergelijken (regel 1) gaan we de medewerker (regel 2) opzoeken.
Waar gaan we zoeken? Natuurlijk in de tabel tblData (regel 5). Maar niet in alle namen; alleen in de eerste, derde, vijfde etc. Met Verschuiving (regel 4) wordt een eigen reeks gemaakt door te verschuiven vanaf de koptekst Medewerkers (regel 5).
Hoeveel gaan we verschuiven? Als je weet dat RIJ(A1:A100) het rijnummer van de betreffende cellen genereert (regel 6; dus de getallen 1 t/m 100) dan is duidelijk dat door vermenigvuldiging met 2 en dan er 1 vanaf trekken de getallen 1, 3, 5 etc ontstaan. Met deze reeks gaan we naar beneden verschuiven vanaf de kopregel.
We blijven met de verschuiving in dezelfde kolom, dus de parameter in regel 7 is leeg.
Het resultaat van de verschuiving moet 1 cel hoog zijn (regel 8) en 1 cel breed (regel 9).
Om nu te zorgen, dat er een reeks van echte namen ontstaat komt de T-functie om de hoek kijken (regel 3).
Dus we gaan de naam uit E2 vergelijken met onze eigen reeks, waarbij een exacte match nodig is (vandaar de 0 in regel 12). Het resultaat is het volgnummer van het de combi medewerker/baas.

LET OP om te zorgen, dat de functie RIJ alle benodigde getallen genereert moet de formule in E3 als een zogenaamde array- of matrix-formule worden ingevoerd. Sluit de invoer NIET af met Enter, maar met Control-Shift-Enter (ofwel een CSE-invoer).

NB bekijk de werking van de diverse onderdelen van de formule:

  1. selecteer cel E3
  2. kies in de menutab Formules in het blok Formules controleren de optie Formule evalueren
  3. kijk welk gedeelte van de formule onderstreept is, druk dan op Evalueren en bekijk het resultaat
  4. herhaal stap 3 totdat de hele formule geëvalueerd is.

In cel E4 de naam van de baas opzoeken is nu een ‘peulenschil’:

=INDEX(tblData[Medewerkers];E3*2)

 

Hoog-Laag-Slot-grafiek

Excel, , , , ,

Binnenkort (nou ja, over een paar maanden) hebben we weer verkiezingen. In de aanloop daar naartoe zien we in de media steeds vaker de resultaten van diverse peilingen.

Op de site peilingwijzer.tomlouwerse.nl is het meest recente resultaat van een wetenschappelijk onderbouwde combinatie van 5 peilers (nee Maurice, geen pijlers!) te vinden.

Een grafiek op die site laat van 13 politieke partijen het verloop van de peilingen in de tijd zien; naast het gemiddelde van de 5 peilers zien we in de grafiek ook een 95%-onzekerheidsmarge (het licht gekleurde gebied).

Hieronder laat ik zien, hoe je in Excel een dergelijke grafiek kunt maken; we gebruiken daar een hoog-laag-slot-grafiek voor.
Daarbij moet wel aangetekend worden, dat de grafiek op de site van Tom Louwerse veel meer interactieve elementen bevat!

Basisgegevens

Zoals voor iedere grafiek hebben we basisgegevens nodig; deze zijn in dit geval op te halen vanaf de download-pagina van de Peilingwijzer.

In het Voorbeeldbestand heb ik op het tabblad Peilingwijzer deze gegevens geplaatst (op dit moment van 12 september 2012 tot en met 27 november 2016); zoals in eerdere voorbeelden ‘gieten’ we deze in de vorm van een tabel (Invoegen/Tabel, kopregels aanvinken) en geven die tabel de naam tblpeilingWijzer.

NB1 komen er nieuwe peiling-data beschikbaar, voeg deze dan onderaan de tabel als nieuwe regels toe.

NB2 komen er nieuwe partijen in het overzicht bij, voeg deze dan rechts aan de tabel als nieuwe kolommen toe.

Parameters

Hierna gaan we een grafiek maken van de peiling-resultaten. Hierbij kan echter maar één partij tegelijkertijd worden weergegeven. Om de invoer daarvan straks te vergemakkelijken leggen we de keuzemogelijkheden in een aparte tabel vast (zie tabblad Param in het Voorbeeldbestand).

NB wordt er in de peilingen een extra politieke partij meegenomen, dan moet deze onderaan toegevoegd worden; aangezien het ook hier een tabel betreft, zal Excel de nieuwe regel automatisch in tblPartijen meenemen.

Het is ook wel handig om te weten wat de eerste en laatste datum is, die in het peilingoverzicht voorkomt.
In cel E2 van het tabblad Param uit het Voorbeeldbestand bepalen we de eerste/kleinste datum door het minimum van die kolom op te zoeken.
Normaal gesproken zou dat kunnen met de formule =MIN(tblPeilingWijzer[Datum]), maar helaas levert dat de waarde 0 op. Dat komt omdat in de datum-kolom geen echte datum staat maar een tekst, dus het wordt wat ingewikkelder:
{=MIN(DATUM(
LINKS(tblPeilingWijzer[Datum];4);
DEEL(tblPeilingWijzer[Datum];6;2);
RECHTS(tblPeilingWijzer[Datum];2)))}

Met behulp van de functie LINKS zoeken we het jaar op, DEEL haalt 2 tekens op vanaf positie 6 (de maand dus) en RECHTS levert de dag. Deze 3 resultaten geven we door aan de functie DATUM, die er een datum van maakt. De functie MIN zoekt dan de kleinste datum op.

LET OP de 3 tekst-functies kijken eigenlijk alleen naar de datum uit de corresponderende rij (de 2e dus). Door na het intikken van de formule niet op Enter te drukken maar op Ctrl-Shift-Enter wordt het een zogenaamde array- of matrix-formule en worden de 3 tekst-functies op alle datums ‘losgelaten’. Vaak wordt dit ook CSE-invoer genoemd; Excel plaatst automatisch accolades om de formule (niet handmatig intypen!).

De formule in E3 mag dan geen verrassingen meer bevatten.

Voor het gemak hebben we de cellen E2 en E3 een naam gegeven MinDatum, resp. MaxDatum.

Grafiek met onzekerheidsmarge

Voordat we een dergelijke grafiek kunnen maken moeten we eerst een methode hebben om de gegevens van één partij uit de basis te destilleren.

Cel C3 (met de naam invPartij) in het tabblad Ovz van het Voorbeeldbestand gebruiken we als keuzevak:

  1. kies in de menutab Gegevens de optie Gegevensvalidatie
  2. in het pop-up-scherm kiezen we bij Toestaan: de optie Lijst
  3. bij Bron: zouden we alle keuzemogelijkheden kunnen intikken gescheiden door een ; (punt-komma), dus bijvoorbeeld VVD;PvdA;GL etc.
    Maar dat is niet zo handig. Die lijst kunnen we ook uit het tabblad Param halen: kolom Partijen in de tabel tblPartijen.
    Helaas kan gegevensvalidatie niet goed omgaan met de nieuwere tabellen (geïntroduceerd in versie 2007), dus de formule =tblPartijen([Partijen]) werkt niet, maar wel als we de functie INDIRECT gebruiken (zie ook het artikel Tabellen (deel 2); denk aan de aanhalingstekens!!).
  4. Eventueel nog een Invoerbericht en/of Foutmelding toevoegen (zie Voorbeeldbestand) en we zijn klaar: klik op OK.

De voorbereidingen zijn klaar, nu nog de gegevens ophalen van de gekozen partij:

  1. in rij 5 van het tabblad Ovz van het Voorbeeldbestand staat een kopregel
  2. vanaf B6 naar beneden staan alle datums, waarvoor er peilingen zijn
  3. in cel C6 moet de eerste peiling van de gekozen partij komen:
    =INDEX(
    INDIRECT(“tblPeilingWijzer[“&invPartij&”]”);
    VERGELIJKEN($B6;tblPeilingWijzer[Datum];0))
    Weet u niet (meer) hoe de functie INDEX werkt? Kijk in het artikel Zoeken: Index en Vergelijken; daar vindt u ook een truc hoe u de de functie makkelijk implementeert.
    Weer gebruiken we INDIRECT om de kolom, waarin gezocht moet worden, afhankelijk te maken van de cel C3 (met de naam invPartij).
  4. de formules in D6 en E6 zijn vergelijkbaar, alleen worden daar de partij-gegevens uit de low- en high-kolom opgehaald.
  5. de drie formules uit C6:E6 worden naar beneden gekopieerd, zodat bij iedere datum de gegevens tevoorschijn komen.
  6. via de menutab Invoegen en de optie Tabel maken we van dit overzicht een tabel (met de naam tblGrafBasis). Wanneer er nieuwe peilingen beschikbaar zijn is het dan voldoende om onderaan de datum toe te voegen. Alle formules worden dan automatisch door Excel ingevuld.

En nu de grafiek:

  1. plaats de cursor ergens in tblGrafBasis
  2. in de menutab Invoegen kiest u in het blok Grafieken de optie Overige grafieken.
    In het uitklapmenu nemen we de eerste grafieksoort in het blokje Hoog/Laag/Slot.
    Dit soort grafieken wordt vaak in de financiële wereld gebruikt om een overzicht van de dagkoersen weer te geven: per dag ziet u dan de hoogste, laagste en slotkoers.
    NB Excel beoordeelt automatisch in welke kolom de hoge, lage of slot-waarde (of in ons geval het gemiddelde) staat.
  3. Nog even wat opmaak regelen:
    * kies in de nieuwe menutab Hulpmiddelen voor grafieken het tabblad Indeling
    * kies in het blokje Huidige selectie de Reeks Gemiddeld 
    * daarna de optie Selectie opmaken
    * en kies bij Lijnkleur een gewenste kleur
    * kies in het blokje Huidige selectie de Hoog/laag-lijnen
    * daarna de optie Selectie opmaken
    * en kies bij Lijnkleur dezelfde kleur, maar met een Transparantie van 75%
    * kies in het blokje Huidige selectie de Reeks Hoog
    * daarna de optie Selectie opmaken
    * en kies bij Markeringsopties Geen
    * Grafiektitel laten verwijzen naar cel E3
    * aan de linker-as een Titel toevoegen, de notatie wijzigen in een percentage zonder decimalen en zorgen dat er het bereik altijd van 0% tot 30% loopt.

Wijzig cel C3 en bekijk het resultaat!

Zoeken: Index en Vergelijken

Excel, , , ,

vertzoekenOp ieder Excel-forum zie je regelmatig de opmerking, dat het gebruik van de standaard-functie VERT.ZOEKEN (verticaal zoeken) niet flexibel is en foutgevoelig (en dat geldt ook voor de horizontale variant). Meestal wordt er dan als alternatief verwezen naar het gebruik van INDEX, gecombineerd met VERGELIJKEN.
Ik heb daar al eens eerder een artikel aan gewijd (Alternatief voor verticaal zoeken).

In de praktijk zie ik, dat er toch vaak met VERT.ZOEKEN wordt gewerkt, zo nodig met behulp van allerlei hulpkolommen. Bijna altijd met het argument: “Ik weet niet meer hoe ik die 2 functies moet combineren.”

Ik kwam pas een handige truc tegen, waardoor het invoeren van deze gecombineerde functies werkelijk een fluitje van een cent wordt.
Hierna zal ik deze tip toelichten, maar eerst laat ik (nogmaals) zien dat de combinatie van INDEX en VERGELIJKEN echt beter is dan VERT.ZOEKEN.

Voorbeeld van VERT.ZOEKEN

vertzoeken1Hiernaast zie je een simpel voorbeeld van een werknemersadministratie: naast een personeelsnummer staat de naam en de afdeling.

Wanneer we een personeelsnummer kennen zien we in één oogopslag welke persoon daar bij hoort. Zou het bestand veel groter zijn (en niet gesorteerd op nummer), dan kan het zoeken wat lastiger worden. Daar komen de Excel-zoekfuncties om de hoek kijken. In het Voorbeeldbestand is in het tabblad VZoeken het bestandje opgenomen.

vertzoeken2In cel F3 staat het personeelsnummer waarvan we de naam en afdeling willen weten. In G3 staat de formule
=VERT.ZOEKEN(F3;B3:D7;2;ONWAAR)
Ofwel: zoek de waarde van F3 op in de eerste kolom (VERTicaal) van het bereik B3:D7 en geef de corresponderende waarde uit de 2e kolom van dat bereik terug. De zoekwaarde moet exact overeenkomen vandaar dat bij de laatste parameter/argument van de functie (Benaderen) ONWAAR is ingevuld.

Wijzig de waarde in F3 en de corresponderende naam wordt opgehaald; net als de betreffende afdeling in cel H3, waar de gegevens uit de 3e kolom worden uitgelezen.

NB1 als het nummer niet in het bestand voorkomt zal de functie #N/B (niet beschikbaar) opleveren.

NB2 de Excel zoek-functie zoekt de waarde van het eerste argument altijd in de eerste kolom van het bereik, dat opgegeven is in het tweede argument. Vandaar dat je niet ‘naar links’ kunt zoeken. Dus als je een naam weet kun je op deze manier niet het nummer opzoeken.

NB3 voeg tussen de kolommen C en D een nieuwe kolom in en kijk wat er met het resultaat van het zoeken gebeurt!

NB4 voeg een nieuwe werknemer onderaan toe, plaats het personeelsnummer in cel F3. Wat gebeurt er?

Index en Vergelijken (deel 1)

indverg1In het tabblad IndVerg van het Voorbeeldbestand is gebruik gemaakt van INDEX en VERGELIJKEN.

Hoe werkt dit?

  1. allereerst moet de zoekwaarde (in dit geval cel F3) vergeleken worden met de kolom met personeelsnummers (B3:B7).  Het resultaat is de positie van de zoekwaarde in het bereik (in dit geval 1; zie cel J3).
    Met de derde parameter van de functie VERGELIJKEN kunnen we aangeven welk soort resultaat er moet worden opgeleverd; met 0 geven we aan, dat er een exacte match moet zijn.
  2. de zo gevonden positie wordt gebruikt in INDEX om een resultaat op te halen in de kolom met namen (C3:C7).

Op deze manier is het probleem van de NB3 van hierboven in ieder geval opgelost.

indverg2Ook de NB2 kunnen we hiermee omzeilen: in cel G7 zoeken we het nummer op in B3:B7, dat hoort bij de naam  in cel F7 door die waarde te vergelijken met de inhoud van C3:C7.

NB5 de functie VERGELIJKEN levert altijd maar 1 resultaat terug; de eerste die aan de voorwaarde voldoet. Een oplossing hiervoor wordt verderop uitgewerkt.

Index en Vergelijken (deel 2)

Ook de NB3 moeten we nog oplossen: als er werknemers bij komen moeten de formules blijven werken.

indverg3Het eerste wat we doen is van het personeelsbestand een tabel maken:

  1. klik op één van de cellen in het bestand
  2. kies in de menutab Invoegen de optie Tabel; zorg dat het vinkje aan staat bij De tabel bevat kopteksten.
  3. de tabel wordt automatisch van een ‘zebra’-opmaak voorzien. indverg4In het Voorbeeldbestand heeft de tabel op het tabblad IndVerg2 de naam tblWerknemers gekregen (klik op Ontwerpen in de nieuwe menutab Hulpmiddelen voor tabellen en wijzig de standaardnaam).

indverg5In cel G3 gaan we opnieuw de alternatieve opzoek-formule maken om de naam bij een nummer te vinden:

  1. tik in: =index(
  2. maar in plaats van het intikken van C3:C7 (het bereik met namen) wijzen we met de cursor de bovenrand van cel C2 aan; de cursor wordt dan een zwarte pijl naar beneden. Op dat moment 1 keer klikken en de formule wordt aangevuld met tblWerknemers[Naam] (ofwel de kolom Naam uit de tabel tblWerknemers).
  3. de rest mag dan duidelijk zijn: tik in ;vergelijken( en klik op cel F3
  4. tik in ; en klik op de bovenrand van cel B2; de formule wordt aangevuld met tblWerknemers[Nr]
  5. tik in ;0))
  6. druk op Enter

Voeg een nieuwe werknemer toe en kijk of de formules nog werken.

Index en Vergelijken (deel 3)

Het blijkt niet zo makkelijk om te onthouden hoe de combinatie van deze functies moet worden ingevoerd. Maar pas kwam ik een handige truc tegen (zie Wyn Hopkins op LinkedIn): gebruik de auto-correctie-mogelijkheden van Office! En dit kan natuurlijk met iedere complexe of anderszins moeilijk te onthouden functie(-combinatie).

  1. indverg6Kies in Excel de menutab Bestand
  2. klik op Opties en dan op Controle.
  3. klik op de button AutoCorrectie-opties
  4. in het nieuwe scherm vullen we onder Vervangen: een afkorting in die we hierna zullen gebruiken, bijvoorbeeld avz (Alternatief Voor Zoeken).
    NB werkt u bij of voor de firma Aluminium Verwerking Zuid dan zult u een andere, niet gebruikte afkorting moeten nemen.
  5. onder Door: komt de formule
    =INDEX(Stap3_Waar_is_het_resultaat?;VERGELIJKEN(Stap1_Wat_zoeken?;Stap2_Waar_zoeken?;0))
  6. Kies Toevoegen en OK

Nu gaan we deze afkorting gebruiken:

  1. open het tabblad IndVerg3 in het Voorbeeldbestand
  2. klik in cel G3; de formule die daar staat gaan we opnieuw opbouwen
  3. tik in avz (of een andere afkorting die bij Vervangen: is ingevuld) en voeg nog een spatie toe
  4. de auto-correctie doet zijn werk en in plaats van avz staat er nu onze index/vergelijken-formule!
  5. dubbelklik op Stap1 (door de underscores wordt alles tot aan de ; geselecteerd) en klik op cel F3
  6. dubbelklik op Stap2 en klik op de bovenrand van cel B2
  7. dubbelklik op Stap3 en klik op de bovenrand van cel C2

Makkelijk toch? Er is nu geen enkele reden meer om VERT.ZOEKEN (of HORIZ.ZOEKEN) te blijven gebruiken.

Index en Vergelijken (multi-criteria)

In ons voorbeeldbestand komen twee mensen voor met de naam Jansen. Excel levert ons bij het zoeken altijd de eerste (zie cel I3 in het tabblad IndVergMulti van het Voorbeeldbestand).
Willen we zeker weten dat we de juiste persoon ophalen dan zullen we nog meer eigenschappen er bij moeten betrekken om zo de zoekopdracht uniek te maken, bijvoorbeeld de geboortedatum.

NB voordat u in Excel een zoek-optie implementeert, moet u zeker weten op welke manier u iemand of iets uniek kunt identificeren; misschien is naam en geboortedatum niet genoeg.

Voordat we de multi-criteria-formule gaan implementeren moeten we eerst kijken naar een alternatieve implementatie van VERGELIJKEN.
In het tabblad IndVergMulti in cel L4 plaatsen we de volgende formule:

=VERGELIJKEN(WAAR;tblWerknemers3[Naam]=G4;0)

In plaats van het vergelijken van een zoekwaarde (G4) met een bepaald bereik (tblWerknemers3[Naam]) vergelijken we hier de zoekwaarde WAAR met een reeks uitkomsten, die ontstaat door te controleren of de cellen in de kolom Naam gelijk zijn aan G4.

NB wilt u weten hoe een formule intern de tussenstappen berekent, kies dan in de menutab Formules de optie Formule evalueren.

Excel moet wel weten dat G4 met ALLE cellen uit de kolom Naam moet worden vergeleken; daarom moet deze formule als een zogenaamde array-formule worden ingevoerd door niet gewoon op Enter te drukken maar op Ctrl-Shift-Enter (soms wordt dit dan ook de CSE-methode genoemd).
Excel plaatst automatisch accolades om de formule bij CSE; dit is niet hetzelfde als wanneer er handmatig accolades geplaatst worden!!

Maar nu multicriteria: de vergelijking van G4 met de cellen van de kolom Naam levert een reeks op met daarin WAAR of ONWAAR; intern Excel is dit een 1 , respectievelijk 0.
Door deze reeks WAAR/ONWAAR (ofwel 1 en 0) te vermenigvuldigen met een andere reeks, die ontstaat door H4 (een datum) te vergelijken met de kolom GebDtm, krijgen we een nieuwe reeks met een 1 als aan alle twee de voorwaarden is voldaan, anders een 0.

In cel M4 staat dan ook de formule
=VERGELIJKEN(1;(tblWerknemers3[Naam]=G4)*(tblWerknemers3[GebDtm]=H4);0)

LET OP ook deze formule moet met CSE worden ingevoerd.

LET OP plaats bij het vermenigvuldigen van dit soort reeksen er haakjes omheen.

NB aangezien de vermenigvuldiging altijd een 1 of 0 oplevert (en niet WAAR of ONWAAR) moet als zoekwaarde een 1 worden opgegeven (bekijk de tussenstappen met Formule evalueren).

Om het juiste resultaat op te halen op basis van de 2 criteria staat in I4 de formule
=INDEX(tblWerknemers3[Nr];VERGELIJKEN(1;(tblWerknemers3[Naam]=G4)*(tblWerknemers3[GebDtm]=H4);0))

LET OP ook deze formule moet met CSE worden ingevoerd!

Cadeau: in de cellen I7 en J7 staat een aangepaste formule, die er voor zorgt, dat bij een onbekende werknemer er geen #N/B als resultaat komt.

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.