Excel

19 nasvetov za ugnezdene formule IF

19 Tips Nested If Formulas

The IF funkcija je ena izmed najbolj pogosto uporabljenih funkcij v Excelu. IF je preprosta funkcija in ljudje imajo radi IF, ker jim daje moč, da naredijo Excel odzvati ko so podatki vneseni v preglednico. Z IF lahko oživite svojo preglednico.



Toda en IF pogosto vodi v drugega in ko združite več kot nekaj IF, bodo vaše formule lahko začele izgledati kot mali Frankensteini :)

Ali so ugnezdeni IF zli? So včasih potrebni? Kakšne so alternative?





Preberite, če želite izvedeti odgovore na ta vprašanja in še več ...

1. Osnovni IF

Preden govorimo o ugnezdenih IF, si na hitro poglejmo osnovno strukturo IF:



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

Funkcija IF izvede preskus in izvede različna dejanja, odvisno od tega, ali je rezultat resničen ali napačen.

Upoštevajte oglate oklepaje ... to pomeni, da so argumenti neobvezni. Vendar morate zagotoviti bodisi vrednost za true ali vrednost za false.

Za ponazoritev tukaj uporabljamo IF za preverjanje rezultatov in izračun 'Pass' za ocene najmanj 65:

Osnovna funkcija IF - vrnitev

Celica D3 v primeru vsebuje to formulo:

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

Kar lahko beremo tako: če je rezultat v C3 najmanj 65, vrnite 'Pass'.

Upoštevajte pa, da če je rezultat manj kot 65, IF vrne vrednost FALSE, ker vrednosti nismo dali, če je false. Za prikaz »Neuspeh« za ocene, ki ne prehajajo, lahko kot napačen argument dodamo »Neuspeh«:

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

Osnovna funkcija IF - z dodano vrednostjo za false

Video: Kako zgraditi logične formule .

2. Kaj pomeni gnezdenje

Gnezdenje preprosto pomeni združevanje formul ena v drugi, tako da ena formula obravnava rezultat druge. Na primer, tukaj je formula, kjer je funkcija TODAY ugnezdena v funkcijo MONTH:

 
= MONTH ( TODAY ())

Funkcija TODAY vrne trenutni datum znotraj funkcije MONTH. Funkcija MONTH vzame ta datum in vrne trenutni mesec. Tudi zmerno zapletene formule pogosto uporabljajo gnezdenje, zato boste gnezdenja povsod videli v bolj zapletenih formulah.

3. Preprost ugnezden IF

Gnezdeni IF sta le še dve stavki IF v formuli, kjer se en stavek IF pojavi v drugem.

Za ponazoritev, spodaj sem razširil prvotno formulo uspeha/neuspeha, da bi obravnaval 'nepopolne' rezultate, tako da sem dodal funkcijo IF in vstavil eno IF v drugo:

Osnovni ugnezdeni IF

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

Zunanji IF se najprej zažene in preveri, ali je C3 prazen. Če je tako, zunanji IF vrne 'Incomplete', notranji IF pa nikoli ne zažene.

Če je rezultat ne prazno , zunanji IF vrne FALSE in izvede se prvotna funkcija IF.

Naučite se ugnezdenih IF z jasen, jedrnat video trening .

4. Vgnezdeni IF za tehtnice

Pogosto boste videli ugnezdene IF -je, ki so nastavljeni za obravnavo 'lestvic' ... na primer za dodelitev ocen, stroškov pošiljanja, davčnih stopenj ali drugih vrednosti, ki se razlikujejo na lestvici z numeričnim vnosom. Dokler na lestvici ni preveč ravni, se gnezdeni IF tukaj dobro obnesejo, vendar morate formulo držati organizirano, sicer bo težko brati.

Trik je v tem, da se odločite za smer (od visoke do nizke ali od nizke do visoke), nato pa ustrezno strukturirate pogoje. Na primer, za dodelitev ocen v vrstnem redu od nizkega do visokega lahko v naslednji tabeli predstavimo potrebno rešitev. Upoštevajte, da ni pogoja za 'A', saj ko preidemo vse ostale pogoje, vemo, da mora biti rezultat večji od 95, zato je 'A'.

Ocena Ocena Pogoj
0-63 F. <64
64-72 D <73
73-84 C <85
85-94 B <95
95-100 TO

Z jasno razumljenimi pogoji lahko vnesemo prvo izjavo IF:

kako uporabiti funkcijo štetja v Excelu za besedilo
 
= IF (C5<64,'F')

To skrbi za 'F'. Zdaj, če želimo ravnati z 'D', moramo dodati še en pogoj:

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

Upoštevajte, da sem za 'napačen' rezultat preprosto spustil še en IF v prvi IF. Za razširitev formule na 'C' ponovimo postopek:

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

Tako nadaljujemo do zadnje stopnje. Nato namesto dodajanja drugega IF samo dodajte končno oceno za napačno.

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

Tu je končna ugnezdena formula IF v akciji:

Dokončan ugnezden primer IF za izračun ocen

Video: Kako narediti ugnezdeni IF za dodeljevanje ocen

5. Vgnezdeni IF imajo logičen tok

Mnoge formule so rešene od znotraj navzven, ker je treba za nadaljevanje preostale formule najprej rešiti 'notranje' funkcije ali izraze.

Vgnezdeni IF imajo svoj lasten logični tok, saj 'zunanji' IF delujejo kot prehod v 'notranje' IF. To pomeni, da rezultati zunanjih IF -jev določajo, ali notranji IF -ji sploh delujejo. Spodnji diagram prikazuje logični tok zgornje formule za oceno.

Logični tok ugnezdenega IF

6. Uporabite Evaluate, da opazujete logični tok

V sistemu Windows lahko uporabite Oceni funkcijo za ogled Excela, kako korak za korakom rešuje vaše formule. To je odličen način, da 'vidite' logični tok bolj zapletenih formul in odpravite težave, ko stvari ne delujejo, kot ste pričakovali. Spodnji zaslon prikazuje okno za oceno, odprto in pripravljeno za uporabo. Vsakič, ko kliknete gumb Oceni, se v formuli reši 'naslednji korak'. Ocenite lahko na zavihku Formule na traku (Alt M, V).

Uporabite Evaluate za prehod skozi ugnezdeni IF, ki dodeljuje ocene

Na žalost različica Excela za Mac ne vsebuje funkcije Oceni, vendar lahko še vedno uporabite spodnji trik F9.

7. S pomočjo tipke F9 opazite rezultate preverjanja

Ko v vrstici s formulami izberete izraz in pritisnete tipko F9, Excel razreši samo izbrani del. To je močan način za potrditev, kaj formula dejansko počne. Na spodnjem zaslonu z okni konic zaslona izberem različne dele formule in nato kliknem F9, da vidim, da je del rešen:

Z uporabo F9 preverite ugnezdeni IF, ki dodeljuje ocene

Če želite razveljaviti F9, uporabite Control + Z (Command + Z) na Macu. Prav tako lahko pritisnete Esc, da zapustite urejevalnik formul brez sprememb.

Video: Kako odpraviti napake v formuli s F9

8. Spoznajte svoje meje

Excel ima omejitve glede tega, kako globoko lahko ugnezdite funkcije IF. Do Excela 2007 je Excel dovoljeval do 7 ravni ugnezdenih IF -jev. V Excelu 2007++ Excel omogoča do 64 ravni.

Vendar samo zato, ker ti lahko gnezdi veliko IF -jev, to ne pomeni tebe naj bi . Vsaka dodatna raven, ki jo dodate, otežuje razumevanje formule in odpravljanje težav. Če se vam zdi, da delate z ugnezdenim IF -jem več kot nekaj ravni globoko, bi morali verjetno uporabiti drugačen pristop - alternative si oglejte spodaj.

9. Ujemi oklepaje kot profesionalec

Eden od izzivov z ugnezdenimi IF -ji je ujemanje ali 'uravnoteženje' oklepajev. Če se oklepaji ne ujemajo pravilno, je formula zlomljena. Na srečo E xcel ponuja nekaj orodij, ki vam pomagajo zagotoviti, da so oklepaji 'uravnoteženi' med urejanjem formul.

Prvič, ko imate več kot en niz oklepajev, so oklepaji barvno označeni, tako da se odpiranje oklepaj ujema z zaključnimi oklepaji. Te barve je precej težko videti, vendar obstajajo, če pogledate natančno:

Oglasne oklepaje se ujemajo z barvami, vendar jih je težko videti

Drugič (in še bolje), ko zaprete oklepaje, bo Excel za kratek čas odebelil ustrezni par. Lahko tudi kliknete formulo in se s puščično tipko premikate po oklepajih, Excel pa bo na kratko krepil obe oklepaji, če obstaja ujemajoči se par. Če ni ujemanja, ne boste videli drznosti.

Na žalost je krepitev funkcija samo za Windows. Če uporabljate Excel na Macu za urejanje zapletenih formul, je včasih smiselno kopirati in prilepiti formulo v dober urejevalnik besedil ( Wrangler za besedilo je brezplačen in odličen), da dobite boljša orodja za oklepaje. Ko se ujemajo oklepaji, bo besedilo Wrangler utripalo, z ukazom + B pa lahko izberete vse besedilo, ki ga vsebujejo oklepaji. Formulo lahko prilepite nazaj v Excel, potem ko ste stvari razčistili.

10. Za navigacijo in izbiro uporabite okno z zaslonskimi konicami

Ko gre za krmarjenje in urejanje ugnezdenih IF -jev, je nasvet funkcijskega zaslona vaš najboljši prijatelj. Z njim lahko krmarite in natančno izberete vse argumente v ugnezdenem IF:

Pomaknite se in izberite argumente formule s konico zaslona

V tem videoposnetku lahko vidite, da veliko uporabljam okno z nasveti za zaslon: Kako zgraditi ugnezdeni IF .

11. Bodite previdni pri besedilu in številkah

Kot hiter opomnik pri delu s funkcijo IF bodite pozorni na ustrezno ujemanje številk in besedila. Pogosto vidim formule, če je taka:

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

Ali je rezultat testa v A1 res besedilo in ne številka? Ne? Potem ne uporabljajte narekovajev okoli številke. V nasprotnem primeru bo logični test vrnil vrednost FALSE, tudi če je vrednost prehodna ocena, ker '100' ni isto kot 100. Če je rezultat testa numeričen, uporabite to:

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

12. Dodajanje prelomov vrstic omogoča enostavno branje ugnezdenih IF -jev

Ko delate s formulo, ki vsebuje veliko ravni ugnezdenih IF -jev, je lahko težavno, da stvari ostanejo naravnost. Ker Excelu ni mar za 'presledke' v formulah (tj. Dodatni presledki ali prelomi vrstic), lahko berljivost ugnezdenih if močno izboljšate tako, da dodate prelome vrstic.

Na spodnjem zaslonu je na primer prikazan ugnezden IF, ki izračuna provizijo na podlagi prodajne številke. Tukaj si lahko ogledate tipično ugnezdeno strukturo IF, ki jo je težko razbrati:

Vgnezdene IF -je brez prelomov vrstic je težko prebrati

Če pa pred vsakim 'value if false' dodam prelome vrstic, logika formule jasno skoči. Poleg tega je formulo lažje urejati:

Prelomi vrstic olajšajo branje ugnezdenih IF

Prelome vrstic v sistemu Windows lahko dodate s kombinacijo tipk Alt + Enter, v računalniku Mac pa uporabite Control + Option + Return.

Video: Kako olajšati branje ugnezdenega IF .

13. Omejite IF -je z AND in OR

Vgnezdeni IF -ji so močni, vendar se hitro zapletejo, ko dodate več ravni. Eden od načinov, da se izognete več ravni, je uporaba IF v kombinaciji s funkcijami AND in OR. Te funkcije vrnejo preprost rezultat TRUE/FALSE, ki odlično deluje znotraj IF, zato jih lahko uporabite za razširitev logike enega samega IF.

Na primer, v spodnji težavi želimo v stolpec D vstaviti »x«, da označimo vrstice, kjer je barva »rdeča« in velikost »majhna«.

IF s funkcijo AND je enostavnejši od dveh ugnezdenih IF

Formulo bi lahko zapisali z dvema ugnezdenima IF -jema, kot je ta:

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

Z zamenjavo testa s funkcijo AND pa lahko poenostavimo formulo:

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

Na enak način lahko preprosto razširimo to formulo s funkcijo OR, da preverimo rdečo ALI modro IN majhno:

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

Vse to bi lahko opraviti z ugnezdenimi IF -ji, vendar bi formula hitro postala bolj zapletena.

Video: ČE to ALI tisto

14. Zamenjajte ugnezdene IF -je z VLOOKUP

Ko ugnezdeni IF preprosto dodeli vrednosti na podlagi enega samega vhoda, ga je mogoče enostavno zamenjati z VLOOKUP funkcija . Na primer, ta ugnezdeni IF dodeli številke petim različnim barvam:

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

Z lahkoto ga lahko zamenjamo s tem (veliko enostavnejšim) VLOOKUP:

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

Ugnezdeno IF proti VLOOKUP

Kot bonus VLOOKUP hrani vrednosti na delovnem listu (kjer jih je mogoče enostavno spremeniti), namesto da bi jih vdelali v formulo.

Čeprav zgornja formula uporablja natančno ujemanje, jo lahko preprosto uporabite VLOOKUP za ocene tudi.

Poglej tudi: 23 stvari, ki jih morate vedeti o VLOOKUP -u

Video: Kako uporabljati VLOOKUP

Video: Zakaj je VLOOKUP boljši od ugnezdenih IF -jev

15. Izberite IZBERI

Funkcija IZBERI lahko ponudi elegantno rešitev, ko morate preslikati preproste zaporedne številke (1,2,3 itd.) Na poljubne vrednosti.

V spodnjem primeru se CHOOSE uporablja za ustvarjanje okrajšav med tednom po meri:

Ugnezdeno IF proti funkciji IZBERI

Seveda, ti bi lahko uporabite dolg in zapleten ugnezden IF, če želite narediti isto stvar, vendar prosim, ne :)

16. Namesto ugnezdenih IF uporabite IFS

Če uporabljate Excel 2016 prek sistema Office 365, lahko namesto ugnezdenih IF -jev uporabite novo funkcijo: funkcijo IFS. Funkcija IFS ponuja posebno strukturo za vrednotenje več pogojev brez gnezdenje:

Funkcija IFS - več pogojev brez gnezdenja

Zgoraj uporabljena formula izgleda tako:

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

Upoštevajte, da imamo samo en par oklepajev!

Kaj se zgodi, ko odprete preglednico, ki uporablja funkcijo IFS v starejši različici Excela? V Excelih 2013 in 2010 (in verjamem, da Excel 2007, vendar ne morem preizkusiti) bo prikazano »_xlfn«. priloženo IFS v celici. Prej izračunana vrednost bo še vedno prisotna, če pa kaj povzroči, da se formula ponovno izračuna, boste videli napako #NAME. Microsoft ima več informacij tukaj .

17. Max ven

Včasih lahko uporabite MAX ali MIN na zelo pameten način, da se izognete stavku IF. Recimo, da imate izračun, ki mora imeti za posledico pozitivno število ali nič. Z drugimi besedami, če izračun vrne negativno število, želite prikazati samo nič.

Funkcija MAX vam omogoča pameten način, da to storite brez IF -ja na vidiku:

 
= MAX (calculation,0)

Ta tehnika vrne rezultat izračuna, če je pozitiven, drugače pa nič.

Ta konstrukcija mi je všeč, ker je tako preprosto . Za celoten opis si oglejte ta članek .

18. Napake ujemanja z IFERROR

Klasična uporaba IF -a je, da ujame napake in poda drugi rezultat, ko pride do napake, na primer:

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

To je grdo in odveč, saj se enaka formula ujema dvakrat, Excel pa mora dvakrat izračunati isti rezultat, če ni napake.

V Excelu 2007 je bila predstavljena funkcija IFERROR, ki vam omogoča, da napake ulovite veliko bolj elegantno:

 
= IFERROR (formula,error_result)

Zdaj, ko formula vrne napako, IFERROR preprosto vrne vrednost, ki jo navedete.

19. Uporabite logično logiko

Vgnezdenim IF -jem se lahko včasih izognete tudi z uporabo tako imenovane 'logične logike'. Beseda boolean se nanaša na TRUE/FALSE vrednosti. Čeprav Excel v celicah prikazuje besede TRUE in FALSE, v notranjosti Excel obravnava TRUE kot 1 in FALSE kot nič. To dejstvo lahko uporabite za pisanje pametnih in zelo hitrih formul. Na primer, v zgornjem primeru VLOOKUP imamo ugnezdeno formulo IF, ki izgleda tako:

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

Z logično logiko lahko formulo prepišete tako:

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

Vsak izraz izvede preskus, nato pa rezultat testa pomnoži z 'vrednostjo, če je res'. Ker testi vrnejo vrednost TRUE ali FALSE (1 ali 0), se rezultati FALSE učinkovito izničijo iz formule.

Za numerične rezultate je logična logika preprosta in izjemno hitra, saj ni razvejanja. Na drugi strani je logična logika lahko zmedena za ljudi, ki tega niso vajeni. Kljub temu je to odlična tehnika, ki jo morate poznati.

Video: Kako uporabiti logično logiko v Excelovih formulah

Kdaj potrebujete ugnezdeni IF?

Ob vseh teh možnostih za izogibanje ugnezdenim IF -jem se morda sprašujete, kdaj je smiselno uporabiti ugnezdene IF?

Mislim, da so ugnezdeni IF smiselni, ko morate oceniti več različnih vhodov sprejeti odločitev.

Recimo, da želite izračunati stanje računa »Plačano«, »Odprto«, »Zamudno« itd. To zahteva, da pogledate starost računa in neverjetno ravnotežje:

Izračun stanja računa z ugnezdenim IF

V tem primeru je ugnezdeni IF popolnoma dobra rešitev.

Tvoje misli?

Kaj pa ti? Ste IF-ster? Se izogibate ugnezdenim IF -jem? Ali so ugnezdeni IF zli? Spodaj delite svoje misli.

S programom se hitro naučite Excelovih formul jedrnat video trening . Avtor Dave Bruns


^