Funkcija IF – ugniježđene formule i izbjegavanje problema

Funkcija IF – ugniježđene formule i izbjegavanje problema

Funkcija IF omogućuje logično uspoređivanje vrijednosti i očekivane vrijednosti testiranjem uvjeta i prikazom rezultata ako je ishod ima vrijednost True ili False.

  • =IF(ako nešto ima vrijednost True (istinito), učini jednu radnju, a ako nije istinito, učini drugu radnju)

Stoga funkcija IF može imati dva rezultata. Prvi će se rezultat prikazati ako je usporedba True (istinita), a drugi ako je usporedba False (neistinita).

Naredbe IF nevjerojatno su robusne i čine osnovu mnogih modela proračunskih tablica, ali i uzrok su mnogih problema s proračunskim tablicama. Naredba IF bi, u idealnom slučaju, primijenila minimalne uvjete, kao što su Muško/Žensko, Da/Ne/Možda, ali ponekad ćete morati procijeniti kompleksnije scenarije za koje je potrebno ugnijezditi* više od 3 funkcije IF zajedno.

* “Ugnježđivanje” se odnosi na združivanje više funkcija u jednu formulu.

Funkciju IF, jednu od logičkih funkcija, koristite da biste dobili jednu vrijednost ako je uvjet ispunjen (true), a drugu ako nije (false).

Sintaksa

IF(logički_test; vrijednost_ako_je_true; [vrijednost_ako_je_false])

Na primjer:

  • =IF(A2>B2;"Izvan okvira proračuna";"U redu")

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

Naziv argumenta

Opis

logički_test   

(obavezno)

Uvjet koji ispitujete.

vrijednost_ako_je_true   

(obavezno)

Vrijednost koju želite da funkcija vrati ako je rezultat argumenta logički_test TRUE.

vrijednost_ako_je_false   

(neobavezno)

Vrijednost koju želite da funkcija vrati ako je rezultat argumenta logički_test FALSE.

Napomene

Iako vam Excel dopušta ugniježđivanje najviše 64 različite funkcije IF, t se ne preporučuje. Zašto?

  • Da biste koristili više naredbi IF, potrebno je mnogo razmišljanja prilikom pravilnog sastavljanja. Provjerite može li njihova logika izračunati ispravno svaki uvjet do kraja. Ako morate ugnijezditi formulu uz preciznost od 100 %, formula bi mogla funkcionirati u 75 % slučajeva, ali u 25 % slučajeva vratit će neočekivane rezultate. Nažalost, vjerojatnost postizanja 25 % je mala.

  • Održavanje višestrukih naredbi IF može postati nevjerojatno složeno, posebno kada se vratite nakon nekog vremena i pokušate shvatiti što ste vi ili, još gore, netko drugi, pokušali učiniti.

Ako imate naredbu IF koja ne prestaje rasti, vrijeme je da pustite miš i smislite novu strategiju.

Pogledajmo kako pravilno stvoriti kompleksnu ugniježđenu IF naredbu pomoću više IF naredbi i kako prepoznati da je vrijeme da koristite neki drugi alat programa Excel.

Primjeri

Slijedi primjer relativno standardne ugniježđene naredbe IF za pretvaranje rezultate testova učenika u ekvivalent slovne ocjene.

Kompleksna složena naredba IF – Formula u ćeliji E2 glasi =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"))))

    Ova kompleksna ugniježđena IF naredba slijedi izravnu logiku:

  1. Ako je Rezultat testa (u ćeliji D2) veći od 89, učenik će dobiti ocjenu A

  2. Ako je Rezultat testa (u ćeliji D2) veći od 79, učenik će dobiti ocjenu B

  3. Ako je Rezultat testa (u ćeliji D2) veći od 69, učenik će dobiti ocjenu C

  4. Ako je Rezultat testa (u ćeliji D2) veći od 59, učenik će dobiti ocjenu D

  5. Učenik će u suprotnom dobiti ocjenu F

Ovaj je primjer relativno siguran jer nije vjerojatno da će se veza između rezultata testa i slovnih ocjena promijeniti, stoga nije potrebno mnogo održavanja. No pitanje je što ako morate segmentirati ocjene između A+, A i A- (itd.)? Sada je vašu IF naredbu s četiri stupnja potrebno ponovno napisati tako da ima 12 stupnjeva! Evo kako bi vaša formula sada trebala izgledati:

  • =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 točna i funkcionirat će kao što se i očekuje, ali trebat će vam više vremena da je napišete i testirate da biste bili sigurni da čini ono što želite. Još jedan očiti problem jest činjenica da ste morali ručno unijeti rezultate i ekvivalentne slovne ocjene. Kolika je vjerojatnost da ćete slučajno nešto pogrešno napisati? Sada zamislite da to morate napraviti 64 puta uz mnogo složenije uvjete! Naravno, moguće je, ali želite li se doista toliko namučiti i vjerojatno napraviti pogreške koje će biti zaista teško uočiti?

Savjet : Za svaku su funkciju u programu Excel potrebne lijeva i desna zagrada (). Excel će vam pokušati pomoći da utvrdite gdje što ide različitim bojama dijelova formule kada je uređujete. Ako ste, na primjer, namjeravali urediti gore navedenu formulu, pomicanjem pokazivača uz svaku desnu zagradu „) odgovarajuća lijeva zagrada poprimit će istu boju. To će vam doista pomoći kada koristite kompleksne ugniježđene formule da biste utvrdili imate li dovoljno odgovarajućih zagrada.

Dodatni primjeri

U nastavku su navedeni vrlo općeniti primjeri izračuna Provizije prodaje na temelju razina ostvarenog Prihoda.

Formula u ćeliji D9 glasi 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)))))

Ova formula kaže IF(C9 je veće od 15 000 prikazat će se vrijednost od 20%, IF(C9 je veće od 12 500, prikazat će se vrijednost od 17,5% itd...

Iako je ova formula vrlo slična prethodnom primjeru s ocjenama, odličan je primjer kako je teško odražavati velike IF naredbe – što biste učinili ako vaša tvrtka ili ustanova odluči dodati nove kompenzacijske razine pa čak i promijeniti postojeće vrijednosti dolara ili postotka? Imali biste pune ruke posla!

Savjet : U traku za formulu možete unijeti prijelome redaka da biste jednostavnije čitali duge formule. Jednostavno pritisnite kombinaciju tipku ALT + ENTER ispred teksta koji želite prelomiti u novi redak.

Ovo je primjer scenarija provizije s neispravnom logikom:

Formula u ćeliji D9 nije ispravna, a glasi =IF(C9>5000;10%;IF(C9>7500;12,5%;IF(C9>10000;15%;IF(C9>12500;17,5%;IF(C9>15000;20%;0)))))

Vidite li što nije u redu? Usporedite redoslijed usporedbi prihoda s prethodnim primjerom. U kojem smjeru ide? Tako je. Ide odozdo prema gore (od 5000 USD prema 15 000 USD), a ne obratno. Ali zašto je to važno? Važno je jer formula ne može izvršiti prvu procjenu za bilo koju vrijednost iznad 5000 USD. Recimo da imate 12 500 USD prihoda – IF naredba prikazat će 10% jer je veće od 5000 i ondje će stati. To može biti velik problem jer u mnogim situacijama takve vrste pogrešaka prolaze neprimijećene sve dok ne prouzrokuju nevolje. Što možete učiniti ako znate da postoje ozbiljne opasnosti prilikom korištenja kompleksnih ugniježđenih IF naredbi? U većini slučajeva možete koristiti funkciju VLOOKUP umjesto pisanja kompleksnih formula s IF funkcijom. Kada koristite VLOOKUP, najprije morate stvoriti referentnu tablicu:

Formula u ćeliji D2 glasi =VLOOKUP(C2;C5:D17;2;TRUE)
  • =VLOOKUP(C2;C5:D17;2;TRUE)

Formula kaže da potražite vrijednosti u ćeliji C2 u rasponu C5:C17. Ako ste vrijednost pronašli, vratite se na odgovarajuću vrijednost iz istog retka u stupcu D.

Formula u ćeliji C9 glasi =VLOOKUP(B9,B2:C6,2,TRUE)
  • =VLOOKUP(B9;B2:C6;2;TRUE)

Formula na sličan način traži vrijednost u ćeliji B9 u rasponu B2:B22. Ako ste vrijednost pronašli, vratite se na odgovarajuću vrijednost iz istog retka u stupcu C.

Napomena : Obje formule VLOOKUP koriste argument TRUE na kraju formule, što znači da želimo da potraže odgovarajući podudarni rezultat. Drugim riječima, podudarat će se s odgovarajućim vrijednostima u tablici s vrijednostima te svim vrijednostima koje se nalaze između njih. U ovom je slučaju tablice s vrijednostima potrebno sortirati uzlaznim redoslijedom, od najmanje prema najvećoj.

VLOOKUP je ovdje opisan vrlo detaljno, no to je zasigurno mnogo jednostavnije od složene ugniježđene IF naredbe s 12 razina! Postoje i manje očite prednosti:

  • Referentne tablice za VLOOKUP pristupačne su i pregledne.

  • Vrijednosti tablice mogu se jednostavno ažurirati te nikada nećete morati dirati formulu ako se uvjeti promjene.

  • Ako ne želite da drugi vide vašu referentnu tablicu ili mijenjati je, prebacite je na drugi radni list.

Jeste li znali?

Sada je na raspolaganjuIFS funkcija koja može zamijeniti više ugniježđenih IF naredbi jednom funkcijom. Umjesto našeg primjera s ocjenama koji ima 4 ugniježđene IF funkcije:

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

Može se uvelike pojednostavniti jednom funkcijom IFS:

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

Funkcija IFS je odlična jer ne trebate brinuti o svim naredbama IF i zagradama.

Napomena : Ta je značajka dostupna samo ako imate pretplatu na Office 365. Ako ste pretplatnik sustava Office 365, provjerite imate li najnoviju verziju sustava Office.

Isprobajte Office 365 ili najnoviju verziju programa Excel

Je li vam potrebna dodatna pomoć?

Postavite pitanje stručnjaku u tehničkoj zajednici za Excel, zatražite podršku u zajednici za odgovore ili predložite novu značajku ili poboljšanje na forumu za Excel User Voice.

Dodatni sadržaji

Prikaz videozapisa o načinu korištenja IF funkcije

IFS funkcija (Office 365, Excel 2016 i novije verzije)

Funkcija COUNTIF broji vrijednosti na temelju više uvjeta

Funkcija COUNTIFS broji vrijednosti na temelju više uvjeta

Funkcija SUMIF zbraja vrijednosti na temelju jednog uvjeta

Funkcija SUMIFS zbraja vrijednosti na temelju više uvjeta

Funkcija AND

Funkcija OR

Funkcija VLOOKUP

Pregled formula u programu Excel

Izbjegavanje neispravnih formula

Pronalaženje pogrešaka u formulama pomoću značajke provjere pogrešaka

Logičke funkcije

Funkcije programa Excel (abecednim redoslijedom)

Funkcije programa Excel (po kategorijama)

Proširite svoje vještine
Istražite osposobljavanje

Jesu li vam ove informacije bile korisne?

Hvala vam na povratnim informacijama!

Hvala vam na povratnim informacijama! Čini se da bi vam pomoglo kad bismo vas povezali s nekim od naših agenata podrške za Office.

×