excelkurz.sk

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.

AB
1 Rokov vo firme 3
2 Hodnotenie (1–5) 4
3
4 Prémia

Klikni na bunku a jej adresa sa vloží do vzorca.

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.

B4 =IF(AND(B1="áno";B2>0);"expres";"čaká")
AB
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.

B3 =IFS(B1>=100;10%;B1>=50;5%;TRUE;0)
AB
1 Kusov 64
2
3 Zľava 5%

To isté vnoreným IF

Rovnaká zľava pre Excel 2016 a starší, ktorý IFS nepozná.

B3 =IF(B1>=100;10%;IF(B1>=50;5%;0))
AB
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.

Vyriešené: 0 z 5 Body: 0 Séria: 0 dní

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í“.

AB
1 Vek 34
2
3 Vstupné

Klikni na bunku a jej adresa sa vloží do vzorca.

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.

ABCD
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í.

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.

AB
1 Body 83
2
3 Známka

Klikni na bunku a jej adresa sa vloží do vzorca.

Cvičenie 4: Poštovné podľa hmotnosti

náročnejšia

Kurié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.

AB
1 Hmotnosť (kg) 4,6
2
3 Poštovné (€)

Klikni na bunku a jej adresa sa vloží do vzorca.

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.

ABCD
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í.

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.

Súvisiace