Funkcija IF – ugnežđene formule i izbegavanje grešaka

Napomene

Iako Excel omogućava da ugnezdite do 64 različite IF funkcije, to se ne preporučuje. Zašto?

• Više IF izjava zahteva dosta razmišljanja da biste ih pravilno napravili i utvrdili da li njihova logika može ispravno da izračuna svaki uslov do samog kraja formule. Ako formulu ne ugnezdite 100% tačno, ona će možda funkcionisati u 75% vreme, ali će u 25% vremena vratiti neočekivane rezultate. Nažalost, veoma su male šanse da ćete primetiti tih 25% neočekivanih rezultata.

• Veliki broj IF izjava može biti izuzetno težak za održavanje, posebno ako se na njih vratite nakon nekog vremena i pokušate da shvatite šta ste vi, ili još gore neko drugi, želeli da postignete.

Ako imate IF izjavu koja izgleda da raste bez ičega, vreme je da spustite miš i da ponovo proučite strategiju.

Hajde da pogledamo kako da ispravno kreirate kompleksnu ugnežđenu IF izjavu pomoću više IF-a i kada da prepoznate da je vreme da koristite drugu alatku u Excel arsenalu.

Primeri

Sledi primer relativno standardne ugnežđene IF izjave koja konvertuje rezultate sa učeničkih testova u ocenu u obliku slova.

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

  Ova kompleksna ugnežđena IF izjava prati jednostavnu logiku:

1. Ako je rezultat na testu (u ćeliji D2) veći od 89, učenik dobija A

2. Ako je rezultat na testu veći od 79, učenik dobija B

3. Ako je rezultat na testu veći od 69, učenik dobija C

4. Ako je rezultat na testu veći od 59, učenik dobija D

5. U drugim slučajevima, učenik dobija F

Ovaj konkretni primer je relativno bezbedan jer se verovatno neće promeniti korelacija između rezultata na testu i ocene na testu, tako da neće zahtevati previše održavanja. Ali, evo neke misao – šta ako treba da segmentišete ocene između A+, A i A- (i tako dalje)? Sada ovu IF izjavu sa četiri uslova treba da prepišete da biste dobili 12 uslova! Evo kako bi sada izgledala vaša formula:

• =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"))))))))))))

I dalje je funkcionalno tačan i radiće na očekivani način, ali pisanje i duže testiranje traje kako biste se uverili da radi ono što želite. Još jedan veliki problem je to što ste ručno morali da unesete ocene i jednake ocene. Kakve su љanse da жeљ sluиajno imati kucanje? Zamislite sada da ovo morate da uradite 64 puta sa kompleksnijim uslovima! Moguće je, ali da li zaista želite da podležete takvoj vrsti napora i verovatno grešaka koje ćete zaista teško uočiti?

Dodatni primeri

Sledi veoma čest primer izračunavanja provizije na prodaju na osnovu dostignutog nivoa prihoda.

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

Ova formula kaže: AKO(C9 je veće od 15.000, prikaži 20%, AKO(C9 je veće od 12.500, prikaži 17,5% itd...

Iako je veoma slična prethodnom primeru sa ocenama, ova formula je odličan primer koliko održavanje velikih IF izjava može biti teško – šta bi trebalo da uradite ako bi vaša organizacija odlučila da doda nove nivoe kompeencije i možda čak i promeni postojeće vrednosti dolara ili procenata? Imaćete mnogo posla!

Evo primera sa izračunavanjem provizije u kojem logika nije na mestu:

Moћeљ li da vidiљ љta nije u redu? Uporedite redosled poređenja prihoda sa prethodnim primerom. U kom smeru se ovaj kreće? Tako je, kretaće se odozdo nagore (od 5.000 USD do 15.000 USD), a ne u drugom uglu. Ali, zašto je to toliko veliki problem? To je velika stvar jer formula ne može da prođe prvu procenu ni za koju vrednost veću od 5.000 USD. Recimo da imate prihod od 12.500 USD – IF izjava će vratiti 10% jer je veća od 5000 USD i tu će stati. To može biti neverovatno problematično jer u mnogim situacijama ovakve greške ne budu zanemaćene dok ne imaju negativan uticaj. Pošto znate da rad sa kompleksnim ugnežđenim IF izjavama sadrži neke ozbiljne zamke, šta možete da uradite? U većini slučajeva možete da koristite funkciju VLOOKUP umesto da pravite kompleksnu formulu pomoću funkcije IF. Kada koristite VLOOKUP, prvo morate da napravite referentnu tabelu:

• =VLOOKUP(C2,C5:D17,2,TRUE)

Ova formula kaže da treba tražiti vrednost iz ćelije C2 u opsegu C5:C17. Ako se vrednost pronađe, vraća se odgovarajuća vrednost iz istog reda u koloni D.

• =VLOOKUP(B9,B2:C6,2,TRUE)

Slično tome, ova formula traži vrednost iz ćelije B9 u opsegu B2:B22. Ako se vrednost pronađe, vraća se odgovarajuća vrednost iz istog reda u koloni C.

Ovde je mnogo detaljnije objašnjeno VLOOKUP , ali ovo je sigurno mnogo jednostavnije od kompleksne ugnežđene IF izjave od 12 nivoa! Postoje i neke druge, manje očigledne, prednosti:

• VLOOKUP referentne tabele su otvorene i lako ih je pregledati.

• Vrednosti u tabeli se lako mogu ažurirati i nikada ne morate da menjate formulu ako se uslovi promene.

• Ako ne želite da ljudi vide ili ometaju referentnu tabelu, samo je stavite na drugi radni list.

Da li ste znali?

Sada postoji funkcija IFS koja može da zameni više ugrađenih IF izjava jednom funkcijom. Na osnovu toga bismo prvi primer sa ocenama, koji ima 4 ugnežđene IF funkcije,

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

mogli znatno da pojednostavimo jednom IFS funkcijom:

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

Funkcija IFS je odlična zato što ne morate da brinete o svim tim IF izjavama i zagradama.

Potrebna vam je dodatna pomoć?

Možete uvek da postavite pitanje stručnjaku u Excel Tech zajednici ili da potražite pomoć u Zajednicama.

Srodne teme

IFS (Microsoft 365, Excel 2016 i novije verzije)
Funkcija COUNTIF će računati vrednosti na osnovu jednog kriterijumaFunkcija COUNTIFS


prebrojava vrednosti na osnovu više kriterijuma SUMIF će sabrati vrednosti na osnovu jednog kriterijuma SUMIFS
funkcija će sabrati vrednosti na osnovu više kriterijuma FunkcijaAND
OR
FunkcijaVLOOKUP
Pregled formula u programuExcel

Kako izbeći neispravne formule Otkrijte greške uformulama

Logičkefunkcije Excel funkcije (abecednim redom)
Excel funkcije (po kategoriji)