IF Formula Excel - Jos sitten lausumat

Lataa esimerkkityökirja

Lataa esimerkkityökirja

Tämä opetusohjelma osoittaa, miten Excel IF -toiminto Excelissä, jos haluat luoda lauseita Jos sitten.

IF -toiminnon yleiskatsaus

IF -toiminto tarkistaa, täyttyykö ehto. Jos TOSI, tee yksi asia, jos EPÄTOSI, tee toinen.

Jos haluat käyttää IF Excel -laskentataulukkotoimintoa, valitse solu ja kirjoita:

(Huomaa, miten kaavasyötteet näkyvät)

IF -toiminnon syntaksi ja tulot:

1	= JOS (looginen_testi, arvo_Jos_tosi, arvo_Jos_väärä)

looginen_testi - Looginen ilmaisu. Esimerkki: A1> 4.

value_if_true - Arvo tai laskenta suoritettavaksi, jos looginen lauseke on TOSI.

value_if_false - Arvo tai laskenta suoritettavaksi, jos looginen lauseke on EPÄTOSI.

IF on "ehdollinen" toiminto. Tämä tarkoittaa, että määrität loogisen testin ja se palauttaa yhden arvon, jos testi arvioidaan tosiksi, ja toisen arvon, jos se on epätosi

IF -toiminnon käyttäminen

Tässä on hyvin yksinkertainen esimerkki, jotta voit nähdä, mitä tarkoitan. Kokeile kirjoittaa seuraava Exceliin:

1	= JOS (2 + 2 = 4, "Se on totta", "Se on väärä!")

Koska 2 + 2 on itse asiassa 4, Excel palauttaa "Se on totta!". Jos käytimme tätä:

1	= JOS (2 + 2 = 5, "Se on totta", "Se on väärä!")

Nyt Excel palauttaa "Se on väärin!", Koska 2 + 2 ei ole 5.

Näin voit käyttää IF: tä laskentataulukossa.

1	= JOS (C4-D4> 0, C4-D4,0)

Käytät urheilubaaria ja asetat yksittäisille välilehdille rajat eri asiakkaille. Olet määrittänyt tämän laskentataulukon tarkistaaksesi, onko jokainen asiakas ylittänyt rajansa. Tässä tapauksessa keskeytät hänet, kunnes hän maksaa välilehden.

Tarkistat, onko C4-D4 (niiden nykyinen välilehden määrä miinus raja) suurempi kuin 0. Tämä on looginen testisi. Jos tämä on totta, JOS palauttaa "Kyllä" - sinun on katkaistava ne. Jos tämä on väärin, JOS palauttaa "Ei" - annat heidän jatkaa juomista.

Mitä IF voi palauttaa

Edellä palautimme tekstimerkkijonon, "Kyllä" tai "Ei". Mutta voit myös palauttaa numeroita tai jopa muita kaavoja.

Oletetaan, että jotkut asiakkaasi käyttävät suuria välilehtiä. Tämän estämiseksi alat veloittaa korkoa asiakkailta, jotka ylittävät rajansa.

Voit käyttää IF: tä tähän:

1	= JOS (C4> D4, C4*0,03,0)

Jos välilehti on korkeampi kuin raja, palauta välilehti kerrottuna 0,03: lla, mikä palauttaa 3% välilehdestä. Muussa tapauksessa palauta 0: ne eivät ole välilehden päällä, joten et veloita korkoa.

IF: n käyttäminen AND: n kanssa

Voit yhdistää IF: n Excelin AND -toimintoon <>. Käytät tätä loogisessa testissä, jonka avulla voit määrittää kaksi tai useampia ehtoja testattavaksi. Excel palauttaa arvon TOSI vain, jos KAIKKI testit ovat oikein.

Joten otit korkosi käyttöön. Mutta jotkut kanta -asiakkaistasi valittavat. He ovat aina maksaneet välilehdensä aiemmin, miksi pidät niistä kiinni nyt? Keksit ratkaisun: et veloita korkoa tietyiltä luotetuilta asiakkailta.

Luo laskentataulukkoosi uusi sarake luotettavien asiakkaiden tunnistamiseksi ja päivitä IF -lausunto AND -funktiolla:

1	= JOS (JA (C4> D4, F4 = "Ei"), C4*0,03,0)

Katsotaanpa AND -osaa erikseen:

1	JA (C4> D4, F4 = "Ei")

Huomaa kaksi ehtoa:

• C4> D4: Tarkista, ovatko ne ylittäneet välilehtien rajan, kuten ennenkin
• F4 = ”Ei”: tämä on uusi bitti, joka tarkistaa, eivätkö he ole luotettu asiakas

Joten nyt palautamme koron vain, jos asiakas on välilehden yläpuolella, ja meillä on "Ei" luotettu asiakas -sarakkeessa. Kantaasi ovat jälleen onnellisia.

Lue lisää Excel AND Function <>: n pääsivulta.

IF: n käyttäminen OR: n kanssa

TAI on toinen Excelin loogisista toiminnoista. Kuten AND, sen avulla voit määrittää useamman kuin yhden ehdon. Mutta toisin kuin AND, se palauttaa arvon TOSI, jos jokin määrittämistäsi testeistä on totta.

Ehkä asiakkaat eivät ole heidän välilehtensä yli, koska et ole ainoa syy, miksi katkaisit heidät. Ehkä annat joillekin ihmisille väliaikaisen kiellon muista syistä, esimerkiksi pelaamisesta tiloissa.

Joten lisäät uuden sarakkeen, jolla tunnistetaan kielletyt asiakkaat, ja päivität "Katkaise?" sarake TAI -testillä:

1	= JOS (TAI (C4> D4, E4 = "Kyllä"), "Kyllä", "Ei")

Katso vain TAI -osaa:

1	TAI (C4> D4, E4 = "Kyllä")

Ehtoja on kaksi:

• C4> D4: tarkistetaan, ovatko ne ylittäneet välilehden rajan
• F4 = ”Kyllä”: uusi osa, joka tarkistaa, ovatko ne tällä hetkellä kiellettyjä

Tämä arvioidaan totta, jos ne ovat välilehden yläpuolella tai jos sarakkeessa E on ”Kyllä”. Kuten huomaat, Harry on katkaistu nyt, vaikka hän ei ole ylittänyt välilehtien rajaa.

Lue lisää Excel TAI -toiminnon <> pääsivulta.

IF: n käyttäminen XOR: n kanssa

XOR on toinen looginen funktio, joka palauttaa arvon "Exclusive Or". Tämä on hieman vähemmän intuitiivinen kuin edelliset, joista olemme keskustelleet.

Yksinkertaisissa tapauksissa määrität kaksi ehtoa ja XOR palauttaa:

• TOSI, jos jompikumpi argumentti on tosi (sama kuin tavallinen TAI)
• EPÄTOSI jos molemmat väitteet pitävät paikkansa
• EPÄTOSI, jos molemmat väitteet ovat vääriä

Esimerkki voisi tehdä tämän selkeämmäksi. Kuvittele, että haluat alkaa antaa kuukausittaisia ​​bonuksia henkilöstöllesi:

• Jos he myyvät yli 800 dollaria ruokaa tai yli 800 dollaria juomia, annat heille puolet bonusta
• Jos he myyvät yli 800 dollaria molemmissa, annat heille täyden bonuksen
• Jos he myyvät alle 800 dollaria molemmissa, he eivät saa bonusta.

Tiedät jo kuinka treenata, jos he saavat täyden bonuksen. Käytät vain IF: tä AND: n kanssa, kuten aiemmin on kuvattu.

1	= JOS (JA (C4> 800, D4> 800), "Kyllä", "Ei")

Mutta miten selvittäisit, kuka saa puolibonuksen? Siellä XOR tulee:

1	= JOS (XOR (C4> = 800, D4> = 800), "Kyllä", "Ei")

Kuten näette, Woodyn juomamyynti oli yli 800 dollaria, mutta ei elintarvikkeiden myyntiä. Joten hän saa puolen bonuksen. Valmentajan kohdalla tilanne on päinvastainen. Diane ja Carla myivät yli 800 dollaria molemmista, joten he eivät saa puolta bonusta (molemmat argumentit ovat TOSI), ja Rebecca teki kynnyksen alle molemmille (molemmat argumentit EPÄTOSI), joten kaava palauttaa jälleen "ei".

Lue lisää Excel XOR -toiminnon pääsivulta <>.

IF: n käyttäminen NOT: n kanssa

NOT on jälleen yksi Excelin loogisista toiminnoista, jota käytetään hyvin yleisesti IF: n kanssa.

EI käännä loogisen testin tulosta. Toisin sanoen se tarkistaa, onko ehto täytetty.

Voit käyttää sitä IF: n kanssa seuraavasti:

1	= JOS (JA (C3> = 1985, EI (D3 = "Steven Spielberg"))), "Katso", "Älä katso")

Tässä on taulukko, jossa on tietoja joistakin 1980 -luvun elokuvista. Haluamme tunnistaa vuonna 1985 tai sen jälkeen julkaistut elokuvat, joita ei ole ohjannut Steven Spielberg.

Koska EI ole sisäkkäin AND -funktiossa, Excel arvioi sen ensin. Sitten se käyttää tulosta osana AND: ta.

Lue lisää Excel NOT Function <>: n pääsivulta.

Sisäkkäiset IF -lausunnot

Voit myös palauttaa IF -lausunnon IF -lauseesi sisällä. Tämän avulla voit tehdä monimutkaisempia laskelmia.

Palataan asiakkaidemme pöytään. Kuvittele, että haluat luokitella asiakkaat velkatason perusteella sinulle:

• $ 0: Ei mitään
• Jopa 500 dollaria: Alhainen
• 500–1000 dollaria: keskikokoinen
• Yli 1000 dollaria: Korkea

Voit tehdä tämän "sisäkkäin" IF -lausekkeilla:

1	= JOS (C4 = 0, "Ei mitään", JOS (C4 <= 500, "Matala", JOS (C4 <= 1000, "Keskitaso", JOS (C4> 1000, "Korkea"))))

On helpompi ymmärtää, jos laitat IF -lauseet eri riveille (ALT + ENTER Windowsissa, CTRL + COMMAND + ENTER Macissa):

12345

=JOS (C4 = 0, "Ei",JOS (C4 <= 500, "Matala",JOS (C4 <= 1000, "Normaali",JOS (C4> 1000, "Korkea", "Tuntematon"))))

JOS C4 on 0, palautamme "Ei mitään". Muussa tapauksessa siirrytään seuraavaan IF -lauseeseen. JOS C4 on 500 tai pienempi, palautamme arvon "Low". Muussa tapauksessa siirrymme seuraavaan IF -lausuntoon … ja niin edelleen.

Monimutkaisten IF -lausekkeiden yksinkertaistaminen apusarakkeiden avulla

Jos sinulla on useita sisäkkäisiä IF -lausekkeita ja käytät myös logiikkatoimintoja, kaavoistasi voi tulla erittäin vaikea lukea, testata ja päivittää.

Tämä on erityisen tärkeää pitää mielessä, jos muut ihmiset käyttävät laskentataulukkoa. Mikä on järkevää päässäsi, ei ehkä ole niin ilmeistä muille.

Helper -sarakkeet ovat erinomainen tapa kiertää tämä ongelma.

Olet analyytikko suuren yrityksen talousosastolla. Sinua on pyydetty luomaan laskentataulukko, joka tarkistaa, onko jokaisella työntekijällä oikeus yrityksen eläkkeeseen.

Tässä kriteerit:

Joten jos olet alle 55 -vuotias, sinulla on oltava 30 vuoden palvelus vyölläsi, jotta voit olla oikeutettu. Jos olet 55–59 -vuotias, tarvitset 15 vuoden palvelua. Ja niin edelleen, jopa 65 -vuotiaana, missä olet oikeutettu riippumatta siitä, kuinka kauan olet työskennellyt siellä.

Voit ratkaista tämän ongelman yhdellä monimutkaisella IF -lausekkeella:

1	= JOS (TAI (F4> = 65, JA (F4> = 62, G4> = 5), JA (F4> = 60, G4> = 10), JA (F4> = 55, G4> = 15), G4) > 30), "Kelpoinen", "Ei kelvollinen")

Vau! Vaikea saada pääsi kiinni siitä, eikö niin?

Parempi lähestymistapa voisi olla avustavien sarakkeiden käyttö. Meillä on viisi loogista testiä, jotka vastaavat ehtotaulukon jokaista riviä. Tämä on helpompi nähdä, jos lisäämme rivinvaihdot kaavaan, kuten aiemmin keskustelimme:

12345678

= JOS (TAI(F4> = 65,JA (F4> = 62, G4> = 5),JA (F4> = 60, G4> = 10),JA (F4> = 55, G4> = 15),G4> 30), "Kelpoinen", "Ei kelvollinen")

Joten voimme jakaa nämä viisi testiä erillisiin sarakkeisiin ja sitten yksinkertaisesti tarkistaa, onko jokin niistä totta:

Taulukon jokaisessa sarakkeessa E – I on jokainen kriteerimme erikseen. Sitten J4: ssä meillä on seuraava kaava:

1	= JOS (COUNTIF (E4: I4, TRUE), "kelvollinen", "ei kelvollinen")

Tässä meillä on IF -käsky, ja looginen testi laskee COUNTIF <> -solun E4: I4 -solujen määrän, jotka sisältävät TOSI -arvon.

Jos COUNTIF ei löydä TOSI -arvoa, se palauttaa 0, joka JOS tulkitsee EPÄTOSI, joten JOS palauttaa "Ei kelvollinen".

Jos COUNTIF löytää TRUE -arvoja, se palauttaa niiden määrän. IF tulkitsee minkä tahansa muun numeron kuin 0 TRUE: ksi, joten se palauttaa arvon "Kelpoinen".

Loogisten testien jakaminen tällä tavalla helpottaa kaavan lukemista, ja jos jotain menee pieleen, on paljon helpompi havaita missä virhe on.

Ryhmittelyn käyttäminen apusarakkeiden piilottamiseen

Helper -sarakkeet helpottavat kaavan hallintaa, mutta kun olet saanut ne paikoilleen ja tiedät, että ne toimivat oikein, ne vievät usein vain tilaa laskentataulukossasi lisäämättä mitään hyödyllistä tietoa.

Voit piilottaa sarakkeet, mutta tämä voi aiheuttaa ongelmia, koska piilotettuja sarakkeita on vaikea havaita, ellet katso sarakkeiden otsikoita tarkasti.

Parempi vaihtoehto on ryhmittely.

Valitse sarakkeet, jotka haluat ryhmitellä, tässä tapauksessa E: I. Paina sitten ALT + VAIHTO + OIKEA NUOLI Windowsissa tai COMMAND + VAIHTO + K Macissa. Voit myös siirtyä valintanauhan "Tiedot" -välilehdelle ja valita "Ryhmä" "Ääriviivat" -osiosta.

Näet sarakkeen otsikoiden yläpuolella olevan ryhmän seuraavasti:

Paina sitten "-" -painiketta piilottaaksesi sarakkeet:

IFS -toiminto

Sisäkkäiset IF -käskyt ovat erittäin hyödyllisiä, kun sinun on suoritettava monimutkaisempia loogisia vertailuja ja sinun on tehtävä se yhdessä solussa. Ne voivat kuitenkin muuttua monimutkaisiksi, kun ne pidentyvät, ja niitä voi olla vaikea lukea ja päivittää näytölläsi.

Microsoft esitteli Excel 2022: sta ja Excel 365: stä toisen toiminnon, IFS: n, helpottaakseen sen hallintaa. Yllä oleva sisäkkäinen IF -esimerkki voidaan saavuttaa seuraavalla IFS: llä:

1234567

= IFS (C4 = 0, "ei",C4 <= 500, "Matala",C4 <= 1000, "Normaali",C4> 1000, "korkea",TOSI, "Tuntematon",)

Voit lukea kaiken siitä Excel IFS -toiminnon pääsivulta <>.

IF: n käyttäminen ehdollisen muotoilun kanssa

Excelin ehdollisen muotoilutoiminnon avulla voit muotoilla solun eri tavoin sen sisällöstä riippuen. Koska IF palauttaa eri arvot loogisen testimme perusteella, saatamme haluta käyttää ehdollista muotoilua helpottaaksemme näiden arvojen näkemistä.

Palataan siis henkilöstömme bonustaulukkoon aikaisemmin.

Palaamme "kyllä" tai "ei" riippuen siitä, minkä bonuksen haluamme antaa. Tämä kertoo meille, mitä meidän on tiedettävä, mutta tieto ei tule meille. Yritetään korjata se.

Näin teet sen:

• Valitse solualue, joka sisältää IF -käskysi. Meidän tapauksessamme se on E4: F8.
• Napsauta "Ehdollinen muotoilu" valintanauhan "Koti" -välilehden "Tyylit" -osiossa.
• Napsauta "Korosta solusäännöt" ja sitten "Tasainen".
• Kirjoita ensimmäiseen ruutuun ”Kyllä” (tai mikä tahansa palautusarvo) ja valitse sitten haluamasi muotoilu toisesta ruudusta. (Valitsen tämän vihreän).
• Toista kaikille palautusarvoillesi (asetan myös Ei -arvot punaisiksi)

Tässä tulos:

IF: n käyttäminen matriisikaavoissa

Taulukko on arvovalikoima, ja Excelissä taulukot esitetään pilkulla erotetuina arvoina suluissa, kuten:

1	{1,2,3,4,5}

Taulukkojen kauneus on, että niiden avulla voit suorittaa laskelman kullekin alueen arvolle ja palauttaa sitten tuloksen. Esimerkiksi SUMPRODUCT -funktio ottaa kaksi taulukkoa, kertoo ne yhteen ja summaa tulokset.

Tämä kaava siis:

1	= SUMPRODUCT ({1,2,3}, {4,5,6})

… Palauttaa 32. Miksi? Käsittelemme sen läpi:

12345

1 * 4 = 42 * 5 = 103 * 6 = 184 + 10 + 18 = 32

Voimme tuoda tähän kuvaan IF -lauseen, joten jokainen näistä kertolaskuista tapahtuu vain, jos looginen testi palauttaa arvon tosi.

Otetaan esimerkiksi nämä tiedot:

https://www.automateexcel.com/excel/wp-content/uploads/2020/07/SUMPRODUCT-Example-Range.png "ei"> 1 = SUMPRODUCT (JOS ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))

Huomautus: Excel 2022 ja sitä vanhemmat versiot muutetaan taulukkokaavaksi painamalla CTRL + VAIHTO + ENTER.

Päätämme jotain tällaista:

https://www.automateexcel.com/excel/wp-content/uploads/2020/07/SUMPRODUCTS-IF-Results-Table.png "ei"> 1 $ C $ 2: $ C $ 10 = $ G2

Jos sarakkeen C nimi on englanniksi sama kuin G2: n ("Olivia"), DO kerro tämän rivin sarakkeiden D ja E arvot. Muussa tapauksessa älä lisää niitä. Laske sitten kaikki tulokset yhteen.

Voit oppia lisää tästä kaavasta SUMPRODUCT IF -kaavan pääsivulla <>.

JOS Google Sheetsissä

IF -toiminto toimii täsmälleen samalla tavalla Google Sheetsissä kuin Excelissä:

Lisämerkinnät

Käytä IF -toimintoa testataksesi, onko ehto TOSI. Jos ehto on TOSI, tee yksi asia. Jos se on EPÄTOSI, tee toinen. Ehdon on oltava looginen lauseke (esim. A1> 5), viittaus soluun, joka sisältää TOSI tai EPÄTOSI, tai taulukko, joka sisältää kaikki loogiset arvot.

IF -toiminto voi testata vain yhtä ehtoa kerrallaan. Voit kuitenkin "liittää" muita loogisia toimintoja IF -ehtoon testataksesi useita ehtoja kerralla:

= jos (JA (a1> 0, a2> 0), TOSI, EPÄTOSI)
= jos (TAI (a1> 0, a2> 0), TOSI, EPÄTOSI)
= jos (XOR (a1> 0, a2> 0), TRUE, FALSE)
TAI Toimintotesti jos yksi tai useampi ehdot täyttyvät.
JA Toimintotesti jos kaikki ehdot täyttyvät.
XOR -toimintojen testi, jos yksi ja ainoa edellytys täyttyy.

Voit myös "sijoittaa" IF -toiminnon IF -funktioon:

1	= jos (a1 <0, jos (a2 <0, "Molemmat", "vain 1"), "vain yksi")

Nyt muutamia konkreettisia esimerkkejä siitä, miten IF -toiminto toimii käytännössä:

1. Aloita uusi työkirja.

2. Anna soluun A1 arvo 10 (ja paina Enter)

3. Kirjoita sitten soluun B1 seuraava kaava:

1	= JOS (A1> 5, "BIGGER THAN 5", "LESS THAN 5")

4. Näytön pitäisi näyttää tältä nyt:

5. Jos olet kirjoittanut kaavan oikein, näet viestin "Suurempi kuin 5" solussa B1.

6. Soluun B1 syöttämäsi kaava suorittaa testin "A1> 5" eli tarkistaa, onko solun A1 arvo suurempi kuin 5. Tällä hetkellä solun A1 arvo on 10 - joten ehto on TOSI ja viesti "BIGGER THAN 5" tulee näkyviin

7. Jos nyt muutamme solun A1 arvon 2:

Thtn solun B2 viesti on nyt “LESS THAN 5”, koska ehto on FALSE.

8. Voit jatkaa arvon muuttamista solussa A1 ja solun B2 viesti muuttuu vastaavasti.

9. Tietenkin on tilanteita, joissa ehto voi tuottaa ilkeitä tuloksia:

• Mitä tapahtuu, jos syötämme arvon 5 soluun A1?

• Entä jos jätämme solun A1 tyhjäksi?

• Entä jos laitamme tekstiä soluun A1, esim. Lause DOG

Lisätietoja Excel IF -toiminnosta

Katsomme nyt IF -toimintoa yksityiskohtaisemmin. Sen avulla voidaan analysoida suuria tietomääriä erittäin helposti.

Kuvittele, että olet aluemyyntipäällikkö ja sinulla on myyntitiimi. Voit tallentaa kunkin henkilön tekemän kokonaismyynnin yksinkertaiseen Excel -laskentataulukkoon:

Oletetaan, että bonuksen kriteerit olivat, että kyseisen henkilön myynti ylitti 40 000 puntaa. Voit vain ”katsella” tietoja ja todeta, että vain Anton, Newton ja Monique saavuttivat tavoitteen.

Tämä on melko helppoa, kun sinulla on vain kourallinen nimiä. Jos sinulla on kuitenkin useita, on virheitä. Onneksi käyttämällä Excelin IF -toimintoa se voidaan tehdä paljon nopeammin ja turvallisemmin.

Määritä uusi työkirja ja kirjoita tiedot yllä kuvatulla tavalla. Kirjoita sitten soluun D4 seuraava kaava:-

1	= JOS (C4> 40000, "BONUS PAYABLE", "NO BONUS")

niin että sinulla on:

Huomaa, kuinka Excel näyttää IF -kaavan rakenteen, joka on hyödyllinen muistelmateos.

Kun olet kirjoittanut kaavan, paina ENTER ja näet sen arvioivan ensimmäisen rivin:

Kaava on arvioitu Martinille - koska hän ansaitsi alle 40 000 puntaa, hänellä ei ole oikeutta bonukseen.

Ja sitten vedämme kaavat alas napsauttamalla oikeaa alakulmaa ja vetämällä alaspäin voimme selvittää, onko jokaisella henkilöllä oikeus bonukseen:

Ja näemme, että Excel on määrittänyt, kuka myyjistä on oikeutettu bonukseen.

Palaa Excelin kaikkien toimintojen luetteloon

VBA IF -lausunnot

Voit myös käyttää If -lausuntoja VBA: ssa. Napsauta linkkiä saadaksesi lisätietoja, mutta tässä on yksinkertainen esimerkki:

1234567

Alatesti_IF ()Jos alue ("a1"). Arvo <0 sittenAlue ("b1"). Arvo = "Negatiivinen"Loppu JosLoppu Jos

Tämä koodi testaa, onko solun arvo negatiivinen. Jos näin on, se kirjoittaa "negatiivisen" seuraavaan soluun.