Tagarchief: Index

Eerbetoon aan Rosling cs

Excel, VBA (Visual Basic for Applications), , , , , ,

Rosling

RoslingOngetwijfeld hebt u op Internet, Youtube of op TV (DWDD of bijvoorbeeld bij Zondag met Lubach) wel eens een presentatie van Hans Rosling gezien.
Iedere keer is het weer interessant en enerverend (eigenlijk ook wel spannend) om te zien hoe hij (openbare, vrij beschikbare) gegevens weet om te zetten in informatie.
Wikipedia: Rosling stichtte de Gapminder Foundation samen met zijn zoon Ola Rosling en zijn schoondochter Anna Rosling Rönnlund. Gapminder ontwikkelde de Trendalyzer-software, die internationale statistieken omzet in bewegende, interactieve en onderhoudende grafieken.
Het doel is de promotie van een wereldvisie gebaseerd op feiten, door verhoogd gebruik en begrip van gratis toegankelijke openbare statistieken. Zijn lezingen aan de hand van Gapminder-visualisaties vielen in de prijzen doordat ze grappig en toch doodernstig zijn. De interactieve animaties zijn vrij beschikbaar op de website van de stichting (zie gapminder.org).
roslingBij veel van de presentaties gebruikt Rosling de Trendalyzer-software, waarmee het mogelijk is om diverse items in samenhang te tonen.
Zoals hiernaast bijvoorbeeld: op de (niet zichtbare) assen is het Inkomen per inwoner tegen de Levensverwachting uitgezet.  Per land wordt dit door een bolletje weergegeven, waarbij de grootte van het bolletje wordt bepaald door het aantal inwoners van dat land. Doordat de grafiek ook nog eens een reis door de tijd kan maken, waardoor we historische ontwikkelingen kunnen zien, hebben we dus te maken met een informatie-overzicht met maar liefst 5 dimensies!

Bellendiagram

Rosling2Toen ik bovenstaand voorbeeld zag, vroeg ik me af in hoeverre het mogelijk zou zijn om dit in Excel na te bouwen.
Zelf gebruik ik in rapportages zogenaamde bellendiagrammen (in het Engels bubble chart)  wel eens, maar ze zijn niet echt gangbaar.
Daarom leek het me wel de moeite waard  om dit idee uit te werken; ook als een soort eerbetoon aan het idealistisch te noemen werk van Rosling cs.

Basis-gegevens

Voordat we een grafiek kunnen gaan maken, moeten we natuurlijk de beschikking hebben over relevante basisgegevens.
Omdat Rosling alleen gebruik maakt van openbare bronnen is dit verzamelen niet zo moeilijk; op de site gapminder.org vinden we de nodige gegevens zelf of verwijzingen naar de bronnen.
In het Voorbeeldbestand zijn die opgenomen in de tabbladen Landen (overzicht van bijna alle landen ter wereld), Inkomen (het jaarinkomen per inwoner van de diverse landen, van 1800 tot 2015), Bevolking (het aantal inwoners per land, van 1800 tot 2015) en LevVerwachting (de levensverwachting bij geboorte per land, van 1800 tot 2015).

Inkomen
Bij sommige landen ontbraken (gedeeltes van) inkomen-gegevens. Om te zorgen dat het tekenen van de grafiek hierna niet spaak zal lopen, heb ik die gegevens aangevuld met de waarde 1 (één).

Bevolking
Het aantal inwoners per land gaat in de grafiek de belgrootte  bepalen. Vandaar dat ook hier de ontbrekende gegevens aangevuld zijn met de waarde 1.

Parameters

Alle gegevens, die nodig zijn voor de besturing van ons Excel-systeem, zijn vastgelegd op het tabblad Parameters:

  • alle landen, die we in de grafiek willen opnemen,
  • het beginjaar en
  • eindjaar (in dit systeem 1800, respectievelijk 2015),
  • het jaar, waarvoor we de grafiek willen zien,
  • een indicator voor de snelheid van de verandering van de grafiek (overgang naar een volgend jaar) en
  • het opschrift van een button, die we maken om de grafiek ‘af te kunnen spelen’.

Rosling3Om verwijzingen in formules overzichtelijker/leesbaarder te maken zijn aan alle parameters namen gegeven. Het snelste gaat dat op de volgende manier:

  1. Rosling4selecteer de cellen, die een naam moeten krijgen (inclusief de cellen daarboven)
  2. kies in de menutab Formules in het blok Gedefinieerde namen de optie Maken obv selectie
  3. zorg dat in het vervolgscherm (in dit geval) alleen het vinkje bij Bovenste rij aan staat en klik OK

NB Klik op één van de parameters (bijvoorbeeld 2015) en zie dat in het Naamvak linksboven niet meer de rij en kolom wordt weergegeven, maar de naam, die we aan de cel hebben gegeven.

Op dezelfde manier heeft de reeks landen ook een naam gekregen. Klik op het pijltje naast het Naamvak en kies Landen om dit te verifiëren.

Berekeningen

Voordat we de grafiek kunnen maken, zullen we alle benodigde gegevens bij elkaar moeten verzamelen in een vorm, die handig is om als bron voor de grafiek te fungeren.

Rosling5In het tabblad Berek van het Voorbeeldbestand staat in cel B3 de formule =Landen (een verwijzing naar de landen op het tabblad Parameters). Deze formule is zo vaak naar beneden gekopieerd als noodzakelijk is om alle landen te zien.

LET OP een dergelijke verwijzing naar een zelf-gedefinieerde naam voor een bereik haalt gegevens op uit de corresponderende regel, dus in dit geval regel 3. Wil je dat niet: selecteer dan eerst alle cellen, die gevuld moeten worden; tik in =Landen en druk dan op Ctrl-Shift-Enter. Op deze manier wordt een zogenaamde matrixformule ingevoerd; Excel zet automatisch accolades rond de formule.

Per land laten we Excel de corresponderende gegevens opzoeken:

  1. in kolom C de regio:
    =VERT.ZOEKEN(B3;LandenBron;2;ONWAAR)
    de inhoud van cel B3 wordt in het bereik LandenBron opgezocht. Als het land gevonden wordt, dan levert de functie het corresponderende resultaat uit de 2e kolom van het blok. Met ONWAAR geven we aan, dat we alleen tevreden zijn als de inhoud van B3 ook echt gevonden is (dus niet Benaderen).
  2. het inkomen per land komt in kolom D:
    =INDEX(Inkomen;VERGELIJKEN($B3;InkLand;0);VERGELIJKEN(KeuzeJaar;InkJaar;0))
    Hoewel de inkomengegevens ook met VERT.ZOEKEN zouden kunnen worden gevonden, heb ik voor de functie Index gekozen (vind ik persoonlijk beter leesbaar en is meer universeel toepasbaar).
    Met behulp van Index zoeken we in het blok Inkomen de gewenste regel en kolom op en krijgen direct het resultaat.
    Maar op welke regel staat het betreffende land? Met
    VERGELIJKEN($B3;InkLand;0)
    is dat zo geregeld: InkLand is de naam van de reeks landen op het tabblad Inkomen. De functie Vergelijken geeft de positie van B3 in deze reeks.
    Op een vergelijkbare manier wordt de juiste kolom opgezocht (het gewenste jaar staat in de parameter Keuzejaar).
    LET OP de 3e parameter van de functie Vergelijken moet 0 (nul) zijn: we zoeken ook weer hier een exacte waarde, geen benadering.
  3. de Levensverwachting en de Bevolkingsgrootte worden ook mbv de functie Index gevuld.

Grafiek

Eindelijk zijn we zover; we gaan de verzamelde informatie grafisch weergeven.

Stap voor stap (op het tabblad Graf van het Voorbeeldbestand staat het uiteindelijke resultaat):

  1. Rosling7maak een nieuw tabblad aan (bijvoorbeeld via Rosling6 onderaan op het scherm, op het einde van de andere tabbladen)
  2. kies in de menutab Invoegen in het blok Grafieken de optie Overige grafieken en kies de eerste optie bij Bel
  3. in het (lege) grafiekgebied rechts klikken en de optie Gegevens selecteren … kiezen en dan Toevoegen
  4. Rosling8in het nieuwe scherm de gegevens voor de x- en y-as en de belgrootte invullen. De reeksnaam laten we leeg; heeft bij een belgrafiek weinig nut.
  5. Klik twee keer op OK en de grafiek is klaar!
  6. nog wat verfraaiingen: de rasterlijnen en legenda weglaten, titels bij de assen etc.
  7. Excel past standaard de assen automatisch aan op basis van de gegevens die gepresenteerd worden. Dat willen we niet, want als het KeuzeJaar wordt veranderd, gaat de grafiek ‘springen’.
    Klik rechts op één van de cijfers van de y-as en zorg dat de Levensverwachting loopt van 10 tot 100 jaar.
    Ook de x-as passen we aan: het Inkomen laten we lopen van 200 naar 100.000. Maar we zijn nog niet klaar: in het gebied met lage inkomens zitten heel veel bellen heel dicht bij elkaar, de hoge inkomens zijn uitschieters. Door deze as logaritmisch weer te geven, worden de lage inkomens duidelijker onderscheiden, terwijl de hogere inkomens ‘in elkaar schuiven’.
    LET OP Een dergelijke logaritmische indeling is moeilijk leesbaar, dus alleen gebruiken als de exacte getallen niet wezenlijk zijn.
  8. alle bellen krijgen dezelfde kleur. Handmatig is dit aan te passen, maar bij deze hoeveelheid niet echt praktisch. Met een VBA-routine zou dit (op basis van de regio) wel mogelijk zijn.
    In dit geval heb ik de bel van Nederland opgezocht (aan de hand van de onderliggende cijfers) en die gekleurd en een label meegegeven.
  9. Het jaar achter de grafiek?
    Maak een tekstvak aan (via de menutab Invoegen) en tik direct in de formulebalk in =Keuzejaar.  Nog wat lay-outen: lettertype en -grootte etcetera en schuif het tekstvak achter de grafiek (in het Voorbeeldbestand zijn de randen bewust zichtbaar gehouden).

Schuifbalk
Rosling9Om gemakkelijk het verloop in de tijd te kunnen volgen is onder aan de grafiek een schuifbalk toegevoegd:

  1. kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen en kies de schuifbalk (rechts naast Aa onder Formulierbestruringselementen)
  2. ’teken’ met de muis ergens op het grafiektabblad de plaats waar de schuifbalk moet komen
  3. Rosling10klik rechts op de schuifbalk en vul de diverse opties in:
    Huidige waarde: laten we beginnen met 1800
    Minimumwaarde: in dit geval 1800
    Maximumwaarde: 2015 dus
    De stappen daaronder worden 1 en 10 (1 jaar verder wanneer op het pijltje wordt geklikt, 10 jaar wanneer er in het lege gebied van de schuifbalk wordt geklikt)
    Koppeling met cel: hier vullen we KeuzeJaar in; een verwijzing naar het tabblad Parameters dus.
  4. klik OK

Mbv deze schuifbalk kunnen we nu makkelijk onze grafiek laten veranderen: de reis in de tijd kan beginnen!

Reis in de tijd
Het is natuurlijk nog mooier als we de veranderingen in de tijd automatisch kunnen laten zien.
Achter de Play-button op het tabblad Graf van het Voorbeeldbestand is een kleine VBA-routine opgenomen, die dit voor zijn rekening neemt.
Klik op Alt-F11 om de routine te bekijken. Hebt u vragen hierover? Schroom niet om contact op te nemen met G-Info.
Om de snelheid aan te passen (van 1 naar 5 sec als pauze) is nog een schuifbalk toegevoegd.

Mastermind

Excel, VBA (Visual Basic for Applications), , , , , , ,

MastermindMastermind! Wie kent het niet?

Dit is een spel, dat waarschijnlijk iedereen wel eens ooit heeft gespeeld.
Ik moest er aan denken, toen ik bij een (gedwongen) opruimronde op zolder één van mijn eerste computers, een TRS-80, tegenkwam.

trs80aNostalgie: ongeveer 40 jaar geleden is mijn “computer-verslaving” begonnen:  eerst met een TRS-80 (van de firma Radio Shack) met een intern geheugen van 4Kb en een cassetterecorder als opslagmedium, al vrij snel met een geheugenuitbreiding tot 16Kb.
trs80bTig jaar later was de luxe niet te overzien: een modern apparaat met 2 diskette-stations en 16Mb intern geheugen!
diskZo’n diskette kon wel 500 Kb aan data bevatten.
En het gemak: na een avond lang programmeren, gewoon op disk wegschrijven; geen angst meer, dat het niet goed op de cassette was terecht gekomen, waar je pas achter kwam als het te laat was (ik geloof, dat ik in die periode pas echt heb leren vloeken!).

Maar even terug naar het onderwerp: Mastermind.
Ik kwam bij het opruimen nog aantekeningen tegen uit die eerste TRS-80-periode: de opzet voor een computer-variant van dit spel; waarschijnlijk gemaakt, zodat ik ook zonder echte tegenstander aan een andere verslaving toe kon komen: spelletjes spelen!

Mastermind in Excel

Een uitdaging dus om te kijken of we Mastermind ook in Excel zouden kunnen  spelen. Het kostte toch nog wel een paar avonden ploeteren, maar het is gelukt.

mastermindKijk maar eens in het Voorbeeldbestand.

Als je op de knop Nieuwe ronde drukt, zal Excel 4 kleuren kiezen uit een serie van 8 (dubbelen zijn toegestaan).
Aan jou, als speler, om in de cellen achter Ronde 1, je eerste gok te plaatsen.
Druk dan op de knop Controleer en onder Resultaat komt het resultaat (sic!).
Z(wart) betekent dat er een kleur goed is gekozen EN op de juiste plaats, W(it) geeft aan dat er een kleur goed is, maar niet op de juiste positie.
Vul dan Ronde 2 in, druk op Controleer etc.

Mijn beste spelronde tot nu toe was: geraden in 4 keer binnen 40 seconden. Ben benieuwd naar jullie resultaten!

Hoe werkt het programma?

Dit artikel en het Voorbeeldbestand zijn deze keer vooral ook voor de fun; dus maar een korte, compacte toelichting.
Geïnteresseerden in VBA vinden in Module1 (te bereiken via Alt-F11) de achterliggende routines. Vooral interessant is daar de combinatie tussen variabelen binnen VBA (in de vorm van een array/matrix) en celbereiken in de Excel-sheets.

Op het tabblad Parameters staan de 8 kleuren, waaruit Excel en jij, als speler, mogen kiezen; deze cellen hebben de naam Kleuren gekregen.

mastermind2Wanneer Excel de opdracht krijgt om aan een Nieuwe ronde te beginnen, dan worden op het tabblad CompKeuze de cellen B3:E3 van willekeurige getallen tussen 1 en 8 voorzien; in de rij daaronder komen dan automatisch de overeenkomende kleuren.

NB wel eerlijk spelen; dus niet gauw hier kijken wat de combinatie is!

Het tabblad Raden bevat het “spelbord”.

Excel-opties

In het Voorbeeldbestand wordt veel met Namen gewerkt om verwijzingen overzichtelijk te houden. Daarnaast zorgt Voorwaardelijke opmaak voor de juiste kleuren. De functie Index wordt her en der gebruikt om kleuren op te zoeken.

En uiteraard wordt er gebruik gemaakt van VBA-routines (Visual Basic for Applications), aangestuurd door knoppen.

Heb je vragen over één van deze toepassingen en het gebruik binnen Mastermind: schroom niet om contact op te nemen met G-Info!

Loterij

Excel, , , , , , , , ,

loterij2Vorige week kreeg ik de vraag, hoe je met Excel het makkelijkst de winnaars van een wedstrijd zou kunnen selecteren.
De bedoeling was om uit een (grote) hoeveelheid goede inzenders willekeurig drie personen er uit te lichten.

Een poosje geleden heb ik al iets geschreven over steekproeven; dezelfde systematiek is ook voor dit probleem toepasbaar.
Maar deze keer een iets andere benadering.

Loterij1

loterijIn het Voorbeeldbestand heb ik in het tabblad Loterij een overzicht opgenomen van alle verkochte loten, inclusief de corresponderende naam (in dit geval de lotnummers 1 t/m 100 en een fictieve naam, die daarvan is afgeleid; bijvoorbeeld bij het eerste lot hoort Naam 1).
NB in plaats van lotnummers kan dit overzicht ook alle goede inzenders van een wedstrijd voorstellen

loterij2Op het tabblad Uitslag worden 3 winnaars geselecteerd:

  1. Allereerst tellen we het aantal verkochte loten (of dus het aantal goede inzenders). In cel C2 staat daartoe de formule:
    =AANTAL(Loterij!B:B)
    Ofwel tel het aantal getallen in kolom B van het tabblad Loterij.
    LET OP zijn in kolom B geen nummers opgenomen maar teksten (bijvoorbeeld A1, A2, B1 etc) dan moet u de formule AANTALARG gebruiken en van het resultaat 1 aftrekken, omdat dan ook het woord LotNr in cel B2 wordt meegeteld
  2. uit het aantal verkochte loten worden dan 3 willekeurige getallen getrokken (cellen C5, C6 en C7):
    =ASELECTTUSSEN(1;$C$2)
    Dus neem een willekeurig getal tussen 1 en de waarde in C2 (in het voorbeeld dus 100); de grenzen doen ook mee.
  3. in cel D5 zoeken we dan de corresponderende naam op:
    =VERT.ZOEKEN(C5;Loterij!B:C;2)
    Deze formule zoekt de waarde uit cel C5 op in kolom B van Loterij en geeft als resultaat de corresponderende cel uit kolom C.
    LET OP bovenstaande formule werkt alleen goed, als de lotnummers in volgorde in kolom B staan en er geen “gaten” zijn. Beter is om de formule
    =VERT.ZOEKEN(C5;Loterij!B:C;2;ONWAAR) te gebruiken; de laatste parameter zorgt er voor, dat Excel naar een exacte match gaat zoeken.
  4. de formule in D5 kan naar beneden gekopieerd worden
  5. bij iedere wijziging in de werkmap worden de Aselect-formules opnieuw berekend; dus iedere keer zullen er andere winnaars tevoorschijn komen. Dit gebeurt ook door op de functietoets F9 te drukken: herberekenen.
    Belangrijk is om van tevoren duidelijk met de “notaris” af te spreken hoe vaak er herberekend zal worden, voordat de definitieve uitslag wordt bepaald.

LET OP met bovenstaande methode is het mogelijk dat prijswinnaar 2 en/of 3 gelijk is aan prijswinnaar 1. Dat is natuurlijk niet de bedoeling. Druk dan nog een keer op F9.

NB worden er aan de lijst in het tabblad Loterij nummers en namen toegevoegd of worden er verwijderd, dan zullen de resultaten in Uitslag direct daaraan worden aangepast; we kijken immers naar alle rijen in de kolommen B en C.

Loterij2

Het Voorbeeldbestand bevat ook een tabblad Loterij2; deze is vergelijkbaar met de eerste, maar is in de vorm van een Excel-tabel opgevoerd. Een groot voordeel hiervan is dat we niet alle cellen uit de kolommen B en C hoeven mee te nemen in de formules: wanneer de tabel wordt uitgebreid of verkleind dan zullen alle corresponderende formules zich automatisch daaraan aanpassen.

loterij3De formule in C2 ziet er anders uit:
=AANTAL(LoterijOvz2[LotNr])
Tel het aantal nummers in de kolom met als kopje LotNr uit de tabel LoterijOvz2 (de tabel uit het tabblad Loterij2).

De rest van kolom C is hetzelfde. Maar in kolom D gebruiken we geen VERT.ZOEKEN maar de functie INDEX. In cel D5 komt dan de formule =INDEX(LoterijOvz2[Naam];C5).
Haal in de kolom met als kopje Naam uit de tabel LoterijOvz2 de waarde op in die regel, die overeenkomt  met de waarde in C5.

NB aanpassingen aan de tabel in Loterij2 worden automatisch meegenomen in de resultaten.

LET OP ook hier kan het nodig zijn om een keer extra op F9 te drukken om geen dubbele prijswinnaars te krijgen.

Loterij3

loterij4In het tabblad Loterij3 van het Voorbeeldbestand is in de Excel-tabel een extra kolom opgenomen, waarin iedere regel van een willekeurig getal tussen 0 en 1 wordt voorzien dmv de formule =ASELECT().
De kans, dat hier dubbele getallen in voorkomen, is heel erg klein.

NB deze functie kent geen parameters, maar, zoals achter iedere functie, dienen er wel 2 haakjes te staan (openen en sluiten).

loterij5De bepaling van de winnaars gaat nu iets anders: in cel C3 wordt de eerste winnaar bepaald door het grootste getal (MAX) in de kolom Aselect van de tabel LoterijOvz3 op te zoeken. In D3 wordt met VERT.ZOEKEN het corresponderende lotnummer gevonden en in E3 met INDEX de naam (zoals uit de formules van kolom F blijkt kunnen we dat laatste ook met VERT.ZOEKEN in de hele tabel LoterijOvz3).

Maar hoe vinden we nu de 2e- en 3e- prijswinnaars? Dan kunnen we niet meer MAX gebruiken.
In cel C4 staat dan ook een andere formule: =GROOTSTE(LoterijOvz3[Aselect];2)
ofwel zoek de tweede in grootte in  de kolom Aselect van de tabel LoterijOvz3.
U begrijpt: in plaats van MAX in cel C3 hadden we ook de functie GROOTSTE met een parameter 1 kunnen gebruiken!

NB uiteraard hadden we in dit geval de winnaars ook kunnen selecteren met de functie KLEINSTE.

Urenregistratie

Excel, VBA (Visual Basic for Applications), , , , , , , ,

prikklokHoewel een echte  prikklok niet vaak meer wordt gebruikt, wordt bij veel bedrijven nog steeds gebruik gemaakt van een bepaalde manier van tijdregistratie.
Dit kan nodig zijn om binnen het bedrijf een kostenallocatiemodel te voeden of om als externe inhuur verantwoording af te leggen over de in rekening gebrachte uren, enzovoort.

Daarom in dit artikel aandacht voor Excel als hulpmiddel voor urenregistratie; inclusief wat VBA om het gebruik wat makkelijker te maken.

Basis

Het is een goede gewoonte om bij de opbouw van een Excel-systeem een scheiding aan te brengen tussen de diverse onderdelen; in dit geval hebben we dan ook de invoer van de gemaakte uren en de rapportage daarover in aparte tabbladen opgenomen.

In het Voorbeeldbestand is dit verder uitgewerkt.
De registratie en rapportage zijn geen complexe items, maar we kunnen wel diverse handige Excel-trucjes gebruiken om het systeem flexibeler en fraaier te maken.

uren1

Laten we de kolommen in het tabblad Invoer eens langs lopen:

  1. in kolom A komt de datum: in cel A4 staat de eerste datum waarop de registratie is begonnen; in A5 staat de formule =A4+1; in A6 de formule =A5+1 etc.
    Het voordeel hiervan is, dat als we met een nieuwe registratie willen beginnen, we alleen de datum in cel A4 hoeven te wijzigen
  2. voor een snel inzicht tonen we in kolom B de dag van de week; niet via een Excel-functie Weekdag (die geeft alleen het volgnummer in de week) maar door opmaak.
    De formule in B4 is =A4.
    NB De invoer is als tabel in Excel opgezet; dat houdt onder andere in, dat als je zo’n formule in B4 intypt deze automatisch in de rest van de kolom wordt overgenomen (maar wel met een verwijzing naar A5, A6 etc).
    Via de celeigenschappen hebben de cellen in kolom B een speciale datumopmaak meegekregen, namelijk dddd. Deze zorgt er voor, dat de datum als volledige weekdag wordt weergegeven.
    NB experimenteer met het aantal d’s en kijk wat dit voor invloed heeft op de opmaak.
  3. in kolom C wordt per dag de begintijd ingevoerd
  4. we gaan er van uit, dat een dag uit 2 blokken bestaat; in D komt dan de eindtijd van blok1, in E een eventueel begin van blok2  en in F een eventueel einde.
    De opmaak van de kolommen C, D, E en F is u:mm (dus minstens 1 positie voor het uur en altijd 2 voor de minuten).
  5. uren2dan wordt het spannender: in kolom G komt een formule, die de gewerkte tijd (nou ja, de aanwezigheid) berekent. Dit gebeurt in 2 gedeeltes: in de eerste Als berekenen we het verschil tussen begin- en eindtijd van het eerste blok (als er nog geen eindtijd is (D4=””) dan maken we dat verschil gelijk aan 0); en we tellen daar het resultaat van de tweede Als bij op, die een eventueel verschil van blok2 bepaalt.
    Ook deze kolom krijgt als opmaak u:mm, omdat we natuurlijk het resultaat in uren en minuten willen weten.
    Wanneer het resultaat nul is (in het weekend of op andere (nog) niet gewerkte dagen), willen we in kolom G niets zien: de opmaak is dan ook uitgebreid met een extra voorwaarde: uren3
  6. Kolom H bevat ook het aantal uren, maar nu uitgedrukt als een decimaal getal. Om de gehanteerde formule in die kolom te begrijpen, moet je weten, dat Excel een tijd als een deel van een gehele dag opslaat: 24 uur is het getal 1, 12 uur is 1/2, 6 uur wordt vastgelegd als 1/4 etc.
    Andersom: willen we de tijd uit kolom G (die dus als fractie van een hele dag is opgeslagen) weergeven als uren, dan moeten we die tijd met 24 vermenigvuldigen. Aangezien we met een Excel-tabel werken, wordt dit via een zogenaamde gestructureerde verwijzing in de formule weergegeven: uren4
    (Op tabellen en gestructureerde verwijzingen zal ik een andere keer terugkomen)
  7. in kolom I kunnen (relevante) opmerkingen, die de registratie verduidelijken, worden opgenomen.

Rapportage

De maandrapportage van de tijdsbesteding is in het Voorbeeldbestand in het tabblad MndOvz opgenomen.
Door de datum van de eerste van een maand op te geven, worden op deze pagina de bij die maand behorende gegevens uit de database opgehaald. Hierbij wordt een alternatief voor verticaal zoeken gebruikt.

uren5

  1. van de bovenste 6 rijen is alleen cel D5 echt van belang: deze geeft aan van welke maand de gegevens worden weergegeven (de eerste van de maand moet worden ingetikt; de opmaak laat alleen maar maand en jaar zien).
    De rest is verfraaiing/toelichting.
  2. in kolom B staan vanaf regel 11 de dagen van de betreffende maand weergegeven; althans het volgnummer van de dagen.
  3. in kolom C staat de werkelijke dag, via de formule =$D$5+B11-1 (dus bij de eerste van de maand (D5) wordt het volgnummer opgeteld; omdat we dan altijd 1 dag te ver uitkomen trekken we er nog 1 vanaf).
    NB1 Kolom C is niet verborgen, maar via groepering ‘dichtgeklapt’. Klik op het +-teken boven D om kolom C zichtbaar te maken. Groeperen zit in de menutab Gegevens, in het blok Overzicht.
    NB2 aangezien we natuurlijk alleen maar datums uit de betreffende maand willen zien (en bijvoorbeeld geen 31 april) is de formule vanaf C12 iets ingewikkelder.
  4. laten we dan eens kijken wat de formule in D11 doet:
    =ALS.FOUT(INDEX(Uren;VERGELIJKEN($C11;Uren[Datum];0);D$10);””)
    Uren is de naam van de tabel uit het invoerblad.
    Uren[Datum] is de datum-kolom in die tabel.
    VERGELIJKEN($C11;Uren[Datum];0) kijkt op welke plaats de waarde uit C11 (in dit geval dus 1 april) in die kolom staat; de 0 zorgt er voor, dat Excel de waarde zoekt, onafhankelijk in welke volgorde die ook zouden staan (een exacte match dus).
    INDEX(Uren; ‘plaats van datum’ ;D$10) geeft de waarde van díe cel in de tabel Uren, die in de rij ‘plaats van datum’ staat en in de kolom, die overeenkomt met de waarde in cel D10.
    ALS.FOUT geeft een lege waarde (“”) als één van de formules INDEX of VERGELIJKEN een fout oplevert (bijvoorbeeld als de datum uit C11 niet in de tabel Uren voorkomt).
    NB in het overzicht staat ook een dichtgeklapte rij 10; daar staat in welke kolom Index moet zoeken.
  5. de overige cellen in het overzicht zijn op eenzelfde manier opgezet
  6. onderaan staat nog een totaaltelling: in cel I42 worden de ‘decimale’ uren opgeteld. In H42 de ‘normale’ uren en minuten; wanneer het aantal uren boven de 24 komt, zal Excel standaard weer opnieuw bij 0 beginnen. Willen we die uren boven de 24 zichtbaar maken dan dienen we de betreffende cel een andere opmaak mee te geven: uren6. Let op de vierkante haken!

VBA

Om het gebruik van het spreadsheet wat te vergemakkelijken is nog een VBA-routine toegevoegd.
De bedoeling van de routine is om bij het openen van het bestand de cursor op de juiste plaats te hebben staan om snel nieuwe invoer te kunnen doen.

De VBA-routine gaat automatisch naar het Invoer-blad en zoekt de regel op net onder de laatste invoer.
uren7
VBA-routines worden veelal opgeslagen in zogenaamde Modules; omdat deze routine direct actief moet worden wanneer de werkmap (in het Engels Workbook) wordt geopend staat deze routine in de map ThisWorkbook en heeft de naam Workbook_Open gekregen.

Laten we de routine even stapsgewijs doorlopen:

  1. open het Voorbeeldbestand
  2. ga naar Visual Basic (bijvoorbeeld via de toetscombinatie Alt-F11)
  3. dubbelklik op de map ThisWorkbook
  4. de routine begint met wat toelichtende commentaarregels (de groene regels na de apostrof)
  5. uren8dan worden 2 variabelen gedeclareerd, die we hierna nodig hebben (strGebrNaam en strDagDeel)
  6. de 2 variabelen worden gevuld; de bedoeling lijkt me duidelijk
  7. en dan het ‘echte’ werk:
    uren9
    Eerst selecteren we het tabblad (de sheet) Invoer; dan selecteren we in de kop (Header) van de tabel Uren de cel met het woord Datum.
    Vervolgens wordt de toets-combinatie Ctrl-Pijl-naar-beneden nagebootst en dus de onderste gevulde datum geselecteerd.
    Dan 2 kolommen naar rechts om in de kolom Begin1 te komen, waar we via Ctrl-Pijl-naar-boven de laatst gevulde cel zoeken.
    Die is al gevuld, dus selecteren we de cel daaronder.
    Als laatste wordt een pop-up op het scherm getoond.
    NB vbCrLf is de code, die er voor zorgt dat de volgende tekst, die via het &-teken aan het voorgaande wordt ‘geplakt’, 1 regel lager zal komen (een zogenaamde harde-return). Zie Teksten samenvoegen voor uitleg.

Als de VBA-routine niet duidelijk is, laat dan commentaar of een vraag achter op de website.

Voetbal en gegevensvalidatie

Excel, , , , , , ,

Hoewel de titel van dit artikel misschien anders doet vermoeden (het gaat dus NIET over het checken van persoonsbewijzen bij een voetbalwedstrijd), gaan we het hebben over het valideren van de invoer van gegevens in een cel in Excel.

De aanleiding hiervoor was een discussie op een internet-forum over “Lege velden in keuzelijst met invoervak“.
Al snel kwam daar de opmerking langs, dat het onderliggende probleem waarschijnlijk makkelijker en beter met gegevensvalidatie kon worden opgelost; dan was er ook geen VBA nodig.
Het probleem in het kort: de toegestane invoer in een cel is afhankelijk van de keuze, die in een andere cel is gemaakt.

voetbalcompetitieOm de daar geschetste oplossing in de praktijk te kunnen laten zien, heb ik de stand van de twee Nederlandse profvoetbalcompetities genomen (Bron: www.voetbaluitslagen.nl).
NB Het valt me nu pas op hoe goed de stad Eindhoven het doet!

Stand voetbalcompetities

Dus eerst maar eens een voorbeeld opzetten (zie Voorbeeldbestand):voetbalcompetitie

  1. eerst de standen van de twee competities onder elkaar gezet, gesorteerd op clubnaam, waarbij de kolom met de punten (Pnt.) voor ons het belangrijkste is
  2. dan een schema opzetten van de beste 3 per competitie (zie hierboven).
    Daarbij maak ik gebruik van de functie Grootste:
    =GROOTSTE(H15:H32;1)
    Dit levert het maximum op van de Eredivisiepunten; in het Voorbeeldbestand, tabblad CompOvz1, heb ik deze reeks een naam gegeven, ErePnt, zodat de bedoeling van de formule duidelijker is.
    Door de 1 te veranderen in 2 vinden we het één na hoogste puntentotaal enz.
    Via Index en Vergelijken (zie het artikel over Verticaal zoeken) vinden we de bijbehorende clubs.
    NB zoals uit de stand hierboven mag blijken gaat het bij een gelijk aantal punten niet altijd goed; er wordt geen rekening gehouden met doelsaldo.
  3. dan nog wat Voorwaardelijke opmaak ‘strooien’ over het overzicht (zie mijn vorige blog) en we krijgen wat beter inzicht in de voetbal-verhoudingen in Nederland.
    NB we zien nu wel dat AZ en Feyenoord een gelijk aantal punten hebben
  4. voetbalcompetitiede verhoudingen tussen de clubs kunnen ook grafisch worden weergegeven.
    Op de x-as staan de gegevens van de kolommen B en C; Excel zorgt zelf voor een duidelijke lay-out wat betreft de indeling van de twee competities.
    De titel is dynamisch, wat in dit geval inhoudt dat deze verandert wanneer de datum in cel B2 wordt gewijzigd:
    * maak een willekeurig titel aan,
    * kvoetbalcompetitielik ergens in de titel,
    * kies de formulebalk en tik een verwijzing naar de cel met de gewenste titel-tekst in, inclusief de naam van het tabblad en een !
    In het voorbeeld staat de titel-tekst in cel N27, waar met behulp van de functie Teken een scheiding tussen de twee elementen van de titel is gemaakt.

Combinatie van grafieken

De grafiek kan wel wat duidelijker: een betere scheiding tussen de twee competities, wie staan bovenaan, hoe staat mijn favoriete club er voor?voetbalcompetitie

  1. in het Voorbeeldbestand, op het tabblad CompOvz2, is het onderscheid tussen de competities geregeld door de behaalde punten in 2 verschillende kolommen te plaatsen, een extra kolomgrafiek toe te voegen, de overlap van de grafieken op 100% te zetten en de kleuren aan te passen
  2. daarna is er een label toegevoegd aan de nummers 1 tot en met 3: achter iedere club (in kolom F) staat een formule, die kijkt of de club bij de eerste 3 hoort:
    =ALS(D15=Ere_1;1;ALS(D15=Ere_2;2;ALS(D15=Ere_3;3;””)))
    Ere_1 is de naam van de cel, die het aantal punten van de aanvoerder van de ranglijst bevat etc.
    Het bereik F15:F52 is als een nieuwe grafiek toegevoegd; bij Opmaak is gekozen voor Geen opvulling maar wel zijn Gegevenslabels toegevoegd
  3. om onze favoriete club er te laten uitspringen, voegen we een nieuwe grafiek toe, die alleen de punten van deze club bevat; zie kolom G:
    =ALS(C15=Voorkeur;D15+E15;””)
    Geef deze grafiek een afwijkende kleur.

Gegevensvalidatie

In het vorige voorbeeld staat de naam van de favoriete club in cel J4, die de naam Voorkeur heeft gekregen.
De (groene) kolom in de grafiek bij de favoriet wordt natuurlijk alleen maar zichtbaar, wanneer die cel een bestaande clubnaam bevat. In Excel dwingen we dat af via Gegevensvalidatie.

  1. voetbalcompetitieselecteer cel J4 in het tabblad CompOvz2 van het Voorbeeldbestand
  2. kies binnen de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  3. er opent zich een keuzescherm:
    voetbalcompetitie
  4. bij Toestaan kiezen we de optie Lijst
  5. en als Bron geven we het bereik op, waar alle clubnamen staan; in het voorbeeld heeft dat bereik de naam Teams.
    LET OP: denk aan het =-teken

voetbalcompetitieSelecteer J4 en tik een clubnaam in; komt deze niet (precies) in de clublijst voor dan krijgt u een foutmelding. Het is daarom handiger om het ‘vinkje’ achter de cel te gebruiken, zodat u de keuzelijst kunt gebruiken.

‘Meervoudige’ keuzelijst

In het vorige voorbeeld bestaat de lijst, waaruit een favoriete club gekozen kan worden, uit 38 teams.
Via scrollen in de zijbalk van de keuzelijst is de gewenste club nog vrij snel te vinden. Bij langere lijsten is dat vaak onhandig.

Als voorbeeld zou het in dit geval makkelijker zijn om eerst een competitie te kiezen (Eredivisie of Jupiler) en daarna pas een club uit de gekozen competitie.voetbalcompetitie
Dat is wat ik bedoel met ‘meervoudige’ keuzelijst: de inhoud van de tweede wordt bepaald door de keuze in de eerste.

Op de volgende manier is dit in te regelen (zie het Voorbeeldbestand, tabblad CompOvz3):

  1. voor de competitie-keuze voegen we weer een gegevensvalidatie toe (aan cel J3): kies binnen de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  2. bij Toestaan kiezen we opnieuw de optie Lijst
  3. voetbalcompetitiebij Bron wordt meestal een celbereik van toegestane elementen opgegeven.
    In dit geval hebben we maar 2 mogelijkheden: Eredivisie of Jupiler. De namen van de betreffende clubs liggen vast in de celbereiken EreTeams, respectievelijk JupTeams. Deze 2 namen geven we als bron van de lijst op, gescheiden door een ; (punt-komma).
  4. aan cel J4 geven we weer een gegevensvalidatie.
    Bij Toestaan kiezen we weer Lijst; bij Bron zijn we geneigd om de cel J3 mee te geven, maar helaas: Excel zal dan alleen de letterlijke tekst uit die cel aan de keuzelijst meegeven (dus EreTeams of JupTeams).
    Nee, bij Bron moeten we invullen
    =INDIRECT(J3)
    De functie Indirect zorgt er voor, dat niet de inhoud van J3 zelf wordt gebruikt, maar de inhoud van het bereik waar J3 naar verwijst (EreTeams of JupTeams dus).