IF Formula Excel - ak potom vyhlásenia

Stiahnite si ukážkový zošit

Stiahnite si ukážkový zošit

Tento tutoriál ukazuje, ako používať Funkcia IF IF v Exceli na vytváranie výkazov If Then.

Prehľad funkcií IF

Funkcia IF kontroluje, či je splnená podmienka. Ak je to PRAVDA, urobte jednu vec, ak NEPRAVDA urobte inú.

Ak chcete použiť funkciu hárku IF Excel, vyberte bunku a zadajte:

(Všimnite si, ako sa zobrazujú vstupy vzorcov)

IF funkcia Syntax a vstupy:

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

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

value_if_true - Hodnota alebo výpočet, ktorý sa má vykonať, ak je logický výraz PRAVDA.

value_if_false - Hodnota alebo výpočet, ktorý sa má vykonať, ak je logický výraz FALSE.

IF je „podmienená“ funkcia. To znamená, že definujete logický test a vráti jednu hodnotu, ak je tento test vyhodnotený ako pravdivý, a inú hodnotu, ak je nepravdivý.

Ako používať funkciu IF

Toto je veľmi základný príklad, aby ste videli, čo tým myslím. Skúste do Excelu napísať nasledujúce:

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

Pretože 2 + 2 sa v skutočnosti rovná 4, Excel vráti „Je to pravda!“. Ak by sme použili toto:

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

Teraz Excel vráti „Je to nepravda!“, Pretože 2 + 2 sa nerovná 5.

Tu je návod, ako môžete použiť IF v tabuľke.

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

Prevádzkujete športový bar a nastavujete limity jednotlivých kariet pre rôznych zákazníkov. Túto tabuľku ste nastavili na kontrolu, či každý zákazník prekročil svoj limit. V takom prípade ho prerušíte, kým nezaplatí svoju kartu.

Skontrolujete, či je C4-D4 (ich aktuálna čiastka na karte mínus ich limit) väčšia ako 0. Toto je váš logický test. Ak je to pravda, IF vráti „Áno“ - mali by ste ich prerušiť. Ak je to nepravda, IF vráti „nie“ - necháte ich piť.

Čo IF môže vrátiť

Hore sme vrátili textový reťazec „Áno“ alebo „Nie“. Môžete však tiež vrátiť čísla alebo dokonca iné vzorce.

Povedzme, že niektorí z vašich zákazníkov používajú veľké karty. Aby ste to odradili, začnete účtovať úroky zákazníkom, ktorí prekročia svoj limit.

Na to môžete použiť IF:

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

Ak je karta vyššia ako limit, vráťte kartu vynásobenú 0,03, čím sa vrátia 3% karty. V opačnom prípade vráťte 0: nie sú nad kartou, takže nebudete účtovať úroky.

Použitie IF s AND

IF môžete skombinovať s funkciou AND programu Excel <>. Toto použijete v logickom teste, ktorý vám umožní určiť dve alebo viac podmienok na testovanie. Excel vráti hodnotu TRUE iba vtedy, ak sú VŠETKY testy pravdivé.

Implementovali ste teda svoju úrokovú sadzbu. Niektorí z vašich pravidelných sa však sťažujú. V minulosti vždy platili svoje karty, prečo na ne teraz zasahujete? Vymyslíte riešenie: určitým dôveryhodným zákazníkom nebudete účtovať úroky.

Do tabuľky vytvoríte nový stĺpec na identifikáciu dôveryhodných zákazníkov a aktualizujete svoj príkaz IF pomocou funkcie AND:

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

Pozrime sa na časť AND oddelene:

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

Všimnite si dvoch podmienok:

• C4> D4: kontrola, či neprekračujú limit kariet, ako predtým
• F4 = „Nie“: toto je nový bit, ktorý kontroluje, či nie sú dôveryhodným zákazníkom

Teraz teda vraciame úrokovú sadzbu iba vtedy, ak je zákazník nad svojou kartou, A v stĺpci dôveryhodného zákazníka máme „Nie“. Vaši štamgasti sú opäť šťastní.

Prečítajte si viac na hlavnej stránke o funkcii Excel A <>.

Použitie IF s OR

ALEBO je ďalšou z logických funkcií Excelu. Rovnako ako AND vám umožňuje definovať viac ako jednu podmienku. Ale na rozdiel od AND, vráti hodnotu TRUE, ak sú niektoré z vami definovaných testov pravdivé.

Možno, že zákazníci nie sú jediným dôvodom, prečo ich prerušiť. Možno niektorým ľuďom dáte dočasný zákaz z iných dôvodov, možno hazard v priestoroch.

Takže pridáte nový stĺpec na identifikáciu zakázaných zákazníkov a aktualizujete svoje „Odrezať?“ stĺpec s testom ALEBO:

1	= IF (ALEBO (C4> D4, E4 = "áno"), "áno", "nie")

Pri pohľade iba na časť ALEBO:

1	ALEBO (C4> D4, E4 = "Áno")

Existujú dve podmienky:

• C4> D4: kontrola, či neprekračujú limit karty
• F4 = „Áno“: nová časť, ktorá kontroluje, či nie sú aktuálne zakázané

To sa vyhodnotí ako pravdivé, ak sú nad kartou, alebo ak je v stĺpci E „Áno“. Ako vidíte, Harry je teraz prerušený, aj keď neprekročil svoj limit kariet.

Prečítajte si viac na hlavnej stránke o funkcii Excel ALEBO <>.

Použitie IF s XOR

XOR je ďalšou logickou funkciou, ktorá vracia hodnotu „Exclusive Or“. Toto je o niečo menej intuitívne ako tie predchádzajúce, o ktorých sme diskutovali.

V jednoduchých prípadoch definujete dve podmienky a XOR sa vráti:

• TRUE, ak je jeden z argumentov pravdivý (rovnaký ako normálny ALEBO)
• FALSE, ak sú oba argumenty pravdivé
• FALSE, ak sú oba argumenty nepravdivé

Príklad by to mohol objasniť. Predstavte si, že chcete začať poskytovať svojim zamestnancom mesačné bonusy:

• Ak predajú viac ako 800 dolárov za jedlo alebo viac ako 800 dolárov za nápoje, dáte im polovičný bonus
• Ak sa v oboch predá viac ako 800 dolárov, poskytnete im plný bonus
• Ak sa v oboch predajú pod 800 dolárov, nedostanú žiadny bonus.

Už viete, ako cvičiť, ak dostanú plný bonus. Použili by ste iba IF s AND, ako je popísané vyššie.

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

Ako by ste však zistili, kto získa polovičný bonus? Tu prichádza XOR:

1	= IF (XOR (C4> = 800, D4> = 800), „áno“, „nie“)

Ako vidíte, predaj nápojov Woody’s presiahol 800 dolárov, predaj potravín však nie. Získava teda polovičný bonus. U Coacha to platí naopak. Diane a Carla predali za obidve viac ako 800 dolárov, takže nedostávajú polovičný bonus (oba argumenty sú PRAVDIVÉ) a Rebecca urobila pod hranicou pre oba (oba argumenty NEPRAVDA), takže vzorec opäť vráti „nie“.

Prečítajte si viac na hlavnej stránke o funkcii Excel XOR <>.

Použitie IF s NOT

NOT je ďalšou z logických funkcií programu Excel, ktorá sa veľmi často používa s IF.

NEVráti výsledok logického testu. Inými slovami, kontroluje, či nebola splnená podmienka.

Môžete ho použiť s IF takto:

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

Tu máme tabuľku s údajmi o niektorých filmoch z 80. rokov minulého storočia. Chceme identifikovať filmy vydané v roku 1985 alebo neskôr, ktoré nerežíroval Steven Spielberg.

Pretože NOT je vnorený do funkcie AND, Excel to vyhodnotí ako prvé. Výsledok potom použije ako súčasť AND.

Prečítajte si viac na hlavnej stránke o funkcii Excel NIE <>.

Vnorené vyhlásenia IF

Výkaz IF môžete tiež vrátiť vo svojom vyhlásení IF. To vám umožní vykonávať komplexnejšie výpočty.

Vráťme sa k stolu našich zákazníkov. Predstavte si, že chcete zákazníkov klasifikovať podľa úrovne ich dlhu voči vám:

• 0 dolárov: žiadne
• Až 500 dolárov: nízke
• 500 až 1 000 dolárov: stredná
• Viac ako 1 000 dolárov: vysoké

Môžete to urobiť „vnorením“ príkazov IF:

1	= IF (C4 = 0, "Žiadny", IF (C4 <= 500, "Nízky", IF (C4 <= 1000, "Stredný", IF (C4> 1000, "Vysoký"))))

Je to jednoduchšie pochopiť, ak vložíte príkazy IF do oddelených riadkov (ALT + ENTER v systéme Windows, CTRL + COMMAND + ENTER v počítačoch Mac):

12345

=IF (C4 = 0, „Žiadne“,IF (C4 <= 500, "nízka",IF (C4 <= 1000, "stredný",IF (C4> 1000, „Vysoká“, „Neznáma“))))

AK je C4 0, vrátime „Žiadne“. V opačnom prípade prejdeme k ďalšiemu príkazu IF. IF C4 je rovný alebo menší ako 500, vrátime „Low“. V opačnom prípade prejdeme k ďalšiemu príkazu IF … a tak ďalej.

Zjednodušenie komplexných výkazov IF pomocou pomocných stĺpcov

Ak máte viacero vnorených príkazov IF a používate aj logické funkcie, vaše vzorce sa môžu veľmi ťažko čítať, testovať a aktualizovať.

Toto je obzvlášť dôležité mať na pamäti, ak budú tabuľku používať iní ľudia. Čo dáva zmysel vo vašej hlave, nemusí byť pre ostatných také zrejmé.

Pomocné stĺpce sú skvelým riešením tohto problému.

Ste analytikom finančného oddelenia veľkej spoločnosti. Boli ste požiadaní o vytvorenie tabuľky, ktorá kontroluje, či má každý zamestnanec nárok na dôchodok spoločnosti.

Tu sú kritériá:

Ak teda máte menej ako 55 rokov, na to, aby ste boli oprávnení, musíte mať pod palcom 30 rokov. Ak máte 55 až 59 rokov, potrebujete 15 rokov služby. A tak ďalej, až do veku 65 rokov, kde máte nárok bez ohľadu na to, ako dlho ste tam pracovali.

Na vyriešenie tohto problému môžete použiť jeden komplexný príkaz IF:

1	= IF (ALEBO (F4> = 65, AND (F4> = 62, G4> = 5), AND (F4> = 60, G4> = 10), AND (F4> = 55, G4> = 15), G4 > 30), „Spôsobilé“, „Nevhodné“)

Fíha! Je ťažké sa v tom zorientovať, však?

Lepším prístupom by mohlo byť použitie pomocných stĺpcov. Máme tu päť logických testov, ktoré zodpovedajú každému riadku v tabuľke kritérií. To je jednoduchšie vidieť, ak do vzorca pridáme konce riadkov, ako sme už diskutovali:

12345678

= IF (ALEBO (F4> = 65,A (F4> = 62, G4> = 5),A (F4> = 60, G4> = 10),A (F4> = 55, G4> = 15),G4> 30), „Spôsobilé“, „Nevhodné“)

Týchto päť testov teda môžeme rozdeliť do samostatných stĺpcov a potom jednoducho skontrolovať, či je niektorý z nich pravdivý:

Každý stĺpec v tabuľke od E do I obsahuje každé z našich kritérií oddelene. Potom v J4 máme nasledujúci vzorec:

1	= IF (COUNTIF (E4: I4, TRUE), „vhodné“, „nevhodné“)

Tu máme príkaz IF a logický test používa COUNTIF <> na spočítanie počtu buniek v rámci E4: I4, ktoré obsahujú TRUE.

Ak COUNTIF nenájde PRAVDU hodnotu, vráti hodnotu 0, ktorá IF interpretuje ako NEPRAVDU, takže IF vráti „Nevhodné“.

Ak COUNTIF nájde nejaké PRAVDIVÉ hodnoty, vráti ich počet. IF interpretuje akékoľvek iné číslo ako 0 ako TRUE, takže vráti „Eitable“.

Rozdelenie logických testov týmto spôsobom uľahčuje čítanie vzorca a ak sa s ním niečo deje, je oveľa jednoduchšie zistiť, kde je chyba.

Použitie zoskupenia na skrytie pomocných stĺpcov

Pomocné stĺpce uľahčujú správu vzorca, ale akonáhle ich máte na mieste a viete, že fungujú správne, často zaberajú miesto v tabuľke bez pridania akýchkoľvek užitočných informácií.

Môžete skryť stĺpce, ale môže to viesť k problémom, pretože skryté stĺpce je ťažké zistiť, pokiaľ sa nepozriete pozorne na hlavičky stĺpcov.

Lepšou možnosťou je zoskupenie.

Vyberte stĺpce, ktoré chcete zoskupiť, v našom prípade E: I. Potom v systéme Windows stlačte kombináciu klávesov ALT + SHIFT + ŠÍPKA VPRAVO alebo na počítačoch COMMAND + SHIFT + K. Môžete tiež prejsť na kartu „Údaje“ na páse s nástrojmi a v sekcii „Obrys“ vybrať „Skupina“.

Skupina sa zobrazí nad hlavičkami stĺpcov takto:

Potom jednoducho stlačte tlačidlo „-“, aby ste skryli stĺpce:

Funkcia IFS

Vnorené príkazy IF sú veľmi užitočné vtedy, keď potrebujete vykonať komplexnejšie logické porovnania a musíte to urobiť v jednej bunke. Môžu sa však skomplikovať, keď sa predĺžia, a je ťažké ich čítať a aktualizovať na obrazovke.

V aplikáciách Excel 2022 a Excel 365 predstavil Microsoft ďalšiu funkciu IFS, ktorá má túto správu trochu uľahčiť. Vnorený príklad IF uvedený vyššie je možné dosiahnuť pomocou IFS takto:

1234567

= IFS (C4 = 0, „Žiadne“,C4 <= 500, „nízka“,C4 <= 1000, "stredný",C4> 1000, „vysoká“,TRUE, „Unknown“,)

Všetko si môžete prečítať na hlavnej stránke funkcie Excel IFS <>.

Použitie IF s podmieneným formátovaním

Funkcia podmieneného formátovania v Exceli vám umožňuje formátovať bunku rôznymi spôsobmi v závislosti od jej obsahu. Pretože IF vracia rôzne hodnoty na základe nášho logického testu, možno budeme chcieť použiť podmienené formátovanie, aby boli tieto rôzne hodnoty lepšie viditeľné.

Vráťme sa teda k našej tabuľke bonusov pre zamestnancov z minulosti.

Vraciame „áno“ alebo „nie“ podľa toho, aký bonus chceme poskytnúť. To nám hovorí, čo potrebujeme vedieť, ale tieto informácie na nás nevyskakujú. Skúsme to napraviť.

Postupujete takto:

• Vyberte rozsah buniek obsahujúci vaše príkazy IF. V našom prípade je to E4: F8.
• Kliknite na položku „Podmienené formátovanie“ v sekcii „Štýly“ na karte „Domov“ na páse s nástrojmi.
• Kliknite na položku „Zvýrazniť pravidlá buniek“ a potom na „Rovná sa“.
• Do prvého poľa zadajte „Áno“ (alebo inú požadovanú návratovú hodnotu) a potom v druhom poli vyberte požadované formátovanie. (Na to vyberiem zelenú).
• Opakujte pre všetky svoje návratové hodnoty (hodnoty „Nie“ tiež nastavím na červeno)

Tu je výsledok:

Použitie IF v maticových vzorcoch

Pole je rozsah hodnôt a v poliach programu Excel sú reprezentované hodnotami oddelenými čiarkami uzavretými do zátvoriek, ako napríklad:

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

Krása polí je, že vám umožňujú vykonať výpočet pre každú hodnotu v rozsahu a potom vrátiť výsledok. Napríklad funkcia SUMPRODUCT vezme dve polia, vynásobí ich a spočíta výsledky.

Takže tento vzorec:

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

… Vráti 32. Prečo? Poďme to spracovať:

12345

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

Do tohto obrázku môžeme vložiť príkaz IF, takže každé z týchto násobení sa stane iba vtedy, ak logický test vráti hodnotu true.

Vezmite napríklad tieto údaje:

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 programe Excel 2022 a staršom musíte stlačiť CTRL + SHIFT + ENTER, aby ste to zmenili na vzorec poľa.

Skončili by sme s niečím takým:

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

Ak je názov v stĺpci C v angličtine rovnaký ako v stĺpci G2 („Olivia“), vynásobte hodnoty v stĺpcoch D a E pre daný riadok. V opačnom prípade ich nerozmnožujte. Potom zhrňte všetky výsledky.

Viac o tomto vzorci sa môžete dozvedieť na hlavnej stránke pre SUMPRODUCT IF Formula <>.

IF v Tabuľkách Google

Funkcia IF funguje v Tabuľkách Google úplne rovnako ako v programe Excel:

Doplňujúce Poznámky

Pomocou funkcie IF vyskúšajte, či je podmienka PRAVDA. Ak je podmienka PRAVDA, urobte jednu vec. Ak je to NEPRAVDA, urobte ďalšie. Podmienkou musí byť logický výraz (napr. A1> 5), odkaz na bunku obsahujúcu hodnotu TRUE alebo FALSE alebo pole obsahujúce všetky logické hodnoty.

Funkcia IF môže súčasne testovať iba jednu podmienku. Do podmienky IF však môžete „vnoriť“ ďalšie logické funkcie a testovať viac podmienok naraz:

= if (AND (a1> 0, a2> 0), TRUE, FALSE)
= if (ALEBO (a1> 0, a2> 0), TRUE, FALSE)
= if (XOR (a1> 0, a2> 0), TRUE, FALSE)
ALEBO Test funkcií, ak jeden alebo viac podmienky sú splnené.
A Funkčný test ak všetky podmienky sú splnené.
Test funkcií XOR, ak jeden a jediný sú splnené podmienky.

Funkciu IF môžete tiež „vnoriť“ do funkcie IF:

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

Teraz niekoľko konkrétnych príkladov toho, ako funkcia IF funguje v praxi:

1. Začnite písať novú pracovnú knihu.

2. Do bunky A1 zadajte hodnotu 10 (a stlačte kláves Enter)

3. Potom do bunky B1 zadajte nasledujúci vzorec:

1	= IF (A1> 5, "VÄČŠÍ AKO 5", "Menej ako 5")

4. Obrazovka by teraz mala vyzerať takto:

5. Ak ste vzorec zadali správne, v bunke B1 sa zobrazí správa „Väčšie ako 5“.

6. Vzorec, ktorý ste zadali do bunky B1, vykoná test „A1> 5“, tj skontroluje, či je hodnota v bunke A1 väčšia ako 5. V súčasnosti je hodnota v bunke A1 10 - takže podmienka je PRAVDA a správa Zobrazí sa „BIGGER THAN 5“

7. Ak teraz zmeníme hodnotu v bunke A1 na 2:

Správa v bunke B2 je teraz „Menej ako 5“, pretože podmienka je NEPRAVDA.

8. V bunke A1 môžete hodnotu stále meniť a správa v bunke B2 sa podľa toho upraví.

9. Samozrejme existujú situácie, kedy by tento stav mohol viesť k škodlivým výsledkom:

• Čo sa stane, ak do bunky A1 zadáme hodnotu 5?

• Čo keď ponecháme bunku A1 prázdnu?

• Čo keby sme do bunky A1 vložili nejaký text, napríklad výraz DOG

Viac o funkcii Excel IF

Teraz sa pozrieme na funkciu IF podrobnejšie. Môže sa použiť na jednoduchú analýzu veľkého množstva údajov.

Predstavte si, že ste oblastný manažér predaja a máte predajný tím. Celkové tržby, ktoré každá osoba dosiahne, môžete zaznamenať do jednoduchej tabuľky programu Excel:

Predpokladajme, že kritéria na bonus boli, že tržby uskutočnené touto osobou presiahli 40 000 GBP. Dalo by sa len „okopnúť“ údajmi a určiť, že cieľ dosiahli iba Anton, Newton a Monique.

Je to celkom jednoduché, keď máte len niekoľko mien. Ak ich však máte niekoľko, existuje priestor pre chyby. Našťastie pomocou funkcie IF programu Excel je možné to urobiť oveľa rýchlejšie a bezpečnejšie.

Nastavte nový zošit a zadajte údaje podľa vyššie uvedeného postupu. Potom do bunky D4 zadajte nasledujúci vzorec:-

1	= IF (C4> 40000, "BONUS PLATNÝ", "ŽIADNY BONUS")

aby ste mali:

Všimnite si toho, ako Excel ukazuje štruktúru vzorca IF - čo je užitočná príručka asistenta.

Po zadaní vzorca stlačte kláves ENTER a uvidíte, že sa vyhodnotí pre prvý riadok:

Vzorec bol vyhodnotený pre Martina - keďže zarobil menej ako 40 000 libier, nemá nárok na žiadny bonus.

Potom vzorce potiahneme nadol kliknutím na pravý dolný roh a potiahnutím nadol môžeme určiť, či má každá osoba nárok na bonus:

A vidíme, že Excel určil, ktorí z Predajcov majú nárok na bonus.

Vráťte sa do zoznamu všetkých funkcií v programe Excel

Vyhlásenia IF VBA

Môžete tiež použiť príkazy If vo VBA. Po kliknutí na odkaz sa dozviete viac, ale tu je jednoduchý príklad:

1234567

Vedľajší test_IF ()Ak je rozsah („a1“). Hodnota <0 potomRozsah („b1“). Hodnota = „Záporné“Koniec AkKoniec Ak

Tento kód otestuje, či je hodnota bunky záporná. Ak je to tak, do ďalšej bunky sa napíše „záporné“.