Datavalidering

Denne artikel beskriver en generel funktion i Excel og er altså ikke rettet rettet mod løsning af et konkret problem, og det kunne måske have været anbragt under 'How to', men nu kommer den altså her.

Af og til har man brug for at kontrollere, hvad en bruger kan indtaste i en eller flere celler. Dette kan man gøre ved hjælp af VBA ved komplicerede valideringer, men i det fleste tilfælde er det nemmere at bruge funktionen Datavalidering som findes under fanen Data. Når man vælger dette punkt, vises dialogboksen til højre. Dialogboksen har tre faneblade og den åbner i fanebladet Indstillinger. Her kan man i den øverste rude vælge, hvad man vil tillade. Som standard er alt tilladt (Vilkårlig værdi), hvilket jo også fremgår at man kan taste stort set alt i alle celler.

Vælger man 'Helt tal' eller 'Decimal', kan man i ruden 'Data' (her skjult under rullelisten) specificere nogle krav til de data, der tastes, som det fremgår af figuren til venstre. Vælger man 'mellem' eller ikke 'mellem' skal man specificere en maksimum- og en minimumsgrænse. Vælger man en af de øvrige muligheder, fx 'lig med' eller 'mindre end', skal man specificere den værdi, som det indtastede skal kontrolleres i forhold til.

Feltet 'Ignorer tomme', kan vælges i alle andre situationer end 'Vilkårlig værdi'. Funktionen gør det muligt at omgå en regel, ved at lade en celle være tom. Har man fx indstillet Tillad til 'Helt tal', Data til 'mellem' og angivet 'Minimum' og 'Maksimum' til hen holdsvis 5 og 10, vil man får en fejl, hvis man indtaster noget, der ikke opfylder betingelsen. Dog kan man taste "ingenting", altså tømme en celle. Fjerner man fluebenet fra 'Ignorer tomme', må cellen ikke være tom, men SKAL indeholde en værdi mellem 5 og 10. Vær i øvrigt opmærksom på, at såvel 5 som 10 er inkluderede, hvis man vælger mellem, mens de ikke er inkluderede, hvis man vælger 'ikke mellem'. 

Som nævnt giver 'Helt tal' kun mulighed for at indtaste hele tal. 'Decimal' giver mulighed for at indtaste såvel hele tal som tal med decimaler. Jeg springer lige 'Liste' over i første omgang og vender tilbage til den nedenfor. 'Dato' giver sjovt nok mulighed for at indtaste datoer, og klokkeslæt for at indtaste klokkeslæt. Også her har man de samme muligheder for at vælge kriterier for data som vist i listen til venstre. Dog skal maksimum- og minimumsværdier angives i korrekt dato- eller klokkeslætsformet. "dd-mm-åå" for datoer og "tt:mm:ss" for klokkeslæt (begge uden anførselstegn). 'Tekstlængde' bestemmer hvor mange tegn, der må tastes i en celle, og her skal der også angives såvel Data som Minimum og Maksimum. Selv om navnet er 'Tekstlængde' gælder det også for tal og formler. I alle de tilfælde, hvor der skal sættes maksimum- og minimumsværdier, kan disse sættes i en celle, som der så refereres til, se figuren til venstre. Dette gør det nemmere at ændre på grænserne, da dette kan gøres i cellen i stedet for at man skal ind og ændre reglen. Ved at indtaste værdier i henholdsvis C1 og C2 kan man styre grænserne dynamisk.

Tilbage er der så kun to de ele,enter, der kan tillades, nemlig Liste og Brugerdefineret. Vælger man 'Liste' kan der kun indtastes værdier, som forekommer på en specificeret liste. I tidligere versioner af Excel skulle denne liste enten være i samme ark som valideringen, eller den skulle være i et navngivet område. I nyere versioner af Excel kan listen godt være i andre ark uden at området skal navngives, men personligt foretrækker jeg at navngive alligevel, også fordi der er andre situationer, se fx artiklen Afhængige dropdownlister, hvor et navngivent område er et krav. Et eller andet sted i projektmappen, fx i Ark2, laver man derfor sin liste. Den skal indeholde alle de valgmuligheder, som brugeren skal have. I eksemplet her har jeg indtastet nogle træer, birk, bøg, ceder, cypres, eg, el, elm, hassel og kastanje i celler under hinanden. Dette område har jeg navngivet 'træer' (uden anførselstegn). Når jeg vælger 'Liste' bliver feltet 'Data' gjort inaktivt, mens man til gengæld skal angive en kilde. Her angiver man navnet på det område, der indeholder listen. Husk lighedstegnet; ellers tror Excel, at det kun er navnet, der er tilladt i cellen, i dette tilfælde 'træer'. Det er stadig muligt at ignorere tomme, men der er nu kommet en ekstra boks, hvor der som standard er sat flueben. Det betyder, at der i den celle, hvor valideringen gælder, bliver vist en rulleliste. Se illustrationen til venstre. Fjernes fluebenet vises rullelisten ikke, men man kan stadig kun indtaste de værdier, der er specificeret på listen. Det gør det bare vanskeligere for brugeren at vide, hvad der må tastes - ikke mindst hvis selve listen findes i et skjult ark.

Tilbage er der så kun typen 'Brugerdefineret'. Selve dialogboksen ligner den fra 'Liste' men i stedet for 'Kilde' findes ruden 'Formel', hvor man kan indtaste en formel, der skal være opfyldt, for at en indtastning kan godkendes. Denne mulighed fx anvendelig, hvis man gerne vil have at flere kriterier skal være opfyldt samtidigt. Eksempel: I celle H1 skal der indtastes et tal. Dette skal være et heltal, det skal være mindre end 10 og det skal være et ulige tal. Umiddelbart kunne man lave en liste med alle ulige heltal mindre end 10, men den opgave bliver voldsom, hvis alle negative hele ulige tal er tilladt, for dem er der jo uendeligt mange af, så liste er udelukket her. De to første betingelser kan løses sammen ved at vælge 'helt tal', sætte 'data' til 'Mindre end' og sætte grænsen til 10, men den sidste betingelse om, at det skal være et ulige tal, kan man ikke vælge sig frem til. I stedet kan man lave en formel, der håndterer alle tre betingelser, og indtaste denne i ruden 'Formel'. I det konkrete tilfælde kunne formlen se således ud: =OG(ER.ULIGE(H1);H1<10;H1-HELTAL(H1)=0). OG bruges for at tilkendegive at alle tre betingelser, skal være opfyldt samtidigt. Er det nok at én af betingelserne er opfyldt kan man i stedet bruge ELLER. ER.ULIGE tester om tallet er ulige; H1<10 tester selvfølgelig om tallet er mindre end 10. Desværre er der ingen funktion, der undersøger om et tal er et heltal, så det må man selv gøre. Det gøres med H1-HELTAL(H1)=0. Hvis tallet i H1 minus heltalsværdien af samme tal, giver 0, er det fordi tallet er et heltal. Giver det ikke 0 er tallet et decimaltal, og det vil vi ikke have J. Desværre kan man  tilsyneladende ikke bruge Brugerdefinerede funktioner (UDF - funktioner, man selv har programmeret) i datavalideringsformler.

Så meget om det første faneblad. Nu videre til de to næste, Meddelelse. Fanebladet Meddelelse er vist til venstre. Her kan man indtaste en meddelelse, der vises i cellen, når brugeren aktiverer den. Denne tekst kan være en vejledning eller forklaring til bruger på det, der må/kan tastes i den pågældende celle. Fjerner man fluebenet fra 'Vis en meddelelse, når cellen er markeret' vises meddelelsen ikke, - og så kunne man lige så godt havde ladet være med at skrive den. Mange synes at meddelelsen er irriterende, fordi teksten kan dække over andre celler, som man gerne vil se, mens man vælger; jeg hører selv til den kategori. På den anden side, kan man altså hjælpe brugerne på vej.

I det sidste faneblad, kan man så indtaste en fejlmeddelelse, der bliver vist, hvos bruger taster noget, der ikke er tilladt. Hvis man ikke taster noget i dette faneblad vises en standardfejlmeddelelse, som typisk er forholdsvis intetsigende. Jeg vil ikke vise meddelelsesboksen, men teksten er "Denne værdi svarer ikke til de datavalideringsbegrænsninger, som er angivet for cellen, og så er der tre knapper: 'Forsøg igen', 'Annuller' og 'Hjælp'. Det er derfor bedre selv at lave en mere sigende tekst. Og det gøres altså i fanebladet Fejlmeddelelse. Se illustrationen til højre.

Man skal måske ikke fornærme brugerne, som jeg gør i illustrationen til højre, selv om man kan være fristet til det, ikke mindst hvis man allerede har vist en tekst, der forklarer, hvad der må tastes J. Som i 'Meddelelse' kan man angive en titel og en
(fejl)meddelelse. Også her kan man ved at fjerne et flueben bestemme at fejlmeddelelsen ikke skal vises, men hvorfor så bruge tid og kræfter på et lave den. I ruden 'Type' kan man vælge mellem tre typer af meddelelser, hvor standard et 'Stop'. Indtastningen blokeres, og de samme tre knapper som i standardmeddelelsen vises. Vælges i stedet for Advarsel, vises en lidt anderledes meddelelsesboks. Her er der fire knapper, og en tekst der siger: "Du måtte kun indtaster hele tal mellem 1 og 10, dit fjold. Vil du fortsætte?' og så er der ud over 'Annuller' og 'Hjælp' en 'Ja' knap og en 'Nej' knap. Vælger man 'Ja' er det altså muligt at få Excel til at acceptere indtastningen, selv om den egentlig er mod den regel, der er defineret for den pågældende celle. Den sidste mulighed er 'Oplysninger' og vælges dette, vises igen en meddelelsesboks med teksten fra fejlmeddelelsen, og nu tre knapper, 'OK', 'Annuller' og 'Hjælp'. Klikkes Annuller vender cellen tilbage til det indhold, der stod før man begyndte at taste, og klikker man OK, accepteres indtastningen.

Til sidst knappen 'Nulstil alle'. Den nulstiller reglen for den pågældende celle eller celleområde. Marker man et celleområde eller flere spredte celler, og vælger Datavalidering vil man blive advaret om, at cellerne indeholder forskellige regler, og at de alle vil blive slettede (reglerne, ikke cellerne eller deres indhold), hvis man fortsætter.

-Tilbage til Excel -