Tagarchief: Datum

Excel en het World-Wide-Web

Excel, , , , , , ,

Nee, dit is geen aankondiging van een nieuwe thriller, die ik geschreven zou hebben (dat laat ik aan een buurman over: zie glengoutstap.nl); in dit artikel wil ik laten zien welke mogelijkheden er zijn om Excel en het internet (www, world-wide-web) met elkaar te verbinden.

Het gaat dan wel om een eenzijdige verbintenis: hoe kunnen we met Excel op een makkelijke (?) manier informatie aan het internet onttrekken.

Methode 1

De meest gebruikte methode is:

  1. zoek op internet de pagina met gegevens, waar je iets mee wilt gaan doen in Excel
  2. selecteer met de muis de betreffende data
  3. kies Kopiëren
  4. ga naar Excel en kies Plakken

Voor eenmalige acties is hier niets mis mee; dit is dan zeker efficiënt en effectief. Maar wordt het een dagelijkse handeling om bijvoorbeeld koersgegevens op te halen, dan kan dat ook anders.

Methode 2

behr.nlVoor onderzoek naar koersverloop van indexen en bedrijven kom ik regelmatig op de site www.behr.nl; niet voor privé-gebruik ;-).
Daar kun je onder andere historische dagkoersen ophalen.
Even zoeken, maar via de URL www.behr.nl/Beurs/Slotkoersen/.a/aegon zijn de dagkoersen van Aegon te vinden.

Behr.nlBehr levert als resultaat een pagina met datums en de daarbij behorende koersen. Geen verdere poespas daar omheen; dus zeker ook geschikt voor een snelle kopieer- en plak-actie.

Maar heb je dit overzicht vaker nodig dan gaat we dat slimmer aanpakken:

  1. zorg in Excel, dat er een nieuwe werkmap geopend klaar staat (bijvoorbeeld door Ctrl-N te drukken)
  2. Webkies binnen de menutab Gegevens in het blok Ext. geg. ophalen (ofwel Externe gegevens ophalen) de optie Van web
  3. WebEr opent zich een nieuw scherm; op de adresregel moet de hierboven genoemde URL ingevoerd worden.
  4. klik op de button Ga naar
  5. de betreffende internet-pagina wordt opgehaald en onder de adresregel weergegeven. Alle blokken die op die pagina als een tabel zijn opgemaakt hebben een tekentje Web gekregen. Door daar op te klikken kan aangegeven worden of dat betreffende blok wel of niet moet worden opgehaald. In dit geval klikken we alleen op het 2e pijltje; dat wordt dan een groen vinkje.
  6. kies dan onderaan het webquery-scherm de optie Importeren
  7. nog even aangeven waar de gegevens moeten komen en klaar: de data staan nu in Excel om verder verwerkt te worden.
  8. sla het bestand op met de naam Aegon.xlsx.

Moet de lijst morgen (of volgende week, volgende maand) geupdate worden:

  1.  open het bestand Aegon.xlsx
  2. klik met de muis rechts op één van de gevulde cellen en kies de onderste optie Vernieuwen

In het Voorbeeldbestand staat op het tabblad Aegon deze web-query. Daar kan die dus ook bijgewerkt worden.

LET OP de koppeling tussen Excel en internet is gebaseerd op het gebruik van tabellen op web-pagina´s.
Door de implementatie van nieuwere technieken bij de ontwikkeling van web-pagina’s komt het steeds vaker voor, dat Excel geen gegevens of veel teveel gegevens gaat ophalen. Aandachtspuntje voor Bill Gates!

Gegevens ‘knippen’

Helaas zijn de data, die op deze manier bij Behr.nl worden opgehaald, niet direct bruikbaar:  op iedere rij staat de datum en de koers in dezelfde cel (gescheiden door een :-teken), de datum-notatie ‘ziet er niet uit’ en de decimalen in de koers worden vooraf gegaan door een punt ipv een komma.
In het tabblad Heineken in het Voorbeeldbestand zijn daarom 2 kolommen toegevoegd, die ons het omzetten uit handen nemen: in de eerste kolom wordt mbv de functie DATUM een echte datum gecreëerd:

=DATUM(“20″&LINKS(B2;2);DEEL(B2;3;2);DEEL(B2;5;2))

PS1 de 2 meest linkse posities van cel B2 geven het jaar weer, het gedeelte vanaf positie 3 en 2 lang (dus positie 3 en 4) vormt de maand en de posities 5 en 6 vormen de dag.
PS2 aangezien Behr geen eeuw-aanduiding gebruikt ‘plakken’ we zelf er 20 voor, anders gaat Excel met datums uit de 20e eeuw werken.

De koers bepalen we op de volgende manier:

=WAARDE(SUBSTITUEREN(RECHTS(B2;LENGTE(B2)-7);”.”;”,”))

We nemen van B2 het rechtse gedeelte (we laten 7 tekens weg: de datum en het :-teken) en substitueren de punt door een komma. Omdat Excel dan ‘denkt’, dat dit een tekst oplevert, moeten we de waarde daarvan nemen.

Gegevens opmaken

In het Voorbeeldbestand worden ook de koersen van KPN opgehaald (op het tabblad KPN, dus).
Deze pagina is verder opgemaakt met kaders etc. Zorg er voor, dat bij het Vernieuwen deze opmaak niet wordt overschreven: klik rechts op één van de cellen uit de web-query (dus niet de hulp-kolommen) en kies de optie Eigenschappen van gegevensbereik …
Zorg dat Celopmaak behouden staat aangevinkt.
Experimenteer met de diverse opties in dit keuzescherm.

AEX

Het verloop van de AEX-index (en de aandelen, die daar onderdeel van uitmaken) zijn te vinden op www.beurs.nl/koersen/aex/p1.

Helaas is dit ook zo’n site met veel ‘ballast’ rond de cijfers: zie het tabblad AEX in het Voorbeeldbestand. Pas op regel 139 komen de data, waar we naar op zoek zijn.

Nog enkele opmerkingen bij dit tabblad:

  1. na het invoeren van het adres in de web-query komt er een Script-foutmelding: op Ja klikken
  2. Webtijdens het bewerken van de Webquery is via de button Opties… de opmaak ingesteld op HTML-indeling. Een groot gedeelte van de internet-opmaak en -mogelijkheden blijft dan gehandhaafd.
  3. WebDaarnaast is via Eigenschappen van gegevensbereik … ingesteld dat iedere minuut de pagina wordt geactualiseerd.

NB1 hoewel de site beurs.nl de koersen echt real-time laat zien, is dat op deze manier niet het geval. Een vertraging van 20-30 minuten is normaal.

NB2 ’s avonds zullen de koersen niet meer veranderen; de AEX sluit om 17:30 uur (en gaat om 9 uur weer van start).

Opmaak AEX

WebIn het tabblad AEX2 is een mooier overzicht van de AEX-koersen te vinden.  Met behulp van de functie VERT.ZOEKEN worden de gegevens uit het tabblad AEX opgehaald (en nu maar hopen dat de lay-out van de site en dus van het tabblad AEX niet verandert!).
Door gebruik te maken van voorwaardelijke opmaak is snel te zien welke koersen veel of weinig zijn gestegen of gedaald ten opzichte van de slotkoers van de vorige dag (de grens, waarbij een regel groen of rood wordt, is in cel C2 aan te passen).

NB bij het Vert.Zoeken wordt gebruik gemaakt van de functie KOLOM. Deze retourneert het kolomnummer van de cel, die als parameter wordt meegegeven. Geef je geen parameter mee dan geeft de functie de kolom terug van de cel waar de functie in staat.
Op deze manier is het mogelijk om de formule in cel C5 door te kopiëren naar het hele gebied zonder dat er nog aanpassingen nodig zijn. Let ook op de notatie van de Zoekwaarde ($B5; de verwijzing is dus half absoluut en half relatief).

Wandelingen op Veldkruus.nl

In het Voorbeeldbestand wordt op het tabblad Veldkruus het totaal-overzicht van de wandel- en fiets-tochten van de website Veldkruus.nl opgehaald.
Duidelijk is te zien, dat ‘harde returns’ in teksten in Excel als nieuwe regels tevoorschijn komen; voor een snelle verwerking van de gegevens (bijvoorbeeld sorteren op lengte) wordt het er dan niet makkelijker op.

NB even reclame maken voor een andere hobby van mij: met enkele ex-collega’s proberen we de veldkruisen, kapelletjes etcetera in Zuid-Limburg in kaart te brengen. Om het nog aantrekkelijker te maken zetten we ook ‘kruis-tochten’ langs deze objecten uit: zie Veldkruus.nl.

Dynamische web-query’s

Een groot nadeel van de hiervoor geschilderde koppeling tussen Excel en het Web is, dat de query een statisch karakter heeft: heb je een mooi overzicht van Heineken gemaakt en je wilt dezelfde gegevens van Starbucks zien, dan moet je alles opnieuw uitvoeren.

Gelukkig kunnen we de query met wat kunstgrepen dynamischer maken. Aan de hand van de slotkoersen van Behr.nl gaan we dit uitwerken. Het begin is hetzelfde als hierboven al aangegeven:

  1. zorg in Excel, dat er een nieuwe werkmap geopend klaar staat (bijvoorbeeld door Ctrl-N te drukken)
  2. kies binnen de menutab Gegevens in het blok Ext. geg. ophalen de optie Van web
  3. in het ‘Nieuwe web-query’-scherm voeren we op de adresregel de URL www.behr.nl/Beurs/Slotkoersen/.a/aegon in.
  4. klik op de button Ga naar
  5. de betreffende internet-pagina wordt opgehaald en onder de adresregel weergegeven. Alle blokken die op die pagina als een tabel zijn opgemaakt hebben een tekentje Web gekregen. In dit geval alleen op het 2e pijltje klikken.

In plaats van importeren, slaan we deze query op. Rechts boven (naast Opties…) zit daarvoor een button Web9. Sla de query op met de naam Slotkoersen.iqy , bijvoorbeeld op het Bureaublad. Kies daarna Annuleren.

Web10Nu gaan we dat bestand, Slotkoersen.iqy, openen met een simpele tekstverwerker (in het voorbeeld met Kladblok; in de Verkenner rechtsklikken op het bestand).
Iedere web-query moet met de eerste 2 regels beginnen; de 3e regel herkennen we natuurlijk direct: dat is onze URL.
Dan een lege regel; gevolgd door de mogelijke instellingen voor web-query’s.

We passen de derde regel aan: op de plaats waar nu hard staat wat gezocht moet gaan worden (in dit geval ‘.a/aegon’) komt een parameter Fonds:

http://www.behr.nl/Beurs/Slotkoersen/[“Fonds”, “Geef codering van fonds (incl. letterindeling zoals ‘.k/kpn’)”]

LET OP de plaats van de [‘s, aanhalingstekens en de komma zijn wezenlijk

Sluit Kladblok en sla de query onder dezelfde naam op en dubbelklik op de bestandsnaam.
Excel zal opstarten en vraagt naar een code voor het fonds (zoals we hiervoor in de iqy bij de parameter hebben ingevoerd) en laat daarna de betreffende gegevens zien.
Klik rechts op een cel en kies Vernieuwen: dezelfde vraag komt weer en nu heb je de mogelijkheid om een ander fonds op te halen.
Zie ook het tabblad Behr1 in het Voorbeeldbestand.

Web11Het kan nog mooier:

  1. dubbelklik op Slotkoersen.iqy
  2. geef een code op: bijvoorbeeld .h/heineken
  3. tik dan in cel B1 in: .a/aegon
  4. rechtsklik op een cel van de web-query en kies Vernieuwen
  5. in plaats van een code tikken we nu in =b1 en zetten de 2 vinkjes aan
  6. tik in cel B1 een andere code: .s/Starbucks en de gegevens worden automatisch opgehaald!

In het tabblad Behr2 van het Voorbeeldbestand is dit nog iets verder uitgewerkt met behulp van Gegevens-validatie.

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.

Teksten samenvoegen

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

Het is weer tijd voor het doen van aangifte voor de belastingen. Dan is het altijd handig om Excel achter de hand te hebben om bedragen op te tellen etc.
Maar wist u, dat Excel ook teksten kan ‘optellen’? Nou ja, beter gezegd samenvoegen.

Het komt in Excel nogal eens voor, dat de inhoud van verschillende cellen moet worden samengevoegd.
Zeker wanneer de gegevens uit een ander systeem of uit een Pdf-bestand worden overgenomen, komen bijvoorbeeld dubbele voor- en achternamen vaak in verschillende cellen terecht.

TEKST.SAMENVOEGEN

tekst samenvoegenIn het Voorbeeldbestand staat op het werkblad Namen een klein lijstje namen. Al snel is te zien dat de regels 4, 6 en 7 nog aanpassing nodig hebben.

Gelukkig heeft Excel een ingebouwde functie voor het samenvoegen van teksten: TEKST.SAMENVOEGEN (klinkt logisch, toch?).

Op basis van de geïmporteerde namen maken we als volgt een nieuw overzicht:

  1. iets onder het bestaande overzicht (in het voorbeeld in cel B10) verwijzen we naar de eerste cel van de kop door in te tikken  =B2
  2. deze formule kopiëren we één cel naar rechts en net zo vaak naar beneden als er regels zijn in het oorspronkelijke bestand. Gebruik hiervoor de vulgreep van cel B10; zie het artikel Doorvoeren en sorteren.
  3. de meeste cellen zijn natuurlijk meteen goed, maar bijvoorbeeld de tweede naam kan nog wel een aanpassing gebruiken. In de betreffende cel (in het voorbeeld B11) moeten de 2 voornamen gecombineerd worden: =TEKST.SAMENVOEGEN(B4;C4)
  4. het resultaat tekst samenvoegen is niet precies wat we willen; er moet nog een spatie tussen de 2 voornamen: =TEKST.SAMENVOEGEN(B4;” “;C4)
  5. deze formule kopiëren we naar alle cellen waar namen moeten worden samengevoegd (in het voorbeeld B13, B14 en C14)
  6. maar nu kloppen de achternamen van Marie Jose nog niet; de formule voegt de verkeerde cellen samen. Dit lossen we snel op:
    * selecteer de betreffende cel (in dit geval C13)
    * klik in de formulebalk of druk op F2 om de formule aan te passen
    * de cellen, die in de formule gebruikt worden hebben dan een gekleurde rand, overeenkomend met de celverwijzing in de formule: tekstsamen3
    * ‘pak’ met de muis de groene rand vast en sleep 1 cel naar rechts
    * idem met de blauwe rand; door dit slepen wordt de formule automatisch aangepast
  7. de achternaam van de tweede persoon is nog niet juist; de celverwijzing dient nog aangepast te worden.

Is het bestand veel groter, dan moet per regel bekeken worden welke formule waar moet komen; het handigste gaat dit door de 2 cellen van de tweede naam te Kopiëren en overal waar dezelfde situatie zich voordoet te Plakken. Idem voor de vierde en de vijfde naam (dan hebben we alle mogelijkheden gehad).

Alternatieve samenvoeging

Waar we in Excel een optelling van getallen regelen via het +-teken, kunnen we ook teksten ‘optellen’. Alleen wordt daar een ander teken voor gebruikt: & (ampersand; zie het interessante artikel op Wikipedia).

Een alternatieve (simpelere) manier om de naam Jan Willem te maken krijgen we dan als volgt: =B4&” “&C4

Zie verder het Voorbeeldbestand.

Flexibele overzichten

Tekst-samenvoegingen kunnen goed gebruikt worden om rapportages/overzichten flexibeler te maken.
Hoe vaak gebeurt het niet dat je een overzicht van een vorig jaar of vorige maand kopieert en vergeet om alle ‘harde’ jaar- en maand-aanduiding aan te passen.

Het is dan ook een goede gewoonte om harde gegevens (vaak parameters genoemd) apart vast te leggen en daar in formules naar te verwijzen.

tekstsamen4In het Voorbeeldbestand op het werkblad Parameters is dit in het kort uitgewerkt:

  1. leg het jaar vast in cel C2 en de maand in C3
  2. typ de volgende formule in cel B7:
    =”Overzicht van “&C3&” “&C2
    (denk aan de spaties op de juiste plaatsen!)
  3. verander een input-variabele en bekijk het resultaat.

In plaats van 2 variabelen kunnen we ook eenzelfde resultaat bereiken door uit te gaan van één datum (cel C4).
In cel F7 voeren we de volgende formule in: =”Overzicht van “&C4
tekstsamen5 Helaas, het resultaat is niet precies wat we verwachten! Excel “vergeet” nu dat we te maken hebben met een datum. We moeten zelf nog wat denkwerk verrichten:  =”Overzicht van “&TEKST(C4;”mmmm jjjj”). Door opmaak aan de broncel mee te geven, krijgen we wel het gehoopte resultaat.

 NB1 omdat we weten, dat cel C4 een datum voorstelt, geven we die cel met behulp van de functie TEKST een datum-opmaak; ‘speel’ met de notatie-instellingen: het aantal m‘s, zet er ook eens een d in etc.

NB2 met dezelfde functie TEKST kan ook de opmaak van een getal worden aangepast. De opmaak volgt dezelfde notatie als bij Celeigenschappen.

De formules in de Excel-sheet worden beter leesbaar wanneer de cellen met parameters een Naam krijgen. Geef je aan cel C2 de naam Jaar mee en aan C3 de naam Maand dan wordt de formule in B7 =”Overzicht van “&Maand&” “&Jaar
Zeker wanneer verwijzingen zich op andere werkbladen bevinden is een dergelijke formule veel duidelijker.

tekstsamen6In het kader van flexibiliteit: om bij een volgende maand niet allerlei wijzigingen te hoeven doorvoeren, moeten de datums natuurlijk ook afhankelijk zijn van een parameter:tekstsamen7
via de functies DATUM, JAAR en MAAND wordt gebruik gemaakt van de waarde in cel C4.
NB met DATUM zijn allerlei mogelijkheden denkbaar. Voorbeeld: wil je de laatste dag hebben van de maand, waarin de datum in C4 valt?
=DATUM(JAAR(C4);MAAND(C4)+1;1)-1
ofwel maak een datum door het jaar van C4 te nemen, de maand van C4 plus 1 (de volgende maand dus) en de eerste dag. Door dan van het resultaat 1 af te trekken weten we zeker dat we op de laatste van de betreffende maand uitkomen!

Eigen functies

In het eerste voorbeeld waren we al even bezig om alle namen goed te krijgen, ook al betrof het maar een bestandje met enkele regels.
Zeker wanneer het meer regels betreft en met meer variaties dan wordt het netjes samenvoegen van teksten een behoorlijk karwei.

tekstsamen8In het Voorbeeldbestand heb ik in het tabblad Tennis een overzicht opgenomen van een tenniscompetitie, zoals die binnen is gehaald vanuit een PDF. Zoals te zien is moet er flink ‘gepoetst’ worden. Voor dit soort werk heb ik een eigen TekstSamenvoeg-functie geschreven. Laten we die eerst aan het werk zetten:

  1. voeg op de plaats van kolom C een nieuwe kolom in
  2. in de nieuwe cel C3 plaatsen we de volgende functie:
    =TekstSamenvoegenRange(D3:E3)
  3. kopieer cel C3 naar beneden (dubbelklik op de vulgreep van cel C3)
  4. ook nu klopt nog niet alles: in regel 7 moeten 3 cellen samengevoegd worden. Daar moet de formule dus worden: =TekstSamenvoegenRange(D7:F7)
  5. kopieer deze cel naar alle regels waar drie cellen moeten worden samengevoegd
  6. selecteer cel D2, ‘pak’ de rand vast en sleep naar C2
  7. selecteer de hele kolom C (klik op de C boven de kolom)
  8. kies kopiëren en daarna direct Plakken/waarden; op deze manier is de inhoud van kolom C ‘hard’ gemaakt
  9. voeg op de plaats van kolom D een nieuwe kolom in
  10. in de nieuwe cel D3 plaatsen we de volgende functie:
    =TekstSamenvoegenRange(G3:I3)
  11. kopieer cel D3 naar beneden (dubbelklik op de vulgreep van cel D3)
  12. op verschillende plaatsen klopt het nu niet, bijvoorbeeld in regel 4. Selecteer cel D4 en druk op de functietoets F2. De cellen G4 t/m I4 hebben nu een blauwe rand. Pak de hoekpunt linksonder en sleep deze één cel naar rechts en druk op Enter.
  13. pas op dezelfde manier de overige formules aan, waar dat nodig is.
  14. sleep het kopje uit cel F2 naar D2
  15. maak kolom D hard en verwijder alle kolommen E t/m J

De VBA-routine van deze functie is te vinden in Module1 van het Voorbeeldbestand. Druk daartoe op Alt-F11 (dus de Alt-toets inhouden en op de functietoets F11 drukken). De commentaarregels (groene tekst) lichten de bedoeling van de functie en de diverse stappen toe.

De functie kent een optionele parameter, de KoppelTekst (die hoeft dus niet ingevuld te worden; in dat geval wordt hier dan een spatie gekozen).
wanneer we in cel C3 hadden ingevoerd =TekstSamenvoegenRange(D3:E3;” “) dan was het resultaat dus hetzelfde geweest (denk aan de aanhalingstekens om de spatie!).  Maar het had ook =TekstSamenvoegenRange(D3:E3;” | “) kunnen zijn etc.
Door =TekstSamenvoegenRange(D3:E3;TEKEN(10)) worden de elementen gescheiden door een ‘harde-return’ (ofwel nieuwe-regel-opdracht). Vink bij Celeigenschappen/Uitlijning dan wel de optie Terugloop aan.

Ik heb nog een andere functie toegevoegd: TekstSamenvoegenTeller.
Kies opnieuw het tabblad Tennis van het originele Voorbeeldbestand en voer het volgende uit:

  1. voeg op de plaats van kolom C TWEE nieuwe kolommen in
  2. in cel D3 tikken we het getal 2 in; in de nieuwe cel C3 plaatsen we de volgende functie:
    =TekstSamenvoegenTeller(E3;D3)
  3. kopieer de cellen C3 en D3 naar beneden
  4. in sommige cellen zal de waarde 2 gewijzigd moeten worden in 3

G-Info en de functie G_Info()

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

In mijn vorige artikel heb ik het gehad over de functies CEL() en INFO().
LET OP: in Excel en Access wordt een functie altijd gevolgd door 2 ronde haken. Bij de meeste functies worden tussen die haakjes argumenten meegegeven.

De naam van die tweede functie triggerde me om een eigen functie G-Info() te ontwikkelen. Maar wat zou die functie moeten doen?

Deze week kwam ik op een idee: wij moesten al nadenken wanneer we volgend voorjaar een korte vakantie zouden plannen; ergens met Hemelvaart of Pinksteren. Maar op welke data vallen die volgend jaar?

Ik wist nog wel, dat deze feestdagen afhankelijk zijn van Pasen, maar wanneer is het nu precies eerste Paasdag?

Pasen

Even “googlen” en al snel was ik een stap verder. Wikipedia:

Het concilie van Nicea bepaalde in 325 dat Pasen moet worden gevierd op de zondag na de eerste volle maan in de lente.

We hoeven dus alleen maar te weten wanneer de lente begint, wanneer het dan weer volle maan wordt en wat dan de eerstvolgende zondag is en we zijn klaar!!

PasenNog maar even verder op Google rondgeneusd en toen bleken er al diverse voorbeelden beschikbaar voor het bepalen van de datum van eerste Paasdag.

Dat is dus minder makkelijk dan ik gedacht had!
Met dank aan de bedenkers van deze routine gaan we dit gebruiken als bouwsteen voor onze nieuwe functie G-Info().

In het Voorbeeldbestand kunt u deze functie aan het werk zien:

Pasenwanneer in cel C2 een jaartal wordt ingetikt zal in cel D2 worden weergegeven, wanneer Pasen in dat jaar valt.

In cel D2 staat de formule =BepaalPasen(Jaar). Wanneer deze functie wordt ingetikt, zal in eerste instantie in D2 een getal komen. Door deze cel een datum-opmaak mee te geven zien we, dat Pasen in 2014 op 20 april is geweest. Ik heb de cel een aangepaste opmaak meegegeven: niet alleen de datum komt in cel D2, deze wordt vooraf gegaan door de tekst “Pasen valt op”.

LET OP: het Voorbeeldbestand bevat een viertal zelf-gedefinieerde namen, zodat de gebruikte formules veel beter leesbaar zijn. Cel C2 bijvoorbeeld heeft de naam Jaar gekregen.
Voor uitleg over het definiëren van namen, zie het artikel Namen ipv celverwijzingen.

G_Info()

De functie G_Info() vormt een uitbreiding op de functie BepaalPasen. Als parameter/argument wordt niet allen het jaar meegegeven, maar ook van welke feestdag de datum bepaald moet worden. Als derde parameter dient nog meegegeven te worden of de functie een datum moet retourneren (een getal dus) of een tekst.

FeestdagenAls voorbeeld: in C7 staat de formule =G_Info(Jaar;$B7;C$6).
Deze levert als resultaat de datum voor Pasen (cel B7) in het jaar 2014 en wel als echte datum (niet als tekst; cel C6)). Het resultaat is een getal, die een datumopmaak mee heeft gekregen.

PS1 let op de $-tekens in de formule; deze zijn zodanig gekozen, dat bij het kopiëren van de formule (naar rechts en naar beneden) de juiste verwijzingen blijven bestaan.
PS2 ik had de formule graag G-Info genoemd, maar het -streepje is in een functienaam niet toegestaan.

De formule in cel D7 geeft ook de Paasdatum weer, maar nu is het resultaat een tekst.

Naast de Paasdatum levert G_Info() ook de data voor Pinksteren, Carnaval, Hemelvaart, Kerstmis en Nieuwjaar (die laatste 2 liggen nogal voor de hand!). Zoals we hierna zullen zien, zijn uitbreidingen makkelijk door te voeren. Een verjaardag of huwelijksdag vergeten is er dus niet meer bij!

VBA

Eigen functies (in het Engels: User Definied Function, UDF) worden op dezelfde plaats gedefinieerd als waar macro’s worden geregistreerd: in de VBA-editor. Wanneer u daar nog nooit mee hebt gewerkt, bekijk dan het betreffende artikel.

Een korte uitleg van het programma:

PasenVBA1

Allereerst definiëren we de naam van de functie: G_Info.
NB zoals al eerder aangegeven is een – (streepje) niet toegestaan.
Daar achter, tussen haakjes, geven we aan welke parameters/argumenten/waarden aan de functie worden meegegeven: het Jaar, waarvoor de feestdagen bepaald moeten worden (moet een Integer=geheel getal zijn), voor welke Feestdag moet er informatie opgeleverd worden (een String=tekst) en een parameter TekstUitvoer (van het type Boolean; kan alleen de waarden WAAR of ONWAAR bevatten).
De groene tekst (achter het ‘-teken) is commentaar en wordt door het programma niet gebruikt.
In het vervolg van het programma willen we tusseninformatie ‘onthouden’ daarom definiëren we via Dim een variabele x.
PasenVBA2Het programma moet nu afhankelijk van de inhoud van de parameter Feestdag de datum van Pasen of van Pinksteren of van Carnaval etcetera genereren.
Binnen VBA is daar een handige constructie voor:
Select Case ….
     Case Voorwaarde1
Vervolg1
      Case Voorwaarde2
Vervolg2
……
End Select
Wanneer in onze functie aan de parameter Feestdag de waarde Pasen wordt meegegeven, moet het programma de Paasdatum gaan bepalen. Afhankelijk van de inhoud van TekstUitvoer (WAAR of ONWAAR) levert het programma een tekst of een datum/getal op. Dit resultaat wordt in de hulpvariabele x bewaard.
PasenVBA3De Select-optie wordt afgesloten met een Case Else: als er aan de parameter Feestdag een andere tekst wordt meegegeven dan waar in ons programma rekening mee is gehouden, zal dit programmagedeelte worden uitgevoerd.

Na afsluiting van de Select-constructie (dus na End Select) moeten we nog zorgen, dat de de functie in Excel het resultaat van het programma weergeeft. Aangezien we het resultaat in de variabele x hebben vastgelegd sluiten we de functie af met G_Info=x.

Het mag nu niet moeilijk meer zijn om de functie uit te breiden met voor u belangrijke feestdagen als verjaardag, huwelijksdag enzovoorts.

PS de twee in het Voorbeeldbestand gebruikte functies (BepaalPasen en G_Info) zijn zonder aanpassingen ook bruikbaar in Access. Kopieer de functies naar de VBA-omgeving (Module) van Access en u kunt ze direct in een query inzetten.

Kalender

In het voorbeeld is ook een jaaroverzicht opgenomen, waarin direct de diverse feestdagen worden geaccentueerd:
Feestdagen2In dag 1 van januari staat de formule =Datum(Jaar;Maand;Dag) en deze formule is naar rechts en naar beneden gekopieerd. Deze Excel-functie genereert afhankelijk van het jaar, maand- en dagnummer de betreffende datum (in Excel-formaat, dus een getal; zie ook het betreffende artikel over Datums).
Jaar is de naam van cel C2, Maand de naam van het bereik met de cijfers 1 t/m 12 voor de maandomschrijvingen en Dag is het bereik met de cijfers 1 t/m 31.
Alle betreffende cellen hebben bij cel-eigenschappen het datumformaat “ddd” meegekregen: dus alleen de weekdag wordt afgekort getoond.

De blanco cellen op het einde van diverse maanden, de kleuren voor de weekenden en de kleuren voor de feestdagen worden m.b.v.  Voorwaardelijke opmaak geregeld (via de menu-optie Start in het blok Stijlen).
Feestdagen3

Data (datums)

Excel,

Ik wilde het in dit artikel gaan hebben over een speciaal gebruik van data in Excel. Maar al snel kreeg ik het gevoel, dat het misschien niet duidelijk zou zijn wat ik met dat woord bedoel.

Gevormd (of misvormd) door een klassieke opleiding wil ik daarmee het meervoud van datum aanduiden, maar tegenwoordig wordt het woord data (al dan niet in combinatie met big) meestal in een andere context gebruikt, namelijk om aan te geven dat we het over gegevens hebben.
Even terzijde: hoe vaak wordt het woord data, dat toch echt een meervoudsvorm is, niet in het enkelvoud gebruikt? (“De data wordt door de volgende programma’s gebruikt.”; brrr).

Dus in dit artikel hanteer ik toch maar het meervoud datums.

 Datums

Datum1

Misschien zul je nu denken, dat hier de formule-freak bezig is geweest (trouwens wel een leuke uitdaging om een formule voor C2 te bedenken!).
Nee, in principe staat in alle cellen precies hetzelfde, namelijk het getal 41748:

Datum2

Tip: wanneer je wilt weten welke formules er gebruikt worden in een spreadsheet, schakel dan over naar het formule-scherm. Dit kan via Formules/Formules weergeven, maar veel gemakkelijker is natuurlijk de toetscombinatie Ctrl-T.

Blijkbaar is een datum voor Excel niets anders dan een getal (om precies te zijn geeft het getal aan hoeveel dagen we ondertussen verwijderd zijn van 1 jan 1900). Daarom is het ook zo makkelijk om te achterhalen hoeveel dagen er tussen twee data, sorry datums, zitten: trek de 2 datums/getallen gewoon van elkaar af! Wil je weten hoeveel dagen er sinds je geboorte zijn verstreken:

Datum3

Tip: wil je ergens in Excel de datum van vandaag hebben, gebruik dan de functie =VANDAAG(); denk aan de haakjes! Iedere dag zal hier dus iets anders komen; wil je de datum “hard” invoeren, gebruik dan ctrl-; (dus de Ctrl-toets inhouden en op de punt-komma drukken).

Maar weer terug naar het voorbeeld: als in alle cellen dezelfde waarde staat, waarom zien we dan iets heel anders?
Precies, we zien iets anders: het is een kwestie van opmaak!

Datum-opmaak

Om hier wat duidelijkheid te geven over de mogelijkheden gaan we het voorbeeld stap voor stap opbouwen:

  • in cel B2 tikken we in: 19-4
    Wat is Excel toch “slim”, we zien dat er 19-apr komt te staan: hij snapt dat we een datum bedoelen! Als je de inhoud van de cel bekijkt (19-04-2014) zie je dat er ook nog een jaar aan is toegevoegd (namelijk het huidige).
    Het is nog vreemder: als je op B2 klikt, zie je dus 19-04-2014 staan, maar dat is niet de echte Excel-waarde, dat is  41748 (zie hiervoor).
  • we gaan de opmaak van cel B2 veranderen:
    Klik op cel B2 en kies de Celeigenschappen (rechtsklikken of Ctrl-1),
    Kies als Categorie: Datum.
    Kies dan als Type: 14-03-01
    Klik OK en je zult in B2 onze notatie zien.
  • Nog even terug naar de Celeigenschappen (Ctrl-1 dus):
    Kies als Categorie: Aangepast.
    Bij Type zie je nu: d-mm-jj
    Hier zit de crux van dit artikel: met de combinatie van de letters d, m en j kun je heel veel opmaakkwesties oplossen!
  • In cel C2 komt dezelfde waarde door in te tikken: =B2
    Celeigenschappen (Ctrl-1), Categorie: Aangepast
    Wis alles bij Type (wat staat er nu bij Voorbeeld?) en tik 1 d in, kijk naar Voorbeeld.
    Nog een d (niks veranderd?), nog een d (hé, wat is dat nu?) en nog een d.
  • We krijgen de smaak te pakken (?): in cel D2 komt =C2
    Ctrl-1, Categorie: Aangepast, alles bij Type wissen.
    1 m, nog een m, nog een m, nog een m, nog een m, nog een m, nog …..
  • cel E2 moet nu lukken.

Tip: op deze manier zijn allerlei combinaties in te voeren
(bijvoorbeeld dddd, “de” d”e” mmmm “van het jaar” jjjj).

Voorbeeldbestand

Kijk ook in het voorbeeldbestand en schroom niet om allerlei lettercombinaties uit te proberen.

Let op: we hebben alleen de opmaak van de cellen aangepast! Zoeken (Ctrl-F) naar bijvoorbeeld zaterdag zal in eerste instantie niets opleveren; pas als je in het zoekscherm aangeeft dat hij naar Waarden moet zoeken (niet Formules) zal Excel iets kunnen vinden. Ook sorteren of voorwaardelijke opmaak (om bijvoorbeeld het weekend een andere kleur te geven, zullen niet (direct) werken (in het downloadbestand is een voorbeeld van voorwaardelijke opmaak opgenomen).