Tagarchief: Tabel

Tabellen (deel 2)

Excel, , ,

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

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

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

Nadelen Tabellen in Excel

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

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

Gegevensvalidatie

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

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

Deze twee tabellen gaan we gebruiken voor een gegevensvalidatie:

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

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

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

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

Gegevensvalidatie met Tabellen

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

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

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

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

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

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

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

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

Kunst en Excel

Excel,

Perry2Een tentoonstelling over het leven en het werk van Grayson Perry in het Bonnefantenmuseum in Maastricht (nog te zien tot en met 5 juni 2016; laat deze kans niet voorbij gaan!) ontroerde mij zeer.
Waarom eigenlijk? Ik weet nog steeds niet of ik zijn (of haar?) vazen nu zo mooi vind. Ook vond ik de wandkleden in eerste instantie niet echt speciaal. Het is toch alleen maar raar om met een roze motor door Europa te toeren met je lievelingsbeer achterop? Een huis bouwen met allemaal vreemde hoekjes en beelden, dat is toch niet normaal?
PerryMotor

Maar alles kwam in perspectief te staan, toen ik de films over zijn leven en werk had bekeken: toen kwam het besef, dat iemand probeert ons een spiegel voor te houden door zijn/haar (fantasie)wereld en leven aanschouwelijk te maken. Maar ik blijf het moeilijk vinden om het gevoel, dat de tentoonstelling bij mij opriep, onder woorden te brengen.

Dat wordt dan het bruggetje naar Excel: het gaat mij makkelijker af om (een onderdeel van) Excel uit te leggen (hoop ik!).

De tentoonstelling liet me ook weer zien, dat ik zelf dit soort kunstzinnige creativiteit mis. Maar voor mij is de Kunst van het Programmeren (was een vak op de TH-Eindhoven) ook een vorm van creativiteit is: hoe los je slim (en mooi) bepaalde problemen op.

Laten we overgaan naar Excel. Deze keer ga ik mijn belofte inlossen en iets vertellen over tabellen in Excel (en waarom we die veel meer moeten gebruiken ofwel de 10 voordelen van Tabellen).

Tabel maken

Nu de tentoonstelling van Perry de wereld overgaat (na Maastricht gaat die naar Denemarken), moet alles natuurlijk geïnventariseerd worden, inclusief de grootte van de objecten.

Kunst1In het Voorbeeldbestand in het tabblad Cat1 heb ik een begin gemaakt met de catalogus: in de kolommen staan de rubrieken die ik wil vastleggen en in de rijen komen dan alle objecten.

Van dit overzicht gaan we een Excel-tabel maken:

  1. selecteer één van de cellen in het overzicht (door er op te klikken), bijvoorbeeld cel C6
  2. kies in de menutab Invoegen binnen het blok Tabellen de optie Tabel
  3. Kunst3Excel beoordeelt zelf welke gegevens waarschijnlijk in de tabel moeten worden opgenomen. In dit geval klopt dat niet helemaal (de kopregel Vazen nemen we niet mee): $B$2 moet gewijzigd worden in $B$3. Het vinkje bij kopteksten is wel juist.

Kunst4En daar is ons nieuwe overzicht: automatisch inclusief Filters bij de koppen (voordeel 1), ‘zebra-lijntjes’ voor de leesbaarheid (voordeel 2), Kunst6een eigen menutab voor diverse instellingen Kunst5 (voordeel 3) en het gegevensgebied heeft direct een naam (hier Tabel4, te vinden in het tabblad Hulpmiddelen voor tabellen en is aanpasbaar; voordeel 4)

Tabel uitbreiden

De grootste voordelen worden pas zichtbaar als we de tabel gaan uitbreiden (zie het Voorbeeldbestand, tabblad Cat2):Kunst7

  1. de logistiek-medewerker van Perry wil niet alleen de H, L en B  van de objecten weten, maar ook hoeveel ruimte deze innemen.
    In cel G3 komt de tekst Inhoud (cm3).
    Aan de zebra-lijntjes is te zien dat Excel automatisch de tabel uitbreidt met een extra kolom (voordeel 5).
    NB selecteer de 3 achter cm, druk op Ctrl-1 (Celeigenschappen) en kies Superscript
  2. start een formule in cel G4 door het =-teken in te tikken, klik dan met de muis in cel D4, tik het *-teken, klik in E4, weer het *-teken, klik in F4 en druk dan op Enter.
    In plaats van de standaardverwijzing =D4 * E4 * F4 heeft Excel een beter leesbare formule geplaatst (voordeel 6).
  3. Nog mooier: de formule is direct naar beneden naar alle rijen gekopieerd (voordeel 7).
    Wel zelf even de opmaak regelen: kies alle cellen met de formule in kolom G en pas de lay-out aan (bijvoorbeeld via Ctrl-1).
  4. We voegen nog 2 vazen aan de lijst toe: in het tabblad Cat2 staan de gegevens daarvan. Selecteer deze gegevens, kies Kopiëren en Plak ze in B9.
    Excel zal de tabel automatisch uitbreiden (zie de zebra-lijntjes) en vult achteraan ook direct de juiste formule toe (voordeel 8).

NB de naamgeving [@Hoogte] is even wennen: de rechte haken geven aan, dat het een naam in een tabel is; door de @ ‘weet’ Excel dat hij niet zomaar een hoogte moet nemen maar die uit dezelfde rij en Hoogte is één van de kopteksten.

Analyse van tabelgegevens

Een betrokken archivaris wil vanuit het overzicht wat extra informatie ophalen (zie het Voorbeeldbestand, tabblad Cat3).
Kunst8

  1. aangezien de archivaris niets kan met cm3, heeft hij een kolom met de inhoud in liters toegevoegd door een koptekst in te tikken en de formule =[@[Inhoud (cm3)]]/1000 (ja, de archivaris kan met Excel omgaan!)
  2. Kunst9aangezien hij wil weten hoeveel werken er uit de diverse jaren komen maakt hij een draaitabel:
    * selecteer één van de cellen in het overzicht (door er op te klikken), bijvoorbeeld cel C6
    * kies in de menutab Invoegen binnen het blok Tabellen de optie Draaitabel
    * Excel kiest automatisch het juiste bereik (hier tblCat3), klik OK
    * sleep Jaar naar de Rijlabels en Omschr naar het Waarden-gebied.
  3. Kunst10nu hij toch bezig is:  misschien is het ook wel handig om te weten hoe hoog de stapel wordt wanneer de werken per jaar in het magazijn moeten worden bewaard:
    * sleep ook de Hoogte naar het Waarden-gebied

Maar wat nu als we nog meer vazen aan onze catalogus toevoegen:

  1. op het tabblad Cat3 van het Voorbeeldbestand staan nog 2 extra exemplaren: kopieer de gegevens en plak ze in cel B11.
    De tabel wordt automatisch uitgebreid en de formules achteraan worden ook naar de nieuwe regels doorgetrokken
  2. Jammer, de draaitabel is niet gewijzigd! Iedereen, die gebruik maakt van dit (verders) formidabele hulpmiddel weet, dat na het aanpassen/uitbreiden van de brongegevens de draaitabel ververst moet worden: klik met de rechter muisknop ergens in de draaitabel en kies Vernieuwen.
    Bij ‘gewone’ Excel-overzichten is dit niet voldoende; dan moet ook nog worden aangegeven dat de draaitabel naar een groter bereik moet ‘kijken’.
    Is de bron een tabel dan is alleen Vernieuwen van de draaitabel voldoende; intern weet Excel dat de tabel ‘gegroeid’ is (voordeel 9).

Verwijzingen naar tabellen

Een andere archivaris is wat minder handig met Excel en weet niet goed hoe hij met draaitabellen moet omgaan (hij vergeet altijd Vernieuwen te kiezen en komt dus met de verkeerde cijfers).

Hij maakt daarom zijn eigen ‘draaitabel’ (creatief toch?) om de totale hoogte en lengte van de vazen per jaar te krijgen:

  1. Kunst11in het Voorbeeldbestand in het tabblad Cat4 komt in cel K19 het jaar waarvan hij het overzicht wil hebben (in het voorbeeld 2000).
  2. en daaronder de formule
    =SOM.ALS(tblCat4[Jaar];K19;tblCat4[Hoogte])
    Ook hier geldt weer: als de tabel wordt uitgebreid met nieuwe vazen dan zal de formule alle nieuwe regels automatisch in het resultaat meenemen.
    NB1 gelukkig hoeft dit niet ‘met de hand’ zo ingevoerd te worden: tik het begin van de formule tot en met het haakje-openen in en selecteer met de muis de cellen B4 tot en met B12, tik de punt-komma etc.
    NB2 in dit geval staat voor de velden Jaar en Hoogte geen @, omdat niet de gegevens uit de overeenkomende regel moeten worden meegenomen maar alles uit de betreffende kolom.
    NB3 omdat de verwijzingen buiten de tabel staan wil Excel wel weten uit welke tabel de gegevens moeten worden opgehaald; vandaar de tblCat4 voor de velden.
  3. voor de lengte in cel K21 tikken we eenzelfde soort formule in (of we kopiëren de formule van K20 en dan op de juiste plaatsen wat aanpassingen)
  4. in J20 en J21 nog een tekst (Hoogte resp. Lengte) en het overzicht is klaar.

NB het voordeel van deze ‘eigen draaitabel’ is dat die bij iedere wijziging van de bron direct wordt aangepast; Vernieuwen is niet nodig.

LET OP In het Voorbeeldbestand staan in J20 en J21 geen harde teksten, maar verwijzingen naar de kopjes in de tabel: =tblCat4[[#Kopteksten];[Hoogte]].
Wanneer de kopjes wijzigen (bijvoorbeeld ipv Hoogte wordt H (cm) gebruikt) dan past het overzicht zich automatisch aan. De draaitabel laat ons dan in de steek!

Kunst12Een van de vele opties van tabellen, die je terug vindt onder Hulpmiddelen voor tabellen, is het al dan niet zichtbaar maken van een Totaalrij (voordeel 10):

  1. selecteer één van de cellen in de tabel van tabblad Cat4 (door er op te klikken), bijvoorbeeld cel C6
  2. vink in de menutab Hulpmiddelen voor tabellen binnen het blok Opties voor tabelstijlen de optie Totaalrij aan
  3. onderaan iedere kolom kan nu een bepaald soort totaal gekozen worden (Som, Aantal, Gemiddelde etcetera).

Kunst13NB naar de totalen kunnen ook weer verwijzingen gemaakt worden; in cel K23 wordt de totale hoogte opgehaald:
=tblCat4[[#Totalen];[Hoogte]]

Cadeau: dit was voordeel nummer 11!

Temperaturen en thermometer

Excel, , , , , , ,

temp1Het is vandaag toch geen weer om buiten te zijn, dus maar achter de PC gekropen om een artikel voor G-Info te schrijven.

Het onderwerp lag voor de hand: waar temp2hebben we het allemaal over de laatste dagen? Juist ja, de temperaturen!

Eens kijken of we (met behulp van Excel) nog wat informatie kunnen toevoegen. Uit KNMI-gegevens maken we een grafiek waardoor het onderwerp nog meer gaat leven.

Voor de fun: in het Voorbeeldbestand vind je ook een “live”-thermometer.

Brongegevens

temp3Voordat we temperatuurgrafieken kunnen maken hebben we brongegevens nodig.
Even “googelen”: al snel bleek, dat de KNMI heel veel (historisch) materiaal klaar heeft staan, dat je gratis kunt downloaden (zie http://www.knmi.nl/klimatologie/uurgegevens/select_uur).

Ik heb voor dit artikel alleen de temperatuur per uur voor drie weerstations voor het lopende jaar binnengehaald (hoewel de gegevens over de duur van de neerslag ook wel eens interessant zouden kunnen zijn!)

Na het downloaden hebben we een tekstbestand op de PC staan; deze gegevens allereerst maar eens in Excel geplakt (zie het tabblad KNMI in het Voorbeeldbestand).
Na diverse regels toelichting komen de gegevens waar het ons om te doen is: het weerstationnummer, de datum, het uur en de temperatuur. Per dag zien we alle gegevens in 1 cel komen; gescheiden door een komma.
Dat wordt “knippen”:

  1. selecteer alle cellen met temperatuurgegevens (in het tabblad KNMI de cellen B17:B13120)
  2. temp4kies in de menutab Gegevens de optie Tekst naar kolommen
  3. er opent zich een nieuw scherm: kies daar de optie Gescheiden en klik op de knop Volgende
  4. zorg dat in het volgende scherm bij Scheidingstekens ook Komma staat aangevinkt en klik op de knop Voltooien
  5. alle gegevens zijn nu mooi over kolommen verdeeld

Wat opvalt: de datum staat in het militaire formaat (jjjjmmdd, dus 4 cijfers voor het jaar, 2 voor de maand en 2 voor de dag; in die volgorde!).
Daarnaast moeten we de temperatuur nog door 10 delen om netjes graden Celsius te krijgen.

TIP op de volgende manier gaat dat het snelst:

  1. klik ergens in een lege cel de waarde 10 in
  2. kopieer deze cel (bijvoorbeeld via Ctrl-C)
  3. selecteer alle temperatuurcellen
  4. klik met de rechtermuisknop ergens in de selectie
  5. kies de optie Plakken speciaal …
  6. kies bij Bewerking de optie Delen
  7. klik op de knop OK
  8. maak de cel uit punt 1 weer leeg

Temperatuurgrafiek1

Om op de standaardmanier een grafiek te maken moeten we de gegevens van de KNMI nog iets anders indelen: na het “knippen” zetten we de temperaturen van de drie weerstations achter elkaar in verschillende kolommen en zetten nog wat verklarende teksten boven de kolommen (zie tabblad Grafiek1).

temp5Maak op de ‘gewone’ manier een grafiek.
Zorg dat op de x-as de Datum en het Uur zichtbaar worden door én de gegevens van kolom B én die van kolom C op te geven.

Door de grote hoeveelheid dagen en daarbinnen de temperatuur per uur wordt de grafiek moeilijk te lezen.

Temperatuurgrafiek2

Een alternatieve grafiek kunnen we creëren door middel van een draaitabel. Een groot voordeel hierbij is dat de brongegevens niet hoeven worden aangepast.

In het Voorbeeldbestand zijn in het tabblad KNMI_data de brongegevens in een Excel-tabel opgenomen. Nieuwe gegevens kunnen onderaan worden toegevoegd; zijn ze nog niet ‘geknipt’, doe dat dan hier.  Excel neemt automatisch de deling van de KNMI-temperatuur door 10 voor zijn rekening en als ’toegift’ wordt de datum in een normaal formaat gezet.

In een draaitabel worden alle gegevens uit de tabel in een bruikbaar formaat gezet (zie tabblad Draai in het Voorbeeldbestand).
Hierbij zijn een paar handigheidjes verwerkt:

  1. in de kolommen zijn de weerstations weergegeven; standaard komen hier de nummers tevoorschijn. Deze nummers heb ik overschreven met de betreffende namen. Voortaan houdt Excel deze wijziging vast.
  2. in de rijen staan naast de uren ook de echte datums uit KNMI_data. Deze zijn echter zodanig gegroepeerd, dat er ook een onderscheid in jaren en maanden wordt gemaakt:
    * klik met de rechtermuisknop op een van de datums
    * temp6kies de optie Groeperen
    * in het vervolgscherm de grenzen ruim genoeg zetten, zodat toekomstige gegevens ook direct goed verwerkt worden
    * klik bij de optie Op de Dagen, Maanden en Jaren aan
    * klik op de knop OK
    * sleep de Jaren en Maanden van de Rijlabels naar het Rapportfilter

Wanneer de draaitabel actief is (klik ergens in de draaitabel) komt er bovenaan een nieuwe set tabbladen tevoorschijn: Hulpmiddelen voor draaitabellen. Daar vinden we binnen de menutab Opties een knop Draaigrafiek.

temp1Het resultaat staat op het tabblad Grafiek2.
Via de filters op Jaren, Maanden en Station kunnen meer of minder en andere gegevens zichtbaar gemaakt worden.

LET OP als er regels aan de tabel KNMI_data zijn toegevoegd, kies dan wel nog de optie Vernieuwen in de Draaitabel of de Draaigrafiek (hoe? Klik rechts ergens in de tabel of aan de rand van de grafiek).

Thermometer

Terwijl ik dit aan het schrijven ben, kijk ik af en toe op de thermometer om te kijken of mijn warmtegevoel overeen komt met de werkelijkheid. Nou, hierbinnen is het bijna 30 graden en zo voelt het ook! Dus nog maar wat water drinken.

Maar dit is wel aanleiding om te kijken of we in Excel ook een thermometer kunnen inbouwen.
temp7Daar gaat ie (zie tabblad Thermometer in het Voorbeeldbestand):

  1. om de keuze van weerstation te vergemakkelijken maken we een klein tabelletje met ‘rugnummers’. Wanneer we aan de tabel KNMI_data ook andere weerstations toevoegen dan moet dit lijstje uitgebreid worden
  2. temp8in de cellen F2 en F3 kan gekozen worden voor welke dag en welk station de temperatuur moet worden weergegeven.
    Denk aan de datum-notatie!
    Cel F3 is van een Gegevensvalidatie voorzien, zodat daar alleen nummers gekozen kunnen worden uit het lijstje van punt 1. In cel F4 komt dan automatisch de naam van het station via de formule
    =VERT.ZOEKEN(F3;Stations;2;ONWAAR)
    De waarde van cel F3 (in dit geval 380) wordt opgezocht in de tabel met de naam Stations (het lijstje uit punt 1). Als die gevonden wordt, dan wordt de waarde uit de 2e kolom teruggegeven (de naam dus). Door Onwaar geven we aan, dat we een exacte match willen; de lijst hoeft dus niet gesorteerd te zijn.
  3. dan gaan we de temperatuur opzoeken, die bij die dag én dat station hoort en dat per uur:
    =SOMMEN.ALS(KNMI_data[Temp];KNMI_data[Datum];$F$2;KNMI_data[Station];Thermometer!$F$3;KNMI_data[Uur];E10)
    Niet schrikken! Verticaal zoeken met verschillende voorwaarden kan niet, dus daarom iets ingewikkelder: tel alle temperaturen in de kolom Temp van de tabel KNMI_data op (we zorgen, dat er maar 1 temperatuur wordt opgehaald, dus dat is een makkie voor Excel) als aan de volgende voorwaarden wordt voldaan:
    * in de kolom Datum van de tabel KNMI_data staat de waarde uit cel F2
    * én in de kolom Station staat de waarde uit cel F3
    * én het Uur komt overeen met cel E10
    Door de relatieve en absolute verwijzingen kunnen we de formule uit F10 naar beneden kopiëren.
  4. omdat op deze manier de waarde 0 (nul) ontstaat als er geen gegevens zijn staat er nog een ALS-formule omheen, die een lege resultaat genereert als er geen temperatuur te vinden is.
  5. temp2in de cellen F5, F6 en F7 worden de minimum-, maximum- en laatst bekende waarde opgehaald (voor de historische data zal dit dus altijd 12 uur ’s nachts zijn).
    NB de formule in cel F7, die als matrixformule is ingevoerd (zie de accolades), vergt teveel uitleg voor dit weer! Probeer zelf de logica te ontrafelen. Kom je er niet uit; stuur een berichtje via de site.
  6. dan een grafiek maken met de 3 waardes uit de cellen F5, F6 en F7, zodat we naast de “actuele” temperatuur ook het minimum en maximum van die dag zien.
    De actuele/laatste waarde van die dag als een staafdiagram, de andere twee als lijngrafiek, inclusief markering.
    Omdat we onder 0 graden ook een markering willen zien, is er nog een reeks toegevoegd met de waarde -50.
  7. Bijna klaar: het moet er nog als een echte thermometer uitzien. Dus een afbeelding van een thermometer gegoogeled en daar de grafiek overheen gelegd, zodanig dat de schalen overeen komen.
    Dan in de grafiek alle overbodige zaken als assen en rasterlijnen verwijderen en het grafiek- en tekengebied transparant maken (de optie Geen opvulling).
    De 2 objecten (Grafiek en Afbeelding) allebei selecteren en Groeperen kiezen, zodat ze samen verplaatst kunnen worden: daar is onze thermometer!

NB door de tabel in het tabblad KNMI_data aan te vullen met de meest recente gegevens zal de thermometer een echt actuele stand weergeven.
Helaas nog geen real-time! Iemand een idee hoe dat zou kunnen??

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.