Gegevensvalidatie is een ingebouwde feature van Excel om te controleren of invoer klopt met bepaalde eisen. In deze tutorial kijken we naar twee toepassingen van gegevensvalidatie: het voorkomen van dubbele factuurnummers in een tabel en het invoegen van een keuzemenu.

Het is mogelijk om invoer automatisch te controleren om te zien of deze voldoet aan een aantal randvoorwaarden. Het voorkomt niet álle fouten, maar hiermee vist u een groot deel van potentiële fouten eruit. Zoals per ongeluk een cijfer te veel te tikken of een verkeerd gegevenstype te gebruiken waardoor een formule niet werkt. Gegevensvalidatie is eigenlijk niets meer dan een vlugge check om te kijken of een veldwaarde voldoet aan een bepaalde invoerverwachting.

Controletool

Voor de hand liggende beschermingen zijn bijvoorbeeld het vastleggen van een gegevenstype, zodat alleen een datum wordt geaccepteerd en geen andere invoer. Of dat er een waarschuwing wordt gegeven als er onverwachte invoer is. Verder in dit artikel, onder Voorkomen dubbele waarden, worden de verschillen tussen deze twee opties gedemonstreerd aan de hand van een tabel. Wat voor nu belangrijk is, is dat Gegevensvalidatie een sterke controletool van Excel is die verschillende invoerfouten kan voorkomen.

Toepassing: Drop Down List

De meeste gebruikers die deze tool kennen, gebruiken hem voor keuzelijsten, oftewel dropdown-lists. In dit voorbeeld hebben we een gastenlijst waar we voor gasten willen toevoegen uit welk land ze afkomstig zijn. Hiervoor verwijzen we naar een ander tabblad genaamd ‘Hulptabellen’ met de volgende tabel erop:

Neem de volgende tabel en selecteer het bereik waar de dropdown-list moet verschijnen:

Ga naar Gegevens > Gegevensvalidatie (Data > Data Validation) boven Hulpmiddelen voor gegevens (Data Tools) om het dialoogvenster te openen. Onder Validatiecriterium: Toestaan (Validation Criteria: Allow) kiest u voor Lijst (List). Zorg ervoor dat Vervolgkeuzelijst in cel (In-cell dropdown) is aangevinkt, dit zorgt namelijk voor de dropdown-list. In het veld onder Bron (Source) verwijzen we naar de hulptabel als volgt:

Tip: Een snelle manier om dit te doen is door op de knop rechts van bron (pijltje omhoog) te klikken, naar het tabblad te navigeren en de cellen selecteren die onderdeel moeten uitmaken van de keuzelijst.

Toepassing: Voorkomen dubbele waarden

Een toepassing is bijvoorbeeld het voorkomen van dubbele waarden in de invoer. Er zijn verschillende manieren om dubbele waardes eenvoudig te verwijderen, zoals we eerder in dit artikel bespraken, maar met gegevensvalidatie is te voorkomen dat dubbele waarden worden ingevoerd. Dat doet u door een booleaanse vergelijking te maken. Als de formule waar is, komt de waarde komt niet voor in het gedefinieerde bereik en is de invoer toegestaan. Bij onwaar is er sprake van een dubbele waarde en geeft Excel, na het aanmaken van een criterium voor gegevensvalidatie, een blokkering en melding.

Neem onderstaande tabel met daarin inkoopnummer en factuurnummers. Deze moeten uniek zijn, dus we willen voor elk van deze twee laatste kolommen voorkomen dat dezelfde waardes in deze tabel worden ingevoerd.

De functie AANTAL.ALS (COUNTIF) van Excel is er om te tellen hoe vaak een bepaalde waarde voorkomt in een bereik. Maar dat kunnen we ook omdraaien: na het definiëren van een bereik vragen we Excel om op te merken als volgens AANTAL.ALS een waarde meer dan eens voorkomt.

Selecteer het bereik waarin de waardes uniek moeten zijn, in dit geval ten eerste C2 t/m C11, en kies Gegevens > Gegevensvalidatie (Data > Data Validation) boven Hulpmiddelen voor gegevens (Data Tools). In het dialoogvenster Gegevensvalidatie kiest u onder Validatiecriterium: Toestaan (Validation Criteria: Allow) voor Aangepast (Custom). Van deze functie moet een booleaanse evaluatie worden gemaakt. Dat doen we door =1 erachter te zetten. Dat betekent dat zolang als de waarde één keer voorkomt de vergelijking WAAR is en de foutmelding die we hierna maken niet wordt aangesproken.

De kolom zetten we vast met het dollarteken, zodat wanneer de tabel groeit met meer crediteuren (en facturen), de gegevensvalidatie meegroeit met de nieuwe regels. Het bereik in dit voorbeeld wordt dan C$2:C11. Als criterium pakt u de eerste cel met een inkoopnummer, dat is C2. De formule wordt dan in zijn geheel: =AANTAL.ALS(C$2:C11;C2)=1.

Omdat in sommige gevallen misschien een inkoopnummer bij meerdere klanten kan zitten, kiezen we hier voor een waarschuwing onder het tabblad Foutmelding (Error Message). Dit zorgt ervoor dat gebruikers een melding zien die ze eventueel kunnen negeren. In het volgende voorbeeld met unieke factuurnummers, wordt dat een blokkade.

Als er we nu een inkoopnummer dubbel invoeren, komt de volgende waarschuwing in beeld:

Voor de kolom met waarden die absoluut uniek moeten zijn, doet u min of meer hetzelfde. De formule hier wordt =AANTAL.ALS(D$2:D11,D2)=1. Alleen kiezen we hier bij Foutmelding voor Stop. Dit zorgt ervoor dat er geen mogelijkheid is om een dubbele waarde in te voeren.

Bij een foutieve invoer, dus wanneer wordt gedetecteerd dat een waarde meer dan 1x voorkomt, geeft Excel nu een foutmelding:

Volg CMWeb op LinkedIn!