Jak používat více příkazů IF v aplikaci Microsoft Excel

Příkaz IF v Excelu spustí logický test, který vrátí jednu hodnotu, pokud je podmínka splněna, a jinou hodnotu, pokud není. Pomocí jednoho příkazu IF aplikace Excel lze použít pouze základní operace, ale pokud vnoříte více příkazů IF, můžete provádět složité úkoly. Jedinou nevýhodou je, že musíte být opatrní při konstrukci vzorce, abyste se vyhnuli chybám. Alternativní funkce aplikace Excel mohou provádět stejnou práci s menším rizikem chyby. Naučte se základy vnořených příkazů IF v Excelu a alternativy k nalezení nejlepšího přístupu pro vaše potřeby.

Funkce KDYŽ v Excelu v podstatě říká „pokud je to pravda, vrátí hodnotu x, ale pokud není, vrátí hodnotu y“. Pokud jste například zjišťovali, zda jsou různá oddělení ve vaší firmě byly pod nebo nad rozpočtem, můžete porovnat jejich výdaje s jejich rozpočty pomocí funkce a nechat ji vrátit „Přes rozpočet“, pokud oddělení utratilo příliš mnoho, nebo „V rámci rozpočtu“, pokud ne. Obecně řečeno, k vytvoření prohlášení používáte porovnávací operátory, jako například:

• = (rovná se)

• (větší než)

• = (větší nebo rovno)

• < (menší než)
• <= (menší nebo rovno)
• <> (není rovno)

Pochopení syntaxe příkazu Excel IF je zásadním krokem k tomu, abyste se jej naučili správně používat. Formát funkce je KDYŽ(logický_test, hodnota_pokud_pravda, [hodnota_pokud_nepravda]) kde hranaté závorky označují, že tento poslední argument je volitelný. Tyto jsou snadno interpretovatelné: The logický_test znamená prohlášení, které chcete otestovat, value_if_true je místo, kde chcete, aby Excel ukázal, zda je test pravdivý, a [value_if_false] je volitelné místo pro to, co má Excel zobrazit, pokud je test nepravdivý.

To stačí k tomu, abyste použili vzorec v Excelu pro výkazy if-then, což lze demonstrovat na příkladu „Překročení rozpočtu/v rámci rozpočtu“ z dříve. Představte si, že máte částku vynaloženou různými odděleními ve sloupci B a částku rozpočtovanou ve sloupci C, přičemž hodnoty začínají na druhém řádku. Do buňky D2 byste zadali =IF(B2>C2, "Přes rozpočet", "V rámci rozpočtu") vrátit výsledek, který hledáte (všimněte si, že k vrácení textu uvnitř musíte použít uvozovky). První část říká „pokud je hodnota v B2 větší než hodnota v C2“ (pokud jsou výdaje větší než rozpočet), druhá část říká „pak vrať text Přes rozpočet a třetí část říká: „Pokud ne, vraťte se V rámci rozpočtu."

Maximální využití nástroje příkazů IF v Excelu znamená možnost kombinovat více příkazů IF, když máte úkol, který je pro něj vhodný. Do původního příkazu IF můžete zahrnout druhý příkaz IF a do něj můžete zahrnout další příkaz IF a tak dále. To je zvláště užitečné, pokud chcete něco kategorizovat do jedné z několika skupin pomocí jediného vzorce. Můžete například použít vnořené příkazy IF k převodu skóre, které studenti dosáhli v testu, na známku.

Chcete-li správně používat více příkazů IF, zamyslete se nad logikou toho, co Excel požadujete. V podstatě po vaší podmínce (první argument ve funkci) můžete přidat další příkaz IF do value_if_true nebo value_if_false mezery, abyste Excelu řekli, co má dělat dál. Přemýšlejte o tom jako o tom, že Excel zkontroluje buňku podle druhého kritéria, pokud dotyčná buňka ne splnit první kritérium (nebo skutečně, pokud to první splňuje), a v tomto procesu můžete pokračovat dlouho čas. Jinými slovy, měníte „jestli toto, tak tamto“ na „pokud toto a tohle, pak tamto“ nebo „když ne tohle, ale tohle, tak tamto“.

Představte si, že jste dali studentům test, kde skóre 85 procent nebo vyšší je A, mezi 70 a 85 procenty je B, mezi 55 a 70 procenty je C, mezi 40 procenty a 55 procenty je D a cokoli nižší je Selhat. Výsledky jsou ve sloupci B, od řádku 2 do řádku 11. Ke kategorizaci skóre studentů můžete použít pečlivě vytvořenou funkci Excel IF s více podmínkami.

První část je snadná (pro skóre v B2, v tomto příkladu): =IF(B2>=85; "A") řekne Excelu, aby vrátil A, pokud student dosáhl 85 nebo vyššího skóre. Vnořování vám umožňuje přidávat další hodnocení pomocí třetího argumentu (co dělat, pokud je podmínka nepravdivá) k přidání dalších podmínek: =KDYŽ(B2>=85; "A", KDYŽ(B2>=70; "B")), který říká Excelu, aby vrátil A pro skóre 85 nebo vyšší, ale pokud ne, vrátil B, pokud je skóre vyšší než 70. Pokračujte tímto způsobem a dokončete vzorec a pokaždé použijte třetí argument pro nový příkaz IF: =KDYŽ(B2>=85; "A", KDYŽ(B2>=70, "B",KDYŽ(B2>=55, "C", KDYŽ(B2>=40, "D", "Nevyhovuje")) ))

Vypadá to složitě, takže jeho rozebrání by mělo upevnit myšlenku. První dva argumenty říkají "pokud je skóre v B2 85 nebo více, pak student dostane A," a třetí říká: "jinak zkontrolujte následující podmínku." The druhý IF vede k tomuto tvrzení, "(pro studenty, kteří dosáhli méně než 85), pokud je skóre v B2 70 nebo vyšší, pak student dostane B, a pokud ne, zkontrolujte následující podmínka." Třetí IF říká: "(pro studenty, kteří dosáhli méně než 70), pokud je skóre v B2 55 nebo vyšší, student získal C, a pokud ne, zkontrolujte následující podmínka“ a čtvrtá říká: „(pro studenty, kteří dosáhli méně než 55), pokud je skóre v B2 vyšší než 40, student dostane D, pokud ne, student selže."

Pokud správně zadáte vzorec pro příkaz IF, vrátí se jedna z možností, které jste zadali do aplikace Excel. Pokud však uděláte chybu, vzorec může vrátit a 0 nebo #NÁZEV? v buňce obsahující váš vzorec.

A 0 chyba znamená, že buď value_if_true nebo value_if_false argument je prázdný. Opravte tento problém tím, že zajistíte zadání hodnoty v obou argumentech, nebo alespoň v value_if_true argument. The value_if_false argument lze zcela odstranit a vrátí FALSE, pokud za znakem nevložíte druhou čárku value_if_true pole.

A #NÁZEV? Chyba vám říká, že jste pravděpodobně něco ve vzorci napsali špatně nebo špatně. To může být trochu obtížnější opravit, protože to může být jakákoli část vzorce, ale nejlepší radou je vše znovu zkontrolovat nebo si vzorec znovu zapsat.

Předchozí příklad vnořeného vzorce funkce IF byl poměrně dlouhý, což je jedna z hlavních nevýhod použití více příkazů IF v aplikaci Excel. Mohou se zkomplikovat a je snadné udělat chybu, protože musíte pečlivě sledovat logiku, abyste se ujistili, že vše funguje tak, jak zamýšlíte. Microsoft poukazuje na to, že nesprávně vnořený vzorec může dokonce většinou vrátit správný výsledek, ale ve vzácných případech nesprávné výsledky. Tyto jsou těžko rozpoznatelné.

Neuvedení podmínek ve správném pořadí je častou chybou. V příkladu hodnocených testů, pokud jste začali s nižším skóre =IF(B2>=40; "D", …" a pokračovali ve vzestupném seznamu ostatních, dokonce i skóre 100 by vrátilo D. Je to proto, že první podmínka je kontrolována jako první, takže pokud jste začali s „pokud je buňka B2 40 nebo vyšší, vraťte D" klasifikoval by jakékoli skóre 40 nebo více jako D, aniž by kontroloval cokoli jiného, ​​protože splňuje první stav. Toto je dokonalý příklad toho, proč musíte dodržet logiku svého vzorce.

Také se musíte ujistit, že na konec více příkazů IF přidáte správný počet závorek, aby se vzorec správně uzavřel. Excel s tím pomáhá tím, že při práci koordinuje barvy a zvýrazňuje závorky.

Technicky můžete vnořit až 64 funkcí IF do jednoho vzorce, ale Microsoft nedoporučuje používat funkci jako je tato z výše uvedených důvodů. Věci se rychle komplikují a je pravděpodobné, že pokud se pokoušíte vnořit více než 10 příkazů IF, pravděpodobně existuje lepší nástroj pro tuto úlohu, která je již součástí Excelu.

Nejpohodlnější alternativou k funkci IF je funkce IFS, což je verze funkce, která provádí stejný úkol jako vnořování do jediné funkce a zvládne až 127 podmínky. Funkce IFS má jednodušší syntaxi: =IFS(logický_test1, hodnota_pokud_pravda1, [logický_test2, hodnota_pokud_pravda2], [logický_test3, hodnota_pokud_pravda3]…) a tak dále. V podstatě to funguje jako vnořené příkazy IF, kromě toho, že následující příkazy IF zabírají místo za "hodnotou, pokud je pravda", aniž by bylo nutné funkci znovu vyvolávat.

Vzorec v příkladu testovací známky lze napsat mnohem jednodušeji pomocí funkce IFS. Namísto:

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

stačí napsat

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

The SKUTEČNÝ v předposledním místě říká "pokud není splněna žádná z ostatních hodnot, vraťte další hodnotu."

Tato funkce je k dispozici pouze v Excelu 2019 nebo Excelu pro Office 365.

Funkce SVYHLEDAT v aplikaci Excel je často jednodušší alternativou k více příkazům IF a je k dispozici v jakékoli verzi aplikace Excel od roku 2007. Není ideální pro všechny situace, protože buď vrací výsledky pro přesnou shodu nebo pro nejbližší číslo, takže jej nebylo možné použít pro známky jako v předchozím příkladu. V mnoha situacích vám však může ušetřit spoustu práce s psaním vzorce, ačkoli musíte sestavit tabulku s možnými hodnotami v jedné sloupec, jako jsou produkty, které váš obchod prodává, a co chcete, aby funkce vracela, jako je cena produktu, v jiném sloupci pro příklad.

Syntaxe funkce SVYHLEDAT je =VLOOKUP(vyhledávací_hodnota, pole_tabulky, číslo indexu_sloupce, [vyhledání_rozsahu]) a učení se každé z komponent vám ukáže, jak ji používat. The vyhledávací_hodnota argument je hodnota, kterou chcete zkontrolovat, což musí být buňka ve stejném sloupci jako začátek tabulky, proti které ji chcete zkontrolovat, buď nad nebo pod ní. The table_array je rozsah buněk obsahujících vaši tabulku informací, jako jsou produkty a jejich odpovídající ceny a col_index_num je sloupec, do kterého má Excel hledat hodnotu, kterou má vrátit 1 označující sloupec zcela vlevo, 2 označující další a tak dále. Konečně, [vyhledání_rozsahu] je nepovinné, ale zadejte „PRAVDA“, pokud chcete, aby aplikace Excel zakládala odpověď na nejbližší hodnotě, nebo „NEPRAVDA“, pokud chcete pouze přesnou shodu.