Categorie archief: Excel

Tour de France 2020

Excel, , , , , ,
Voorbeeldbestand: Tour2020.xlsxDownload

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.

Kindernamen ofwel Sorteren in draaitabel

Excel, , , , , ,
Voorbeeldbestand: Kindernamen.xlsxDownload
Kindernamen

Bij een analyse van de populariteit van diverse kindernamen gebruikte ik (uiteraard) draaitabellen. Bij het sorteren realiseerde ik me weer dat daar bij een draaitabel wel wat haken en ogen aan zitten, maar dat deze optie ook extra mogelijkheden biedt.
Een mooie aanleiding voor een nieuw artikel.

Brongegevens

Gelukkig hoeven we voor een overzicht van namen, die in de loop van de jaren aan kinderen zijn gegeven, niet zelf op onderzoek uit; al jaren publiceert het SVB deze.
Op de website svbkindernamen.nl kunnen we de namen van de laatste 5 jaar terugvinden.

NB om te vermijden dat namen naar personen kunnen worden herleid, publiceert het SVB namen, die minder dan 25 keer voorkomen, niet.

Met wat kopiëren en plakken kunnen we de overzichten van de site snel overhevelen naar Excel.
Het tabblad Data van het Voorbeeldbestand bevat alle namen van de laatste 5 jaar met het aantal keren dat ze toegekend zijn en de rangorde in het betreffende jaar; de gegevens zijn aangevuld met 2 extra kolommen: het jaar en of het een jongens- of meisjesnaam is.

De gegevens staan in een Excel-tabel tblData. Zoals te zien is, zijn nog enkele hulpkolommen en -cellen toegevoegd:

  1. in cel C3 (met de naam MaxJr) wordt het hoogste jaar opgehaald.
    Tik in cel C3 in: =max( en wijs dan met de muis de bovenrand van cel B6 aan (het muisteken wordt een pijltje naar beneden) en geef een muisklik. Excel vult zelf de formule aan: =max(tblData[Jaar]
    Druk op Enter.
  2. op een vergelijkbare manier bevat cel C4 (met de naam MinJr) het laagste jaar
  3. in de nieuwe kolom Lengte wordt het aantal letters van de naam bepaald:
    =LENGTE([@Voornaam])
    De functie Lengte berekent hier de lengte van de naam in de kolom Voornaam in deze tabel (aangeduid door de rechte haken) en wel de cel in de overeenkomende rij (aangeduid door de @).
    NB je hoeft dit soort notaties niet te onthouden; klik bij het invoeren van de formule op de gewenste cel en Excel vult alles automatisch in.
  4. de beginletter van de naam vinden we met de volgende formule:
    =LINKS([@Voornaam])
    Om te zorgen dat Ömer ook onder de O komt is de gebruikte formule iets uitgebreid:
    =ALS(LINKS([@Voornaam])=”Ö”; “O”;LINKS([@Voornaam]))
  5. in de kolom daarnaast bepalen we of de naam vaker voorkomt dan het jaar daarvoor:
    =ALS([@Jaar]=MinJr;”-“;
         ALS([@Aantal] >
              SOMMEN.ALS([Aantal];
                   [Jaar];[@Jaar]-1;[JM];[@JM];[Voornaam];[@Voornaam]
              );
         “J”;”N”))
    De eerste Als zorgt er voor, dat als het om een naam uit het eerste jaar gaat (MinJr), de aanduiding een streepje wordt (we weten niet of die in populariteit gestegen is of gedaald).
    Anders: als het Aantal uit de betreffende regel groter is dan ‘iets anders’ dan wordt het resultaat gelijk aan J, anders N.
    Maar wat is dat ‘iets anders’? Met de functie Sommen.als tellen we alle (geen @) gegevens op uit de kolom Aantal, die voldoen aan de voorwaardes daarna: het Jaar moet gelijk zijn aan het jaar uit de huidige regel minus 1, de code JM moet gelijk zijn aan die uit de huidige regel én de Voornaam moet overeenkomen.
    NB als de bron-data consistent zijn dan levert deze exercitie maar 1 resultaat op: het aantal keren dat de naam een vorig jaar voorkwam.
  6. om straks te kunnen zien of een naam in de loop van de jaren alleen maar populairder wordt ziet u nog een kolom SteedsStijgend met de formule:
    =AANTALLEN.ALS(
         [JM];[@JM];
         [Voornaam];[@Voornaam];
         [StijgendJN];”J”
         )
         =MaxJr – MinJr
    Op een vergelijkbare manier als hiervoor met Sommen.als bepalen we met behulp van Aantallen.als eerst het aantal keren dat bij een naam de codering StijgendJN gelijk is aan J.
    Daarna vergelijken we of dit aantal gelijk is aan MaxJr minus MinJr; als dat zo is, dan wordt het resultaat WAAR, anders ONWAAR.
    NB door het gebruik van de cellen MaxJr en MinJr hoeft er aan ons ‘analyse-systeem’ niets meer gewijzigd te worden wanneer er gegevens van andere jaren worden toegevoegd.

Jaar-overzicht

Allereerst maken we een overzicht van totalen per jaar (zie het tabblad JrOverz# van het Voorbeeldbestand).
De eerste conclusie zou kunnen zijn, dat er per jaar ruim 110.000 kinderen worden geboren en dat er blijkbaar per jaar meer jongens bij komen dan meisjes.

Deze getallen toch maar eens checken. Via Statline van het CBS zien we dat het aantal geboren kinderen in deze jaren ongeveer 170.000 is geweest en dat er ongeveer 5% meer jongens dan meisjes worden geboren:

NB wereldwijd worden er zelfs 7% meer jongens dan meisjes geboren. Volgens diverse bronnen zou uit onderzoek blijken, dat vrouwelijke embryo’s een net iets grotere kans hebben om te overlijden in de buik. Bij de bevruchting zou de verhouding wel degelijk 50/50 zijn.

Dat de totalen in het SVB-overzicht lager zijn is te wijten aan het uitsluiten van namen die minder dan 25 keer per jaar voorkomen. De verhouding jongens-meisjes volgens het SVB ligt nog verder scheef; blijkbaar worden voor meisjes vaker dan voor jongens originele namen bedacht!

Wanneer we dit overzicht beperken door de beginletter van de naam als filter te gebruiken, kunnen we diverse opvallende ontdekkingen doen.
Bijvoorbeeld: er zijn 3x zoveel meer jongens- dan meisjesnamen met een B in gebruik (tenminste namen die vaker dan 25 keer voor komen). Wanneer we daar de aantallen voor corrigeren, dan is de verhouding jongens t.o.v. meisjes binnen deze categorie iets groter dan over de hele populatie.

Bij deze draaitabel is de sortering nog rechttoe rechtaan: bij het aanmaken van de draaitabel heeft Excel de rijen en kolommen oplopend gesorteerd. Wil je de sortering anders hebben:

  • klik op een rij- of kolomnaam
  • klik op de menutab Gegevens
  • klik op of
  • of klik rechts op een rij- of kolomnaam en kies binnen de menu-optie Sorteren de gewenste volgorde:

Top-10

Laten we eens een Top-10 maken (zie ook het artikel Top-5):

  1. klik ergens in het tabblad Data van het Voorbeeldbestand op een cel in de tabel tblData
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel en klik op OK
  3. plaats het Jaar in de Kolommen, Voornaam in de Rijen en Aantal en Populariteit in het Waarden-gebied

  1. klik rechts op één van de voornamen in de draaitabel en kies de optie Filteren en daarna de optie Top-tien.
    Zorg dat de volgende instelling is geselecteerd en sluit af via OK:
  2. de namen staan nu nog in alfabetische volgorde; we willen ze natuurlijk van hoog naar laag van het aantal keren dat ze gebruikt zijn:
    klik rechts op één van de voornamen in de draaitabel en kies de optie Sorteren en daarna Meer sorteeropties. Vul het vervolgscherm als volgt in en klik op OK:

Om een top-10 van jongens óf meisjes en/of andere deelselecties te maken zijn in het tabblad Top10 van het Voorbeeldbestand de velden JM, BegLetter en Lengte in het Filters-gebied geplaatst:

In het overzicht dat we op deze manier hebben gemaakt, staan de namen in de volgorde van het Eindtotaal. Wil je bijvoorbeeld de volgorde van 2019 zien, doe dan het volgende:

  1. klik rechts op één van de voornamen en kies de opties Sorteren en dan Meer sorteeropties
  2. kies op het vervolgscherm Meer opties
  3. vul het volgende scherm als volgt in:

LET OP op deze manier is de sortering wel aan te passen aan het gewenste jaar. De namen van de top-10 veranderen daardoor niet. De filtering wordt door Excel altijd op basis van het Eindtotaal gemaakt.

Top-10 per jaar

Een echte top-10 per jaar kunnen we alleen maken door ook het Jaar in het Filters-gebied van de draaitabel te plaatsen.

Zie het tabblad JrTop10 van het Voorbeeldbestand.

Wat opvalt: korte, krachtige namen zijn erg in trek!

Tendens over de jaren

Bij een ‘analyse’ mag een verloop in de tijd niet ontbreken (zie het tabblad JrTendens in het Voorbeeldbestand):

  1. maak een draaitabel op basis van de brongegevens
  2. plaats het Jaar in de Kolommen, de Voornaam in de Rijen en 2x het Aantal in het Waarden-gebied
  3. om een deelselectie te kunnen maken plaatsen we JM, BegLetter en Lengte in het Filters-gebied
  4. klik rechts op een van de getallen in de eerste Aantal-kolom, pas de naam aan (StDal)
  5. kies Waarden weergeven als en vul het vervolgscherm in:

Wanneer we nu het overzicht voor jongens maken, valt direct een vreemde sortering van de namen op.

Alle namen staan netjes alfabetisch op de eerste 2 na!
De reden daarvoor zag ik zo gauw niet, tot ik me ineens realiseerde dat Excel Jan en Sep waarschijnlijk als maandaanduiding ziet. Wat blijkt: Excel gaat bij een sortering in een draaitabel niet zonder meer uit van een standaard ‘alfabetische’ sortering maar gaat ook op zoek naar Aangepaste lijsten (zie Doorvoeren en sorteren). Als er gegevens zijn die aan een Aangepaste lijst voldoen, dan worden deze gegevens eerst gesorteerd, daarna komen de anderen in alfabetische volgorde.

Als je het probleem onderkent, is de oplossing vaak dichtbij.
Bij een sortering buiten een draaitabel kun je op de grote button Sorteren klikken en krijg je een vervolgscherm waarin je kunt aangeven op welke manier er gesorteerd moet worden:

Standaard staat de sortering op A-Z, maar die kun je wijzigen. Waaronder de optie om een Aangepaste lijst te gebruiken.
Bij een draaitabel werkt dit echter niet. Via de volgende weg kun je dit oplossen:

  1. klik rechts op een van de namen en kies de optie Sorteren en dan Meer sorteeropties
  2. klik op de button Meer opties
  3. zet de optie Automatisch sorteren in het vervolgscherm uit en kies als Sorteervolgorde: Geen berekening

    Bij Sorteervolgorde zit nu de optie verstopt of je wel of niet een Aangepaste lijst wilt gebruiken!

NB aan het veld StDal is een Voorwaardelijke opmaak toegevoegd, zodat de tendens in de loop van de jaren sneller duidelijk is.

Steeds populairdere namen

Wanneer we een overzicht willen hebben van alle namen die in de loop der jaren steeds populairder worden, maken we een draaitabel waaraan we als filter ook het veld SteedsStijgend toevoegen (zie het tabblad JrStijgend van het Voorbeeldbestand).

De naam Fos zal in 2015 ook wel zijn voorgekomen, maar het aantal zal lager dan 25 zijn geweest; door het SVB worden die in het kader van de privacy weggelaten.

Selectie

Ben je op zoek naar een handig hulpmiddel om een naam te vinden voor een kind (populair of juist niet, beginnend met een bepaalde letter, een korte of lange naam)?
Het tabblad Selectie van het Voorbeeldbestand bevat een draaitabel met slicers waarmee een keuze maken heel eenvoudig is (?):

NB de inhoud van de slicers past zich automatisch aan aan keuzes die in andere slicers zijn gemaakt. Door als beginletter G te kiezen bij de jongens zijn er alleen nog namen met 4 tot 8 letters over.

Totalen in draai-grafiek

Excel, , , , , , ,
Voorbeeldbestand: DrGrafTotaal.xlsmDownload

Totalen in een grafiek weergeven, dat is simpel: voeg de betreffende reeks toe aan de grafiek-gegevens. Misschien dat je deze reeks nog aan de secundaire as moet koppelen, maar daarmee ben je klaar.

Maar bij een draai-grafiek (een grafiek gebaseerd op een draaitabel) zul je merken dat je wel wat flexibiliteit inlevert; dan is een lijntje met totalen toevoegen niet mogelijk.
Met VBA kun je een heel eind komen, maar zonder programmeren niet. In dit artikel wat alternatieve mogelijkheden.

Basis-gegevens

We gaan uit van een tabel (tblData2 op het tabblad Data van het Voorbeeldbestand) met daarin Datums en het Soort artikel met een bijbehorend Aantal.

Wil je zien hoe dit overzicht tot stand is gekomen: kijk op het tabblad Basis van het Voorbeeldbestand. Daar worden telkens random nieuwe data gecreëerd.

Draaitabel

Om deze gegevens snel te kunnen analyseren maken we een draaitabel:

  1. selecteer een cel in de brongegevens
  2. klik in de menutab Invoegen in het blok Tabellen op de optie Draaitabel en dan op de button OK
  3. sleep de Datum naar het Rijen-gebied, Soort naar de Kolommen en Aantal naar het Waarden-gebied
  4. standaard zal Excel de datums direct groeperen (in Jaren, Kwartalen en Maanden)
    LET OP één van de namen in de Rijen is nog steeds Datum, maar deze bevat nu de maanden.
    Wil je een andere groepering? Zie het artikel Groeperen in een draaitabel.
  5. sleep Jaren naar het Filters-gebied en verwijder Kwartalen uit de Rijen.
  6. de Soort is alfabetisch gesorteerd: versleep de kolom met de waarde Drie naar rechts (door de rand met de muis ‘vast te pakken’).
    Het resultaat staat in het tabblad Ovz1 van het Voorbeeldbestand.

Draaigrafiek

Een grafiek maakt de onderlinge verhoudingen tussen de cijfers vaak een stuk duidelijker:

  1. selecteer een cel van de draaitabel
  2. kies in de menutab Hulpmiddelen voor draaitabellen/Analyseren in het blok Extra de optie Draaigrafiek
  3. kies de optie Gegroepeerde kolom en klik op OK

Verander je nu iets in de draaitabel (filter je bijvoorbeeld een bepaald jaar uit) dan past de grafiek zich automatisch aan.

Draaigrafiek aanpassen 1

De standaard-grafiek kent wel wat nadelen.
Allereerst willen we van die ‘lelijke’ veldknoppen af: klik rechts op één van de knoppen en kies de optie Alle veldknoppen verbergen in grafiek.

Op het tabblad Ovz2 van het Voorbeeldbestand staat het resultaat:

LET OP Excel geeft alle kolommen automatisch een kleur; normaal wijzig ik deze handmatig in vaste kleuren zodat ook bij filtering iedere Soort zijn eigen kleur houdt. Helaas: bij draaigrafieken worden deze instellingen door Excel niet vastgehouden.

Draaigrafiek aanpassen 2

Ook al is een grafiek bedoeld om intuïtief inzicht in de onderliggende cijfers te krijgen, dan nog werkt het goed (of is het zelfs noodzakelijk) om in een grafiek de waarde(s) van de belangrijkste gegeven(s) te laten zien.

In het tabblad Ovz3 van het Voorbeeldbestand heeft de grafiek een veelzeggende titel meegekregen:

  1. in een lege cel creëren we daartoe eerst de volgende formule:
    =ALS(C2=”(Alle)”; “Totaal alle jaren: “;
    “Totaal voor ” & C2 &”: “) & TEKST(DRAAITABEL.OPHALEN(“Aantal”;$B$4);”#.##0″)
    Als in cel C2 alle jaren zijn gekozen dan nemen we een overeenkomende tekst, anders wordt de tekst Totaal voor met daarachter de inhoud van cel C2 (gekoppeld door het &-teken). Achter de zo gemaakte tekst plaatsen we met behulp van de functie DRAAITABEL.OPHALEN (zie het betreffende artikel) het totaal van Aantal. Met de functie Tekst geven we een opmaak mee (een punt voor de duizendtallen en geen decimalen).
  1. zorg dat de grafiek een Grafiektitel heeft; bijvoorbeeld op de volgende manier: selecteer de grafiek en klik op de + rechts daarvan en vink de betreffende optie aan.
  1. klik op de Grafiektitel en daarna in de formulebalk. Tik in = en klik dan op de cel uit de eerste stap (in Ovz3 is dat cel I2).
    In de formulebalk komt dan automatisch de formule:
    =’Ovz3′!$I$2. Druk op Enter.

Nog een paar slicers toevoegen (zie Slicers in Excel) en we hebben (het begin van) een interactief dashboard.

Bij het filteren in de draaitabel (hier mer behulp van slicers) kan het gebeuren dat het scherm ‘verspringt’; door een paar aanpassingen aan de draaitabel blijft de opmaak stabiel:

  • om te zorgen dat in jaren waar (nog) niet alle maanden gevuld zijn (in het voorbeeld 2020), toch alle maanden zichtbaar zijn (en dus ook in de grafiek): klik rechts op een van de maanden in de draaitabel, kies Veldinstellingen, vink op het tabblad Indeling&afdrukken de optie Items zonder gegevens weergeven aan
  • klik rechts op een van de cellen in het Waarden-gebied, kies Opties voor draaitabel en vink de optie Kolombreedte automatisch aanpassen uit

Draaigrafiek aanpassen 3

Maar we zijn niet gauw tevreden: we hebben nu het totaal aantal in de titel staan, maar wat zijn de totalen per maand? In de draaitabel staan de betreffende getallen netjes op het einde van iedere rij, maar we zien die niet terug in de grafiek.

Nog erger: er is ook geen optie om dat klaar te krijgen!
Uiteraard kunnen we met VBA aan de slag, maar daar is wel wat programmeer-arbeid voor nodig. Eens even kijken of het ook zonder kan: ja natuurlijk, we zorgen dat één reeks labels heeft en laten de inhoud van die labels wijzen naar de totalen per maand.

  1. zorg dat ergens in het tabblad een reeks cellen gevuld is met de rij-totalen. In het tabblad Ovz4 staan in de cellen C24:C35 verwijzingen naar de draaitabel met behulp van de functie DRAAITABEL.OPHALEN.
  2. klik op één van de kolommen in de grafiek
  1. klik op de + rechts van de grafiek en zet de Gegevenslabels aan, inclusief de optie Basis, binnenkant zodat alle labels op dezelfde hoogte komen

  1. klik rechts op een van de labels en kies de optie Gegevenslabels opmaken
  2. bij Labelopties kun je de waarden opgeven die weergegeven moeten worden (Waarde uit cellen). Maak hier een verwijzing naar de cellen C24:C35; vink de optie bij Waarde uit en dan Waarde uit cellen aan.

Helaas, deze methode heeft 2 (?) tekortkomingen (zie het tabblad Ovz4 van het Voorbeeldbestand):

  • de labels worden gecentreerd op de overeenkomende kolom
  • als de betreffende soort uitgefilterd wordt zijn ook de labels weg!

Draaigrafiek aanpassen 4

In het tabblad Ovz5 van het Voorbeeldbestand staat een grafiek die de totalen per maand toont, inclusief de procentuele verdeling over het jaar.

De volgende aanpassingen zijn doorgevoerd:

  1. allereerst is er ruimte gemaakt onder aan de grafiek: klik rechts op de linkeras, kies As opmaken en zorg dat de minimumgrens niet meer automatisch wordt bepaald maar (in dit geval) -200 is
  2. de notatie van de as is zodanig aangepast, dat de negatieve getallen niet worden weergegeven: #.##0;
  3. vanaf cel C24 worden de maandtotalen opgehaald:
    =DRAAITABEL.OPHALEN(“Aantal”;$B$4;”Datum”;B24)
  4. vanaf cel D24 bepalen we de inhoud van de teksten die we gaan toevoegen:
    =ALS(C24=0;””;
    TEKST(C24;”#.##0″)&
    TEKEN(13)&
    TEKST(C24/DRAAITABEL.OPHALEN(“Aantal”;$B$4);”0%”))
    Als C24 nul is dan hoeft er niets weergegeven te worden, anders de inhoud van cel C24 samen met het resultaat van het maandresultaat (C24) gedeeld door het totale resultaat (weergegeven als percentage zonder decimalen); tussen de twee elementen staat een code 13 (‘naar de volgende regel’).
    NB in cel D24 en verder is het resultaat van code 13 niet te zien, maar dadelijk in de grafiek wel.
  5. klik ergens in de grafiek en kies dan in de menutab Invoegen in het blok Illustraties de optie Vormen en daarna bij Basisvormen de optie Tekstvak en ’teken’ met de muis waar je het tekstvak wilt hebben
    LET OP als je niet eerst ergens in de grafiek klikt wordt het tekstvak niet aan de grafiek gekoppeld maar aan het tabblad; bij het verplaatsen van de grafiek gaat het tekstvak dan niet mee!
  6. klik in de formulebalk, tik het =-teken en klik met de muis op de cel met de tekst die weergegeven moet worden
  7. pas de grootte van de tekst en/of het tekstvak aan en verplaats het tekstvak naar de juiste plaats (pak met de muis de ‘rand vast’)
  8. herhaal de stappen 5 t/m 7 voor alle maanden
    NB je kunt ook het eerste tekstvak kopiëren (Ctrl-C) en dan via Ctrl-V zoveel tekstvakken maken als nodig zijn. Die moeten dan nog verplaatst worden en de inhoud aangepast.

Verder zoeken 2

Excel, VBA (Visual Basic for Applications), , , ,
Voorbeeldbestand: Meervoudig zoeken.zipDownload

Er zijn op deze site al heel wat artikelen verschenen over het zoeken in Excel. Een artikel in november 2017 heeft de naam Verder-zoeken en gaat over het zoeken in niet-unieke items.

Onlangs kreeg ik een vraag van Dean, die in het verlengde van dat artikel ligt: om de rapportvoorbereidingen op een school beter te kunnen doen, zou het handig zijn om per leerling niet alleen gemiddeldes over onderdelen uit te rekenen, maar ook de maximum behaalde score en te zien voor welk onderdeel dit maximum is behaald. Dit laatste leverde hoofdbrekens op: uiteraard kan dat maximum bij meer dan één onderdeel voorkomen.
Met behulp van enkele hulpkolommen kwamen we snel tot een werkbare oplossing.

Maar: het zou natuurlijk veel mooier zijn, als dit met behulp van een Excel-functie zou kunnen. Helaas deze bestaat (nog) niet, dus tijd voor het bouwen van een Eigen functie in Visual Basic.

NB in de nieuwste versies van Excel bestaat de functie X.ZOEKEN. Ook al is deze functie veel flexibeler dan het bestaande VERT.ZOEKEN, maar ook deze functie levert altijd slechts maar één resultaat op.

Probleem

Dean had een mooie werkmap in elkaar gezet, waarbij uiteindelijk op een tabblad een cijfer-overzicht werd gegenereerd.

In het overzicht op het tabblad Rapport van het Voorbeeldbestand heb ik wat Voorwaardelijke opmaak toegevoegd om snel inzicht te krijgen in de spreiding van de resultaten.
Het bepalen van het gemiddelde per leerling of onderdeel leverde geen probleem op; ook het bepalen van de hoogste score niet. Maar wel: bij welk onderdeel (of leerling) komt dit maximum voor?

Oplossing 1

Dean was er al snel achter gekomen dat hij met de standaardfuncties HOR.ZOEKEN en VERT.ZOEKEN niet veel verder kwam. Maar zoeken op www.ginfo.nl leverde het begin van een oplossing in de vorm van de combinatie Index-Vergelijken.

In cel J3 staat de formule:
=INDEX($C$2:$G$2;VERGELIJKEN(I3;C3:G3;0))
De functie Index levert die waarde uit het bereik C2:G2, waarvan het volgnummer gelijk is aan het resultaat van de functie Vergelijken.
Deze laatste functie ‘kijkt’ op welke plaats de waarde uit cel I3 in het bereik C3:G3 staat; de derde parameter (0, nul) geeft aan dat er een exacte vergelijking moet worden uitgevoerd.

NB1 op een vergelijkbare manier wordt in cel C15 bekeken welke leerling de hoogste score op het eerste onderdeel heeft gehaald.

NB2 moeite om te onthouden hoe de combinatie van deze functies moet worden ingevoerd? Gebruik de avz-truc uit het artikel Zoeken: Index en vergelijken.

Helaas bij leerling 9 gaat dat fout: de maximale score 7,7 komt 2 keer voor maar de formule laat alleen Item 2 zien.

Oplossing 2

Gelukkig: de functie ZoekWaarde levert wel het gewenste resultaat!
Maar … deze functie is niet terug te vinden in de gereedschapskist van Excel. Dit is een zogenaamde Eigen functie (in het Engels UDF, User Defined Function). Hoe je deze in je spreadsheets kunt implementeren, zal ik hierna uitleggen.

De functie ziet er als volgt uit:

Deze functie kent 3 parameters (Waarde, Waarzoeken en Resultaat); de functie kijkt waar de Waarde in het bereik Waarzoeken voorkomt en levert de overeenkomende waarde uit het bereik Resultaat. Komt de Waarde vaker dan één keer voor dan worden de resultaten gescheiden door een /.

De functie kan op dezelfde manier in een Excel-cel ingevoerd worden als iedere andere (standaard-)functie. Weet je niet welke parameters er in welke volgorde nodig zijn, ga dan als volgt te werk:

  1. plaats de cursor in de cel waar de formule moet komen
  2. tik in =zoekwaarde(
    Of tik het eerste gedeelte in en klik dubbel op de functie in het lijstje dat automatisch tevoorschijn komt:
  3. klik op denet voor de formulebalk
  4. vul in de pop-up de parameters (argumenten) in:

NB voor Eigen functies is geen Help beschikbaar; zorg dat de namen van de parameters aangeven wat de bedoeling is (dus niet Bereik1, Bereik2 of iets dergelijks)

In het tabblad Rapport van het Voorbeeldbestand is deze functie gebruikt in kolom K en rij 16 met het gewenste resultaat.

NB zoals uit de formules in kolom L en rij 17 blijkt, is het voor deze functie niet nodig dat het hele resultaat-bereik wordt opgegeven; de begincel is voldoende.

In het tabblad Rap2 van het Voorbeeldbestand is te zien, dat wanneer de rapportcijfers gehele getallen zijn, het probleem van meervoudige resultaten nog veel vaker zal voorkomen.

NB de functie kent geen enkele fout-detectie; in de praktijk hoeft dit bij het (met verstand) toepassen geen probleem te zijn. In regel 19 staan de resultaten van ‘vreemd’ gebruik van de functie.

Oplossing 3

De hiervoor geschetste oplossing lijkt veel op de Index-Vergelijken-oplossing met dat verschil, dat meervoudige resultaten mogelijk zijn.
Om ook het probleem van foutdetectie aan te pakken (en de functie wat flexibeler te maken) bevat het Voorbeeldbestand nog een andere Eigen functie; deze heeft 4 parameters waarvan de laatste optioneel is (hoeft dus niet ingevoerd te worden; in dat geval zal Excel bij deze functie “/” gebruiken): IndexVergelijken(Result_matrix, Zoekwaarde, Zoeken_matrix, Optional Scheiding As String = “/”)

Na de declaratie van de functie (Public is de standaardinstelling; maak je de functie Private dan zul je bij het invoeren niet meer uit de lijst kunnen kiezen en is het niet mogelijk om het Functieargumenten-scherm op te roepen) staan diverse commentaar-regels, die uitleg geven over de functie en de randvoorwaarden.
Daarna volgen 3 regels waarin variabelen, die binnen de functie gebruikt worden, worden gedeclareerd (Boolean variabelen kunnen alleen de waarden False en True bevatten; variabelen van het type Long kunnen grote gehele getallen weergeven).
In het volgende gedeelte worden diverse testen uitgevoerd op de ingevoerde parameters. Als een test de waarde Waar/True oplevert, wordt een foutmelding als resultaat van de functie doorgegeven en stopt de verdere verwerking (Exit Function).

In het 2e gedeelte vindt de daadwerkelijke verwerking van de parameters plaats.
Eerste krijgen de 2 booleans een waarde.
Dan lopen we met 2 geneste For-Next–loops door alle cellen van de rijen en kolommen van Zoeken_matrix (door bovenstaande controles kan het maar 1 kolom of 1 rij zijn).
Als de Zoekwaarde in een cel voorkomt wordt de overeenkomende waarde uit de Result-matrix aan het resultaat van de functie toegevoegd, inclusief de standaard- of de in de 4e parameter opgegeven scheidingstekst. Als het zoek- en resultaat-gebied gelijk zijn wordt de betreffende rij of kolom als resultaat doorgegeven.
Als na de For-Next-loops blijkt dat er minstens één resultaat is gevonden wordt de laatste scheidingstekst verwijderd (Len bepaalt de lengte van een tekst). Als er geen enkel resultaat is gevonden, wordt als resultaat van de eigen functie de foutcode xlErrNA teruggegeven; in de Nederlandstalige versie van Excel krijg je dan de standaard-foutmelding #N/B.

In het tabblad Rap3 van het Voorbeeldbestand staan diverse voorbeelden van het gebruik van deze eigen functie IndexVergelijken.

NB1 omdat deze eigen functie heel veel lijkt op het gebruik van de combinatie Index-Vergelijken heb ik ook de volgorde van de parameters aangehouden, zoals ze daarbij worden ingevoerd.
De werking van de functie verandert niet, als de eerste regel wordt gewijzigd in het meer logische
Public Function IndexVergelijken(Zoekwaarde, Zoeken_matrix, Result_matrix, Optional Scheiding As String = “/”)

NB2 optionele parameters moeten altijd achteraan komen.

Extra voorbeelden 1

Hiernaast staat een afdelingsoverzicht met bijbehorende persoon; aan iedere persoon is een waarde toegekend (zie de Excel-tabel tblAfdPers in het tabblad Ovz1 in het Voorbeeldbestand).

Wil je weten bij welke afdeling of persoon een bepaalde waarde hoort, dan kun je geen gebruik maken van VERT.ZOEKEN (deze functie kan niet ‘naar links kijken’).

De combinatie Index en Vergelijken ligt hier meer voor de hand. Deze formule voldoet als je zeker weet dat de waardes uniek zijn. In dit geval lijkt dat zo, maar …

Het is veiliger om de nieuwe functie IndexVergelijken() te gebruiken:

De waarde 21 komt bij 2 afdelingen voor, telkens bij de persoon P1.
In cel I10 is met deze functie een persoonsoverzicht gemaakt voor Afd3, terwijl in cel I13 te zien is in welke afdelingen P4 voor komt.

Extra voorbeelden 2

Op het tabblad Ovz2 van het Voorbeeldbestand staan voorbeelden van het zoeken in een draaitabel met de functie IndexVergelijken().

Cel D19 bevat het resultaat van de functie wanneer de zoek- en resultaat-matrix gelijk zijn.

Implementatie van een Eigen functie

Er zijn ruwweg 2 methodes: koppel de functie aan de werkmap waar je hem nodig hebt (net als in het Voorbeeldbestand) of zorg er voor, dat de functie in iedere werkmap beschikbaar is. De functie moet dan opgenomen worden in een zogenaamde persoonlijke macro-werkmap (PERSONAL.XLSB). Helaas kent deze methode nogal wat haken en ogen. We gaan daar hier niet verder op in. Misschien een idee voor een apart artikel?

Visual Basic Editor

In allebei de gevallen hebben we de Visual Basic Editor nodig. Er zijn verschillende mogelijkheden om die te openen:

  1. druk op de toetscombinatie Alt-F11
  2. klik rechts op één van de tabs van de werkbladen onderaan (in het Voorbeeldbestand bijvoorbeeld op de tab Rapport) en kies de optie Programmacode weergeven
  1. klik in de menutab Ontwikkelaars in het blok Programmacode op de optie Visual Basic
    NB staat de menutab Ontwikkelaars niet in het ‘lint’, klik dan rechts op één van de menutabs, kies de optie Het lint aanpassen en zorg dat in het rechterblok de optie Ontwikkelaars is aangevinkt.

In aparte werkmap opnemen
  1. maak of open het bestand waar de eigen functie gebruikt zal gaan worden (in dit voorbeeld Map1).
    Open ook het Voorbeeldbestand.
  2. open de Visual Basic Editor. Linksboven (tenminste in de standaardinstelling van VBA) staat de project-verkenner, waar alle geopende Excel-projecten/werkmappen weergegeven worden.
  3. wanneer het nieuwe project geen modules heeft (zoals hiernaast Map1), zorg dan dat dit project is geselecteerd en kies Invoegen/Module
  1. dubbel-klik op Module1 van het project Meervoudig zoeken. Selecteer de hele functie IndexVergelijken (vanaf Public tot en met End Function) en kopieer (bijvoorbeeld door Ctrl-C)
  2. dubbel-klik op Module1 van het project waar de functie gebruikt zal gaan worden (hier dus Map1) en plak de eigen functie (Ctrl-V)
  3. sluit VBA af via Alt-Q of Bestand/Sluiten en terugkeren
  4. de eigen functie is nu te gebruiken in de nieuwe werkmap.

LET OP wanneer het bestand bewaard wordt moet je er wel voor zorgen dat je deze opslaat als werkblad met macro’s (dus extensie xlsm) of als binair (met de extensie xlsb).

Data verrijken deel 2

Excel, , ,
Voorbeeldbestand: DataVerrijken2.xlsxDownload

In het vorige artikel hebben we gezien dat er verschillende manieren zijn om data aan te vullen met andere gegevens.
De meest flexibele methode is het gebruik van Power Query in samenhang met het Excel-gegevensmodel.

Deze keer gaan we opnieuw in op deze methode, waarbij ook aandacht voor de diverse manieren van het samenvoegen van Power Query’s en de consequenties daarvan.

Basis-gegevens

Als voorbeeld voor dit artikel gebruiken we een tijdregistratie van een medewerker van een ICT-afdeling.

Hij krijgt zijn opdrachten van diverse personen en wordt ingezet om telefonisch mensen uit diverse regio’s te ondersteunen.

Het tabblad Data van het Voorbeeldbestand bevat daartoe een Excel-tabel (met de naam tblData).
Om de invoer van de opdrachtgever te vergemakkelijken (en invoer-fouten te voorkomen) is de input van de namen van een Gegevens-validatie voorzien.
De bron voor deze validatie ligt vast op het tabblad Basis in de Excel-tabel tblTeams. Helaas is Microsoft bij het implementeren van tabellen ‘vergeten’ om de mogelijkheden daarvan ook bij gegevens-validatie toe te staan. Dat moet daarom via een omweg ingeregeld worden.

Voer de volgende stappen uit:

  1. kies in de menutab Formules in het blok Gedefinieerde namen de optie Naam definiëren.
  1. het vervolgscherm vullen we in zoals hiernaast weergegeven.
    De verwijzing kun je het makkelijkst maken door op de pijl-omhoog te klikken, zo nodig nog het tabblad Basis te selecteren en dan op de bovenrand van de cel Naam te klikken.
    Deze gedefinieerde naam kunnen we nu gebruiken binnen de gegevens-validatie.
  1. selecteer alle cellen in de kolom OpdrGever op het tabblad Data.
  2. kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  3. selecteer bij Toestaan de optie Lijst en vul bij Bron in: =Namen
    NB denk aan het =-teken!
  4. de overige standaard-instellingen laten we zo, dus klik op OK

NB in nieuwe records van de tabel zal de gegevens-validatie automatisch worden doorgevoerd.

Ook de gegevens in de kolom Regio in het tabblad Data hebben op een vergelijkbare manier een validatie gekregen. Daarbij is als naam Regios gebruikt, die verwijst naar de kolom Regio in het tabblad Basis.
Maar de ICT-afdeling moet voor sommige werkzaamheden een andere regio-code in kunnen voeren. Daarom is de optie Foutmelding weergeven in het tabblad Foutmelding van de Gegevensvalidatie uitgevinkt.

Overzicht per opdrachtgever en regio

Dit overzicht kunnen we snel met behulp van een draaitabel genereren op basis van de gegevens van het tabblad Data.

Vanwege de flexibiliteit gaan we eerst een koppeling maken in Power Query:

  1. selecteer een van de cellen in de tabel tblData
  2. kies in de menutab Gegevens in het blok Ophalen en transformeren de optie Uit tabel

NB tijden worden binnen Excel als een decimaal getal vastgelegd: 12:00 als 0,5, 6:00 als 0,25, 3:00 als 0,125, 2:24 als 0,1 etcetera

  1. Power Query heeft al een koppeling gemaakt naar de bron en de kolomtypes gewijzigd
  2. wijzig de naam in q_tblData om het verschil met de tabel extra duidelijk te maken
  3. in plaats van nummers voor de regio’s willen we een omschrijving hebben (1=Noord, 2=Oost, 3=Zuid, 4=West):
    * kies in de menutab Kolom toevoegen de optie Kolom vanuit voorbeelden
    * tik in de eerste regel Noord in, in de volgende Zuid enzovoort net zolang tot PQ ‘snapt’ wat de omschrijving moet zijn
  4. we voegen nog een Aangepaste kolom toe met de naam Tijd en als formule =[EindTijd]-[BeginTijd]
  5. wijzig het type van Tijd in Decimaal getal
    LET OP NIET wijzigen in Tijd; dan kan er in de hierop gebaseerde draaitabel niet mee gerekend worden
  6. de Begin– en Eindtijd hebben we niet meer nodig, dus die kunnen verwijderd worden
  7. kies als laatste stap in de menutab Start het ‘vinkje achter Sluiten en laden en zorg dan dat er Alleen een verbinding gemaakt wordt en dat de gegevens aan het gegevensmodel worden toegevoegd

Nu gaan we het overzicht maken (zie het tabblad Ovz1 van het Voorbeeldbestand):

  1. ga naar een nieuw tabblad
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. verplaats vanuit de tabel q_tblData het veld OpdrGever naar de Rijen, RegioNaam naar de Kolommen en de Tijd naar het Waarden-gebied
  4. de Som van Tijd wordt nu als een decimaal getal weergegeven. Klik rechts op één van de getallen en kies Getalnotatie. Kies in het vervolgscherm als categorie Tijd en het gewenste Type.

Overzicht per team en regio

Zoals te zien is in het tabblad Basis van het Voorbeeldbestand zijn de opdrachtgevers aan teams gekoppeld.

Voordat we verder gaan maken we eerst 2 verbindingen op de manier zoals hiervoor beschreven. De eerste verbinding wordt tot stand gebracht met tblTeams en krijgt de naam q_tblTeams, de andere met tblRegio met de naam q_tblRegio.

Op de manier zoals beschreven in het vorige artikel worden de 3 q_tbl-verbindingen aan elkaar gekoppeld (zie q_tblData2 in het Voorbeeldbestand). In deze query zorgen we er ook voor, dat wanneer er geen regio gevonden kan worden (als de code groter dan 4 is) er Onbekend wordt gegenereerd (via de optie Waarde vervangen).

Op basis van q_tblData2 kunnen we een draaitabel maken die de Tijden uitzet tegen Regio en Team (zie het tabblad Ovz2 van het Voorbeeldbestand).

Maar …. misschien was het u hiervoor ook al opgevallen: de draaitabel telt alles goed op behalve de totaal-tijd rechtsonder!

Gelukkig is het maar een kwestie van lay-out: ik heb het verkeerde Type in de categorie Tijd bij Getalnotatie gekozen (namelijk 13:30). Dit type begint na 24 uur weer bij 0.
Wanneer we een aangepaste getalnotatie kiezen en we zetten vierkante haken rond het uur (ik heb [u]:mm ingetikt) dan ziet het er beter uit:

NB bij de team-indeling is een test-naam blijven staan. Wanneer we die verwijderen en op de menutab Gegevens in het blok Verbindingen de optie Alles vernieuwen kiezen dan komt er in de draaitabel een naam (leeg) tevoorschijn. Uiteraard is dit in q_tblData2 op dezelfde manier als bij Regio om te zetten naar Onbekend.

Overzicht per team en regio 2

Hoe komt het nu, dat we die onbekende gevallen in ons overzicht te zien krijgen?
Er worden codes of namen gebruikt die niet in de basis-gegevens voorkomen. Gelukkig zien we dat direct omdat dan in het resultaat lege velden voorkomen. Ook gelukkig, dat Excel de koppeling standaard zodanig legt dat deze ‘vreemde’ records niet verdwijnen.
Bij het query’s samenvoegen zien we deze tussenstap:

Waar het om gaat is het Type join (verbinding). Standaard staat deze ingesteld op Left outer. Dit betekent dat alle records uit de eerste tabel worden getoond en als er een overkomst te vinden is in de tweede tabel dan worden deze records aangevuld met die gegevens.

Power query kent verschillende joins:

De join Left outer is de meest gebruikte (en ook bruikbare), maar de Inner kan ook handig zijn.
Wanneer we in ons voorbeeld records met regio-codes groter dan 4 en/of ‘vreemde’ namen niet willen meenemen dan gebruiken we dit type verbinding.

In het tabblad Ovz3 van het Voorbeeldbestand is op basis van q_tblData3 een overzicht gegenereerd. In de onderliggende verbindingen is gebruik gemaakt van inner-joins:

LET OP afwijken van de standaard-join (Left outer) moet u alleen doen als het echt nodig is. Het risico van verdwijnende records is dan altijd aanwezig; het is raadzaam om in dat geval een controle in te bouwen: