En viktig funksjon i Excel er Hvis-funksjonen. Dette er en av de første milepælene som Excel-brukere bør komme igjennom. Det er ikke nødvendigvis slik at Hvis-funksjonen er så vanskelig i seg selv, men med litt kreativitet kan den brukes til mye forskjellig. Her vil vi vise seks smarte varianter av Hvis-funksjonen.
Utover den vanlige introduksjonen blir ofte begrepet Nøstet hvis presentert som en måte å bruke Hvis-funksjonen på. Dette betyr at det brukes en “hvis i en hvis i en hvis osv.”, men dette er strengt tatt ikke en god metode. Det finnes bedre metoder, som f.eks. finn.rad, som kommer i neste artikkel.
I denne artikkelen skal vi først og fremst se på mer nyttige varianter av Hvis (men det kommer også en nøstet Hvis).
Om du synes artikkelen er litt lang, kan du trøste deg med at det er korte videoer til hvert eksempel!
Den vanlige introduksjonen til en Hvis-funksjon
En Hvis-funksjon er et Ja / Nei-spørsmål. Dvs. gjør dette dersom svaret er Ja, ellers gjør det andre. Det er strengt tatt hele funksjonen…
I først omgang kan dette gjøres ganske enkelt. Er en person eldre enn 67? Et naturlig Ja / Nei spørsmål. Dersom svaret er Nei, kan det logisk trekkes ut at denne personen må være under 67, uten at det trengs et nytt spørsmål. Og det er et viktig poeng her.
Et oppsett kan se slik ut i Excel:
Hvis-funksjonene består av 3 argumenter.
• Logisk_test -> dette er et uttrykk som skal returnere SANN eller USANN. Ofte er det større, mindre eller lik som brukes, og at det er enkle celler som måles mot hverandre. Men det kan gjerne være formler og funksjoner, så lenge sluttresultatet blir SANN eller USANN.
• SANN -> Hva skal skje dersom svaret er Ja i forhold til logisk_test. Dette kan være en tekst som i bildet, men da må det brukes ” først og sist. Eller hente verdien fra en celle. Eller gjøre en fullstendig ny beregning.
• USANN -> Hva skal skjer dersom svaret er Nei. Samme regler som argumentet SANN.
I bildet over sjekkes det om Alder er større eller lik 67. Hvis det er sant, skal det skrives Ja i cellen. Dersom det er usant, altså er alder under 67, skriv Nei.
Etter kopieringen av funksjonen blir resultatet:
Det å skrive 67 direkte inn i funksjon er ikke så praktisk dersom det er en verdi som kan endrer seg ofte. (Kanskje ikke pensjonsalder med aller første, men poenget er der.)
Cellen med 67 kan selvfølgelig ligge hvor som helst.
Se video av eksempelet
Bruke funksjoner som en del av logisk_test
Litt mer komplisert og interessant er å benytte funksjoner som en del at testen. Noen ganger er det helt naturlig benytte en kolonne som Alder i eksemplet over, mens i andre tilfeller kan det være greit å lage en støtte-kolonne, som igjen brukes av testen eller at er det ønskelig å finne resultatet direkte.
Er personen født i en helg eller ukedag?
Eksempelet er litt banalt, men metoden er alltid den samme. Først må en lure på noe, vurdere de data en har tilgjengelig, og prøve finne en løsning.
Eksempel 1 – Eller funksjon
Ettersom det finnes en kolonne med ukedagen, kan denne brukes. I forhold til den logiske testen, skal svaret være Ja dersom ukedagen enten er lørdag eller søndag. Altså to alternativer som begge er ønskelige. Til dette finnes ELLER-funksjonen som kan ha mange logiske tester og dersom en av dem er SANN, vil hele uttrykket være SANN.
Eksempel 2 – Ukedag funksjon
Dersom ukedag ikke er tilgjengelig, som det var i eksemplet over, kan det gjøres med annen funksjon. En som heter Ukedag. Denne funksjonen returnerer tallet 1 for mandag til 7 for søndag. Dette betyr at dersom resultatet fra ukedagsfunksjonen er 6 eller mer, så er det en helg.
Er datoen tidlig eller sent i måneden?
En variant som dukket opp under et kurs, var mulighet for å sjekke om det var overvekt av salg tidlig eller sent i måneden, eller om det var en jevn fordeling. Funksjon som heter DAG gir tallet for dagen i måneden, som er det som trengs for denne testen. (Merk også at det finnes MÅNED og ÅR funksjoner.)
Dette skapte en kolonne, som igjen kunne vært utgangpunkt for andre funksjoner, filter og pivottabell.
På grunn av at det trengs en funksjon for den logiske testen, er dette et eksempel der en nøstet hvis-funksjon er et alternativ dersom en ønsket “Sent”, “Midten” og “Tidlig” som alternativer.
Den andre Hvis-funksjonen er USANN argumentet til den første. Dvs. at dersom den første testen er USANN, forsetter Excel til å neste ledd, som er nok en Hvis-funksjon med en litt annen logisk test.
Se video av eksempelet
Finne ut ukedagen til neste bursdag
Det som er utfordringen med denne varianten er å finne ut hva som er neste bursdag. Det er enkelt for mennesker, men for en datamaskin er det litt verre. Vi må finne ut om bursdagen har vært dette året eller ikke.
Vi henter ut dd-mm- fra fødselsdato og legger til år, for så gjøre en enkel hvis test om fødselsdagen er frem tid, i så fall har ikke bursdagen vært dette år, eller må det legges til et år for å finne neste bursdag.
Denne varianten gjør fødselsdato om til bursdag i år, der H4 har verdien for i år. Merk at vi må gjøre noe matematisk for at teksten skal bli en dato. Derfor trengs det parentes rundt TEKST([@Fødselsdato];”dd-mm-“)&$H$4 for at hele utrykket skal gjøres om og ikke bare H4.
Den logiske testen blir om den beregnende datoen er større enn i dag (G4 har verdien for i dag).
Hele utrykket blir som følger, dersom bursdagen for i år er frem i tid, bruk datoen for i år, ellers legg til et år for årstallet (H4 +1)
For å finne hvilken uke resultatet er, legges det til en Tekst funksjon rundt hele utrykket.
Se video av eksempelet
Bruke hvis som kosmetikk
I en del tilfeller er det ikke til å unngå at det enten vises en feilmelding i en celle, uten at det direkte betyr at beregningen i seg selv er feil, men heller at det er mangler eller er mangelfulle verdier andre steder. #DIV/0 er ganske typisk, dersom cellen som det skal deles på er tom eller er satt til 0.
Til dette finnes en enkel funksjon som heter Hvisfeil.
Hvisfeil-funksjonen har to argumenter. Det første argument (verdi) er selve beregningen i seg selv, den som har potensiale til å bli en feilverdi. Det andre argumentet er verdi_hvis_feil. I dette argumentet kan det være hva som helst. Ofte blir det brukt til å skrive en tekst eller peke på en celle med ønsket informasjon. Dersom en ikke ønsker at noe skal vises, så kan det skrives doble anførselstegn, “”, som bare betyr ‘vis ingen ting’.
Tankesettet med denne funksjonen blir: hvis beregningen gir et ok resultat, gi meg det resultatet, ellers gi meg det alternative resultatet.I andre tilfeller kan det være at en beregning trenger verdier fra to celler, men at det vises et rart eller mystisk resultat. Da kan en bruke en vanlig Hvis-funksjon for å sjekke om en celle er tom, og dermed ikke vise noe, mens en kan vise resultatet når alle nødvendige celler fylt ut.
Den logiske testen er her veldig enkel, og her kan det brukes en eller-funksjon for å sjekke flere celler før beregningen blir utført.
Hvis funksjonen som del av en betinget formatering
På samme vis som du kan styre resultatet via en Hvis-funksjon, kan også kreative varianter brukes som regler for betinget formatering. Ovenfor ble det brukt en eller-funksjon for vise om en fødselsdato var en helg. Den samme effekten kan brukes til å vise med farge om den neste bursdagen er en helg.
Merk ønsket område og gå til Hjem -> Betinget formatering -> Ny regel
Velg Bruk en formel til å bestemme… (1) som gir muligheten til å skrive inn en formel i feltet (2). Det kan være greit å skrive inn funksjon utenfor denne boksen først og kontrollere at den gir riktig resultat. Det som er poenget er at sluttresultatet skal være SANN eller USANN for hele funksjonen.
Videre vil referansen være en absolutt referanse (med dollar-tegn) når du klikker på den ønskede cellen. Trykk F4 flere ganger slik at det ikke er dollar-tegn i referansen (relativ referanse).
Til slutt klikk på Formater… og velg ønsket format for presentasjonen (3).
Se video av eksempelet
Hvis funksjonen som del av datavalidering
Datavalidering kan ofte brukes med hell i regneark der det skal foregå en del inntasting, selv om datavalidering har store svakheter, kan en godt si at det er bedre enn ingen ting. I dette eksemplet er det fødselsnr som skal skrives inn. For de som har jobbet med fødselsnr i praksis, vet godt hvilke utfordringer som skjer.
Hovedpoenget er at celleformateringen må være Tekst-formatet, ellers vil den ledende nullen forsvinne når verdien skrives inn. Fødselsnr er per definisjon IKKE et tall. Denne problemstillingen blir enkelt løst med at formatet i cellen er tekst.
Selv om formatet er tekst, betyr ikke det at verdien som skrives automatisk er riktig. Det er to regler til som må være tilstede. Det skal være nøyaktig 11 siffer OG de 6 første siffer skal utgjøre en gyldig dato.
For 11 siffer kan vi bruke: LENGDE(A7)=11
For å sjekke om 6 første som dato: IKKE(ERFEIL(DATOVERDI(TEKST(VENSTRE(A21;6);”00-00-00″))))
- IKKE snur SANN til USANN og USANN til SANN
- ERFEIL sjekker om verdien gir feilmelding eller ikke
- DATOVERDI gjør en tekst om til dato, og gir feilmelding dersom teksten ikke kan gjøres om til en dato.
I sum: henter 6 første fra fødelsnr og gjøre det om til en dato, dersom den lot seg returnerer ERFEIL USANN (siden ikke er feil) og funksjonen IKKE gjør dette resultatet om til SANN, som er det resultatet vi ønsker for valideringen.
Tilslutt settes de to testene sammen med en OG-funksjon, siden begge må være sanne for at vi skal godta verdien.
Legge til datavalidering
Merk området med fødelsnr og gå til Data -> Datavalidering
I den første fanen i dialogboksen, velg Egendefinert under Tillat (1).
I formel blir hele utrykket: (2)
=HVIS(OG(LENGDE(A7)=11;IKKE(ERFEIL(DATOVERDI(TEKST(VENSTRE(A7;6);”00-00-00″)))));SANN;USANN)
Her er det veldig fornuftig å sette opp utrykket i en celle og bare lime inn funksjonen.
I fanen Advarsel om feil kan du skrive inn en ønsket melding.
Ved å skrive inn noe annet enn en verdi som ikke fyller kravene gir nå følgende melding:
Se video av eksempelet
Kommentar
Selv om Hvis-funksjonen i seg selv er relativt enkel, så er det ikke dermed sagt at den ikke er nyttig. Og som vist over er i sammenheng med andre funksjoner at den virkelig kan være elegant. Både i forhold til beregninger, men, som vist, også veldig verdifullt i betinget formater og validering.
Det er vanskelig å sette opp en generell visning fordi formen på en hvis-funksjon gjør den ekstremt anvendelig. Her er det først og fremst snakk om litt forståelse og litt kreativitet.
Og hele basisen for en hvis-funksjon er et Ja / Nei spørsmål.
Hev din Excel-kompetanse
Delta på et av våre Excel kurs og lær hvordan du jobber smart og effektivt med Excel!
Sjekk også vår YouTube-kanal for korte videoer om Excel.