Excel

19 tipov pre vnorené vzorce IF

19 Tips Nested If Formulas

The IF funkcia je jednou z najčastejšie používaných funkcií v Exceli. IF je jednoduchá funkcia a ľudia ho milujú, pretože im dáva silu vytvárať Excel odpovedať pretože informácie sa zadávajú do tabuľky. S IF môžete oživiť svoju tabuľku.



Ale jeden IF často vedie k druhému, a keď skombinujete viac ako pár IF, vaše vzorce môžu začať vyzerať ako malé Frankensteiny :)

Sú vnorené IF zlé? Sú niekedy nevyhnutné? Aké sú alternatívy?





Pokračujte v čítaní, aby ste sa dozvedeli odpovede na tieto otázky a ďalšie ...

1. Základný IF

Predtým, ako budeme hovoriť o vnorenom IF, rýchlo si prezrieme základnú štruktúru IF:



 
= IF (test,[true],[false])

Funkcia IF spustí test a vykoná rôzne akcie v závislosti od toho, či je výsledok pravdivý alebo nepravdivý.

Všimnite si hranatých zátvoriek ... to znamená, že argumenty sú voliteľné. Musíte však dodať buď hodnota pre hodnotu true alebo hodnota pre hodnotu false.

Na ilustráciu tu používame IF na kontrolu skóre a výpočet „úspešnosti“ pre skóre najmenej 65:

Základná funkcia IF - návrat

Bunka D3 v tomto príklade obsahuje tento vzorec:

 
= IF (C3>=65,'Pass')

Dá sa to čítať takto: ak je skóre v C3 najmenej 65, vráťte „Pass“.

Všimnite si však, že ak je skóre menej ako 65, IF vráti FALSE, pretože ak je false, nezadali sme hodnotu. Ak chcete, aby sa pri nepriechodných skóre zobrazovalo „Zlyhanie“, môžeme ako falošný argument pridať výraz „Zlyhať“:

 
= IF (C3>=65,'Pass','Fail')

Základná funkcia IF - s hodnotou pridanou pre false

Video: Ako vytvoriť logické vzorce .

2. Čo znamená vnorenie

Vnorenie jednoducho znamená skombinovať vzorce navzájom do jedného, ​​aby jeden vzorec zvládol výsledok druhého. Tu je napríklad vzorec, v ktorom je funkcia DNES vnorená do funkcie MESIAC:

 
= MONTH ( TODAY ())

Funkcia DNES vráti aktuálny dátum vo funkcii MESIAC. Funkcia MONTH použije tento dátum a vráti aktuálny mesiac. Aj stredne zložité vzorce často používajú vnorenie, takže v komplexnejších vzorcoch uvidíte vnorenie všade.

3. Jednoduché vnorené IF

Vnorený IF je iba dvoma ďalšími príkazmi IF vo vzorci, kde sa jeden príkaz IF nachádza vo vnútri druhého.

Na ilustráciu, nižšie som rozšíril pôvodný vzorec prechodu/neúspechu vyššie, aby spracoval „neúplné“ výsledky pridaním funkcie IF a vnorením jedného IF do druhého:

Základný vnorený IF

 
= IF (C3='','Incomplete', IF (C3>=65,'Pass','Fail'))

Najprv sa spustí vonkajší IF a testuje, či je C3 prázdne. Ak je to tak, vonkajší IF vráti „Neúplné“ a vnútorné IF sa nikdy nespustí.

Ak je skóre nie prázdne , vonkajší IF vráti FALSE a je spustená pôvodná funkcia IF.

Naučte sa vnorené IF s jasné, stručné video školenie .

4. Vnorený IF pre váhy

Často uvidíte vnorené IF nastavené tak, aby zvládali „váhy“ ... napríklad na priradenie známok, nákladov na dopravu, daňových sadzieb alebo iných hodnôt, ktoré sa líšia na stupnici s číselným vstupom. Pokiaľ v škále nie je príliš veľa úrovní, vnorené IF fungujú dobre, ale musíte udržiavať vzorec organizovaný, inak bude ťažké ho čítať.

Ide o to, rozhodnúť sa pre smer (od najvyššieho k najnižšiemu alebo od najnižšieho k najvyššiemu) a podľa toho štruktúrovať podmienky. Napríklad na priradenie známok v poradí „od najnižšieho po najvyššie“ môžeme v nasledujúcej tabuľke reprezentovať potrebné riešenie. Upozorňujeme, že pre „A“ nie je žiadna podmienka, pretože keď sme splnili všetky ostatné podmienky, vieme, že skóre musí byť vyššie ako 95, a teda aj „A“.

Skóre Stupeň Podmienka
0-63 F <64
64-72 D <73
73-84 C. <85
85-94 B <95
95-100 TO

Za jasne pochopených podmienok môžeme zadať prvý príkaz IF:

ako na to druhá odmocnina v programe Excel
 
= IF (C5<64,'F')

To sa stará o „F“. Teraz, aby sme zvládli „D“, musíme pridať ďalšiu podmienku:

 
= IF (C5<64,'F', IF (C5<73,'D'))

Všimnite si toho, že som jednoducho vložil ďalší IF do prvého IF pre „falošný“ výsledok. Aby sme vzorec rozšírili na „C“, zopakujeme postup:

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C')))

Pokračujeme touto cestou, kým nedosiahneme poslednú triedu. Potom namiesto pridania ďalšieho IF pridajte iba konečnú známku ako nepravdu.

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))

Tu je posledný vnorený vzorec IF v akcii:

Dokončený vnorený príklad IF na výpočet známok

Video: Ako vytvoriť vnorený IF na priradenie známok

5. Vnorené IF majú logický tok

Mnoho vzorcov je vyriešených zvnútra von, pretože pre pokračovanie zvyšku vzorca je potrebné najskôr vyriešiť „vnútorné“ funkcie alebo výrazy.

Vnorené IF majú svoj vlastný logický tok, pretože „vonkajšie“ IF pôsobia ako brána k „vnútorným“ IF. To znamená, že výsledky z vonkajších IF určujú, či vnútorné IF dokonca bežia. Nasledujúci diagram vizualizoval logický priebeh vyššie uvedeného vzorca.

Logický tok vnoreného IF

6. Na vyhodnotenie logického toku použite vyhodnotenie

V systéme Windows môžete použiť Vyhodnoťte funkciu sledovať, ako Excel rieši vaše vzorce, krok za krokom. Je to skvelý spôsob, ako „vidieť“ logický tok zložitejších vzorcov a ako riešiť problémy, keď veci nefungujú podľa vašich predstáv. Nasledujúca obrazovka zobrazuje okno Vyhodnotiť otvorené a pripravené na použitie. Zakaždým, keď kliknete na tlačidlo Vyhodnotiť, „ďalší krok“ vo vzorci je vyriešený. Vyhodnocovanie nájdete na páse s nástrojmi na karte Vzorce (Alt M, V).

Použitie vyhodnotenia na prechádzanie vnoreným IF, ktorý priraďuje známky

Verzia Excelu pre Mac bohužiaľ neobsahuje funkciu Vyhodnotiť, ale stále môžete použiť nižšie uvedený trik F9.

7. Na vyhľadanie výsledkov kontroly použite kláves F9

Keď na paneli vzorcov vyberiete výraz a stlačíte kláves F9, Excel vyrieši iba vybratú časť. Je to účinný spôsob, ako potvrdiť, čo formula skutočne robí. Na nasledujúcej obrazovke používam okná s tipmi obrazovky na výber rôznych častí vzorca a potom kliknutím na F9 zobrazíte vyriešenie tejto časti:

Pomocou klávesu F9 skontrolujte vnorený IF, ktorý priraďuje známky

Na počítači Mac vráťte kláves F9 pomocou kombinácie klávesov Control + Z (Command + Z). Môžete tiež stlačením klávesu Esc opustiť editor vzorcov bez akýchkoľvek zmien.

Video: Ako ladiť vzorec pomocou F9

8. Poznajte svoje limity

Excel má obmedzenia v tom, ako hlboko môžete vnoriť funkcie IF. Až do Excelu 2007 umožňoval Excel až 7 úrovní vnorených IF. V Exceli 2007+ umožňuje Excel až 64 úrovní.

Avšak len preto, že vy môcť hniezdo veľa IF, to neznamená, že ty mal by . Každá ďalšia úroveň, ktorú pridáte, sťažuje pochopenie vzorca a riešenie problémov. Ak zistíte, že pracujete s vnoreným IF, ak je hlboký viac ako niekoľko úrovní, mali by ste pravdepodobne zvoliť iný prístup - alternatívy nájdete nižšie.

9. Priraď zátvorky ako profík

Jednou z výziev vnorených IF je párovanie alebo vyvažovanie zátvoriek. Ak sa zátvorky nezhodujú správne, váš vzorec je poškodený. Našťastie E xcel poskytuje niekoľko nástrojov, ktoré vám pomôžu pri úprave vzorcov zaistiť „vyváženosť“ zátvoriek.

Po prvé, keď máte viac ako jednu sadu zátvoriek, zátvorky sú farebne odlíšené, takže úvodné zátvorky sa zhodujú so záverečnými zátvorkami. Tieto farby sú dosť ťažké vidieť, ale sú tam, ak sa pozriete pozorne:

Rovnice v zátvorkách sú farebne zladené, ale je ich ťažko vidieť

Za druhé (a lepšie), keď zatvoríte zátvorky, Excel krátko označí zodpovedajúci pár. Môžete tiež kliknúť na vzorec a pomocou klávesu so šípkou sa pohybovať v zátvorkách a Excel v prípade zodpovedajúcej dvojice stručne tučne označí obe zátvorky. Ak neexistuje zhoda, neuvidíte žiadne tučné písmo.

Tučné písmo je bohužiaľ iba v systéme Windows. Ak používate Excel na počítači Mac na úpravu zložitých vzorcov, niekedy má zmysel skopírovať a prilepiť vzorec do dobrého textového editora ( Text Wrangler je bezplatný a vynikajúci), aby ste získali lepšie nástroje na párovanie zátvoriek. Po zhode zátvoriek začne blikať textový Wrangler a pomocou príkazu + B môžete vybrať všetok text obsiahnutý v zátvorkách. Vzorec môžete prilepiť späť do programu Excel potom, čo ste si veci upravili.

10. Na navigáciu a výber použite okno s tipom na obrazovku

Pokiaľ ide o navigáciu a úpravu vnorených IF, tip na funkčnej obrazovke je váš najlepší priateľ. S ním môžete navigovať a presne vyberať všetky argumenty vo vnorenom IF:

Navigujte a vyberte argumenty vzorca pomocou tipu na obrazovke

V tomto videu ma môžete vidieť, ako často používam okno s tipom na obrazovku: Ako vybudovať vnorené IF .

11. Dávajte si pozor na text a čísla

Len ako rýchle pripomenutie, pri práci s funkciou IF dbajte na to, aby ste správne zhodovali čísla a text. Vzorce často vidím takto:

 
= IF (A1='100','Pass','Fail')

Je skóre testu v A1 naozaj text a nie číslo? Nie? Potom nepoužívajte úvodzovky okolo čísla. V opačnom prípade logický test vráti hodnotu FALSE, aj keď je hodnota vyhovujúcim skóre, pretože „100“ nie je to isté ako 100. Ak je skóre testu číselné, použite toto:

 
= IF (A1=100,'Pass','Fail')

12. Pridajte konce riadkov, aby boli vnorené IF ľahko čitateľné

Keď pracujete so vzorcom, ktorý obsahuje mnoho úrovní vnorených IF, môže byť náročné udržať poriadok. Pretože Excel nezaujíma „prázdne miesto“ vo vzorcoch (t. J. Medzery alebo zalomenia riadkov), pridaním zalomení riadkov môžete výrazne zlepšiť čitateľnosť vnorených prvkov if.

Nasledujúca obrazovka napríklad zobrazuje vnorený IF, ktorý vypočítava sadzbu provízie na základe predajného čísla. Tu vidíte typickú vnorenú štruktúru IF, ktorú je ťažké dešifrovať:

Vnorené IF bez zalomenia riadkov sú ťažko čitateľné

Ak však pred každú hodnotu „hodnota if false“ pridám zalomenie riadka, logika vzorca jasne vyskočí. Vzorec je navyše jednoduchšie upravovať:

Zlomky riadkov uľahčujú čítanie vnorených IF

V systéme Windows môžete pridať zalomenie riadka pomocou klávesov Alt + Enter, na počítačoch Mac použite kombináciu klávesov Control + Option + Return.

Video: Ako uľahčiť čítanie vnoreného IF .

13. Obmedzte IF pomocou AND a OR

Vnorené IF sú silné, ale s pridaním ďalších úrovní sa rýchlo skomplikujú. Jeden spôsob, ako sa vyhnúť viacerým úrovniam, je použiť IF v kombinácii s funkciami AND a OR. Tieto funkcie vracajú jednoduchý výsledok TRUE/FALSE, ktorý funguje perfektne vo vnútri IF, takže ich môžete použiť na rozšírenie logiky jedného IF.

Napríklad v nižšie uvedenom probléme chceme vložiť „x“ do stĺpca D, aby sme označili riadky, kde je farba „červená“ a veľkosť je „malá“.

IF s funkciou AND je jednoduchšie ako dva vnorené IF

Vzorec by sme mohli napísať s dvoma vnorenými IFmi takto:

 
= IF (B6='red', IF (C6='small','x',''),'')

Nahradením testu funkciou AND však môžeme vzorec zjednodušiť:

 
= IF ( AND (B6='red',C6='small'),'x','')

Rovnakým spôsobom môžeme tento vzorec ľahko rozšíriť o funkciu ALEBO a skontrolovať tak červené alebo modré A malé:

 
= IF ( AND ( OR (B4='red',B4='blue'),C4='small'),'x','')

Toto všetko mohol vykonať s vnorenými IF, ale vzorec by sa rýchlo stal zložitejším.

Video: AK TO ALEBO tamto

14. Vymeňte vnorené IF za VLOOKUP

Keď vnorený IF jednoducho priraďuje hodnoty na základe jedného vstupu, je možné ho ľahko nahradiť príponou Funkcia VLOOKUP . Tento vnorený IF napríklad priradí čísla piatim rôznym farbám:

 
= IF (E3='red',100, IF (E3='blue',200, IF (E3='green',300, IF (E3='orange',400,500))))

Môžeme ho ľahko nahradiť týmto (oveľa jednoduchším) VLOOKUPOM:

 
= VLOOKUP (E3,B3:C7,2,0)

Vnorené IF vs VLOOKUP

Ako bonus VLOOKUP namiesto vloženia do vzorca ponechá hodnoty v pracovnom hárku (kde ich je možné ľahko zmeniť).

Napriek tomu, že vyššie uvedený vzorec používa presnú zhodu, môžete ho ľahko použiť VLOOKUP pre ročníky tiež.

Pozri tiež: 23 vecí, ktoré musíte vedieť o VLOOKUP

Video: Ako používať VLOOKUP

Video: Prečo je VLOOKUP lepší ako vnorené IF

15. Zvoľte ZVOLIŤ

Funkcia CHOOSE môže poskytnúť elegantné riešenie, keď potrebujete namapovať jednoduché po sebe idúce čísla (1,2,3 atď.) Na ľubovoľné hodnoty.

V nižšie uvedenom príklade sa funkcia CHOOSE používa na vytváranie vlastných skratiek pre všedný deň:

Vnorené IF vs CHOOSE (funkcia)

Jasné, že ty mohol Na to isté použite dlhý a komplikovaný vnorený IF, ale nerobte to :)

16. Namiesto vnorených IF používajte IFS

Ak používate Excel 2016 cez Office 365, namiesto vnorených IFs môžete použiť novú funkciu: funkciu IFS. Funkcia IFS poskytuje špeciálnu štruktúru na hodnotenie viacerých podmienok bez hniezdenie:

Funkcia IFS - viacnásobné podmienky bez vnorenia

Vyššie uvedený vzorec vyzerá takto:

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

Všimnite si, že máme iba jeden pár zátvoriek!

Čo sa stane, keď otvoríte tabuľku, ktorá používa funkciu IFS v staršej verzii Excelu? V Exceli 2013 a 2010 (a verím, že Excel 2007, ale nemôžem testovať) sa zobrazí „_xlfn“. pripojené k IFS v bunke. Predtým vypočítaná hodnota tam stále bude, ale ak niečo spôsobí prepočítanie vzorca, zobrazí sa chyba #NÁZOV. Spoločnosť Microsoft má viac informácií tu .

17. Maximálne

Niekedy môžete použiť MAX alebo MIN veľmi šikovným spôsobom, čím sa vyhnete vyhláseniu IF. Predpokladajme napríklad, že máte výpočet, ktorého výsledkom musí byť kladné číslo alebo nula. Inými slovami, ak výpočet vráti záporné číslo, chcete iba ukázať nulu.

Funkcia MAX vám ponúka šikovný spôsob, ako to urobiť bez IF kdekoľvek na dohľad:

 
= MAX (calculation,0)

Táto technika vráti výsledok výpočtu, ak je kladný, a nulu, inak.

Milujem túto konštrukciu, pretože je len také jednoduché . Úplné znenie nájdete v tomto článku .

18. Chyby pasce s IFERROR

Klasické použitie IF je zachytiť chyby a poskytnúť iný výsledok, keď dôjde k chybe, napríklad takto:

 
= IF ( ISERROR (formula),error_result,formula)

Je to škaredé a nadbytočné, pretože ten istý vzorec platí dvakrát a Excel musí dvakrát vypočítať ten istý výsledok, ak neexistuje žiadna chyba.

V programe Excel 2007 bola zavedená funkcia IFERROR, ktorá vám umožňuje oveľa elegantnejšie zachytiť chyby:

 
= IFERROR (formula,error_result)

Keď vzorec vyvolá chybu, IFERROR jednoducho vráti hodnotu, ktorú zadáte.

19. Použite booleovskú logiku

Vnoreným IF sa tiež niekedy môžete vyhnúť používaním takzvanej „booleovskej logiky“. Slovo boolean označuje hodnoty PRAVDIVÉ/NEPRAVDIVÉ. Napriek tomu, že Excel zobrazuje v bunkách slová PRAVDA a NEPRAVDA, interne považuje Excel PRAVDU za 1 a NEPRAVDU za nulu. Túto skutočnosť môžete použiť na písanie chytrých a veľmi rýchlych vzorcov. Napríklad vo vyššie uvedenom príklade VLOOKUP máme vnorený vzorec IF, ktorý vyzerá takto:

 
= IF (E3='red',100, IF (E3='blue',200, IF (E3='green',300, IF (E3='orange',400,500))))

Pomocou boolovskej logiky môžete vzorec prepísať takto:

 
=(E3='red')*100+(E3='blue')*200+(E3='green')*300+(E3='orange')*400+(E3='purple')*500

Každý výraz vykoná test a potom výsledok testu vynásobí „hodnotou, ak je pravdivá“. Pretože testy vrátia hodnotu TRUE alebo FALSE (1 alebo 0), FALSE výsledky efektívne zrušia vzorec.

Pre číselné výsledky je booleovská logika jednoduchá a extrémne rýchla, pretože neexistuje žiadne vetvenie. Na druhej strane, booleovská logika môže byť mätúca pre ľudí, ktorí na to nie sú zvyknutí. Napriek tomu je to skvelá technika, o ktorej je potrebné vedieť.

Video: Ako používať booleovskú logiku vo vzorcoch programu Excel

Kedy potrebujete vnorený IF?

Pri všetkých týchto možnostiach, ako sa vyhnúť vnoreným IF, by vás mohlo zaujímať, kedy má zmysel používať vnorené IF?

Myslím si, že vnorené IF majú zmysel, keď potrebujete vyhodnotiť niekoľko rôznych vstupov rozhodnut sa.

Predpokladajme napríklad, že chcete vypočítať stav faktúry „Zaplatené“, „Otvorené“, „Po lehote splatnosti“ atď. To vyžaduje, aby ste sa pozreli na vek faktúry. a vynikajúca rovnováha:

Výpočet stavu faktúry s vnoreným IF

V tomto prípade je vnorený IF úplne dobrým riešením.

Tvoje myšlienky?

A čo ty? Ste IF-ster? Vyhýbate sa vnoreným IF? Sú vnorené IF zlé? Podeľte sa o svoje myšlienky nižšie.

Naučte sa vzorce Excelu rýchlo pomocou stručné video školenie . Autor Dave Bruns


^