Excel

19 vinkkiä sisäkkäisiin IF -kaavoihin

19 Tips Nested If Formulas

The IF -toiminto on yksi Excelin käytetyimmistä toiminnoista. IF on yksinkertainen toiminto, ja ihmiset rakastavat IF: tä, koska se antaa heille mahdollisuuden tehdä Excel vastata kun 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ä, kaavasi voivat alkaa näyttää pieniltä Frankensteinilta :)

Ovatko sisäkkäiset IF: t pahoja? Ovatko ne joskus tarpeellisia? Mitkä ovat vaihtoehdot?





Lue lisää saadaksesi vastaukset näihin ja moniin muihin kysymyksiin ...

1. Perus IF

Ennen kuin puhumme sisäkkäisestä IF: stä, tarkastellaan nopeasti IF -perusrakennetta:



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

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

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

Havainnollistamiseksi tässä käytämme IF: tä pisteiden tarkistamiseen ja vähintään 65: n pisteiden laskemiseen:

IF -perustoiminto - paluu

Esimerkin solu D3 sisältää seuraavan kaavan:

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

Tämä voidaan lukea seuraavasti: jos C3 -pistemäärä on 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ää epäonnistuneiden pisteiden epäonnistumisen, voimme lisätä epäonnistumisen virheelliseksi argumentiksi seuraavasti:

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

IF -perustoiminto - lisätty arvo epätosi

Video: Kuinka rakentaa loogisia kaavoja .

2. Mitä pesiminen tarkoittaa

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

 
= MONTH ( TODAY ())

TODAY -funktio palauttaa KUUKAUSI -funktion nykyisen päivämäärän. KUUKAUSI -funktio ottaa kyseisen päivämäärän ja palauttaa kuluvan kuukauden. Jopa kohtalaisen monimutkaiset kaavat käyttävät sisäkkäin usein, joten näet pesimisen kaikkialla monimutkaisemmissa kaavoissa.

3. Yksinkertainen sisäkkäinen IF

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

Havainnollistamiseksi alla olen laajentanut yllä olevaa hyväksyttävää/epäonnistunutta kaavaa käsittelemään epätäydellisiä tuloksia lisäämällä IF -funktion ja liittämällä yhden IF toiseen:

Perus sisäkkäinen IF

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

Ulompi IF suoritetaan ensin ja testaa, onko C3 tyhjä. Jos näin on, ulkoinen IF palauttaa epätäydellisen ja sisäinen IF ei koskaan toimi.

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

Opi sisäkkäisiä IF -ohjelmia selkeä, ytimekäs videokoulutus .

4. Sisäkkäinen IF asteikolle

Näet usein sisäkkäisiä IF -asetuksia, jotka on määritetty käsittelemään `` asteikot '' ... esimerkiksi määrittämään arvosanat, toimituskulut, verokannat tai muut arvot, jotka vaihtelevat asteikolla numeerisen syötteen avulla. Niin kauan kuin asteikolla ei ole liikaa tasoja, sisäkkäiset IF -välineet toimivat täällä hyvin, mutta sinun on pidettävä kaava järjestyksessä, tai siitä tulee vaikeaa lukea.

Temppu on päättää suunta (korkeasta matalaan tai matalasta korkeaan) ja rakentaa olosuhteet sen mukaisesti. Jos esimerkiksi haluat antaa arvosanoja 'matalasta korkeaan', voimme esittää seuraavassa taulukossa tarvittavan ratkaisun. Huomaa, että A: lle 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.

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 ymmärretty selvästi, voimme syöttää ensimmäisen IF -lausekkeen:

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

Tämä hoitaa 'F'. Nyt 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 saamiseksi. Jos haluat laajentaa kaavan käsittelemään C: tä, toistamme prosessin:

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

Jatkamme tällä tiellä, kunnes saavutamme viimeisen luokan. Lisää sen sijaan uuden IF: n sijaan lopullinen arvosana epätosi.

 
= 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 laskemiseen

Video: Kuinka tehdä sisäkkäinen IF, jos haluat antaa arvosanoja

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

Monet kaavat ratkaistaan ​​sisältä ulospäin, koska 'sisäiset' toiminnot tai lausekkeet on ratkaistava ensin, jotta kaavan loppuosa jatkuu.

Sisäkkäisillä sijoitusrahastoilla on oma looginen kulkunsa, koska 'ulkoiset' sijoitusrahastot toimivat porttina 'sisäisiin' sijoitusrahastoihin. Tämä tarkoittaa sitä, että ulkoisten sijoitusrahastojen tulokset määräävät, toimivatko sisäiset sijoitusrahastot edes. Alla oleva kaavio havainnollisti yllä olevan arvosanakaavan loogista kulkua.

Sisäisen IF: n looginen kulku

6. Käytä Evaluatea loogisen kulun seuraamiseen

Windowsissa voit käyttää Arvioi ominaisuus katsomaan Excelin ratkaisemaan kaavasi vaihe vaiheelta. Tämä on loistava tapa 'nähdä' monimutkaisempien kaavojen looginen kulku ja tehdä vianetsintä, kun asiat eivät toimi odotetulla tavalla. Alla olevassa näytössä näkyy Arvioi -ikkuna avoinna ja käyttövalmis. Aina kun napsautat Arvioi -painiketta, kaavan seuraava vaihe ratkaistaan. Arviointi löytyy nauhan Kaavat -välilehdestä (Alt M, V).

Evaluate -toiminnon avulla voit käydä läpi sisäkkäisen IF: n, joka antaa arvosanoja

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

7. Käytä tarkistusta F9 -näppäimellä

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 näytön kärki -ikkunoita kaavan eri osien valitsemiseen ja napsauta sitten F9 nähdäksesi, että osa on ratkaistu:

Tarkista F9 -näppäimellä sisäinen IF, joka antaa arvosanoja

Kumoa F9 painamalla Ctrl + Z (Command + Z) Macissa. Voit myös poistua kaavaeditorista ilman muutoksia painamalla Esc -näppäintä.

Video: Kaavan virheenkorjaus F9: llä

8. Tunne rajasi

Excelillä on rajoituksia sille, kuinka syvälle IF -toimintoja voi sijoittaa. Jopa Excel 2007: een asti Excel salli jopa 7 sisäkkäisten IF -tasojen tasoa. Excel 2007+: ssa Excel mahdollistaa jopa 64 tasoa.

Kuitenkin vain siksi, että sinä voi pesivät paljon IF: itä, se ei tarkoita sinua pitäisi . Jokainen lisätty taso vaikeuttaa kaavan ymmärtämistä ja vianetsintää. Jos huomaat työskentelevänsi sisäkkäisen IF: n kanssa, joka on enemmän kuin muutaman tason syvä, sinun pitäisi todennäköisesti valita erilainen lähestymistapa - katso alta vaihtoehtoja.

9. Yhdistä sulut kuin ammattilainen

Yksi sisäkkäisten sijoitusrahastojen haasteista on sulkeiden sovittaminen tai tasapainottaminen. Jos sulkeita ei täsmää oikein, kaava on rikki. Onneksi E xcel tarjoaa pari työkalua, joiden avulla voit varmistaa, että sulut ovat tasapainossa kaavojen muokkaamisen aikana.

Ensinnäkin, kun sinulla on useampi kuin yksi sulkujoukko, sulut on värikoodattu siten, että avaavat sulut vastaavat sulkeita. Nämä värit ovat melko vaikea nähdä, mutta ne ovat siellä, jos tarkastelet tarkasti:

Kaavan suluissa on värit, mutta niitä on vaikea nähdä

Toinen (ja parempi), kun suljet sulut, Excel lihavoi lyhyesti vastaavan parin. Voit myös napsauttaa kaavaa ja käyttää nuolinäppäimiä sulkujen selaamiseen, ja Excel lihavoi lyhyesti molemmat sulkeet, kun on olemassa vastaava pari. Jos vastaavuutta ei ole, et näe lihavointia.

Valitettavasti lihavointi on vain Windows-ominaisuus. Jos käytät Exceliä Macissa monimutkaisten kaavojen muokkaamiseen, 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 täsmäävät, ja voit valita kaikki suluissa olevat tekstit Command + B: llä. Voit liittää kaavan takaisin Exceliin, kun olet suorittanut asiat.

10. Käytä näytön vihjeikkunaa navigoidaksesi ja valitaksesi

Kun kyse on sisäkkäisten IF -tiedostojen selaamisesta ja muokkaamisesta, toimintonäytön vinkki on paras ystäväsi. Sen avulla voit navigoida ja valita tarkasti kaikki argumentit sisäkkäisestä IF: stä:

Navigoi ja valitse kaava -argumentit näytön kärjellä

Näet minun käyttävän ruudukon vihjeikkunaa paljon tässä videossa: Kuinka rakentaa sisäkkäinen IF .

11. Ole varovainen tekstin ja numeroiden kanssa

Muista vain, että jos käytät IF -toimintoa, varmista, että numerot ja teksti vastaavat oikein. Näen usein tällaisia ​​kaavoja JOS:

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

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

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

12. Lisää rivinvaihtoja, jotta sisäkkäiset IF: t ovat helppolukuisia

Kun käytät kaavaa, joka sisältää monia sisäkkäisten IF -tasojen tasoja, voi olla vaikeaa pitää asiat suorana. Koska Excel ei välitä kaavojen tyhjästä välilyönnistä (esim. Ylimääräisistä välilyönneistä tai rivinvaihdoista), voit parantaa sisäkkäisten ifien luettavuutta huomattavasti lisäämällä rivinvaihtoja.

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

Sisäkkäisiä IF -viivoja ilman rivinvaihtoja on vaikea lukea

Kuitenkin, jos lisään rivinvaihdot ennen jokaista arvoa 'false if false', kaavan logiikka hyppää selkeästi esiin. Lisäksi kaavaa on helpompi muokata:

Rivivälit helpottavat sisäkkäisten IF -tiedostojen lukemista

Voit lisätä rivinvaihtoja Windowsissa näppäinyhdistelmällä Alt + Enter, Macissa käytä Control + Option + Return.

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

13. Rajoita IF: tä AND- ja OR -painikkeilla

Sisäkkäiset IF: t ovat tehokkaita, mutta niistä tulee monimutkaisia ​​nopeasti, kun lisäät tasoja. Yksi tapa välttää lisää tasoja on käyttää IF: tä yhdessä AND- ja OR -toimintojen kanssa. Nämä toiminnot palauttavat yksinkertaisen TRUE/FALSE -tuloksen, joka toimii täydellisesti IF: n sisällä, joten voit käyttää niitä yhden IF: n logiikan laajentamiseen.

Esimerkiksi alla olevassa tehtävässä haluamme sijoittaa 'x' sarakkeeseen D merkitsemään rivit, joissa väri on 'punainen' ja koko on 'pieni'.

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

Voisimme kirjoittaa kaavan kahdella sisäkkäisellä IF: llä seuraavasti:

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

Korvaamalla testi AND -funktiolla voimme kuitenkin yksinkertaistaa kaavaa:

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

Samalla tavalla voimme helposti laajentaa tätä kaavaa OR -funktiolla, jotta voimme tarkistaa punaisen tai sinisen JA pienen:

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

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

Video: JOS tämä TAI se

14. Korvaa sisäkkäiset IF: t VLOOKUPilla

Kun sisäkkäinen IF yksinkertaisesti määrittää 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ällytetty IF vs VLOOKUP

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

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

Katso myös: 23 asiaa tietää VLOOKUPista

Video: Kuinka käyttää VLOOKUPia

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

15. Valitse VALITSE

VALITSE -toiminto voi tarjota tyylikkään ratkaisun, kun haluat kartoittaa yksinkertaisia, peräkkäisiä numeroita (1,2,3 jne.) Mielivaltaisiin arvoihin.

Alla olevassa esimerkissä CHOOSE -toimintoa käytetään mukautettujen viikonpäivien lyhenteiden luomiseen:

Sisällytetty IF vs VALITSE -toiminto

Varmasti sinä voisi Käytä pitkää ja monimutkaista sisäkkäistä IF: tä tekemään sama asia, mutta älä :)

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

Jos käytät Excel 2016: ta Office 365: n kautta, voit käyttää uutta toimintoa sisäkkäisten IF: iden sijasta: IFS -funktiota. IFS -toiminto tarjoaa erityisen rakenteen useiden ehtojen arvioimiseksi ilman pesiminen:

IFS -toiminto - useita ehtoja ilman sisäkkäisyyttä

Yllä 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 vanhemmassa Excel -versiossa? Excel 2013: ssa ja 2010: ssä (ja uskon Excel 2007: n, mutta en voi testata) näet '_xlfn.' liitetään IFS: ään solussa. Aiemmin laskettu arvo on edelleen olemassa, mutta jos jokin saa kaavan laskemaan uudelleen, näet #NAME -virheen. Microsoftilla on lisätietoja täältä .

17. Max ulos

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

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

 
= MAX (calculation,0)

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

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

18. Trap -virheet IFERRORilla

Klassinen IF -käyttö on virheiden sieppaaminen ja toisen tuloksen antaminen virheen sattuessa, kuten tämä:

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

Tämä on rumaa ja tarpeetonta, koska sama kaava menee kahdesti sisään, ja Excelin on laskettava sama tulos kahdesti, jos virhettä ei ole.

Excel 2007: ssä otettiin käyttöön IFERROR -toiminto, jonka avulla voit vangita virheet paljon tyylikkäämmin:

 
= IFERROR (formula,error_result)

Kun kaava antaa virheen, IFERROR palauttaa antamasi arvon.

19. Käytä boolen logiikkaa

Voit myös joskus välttää sisäkkäisiä IF: itä käyttämällä ns. Boolen logiikkaa. Sana boolean viittaa TRUE/FALSE -arvoihin. Vaikka Excel näyttää sanat TOSI ja EPÄTOSI soluissa, Excel pitää sisäisesti TOSI -arvoa 1 ja EPÄTOSI nollana. Voit käyttää tätä tosiasiaa fiksujen 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 logiikan avulla voit kirjoittaa kaavan uudelleen näin:

 
=(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 se on totta'. Koska testit palauttavat joko TOSI tai EPÄTOSI (1 tai 0), EPÄTOSI -tulokset mitätöivät itse kaavan.

Numeerisia tuloksia varten boolen logiikka on yksinkertainen ja erittäin nopea, koska haarautumista ei ole. Huonona puolena boolean logiikka voi olla hämmentävää ihmisille, jotka eivät ole tottuneet näkemään sitä. Silti se on loistava tekniikka tietää.

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

Milloin tarvitset sisäkkäisen IF: n?

Kaikkien näiden vaihtoehtojen avulla vältät sisäkkäiset IF: t ja saatat ihmetellä, milloin on järkevää käyttää sisäkkäistä IF: tä?

Uskon, että sisäkkäisissä IF -tiedostoissa 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:

Laskun tilan laskeminen sisäkkäisellä IF: llä

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

Sinun ajatuksesi?

Entä sinä? Oletko IF-sterri? Vältätkö sisäkkäisiä IF: itä? Ovatko sisäkkäiset IF: t pahoja? Jaa ajatuksesi alla.

Opi Excel -kaavat nopeasti tiivis videokoulutus . Kirjailija Dave Bruns


^