Funkcia IF – vnorené vzorce a ako sa vyhnúť nástrahám

Funkcia IF – vnorené vzorce a ako sa vyhnúť nástrahám

Funkcia IF umožňuje vykonávanie logických porovnaní medzi hodnotou a tým, čo očakávate, prostredníctvom testovania podmienok a vrátenia výsledkov v podobe hodnôt True alebo False.

  • =IF(niečo má hodnotu True, potom niečo urobte, inak urobte niečo iné)

Preto príkaz IF môže mať dva výsledky. Prvým výsledkom je, či vaše porovnanie má hodnotu True, a druhým výsledkom je, či vaše porovnanie má hodnotu False.

Príkazy IF sú neuveriteľne výkonné a tvoria základ mnohých modelov tabuľkových hárkov, zároveň sú však aj hlavnou príčinou mnohých problémov vyskytujúcich sa v tabuľkových hárkoch. V ideálnom prípade sa príkaz IF vzťahuje len na minimálne množstvo podmienok, napríklad na Muž/Žena, Áno/Nie/Možno. Niekedy je ale potrebné vypočítať oveľa zložitejšiu situáciu, ktorá si vyžaduje viac ako 3 vnorené* funkcie IF.

* Vnorenie predstavuje spájanie viacerých funkcií dokopy do jedného vzorca.

Používanie funkcie IF, jednej z logických funkcií na vrátenie jednej hodnoty, ak je podmienka pravdivá a druhej hodnoty, ak je nepravdivá.

Syntax

IF(logický_test; hodnota_ak_pravda; [hodnota_ak_nepravda])

Príklad:

  • =IF(A2>B2;"Prekročený rozpočet";"OK")

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

Názov argumentu

Popis

logický_test   

(povinné)

Predstavuje podmienku, ktorá sa má testovať.

hodnota_ak_pravda   

(povinné)

Predstavuje hodnotu, ktorá sa má vrátiť, ak sa argument logický_test vyhodnotí ako TRUE.

hodnota_ak_nepravda   

(voliteľné)

Predstavuje hodnotu, ktorá sa má vrátiť, ak sa argument logický_test vyhodnotí ako FALSE.

Poznámky

Hoci Excel vám umožní vnoriť až 64 rôznych funkcií IF, vôbec sa neodporúča tak urobiť. Prečo?

  • Viacero príkazov IF vyžaduje dobre si premyslieť, ako ich správne zostaviť a pamätať pritom na to, aby ich logika robila správne výpočty prostredníctvom každej podmienky až do konca. Ak ste nevnorili svoj vzorec na 100 % presne, potom možno bude fungovať na 75 %, ale vráti neočakávané výsledky v 25 % prípadov. Žiaľ, šanca, že zachytíte tých 25 %, je malá.

  • Spravovať viacnásobné príkazy IF môže byť neuveriteľne ťažké, najmä keď sa k nim vrátite neskôr a pokúšate sa zistiť, čo ste sa snažili urobiť, alebo, čo je ešte horšie, čo sa iní snažili urobiť.

Ak za vám zdá, že príkaz IF stále rastie a nikam nevedie, je načase dať si prestávku a popremýšľať nad ďalším postupom.

Poďme sa spoločne pozrieť na to, ako sa dá pomocou viacerých funkcií IF správnym spôsobom vytvoriť zložitý vnorený príkaz IF a kedy je lepšie použiť iný nástroj Excelu.

Príklady

Nižšie nájdete príklad relatívne bežného vnoreného príkazu IF, ktorý slúži na prevod výsledkov testov študentov na ich ekvivalentnú známku.

Zložitý vnorený príkaz IF – vzorec v bunke 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"))))

    Tento zložitý vnorený príkaz IF má jednoduchú logickú postupnosť:

  1. Ak je výsledok testu (v bunke D2) väčší ako 89, študent dostane známku A.

  2. Ak je výsledok testu väčší ako 79, študent dostane známku B.

  3. Ak je výsledok testu väčší ako 69, študent dostane známku C.

  4. Ak je výsledok testu väčší ako 59, študent dostane známku D.

  5. V opačnom prípade dostane študent známku F.

Tento konkrétny príklad je relatívne nenáročný, pretože je nepravdepodobné, že by sa korelácia medzi výsledkami testov a známkami v budúcnosti zmenila, a preto nie je ani údržba tohto príkladu náročná. Čo však v prípade, ak chcete známku A ešte podrobnejšie rozlíšiť ako A+, A a A- (atď)? Štyri podmienky príkazu IF sa budú musieť rozšíriť na 12 podmienok. Váš vzorec bude vyzerať takto:

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

Príkaz je aj naďalej funkčne presný a bude fungovať podľa očakávaní, jeho zápis a otestovanie, či slúži na to, na čo má, však bude trvať dlhšie. Ďalším problémom je, že výsledky a príslušnú známku musíte zadávať manuálne. Aká je pravdepodobnosť výskytu neúmyselných preklepov? Predstavte si, že musíte tento krok zopakovať 64-krát s ešte zložitejšími podmienkami. Áno, dá sa to zvládnuť, ale naozaj chcete vynaložiť až také veľké úsilie a urobiť pritom aj pár chýb, ktoré je potom dosť ťažké vôbec zaregistrovať?

Tip : V každej funkcii v Exceli musia byť ľavé aj pravé zátvorky (). Excel vám pomôže zistiť, kde treba čo zadať, a to farebným rozlíšením jednotlivých častí vzorca pri vykonávaní úprav. Ak chcete napríklad zmeniť vyššie uvedený vzorec a pri pohybe kurzorom prejdete za jednotlivé pravé zátvorky, príslušná ľavá zátvorka sa zmení na rovnakú farbu. Môže to byť užitočné, keď v zložitých vnorených vzorcoch chcete skontrolovať, či ste zadali všetky ľavé aj pravé zátvorky.

Ďalšie príklady

Uvádzame bežný príklad výpočtu provízie z predaja na základe úrovní dosiahnutého príjmu.

Vzorec v bunke 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)))))

Tento vzorec znamená: IF(C9 je väčšie ako 15 000, potom sa vráti 20 %, IF(C9 je väčšie ako 12 500, potom sa vráti 17,5 %, atď...

Tento príklad je podobný tomu prechádzajúcemu so známkami, je to však najmä príklad toho, aké zložité môže byť udržiavanie dlhých príkazov IF – čo je potrebné urobiť, keď sa vaša organizácia rozhodne pridať nové úrovne kompenzácie, prípadne ako zmeniť použitý dolár na inú menu alebo percentuálne hodnoty. Mali by ste s tým veľmi veľa práce.

Tip : Ak chcete, aby boli dlhé vzorce jednoduchšie čitateľné, môžete do riadka vzorcov vložiť zlomy riadkov. Pred textom, ktorý chcete zalomiť do nového riadka, stlačte jednoducho kombináciu klávesov ALT + ENTER.

Tu je príklad scenára s províziou v nesprávnom logickom poradí:

Vzorec D9 je v nasledujúcom nesprávnom poradí =IF(C9>5000;10%;IF(C9>7500;12,5%;IF(C9>10000;15%;IF(C9>12500;17,5%;IF(C9>15000;20%;0)))))

Vidíte, kde je chyba? Porovnajte poradie porovnaní príjmov s predchádzajúcim príkladom. Ktorým smerom sa to uberá? Správne. Smeruje to zdola nahor (5 000 € – 15 000 €) a nie opačne. Prečo to však predstavuje až taký veľký problém? Pretože vzorec nemôže vynechať prvé hodnotenie akejkoľvek hodnoty nad sumu 5 000 €. Povedzme, že sme získali z príjmov 12 500 €. Príkaz IF vráti hodnotu 10 %, pretože je väčšia ako 5 000 €, a zastaví sa. Toto môže byť neuveriteľne problematické, pretože si tieto typy chýb v mnohých situáciách ani len nevšimneme. Prezradí ich až ich negatívny vplyv. Čo by ste teda robili, keby ste vedeli, že sa v zložitých vnorených príkazoch IF nachádzajú nástrahy? Vo väčšine prípadov môžete namiesto vytvárania zložitých vzorcov pomocou funkcie IF použiť funkciu VLOOKUP. Ak chcete použiť funkciu VLOOKUP, musíte najprv vytvoriť referenčnú tabuľku:

Vzorec v bunke D2 je =VLOOKUP(C2;C5:D17;2;TRUE)
  • =VLOOKUP(C2;C5:D17;2;TRUE)

Tento vzorec vyhľadá hodnotu z bunky C2 v rozsahu C5:C17. Ak sa hodnota nájde, vráti sa príslušná hodnota z rovnakého riadka do stĺpca D.

Vzorec v bunke C9 je =VLOOKUP(B9;B2:C6;2;TRUE)
  • =VLOOKUP(B9;B2:C6;2;TRUE)

Tento vzorec podobne vyhľadá hodnotu z bunky B9 v rozsahu B2:B22. Ak sa hodnota nájde, vráti sa príslušná hodnota z rovnakého riadka do stĺpca C.

Poznámka : Oba príkazy VLOOKUP použijú argument TRUE na konci vzorcov, čo znamená, že môžeme pomocou nich vyhľadávať približné zhody. Inými slovami – budú sa vyhľadávať zhody s presnými hodnotami vo vyhľadávacej tabuľke a tiež akékoľvek hodnoty, ktoré medzi ne spadajú. V tomto prípade musia byť vyhľadávacie tabuľky zoradené vzostupne od najmenšieho po najväčšie.

Ďalšie informácie o funkcii VLOOKUP nájdete tu, toto je však oveľa jednoduchšie ako zložitý vnorený príkaz IF s 12 úrovňami. Objavte aj ďalšie výhody, ktoré nie sú také zjavné:

  • Referenčné tabuľky funkcie VLOOKUP sa rýchlo otvárajú a jednoducho prezerajú.

  • Možno ich tiež jednoducho aktualizovať a v prípade zmien podmienok sa vzorca nemusíte ani dotknúť.

  • Ak nechcete, aby používatelia vašu referenčnú tabuľku videli alebo ju používali, vložte ju do iného hárka.

Viete, že?

V súčasnosti je k dispozícii funkcia IFS, pomocou ktorej môžete viaceré vnorené príkazy IF nahradiť jednou funkciou. Namiesto prvého príkladu so známkami, kde boli 4 vnorené funkcie IF:

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

môžeme vzorec zjednodušiť pomocou funkcie IFS:

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

Používanie funkcie IFS je skvelé, pretože sa nemusíte zaoberať všetkými tými príkazmi IF a zátvorkami.

Poznámka : Táto funkcia je k dispozícii len vtedy, ak máte predplatné na Office 365. Ak ste predplatiteľom služieb Office 365, uistite sa, že používate najnovšiu verziu balíka Office.

Vyskúšajte Office 365 alebo najnovšiu verziu Excelu

Potrebujete ďalšiu pomoc?

Vždy sa môžete opýtať odborníka v komunite technikov pre Excel, získať podporu v rámci komunity lokality Answers alebo navrhnúť novú funkciu či vylepšenie na lokalite Excel User Voice.

Súvisiace témy

Video: Pokročilé funkcie IF
Funkcia IFS (Office 365, Excel 2016 a novšie verzie)
Funkcia COUNTIF spočíta počet hodnôt na základe jedného kritéria
Funkcia COUNTIFS spočíta počet hodnôt na základe viacerých kritérií
Funkcia SUMIF spočíta hodnoty na základe jedného kritéria
Funkcia SUMIFS spočíta hodnoty na základe viacerých kritérií
Funkcia AND
Funkcia OR
Funkcia VLOOKUP
Prehľad vzorcov v Exceli
Zabránenie vzniku nefunkčných vzorcov
Zisťovanie chýb vo vzorcoch pomocou kontroly chýb
Logické funkcie
Zoznam funkcií Excelu (podľa abecedy)
Zoznam funkcií Excelu (podľa kategórie)

Rozšírte svoje zručnosti
Preskúmať školenie
Buďte medzi prvými, ktorí získajú nové funkcie
Pridajte sa k insiderom pre Office

Boli tieto informácie užitočné?

Ďakujeme za vaše pripomienky!

Ďakujeme vám za pripomienky. Pravdepodobne vám pomôže, ak vás spojíme s pracovníkom podpory pre Office.

×