Zatímco v Excelu je funkce IF nezbytná pro základní logické testy, složité podmínky často vyžadují vnořené příkazy IF pro vylepšené zpracování dat. V tomto obsáhlém průvodci podrobně pokryjeme základy vnořeného IF, od syntaxe až po praktické aplikace, včetně kombinací vnořených IF s podmínkami AND/OR. Kromě toho se podělíme o to, jak zlepšit čitelnost vnořených funkcí IF a také o několik tipů ohledně vnořených IF, a prozkoumáme výkonné alternativy, jako je SVYHLEDAT, IFS a další, aby bylo použití složitých logických operací snazší a efektivnější.

• Syntaxe vnořeného IF
• Praktické příklady vnořených IF
• Vnořeno, pokud s podmínkou AND / OR

• Díky tomu jsou vnořené IF snadno čitelné
• Pořadí vnořených funkcí KDYŽ
• S čísly a textem by se mělo zacházet jinak
• Omezení vnořených IF

• Pomocí funkce VLOOKUP
• Pomocí IFS
• Pomocí CHOOSE a MATCH

Funkce Excel IF vs. vnořené příkazy IF

Funkce IF a vnořené příkazy IF v Excelu slouží podobným účelům, ale výrazně se liší svou složitostí a aplikací.

Použití vnořených IF

Tato část ukazuje základní použití vnořených příkazů IF v Excelu, včetně syntaxe, praktických příkladů a způsobu jejich použití s ​​podmínkami AND nebo OR.

Syntaxe vnořeného IF

Pochopení syntaxe funkce je základem její správné a efektivní aplikace v Excelu. Začněme syntaxí vnořených příkazů if.

Syntax:

=IF(condition1, result1, IF(condition2, result2, IF(condition3, result3, result4)))

Argumenty:

• Condition1, Condition2, Condition3: These are the conditions you want to test. Each condition is evaluated in order, starting with Condition1.
• Result1: This is the value returned if Condition1 is TRUE.
• Result2: This value is returned if Condition1 is FALSE and Condition2 is TRUE. It's important to note that Result2 is only evaluated if Condition1 is FALSE.
• Result3: This value is returned if both Condition1 and Condition2 are FALSE, and Condition3 is TRUE. Essentially, for Result3 to be evaluated, the previous conditions (Condition1 and Condition2) must both be FALSE.
• Result4: This result is returned if all the conditions (Condition1, Condition2, and Condition3) are FALSE.
  
  In short, this expression can be interpreted as follows:
  Test condition1, if TRUE, return result1, if FALSE,
  test condition2, if TRUE, return result2, if FALSE,
  test condition3, if TRUE, return result3, if FALSE,
  return result4

Pamatujte, že ve vnořené struktuře IF je každá následující podmínka vyhodnocena pouze v případě, že jsou všechny předchozí podmínky NEPRAVDA. Tato sekvenční kontrola je zásadní pro pochopení toho, jak fungují vnořené IF.

Praktické příklady vnořených IF

Nyní se pojďme ponořit do použití vnořeného IF se dvěma praktickými příklady.

Příklad 1: Systém klasifikace

Jak je znázorněno na snímku obrazovky níže, předpokládejme, že máte seznam skóre studentů a chcete přidělovat hodnocení na základě těchto skóre. K provedení tohoto úkolu můžete použít vnořené IF.

Vyberte prázdnou buňku (v tomto případě C2), zadejte následující vzorec a stiskněte vstoupit získat výsledek. Poté přetáhněte Naplňte rukojeť dolů, abyste získali zbytek výsledků.

=IF(B2>=90,$F$2,IF(B2>=80,$F$3,IF(B2>=70,$F$4,IF(B2>=60,$F$5,$F$6))))

Příklad 2: Výpočet prodejní provize

Představte si scénář, kdy obchodní zástupci dostávají různé sazby provizí na základě jejich prodejních úspěchů. Jak je znázorněno na obrázku níže, chcete vypočítat provizi prodejce na základě těchto různých prodejních prahů a vnořené příkazy IF vám s tím mohou pomoci.

Vyberte prázdnou buňku (v tomto případě C2), zadejte následující vzorec a stiskněte vstoupit získat výsledek. Poté přetáhněte Naplňte rukojeť dolů, abyste získali zbytek výsledků.

=B2*IF(B2>20000,$F$2,IF(B2>=10000,$F$3,$F$4))

Vnořeno, pokud s podmínkou AND / OR

V této části upravím výše uvedený první příklad „systém hodnocení“, abych demonstroval, jak kombinovat vnořené podmínky IF s AND nebo OR v Excelu. V revidovaném příkladu známkování jsem zavedl další podmínku založenou na "Účasti".

Použití vnořeného if s podmínkou AND

Pokud student splní kritéria hodnocení i docházky, získá lepší známku. Například student, jehož skóre je 60 nebo vyšší a jehož míra docházky je 95 % nebo vyšší, bude mít lepší známku o jednu úroveň, například z A na A+, B na B+ a tak dále. Pokud je však míra účasti nižší než 95 %, bude se hodnocení řídit původními kritérii založenými na skóre. V takových případech musíme použít vnořený příkaz IF s podmínkou AND.

Vyberte prázdnou buňku (v tomto případě D2), zadejte následující vzorec a stiskněte vstoupit získat výsledek. Poté přetáhněte Naplňte rukojeť dolů, abyste získali zbytek výsledků.

=IF(AND(B2>=60, C2>=95%),IF(B2>=90, "A+", IF(B2>=80, "B+", IF(B2>=70, "C+", "D+"))),IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))))

Použití vnořené if s podmínkou OR

V tomto případě bude hodnocení studenta o jednu úroveň zvýšeno, pokud je jeho skóre 95 nebo vyšší, nebo pokud je jeho účast 95 % nebo více. Zde je návod, jak toho dosáhnout pomocí vnořených podmínek IF a OR.

Vyberte prázdnou buňku (v tomto případě D2), zadejte následující vzorec a stiskněte vstoupit získat výsledek. Poté přetáhněte Naplňte rukojeť dolů, abyste získali zbytek výsledků.

=IF(OR(B2>=95, C2>=95%),IF(B2>=90, "A+", IF(B2>=80, "B+", IF(B2>=70, "C+", IF(B2>=60, "D+", "F+")))),IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))))

Tipy a triky pro vnořené IF

Tato část obsahuje čtyři užitečné tipy a triky pro vnořené IF.

Díky tomu jsou vnořené IF snadno čitelné

Typický vnořený příkaz IF může vypadat kompaktně, ale může být těžké ho dešifrovat.

V následujícím vzorci je náročné rychle identifikovat, kde končí jedna podmínka a začíná další, zvláště když se zvyšuje složitost.

=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))

Řešení: Přidání zalomení řádků a odsazení

Chcete-li usnadnit čtení vnořených KDYŽ, můžete vzorec rozdělit na více řádků s každým vnořeným KDYŽ na novém řádku. Ve vzorci jednoduše umístěte kurzor před IF a stiskněte klávesy Alt + Enter.

Po porušení výše uvedeného vzorce se zobrazí takto:

=IF(A2>=90, "A",
      IF(A2>=80, "B",
          IF(A2>=70, "C",
              IF(A2>=60, "D", "F")))
)

Tento formát objasňuje, kde je každá podmínka a odpovídající výstup, a zlepšuje čitelnost vzorce.

Pořadí vnořených funkcí KDYŽ

Pořadí logických podmínek ve vnořeném vzorci IF je klíčové, protože určuje, jak Excel tyto podmínky vyhodnotí, a tím ovlivní konečný výsledek vzorce.

Správný vzorec

V příkladu systému hodnocení používáme následující vzorec k přidělování známek na základě skóre.

=IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F"))))

Excel vyhodnocuje podmínky ve vnořeném vzorci IF postupně, od první podmínky po poslední. Tento vzorec nejprve zkontroluje práh nejvyššího skóre (>=90 pro „A“) a poté se přesune k nižším prahovým hodnotám. Zajišťuje porovnání skóre s nejvyšší známkou, na kterou se kvalifikuje. Pokud je první podmínka pravdivá (A2>=90), vrátí "A" a nevyhodnocuje žádné další podmínky.

Nesprávně uspořádaný vzorec

Pokud by bylo pořadí podmínek obráceno, počínaje nejnižší prahovou hodnotou, vrátilo by se nesprávné výsledky.

=IF(B2>=60, "D", IF(B2>=70, "C", IF(B2>=80, "B", IF(B2>=90, "A", "F"))))

V tomto nesprávném vzorci by skóre 95 okamžitě splnilo první podmínku B2>=60 a bylo by nesprávně přiřazeno hodnocení „D“.

S čísly a textem by se mělo zacházet jinak

Tato část vám ukáže, jak se s čísly a textem zachází odlišně ve vnořených příkazech IF.

Čísla

Čísla se používají pro aritmetická srovnání a výpočty. Ve vnořených příkazech IF můžete přímo porovnávat čísla pomocí operátorů jako >, <, =, >= a <=.

Text

Ve vnořených příkazech IF by měl být text uzavřeno ve dvojitých uvozovkách. Viz A, B, C, D a F v následujícím vzorci:

=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))

Omezení vnořených IF

Tato část uvádí několik omezení a nevýhod vnořených IF.

Složitost a čitelnost:

Přestože Excel umožňuje vnořit až 64 různých funkcí IF, není to vůbec vhodné dělat. Čím více úrovní vnoření, tím složitější se vzorec stává. To může vést ke vzorcům, které je obtížné číst, pochopit a udržovat.

Náchylná k chybám:

Kromě toho mohou být komplexní vnořené příkazy IF náchylné k chybám a mohou být náročné na ladění nebo úpravu.

Těžko rozšiřitelné nebo škálovatelné:

Pokud se vaše logika změní nebo potřebujete přidat další podmínky, hluboce vnořené IF může být obtížné upravit nebo rozšířit.

Pochopení těchto omezení je klíčem k efektivnímu používání vnořených příkazů IF v Excelu. Kombinace vnořených IF s jinými funkcemi nebo hledání alternativních přístupů může často vést k efektivnějšímu a udržitelnějšímu řešení.

Alternativy k Nested IF

Tato část uvádí několik funkcí v Excelu, které lze použít jako alternativy k vnořeným příkazům IF.

Pomocí funkce VLOOKUP

K provedení výše uvedených dvou praktických příkladů můžete místo vnořených příkazů IF použít funkci SVYHLEDAT. Můžete to udělat takto:

Příklad 1: Systém hodnocení s funkcí VLOOKUP

Zde ukážu, jak používat SVYHLEDAT k přidělování známek na základě skóre.

Krok 1: Vytvořte vyhledávací tabulku pro známky

Nejprve musíte vytvořit vyhledávací tabulku (jako v tomto případě E1:F6) pro rozsah skóre a odpovídající známky. Poznámka: Skóre v prvním sloupci tabulky musí být seřazeny vzestupně.

Krok 2: Použijte funkci SVYHLEDAT pro přiřazení známek

Vyberte prázdnou buňku (v tomto případě C2), zadejte následující vzorec a stiskněte vstoupit klíč k získání první třídy. Vyberte tuto buňku vzorce a přetáhněte ji Naplňte rukojeť dolů, abyste získali zbytek známek.

=VLOOKUP(B2,$E$2:$F$6,2,TRUE)

Příklad 2: Výpočet prodejní provize pomocí funkce VLOOKUP

Můžete také použít SVYHLEDAT k provedení výpočtu prodejní provize v Excelu. Postupujte prosím následovně.

Krok 1: Vytvořte vyhledávací tabulku pro známky

Nejprve musíte vytvořit vyhledávací tabulku pro prodej a odpovídající provizní sazbu, jako je v tomto případě E2:F4. Poznámka: Prodeje v prvním sloupci tabulky musí být seřazeny vzestupně.

Krok 2: Použijte funkci SVYHLEDAT pro přiřazení známek

Vyberte prázdnou buňku (v tomto případě C2), zadejte následující vzorec a stiskněte klávesu Enter, abyste získali první provizi. Vyberte tuto buňku vzorce a přetáhněte její úchyt dolů, abyste získali zbytek výsledků.

=B2*VLOOKUP(B2,$E$2:$F$4,2,TRUE)

Pomocí IFS

Projekt Funkce IFS zjednodušuje proces tím, že eliminuje potřebu vnořování a usnadňuje čtení a správu vzorců. Zvyšuje čitelnost a zjednodušuje zpracování více podmíněných kontrol. Chcete-li využívat funkci IFS, ujistěte se, že používáte Excel 2019 nebo novější, nebo máte předplatné Office 365. Podívejme se, jak jej lze aplikovat na praktických příkladech.

Příklad 1: Systém klasifikace s IFS

Za předpokladu stejných kritérií hodnocení jako dříve lze funkci IFS použít následovně:

Vyberte prázdnou buňku, například C2, zadejte následující vzorec a stiskněte vstoupit abyste získali první výsledek. Vyberte tuto výsledkovou buňku a přetáhněte ji Naplňte rukojeť dolů, abyste získali zbytek výsledků.

=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",B2<60,"F")

Příklad 2: Výpočet prodejní provize s IFS

Pro scénář výpočtu prodejní provize se funkce IFS použije následovně:

Vyberte prázdnou buňku, například C2, zadejte následující vzorec a stiskněte vstoupit abyste získali první výsledek. Vyberte tuto výsledkovou buňku a přetáhněte ji Naplňte rukojeť dolů, abyste získali zbytek výsledků.

=B2*IFS(B2>20000,20%,B2>=10000,15%,TRUE,10%)

Pomocí CHOOSE a MATCH

Přístup CHOOSE and MATCH může být efektivnější a snadněji ovladatelný ve srovnání s vnořenými příkazy IF. Tato metoda zjednodušuje vzorec a zjednodušuje aktualizace nebo změny. Níže předvedu, jak použít kombinaci funkcí CHOOSE a MATCH ke zvládnutí dvou praktických příkladů v tomto článku.

Příklad 1: Systém hodnocení pomocí CHOOSE a MATCH

Můžete použít kombinaci funkcí CHOOSE a MATCH k přiřazení známek na základě různých skóre.

Krok 1: Vytvořte vyhledávací pole s hodnotami vyhledávání

Nejprve musíte vytvořit rozsah buněk obsahujících prahové hodnoty, které bude MATCH prohledávat, jako je v tomto případě $E$2:$E$6. Poznámka: Aby funkce MATCH při použití přibližného typu shody fungovala správně, musí být čísla v tomto rozsahu seřazena vzestupně.

Krok 2: Použijte CHOOSE a MATCH pro přiřazení známek

Vyberte prázdnou buňku (v tomto případě C2), zadejte následující vzorec a stiskněte vstoupit klíč k získání první třídy. Vyberte tuto buňku vzorce a přetáhněte ji Naplňte rukojeť dolů, abyste získali zbytek výsledků.

=CHOOSE(MATCH(B2, $E$2:$E$6, 1), "F", "D", "C", "B", "A")

Příklad 2: Výpočet prodejní provize s IFS

Použití kombinace CHOOSE a MATCH pro výpočet prodejní provize může být také efektivní, zejména pokud jsou sazby provize založeny na specifikovaných prodejních prahových hodnotách. Pojďme se podívat, jak to zvládneme.

Krok 1: Vytvořte vyhledávací pole s hodnotami vyhledávání

Nejprve musíte vytvořit rozsah buněk obsahujících prahové hodnoty, které bude MATCH prohledávat, jako je v tomto případě $E$2:$E$4. Poznámka: Aby funkce MATCH při použití přibližného typu shody fungovala správně, musí být čísla v tomto rozsahu seřazena vzestupně.

Krok 2: Aplikujte CHOOSE a MATCH, abyste získali výsledky

Vyberte prázdnou buňku (v tomto případě C2), zadejte následující vzorec a stiskněte vstoupit klíč k získání první třídy. Vyberte tuto buňku vzorce a přetáhněte ji Naplňte rukojeť dolů, abyste získali zbytek výsledků.

=B2*CHOOSE(MATCH(B2, $E$2:$E$4, 1), 10%, 15%, 20%)

Závěrem lze říci, že zvládnutí vnořených příkazů IF v Excelu je cenná dovednost, která zvyšuje vaši schopnost zpracovávat složité logické scénáře při analýze dat a rozhodovacích procesech. I když jsou vnořené IF výkonné pro složité logické operace, je důležité mít na paměti jejich omezení. Jednodušší alternativy jako VLOOKUP, IFS a CHOOSE with MATCH mohou v určitých scénářích poskytnout efektivnější řešení. Vyzbrojeni těmito poznatky můžete nyní s jistotou použít ty nejvhodnější techniky Excelu na své úlohy analýzy dat a zajistit tak přehlednost, přesnost a efektivitu ve vašich tabulkách. Pro ty, kteří se chtějí hlouběji ponořit do možností Excelu, se náš web může pochlubit velkým množstvím výukových programů. Zde najdete další tipy a triky pro Excel.

Související články