HVIS-funksjonen – nestede formler og unngåelse av feil

Med HVIS-funksjoner kan du foreta en logisk sammenligning mellom en verdi og hva du forventer, ved å teste for en betingelse og returnere et resultat hvis SANN eller USANN.

  • =HVIS(noe er SANN, gjøre noe, ellers gjør noe annet)

Et HVIS-uttrykk kan derfor ha to resultater. Det første resultatet er hvis sammenligningen er SANN, og det andre er hvis den er USANN.

HVIS-uttrykk er utrolig robuste og danner grunnlaget for mange regnearkmodeller, men de er også den opprinnelige årsaken til mange problemer i regnearket. Ideelt sett bør et HVIS-uttrykk gjelde for minimale vilkår, for eksempel han/hun, ja/nei/kanskje, for å nevne noen, men noen ganger kan det hende du må evaluere mer komplekse scenarioer som krever nesting* mer enn 3 HVIS-funksjonen sammen.

* «Nesting» refererer til et forsøk på å koble sammen flere funksjoner i én formel.

Bruk HVIS-funksjonen, en av de logiske funksjonene, for å returnere én verdi hvis en betingelse er sann, og en annen verdi hvis den er usann.

Syntaks

HVIS(logisk_test; verdi_hvis_sann; [verdi_hvis_usann])

Eksempel:

  • =HVIS(A2>B2;"Over budsjettet";"OK")

  • =HVIS(A2=B2,B4-A4,"")

Argumentnavn

Beskrivelse

logisk_test   

(obligatorisk)

Betingelsen du vil teste.

verdi_hvis_sann   

(obligatorisk)

Verdien som returneres hvis resultatet av logisk_test er SANN.

verdi_hvis_usann   

(valgfritt)

Verdien som returneres hvis resultatet av logisk_test er USANN.

Merknader

Selv om Excel gjør at du kan neste opptil 64 forskjellige HVIS-funksjoner, anbefales det ikke å gjøre dette. Hvorfor?

  • Flere HVIS-setninger krever mye tanke for å bygge riktig, og sørg for at logikken kan beregnes riktig gjennom hver betingelse helt til slutten. Hvis du ikke nester formelen 100 % nøyaktig, kan det fungere 75 % av tiden, men returnere uventede resultater 25 % av tiden. Dessverre er sjansen for at du fanger de 25 % slanke.

  • Uttrykk med flere HVIS kan bli veldig vanskelig å vedlikeholde, særlig når du kommer tilbake litt senere og prøver å finne ut hva du, eller enda verre, noen andre prøvde å gjøre.

Hvis du finner at du har et HVIS-uttrykk som bare ser ut til å fortsette å vokse, uten at slutten nærmer seg, er det på tide å slippe musen og tenke over strategien på nytt.

La oss se hvordan du oppretter et komplekst nestet HVIS-uttrykk ved hjelp av flere HVIS, og hvordan du forstår at det er på tide å bruke et annet verktøy i arsenalet til Excel.

Eksempler

Følgende er et eksempel på et relativt standard nestet HVIS-uttrykk som konvertere resultater fra studentprøver til de tilsvarende bokstavkarakterene.

Komplekst nestet HVIS-uttrykk – formelen i celle E2 er =HVIS(B2>97,"A+",HVIS(B2>93,"A",HVIS(B2>89,"A-",HVIS(B2>87,"B+",HVIS(B2>83,"B",HVIS(B2>79,"B-",HVIS(B2>77,"C+",HVIS(B2>73,"C",HVIS(B2>69,"C-",HVIS(B2>57,"D+",HVIS(B2>53,"D",HVIS(B2>49,"D-","F"))))))))))))
  • =HVIS(D2>89,"A";HVIS(D2>79,"B";HVIS(D2>69,"C";HVIS(D2>59,"D";"F")))

    Dette komplekse nestede HVIS-uttrykket følger en enkel logikk:

  1. Hvis testresultatet (i celle D2) er større enn 89, får studenten en A

  2. Hvis testresultatet (i celle D2) er større enn 79, får studenten en B

  3. Hvis testresultatet er større enn 69, får studenten en C

  4. Hvis testresultatet er større enn 59, får studenten en D

  5. Ellers får studenten en F

Dette eksemplet er relativt trygt fordi det ikke er sannsynlig at korrelasjonen mellom testresultater og bokstavkarakterer endres, så det krever ikke mye vedlikehold. Men her er en tanke – hva om du trenger å segmentere karakterene mellom A+, A og A- (og så videre)? Nå må den fire betingelsen HVIS-setningen skrives på nytt for å ha 12 betingelser! Slik vil formelen se ut nå:

  • =HVIS(B2>97,"A+",HVIS(B2>93,"A";HVIS(B2>89,"A-";HVIS(B2>87,"B+",HVIS(B2>83,"B";HVIS(B2>79,"B-",HVIS(B2>77,"C+",HVIS(B2>73,"C";HVIS(B2>69,"C-",HVIS(B2>57,"D+",HVIS(B2>53,"D";HVIS(B2>49,"D-";"F")))

Den er fortsatt funksjonsmessig nøyaktig og vil fungere som forventet, men den tar lang tid å skrive og enda lengre tid å teste for å sikre at den gjør det du ønsker. Et annet åpenbart problem er at du ville måttet angi resultatene og de tilsvarende bokstavkarakterene for hånd. Hva er sannsynligheten for en utilsiktet skrivefeil? Forestill deg at du gjør dette 64 ganger med desto mer komplekse betingelser. Selvsagt er det mulig å gjøre det riktig, men vil du virkelig bruke tiden på denne typen arbeid og risikere å gjøre feil som nesten er umulig å få øye på?

Tips!: Alle funksjoner i Excel krever en begynnende og avsluttende parentes: (). Excel prøver å hjelpe deg med å finne ut hva som skal plasseres hvor, ved å fargelegge ulike deler av formelen når du redigerer den. Hvis du for eksempel skulle redigere formelen ovenfor, ville de begynnende parentesene endres til samme farge som hver av de avsluttende parentesene, etterhvert som markøren flyttes forbi hver av parentesene. Dette kan være spesielt nyttig i komplekse nestede formler, når du prøver å finne ut om du har nok samsvarende parenteser.

Flere eksempler

Følgende er et svært vanlig eksempel på å beregne salgsprovisjon basert på oppnådd omsetning.

Formelen i celle D9 er HVIS(C9>15000,20%,HVIS(C9>12500,17.5%,HVIS(C9>10000,15%,HVIS(C9>7500,12.5%,HVIS(C9>5000,10%,0)))
  • =HVIS(C9>15000,20%,HVIS(C9>12500,17.5%,HVIS(C9>10000,15%,HVIS(C9>7500,12.5%,HVIS(C9>5000,10%,0)))

Denne formelen sier at HVIS(C9 er større enn 15 000, returner 20 %, HVIS(C9 er større enn 12 500, returner 17,5 %, og så videre …

Selv om den ligner på det tidligere karaktereksempelet, er denne formelen et godt eksempel på hvor vanskelig det kan være å vedlikeholde store HVIS-setninger – hva må du gjøre hvis organisasjonen har bestemt seg for å legge til nye kompensasjonsnivåer og muligens også endre eksisterende valuta- eller prosentverdier? Du har mye arbeid på hendene!

Tips!: Du kan sette inn linjeskift i formellinjen for å gjøre lange formler enklere å lese. Du trykker bare på ALT+ENTER før teksten du vil bryte til en ny linje.

Her er et eksempel på provisjonscenarioet med logikken ute av rekkefølge:

Formelen i celle D9 er ute av rekkefølge som =HVIS(C9>5000,10%,HVIS(C9>7500,12.5%,HVIS(C9>10000,15%,HVIS(C9>12500,17.5%,HVIS(C9>15000,20%,0)))))

Kan du se hva som er galt? Sammenlign rekkefølgen på inntektssammenligningene med forrige eksempel. Hvilken vei går dette? Det stemmer, den går fra bunnen opp (kr 5000 til kr 15 000), ikke omvendt. Men hvorfor skal det være så viktig? Det er svært viktig fordi formelen ikke kan bestå den første evalueringen for en verdi over kr 50 000. La oss si at du har kr 12 500 i inntekt – HVIS-setningen returnerer 10 % fordi det er større enn kr 50 000, og det stopper der. Dette kan være utrolig problematisk fordi disse feiltypene i mange situasjoner går ubemerket frem til de har hatt en negativ innvirkning. Så vel vitende om at det er noen alvorlige fallgruver med komplekse nestede HVIS-setninger, hva kan du gjøre? I de fleste tilfeller kan du bruke FINN.OPP-funksjonen i stedet for å bygge en kompleks formel med HVIS-funksjonen. Ved hjelp av FINN.OPPmå du først opprette en referansetabell:

Formelen i celle D2 er =FINN.RAD(C2,C5:D17,2,SANN)
  • =FINN.RAD(C2,C5:D17,2,SANN)

Denne formelen ber om å søke etter verdien i C2 i intervallet C5:C17. Hvis verdien blir funnet, returneres den tilsvarende verdien fra den samme raden i kolonne D.

Formelen i celle C9 er =FINN.RAD(B9,B2:C6,2,SANN)
  • =FINN.RAD(B9,B2:C6,2,SANN)

Denne formelen ser på samme måte etter verdien i celle B9 i intervallet B2:B22. Hvis verdien blir funnet, returneres den tilsvarende verdien fra den samme raden i kolonne C.

Obs!: Begge disse FINN.RAD-ene bruker argumentet SANN på slutten av formlene, noe som betyr at vi vil at de skal se etter et tilnærmet treff. Med andre ord vil det være samsvar med de nøyaktige verdiene i oppslagstabellen, samt eventuelle verdier som faller mellom dem. I dette tilfellet må oppslagstabeller være sortert i stigende rekkefølge, fra minst til størst.

FINN.FORDELING dekkes i mye mer detaljert her,men dette er sikkert mye enklere enn en kompleks nestet HVIS-setning på 12 nivåer! Det finnes også andre mindre opplagte fordeler:

  • FINN.RAD-referansetabeller er åpenbare og lette å se.

  • Tabellverdier kan enkelt oppdateres, og du slipper å røre formelen hvis betingelsene dine endres.

  • Hvis du ikke vil at andre skal se eller forstyrre referansetabellen, må du bare plassere den i et annet regneark.

Visste du at?

Det finnes nå en HVIS.SETT-funksjon som kan erstatte flere, nestede HVIS-setninger med én enkelt funksjon. Så i stedet for det første karakter-eksemplet vårt som har 4 nestede HVIS-funksjoner:

  • =HVIS(D2>89,"A";HVIS(D2>79,"B";HVIS(D2>69,"C";HVIS(D2>59,"D";"F")))

Kan det gjøres mye enklere med én enkel HVIS.SETT-funksjon:

  • =HVIS.SETT(D2>89,"A",D2>79,"B",D2>69,"C",D2>59,"D",SANN,"F")

Funksjonen HVIS.SETT er flott, fordi du ikke trenger å bekymre deg om alle disse HVIS-utsagnene og parentesene.

Obs!: Denne funksjonen er bare tilgjengelig hvis du har et Microsoft 365-abonnement. Hvis du er en Microsoft 365-abonnent, må du forsikre deg om at du har den nyeste versjonen av Office.

Kjøp eller prøv Microsoft 365

Trenger du mer hjelp?

Du kan alltid spørre en ekspert i Excels tekniske fellesskap eller få hjelp i Svar-fellesskapet.

Beslektede emner

Video: Avanserte HVIS-funksjoner
HVIS.SETT (Microsoft 365, Excel 2016 og nyere)
ANTALL.HVIS-funksjonen teller verdier basert på ett enkelt vilkår
ANTALL.HVIS.SETT-funksjonen teller verdier basert på flere vilkår
SUMMERHVIS-funksjonen summerer verdier basert på ett enkelt vilkår
SUMMER.HVIS.SETT-funksjonen summerer verdier basert på flere vilkår
OG (funksjon)
ELLER (funksjon)
FINN.OPP (funksjon)
Oversikt over formler i Excel
Slik unngår du brutte formler
Oppdage feil i formler
Logiske funksjoner
Excel (alfabetisk)
Excel funksjoner (etter kategori)

Trenger du mer hjelp?

Utvid ferdighetene dine
Utforsk opplæring
Vær først ute med de nye funksjonene
Bli med i Microsoft Office Insider-deltakere

Var denne informasjonen nyttig?

Hvor fornøyd er du med språkkvaliteten?
Hva påvirket opplevelsen din?

Takk for tilbakemeldingen!

×