Tagarchief: Gegevensvalidatie

Unieke waarden

Er bestaan nogal wat situaties, waarbij je wilt weten of items allemaal verschillend zijn of niet.
En, als ze niet allemaal verschillend zijn, welke unieke exemplaren komen er dan voor?

In Excel kennen we een dergelijk probleem ook: welke unieke waarden komen er in een rij of kolom voor?

Deze keer zullen we enkele methoden de revue laten passeren; van simpele tot complexe, van opmaak tot selectie.

Voorwaardelijke opmaak

In het Voorbeeldbestand heb ik op het tabblad Data1 een tabel opgenomen met in de kolommen oa de afdelingen en het soort bedrag.

Alle cellen in die kolommen hebben een voorwaardelijke opmaak gekregen:

  1. selecteer cel C3
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak
  3. klik op de optie Nieuwe regel
  4. in het vervolgscherm kiezen we als Type: Een formule gebruiken
  5. en de formule wordt:
    =AANTAL.ALS(C$3:C3;C3)=1
    ofwel: als in het bereik C$3:C3 de waarde uit cel C3 1 keer voor komt mag de cel een opmaak krijgen.
    Dat is natuurlijk altijd waar!
    Maar wat gebeurt er als we deze voorwaardelijke opmaak ook ‘loslaten’ op een cel lager? Excel interpreteert de formule dan als =AANTAL.ALS(C$3:C4;C4)=1 en kijkt of de waarde uit C4 slechts 1 keer voor komt in het bereik C$3:C4.
    Ook wanneer deze formule in kolom D wordt toegepast is de controle precies wat we zoeken.
    NB om de formule ook in andere kolommen te kunnen gebruiken is alleen de 3 absoluut gemaakt als begin van het bereik (vandaar het $-teken vóór de 3) en is niet het meer voor de hand liggende $C$3 gebruikt.
  6. voeg nog de gewenste opmaak toe (hier is voor een licht-groene opvulling van de cel gekozen)
  7. klik op OK
  8. zorg dat de opmaak op alle relevante cellen van kolom C en D wordt toegepast:
    * kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak
    klik op de optie Regels beheren
    * vul bij Van Toepassing op het gewenste bereik in (in dit geval $C$3:$D$32)

NB1 het overzicht is een Excel-tabel. Daarom zal Excel, wanneer er een regel aan wordt toegevoegd, ook de Voorwaardelijke opmaak direct meenemen; het bereik wordt automatisch aangepast.

NB2 welke unieke waarden er in een tabel-kolom zitten ziet u ook direct door op het ‘vinkje’ achter een kolomkop te klikken.

NB3 het vorige ‘recept’ kan met 1 stap ingekort worden: selecteer in de eerste stap niet één cel, maar het totale bereik waar de opmaak voor moet gelden; stap 8 kan dan vervallen.

Via draaitabel

De vorige oplossing was een optische methode om unieke waarden te vinden; bij een grote tabel niet echt handig.

Het maken van een draaitabel is echter een simpele en doeltreffende manier om snel de unieke waarden in een kolom te vinden:

  1. selecteer een cel in de tabel met gegevens, bijvoorbeeld B2
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. in het vervolgscherm is alles al goed ingevuld; de bron voor de draaitabel is de totale Excel-tabel (met de naam tblData1)
  4. klik OK
  5. sleep het veld Afd naar de Rijlabels en daar zijn alle unieke afdelingsnamen!
  6. hetzelfde kunt ook doen voor de Soort, maar u kunt beter de vorige draaitabel uitbreiden: sleep Soort naar Kolomlabels.
    Sleep ook nog Bedrag naar het Waarde-gebied en u hebt meteen een overzicht van de verdeling van de bedragen naar afdeling en soort!

    Zie ook het tabblad Data1 in het Voorbeeldbestand.

Even een uitstapje: wilt u weten wat het totaal van Srt1 is dan kunt u ook de volgende formule gebruiken: =SOM.ALS(tblData1[Soort];”Srt1″;tblData1[Bedrag]) maar beter is het volgende:

  1. plaats in een bepaalde cel de waarde Srt1, bijvoorbeeld in G14 (zie het tabblad Data1 in het Voorbeeldbestand)
  2. in de cel daarnaast gaan we een formule opbouwen:
    tik in =som.als(
  3. wijs met de cursor de bovenkant van de kolom Afd aan (de cursor wordt dan een zwarte pijl naar beneden) en klik.
    De formule wordt dan aangevuld met tblData1[Soort] ofwel alle cellen in de kolom Soort van de Excel-tabel tblData1.
  4. tik in ; (de punt-komma) en klik op cel G14 en plaats weer een ;
  5. als laatste voegen we de kolom Bedrag toe, die onder voorwaarden gesommeerd moet worden; dat gaat op dezelfde manier als in stap 3.
  6. nog een haakje sluiten en Enter

Door in G14 een andere soort in te typen, krijgt u daarvan het totaal.

Maar wat als er aan 2 (of meer voorwaarden moet worden voldaan?

U moet dan de functie SOMMEN.ALS gebruiken.
Deze werkt net iets anders: eerst geeft u het bereik op, waarvan u de som wilt bepalen, dan het eerste criterium-bereik met daarachter het criterium zelf, daarna een nieuw criterium-bereik met criterium etc.

Gegevensvalidatie

Maar in G14 kunnen nu willekeurige teksten worden ingevoerd. Dat is natuurlijk niet de bedoeling; we willen alleen bestaande soorten kunnen opgeven.

Dit gaat eenvoudig met gegevens-validatie:

  1. plaats de cursor in de cel die u wilt valideren (bijvoorbeeld cel G19 zoals in het tabblad Data1)
  2. kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  3. kies bij Toestaan: de optie Lijst
  4. en bij Bron: kiest u de kolom Soort uit de Excel-tabel op de manier zoals hierboven aangegeven.
    NB Excel vertaalt de kolom direct naar daadwerkelijke cellen, maar als de tabel groter (of kleiner wordt) verandert dit bereik mee.
  5. klik op OK.

Oeps, het resultaat is niet helemaal wat we hoopten. In de keuzelijst staan alle voorkomens uit de kolom Soort, we willen natuurlijk alleen unieke waarden hebben!

Unieke waarden via filtering

Om een lijstje te maken van unieke waarden (maar u wilt geen draaitabel gebruiken) dan kent Excel nog een andere aanpak:

  1. kies in de menutab Gegevens in het blok Sorteren en filteren de optie Geavanceerd
  2. in het Uitgebreid filter kiezen we als Actie de optie Kopiëren naar andere locatie
  3. voor het Lijstbereik selecteert u alle gegevens uit de kolom Afd van de tabel, INCLUSIEF de kop
  4. bij Kopiëren naar vult u de cel in, waar de unieke waarden moeten komen (in het voorbeeld heeft deze cel de naam AfdKop gekregen)
  5. vergeet niet het vinkje te plaatsen bij Unieke records!

In het tabblad Data1 van het Voorbeeldbestand ziet u het resultaat van deze actie.

Ook voor de kolom Soort zijn de unieke waarden opgehaald.

NB1 Wat direct opvalt is dat er geen (automatische) sortering plaats vindt. De volgorde is gelijk aan de volgorde in de bron.

NB2 als er regels aan de bron-tabel worden toegevoegd dan moeten de ophaal-acties opnieuw worden uitgevoerd.
Excel ‘onthoudt’ de gegevens van de laatste actie. Dit is ook te zien in het overzicht Namen beheren in de menutab Formules.

LET OP1 de locatie, waar de unieke waarden moeten komen, moet op hetzelfde tabblad staan als waar de brongegevens zich bevinden.

LET OP2 vergeet niet om bij het Uitgebreid filter ook de kop van de kolom mee te nemen, anders krijgt u de eerste waarde dubbel. Dit is nergens in de Microsoft-documentatie terug te vinden!

Gegevensvalidatie

Op basis van deze unieke waarden kunnen nu makkelijk Gegevensvalidaties worden gemaakt.
Maar om het geheel dynamisch te houden (als er nieuwe unieke waarden bijkomen moet dit automatisch worden meegenomen bij de gegevensvalidatie) wordt als bron-lijst niet  een hard bereik genomen (bijvoorbeeld R3:R5).

Nee, we creëren een dynamisch bereik door een naam toe te voegen:

  1. kies in de menutab Formules in het blok Gedefinieerde namen de optie Naam definiëren
  2. vul een naam in (bijvoorbeeld AfdUniek)
  3. en plaats de formule
    =VERSCHUIVING(AfdKop;1;0;AANTALARG(Data1!$R:$R)-1)
    in het vak Verwijst naar.

Bij gegevensvalidatie (zie cel G20 in het tabblad Data1 van het Voorbeeldbestand) gebruikt u als Bron de naam AfdUniek (vergeet niet het =-teken daar voor te zetten!).

In de cellen G21 en H21 is op een vergelijkbare manier gegevensvalidatie toegepast, maar daarbij zijn als bron voor de verschuiving 2 simpele draaitabellen gebruikt.

LET OP worden er regels aan de oorspronkelijke tabel toegevoegd, vergeet dan niet de draaitabellen te vernieuwen anders bevatten de gegevensvalidaties niet de meest recente gegevens.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Hoog-Laag-Slot-grafiek

Binnenkort (nou ja, over een paar maanden) hebben we weer verkiezingen. In de aanloop daar naartoe zien we in de media steeds vaker de resultaten van diverse peilingen.

Op de site peilingwijzer.tomlouwerse.nl is het meest recente resultaat van een wetenschappelijk onderbouwde combinatie van 5 peilers (nee Maurice, geen pijlers!) te vinden.

Een grafiek op die site laat van 13 politieke partijen het verloop van de peilingen in de tijd zien; naast het gemiddelde van de 5 peilers zien we in de grafiek ook een 95%-onzekerheidsmarge (het licht gekleurde gebied).

Hieronder laat ik zien, hoe je in Excel een dergelijke grafiek kunt maken; we gebruiken daar een hoog-laag-slot-grafiek voor.
Daarbij moet wel aangetekend worden, dat de grafiek op de site van Tom Louwerse veel meer interactieve elementen bevat!

Basisgegevens

Zoals voor iedere grafiek hebben we basisgegevens nodig; deze zijn in dit geval op te halen vanaf de download-pagina van de Peilingwijzer.

In het Voorbeeldbestand heb ik op het tabblad Peilingwijzer deze gegevens geplaatst (op dit moment van 12 september 2012 tot en met 27 november 2016); zoals in eerdere voorbeelden ‘gieten’ we deze in de vorm van een tabel (Invoegen/Tabel, kopregels aanvinken) en geven die tabel de naam tblpeilingWijzer.

NB1 komen er nieuwe peiling-data beschikbaar, voeg deze dan onderaan de tabel als nieuwe regels toe.

NB2 komen er nieuwe partijen in het overzicht bij, voeg deze dan rechts aan de tabel als nieuwe kolommen toe.

Parameters

Hierna gaan we een grafiek maken van de peiling-resultaten. Hierbij kan echter maar één partij tegelijkertijd worden weergegeven. Om de invoer daarvan straks te vergemakkelijken leggen we de keuzemogelijkheden in een aparte tabel vast (zie tabblad Param in het Voorbeeldbestand).

NB wordt er in de peilingen een extra politieke partij meegenomen, dan moet deze onderaan toegevoegd worden; aangezien het ook hier een tabel betreft, zal Excel de nieuwe regel automatisch in tblPartijen meenemen.

Het is ook wel handig om te weten wat de eerste en laatste datum is, die in het peilingoverzicht voorkomt.
In cel E2 van het tabblad Param uit het Voorbeeldbestand bepalen we de eerste/kleinste datum door het minimum van die kolom op te zoeken.
Normaal gesproken zou dat kunnen met de formule =MIN(tblPeilingWijzer[Datum]), maar helaas levert dat de waarde 0 op. Dat komt omdat in de datum-kolom geen echte datum staat maar een tekst, dus het wordt wat ingewikkelder:
{=MIN(DATUM(
LINKS(tblPeilingWijzer[Datum];4);
DEEL(tblPeilingWijzer[Datum];6;2);
RECHTS(tblPeilingWijzer[Datum];2)))}

Met behulp van de functie LINKS zoeken we het jaar op, DEEL haalt 2 tekens op vanaf positie 6 (de maand dus) en RECHTS levert de dag. Deze 3 resultaten geven we door aan de functie DATUM, die er een datum van maakt. De functie MIN zoekt dan de kleinste datum op.

LET OP de 3 tekst-functies kijken eigenlijk alleen naar de datum uit de corresponderende rij (de 2e dus). Door na het intikken van de formule niet op Enter te drukken maar op Ctrl-Shift-Enter wordt het een zogenaamde array- of matrix-formule en worden de 3 tekst-functies op alle datums ‘losgelaten’. Vaak wordt dit ook CSE-invoer genoemd; Excel plaatst automatisch accolades om de formule (niet handmatig intypen!).

De formule in E3 mag dan geen verrassingen meer bevatten.

Voor het gemak hebben we de cellen E2 en E3 een naam gegeven MinDatum, resp. MaxDatum.

Grafiek met onzekerheidsmarge

Voordat we een dergelijke grafiek kunnen maken moeten we eerst een methode hebben om de gegevens van één partij uit de basis te destilleren.

Cel C3 (met de naam invPartij) in het tabblad Ovz van het Voorbeeldbestand gebruiken we als keuzevak:

  1. kies in de menutab Gegevens de optie Gegevensvalidatie
  2. in het pop-up-scherm kiezen we bij Toestaan: de optie Lijst
  3. bij Bron: zouden we alle keuzemogelijkheden kunnen intikken gescheiden door een ; (punt-komma), dus bijvoorbeeld VVD;PvdA;GL etc.
    Maar dat is niet zo handig. Die lijst kunnen we ook uit het tabblad Param halen: kolom Partijen in de tabel tblPartijen.
    Helaas kan gegevensvalidatie niet goed omgaan met de nieuwere tabellen (geïntroduceerd in versie 2007), dus de formule =tblPartijen([Partijen]) werkt niet, maar wel als we de functie INDIRECT gebruiken (zie ook het artikel Tabellen (deel 2); denk aan de aanhalingstekens!!).
  4. Eventueel nog een Invoerbericht en/of Foutmelding toevoegen (zie Voorbeeldbestand) en we zijn klaar: klik op OK.

De voorbereidingen zijn klaar, nu nog de gegevens ophalen van de gekozen partij:

  1. in rij 5 van het tabblad Ovz van het Voorbeeldbestand staat een kopregel
  2. vanaf B6 naar beneden staan alle datums, waarvoor er peilingen zijn
  3. in cel C6 moet de eerste peiling van de gekozen partij komen:
    =INDEX(
    INDIRECT(“tblPeilingWijzer[“&invPartij&”]”);
    VERGELIJKEN($B6;tblPeilingWijzer[Datum];0))
    Weet u niet (meer) hoe de functie INDEX werkt? Kijk in het artikel Zoeken: Index en Vergelijken; daar vindt u ook een truc hoe u de de functie makkelijk implementeert.
    Weer gebruiken we INDIRECT om de kolom, waarin gezocht moet worden, afhankelijk te maken van de cel C3 (met de naam invPartij).
  4. de formules in D6 en E6 zijn vergelijkbaar, alleen worden daar de partij-gegevens uit de low- en high-kolom opgehaald.
  5. de drie formules uit C6:E6 worden naar beneden gekopieerd, zodat bij iedere datum de gegevens tevoorschijn komen.
  6. via de menutab Invoegen en de optie Tabel maken we van dit overzicht een tabel (met de naam tblGrafBasis). Wanneer er nieuwe peilingen beschikbaar zijn is het dan voldoende om onderaan de datum toe te voegen. Alle formules worden dan automatisch door Excel ingevuld.

En nu de grafiek:

  1. plaats de cursor ergens in tblGrafBasis
  2. in de menutab Invoegen kiest u in het blok Grafieken de optie Overige grafieken.
    In het uitklapmenu nemen we de eerste grafieksoort in het blokje Hoog/Laag/Slot.
    Dit soort grafieken wordt vaak in de financiële wereld gebruikt om een overzicht van de dagkoersen weer te geven: per dag ziet u dan de hoogste, laagste en slotkoers.
    NB Excel beoordeelt automatisch in welke kolom de hoge, lage of slot-waarde (of in ons geval het gemiddelde) staat.
  3. Nog even wat opmaak regelen:
    * kies in de nieuwe menutab Hulpmiddelen voor grafieken het tabblad Indeling
    * kies in het blokje Huidige selectie de Reeks Gemiddeld 
    * daarna de optie Selectie opmaken
    * en kies bij Lijnkleur een gewenste kleur
    * kies in het blokje Huidige selectie de Hoog/laag-lijnen
    * daarna de optie Selectie opmaken
    * en kies bij Lijnkleur dezelfde kleur, maar met een Transparantie van 75%
    * kies in het blokje Huidige selectie de Reeks Hoog
    * daarna de optie Selectie opmaken
    * en kies bij Markeringsopties Geen
    * Grafiektitel laten verwijzen naar cel E3
    * aan de linker-as een Titel toevoegen, de notatie wijzigen in een percentage zonder decimalen en zorgen dat er het bereik altijd van 0% tot 30% loopt.

Wijzig cel C3 en bekijk het resultaat!


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Tabellen (deel 2)

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.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Elf-proef

bsnBSN: iedereen heeft er een, maar weinig mensen zullen dit nummer uit hun hoofd kennen.
Bij het invullen worden dan ook vaak fouten gemaakt. Iedere instantie wil daarom graag geautomatiseerd checken of het ingevoerde getal juist is.

De controle die je op een BSN kunt uitvoeren is een variant van de elf-proef. Vóór het IBAN-tijdperk werd deze gebruikt om bankrekeningnummers te controleren.

Hoewel de toepassing van de elf-proef voor bankrekeningnummers niet meer geldt, komt deze in de praktijk toch nog regelmatig voor. In Excel is die in te bouwen, maar vergt nogal wat hulpkolommen; de elf-proef is dan ook bij uitstek geschikt om via VBA in een eigen formule te verwerken.
Hieronder (na uitleg van de elf-proef) dan ook voorbeelden in Excel en met behulp van VBA (deze functie is dan ook zonder meer bruikbaar in Access).
Daarna behandelen we een alternatieve functie die ook geschikt is voor de controle van een BSN.

Elf-proef

Wikipedia: “De elfproef (11-proef) is een test die in het Nederlandse elektronische betalingsverkeer werd uitgevoerd op negen- en tiencijferige Nederlandse bankrekeningnummers, voor de invoering van het IBAN, om te controleren of het nummer een geldig rekeningnummer kan zijn. Varianten van de elfproef die gebruikmaken van een controlecijfer, worden toegepast bij andere belangrijke nummers, zoals het burgerservicenummer en het betalingskenmerk op een acceptgiro.

Het laatste cijfer van het rekeningnummer wordt met 1 vermenigvuldigd, het voorlaatste met 2, het op twee na laatste met 3, enzovoorts. De producten worden bij elkaar opgeteld en vervolgens wordt de som gedeeld door 11. Het resultaat van deze deling moet voor een geldig rekeningnummer een geheel getal zijn.

Duidelijk toch: ieder cijfer van het rekeningnummer moeten we vermenigvuldigen met zijn gewicht, die producten optellen, de som moet dan deelbaar zijn door 11 oftewel de rest (na deling; bijvoorbeeld bij het delen van 24 door 11 is de rest 2) moet dan nul zijn.

Excel-voorbeeld 1

Ik bedacht me dat ik ooit eens voor een toepassing de functie Elfproef had gemaakt en die later ook bij cursussen gebruikt had. Even zoeken en ja hoor gevonden.

Maar voordat we die gaan gebruiken kijken we eerst in Excel hoe de elfproef werkt: ziet tabblad Vb1 van het Voorbeeldbestand.
Elfproef1Van het banknummer in kolom A wordt telkens, van achter af, een cijfer geïsoleerd (kolommen D, F, H etcetera; bekijk ook de formules in de kolommen E, G enzovoort) en die cijfers worden in kolom C met hun gewicht vermenigvuldigd. In kolom C worden die producten ook nog opgeteld en wordt de Rest bepaald bij deling door 11. Als daar 0 uitkomt dan is het antwoord ok, anders Geen juist nr.

Elfproef2Deze elfproef is bedoeld voor bankrekeningnummers bestaande uit 9 of 10 cijfers. Kolom A bevat dan ook een input-controle, zodat we in de berekeningen niet allemaal controles hoeven in te bouwen.
De input-controle is geïmplementeerd met behulp van Gegevens-validatie: kies de menutab Gegevens en dan in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie. De drie tabbladen zijn als volgt ingevuld:

Elfproef3

Elfproef4Elfproef5

VBA-voorbeeld 1

Om al die hulpkolommen te vermijden is in kolom B gebruik gemaakt van een eigen functie ElfProef1, die dezelfde resultaten oplevert:
Elfproef6In het Voorbeeldbestand is de functie te vinden door de VBA-editor te starten (Alt-F11 of  via het tabblad Ontwikkelaars of door onderaan rechts te klikken op de naam van het tabblad Vb1 en Module1 te kiezen).

Enige uitleg is op zijn plaats:

  1. In Excel wordt de functie aangeroepen met als parameter (de verwijzing tussen de haakjes) een bankrekeningnummer (in het voorbeeld door te verwijzen naar de cellen in kolom A). In de definitie van de Function ElfProef1 staat dan ook dat er een parameter getal wordt doorgegeven van het type Long (een groot geheel getal). Consequentie hiervan is wel, dat als de functie in Excel verwijst naar een tekst (bijvoorbeeld naar cel B1) de functie niets doet en als resultaat #WAARDE! teruggeeft.
    Achter de functie staat As String: het resultaat van de functie is een tekst (string).
  2. ‘groene’ teksten is voor VBA commentaar; hebben verder geen functie. Invoeren door een enkele aanhalingsteken in te tikken.
  3. met Dim geven we aan welke variabelen we in het programma/de functie gaan gebruiken
  4. om hierna makkelijk de cijfers één voor één te kunnen ‘losknippen’ maken we met behulp van de VBA-functie Str van het getal een tekst (string)
  5. door middel van de For-loop wordt de berekening uitgevoerd: de eerste keer wordt i gelijk aan 1, via Next wordt die 2 enzovoort net zolang tot i groter is dan de lengte van de tekst; dan gaat het programma verder met de opdracht na Next
  6.  in de loop wordt de waarde (Val) bepaald van de diverse cijfers en die waarde wordt met i vermenigvuldigd en bij het (reeds bestaande) lngResult opgeteld.
    Omdat het laatste cijfer het gewicht 1 krijgt etc moeten we met het losknippen achteraan beginnen: dus we nemen het gedeelte (Mid) van de tekst dat start op positie lengte + 1  – i; de laatste parameter van de Mid-functie (het getal 1) geeft aan dat we 1 positie uit de tekst knippen.
  7. we gaan er van uit, dat het bankrekeningnummer voldoet, dus geven standaard als output voor de functie ok mee
  8. maar we moeten nog testen wat de deling door 11 oplevert: hier delen we de som door 11, nemen dan het gehele deel daarvan (de functie Int), vermenigvuldigen dan weer met 11 en kijken  of dit weer gelijk is aan de oorspronkelijke som. Zo niet (<> betekent niet gelijk) dan wordt de output van de functie ONGELDIG.

NB de kolommen B en C zijn van een voorwaardelijke opmaak voorzien, zodat snel duidelijk is wanneer een banknummer aan de elfproef voldoet.

Excel-voorbeeld 2

In het Voorbeeldbestand is een tweede voorbeeld opgenomen (zie tabblad Vb2).
Elfproef7We maken van het bankrekeningnummer uit cel A3 (met dezelfde gegevensvalidatie als in voorbeeld 1) eerst een tekst van 10 tekens: we zetten daartoe vóór het rekeningnummer eerst 10 nullen (mbv het &-teken) en nemen dan de 10 rechtse tekens daarvan (cel B3).

NB in cel B4 staat een alternatief met de functie Herhaling, waardoor het duidelijker is dat we 10 keer de nul er voor zetten.

In de kolommen ontleden we de tekst weer in 10 losse gedeelten (van achter naar voren); er wordt een 0 bij opgeteld om Excel automatisch de tekst te laten omzetten naar een getal. In rij 4 wordt de vermenigvuldiging uitgevoerd, waarna in cel D4 de Som daarvan wordt bepaald. In cel D3 bepalen we dan of een deling door 11 als rest 0 oplevert.

In cel D5 wordt de vermenigvuldiging, optelling en check in één formule uitgevoerd:

=ALS(REST(SOMPRODUCT(E2:N2;E3:N3);11)=0;”ok”;”ONGELDIG”)

Voor uitleg over de functie SOMPRODUCT zie mijn vorige artikel.

VBA-voorbeeld 2

In de VBA-editor is ook een functie ElfProef2 opgenomen (alles met isBSN even overslaan).

De verschillen met de vorige functie:

  1. de input-parameter getal is gedeclareerd als String (tekst)
  2. een voordeel daarvan is, dat we de functie exacter kunnen laten aangeven wanneer de invoer niet aan onze eisen voldoet:
    If getal = “” Or IsNull(getal) Or Not IsNumeric(getal) Then
            ElfProef2 = “Geen juiste invoer”
            Exit Function
        End If
    Dus als de invoer leeg is (op 2 manieren) of als het geen numerieke waarde (getal) is dan wordt de output van de functie Geen juiste invoer en wordt deze afgebroken.
    NB deze test kan uitgebreid en nog concreter gemaakt worden afhankelijk van de behoefte.
  3. in de functie vertalen we het getal naar een tekst van 10 tekens (vergelijkbaar met wat we in Excel hebben gedaan)
  4. de constructie lngResult Mod 11 is vergelijkbaar met de functie Rest in Excel

BSN

Zoals hiervoor aangegeven wordt voor de controle van het BSN een vorm van de elfproef wordt gebruikt: het enige verschil met de standaard is, dat het laatste cijfer niet met +1 maar met -1 wordt vermenigvuldigd.

Bij de functie ElfProef2 is daar al rekening mee gehouden:

  1. optioneel (dus niet verplicht) kan aan de functie een tweede parameter isBSN worden meegegeven (Optional isBSN As Boolean = False). Een variabele van het type Boolean kan alleen de waarde WAAR of ONWAAR (True of False) hebben;  als de parameter niet wordt meegegeven dan krijgt deze de waarde ONWAAR/False.
  2. dmv de regel
    If    i = 1   And    isBSN     Then    hulp   =   – hulp
    zorgen we er voor, dat als i gelijk is aan 1 EN isBSN waar is dat dan het tegenovergestelde van hulp wordt opgeteld

Elfproef8In het tabblad BSN van het Voorbeeldbestand zien we dat het standaard-gebruik van de functie ElfProef2 in cel C3 aangeeft dat we te maken zouden hebben met een ongeldig BSN.

Gebruiken we echter de vorm =ElfProef2(B3;WAAR), zoals in cel D3, dan levert dit als resultaat ok op.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Weekdagen en weeknummers

Kalender-2016-LandscapeEén van de eerste artikelen op de site van G-Info ging over datums en de mogelijkheden van de diverse opmaak-opties (klik hier voor het betreffende artikel).

Vorige week kreeg ik een vraag over weekdagen en weeknummers in Access. Toen bleek, dat het probleem niet met opmaak was op te lossen, viel het me weer op, hoe ingewikkeld het kan zijn om dit soort vraagstukken met behulp van formules te tackelen.
Ook de (soms) grote verschillen tussen Excel en Access maken het er niet makkelijker op(hoezo, allebei een onderdeel van Microsoft Office!) .

Daarom maar eens wat mogelijkheden (en onmogelijkheden) op een rijtje gezet.

Opmaak in Excel

Zoals gezegd heb ik daar in een eerder artikel al eens over geschreven.
KalenderIn het Voorbeeldbestand heb ik een overzicht gemaakt met de dagen van de laatste week van 2016 en de eerste 2 van 2016.
De datums staan in kolom F; in de kolommen G t/m J staan verwijzingen naar de dagen in kolom F, maar met behulp van celopmaak (Ctrl-1 indrukken) is de lay-out van de cellen veranderd.

Voordeel hiervan is, dat makkelijk en snel de opmaak aangepast kan worden aan je wensen.
Nadeel is echter, dat je niet rechtstreeks formules of filters op deze cellen kunt los laten; de inhoud van die cellen blijft een datum (intern voor Excel een getal). Ook is het niet mogelijk om op deze cellen een Voorwaardelijke opmaak toe te passen.

NB wanneer je een Filter aanbrengt op het overzicht zul je zien, dat de filtermogelijkheden voor de kolommen F t/m J allemaal hetzelfde zijn: Excel ziet, dat het datums betreft en geeft daarom de mogelijkheden om te filteren op jaar, maand of dag. Het is NIET mogelijk om te filteren op bijvoorbeeld maandag.

Kalender2Experimenteer met de aangepaste opmaak via Celeigenschappen (Ctrl-1). Gebruik combinaties van de letters d, m en j; 1 letter, 2 letters etc. Het resultaat zie je direct in het vak Voorbeeld.

Datum-formules in Excel

Wil je kunnen rekenen, filteren, sorteren met de diverse onderdelen van een datum dan voldoet de opmaak niet meer. Je zult dan formules moeten gebruiken, waarmee de datum ‘ontleed’ wordt.

In het Voorbeeldbestand zijn in de kolommen K, L en M formules gebruikt om het jaar, de maand en de dag van een datum op te halen; in cel K4 bijvoorbeeld staat de formule =JAAR(F4).

Niets bijzonders, handig, recht-toe-recht-aan.
Willen we echter de weekdag ophalen (is het een maandag, dinsdag?) , dan blijkt er in Excel geen functie te zijn, die dat rechtsreeks oplevert; wel kunnen we het volgnummer van de dag in de week opvragen met de functie Weekdag.
Daar zit echter een addertje onder het gras: er is in de wereld geen overeenstemming over welk nummer bijvoorbeeld de maandag moet krijgen: 1 zoals in Europa gebruikelijk is of moet het 2 zijn (een nieuwe week begint op zondag)?
Aan de functie Weekdag moet dus altijd kenbaar worden gemaakt wat de eerste dag van de week is (als je dat niet meegeeft, dan wordt de standaard genomen en krijgt zondag volgnummer 1).
In cel N4 staat de formule =WEEKDAG(F4;KzWkDag)

Kalender3

Er wordt dus gezocht naar de weekdag, die hoort bij de datum in cel F4, waarbij als Type_getal de waarde 2 wordt meegegeven (maandag krijgt volgnummer 1).
Type_getal verwijst naar de cel met de naam KzWkDag (cel C3). Via Gegevensvalidatie kunnen daar alleen de voor deze functie toegestane waarden 1, 2, 3 en 11 t/m 17 ingevoerd worden.
Met VERT.ZOEKEN is in cel D3 te zien wat een bepaalde waarde inhoudt. Op het tabblad Parameters is de bron voor de Gegevensvalidatie en het verticaal zoeken te vinden in de kolommen B en C.

NB met een formule kunnen we dus het volgnummer van een weekdag afleiden, willen we echter een omschrijving dan wordt het iets ingewikkelder.
In cel Q4 staat een voorbeeld:
=KIEZEN(N4;”ma”;”di”;”wo”;”do”;”vr”;”za”;”zo”)
Let wel op: de volgorde van de dagen is afhankelijk van het Type_getal, dat in cel N4 is gebruikt!

Met opmaak is het niet mogelijk om in Excel het weeknummer te zien; wel is daar een functie voor. In het Voorbeeldbestand wordt die in kolom O gebruikt.
In cel O4 komt dan de formule =WEEKNUMMER(F4;KzWkNr)

Ook deze functie kent een 2e paramater (het Type_resultaat). Welke keuze hier gemaakt moet worden is nog iets ingewikkelder dan bij Weekdag. Want niet alleen is het van belang op welke dag een nieuwe week begint, ook moeten we aangeven wanneer we met weeknummer 1 beginnen.
Voor dat laatste onderscheidt Excel 2 systemen: bij Systeem 1 is Nieuwjaar het begin van week 1, bij Systeem 2 (het Europese systeem) begint week 1 met die week, waarin de eerste donderdag valt (en de week begint dan automatisch op maandag).

Om Systeem 2 te kiezen moet als 2e paramater voor Weeknummer het getal 21 meegegeven worden.

Opmaak in Access

Net als in Excel kunnen we in Access de datum “in stukken knippen” door een opmaak daaraan mee te geven.
In een query, die gebaseerd is op een tabel met een veld Datum, kunnen we bijvoorbeeld een nieuw veld toevoegen (met de naam JrF) om het jaar af te leiden:
JrF: Format([datum];”yyyy”)

LET OP Access verwacht Engelse functienamen en ook de Engelse afkorting voor het jaar.

We kunnen ook minder letters aan de functie meegeven, maar het resultaat is soms iets anders dan in Excel:
y: dit levert het volgnummer van de dag in het jaar
yy: het jaar in 2 cijfers
yyy: het jaar in 2 cijfers en direct daarachter het volgnummer van de dag in het jaar (dus een combinatie van yy en y)

Voor het maandnummer kan een vergelijkbare formule gebruikt worden:
MndF: Format([datum];”m”)
mm: het maandnummer altijd in 2 cijfers
mmm: de afkorting voor de maand (bijvoorbeeld jan)
mmmm: een volledige maand-aanduiding (bijvoorbeeld januari)

Voor de dag van de maand gebruiken we dan:
DagF: Format([datum];”d”)
dd: het dagnummer altijd in 2 cijfers
ddd: de afkorting voor de dag van de week (bijvoorbeeld ma)
dddd: een volledige dag-aanduiding (bijvoorbeeld maandag)

Binnen Access is het op deze manier ook mogelijk om het kwartaalnummer automatisch te genereren:
KwF: Format([datum];”q”)

Ook kunnen we de w als parameter gebruiken, maar ook nu dienen we nog extra parameters mee te geven:
WkF: Format([datum];”w”;2)
Deze formule levert het volgnummer van de dag in de week op. Maar net als in Excel moeten we dan wel opgeven op welke dag een nieuwe week begint. Geven we als 3e parameter een 0 (nul) mee, dan worden systeeminstellingen gebruikt, 1 dan begint de week op zondag etc.

Als laatste nog:
Wk2F: Format([datum];”ww”;2;2)
Dit levert het weeknummer in het jaar; net als in Excel is het wel van belang welk systeem daarbij gebruikt moet worden.
Als de 4e parameter 0 (nul) is dan worden de systeeminstellingen gebruikt, 1 dan begint de eerste week op Nieuwjaar, 2 dan is de eerste week afhankelijk van de eerste donderdag en 3 dan is de eerste week die week, die volledig in het nieuwe jaar ligt.

LET OP de functie Format retourneert een tekst; wil je rekenen met de uitkomst hiervan neem dan de waarde van het resultaat: Val(Format([datum];”d”))

Datum-formules in Access

Willen we direct rekenen met de dagen, maanden, weken etc (zonder gebruik te maken van de Val-functie) dan kent Access nog de functie DatePart.

Als we bijvoorbeeld in een query als veld opgeven
JrNr: DatePart([datum];”yyyy”)
dan resulteert dit in een getal met 4 cijfers, dat het jaar aangeeft.

Alle instellingen, die hiervoor bij Access-opmaak zijn toegelicht, zijn hier toepasbaar.
Dus bijvoorbeeld DatePart([datum];”ww”;1;2) levert het weeknummer (als getal) op, waarbij een week begint op zondag en de eerste donderdag bepalend is voor de weeknummer 1.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd: