Funkcija IF – ugnezdene formule in izogibanje pastem

Funkcija IF – ugnezdene formule in izogibanje pastem

Z okoljem Microsoft 365 lahko kar najbolje izkoristite svoj čas

Naročite se zdaj

S funkcijo IF lahko opravite logično primerjavo med vrednostjo in med tem, kar pričakujete. To naredite tako, da preizkusite pogoj in dobite rezultat »True« ali »False«.

  • =IF(če je nekaj ovrednoteno kot »True«, izvedi določeno dejanje, v nasprotnem primeru izvedi neko drugo dejanje)

Izjava IF ima lahko torej dva rezultata. Prvi rezultat je, če je primerjava ovrednotena kot »True«, drugi rezultat pa, če je primerjava ovrednotena kot »False«.

Če so izjave izredno zanesljive in so osnova številnih modelov preglednice, so pa tudi glavni vzrok številnih težav s preglednicami. V idealnem primeru bi morala izjava IF veljati za minimalne pogoje, na primer moška/ženska, da/ne/morda, če želite poimenovati nekaj, včasih pa morate ovrednotiti zahtevnejše scenarije, ki zahtevajo več kot 3, če delujejo skupaj.

* »Ugnezdenje« se nanaša na pridruževanje več funkcij v eno formulo.

Uporabite logično funkcijo IF, s katero boste vrnili eno vrednost, če za pogoj velja vrednost »true«, in drugo vrednost, če za pogoj velja vrednost »false«.

Sintaksa

IF(logični_preskus; vrednost_če_je_true; [vrednost_če_je_false])

Primer:

  • =IF(A2>B2; "Prekoračitev proračuna"; "V redu")

  • =IF(A2=B2,B4-A4,"")

Ime argumenta

Opis

logical_test   

(zahtevano)

Pogoj, ki ga želite preskusiti.

value_if_true   

(zahtevano)

Vrednost, ki jo želite pridobiti, če je rezultat argumenta logical_test TRUE.

value_if_false   

(izbirno)

Vrednost, ki jo želite pridobiti, če je rezultat argumenta logical_test FALSE.

Pripombe

Čeprav v Excelu lahko ugnezdite največ 64 različnih funkcij IF, pa vam priporočamo, da funkcij IF sploh ne ugnezdite. Zakaj?

  • Pri ustvarjanju več izjav IF morate temeljito razmisliti, da jih ustvarite pravilno, in se prepričati, da lahko njihova logika pravilno izračuna Vsak posamezen pogoj do konca formule. Če formule ne ugnezdite 100 % natančno, bo formula morda delovala v 75 % primerih, v 25 % primerih pa bo vrnila nepričakovane rezultate. Žal pa boste le težko dosegli teh 25 %.

  • Ohranjanje več izjav IF lahko postane zelo težavno, zlasti če čez nekaj časa poskusite znova uporabiti izjavo vrnete in poskusite ugotoviti, kaj ste vi ali neka druga poskušali doseči.

Če imate izjavo IF, ki se neprestano povečuje in ni videti, da bi se končala, odložite miško in razmislite o strategiji.

Oglejmo si, kako ustrezno ustvariti zahtevno ugnezdeno formulo IF z več izjavami IF in kdaj je smiselno uporabiti drugo Excelovo orodje.

Primeri

To je primer razmeroma standardne ugnezdene izjave IF, s katero lahko rezultate preverjanj znanja študenta pretvorite v oceno, podano s črko.

Kompleksno ugnezdena izjava IF – formula v celici E2 je =IF(B2>97,"A+",IF(B2>93,"A",IF(B2>89,"A-",IF(B2>87,"B+",IF(B2>83,"B",IF(B2>79,"B-",IF(B2>77,"C+",IF(B2>73,"C",IF(B2>69,"C-",IF(B2>57,"D+",IF(B2>53,"D",IF(B2>49,"D-","F"))))))))))))
  • =IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))

    Ta zahtevna ugnezdena izjava IF temelji na razumljivi logiki:

  1. Če je rezultat preizkusa znanja (v celici D2) večji od 89, potem študent prejme oceno A

  2. Če je rezultat preizkusa znanja večje od 79, prejme študent oceno B

  3. Če je rezultat preizkusa znanja večje od 69, prejme študent oceno C

  4. Če je rezultat preizkusa znanja večje od 59, prejme študent oceno D

  5. Drugače prejme študent oceno F

Ta določen primer je razmeroma varen, saj se odnos med rezultati preizkusa in ocenami s črkami najverjetneje ne bo spremenil, zato razmerja ne bo treba vzdrževati. Kaj pa bi naredili, če ni morali ocene dodatno razdeliti, na primer A+, A in A- (in tako dalje)? Svoje štiri izjave s pogojem IF morate preoblikovati, da dobite 12 pogojev! Vaša formula bi bila nato videti tako:

  • =IF(B2>97,"A+",IF(B2>93,"A",IF(B2>89,"A-",IF(B2>87,"B+",IF(B2>83,"B",IF(B2>79,"B-", IF(B2>77,"C+",IF(B2>73,"C",IF(B2>69,"C-",IF(B2>57,"D+",IF(B2>53,"D",IF(B2>49,"D-","F"))))))))))))

Funkcionalno je še vedno pravilna in bo delovala tako, kot pričakujete, vendar porabite več časa, da jo napišete in preverite, ali deluje tako, kot želite. Druga pomanjkljivost je v tem, da morate ročno vnesti rezultate in pripadajoče ocene. Kakšne so verjetnosti, da boste pri tem naredili napako? Zdaj pa poskusite to narediti 64-krat z bolj zahtevnimi pogoji! Seveda je mogoče, vendar boste za to potrebovali veliko časa, velika pa je tudi verjetnost, da boste naredili napako.

Namig: Vsaka funkcija v Excelu mora imeti začetni in zaključni oklepaj »()«. Excel bo poskusil ugotoviti, kaj sodi kam, tako, da bo obarval različne dele formule, ko boste končali urejanje. Če na primer uredite zgornjo formulo, se v trenutku, ko premaknete kazalec prek vsakega zaključnega oklepaja »)«, enako obarva pripadajoč začetni oklepaj. To je lahko posebej uporabno v zahtevnih ugnezdenih formulah, ko poskušate ugotoviti, ali imate dovolj ujemajočih se oklepajev.

Dodatni primeri

V nadaljevanju je zelo pogost primer računanja provizije prodaje na podlagi doseženega prihodka.

Formula v celici D9 je IF(C9>15000,20%,IF(C9>12500,17.5%,IF(C9>10000,15%,IF(C9>7500,12.5%,IF(C9>5000,10%,0)))))
  • =IF(C9>15000,20%,IF(C9>12500,17.5%,IF(C9>10000,15%,IF(C9>7500,12.5%,IF(C9>5000,10%,0)))))

Ta formula pravi: IF(C9 je večje od 15.000, potem vrni 20 %, IF(C9 je večje od 12.500, potem vrni 17,5 % in tako dalje ...

Čeprav je ta formula izredno podobna prejšnjim primerom ocen, je to odlična primer, kako težko je vzdrževati velike IF-kaj bi morali storiti, če se je vaša organizacija odločila, da bo dodala nove ravni odškodnine in morda celo spremenila obstoječe dolarske ali odstotne vrednosti? Imel boš veliko dela na svojih rokah!

Namig: V vnosno vrstico lahko vstavite prelome vrstic in poskrbite, da so dolge formule lažje berljive. Pred besedilom, ki ga želite prelomiti v novo vrstico, pritisnite ALT+ENTER.

Tu je primer scenarija za komisijo z neurejeno logiko:

Formula v celici D9 ni pravilna kot =IF(C9>5000,10%,IF(C9>7500,12.5%,IF(C9>10000,15%,IF(C9>12500,17.5%,IF(C9>15000,20%,0)))))

Ali lahko vidite, kaj je narobe? Primerjajte vrstni red primerjave prihodkov s prejšnjim primerom. Kam gre ta pot? To je res, to se dogaja od spodaj navzgor ($5.000 do $15.000), ne ravno obratno. Toda zakaj bi moralo biti to tako pomembno? To je velika stvar, ker formula ne more posredovati prve Ocene za katero koli vrednost nad $5.000. Recimo, da imate $12.500 v prihodkih – izjava IF se bo vrnila 10%, ker je večja od $5.000 in bo tam končana. To je lahko zelo problematično, ker se v številnih primerih te vrste napak neopaženo odprejo, dokler ne bodo imele negativnega učinka. Ali ste vedeli, da obstaja nekaj resnih pasti z zapletenimi ugnezdenimi stavki IF, kaj lahko naredite? V večini primerov lahko uporabite funkcijo VLOOKUP namesto zgradbe kompleksne formule s funkcijo IF. S funkcijo VLOOKUPmorate najprej ustvariti referenčno tabelo:

Formule v celici D2 =VLOOKUP(C2,C5:D17,2,TRUE)
  • =VLOOKUP(C2,C5:D17,2,TRUE)

Ta formula pravi, da poiščete vrednost v celici C2 v obsegu C5:C17. Če vrednosti ne najdete, vrnite ustrezno vrednost iz iste vrstice v stolpcu D.

Formula v celici D9 je =VLOOKUP(B9,B2:C6,2,TRUE)
  • =VLOOKUP(B9,B2:C6,2,TRUE)

Ta formula podobno poišče vrednost v celici B9 v obsegu B2:B22. Če vrednosti ne najdete, vrnite ustrezno vrednost iz iste vrstice v stolpcu C.

Opomba: Obe funkciji VLOOKUP uporabljata argument TRUE na koncu formul, kar pomeni, da morajo poiskati približno ujemanje. Povedano drugače, formula bo poiskala natančno ujemajoče se vrednosti v iskalni tabeli, poleg tega pa tudi morebitne vrednosti med njimi. V tem primeru je treba iskalne tabele razvrstiti v naraščajočem vrstnem redu – od najmanjše do največje.

Funkcija VLOOKUP je tukajpodrobno opisana, vendar je to veliko preprostejše od 12-ravnih, kompleksnih ugnezdenih izjav IF! Nudi pa tudi druge očitne prednosti:

  • Funkcija VLOOKUP se sklicuje na tabele tako, da so sklici dobro vidni.

  • Vrednosti v tabeli je mogoče preprosto posodobiti. Če se pogoji spremenijo, vam formule ni treba spreminjati.

  • Če ne želite, da drugi vidijo ali pa posegajo v referenčno tabelo, jo postavite na drug delovni list.

Ali ste vedeli?

Zdaj je na voljo funkcija IFS, ki lahko več ugnezdenih izjav IF nadomesti z eno funkcijo. Torej namesto našega prvotnega primera z ocenami, ki ima 4 ugnezdene funkcije IF:

  • =IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))

Lahko dobite bolj preprosto funkcijo IFS:

  • =IFS(D2>89,"A",D2>79,"B",D2>69,"C",D2>59,"D",TRUE,"F")

Funkcija IFS je odlična zato, ker vam ni treba skrbeti zaradi izjav IF in oklepajev.

Opomba: Ta funkcija je na voljo le, če imate naročnino na Office 365. Če ste naročnik na Microsoft 365, preverite, ali imate najnovejšo različico Officea.

Potrebujete dodatno pomoč?

Kadar koli lahko zastavite vprašanje strokovnjaku v skupnosti tehničnih strokovnjakov za Excel, pridobite podporo skupnosti Answers ali predlagate novo funkcijo oziroma izboljšavo na spletnem mestu Excel User Voice.

Sorodne teme

Video: napredna funkcija funkcije
IFS (Microsoft 365; Excel 2016 in novejše različice)
funkcija COUNTIF bo štela vrednosti, ki temeljijo na enem merilu
funkcija COUNTIFS bo štela vrednosti, ki temeljijo na več merilih
SUMIF bo povzemala vrednosti, ki temeljijo na enem pogoju
, ki ga bo funkcija SUMIFS seštevanje vrednosti, ki temeljijo na več pogojih
in
OR function
funkciji funkcijeVLOOKUP
s pregledom formul v Excelu
Kako se izogniti zlomljenim formulam
zazna napake v formulah
logične funkcije
Excelove funkcije (po abecedi)
Excelove funkcije (po kategorijah)

Opomba:  Ta stran je bila prevedena z avtomatizacijo in lahko vsebuje slovnične napake ali nepravilnosti. Želimo, da bi bila ta vsebina za vas uporabna. Ali nam lahko sporočite, če so bile te informacije uporabne? Tukaj je referenčni članek v angleščini.

Ali potrebujete dodatno pomoč?

Razširite poznavanje Officea
Oglejte si izobraževanje
Prvi dobite nove funkcije
Pridružite se programu Office Insider

Vam je bila informacija v pomoč?

Zahvaljujemo se vam za povratne informacije.

Zahvaljujemo se vam za povratne informacije. Videti je, da bi vam prišla prav pomoč enega od naših Officeovih agentov za podporo.

×