IF Formula Excel - příkazy If Then

Stáhněte si ukázkový sešit

Stáhněte si ukázkový sešit

Tento tutoriál ukazuje, jak používat Funkce IF IF v Excelu k vytvoření příkazů If Then.

Přehled funkcí IF

Funkce IF kontroluje, zda je splněna podmínka. Pokud PRAVDA udělá jednu věc, pokud NEPRAVDA udělá jinou.

Chcete -li použít funkci listu IF Excel, vyberte buňku a zadejte:

(Všimněte si, jak se zobrazují vstupy vzorců)

Funkce IF Syntaxe a vstupy:

1	= IF (logický_test, value_if_true, value_if_false)

logický_test - Logický výraz. Příklad: A1> 4.

value_if_true - Hodnota nebo výpočet se provede, pokud je logický výraz PRAVDA.

value_if_false - Hodnota nebo výpočet se provede, pokud je logický výraz FALSE.

IF je „podmíněná“ funkce. To znamená, že definujete logický test a vrátí jednu hodnotu, pokud je tento test vyhodnocen jako pravdivý, a jinou hodnotu, pokud je nepravdivý.

Jak používat funkci IF

Toto je velmi základní příklad, abyste pochopili, co tím myslím. Zkuste do Excelu zadat následující:

1	= IF (2 + 2 = 4, „Je to pravda“, „Je to nepravda!“)

Protože 2 + 2 se ve skutečnosti rovná 4, Excel vrátí „Je to pravda!“. Pokud bychom použili toto:

1	= IF (2 + 2 = 5, „Je to pravda“, „Je to nepravda!“)

Nyní Excel vrátí „Je to nepravda!“, Protože 2 + 2 se nerovná 5.

Zde je návod, jak můžete použít IF v tabulce.

1	= IF (C4-D4> 0, C4-D4,0)

Provozujete sportovní bar a nastavujete limity jednotlivých karet pro různé zákazníky. Tuto tabulku jste nastavili tak, aby zkontrolovala, zda každý zákazník překročil svůj limit. V takovém případě je odříznete, dokud nezaplatí svou kartu.

Zkontrolujete, zda C4-D4 (jejich aktuální částka na kartě mínus jejich limit) je větší než 0. Toto je váš logický test. Pokud je to pravda, IF vrátí „Ano“ - měli byste je odříznout. Pokud je to nepravdivé, IF vrátí „Ne“ - necháte je dál pít.

Co IF může vrátit

Nahoře jsme vrátili textový řetězec „Ano“ nebo „Ne“. Můžete ale také vrátit čísla nebo dokonce jiné vzorce.

Řekněme, že někteří vaši zákazníci používají velké karty. Abyste tomu zabránili, začnete účtovat úroky zákazníkům, kteří překročí svůj limit.

K tomu můžete použít IF:

1	= IF (C4> D4, C4*0,03,0)

Pokud je karta vyšší než limit, vraťte kartu vynásobenou 0,03, čímž se vrátí 3% karty. Jinak vraťte 0: nejsou nad kartou, takže nebudete účtovat úroky.

Použití IF s AND

IF můžete zkombinovat s funkcí AND AND <>. Toto použijete v logickém testu, který vám umožní zadat dvě nebo více podmínek k testování. Excel vrátí hodnotu TRUE, pouze pokud jsou všechny testy pravdivé.

Implementovali jste tedy svoji úrokovou sazbu. Někteří vaši štamgasti si ale stěžují. V minulosti vždy platili své karty, proč na ně teď zasahujete? Vymyslíte řešení: některým důvěryhodným zákazníkům nebudete účtovat úroky.

Do tabulky vytvoříte nový sloupec k identifikaci důvěryhodných zákazníků a aktualizujete svůj příkaz IF pomocí funkce AND:

1	= IF (AND (C4> D4, F4 = "Ne"), C4*0,03,0)

Podívejme se na část AND samostatně:

1	A (C4> D4, F4 = "Ne")

Všimněte si dvou podmínek:

• C4> D4: kontrola, zda nepřekračují limit karet, jako dříve
• F4 = „Ne“: toto je nový bit, který kontroluje, zda nejsou důvěryhodnými zákazníky

Nyní tedy vracíme úrokovou sazbu pouze v případě, že je zákazník nad kartou, A ve sloupci důvěryhodných zákazníků máme „Ne“. Vaši štamgasti jsou opět šťastní.

Přečtěte si více na hlavní stránce o funkci Excel A <>.

Použití IF s OR

NEBO je další z logických funkcí Excelu. Stejně jako AND vám umožňuje definovat více než jednu podmínku. Ale na rozdíl od AND vrátí TRUE, pokud jsou pravdivé KAŽDÉ z vámi definovaných testů.

Možná, že zákazníci, kteří jsou nad kartou, nejsou jediným důvodem, proč byste je odřízli. Možná dáte některým lidem dočasný zákaz z jiných důvodů, možná hazard v prostorách.

Přidáte tedy nový sloupec k identifikaci zakázaných zákazníků a aktualizujete své „Odříznout?“ sloupec s testem NEBO:

1	= IF (NEBO (C4> D4, E4 = "Ano"), "Ano", "Ne")

Při pohledu na část NEBO:

1	NEBO (C4> D4, E4 = "Ano")

Existují dvě podmínky:

• C4> D4: kontrola, zda nepřekračují limit karty
• F4 = „Ano“: nová část, kontrola, zda nejsou aktuálně zakázány

To bude vyhodnoceno jako pravdivé, pokud jsou nad jejich záložkou, nebo pokud je ve sloupci E „Ano“. Jak vidíte, Harry je nyní přerušen, i když nepřekročil svůj limit karet.

Přečtěte si více na hlavní stránce o funkci Excel NEBO <>.

Použití IF s XOR

XOR je další logická funkce, která vrací „Exclusive Or“. To je o něco méně intuitivní než ty předchozí, o kterých jsme diskutovali.

V jednoduchých případech definujete dvě podmínky a XOR se vrátí:

• TRUE, pokud je kterýkoli argument pravdivý (stejný jako normální NEBO)
• FALSE, pokud jsou oba argumenty pravdivé
• FALSE, pokud jsou oba argumenty nepravdivé

Příklad by to mohl vyjasnit. Představte si, že chcete začít poskytovat svým zaměstnancům měsíční bonusy:

• Pokud prodají přes 800 dolarů za jídlo nebo přes 800 dolarů za nápoje, dáte jim poloviční bonus
• Pokud se v obou prodá přes 800 USD, poskytnete jim plný bonus
• Pokud se v obou prodají pod 800 USD, nedostanou žádný bonus.

Už víte, jak cvičit, pokud dostanou plný bonus. Použili byste pouze IF s AND, jak bylo popsáno výše.

1	= IF (AND (C4> 800, D4> 800), "Yes", "No")

Ale jak byste zjistili, kdo dostane poloviční bonus? Tady přichází XOR:

1	= IF (XOR (C4> = 800, D4> = 800), "Ano", "Ne")

Jak vidíte, prodej nápojů Woody’s přesáhl 800 dolarů, ale ne prodej potravin. Získá tedy poloviční bonus. U Coach je to naopak. Diane a Carla prodaly více než 800 $ za oba, takže nedostávají poloviční bonus (oba argumenty jsou PRAVDĚ) a Rebecca udělala pod hranicí pro oba (oba argumenty FALSE), takže vzorec opět vrací „Ne“.

Přečtěte si více na hlavní stránce o funkci Excel XOR <>.

Použití IF s NOT

NOT je další z logických funkcí Excelu, která se u IF velmi běžně používá.

NEVrátí výsledek logického testu. Jinými slovy, kontroluje, zda nebyla splněna podmínka.

Můžete jej použít s IF takto:

1	= IF (AND (C3> = 1985, NOT (D3 = "Steven Spielberg")), "Watch", "Don’t Watch")

Zde máme tabulku s údaji o některých filmech z 80. let. Chceme identifikovat filmy vydané v roce 1985 nebo později, které nerežíroval Steven Spielberg.

Protože NOT je vnořen do funkce AND, Excel to vyhodnotí jako první. Výsledek pak použije jako součást AND.

Přečtěte si více na hlavní stránce funkce Excel NOT <>.

Vnořené prohlášení IF

Příkaz IF můžete také vrátit ve svém prohlášení IF. To vám umožní provádět složitější výpočty.

Vraťme se k tabulce našich zákazníků. Představte si, že chcete klasifikovat zákazníky podle úrovně zadlužení vůči vám:

• $ 0: Žádné
• Až 500 $: Nízká
• 500 až 1 000 $: střední
• Více než 1 000 $: Vysoká

To lze provést „vnořením“ příkazů IF:

1	= IF (C4 = 0, "Žádný", IF (C4 <= 500, "Nízký", IF (C4 <= 1000, "Střední", IF (C4> 1000, "Vysoký"))))

Je snazší pochopit, když vložíte příkazy IF na samostatné řádky (ALT + ENTER ve Windows, CTRL + COMMAND + ENTER v počítačích Mac):

12345

=IF (C4 = 0, "Žádný",IF (C4 <= 500, "nízká",IF (C4 <= 1000, "střední",IF (C4> 1000, "Vysoká", "Neznámá"))))

IF C4 je 0, vrátíme „None“. Jinak přejdeme k dalšímu příkazu IF. IF C4 je roven nebo menší než 500, vrátíme „Low“. Jinak přejdeme k dalšímu příkazu IF … a tak dále.

Zjednodušení složitých IF prohlášení pomocí pomocných sloupců

Pokud máte více vnořených příkazů IF a používáte také logické funkce, může být velmi obtížné číst, testovat a aktualizovat vzorce.

To je obzvláště důležité mít na paměti, pokud tabulku budou používat i další lidé. To, co má ve vaší hlavě smysl, nemusí být pro ostatní tak zřejmé.

Sloupce pomocníků jsou skvělým způsobem, jak tento problém vyřešit.

Jste analytik finančního oddělení velké společnosti. Byli jste požádáni o vytvoření tabulky, která kontroluje, zda má každý zaměstnanec nárok na firemní důchod.

Zde jsou kritéria:

Pokud je vám tedy méně než 55 let, musíte mít za sebou 30 let služby, abyste byli způsobilí. Pokud je vám 55 až 59 let, potřebujete 15 let služby. A tak dále, až do věku 65 let, kde máte nárok bez ohledu na to, jak dlouho jste tam pracovali.

K vyřešení tohoto problému můžete použít jeden komplexní příkaz IF:

1	= IF (NEBO (F4> = 65, AND (F4> = 62, G4> = 5), AND (F4> = 60, G4> = 10), AND (F4> = 55, G4> = 15), G4 > 30), „Způsobilé“, „Nevhodné“)

Fíha! Je těžké se v tom zorientovat, že?

Lepším přístupem by mohlo být použití pomocných sloupců. Máme zde pět logických testů, které odpovídají každému řádku v tabulce kritérií. To je snazší zjistit, pokud do vzorce přidáme konce řádků, jak jsme diskutovali dříve:

12345678

= IF (NEBO(F4> = 65,A (F4> = 62, G4> = 5),A (F4> = 60, G4> = 10),A (F4> = 55, G4> = 15),G4> 30), „Způsobilé“, „Nevhodné“)

Těchto pět testů tedy můžeme rozdělit do samostatných sloupců a poté jednoduše zkontrolovat, zda je některý z nich pravdivý:

Každý sloupec v tabulce od E do I obsahuje každé z našich kritérií samostatně. Pak v J4 máme následující vzorec:

1	= IF (COUNTIF (E4: I4, TRUE), "Způsobilé", "Nevhodné")

Zde máme příkaz IF a logický test používá COUNTIF <> k počítání počtu buněk v E4: I4, které obsahují TRUE.

Pokud COUNTIF nenajde PRAVDU hodnotu, vrátí 0, což IF interpretuje jako NEPRAVDU, takže IF vrátí „Nevhodné“.

Pokud COUNTIF najde nějaké SKUTEČNÉ hodnoty, vrátí jejich počet. IF interpretuje jakékoli jiné číslo než 0 jako PRAVDA, takže vrátí „Způsobilé“.

Rozdělení logických testů tímto způsobem usnadňuje čtení vzorce a pokud se s ním něco děje, je mnohem snazší zjistit, kde je chyba.

Skrytí pomocných sloupců pomocí seskupení

Pomocné sloupce usnadňují správu vzorce, ale jakmile je máte na svém místě a víte, že fungují správně, často zabírají místo v tabulce, aniž by přidaly jakékoli užitečné informace.

Dalo by se skrýt sloupce, ale to může vést k problémům, protože skryté sloupce je těžké zjistit, pokud se nepodíváte pozorně na záhlaví sloupců.

Lepší možností je seskupování.

Vyberte sloupce, které chcete seskupit, v našem případě E: I. Poté stiskněte ALT + SHIFT + ŠIPKA VPRAVO ve Windows nebo COMMAND + SHIFT + K na Macu. Můžete také přejít na kartu „Data“ na pásu karet a v části „Osnova“ vybrat „Skupina“.

Skupinu uvidíte nad záhlavími sloupců takto:

Poté jednoduše stisknutím tlačítka „-“ skryjte sloupce:

Funkce IFS

Vnořené příkazy IF jsou velmi užitečné, když potřebujete provádět složitější logická srovnání a potřebujete to udělat v jedné buňce. Mohou se však komplikovat, protože se prodlužují, a může být obtížné je číst a aktualizovat na obrazovce.

Z aplikací Excel 2022 a Excel 365 společnost Microsoft představila další funkci IFS, která má tuto správu trochu usnadnit. Vnořeného příkladu IF výše lze dosáhnout pomocí IFS takto:

1234567

= IFS (C4 = 0, „Žádný“,C4 <= 500, „nízká“,C4 <= 1000, "střední",C4> 1000, „Vysoká“,PRAVDA, „Neznámý“,)

Vše si můžete přečíst na hlavní stránce funkce Excel IFS <>.

Použití IF s podmíněným formátováním

Funkce podmíněného formátování v Excelu vám umožňuje formátovat buňku různými způsoby v závislosti na jejím obsahu. Protože IF vrací různé hodnoty na základě našeho logického testu, možná bychom chtěli použít podmíněné formátování, aby byly tyto různé hodnoty lépe viditelné.

Vraťme se tedy k naší tabulce bonusů pro zaměstnance dříve.

Vracíme „Ano“ nebo „Ne“ podle toho, jaký bonus chceme dát. To nám říká, co potřebujeme vědět, ale informace na nás nevyskočí. Zkusme to napravit.

Postupujete takto:

• Vyberte oblast buněk obsahující vaše příkazy IF. V našem případě je to E4: F8.
• Klikněte na „Podmíněné formátování“ v části „Styly“ na kartě „Domů“ na pásu karet.
• Klikněte na „Zvýraznit pravidla buněk“ a poté na „Rovno“.
• Do prvního pole zadejte „Ano“ (nebo jakoukoli návratovou hodnotu, kterou potřebujete), a pak z druhého pole vyberte požadované formátování. (Vyberu pro to zelenou).
• Opakujte pro všechny své návratové hodnoty (hodnoty „Ne“ také nastavím na červenou)

Zde je výsledek:

Použití IF v maticových vzorcích

Pole je rozsah hodnot a v polích aplikace Excel jsou reprezentovány jako hodnoty oddělené čárkami uzavřené v závorkách, jako například:

1	{1,2,3,4,5}

Krása polí spočívá v tom, že vám umožňují provést výpočet pro každou hodnotu v rozsahu a poté vrátit výsledek. Například funkce SUMPRODUCT bere dvě pole, vynásobí je dohromady a sečte výsledky.

Takže tento vzorec:

1	= SUMPRODUCT ({1,2,3}, {4,5,6})

… Vrací 32. Proč? Pojďme to zpracovat:

12345

1 * 4 = 42 * 5 = 103 * 6 = 184 + 10 + 18 = 32

Do tohoto obrázku můžeme vnést příkaz IF, takže ke každému z těchto násobení dojde pouze v případě, že logický test vrátí hodnotu true.

Vezměte si například tato data:

https://www.automateexcel.com/excel/wp-content/uploads/2020/07/SUMPRODUCT-Example-Range.png "no"> 1 = SUMPRODUCT (IF ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))

Poznámka: V aplikaci Excel 2022 a dřívější musíte stisknout CTRL + SHIFT + ENTER, aby se toto změnilo na vzorec pole.

Skončili bychom s něčím takovým:

https://www.automateexcel.com/excel/wp-content/uploads/2020/07/SUMPRODUCTS-IF-Results-Table.png "no"> 1 $ C $ 2: $ C $ 10 = $ G2

Pokud je v angličtině název ve sloupci C stejný jako v G2 („Olivia“), znásobte hodnoty ve sloupcích D a E pro daný řádek. Jinak je nerozmnožujte. Poté sečtěte všechny výsledky.

Více o tomto vzorci se můžete dozvědět na hlavní stránce pro SUMPRODUCT IF Formula <>.

IF v Tabulkách Google

Funkce IF funguje v Tabulkách Google úplně stejně jako v Excelu:

Další poznámky

Pomocí funkce IF otestujte, zda je podmínka PRAVDA. Pokud je podmínka PRAVDA, proveďte jednu věc. Pokud je to NEPRAVDA, udělejte další. Podmínkou musí být logický výraz (např. A1> 5), odkaz na buňku obsahující hodnotu TRUE nebo FALSE nebo pole obsahující všechny logické hodnoty.

Funkce IF může současně testovat pouze jednu podmínku. Můžete však „vnořit“ další logické funkce do podmínky IF a testovat více podmínek najednou:

= if (AND (a1> 0, a2> 0), TRUE, FALSE)
= if (NEBO (a1> 0, a2> 0), PRAVDA, NEPRAVDA)
= if (XOR (a1> 0, a2> 0), TRUE, FALSE)
NEBO Test funkcí, pokud jeden nebo více podmínky jsou splněny.
A Test funkcí, pokud Všechno podmínky jsou splněny.
Test funkcí XOR, pokud jeden a jediný jsou splněny podmínky.

Funkci IF můžete také „vnořit“ do funkce IF:

1	= if (a1 <0, if (a2 <0, "Both", "only 1"), "only one")

Nyní několik konkrétních příkladů toho, jak funkce IF funguje v praxi:

1. Spusťte novou pracovní knihu.

2. Do buňky A1 zadejte hodnotu 10 (a stiskněte Enter)

3. Poté do buňky B1 zadejte následující vzorec:

1	= IF (A1> 5, "VĚTŠÍ NEŽ 5", "MÉNĚ NEŽ 5")

4. Obrazovka by nyní měla vypadat takto:

5. Pokud jste vzorec zadali správně, v buňce B1 se zobrazí zpráva „Větší než 5“.

6. Vzorec, který jste zadali do buňky B1, provede test „A1> 5“, tj. Zkontroluje, zda je hodnota v buňce A1 větší než 5. Aktuálně je hodnota v buňce A1 10 - podmínka je tedy PRAVDA a zpráva Zobrazí se „BIGGER THAN 5“

7. Pokud nyní změníme hodnotu v buňce A1 na 2:

Zpráva v buňce B2 je nyní „MÉNĚ NEŽ 5“, protože podmínkou je NEPRAVDA.

8. Můžete stále měnit hodnotu v buňce A1 a zpráva v buňce B2 se podle toho upraví.

9. Samozřejmě existují situace, kdy by tento stav mohl poskytnout škodlivé výsledky:

• Co se stane, když do buňky A1 zadáme hodnotu 5?

• Co když necháme buňku A1 prázdnou?

• Co kdybychom do buňky A1 vložili nějaký text, např. Frázi DOG

Více o funkci IF IF

Nyní se podíváme na funkci IF podrobněji. Lze jej použít k velmi snadné analýze velkého množství dat.

Představte si, že jste oblastním prodejním manažerem a máte prodejní tým. Celkové tržby, které každý člověk dosáhne, můžete zaznamenat do jednoduché tabulky aplikace Excel:

Předpokládejme, že kritériem pro bonus bylo, že tržby uskutečněné touto osobou přesáhly 40 000 GBP. Dalo by se jen "oko-koule" data a určit, že pouze Anton, Newton a Monique dosáhl cíle.

Je to docela snadné, když máte jen několik jmen. Pokud jich však máte několik, existuje prostor pro chyby. Naštěstí pomocí funkce IF v Excelu to lze udělat mnohem rychleji a bezpečněji.

Nastavte nový sešit a zadejte data podle výše. Potom do buňky D4 zadejte následující vzorec:-

1	= IF (C4> 40000, "BONUS PLATITELNÝ", "ŽÁDNÝ BONUS")

takže máte:

Všimněte si toho, jak Excel ukazuje strukturu vzorce IF - což je užitečná příručka asistenta.

Po zadání vzorce stiskněte klávesu ENTER a uvidíte, že se vyhodnotí pro první řádek:

Vzorec byl vyhodnocen pro Martina - protože vydělal méně než 40 000 liber, nemá nárok na žádný bonus.

A poté přetáhneme vzorce dolů kliknutím na pravý dolní roh a tažením dolů můžeme určit, zda má každý člověk nárok na bonus:

A vidíme, že Excel určil, kteří z prodejců mají nárok na bonus.

Zpět na seznam všech funkcí v Excelu

Prohlášení VBA IF

Můžete také použít příkazy If ve VBA. Kliknutím na odkaz se dozvíte více, ale zde je jednoduchý příklad:

1234567

Dílčí test_IF ()Pokud rozsah („a1“). Hodnota <0 pakRozsah („b1“). Hodnota = „Negativní“Konec IfKonec If

Tento kód otestuje, zda je hodnota buňky záporná. Pokud ano, napíše do další buňky „záporné“.