Mere om brugerdefineret datavalidering
I en tidligere artikel om datavalidering var jeg inde på muligheden for at bruge formler (og dermed funktioner) til kompliceret datavalidering, og det vil jeg gå lidt videre med i denne artikel. Det er ikke muligt at komme ind på alle de mange muligheder, der findes for at opbygge avancerede datavalideringer, så jeg vil nøjes med at belyse mulighederne med nogle eksempler.
Brugerdefinerede funktioner (UDF) i datavalidering
Jeg vil dog starte et andet sted. I den tidligere artikel skrev jeg, at det ikke var muligt at anvende såkaldt brugerdefinerede funktioner (UDF) i sin datavalidering i modsætning til Excels indbyggede funktioner, og det er også korrekt. Dette kan være et irritationsmoment, hvis man fx har lavet en brugerdefineret funktion, der løser en opgave/undersøger noget, som det ellers vil kræve en kompliceret formel, at undersøge, da man jo så vil skulle have denne formel ind i datavalideringsdialogen. Det er dog muligt, i et vist omfang at "omgå" begrænsningen omkring de brugerdefinerede funktioner.
For at det skal virke, skal man have en brugerdefineret funktion, der som output "leverer" booleske værdier, altså SAND eller FALSK. I eksemplet har jeg brugt denne simple en (og ikke særligt spændende) funktion, der tester om det, der indtastes i en celle er deleligt med 25. I så fald returneres SAND, i modsat fald returneres FALSK. Opgaven kunne være løst med en simpel formel, men nu er det altså for at illustrere brugen af UDF i brugerdefineret datavalidering.
Function ERTOFEM(cel)
If cel Mod 25 = 0 Then
ERTOFEM = True
Else
ERTOFEM = False
End If
End Function
Jeg
skal nu bruge min UDF i en celle i arket, men denne brug kan placeres så langt
væk, at den ikke falder i øjnene eller kolonnen/rækken, den placeres i kan
senere skjules. I dette tilfælde skriver jeg =ERTOFEM(A1), eller hvilken celle
det nu end er, jeg skal datavalidere. Nu navngives den celle, der indeholder
formlen med UDF'en, i dette tilfælde G1. Det kan gøres
ved hjælp af Navnestyring i fanebladet Formler eller nemmere ved bare at skrive et navn
i feltet Navn helt til venstre i formellinjen. Her har jeg kaldt den celle, der indeholder min UDF for "valid" -
uden anførselstegn.
NB! Normalt ville jeg anbringe den celle, hvor jeg bruger funktionen, noget længere væk fra det dataområde, jeg ellers arbejder i, men her står den tæt på, for at kunne vise såvel feltet Navn, som den celle, der er navngivet, og den formel, der står i cellen.
I den celle, hvor jeg skal lave datavalidering, her A1, vælger jeg nu Datavalidering fra fanebladet Data. Under Tillad vælges Brugerdefineret og i ruden Formel skrives =valid (eller hvilket navn, man nu end har givet den celle, hvor funktionen er brugt). Når der nu tastes i cellen, vil datavalidering sammenligne med den celle, hvor funktionen er brugt.
Det er vigtigt at understrege, at denne funktion kun virker, når det er en enkelt celle, der skal datavalideres, ikke hvis valideringen skal kopieres. Dette skyldes, at =valid hele tiden peget tilbage mod den celle, der har navnet, og da navngivne celler altid er absolutte referencer, vil det altså ikke virke, hvis valideringen kopieres.
Eksempler på brugerdefinerede datavalideringer
I den foregående artikel gav jeg et eksempel på en brugerdefineret validering, der skulle undersøge om flere betingelser, var opfyldt samtidigt. Eksemplet var, at der skulle indtastes et ulige heltal mindre end 10, og det kunne løses med denne formel under brugerdefineret validering: =OG(ER.ULIGE(H1);H1<10;H1-HELTAL(H1)=0). Funktionerne OG() eller ELLER() vil altid indgå i denne type valideringer, hvor flere betingelser skal undersøges samtidigt. OG() bruges, hvis alle betingelserne skal være opfyldt samtidigt, og ELLER() bruges, hvis det er nok, at én af betingelserne skal være opfyldt. De to funktioner kan naturligvis kombineres, hvis man fx står med den udfordring at én af to betingelser skal være opfyldt samtidigt med at en tredje betingelse er opfyldt edller at enten skal to (eller flere) betingelser være opfyldt samtidigt, eller også skal en anden betingelse være opfyldt. Et eksempel i forlængelse af ovenstående kunne være, at enten skal de nævnte betingelser være opfyldt, eller også skal tallet være præcis 17,3. Det kan løses med =ELLER(OG(ER.ULIGE(H1);H1<10;H1-HELTAL(H1)=0);H1=17,3).
Mønstre
Den brugerdefinerede datavalidering kan også bruges til at sikre mønstre i det indtastede. I en virksomhed bruger man varenumre, der altid er defineret som et bogstav efter fulgt af 5 cifre, altså fx T12345. Man kunne selvfølgelig oprette en liste med alle de mulige varenumre, men der skal ikke mange til, før det bliver en uoverkommelig opgave. I stedet kan man undersøge om det, der indtastes faktisk overholder reglen (men ikke om der er tastet et forkert nummer). Her som mange andre steder, kan det betale sig,at eksperimentere med formlerne i en celle i regnearket indtil de virker, og når de gør det, så kopiere dem til datavalideringsdialogboksen. I dette tilfælde skal værdien tastes i A1. Det er vigtigt at huske, at de funktioner, der bruges, i den sidste ende skal returnere SAND eller FALSK.
Lad mig bygge formlen op fra grunden. Først og fremmest skal cellen altid indeholde seks tegn, nemlig et bogstav og fem cifre. Det kan testes med LÆNGDE(A1)=6, som netop returnerer SAND, hvis der står 6 tegn, FALSK, hvis der står flere eller færre. Vi skal nu sikre, at de sidste 5 tegn er cifre. Først skal vi imidlertid have fundet dem, hvilket kan gøres med HØJRE(A1;5). Dette returnerer imidlertid værdien af de sidste fem tegn, ikke SAND eller FALSK. Vi kan derfor "pakke" denne formel ind i ER.TAL(), der netop undersøger om noget er tal eller ej, altså ER.TAL(HØJRE(A1;5)). Dette vil imidlertid give værdien FALSK, selv om de sidste fire tegn faktisk er cifre. Dette skyldes, at funktionen HØJRE() er en tekstfunktion og derfor returnerer en tekst-værdi. Vi skal altså have lavet dette om til tal. Dette kan gøres ved at gange resultatet af HØJRE() funktionen med 1 eller ved at pakke yderligere ind i funktionen VÆRDI, altså enten ER.TAL(VÆRDI(HØJRE(A1;5))) eller ER.TAL((HØJRE(A1;5)*1)). Her kan man bruge, hvad der passer én bedst. Nu mangler vi bare at undersøge om det første tegn er en tekst. Funktionen ER.TEKST() falder nok i øjnene her, men desværre kan den ikke bruges, da den vil returnere SAND også på et tal (ciffer). Det skyldes at VENSTRE som skal bruges for at isolere det første tegn, jo returnerer en tekstværdi, også selv om der faktisk står et ciffer, og derfor vil ER.TEKST() altid returnere SAND efter en tekstfunktion, som, VENSTRE(), HØJRE() osv. I stedet kan vi undersøge om det første ciffer IKKE er et tal. Det kan gøres med IKKE(ER.TAL(VÆRDI(VENSTRE(A1;1)))) - eller igen ved at gange resultatet af VENSTRE() med 1 i stedet for at bruge værdi. Nu skal vi så bare have kombineret alle disse tests, hvilket altså kan gøres med OG. Formlen ser nu således ud - her har jeg undladt funktionen VÆRDI() og i stedet netop ganget med 1 for at lave tallets tekstværdi om til en talværdi.
=OG(LÆNGDE(A1)=6;ER.TAL(VÆRDI(HØJRE(A1;5)));IKKE(ER.TAL(VÆRDI(VENSTRE(A1;1)))))
og den kan så kopieres til formelruden i Datavalidering, hvis man har udarbejdet den i en celle, eller naturligvis tastes direkte i ruden, hvis man er sikker på, at man har styr på, hvordan den skal "skrues sammen". På samme måde, men naturligvis med andre funktioner og en anden slutformel, kan man undersøge for andre mønstre. Er mønstret således ciffer, ciffer, bogstav, ciffer, ciffer, fx 12P34, kan det løses med denne valideringsformel;
=OG(LÆNGDE(A1)=5;ER.TAL(HØJRE(A1;2)*1);ER.TAL(VENSTRE(A1;2)*1);IKKE(ER.TAL(MIDT(A1;3;1)*1)))
Validering på anden celle end reglen skal gælde for
I nogle tilfælde er det ikke muligt at bestemme direkte, hvad der må tastes i en celle, da valideringen afhænger af resultatet i en anden celle. I så fald må man inddrage denne celle i valideringsreglen. I dette eksempel skal jeg beregne ydelsen på et lån ved hjælp af funktionen YDELSE(). Renten er angivet i celle A1, løbetiden på lånet står i A2 og lånebeløbet i A3. Ydelsen beregnes i A5 med en almindelig YDELSE() funktion som =-YDELSE(A1/12;A2;A3). Når jeg har sat '-' foran funktionsnavnet, skyldes det at YDELSE() returnerer et negativt tal som resultat, og for mig er det nemmere at overskue, hvis jeg ikke skal huske at indtaste min valideringsregel, som et negativt tal. Betingelsen i dette tilfælde er rettet mod Lånebeløbet, og det skal sikres, at den månedlige ydelse ikke overstiger 700 kr. Det kan jeg gøre ved simpelthen at indtaste denne simple formel i formelruden: =A5<700, men husk, at det skal være i A3, som jo er den celle, der skal valideres, selv om reglen tester på en anden celle.
Endnu et eksempel på en indirekte validering. I nogle celler har jeg en liste, og i resultatcellen funktionen SUMPRODUKT(), som skal beregne summen af nogle værdier, der opfylder en betingelse, fx: =SUMPRODUKT((A1:A50=D1)*(B1:B50)). Her leder den i A-kolonnen efter den værdi, som er specificeret i D1 og sammenlægger så de tilhørende tal i B-kolonnen. Nu vil jeg sikre mig, at der ikke indtastes en værdi i D1, der giver et mindre resultat end 40 i den celle, hvor SUMPROUKT() står, her fx E1. Det kan løses med denne valideringsregel i D1: =SUMPRODUKT((A1:A5=D1)*(B1:B5))>=40