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.
-
=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:
-
Jos Koepisteet-arvo (solussa D2) on suurempi kuin 89, opiskelija saa arvosanan A
-
Jos Koepisteet-arvo on suurempi kuin 79, opiskelija saa arvosanan B
-
Jos Koepisteet-arvo on suurempi kuin 69, opiskelija saa arvosanan C
-
Jos Koepisteet-arvo on suurempi kuin 59, opiskelija saa arvosanan D
-
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.
-
=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ä:
Näetkö, mikä on vialla? Vertaa tuottovertailujen järjestystä edelliseen esimerkkiin. Mihin suuntaan tämä menee? Aivan, se menee alhaalta ylöspäin (5 000 dollarista 15 000 dollariin), ei päinvastoin. Mutta miksi se olisi niin iso juttu? Se on iso juttu, koska kaava ei voi läpäistä ensimmäistä arviota mistään yli 5 000 dollarin arvosta. Oletetaan, että sinulla on 12 500 dollarin tulot – JOS-tiliote palauttaa 10% koska se on suurempi kuin 5 000 dollaria, ja se pysähtyy siihen. Tämä voi olla uskomattoman ongelmallista, koska useimmissa tilanteissa tällaiset virheet jäävät huomaamatta, kunnes niillä on ollut kielteinen vaikutus. Joten tietäen, että on olemassa vakavia sudenkuoppia, joissa on monimutkaisia sisäkkäisia JOS-lausekkeita, mitä voit tehdä? Useimmissa tapauksissa voit käyttää PHAKU-funktiota sen sijaan, että rakentaisit monimutkaisen kaavan JOS-funktiolla. PHAKU-funktion avulla sinun on ensin luotava viitetaulukko:
-
=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.
-
=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 yksityiskohtaisemmin, mutta tämä on varmasti paljon yksinkertaisempaa 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 sinulla on Microsoft 365 -tilaus. Jos olet Microsoft 365 -tilaaja, varmista, että käytössäsi on Officen uusin versio.Microsoft 365:n ostaminen tai kokeileminen
Tarvitsetko lisätietoja?
Voit aina kysyä neuvoa Excel Tech Community -yhteisön asiantuntijalta tai saada tukea tukiyhteisöltä.
Aiheeseen liittyvät artikkelit
Video: JOS-lisäfunktiot JOS.JOUKKO-funktio (Microsoft 365, Excel 2016 ja uudemmat) LASKE.JOS-funktio laskee arvot yhden ehdon perusteellaLASKE.JOS.JOUKKO-funktio laskee arvot useiden ehtojen perusteellaSUMMA.JOS-funktio laskee yhteen arvoja yhden ehdon perusteellaSUMMA.JOS.JOUKKO-funktio laskee yhteen arvoja useiden ehtojenja funktion TAIPHAKU-funktion perusteella.Yleiskatsaus Excelin kaavoistaViallisten kaavojen välttäminenVirheiden havaitseminen kaavoissaLoogiset funktiotExcel-funktiot (aakkosjärjestyksessä)Excel-funktiot (luokittain)
