Excellerend
Kwartaaltip 2014-2
Excellerend Heemraadweg 21 2741 NC Waddinxveen 06 – 5115 97 46
[email protected] BTW: NL0021459225 Bank: NL72 ABNA 0536825491 KVK: 24389967
Onjuiste invoer! Wanneer je werkt met een Excelbestand waarbij niet alle invoer in een cel mag komen te staan dan is het goed om te weten dat Excel regels kan gebruiken om de handmatige invoer te beoordelen op juistheid. Dit heet valideren. Ik vind het een leuke functionaliteit, en als je een model hebt gebouwd dat een ander gebruikt, kun je die persoon er via deze techniek op wijzen dat niet alle invoer in het model geaccepteerd is.
Validatie - Instellingen Het is mogelijk dezelfde validatie op één cel, of op een cellenbereik te zetten. Ik werk het voorbeeld hier uit voor één cel. Klik op de tab Gegevens en in de groepering “Hulpmiddelen voor gegevens” klik je op Gegevensvalidatie > Gegevensvalidatie…
Excel komt dan met een dialoogvenster dat uit drie tabbladen bestaat:
Pagina 1 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2014-2
Op het tabblad Instellingen bepaal je de validatiecriteria. Bij “Toestaan:” zijn diverse mogelijkheden om uit te kiezen welke ik stuk voor stuk kort zal toelichten:
1) Alle waarden. Hiermee accepteer je alle invoer. 2) Geheel getal. Deze optie geeft je de mogelijkheid om af te dwingen dat er alleen hele getallen ingevoerd mogen worden. Decimalen zijn niet toegestaan en tekst ook niet. Hierbij kun je het bereik dat je toestaat op verschillende manieren bepalen:
Pagina 2 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2014-2
a. Tussen. Je krijgt dan een veld om het minimum in te vullen en een veld voor het maximum. In deze velden kun je behalve en getal, ook een formule neerzetten: i. =MIN(B1:B10); ii. =MIN(B1:B10)+100; iii. =WEEKNUMMER(NU()), b. Niet tussen. Idem als tussen. c. Gelijk aan. Hiermee kun je afdwingen dat er maar één getal ingevoerd kan worden. Ook kun je maar naar één cel verwijzen. d. Niet gelijk aan. Precies het tegenovergestelde. Hiermee voorkom je dat een bepaald getal ingevoerd kan worden. e. Groter dan. f. Kleiner dan. g. Groter dan of gelijk aan. h. Kleiner dan of gelijk aan. 3) Decimaal. Met deze optie kun je aangeven dat er wel decimalen ingevoerd mogen worden. Het aantal decimalen kun je hiermee niet beperken. Verder werkt deze optie gelijk aan ‘Geheel getal’. 4) Lijst. Zodra je deze optie kiest, verschijnt het volgende dialoogvenster:
Pagina 3 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2014-2
Wanneer in een cel alleen maar ja of nee mag komen te staan, kun je dat bepalen door bij Bron: te typen: ja;nee. Items moeten gescheiden worden door een puntkomma.1 Het nadeel is wel dat validatie hoofdlettergevoelig is. Dus “JA” intikken terwijl “ja” mag, zal niet lukken. Het is ook mogelijk om te verwijzen naar een aantal cellen via =B1:B10. Alleen de waarden (tekst en getallen) in de cellen B1 tot en met B10 mogen ingevoerd worden. Deze lijst met mogelijkheden mag ook op een ander tabblad staan: =Blad2!B1:B10. En als er in het bestand een lijst een naam gekregen heeft (zie www.excellerend.nl, cursus voor gevorderden les 1), zoals “maanden”, kan ook daarnaar verwezen worden via =maanden Bij de optie “Lijst” wordt ook een vierkantje rechts getoond “Vervolgkeuzelijst in cel”. Wanneer dit vierkantje aangevinkt is, verschijnt rechts van de cel een met de keuzelijst.
levert op: 5) Datum. Deze optie werkt net als ‘Geheel getal’ en ‘Decimaal’, je dient namelijk een begin- en einddatum op te geven met weer de opties:
1
Voor Engelstalige versies van Excel geldt als scheidingsteken een komma.
Pagina 4 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2014-2
6) Tijd. Deze optie werkt net als ‘Datum’, ‘Geheel getal’ en ‘Decimaal’, je dient namelijk een begin- en eindtijd op te geven, met weer dezelfde opties. 7) Lengte tekst. Met deze optie kan de lengte van de invoer begrensd worden. Er dient ook hier een minimum en maximum opgegeven te worden en de opties zijn dezelfde als bij ‘Datum’, ‘Tijd’, ‘Geheel getal’ en ‘Decimaal’. Deze beperking geldt ook als je een functie invoert. De functie zelf mag heel lang worden, als de uitkomst van de formule maar voldoet aan de beperking. 8) Aangepast. Deze laatste optie herbergt veel mogelijkheden om de invoer te beperken. Je kunt alleen een formule invoeren, maar dat kan heel uitgebreid: a. =A1=AFRONDEN(A1;-2). Deze formule zorgt ervoor dat de invoer een veelvouden van 100 moet zijn omdat de invoer in de cel gelijk moet zijn aan de afgerond invoer op honderdtallen. b. =ISTEKST(A1). Deze formule zorgt ervoor dat er alleen tekst ingevoerd kan worden. Al zijn getallen voorafgegaan door een quote voor Excel ook tekst. c. =IS.EVEN(A1) zorgt ervoor dat er alleen even getallen ingevoerd mogen worden. d. Validatie op het celbereik A2 t/m A10: =A2>A1. Dit zorgt ervoor dat de invoer in een cel hoger moet zijn dan het getal dat op dat moment in de cel erboven staat. e. =AANTAL.ALS($A$1:A2;A2)<2. Deze formule zorgt ervoor dat er alleen unieke getallen ingevoerd mogen worden in een bereik dat begin bij cel A1. Wanneer je het vierkantje bij “Lege cellen negeren” aanvinkt, accepteer je lege cellen, ook al voldoen die niet aan het criterium.
Validatie - Invoerbericht Het tweede tabblad bevat de mogelijkheid om de gebruiker te laten weten dat de geselecteerde cel een validatie bevat die de invoer beperkt:
Pagina 5 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2014-2
Bij het selecteren van de cel verschijnt dan dit bericht:
De titel wordt vet weergegeven. Het bericht kan maximaal 255 karakters bevatten.
Validatie - Foutmelding Het derde tabblad geeft de mogelijkheid om de algemene foutmelding bij nietgeaccepteerde invoer aan te passen.
Pagina 6 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2014-2
Bovenstaand scherm geeft bij foutieve invoer de volgende melding:
Zoals in het eerste scherm te zien valt, zijn er drie Stijlen bij de foutmelding mogelijk. De eerste stijl ‘Stoppen’ betekent dat de invoer niet geaccepteerd wordt. Bij “Opnieuw” blijft de invoer in de cel staan, maar de cel komt in de bewerkingsmodus te staan zodat de invoer aangepast kan worden. Bij “Annuleren” verdwijnt de invoer. De tweede optie geeft een waarschuwing aan de persoon die iets verkeerds ingevoerd heeft. De gebruiker krijgt dan wel de mogelijkheid de invoer alsnog door te voeren:
Pagina 7 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2014-2
Als je op Nee klikt, blijft de invoer in de cel staan en komt de cel in bewerkingsmodus. Je kunt dus rechtstreeks de invoer aanpassen. Als je op annuleren klikt, blijft de cel actief, maar verdwijnt de invoer. De derde optie bij de Stijl is ‘Info”. Dit betekent dat de invoer sowieso geaccepteerd wordt, maar de gebruiker krijgt wel een melding dat de invoer afwijkt.
Ook hier geldt dat op Annuleren klikken ervoor zorgt dat de invoer verdwijnt. Klik je op OK, dan komt de invoer in de cel te staan.
Controle achteraf Stel dat er cellen in een tabel zijn die –om wat voor reden dan ook- ongeldige waarden bevatten. In onderstaand voorbeeld mag de waarde alleen een even getal zijn.
Op deze cellen is –achteraf- de volgende validatie gezet:
Pagina 8 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2014-2
Er gebeurt dan niets meer met de invoer, want er wordt niets meer handmatig ingevoerd. Via Gegevens > Hulpmiddelen voor gegevens > Gegevensvalidatie > Ongeldige gegevens omcirkelen, is het eenvoudig de oneven cijfers te achterhalen:
Deze actie geldt voor het hele werkblad, dus alle cellen waarop validatie staat. Validatie op een ander tabblad blijft ongemoeid. Na correctie van de cel zelf, verdwijnt de cirkel, maar als er naar een andere cel wordt verwezen en díe cel wordt gecorrigeerd, dan verdwijnt de cirkel pas bij hernieuwd klikken op “Ongeldige gegevens omcirkelen”. Via Gegevens > Hulpmiddelen voor gegevens > Gegevensvalidatie > Validatiecirkels wissen, verwijder je de cirkels weer van het beeldscherm.
Aanpassen validatie Wanneer je een cel selecteert met validatie erop, kun je die validatie aanpassen via het tabblad “Instellingen”. Onderaan staan een vierkantje waarmee je in één keer alle cellen met dezelfde validatie kunt aanpassen:
Zwaktes van validatie Zoals in het begin van deze kwartaaltip uitgelegd, beperkt validatie de handmatige invoer. Wanneer data ingevoerd wordt via een macro, dan werkt het niet. Wanneer in een cel met validatie een formule wordt gebruikt waarbij wordt verwezen naar een andere cel, dan werkt validatie wel, maar zodra de waarde in de cel waarnaar verwezen wordt verandert, dan wordt de invoer niet opnieuw gevalideerd en kan er ongewenste invoer in komen te staan. Tenslotte geldt dat als je een cel zonder validatie kopieert en plakt over een cel heen mèt validatie, dat dan de validatie verdwijnt. Maar in de voorgaande paragraaf is in ieder geval aangetoond dat onjuiste invoer ook achteraf nog eenvoudig te vinden is. Einde kwartaaltip 2014-02
Pagina 9 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2014-2
De kwartaaltip(s) zijn als PDF te downloaden via: www.excellerend.nl\kwartaaltips.html Wilt u een op maat gemaakte cursus Excel voor uw organisatie regelen? Zoekt u ondersteuning in het bouwen van rekenmodellen, controles, of denkt u dat iets mogelijk is in Excel maar u weet niet hoe? Neem dan contact op met Richard Meijles: 06 - 5115 9746 of via e-mail:
[email protected]. Wilt u geen kwartaaltips meer ontvangen? Klik dan op:
[email protected] Hebt u een verzoek voor een kwartaaltip? Klik dan op:
[email protected] en vermeld tevens uw Excelversie. Richard Meijles <><
Pagina 10 van 10
www.excellerend.nl
