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

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-uttrykk sammen krever mer oppmerksomhet, slik at de bygges opp riktig. Du må kontrollere at logikken kan kalkulere riktig gjennom hver betingelse helt frem til slutten. Hvis du ikke nester uttrykket 100 prosent nøyaktig, fungerer det kanskje 75 prosent av tiden, men returnerer uventede resultater 25 prosent av tiden. Sannsynligheten for å få øye på de 25 prosentene er liten.

  • 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 testresultatene og bokstavkarakterene vil endre seg. Dermed krever det ikke mye vedlikehold. Her er likevel en tanke – Hva gjør du hvis du trenger å segmentere karakterer mellom A+, A og A- (og så videre)? Nå må de fire betingede HVIS-uttrykkene omskrives til å ha 12 betingelser. Slik ville formelen sett 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 er utrolig lik det andre karaktereksempelet, er denne formelen et godt eksempel på hvor vanskelig det kan være å vedlikeholde store HVIS-uttrykk. Hva ville du gjort hvis organisasjonen bestemte seg for å legge til nye kompensasjonsnivåer og muligens også endre eksisterende penge- eller prosentverdier? Du ville hatt mye jobb foran deg.

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 av inntektsammenligninger med det forrige eksemplet. Hvilken retning går denne i? Helt riktig. Den kommer nedenfra ($ 5 000 til $ 15 000), ikke omvendt. Hva er imidlertid problemet med dette? Problemet er at formelen ikke kan passere første evaluering for verdier over $ 5 000. La oss si at du har $ 12 500 i omsetning – HVIS-uttrykket returnerer 10 prosent fordi det er større enn $ 5000, og det stopper der. Dette kan være veldig problematisk ettersom denne typen feil i mange tilfeller går uoppdaget helt frem til de får en negativ virkning. Hva kan du gjøre når du vet at det er noen alvorlige feller med komplekse nestede HVIS-setninger? I de fleste tilfeller kan du bruke FINN.RAD-funksjonen i stedet for å bygge en komplisert formel med HVIS-funksjonen. Ved hjelp av FINN.RAD, må 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.

Merknad: 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.RAD er dekket i større detalj her, men dette er helt klart mye enklere enn et komplekst nestet HVIS-uttrykk 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.

Merknad: Denne funksjonen er bare tilgjengelig hvis du har et Office 365-abonnement. Hvis du er en Office 365-abonnent, må du kontrollere at du har den nyeste versjonen av Office.

Prøv Office 365 eller den nyeste versjonen av Excel

Trenger du mer hjelp?

Du kan alltid spørre en ekspert i Excel tekniske fellesskap, få støtte i Svar-fellesskapet eller foreslå en ny funksjon eller forbedring på Excel User Voice.

Beslektede emner

VIDEO: Avanserte HVIS-funksjoner
HVIS.SETT-funksjonen (Office 365, Excel 2016 og nyere)
ANTALL.HVIS-funksjonen vil telle verdier basert på ett enkelt vilkår
ANTALL.HVIS.SETT-funksjonen vil telle verdier basert på flere vilkår
SUMMERHVIS-funksjonen vil summere verdier basert på et enkelt vilkår
SUMMER.HVIS.SETT-funksjonen vil summere verdier basert på flere vilkår
OG-funksjonen
ELLER-funksjonen
FINN.RAD-funksjonen
Oversikt over formler i Excel
Unngå formler med feil
Bruk feilkontroll til oppdage feil i formler
Logiske funksjoner
Excel-funksjoner (alfabetisk)
Funksjoner i Excel (etter kategori)

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

Var denne informasjonen nyttig?

Takk for tilbakemeldingen!

Takk for tilbakemeldingen! Det høres ut som det kan være lurt å sette deg i kontakt med én av våre Office-kundestøtteagenter.

×