IF-funktsioon – pesastatud valemitega seotud probleemide vältimine

IF-funktsioon – pesastatud valemitega seotud probleemide vältimine

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.

Keerukas pesastatud IF-lause – lahtris E2 on valem =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"))))))))))))
  • =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.

  1. Kui testitulemus (lahtris D2) on üle 89 punkti, saab õppur hinde A.

  2. Kui testitulemus on üle 79 punkti, saab õppur hinde B.

  3. Kui testitulemus on üle 69 punkti, saab õppur hinde C.

  4. Kui testitulemus on üle 59 punkti, saab õppur hinde D.

  5. 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.

Näpunäide. : 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.

Lahtris D9 on valem 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)))))
  • 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 ...

Ühest küljest on see valem väga sarnane eelmise hinnete näitega, teisalt aga kujutab see ilmekalt, kui raske võib olla keerukate IF-lausete haldamine. Mida peaksite tegema juhul, kui teie ettevõte otsustab lisada hüvitustasemeid või muuta olemasolevaid rahalisi või protsendilisi väärtusi? Sel juhul oleks teil palju tööd!

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.

Lahtris D9 olevas valemis on tingimused vales järjestuses: =IF(C9>5000;10%;IF(C9>7500;12,5%;IF(C9>10 000;15%;IF(C9>12 500;17,5%;IF(C9>15 000;20%;0)))))

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.

Lahtris D2 on valem =VLOOKUP(C2;C5:D17;2;TRUE)
  • =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.

Lahtris C9 on valem =VLOOKUP(B9;B2:C6;2;TRUE)
  • =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 põhjalikumalt käsitletud siin, kuid kindlasti on see valem oluliselt lihtsam kui keerukas, 12-tasemeline 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.

Märkus. : See funktsioon on saadaval ainult juhul, kui teil on Office 365 tellimus. Kui teil on mõni Office 365 tellimus, veenduge, et teil oleks Office’i uusim versioon.

Proovige teenusekomplekti Office 365 või Exceli uusimat versiooni

Kas vajate rohkem abi?

Võite oma küsimuse alati esitada mõnele Exceli tehnikakogukonna eksperdile, otsida abi vastustefoorumist või soovitada mõnd uut funktsiooni või täiustust Exceli User Voice’i lehel.

Seotud teemad

Täpsemad IF-funktsioonid
Funktsioon IFS
COUNTIF
COUNTIFS
Funktsioon SUMIF
SUMIFS
Funktsioon AND
OR
VLOOKUP
Exceli valemite ülevaade
Katkenud valemite vältimine
Valemivigade tuvastamine
Loogikafunktsioonid (teatmematerjalid)
Exceli funktsioonid (tähestikuliselt)
Exceli funktsioonid (kategooriate kaupa)

Täiendage oma oskusi
Tutvuge koolitusmaterjalidega
Kasutage uusi funktsioone enne teisi
Liituge Office Insideri programmiga

Kas sellest teabest oli abi?

Täname tagasiside eest!

Täname tagasiside eest! Tundub, et võiksime teid kokku viia ühega meie Office'i tugiagentidest, kes aitab teil probleemi lahendada.

×