Excel

19 tip til indlejrede IF-formler

19 Tips Nested If Formulas

Det HVIS-funktion er en af ​​de mest anvendte funktioner i Excel. IF er en simpel funktion, og folk elsker IF, fordi det giver dem magt til at lave Excel svare som oplysninger indtastes i et regneark. Med IF kan du give dit regneark liv.

Men en IF fører ofte til en anden, og når du kombinerer mere end et par IF'er, kan dine formler begynde at ligne små Frankensteins :)



Er indlejrede IF'er onde? Er de nogle gange nødvendige? Hvad er alternativerne?



Læs videre for at lære svarene på disse spørgsmål og mere ...

1. Grundlæggende IF

Før vi taler om indlejret IF, lad os hurtigt gennemgå den grundlæggende IF-struktur:



 
= IF (test,[true],[false])

IF-funktionen kører en test og udfører forskellige handlinger afhængigt af om resultatet er sandt eller falsk.

Bemærk de firkantede parenteser ... disse betyder, at argumenterne er valgfri. Du skal dog levere enten en værdi for sand eller en værdi for falsk.

For at illustrere bruger vi her IF til at kontrollere scores og beregne 'Pass' for scores på mindst 65:



Grundlæggende IF-funktion - returner

Celle D3 i eksemplet indeholder denne formel:

 
= IF (C3>=65,'Pass')

Hvilket kan læses således: Hvis scoren i C3 er mindst 65, skal du returnere 'Pass'.

Bemærk dog, at hvis scoren er Mindre end 65, IF returnerer FALSK, da vi ikke angav en værdi, hvis den var falsk. For at vise 'Fail' for ikke-bestående scores, kan vi tilføje 'Fail' som det falske argument sådan:

 
= IF (C3>=65,'Pass','Fail')

Grundlæggende IF-funktion - med en værdi tilføjet for falsk

Video: Hvordan man bygger logiske formler .

2. Hvad indlejring betyder

Indlejring betyder simpelthen at kombinere formler, den ene inden i den anden, så den ene formel håndterer resultatet af en anden. For eksempel er her en formel, hvor TODAY-funktionen er indlejret inde i MONTH-funktionen:

 
= MONTH ( TODAY ())

I DAG-funktionen returnerer den aktuelle dato inde i MÅNED-funktionen. MÅNED-funktionen tager den dato og returnerer den aktuelle måned. Selv moderat komplekse formler bruger ofte indlejring, så du kan se indlejring overalt i mere komplekse formler.

3. En simpel nestet IF

En indlejret IF er blot to yderligere IF-udsagn i en formel, hvor den ene IF-sætning vises inde i den anden.

For at illustrere har jeg nedenfor udvidet den oprindelige formel for beståelse / mislykkelse ovenfor for at håndtere 'ufuldstændige' resultater ved at tilføje en IF-funktion og indlejre den ene inden i den anden:

En grundlæggende nestet IF

 
= IF (C3='','Incomplete', IF (C3>=65,'Pass','Fail'))

Den ydre IF kører først og tester for at se, om C3 er tom. Hvis ja, returnerer ydre IF 'ufuldstændig', og den indre IF kører aldrig.

Hvis scoren er ikke blank , returnerer det ydre IF FALSE, og den oprindelige IF-funktion køres.

Lær indlejrede IF'er med klar, kortfattet videotræning .

4. En indlejret IF for skalaer

Du ser ofte indlejrede IF'er, der er indstillet til at håndtere 'skalaer' ... for eksempel til at tildele karakterer, forsendelsesomkostninger, afgiftssatser eller andre værdier, der varierer på en skala med et numerisk input. Så længe der ikke er for mange niveauer i skalaen, fungerer indlejrede IF'er fint her, men du skal holde formlen organiseret, ellers bliver det svært at læse.

Tricket er at bestemme en retning (høj til lav eller lav til høj) og derefter strukturere forholdene i overensstemmelse hermed. For eksempel kan vi tildele karakterer i en 'lav til høj' rækkefølge, og vi kan repræsentere den nødvendige løsning i følgende tabel. Bemærk, at der ikke er nogen betingelse for 'A', for når vi har gennemgået alle de andre betingelser, ved vi, at scoren skal være større end 95, og derfor et 'A'.

Score karakter Tilstand
0-63 F <64
64-72 D <73
73-84 C <85
85-94 B <95
95-100 TIL

Med de betingelser, der er klart forstået, kan vi gå ind i den første IF-sætning:

venstre og højre funktion i excel
 
= IF (C5<64,'F')

Dette tager sig af 'F'. For at håndtere 'D' skal vi nu tilføje en anden betingelse:

 
= IF (C5<64,'F', IF (C5<73,'D'))

Bemærk, at jeg simpelthen droppede en anden IF i den første IF for det 'falske' resultat. For at udvide formlen til at håndtere 'C' gentager vi processen:

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C')))

Vi fortsætter på denne måde, indtil vi når den sidste klasse. Derefter skal du i stedet for at tilføje endnu en IF tilføje den endelige karakter for falsk.

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))

Her er den endelige nestede IF-formel i aktion:

Fuldført indlejret IF-eksempel til beregning af karakterer

Video: Hvordan man laver et indlejret IF til at tildele karakterer

5. Indlejrede IF'er har et logisk flow

Mange formler løses indefra og ud, fordi 'indre' funktioner eller udtryk skal løses først for at resten af ​​formlen skal fortsætte.

Indlejrede IF'er har deres egen logiske strøm, da de 'ydre' IF'er fungerer som en gateway til 'indre' IF'er. Dette betyder, at resultater fra ydre IF'er bestemmer, om indre IF'er endda kører. Diagrammet nedenfor visualiserede den logiske strøm af karakterformlen ovenfor.

Den logiske strøm af et indlejret IF

6. Brug Evaluer til at se det logiske flow

På Windows kan du bruge Evaluer funktion for at se Excel løse dine formler trin for trin. Dette er en fantastisk måde at 'se' den logiske strøm af mere komplekse formler og at foretage fejlfinding, når tingene ikke fungerer, som du forventer. Skærmen nedenfor viser vinduet Evaluer åbent og klar til brug. Hver gang du klikker på knappen Evaluer, løses 'næste trin' i formlen. Du kan finde Evaluer på fanen Formler på båndet (Alt M, V).

Brug Evaluere til at gå gennem et indlejret IF, der tildeler karakterer

Desværre indeholder Mac-versionen af ​​Excel ikke funktionen Evaluer, men du kan stadig bruge F9-tricket nedenfor.

7. Brug F9 til at kontrollere resultaterne

Når du vælger et udtryk i formellinjen og trykker på F9-tasten, løser Excel kun den valgte del. Dette er en effektiv måde at bekræfte, hvad en formel virkelig gør. På skærmen nedenfor bruger jeg skærmtipsvinduerne til at vælge forskellige dele af formlen og derefter klikke på F9 for at se den del løst:

Brug F9 til at kontrollere et indlejret IF, der tildeler karakterer

Brug Control + Z (Command + Z) på en Mac til at fortryde F9. Du kan også trykke på Esc for at afslutte formeleditoren uden ændringer.

Video: Sådan debugger du en formel med F9

8. Kend dine grænser

Excel har grænser for, hvor dybt du kan rede IF-funktioner. Op til Excel 2007 tillod Excel op til 7 niveauer af indlejrede IF'er. I Excel 2007+ tillader Excel op til 64 niveauer.

Dog bare fordi du kan rede mange IF'er, det betyder ikke dig skulle gerne . Hvert ekstra niveau, du tilføjer, gør formlen vanskeligere at forstå og fejlfinde. Hvis du finder dig selv i at arbejde med en indlejret IF, der er mere end et par niveauer dybt, bør du sandsynligvis tage en anden tilgang - se nedenstående for alternativer.

9. Match parenteser som en professionel

En af udfordringerne med indlejrede IF'er er at matche eller 'balancere' parenteser. Når parenteser ikke matches korrekt, er din formel brudt. Heldigvis giver E xcel et par værktøjer, der hjælper dig med at sikre, at parenteser er 'afbalancerede', mens du redigerer formler.

For det første, når du har mere end et sæt parenteser, er parenteserne farvekodede, så de åbne parenteser matcher de lukkede parenteser. Disse farver er ret svære at se, men de er der, hvis man ser nøje:

Formel parenteser er farvematchede, men svære at se

For det andet (og bedre), når du lukker parenteser, vil Excel kort trække det matchende par. Du kan også klikke på formlen og bruge piletasten til at bevæge sig mellem parenteser, og Excel vil kort fed begge parenteser, når der er et matchende par. Hvis der ikke er noget match, ser du ingen fed skrift.

Desværre er fed skrift kun en Windows-funktion. Hvis du bruger Excel på en Mac til at redigere komplekse formler, er det nogle gange fornuftigt at kopiere og indsætte formlen i en god teksteditor ( Tekst Wrangler er gratis og fremragende) for at få bedre parentes matchende værktøjer. Text Wrangler vil blinke, når parenteser matches, og du kan bruge Kommando + B til at vælge al tekst indeholdt i parentes. Du kan indsætte formlen i Excel, efter at du har rettet tingene ud.

10. Brug skærmtipsvinduet til at navigere og vælge

Når det kommer til at navigere og redigere indlejrede IF'er, er tip til funktionsskærm din bedste ven. Med det kan du navigere og præcist vælge alle argumenter i et indlejret IF:

Naviger og vælg formelargumenter med skærmtip

Du kan se mig bruge skærmtipsvinduet meget i denne video: Hvordan man bygger et indlejret IF .

11. Pas på med tekst og tal

Ligesom en hurtig påmindelse, når du arbejder med IF-funktionen, skal du sørge for, at du har en korrekt matchende tal og tekst. Jeg ser ofte formler, HVIS som denne:

 
= IF (A1='100','Pass','Fail')

Er testresultatet i A1 virkelig tekst og ikke et nummer? Ingen? Brug derefter ikke anførselstegn omkring nummeret. Ellers returnerer den logiske test FALSK, selv når værdien er en bestået score, fordi '100' ikke er den samme som 100. Hvis testscoren er numerisk, skal du bruge denne:

 
= IF (A1=100,'Pass','Fail')

12. Tilføj linjeskift gør indlejrede IF'er lette at læse

Når du arbejder med en formel, der indeholder mange niveauer af indlejrede IF'er, kan det være vanskeligt at holde tingene lige. Da Excel ikke er ligeglad med 'hvidt rum' i formler (dvs. ekstra mellemrum eller linjeskift), kan du i høj grad forbedre læsbarheden af ​​indlejrede ifs ved at tilføje linjeskift.

For eksempel viser skærmen nedenfor en indlejret IF, der beregner en provision på basis af et salgsnummer. Her kan du se den typiske indlejrede IF-struktur, som er svær at dechifrere:

Indlejrede IF'er uden linjeskift er vanskelige at læse

Men hvis jeg tilføjer linjeskift før hver 'værdi, hvis falsk', springer formelens logik klart ud. Plus, formlen er lettere at redigere:

Linjeskift gør indlejrede IF'er lettere at læse

Du kan tilføje linjeskift på Windows med Alt + Enter, på en Mac, brug Control + Option + Return.

Video: Sådan gør du en indlejret HVIS lettere at læse .

13. Begræns IF'er med AND og OR

Indlejrede IF'er er stærke, men de bliver hurtigt komplicerede, når du tilføjer flere niveauer. En måde at undgå flere niveauer er at bruge IF i kombination med AND- og ELLER-funktionerne. Disse funktioner returnerer et simpelt TRUE / FALSE-resultat, der fungerer perfekt inden i IF, så du kan bruge dem til at udvide logikken for en enkelt IF.

For eksempel i problemet nedenfor vil vi placere et 'x' i kolonne D for at markere rækker, hvor farven er 'rød' og størrelsen er 'lille'.

IF med AND-funktionen er enklere end to indlejrede IF'er

Vi kunne skrive formlen med to indlejrede IF'er som denne:

 
= IF (B6='red', IF (C6='small','x',''),'')

Men ved at erstatte testen med AND-funktionen kan vi forenkle formlen:

 
= IF ( AND (B6='red',C6='small'),'x','')

På samme måde kan vi let udvide denne formel med OR-funktionen for at kontrollere for rød ELLER blå OG lille:

 
= IF ( AND ( OR (B4='red',B4='blue'),C4='small'),'x','')

Alt det her kunne gøres med indlejrede IF'er, men formlen bliver hurtigt mere kompleks.

Video: HVIS dette ELLER det

14. Erstat Nested IFs med VLOOKUP

Når en indlejret IF simpelthen tildeler værdier baseret på en enkelt input, kan den let erstattes med VLOOKUP-funktion . For eksempel tildeler dette indlejrede IF tal til fem forskellige farver:

 
= IF (E3='red',100, IF (E3='blue',200, IF (E3='green',300, IF (E3='orange',400,500))))

Vi kan nemt erstatte det med denne (meget enklere) VLOOKUP:

 
= VLOOKUP (E3,B3:C7,2,0)

Nestet IF vs VLOOKUP

Som en bonus beholder VLOOKUP værdier på regnearket (hvor de let kan ændres) i stedet for at integrere dem i formlen.

Selvom formlen ovenfor bruger nøjagtig matchning, kan du nemt bruge den VLOOKUP for karakterer såvel.

Se også: 23 ting at vide om VLOOKUP

Video: Sådan bruges VLOOKUP

Video: Hvorfor VLOOKUP er bedre end indlejrede IF'er

15. Vælg VÆLG

VÆLG-funktionen kan give en elegant løsning, når du skal kortlægge enkle, fortløbende tal (1,2,3 osv.) Til vilkårlige værdier.

I eksemplet nedenfor bruges VÆLG til at oprette brugerdefinerede forkortelser på hverdage:

Indlejret IF vs VÆLG-funktionen

Sikker på, du kunne Brug en lang og kompliceret indlejret HVIS til at gøre det samme, men vær venlig ikke :)

16. Brug IFS i stedet for indlejrede IF'er

Hvis du bruger Excel 2016 via Office 365, er der en ny funktion, du kan bruge i stedet for indlejrede IF'er: IFS-funktionen. IFS-funktionen giver en særlig struktur til evaluering af flere forhold uden indlejring:

IFS-funktionen - flere forhold uden indlejring

Ovenstående formel ser sådan ud:

 
= IFS (D5<60,'F',D5<70,'D',D5<80,'C',D5<90,'B',D5>=90,'A')

Bemærk, at vi kun har et par parenteser!

Hvad sker der, når du åbner et regneark, der bruger IFS-funktionen i en ældre version af Excel? I Excel 2013 og 2010 (og jeg tror, ​​Excel 2007, men kan ikke teste), vil du se '_xlfn.' vedhæftet til IFS i cellen. Den tidligere beregnede værdi vil stadig være der, men hvis noget får formlen til at genberegne, vil du se en #NAME-fejl. Microsoft har flere oplysninger her .

17. Max ud

Nogle gange kan du bruge MAX eller MIN på en meget smart måde, der undgår en IF-sætning. Antag for eksempel, at du har en beregning, der skal resultere i et positivt tal eller nul. Med andre ord, hvis beregningen returnerer et negativt tal, vil du bare vise nul.

MAX-funktionen giver dig en smart måde at gøre dette uden IF, hvor som helst i syne:

 
= MAX (calculation,0)

Denne teknik returnerer resultatet af beregningen, hvis den er positiv, og ellers nul.

Jeg elsker denne konstruktion, fordi den er bare så simpelt . Se denne artikel for en fuldstændig opskrivning .

18. Fældefejl med IFERROR

En klassisk brug af IF er at fange fejl og levere et andet resultat, når en fejl kastes, som denne:

 
= IF ( ISERROR (formula),error_result,formula)

Dette er grimt og overflødigt, da den samme formel går to gange, og Excel skal beregne det samme resultat to gange, når der ikke er nogen fejl.

I Excel 2007 blev IFERROR-funktionen introduceret, som giver dig mulighed for at fange fejl meget mere elegant:

 
= IFERROR (formula,error_result)

Når formlen nu kaster en fejl, returnerer IFERROR simpelthen den værdi, du angiver.

19. Brug boolsk logik

Du kan også undertiden undgå indlejrede IF'er ved at bruge det, der kaldes 'boolsk logik'. Ordet boolsk henviser til SAND / FALSK-værdier. Selvom Excel viser ordene SAND og FALSK i celler, behandler Excel internt SAND som 1 og FALSK som nul. Du kan bruge denne kendsgerning til at skrive kloge og meget hurtige formler. For eksempel i VLOOKUP-eksemplet ovenfor har vi en indlejret IF-formel, der ser sådan ud:

 
= IF (E3='red',100, IF (E3='blue',200, IF (E3='green',300, IF (E3='orange',400,500))))

Ved hjælp af boolsk logik kan du omskrive formlen sådan:

 
=(E3='red')*100+(E3='blue')*200+(E3='green')*300+(E3='orange')*400+(E3='purple')*500

Hvert udtryk udfører en test og multiplicerer derefter resultatet af testen med 'værdien, hvis den er sand'. Da tests returnerer enten SAND eller FALSK (1 eller 0), annullerer FALSE-resultaterne sig selv formlen.

For numeriske resultater er boolsk logik enkel og ekstrem hurtig, da der ikke er nogen forgrening. På ulempen kan boolsk logik være forvirrende for folk, der ikke er vant til at se det. Alligevel er det en fantastisk teknik at vide om.

Video: Sådan bruges boolsk logik i Excel-formler

Hvornår har du brug for et indlejret IF?

Med alle disse muligheder for at undgå indlejrede IF'er, undrer du dig måske over, hvornår det giver mening at bruge en indlejret IF?

Jeg tror, ​​at indlejrede IF'er giver mening, når du har brug for at evaluere flere forskellige indgange at træffe en beslutning.

Antag for eksempel, at du vil beregne en fakturastatus for 'Betalt', 'Åben', 'Forsinket' osv. Dette kræver, at du ser på fakturaalderen og udestående saldo:

Beregning af fakturastatus med et indlejret IF

I dette tilfælde er en indlejret IF en perfekt fin løsning.

Dine tanker?

Hvad med dig? Er du en IF-ster? Undgår du indlejrede IF'er? Er indlejrede IF'er onde? Del dine tanker nedenfor.

Lær Excel-formler hurtigt med kortfattet videotræning . Forfatter Dave Bruns


^