Excel-ohjeet
IF: Anna Excelin tehdä päätökset puolestasi
Läpi vai hylätty? Ilmainen toimitus vai ei? Myöhässä vai ajoissa? IF-funktion avulla Excel osaa vastata kyllä-tai-ei-kysymyksiin ja näyttää oikean tuloksen automaattisesti.
Kirjoitatko usein samaa sanaa yhä uudelleen ja uudelleen?
Kuvittele, että sinulla on luokan koetulokset. Kaikki, joilla on vähintään 50 pistettä, pääsevät läpi, muut hylätään. Voisit lukea jokaisen tuloksen ja kirjoittaa viereen "Läpi" tai "Hylätty" käsin. Neljän oppilaan kanssa se sujuu. Neljänsadan kanssa se on pitkä iltapäivä, ja jos tulos muuttuu, sinun pitää muistaa korjata myös sana.
IF-funktio tekee tämän puolestasi. Kerrot Excelille säännön kerran, ja se soveltaa sitä jokaiseen riviin.
Teet jo päivittäin tällaisia päätöksiä: "Jos sataa, otan sateenvarjon mukaan. Jos ei, jätän sen kotiin." IF on juuri sitä, kirjoitettuna Excelille.
Onko IF:stä hyötyä minulle?
Todennäköisesti, jos haluat joskus solun sanovan eri asioita toisen solun luvun tai sanan mukaan:
- Merkitse koetulokset läpäistyiksi tai hylätyiksi
- Näytä "Myöhässä", kun maksu on myöhässä
- Anna bonus vain, kun myynti ylittää tavoitteen
- Näytä "Varasto vähissä", kun määrä käy pieneksi
Jos huomaat katsovasi saraketta ja kirjoittavasi nimilappua käsin, IF voi todennäköisesti tehdä sen puolestasi.
Katso, miten se toimii
Tässä on neljä oppilasta. Sarakkeessa A on nimi ja sarakkeessa B pisteet. Sarakkeeseen C haluamme Excelin kirjoittavan Läpi, kun pisteitä on vähintään 50, ja muuten Hylätty.
| A | B | C | |
|---|---|---|---|
| 1 | Oppilas | Pisteet | Tulos |
| 2 | Anna | 72 | Läpi |
| 3 | Ben | 45 | Hylätty |
| 4 | Cara | 50 | Läpi |
| 5 | Dan | 38 | Hylätty |
Kirjoitamme yhden kaavan soluun C2 ja kopioimme sen alaspäin. Jokaisella rivillä Excel katsoo pisteitä (sininen), kysyy "onko niitä vähintään 50?" ja kirjoittaa Läpi (vihreä) tai Hylätty (oranssi).
Tarkistettava solu Vastaus on kyllä Vastaus on ei
Huomaa, että Cara sai täsmälleen 50 ja pääsi läpi. Se johtuu siitä, että kysymyksemme oli "50 tai enemmän", ei "enemmän kuin 50". Tällaisilla pienillä yksityiskohdilla on väliä, ja katsomme niitä tarkemmin alla.
Kaava kolmessa yksinkertaisessa osassa
Tässä on kaava, joka teki työn:
=JOS( B2>=50; "Läpi"; "Hylätty" )
Se on kysymys, jota seuraa kaksi mahdollista vastausta:
Mikä on kysymys?
B2>=50 tarkoittaa "onko solun B2 pistemäärä suurempi tai yhtä suuri kuin 50?" Tätä kutsutaan testiksi. Se voi olla vain tosi tai epätosi.
Mitä jos vastaus on kyllä?
"Läpi" näytetään, kun testi on tosi.
Mitä jos vastaus on ei?
"Hylätty" näytetään, kun testi on epätosi.
Muuta Benin pisteiksi 60, niin hänen tuloksensa muuttuu heti arvoon "Läpi". Sinun ei tarvitse koskea kaavaan enää koskaan.
Vertailumerkit
IF-funktion kysymys on yleensä vertailu. Näitä merkkejä voit käyttää:
| Merkki | Tarkoittaa | Esimerkki |
|---|---|---|
= |
On yhtä suuri kuin | A2="Maksettu" |
<> |
Ei ole yhtä suuri kuin | A2<>"Maksettu" |
> |
On suurempi kuin | B2>100 |
>= |
On suurempi tai yhtä suuri kuin | B2>=50 |
< |
On pienempi kuin | B2<10 |
<= |
On pienempi tai yhtä suuri kuin | B2<=10 |
Puolipiste vai pilkku? Se riippuu asuinpaikastasi
Jos internetistä kopioitu kaava antaa virheen, vaikka se näyttää täydelliseltä, syynä on usein erotinmerkki.
| Tyyli | Erotinmerkki | Tyypilliset alueet | Esimerkki |
|---|---|---|---|
| Eurooppalainen | Puolipiste ; | Suomi, Ruotsi, Saksa, Ranska, Espanja | =JOS(B2>=50; "Läpi"; "Hylätty") |
| Amerikkalainen | Pilkku , | Yhdysvallat, Britannia, Kanada, Australia | =IF(B2>=50, "Pass", "Fail") |
Kaava tekee saman asian kummallakin tyylillä. Monissa Euroopan maissa pilkku on desimaalimerkki (2,5 eikä 2.5), joten Excel käyttää osien välillä puolipistettä. Jos saat virheen, vaihda jokainen pilkku puolipisteeksi tai päinvastoin. Huomaa myös, että internetin esimerkeissä on yleensä englanninkielinen funktion nimi IF, kun suomenkielinen Excel vaatii nimen JOS. Tämän artikkelin esimerkeissä käytetään puolipisteitä.
Näin käytät sitä itse
- Napsauta solua, johon haluat tuloksen.
- Kirjoita =JOS(
- Napsauta solua, jonka haluat tarkistaa, ja kirjoita vertailu, esimerkiksi >=50, ja sen perään puolipiste.
- Kirjoita, mitä näytetään, jos vastaus on kyllä, ja lainausmerkeissä, jos se on sana, sitten puolipiste.
- Kirjoita, mitä näytetään, jos vastaus on ei, kirjoita lopetussulku ) ja paina Enter.
- Täytä loput rivit kaksoisnapsauttamalla solun oikean alakulman pientä neliötä.
=JOS(B2>=1000; B2*0,1; 0) antaa 10 %
bonuksen, kun myynti on vähintään 1000, ja muuten 0.
Useampi kuin kaksi lopputulosta
Entä jos haluat kolme tulosta, esimerkiksi Hyvä, Läpi ja Hylätty? Voit laittaa toisen JOS-funktion ensimmäisen "ei"-osaan:
=JOS(B2>=70; "Hyvä"; JOS(B2>=50; "Läpi"; "Hylätty"))
Excel tarkistaa ensimmäisen kysymyksen. Jos pisteitä on vähintään 70, se kirjoittaa "Hyvä". Jos ei, se siirtyy toiseen kysymykseen. Tätä kutsutaan sisäkkäiseksi JOS-funktioksi (nested IF). Se toimii hyvin kahdella tai kolmella tasolla. Sen jälkeen siitä tulee vaikealukuinen, joten älä mene sitä syvemmälle.
IF ja OR: Kun jompikumpi syy riittää
Tähän asti kysymyksessämme oli vain yksi osa. Mutta todellinen elämä on usein monimutkaisempaa: "Tarjoamme ilmaisen toimituksen, jos tilaus on yli 100 tai asiakas on jäsen." Yksi syy riittää.
Jos haluat esittää kysymyksen, jossa on useita osia, voit sijoittaa OR-funktion (TAI) IF-funktion sisään.
Esimerkki
Tässä on neljä tilausta. Sarakkeessa B on tilauksen summa ja sarakkeessa C kerrotaan, onko asiakas jäsen. Sarakkeen D pitää sanoa Ilmainen, jos summa on vähintään 100 tai asiakas on jäsen. Muuten sen pitää sanoa Maksullinen.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Tilaus | Summa | Jäsen | Toimitus |
| 2 | 1001 | 120 | Ei | Ilmainen |
| 3 | 1002 | 60 | Kyllä | Ilmainen |
| 4 | 1003 | 40 | Ei | Maksullinen |
| 5 | 1004 | 150 | Kyllä | Ilmainen |
Vihreät solut sarakkeissa B ja C ovat ehtoja, jotka ovat tosia. Tilauksella 1001 on vain yksi (summa), tilauksella 1002 vain yksi (jäsenyys) ja tilauksella 1004 molemmat. Kaikki kolme saavat ilmaisen toimituksen, koska yksi riittää. Tilauksella 1003 ei ole kumpaakaan, joten asiakas maksaa.
Tässä on kaava solussa D2:
=JOS( TAI(B2>=100; C2="Kyllä"); "Ilmainen"; "Maksullinen" )
Rakenne on täsmälleen sama kuin aiemmin: kysymys, sitten vastaus kyllä-tapaukseen ja sitten vastaus ei-tapaukseen. Ainoa uusi asia on, että kysymys (sininen osa) on nyt TAI(...), jossa on kaksi puolipisteellä erotettua ehtoa.
Lue se ääneen
Hyvä tapa tarkistaa kaava on lukea se lauseena: "Jos summa on vähintään 100 tai jäsensolussa lukee Kyllä, näytä Ilmainen. Muuten näytä Maksullinen."
TAI-funktiossa voi olla enemmän kuin kaksi ehtoa
Lisää vain ehtoja sulkujen sisään:
=JOS(TAI(B2>=100; C2="Kyllä"; D2="Lahjakortti"); "Ilmainen"; "Maksullinen")
Jos mikä tahansa kolmesta on tosi, tulos on "Ilmainen".
OR ja AND
Sen sisarfunktio on AND (JA), joka on tosi vain, kun kaikki ehdot ovat tosia. Käytät sitä samassa paikassa, IF-funktion sisällä.
| OR (TAI) | AND (JA) | |
|---|---|---|
| Tosi, kun | Vähintään yksi ehto on tosi | Kaikki ehdot ovat tosia |
| Arkipäivän esimerkki | "Ota mukaan henkilökortti tai passi" | "Ota mukaan henkilökortti ja lippu" |
| Kaava | TAI(B2>=100; C2="Kyllä") |
JA(B2>=100; C2="Kyllä") |
| Tilaus 1002 (60, jäsen) | Ilmainen | Maksullinen |
JA-funktiolla vain tilaus 1004 (150 ja jäsen) saisi ilmaisen toimituksen. TAI:n ja JA:n välillä valitseminen on vain kysymys: "Riittääkö yksi syy, vai tarvitsenko kaikki?"
Näin se näkyy internetin esimerkeissä
Useimmat internetin esimerkit on kirjoitettu englanninkielisillä funktioiden nimillä ja pilkuilla. Sama kaava näyttää silloin tältä:
=IF(OR(B2>=100, C2="Yes"), "Free", "Pay")
Käännä funktioiden nimet (IF on JOS, OR on TAI) ja vaihda pilkut puolipisteiksi, niin kaava toimii suomenkielisessä Excelissä.
Yleisiä ongelmia ja niiden ratkaisut
Näet #NIMI?
Useimmiten sanalta puuttuvat lainausmerkit. Jos kirjoitat
=JOS(B2>=50; Läpi; Hylätty), Excel etsii
jotakin, jonka nimi on Läpi. Kirjoita "Läpi" ja
"Hylätty". Syynä voi olla myös se, että olet
kirjoittanut englanninkielisen nimen IF suomenkieliseen
Exceliin.
Excel sanoo, että kaavassa on ongelma
Tarkista erotinmerkit. Jos Excelisi käyttää eurooppalaisia asetuksia, käytä puolipisteitä (;) pilkkujen (,) sijaan, kuten yllä selitettiin. Laske myös sulut: jokainen alkusulku tarvitsee loppusulun. Tämä on helppo tehdä väärin TAI-funktion ja sisäkkäisten JOS-funktioiden kanssa.
Tulos on EPÄTOSI tai 0 sanan sijaan
Jos jätät "ei"-vastauksen pois, Excel näyttää EPÄTOSI, kun
testi ei toteudu. Jos haluat solun näyttävän tyhjältä,
kirjoita kaksi lainausmerkkiä ilman mitään välissä:
=JOS(B2>=50; "Läpi"; "").
Väärät rivit läpäisevät tai hylätään
Tarkista raja-arvo. "Suurempi kuin" (>) ja "suurempi tai yhtä suuri kuin" (>=) antavat eri tuloksen täsmälleen 50 pisteelle. Sano sääntö ääneen ja valitse merkki, joka vastaa sitä.
Lukua ei tunnisteta
Joskus luku on tallennettu tekstinä, jolloin se näyttää
luvulta, mutta Excel ei vertaa sitä oikein. Jos solussa on
pieni vihreä kolmio tai luku on tasattu vasemmalle, muunna
se oikeaksi luvuksi. Vältä myös lainausmerkkejä lukujen
ympärillä testissä: B2>="50" vertaa tekstiin,
ei lukuun 50.
TAI ei anna virhettä mutta väärän vastauksen
Varmista, että TAI on JOS-funktion sisällä
ensimmäisenä osana. Kaava kuten
=TAI(JOS(...); ...) tekee aivan muuta. Muista
myös, että tekstin pitää vastata solun sisältöä: "Kyllä" ei
täsmää arvoon "Kyllä " ylimääräisen välilyönnin kanssa.
Lyhyt versio
- IF esittää kyllä-tai-ei-kysymyksen ja näyttää yhden tuloksen kyllä-vastaukselle ja toisen ei-vastaukselle.
- Kaava on =JOS(kysymys; tulos jos kyllä; tulos jos ei).
- Sanat kirjoitetaan lainausmerkkeihin, luvut ja solut eivät.
- Laita toinen JOS "ei"-osaan, kun tarvitset kolme lopputulosta.
- Käytä TAI-funktiota JOS-funktion sisällä, kun yksi syy riittää, ja JA-funktiota, kun tarvitset kaikki.
- Käytä puolipisteitä eurooppalaisessa Excelissä ja pilkkuja amerikkalaisessa Excelissä.