Excel

19 nasvetov za ugnezdene formule IF

19 Tips Nested If Formulas

The IF funkcija je ena najpogosteje uporabljenih funkcij v Excelu. IF je preprosta funkcija in ljudje jo imajo radi, ker jim daje moč za izdelavo Excela odgovorite saj se podatki vnesejo v preglednico. Z IF lahko svojo proračunsko tablico oživite.

Toda en IF pogosto pripelje do drugega in ko združite več kot nekaj IF, lahko vaše formule začnejo izgledati kot mali Frankensteini :)



Ali so ugnezdeni IF zlobni? So včasih potrebni? Katere so alternative?



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

1. Osnovni IF

Preden se pogovorimo o ugnezdenem IF, na hitro preglejmo osnovno strukturo IF:



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

Funkcija IF izvede preizkus in izvaja različna dejanja, odvisno od tega, ali je rezultat resničen ali neresničen.

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

Za ponazoritev tukaj uporabljamo IF za preverjanje rezultatov in izračun 'uspešno' za rezultate najmanj 65:



Osnovna funkcija IF - vrnitev

Celica D3 v primeru vsebuje to formulo:

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

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

Upoštevajte pa, da če je rezultat manj kot 65, IF vrne FALSE, ker nismo navedli vrednosti, če je false. Če želite prikazati 'Fail' za neprehodne rezultate, lahko kot napačen argument dodamo 'Fail':

 
= 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 kombiniranje formul, ena znotraj druge, tako da ena formula obravnava rezultat druge. Tu je na primer formula, kjer je funkcija TODAY ugnezdena znotraj funkcije MONTH:

 
= MONTH ( TODAY ())

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

3. Preprost ugnezdeni IF

Vgnezdeni IF je samo še dva stavka IF v formuli, kjer se en stavek IF pojavi znotraj drugega.

Za ponazoritev spodaj sem razširil prvotno formulo za podajo / neuspeh, da obravnavam 'nepopolne' rezultate z dodajanjem funkcije IF in vstavljanjem enega IF v drugega:

Osnovni ugnezdeni IF

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

Zunanji IF najprej zažene in preskusi, ali je C3 prazen. V tem primeru zunanji IF vrne 'Nedokončano', notranji IF pa se nikoli ne zažene.

Če je rezultat ni prazno , zunanji IF vrne FALSE in zažene 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, nastavljene za obdelavo 'lestvic' ... na primer za dodeljevanje ocen, stroškov pošiljanja, davčnih stopenj ali drugih vrednosti, ki se na številčni lestvici razlikujejo. Dokler na lestvici ni preveč stopenj, vgnezdeni IF tukaj dobro delujejo, vendar morate formulo ohraniti organizirano, sicer postane težko berljiva.

Trik je, da se odločite za smer (od visoke do najnižje ali od nizke do visoke), nato pa ustrezno strukturirajte pogoje. Če želimo na primer dodeliti ocene v vrstnem redu od „nizke do visoke“, lahko predstavimo rešitev, ki jo potrebujemo v naslednji tabeli. Upoštevajte, da za „A“ ni pogoja, saj, ko preidemo vse druge pogoje, vemo, da mora biti rezultat večji od 95, torej „A“.

Rezultat Razred Stanje
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 poskrbi za 'F'. Zdaj moramo za obdelavo 'D' dodati še en pogoj:

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

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

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

Pot nadaljujemo do zadnjega razreda. Nato namesto da dodate še eno IF, dodajte le končno oceno za false.

 
= 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 ugnezdeni primer IF za izračun ocen

Video: Kako narediti ugnezdeni IF za dodeljevanje ocen

5. Vgnezdeni IF imajo logičen tok

Številne formule se rešujejo od znotraj navzven, ker je treba najprej nadaljevati 'notranje' funkcije ali izraze, da se nadaljuje preostali del formule.

Vgnezdeni investicijski skladi imajo svoj logični tok, saj „zunanji“ investicijski skladi delujejo kot prehod do „notranjih“ investicijskih skladov. To pomeni, da rezultati zunanjih IF določajo, ali notranji IF sploh delujejo. Spodnji diagram je prikazal logični tok zgornje formule.

Logični tok ugnezdenega IF

6. Uporabite Evaluate za ogled logičnega toka

V sistemu Windows lahko uporabite Ocenite funkcijo če si želite ogledati, kako Excel po korakih 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 pričakujete. Spodnji zaslon prikazuje okno za ocenjevanje odprto in pripravljeno za uporabo. Vsakič, ko kliknete gumb Oceni, se reši 'naslednji korak' v formuli. Oceno najdete na zavihku Formule na traku (Alt M, V).

Uporaba funkcije Evaluate za prehod skozi ugnezdeni IF, ki dodeljuje ocene

Žal različica Excela za Mac ne vsebuje funkcije Evaluate, vendar lahko vseeno uporabite spodnji trik F9.

7. Uporabite F9 za opazovanje rezultatov preverjanja

Ko v vrstici s formulami izberete izraz in pritisnete tipko F9, Excel reši samo izbrani del. To je močan način, da potrdite, kaj formula v resnici počne. Na spodnjem zaslonu z okni nasvetov zaslona izbiram različne dele formule, nato pa kliknem F9, da vidim, da je ta del rešen:

S pomočjo F9 preverite ugnezdeni IF, ki dodeli ocene

Za razveljavitev F9 uporabite Control + Z (Command + Z) na Macu. Za izhod iz urejevalnika formul brez sprememb lahko pritisnete tudi Esc.

Video: Kako odpraviti napake v formuli s F9

8. Spoznajte svoje meje

Excel ima omejitve, kako globoko lahko ugnezdiš funkcije IF. Do Excel 2007 je Excel dovoljeval do 7 ravni ugnezdenih IF. V programu Excel 2007+ Excel omogoča do 64 stopenj.

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

9. Ujemite oklepaje kot profesionalec

Eden od izzivov pri ugnezdenih IF je ujemanje ali uravnoteženje oklepajev. Ko se oklepaji ne ujemajo pravilno, je vaša formula pokvarjena. Na srečo E xcel ponuja nekaj orodij, ki vam pomagajo zagotoviti, da so oklepaji med urejanjem formul 'uravnoteženi'.

Prvič, ko imate več naborov oklepajev, so oklepaji barvno označeni, tako da se začetni oklepaji ujemajo z zaključnimi oklepaji. Te barve je precej težko videti, vendar so tam, če dobro pogledate:

Oklepaji formule se ujemajo z barvami, vendar jih je težko videti

Drugič (in bolje), ko zaprete oklepaje, bo Excel na kratko poudaril ujemajoči se par. Prav tako lahko kliknete formulo in se s puščično tipko premikate med oklepaji, Excel pa bo naglo krepil obe oklepaji, če obstaja ujemajoč se par. Če ni ujemanja, ne boste videli drznih.

Na žalost je krepko besedilo samo za sistem Windows. Če v sistemu Mac uporabljate Excel za urejanje zapletenih formul, je včasih smiselno formulo kopirati in prilepiti v dober urejevalnik besedil ( Besedilo Wrangler je brezplačna in odlična), da dobite boljša orodja za ujemanje v oklepajih. Ko se ujemajo oklepaji, bo besedilo Wrangler utripalo in z ukazi + B lahko izberete celotno besedilo v oklepajih. Formulo lahko prilepite nazaj v Excel, potem ko ste stvari uredili.

10. Za krmarjenje in izbiro uporabite okno nasveta zaslona

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

S konico zaslona se pomikajte in izberite argumente formule

V tem videoposnetku me lahko vidite, kako veliko uporabljam okno nasveta zaslona: Kako zgraditi ugnezdeni IF .

11. Pazite na besedilo in številke

Kot hiter opomnik pri delu s funkcijo IF bodite pozorni na pravilno ujemanje številk in besedila. Pogosto vidim formule, ČE so taki:

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

Je testna ocena v A1 res besedilo in ne številka? Ne? Nato ne uporabljajte narekovajev okoli številke. V nasprotnem primeru bo logični test vrnil FALSE, tudi če je vrednost prehodna ocena, ker '100' ni enako kot 100. Če je testna številka številčna, uporabite to:

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

12. Dodajanje prelomov vrstic olajša branje vgnezdenih IF

Ko delate s formulo, ki vsebuje številne ravni ugnezdenih IF, je lahko težavno, da stvari ostanejo naravnost. Ker Excel ne skrbi za 'presledek' v formulah (tj. Dodatni presledki ali prelomi vrstic), lahko z dodajanjem prelomov vrstice močno izboljšate čitljivost ugnezdenih ifs.

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

Vgnezdene IF brez prelomov vrstic je težko brati

Če dodam prelome vrstic pred vsako 'vrednost, če je napačno', logika formule jasno izstopi. Poleg tega je formulo lažje urejati:

Prelomi vrstic olajšajo branje ugnezdenih IF

V sistemu Windows lahko dodate prelome vrstic z Alt + Enter, v Macu pa uporabite Control + Option + Return.

Video: Kako olajšati branje ugnezdenega IF .

13. Omejite IF s AND in OR

Vgnezdeni IF so zmogljivi, vendar se z dodajanjem več ravni hitro zapletejo. Eden od načinov, kako se izogniti večim nivojem, je uporaba IF v kombinaciji s funkcijama AND in OR. Te funkcije vrnejo preprost rezultat TRUE / FALSE, ki popolnoma deluje znotraj IF, zato jih lahko uporabite za razširitev logike posameznega IF.

Na primer, v spodnji težavi želimo v stolpec D postaviti znak '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, 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 to formulo enostavno razširimo s funkcijo OR, da preverimo rdečo ALI modro IN majhno:

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

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

Video: ČE je to ALI tisto

14. Vgnezdene IF zamenjajte z VLOOKUP

Ko ugnezdeni IF preprosto dodeli vrednosti na podlagi enega samega vhoda, ga je mogoče enostavno nadomestiti 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 nadomestimo s tem (veliko preprostejšim) VLOOKUPOM:

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

Vgnezdeni IF vs VLOOKUP

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

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

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

Video: Kako uporabljati VLOOKUP

Video: Zakaj je VLOOKUP boljši od ugnezdenih IF

15. Izberite IZBERI

Funkcija IZBERI lahko nudi elegantno rešitev, kadar morate preslikati preprosta zaporedna števila (1,2,3 itd.) Na poljubne vrednosti.

V spodnjem primeru se CHOOSE uporablja za ustvarjanje okrajšav v tednu po meri:

Vgnezdeno IF v primerjavi s funkcijo CHOOSE

Seveda, ti lahko za isto naredite dolg in zapleten ugnezdeni IF, vendar prosim ne :)

16. Namesto ugnezdenih IF uporabite IFS

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

Funkcija IFS - več pogojev brez gnezdenja

Zgoraj uporabljena formula je videti 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 Excelu 2013 in 2010 (in verjamem, da Excel 2007, vendar ga ne morem preizkusiti) boste videli '_xlfn.' dodana IFS v celici. Prej izračunana vrednost bo še vedno prisotna, če pa nekaj povzroči preračun formule, boste videli napako #NAME. Microsoft je več informacij tukaj .

17. Največji izhod

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

Funkcija MAX vam omogoča pameten način, da to storite brez IF, kjer koli je na vidiku:

 
= MAX (calculation,0)

Ta tehnika vrne rezultat izračuna, če je pozitiven, v nasprotnem primeru pa nič.

Všeč mi je ta gradnja, ker je prav tako preprosto . Za celoten opis glejte ta članek .

18. Zajemi napake z IFERROR

Klasična uporaba IF je zajemanje napak in zagotavljanje drugega rezultata, ko se napaka vrne, kot je ta:

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

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

V Excelu 2007 je bila predstavljena funkcija IFERROR, ki vam omogoča veliko bolj elegantno lovljenje napak:

 
= IFERROR (formula,error_result)

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

19. Uporabite logično logiko

Vgnezdenim IF se lahko včasih izognete tudi z uporabo 'logične logike'. Beseda boolean se nanaša na TRUE / FALSE vrednosti. Čeprav Excel v celicah prikaže besede TRUE in FALSE, Excel znotraj TRUE kot 1, FALSE pa kot nič. To dejstvo lahko uporabite za pisanje pametnih in zelo hitrih formul. Na primer, v zgornjem primeru VLOOKUP imamo vgnezdeno formulo IF, ki je videti takole:

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

Z uporabo logične logike lahko formulo prepišete tako:

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

Vsak izraz izvede test, nato pa rezultat preizkusa pomnoži z vrednostjo, če je res. Ker testi vrnejo TRUE ali FALSE (1 ali 0), rezultati FALSE dejansko izničijo formulo.

Za numerične rezultate je logična logika preprosta in izjemno hitra, saj ni razvejanja. Slaba stran je, da logična logika zmede ljudi, ki je niso vajeni videti. Kljub temu je to odlična tehnika, o kateri morate vedeti.

Video: Kako uporabljati logično logiko v Excelovih formulah

Kdaj potrebujete ugnezdeni IF?

Z vsemi temi možnostmi za izogibanje ugnezdenim IF-jem se boste morda vprašali, kdaj je smiselno uporabiti ugnezdeni IF?

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

Recimo, da želite na primer izračunati stanje računa 'Plačano', 'Odprto', 'Zapadlo' 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? Ali se izogibate ugnezdenim IF? Ali so ugnezdeni IF zlobni? Delite svoje misli spodaj.

Na hitro se naučite formul Excel jedrnat video trening . Avtor Dave Bruns


^