Funkcija IF – ugnezdene formule in izogibanje pastem

Funkcija IF – ugnezdene formule in izogibanje pastem

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«.

Izjave IF so izredno robustne in tvorijo osnovno številnih modelov preglednic, so pa tudi korenski vzrok številnih težav s preglednicami. Izjava IF bi morala biti uporabljena za minimalne pogoje, med drugim »Moški/ženska« in »Da/ne/morda«. Toda včasih boste morali ovrednotiti bolj zahtevne scenarije, ki zahtevajo ugnezdenje* več kot 3 funkcij IF.

* »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 primer precej podoben prejšnjemu primeru za ocene, je ta formula odličen primer, kako težko je vzdrževati velike izjave IF. Kaj bi morali narediti, če bi vaša organizacija sklenila, da je treba dodati nove stopnje kompenzacije in po možnosti tudi spremeniti obstoječe vrednosti za dolar ali odstotek? Veliko dela bi imeli!

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 opazite napako? Primerjajte vrstni red primerjave prihodka s prejšnjim primerom. V katero smer gre? Tako je, od spodaj navzgor (5000 $ do 15.000 $), ne obratno. Zakaj je to težava? Zato, ker formula ne more opraviti prvega ovrednotenja za vrednost, večjo od 5000 $. Recimo, da vaš prihodek znaša 12.500 $. Izjava IF bo vrnila 10 %, ker je več od 5000 €, nato pa se bo tam ustavila. To je lahko zelo težavno, ker so tovrstne težave pogosto spregledane, dokler ne privedejo do negativnega učinka. Zdaj veste, da lahko pride do resnih težav z zahtevnimi ugnezdenimi izjavami IF. Kaj lahko naredite? V večini primerov vam ni treba uporabiti zahtevne formule s funkcijo IF, temveč lahko uporabite funkcijo VLOOKUP. Ko uporabljate funkcijo VLOOKUP, morate 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.

Več podrobnosti o funkciji VLOOKUP najdete tukaj, vsekakor pa je bolj preprosta od zahtevne ugnezdene izjave IF z 12 stopnjami! 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 Office 365, preverite, ali imate najnovejšo različico sistema Office.

Preskusite Office 365 ali najnovejšo različico Excela

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

Videoposnetek: napredne funkcije IF
Funkcija IFS (Office 365, Excel 2016 in novejše različice)
Funkcija COUNTIF bo seštela vrednosti na podlagi enega pogoja
Funkcija COUNTIFS bo seštela vrednosti na podlagi več pogojev
Funkcija SUMIF bo seštela vrednosti na podlagi enega pogoja
Funkcija SUMIFS bo seštela vrednosti na podlagi več pogojev
Funkcija AND
Funkcija OR
Funkcija VLOOKUP
Pregled formul v Excelu
Kako se izogniti prelomljenim formulam
Zaznavanje napak v formulah s funkcijo za iskanje napak
Logične funkcije
Excelove funkcije (po abecedi)
Excelove funkcije (po kategoriji)

Razširite svoja znanja
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.

×