Deze week kwam de vraag binnen of het mogelijk was om met behulp van een formule gegevens uit een tabel, die aan een bepaalde voorwaarde voldoen, te selecteren. De eerste reactie was: hup, dat doen we even. Maar dat viel toch nog tegen.
Want niet iedereen heeft de beschikking over Excel 2021 of Excel 365. Daar ben je met één formule in een cel klaar. Maar niet getreurd: er zijn altijd alternatieven denkbaar.
Nieuwe Filter-functie
Meer specifiek was de vraag hoe de namen in een bestand met een formule zouden kunnen worden geselecteerd op basis van de selectie in kolom D (zie Voorbeeldbestand).
Vanaf Excel-2021 en -365 bestaan er zogenaamde Spill-functies (ik kwam ergens de Nederlandse benaming Overloop-functies tegen). Het speciale van dit soort functies is dat wanneer je een formule met zo’n functie in een cel plaatst, het resultaat over meerdere cellen terecht kan komen (vandaar spill en overloop). In dit geval plaatsen we ergens de formule =FILTER(Tabel1[Naam];Tabel1[Selectie]=Waar;”Niets gevonden”) en het resultaat is dan alle namen ‘met een vinkje’ in cellen onder elkaar.
‘Oude’ oplossing1
Maar heb je niet de beschikking over deze nieuwe functies dan zul je iets anders moeten bedenken.
Een oplossing is om op basis van de tabel met namen en selecties een draaitabel te maken. Plaats het veld Naam in het vak Rijen en Selectie in het vak Filters (of maak een slicer zoals in het Voorbeeldbestand). Uiteraard kiezen we bij Selectie dan alleen de optie Waar.
In het voorbeeld is ook een lege naam aangevinkt en deze komt in de rij (leeg) tevoorschijn.
NB Excel sorteert alle namen automatisch op alfabetische volgorde.
Wil je de oorspronkelijke volgorde aanhouden, plaats dan ook Nr in het vak Rijen.
In het voorbeeld hiernaast zijn ook de geselecteerde lege namen in de draaitabel uitgefilterd.
Maar helaas deze oplossing voldeed niet aan de verwachtingen: “Kan het ook met een formule zodat er na het aanvinken van de personen geen extra handeling (het vernieuwen van de draaitabel) meer nodig is?“
Oplossing2
Voor deze oplossing hebben we een hulpkolom nodig (zie kolom E in het Voorbeeldbestand). Daar geven we iedere geselecteerde naam een eigen volgnummer.
In de eerste regel (cel E3) plaatsen we de formule =ALS(D3;1;0) Dus als cel D3 de waarde Waar bevat dan wordt het volgnummer 1 anders 0.
Cel E4 krijgt de formule =E3+D4 We maken hier gebruik van het feit dat Excel de waarde Waar bij een berekening omzet naar 1 (en Onwaar naar 0). Cel E4 kan dan naar beneden gekopieerd worden.
NB we hadden in cel E3 dus ook de formule =D3+0 kunnen plaatsen.
En nu het resultaat. In cel N3 plaatsen we de formule =ALS.FOUT(INDEX($C$3:$C$32;VERGELIJKEN(B3;$E$3:$E$32;0));””)
Als eerste gaan we de waarde van de teller in B3 Vergelijken met de hulp-kolom E. De derde parameter is een 0, dus Excel zoekt een exacte match en dan ook nog de eerste die hij (of zij?) tegenkomt.
Op basis van de gevonden regel in de tabel haalt INDEX de overeenkomende naam op. Als het ergens mis gaat (bijvoorbeeld als het volgnummer niet voorkomt in de hulpkolom) dan is het resultaat “” (niets dus).
In de kolommen F en G staan 2 alternatieven voor de bepaling van de hulpvariabele: cel F3 bevat de formule =AANTAL.ALS($D$3:D3;WAAR). Deze kan naar beneden gekopieerd worden waarbij de eerste D3 blijft staan (deze is absoluut) en de tweede wordt D4, D5 et cetera (een relatieve verwijzing).
Oplossing3
Deze oplossing bouwt door op het vorige artikel op de website van G-Info over de Aggregaat-functie. Bekijk de formule in cel O3 van het Voorbeeldbestand: =ALS.FOUT(INDEX(C$3:C$32;AGGREGAAT(15;6;(RIJ($D$3:$D$32)-RIJ($D$3)+ 1)/($D$3:$D$32 =WAAR);RIJ($A1))); “”)
Gebruik de optie Formule evalueren om de werking te onderzoeken.
Ik kwam pas een (oud) artikel van Chandoo tegen, waarin hij aangeeft dat hij af en toe door de bomen het bos niet meer ziet: zijn artikelen en video’s genereren zoveel reacties, dat hij niet snel meer ziet of er vragen bijzitten die beantwoord moeten worden.
Zijn oplossing gaan we in dit artikel vanG-Infoeens onder de loep nemen.
Probleem
Chandoo wilde dus snel zijn reacties filteren, zodat hij alleen vragen overhield. Door alleen te selecteren op het feit of een zin een vraagteken bevat, levert meestal niet het gewenste resultaat op (zoals uit het simpele voorbeeld hiernaast mag blijken).
Zijn oplossing was om om te onderzoeken of bepaalde woorden voorkomen in de reacties. Dat bleek nog niet voldoende: ook bepaalde combinaties van woorden kunnen aangeven dat het een vraag betreft:
Oplossing
Om te controleren of een zin een vraag bevat, moeten we analyseren of één of meerdere ‘Vraag-woorden‘ in de zin voorkomen.
In het Voorbeeldbestand zijn de woorden die aangeven of we te maken hebben met een vraag opgenomen in een Excel-tabel met de naam tblVrWoorden (zie kolom B in het tabblad Vraag). De tabel-kolom met woorden heeft de naam Vraag-woorden.
NB voor uitleg over de voordelen van en toelichting op het gebruik van Excel-tabellen zie de artikelen Kunst en Excel en Tabellen (deel 2).
In cel E3 staat de formule =AANTAL.ALS(D3;”*”&tblVrWoorden[Vraag-woorden]&”*”)
Ofwel: bepaal het aantal keren dat één van de mogelijke Vraag-woorden voorkomt in D3.
Controleer de werking van de formule in E3 door middel van de menu-optie Formule evalueren in de menutab Formules. Klik op Evalueren en Excel zal de stap die onderstreept is uitvoeren:
Blijf op Evalueren klikken om alle berekeningstappen na te lopen. Doe je dit ook voor de cellen E4, E5 etcetera dan is duidelijk te zien dat Excel telkens maar één vraagwoord onder de loep neemt in plaats van de hele reeks.
NB de functie Aantal.Als herkent het gebruik van wildcards; door vóór en achter de vraagwoorden een * te plaatsen geven we aan, dat er nog iets vóór of achter het woord (of woordcombinatie) mag staan ofwel dat zo’n woord willekeurig ergens in de tekst kan voorkomen.
Maar doe nu eens het volgende:
selecteer cel E6
klik in de formulebalk achter de formule
druk op het toetsenbord op de functietoets F9 (Berekenen)
de formule verandert in: ={0;0;0;1;0;0;1;0;0;0;0}
druk NIET op Enter maar op Esc
NB je kunt ook een gedeelte van een formule laten berekenen: selecteer bijvoorbeeld in de formulebalk alleen het gedeelte achter de punt-komma en vóór het haakje-sluiten en druk dan op F9.
Nu is te zien, dat Excel toch slimmer is dan we dachten! Hij heeft wel alle Vraag-woorden vergeleken met de inhoud van cel D6 en geconstateerd dat er 2x sprake is van een vraag (de 4e en 7e optie). De functie Aantal.Als levert een array van resultaten op en daar kunnen we gebruik van maken.
In kolom F bepalen we de Som van de waardes uit de array, die door Aantal.Als is gegenereerd.
LET OP we moeten Excel wel ‘influisteren’ dat we te maken hebben met een array-berekening: sluit de formule NIET af met Enter maar met Ctrl-Shift-Enter, de zogenaamde CSE-methode.
Een alternatieve oplossing, waarbij de CSE-methode niet nodig is, is het gebruik van de functie SomProduct (zie kolom G). Lees ook het artikel SOMPRODUCT: meer dan SOM en PRODUCT.
Ander voorbeeld
Een garage heeft een goederenoverzicht met codes en omschrijvingen (zie het tabblad Onderdelen van het Voorbeeldbestand). Om de efficiency en de consistentie te waarborgen wordt afgesproken dat aan de servicecorner deze codes ook gebruikt moeten worden bij het aanmaken van de werkbonnen.
Om verschil te maken met bijvoorbeeld klantnummers wordt vóór een goederencode een # geplaatst.
In kolom C kunnen we met een vergelijkbare formule als hiervoor beoordelen of er sprake is van een onderdeel (let op het # achter het eerste sterretje).
Als we de garage dan toch automatiseren: in kolom D zoeken we, als er sprake is van een Onderdeel, het betreffende nummer op: =ALS([@[Onderdeel?]]=”Ja”;WAARDE(DEEL([@Opmerking];VIND.ALLES(“#”;[@Opmerking])+1;7));””) en in kolom E de daarbij behorende omschrijving: =ALS([@[Onderdeel?]]=”Ja”;INDEX(tblOnderdelen[Omschrijving]; VERGELIJKEN([@[Welk?]];tblOnderdelen[Goederencode];0));””)
Nog een voorbeeld
Een school werkt niet met cijfers voor proefwerken met met lettercodes (zie het tabblad Proefwerk van het Voorbeeldbestand).
Ook ligt in een tabelletje vast welke codes overeenkomen met een voldoende.
Nu wordt per leerling in kolom P de tabel met voldoende-codes vergeleken met de 10 proefwerkkolommen en wordt het aantal voldoendes bepaald. Nog wat Voorwaardelijke opmaak en we zien snel hoe de diverse leerlingen er voor staan.
LET OP dit is weer een CSE-formule.
NB1 telt nv niet mee als voldoende (de betekenis is ineens niet voldoende 😉) dan verwijderen we de betreffende rij in de tabel tblVoldCd. Het resultaat in kolom P past zich direct aan.
NB2 om te zien wat de invloed is van de proefwerk-resultaten op kolom P staat vanaf rij 10 in het tabblad Proefwerk een overzicht waarbij de resultaten bij iedere druk op de F9-toets willekeurig veranderen.
Cadeaus
Decembermaand is de tijd van cadeaus. Voor de liefhebbers hier 2 links:
Chandoo: uitleg van diverse ‘interessante’ formules
How to Excel: een overzicht van diverse Excel-websites
Overal kom je ze tegen, histogrammen: een grafische weergave van een frequentietabel.
Iedereen die met Excel werkt, heeft wel eens zoiets gemaakt.
In dit artikel laten we diverse mogelijkheden de revue passeren om zo’n overzicht te maken. We zoomen daarbij vooral in op verschillende methoden om een frequentietabel te maken. Volgende keer komen diverse alternatieven voor het histogram aan bod.
Voor het maken van een frequentietabel is de functie INTERVAL heel erg handig. Maar ook enkele ‘vreemde’ eigenschappen daarvan komen aan bod (zoals beloofd in Excel en kaarten 2).
Basis
Zoals gezegd: je komt ze bijna dagelijks tegen, histogrammen (en dus ook frequentietabellen). Ieder krantenartikel, waarin aantallen voorkomen, wordt wel verduidelijkt (?) met een grafiekje. Maar ook HR-medewerkers zijn er verzot op (leeftijds-, functie- en salarisopbouw kunnen daar goed mee worden geïllustreerd) en ook leerkrachten in het onderwijs gebruiken het veel (om de verdeling van proefwerkpunten te bepalen bijvoorbeeld).
In het Voorbeeldbestand op het tabblad Basis staat een fictief overzicht van de resultaten van 5 proefwerken van 10 leerlingen. Met behulp van de functie ASELECTTUSSEN worden (bij een wijziging in Excel of het drukken op F9) iedere keer nieuwe resultaten tussen 2 en 10 gegeneerd (inclusief die 2 grenzen).
Op hetzelfde tabblad staat een vergelijkbaar overzicht maar daar krijgen de proefwerkresultaten 1 decimaal. De gebuikte formule wordt dan: =ASELECTTUSSEN(20;100)/10
Deze overzichten gebruiken we in het vervolg van het artikel als voorbeeld-input.
Frequentietabel 1
We beginnen met een proefwerkoverzicht zonder decimalen. Daarvan gaan we een frequentietabel maken: hoe vaak komt ieder cijfer voor.
NB direct is te zien dat het voorbeeld niet echt reëel is: het cijfer 10 komt wel heel vaak voor! Maar hoe de verdeling van de overige cijfers is, is zelfs met dit kleine aantal moeilijker te beoordelen.
Op het tabblad FreqTabel1 van het Voorbeeldbestand staat ons eerste resultaat. In kolom J wordt de absolute frequentie van ieder mogelijk cijfer geturfd: =AANTAL.ALS($C$3:$G$12;I3)
In cel J3 tellen we het aantal keren, dat de waarde uit I3 voorkomt in het bereik C3:G12.
NB ter controle tellen we onderaan in kolom J de frequenties op, zodat we zeker weten dat alle cijfers zijn meegenomen. Selecteer de cel onder de frequenties en druk op Alt-= (dus de Alt-toets vasthouden en op = drukken). Deze sneltoets werkt ook als je getallen in een rij wilt optellen.
In kolom K bepalen we de cumulatieve absolute frequentie door bij het vorige resultaat in die kolom het resultaat uit kolom J op te tellen (in cel K4 staat de formule =K3+J4).
De formules in de kolommen L en M, waarin de relatieve en cumulatieve relatieve frequenties worden bepaald, spreken voor zich.
Absolute frequentie 2
Een alternatieve methode voor het bepalen van de absolute frequentie is het gebruik van de Excel-functie Interval (zie het tabblad FreqTabel1 van het Voorbeeldbestand).
Allereerst de volgende waarschuwing: Interval is een ‘vreemde’ functie. Niet alleen de input wordt gevormd door een gebied/bereik van cellen (net als bij Som bijvoorbeeld) ook het resultaat bestaat uit meer dan één getal. Het is een zogenaamde matrix-functie.
selecteer eerst de cellen waar het resultaat moet komen. Interval levert de frequenties op, horend bij een gewenst interval. In dit geval bij de cijfers 1 t/m 10. Dus het resultaat moet uit 10 cellen bestaan. LET OP het resultaat van de functie Interval moet altijd in een kolom komen, dus selecteer cellen ONDER ELKAAR. In het voorbeeld de cellen N3:N12.
tik in =interval(
voer dan de eerste parameter van de functie in, de Gegevensmatrix. Dit zijn de brongegevens, in dit geval het bereik C3:G12.
tik in ;
dan komt de tweede parameter, de Interval_verw. Dit moeten cellen zijn die de gewenste intervallen aangeven; in het voorbeeld de cellen I3:I12.
tik in )
sluit de functie NIET af door op Enter te drukken, maar tegelijkertijd op Ctrl-Shift-Enter. Op deze manier wordt een matrix-formule ingevoerd, ook wel een CSE-formule genoemd. In de 10 cellen staat nu overal exact dezelfde formule: {=INTERVAL(C3:G12;I3:I12)} LET OP de accolades hebben we niet zelf ingevoerd; dit is een indicatie dat hier een matrix-formule staat. Onderdelen van een matrix-formule kunnen niet worden gewijzigd, er kunnen geen regels tussengevoegd worden etc. Wil je een wijziging doorvoeren: selecteer alle bij elkaar horende cellen, klik in de Formulebalk en druk op Ctrl-Enter. Nu wordt in alle cellen dezelfde formule ingevoerd, maar niet als matrix-formule en dus horen ze niet meer bij elkaar. NB in de Engelstalige Excel-versies heet deze functie Frequency; dit geeft beter de bedoeling van de functie aan dan Interval.
NB1 het is in een matrix-formule niet nodig om de bereiken absoluut (met $-tekens) in te voeren.
NB2 hiervoor hebben we net gedaan of de Interval-functie de frequenties van de diverse cijfers heeft geturfd, maar eigenlijk zijn de waardes in het bereik Interval_verw de te hanteren bovengrenzen van de diverse intervallen. Maar aangezien de proefwerkpunten geen decimalen bevatten, zijn de bovengrenzen ook de enige cijfers die in dat interval voorkomen.
Cumulatieve absolute frequentie 2
In cel O3 van het tabblad FreqTabel1 van het Voorbeeldbestand hebben we de formule =INTERVAL($C$3:$G$12;I3) geplaatst. Er is maar 1 bovengrens van een interval, dus ook maar 1 resultaat. De formule hoeft dan ook niet met CSE afgesloten te worden, een Enter volstaat.
Deze formule turft het aantal keren, dat een getal uit het bereik van de eerste parameter kleiner of gelijk is aan de waarde in cel I3. In dit geval dus <=1; dat komt in het voorbeeld niet voor. Wanneer deze formule naar beneden gekopieerd wordt krijgen we de gewenste cumulatieve resultaten.
Histogram
Bij een frequentietabel hoort ook de grafische weergave, een histogram.
Maak een grafiek met daarin de absolute frequentie en de cumulatie daarvan, zorg dat op de x-as de intervalgrenzen komen (dus de cijfers 1 t/m 10) en zorg dat het een combinatiegrafiek wordt met de cumulatieve waarden uitgezet op de secundaire as.
LET OP kies bij het gebruik van een secundaire as de maximale waarden van de assen zodanig dat de horizontale rasterlijnen aan allebei de kanten bij weergegeven getallen uitkomen. In dit geval is het maximum rechts 4x groter dan links.
In een volgend artikel zullen we diverse andere methoden voor het maken van een histogram de revue laten passeren met daarbij de voor- en nadelen van de diverse alternatieven.
NB wanneer de brongegevens in de loop van de tijd nog uitgebreid worden, plaats deze dan in een Excel-tabel. Na uitbreiding hiervan wordt automatisch de frequentietabel geactualiseerd (zie het tweede blok op het tabblad FreqTabel1 van het Voorbeeldbestand). In het voorbeeld was het dan wel logischer geweest als ik de proefwerken in de rijen had geplaatst en de leerlingen in de kolommen! Maar in mijn-tijd-als-docent was dit wel de indeling in de lerarenagenda:
Frequentietabel 2
We gebruiken hetzelfde soort proefwerkoverzicht als in het vorige hoofdstuk, maar nu met 1 decimaal (zie het tabblad FreqTabel2 van het Voorbeeldbestand). Dankzij de voorwaardelijke opmaak van Excel zien we snel waar de hoge en lage punten zitten.
Hadden we dat vroeger in de lerarenagenda ook maar zo makkelijk gehad. Toen gebruikten we gekleurde pennen (exacter uitgedrukt: pennen met gekleurde inkt) met alle problemen van dien, als een cijfer achteraf nog aangepast moest worden.
Om de voorwaardelijke opmaak te kunnen toepassen gebruiken we een hulpkolom I; in de cellen I3:I6 staan de cijfers 4, 6, 8 en 10. De regels voor de voorwaardelijke opmaak zien er als volgt uit:
De vorige opmaak leverde wel een bonte kermis op. Het mag wel wat subtieler. Maar daardoor is het iets moelijker om te zien in welke categorie een cijfer valt.
De gebruikte regels:
Hé, wat vreemd. Alle cijfers krijgen de goede kleur maar zijn allemaal cursief en vet, terwijl dat in de opmaakregels alleen voor de laagste en hoogste punten is ingesteld. Als deskundige Excel-ler hebt u natuurlijk al gezien hoe dat komt. We nemen als voorbeeld het cijfer 5,6 : dit voldoet niet aan de eerste regel maar wel aan de tweede, dus krijgt het een oranje opmaak. Maar hij voldoet ook aan de derde regel! Dus zou het cijfer een licht-groene opmaak moeten krijgen, maar dat kan Excel niet: én oranje én licht-groen. Het cijfer krijgt de eerste kleur. 5,6 voldoet ook aan regel 4. Donker-groen maken kan Excel niet meer, maar wel cursief en vet.
We moeten ook de laatste kolom binnen de opmaak-regels gebruiken (Stoppen indien Waar). Als een cijfer aan een regel voldoet dan worden de volgende regels niet meer uitgevoerd.
NB1 Het laatste vinkje hoeft niet meer; de opmaak-routine stopt toch al
NB2 bij het opmaken van het ‘kermis-overzicht’ is de stop-optie niet nodig omdat ook hier geldt dat Excel maar één opvulkleur aan een cel kan toewijzen.
Even genoeg over de opmaak; dit artikel gaat over frequentietabellen. Om de proefwerkresultaten met decimalen in de juiste categorie onder te brengen gebruiken we weer de functie Aantal.Als.
Maar wel iets ingewikkelder dan hierboven: in cel K3 staat de formule =AANTAL.ALS($C$3:$G$12;”<=”&I3) Turf het aantal getallen in het bereik C3:G12, die voldoen aan de voorwaarde dat ze kleiner of gelijk zijn aan de waarde in cel I3.
LET OP de voorwaarde moet een tekst-vorm hebben: dus de tekst <= (zie de “-tekens) wordt met behulp van & samengevoegd met de inhoud van cel I3.
Deze is formule is naar beneden gekopieerd. Helaas, nu hebben we niet de frequenties maar de cumulatieven. Geen nood, in kolom L gaan we de frequenties bepalen. De eerste berekening is oké. Dus cel L3 is hetzelfde als K3. In cel L4 plaatsen we de formule =AANTAL.ALS($C$3:$G$12;”<=”&I4)-L3. Maar die voldoet niet als we die “naar beneden kopiëren”: we hadden =AANTAL.ALS($C$3:$G$12;”<=”&I5)-SOM($L$3:L3) moeten gebruiken.
In kolom M hebben we nog een alternatieve berekening voor de frequenties: M3 is weer gelijk aan K3, in M4 staat de formule =AANTALLEN.ALS($C$3:$G$12;”<=”&I4;$C$3:$G$12;”>”&I3) Dus een Als met 2 voorwaarden, dat kan alleen met de functie AantalLEN.als. Deze formule kan naar beneden gekopieerd worden.
NB wil je toch Aantal.Als gebruiken: in cel M4 had ook de formule =AANTAL.ALS($C$3:$G$12;EN(“<=”&I4;”>”&I3)) kunnen staan. Hierbij zijn met behulp van de functie EN 2 voorwaarden gecombineerd tot 1.
Zoals uit de vorige alinea’s mag blijken is het maken van een frequentietabel met behulp van Aantal.Als niet altijd even makkelijk. We mogen blij zijn dat Excel de functie Interval kent:
selecteer de cellen N3:N6
tik in de de formulebalk in: =INTERVAL(C3:G12;I3:I6)
druk op Ctrl-Shift-Enter
klaar!
NB1 niet tevreden met de grenzen van de intervallen? Is alles boven 5,5 een voldoende? Wijzig de inhoud van cel I4 in 5,5 en de hele sheet wordt automatisch aangepast. Niet alleen de frequentietabellen maar ook de voorwaardelijke opmaak.
Maar wat gebeurt er als je de 10 in cel I6 wijzigt in 9? Dan worden niet alle punten in de frequentietabel meegeteld; dat is niet de bedoeling. Gelukkig heeft Microsoft daar rekening mee gehouden.
Ter verduidelijking bevat het tabblad FreqTabel2 nog een ander overzicht:
selecteer de cellen J10:J22
tik in de de formulebalk in: =INTERVAL(C3:G12;I10:I19)
druk op Ctrl-Shift-Enter
Bewust is het resultaatbereik groter gekozen dan we gewend zijn; de eerste 10 regels zijn logisch: eerst wordt het aantal proefwerkresultaten kleiner of gelijk aan 1 geturfd (0 dus), dan hoeveel groter dan 1 en kleiner of gelijk aan 2 etc. Maar dan komt er nog een resultaat; in dit geval 0. Excel levert via de Interval-functie altijd één waarde meer dan het aantal gedefinieerde intervallen. Hier wordt geteld hoeveel brongegevens groter zijn dan de hoogst gedefnieerde intervalgrens. Wijzig de 10 in bijvoorbeeld 9,5 en je ziet het volgende resultaat:
De laatste 2 regels laten zien, dat de Interval-functie geen resultaat meer oplevert, dus we hadden hiervoor bij punt 1 beter de cellen J10:J20 kunnen kiezen.
Interval-functie
Laten we de Interval-functie nog eens even verder onder de loep nemen. In het tabblad Interval van het Voorbeeldbestand ziet u weer dezelfde brongegevens staan. Op basis van de 10 intervalgrenzen in kolom I zijn de bijbehorende frequenties in kolom J met behulp van de Interval-functie bepaald, inclusief 1 extra cel (en 2 niet-nuttige cellen).
De frequentietabel in de kolommen L en M laat zien dat de Interval-functie ook werkt wanneer de grenzen in de volgorde hoog-laag staan (in de kolom M staat de formule .
En nog mooier: de intervalgrenzen mogen ook willekeurig door elkaar staan (zie de kolommen O en P)! Maar wanneer zou je zoiets nu doen?
De kolommen R en S laten nog een andere eigenschap van de functie zien: wanneer een interval een tweede keer voorkomt dan wordt de bijbehorende frequentie 0, zodat we geen dubbeltelling krijgen (dit geldt ook als zo’n grens nog vaker voorkomt). Consequentie is wel dat de frequenties van andere intervallen veranderen. En dat is natuurlijk terecht omdat de getallen in kolom R de bovengrens van een interval aanduiden.
Interval-functie 2
Hiervoor hebben we al regelmatig de functie Interval gebruikt, waarbij de werking (na wat oefening) ‘normaal’ begint aan te voelen.
We oefenen nog even met een nieuw voorbeeld (zie het tabblad Interval2 van het Voorbeeldbestand): van enkele leerlingen hebben we scores verzameld. De data zijn vastgelegd in een Excel-tabel met de naam tblLLscore. Het aantal records staat onder de kolom LLnr, daarnaast is het totaal van de scores bepaald met de formule . De twee totalen zijn (bijna) automatisch gegenereerd door in de menutab Hulpmiddelen voor tabellen de Totaalrij te activeren:
Een overzicht van de verdeling van de scores is nu snel gemaakt:
plaats de scores 0 tot en met 10 in een kolom (hier kolom E)
selecteer daarnaast de cellen in kolom F, tik in de formule =INTERVAL(tblLLscore[Score];E3:E13) en druk op Ctrl-Shift-Enter
in kolom G sommeren we de scores als de score overeenkomt met cel E3, E4 etc.
onderaan sommeren we de resultaten van de kolommen F en G
LET OP de resultaat-tabel in de kolommen E:G kan niet de vorm van een Excel-tabel krijgen; Excel staat een combinatie van zo’n tabel en een matrix-formule niet toe!
We weten nu hoe de verdeling over de verschillende scores is, maar hoe is de verdeling over de leerlingen? In het voorbeeld is het aantal leerlingen beperkt; we zien in één oogopslag dat alleen de nummers 1 tot en met 5 voorkomen. Maar hoe weet je dat wanneer de bron veel uitgebreider is? Een oplossing is het gebruik van Filter:
kies in de menutab Gegevens in het blok Sorteren en filteren de optie Geavanceerd
vul het scherm van het Uitgebreid filter in zoals hiernaast
klik op OK
Het klopt, er zijn 5 verschillende leerlingennummers. We hebben de kolom I aangevuld met J en K en daar de Excel-tabel tblLLresult van gemaakt. In kolom J staat de formule =AANTAL.ALS(tblLLscore[LLnr];[@LLnr]) en in K: =SOM.ALS(tblLLscore[LLnr];[@LLnr];tblLLscore[Score])
En de tabel heeft ook een Totaalrij gekregen.
Een andere methode om vanuit de basis tot een leerlingen-overzicht te komen is door het gebruik van Power Query. In de kolommen M:O staat het resultaat daarvan.
Wat gebeurt er als er nieuwe scores bij komen?
selecteer met de cursor cel C16 (de laatste score)
druk op de Tab-toets
de Totaalrij van de tabel verschuift automatisch naar beneden
voeg nu nieuwe gegevens toe: leerling 3 heeft een 7 gescoord.
Alle overzichten zijn overeenkomstig aangepast. Het resultaat van Power Query hebben we wel moeten Vernieuwen (rechts klikken op één van de cellen in M:O).
Voeg op dezelfde manier voor leerlingnummer 6 de score 8 toe:
Helaas: ons leerlingenoverzicht is niet meer compleet. We zullen handmatig het leerlingnummer 6 moeten toevoegen om alles weer kloppend te maken. Fijn dat we controle-totalen hebben toegevoegd!
NB misschien toch handiger om voor dit soort overzichten draaitabellen te gebruiken?
Interval-functie-3a
Maar de functie heeft een speciale eigenschap, die goed gebruikt kan worden bij het turven van het aantal unieke gegevens (en dat was uiteindelijk de aanleiding voor dit artikel).
Op het tabblad Uniek van het Voorbeeldbestand hebben we de kolom met leerlingnummers gekopieerd naar kolom B.
Daarnaast hebben we met behulp van de Interval-functie een frequentieoverzicht voor deze leerlingen gemaakt door de Gegevensmatrix en Interval_verw gelijk te maken.
Leerling 1 komt 4 keer voor, de volgende 3 keer etc. Komt het leerlingnummer nogmaals voor dan is de bijbehorende frequentie 0; we krijgen dus geen dubbeltellingen!
Door alle frequenties in kolom C op te tellen weten we dus ook het totaal aantal vastgelegde scores.
Maar hoe bepalen we nu het aantal unieke leerlingnummers? Met de volgende formule is dit eenvoudig: LET OP om Excel te ‘dwingen’ om de ALS uit te voeren voor alle cellen in C3:C16 moet de formule afgesloten worden met Ctrl-Shift-Enter.
We hebben de frequenties van kolom C niet per se nodig. In cel G3 staat de formule =SOM(ALS(INTERVAL(B3:B16;B3:B16)>0;1;0)) die ook het juiste aantal unieke leerlingen oplevert. NB deze formule kan met Enter afgesloten worden; blijkbaar snapt Excel door het gebruik van Interval dat hij/zij de ALS vaker moet uitvoeren.
Nog korter (in cel H3): =SOM(1*(INTERVAL(B3:B16;B3:B16)>0))
NB1 denk aan de extra haakjes na 1*.
NB2 wil je kijken hoe deze formules werken? Gebruik de optie Formules/Formules evalueren.
Interval-functie-3b
Maar de weg van een Excel-ler is niet altijd even geplaveid, dat weet u ongetwijfeld. Wanneer we niet te maken hebben met leerlingnummers maar met namen (zoals in het tabblad Uniek2 van het Voorbeeldbestand) dan werkt de Interval-functie niet meer!
Gelukkig is daar wat op te vinden. Ter verduidelijking hebben we in kolom E een formule staan met de functie Vergelijken: vergelijk de naam van de leerling in dezelfde regel (zie de @) met ALLE namen in de Leerling-kolom. Als die te vinden is (en dat is hier natuurlijk altijd zo!) dan levert de functie de positie op van deze naam; komt de naam vaker voor dan wordt telkens dezelfde positie opgeleverd. Op deze manier vertalen we de namen dus naar getallen, waarbij dezelfde namen dezelfde getallen opleveren. U voelt het al aankomen: op deze hulpkolom kunnen we wel de Interval-functie loslaten!
We hebben de hulpkolom E niet nodig: zoals te zien is levert de formule in cel J3 direct de juiste informatie.
In een vorig artikel (Excel en kaarten) hebben we laten zien hoe je gegevens op een landkaart kunt plotten; beter gezegd: op ieder willekeurig plaatje.
De daarbij gehanteerde methode maakt het mogelijk om op detailniveau gegevens op een kaart te plaatsen. Microsoft heeft vanaf versie 2016 een ingebouwde grafiek-optie waarmee gegevens op land, provincie en gemeente-niveau als een landkaart kunnen worden weergegeven. Hoe dat in zijn werk gaat (en welke problemen je daarbij moet zien te tackelen) komt in dit artikel aan bod.
Basisgegevens
Bij het CBS zijn heel veel gegevens te vinden, zoals bijvoorbeeld een overzicht van alle gemeentes per provincie (zie het tabblad Gemeenten_alfabetisch_2020 van het Voorbeeldbestand waar de stand van 1 jan 2020 is opgenomen).
We zijn eigenlijk alleen maar geïnteresseerd in de provincie- en gemeentenamen. Met Power Query is dat zo omgezet. Het resultaat staat in de tabel tblGem op het tabblad Data van het Voorbeeldbestand.
Willen we weten hoeveel gemeentes Nederland telde op 1 jan 2020 dan tellen we het aantal niet-lege cellen in de kolom Gemeentenaam met behulp van de functie AANTALARG.
LET OP de functie AANTAL telt alleen gevulde cellen mee, als daar getallen in staan.
In cel C3 wordt op een zelfde manier het aantal cellen in de kolom Provincienaam geteld. Dat levert hetzelfde resultaat op! Hoe bepalen we nu hoeveel provincies er in Nederland zijn (we weten natuurlijk allemaal wel dat het antwoord 12 moet zijn)? De tabel tblGem heeft daartoe een hulpkolom gekregen waarin het volgnummer van de gemeente binnen de provincie wordt geturfd. In cel D8 is daartoe de formule =AANTAL.ALS($B$8:B8;[@Provincienaam]) ingevoerd.
NB de formule wordt in de Excel-tabel automatisch in de hele kolom doorgevoerd. In D9 staat dezelfde formule, maar de laatste B8 is dan B9.
In cel C4 bepalen we het aantal provincies door te tellen hoe vaak het eerste volgnummer voorkomt: =AANTAL.ALS(tblGem[GemPerProv];1)
Voor de liefhebbers staat in cel C5 een formule die het aantal provincies bepaalt zonder gebruik te maken van een hulpkolom: =SOM(ALS( INTERVAL( VERGELIJKEN(tblGem[Provincienaam];tblGem[Provincienaam];0); VERGELIJKEN(tblGem[Provincienaam];tblGem[Provincienaam];0) )>0;1))
In een van de volgende artikelen zal ik de functie INTERVAL eens onder de loep nemen.
Kaartgrafiek 1
Als eerste gaan we een kaartgrafiek maken, waarin per provincie is aangegeven hoeveel gemeentes het bevat. Daartoe maken we op basis van de gegevens uit de Excel-tabel tblGem een draaitabel (zie het tabblad ProvGem van het Voorbeeldbestand).
We maken nu de grafiek als volgt:
plaats de cursor op een van de provincienamen
kies in de menutab Invoegen in het blok Grafieken de optie Kaarten
op dit moment kent Excel daarbinnen maar één mogelijkheid, de zogenaamde Choropletenkaart
(“Thematische kaart waarop gemiddelde intensiteiten, dichtheden of relatieve waarden van verschijnselen binnen van te voren begrensde gebieden (meestal administratieve eenheden) zijn weergegeven door middel van vlaksymbolen die verschillen in grijswaarde.“)
En dan het resultaat:
Helaas dus, we zullen de gegevens van de draaitabel eerst moeten kopiëren. Dat hebben we in het tabblad ProvGem gedaan naar de kolommen F en G. De werkwijze wordt dan:
selecteer alle cellen met gegevens, inclusief een kopregel. In het voorbeeld is dat het bereik F3:G15
kies in de menutab Invoegen in het blok Grafieken de optie Kaarten
klik op Choropletenkaart
Na de eerste euforie zien we ineens een ‘grijze’ vlek rechtsboven. Wanneer je met de muis over de kaart beweegt zie je per provincie het bijbehorende aantal, maar bij Groningen krijgen we dit:
Excel heeft zelf geen info over landen, gemeentes en dergelijke vastliggen. Om de kaart te kunnen maken worden gegevens van Bing en TomTom opgehaald wanneer je een grafiek probeert te maken. In de praktijk blijkt dit ophalen lang niet altijd vlekkeloos te gaan. In dit geval weet Excel (of Bing?) niet of we de gemeente of de stad Groningen bedoelen en laat deze gegevens dan weg. Maar waarom gaat het bij Utrecht dan wel goed??? Wijzig je cel F8 in: Provincie Groningen dan worden alle provincies ingetekend.
NB bij het experimenteren met de Kaartgrafiek blijkt dat deze ingebouwde optie nog niet geheel stabiel en betrouwbaar is. In het begin worden plaatsnamen of provincies of andere geografische indelingen soms niet direct herkend. Het blijkt te helpen om te blijven proberen met andere en meer namen tot het systeem deze herkend. Daarna wil het vaak ook met namen lukken, die eerst niet geplot werden.
Welke gegevens er gebruikt kunnen worden is ook niet helemaal duidelijk; het is mij gelukt om de regio’s van Frankrijk ‘in te kleuren’ maar niet de departementen (zie het tabblad Frankrijk van het Voorbeeldbestand). En er gebeuren nog meer ‘vreemde’ dingen: bijvoorbeeld de regio in het noord-oosten heb ik Alsace genoemd, maar Excel/Bing vertaalt dit naar Grand Est.
Een goede methode om Excel (of Bing) te helpen bij het analyseren van de gegevens is om bij de categorie ook het hogere niveau op te nemen. In dit voorbeeld is het hogere niveau van de provincies het land. Klik rechts op een van de provincies in de kaartgrafiek en kies Gegevens selecteren:
Kies bij de aslabels Bewerken en zorg dat ook de kolom met landnamen wordt meegenomen. Ook op deze manier wordt de provincie Groningen herkend:
De standaard-grafiek is op de volgende manier aangepast:
de grafiektitel is aangepast door er in te klikken en wijzigingen aan te brengen. LET OP een dynamische grafiektitel (een verwijzing naar een cel in de Excel-sheet) is hierbij niet mogelijk. Uiteraard kun je nog wel een Tekstvak toevoegen, waarin je een dynamische verwijzing maakt (zie het tabblad GemPerProv van het Voorbeeldbestand)
door rechts te klikken op één van de provincies kun je de Gegevensreeks opmaken
de kaartprojectie laten we voor wat het is; voor echte geografen is dit waarschijnlijk wel interessant
normaal blijft het Kaartgebied ook op Automatisch staan. Soms is één van de overige instellingen wel handig: Experimenteren dus.
bij Kaartlabels heb je 3 mogelijkheden: Geen (dit is de standaardwaarde), Alleen best passend en Alles weergeven. Bij de 2e optie wordt er alleen een label geplaatst wanneer er voldoende ruimte is; bij de 3e manier zul je af en toe een label afgekapt zien (dmv …..)
bij Reekskleur heb ik gekozen voor 3 kleuren; de precieze kleuren en de grenswaarden kunnen daaronder gewijzigd worden
als laatste: klik rechts op één van de provincies en kies Gegevenslabels toevoegen. Door Waarden aan te vinken wordt de Kaartgrafiek aangevuld met de exacte waardes.
Kaartgrafiek 2
Een overzicht van de provincies van Nederland is leuk, maar we willen ook graag per provincie inzoomen.
Het eerste wat we doen is (natuurlijk) een draaitabel maken waarmee we per provincie een overzicht van de gemeentes krijgen (zie het tabblad GemPerProv van het Voorbeeldbestand).
Zoals hiervoor al aangegeven moeten we nog een tussenstap maken voordat we naar de Kaartgrafiek kunnen: daarom hebben we een paar hulpkolommen (F en G) ingevoegd met daarin een formule =ALS(OF(B5=””;B5=”Eindtotaal”);””;B5)
NB omdat een Kaartgrafiek ook niet kan omgaan met dynamische bereiken (waarbij we gebruik maken van de functie Verschuiving) is er in de hulpkolommen ruimte gemaakt voor 100 gemeentes.
In de hulpkolom E is een verwijzing gemaakt naar de gekozen provincie.
Op basis van deze hulpkolommen (met 100 regels) is de kaartgrafiek gemaakt. Kies je in cel C2 een andere provincie dan zal de kaart automatisch aangepast worden.
NB1 wel zal rechtsboven in de grafiek altijd een waarschuwingsteken staan Klik je daar op, dan zul je zien dat maar een (klein) gedeelte van de 100 regels in de grafiek kunnen worden weergegevens; maar dat is ook logisch.
NB2 helaas, niet voor alle provincies gaat het (direct) goed. Kies je bijvoorbeeld Drenthe dan zie je alleen de provincie en niet de gemeentes. Dit is weer op te lossen door bij de aslabels de kolom met provincie NIET mee te nemen. Bij Groningen wordt er helemaal niets getoond! In Noord-Brabant wordt de gemeente Best niet ingevuld, in Limburg hebben we een probleem met de naam Bergen (L).
In de draaitabel wordt in de 2e kolom het aantal gemeentes per gemeente geteld; dat levert natuurlijk altijd als resultaat een 1 op. Ook in de hulpkolom G komt dus een 1. Maar de gebruikte formule daar is iets aangepast: =ALS(OF(B5=””;B5=”Eindtotaal”);””;C5+ALS(RijCur=RIJ();10;0)) Bij de waarde uit kolom C wordt 10 opgeteld als de waarde in de cel met de naam RijCur (dat is hier cel F2) gelijk is aan de Rij waarin de formule staat. De waarde in cel RijCur wordt aangepast door een kleine VBA-routine die aan dit werkblad is gekoppeld: Zo gauw de selectie in dit tabblad wordt gewijzigd (door het verplaatsen van de cursor of het klikken met de muis) wordt de waarde van de geselecteerde rij in de cel RijCur geplaatst. Op dat moment komt in kolom G in die rij een hogere waarde te staan, die er automatisch voor zorgt dat deze gemeente in de grafiek een andere kleur krijgt. Door met de muis op één van de gemeentenamen te klikken kunnen we snel onze topografische kennis opvijzelen!
Kaartgrafiek 3
Een kaartgrafiek kan ook gebruikt worden om een indeling te laten zien. Als de waardes voor de ‘y-as’ geen getallen zijn maar teksten kiest Excel automatisch dit type grafiek (zie het tabblad Utrecht van het Voorbeeldbestand).
De keuze welk type moet worden gegenereerd zit wat verstopt:
klik rechts op een van de gekleurde vlakken en kies Gegevens selecteren
kies bij de Legendagegevens de optie Bewerken
via de radio-buttons kun je dan een van de twee grafiektypen kiezen
Kaartgrafiek 4
Als voorbeeld staan in het tabblad Corona van het Voorbeeldbestand de aantallen Corona-besmettingen van 17 nov voor de verschillende gemeentes in Zuid-Limburg. Niet alleen de aantallen sec, maar ook het aantal besmettingen per 100.000 inwoners. Dit om de gemeentes met elkaar te kunnen vergelijken.
NB de brongegevens van de eerste grafiek worden gevormd door alle gegevens van de kolommen B, C en D. Een kaartgrafiek kan maar één legendawaarde weergeven, dat zal hier dus kolom C zijn. Wanneer je nu deze kolom verbergt (klik op het -teken boven de D) worden de gegevens van de volgende kolom gebruikt. Kolom C is in een groep geplaatst: selecteer de hele kolom door op de letter C te klikken en kies dan Groeperen in de menutab Invoegen.
3D-kaartgrafiek
Dit is een combinatie van een 3D– en een Kaartgrafiek. Microsoft heeft dit niet onder de kaartgrafieken gerubriceerd, althans het zit helemaal ergens anders in de menu-structuur.
plaats de cursor ergens in het bereik met gegevens, die geplot moeten worden. In het voorbeeld is dat in de Excel-tabel tblCorona op het tabblad Corona van het Voorbeeldbestand.
kies dan in de menutab Invoegen de optie 3D–kaart
vul de diverse items als volgt in:
‘speel’ wat met de zoom- en navigeer-knoppen:
Het onderdeel Rondleidingen kent nog veel meer mogelijkheden, maar het voert voor dit artikel te ver om daar op in te gaan.
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 AantalAls 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:
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.
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 OPVergelijken 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.
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.
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.
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.
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:
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.
in de kolommen Naam en Land worden de gegevens opgehaald uit het tabblad Teams
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.
in de kolom TotaalPunten wordt het totaal van de renner over alle etappes berekend: =SOM(tblRenners[@[Etap1]:[Etap21]])
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.
Voorbeeldbestand: Filter2019.xlsx
.
.
Klik je daar op, dan zul je zien dat maar een (klein) gedeelte van de 100 regels in de grafiek kunnen worden weergegevens; maar dat is ook logisch.
Via de menutab Gegevens in het blok Hulpmiddelen voor gegevens is aan cel H6 een Gegevensvalidatie toegewezen:
