Tagarchief: Indirect

Grafieken verduidelijken en interactief maken



Een grafiek zegt meer dan 1000 getallen“.
Met deze parafrasering van een bekende zegswijze ben ik het meestal eens, maar soms valt de uitwerking tegen.

In dit artikel wil ik (aan de hand van cijfers over gemiddelde geboortegewichten) met enkele voorbeelden laten zien hoe je een grafiek aan duidelijkheid kunt laten winnen.

Basis-gegevens

Op zoek naar gegevens over de spreiding van gewichten van baby’s bij hun geboorte kon ik bij het CBS niet veel vinden dat bruikbaar was voor dit artikel. Wel zag ik op de website van het Nederlands Tijdschrift Voor Geneeskunde een onderzoek, waarvan de resultaten wel geschikt waren:

LET OP dit onderzoek stamt uit 1990 (over geboortes in de jaren 70-80); we weten dat het gemiddeld geboortegewicht in de loop van de tijd is gestegen. Volgens het CBS was dit gemiddelde in 2005 ongeveer 60 gram hoger dan in 1990. Wanneer we dit doortrekken zullen de gemiddelde gewichten nu zeker 100 tot 150 gram hoger uitvallen dan de cijfers in bovenstaand onderzoek.

In het tabblad Data van het Voorbeeldbestand zijn deze cijfers overgenomen:

NB1 de cijfers zijn iets anders gerubriceerd, zodat we daar makkelijker een grafiek van kunnen maken.

NB2 Week geeft de zwangerschapsduur aan, n het aantal waarnemingen in de betreffende categorie en de percentielkolommen (P5, P10 etc) dat gewicht waarbij 5%, 10% etc van de geboortegewichten kleiner zijn. Dus in de eerste regel: van de 65 meisjes (het eerste kind, geboren in de 36e week) is het gemiddelde gewicht 2486 gram, is 5% lichter dan 1790 gram en 95% lichter dan 3250 gram.

De gegevens zijn opgenomen in de Excel-tabel met de naam tblData.

Grafiek 1

Door de combinatie van 2 soorten geslacht en 2 soorten geborenen kent het onderzoek dus 4 verschillende categorieën.

Per categorie kunnen we een grafiek maken, maar in het tabblad Selectie van het Voorbeeldbestand staat een interactieve variant: afhankelijk van de keuze voor Geslacht en Eerst/later wordt de grafiek automatisch aangepast.

NB de cellen C2 en C3 zijn met behulp van Gegevensvalidatie afgeschermd, zodat alleen zinvolle invoer mogelijk is.

Op basis van deze cellen (C2 en C3) wordt een hulptabel ingevuld. Hierbij wordt de functie SOMMEN.ALS gebruikt:

  • in de eerste parameter wordt aangegeven waar de gegevens kunnen worden gevonden. In dit geval gebruiken we de functie INDIRECT om de juiste kolom in de tabel tblData te benaderen.
  • de tweede en derde parameter bepalen het eerste criterium waaraan de gegevens, die we willen ophalen, moeten voldoen (alle regels in de tabel tblData waar in de kolom Geslacht de waarde van cel C2 staat)
  • via het vierde en vijfde argument wordt de soort geboorte (al dan niet Eerstgeborene) geselecteerd
  • en het zesde en zevende argument bepalen de juiste week

NB door de 3 criteria voldoet altijd maar 1 cel uit de basis-tabel. Het resultaat is dus geen echte som, maar slechts die ene waarde.

Op basis van deze hulptabel kunnen we snel een grafiek genereren:

Maar wat zien we hier nu eigenlijk?
Met enkele aanpassingen krijgen we een grafiek die beter te begrijpen is:

NB misschien moet de betekenis van P5 etc nog toegelicht worden; dit is afhankelijk van de doelgroep.

Wat is er toegevoegd:

  1. uiteraard een legenda
  2. de reeks Gem is naast de P50 gezet: klik rechts in de grafiek, kies de optie Gegevens selecteren en gebruik in het vervolgscherm de pijltjes:

    NB het gemiddelde en de 50-percentiel zijn in de meeste gevallen ongeveer gelijk aan elkaar
  3. om duidelijker te maken welke lijn het gemiddelde is, heeft deze lijn markeringen gekregen
  4. bij de horizontale as is aangegeven wat de getallen voorstellen
  5. en misschien wel het belangrijkste: de grafiektitel, die aangeeft wat er in de grafiek staat en welke selectie daarbij is gemaakt.
    In cel L17 van het tabblad Selectie wordt de tekst voor de titel gemaakt:

    Tussen de &-tekens staan verwijzingen naar de cellen met de namen Geslacht (C2) en Eerst_later (C3).
    Achter het woord gram staat een ‘harde return’, druk bij het invoeren op Alt-Enter.
    Maak dan een willekeurige grafiektitel, klik daarin en zet in de formulebalk : =Selectie!L17
    LET OP vergeet niet ook de naam van het werkblad in te voeren; je kunt ook na het intikken van het =-teken met de muis op de cel met de tekst klikken.

Om de gegevens van een pasgeborene makkelijk te kunnen vergelijken is de invoer nog uitgebreid met een geboorteweek en in de hulptabel wordt dan met Voorwaardelijke opmaak de overeenkomende regel geaccentueerd:

Op het tabblad Selectie van het Voorbeeldbestand worden ook diverse gemiddeldes berekend:

In M2 wordt het gemiddelde bepaald van alle gemiddelde gewichten uit het onderzoek.
Maar …. dit is niet juist! Per geboorteweek is het aantal baby’s niet gelijk. De betreffende gemiddelde gewichten moeten gewogen worden met deze aantallen.

Om deze ‘weging’ makkelijk te kunnen uitvoeren is aan de brongegevens een berekende kolom toegevoegd, n*Gem (zie het tabblad Data).

In cel M3 wordt op de juiste manier het gemiddelde bepaald:
=SOM(tblData[n*Gem])/SOM(tblData[n])
ofwel de som van alle waardes in de kolom n*Gem in de tabel tblData gedeeld door de som van alle waardes in de kolom n van die tabel.

NB op de CBS-site is te lezen: “In de periode 1989-1991 woog een baby gemiddeld 3 372 gram bij geboorte, in de periode 2004-2006 was dat 3 434 gram.
Dus het gemiddelde gewicht van 1990 is ongeveer gelijk aan dat uit het onderzoek met geboortes uit 1970-1980.

Afhankelijk van de waardes in de cellen C2 (met de naam Geslacht) en C3 (met de naam Eerst_later) wordt het gemiddelde van die selectie bepaald:
=SOMMEN.ALS(tblData[n*Gem];tblData[Geslacht];Geslacht;tblData[Eerst/later];Eerst_later)/
SOMMEN.ALS(tblData[n];tblData[Geslacht];Geslacht;tblData[Eerst/later];Eerst_later)
Dus in plaats van een gewone SOM-formule gebruiken we nu SOMMEN.ALS, waarmee we criteria kunnen opgeven voor het optellen.

Op een vergelijkbare manier worden ook de gemiddeldes voor het geselecteerde geslacht en de geselecteerde soort geboorte berekend.

Wil je liever geen hulpkolom (n*Gem) gebruiken dan biedt de functie SOMPRODUCT uitkomst (zie cel N3 in het tabblad Selectie; deze functie vermenigvuldigt alle elementen uit een reeks met de overeenkomende elementen van de andere reeksen en sommeert de resultaten daarvan).
Ook de andere gemiddeldes kunnen op een vergelijkbare manier bepaald worden. In cel N4 staat bijvoorbeeld de formule:
=SOMPRODUCT(tblData[n];tblData[Gem];
1*(tblData[Geslacht]=Geslacht);
1*(tblData[Eerst/later]=Eerst_later))/
SOMPRODUCT(tblData[n];

1*(tblData[Geslacht]=Geslacht);
1*(tblData[Eerst/later]=Eerst_later))

De criteria die we willen meegeven, staan in aparte reeksen; bijvoorbeeld de kolom Geslacht wordt vergeleken met de waarde in de cel Geslacht (dit is cel C2). Dit levert een reeks op van diverse WAAR’s en ONWAAR‘s; door deze met 1 te vermenigvuldigen wordt dit een reeks 1’n en 0‘n.

Grafiek 2

Wanneer je alleen de grafiek toont (en dus niet de hulptabel) dan is het niet altijd even makkelijk om de exacte waardes af te leiden.
Daarom zijn in bovenstaande grafiek (zie het tabblad Selectie2 van het Voorbeeldbestand) ook de waardes van het gemiddelde en van de P25 en P75 opgenomen van de geselecteerde geboorte-week.
De hulptabel heeft daartoe een extra kolom (WkSel) gekregen:

NB de functie NB() levert als resultaat #N/B. Het voordeel hiervan is dat deze waarde in een grafiek niet wordt weergegeven (zie het artikel Grafiek zonder nullen).
In Excel wordt intern WAAR omgezet in de waarde 1. De reeks WkSel is aan de grafiek toegevoegd en aan de secundaire as gekoppeld. Nog een markering toevoegen aan deze ‘lijn’ en op de juiste plaats zien we een signalering.
Door ook een kolom Label toe te voegen aan de hulptabel kunnen we deze extra signalering van een Gegevenslabel voorzien. Voor de exacte implementatie, zie het tabblad Selectie2 van het Voorbeeldbestand.
Kies je nu een andere geboorteweek (cel C4) dan past de signalering zich automatisch aan:

Grafiek 3

Dit is eigenlijk geen andere grafiek, alleen de manier van selecteren is anders (makkelijker; zie het tabblad Selectie3 van het Voorbeeldbestand).

De hoofdselecties worden gemaakt met behulp van Keuzerondjes, de geboorteweek selecteer je met een Schuifbalk.

Deze voeg je toe via de menutab Ontwikkelaars. In het blok Besturingselementen kies je de optie Invoegen. Selecteer de gewenste optie binnen de Formulierbesturingselementen. Na de selectie kun je zo’n element op de gewenste plaats ‘met de muis tekenen’.

Bij Keuzerondjes is de volgende werkwijze het handigst:

  1. teken één keuzerondje
  2. klik rechts, verander de tekst in m, kies Besturingselement opmaken en maak een koppeling met een cel (in het voorbeeld op tabblad Selectie3 is dat N2)
  3. tik Ctrl-C en ergens anders Ctrl-V, verander de tekst in v
  4. voeg eventueel een groepsvak toe, die je om deze twee keuzerondjes tekent
  5. doe hetzelfde voor het soort geborene; maak daar een koppeling met een andere cel (in het voorbeeld N3)
  6. door een keuzerondje aan te klikken wordt er in cel N2, respectievelijk N3 de waarde 1 of 2 geplaatst; in de cellen daarnaast wordt een ‘vertaling’ gemaakt.

De Schuifbalk is eenvoudiger:

  1. teken deze op de plaats waar je hem wilt hebben (kun je later natuurlijk nog aanpassen)
  2. klik rechts, kies Besturingselement opmaken en maak een koppeling met een cel (in het voorbeeld op tabblad Selectie3 is dat N4).
    Stel ook de minimum- (in het voorbeeld 36) en de maximumwaarde (43) in.

Grafiek 4

Met behulp van het systeem in het tabblad Vergelijken van het Voorbeeldbestand kun je snel een eigen waarneming afzetten tegen de populatie uit het onderzoek.

Voer de naam, de zwangerschapsduur (in weken) en het geboortegewicht in. In de grafiek is direct de relatieve positie tussen de percentiellijnen te zien. Door wat extra berekeningen wordt via een label ook een schatting voor de absolute positie weergegeven.

Een korte toelichting op de berekening:

  1. zoek eerst de rij op van de geboorteweek (in cel N10 met de functie VERGELIJKEN)
  1. met behulp van deze waarde en de functies VERGELIJKEN en VERSCHUIVING wordt in cel N11 de kolom opgezocht waarvan de score gelijk of kleiner is aan het geboortegewicht uit cel D4.
    LET OP1 de derde parameter in de VERGELIJKEN-functie is weggelaten; meestal is deze parameter gelijk aan 0 (nul; dan wordt een exacte match gezocht). Vaak zal het gezochte geboortegewicht namelijk niet in de tabel voorkomen.
    LET OP2 wanneer je de derde parameter weglaat (beter gezegd: als die niet gelijk is aan 0) dan moet de reeks waarin je zoekt, gesorteerd zijn.
  2. in cel N12 bepalen we de percentiel-waarde die bij die kolom hoort
  3. ook het bijbehorende gewicht wordt dan opgezocht in cel N13
  4. in kolom O worden de vorige 2 stappen uitgevoerd voor een hogere percentielkolom
  5. we interpoleren tussen de hiervoor bepaalde waardes om een schatting te krijgen voor het percentiel dat bij het opgegeven geboortegewicht hoort (zie cel O14)

Om de markering op de juiste plaats te krijgen voegen we een nieuwe kolom in de hulptabel toe met voor elke geboorteweek de waarde NB() en alleen in de betreffende geboorteweek komt het geboortegewicht (zie de kolom Verg in de hulptabel op het tabblad Vergelijken).

Net als bij de vorige grafiek moet aan de hierbij behorende ‘lijn’ een markering meegegeven worden, voordat het markeringspunt zichtbaar zal zijn. Geef deze reeks ook een label mee (zie kolom VergLabel).

Als laatste moeten nog de verticale en horizontale lijnen ingevoegd worden:

  1. de verticale kan het makkelijkst door een nieuwe reeks te maken net als in de vorige grafiek (zie kolom VergWk in de hulptabel van het tabblad Vergelijken). Kies als grafiektype een Kolomdiagram en koppel deze aan de secundaire as.
  2. de horizontale lijn wordt geproduceerd door een reeks toe te voegen met voor iedere week dezelfde waarde, namelijk het geboortegewicht uit cel D4.

NB het ‘systeem’ is nog niet fool-proof: vul je een hoog geboortegewicht in, dan zal het zoeken naar het overeenkomende percentiel problemen opleveren.


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


Tour de France 2020



De Tour de France: ieder jaar kijk ik er weer naar uit, deze keer wat langer dan anders!
Een artikel op de site van G-Info met de Tour-gegevens als basis mag dan ook niet ontbreken.

Overal vind je wel standen en overzichten, dus dat gaan we niet overdoen. Het leek me wel aardig om te kijken of we een overzicht kunnen maken van de meest constant-presterende renners.
We zullen daarbij allerlei manieren van tellen en zoeken gaan gebruiken, variërend van de functie AANTAL.ALS, de combinatie van Index en Vergelijken tot het gebruik van Gegevensvalidatie en Keuzelijsten.

Doel

Dit jaar gaat de Tour al direct los: de eerste dagen moeten veel ‘heuvels’ en bergen bedwongen worden (zie het tabblad Etappes in het Voorbeeldbestand). De sprinters hebben dan niet al te veel kans. Misschien vallen er zelfs al renners uit die categorie uit voordat ze aan een massa-sprint kunnen beginnen.

Daarom gaan we in dit artikel eens kijken welke renners zich het meest in de top-10 laten zien: dat noemen we dan maar de meest-constante renners.

Bron-gegevens

Etappes

Onder andere op www.touretappe.nl kun je een overzicht van de etappes vinden. Op het tabblad Etappes van het Voorbeeldbestand heb ik die overgenomen. Alle etappes zijn via de optie Koppeling (rechts klikken op een cel) aan een pagina van die site gelinked, zodat de details van een etappe direct zijn te vinden.
Met behulp van voorwaardelijke opmaak zijn de soorten etappes zichtbaar gemaakt. Onderaan wordt het aantal per soort geteld. In cel H26 staat daartoe de formule:
=AANTAL.ALS(tblEtappes[Type];G26)
Tel het Aantal Als in de kolom Type van de Excel-tabel tblEtappes de waarde uit cel G26 (hier Bergen) voor komt.
Van de 21 etappes zijn er dus 9 als berg-etappe gekwalificeerd!

Teams en renners

Op het tabblad Teams van het Voorbeeldbestand staat de definitieve deelnemerslijst (in een Excel-tabel tblTeams) zoals die op de site wielerflits.nl is terug te vinden. Op die site zijn de ploegen en renners voorzien van een landen-vlaggetje; bij het plakken in Excel wordt dit vertaald naar een code. Die kunnen we goed gebruiken om ons overzicht te verrijken met echte landnamen.

Aan de tabel tblTeams zijn daarom 2 kolommen toegevoegd:

  • Nr: ieder team en renner krijgt een nummer: het team van de vorige winnaar heeft nummer 0, de kopman van dat team krijgt nummer 1 en de overige renners krijgen hun nummer in alfabetische volgorde.

NB1 is Dumoulin bijgelovig? Hij heeft nummer 13 geruild met de Noor Grøndahl Jansen.

NB2 de kolom Nr kun je handigst op de volgende manier vullen: de eerste cel (D6) krijgt nummer 0 en in de cel daaronder plaatsen we de formule =D6+1. Deze formule doorvoeren naar alle cellen daaronder. Wis dan in de lege regels de cel in kolom D en vul bij het team het volgende tiental in (Jumbo krijgt dan nummer 10, BORA 20 etc).

  • Land: aan de hand van de vlagcode uit de eerste kolom bepalen we uit welk land het team of renner komt (zie tabblad Landen). Dat zou met VERT.ZOEKEN kunnen, maar we gebruiken liever de universeel toepasbare INDEX-VERGELIJKEN-methode (zie het artikel Zoeken: index en vergelijken, inclusief de avz-truc).

Landen

Aan iedere unieke VlagCd uit het tabblad Teams hebben we een land-omschrijving gekoppeld (in de Excel-tabel tblLand van het tabblad Landen in het Voorbeeldbestand).

In de derde kolom van die tabel (AantRenners) bepalen we het aantal renners per land: =AANTAL.ALS(tblRenners[Land];[@Land])
Turf het Aantal Als in de kolom Land van de tabel tblRenners de waarde uit de kolom Land in deze regel (vandaar de @) voor komt.

NB de tabel tblRenners is terug te vinden op het tabblad Renners van het Voorbeeldbestand; zie hierna.

Punten

Op het tabblad Punten van het Voorbeeldbestand hebben we vastgelegd hoe de puntenverdeling voor de eerste 10 renners van iedere etappe moet zijn.

NB1 mocht het eindresultaat straks niet bevallen, dan kunt u natuurlijk proberen uw favoriete renner te helpen door de puntenverdeling aan te passen 😉

NB2 een totaal-regel onder een Excel-tabel wordt automatisch gegenereerd als de betreffende optie is aangevinkt op de menutab Ontwerpen.

Uitslagen

Uitslag 2e etappe op letour.fr

De uitslagen verwerken is heel eenvoudig: vul van de eerste 10 renners hun rugnummers in bij de betreffende etappe (zie het tabblad Uitslagen van het Voorbeeldbestand).
Op de officiële tour-site www.letour.fr kun je die rugnummers in de uitslagen vinden.

Maar wat als je alleen maar de namen hebt?
(Er zijn waarschijnlijk nog wel meer mensen die dan meteen aan Theo Koomen, of was het Barend Barendse, moeten denken: “Aan namen heb ik niks. Rugnummers moet ik hebben“).
In Excel zijn er dan allerlei opties om het rugnummer te vinden. Hier komen er een paar:

  1. ga naar het tabblad Teams van het Voorbeeldbestand, druk in Ctrl-F, tik een gedeelte van de naam in en klik op Alles zoeken. In het onderste gedeelte van het zoek-scherm komen alle cellen die voldoen.

    Klik op de gewenste naam en u ziet het rugnummer daarnaast staan.
  2. gebruik Index en Vergelijken:

    In cel O2 van het tabblad Uitslagen wordt eerst met behulp van de functie Vergelijken gekeken op welke positie in de kolom Naam van de tabel tblTeams de invoer in cel H2 staat. Deze functie kent zogenaamde ‘wildcards’, dus we hoeven maar een gedeelte van de naam in te tikken (de *’s geven aan dat het er niet toe doet, wat er voor en achter de inhoud van cel H2 staat). Daarna wordt deze positie gebruikt om met behulp van de functie Index het betreffende Nr op te halen.
    Ter controle halen we in cel P2 op een vergelijkbare manier de naam op die hoort bij het rugnummer.
    LET OP Vergelijken geeft de eerste positie terug waarvan de naam voldoet aan de voorwaarde. Is het niet de juiste naam? Tik meer letters in, bijvoorbeeld daniel f om de renner met nummer 76 op te zoeken.
  3. denk je het rugnummer wel ongeveer te weten omdat je het team kent en je weet welk tiental bij deze ploeg hoort:

    De ploeg van Jumbo-Visma begint met renner 11, Dumoulin zit vooraan in het alfabet (en hij is geen kopman!), dus zal het wel 13, 14 of 15 zijn.
    Tik het nummer in in cel O4 en je ziet of je goed hebt gegokt.
  4. Via de menutab Gegevens in het blok Hulpmiddelen voor gegevens is aan cel H6 een Gegevensvalidatie toegewezen:

    Alleen gegevens uit kolom G van het tabblad Teams zijn toegestaan.
    In die kolom G staat voor iedere renner (en team) een koppeling van nummer en naam met een extra spatie daartussen:

    NB kolom G is standaard niet zichtbaar; via Groeperen kan de kolom ‘ingeklapt’ worden.
    LET OP je kunt een kolom ook Verbergen (via rechtsklikken op een kolomletter) maar ik ben daar geen voorstander van: het zichtbaar maken is niet zo makkelijk en vaak zie je niet dat er een kolom verborgen is.
  1. een andere, minder gebruikte, optie is een keuzelijst (met invoervak).
    Kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen.

Klik op de 2e optie binnen de Formulierbesturingselementen.

‘Teken’ nu met de cursor het gebied waar de keuzelijst moet komen.

Dan komt de vraag om het besturingselement op te maken: zorg dat in het Invoerbereik de cellen geselecteerd worden met de namen van de renners en dat er een Koppeling komt met de cel naast het invoervak (zie het tabblad Uitslagen in het Voorbeeldbestand).

Wanneer je nu een naam selecteert dan komt in de gekoppelde cel de positie van deze renner in de lijst te voorschijn. Met behulp van de formule =INDEX(tblTeams[Nr];Uitslagen!N8) wordt het rugnummer opgehaald.

  1. een andere keuzelijst maakt gebruik van Active-X; iets ingewikkelder maar wel een stuk flexibeler.

Kies opnieuw in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen. Maar, let op, klik dan op de 2e optie binnen de Active-X besturingselementen.
‘Teken’ weer met de cursor het gebied waar de keuzelijst moet komen. Nu moet je de Eigenschappen aanpassen: klik op de betreffende button in de menubalk en vul de 4 eigenschappen in zoals hiernaast (achter de pijltjes).

LET OP bij het gebruik van Active-X-elementen moet je de Ontwerpmodus uitzetten, wanneer je deze wilt gebruiken (en andersom als je de eigenschappen wilt aanpassen).

Resultaten per renner

In het tabblad Renners van het Voorbeeldbestand worden de resultaten per renner ‘automatisch’ bepaald; alleen de kolom Nr bevat harde waarden, de overigen worden afgeleid of berekend:

  1. in cel H6 staat de formule:
    =ALS.FOUT(
    INDEX(tblPunten[Punten];
    VERGELIJKEN([@Nr];INDIRECT(“tblUitslagen[“&H$4&”]”);0));
    “”)

    Aangezien cel H4 de waarde 1 bevat, wordt de 2e parameter binnen de Vergelijken-functie INDIRECT(“tblUitslagen[1]”); Excel vertaalt dit dan naar een bereik van cellen en wel de eerste kolom in de tabel tblUitslagen.
    De Vergelijken-functie kijkt dan of het rugnummer in die kolom voorkomt. De positie daarvan (1 tot 10) wordt gebruikt om met behulp van de functie Index het daarbij behorende aantal punten te genereren. Als een renner geen top-10-resultaat in een etappe heeft behaald, dan zou er een foutmelding komen; met de functie Als.Fout zorgen we er voor dat in dat geval de cel gevuld wordt met een lege tekst.
    Deze formule kan naar beneden en rechts gekopieerd, zodat voor alle renners voor alle etappes de resultaten worden bepaald.
  2. in de kolommen Naam en Land worden de gegevens opgehaald uit het tabblad Teams
  3. zo ook voor de kolom Team, behalve dat daarvoor een berekening rond het rugnummer plaats vindt:
    =INDEX(tblTeams[Naam];
    VERGELIJKEN(AFRONDEN.BENEDEN([@Nr];10);tblTeams[Nr];0)
    )
    Het rugnummer wordt dus naar beneden afgerond op het dichtstbijzijnde veelvoud van 10.
  4. in de kolom TotaalPunten wordt het totaal van de renner over alle etappes berekend: =SOM(tblRenners[@[Etap1]:[Etap21]])
  5. Dan blijft er nog 1 kolom over: Rang.
    Via de formule =RANG.GELIJK([@TotaalPunten];[TotaalPunten]) wordt in die kolom per renner de rangorde in het totaal bepaald.

Kies met het driehoekje achter Rang de gewenste sortering en u weet welke renner(s) bovenaan staat/staan.

LET OP wanneer er weer nieuwe uitslagen zijn toegevoegd, worden alle formules automatisch herberekend, maar …. de sortering wordt niet vanzelf aangepast. Die moet u zelf nogmaals uitvoeren.

Resultaten per team

In het tabblad Teams van het Voorbeeldbestand wordt op de ondertussen bekende manier per renner de TotaalPunten van die renner opgehaald. Het totaal per team berekenen we met een gewone SOM-formule.

In datzelfde tabblad staat ook een ranglijst van de teams. De formules daarin mogen geen verrassing meer zijn.

Boven die tabel staat een controlegetal: het totaal aantal punten van alle renners gedeeld door het totaal aantal dat per etappe verdiend kan worden. Dit moet een geheel getal zijn. Met voorwaardelijke opmaak krijgt de cel een kleur.

Resultaten per land

Om het totaal aantal punten per land te bepalen gebruiken we op het tabblad Landen van het Voorbeeldbestand de formule:
=SOM.ALS(tblRenners[Land];[@Land];tblRenners[TotaalPunten])

LET OP gebruik de gegevens van tblRenners en niet van tblTeams anders worden ook de totalen van de teams meegeteld.

Frankrijk heeft de meeste renners rond rijden, logisch (?) dat dit land dan bovenaan staat.

We delen het aantal punten door het aantal renners per land en we krijgen een andere ranglijst.

Resultaten per land en team

In het tabblad OvzLandTeam van het Voorbeeldbestand staat een draaitabel op basis van de tabel tblRenners. En de rijen én de kolommen worden daarin automatisch gesorteerd (zie ook het artikel Kindernamen).

Bovenin ziet u ook weer een controlegetal; als de uitslagen compleet zijn ingevuld zal dit een geheel getal zijn.

Genormeerde resultaten per land

Het tabblad OvzLandTeam van het Voorbeeldbestand bevat ook een draaitabel, nu op basis van de tabel tblLand.

Per land wordt het aantal renners geteld met daarnaast het aantal genormeerde punten (ofwel het totaal aantal punten gedeeld door het aantal renners).

NB in het hele Tour de France-systeem worden alle overzichten direct geactualiseerd na invoer van een uitslag, omdat die allemaal met formules zijn opgebouwd. Dat geldt niet voor de 2 Ovz-tabbladen: dat zijn draaitabellen en die moeten na het opvoeren van nieuwe uitslagen handmatig Vernieuwd worden (met de muis rechtsklikken op een cel in de draaitabel).

LET OP allebei de draaitabellen dienen Vernieuwd te worden aangezien ze op verschillende bronnen zijn gebaseerd.


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


Hoog-Laag-Slot-grafiek

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!


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

Tabellen (deel 2)

TabelIn mijn vorige artikel (Kunst en Excel) heb ik 11 voordelen van het gebruik van tabellen in Excel besproken.

Op een forum voor Excel-experts heb ik gevraagd of er ook nadelen zijn aan tabellen.

Daar zijn 2 reacties op gekomen, waarvan de tweede aanleiding is geweest voor dit artikel.

Nadelen Tabellen in Excel

De eerste reactie ging over de naam, die de tabel automatisch krijgt (Tabel1, de volgende Tabel2 etcetera). De naam moet dan nog aangepast worden om zodoende een zinvolle beschrijving te krijgen; tsja, hier zie ik zo gauw geen echt probleem (en ook geen oplossing).
Maar ook werd aangegeven, dat wanneer de tabel uit de werkmap wordt verwijderd de naam nog wel actief zou blijven. Zover ik kan nagaan, geldt dat wel voor cellen (of celbereiken) die een naam krijgen en daarna worden verwijderd, maar niet voor tabellen!

In de tweede reactie werd aangegeven, dat tabellen niet bij een gegevensvalidatie kunnen worden gebruikt.
Ja, daar heeft Microsoft een steek laten vallen: standaard kan alleen een celbereik als keuzelijst worden opgegeven. Wel is het mogelijk om een ‘gewone’ naam als bron daarvoor op te geven, maar dan moet je in de tabel de gewenste reeks een aparte naam geven en er voor zorgen, dat bij uitbreiding van de lijst deze naam ook wordt aangepast.
Maar voor dit probleem hebben we gelukkig wel een oplossing!

Gegevensvalidatie

TabelIn het Voorbeeldbestand heb ik een tabblad Param opgenomen, waar 2 tabellen staan:

  1. in het eerste blok staan afkortingen en omschrijvingen voor alle afdelingen die kunnen worden gebruikt.
    De tabel heeft de naam tblAfd gekregen (hoe je een tabel maakt en de naam kunt aanpassen is in het artikel Kunst en Excel besproken).
  2. daarnaast staat een overzicht van de mogelijke kostensoorten; deze tabel heeft de naam tblKst

Deze twee tabellen gaan we gebruiken voor een gegevensvalidatie:

  1. Tabelde invoer in cel B3 van het tabblad GegVal moet beperkt worden tot die drie afkortingen voor de afdelingen, die in Param zijn opgenomen (dus D, C en O).
  2. kies de menuoptie Gegevens en dan binnen het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie:
    Tabel
  3. Tabelin het vervolgscherm kiezen we bij Toestaan de optie Lijst.
  4. dan komt de optie Bron tevoorschijn; klik op Tabel en selecteer dan de cellen B3 tot en met B5 in het tabblad Param.
  5. Klik OK. Aan cel B3 in GegVal is nu een keuzepijltje toegevoegd; als je daar op klikt kun je één van de drie mogelijkheden kiezen.

Op dezelfde manier kan ook de invoer in cel C3 beperkt worden tot de drie mogelijke kostensoorten uit Param (de cellen E3 t/m E5).

Door de cellen B3 en C3 te kopiëren kan de gegevensvalidatie uitgebreid worden naar andere cellen.

Een groot nadeel is, dat als er nieuwe opties bijkomen voor de afdelingen en/of de kostensoorten de betreffende gegevensvalidaties moeten worden aangepast.

Gegevensvalidatie met Tabellen

In plaats van een verwijzing naar de cellen B3 t/m B5 in het tabblad Param (als bron geven we dan op =Param!$B$3:$B$5) zouden we de tabelverwijzing =tblAfd[Afd] willen gebruiken (dus de kolom Afd in de tabel tblAfd).
Als we dat doen krijgen we een foutmelding, maar die kunnen we omzeilen door de functie INDIRECT te gebruiken.

Deze functie zet een tekst om in een Excel-verwijzing. Als voorbeeld: INDIRECT(“B”&A1) geeft een verwijzing naar de cel B1 als A1 de waarde 1 bevat, maar verwijst naar B2 als de waarde van A1 gelijk is aan 2 etc.

Door als bron voor de gegevensvalidatie in te voeren =INDIRECT(“tblAfd[Afd]”) krijgen we toch het gewenste resultaat.

TabelBekijk in het tabblad Boekingen1 de gegevensvalidatie van de cellen in kolom C en E. Deze maken gebruik van deze methode.

Voor de opvoer van boekingen in dit tabblad is ook gebruik gemaakt van een tabel. Een voordeel is dat bij de opvoer van een nieuwe regel (record) onderaan de gegevensvalidatie ook direct wordt overgenomen.
Hetzelfde geldt voor de formules in de kolommen D en F, waar met behulp van VERT.ZOEKEN de omschrijving van de gekozen afdeling of kostensoort wordt opgehaald.

In het tabblad Boekingen2 worden alleen de omschrijvingen van de afdelingen en de kostensoorten ingevoerd. Ook de invoer hiervan is via gegevensvalidatie ´beveiligd´.

Wanneer er nu een afdeling aan het tabblad Param wordt toegevoegd (bijvoorbeeld H met als omschrijving HRM) dan zullen alle gegevensvalidaties automatisch deze mogelijkheid meenemen. Uiteraard geldt dit ook voor de uitbreiding van het aantal kostensoorten.

LET OP: in het tabblad GegVal wordt HRM niet als keuzemogelijkheid getoond, omdat daar de gegevensvalidatie ‘hard’ aan de cellen B3 t/m B5 van Param  is gekoppeld.


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

Cel.Lezen

Een paar weken geleden kreeg ik via de website ginfo.nl een vraag over de Excel-functie Cel.Lezen.

Ik begon aan mijn geheugen te twijfelen, want ik (her)kende de functie niet.
Dus maar even gegoogeled; wat bleek: Cel.Lezen is een restant uit een ver verleden, toen VBA nog niet geïmplementeerd was, een zogenaamde Excel4Macro.
cellezenCel.Lezen is één van de vele functies, die niet meer gedocumenteerd zijn, maar nog wel gebruikt kunnen worden; niet direct, als een standaard-functie, maar via een omweg (het gebruik van de optie Namen beheren).
Steeds meer mogelijkheden van Cel.Lezen zijn (of worden) in nieuwere versies van Excel ingebouwd, maar soms kan deze functie toch nog goed bruikbaar zijn.
En los daarvan: het is nooit weg om nog eens met het onderdeel Namen van Excel te stoeien!

Gebruik van Cel.Lezen

Wat kun je doen met Cel.Lezen?
Deze macro/functie levert 66 (!) verschillende soorten informatie over de inhoud of de opmaak van een cel. CelLezenHierbij valt te denken aan de celverwijzing (welke rij, welke kolom) maar ook de inhoud, of de cel een formule bevat, welke kleuren gebruikt zijn etc.
In het Voorbeeldbestand is in het tabblad InfoType een totaal-overzicht opgenomen.

De functie Cel.Lezen heeft 2 parameters nodig: de eerste is het InfoTypeNummer en de tweede is de cel, waarvan de informatie opgehaald moet worden.

CelLezenZoals hiervoor al aangegeven, kan Cel.Lezen niet rechtstreeks aangeroepen worden; dit kan wel via een eigen formule/naam geregeld worden.

Een voorbeeld (zie tabblad VB in het Voorbeeldbestand):
het is altijd goed om te weten of en waar in een kolom (bijvoorbeeld C) formules staan.
We gaan dat in kolom D als volgt aangeven:

  1. plaats de cursor in cel D3
  2. kies binnen de menu-tab Formules de optie Namen beheren en dan de button Nieuw
  3. CelLezenvul het scherm in:
    * kies als naam BevatFormule
    * het bereik, waar deze naam geldig is, beperken we tot het betreffende tabblad (zie hieronder), dus Vb
    * en we verwijzen niet, zoals gebruikelijk is voor een Naam, naar een cel(bereik), maar naar een formule, namelijk
    =CEL.LEZEN(48;C3)
    (InfoTypeNummer 48 geeft als resultaat WAAR als de betreffende cel een formule bevat)
    * klik OK
  4. in het overzichtsscherm Namen beheren zien we onze nieuwe naam
  5. tik in cel D3 de formule =BevatFormule
  6. kopieer de formule in D3 naar beneden tot en met cel D15

NB1 als je goed kijkt in het overzichtsscherm Namen beheren, dan zul je zien dat onze formule iets is aangepast: aan cel C3 is ook de bladnaam Vb toegevoegd; dit is niet te voorkomen. Dat is ook de reden, dat we hiervoor de naam alleen maar geldig maken op het tabblad Vb

NB2 we hebben een relatieve verwijzing naar C3 gebruikt (zonder $-tekens); dit zorgt er voor, dat de formule in D4 naar C4 ‘kijkt’ etc.
Het effect hiervan zie je ook in Namen beheren: de formule bij Verwijst naar: is afhankelijk van de plaats van de cursor in het tabblad.

BevatFormule (vervolg)

Hiervoor is met behulp van de zelf-gedefinieerde naam BevatFormule een mogelijkheid gecreëerd om zichtbaar te maken of cellen al dan niet een formule bevatten.

In het tabblad Vb2 van het Voorbeeldbestand is een alternatieve methode gebruikt:
op dezelfde manier als hiervoor is, met cel D3 geselecteerd, een nieuwe naam gemaakt, BevatFormule2.
Deze naam is alleen geldig in Vb2 en heeft als verwijzing
=CEL.LEZEN(48;D3)

LET OP deze formule verwijst dus naar de cel zelf. Wanneer we ergens in het tabblad dan ook intikken =BevatFormule2, dan zal het resultaat altijd WAAR zijn!

Deze naam is in het voorbeeld gebruikt om via Voorwaardelijke opmaak zichtbaar te maken waar ergens in kolom C een formule wordt gebruikt.

NB de naam BevatFormule2 kan ook voor de gehele werkmap werkend worden gemaakt: wijzig de verwijzing naar cel D3 in INDIRECT(“RK”;Onwaar)

Overzicht

CelLezenOm de werking van Cel.Lezen verder te verduidelijken is in het Voorbeeldbestand het tabblad Ovz opgenomen. Hierbij is gebruik gemaakt van de naam CelInfo met als formule
=CEL.LEZEN($C$2;B5)
LET OP
de eerste parameter is absoluut, de tweede relatief: waar de formule ook gebruikt wordt, het InfoType is altijd de inhoud van cel C2

Door in cel C2 een nummer tussen 1 en 66 in te voeren wordt in C5:C9 de informatie over de overeenkomende cellen in kolom B weergegeven.

Met behulp van InfoTypeNummer=1 zien we de (absolute) celverwijzingen etc.

Herberekenen

Eén van de nadelen van dit soort implementatie van functies is, dat Excel het resultaat niet altijd bijwerkt.
Excel probeert namelijk zo efficiënt met de rekentijd om te gaan en beoordeelt na het aanpassen van een cel welke andere cellen allemaal opnieuw berekend moeten worden.
CelLezenMaar, wanneer je bijvoorbeeld de achtergrondkleur van een cel wijzigt, zal Excel geen enkele cel spontaan gaan herberekenen. En helaas ook niet wanneer we Excel daartoe dwingen door op F9 (Handmatig herberekenen) te drukken.

Wijzig in het tabblad Kleur van het Voorbeeldbestand bijvoorbeeld de achtergrondkleur van cel B3 in grijs. Wat gebeurt er met C3? En als je op F9 drukt? Niets dus.
Het enige wat helpt, is om cel C3 te selecteren, op F2 te drukken (inhoud cel wijzigen) en dan op Enter.
Voor de code-kolom is als Naam de volgende formule gebruikt:
=CEL.LEZEN(38;B3).

Maar voor alles (?) is een oplossing. We moeten gewoon zorgen dat onze formule een element bevat, dat bij een herberekening altijd een nieuwe waarde krijgt. Een voorbeeld van zoiets is de functie Nu(), die bij iedere wijziging in Excel opnieuw wordt bepaald.
Dus nogmaals een Naam vastleggen (CelKleur2):
=CEL.LEZEN(38+Nu()*0;B3).
Wijzig één van de kleuren in kolom E en druk op F9: et voilà!

NB1 de functie Nu() levert een getal op; dat zou de inhoud van de eerste parameter veranderen, dat is natuurlijk ook weer niet de bedoeling. Vandaar het vermenigvuldigen met 0.

NB2 de truc om te zorgen dat Excel wat ‘actiever’ is bij het herberekenen heb ik ooit op de site van Jan Karel Pieterse gevonden: jkp-ads.com.

Waarschuwingen

Waarschuwing1: aangezien Cel.Lezen voor Excel een soort macro is, moet een bestand, dat gebruik maakt van deze formule, opgeslagen worden met de extensie xlsm.

Waarschuwing2: bij het googelen kwam ik enkele keren tegen, dat het kopieren van cellen met verwijzingen naar Cel.Lezen naar andere tabbladen, kan zorgen voor een crash van Excel. OEPS!

Waarschuwing3: of Microsoft de  Excel4Macro’s in een volgende versie nog zal blijven ondersteunen is maar de vraag.


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