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-setninger krever en god tanke ting å bygge på riktig måte, og sørger for at logikken kan beregnes riktig gjennom hver enkelt tilstand helt til slutten. Hvis du ikke nester formelen 100% nøyaktig, kan den fungere som 75% av tiden, men returnerer uventede resultater på 25% av tiden. Dessverre er sjansene av deg å snakke med 25% slank.

  • 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 sikkert fordi det ikke er sannsynlig at korrelasjonen mellom test resultater og bokstav karakterer endres, slik at det ikke krever mye vedlikehold. Men her er en tanke – hva hvis du trenger å segmentere karakterer mellom en +, A og A-(og så videre)? Nå er den fire betingelsen hvis setningen må skrives på nytt for å ha 12 betingelser. Slik ser formelen 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 det er remarkably på samme måte som i eksempelet ovenfor, er denne formelen et flott eksempel på hvor vanskelig det kan være å opprettholde store hvis-setninger – hva må du gjøre hvis organisasjonen besluttet å legge til nye kompensasjons nivåer og til og med endre eksisterende dollar-eller prosent verdier? Du har mye arbeid på hendene dine!

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å omsetnings sammenligninger i forrige eksempel. Hvilken måte skal dette gjøre? Det er riktig, den går fra bunn opp ($5 000 til $15 000), ikke den andre veien. Men hvorfor bør det være en slik stor avtale? Det er en stor avtale fordi formelen ikke kan sende den første evalueringen for en verdi over $5 000. La oss si at du har $12 500 i inntekt – hvis-uttrykket vil returnere 10% fordi den er større enn $5 000, og den vil stoppe der. Dette kan være utrolig problematiske fordi det er mange situasjoner at disse feil typene går tapt før de har hatt en negativ innvirkning. Så vet du at det finnes noen alvorlige feller med kompliserte nestede hvis-setninger, hva kan du gjøre? I de fleste tilfeller kan du bruke Finn. rad-funksjonen i stedet for å bygge en kompleks formel med hvis-funksjonen. Hvis du bruker Finn. rad, må du først opprette en referanse tabell:

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. rad er mye mer detaljert,men dette er helt enklere enn et 12-nivås, kompleks nestet hvis-uttrykk. 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.

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: Advanced hvis
functionsHvis (Microsoft 365, Excel 2016 og nyere)
. Hvis-funksjonen vil telle verdier basert på ett enkelt vilkår
Antall-funksjonen teller verdier basert på flere vilkår
funksjonen SUMMERHVIS vil summere verdier basert på ett enkelt vilkår
Summer-funksjonen vil summere verdier basert på flere vilkår
og funksjoner
eller funksjon
Finn. rad-funksjon
Oversikt over formler i Excel
hvordan du unngår brutte formler
oppdage feil i formler
logiske funksjoner
i Excel-funksjoner(alfabetisk)
Excel (etter kategori)

Obs!:  Denne siden er oversatt gjennom automatisering og kan inneholde grammatiske feil eller unøyaktigheter. Formålet vårt er at innholdet skal være nyttig for deg. Kan du fortelle oss om informasjonen var nyttig? Her er den engelske artikkelen for referanse.

Bli bedre på Office
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.

×