Kako uporabljati več stavkov IF v Microsoft Excelu

Stavek IF v Excelu zažene logični preizkus, ki vrne eno vrednost, če je pogoj izpolnjen, in drugo vrednost, če ni. Uporaba enega Excelovega stavka IF lahko sprejme samo osnovne operacije, če pa ugnezdite več stavkov IF, lahko izvajate zapletena opravila. Edina pomanjkljivost je, da morate biti pri sestavljanju formule previdni, da se izognete napakam. Alternativne funkcije Excel lahko opravijo enako delo z manjšo nevarnostjo napak. Naučite se osnov ugnezdenih stavkov IF v Excelu in alternativ za iskanje najboljšega pristopa za vaše potrebe.

Funkcija IF v Excelu v bistvu pravi "če je to res, vrni vrednost x, če pa ni, vrni vrednost y." Na primer, če ste preverjali, ali so v vašem podjetju različni oddelki so bili pod ali nad proračunom, lahko primerjate njihove odhodke z njihovimi proračuni z uporabo funkcije in vrnejo »Prek proračuna«, če je oddelek porabil preveč, ali »V okviru proračuna«, če je ni. Na splošno za sestavo izjave uporabite primerjalne operatorje, kot so:

• = (enako)

• (večji kot)

• = (večje ali enako)

• < (manjše od)
• <= (manjše ali enako)
• <> (ni enako)

Razumevanje sintakse stavka Excel IF je ključni korak k učenju pravilne uporabe. Format funkcije je IF(logični_test, vrednost_če_true, [vrednost_če_napačno]) kjer oglati oklepaji označujejo, da je ta zadnji argument neobvezen. Te je enostavno razlagati: The logični_test pomeni izjavo, ki jo želite preizkusiti, vrednost_če_resnična je mesto za to, kar želite, da Excel pokaže, ali je test resničen, in [value_if_false] je izbirno mesto za to, kar želite, da Excel prikaže, če je test napačen.

To je dovolj, da uporabite Excelovo formulo za izjave if-then, kar je mogoče prikazati s prejšnjim primerom »Prek proračuna/v okviru proračuna«. Predstavljajte si, da imate znesek, ki ga porabijo različni oddelki v stolpcu B, in znesek, predviden v proračunu v stolpcu C, pri čemer se vrednosti začnejo v drugi vrstici. V celico D2 bi vnesli =IF(B2>C2, "Presežen proračun", "Znotraj proračuna") da vrnete rezultat, ki ga iščete (upoštevajte, da morate uporabiti narekovaje, da ga dobite, da vrnete besedilo znotraj). Prvi del pravi "če je vrednost v B2 večja od vrednosti v C2" (če so izdatki večji od proračuna), drugi del pravi "nato vrni besedilo Presežek proračuna in tretji del pravi "če ne, potem se vrni V okviru proračuna."

Izkoristiti orodje za izjave Excela IF pomeni, da lahko kombinirate več stavkov IF, ko imate nalogo, ki je za to primerna. Drugi stavek IF lahko vključite v svoj izvirni stavek IF, v ta pa lahko vključite še en stavek IF in tako naprej. To je še posebej uporabno, če želite nekaj razvrstiti v eno od več skupin z eno samo formulo. Uporabite lahko na primer ugnezdene stavke IF za pretvorbo rezultatov, ki so jih študenti dosegli na testu, v oceno.

Če želite pravilno uporabiti več stavkov IF, razmislite o logiki tega, kar zahtevate od Excela. V bistvu lahko po vašem pogoju (prvi argument v funkciji) dodate še en stavek IF v vrednost_če_resnična oz vrednost_če_napačno presledki, da Excelu poveste, kaj naj stori naprej. Razmišljajte o tem, kot da bi pridobili Excel, da preveri celico glede na drugo merilo, če zadevna celica ne izpolnjevati prvi kriterij (ali res, če izpolnjuje prvo), in ta postopek lahko nadaljujete še dolgo čas. Z drugimi besedami, spremenite "če to, potem to" v "če to in to, potem to" ali "če ne to, ampak to, potem ono."

Predstavljajte si, da ste študentom dali test, kjer je rezultat 85 odstotkov ali več A, med 70 in 85 odstotki je B, med 55 in 70 odstotki je C, med 40 in 55 odstotki je D in vse nižje je Neuspešno. Ocene so v stolpcu B, od vrstice 2 do vrstice 11. Za kategorizacijo rezultatov učencev lahko uporabite skrbno izdelano funkcijo Excel IF z več pogoji.

Prvi del je enostaven (za rezultat v B2 v tem primeru): =IF(B2>=85, "A") pove Excelu, naj vrne A, če je študent dosegel 85 ali več točk. Gnezdenje vam omogoča, da dodate druge ocene, pri čemer uporabite tretji argument (kaj storiti, če je pogoj napačen), da dodate dodatne pogoje: =IF(B2>=85, "A", IF(B2>=70, "B")), ki pove Excelu, naj vrne A za ocene 85 ali več, če pa ne, vrne B, če je rezultat več kot 70. Nadaljujte na ta način, da dokončate formulo, pri čemer vsakič uporabite tretji argument za nov stavek IF: =IF(B2>=85, "A", IF(B2>=70, "B",IF(B2>=55, "C", IF(B2>=40, "D", "Neuspešno")) ))

To se zdi zapleteno, zato bi moralo razčleniti idejo. Prva dva argumenta pravita "če je rezultat v B2 85 ali več, potem študent dobi A", tretji pa pravi, "sicer preverite naslednji pogoj." The drugi IF vodi do te izjave: "(za študente, ki dosežejo manj kot 85), če je rezultat v B2 70 ali višji, dobi študent B, in če ne, preveri naslednji pogoj." Tretji IF pravi: "(za študente, ki dosežejo manj kot 70), če je rezultat v B2 55 ali višji, dobi študent C, če ne, preveri naslednji pogoj" in četrti pravi "(za študente, ki dosežejo manj kot 55), če je rezultat v B2 višji od 40, dobi študent D, če ne, študent ne uspe."

Če pravilno vnesete formulo za stavek IF, bo vrnil eno od možnosti, ki ste jih dali Excelu. Če pa naredite napako, lahko formula vrne a 0 oz #NAME? v celici, ki vsebuje vašo formulo.

A 0 napaka pomeni, da bodisi vrednost_če_resnična oz vrednost_če_napačno argument je prazen. To težavo odpravite tako, da zagotovite, da je vrednost vnesena v oba argumenta ali vsaj v vrednost_če_resnična prepir. The vrednost_če_napačno argument je mogoče popolnoma odstraniti in vrnil bo FALSE, če ne vstavite druge vejice za vrednost_če_resnična polje.

A #NAME? napaka vam pove, da ste verjetno kaj napačno črkovali ali napačno vnesli v formulo. To je morda nekoliko težje popraviti, ker je lahko kateri koli del formule, vendar je najboljši nasvet, da še enkrat preverite vse ali ponovno napišete formulo.

V prejšnjem primeru je formula za ugnezdeno funkcijo IF postala precej dolga, kar je ena od glavnih pomanjkljivosti uporabe več stavkov IF v Excelu. Lahko postanejo zapleteni in enostavno je narediti napako, ker morate natančno slediti logiki, da se prepričate, da vse deluje tako, kot želite. Microsoft poudarja, da lahko napačno ugnezdena formula večino časa celo vrne pravi rezultat, v redkih primerih pa napačne rezultate. Te je težko opaziti.

Če pogoje ne postavimo v pravi vrstni red, je pogosta napaka. V primeru ocenjenih testov, če ste začeli z nižjo oceno =IF(B2>=40, "D", …" in nadaljeval z naštevanjem ostalih v naraščajočem vrstnem redu, bi celo rezultat 100 vrnil D. To je zato, ker je prvi pogoj preverjen, tako da če ste začeli z "če je celica B2 40 ali več, vrnite D" bi vsak rezultat 40 ali več razvrstil kot D, ne da bi preveril kaj drugega, ker ustreza prvemu stanje. To je odličen primer, zakaj morate do konca slediti logiki svoje formule.

Prav tako morate zagotoviti, da dodate pravo število oklepajev na konec več stavkov IF, da pravilno zaprete formulo. Excel pomaga pri tem z barvno usklajevanjem in poudarjanjem oklepajev med delom.

Tehnično lahko v eno formulo ugnezdite do 64 funkcij IF, vendar Microsoft ne priporoča uporabe te funkcije iz zgornjih razlogov. Stvari se hitro zapletejo in verjetno je, da če poskušate ugnezditi več kot 10 stavkov IF, obstaja verjetno boljše orodje za delo, ki je že vključeno v Excel.

Najbolj priročna alternativa funkciji IF je funkcija IFS, ki je različica funkcija, ki opravlja isto nalogo kot gnezdenje v eni sami funkciji in lahko obravnava do 127 pogoji. Funkcija IFS ima enostavnejšo sintakso: =IFS(logični_test1, vrednost_če_resnično1, [logični_test2, vrednost_če_resnično2], [logični_test3, vrednost_če_resnična3]…) in tako naprej. V bistvu to deluje kot ugnezdeni stavki IF, le da poznejši stavki IF zasedajo prostor za "vrednostjo, če je resnična", ne da bi morali znova priklicati funkcijo.

Formulo v primeru testne ocene je mogoče zapisati veliko preprosteje s funkcijo IFS. Namesto:

=IF(B2>=85, "A", IF(B2>=70, "B",IF(B2>=55, "C", IF(B2>=40, "D", "Neuspešno")) ))

preprosto napišite

=IFS(B2>=85, "A", B2>=70, "B", B2>=55, "C", B2>=40, "D", TRUE, "Neuspešno")

The PRAV na predzadnjem mestu piše "če nobena od drugih vrednosti ni izpolnjena, vrni naslednjo vrednost."

Ta funkcija je na voljo samo v Excelu 2019 ali Excelu za Office 365.

Funkcija VLOOKUP v Excelu je pogosto preprostejša alternativa več stavkom IF in je na voljo v kateri koli različici Excela od leta 2007 dalje. Ni idealen za vse situacije, ker vrne rezultate za natančno ujemanje ali za najbližji številka, zato je ni bilo mogoče uporabiti za ocene kot prejšnji primer. Vendar pa vam lahko v mnogih situacijah prihrani veliko dela pri pisanju formule, čeprav morate sestaviti tabelo z možnimi vrednostmi v eni stolpec, kot so izdelki, ki jih prodaja vaša trgovina, in kaj želite, da funkcija vrne, kot je cena izdelka, v drugem stolpcu, za primer.

Sintaksa za VLOOKUP je =VLOOKUP(vrednost_iskanja, niz_tabel, številka_indeks_stolpca, [iskanje_obsega]) in učenje vsake od komponent vam pokaže, kako jo uporabljati. The iskalna_vrednost argument je vrednost, ki jo želite preveriti, ki mora biti celica v istem stolpcu kot začetek tabele, v kateri jo želite preveriti, nad ali pod njo. The tabele_array je obseg celic, ki vsebujejo vašo tabelo z informacijami, kot so izdelki in njihove ustrezne cene, in indeks_stolpca je stolpec, v katerem želite, da Excel poišče vrednost, ki jo želite vrniti 1 označuje skrajni levi stolpec, 2 označevanje naslednjega in tako naprej. končno, [range_lookup] je izbirno, vendar vnesite »TRUE«, če želite, da Excel temelji odgovor na najbližji vrednosti ali »FALSE«, če želite samo natančno ujemanje.