Ako hniezdi viac IF funkcie v programe Excel

01 z 06

Ako vnorené IF funkcie fungujú

Užitočnosť IF funkcie môže byť rozšírená vložením alebo vložením viacerých IF funkcií do seba.

Vnorené funkcie IF zvyšujú počet možných podmienok, ktoré je možné testovať, a zvyšujú počet krokov, ktoré je možné podniknúť na riešenie týchto výsledkov.

Najaktuálnejšie verzie programu Excel umožňujú vnorené 64 funkcií IF, pričom v programe Excel 2003 a starších sa povolilo len sedem.

Funkcia hniezdenia IF funkcie

Ako je znázornené na obrázku vyššie, tento výukový program používa len dve funkcie IF, aby vytvoril nasledujúci vzorec, ktorý vypočítava ročnú sumu odpočtu pre zamestnancov na základe ich ročného platu.

Vzorec použitý v príklade je uvedený nižšie. Funkcia vnoreného rozhrania IF funguje ako argument value_if_false pre prvú IF funkciu.

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

Rôzne časti vzorca sú oddelené čiarkami a vykonávajú nasledujúce úlohy:

1. Prvá časť, D7, kontroluje, či je plat zamestnanca nižší ako 30 000 USD
2. Ak je, stredná časť, $ D $ 3 * D7 , násobí plat o sadzbu odpočtu 6%
3. Ak nie je, druhá funkcia IF: IF (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7) testuje dve ďalšie podmienky:
   • D7> = 50000 , kontroluje, či je plat zamestnanca vyšší alebo rovný 50 000 USD
   • Ak je, $ D $ 5 * D7 násobí plat o sadzbe zrážky vo výške 10%
   • Ak nie je, $ D $ 4 * D7 násobí plat o sadzbe zrážky 8%

Zadanie údajov výučby

Zadajte údaje do buniek C1 až E6 pracovného hárka programu Excel, ako je vidieť na obrázku vyššie.

Jediné údaje, ktoré tu neboli zadané, sú samotné funkcie IF umiestnené v bunke E7.

Pre tých, ktorí nemajú pocit, že píšu, sú na tomto odkazu k dispozícii údaje a pokyny na ich kopírovanie do programu Excel.

Poznámka: Pokyny na kopírovanie údajov nezahŕňajú kroky formátovania pre pracovný hárok.

To neovplyvní dokončenie tutoriálu. Váš pracovný hárok môže vyzerať odlišne od zobrazeného príkladu, avšak funkcia IF vám poskytne rovnaké výsledky.

02 z 06

Spustenie funkcie Vnorená IF

Hoci je možné jednoducho zadať úplný vzorec

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

do bunky E7 pracovného hárka a nechať ho pracovať, je často jednoduchšie použiť dialógové okno funkcie pre zadanie potrebných argumentov.

Použitie tohto dialógového okna je o niečo zložitejšie pri zadávaní vnorených funkcií, pretože musí byť zadaná vnorená funkcia. Druhé dialógové okno sa nedá otvoriť pre zadanie druhej sady argumentov.

V tomto príklade sa vnorená funkcia IF zadá do tretieho riadka dialógového okna ako argument Value_if_false .

• Keďže pracovný hárok vypočítava ročný odpočet pre niekoľko zamestnancov, vzorec sa najskôr zapíše do jednej bunky E7 pomocou absolútnych bunkových odkazov na sadzby zrážok a potom sa skopíruje do buniek E8: E11.

Tutorial Kroky

1. Kliknutím na bunku E7 ju vytvoríte aktívnou bunkou. - umiestnenie vnoreného vzorca IF.
2. Kliknite na kartu Vzorky pásky
3. Kliknutím na ikonu Logic otvorte rozbaľovací zoznam funkcií.
4. Ak chcete zobraziť dialógové okno funkcie, kliknite na IF v zozname.

Údaje zadané do prázdnych riadkov v dialógovom okne tvoria argumenty funkcie IF.

Tieto argumenty udávajú funkciu podmienku, ktorá sa testuje, a aké kroky treba vykonať, ak je podmienka pravdivá alebo nepravdivá.

Tutorial Možnosť skratky

Ak chcete pokračovať s týmto príkladom, môžete

• zadajte argumenty do dialógového okna, ako je znázornené na obrázku vyššie, a potom prejdite na posledný krok, ktorý zahŕňa kopírovanie vzorca do riadkov 7 až 10;
• alebo nasledujte nasledujúcich päť strán, ktoré poskytujú podrobné pokyny a vysvetlenia pre zadanie troch argumentov.

03 z 06

Zadanie argumentu Logical_test

Argument Logical_test je vždy porovnanie dvoch položiek dát. Tieto údaje môžu byť čísla, odkazy buniek , výsledky vzorcov alebo dokonca textové údaje.

Pri porovnaní dvoch hodnôt používa Logical_test porovnávací operátor medzi hodnotami.

V tomto príklade sú tri platové úrovne, ktoré určujú ročný odpočet zamestnanca.

• menej ako 30 000 USD
• medzi 30 000 a 49 999 USD
• 50 000 dolárov alebo viac

Jedna funkcia IF môže porovnať dve úrovne, ale tretia platová úroveň vyžaduje použitie druhej vnorenej funkcie IF.

Prvé porovnanie bude medzi ročným platom zamestnanca, ktorý sa nachádza v bunke D, s prahovým platom vo výške 30 000 USD.

Keďže cieľom je určiť, či je hodnota D7 menšia ako 30 000 USD, použije sa medzi hodnotami menší počet operátorov "<".

Tutorial Kroky

1. Kliknite na riadok Logical_test v dialógovom okne
2. Kliknutím na bunku D7 pridajte tento odkaz na riadok Logical_test
3. Stlačte menej než klávesa "<" na klávesnici
4. Zadajte hodnotu 30000 za menej ako symbol
5. Vyplnený logický test by mal znieť: D7 <30000

Poznámka: Nezadávajte znamienko dolára ($) alebo oddeľovač čiarkou (,) s hodnotou 30000.

Na konci riadku Logical_test sa zobrazí neplatné chybové hlásenie, ak sa jeden z týchto symbolov zadá spolu s údajmi.

04 z 06

Zadanie argumentu Value_if_true

Argument Value_if_true informuje IF funkciu, čo má robiť, keď je pravdivý Logical_test.

Argument Value_if_true môže byť vzorec, blok textu, hodnota , referencia bunky alebo bunka môže zostať prázdna.

V tomto príklade, keď sú údaje v bunke D7 menšie ako 30 000 USD. Excel vynásobí ročný plat zamestnanca v bunke D7 sadzbou odpočtu vo výške 6%, ktorá sa nachádza v bunke D3.

Relatívne a absolútne odkazy na bunky

Normálne, keď je vzorec kopírovaný do iných buniek, relatívne referencie buniek vo vzore sa menia tak, aby odrážali nové umiestnenie vzorca. To umožňuje jednoduché používanie rovnakého vzorca na viacerých miestach.

Občas sa však zmena odkazov na bunky, keď sa kopíruje funkcia, spôsobí chyby.

Aby sa zabránilo týmto chybám, odkazy na bunky môžu byť vykonané Absolútne, čo im zabraňuje v zmene pri ich kopírovaní.

Absolútne odkazy na bunky sú vytvorené pridaním znakov dolára okolo pravidelnej referencie buniek, napríklad $ D $ 3 .

Pridávanie znakov v dolári sa ľahko uskutoční stlačením klávesu F4 na klávesnici po zadaní odkazu na bunku do dialógového okna.

V tomto príklade je rýchlosť odpočtu umiestnená v bunke D3 zadaná ako absolútny odkaz bunky do riadka Value_if_true dialógového okna.

Tutorial Kroky

1. Kliknite na riadok Value_if_true v dialógovom okne
2. Kliknite na bunku D3 v pracovnom hárku a pridajte tento odkaz na linku Value_if_true
3. Stlačením klávesu F4 na klávesnici vykonáte D3 absolútnu bunkovú referenciu ($ D $ 3)
4. Stlačte kláves hviezdičky ( * ) na klávesnici - hviezdička je symbol násobenia v programe Excel
5. Kliknutím na bunku D7 pridajte odkaz na bunku na riadok Value_if_true
6. Vyplnený riadok Value_if_true by mal znieť: $ D $ 3 * D7

Poznámka : D7 sa ​​nezadá ako absolútna referencia bunky, pretože sa musí zmeniť, keď je vzorec skopírovaný do buniek E8: E11, aby sa získal správny odpočet pre každého zamestnanca.

05 z 06

Zadanie funkcie Vnořená IF ako argument Value_if_false

Za normálnych okolností argument Value_if_false informuje IF funkciu, čo robiť, keď Logical_test je falošný, avšak v tomto prípade je ako tento argument zadaná vnorená funkcia IF.

Tým sa vyskytnú nasledovné výsledky:

• Argument Logical_test vo funkcii vnořeného IF ( D7> = 50000 ) testuje všetky platy, ktoré nie sú nižšie ako 30 000 USD.
• Pre tie platy vyššie alebo rovné 50 000 dolárov sa hodnota Value_if_true vynásobí mierou odpočítania vo výške 10%, ktorá sa nachádza v bunke D5.
• Pre ostatné platy - tie, ktoré sú vyššie ako 30 000 USD, ale menej ako 50 000 USD - argument Value_if_false ich vynásobí sadzbou odpočtu 8%, ktorá sa nachádza v bunke D4.

Tutorial Kroky

Ako bolo spomenuté na začiatku tutoriálu, druhé dialógové okno sa nedá otvoriť pre zadanie vnorenej funkcie, takže musí byť zadané do riadku Value_if_false.

Poznámka: vnorené funkcie nezačínajú s rovnakým znamienkom - ale skôr s názvom funkcie.

1. Kliknite na riadok Value_if_false v dialógovom okne
2. Zadajte nasledujúcu funkciu IF
   IF (D7> = 50000, D $ $ 5 * D7, $ D $ 4 * D7)
3. Kliknutím na tlačidlo OK dokončite funkciu IF a zatvorte dialógové okno
4. Hodnota 3 678,96 dolárov by sa mala objaviť v bunke E7 *
5. Keď kliknete na bunku E7, úplnú funkciu
   = IF (D7 = 50000, D $ $ 5 * D7, $ D $ 4 * D7))
   sa zobrazí vo formulári nad pracovným hárkom

* Keďže R. Holt zarobí viac ako 30 000 dolárov, ale menej ako 50 000 dolárov ročne, na výpočet jeho ročného odpočtu sa použije vzorec 45 987 * 8%.

Ak boli dodržané všetky kroky, váš príklad by mal v súčasnosti zodpovedať prvému obrázku v tomto článku.

Posledným krokom je kopírovanie formulára IF do buniek E8 až E11 použitím rukoväte na vyplnenie na dokončenie pracovného listu.

06 z 06

Kopírovanie vstavaných IF funkcií pomocou rukoväte výplne

Ak chcete dokončiť pracovný hárok, vzorec obsahujúci vnorenú funkciu IF sa musí kopírovať do buniek E8 až E11.

Keď je funkcia skopírovaná, aplikácia Excel aktualizuje referenčné odkazy na bunky tak, aby odrážali nové umiestnenie funkcie a súčasne zachovávajú absolútne odkazy na bunky rovnaké.

Jeden jednoduchý spôsob, ako skopírovať vzorce v programe Excel, je s plniacou rukoväťou.

Tutorial Kroky

1. Kliknutím na bunku E7 ju vytvoríte aktívnou bunkou .
2. Umiestnite ukazovateľ myši na čierny štvorec v pravom dolnom rohu aktívnej bunky. Ukazovateľ sa zmení na znamienko plus "+".
3. Kliknite na ľavé tlačidlo myši a presuňte rukoväť výplne smerom dole na bunku E11.
4. Uvoľnite tlačidlo myši. Bunky E8 až E11 sa naplnia výsledkami vzorca, ako je znázornené na obrázku vyššie.