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 zmogljive in tvorijo osnovo za številne modele preglednic, so pa tudi korenski vzrok številnih težav s preglednicami. Izjava IF naj bi v idealnem primeru veljala za minimalne pogoje, na primer »Moški/ženska« in »Da/ne/morda«. Toda včasih boste morali ovrednotiti bolj zapletene scenarije, ki zahtevajo gnezdenje* več kot 3 funkcij IF 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 primer zelo podoben primeru prejšnjih ocen, je ta formula odličen primer, kako težko je vzdrževati velike izjave IF. Kaj bi morali narediti, če bi vaša organizacija dodala nove stopnje nadomestila in morda celo spremenila obstoječe vrednosti v dolarjih ali odstotkih? 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 vidite, kaj je narobe? Primerjajte vrstni red primerjav prihodka s prejšnjim primerom. V katero smer gre ta? Tako je, od spodaj navzgor (5.000 $ na 15.000 $), ne obratno. Toda zakaj bi to moralo biti tako pomembno? To je veliko zato, ker formula ne more prenesti prve ocene za katero koli vrednost nad 5000 $. Recimo, da ste dobili 12.500 $ prihodka – izjava IF bo vrnila 10 %, ker je večja od 5.000 $ in tam bo tudi zaujšana. To je lahko zelo težavno, saj v veliko primerih teh vrst napak neonemamo, dokler niso negativno vplivale na njih. Kaj lahko naredite, če veste, da obstajajo resne paste z zapletenimi ugnezdenimi izjavami IF? V večini primerov lahko uporabite funkcijo VLOOKUP namesto zapletene formule s funkcijo IF. Če 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 funkcijo VLOOKUP je na voljo tukaj,vendar je to bolj preprosto 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 Microsoft 365. Če ste naročnik na Microsoft 365, preverite, ali imate najnovejšo različico Officea.

Povezava do preskusa ali nakupa okolja Microsoft 365

Potrebujete dodatno pomoč?

Kadar koli lahko zastavite vprašanje strokovnjaku v skupnosti tehničnih strokovnjakov za Excel ali pridobite podporo v skupnosti Answers.

Sorodne teme

Videoposnetek: Napredne funkcije IF
Funkcija IFS (Microsoft 365, Excel 2016 in novejše različice)
Funkcija COUNTIF bo štela vrednosti na podlagi enega pogoja
Funkcija COUNTIFS bo štela vrednosti na podlagi več pogojev
Funkcija SUMIF bo seštevla vrednosti na podlagi enega pogoja
Funkcija SUMIFS bo seštevla vrednosti na podlagi več pogojev
Funkcija AND
Funkcija OR
Funkcija VLOOKUP
Pregled formul v Excel
Kako se izogniti neskončenim formulam
Zaznavanje napak v formulah
Logične funkcije
Excel funkcije (po abecedi)
Excel (po kategoriji)

Ali potrebujete dodatno pomoč?

Razširite svoja znanja
Oglejte si izobraževanje

Vam je bila informacija v pomoč?

Kako ste zadovoljni s kakovostjo prevoda?
Kaj je vplivalo na vašo izkušnjo?

Zahvaljujemo se vam za povratne informacije.

×