Medtem ko je v Excelu funkcija IF bistvena za osnovne logične preizkuse, kompleksni pogoji pogosto zahtevajo ugnezdene stavke IF za izboljšano obdelavo podatkov. V tem obsežnem vodniku bomo podrobno obravnavali osnove ugnezdenega IF-ja, od sintakse do praktičnih aplikacij, vključno s kombinacijami ugnezdenega IF-ja s pogoji IN/ALI. Poleg tega bomo delili, kako izboljšati berljivost ugnezdenih funkcij IF, ter nekaj nasvetov o ugnezdenih funkcijah IF in raziskali zmogljive alternative, kot so VLOOKUP, IFS in druge, da naredimo kompleksne logične operacije enostavnejše za uporabo in učinkovitejše.

• Sintaksa ugnezdenega IF
• Praktični primeri ugnezdenih IF
• Ugnezdeno, če je s pogojem IN/ALI

• Ugnezdeni IF omogoča enostavno branje
• Vrstni red ugnezdenih funkcij IF
• Številke in besedilo je treba obravnavati drugače
• Omejitve ugnezdenega IF

• Uporaba VLOOKUP
• Uporaba IFS
• Uporaba CHOOSE in MATCH

Excelova funkcija IF v primerjavi z ugnezdenimi stavki IF

Funkcija IF in ugnezdeni stavki IF v Excelu služijo podobnim namenom, vendar se bistveno razlikujejo po kompleksnosti in uporabi.

Uporaba ugnezdenega IF

Ta razdelek prikazuje osnovno uporabo ugnezdenih stavkov IF v Excelu, vključno s sintakso, praktičnimi primeri in kako jih uporabljati s pogoji IN ali ALI.

Sintaksa ugnezdenega IF

Razumevanje sintakse funkcije je osnova za njeno pravilno in učinkovito uporabo v Excelu. Začnimo s sintakso ugnezdenih stavkov if.

Sintaksa:

=IF(condition1, result1, IF(condition2, result2, IF(condition3, result3, result4)))

Argumenti:

• Condition1, Condition2, Condition3: These are the conditions you want to test. Each condition is evaluated in order, starting with Condition1.
• Result1: This is the value returned if Condition1 is TRUE.
• Result2: This value is returned if Condition1 is FALSE and Condition2 is TRUE. It's important to note that Result2 is only evaluated if Condition1 is FALSE.
• Result3: This value is returned if both Condition1 and Condition2 are FALSE, and Condition3 is TRUE. Essentially, for Result3 to be evaluated, the previous conditions (Condition1 and Condition2) must both be FALSE.
• Result4: This result is returned if all the conditions (Condition1, Condition2, and Condition3) are FALSE.
  
  In short, this expression can be interpreted as follows:
  Test condition1, if TRUE, return result1, if FALSE,
  test condition2, if TRUE, return result2, if FALSE,
  test condition3, if TRUE, return result3, if FALSE,
  return result4

Ne pozabite, da je v ugnezdeni strukturi IF vsak naslednji pogoj ovrednoten le, če so vsi prejšnji pogoji FALSE. To zaporedno preverjanje je ključnega pomena za razumevanje delovanja ugnezdenih IF-jev.

Praktični primeri ugnezdenih IF

Zdaj pa se poglobimo v uporabo ugnezdenega IF z dvema praktičnima primeroma.

Primer 1: Sistem ocenjevanja

Kot je prikazano na spodnjem posnetku zaslona, ​​predpostavimo, da imate seznam rezultatov študentov in želite dodeliti ocene na podlagi teh rezultatov. Za izvedbo te naloge lahko uporabite ugnezdeni IF.

Izberite prazno celico (v tem primeru C2), vnesite naslednjo formulo in pritisnite Vnesite da bi dobili rezultat. Nato povlecite Ročica za polnjenje navzdol, da dobite ostale rezultate.

=IF(B2>=90,$F$2,IF(B2>=80,$F$3,IF(B2>=70,$F$4,IF(B2>=60,$F$5,$F$6))))

Primer 2: Izračun prodajne provizije

Predstavljajte si scenarij, v katerem prodajni predstavniki prejemajo različne stopnje provizije glede na svoje prodajne dosežke. Kot je prikazano na spodnjem posnetku zaslona, ​​želite izračunati provizijo prodajalca na podlagi teh različnih prodajnih pragov, pri tem pa vam lahko pomagajo ugnezdeni stavki IF.

Izberite prazno celico (v tem primeru C2), vnesite naslednjo formulo in pritisnite Vnesite da bi dobili rezultat. Nato povlecite Ročica za polnjenje navzdol, da dobite ostale rezultate.

=B2*IF(B2>20000,$F$2,IF(B2>=10000,$F$3,$F$4))

Ugnezdeno, če je s pogojem IN/ALI

V tem razdelku spreminjam zgornji prvi primer "sistema ocenjevanja", da pokažem, kako združiti ugnezdeni ČE s pogojem IN ali ALI v Excelu. V popravljenem primeru ocenjevanja sem uvedel dodaten pogoj, ki temelji na "Stopnja prisotnosti".

Uporaba ugnezdenega if s pogojem IN

Če študent izpolnjuje merila za rezultat in prisotnost, prejme povišanje ocene. Na primer, študentu, katerega rezultat je 60 ali višji in katerega stopnja prisotnosti je 95 % ali več, bo ocena povišana za eno stopnjo, na primer iz A v A+, B v B+ itd. Če pa je stopnja prisotnosti pod 95 %, bo ocenjevanje sledilo prvotnim merilom, ki temeljijo na rezultatih. V takih primerih moramo uporabiti ugnezdeni stavek IF s pogojem IN.

Izberite prazno celico (v tem primeru D2), vnesite naslednjo formulo in pritisnite Vnesite da bi dobili rezultat. Nato povlecite Ročica za polnjenje navzdol, da dobite ostale rezultate.

=IF(AND(B2>=60, C2>=95%),IF(B2>=90, "A+", IF(B2>=80, "B+", IF(B2>=70, "C+", "D+"))),IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))))

Uporaba ugnezdenega if s pogojem ALI

V tem primeru se bo študentova ocena zvišala za eno stopnjo, če je njegov rezultat 95 ali višji ali če je njegova stopnja prisotnosti 95 % ali več. Evo, kako lahko to dosežemo z uporabo ugnezdenih pogojev IF in OR.

Izberite prazno celico (v tem primeru D2), vnesite naslednjo formulo in pritisnite Vnesite da bi dobili rezultat. Nato povlecite Ročica za polnjenje navzdol, da dobite ostale rezultate.

=IF(OR(B2>=95, C2>=95%),IF(B2>=90, "A+", IF(B2>=80, "B+", IF(B2>=70, "C+", IF(B2>=60, "D+", "F+")))),IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))))

Namigi in triki za ugnezdeni IF

Ta razdelek zajema štiri uporabne nasvete in trike za ugnezdeni IF.

Ugnezdeni IF omogoča enostavno branje

Tipičen ugnezdeni stavek IF je morda videti kompakten, vendar ga je težko dešifrirati.

V naslednji formuli je težko hitro ugotoviti, kje se en pogoj konča in kje se začne drugi, zlasti ko se zapletenost povečuje.

=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))

Rešitev: Dodajanje prelomov vrstic in zamikov

Da bo ugnezdeni IF enostaven za branje, lahko formulo razdelite na več vrstic, pri čemer je vsak ugnezdeni IF v novi vrstici. V formuli preprosto postavite kazalec pred IF in pritisnite tipki Alt + Enter.

Po prekinitvi zgornje formule bo prikazano takole:

=IF(A2>=90, "A",
      IF(A2>=80, "B",
          IF(A2>=70, "C",
              IF(A2>=60, "D", "F")))
)

S to obliko je bolj jasno, kje je vsak pogoj in ustrezen rezultat, kar izboljša berljivost formule.

Vrstni red ugnezdenih funkcij IF

Vrstni red logičnih pogojev v ugnezdeni formuli IF je ključnega pomena, ker določa, kako Excel ovrednoti te pogoje in tako vpliva na končni rezultat formule.

Pravilna formula

V primeru sistema ocenjevanja uporabljamo naslednjo formulo za dodeljevanje ocen na podlagi rezultatov.

=IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F"))))

Excel ovrednoti pogoje v ugnezdeni formuli IF zaporedno, od prvega do zadnjega pogoja. Ta formula najprej preveri najvišji prag točk (>=90 za "A") in se nato premakne na nižje pragove. Zagotavlja, da se rezultat primerja z najvišjo oceno, za katero izpolnjuje pogoje. Če je prvi pogoj resničen (A2>=90), vrne "A" in ne ovrednoti nobenih nadaljnjih pogojev.

Napačna urejena formula

Če bi bil vrstni red pogojev obrnjen, začenši z najnižjim pragom, bi vrnil nepravilne rezultate.

=IF(B2>=60, "D", IF(B2>=70, "C", IF(B2>=80, "B", IF(B2>=90, "A", "F"))))

V tej nepravilni formuli bi rezultat 95 takoj izpolnil prvi pogoj B2>=60 in bi mu bila nepravilno dodeljena ocena "D".

Številke in besedilo je treba obravnavati drugače

Ta razdelek vam bo pokazal, kako se števila in besedilo različno obravnavajo v ugnezdenih stavkih IF.

Številke

Številke se uporabljajo za aritmetične primerjave in izračune. V ugnezdenih stavkih IF lahko neposredno primerjate števila z uporabo operatorjev, kot so >, <, =, >= in <=.

Besedilo

V ugnezdenih stavkih IF mora biti besedilo v dvojnih narekovajih. Oglejte si A, B, C, D in F v naslednji formuli:

=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))

Omejitve ugnezdenega IF

V tem razdelku je navedenih več omejitev in slabosti ugnezdenih IF.

Kompleksnost in berljivost:

Čeprav Excel omogoča ugnezdenje do 64 različnih funkcij IF, tega nikakor ni priporočljivo. Več kot je ravni gnezdenja, bolj zapletena postane formula. To lahko vodi do formul, ki jih je težko brati, razumeti in vzdrževati.

Nagnjenost k napakam:

Poleg tega lahko zapleteni ugnezdeni stavki IF postanejo nagnjeni k napakam in jih je težko odpravljati ali spreminjati.

Težko razširiti ali povečati:

Če se vaša logika spremeni ali morate dodati več pogojev, je globoko ugnezdene IF težko spremeniti ali razširiti.

Razumevanje teh omejitev je ključno za učinkovito uporabo ugnezdenih stavkov IF v Excelu. Pogosto združevanje ugnezdenih IF-jev z drugimi funkcijami ali iskanje alternativnih pristopov lahko pripelje do učinkovitejših in vzdrževanih rešitev.

Alternative za ugnezdeni IF

V tem razdelku je navedenih več funkcij v Excelu, ki jih je mogoče uporabiti kot alternativo ugnezdenim stavkom IF.

Uporaba VLOOKUP

Namesto ugnezdenih stavkov IF lahko uporabite funkcijo VLOOKUP, da dosežete zgornja dva praktična primera. Takole lahko to storite:

Primer 1: Sistem ocenjevanja z VLOOKUP

Tukaj bom pokazal, kako uporabiti VLOOKUP za dodeljevanje ocen na podlagi rezultatov.

1. korak: Ustvarite iskalno tabelo za ocene

Najprej morate ustvariti iskalno tabelo (kot je v tem primeru E1:F6) za razpon rezultatov in ustrezne ocene. Opombe: Rezultati v prvem stolpcu tabele morajo biti razvrščeni v naraščajočem vrstnem redu.

2. korak: Uporabite funkcijo VLOOKUP za dodeljevanje ocen

Izberite prazno celico (v tem primeru C2), vnesite naslednjo formulo in pritisnite Vnesite ključ za prvi razred. Izberite to celico formule in jo povlecite Ročica za polnjenje navzdol, da dobiš ostale ocene.

=VLOOKUP(B2,$E$2:$F$6,2,TRUE)

Primer 2: Izračun prodajne provizije z VLOOKUP

Za izračun prodajne provizije v Excelu lahko uporabite tudi VLOOKUP. Prosimo, naredite naslednje.

1. korak: Ustvarite iskalno tabelo za ocene

Najprej morate ustvariti iskalno tabelo za prodajo in ustrezno stopnjo provizije, kot je v tem primeru E2:F4. Opombe: Prodaja v prvem stolpcu tabele mora biti razvrščena v naraščajočem vrstnem redu.

2. korak: Uporabite funkcijo VLOOKUP za dodeljevanje ocen

Izberite prazno celico (v tem primeru C2), vnesite naslednjo formulo in pritisnite tipko Enter, da dobite prvo provizijo. Izberite to celico formule in povlecite njeno ročico za polnjenje navzdol, da dobite preostale rezultate.

=B2*VLOOKUP(B2,$E$2:$F$4,2,TRUE)

Uporaba IFS

O IFS funkcija poenostavi postopek z odpravo potrebe po gnezdenju in olajša branje in upravljanje formul. Izboljša berljivost in poenostavi ravnanje z več pogojnimi preverjanji. Če želite uporabiti funkcijo IFS, se prepričajte, da uporabljate Excel 2019 ali novejšo različico ali imate naročnino na Office 365. Poglejmo, kako ga je mogoče uporabiti v praktičnih primerih.

Primer 1: Sistem ocenjevanja z IFS

Ob predpostavki istih meril za ocenjevanje kot prej se lahko funkcija IFS uporabi na naslednji način:

Izberite prazno celico, na primer C2, vnesite naslednjo formulo in pritisnite Vnesite da bi dobili prvi rezultat. Izberite to celico z rezultati in jo povlecite Ročica za polnjenje navzdol, da dobite ostale rezultate.

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

Primer 2: Izračun prodajne provizije z IFS

Za scenarij izračuna prodajne provizije se funkcija IFS uporabi na naslednji način:

Izberite prazno celico, na primer C2, vnesite naslednjo formulo in pritisnite Vnesite da bi dobili prvi rezultat. Izberite to celico z rezultati in jo povlecite Ročica za polnjenje navzdol, da dobite ostale rezultate.

=B2*IFS(B2>20000,20%,B2>=10000,15%,TRUE,10%)

Uporaba CHOOSE in MATCH

Pristop CHOOSE in MATCH je lahko učinkovitejši in lažji za upravljanje v primerjavi z ugnezdenimi stavki IF. Ta metoda poenostavi formulo in naredi posodobitve ali spremembe enostavnejše. Spodaj bom prikazal, kako uporabiti kombinacijo funkcij CHOOSE in MATCH za obravnavanje dveh praktičnih primerov v tem članku.

Primer 1: Sistem ocenjevanja z IZBERI in UJEMAJ

Za dodeljevanje ocen na podlagi različnih rezultatov lahko uporabite kombinacijo funkcij CHOOSE in MATCH.

1. korak: ustvarite iskalno matriko z iskalnimi vrednostmi

Najprej morate ustvariti obseg celic, ki vsebujejo mejne vrednosti, po katerih bo iskal MATCH, na primer $E$2:$E$6 v tem primeru. Opombe: Številke v tem obsegu morajo biti razvrščene v naraščajočem vrstnem redu, da funkcija MATCH pravilno deluje pri uporabi vrste približnega ujemanja.

2. korak: Uporabite CHOOSE in MATCH za dodelitev ocen

Izberite prazno celico (v tem primeru C2), vnesite naslednjo formulo in pritisnite Vnesite ključ za prvi razred. Izberite to celico formule in jo povlecite Ročica za polnjenje navzdol, da dobite ostale rezultate.

=CHOOSE(MATCH(B2, $E$2:$E$6, 1), "F", "D", "C", "B", "A")

Primer 2: Izračun prodajne provizije z IFS

Uporaba kombinacije CHOOSE in MATCH za izračun prodajne provizije je prav tako lahko učinkovita, zlasti če stopnje provizije temeljijo na določenih prodajnih pragovih. Poglejmo, kako lahko.

1. korak: ustvarite iskalno matriko z iskalnimi vrednostmi

Najprej morate ustvariti obseg celic, ki vsebujejo mejne vrednosti, po katerih bo iskal MATCH, na primer $E$2:$E$4 v tem primeru. Opombe: Številke v tem obsegu morajo biti razvrščene v naraščajočem vrstnem redu, da funkcija MATCH pravilno deluje pri uporabi vrste približnega ujemanja.

2. korak: Uporabite CHOOSE in MATCH, da dobite rezultate

Izberite prazno celico (v tem primeru C2), vnesite naslednjo formulo in pritisnite Vnesite ključ za prvi razred. Izberite to celico formule in jo povlecite Ročica za polnjenje navzdol, da dobite ostale rezultate.

=B2*CHOOSE(MATCH(B2, $E$2:$E$4, 1), 10%, 15%, 20%)

Skratka, obvladovanje ugnezdenih stavkov IF v Excelu je dragocena veščina, ki izboljša vašo sposobnost obvladovanja zapletenih logičnih scenarijev pri analizi podatkov in postopkih odločanja. Medtem ko so ugnezdeni IF-ji zmogljivi za zapletene logične operacije, je pomembno, da upoštevate njihove omejitve. Preprostejše alternative, kot so VLOOKUP, IFS in CHOOSE with MATCH, lahko v določenih scenarijih zagotovijo bolj poenostavljene rešitve. Oboroženi s temi vpogledi lahko zdaj samozavestno uporabite najprimernejše Excelove tehnike za naloge analize podatkov, s čimer zagotovite jasnost, natančnost in učinkovitost v svojih preglednicah. Za tiste, ki se želijo poglobiti v zmogljivosti Excela, se naše spletno mesto ponaša s številnimi vadnicami. Tukaj odkrijte več nasvetov in trikov za Excel.

Povezani članki