Nzira yeNest Multiple IF Functions muExpcel

01 of 06

Iyo yakagadziriswa IF Imabasa inoshanda

Kubatsira kwebasa reF IF kunogona kuwedzerwa nekuisa kana kuvhara kazhinji IF inoshanda mukati memumwe.

Nested IF inobatsira kuwedzera nhamba yemamiriro ezvinhu anokwanisa kuongororwa uye kuwedzerwa nhamba yezviito zvingatorwa kutarisana nemigumisiro iyi.

Shanduro dzekupedzisira dze Excel dzinobvumira 64 IF iyo inoshanda kuti ive nendere mukati memumwe, asi Excel 2003 uye yapfuura yakabvumira zvinomwe chete.

Nesting IF Imabasa Tutori

Sezvinoratidzwa mumufananidzo wepamusoro, iyi dzidzo inoshandisa mairi maviri eF IF inoshandisa kuumba iyo inotevera inoshandiswa iyo inoyera chiyero chekubviswa kwegore negore kune vashandi vanobva pamutengo wavo wegore negore.

Shanduro inoshandiswa mumuenzaniso inoratidzwa pasi apa. Nzvimbo yakaitwa IF iyo basa inofanana ne-value_if_false argument yekutanga IF iyo.

= IF (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7))

Zvikamu zvakasiyana zvemuumbi wacho zvakaparadzaniswa nemagetsi uye kuita mabasa anotevera:

1. Chikamu chekutanga, D7, chinotarisa kuona kana mushandi wemushandi ari pasi pe $ 30,000
2. Kana iri, chikamu chepakati, $ D $ 3 * D7 , inowedzera mubhadharo kuburikidza nekuderedzwa kwema 6%
3. Kana iyo isiri iyo, yechipiri IF inoshanda: IF (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7) miedzo miviri mimwe mitemo:
   • D7> = 50000 , anoongorora kuti aone kana mushandi wemushandi mukuru kudarika kana akaenzana ne $ 50,000
   • Kana iri, $ D $ 5 * D7 inowedzera mubhadharo nehuwandu hwekuderedzwa hwe 10%
   • Kana isiri iyo, $ D $ 4 * D7 inowedzera mubhadharo nekuderedzwa kwechikamu che8%

Kupinda Tutorial Data

Isa data mumasero C1 kusvika E6 epa Excel worksheet sezvinoonekwa mumufananidzo uri pamusoro.

Iyo chete data isina kupinda panguva ino ndeyo IF iyo basa pachayo iri muchitokisi E7.

Kune avo vasinganzwi kufarira kuverenga, deta uye mirayiridzo yekunyoresa kuExcel inowanikwa pane iyi link.

Cherechedza: Mirayiridzo yekukopa deta haisanganisi matanho ekufambisa kwepepaji.

Izvi hazvizovhiringidzi nekupedzisa tutori. Shanduro yako yebasa inogona kutarisa kusiyana nemuenzaniso wakaratidzwa, asi iyo IF basa inokupa mhinduro imwe chete.

02 of 06

Kutanga Nested IF Function

Kunyange zvazvo zvichibvira kungopinda mazano akazara

= IF (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7))

muchitokisi E7 chebasa rekushanda uye kuti iite basa, kazhinji kazhinji nyore kushandisa sarudzo yebhokisi rebasa kuti uise mhinduro dzinodikanwa.

Kushandisa bhokisi rebhokisi rinenge rakanyanyisa pakunyorera nheyo mabasa nokuti basa rakasvibiswa rinofanira kuiswa mukati. A second dialog box haigoni kuvhurwa kuti iise sechipiri yezvakakavhiringidza.

Pamusana pemuenzaniso uyu, nested IF iyo basa ichapinda mumutsara wechitatu webhokisi rebhokisi se Value_if_false argument.

• Kubva sezvo pepa rekushanda rinotarisa kuderedzwa kwegore negore kune vashandi vanoverengeka, rondedzero inotanga kupinda muchikamu chimwechete E7 uchishandisa masero ekutengesa kwemajeri ekuderedza uye akaikopa kumasero E8: E11.

Tutorial Steps

1. Dzvanya pane cell E7 kuti iite iyo inoshandiswa seki. - nzvimbo yenharaunda yeF IF formula.
2. Dzvanya paMafomu tab yebhoni
3. Kira pa Logical icon vhura basa rinoderedza pasi.
4. Dzvanya kana IF iri muzorongwa kuti ibudise bhokisi rebasa rebasa.

Dhavhidha inopinda mumiganhu isina kuvhara mubhokisi rebhokisi fomu nharo dze IF IF function.

Iyi nharo dzinoudza basa iro mamiriro ekuongorora uye zviito zvekutora kana chiitiko chiri chechokwadi kana chenhema.

Tutorial Shortcut Option

Kuti urambe uine muenzaniso uyu, unogona

• pinda nharo mubhokisi rebhokisi sezvinoratidzwa mumufananidzo uri pamusoro uye wotumira kusvika danho rekupedzisira rinosanganisira kukopa fomu yacho kusvika ku7 kusvika ku10;
• kana kutevera mumapeji mashanu anotevera anopa tsanangudzo yakajeka uye tsananguro yekupinda nharo nhatu.

03 of 06

Kupinda Logical_test Argument

The Logical_test nharo nguva dzose inofananidzwa pakati pezvinhu zviviri zve data. Iyi dheta inogona kuva nhamba, zvinyorwa zvitsanangudzo , zvigumisiro zvemafomu, kana kunyange nhamba yemashoko.

Kuenzanisa maitiro maviri, Logical_test inoshandisa mutengesi wekuenzanisa pakati pehutano.

Mumuenzaniso uyu, kune mitatu mibare yemari inotarisa kushandiswa kwemunhu gore negore.

• pasi pe $ 30,000
• pakati pe $ 30,000 ne $ 49,999
• $ 50,000 kana kupfuura

Imwe nhengo imwechete iyo IF inogona kuenzanisa mazinga maviri, asi yechitatu yehutano yehutano inoda kushandiswa kwechipiri chikafu IF inoshanda.

Kuenzanisa kwekutanga kuchava pakati pemari yevashandi vegore, iri muchitokisi D, uye chikumbaridzo chemari ye $ 30,000.

Sezvo chinangwa chiri chekutarisisa kana D7 iri pasi pe $ 30,000, Muduku pane mutengi "<" anoshandiswa pakati pehutsika.

Tutorial Steps

1. Dzvanya pa Logical_test line mubhokisi rebhokisi
2. Dzvanya pane sero D7 kuti uwedzere kurefera iyi sekero ku Logical_test line
3. Dhinda pasi pasi pekiyi "<" pane keyboard
4. Tora 30000 mushure mekuita zvishoma
5. Chiyero chakakwana chakagadziriswa chinofanira kuverengwa: D7 <30000

Cherechedza: Usapinda mucherechedzo wemadhora ($) kana comparator (,) ne 30000.

Chimwe chisina kukodzera mharidzo yehuyo ichaonekwa pamagumo e Logical_test line kana imwe yezviratidzo izvi inopinda pamwe chete nedata.

04 of 06

Kupinda muKukosha_if_Kutaurirana Kwechokwadi

Zvakakosha_if_zvokwadi inotaurira IF IF basa raunofanira kuita kana Logical_test ichokwadi.

Kukosha_ku_kukakavadzana kwechokwadi kunogona kuva chirevo, bhokisi remavara, kukosha , sero retaundi , kana sero rinogona kusiyiwa risina chinhu.

Mumuenzaniso uyu, apo data iri muchitokisi D7 iri pasi pe $ 30,000. Excel inowedzera mushandi wegore negore mujeri D7 nekuderedzwa kwekutsvaga kwe6% iri muchitokisi D3.

Rudo vs. Absolute Cell References

Kazhinji, apo chimiro chinonyorwa kune mamwe masero seanoshandiswa sezvikamu zvinyorwa mumushanduko unoshandura kuratidza nzvimbo itsva yefomula. Izvi zvinoita kuti zvive nyore kushandisa maitiro akadaro munzvimbo dzakawanda.

Dzimwe nguva, zvisinei, kuva nesero rekushandura kunoshanduka apo basa rakakopihwa richaguma nekukanganisa.

Kuti zvidzivirire zvikanganiso izvi, zvinyorwa zveseti zvinogona kuitwa Absolute izvo zvinovadzivisa kubva pakuchinja kana zvichinyorwa.

Absolute cell references zvinoshandiswa nekuwedzera dhiwarisi zviratidzo zvakakomberedza nguva dzose seferensi, sepa $ D $ 3 .

Kuwedzera dhilar zviratidzo zviri nyore kuburikidza nekusimudzira F4 key pabhobhoyo mushure mokunge sero retaundi rave raiswa mubhokisi rebhokisi.

Muchiitiko chacho, chiyero chekuderedzwa chiri muchitokisi D3 chakapinda sechinyorwa cheseferensi chinenge chiri muRugu_if_true mutsara webhokisi rebhokisi.

Tutorial Steps

1. Dzvanya paIndaneti_if_inzira yechokwadi mubhokisi rebhokisi
2. Dzvanya pane cell D3 mune pepa rekushandisa kuti uwedzere iyi tsero yesero kuRugu_if_kwechokwadi
3. Dhinda f4 key pane keyboard kuitira kuti D3 ive yakazara cell reference ($ D $ 3)
4. Dhinda pikoriki ( * ) kiyi pane keyboard - the asterisk chiratidzo chokuwedzera muExpcel
5. Dzvanya pane sero D7 kuti uwedzere iyi tsananguro yesero kuRi_ku_tambo
6. Iyo yakagadzirwa Value_if_true line inofanira kuverengwa: $ D $ 3 * D7

Cherechedza : D7 haina kupinda semhedzisiro yakakwana nokuti inofanira kuchinja apo iyo fomendi inyorerwa kumasero E8: E11 kuitira kuwana mari yakakwana yokudha yevashandi.

05 of 06

Kupinda Basa reNIFed NIFI seRugu_if_false argument

Kazhinji, iyo Value_if_false argument inotaurira IF basa ravanofanira kuita kana Logical_test isiri, asi munyaya ino, nested IF chinoshanda chinopindirwa sechinhu ichi.

Nokuita kudaro, zvinotevera zvinouya:

• The Logical_test nharo munharaunda ye IF iyo yakasikwa ( D7> = 50000 ) kuedza misha yose isiri pasi pe $ 30,000.
• Kune iyo mibairo yakakura kudarika kana yakaenzana ne $ 50,000 iyo Value_if_true argument inovadzidzisa kuburikidza nekuderedzwa kwekutora kwe 10% iri muchitokisi D5.
• Nokuda kwezvakasara muhomwe - izvo zvakakura kudarika $ 30,000 asi pasi pe $ 50,000 - Value_if_false argument inovawanza nekuderedzwa kwehuwandu hwe8% iri muchitokisi D4.

Tutorial Steps

Sezvakataurwa pakutanga kwekudzidzisa, yechipiri yebhokisi yebhokisi haigoni kuvhurwa kuti iite basa rakasvibiswa kuitira kuti inofanira kuiswa muRugu_if_false line.

Cherechedza: nesteded works do not start with a equal equal - but rather with the name's name.

1. Dzvanya pane Value_if_false mutsara mubhokisi rebhokisi
2. Pinda zvinotevera IF IF function
   KANA (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7)
3. Dzvanya OK kuti uite basa re IF kanavhara bhokisi rebhokisi
4. Kukosha kwe $ 3 678.96 kunofanira kuonekwa muchitokisi E7 *
5. Paunenge uchinyora pane cell E7, basa rakakwana
   = IF (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7))
   inowanikwa mubhairi yepamusoro pane pepa rekushanda

* Sezvo R. Holt achiwana mari inodarika $ 30,000 asi pasi pe $ 50,000 pagore, iyo $ 45,987 * 8% inoshandiswa kuverenga kubviswa kwegore negore.

Kana matanho ose akateverwa, muenzaniso wako unofanirwa kunge uchifananidza nemufananidzo chaiwo munyaya ino.

Nhanho yekupedzisira inosanganisira kukopa iyo IF formula kumasero E8 kusvika E11 uchishandisa gwaro rinogutsa kuti upedze pepa rekushanda.

06 of 06

Kunyora Nested IF Imabasa uchishandisa Fill Handle

Kuti upedze pepa rekushanda, chirevo chine nested IF chinoshanda chinofanira kunyorerwa kumajeri E8 kusvika E11.

Sezvo basa racho rakadhindwa, Excel ichavandudza zvinyorwa zvinyorwa zvinyorwa kuti zviratidze nzvimbo itsva yebasa kana uchichengetedza kushandiswa kwekero yese zvakafanana.

Imwe nzira iri nyore yekukopa mazano mu Excel ndeye Fill Handle.

Tutorial Steps

1. Dzvanya pane cell E7 kuti iite iyo inoshandiswa seki .
2. Isa iyo pointer yeguru pamusoro pekona mutsara muzasi yekona yekona yekasa rinoshanda. Iyo pointer ichashandura kune imwe chiratidzo "+".
3. Dzvanya kumusana webhenekisi mutsara uye wedhonzai kuzadza tsvimbo pasi kune cell E11.
4. Bvisa mutsara. Mitsara E8 kusvika E11 ichazadzwa nemigumisiro yechirongwa sezvinoratidzwa mumufananidzo uri pamusoro.