Excellence Utvikling

Excel utvikling og VBA makro programmering

Hva er datavalidering

Bruker du i dag Excels mulighet for datavalidering? Så snart du lager regneark som skal deles med mange burde du absolutt ha hørt om denne funksjonen. I prinsippet hjelper deg datavalidering å bestemme hva brukeren kan taste in i en celle. Så kan du bestemme at brukeren bare kan taste in helle taller, eller verdier som ligger i en liste. Dette er viktig når filen skal deles og du vil kontrollere hva folk taster in. Hvis du skal bruke en celle der brukeren skal taste in en verdi for en beregning, er det rett og slett ugunstig når noen taster in bokstaver (som kan f.eks. også skjer ved skrivefeil). Da blir alle påfølgende beregninger feil. Ved siden av feilreduksjon blir arket også mye mer brukervennlig.

Datavalidering i Excel finner du under «Data» > «Dataverktøy» > «Datavalidering».
Datavalidering i Excel finner du under «Data» > «Dataverktøy» > «Datavalidering».

Type data validering som finnes i Excel

Det finns seks muligheter å validere data på, pluss egendefinert validering hvor du kan bruke formler for å sjekke om en verdi er tillat:

  • Heltall
  • Desimaltall
  • Liste
  • Dato
  • Klokkeslett
  • Tekstlengden
  • Egendefinert (via formel)

I tillegg til type inntast, kan du i de fleste tilfeller også kontrollere størrelsen på f.eks. tall. Så kan du velge at verdien skal være større/mindre enn en gitt tall, ligge mellom to verdier, skal være det samme eller ikke det same som en annen verdi. Og det er ikke nok. Med egendefinerte formler kan du faktisk kontroller alt annet – du må bare klare å formulere det i en formel.

Forskellige typer datavalidering tilgjengelig i Excel
Flere typer datavalidering tilgjengelig i Excel: Heltall, desimaltall, liste, dato, klokkeslett, tekstlengden og egendefinert (via formel).

Datavalidering fra liste blir nedtrekksmeny / rullegardinmeny

Når du velger «liste» som valideringstype, så kan du legge in enten et celleområde eller en liste med verdier som skal kunne tastes in i cellen. Når brukeren da klikker på cellen, får brukeren en nedtrekksmeny (også kjent som rullegardinmeny eller drop down) som viser alle verdier som er tillat å taste in (og bruker kan da naturlig nok klikke på verdien og må ikke taste den inn – veldig hyggelig for brukeren).

Nedtrekksmeny / Rullegardinmeny / Dropdownmeny
Et typisk nedtrekksmeny, realisert med datavalidering.

«Fast» eller «dynamisk» liste med funksjonen FORSKYVNING

Har du et fast område av celler som skal brukes for liste validering, så markerer du enkelt og greit cellene det gjelder. Men hvis celleområdet endres bestandig (f.eks. fordi brukeren legger til verdier eller det hentes nye verdier fra web, database o.l.), så er det en bra ide å lage en dynamisk liste. Selvsagt kan du bare markere hundrevis av celler for å sikre at alle verdier som eventuelt legges til senere er med. Men resultatet er blant annet at brukeren må navigere gjennom en veldig lang drop down meny.

FORSKYVNING kan være litt vanskelig å forstå når du hører om funksjonen for første gang (det var det i hvert fall for meg når jeg startet med Excel) men egentlig er den ikke veldig komplisert. FORSKYVNING leverer en celle-referanse basert på fem parameter:

=FORSKYVNING(ref, rader, kolonner, [høyde], [bredde])

Første parameter (ref) er «start-cellen», som er første verdien i lista i vårt tilfelle. Deretter oppgir man hvor mange rader og kolonner man ønsker å gå til høyre/venstre eller opp/ned. Disse verdiene er faktisk 0 i vårt tilfelle, siden vi ønsker å starte med første celle. Viktig er [høyde]: Her oppgir man dimensjonen av referansen, dvs. hvor mange rader ned eller opp referansen skal gå.

Siden vi snakker om en dynamisk liste vet vi ikke hvor lenge den er. Men vi kan enkelt finne det ut med hjelp av ANTALLA. ANTALLA teller rett og slett hvor mange ikke tome celler det finns i et område og levere tilbake en tall.

=ANTALLA(verdi1,…)

For å komme tilbake til FORSKYVNING, så kan vi la [bredde] stå tom – en liste skal naturlig nok bare gå over en kolonne og ikke flere.

Eksempel på nedtrekksmeny

Datavalidering i Excel fra lista
I celle C5 har vi en datavalidering med nedtrekksmeny. Det er mulig å velge verdier som står i celle E5 og nedover. Med hjelp av funksjonen FORSKVNING oppdateres listen automatisk.

I celle C5 har vi en nedtrekksmeny hvor bruken kan velge et produkt fra en liste som begynner i celle E5 og går nedover. Som beskrevet bruker vi en datavalidering av type «liste» for å få takk i dette drop down. Spennende er formelen som brukes:

=FORSKYVNING($E$5,0,0,ANTALLA($E$5:$E$1048576))

Hva gjør formelen? Vel vi starter på celle E5, som er første celle i liste. Rad og kolonne forandrer vi ikke, derfor er de neste to parameter «0». Men listen har en høyde som bestemmes med ANTALLA mellom celle E5 og den absolutt siste cellen på arket, E1048576. I dette tilfelle leverer ANTALLA resultatet «4». Og FORSKYVNING leverer dermed et referanse $E$5:$E$8. Du kan nå fritt lege til verdier, og listen oppdateres automatisk – bare prøv, du finner eksemplet til nedlasting litt lengre ned.

Meldinger til brukeren: «Inndatamelding» og «Advarsel om feil»

Innstastmelding og feilmelding ved Excel's datavalidering
I celle C5 har vi en datavalidering som sjekker om datoen ligger mellom 1.1.1900 og dagen dato. Her har vi også en inndatamelding som vises når brukeren aktiverer celle C5 og en feilmelding som vises når en ugyldig verdi (f.eks. en større dato, desimaltall eller bokstaver) tastes inn.

Det er også mulig å opprette en «Inndatamelding» som vises når bruken klikker på en celle som inneholder en datavalidering. Her er det mulig å gi brukeren informasjon om hvilken type verdi som skal tastes inn. Men tenk på at for mange meldinger kan bli irriterende for brukeren. Noe som absolutt anbefales er derimot en melding med «Advarsel om feil». Brukeren skal være klart over når en verdi som ikke er tillatt er tastet in. Også er det et godt tidspunkt å gi brukeren informasjon om tillatte verdier i cellen.

Last ned eksemplet her:

Last ned Excel fil
datavalidering.xlsx

Om Excellence Utvikling

Excellence Utvikling hjelper bedrifter med alle oppgaver rundt Microsoft Excel, blant annet utvikling av kalkyler, dashboards, makroer og mye mer. Ta gjerne kontakt med alle typer spørsmål eller finn ut mer om våre Excel konsulent tjenester. Lik gjerne siden på Facebook, så får du alle nyheter.