19 tip til indlejrede IF -formler

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

Men det ene IF fører ofte til det andet, 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 HVIS

Inden 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 ... det betyder, at argumenterne er valgfrie. Du skal dog levere enten en værdi for sand eller en værdi for falsk.

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

Celle D3 i eksemplet indeholder denne formel:

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

Hvilket kan læses sådan: Hvis scoren i C3 er mindst 65, returneres '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åede scores kan vi tilføje 'Fail' som det falske argument således:

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

Video: Sådan bygger du logiske formler .

2. Hvad betyder indlejring

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

 
= MONTH ( TODAY ())

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

3. En simpel indlejret IF

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

For at illustrere, nedenfor har jeg udvidet den oprindelige pass/fail formel ovenfor til at håndtere 'ufuldstændige' resultater ved at tilføje en IF -funktion og indlejre en IF inde i den anden:

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

Det ydre IF kører først og tester om C3 er tom. Hvis ja, returnerer ydre IF 'Ufuldstændig', og det indre IF kører aldrig.

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

4. Et indlejret IF for skalaer

Du ser ofte indlejrede IF'er konfigureret til at håndtere 'skalaer' ... for eksempel til at tildele karakterer, forsendelsesomkostninger, skatteprocenter 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, eller det bliver 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, for at tildele karakterer i en 'lav til høj' rækkefølge, kan vi repræsentere den løsning, der er nødvendig 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'.

Når betingelserne er klart forstået, kan vi indtaste den første IF -erklæring:

venstre og højre funktion i excel

 
= IF (C5<64,'F')

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

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

Bemærk, at jeg simpelthen faldt 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, i stedet for at tilføje en anden IF, skal du bare 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 sidste indlejrede IF -formel i aktion:

Video: Sådan laver du 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 først skal løses, for at resten af ​​formlen kan fortsætte.

Indlejrede IF'er har deres eget logiske flow, da de 'ydre' IF'er fungerer som en gateway til 'indre' IF'er. Det betyder, at resultater fra ydre IF'er afgør, om indre IF'er overhovedet kører. Diagrammet nedenfor visualiserede den logiske strøm af karakterformlen ovenfor.

6. Brug Evaluer til at se det logiske flow

I 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 herunder 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).

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

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 kraftfuld måde at bekræfte, hvad en formel virkelig gør. På skærmen herunder bruger jeg skærmspidsvinduerne til at vælge forskellige dele af formlen og derefter klikke på F9 for at se den del løst:

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 nest mange IF'er, det betyder ikke dig bør . Hvert yderligere niveau, du tilføjer, gør formlen vanskeligere at forstå og fejlfinde. Hvis du finder dig selv i at arbejde med et indlejret HVIS mere end et par niveauer dybt, bør du nok tage en anden tilgang - se nedenstående for alternativer.

9. Match parenteser som en proff

En af udfordringerne med indlejrede IF'er er at matche eller 'balancere' parenteser. Når parenteser ikke matcher korrekt, er din formel brudt. Heldigvis giver E xcel et par værktøjer til at hjælpe 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å åbning af parenteser matcher lukning af parenteser. Disse farver er ret svære at se, men de er der, hvis du ser nærmere på:

For det andet (og bedre), når du lukker en parentes, vil Excel kort markere det matchende par. Du kan også klikke ind i formlen og bruge piletasten til at bevæge dig gennem parenteser, og Excel markerer begge parenteser kort, når der er et matchende par. Hvis der ikke er nogen match, ser du ingen fed skrift.

Desværre er fed skrift 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 tekstredigerer ( Tekst Wrangler er gratis og fremragende) for at få bedre parenteser, der matcher værktøjer. Text Wrangler blinker, når parenteser matches, og du kan bruge Command + B til at vælge al tekst, der er indeholdt i parenteser. Du kan indsætte formlen tilbage i Excel, når du har rettet op på tingene.

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

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

Du kan se mig bruge skærmspidsvinduet meget i denne video: Sådan bygger du et indlejret IF .

11. Pas på med tekst og tal

Bare som en hurtig påmindelse, når du arbejder med IF -funktionen, skal du passe på, at du matcher tal og tekst korrekt. Jeg ser ofte formler HVIS sådan:

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

Er testresultatet i A1 virkelig tekst og ikke et tal? Ingen? Brug derefter ikke anførselstegn omkring tallet. Ellers returnerer den logiske test FALSK, selvom værdien er en bestået score, fordi '100' ikke er det samme som 100. Hvis testresultatet er numerisk, skal du bruge dette:

 
= 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 svært at holde tingene lige. Fordi Excel er ligeglad med 'hvidt mellemrum' 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 et indlejret IF, der beregner en provision på basis af et salgstal. Her kan du se den typiske indlejrede IF -struktur, som er svær at tyde:

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

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

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

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

Indlejrede IF'er er kraftfulde, men de bliver hurtigt komplicerede, efterhånden som du tilføjer flere niveauer. En måde at undgå flere niveauer på er at bruge IF i kombination med AND og OR -funktionerne. Disse funktioner giver et simpelt SANDT/FALSKT resultat, der fungerer perfekt inde i IF, så du kan bruge dem til at udvide logikken i et enkelt IF.

For eksempel i nedenstående problem vil vi sætte et 'x' i kolonne D for at markere rækker, hvor farven er 'rød' og størrelsen er 'lille'.

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

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

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 ELLER -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 ville hurtigt blive mere kompleks.

Video: HVIS dette ELLER det

14. Erstat indlejrede IF'er med VLOOKUP

Når et indlejret IF blot tildeler værdier baseret på et enkelt input, kan det 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)

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

Selvom ovenstående formel 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 herunder bruges CHOOSE til at oprette brugerdefinerede hverdagsforkortelser:

Sikkert, du kunne brug en lang og kompliceret indlejret IF til at gøre det samme, men lad være med at gøre det :)

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:

Formlen brugt ovenfor ser sådan ud:

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

Bemærk, vi har kun et par paranteser!

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 på Excel 2007, men kan ikke teste) vil du se '_xlfn.' vedhæftet IFS i cellen. Den tidligere beregnede værdi vil stadig være der, men hvis noget får formlen til at genberegne, ser du en #NAME -fejl. Microsoft har mere information her .

17. Max ud

Nogle gange kan du bruge MAX eller MIN på en meget smart måde, der undgår en IF -erklæring. Antag f.eks., 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 på uden IF, hvor som helst:

 
= MAX (calculation,0)

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

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

18. Trapfejl 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 ind 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 lader dig fange fejl meget mere elegant:

 
= IFERROR (formula,error_result)

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

19. Brug boolsk logik

Du kan også nogle gange undgå indlejrede IF'er ved at bruge det, der kaldes 'boolsk logik'. Ordet boolsk refererer 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 smarte 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 her:

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

Hvert udtryk udfører en test og multiplicerer derefter testens resultat med 'værdien hvis sand'. Da test returnerer enten SAND eller FALSK (1 eller 0), annullerer FALSKE resultaterne effektivt formlen.

For numeriske resultater er boolsk logik enkel og ekstremt hurtig, da der ikke er nogen forgrening. På bagsiden 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 kan du måske undre dig over, hvornår det er fornuftigt at bruge et indlejret IF?

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

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

I dette tilfælde er et indlejret IF en helt 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 herunder.