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.

Ak sú výkazy neuveriteľne robustné a tvoria základ mnohých tabuľkových modelov, ale sú tiež hlavnou príčinou mnohých problémov s tabuľkovým hárkom. V ideálnom prípade by sa príkaz IF mal vzťahovať na minimálne podmienky, ako napríklad samec/samica, áno/nie/možno, aby sme vymenovali aspoň niektoré, ale niekedy možno budete musieť vyhodnotiť zložitejšie scenáre, ktoré vyžadujú Vnorenie * viac než 3, ak budú fungovať spoločne.

* 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, čo sa deje? Porovnajte poradie porovnaní výnosov s predchádzajúcim príkladom. Akým spôsobom je to jedna ide? To je pravda, to bude od zdola nahor ($5 000 až $15 000), nie naopak. Ale prečo by to malo byť taký veľký problém? Je to veľký problém, pretože vzorec nemôže prejsť prvým vyhodnotením pre ľubovoľnú hodnotu cez $5 000. Povedzme, že máte $12 500 v príjmoch – príkaz IF vráti 10%, pretože je väčší ako $5 000 a zastaví sa tam. Môže to byť nesmierne problematické, pretože v mnohých situáciách sa tieto typy chýb neprejavia bez povšimnutia, kým neovplyvnia negatívny vplyv. S vedomím, že existuje niekoľko vážnych úskalia s komplexnou vnorenou IF statements, čo môžete urobiť? Vo väčšine prípadov môžete použiť funkciu VLOOKUP namiesto vytvárania zložitých vzorcov pomocou funkcie IF. Pomocou funkcie VLOOKUPmusíte najprv vytvoriť odkazovú 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.

Funkcia VLOOKUP je na tomto miesteoveľa podrobnejšie, ale je to oveľa jednoduchšie než 12-úrovni, zložitá vnorená if Statement. 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 Microsoft 365. Ak ste predplatiteľom služieb Microsoft 365, uistite sa, že používate najnovšiu verziu balíka Office.

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: rozšírené funkcie
if(funkcia) IFS (Microsoft 365, Excel 2016 a novšia verzia)
funkcia COUNTIF spočíta hodnoty na základe jedného kritéria
, funkcia COUNTIFS spočíta hodnoty na základe viacerých kritérií
, funkcia SUMIF spočíta hodnoty na základe jedného kritéria
, ktoré bude funkcia SUMIFS sčítanie hodnôt založených na viacerých kritériách
a funkciách
alebo
funkciách funkcieVLOOKUP
– Prehľad vzorcov v Exceli
ako zabrániť rozbitým vzorcom
rozpoznať chyby vo vzorcoch
logické funkcie
funkcie programu Excel (podľa abecedy)
excelových funkcií (podľa kategórie)

Poznámka:  Táto stránka bola preložená automaticky a môže obsahovať gramatické chyby alebo nepresnosti. Naším cieľom je, aby bol tento obsah pre vás užitočný. Môžete nám dať vedieť, či boli tieto informácie pre vás užitočné? Tu nájdete anglický článok ako referenciu.

Rozšírte svoje zručnosti práce s balíkom Office
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.

×