Ako používať viacero príkazov IF v programe Microsoft Excel

Príkaz IF v Exceli spustí logický test, ktorý vráti jednu hodnotu, ak je podmienka splnená, a inú hodnotu, ak nie je splnená. Použitie jedného príkazu IF v Exceli umožňuje iba základné operácie, ale ak vnoríte viacero príkazov IF, môžete vykonávať komplikované úlohy. Jedinou nevýhodou je, že pri zostavovaní vzorca musíte byť opatrní, aby ste sa vyhli chybám. Alternatívne funkcie Excelu môžu vykonávať rovnakú prácu s menším rizikom chyby. Naučte sa základy vnorených príkazov IF v Exceli a alternatívy na nájdenie najlepšieho prístupu pre vaše potreby.

Funkcia IF v Exceli v podstate hovorí „ak je to pravda, vráťte hodnotu x, ale ak nie, vráťte hodnotu y“. Napríklad, ak ste zisťovali, či rôzne oddelenia vo vašej firme boli pod alebo nad rozpočtom, môžete porovnať ich výdavky s ich rozpočtami pomocou funkcie a nechať ju vrátiť „nad rozpočet“, ak oddelenie minulo príliš veľa, alebo „v rámci rozpočtu“, ak nie. Vo všeobecnosti na vytvorenie svojho vyhlásenia používate porovnávacie operátory, ako napríklad:

• = (rovná sa)

• (väčší než)

• = (väčšie alebo rovné)

• < (menšie ako)
• <= (menší alebo rovný)
• <> (nerovná sa)

Pochopenie syntaxe príkazu Excel IF je zásadným krokom k tomu, aby ste sa ho naučili správne používať. Formát funkcie je IF(logický_test, hodnota_ak_pravda, [hodnota_ak_nepravda]) kde hranaté zátvorky označujú, že tento posledný argument je voliteľný. Tieto sa dajú ľahko interpretovať: The logický_test znamená vyhlásenie, ktoré chcete otestovať, hodnota_ak_pravda je miesto, kde chcete, aby Excel ukázal, ak je test pravdivý, a [value_if_false] je voliteľné miesto pre to, čo má Excel zobraziť, ak je test nepravdivý.

To stačí na to, aby ste použili vzorec v Exceli pre výkazy ak-potom, čo možno demonštrovať na príklade „Nad rozpočet/v rámci rozpočtu“ z predchádzajúceho. Predstavte si, že máte v stĺpci B sumu vynaloženú rôznymi oddeleniami a sumu rozpočtovanú v stĺpci C, pričom hodnoty začínajú v druhom riadku. Do bunky D2 by ste napísali =IF(B2>C2, "Nad rozpočet", "V rámci rozpočtu") vrátiť výsledok, ktorý hľadáte (upozorňujeme, že na vrátenie textu je potrebné použiť úvodzovky). Prvá časť hovorí „ak je hodnota v B2 väčšia ako hodnota v C2“ (ak sú výdavky väčšie ako rozpočet), druhá časť hovorí „potom vráťte text Nad rozpočet a tretia časť hovorí „ak nie, tak sa vráťte V rámci rozpočtu."

Maximálne využitie nástroja na príkazy IF v Exceli znamená možnosť kombinovať viacero príkazov IF, keď máte úlohu, ktorá je na to vhodná. Do pôvodného príkazu IF môžete zahrnúť druhý príkaz IF a do neho môžete zahrnúť ďalší príkaz IF atď. To je obzvlášť užitočné, ak chcete niečo kategorizovať do jednej z niekoľkých skupín pomocou jedného vzorca. Môžete napríklad použiť vnorené príkazy IF na konverziu skóre, ktoré študenti dosiahli v teste, na známku.

Ak chcete správne použiť viacero príkazov IF, zamyslite sa nad logikou toho, čo od Excelu požadujete. V podstate po vašej podmienke (prvý argument vo funkcii) môžete pridať ďalší príkaz IF do hodnota_ak_pravda alebo hodnota_ak_nepravda medzery, aby ste Excelu povedali, čo má robiť ďalej. Myslite na to ako na prinútenie Excelu, aby skontroloval bunku podľa druhého kritéria, ak daná bunka nespĺňa splniť prvé kritérium (alebo ak áno, ak spĺňa prvé), a v tomto procese môžete pokračovať dlho čas. Inými slovami, meníte „ak toto, tak tamto“ na „ak toto a toto, potom tamto“ alebo „ak nie toto, ale toto, tak tamto“.

Predstavte si, že ste dali študentom test, kde skóre 85 percent alebo vyššie je A, medzi 70 a 85 percentami je B, medzi 55 a 70 percentami je C, medzi 40 percentami a 55 percentami je D a čokoľvek nižšie je Zlyhanie. Body sú v stĺpci B, od riadku 2 po riadok 11. Na kategorizáciu skóre študentov môžete použiť starostlivo zostavenú funkciu Excel IF s viacerými podmienkami.

Prvá časť je jednoduchá (pre skóre v B2, v tomto príklade): =IF(B2>=85, "A") povie Excelu, aby vrátil A, ak študent dosiahol skóre 85 alebo vyššie. Vnorenie vám umožňuje pridať ďalšie známky pomocou tretieho argumentu (čo robiť, ak je podmienka nepravdivá) na pridanie ďalších podmienok: =IF(B2>=85; "A", IF(B2>=70; "B")), ktorý hovorí Excelu, aby vrátil A pre skóre 85 alebo vyššie, ale ak nie, vrátil B, ak je skóre vyššie ako 70. Pokračujte týmto spôsobom a dokončite vzorec, pričom zakaždým použite tretí argument pre nový príkaz IF: =AK(B2>=85, "A", IF(B2>=70, "B",AK(B2>=55, "C", IF(B2>=40, "D", "Nevyhovuje")) ))

Vyzerá to komplikovane, takže jeho rozobratie by malo túto myšlienku upevniť. Prvé dva argumenty hovoria „ak je skóre v B2 85 alebo viac, potom študent dostane A,“ a tretí hovorí, „inak, skontrolujte nasledujúcu podmienku“. The druhý IF vedie k tomuto tvrdeniu, "(pre študentov, ktorí dosiahli menej ako 85), ak je skóre v B2 70 alebo vyššie, potom študent dostane B, a ak nie, skontrolujte nasledujúca podmienka." Tretí IF hovorí: "(pre študentov, ktorí dosiahli menej ako 70), ak je skóre v B2 55 alebo vyššie, študent získal C, a ak nie, skontrolujte nasledujúca podmienka“ a štvrtá hovorí „(pre študentov, ktorí dosiahli skóre menej ako 55), ak je skóre v B2 vyššie ako 40, študent dostane D, ak nie, študent zlyhá."

Ak zadáte vzorec pre svoj príkaz IF správne, vráti sa jedna z možností, ktoré ste dali Excelu. Ak sa však pomýlite, vzorec môže vrátiť a 0 alebo #NÁZOV? v bunke obsahujúcej váš vzorec.

A 0 chyba znamená, že buď hodnota_ak_pravda alebo hodnota_ak_nepravda argument je prázdny. Vyriešte tento problém zabezpečením zadania hodnoty do oboch argumentov, alebo aspoň do hodnota_ak_pravda argument. The hodnota_ak_nepravda argument môže byť úplne odstránený a vráti FALSE, ak za znakom nevložíte druhú čiarku hodnota_ak_pravda lúka.

A #NÁZOV? Chyba vám hovorí, že ste pravdepodobne niečo vo vzorci napísali nesprávne alebo nesprávne. To môže byť trochu ťažšie opraviť, pretože to môže byť akákoľvek časť vzorca, ale najlepšou radou je všetko ešte raz skontrolovať alebo vzorec napísať znova.

Predchádzajúci príklad vnoreného vzorca funkcie IF bol dosť dlhý, čo je jedna z hlavných nevýhod používania viacerých príkazov IF v Exceli. Môžu sa skomplikovať a je ľahké urobiť chybu, pretože musíte starostlivo sledovať logiku, aby ste sa uistili, že všetko funguje tak, ako zamýšľate. Microsoft poukazuje na to, že nesprávne vnorený vzorec môže dokonca väčšinou vrátiť správny výsledok, ale v zriedkavých prípadoch nesprávne výsledky. Toto je ťažké rozpoznať.

Neuvedenie podmienok v správnom poradí je častou chybou. V príklade klasifikovaných testov, ak ste začali s nižším skóre =IF(B2>=40; "D", …" a pokračovali v zozname ostatných vo vzostupnom poradí, dokonca aj skóre 100 by vrátilo D. Je to preto, že prvá podmienka sa kontroluje ako prvá, takže ak ste začali s „ak je bunka B2 40 alebo vyššia, vráťte D" klasifikoval by akékoľvek skóre 40 alebo viac ako D bez toho, aby skontroloval čokoľvek iné, pretože spĺňa prvé stav. Toto je dokonalý príklad toho, prečo musíte dodržiavať logiku svojho vzorca.

Musíte sa tiež uistiť, že na koniec viacerých príkazov IF pridáte správny počet zátvoriek, aby sa vzorec správne uzavrel. Excel s tým pomáha farebnou koordináciou a zvýrazňovaním zátvoriek počas práce.

Technicky môžete vnoriť až 64 funkcií IF do jedného vzorca, ale spoločnosť Microsoft neodporúča používať túto funkciu z vyššie uvedených dôvodov. Veci sa rýchlo skomplikujú a je pravdepodobné, že ak sa pokúšate vnoriť viac ako 10 príkazov IF, pravdepodobne existuje lepší nástroj pre túto úlohu, ktorú už Excel obsahuje.

Najpohodlnejšou alternatívou funkcie IF je funkcia IFS, ktorá je verziou funkcia, ktorá vykonáva rovnakú úlohu ako vnorenie do jednej funkcie a dokáže zvládnuť až 127 podmienky. Funkcia IFS má jednoduchšiu syntax: =IFS(logický_test1, hodnota_ak_pravda1, [logický_test2, hodnota_ak_pravda2], [logický_test3, hodnota_ak_pravda3]…) a tak ďalej. V podstate to funguje ako vnorené príkazy IF, s výnimkou toho, že nasledujúce príkazy IF zaberajú miesto za „hodnotou, ak je pravda“ bez toho, aby bolo potrebné znova vyvolať funkciu.

Vzorec v príklade testovacej známky možno napísať oveľa jednoduchšie pomocou funkcie IFS. Namiesto:

=AK(B2>=85, "A", IF(B2>=70, "B",AK(B2>=55, "C", IF(B2>=40, "D", "Nevyhovuje")) ))

jednoducho napíš

=IFS(B2>=85; "A", B2>=70; "B", B2>=55; "C", B2>=40; "D", TRUE; "Nevyhovuje")

The PRAVDA v predposlednom mieste hovorí "ak nie je splnená žiadna z ostatných hodnôt, vráťte ďalšiu hodnotu."

Táto funkcia je dostupná iba v Exceli 2019 alebo Exceli pre Office 365.

Funkcia VLOOKUP v Exceli je často jednoduchšou alternatívou k viacerým príkazom IF a je dostupná v akejkoľvek verzii Excelu od roku 2007. Nie je ideálny pre všetky situácie, pretože buď vracia výsledky pre presnú zhodu alebo pre najbližšie číslo, takže ho nebolo možné použiť pre známky ako predchádzajúci príklad. V mnohých situáciách vám však môže ušetriť veľa práce s písaním vzorca, hoci musíte zostaviť tabuľku s možnými hodnotami v jednom stĺpec, napríklad produkty, ktoré váš obchod predáva, a to, čo chcete, aby funkcia vracala, napríklad cena produktu, v inom stĺpci pre príklad.

Syntax pre VLOOKUP je =VLOOKUP(vyhľadávacia_hodnota, tabuľkové_pole, stĺpec_index_num, [vyhľadávanie_rozsahu]) a oboznámenie sa s každým komponentom vám ukáže, ako ho používať. The vyhľadávaná_hodnota argument je hodnota, ktorú chcete skontrolovať, čo musí byť bunka v rovnakom stĺpci ako začiatok tabuľky, proti ktorej ju chcete skontrolovať, buď nad ňou alebo pod ňou. The table_array je rozsah buniek obsahujúcich vašu tabuľku informácií, ako sú produkty a ich zodpovedajúce ceny a col_index_num je stĺpec, v ktorom má Excel hľadať hodnotu, s ktorou sa má vrátiť 1 označujúci stĺpec úplne vľavo, 2 označenie ďalšieho atď. nakoniec [vyhľadávanie_rozsahu] je voliteľné, ale zadajte "TRUE", ak chcete, aby Excel zakladal odpoveď na najbližšej hodnote, alebo "FALSE", ak chcete iba presnú zhodu.