JOS-funktio – sisäkkäiset kaavat ja ongelmien välttäminen

JOS-funktio – sisäkkäiset kaavat ja ongelmien välttäminen

JOS-funktion avulla voit tehdä loogisen vertailun arvon ja odotetun arvon välillä testaamalla ehtoa ja palauttamalla tuloksen, jos lauseke on Tosi tai Epätosi.

  • JOS(jokin on Tosi, tee jotain, muutoin tee jotain muuta)

JOS-lausekkeella voi näin olla kaksi tulosta. Ensimmäinen tulos on se, että vertailu on Tosi, ja toinen, että vertailu on Epätosi.

JOS-lausekkeet ovat erittäin monipuolisia ja muodostavat perustan monille laskentataulukkomalleille, mutta ne ovat myös monien laskentataulukko-ongelmien perimmäinen syy. Ihannetapauksessa JOS-lauseketta tulisi käyttää vain yksinkertaisissa ehdoissa, kuten Mies/Nainen, Kyllä/Ei/Ehkä, mutta joskus on arvioitava monimutkaisempia skenaarioita, jotka vaativat yli kolmen JOS-funktion asettamista yhteen sisäkkäin*.

* "Sisäkkäin asettaminen" tarkoittaa useiden funktioiden yhdistämistä samaan kaavaan.

JOS-funktiolla, joka on looginen funktio, voit palauttaa yhden arvon, jos ehto on tosi, ja toisen arvon, jos ehto on epätosi.

Syntaksi

JOS(looginen_testi, arvo_jos_tosi, [arvo_jos_epätosi])

Esimerkki:

  • =JOS(A2>B2;"Ylittää budjetin";"OK")

  • =JOS(A2=B2;B4-A4;"")

Argumentin nimi

Kuvaus

looginen_testi   

(pakollinen)

Testattava ehto.

arvo_jos_tosi   

(pakollinen)

Palautettava arvo, jos loogisen_testin tulos on TOSI.

arvo_jos_epätosi   

(valinnainen)

Palautettava arvo, jos loogisen_testin tulos on EPÄTOSI.

Huomautuksia

Vaikka Excelissä voidaankin asettaa sisäkkäin jopa 64 eri JOS-funktiota, se ei ole lainkaan suositeltavaa. Miksi?

  • Useiden JOS-lausekkeiden muodostaminen oikein edellyttää paljon harkintaa, jotta niiden logiikka voi laskea oikein jokaisen ehdon alusta loppuun. Jos et sijoita kaavan osia sisäkkäin 100-prosenttisen täsmällisesti, se voi toimia oikein 75 % ajasta, mutta antaa odottamattomia arvoja 25 % ajasta. On epätodennäköistä, että tunnistaisit kyseiset 25 %.

  • Usean JOS-lausekkeen ylläpidosta voi tulla uskomattoman vaikeaa, etenkin, kun myöhemmin yrität selvittää, mitä sinä itse olet tai joku muu on yrittänyt tehdä.

Jos huomaat, että JOS-lausekkeesi pitenee loputtomasti, on aika miettiä strategiaa uudelleen.

Katsotaanpa, miten monimutkainen sisäkkäisiä osia sisältävä JOS-lauseke luodaan oikein useiden JOS-funktioiden avulla, ja milloin on syytä käyttää jotain toista Excelin työkalua.

Esimerkkejä

Seuraavassa on esimerkki melko tavallisesta sisäkkäisiä osia sisältävästä JOS-lausekkeesta, jolla opiskelijoiden koepisteet muunnetaan vastaaviksi kirjainarvosanoiksi.

Monimutkainen sisäkkäinen JOS-lauseke – kaava solussa E2 on =JOS(B2>97;"A+";JOS(B2>93;"A";JOS(B2>89;"A-";JOS(B2>87;"B+";JOS(B2>83;"B";JOS(B2>79;"B-";JOS(B2>77;"C+";JOS(B2>73;"C";JOS(B2>69;"C-";JOS(B2>57;"D+";JOS(B2>53;"D";JOS(B2>49;"D-";"F"))))))))))))
  • =JOS(D2>89;"A";JOS(D2>79;"B";JOS(D2>69;"C";JOS(D2>59;"D";"F"))))

    Tämä monimutkainen sisäkkäisiä osia sisältävä JOS-lauseke noudattaa yksinkertaista logiikkaa:

  1. Jos Koepisteet-arvo (solussa D2) on suurempi kuin 89, opiskelija saa arvosanan A

  2. Jos Koepisteet-arvo on suurempi kuin 79, opiskelija saa arvosanan B

  3. Jos Koepisteet-arvo on suurempi kuin 69, opiskelija saa arvosanan C

  4. Jos Koepisteet-arvo on suurempi kuin 59, opiskelija saa arvosanan D

  5. Muutoin opiskelija saa arvosanan F

Tässä esimerkissä oleva tilanne on melko turvallinen, koska koepisteiden ja kirjainarvosanojen välinen vastaavuus ei todennäköisesti muutu, joten kaava ei juuri tarvitse ylläpitoa. Mutta entä, jos sinun on jaettava arvosanat osiin A+, A ja A- (ja niin edelleen)? Nyt aiemmin neljä ehtoa sisältäneisiin JOS-lausekkeisiin onkin kirjoitettava 12 ehtoa. Kaava näyttäisi nyt tältä:

  • =JOS(B2>97;"A+";JOS(B2>93;"A";JOS(B2>89;"A-";JOS(B2>87;"B+";JOS(B2>83;"B";JOS(B2>79;"B-"; JOS(B2>77;"C+";JOS(B2>73;"C";JOS(B2>69;"C-";JOS(B2>57;"D+";JOS(B2>53;"D";JOS(B2>49;"D-";"F"))))))))))))

Se on yhä toiminnallisesti tarkka ja toimii odotetulla tavalla, mutta sen kirjoittaminen ja halutun toiminnan varmistaminen testaamalla vie aikaa. Toisen selvä ongelma on ollut, että pisteet ja vastaavat kirjainarvosanat on pitänyt kirjoittaa käsin. Miten todennäköistä on, että teet kirjoitusvirheen? Kuvittele sitten, että yrität tehdä tämän 64 kertaa käyttäen vielä monimutkaisempia ehtoja. Se on varmasti on mahdollista, mutta haluatko varmasti nähdä tällaista vaivaa ja tehdä todennäköiset virheet, jotka on todella vaikea löytää?

Vihje: Jokainen kaava Excelissä vaatii aloittavan ja lopettavan sulkeen (). Excel yrittää auttaa sinua sijoittamaan kaavan osat oikeille paikoilleen värittämällä kaavan eri osat muokatessasi niitä. Jos esimerkiksi muokkaisit edellä olevaa kaavaa ja siirtäisit kohdistinta jokaisen lopettavan sulkeen ")" ohi, sitä vastaava aloittava sulje muuttuisi samanväriseksi. Tämä voi olla erityisen hyödyllistä monimutkaisissa sisäkkäisiä osia sisältävissä kaavoissa, kun yrität varmistaa, että kaavassa on tarpeeksi toisiinsa liittyviä sulkeita.

Lisää esimerkkejä

Seuraavassa on hyvin yleinen esimerkki myyntipalkkion laskemisesta tuottosaavutustasojen perusteella.

Kaava solussa D9 on JOS(C9>15000;20%;JOS(C9>12500;17,5%,JOS(C9>10000;15%,JOS(C9>7500;12,5%,JOS(C9>5000;10%;0)))))
  • =JOS(C9>15000;20%;JOS(C9>12500;17,5%;JOS(C9>10000;15%;JOS(C9>7500;12,5%,JOS(C9>5000;10%;0)))))

Tämä kaava kertoo, että JOS(C9 on suurempi kuin 15 000, palauta 20 %; JOS(C9 on suurempi kuin 12 500, palauta sitten 17,5 % jne.

Vaikka esimerkki on hyvin samanlainen kuin aiempi arvosanaesimerkki, tämä kaava on myös hyvä esimerkki siitä, miten vaikeaa pitkien JOS-lausekkeiden ylläpito voi olla – mitä sinun olisi esimerkiksi tehtävä, jos organisaatiosi päättäisi lisätä uusia hyvitystasoja ja ehkä jopa muuttaa olemassa olevia rahasumma- tai prosenttiarvoja? Työtä olisi paljon.

Vihje: Voit helpottaa pitkien kaavojen lukemista lisäämällä kaavarivillä rivinvaihtoja. Paina ALT+ENTER ennen tekstiä, jonka haluat siirtää uudelleen riville.

Tässä on esimerkki myyntipalkkioskenaariosta, jossa logiikka on väärässä järjestyksessä:

Kaava solussa D9 on väärässä järjestyksessä, eli JOS(C9>5000;10%;JOS(C9>7500;12,5%,JOS(C9>10000;15%,JOS(C9>12500;17,5%,JOS(C9>15000;20%;0)))))

Huomaatko, mikä on väärin? Vertaile järjestystä tuottovertailussa edellisessä esimerkissä. Mikä järjestys tässä on? Aivan oikein, järjestys on alhaalta ylös (5 000 dollarista 15 000 dollariin), ei toisin päin. Mutta miksi tämä on tärkeää? Tämä on tärkeä, koska kaava ei voi välittää minkään yli 5 000 dollaria suuremman arvon ensimmäistä arvioita. Oletetaan, että tuottosi on 12 500 dollaria. Tällöin JOS-lauseke palauttaa arvon 10 %, koska se on suurempi kuin 5 000 dollaria euroa, ja pysähtyy sitten. Tämä voi olla erittäin ongelmallista, koska monissa tilanteissa tämäntyyppiset virheet jäävät huomaamatta, kunnes ne ovat jo vaikuttaneet negatiivisesti. Tiedämme siis, että monimutkaisissa sisäkkäisiä osia sisältävissä JOS-lausekkeissa on vakavia ongelmia. Mitä voimme tehdä? Useimmissa tilanteissa voit käyttää PHAKU-funktiota sen sijaan, että muodostaisit monimutkaisen kaavan JOS-funktion avulla. PHAKU-funktiota käyttäessäsi sinun on ensin luotava viitetaulu:

Kaava solussa D2 on =PHAKU(C2;C5:D17;2;TOSI)
  • =PHAKU(C2;C5:D17;2;TOSI)

Tämä kaava ohjaa etsimään solun C2 arvoa alueelta C5:C17. Jos arvo löytyy, kaava palauttaa vastaavan arvon samalta riviltä sarakkeesta D.

Kaava solussa C9 on =PHAKU(B9;B2:C6;2;TOSI)
  • =PHAKU(B9;B2:C6;2;TOSI)

Tämä kaava etsii vastaavasti solussa B9 olevaa arvoa alueelta B2:B22. Jos arvo löytyy, kaava palauttaa vastaavan arvon samalta riviltä sarakkeesta C.

Huomautus: Molempia näistä PHAKU-haut käyttävät TOSI-argumenttia kaavan lopussa, mikä tarkoittaa, että haluamme niiden etsivän summittaista vastinetta. Kaava toisin sanoen löytää täsmälliset hakutaulukon arvot sekä kaikki niiden välillä olevat arvot. Tässä tapauksessa hakutaulukot on lajiteltava nousevaan järjestykseen pienimmästä suurimpaan.

PHAKU-funktiota käsitellään tarkemmin täällä, mutta se on joka tapauksessa paljon yksinkertaisempi kuin 12-tasoinen monimutkainen ja sisäkkäisiä osia sisältävä JOS-lauseke. Sillä on myös muita etuja, jotka eivät ole yhtä itsestäänselviä:

  • PHAKU-viitetaulut ovat helposti nähtävillä.

  • Taulun arvot voi helposti päivittää, eikä kaavaa tarvitse koskaan muuttaa ehtojen muuttuessa.

  • Jos et halua ihmisten näkevän tai muuttavan viitetaulua, voit siirtää sen toiseen laskentataulukkoon.

Tiesitkö?

Nyt käytettävissä on JOS.JOUKKO-funktio, jolla voi korvata useita sisäkkäisiä JOS-lausekkeita. Tarkastellaan ensimmäistä arvosanaesimerkkiämme, jossa on neljä sisäkkäistä JOS-funktiota:

  • =JOS(D2>89;"A";JOS(D2>79;"B";JOS(D2>69;"C";JOS(D2>59;"D";"F"))))

Sitä voidaan yksinkertaistaa selvästi yhdellä JOS.JOUKKO-funktiolla:

  • =JOS.JOUKKO(D2>89;"A";D2>79;"B";D2>69;"C";D2>59;"D";TOSI;"F")

JOS.JOUKKO-funktio on kätevä, koska sinun ei tarvitse huolehtia kaikista JOS-lausekkeista ja sulkeista.

Huomautus: Tämä toiminto on käytettävissä ainoastaan, jos olet Office 365 -tilaaja. Jos olet Office 365 -tilaaja, varmista, että käytössäsi on Officen uusin versio.

Kokeile Office 365:tä tai Excelin uusinta versiota

Tarvitsetko lisätietoja?

Voit aina kysyä neuvoa Excel Tech Community -yhteisön asiantuntijalta, saada tukea yhteisön vastauksista tai ehdottaa uutta ominaisuutta tai parannusta Excel User Voice -sivustolla.

Aiheeseen liittyvät artikkelit

Video: Edistyneet JOS-funktiot
JOS.JOUKKO-funktio (Office 365, Excel 2016 ja uudemmat versiot)
LASKE.JOS-funktio laskee arvoja yhden ehdon perusteella
LASKE.JOS.JOUKKO-funktio laskee arvoja usean ehdon perusteella
SUMMA.JOS-funktio laskee arvot yhteen yhden ehdon perusteella
SUMMA.JOS.JOUKKO-funktio laskee arvot yhteen usean ehdon perusteella
JA-funktio
TAI-funktio
PHAKU-funktio
Yleiskatsaus Excelin kaavoihin
Viallisten kaavojen välttäminen
Virheentarkistuksen käyttäminen kaavoissa
Loogiset funktiot
Excelin funktiot (aakkosjärjestyksessä)
Excelin funktiot (luokittain)

Kehitä taitojasi
Tutustu koulutusmateriaaliin
Saat uudet ominaisuudet ensimmäisten joukossa
Liity Office Insider -käyttäjiin

Oliko näistä tiedoista hyötyä?

Kiitos palautteesta!

Kiitos palautteestasi! Näyttää siltä, että Office-tukiedustajamme avusta voi olla sinulle hyötyä.

×