IF-funktsioon võimaldab loogiliselt võrrelda väärtust ja eeldatud tulemit, kontrollides väärtuse vastavust teatud tingimusele; ning tagastab erinevad tulemid, kui vastus on tõene (True) või väär (False).
-
=KUI(miski on tõene, siis tee seda, vastasel juhul tee midagi muud).
Seega võib IF-funktsioonil olla kaks tulemit. Esimene tulem kuvatakse juhul, kui võrdluse tulemus osutub tõeseks (True) ja teine tulem juhul, kui võrdluse tulemus on väär (False).
IF-laused on äärmiselt töökindlad ja aluseks paljudele arvutustabelimudelitele, kuid need on ka paljude arvutustabelis ilmnevate probleemide algpõhjuseks. Ideaaljuhul tuleks IF-lause puhul rakendada nii vähe tingimusi kui võimalik (nt mees/naine, jah/ei ja võib-olla), kuid mõnikord tuleb kasutada keerukamaid stsenaariume, mis nõuavad enam kui kolme IF-funktsiooni pesastamist*.
* „Pesastamine” viitab siin mitme funktsiooni ühendamisele samas valemis.
Funktsiooni IF, mis on üks loogikafunktsioonidest, kasutage siis, kui soovite, et tagastataks üks väärtus, kui tingimus on täidetud ja teine väärtus, kui tingimus on väär.
Süntaks
IF(loogika_test; [väärtus_kui_tõene]; [väärtus_kui_väär])
Siin on mõned näited.
-
=IF(A2>B2;"Eelarvest väljas";"OK")
-
=IF(A2=B2;B4–A4;"")
|
Argumendi nimi |
Kirjeldus |
|
loogika_test (nõutav) |
Tingimus, mille täidetust soovite kontrollida. |
|
väärtus_kui_tõene (nõutav) |
Väärtus, mida soovite tagastada siis, kui loogika_test annab tulemuseks TRUE. |
|
väärtus_kui_väär (valikuline) |
Väärtus, mida soovite tagastada juhul, kui loogika_test annab tulemuseks FALSE. |
Märkused
Kuigi Excel võimaldab pesastada kuni 64 erinevat IF-funktsiooni, pole soovitatav seda teha. Miks?
-
Mitme IF-lause koostamisel on vaja need väga hoolikalt läbi mõelda ja veenduda, et need arvutatakse iga tingimuse puhul õigesti kuni lõpptulemini. Kui te funktsioone 100% õigesti ei pesasta, võib valem toimida 75% juhtudel, kuid tagastada eeldatust erineva tulemi 25% juhtudel. Kahjuks on üsna vähetõenäoline, et need 25% juhtudest ära tunnete.
-
Mitme IF-lause haldamine võib olla väga keerukas, eriti juhul, kui vaatate neid üle mõna aja möödudes, püüdes aru saada, mida teie olete (või halvemal juhul keegi teine on) püüdnud teha.
Kui leiate end olukorrast, kus IF-lause näib üha kasvavat, ilma et selle lõpp paistaks, on aeg hiir käest panna ja oma strateegia uuesti läbi mõelda.
Vaatame, kuidas õigesti koostada keerukat, mitmest IF-funktsioonist koosnevat pesastatud IF-lauset ja millal tuleks mõista, et on aeg kasutada mõnda muud Exceli arsenalis olevat tööriista.
Näited
Allpool on näide üsna tavalisest pesastatud IF-lausest, mille abil saab õppurite testitulemused teisendada neile vastavaks tähena esitatud hindeks.
-
=IF(D2>89;"A";IF(D2>79;"B";IF(D2>69;"C";IF(D2>59;"D";"F"))))
See keerukas pesastatud IF-lause järgib lihtsat loogikat.
-
Kui testitulemus (lahtris D2) on üle 89 punkti, saab õppur hinde A.
-
Kui testitulemus on üle 79 punkti, saab õppur hinde B.
-
Kui testitulemus on üle 69 punkti, saab õppur hinde C.
-
Kui testitulemus on üle 59 punkti, saab õppur hinde D.
-
Muul juhul saab õppur hinde F.
Selle konkreetset näite puhul võib õigetele tulemitele üsana kindel olla, sest testitulemite ja täheliste hinnete vastastikune seos tõenäoliselt ei muutu ja seega pole vaja valemit pidevalt üle vaadata. Kuid mis saab siis, kui teil on vaja hindeid esitada täpsemal kujul A+, A ja A- (jne)? Nüüd tuleb 4 tingimusega IF-lause ümber kirjutada nii, et selles oleks 12 tingimust! Sel juhul näeks valem välja järgmine.
-
=IF(B2>97;"A+";IF(B2>93;"A";IF(B2>89;"A-";IF(B2>87;"B+";IF(B2>83;"B";IF(B2>79;"B-"; IF(B2>77;"C+";IF(B2>73;"C";IF(B2>69;"C-";IF(B2>57;"D+";IF(B2>53;"D";IF(B2>49;"D-";"F"))))))))))))
See valem toimib endiselt õigesti ja ootuspäraselt, kuid selle kirjutamine võtab kaua aega ja veelgi rohkem aega kulub valemi katsetamisele veendumaks, et see teeks seda, mida soovite. Teine ilmne probleem on see, et tulemused ja neile vastavad tähelised hinded tuleb sisestada käsitsi. Milline on tõenäosus, et teete kogemata mõne kirjavea? Kujutage nüüd ette, et veelgi keerukamate tingimuste korral peaksite andmeid käsitsi sisestama 64 korda! Kindlasti on see võimalik, kuid kas te tõesti soovite kogu seda vaeva ja sellega kaasneda võivaid vigu, mida on väga raske märgata.
: Igal Exceli funktsioonil peab olema algus- ja lõpusulg (). Excel püüab aidata teil mõista, mida ja kuhu peaksite sisestama, kuvades valemi redigeerimise ajal selle eri osad värviliselt. Näiteks juhul, kui redigeeriksite ülalesitatud valemit ja nihutaksite kursori igale valemis olevale lõpusulule „)”, kuvataks sellele vastav algussulg sama värviga. See võib olla eriti kasulik keerukate pesastatud valemite korral, kui püüate välja selgitada, kas teil on piisav arv sulupaare.
Lisanäited
Allpool on väga levinud näide selle kohta, kuidas arvutada müügivahendustasu teenitud tulu alusel.
-
IF(C9>15 000;20%;IF(C9>12 500;17,5%;IF(C9>10 000;15%;IF(C9>7500;12,5%;IF(C9>5000;10%;0)))))
Selle valemi süntaks ütleb järgmist: KUI(lahtris C9 olev väärtus on suurem kui 15 000, tagasta tulem 20%; KUI(lahtris C9 olev väärtus on suurem kui 12 500, tagasta tulem 17,5% jne ...
Kuigi see sarnaneb oluliselt varasema hinnete näitega, on see valem suurepärane näide sellest, kui keeruline võib olla suurte IF-lausete haldamine – mida oleks vaja teha, kui teie ettevõte otsustab lisada uusi kompensatsioonitasemeid ja võib-olla isegi muuta olemasolevaid dollareid või protsendiväärtusi? Teil oleks palju tööd kätel!
: Pikkade valemite lugemise hõlbustamiseks saate valemiribale lisada reapiirid. Selleks vajutage enne uuele reale lisatavat teksti klahvikombinatsiooni ALT+ENTER.
Allpool on näide vahendustasu arvutamise valemist, mille loogikatesti osad on vales järjestuses.
Kas märkate, mis on valesti? Võrrelge tulusummade järjestust eelmise näite omaga. Kuidas on summad selles valemis esitatud? Jah, summad on esitatud kasvavas järjestuses (5000 kuni 15 000 €), mitte vastupidi. Aga miks peaks sellest nii suurt numbrit tegema? See on oluline seetõttu, et valem ei pääse kaugemale esimesest hindamiskriteeriumist 5000 €. Oletame, et teie tulu on 12 500 €, kuid IF-lause tagastab tulemi 10%, sest tulusumma on üle 5000 € ja valem lõpetab arvutamise seal. See võib tekitada uskumatult suuri probleeme, sest paljudes olukordades ei märgata seda tüüpi vigu enne, kui need on avaldanud negatiivset mõju. Mida saaksite siis teha, võttes arvesse, et keerukate IF-lausete puhul on probleemide ilmnemise oht väga suur? Enamikul juhtudel saate keeruka IF-funktsioonidega valemi koostamise asemel kasutada funktsiooni VLOOKUP. Funktsiooni VLOOKUP kasutamisel peate esmalt looma viitetabeli.
-
=VLOOKUP(C2;C5:D17;2;TRUE)
Selle valemi süntaksi kohaselt tuleb lahtris C2 olevat väärtust otsida vahemikust C5:C17. Kui see väärtus leitakse, tagastatakse sellele vastav väärtus sama rea veerust D.
-
=VLOOKUP(B9;B2:C6;2;TRUE)
Samamoodi otsib see valem lahtris B9 olevat väärtust vahemikust B2:B22. Kui see väärtus leitakse, tagastatakse sellele vastav väärtus sama rea veerust C.
: Mõlemad VLOOKUP-funktsioonid kasutavad valemi lõpus argumenti TRUE, mis tähendab, et soovime otsida ligikaudset vastet. Teisisõnu leiab valem otsingutabelist nii täpsed väärtused kui ka kõik vahepealsed väärtused. Praegusel juhul peavad otsingutabelid olema sorditud tõusvas järjestuses, (väikseimast suurimani).
Funktsiooni VLOOKUP on siin käsitletud palju üksikasjalikumalt, kuid see on kindlasti palju lihtsam kui 12-tasemeline keerukas pesastatud IF-lause! Sellel funktsioonil on ka muid, vähem ilmseid eeliseid.
-
VLOOKUP-i viitetabelid on teil silme ees ja seega on neist hea ülevaadet saada.
-
Tabelis olevaid väärtusi saab hõlpsalt värskendada ja tingimuste muutmise korral ei pea te kunagi muutma valemit.
-
Kui te ei soovi, et teised inimesed viitetabelit näeksid või seal muudatusi teeksid, paigutage see lihtsalt teisele töölehele.
Kas teadsite?
Nüüd on saadaval IFS-funktsioon, mille abil saab mitu pesastatud IF-lauset asendada ühe funktsiooniga. Seega saab meie esialgse, nelja pesastatud IF-funktsiooniga hinnetenäite:
-
=IF(D2>89;"A";IF(D2>79;"B";IF(D2>69;"C";IF(D2>59;"D";"F"))))
IFS-funktsiooni kasutades palju lihtsamaks muuta:
-
=IFS(D2>89;"A";D2>79;"B";D2>69;"C";D2>59;"D";TRUE;"F")
IFS-funktsiooni on väga hea kasutada seetõttu, et te ei pea muretsema kõigi eraldi IF-lausete ja sulgude pärast.
: See funktsioon on saadaval ainult juhul, kui teil on Microsoft 365 tellimus. Kui olete Microsoft 365 tellija, veenduge, et teil oleks Office’i uusim versioon.Microsoft 365 ostmine või proovimine
Kas vajate rohkem abi?
Kui teil on küsimusi, saate need esitada Exceli tehnikakogukonnafoorumis, kus teile vastavad asjatundjad, või teistele kasutajatele kogukonnafoorumis.
Seotud teemad
Video: täpsemad IF-funktsioonid FUNKTSIOON IFS (Microsoft 365, Excel 2016 ja uuemad versioonid) Funktsioon COUNTIF loendab väärtused ühe kriteeriumi aluselFunktsioon COUNTIFS loendab väärtused mitme kriteeriumi aluselFunktsioon SUMIF summeerib väärtused ühe kriteeriumi aluselFunktsioon SUMIFS liidab väärtused mitme kriteeriumi aluselJA funktsioonOR funktsioonVLOOKUP Exceli valemite ülevaade Vigastevalemite vältimineValemite vigade tuvastamineLoogikafunktsioonidExceli funktsioonid (tähestikuliselt)Exceli funktsioonid (kategooriate kaupa)
