Excel

19 vinkkiä sisäkkäisiin IF-kaavoihin

19 Tips Nested If Formulas

JOS-toiminto on yksi eniten käytettyjä toimintoja Excelissä. IF on yksinkertainen toiminto, ja ihmiset rakastavat IF: tä, koska se antaa heille voiman tehdä Excel vastata koska tiedot syötetään laskentataulukkoon. IF: n avulla voit herättää laskentataulukon eloon.

Mutta yksi IF johtaa usein toiseen, ja kun yhdistät enemmän kuin pari IF: tä, kaavat voivat alkaa näyttää pieniltä Frankensteineiltä :)



Ovatko sisäkkäiset IF: t pahat? Ovatko ne joskus välttämättömiä? Mitkä ovat vaihtoehdot?



Lue oppia vastauksia näihin kysymyksiin ja lisää ...

1. Perus IF

Ennen kuin puhumme sisäkkäisistä IF: stä, tarkistetaan nopeasti IF: n perusrakenne:



 
= IF (test,[true],[false])

IF-toiminto suorittaa testin ja suorittaa erilaisia ​​toimintoja sen mukaan, onko tulos totta vai väärä.

Huomaa hakasulkeet ... nämä tarkoittavat, että argumentit ovat valinnaisia. Sinun on kuitenkin toimitettava jompikumpi arvo tosi tai arvo vääriä.

Havainnollistamiseksi tässä käytämme IF: tä tarkistamaan pisteet ja laskemaan 'Pass' vähintään 65-pisteille:



IF-perustoiminto - paluu

Esimerkin solu D3 sisältää tämän kaavan:

 
= IF (C3>=65,'Pass')

Mikä voidaan lukea näin: jos C3: n pisteet ovat vähintään 65, palauta 'Pass'.

Huomaa kuitenkin, että jos pisteet ovat vähemmän kuin 65, JOS palauttaa EPÄTOSI, koska emme antaneet arvoa, jos se on epätosi. Jos haluat näyttää 'Fail' epäonnistuneille pisteille, voimme lisätä 'Fail' vääräksi argumentiksi näin:

 
= IF (C3>=65,'Pass','Fail')

IF-perustoiminto - arvon lisäys väärälle arvolle

Video: Kuinka rakentaa loogisia kaavoja .

2. Mitä pesiminen tarkoittaa

Pesiminen tarkoittaa yksinkertaisesti kaavojen yhdistämistä toistensa sisällä siten, että yksi kaava käsittelee toisen tuloksen. Esimerkiksi tässä on kaava, jossa TODAY-toiminto on sisäkkäin MONTH-funktion sisällä:

 
= MONTH ( TODAY ())

TODAY-funktio palauttaa nykyisen päivämäärän MONTH-toiminnon sisällä. MONTH-funktio ottaa kyseisen päivämäärän ja palauttaa kuluvan kuukauden. Jopa kohtalaisen monimutkaiset kaavat käyttävät pesimistä usein, joten näet pesinnät kaikkialla monimutkaisemmissa kaavoissa.

3. Yksinkertainen sisäkkäinen IF

Sisäkkäinen IF on vain kaksi muuta IF-käskyä kaavassa, jossa yksi IF-lause esiintyy toisen sisällä.

Havainnollistamiseksi alla olen laajentanut yllä olevaa alkuperäistä läpäisy- ja hylkäyskaavaa käsittelemään epätäydellisiä tuloksia lisäämällä IF-funktion ja sijoittamalla yhden IF: n toiseen:

Perus sisäkkäinen IF

 
= IF (C3='','Incomplete', IF (C3>=65,'Pass','Fail'))

Uloin IF suoritetaan ensin ja testataan, onko C3 tyhjä. Jos näin on, ulompi IF palauttaa 'Epätäydellinen', eikä sisempi IF koskaan toimi.

Jos pisteet ovat ei tyhjä , ulompi IF palauttaa FALSE, ja alkuperäinen IF-toiminto suoritetaan.

Opi sisäkkäisiä IF: itä selkeä, ytimekäs videokoulutus .

4. Sisäkkäinen IF vaa'oille

Näet usein sisäkkäisiä IF: itä, jotka on määritetty käsittelemään 'asteikkoja' ... esimerkiksi määrittämään arvosanoja, toimituskuluja, verokantoja tai muita arvoja, jotka vaihtelevat asteikolla numeerisella syötteellä. Niin kauan kuin asteikossa ei ole liikaa tasoja, sisäkkäiset IF: t toimivat hyvin tässä, mutta sinun on pidettävä kaava järjestyksessä, muuten sen lukeminen on vaikeaa.

Temppu on päättää suunta (korkeasta matalaan tai matalasta korkeaan) ja sitten jäsentää olosuhteet vastaavasti. Esimerkiksi arvosanojen asettamiseksi 'matalasta korkeaan' järjestyksessä voimme esittää tarvittavan ratkaisun seuraavassa taulukossa. Huomaa, että A: lla ei ole ehtoa, koska kun olemme käyneet läpi kaikki muut ehdot, tiedämme, että pistemäärän on oltava suurempi kuin 95, ja siksi A: n.

Pisteet Arvosana Kunto
0-63 F <64
64-72 D <73
73-84 C <85
85-94 B <95
95-100 TO

Kun olosuhteet on selvästi ymmärretty, voimme kirjoittaa ensimmäisen IF-lauseen:

laskea, jos suurempi tai yhtä suuri kuin
 
= IF (C5<64,'F')

Tämä huolehtii F: stä. D: n käsittelemiseksi meidän on lisättävä toinen ehto:

 
= IF (C5<64,'F', IF (C5<73,'D'))

Huomaa, että pudotin yksinkertaisesti toisen IF: n ensimmäiseen IF: hen 'väärän' tuloksen vuoksi. Laajennetaan kaava käsittelemään C, toistamalla prosessi:

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C')))

Jatkamme tällä tavalla, kunnes saavutamme viimeisen palkkaluokan. Sitten sen sijaan, että lisäät toisen IF: n, lisää vain viimeinen arvosana vääräksi.

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))

Tässä on viimeinen sisäkkäinen IF-kaava toiminnassa:

Valmis sisäkkäinen IF-esimerkki arvosanojen laskemiseksi

Video: Kuinka tehdä sisäkkäinen IF-arvo luokkien määrittämiseksi

5. Sisäkkäisillä IF: llä on looginen kulku

Monet kaavat ratkaistaan ​​sisältäpäin, koska 'sisäiset' toiminnot tai lausekkeet on ensin ratkaistava, jotta kaavan loppu jatkuisi.

Sisäkkäisillä IF: llä on oma looginen virtauksensa, koska 'ulkoiset' IF: t toimivat porttina 'sisäisiin' IF: iin. Tämä tarkoittaa, että ulkoisten IF: ien tulokset määrittävät, toimivatko sisäiset IF: t edes. Alla oleva kaavio havainnollisti yllä olevan arvokaavan loogista kulkua.

Sisäkkäisen IF: n looginen kulku

6. Tarkkaile loogista kulkua Arvioi-toiminnolla

Windowsissa voit käyttää Arvioi ominaisuus katsomaan Excelin ratkaisevan kaavasi askel askeleelta. Tämä on hieno tapa 'nähdä' monimutkaisempien kaavojen looginen kulku ja vianetsintä, kun asiat eivät toimi odotetulla tavalla. Alla olevassa näytössä näkyy Arvioi-ikkuna auki ja käyttövalmis. Aina kun napsautat Arvioi-painiketta, kaavan seuraava vaihe ratkaistaan. Löydät Arvioi-valintanauhan Kaavat-välilehdeltä (Alt M, V).

Arvioi-toiminnolla voit siirtyä sisäkkäisen IF: n läpi, joka määrittää arvosanat

Valitettavasti Excelin Mac-versio ei sisällä Arvioi-ominaisuutta, mutta voit silti käyttää alla olevaa F9-temppua.

7. Käytä F9-näppäintä tarkistaaksesi tulokset

Kun valitset lausekkeen kaavapalkista ja painat F9-näppäintä, Excel ratkaisee vain valitun osan. Tämä on tehokas tapa vahvistaa, mitä kaava todella tekee. Alla olevassa näytössä käytän kaavan eri osia näytön vihjeikkunoilla ja napsautan sitten F9 nähdäksesi osan ratkaistuna:

Tarkista F9: llä sisäkkäinen IF, joka antaa arvosanat

Kumoa F9 painamalla Mac-näppäimiä Control + Z (Command + Z). Voit myös sulkea kaavaeditorin ilman muutoksia painamalla Esc-näppäintä.

Video: Kaavan virheenkorjaus F9: llä

8. Tunne rajasi

Excelillä on rajoituksia sille, kuinka syvälle IF-toiminnot voidaan sijoittaa. Excel 2007 saakka Excel sallii jopa 7 tasoa sisäkkäisiä IF: itä. Excel 2007+: ssa Excel sallii jopa 64 tasoa.

Kuitenkin vain siksi, että sinä voi pesä paljon IF: itä, se ei tarkoita sinua pitäisi . Jokainen lisäämäsi taso vaikeuttaa kaavan ymmärtämistä ja vianmääritystä. Jos huomaat työskentelevän sisäkkäisen IF: n kanssa, joka on yli muutaman tason syvä, sinun pitäisi todennäköisesti käyttää erilaista lähestymistapaa - katso vaihtoehtoja alla.

9. Yhdistä sulkeet kuin ammattilainen

Yksi sisäkkäisten sijoitusrahastojen haasteista on sulkujen sovittaminen tai 'tasapainottaminen'. Kun sulkeita ei ole sovitettu oikein, kaava on rikki. Onneksi E xcel tarjoaa pari työkalua, joiden avulla voit varmistaa, että sulkeet ovat tasapainossa kaavoja muokattaessa.

Ensinnäkin, kun sinulla on useampi kuin yksi sulkujoukko, sulut värikoodataan niin, että avaavat sulut vastaavat sulkeja. Näitä värejä on melko vaikea nähdä, mutta ne ovat olemassa, jos tarkastelet tarkasti:

Kaavasulkeet ovat värien mukaisia, mutta niitä on vaikea nähdä

Toinen (ja parempi), kun suljet sulkeet, Excel rohkaisee vastaavan parin lyhyesti. Voit myös napsauttaa kaavaa ja liikkua sulkeissa nuolinäppäimellä, ja Excel rohkaisee molemmat sulut lyhyesti, kun parit ovat yhteensopivia. Jos ottelua ei ole, et näe taitoa.

Valitettavasti taivutus on vain Windows-ominaisuus. Jos muokkaat monimutkaisia ​​kaavoja Excelissä Macissa, on joskus järkevää kopioida ja liittää kaava hyvään tekstieditoriin ( Teksti Wrangler on ilmainen ja erinomainen) saadaksesi parempia sulkeita vastaavia työkaluja. Teksti Wrangler vilkkuu, kun sulkeet sovitetaan, ja voit käyttää komentoja + B valitaksesi kaiken sulkeissa olevan tekstin. Voit liittää kaavan takaisin Exceliin, kun olet suoristanut asiat.

10. Navigoi ja valitse näytön vihjeikkunan avulla

Sisäkkäisten IF: ien navigoinnissa ja muokkauksessa toimintonäytön kärki on paras ystäväsi. Sen avulla voit navigoida ja valita kaikki argumentit sisäkkäisessä IF: ssä:

Navigoi ja valitse kaavan argumentit näytön kärjellä

Voit nähdä minun käyttävän näytön vihjeikkunaa paljon tässä videossa: Kuinka rakentaa sisäkkäinen IF .

11. Ole varovainen tekstin ja numeroiden suhteen

Aivan kuten nopea muistutus, kun työskentelet IF-toiminnon kanssa, varmista, että numerot ja teksti sopivat oikein. Näen usein kaavoja JOS näin:

 
= IF (A1='100','Pass','Fail')

Onko testipisteet A1: ssä Todella teksti eikä numero? Ei? Älä sitten käytä lainauksia numeron ympärillä. Muussa tapauksessa looginen testi palauttaa EPÄTOSI, vaikka arvo olisi läpäisevä pisteet, koska 100 ei ole sama kuin 100. Jos testitulos on numeerinen, käytä tätä:

 
= IF (A1=100,'Pass','Fail')

12. Lisää rivinvaihdot tekevät sisäkkäisistä IF: stä helposti luettavia

Kun työskentelet kaavan kanssa, joka sisältää useita sisäkkäisiä IF: itä, voi olla hankalaa pitää asiat suorana. Koska Excel ei välitä 'kaavojen' välilyönnistä (eli ylimääräisistä välilyönneistä tai rivinvaihdoista), voit parantaa huomattavasti sisäkkäisten IF-tiedostojen luettavuutta lisäämällä rivikatkoja.

Esimerkiksi alla olevassa näytössä näkyy sisäkkäinen IF, joka laskee palkkiokannan myyntiluvun perusteella. Täältä näet tyypillisen sisäkkäisen IF-rakenteen, jota on vaikea tulkita:

Sisäkkäisiä IF: itä ilman rivikatkoja on vaikea lukea

Jos kuitenkin lisätään rivinvaihdot ennen kutakin arvoa, jos väärä, kaavan logiikka hyppää selvästi ulos. Lisäksi kaavaa on helpompi muokata:

Rivinvaihdot helpottavat sisäkkäisten IF: ien lukemista

Voit lisätä rivinvaihtoja Windowsissa Alt + Enter-näppäimillä, Macissa käyttämällä Control + Option + Return.

Video: Kuinka tehdä sisäkkäisestä IF: stä helpompi lukea .

13. Rajoita IF-arvoja AND- ja OR-näppäimillä

Sisäkkäiset IF: t ovat tehokkaita, mutta ne muuttuvat monimutkaisiksi nopeasti, kun lisäät lisää tasoja. Yksi tapa välttää enemmän tasoja on käyttää IF: tä yhdessä AND- ja OR-toimintojen kanssa. Nämä toiminnot palauttavat yksinkertaisen TOSI / EPÄTOSI-tuloksen, joka toimii täydellisesti IF: n sisällä, joten voit käyttää niitä yhden IF: n logiikan laajentamiseen.

Esimerkiksi alla olevaan ongelmaan haluamme laittaa 'x' sarakkeeseen D merkitsemään rivejä, joissa väri on 'punainen' ja koko on 'pieni'.

AND-funktio on yksinkertaisempi kuin kaksi sisäkkäistä IF: tä

Voisimme kirjoittaa kaavan kahdella sisäkkäisellä IF: llä näin:

 
= IF (B6='red', IF (C6='small','x',''),'')

Korvaamalla testin AND-funktiolla voimme kuitenkin yksinkertaistaa kaavaa:

 
= IF ( AND (B6='red',C6='small'),'x','')

Samalla tavalla voimme helposti laajentaa tätä kaavaa OR-funktiolla tarkistaaksesi punaisen TAI sinisen JA pienen:

 
= IF ( AND ( OR (B4='red',B4='blue'),C4='small'),'x','')

Kaikki tämä voisi tehtävä sisäkkäisillä IF: llä, mutta kaava muuttuisi nopeasti monimutkaisemmaksi.

Video: JOS tämä TAI se

14. Korvaa sisäkkäiset IF: t VLOOKUP: lla

Kun sisäkkäinen IF määrittää vain arvot yhden tulon perusteella, se voidaan helposti korvata VLOOKUP-toiminto . Esimerkiksi tämä sisäkkäinen IF määrittää numerot viidelle eri värille:

 
= IF (E3='red',100, IF (E3='blue',200, IF (E3='green',300, IF (E3='orange',400,500))))

Voimme helposti korvata sen tällä (paljon yksinkertaisemmalla) VLOOKUP:

 
= VLOOKUP (E3,B3:C7,2,0)

Sisäkkäin IF vs. VLOOKUP

Bonuksena VLOOKUP pitää arvot laskentataulukossa (jossa niitä voidaan helposti muuttaa) sen sijaan, että upotettaisiin ne kaavaan.

Vaikka yllä oleva kaava käyttää tarkkaa vastaavuutta, voit käyttää sitä helposti VLOOKUP-arvosanat yhtä hyvin.

Katso myös: 23 tietoa VLOOKUPista

Video: Kuinka käyttää VLOOKUPia

Video: Miksi VLOOKUP on parempi kuin sisäkkäiset IF: t

15. Valitse VALITSE

VALINTA-toiminto voi tarjota tyylikkään ratkaisun, kun sinun on kartoitettava yksinkertaiset, peräkkäiset numerot (1,2,3 jne.) Mielivaltaisiin arvoihin.

Alla olevassa esimerkissä VALITA käytetään luomaan mukautettuja arkipäivän lyhenteitä:

Sisäkkäin IF vs. VALITSE-toiminto

Varmasti sinä voisi käytä samaa ja pitkää ja monimutkaista sisäkkäistä IF: tä, mutta älä :)

16. Käytä IFS: ää sisäkkäisten IF: n sijaan

Jos käytät Excel 2016: ta Office 365: n kautta, sisäkkäisten IF: iden sijaan on uusi toiminto: IFS-toiminto. IFS-toiminto tarjoaa erityisen rakenteen useiden olosuhteiden arvioimiseksi ilman pesiä:

IFS-toiminto - useita ehtoja ilman pesimistä

Edellä käytetty kaava näyttää tältä:

 
= IFS (D5<60,'F',D5<70,'D',D5<80,'C',D5<90,'B',D5>=90,'A')

Huomaa, että meillä on vain yksi pari sulkeita!

Mitä tapahtuu, kun avaat laskentataulukon, joka käyttää IFS-toimintoa Excelin vanhemmassa versiossa? Excel 2013: ssa ja 2010: ssä (ja uskon Excel 2007: n, mutta en voi testata) näet '_xlfn'. liitetään solun IFS: ään. Aikaisemmin laskettu arvo on edelleen olemassa, mutta jos jokin saa kaavan laskemaan uudelleen, näet #NAME -virheen. Microsoft on lisätietoja täältä .

17. Max ulos

Joskus voit käyttää MAXia tai MINia erittäin fiksulla tavalla, joka välttää IF-käskyn. Oletetaan esimerkiksi, että sinulla on laskelma, jonka on saatava positiivinen luku tai nolla. Toisin sanoen, jos laskelma palauttaa negatiivisen luvun, haluat vain näyttää nollan.

MAX-toiminto antaa sinulle fiksun tavan tehdä tämä ilman IF: tä missään näkyvissä:

 
= MAX (calculation,0)

Tämä tekniikka palauttaa laskennan tuloksen, jos se on positiivinen, ja nolla muuten.

Rakastan tätä rakennetta, koska se vain niin yksinkertaista . Katso täydellinen artikkeli tästä artikkelista .

18. Ansa virheitä IFERROR

IF: n klassinen käyttö on virheiden ansaitseminen ja toisen tuloksen antaminen virheen heitettäessä, kuten tämä:

 
= IF ( ISERROR (formula),error_result,formula)

Tämä on ruma ja tarpeeton, koska sama kaava menee sisään kahdesti, ja Excelin on laskettava sama tulos kahdesti, kun virheitä ei ole.

Excel 2007: ssä otettiin käyttöön IFERROR-toiminto, jonka avulla virheet voidaan ansaita paljon tyylikkäästi:

 
= IFERROR (formula,error_result)

Nyt kun kaava heittää virheen, IFERROR yksinkertaisesti palauttaa antamasi arvon.

19. Käytä loogista logiikkaa

Voit myös joskus välttää sisäkkäisiä IF: itä käyttämällä ns. Boolean logiikkaa. Sana boolean viittaa TOSI / EPÄTOSI-arvoihin. Vaikka Excel näyttää sanat TOSI ja EPÄTOSI soluissa, sisäisesti Excel käsittelee TOSI arvona 1 ja EPÄTOSI nollana. Voit käyttää tätä tosiasiaa älykkäiden ja erittäin nopeiden kaavojen kirjoittamiseen. Esimerkiksi yllä olevassa VLOOKUP-esimerkissä meillä on sisäkkäinen IF-kaava, joka näyttää tältä:

 
= IF (E3='red',100, IF (E3='blue',200, IF (E3='green',300, IF (E3='orange',400,500))))

Boolen logiikkaa käyttämällä voit kirjoittaa kaavan uudestaan ​​seuraavasti:

 
=(E3='red')*100+(E3='blue')*200+(E3='green')*300+(E3='orange')*400+(E3='purple')*500

Jokainen lauseke suorittaa testin ja kertoo sitten testin tuloksen arvolla, jos tosi. Koska testit palauttavat joko TOSI tai EPÄTOSI (1 tai 0), EPÄTOSI-tulokset peruuttavat itse kaavan.

Numeeristen tulosten osalta looginen logiikka on yksinkertainen ja erittäin nopea, koska haarautumista ei ole. Haittapuolena looginen logiikka voi olla hämmentävää ihmisille, jotka eivät ole tottuneet näkemään sitä. Silti se on hieno tekniikka tietää.

Video: Boolen logiikan käyttäminen Excel-kaavoissa

Milloin tarvitset sisäkkäisen IF: n?

Kaikilla näillä vaihtoehdoilla sisäkkäisten IF: n välttämiseksi saatat miettiä, milloin on järkevää käyttää sisäkkäistä IF: tä?

Mielestäni sisäkkäisillä IF: llä on järkeä, kun sinun on arvioitava useita eri tuloja tehdä päätös.

Oletetaan esimerkiksi, että haluat laskea laskun tilan Maksettu, Avoin, Erääntynyt jne. Tämä edellyttää, että tarkastelet laskun ikää ja erääntynyt saldo:

Laskutilan laskeminen sisäkkäisellä IF: llä

Tässä tapauksessa sisäkkäinen IF on täysin hieno ratkaisu.

Sinun ajatuksesi?

Entä sinä? Oletko IF-ster? Välttätkö sisäkkäisiä IF: itä? Ovatko sisäkkäiset IF: t pahat? Jaa ajatuksesi alla.

Opi Excel-kaavat nopeasti ytimekäs videokoulutus . Kirjailija Dave Bruns


^