Jak hnízda více funkcí IF v aplikaci Excel

01 z 06

Jak fungují vnořené IF funkce

Užitnost IF funkce může být rozšířena vložením nebo vložením více IF funkcí do sebe.

Vnořené funkce IF zvyšují počet možných podmínek, které lze testovat, a zvyšují počet kroků, které je možné podniknout k řešení těchto výsledků.

Nejnovější verze aplikace Excel umožňují vnoření 64 funkcí IF, zatímco v aplikaci Excel 2003 a dříve povoleno pouze sedm.

Tvorba hvězdných funkcí IF

Jak je znázorněno na obrázku výše, tento výukový program využívá pouze dvě funkce IF, aby vytvořil následující vzorec, který vypočítává roční částku odpočtu pro zaměstnance na základě jejich roční mzdy.

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 = 50000, $ D $ 5 * D7, $ D $ 4 * D7))

Různé části vzorce jsou odděleny čárkami a provádějí následující úkoly:

1. První část D7 kontroluje, zda je plat zaměstnanců nižší než 30 000 USD
2. Pokud tomu tak je, střední část $ D $ 3 * D7 násobí plat o 6%
3. Pokud tomu tak není, druhá funkce IF: IF (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7) testuje dvě další podmínky:
   • D7> = 50000 , kontroluje, zda je plat zaměstnance větší nebo roven 50 000 USD
   • Pokud tomu tak je, $ D $ 5 * D7 násobí plat o 10%
   • Pokud tomu tak není, $ D $ 4 * D7 násobí plat o 8%

Zadání výukového data

Zadejte data do buněk C1 až E6 listu aplikace Excel, jak je vidět na obrázku výše.

Jediné údaje, které nebyly zadány v tomto bodě, jsou samotné funkce IF umístěné v buňce E7.

Pro ty, kteří nechtějí psát, jsou na tomto odkazu k dispozici data a instrukce pro jejich kopírování do aplikace Excel.

Poznámka: Pokyny pro kopírování dat neobsahují kroky formátování pro pracovní list.

To neovlivní dokončení tutoriálu. Váš pracovní list může vypadat jinak než ukázaný příklad, ale funkce IF vám poskytne stejné výsledky.

02 ze dne 06

Spuštění funkce Vnořená funkce IF

I když je možné zadat úplný vzorec

= IF (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7))

do buňky E7 listu a nechte jej pracovat, je často snadnější použít dialogové okno funkce pro zadání potřebných argumentů.

Použití dialogového okna je trochu trickier při zadávání vnořených funkcí, protože vnořené funkce musí být zadána. Druhé dialogové okno nelze otevřít pro zadání druhé sady argumentů.

V tomto příkladu bude vnořená funkce IF zadána do třetího řádku dialogového okna jako argument Value_if_false .

• Vzhledem k tomu, že pracovní list vypočítává roční odpočet pro několik zaměstnanců, bude vzorec nejprve zadán do jedné buňky E7 za použití absolutních buněčných odkazů pro sazby odečtení a poté bude zkopírován do buněk E8: E11.

Tutorial Kroky

1. Klikněte na buňku E7, aby se stala aktivní buňkou. - umístění pro vnořený vzorec IF.
2. Klikněte na kartu Vzorky karet
3. Klikněte na ikonu Logické otevřete rozbalovací seznam funkcí.
4. Klepnutím na IF v seznamu vyvoláte dialogové okno funkce.

Údaje zadané do prázdných řádků v dialogovém okně tvoří argumenty funkce IF.

Tyto argumenty oznamují funkci testující podmínku a kroky, které je třeba provést, pokud je podmínka pravdivá nebo nepravdivá.

Možnost zkratky tutoriálu

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 k poslednímu kroku, který zahrnuje kopírování vzorce do řádků 7 až 10;
• nebo následujte následujících pět stránek, které poskytují podrobné pokyny a vysvětlení pro zadání těchto tří argumentů.

03 ze dne 06

Zadání argumentu Logical_test

Argument Logical_test je vždy srovnání dvou datových položek. Tato data mohou být čísla, odkazy na buňky , výsledky vzorců nebo dokonce i textová data.

Pro porovnání dvou hodnot použije Logical_test porovnávací operátor mezi hodnotami.

V tomto příkladu existují tři úrovně platů, které určují roční odpis zaměstnance.

• méně než 30 000 dolarů
• mezi 30 000 a 49 999 USD
• 50 000 dolarů nebo více

Jedna funkce IF může porovnávat dvě úrovně, ale třetí úroveň platů vyžaduje použití druhé vnořené funkce IF.

První srovnání bude mezi ročním platem zaměstnance, který se nachází v buňce D, s prahovou platou ve výši 30 000 USD.

Protože cílem je zjistit, zda je hodnota D7 menší než 30 000 USD, použije se mezi hodnotami menší operátor "<".

Tutorial Kroky

1. Klikněte na řádek Logical_test v dialogovém okně
2. Klepnutím na buňku D7 přidejte tento odkaz na linku Logical_test
3. Stiskněte méně než klávesa "<" na klávesnici
4. Zadejte 30000 za méně než symbol
5. Dokončený logický test by měl znít: D7 <30000

Poznámka: Do pole 30000 nezadávejte znak dolaru ($) nebo oddělovač čárky (,).

Na konci řádek Logical_test se objeví neplatná chybová zpráva, pokud je jeden z těchto symbolů zadán společně s daty.

04 z 06

Zadání argumentu Value_if_true

Argument Value_if_true informuje IF funkci, co dělat, když je true Logical_test.

Argument Value_if_true může být vzorec, blok textu, hodnota , odkaz na buňku nebo buňka může zůstat prázdná.

V tomto příkladu, pokud jsou data v buňce D7 nižší než 30 000 USD. Excel vynásobí roční plat zaměstnance v buňce D7 snížením o 6%, který se nachází v buňce D3.

Relativní vs. Absolutní odkazy na buňky

Normálně, když je vzorec zkopírován do jiných buněk, relativní odkazy na buňky ve vzorci se změní tak, aby odrážely nové umístění vzorce. To usnadňuje použití stejného vzorce na více místech.

Občas se však změna odkazů na buňky změní při kopírování funkce způsobí chyby.

Chcete-li zabránit těmto chybám, mohou být odkazy na buňky provedeny v absolutní hodnotě, která je zastaví při jejich kopírování.

Absolutní odkazy na buňky jsou vytvářeny přidáním dolarových značek kolem pravidelných odkazů na buňky, například $ D $ 3 .

Přidání označení dolaru lze snadno provést stisknutím klávesy F4 na klávesnici po vložení odkazu na buňku do dialogového okna.

V příkladu je rychlost odečítání umístěná v buňce D3 zadána jako absolutní odkaz buňky do řádku Value_if_true dialogového okna.

Tutorial Kroky

1. Klepněte na řádek Value_if_true v dialogovém okně
2. Klepnutím na buňku D3 v listu přidejte tento článek odkaz na hodnotu Value_if_true
3. Stisknutím klávesy F4 na klávesnici provedete D3 absolutní referenci buňky ($ D $ 3)
4. Stiskněte klávesu hvězdičky ( * ) na klávesnici - hvězdička je násobící symbol v aplikaci Excel
5. Klepnutím na buňku D7 přidejte tento článek na řádek Value_if_true
6. Dokončený řádek Value_if_true by měl číst: $ D $ 3 * D7

Poznámka : D7 není zadáván jako absolutní odkaz na buňku, protože se musí změnit, když je vzorec zkopírován do buněk E8: E11, aby se pro každého zaměstnance získala správná částka odpočtu.

05 ze dne 06

Zadání vnořené IF funkce jako argument Value_if_false

Normálně hodnota argumentu Value_if_false říká IF funkci, co má dělat, když Logical_test je falešný, ale v tomto případě je jako tento argument zadána vnořená funkce IF.

Tím dochází k následujícím výsledkům:

• Argument Logical_test ve funkci vnořené IF ( D7> = 50000 ) testuje všechny platy, které nejsou menší než $ 30,000.
• U těch platů větších nebo rovných 50 000 dolarů se hodnota Value_if_true násobí hodnotou 10%, která se nachází v buňce D5.
• U zbývajících platů - těch, které jsou větší než 30 000 dolarů, ale méně než 50 000 dolarů - argument Value_if_false je násobí jejich 8% sazbou odpočtu v buňce D4.

Tutorial Kroky

Jak bylo uvedeno na začátku tutoriálu, nelze otevřít druhé dialogové okno pro zadání vnořené funkce, takže musí být zadáno do řádku Value_if_false.

Poznámka: vnořené funkce nezačínají s označením rovnosti - ale spíše s názvem funkce.

1. V dialogovém okně klikněte na řádek Value_if_false
2. Zadejte následující funkci IF
   Pokud (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7)
3. Klepnutím na tlačítko OK dokončete funkci IF a zavřete dialogové okno
4. Hodnota $ 3,678.96 by se měla zobrazit v buňce E7 *
5. Když klepnete na buňku E7, úplnou funkci
   = IF (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7))
   se zobrazí ve sloupci vzorců nad tabulkou

* Vzhledem k tomu, že R. Holt vydělává více než 30 000 dolarů, ale méně než 50 000 dolarů za rok, použije se pro výpočet jeho ročního odpočtu vzorce 45 897,88 * 8%.

Pokud byly dodrženy všechny kroky, váš příklad by měl v současné době odpovídat prvnímu obrázku v tomto článku.

Posledním krokem je kopírování vzorce IF do buněk E8 až E11 pomocí popisovače pro dokončení listu.

06 z 06

Kopírování vnořených IF funkcí pomocí rukojeti Fill

Chcete-li vyplnit pracovní list, musí být vzorek obsahující vnořenou funkci IF kopírován do buněk E8 až E11.

Jak je funkce zkopírována, aplikace Excel aktualizuje relativní odkazy na buňky tak, aby odrážely nové umístění funkce, přičemž zachovávají absolutní odkaz na buňky stejný.

Jeden snadný způsob, jak kopírovat vzorce v aplikaci Excel, je pomocí rukojeti Fill.

Tutorial Kroky

1. Klikněte na buňku E7, aby se stala aktivní buňkou .
2. Umístěte ukazatel myši na černý čtverec v pravém dolním rohu aktivní buňky. Ukazatel se změní na znaménko plus "+".
3. Klepněte na levé tlačítko myši a přetáhněte rukojeť výplně dolů do buňky E11.
4. Uvolněte tlačítko myši. Buňky E8 až E11 budou vyplněny výsledky vzorce, jak je uvedeno na obrázku výše.