Užitečnost funkce IF je rozšířena vložením nebo vnořením více funkcí IF uvnitř sebe. Vnořené funkce IF zvyšují počet možných podmínek, které jsou testovány, a zvyšují počet akcí, které jsou podniknuty k řešení těchto výsledků. Pro ještě další možné testované podmínky je nejlepší použít funkce IF, AND a OR. Pokyny v tomto článku platí pro Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 pro Mac, Excel 2016 pro Mac, Excel pro Mac 2011 a Excel Online.
Výukový program Nest IF Functions
Jak je znázorněno na obrázku, tento kurz používá dvě funkce IF k vytvoření vzorce, který vypočítá částku ročního odpočtu pro zaměstnance na základě jejich ročního platu. Vzorec použitý v příkladu je uveden níže. Vnořená funkce IF funguje jako argument value_if_false pro první funkci IF. = IF (D7 <30000, $ D $ 3 * D7, IF (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7))
Jednotlivé části vzorce jsou odděleny čárkami a provádějí následující úkoly:
- První část, D7 <30000, kontroluje, zda je plat zaměstnance nižší než 30 000 $.
- Pokud je plat nižší než 30 000 $, střední část, $ D $ 3 * D7, vynásobí plat sazbou odpočtu 6%.
- Pokud je plat vyšší než 30 000 $, testuje druhá IF funkce IF (D7> = 50 000, $ D $ 5 * D7, $ D $ 4 * D7) další dvě podmínky.
- D7> = 50000 kontroluje, zda je plat zaměstnance vyšší nebo roven 50 000 USD.
- Pokud je plat roven nebo větší než 50 000 $, vynásobí $ D $ 5 * D7 plat srážkovou sazbou 10%.
- Pokud je plat nižší než 50 000 $, ale vyšší než 30 000 $, vynásobí $ D $ 4 * D7 plat srážkovou sazbou 8%.
Zadejte data výukového programu
Zadejte data do buněk C1 až E6 listu aplikace Excel, jak je vidět na obrázku. Jedinými údaji, které se v tomto okamžiku nezadávají, je samotná funkce IF umístěná v buňce E7. Pokyny pro kopírování dat nezahrnují kroky formátování listu. To neruší dokončení tohoto kurzu. Váš list může vypadat jinak, než je ukázaný příklad, ale funkce IF vám poskytne stejné výsledky.
Spusťte funkci vnořeného IF
Je možné pouze zadat kompletní vzorec = IF (D7 <30000, $ D $ 3 * D7, IF (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7))
do buňky E7 listu a nechte to fungovat. V aplikaci Excel Online je to metoda, kterou musíte použít. Pokud však používáte verzi aplikace Excel pro stolní počítače, je často snazší zadat potřebné argumenty pomocí dialogového okna funkce. Používání dialogového okna je při zadávání vnořených funkcí trochu složitější, protože je nutné zadat vnořenou funkci. Druhé dialogové okno nelze otevřít a zadat druhou sadu argumentů. V tomto příkladu je vnořená funkce IF zadána do třetího řádku dialogového okna jako argument Value_if_false. Vzhledem k tomu, že list vypočítává roční odpočet pro několik zaměstnanců, je vzorec nejprve zadán do buňky E7 pomocí absolutních odkazů na buňky pro míry odpočtu a poté zkopírován do buněk E8: E11.
Výukové kroky
- Vyberte buňku E7 aby se stala aktivní buňkou. Zde se bude nacházet vnořený vzorec IF.
- Vybrat Vzorce.
- Vybrat Logický otevřete rozevírací seznam funkcí.
- Vybrat LI v seznamu vyvoláte dialogové okno funkce.
Data zadaná do prázdných řádků v dialogovém okně tvoří argumenty funkce IF. Tyto argumenty sdělují funkci testovanou podmínku a jaké akce je třeba provést, pokud je podmínka pravdivá nebo nepravdivá.
Možnost zástupce výukového programu
Chcete-li pokračovat v tomto příkladu, můžete:
- Zadejte argumenty do dialogového okna, jak je znázorněno na obrázku výše, a pak přejděte na poslední krok, který zahrnuje kopírování vzorce do řádků 7 až 10.
- Nebo postupujte podle dalších kroků, které nabízejí podrobné pokyny a vysvětlení pro zadání tří argumentů.
Zadejte argument Logical_test
Argument Logical_test porovnává dvě položky dat. Tato data mohou být čísla, odkazy na buňky, výsledky vzorců nebo dokonce textová data. K porovnání dvou hodnot používá Logical_test operátor porovnání mezi hodnotami. V tomto příkladu existují tři úrovně platu, které určují roční odpočet zaměstnance:
- Méně než 30 000 $.
- Mezi 30 000 a 49 999 $.
- 50 000 $ nebo více
Jedna funkce IF může porovnávat dvě úrovně, ale třetí úroveň platu vyžaduje použití druhé vnořené funkce IF. První srovnání je mezi ročním platem zaměstnance umístěným v buňce D a prahovým platem 30 000 USD. Jelikož cílem je zjistit, zda je D7 nižší než 30 000 $, operátor Less Than ( < ) se používá mezi hodnotami.
Výukové kroky
- Vybrat Logický_test řádek v dialogovém okně.
- Vyberte buňku D7 přidat tento odkaz na buňku do řádku Logical_test.
- zmáčkni méně než klíč ( < ) na klávesnici.
- Typ 30000 za symbolem méně než.
- Dokončený logický test se zobrazuje jako D7 <30000.
Nezadávejte znak dolaru ( $ ) nebo oddělovač čárky ( , ) s 30000. Pokud je spolu s daty zadán některý z těchto symbolů, na konci řádku Logical_test se zobrazí neplatná chybová zpráva.
Zadejte argument Value_if_true
Argument Value_if_true říká funkci IF, co má dělat, když je Logical_test true. Argument Value_if_true může být vzorec, blok textu, hodnota, odkaz na buňku nebo může být buňka ponechána prázdná. V tomto příkladu, když jsou data v buňce D7 menší než 30 000 USD, Excel vynásobí roční plat zaměstnance v buňce D7 sazbou odpočtu 6 procent umístěnou v buňce D3.
Relativní vs. absolutní odkazy na buňky
Normálně, když je vzorec zkopírován do jiných buněk, změní se relativní odkazy na buňky ve vzorci tak, aby odrážely nové umístění vzorce. Díky tomu je snadné používat stejný vzorec na více místech. Občas změny odkazů na buňky při kopírování funkce vedou k chybám. Aby se těmto chybám předešlo, lze odkazy na buňky udělat Absolutní, což jim při kopírování zabrání ve změně. Absolutní odkazy na buňky se vytvářejí přidáním znaku dolaru kolem běžného odkazu na buňku, například $ D $ 3. Přidání znaku dolaru lze snadno provést stisknutím klávesy F4 po zadání odkazu na buňku do dialogového okna. V příkladu je sazba odpočtu umístěná v buňce D3 zadána jako absolutní odkaz na buňku do řádku Value_if_true v dialogovém okně.
Výukové kroky
- Vybrat Hodnota_pravda řádek v dialogovém okně.
- Vyberte buňku D3 v listu přidáte tento odkaz na buňku na řádek Value_if_true.
- zmáčkni F4 klíč, aby se D3 stal absolutním odkazem na buňku ($ D $ 3).
- Stiskněte hvězdičku ( * ). Hvězdička je symbol násobení v aplikaci Excel.
- Vyberte buňku D7 přidat tento odkaz na buňku do řádku Value_if_true.
- Vyplněný řádek Value_if_true se zobrazuje jako $ D $ 3 * D7.
D7 není zadán jako absolutní odkaz na buňku. Je třeba změnit, když se vzorec zkopíruje do buněk E8: E11, aby se pro každého zaměstnance získala správná částka odpočtu.
Zadejte funkci vnořeného IF jako argument Value_if_false
Normálně argument Value_if_false říká funkci IF, co má dělat, když je Logical_test false. V tomto případě je jako tento argument zadána vnořená funkce IF. Tímto způsobem dojde k následujícím výsledkům:
- Argument Logical_test ve vnořené funkci IF (D7> = 50000) testuje všechny platy, které nejsou menší než 30 000 $.
- U platů vyšších nebo rovných 50 000 USD je argument Value_if_true vynásobí mírou odpočtu 10% umístěnou v buňce D5.
- U zbývajících platů (těch, které jsou vyšší než 30 000 $, ale méně než 50 000 $) je argument Value_if_false vynásobí mírou odpočtu 8% umístěnou v buňce D4.
Výukové kroky
Jak již bylo uvedeno na začátku kurzu, nelze otevřít druhé dialogové okno pro vstup do vnořené funkce, takže je nutné jej zadat do řádku Value_if_false. Vnořené funkce nezačínají znaménkem rovná se, ale spíše názvem funkce.
- Vybrat Value_if_false řádek v dialogovém okně.
- Zadejte následující funkci IF:
IF (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7) - Vybrat OK dokončete funkci IF a zavřete dialogové okno.
- V buňce E7 se objeví hodnota 3 678,96 USD. Vzhledem k tomu, že R. Holt vydělává více než 30 000 $, ale méně než 50 000 $ ročně, je pro výpočet jeho ročního odpočtu použit vzorec 45 987 * 8%.
- Vyberte buňku E7 zobrazit kompletní funkci = IF (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7)) na řádku vzorců nad listem.
Po provedení těchto kroků se váš příklad nyní shoduje s prvním obrázkem v tomto článku. Poslední krok zahrnuje kopírování vzorce IF do buněk E8 až E11 pomocí úchytu k vyplnění listu.
Zkopírujte vnořené funkce IF pomocí rukojeti výplně
Chcete-li vyplnit list, zkopírujte vzorec obsahující vnořenou funkci IF do buněk E8 až E11. Při kopírování funkce Excel aktualizuje relativní odkazy na buňky, aby odrážely nové umístění funkce, přičemž absolutní odkaz na buňku udržuje stejný. Jedním ze snadných způsobů kopírování vzorců v aplikaci Excel je rukojeť Vyplnění.
Výukové kroky
- Vyberte buňku E7 aby se stala aktivní buňkou.
- Umístěte ukazatel myši na čtverec v pravém dolním rohu aktivní buňky. Ukazatel se změní na znaménko plus (+).
- Vyberte a přetáhněte rukojeť výplně dolů do buňky E11.
- Buňky E8 až E11 jsou vyplněny výsledky vzorce, jak je znázorněno na obrázku výše.