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äistoiminnolla" tarkoitetaan useiden funktioiden yhdistämistä yhteen 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 Excel sallii sisäkkäin enintään 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 sisäkkäin käytä kaavaa 100%, se voi toimia 75 % ajasta, mutta palauttaa odottamattomia tuloksia 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 sinulla on JOS-lausunto, joka näyttää vain kasvavan ilman loppua, on aika laskea hiiri alas ja miettiä strategiaasi uudelleen.
Katsotaan, miten voit luoda monimutkaisen sisäkkäisen JOS-lausekkeen oikein useiden PDF-tiedostojen avulla ja milloin tunnistaa, että on aika käyttää toista työkalua Excelin asevarastossa.
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ämä esimerkki on suhteellisen turvallinen, koska ei ole todennäköistä, että testitulosten ja kirjainarvosanojen välinen korrelaatio muuttuu, joten se ei vaadi paljon ylläpitoa. Mutta tässä on ajatus – entä jos haluat segmentoida arvosanat 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 edelleen toiminnallisesti tarkka ja toimii odotetulla tavalla, mutta sen kirjoittaminen kestää kauan ja testi kestää kauemmin sen varmistamiseksi, että se tekee mitä haluat. Toinen räikeä ongelma on se, että olet joutunut antamaan pisteet ja vastaavat kirjainarvosanat käsin. Millä todennäköisyydellä sinulla on vahingossa kirjoitusvirhe? Kuvittele sitten, että yrität tehdä tämän 64 kertaa käyttäen vielä monimutkaisempia ehtoja. Toki, se on mahdollista, mutta haluatko todella alistaa itsesi tällaiselle vaivalle ja todennäköisille virheille, joita on todella vaikea havaita?
Vihje: Jokainen kaava Excelissä vaatii aloittavan ja lopettavan sulkeen (). Excel yrittää auttaa sinua selvittämään, mitä tapahtuu, värittämällä kaavan eri osia muokatessasi sitä. Jos esimerkiksi muokkaat yllä olevaa kaavaa, kun siirrät kohdistimen kunkin lopetussulkeen ")" ohi, sen vastaava alkusulje muuttuu samalla värillä. Tämä voi olla erityisen hyödyllistä monimutkaisissa sisäkkäisinä kaavoissa, kun yrität selvittää, onko sinulla tarpeeksi vastaavia 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 se muistuttaa huomattavasti aiempaa Arvosanat-esimerkkiä, tämä kaava on hyvä esimerkki siitä, miten vaikeaa suurten JOS-lausekkeiden ylläpitäminen voi olla – mitä sinun pitäisi tehdä, jos organisaatiosi päättäisi lisätä uusia korvaustasoja ja mahdollisesti jopa muuttaa olemassa olevia dollari- tai prosenttiarvoja? Sinulla olisi paljon töitä!
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 käyttäjien näkevän viitetaulukkoa tai häiritsevän sitä, sijoita se 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")
IFS-funktio on erinomainen, koska sinun ei tarvitse huolehtia kaikista näistä JOS-lauseista 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
IFS-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 arvot yhteen yhden ehdon perusteellaSUMMA.JOS.JOUKKO-funktio laskee yhteen arvot useiden ehtojen perusteellaJA-funktioTAI-funktioPHAKU-funktioExcelin kaavojen yleiskatsausRikkoutuneiden kaavojen välttäminenKaavoissa esiintyvien virheiden tunnistaminenLoogiset funktiotExcel-funktiot (aakkosjärjestyksessä)Excel-funktiot (luokittain)
