IF s viacerými podmienkami: AND, OR a IFS
Keď o výsledku rozhoduje viac podmienok, IF sa spojí s funkciami AND alebo OR. Pri viacerých pásmach, napríklad známkach, pomôže IFS alebo IF vnorené do seba.
Vyskúšaj si to: Nárok na prémiu
Do bunky B4 napíš =IF(AND(B1>=2;B2>=4);"áno";"nie") a stlač Enter. Prémiu dostane, kto je vo firme aspoň 2 roky a zároveň má hodnotenie aspoň 4.
| A | B | |
|---|---|---|
| 1 | Rokov vo firme | 3 |
| 2 | Hodnotenie (1–5) | 4 |
| 3 | ||
| 4 | Prémia |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =IF(AND(B1>=2;B2>=4);"áno";"nie")
AND(B1>=2;B2>=4) vráti TRUE, len keď platia obe podmienky. IF potom podľa toho vráti „áno“ alebo „nie“.
AND: musia platiť všetky podmienky
=AND(logická_hodnota1; [logická_hodnota2]; …)
AND vráti TRUE, len keď platia všetky podmienky. Stačí, aby jedna neplatila, a vráti FALSE. Podmienok môže byť najviac 255. Sama osebe AND vráti len TRUE alebo FALSE, preto ju dávaš do IF ako podmienku:
=IF(AND(B1>=2;B2>=4);"áno";"nie")
OR: stačí jedna podmienka
=OR(logická_hodnota1; [logická_hodnota2]; …)
OR vráti TRUE, keď platí aspoň jedna podmienka. FALSE vráti, len keď neplatí žiadna. Typický príklad
je vstup zadarmo pre deti alebo seniorov: =IF(OR(B1<6;B1>=70);"zadarmo";"platí").
Pomôcka: keď vo vete povieš „a zároveň“, použi AND. Keď povieš „alebo“, použi OR. AND a OR sa dajú aj
kombinovať, napríklad OR(B2<=1,5;AND(B2<=2;C2="áno")).
IFS: viac pásiem bez vnárania
=IFS(logický_test1; hodnota_ak_pravda1; [logický_test2; hodnota_ak_pravda2]; …)
IFS berie dvojice podmienka a výsledok a vráti výsledok prvej podmienky, ktorá platí. Zvyšné už neskúša, preto na poradí záleží: pri pásmach začni najvyšším alebo najnižším a pokračuj postupne. Dvojíc môže byť najviac 127.
Keď neplatí žiadna podmienka, IFS vráti chybu #N/A. Na koniec preto daj podmienku TRUE, ktorá
platí vždy a zachytí všetko ostatné. IFS je v Exceli 2019 a novšom a v Exceli pre Microsoft 365. V Exceli 2016 vráti
#NAME?.
Vnorené IF
Pred IFS sa pásma riešili tak, že sa ako tretí argument IF napísalo ďalšie IF:
=IF(B1<=2;3,5;IF(B1<=10;5,9;9,9)). Funguje to v každej verzii Excelu. Vnoriť sa dá až 64
funkcií IF, ale pri viac ako troch-štyroch je vzorec ťažko čitateľný. Každé IF potrebuje vlastnú pravú
zátvorku, tie sa zbierajú na konci vzorca.
Príklady
AND s textom a číslom
Objednávka ide expresne, keď je zaplatená a zároveň je tovar na sklade.
=IF(AND(B1="áno";B2>0);"expres";"čaká") | A | B | |
|---|---|---|
| 1 | Zaplatená | áno |
| 2 | Na sklade (ks) | 12 |
| 3 | ||
| 4 | Odoslanie | expres |
IFS: zľava podľa množstva
Od 100 kusov je zľava 10 %, od 50 kusov 5 %, inak žiadna. Pre 64 kusov platí druhá podmienka.
=IFS(B1>=100;10%;B1>=50;5%;TRUE;0) | A | B | |
|---|---|---|
| 1 | Kusov | 64 |
| 2 | ||
| 3 | Zľava | 5% |
To isté vnoreným IF
Rovnaká zľava pre Excel 2016 a starší, ktorý IFS nepozná.
=IF(B1>=100;10%;IF(B1>=50;5%;0)) | A | B | |
|---|---|---|
| 1 | Kusov | 64 |
| 2 | ||
| 3 | Zľava | 5% |
Časté chyby a čo znamenajú
AND namiesto OR
Podmienka AND(B1<6;B1>=70) nikdy neplatí, lebo nikto nemá menej ako 6 a zároveň aspoň 70
rokov. Keď výsledok vychádza stále rovnako, skontroluj, či nemá byť OR.
Zlé poradie podmienok v IFS alebo vo vnorenom IF
Pri známkach s prvou podmienkou B1>=50 dostane 4 aj ten, kto má 95 bodov. IFS aj vnorené IF
skončia pri prvej splnenej podmienke, takže najprv musí byť najprísnejšia.
Chyba #N/A z IFS
Neplatí žiadna podmienka. Pridaj na koniec dvojicu TRUE;hodnota pre všetky ostatné prípady.
Chyba #NAME?
Excel funkciu IFS nepozná, lebo je starší ako verzia 2019. Použi vnorené IF.
Podmienka napísaná ako v matematike
Zápis 18<=B1<=26 Excel nevyhodnotí tak, ako čakáš. Najprv porovná 18 s B1 a výsledok TRUE
alebo FALSE porovná s 26. Správne je AND(B1>=18;B1<=26).
Cvičenia
Päť úloh od ľahkých po náročnejšie. Vzorec píš do vyznačenej bunky, kontrolu spustíš klávesom Enter alebo tlačidlom Skontrolovať. Kontrola vzorec vyskúša aj na hraničných hodnotách, napríklad presne 70 rokov.
Cvičenie 1: Vstup zadarmo
ľahkáHrad má vstup zadarmo pre deti mladšie ako 6 rokov a pre seniorov od 70 rokov. Do B3 napíš vzorec, ktorý podľa veku v B1 vráti „zadarmo“, inak „platí“.
| A | B | |
|---|---|---|
| 1 | Vek | 34 |
| 2 | ||
| 3 | Vstupné |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =IF(OR(B1<6;B1>=70);"zadarmo";"platí")
OR vráti TRUE, keď platí aspoň jedna podmienka. Človek nemôže byť naraz dieťa aj senior, preto by tu AND nikdy nevrátila TRUE.
Cvičenie 2: Kto postupuje
strednáDo ďalšieho kola výberového konania postupuje, kto má z testu (stĺpec B) aspoň 70 bodov a zároveň prešiel pohovorom. Do D2 napíš vzorec s funkciami IF a AND, ktorý vráti „postupuje“, inak „končí“. Vzorec sa skopíruje do D3 až D6.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Uchádzač | Test | Pohovor | Výsledok |
| 2 | Ďurica | 82 | áno | |
| 3 | Kubišová | 91 | nie | |
| 4 | Lenhart | 64 | áno | |
| 5 | Oravcová | 70 | áno | |
| 6 | Pavlík | 55 | nie |
Klikni na bunku a jej adresa sa vloží do vzorca. Bunky s prerušovaným okrajom ukážu, čo vzorec vypočíta po skopírovaní.
Riešenie: =IF(AND(B2>=70;C2="áno");"postupuje";"končí")
AND vráti TRUE, len keď uchádzač splní oboje. Kubišová má najviac bodov, ale pohovorom neprešla, preto končí. Oravcová má presne 70 bodov a postupuje.
Cvičenie 3: Známka podľa bodov
strednáStupnica: od 90 bodov 1, od 80 bodov 2, od 65 bodov 3, od 50 bodov 4, menej ako 50 bodov 5. Do B3 napíš vzorec s funkciou IFS, ktorý podľa bodov v B1 vráti známku.
| A | B | |
|---|---|---|
| 1 | Body | 83 |
| 2 | ||
| 3 | Známka |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =IFS(B1>=90;1;B1>=80;2;B1>=65;3;B1>=50;4;TRUE;5)
IFS skúša podmienky zľava. Pri 83 bodoch neplatí B1>=90, ale platí B1>=80, takže vráti 2 a ďalej nepokračuje. TRUE na konci zachytí všetko pod 50 bodov.
Cvičenie 4: Poštovné podľa hmotnosti
náročnejšiaKuriér účtuje balík do 2 kg za 3,50 €, do 10 kg za 5,90 € a ťažší za 9,90 €. Firma má Excel 2016, ktorý IFS nepozná. Do B3 napíš vzorec s vnoreným IF, ktorý podľa hmotnosti v B1 vráti cenu.
| A | B | |
|---|---|---|
| 1 | Hmotnosť (kg) | 4,6 |
| 2 | ||
| 3 | Poštovné (€) |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =IF(B1<=2;3,5;IF(B1<=10;5,9;9,9))
Vnútorné IF sa vyhodnotí, len keď balík váži viac ako 2 kg. Vtedy už stačí rozlíšiť do 10 kg a ťažší. Každé vnorené IF potrebuje vlastnú zátvorku, preto sú na konci dve.
Cvičenie 5: Štipendium
náročnejšiaŠtipendium dostane študent s priemerom do 1,5. Dostane ho aj ten, kto má priemer do 2,0 a zároveň nárok na sociálnu podporu (stĺpec C). Do D2 napíš vzorec s funkciami IF, OR a AND, ktorý vráti „áno“ alebo „nie“. Vzorec sa skopíruje do D3 až D6.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Študent | Priemer | Podpora | Štipendium |
| 2 | Hana | 1,3 | nie | |
| 3 | Marek | 1,8 | áno | |
| 4 | Zuzana | 1,8 | nie | |
| 5 | Oliver | 2,4 | áno | |
| 6 | Veronika | 1,5 | nie |
Klikni na bunku a jej adresa sa vloží do vzorca. Bunky s prerušovaným okrajom ukážu, čo vzorec vypočíta po skopírovaní.
Riešenie: =IF(OR(B2<=1,5;AND(B2<=2;C2="áno"));"áno";"nie")
OR spojí dve cesty a stačí jedna. Druhá cesta je AND, lebo priemer do 2,0 nestačí bez sociálnej podpory. Zuzana má 1,8 bez podpory, preto štipendium nedostane.
Otázky a odpovede
Ako napísať IF s viacerými podmienkami?
Podmienky daj do AND alebo OR a túto funkciu použi ako prvý argument IF:
=IF(AND(podmienka1;podmienka2);"áno";"nie"). AND chce všetky podmienky, OR aspoň jednu.
Je lepšie IFS alebo vnorené IF?
IFS je čitateľnejšia a netreba v nej počítať zátvorky. Vnorené IF funguje aj v Exceli 2016 a starších verziách. Ak súbor otvárajú ľudia so staršou verziou, zvoľ vnorené IF.
Ako funguje funkcia AND v Exceli?
AND vráti TRUE, keď platia všetky podmienky v zátvorke, inak FALSE. Samotná vráti len TRUE alebo FALSE, preto sa najčastejšie používa ako podmienka vo funkcii IF.
Ako funguje funkcia OR v Exceli?
OR vráti TRUE, keď platí aspoň jedna podmienka. Hodí sa, keď vedie k výsledku viac ciest, napríklad „študent alebo senior“.
Dá sa namiesto IFS použiť VLOOKUP?
Áno, pri mnohých pásmach je to často prehľadnejšie. Pásma napíšeš do tabuľky a VLOOKUP s TRUE nájde správne pásmo. Hranice sa potom menia v tabuľke, nie vo vzorci.