HVIS-formler i Excel: fra én betingelse til mange

HVIS er den funktion, der lader et regneark træffe beslutninger. Skal ordren sendes gratis? Bestod eleven? Er fakturaen forfalden, og hvor meget? Næsten alle rigtige projektmapper har en HVIS et sted, og næsten alle Excel-test har mindst ét spørgsmål, der kræver den.

HVIS er også dér, hvor mange formler begynder at gå stille og roligt galt. En indlejret HVIS i den forkerte rækkefølge, et tal i anførselstegn eller et manglende tredje argument giver et svar, der ser fint ud, men ikke er det. Denne guide starter med den grundlæggende form og bygger op til flere betingelser, flere udfald og de alternativer, der ofte er bedre end endnu en HVIS. Undervejs gennemgår den de fejl, der står bag de fleste forkerte svar, så du kan få øje på dem i dine egne regneark.

Sådan virker HVIS

HVIS tager tre argumenter: en test, en værdi, hvis testen er sand, og en værdi, hvis den er falsk.

=HVIS(B2>=60;"Bestået";"Ikke bestået")

Den letteste måde at læse en HVIS på er som en sætning: Hvis B2 er mindst 60, så vis Bestået, ellers vis Ikke bestået. Kan du ikke sige din formel højt i én sætning på den måde, skal den sandsynligvis deles op eller skrives om.

De tre dele er:

• logisk_test: alt, der enten er sandt eller falsk. Som regel en sammenligning som B2>=60, men det kan også være en funktion, der returnerer SAND eller FALSK, for eksempel ER.TOM eller OG.
• værdi_hvis_sand: det, cellen viser, når testen er sand.
• værdi_hvis_falsk: det, cellen viser, når den er falsk.

Øv den grundlæggende form i bestået eller ikke bestået med HVIS.

Sådan skriver du testen

Det er i testen, de fleste HVIS-fejl opstår, så det betaler sig at være præcis. Excel har seks sammenligningsoperatorer:

Det svære er ikke tegnene, men at oversætte ord til dem. "Mindst 60" tager 60 med, så det er >=. "Mere end 60" gør ikke, så det er >. "Op til 30" er <=, og "under 30" er <. Test og krav fra virkeligheden er fulde af de ord, og en formel med >, hvor opgaven sagde "mindst", er forkert for præcis én værdi, nemlig den værdi, ingen tjekker. Når en grænse betyder noget, så test grænsen selv: Skriv 60 i cellen, og se, hvad formlen siger.

To regler mere om testen:

• Tekst skal stå i anførselstegn, tal og cellereferencer skal ikke. C2="DK" sammenligner med teksten DK. C2=DK får Excel til at lede efter et navn, der hedder DK, og giver #NAVN?.
• Tekstsammenligninger skelner ikke mellem store og små bogstaver. C2="dk" er sand, når der står DK, Dk eller dk i C2. Har du brug for et præcist match, hvor store og små bogstaver tæller, så brug EKSAKT i testen.

Hvad HVIS kan returnere

Det andet og tredje argument kan være næsten hvad som helst: tekst, et tal, en cellereference, en hel beregning eller en anden funktion.

Det er, når HVIS returnerer en beregning i stedet for tekst, at den for alvor gør nytte. En provision, der kun gives over en bestemt grænse, er én HVIS:

Læs den sådan: Hvis salget er over 10.000, så returnér salget gange 5 %, ellers returnér 0. Bonusopgaven træner samme idé.

Et par returværdier fortjener en kommentar:

• En celle, der ser tom ud: Brug "", to anførselstegn uden noget imellem. =HVIS(B2>10000;B2*0,05;"") viser ingenting, når der ikke er nogen provision. Husk, at cellen ikke er helt tom. Den indeholder en tom tekst, som TÆLV tæller med, og som nogle formler behandler anderledes end en tom celle.
• Nul: Returnér 0, når resultatet skal bruges i andre beregninger. En kolonne med tal og enkelte "" kan få formler, der ganger eller sammenligner, til at gå i stykker.
• Ingenting: Udelader du det tredje argument, =HVIS(B2>10000;B2*0,05), returnerer HVIS FALSK, når testen er falsk. Det er næsten aldrig det, du vil have i en rapport, og det er svaret på det klassiske spørgsmål "hvorfor står der FALSK?".

To betingelser, der begge skal være opfyldt: OG

Regler i virkeligheden har ofte mere end én betingelse. "Gratis fragt ved køb for mindst 500 kr. til en dansk adresse" er to test, og begge skal bestås. Det er det, OG er til.

OG tager et vilkårligt antal test og returnerer kun SAND, hvis alle er sande. Sæt den ind som det første argument i HVIS:

Fejlen, du skal undgå, er at skrive betingelsen, sådan som du ville sige den. Excel forstår ikke B2>=500 OG C2="DK" som en test og afviser formlen, før den overhovedet bliver beregnet. OG er en funktion, så testene skal stå inde i dens parenteser, adskilt af semikolon. Øv det i to betingelser med OG.

Én af betingelserne: ELLER

ELLER er makker til OG. Den returnerer SAND, hvis mindst én af dens test er sand, og kun FALSK, når de alle er falske. Brug den, når én af flere grunde er nok:

Her bliver en sag eskaleret, hvis den er markeret som hastende, eller hvis den har været åben i mere end en uge. Den her haster ikke, men den er ni dage gammel, så den bliver eskaleret. Opgaven med ELLER har samme opbygning.

En god måde at tjekke OG og ELLER på er at spørge, hvad der sker i yderpunkterne. Med OG gør én fejlet test det hele falsk. Med ELLER gør én bestået test det hele sandt. Opfører din formel sig omvendt, har du brugt den forkerte.

IKKE, og OG og ELLER sammen

IKKE vender en test om: IKKE(C2="DK") er sand for alle lande undtagen Danmark. I praksis siger C2<>"DK" det samme lettere, så IKKE er mest nyttig rundt om en funktion, for eksempel IKKE(ER.TOM(B2)).

Du kan også kombinere OG og ELLER i samme test. "Godkend, hvis beløbet er under 500, eller hvis det er under 5.000, og en leder har godkendt det" bliver til:

=HVIS(ELLER(B2<500;OG(B2<5000;C2="Ja"));"Godkend";"Gennemse")

Byg en formel som den her indefra og ud. Skriv og test OG for sig i en ledig celle, pak den derefter ind i ELLER, og pak til sidst det hele ind i HVIS. Opgaven OG og ELLER kombineret tager dig gennem en af dem.

Mere end to udfald: indlejret HVIS

HVIS har to udfald. Når du har brug for tre eller flere, for eksempel kundeniveauer, karakterer eller fragtintervaller, er den klassiske løsning at sætte en ny HVIS ind som den første HVIS' værdi_hvis_falsk:

Læs den udefra og ind: Hvis købet er på mindst 100.000, så Guld. Ellers, hvis købet er på mindst 50.000, så Sølv. Ellers Bronze. Excel stopper ved den første test, der er sand, og ser aldrig på resten. Derfor betyder rækkefølgen så meget.

Sæt det højeste interval først, når du bruger >=. Se, hvad der sker, hvis du starter med det laveste i stedet:

=HVIS(B2>=50000;"Sølv";HVIS(B2>=100000;"Guld";"Bronze"))

En kunde, der har købt for 200.000, består den første test, B2>=50000, og får Sølv. Testen for Guld bliver aldrig nået. Der kommer ingen fejl, og alle kunder over 50.000 havner i stilhed på det forkerte niveau. Når du skriver intervaller, så gå enten oppefra med >= eller nedefra med <, og test én værdi i hvert interval, før du kopierer formlen ned.

Opgaven med indlejret HVIS beder om tre udfald, og det er cirka dér, indlejrede HVIS holder op med at være behagelige. Excel tillader op til 64 niveauer, men ingen kan læse en formel med ti slutparenteser til sidst, heller ikke dig selv om et halvt år.

HVISER: den læsbare kæde

Excel 2019 og Microsoft 365 har HVISER, som tager par af test og resultater og returnerer resultatet af den første sande test. Niveauerne fra forrige afsnit bliver til:

Den gør det samme uden indlejringen: test, resultat, test, resultat. To ting skal du vide:

• HVISER har intet "ellers"-argument. Er ingen test sand, returnerer den #I/T. Det er almindeligt at slutte med SAND som sidste test. Den er altid sand, så det sidste resultat fungerer som ellers-værdien.
• Rækkefølgen betyder stadig noget. HVISER stopper ved den første sande test, præcis som indlejret HVIS, så det højeste interval skal stadig stå først.

Øv HVISER med karakterer og fragtpriser i intervaller. Arbejder du sammen med nogen, der stadig bruger Excel 2016, ser de #NAVN? i stedet for dit resultat, så tjek det, før du bygger en delt projektmappe på den.

SKIFT: når du matcher faste værdier

Når beslutningen er "hvis koden er N, så vis Nord, og hvis den er S, så vis Syd", tester du ikke intervaller, men matcher faste værdier. Det er SKIFT lavet til:

SKIFT tager den værdi, der skal tjekkes, derefter par af værdier og resultater og et valgfrit sidste argument til, når intet passer. Den er kortere end den tilsvarende HVISER, fordi du ikke skal gentage B2= i hver test. Prøv den i lav koder om til navne med SKIFT.

Når en opslagstabel slår enhver HVIS

Indlejret HVIS, HVISER og SKIFT lægger alle forretningsreglerne ind i selve formlen. Det er fint for tre niveauer, der aldrig ændrer sig. Det bliver et problem, når der er ti intervaller, eller når grænserne ændrer sig hvert år, for så skal nogen rette hver eneste kopi af formlen og ramme hvert eneste tal rigtigt.

Alternativet er at lægge intervallerne i en lille tabel i regnearket og slå op i den. Til grænser finder LOPSLAG med SAND det interval, en værdi falder i, så længe første kolonne er sorteret fra mindst til størst:

=LOPSLAG(B2;$F$2:$G$4;2;SAND)

Med 0, 50.000 og 100.000 i F2:F4 og Bronze, Sølv og Guld ved siden af giver den samme svar som den indlejrede HVIS, og ændrer en grænse sig, retter du én celle. XOPSLAG gør det samme med match_tilstand -1. Vores guide til opslag gennemgår opslag i intervaller i detaljer, og opgaven med tilnærmet match træner det.

En god tommelfingerregel: Kan reglen siges i én sætning og har højst tre udfald, så brug HVIS eller HVISER. Har den flere udfald, eller vil tallene i den sandsynligvis ændre sig, så brug en tabel.

HVIS og tomme celler

Data har ofte huller: en manglende mailadresse, en leveringsdato, der ikke er udfyldt endnu, eller et pointtal for en elev, der var fraværende. En formel, der overser dem, giver misvisende resultater, for eksempel en advarsel om forsinkelse på en ordre, der slet ikke er sendt endnu.

ER.TOM returnerer SAND, når en celle er tom, og er en naturlig første test:

Du kan også skrive testen som B2="", der er sand for en tom celle og for en celle med en tom tekst, for eksempel den "", en anden HVIS returnerer. ER.TOM er falsk for sådan en tom tekst, så er kolonnen fyldt ud med formler, er B2="" som regel den sikreste test. Opgaven med manglende værdier øver versionen med ER.TOM.

Et almindeligt mønster er at sætte tjekket for tomme celler først og den egentlige beregning bagefter, så beregningen kun kører, når der er data: =HVIS(B2="";"";B2*1,25).

HVIS og en del af en tekst

"Hvis emnet indeholder ordet refusion" lyder som en opgave for HVIS med et jokertegn, men HVIS forstår ikke jokertegn. =HVIS(B2="*refusion*";"Refusion";"Andet") sammenligner B2 med teksten *refusion*, som den står, og er næsten altid falsk.

Tricket er SØG, der returnerer placeringen af én tekst i en anden, eller fejlen #VÆRDI!, når den ikke findes. ER.TAL laver det om til SAND eller FALSK:

SØG skelner ikke mellem store og små bogstaver, og det er som regel det, du vil have, når du leder efter ord. Brug FIND i stedet, når store og små bogstaver betyder noget, for eksempel i varenumre. Opgaven med et ord i en tekst træner mønsteret. Skal du bare tælle, hvor mange celler der indeholder et ord, forstår TÆL.HVIS faktisk jokertegn: =TÆL.HVIS(B2:B100;"*refusion*").

HVIS og fejl

Nogle gange er testen ikke problemet, men beregningen. En pris pr. stk. dividerer omsætningen med antallet, og en række med 0 stk. giver #DIVISION/0!. Du kunne skrive =HVIS(C2=0;0;B2/C2), og det er en helt fin formel. HVIS.FEJL er kortere, når enhver fejl skal erstattes:

=HVIS.FEJL(B2/C2;0)

HVIS.FEJL returnerer beregningens resultat, hvis der ikke er nogen fejl, og sit andet argument, hvis der er. Den bruges også tit rundt om opslag: =HVIS.FEJL(LOPSLAG(D2;A2:B6;2;FALSK);"Ukendt"). Øv begge i undgå #DIVISION/0! og skjul #I/T.

Brug HVIS.FEJL med omtanke, ikke som et tæppe over det hele. Den skjuler alle fejl, også dem, der betyder, at din formel er gået i stykker, for eksempel en slettet kolonne eller en tastefejl i et område. Forventer du kun #I/T fra et opslag, fanger HVISIT netop den fejl og lader de andre vise sig, så en rigtig fejl stadig springer i øjnene.

HVIS på en hel kolonne

Som regel skriver du HVIS én gang øverst i en hjælpekolonne og kopierer den ned med Ctrl+D eller et dobbeltklik på fyldhåndtaget. Hver række får sin egen kopi med referencerne flyttet til netop den række. Peger formlen på en fast celle, for eksempel en grænse i F1, så lås den med $: =HVIS(B2>=$F$1;"Bestået";"Ikke bestået"). Uden dollartegnene sammenligner anden række med F2, der er tom, og alle rækkerne derfra bliver bedømt i forhold til nul.

Er det, du egentlig vil have, et antal eller en total for de rækker, der opfylder en betingelse, har du måske slet ikke brug for hjælpekolonnen. "Hvor mange bestod?" er =TÆL.HVIS(B2:B30;">=60"), og "samlet provision over 10.000" er SUM.HVIS. En hjælpekolonne med HVIS er god til at vise beslutningen på hver række. Til ét samlet tal er den en omvej.

Fejl, der giver forkerte svar uden en fejlmeddelelse

Det er de HVIS-problemer, der koster mest, fordi Excel ikke markerer nogen af dem:

• Intervaller i den forkerte rækkefølge. Den første sande test vinder. Start oppefra med >= eller nedefra med <, aldrig en blanding.
• Den forkerte operator ved grænsen. >, hvor reglen siger "mindst". Test selve grænseværdien.
• Tal i anførselstegn. =HVIS(B2>="60";"Bestået";"Ikke bestået") sammenligner et tal med teksten "60". Tekst regnes for større end ethvert tal, så testen er falsk for alle pointtal, og ingen består. Anførselstegn er kun til tekst.
• Tal gemt som tekst. Det omvendte problem: Står der teksten "75" i B2 efter en import, er B2>=60 sand for enhver værdi, fordi tekst er større end ethvert tal. Tal gemt som tekst står som regel i venstre side af cellen. Lav dem om, før du stoler på nogen sammenligning.
• Et manglende tredje argument, der viser FALSK i rapporten.
• Faste grænser skrevet ind i hundredvis af formler. Læg dem i en celle, og referér til den med $, så en ændring er én rettelse.
• HVIS.FEJL rundt om det hele, der skjuler de fejl, du havde brug for at se.
• SAND og FALSK i anførselstegn. "SAND" er tekst, ikke den logiske værdi. Skal et resultat bruges i en anden test, så returnér SAND eller FALSK uden anførselstegn.

HVIS i Excel-test

HVIS og dens makkere optræder i næsten alle arbejdsgiveres Excel-test, som regel i en af disse former:

• En simpel beslutning: "Vis Ja i D2, hvis målet blev nået." Én HVIS med en sammenligning, ofte med et mål i en anden celle.
• To betingelser: "Markér medarbejdere med mindst fem års anciennitet og en vurdering på 4 eller mere." OG inden i HVIS. Hold øje med ordene "mindst" og "mere end".
• Flere udfald: "Giv en karakter: A for 90 og derover, B for 80 og derover …" Indlejret HVIS eller HVISER, højeste interval først.
• En beregning i det ene udfald: "Beregn en bonus på 10 % for salg over 50.000, ellers 0." HVIS, der returnerer en beregning.
• Fejlhåndtering: "Vis 0 i stedet for en fejl." HVIS.FEJL rundt om en division eller et opslag.

Læs opgaven to gange, og kig efter ordene ved grænserne og den præcise tekst, der skal returneres. Står der, at du skal vise "Ja", kan en formel, der viser "ja" eller "J", blive regnet for forkert, alt efter testen. Tjek én række på hver side af hver grænse, før du går videre. Vores guide til jobsamtalen fortæller mere om, hvordan tidspresset ændrer den måde, du arbejder på.

Opsummering

HVIS træffer en beslutning: en test, et resultat, hvis den er sand, og et resultat, hvis den er falsk. OG og ELLER samler flere test til én. Til tre eller flere udfald kan du indlejre HVIS eller bruge HVISER, og uanset hvad skal det højeste interval stå først, fordi den første sande test vinder. SKIFT er til faste koder, og en opslagstabel er bedre end enhver HVIS, så snart der er mange intervaller, eller grænserne ændrer sig.

De fleste HVIS-fejl er tavse: den forkerte operator ved en grænse, et tal i anførselstegn eller intervaller i den forkerte rækkefølge. Sig hver formel højt som en sætning, test grænseværdierne, så gør opgaverne nedenfor mønstrene til rygmarvsrefleks.