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)))))

Näetkö, mikä on vialla? Vertaa Tuotto-vertailujen järjestystä edelliseen esimerkkiin. Miten tämä tapahtuu? Aivan, se on alhaalta ylöspäin (5 000–15 000 $), ei toisin päin. Mutta miksi sen pitäisi olla niin iso juttu? Tämä on iso asia, koska kaava ei voi välittää ensimmäistä arviointia millään arvolla, joka on yli 5 000 euroa. Oletetaan, että sinulla on 12 500 $ tuloja – JOS-lauseke palauttaa 10 %, koska se on suurempi kuin 5 000 $ ja se pysähtyy siihen. Tämä voi olla erittäin ongelmallista, koska monet näistä virhetyypeistä eivät ole huomanneet, ennen kuin niillä on ollut negatiivinen vaikutus. Mitä voit tehdä, jos tiedät, että monimutkaisissa sisäkkäisissä JOS-lausekkeissa on vakavia ongelmia? Useimmissa tapauksissa PHAKU-funktiota voi käyttää monimutkaisen kaavan sijasta JOS-funktion kanssa. PHAKU-funktionavulla sinun on ensin luotava viitetaulukko:

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 tässä paljon tarkemmin,mutta tämä on varmasti paljon yksinkertaisempi kuin 12-tason monimutkainen sisäkkäinen 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 Microsoft 365 -tilaaja. Jos olet Microsoft 365 -tilaaja, varmista, että käytössäsi on Officen uusin versio.

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.S-funktio (Microsoft 365, Excel 2016 ja uudemmat)
LASKE.JOS-funktio laskee arvot yhden ehtojen perusteella.
LASKE.JOS.JOS.MÄÄRÄ-funktio laskee arvot useiden ehtojen perusteella
SUMMA.JOS-funktio summaa arvot yhden ehtojen perusteella
SUMMA.JOS.JOS.MÄÄRÄ-funktio summaa arvot useiden ehtojen perusteella
JA-funktio
TAI-funktio
PHAKU-funktio
Yleistä Excelin kaavoista
Katkenneiden kaavojen vältteleminen
Kaavoissa havaittujen virheiden etsiminen
Loogiset funktiot
Excelin funktiot (aakkosjärjestyksessä)
Excel-funktiot (luokittain)

Tarvitsetko lisäohjeita?

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

Oliko näistä tiedoista hyötyä?

×