"Gegevensvalidatie"

Concepts

Welkom terug — de poortwachter van je cel

Fijn dat je er weer bent. Dit is de vierde en laatste les van Module 12, en daarmee de afsluiter van het blok "Slimme formules". Je hebt al flink wat gereedschap in je koffer. Met **ALS** (ch12a) laat je Excel een beslissing nemen. Met **X.ZOEKEN** (ch12b) haal je bij een code automatisch de juiste naam of het juiste tarief op. En met **voorwaardelijke opmaak** (ch12c) laat je cellen vanzelf kleuren als er iets opvalt. Vandaag komt daar een laatste, heel praktisch stuk bij — en in de missie laat ik je zien hoe alles samenkomt.

Karin schuift een spreadsheet naar je toe waar het nogal een rommeltje in is. *"Kijk eens mee. Iemand heeft hier boekingen ingetypt. In de kolom BTW-tarief staat de ene keer `21`, dan weer `0,21`, en hier staat zelfs `eenentwintig procent`. In de kolom grootboekrekening staat `7000`, maar drie regels lager `7oo0` met letters. En bij datum staat `31-02-2026` — een dag die niet bestaat. Wat denk je dat er met je administratie gebeurt als zulke troep erin komt?"*

Precies: je formules lopen vast, je X.ZOEKEN vindt niks, en je optellingen kloppen niet meer. Het probleem is niet dat de typist dom is — het probleem is dat de cel *alles* accepteert. Vandaag leer je hoe je dat dichttimmert. **Gegevensvalidatie** is een poortwachter die je voor een cel zet: hij laat alleen invoer door die aan jouw regels voldoet, en weigert de rest beleefd maar beslist.

> TIP: Gegevensvalidatie voorkomt rommel aan de bron. In plaats van achteraf fouten opsporen, zorg je dat foute invoer er niet eens in kán komen. Een cel die alleen een datum, alleen een bedrag boven nul, of alleen een keuze uit een lijst toelaat.

---

Waar het zit — en wat je instelt

Je vindt het menu op het lint onder **Gegevens**. Klik eerst de cel (of een heel celbereik) aan dat je wilt beveiligen, en kies dan:

   Gegevens  →  Gegevensvalidatie  →  Gegevensvalidatie...

Er opent een venster met drie tabbladen. Onthoud die drie goed, want ze komen vandaag steeds terug:

| Tabblad | Wat je er instelt | |---|---| | **Instellingen** | De regel zelf: wat mag er in de cel? (lijst, getal, datum, tekstlengte) | | **Invoerbericht** | Een tip die verschijnt zodra je de cel selecteert | | **Foutmelding** | Wat er gebeurt als iemand toch iets fouts typt |

Op het tabblad **Instellingen** kies je onder "Toestaan" het **validatietype**. Dat is de kern. We lopen de belangrijkste types één voor één langs, met telkens een voorbeeld uit de administratie van Van Ginkel Solutions BV.

Lijst | keuzelijst
Een dropdown met vaste keuzes
BTW-tarief, status, rekening
De belangrijkste — meeste fouten weg
---
Geheel getal / Decimaal | grenzen
Alleen getallen, eventueel met grens
Bedrag groter dan 0
---
Datum | binnen periode
Alleen een geldige datum
Binnen het boekjaar
---
Tekstlengte | aantal tekens
Precies of maximaal zoveel tekens
Rekeningnummer van 4 cijfers

> TIP: De drie tabbladen zijn los te combineren. Je kunt een regel instellen zónder invoerbericht, of een invoerbericht zonder strenge foutmelding. Begin altijd op het tabblad Instellingen met de regel zelf.

---

Het belangrijkste type: de keuzelijst (Lijst)

Verreweg het meest gebruikte validatietype is **Lijst**. Daarmee maak je van een gewone cel een **keuzelijst** (een dropdown): de gebruiker klikt op een pijltje en kiest uit een paar vaste opties, in plaats van zelf te typen. Geen tikfouten, geen verschillende schrijfwijzen — alleen wat jij toestaat.

Stel je wilt dat in de kolom **Betaalstatus** alleen `Open`, `Betaald` of `Vervallen` mag staan. Selecteer de cellen, ga naar Gegevensvalidatie, en kies bij Toestaan voor **Lijst**. In het vak **Bron** typ je de opties, gescheiden door een puntkomma:

   Toestaan:  Lijst
   Bron:      Open;Betaald;Vervallen

Klik op OK, en elke cel heeft nu een pijltje met die drie keuzes. Dit is prima voor een kort, vast lijstje dat bijna nooit verandert.

Maar voor een lange lijst — zeg, al je grootboekrekeningen of BTW-tarieven — is het handiger om de bron naar een **celbereik** te laten verwijzen in plaats van alles in dat smalle vakje te typen. Dan zet je de keuzes ergens netjes in een kolom, en wijs je dat bereik aan. Stel je hebt op een tabblad de tarieven staan:

        E
   ┌──────────┐
 1 │ 0,21     │
 2 │ 0,09     │
 3 │ 0        │
   └──────────┘

Dan kies je bij Bron niet losse tekst, maar het bereik:

   Toestaan:  Lijst
   Bron:      =$E$1:$E$3

Het grote voordeel: verandert de lijst — je voegt bijvoorbeeld een tarief toe in E4 — dan groeit de keuzelijst mee zodra je het bereik aanpast. Je beheert de keuzes op één centrale plek. Dat is precies hoe je het in een echte administratie wilt: één lijst grootboekrekeningen, waar al je keuzelijsten naar verwijzen.

> TIP: Voor een kort, vast lijstje: typ de opties met puntkomma's in Bron. Voor een lange of veranderende lijst: verwijs naar een celbereik (`=$E$1:$E$3`). Zo beheer je de keuzes op één plek en hoef je ze maar één keer bij te werken.

---

Getallen begrenzen: Geheel getal en Decimaal

Soms wil je geen lijst, maar een **grens** op een getal. In de kolom Bedrag bijvoorbeeld mag nooit een negatief getal of een stuk tekst staan. Daarvoor kies je bij Toestaan voor **Decimaal** (getallen mét cijfers achter de komma, zoals bedragen) of **Geheel getal** (alleen ronde getallen, zonder komma).

Onder Toestaan verschijnt dan een vak **Gegevens** met opties als *groter dan*, *tussen*, *kleiner dan of gelijk aan*, enzovoort. Wil je dat een bedrag altijd boven nul ligt:

   Toestaan:  Decimaal
   Gegevens:  groter dan
   Minimum:   0

Nu weigert de cel een leeg bedrag, een negatief getal of tekst. Wil je een bedrag binnen een bandbreedte houden — zeg een kasbedrag tussen 0 en 5000 — kies dan *tussen*:

   Toestaan:  Decimaal
   Gegevens:  tussen
   Minimum:   0
   Maximum:   5000

Het verschil tussen Geheel getal en Decimaal: een aantal stuks in een voorraad is een **geheel getal** (je hebt geen 3,5 dozen), maar een **bedrag** is decimaal (€ 12,50 moet kunnen). Kies dus bewust.

> TIP: Gebruik Decimaal voor bedragen (komma's toegestaan) en Geheel getal voor aantallen. Met de optie "groter dan 0" houd je negatieve bedragen en lege of tekstuele invoer in één keer buiten de deur.

---

Datums binnen het boekjaar, en tekst op lengte

Het type **Datum** werkt net als de getallen, maar dan met data. Heel nuttig om te zorgen dat een boeking binnen het juiste **boekjaar** valt. Stel het boekjaar 2026 loopt van 1 januari tot en met 31 december:

   Toestaan:  Datum
   Gegevens:  tussen
   Begindatum: 1-1-2026
   Einddatum:  31-12-2026

Nu kan niemand per ongeluk een factuur in 2025 of 2027 boeken, en — heel handig — Excel weigert ook een onbestaande datum zoals 31 februari, want dat is geen geldige datum. Eén regel die twee soorten fouten tegenhoudt.

Tot slot **Tekstlengte**. Daarmee eis je een bepaald *aantal tekens*. Heel praktisch voor codes die altijd even lang zijn. Een grootboekrekeningnummer bij Van Ginkel Solutions BV is bijvoorbeeld altijd precies vier tekens (`1100`, `7000`, `8000`):

   Toestaan:  Tekstlengte
   Gegevens:  is gelijk aan
   Lengte:    4

Typt iemand `700` (drie tekens) of `70000` (vijf), dan wordt het geweigerd. Let op: tekstlengte controleert alléén het aantal tekens, niet of het cijfers zijn. Voor "precies 4 tekens én alleen cijfers" zou je verder moeten gaan, maar voor de meeste administraties is de lengtecontrole al een prima vangnet.

Datum binnen boekjaar | periode
Tussen 1-1 en 31-12
Houdt verkeerd jaar buiten
Weigert ook 31 februari
---
Bedrag groter dan 0 | grens
Decimaal, groter dan 0
Geen negatief, geen tekst
Bedragen blijven netjes
---
Rekeningnummer 4 tekens | lengte
Tekstlengte is gelijk aan 4
Niet 700, niet 70000
Codes blijven uniform

> TIP: Een datumvalidatie "tussen 1-1 en 31-12" vangt twee fouten tegelijk: een boeking in het verkeerde jaar én een onbestaande datum. Tekstlengte is ideaal voor codes met een vaste lengte zoals een rekeningnummer van 4 tekens.

---

Invoerbericht en foutmelding — de hulp en de grens

De regel zelf staat nu. De twee andere tabbladen maken het gebruiksvriendelijk. Het **Invoerbericht** is een gele tip die verschijnt zodra iemand de cel aanklikt — nog vóór er iets fout kan gaan. Je gebruikt het om uit te leggen wat de bedoeling is. Op het tabblad Invoerbericht vul je een titel en een tekst in:

   Titel:  Grootboekrekening
   Bericht: Kies een rekening uit de lijst. Twijfel je?
            Kijk op het tabblad 'Rekeningschema'.

Het **Foutmelding** komt pas als het misgaat: iemand typt toch iets dat de regel niet toestaat. Hier kies je belangrijk genoeg om even bij stil te staan — de **stijl** van de melding. Er zijn drie soorten, en het verschil is groot:

| Stijl | Pictogram | Wat hij doet | |---|---|---| | **Stoppen** | rood kruis | Weigert de invoer helemaal. Je kunt niet door zonder het te verbeteren. | | **Waarschuwing** | geel driehoekje | Waarschuwt, maar je mág doorgaan als je op "Ja" klikt. | | **Informatie** | blauwe i | Geeft alleen een melding; de invoer wordt gewoon geaccepteerd. |

Voor een grootboekrekening of een BTW-tarief wil je **Stoppen** — daar mag echt niks fouts in. Voor een veld waar een uitzondering soms denkbaar is, kan **Waarschuwing** passend zijn. Vul ook hier een titel en tekst in, zodat de gebruiker snapt wat er mis is:

   Stijl:   Stoppen
   Titel:   Ongeldige rekening
   Bericht: Deze rekening staat niet in het schema.
            Kies een nummer uit de keuzelijst.

Karin wijst naar het rode kruis. *"Het verschil tussen die drie is in de praktijk enorm. Met Stoppen kan een typist gewoon niet verder met rommel — dat is wat je wilt op een rekening of een tarief. Met Waarschuwing laat je 'm zelf beslissen. Schrijf altijd een nette tekst: 'Ongeldige invoer' helpt niemand, maar 'Kies een nummer uit de keuzelijst' wel."*

> TIP: Invoerbericht = hulp vooraf (geel tipje bij selectie). Foutmelding = grens achteraf. Kies bewust de stijl: Stoppen weigert echt, Waarschuwing laat doorgaan na bevestiging, Informatie meldt alleen. Voor rekeningen en tarieven: altijd Stoppen.

---

Alles samen — de afsluiter van Module 12

Nu het mooie. Vandaag is de laatste les van de module, dus laten we zien hoe de vier stukken samenwerken. Stel je hebt een cel waarin een BTW-tarief gekozen moet worden. Dan zetten de gereedschappen van deze module zich op een rij:

   ÉÉN CEL, VIER GEREEDSCHAPPEN SAMEN

   [Gegevensvalidatie · Lijst]
        keuzelijst: 0,21  ·  0,09  ·  0
        → je kunt alleen een geldig tarief kiezen
              │
              ▼
   [X.ZOEKEN]  (ch12b)
        zoekt bij het gekozen tarief de omschrijving op
        21% → "Hoog tarief",  9% → "Laag tarief"
              │
              ▼
   [ALS]  (ch12a)
        rekent: BTW-bedrag = bedrag × gekozen tarief
              │
              ▼
   [Voorwaardelijke opmaak]  (ch12c)
        kleurt de regel rood als er tóch iets niet klopt

Zie je hoe ze in elkaar grijpen? De **validatie-lijst** zorgt dat er alleen een geldig tarief in de cel komt. **X.ZOEKEN** vertaalt die keuze naar een leesbare omschrijving. **ALS** rekent ermee. En **voorwaardelijke opmaak** is het laatste vangnet dat opvalt als er onverhoopt toch iets misgaat. Validatie voorkomt fouten vooraf, opmaak signaleert ze achteraf — samen vormen ze een dubbel slot op de kwaliteit van je administratie.

> TIP: Validatie en voorwaardelijke opmaak zijn elkaars partners. Validatie houdt fout invoer tegen aan de poort; voorwaardelijke opmaak kleurt op wat er ondanks alles toch doorheen glipt. Gebruik ze samen voor een waterdichte invoer.

---

Beheer — kopiëren, verwijderen en ongeldige gegevens omcirkelen

Tot slot drie praktische beheerklusjes die je vaak nodig hebt.

**Validatie kopiëren.** Heb je één cel goed ingesteld en wil je dezelfde regel op een hele kolom? Kopieer de cel (Ctrl+C), selecteer het doelbereik, en gebruik **Plakken speciaal → Validatie** (rechtermuisknop → Plakken speciaal → kies "Validatie"). Zo neem je alleen de regel mee, zonder de inhoud of opmaak te overschrijven.

**Validatie verwijderen.** Wil je een regel weghalen? Selecteer de cellen, ga naar Gegevens → Gegevensvalidatie, en klik op de knop **Wissen** (of "Alles wissen") linksonder in het venster. De cel accepteert dan weer alles.

**Ongeldige gegevens omcirkelen.** Een echte verborgen parel. Soms zet je validatie pas in op een kolom die al gevuld is — en dan zit er misschien al rommel in die niet aan de nieuwe regel voldoet. Excel kan die er voor je uitlichten. Kies **Gegevens → Gegevensvalidatie → Ongeldige gegevens omcirkelen**. Elke cel die de regel overtreedt krijgt een rode ovaal eromheen, zodat je in één oogopslag ziet wat je nog moet opschonen. Met "Validatiecirkels wissen" haal je ze daarna weer weg.

Validatie kopiëren | hergebruik
Cel kopiëren (Ctrl+C)
Plakken speciaal → Validatie
Alleen de regel, niet de inhoud
---
Validatie verwijderen | wissen
Gegevensvalidatie → Wissen
Cel accepteert weer alles
Snel een regel weghalen
---
Ongeldig omcirkelen | opschonen
Bestaande rommel opsporen
Rode ovaal om foute cellen
Ideaal bij een gevulde kolom

> TIP: "Ongeldige gegevens omcirkelen" is goud waard als je validatie achteraf instelt op een al gevulde kolom. Excel zet een rode ovaal om elke cel die niet aan de regel voldoet, zodat je precies ziet wat je moet opruimen.

---

Afronding van Module 12

Hiermee zit Module 12 erop. Even terugkijken: je begon de module met **ALS** om Excel beslissingen te laten nemen, je leerde **X.ZOEKEN** om bij een code de juiste gegevens op te halen, je liet cellen vanzelf kleuren met **voorwaardelijke opmaak**, en vandaag sloot je af met **gegevensvalidatie** om foute invoer al aan de poort tegen te houden. Vier gereedschappen die in de praktijk constant samenwerken — precies zoals je in het diagram zag.

Karin pakt haar koffie. *"Dit is het gereedschap waarmee je een invoersjabloon bouwt waar een collega niks mee fout kan doen. In de missie ga je dat echt maken: een boekingsformulier voor Van Ginkel Solutions BV met keuzelijsten, datumcontrole en een nette foutmelding — en als bonus haal je met X.ZOEKEN automatisch de rekeningnaam erbij. Daarmee laat je alles van deze module samenkomen. Aan de slag, je kunt dit zelf."*

> TIP: Module 12 in één zin: ALS beslist, X.ZOEKEN haalt op, voorwaardelijke opmaak signaleert en gegevensvalidatie voorkomt. Samen maken ze van een losse spreadsheet een betrouwbaar invoersysteem.

---

Missie

STORY: Karin zet een leeg werkblad voor je klaar. *"Tijd om alles van Module 12 te laten samenkomen. Je bouwt voor Van Ginkel Solutions BV een boekingsformulier waar een collega niks mee fout kan doen. Een keuzelijst voor de grootboekrekening, een keuzelijst voor het BTW-tarief, een datumcontrole binnen het boekjaar, en nette foutmeldingen. Als bonus haal je met X.ZOEKEN automatisch de rekeningnaam erbij. Ik geef de richting; jij stelt het grotendeels zelf in. Begin met het neerzetten van de keuzelijsten."*

Stap 1 — Zet de bronlijsten klaar

Open een nieuwe werkmap. We hebben twee bronlijstjes nodig waar onze keuzelijsten naar verwijzen. Zet die rechts op het blad, in de kolommen E en F, zodat ze het formulier links niet in de weg staan.

Maak eerst een mini-rekeningschema in **E** en **F**, en de BTW-tarieven in **H**:

        E         F                        H
   ┌────────┬──────────────────────┐  ┌────────┐
 1 │ 1100   │ Bank                 │  │ 0,21   │
 2 │ 1500   │ Te vorderen BTW      │  │ 0,09   │
 3 │ 7000   │ Inkoopwaarde omzet   │  │ 0      │
 4 │ 8000   │ Omzet handelsgoed.   │  │        │
   └────────┴──────────────────────┘  └────────┘

Kolom E bevat de rekeningnummers, kolom F de bijbehorende namen, en kolom H de drie geldige BTW-tarieven. Dit zijn de bronnen waar je validatie en je X.ZOEKEN straks naar wijzen.

Stap 2 — Bouw het formulierkader

Maak links het invoerformulier. Typ in **A1** `BOEKING INVOEREN` en zet daaronder de labels in kolom A. De cursist vult straks in kolom B in.

        A                      B
   ┌────────────────────┬──────────────────┐
 1 │ BOEKING INVOEREN                       │
 2 │ Datum              │ (in te vullen)    │
 3 │ Grootboekrekening  │ (in te vullen)    │
 4 │ Rekeningnaam       │ (X.ZOEKEN)        │
 5 │ Bedrag excl. BTW   │ (in te vullen)    │
 6 │ BTW-tarief         │ (in te vullen)    │
   └────────────────────┴──────────────────┘

De cellen **B2, B3, B5 en B6** krijgen straks validatie. **B4** vult zichzelf met een formule. Laat ze voorlopig leeg.

Stap 3 — Keuzelijst voor de grootboekrekening (zelf instellen)

Nu zelf aan de slag. Selecteer cel **B3** en open Gegevens → Gegevensvalidatie. Het doel: een keuzelijst die alleen de rekeningnummers uit kolom E toelaat. Kies dus type **Lijst** en verwijs bij Bron naar het bereik met de nummers.

   Toestaan:  Lijst
   Bron:      =$E$1:$E$4

Geef het meteen een **invoerbericht** (tabblad Invoerbericht) en een **foutmelding** met stijl **Stoppen** (tabblad Foutmelding). Verzin zelf nette teksten — denk aan wat een collega zou helpen. Bijvoorbeeld:

   Invoerbericht  → Titel:   Grootboekrekening
                    Bericht: Kies een nummer uit de lijst.
   Foutmelding    → Stijl:   Stoppen
                    Titel:   Ongeldige rekening
                    Bericht: Kies een nummer uit de keuzelijst.

Klik OK en test: cel B3 heeft nu een pijltje met de vier rekeningnummers.

Stap 4 — Keuzelijst voor het BTW-tarief, plus de rekeningnaam met X.ZOEKEN

Doe nu hetzelfde voor het **BTW-tarief** in cel **B6**: type Lijst, Bron `=$H$1:$H$3`, met een passende foutmelding (stijl Stoppen). Nu kan daar alleen 0,21, 0,09 of 0 in.

Dan de bonus. In **B4** laat je de **rekeningnaam** automatisch verschijnen op basis van het gekozen nummer in B3 — precies waar X.ZOEKEN (ch12b) voor is. Het gekozen nummer staat in B3, de nummers in E1:E4, de namen in F1:F4. Typ in **B4**:

=X.ZOEKEN(B3;$E$1:$E$4;$F$1:$F$4)

Test het: kies in B3 het nummer `7000`, en in B4 verschijnt vanzelf `Inkoopwaarde omzet`. Eén keuze in de lijst, en de naam rolt er automatisch bij. Zo grijpen validatie en X.ZOEKEN in elkaar.

Stap 5 — Datumcontrole en bedrag begrenzen (zelf instellen)

Twee regels die je nu zelfstandig instelt.

**Datum (cel B2).** Het boekjaar is 2026. Stel een validatie in van type **Datum**, optie *tussen*, met begindatum 1-1-2026 en einddatum 31-12-2026. Geef een foutmelding met stijl Stoppen.

   Toestaan:   Datum
   Gegevens:   tussen
   Begindatum: 1-1-2026
   Einddatum:  31-12-2026

**Bedrag (cel B5).** Een bedrag mag nooit negatief of nul zijn. Stel een validatie in van type **Decimaal**, optie *groter dan*, minimum 0.

   Toestaan:  Decimaal
   Gegevens:  groter dan
   Minimum:   0

Test ze allebei: typ in B2 eens `31-2-2026` (bestaat niet) of `15-8-2025` (verkeerd jaar) — beide worden geweigerd. Typ in B5 eens `-50` — ook geweigerd.

Stap 6 — Test als geheel en controleer met omcirkelen

Vul nu een complete, geldige boeking in en kijk of alles soepel werkt:

        A                      B
   ┌────────────────────┬──────────────────────┐
 2 │ Datum              │ 15-3-2026             │
 3 │ Grootboekrekening  │ 7000                  │
 4 │ Rekeningnaam       │ Inkoopwaarde omzet  ← X.ZOEKEN
 5 │ Bedrag excl. BTW   │ 1000                  │
 6 │ BTW-tarief         │ 0,21                ← uit keuzelijst
   └────────────────────┴──────────────────────┘

Mooi: elk veld is beveiligd, de rekeningnaam vult zichzelf, en een collega kan er geen rommel in typen. Controleer tot slot nog of er geen oude fouten in zitten: kies **Gegevens → Gegevensvalidatie → Ongeldige gegevens omcirkelen**. Bij een net ingevuld formulier verschijnen er geen rode ovalen — bewijs dat alles binnen de regels valt.

Sla het bestand op als `Van Ginkel Solutions BV boekingsformulier`.

**Karin kijkt mee en knikt tevreden.** *"Kijk eens wat je hebt staan. Een boekingsformulier waar een collega gewoon niks mee fout kán doen: een datum die binnen het boekjaar moet vallen, een bedrag dat boven nul moet liggen, en keuzelijsten waaruit alleen een bestaande rekening en een geldig tarief gekozen kunnen worden — met nette foutmeldingen die uitleggen wat er moet. En de rekeningnaam haalt X.ZOEKEN er automatisch bij. Dit is precies waar Module 12 naartoe werkte: ALS, X.ZOEKEN, voorwaardelijke opmaak en gegevensvalidatie die samen één betrouwbaar systeem vormen. Knap gewerkt — en hiermee is de module af."*