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 mitme funktsiooni ühendamise tavale ühes 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 seda soovitatav 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 ei pesasta oma valemit 100% õigesti, võib see toimida 75% juhtudel, kuid tagastada ootamatud tulemid 25% ajast. 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 IF-lausega, mis näib lihtsalt kasvavat lõputa, on aeg hiir maha panna ja strateegia läbi mõelda.
Vaatame, kuidas luua mitme IFs-i abil õigesti keerukat pesastatud IF-lauset ja millal tuvastada, 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.
See konkreetne näide on suhteliselt turvaline, kuna pole tõenäoline, et testitulemuste ja täheliste hinnete korrelatsioon muutub, nii et see ei nõua palju hooldust. Kuid mõte on järgmine: mida teha, kui teil on vaja hinded A+, A ja A- (jne) vahel jaotada? Nüüd tuleb 4 tingimusega IF-lause ümber kirjutada nii, et selles oleks 12 tingimust! Valem näeks nüüd välja selline:
-
=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 on siiski funktsionaalselt täpne ja töötab ootuspäraselt, kuid kirjutamine võtab palju aega ja testimiseks kulub rohkem aega, et veenduda, kas see teeb seda, mida soovite. Teine pilku võtv probleem on see, et peate hinded ja võrdväärsed tähelise hinnete käsitsi sisestama. Millised on soovimatud trükivead? Kujutage nüüd ette, et veelgi keerukamate tingimuste korral peaksite andmeid käsitsi sisestama 64 korda! Muidugi, see on võimalik, kuid kas sa tõesti tahad ennast sellisele pingutusele ja võimalikele vigadele allutada, mida on väga raske märgata?
Näpunäide.: Igal Exceli funktsioonil peab olema algus- ja lõpusulg (). Excel püüab aidata teil välja selgitada, mis juhtub, kui värvite valemi redigeerimisel erinevaid osi. Näiteks kui redigeeriksite ülaltoodud valemit, pööratakse kursori viimisel igast lõpusulust ")", muutub sellele vastav algussulg sama värviks. See võib olla eriti kasulik keerukate pesastatud valemite korral, kui proovite välja selgitada, kas teil on piisavalt vastavaid sulgusid.
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!
Näpunäide.: 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 näete, mis viga? Võrrelge tulusummade järjestust eelmise näite omaga. Kuidas on summad selles valemis esitatud? See on õige, see läheb alt üles (5000 kuni 15 000 $), mitte vastupidi. Aga miks peaks sellest nii suurt numbrit tegema? See on suur asi, kuna valem ei saa läbida esimest hindamist üle 5000 euro väärtuse kohta. Oletame, et teil on 12 500 $ tulu – IF-lause tagastab 10%, kuna see on suurem kui 5000 $ ja see peatub seal. See võib olla uskumatult problemaatiline, sest paljudel juhtudel jäävad seda tüüpi vead märkamatuks, kuni 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ärkus.: 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 inimesed viitetabelit näeksid või häiriksid, pange see lihtsalt mõnele muule 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")
Funktsioon IFS on väga hea, kuna te ei pea muretsema kõigi nende IF-lausete ja sulgude pärast.
Märkus.: 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
IFS funktsioon (Microsoft 365, Excel 2016 ja uuemad versioonid) Funktsioon COUNTIF loendab väärtused ühe kriteeriumi aluselFunktsioon COUNTIFS loendab väärtused mitme kriteeriumi aluselSUMIF funktsioon liidab väärtused ühe kriteeriumi aluselSUMIFS funktsioon liidib väärtused mitme kriteeriumi aluselAND funktsioonOR FunktsioonVLOOKUPExceli valemite ülevaadeVigaste valemite vältimineValemivigade tuvastamineLoogikafunktsioonidExceli funktsioonid (tähestikuliselt)Exceli funktsioonid (kategooriate kaupa)
