Geldigheidsregels voor gegevensinvoer opstellen

1. Leerdoel

De student moet geldigheidsregels voor gegevens kunnen opstellen teneinde de gegevensinvoer in tabellen te valideren.

2. Toelichting

Bij het invoeren van gegevens in een tabel is het van belang dat dit correct gebeurt.  Tik- en andere fouten moeten dus zoveel mogelijk gedetecteerd en voorkomen worden.  Om dit te realiseren kunnen geldigheidsregels worden opgesteld, waarin een aantal voorwaarden wordt vermeld waaraan de invoer van gegevens moet voldoen om te worden aanvaard.

Zo lijkt het in ons voorbeeld van daarnet logisch dat in het bestand 'Oefening_tabel.xlsx' de geboortedatum van studenten in het tweede en derde jaar niet na 1999 kan en mag vallen (in de onderstelling dat we momenteel 2016 schrijven en dat studenten, die zich in een hogeschool inschrijven, niet jonger dan 17 jaar zijn).  

Evenzo moet de stagequotering tussen het cijfer 0 (nul) en 20 liggen.  Elk ander ingevoerd cijfer moet door MS Excel worden geweigerd omdat het niet correct is.

3. Werkwijze

3.1. Validering van de stagequotering

3.1.1. Gegevensinvoer beperken

Om ervoor te zorgen dat er in de kolommen voor de stagequoteringen enkel decimale cijfers tussen 0 en 20 kunnen worden ingevoerd, ga je als volgt te werk.

1. Selecteer de kolommen 'I' en 'J' om zo dadelijk voor deze kolommen in te stellen dat enkel waarden tussen 0 en 20 kunnen worden ingetikt. Een stagequotering kan namelijk nooit kleiner dan 0 zijn en niet hoger dan 20.

2. Klik in het lint op het tabblad 'Gegevens'

3. Klik vervolgens in de groep 'Hulpmiddelen voor gegevens' op de opdrachtknop 'Gegevensvalidatie'

4. Daardoor wordt het dialoogvenster 'Gegevensvalidatie' geopend

5. Klik op het tabblad 'Instellingen', wanneer dit nog niet geopend is, en voer volgende info in.

6. Klik op de vervolgknop naast de keuzelijst 'Toestaan' om de standaardwaarde 'Alle waarden' te wijzigen in 'Decimaal'

7. Klik de keuzelijst 'Gegeven' open en kies voor de waarde 'tussen', indien dat al niet het geval is.
   Bemerk dat deze keuzelijst nog heel wat andere keuzemogelijkheden bevat die je later kan gebruiken.

8. Tik in het invoerveld 'Minimum:' de waarde 0 in en in het invoerveld 'Maximum:' de waarde 20.

9. Omdat je nu hebt ingesteld dat de waarden, die als stagequoteringen in de kolommen I en J mogen worden ingevoerd, tussen de decimale waarden 0 en 20 moeten liggen, klik je tenslotte op de knop 'OK'.

10. Test de goede werking van de zojuist ingevoerde validatieregel uit door enkele cijfers in één van de cellen van kolom I of J te tikken. Wanneer je een waarde groter dan 20 of kleiner dan 0 intikt, dan verschijnt een foutmelding op het moment dat je de cel verlaat.

11. Door op de knop 'Opnieuw' te klikken, kan een nieuwe waarde worden ingevoerd. Een klik op de knop 'Annuleren' maakt de cel geheel leeg.

3.1.2. Invoerbericht weergeven

Indien je dat wenst, kan MS Excel een bericht op het beeldscherm laten verschijnen wanneer een cel in de kolommen 'I' en 'J' geselecteerd wordt. In dit bericht krijgt de gebruiker informatie aangaande aard van de gegevens, die in de cel kan worden ingevoerd, nog vóór met het intikken wordt gestart.

1. Selecteer de kolommen 'I' en 'J' opnieuw
   
   
2. Klik andermaal op de opdrachtknop 'Gegevensvalidatie'
   
   
3. Klik thans in het gelijknamige dialoogvenster op het tabblad 'Invoerbericht'
   
   
4. Tik een gepaste titel in, alsook een gepast invoerbericht, en klik op 'OK'
   

5. Wanneer je nu een cel in de kolom 'I' of 'J' selecteert, verschijnt het invoerbericht automatisch.

3.1.3. Foutmelding weergeven

Evenmin verplicht, maar wel nuttig, kan de informatie zijn die in het tabblad 'Foutmelding' kan worden ingetikt.  Deze informatie verschijnt onder de vorm van een foutbericht, wanneer de gebruiker een waarde invoert, die niet aan de opgestelde geldigheidsregels voldoet.

1. Selecteer de kolommen 'I' en 'J' opnieuw
   
   
2. Klik andermaal op de opdrachtknop 'Gegevensvalidatie'
   
   
3. Klik thans in het gelijknamige dialoogvenster op het tabblad 'Foutmelding'
   
   
4. Kies in de keuzelijst 'Stijl' een gepast icoon:
   
   
   • Hiermee voorkom je dat gebruikers ongeldige gegevens in een cel invoeren. De foutmelding Stoppen heeft twee opties: Opnieuw of Annuleren
     
     
   • Hiermee waarschuw je gebruikers dat de ingevoerde gegevens ongeldig zijn, maar wordt de ongeldige invoer niet verhinderd. Wanneer een Waarschuwing wordt weergegeven, kunnen gebruikers op Ja klikken om de ongeldige invoer toch nog te accepteren, op Nee klikken om de ongeldige invoer te wijzigen of op Annuleren klikken om de ongeldige invoer te verwijderen
     
     
   • Hiermee laat je gebruikers weten dat de ingevoerde gegevens ongeldig zijn, maar wordt ongeldige invoer niet verhinderd. Dit type foutmelding is het meest flexibel. Wanneer de foutmelding 'Informatie' wordt weergegeven, kunnen gebruikers op OK klikken om de ongeldige waarde te accepteren of op Annuleren klikken om de invoer te verwijderen
     
     
5. Tik een gepaste titel in alsook een gepaste foutmelding en klik op 'OK'

6. Om de goede werking van het foutbericht te testen, tik je nu in een cel van kolom 'I' of kolom 'J' een waarde groter dan 20 of kleiner dan 0. De foutmelding zou bij het verlaten van de cel moeten verschijnen.

3.2. Validering van de geboortedatum

Om te controleren of een manueel ingevoerde geboortedatum correct werd ingevoerd, kan je een gelijkaardige 'validering' opzetten.

1. Selecteer kolom 'E'

2. Kies in het tabblad 'Gegevens' voor de opdrachtknop 'Gegevensvalidatie'
   
   
3. Vul in het dialoogvenster 'Gegevensvalidatie' het volgende in:

4. Daardoor verschijnt een foutmelding indien een geboortedatum na 1/01/1999 wordt ingevoerd, het is namelijk onwaarschijnlijk dat een student jonger is dan 17 jaar wanneer die in 2016 schoolloopt in het hoger onderwijs.
   

5. Vul desgewenst de informatie in de andere tabbladen ('Invoerbericht' en 'Foutmelding') in.
   
   
6. Test de geldigheidsregel uit door een niet-valide datum (na 01/01/1999) in te voeren.

2.3. Invoer van het 'Geslacht' valideren

Wanneer de ingevoerde waarden in de kolom 'Geslacht' niet gevalideerd worden, dan is het voor gebruikers mogelijk om het geslacht te omschrijven als 'M', 'm', 'Man', 'man', 'V', 'v', 'Vrouw', 'vrouw', ....

De verwerking van deze ingevoerde waarden in bijvoorbeeld een frequentietabel, zou er later toe leiden dat er niet twee geslachten in de tabel worden uitgelijst, maar wel zes, of acht, of....

Om te voorkomen dat in de kolom 'Geslacht' andere waarden dan 'M' en 'V' worden ingevoerd, kan volgende geldigheidsregel worden ingevoerd:

1. Selecteer de kolom 'Geslacht'
   

2. Kies in het tabblad 'Gegevens' voor de opdrachtknop 'Gegevensvalidatie'
   
   
3. Vul in het tabblad 'Instellingen' van het dialoogvenster 'Gegevensvalidatie' het volgende in:

• Kies in de keuzelijst 'Toestaan' voor de optie 'Lijst'
  
  
• Tik in het invoerveld 'Bron' de afkorting voor 'Vrouw' en 'Man', van elkaar gescheiden door een puntkomma: V;M
  
  
• Klik op de knop 'OK'

4. Wanneer nu in een cel van de kolom 'Geslacht' geklikt wordt, verschijnt een icoon met vervolgknop. 

5. Bij het aanklikken van dit icoon wordt een menu geopend waarin de keuzemogelijkheden 'M' en 'V' aanklikbaar zijn. 

6. Dit voorkomt dat in deze kolom andere waarden worden ingevoerd. 
   Let op: Alle keuzemogelijkheden, die in het keuzelijstje verschijnen, moeten bij het intikken in het invoerveld 'Bron' steeds van elkaar gescheiden worden door een puntkomma zoals in 'Man;Vrouw;Onbekend'
   
   
7. Zoals steeds kunnen via de andere tabbladen van dit dialoogvenster Invoer- en Foutberichten worden toegevoegd.
   
   
8. Test de werking van de keuzelijst voor het veld 'Geslacht' uit.

2.4. Invoer van de 'Provincie' valideren

Bovenstaande werkwijze voor het aanmaken van een keuzelijst ter validering van de ingevoerde waarden werkt prima wanneer de keuzelijst slechts een beperkt aantal waarden moet bevatten ; bijvoorbeeld: 'Ja;Neen;Onbekend;Niet van toepassing'. 

Er zijn twee situaties waarin een andere werkwijze, maar met een zelfde resultaat, handiger is:

• Het onderhouden van een lange lijst met keuzemogelijkheden, telkens gescheiden door een puntkomma, is niet makkelijk, zeker wanneer achteraf fouten in de keuzelijst moeten worden rechtgezet.

• Wanneer een keuzelijst met dezelfde keuzemogelijkheden (bijvoorbeeld: 'Ja;Neen;Onbekend') voor verschillende variabelen (velden, of nog: kolommen) steeds opnieuw moet worden ingevoerd, dan leidt dit tot veel overbodig tikwerk.  Het zou namelijk handiger zijn indien je in dat geval de keuzemogelijkheden één keer intikt en steeds opnieuw aan verschillende variabelen ter validering kan toewijzen ; en dat is precies wat hierna wordt toegelicht.

1. Tik, op een willekeurige plek op het werkblad, de keuzemogelijkheden onder elkaar in. In ons voorbeeld zijn dat de namen van de provincies, maar dit kan net zo goed een keuzelijst zijn met als keuzemogelijkheden 'Ja', 'Neen', 'Weet niet', of 'Goed', 'Slecht', 'Weet niet', ....

Let wel: plaats het lijstje met keuzemogelijkheden 'buiten' de eigenlijke tabel (doorgaans best rechts van de laatste kolom van je tabel).  Wanneer je het lijstje daarentegen onder één van kolomkoppen plaatst, loop je het risico dat het in de weg staat wanneer je je tabel begint in te vullen met cases.

Je kan de provincienamen uit onderstaande figuur dus best bijvoorbeeld in kolom 'M' intikken

2. Selecteer, na dit tikwerk, de kolom 'Provincie'
   

3. Kies in tabblad 'Gegevens' de opdrachtknop 'Gegevensvalidatie'
   
   
4. Kies in het tabblad 'Instellingen' van het dialoogvenster 'Gegevensvalidatie' de optie 'Lijst' in de keuzelijst 'Toestaan:'

5. Klik op de knop 'Celadres' (nummer 5 in bovenstaande figuur) om de werkbalk 'Gegevensvalidatie' te openen.

6. Selecteer in het werkblad al de cellen die de keuzemogelijkheden van de lijst bevatten.  Bemerk dat in de werkbalk het geselecteerde celbereik wordt opgegeven ; in ons voorbeeld: =$M$3:$M$7.

7. Klik opnieuw op de knop 'Celadres' in de werkbalk 'Gegevensvalidatie' om de werkbalk te sluiten en terug te keren naar het dialoogvenster 'Gegevensvalidatie', waarin thans het geselecteerde celbereik, dat de keuzemogelijkheden van de lijst bevat, is ingevuld in het invoerveld 'Bron'.

8. Zoals steeds kunnen via de andere tabbladen van dit dialoogvenster 'Gegevensvalidatie' 'Invoerberichten' en 'Foutmeldingen' worden toegevoegd.
   

9. Test de werking van de keuzelijst voor het veld 'Provincie' uit.

10. Verwijder het lijstje met keuzemogelijkheden NOOIT, omdat anders de keuzelijst, die ervan afhankelijk is, niet langer naar behoren functioneert.
    

11. Sla het bestand 'Oefening_tabel.xlsx' opnieuw op om de aangebrachte wijzigingen te bewaren.

3. Oefening

Voer in de tabel in het bestand 'Oefening_tabel.xlsx' de gegevens in van de volgende drie fictieve studenten. Je kan aldus de goede werking van de validatieregels, de Invoerberichten en Foutmeldingen testen.

4. Bronnen

Helpprogramma bij Microsoft Excel 2016: zoek op 'gegevensvalidatie'.