Momwe Mungagwirire Ntchito Zambiri za IF mu Excel

01 ya 06

Momwe Ntchito Zogwirira Ntchito IFI Zimagwira Ntchito

Kuwathandiza kwa ntchito ya IF kungaperekedwe mwa kuika kapena kudyetsa ma fomu ambiri mkati mwa wina ndi mzake.

Nested IF ikuwonjezera kuchuluka kwa zinthu zomwe zingathe kuyesedwa ndikuwonjezera chiwerengero cha zochita zomwe zingatengedwe kuti zithetsedwe ndi zotsatirazi.

Zolemba zatsopano za Excel zimapereka 64 IF IF ikugwira ntchito kuti ikhale yodzala pakati, pamene Excel 2003 ndi kale zinaloledwa zisanu ndi ziwiri zokha.

Kumanga IFUTO Yophunzira

Monga momwe tawonetsera pa chithunzi pamwambapa, phunziroli limagwiritsa ntchito zigawo ziwiri zokha zapakhomo kuti zikhazikitse chiwerengero chotsatira chomwe chimawerengetsera ndalama zomwe zimaperekedwa pachaka kwa antchito malinga ndi malipiro awo pachaka.

Fomu yomwe imagwiritsidwa ntchito mu chitsanzo ikuwonetsedwa pansipa. Nchito yodetsedwa ya IF IFI imakhala ngati ndondomeko ya mtengo_if_if_false ya IF IF yoyamba ikugwira ntchito.

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

Mbali zosiyana za ndondomekoyi zimasiyanitsidwa ndi makasitomala ndikuchita ntchito zotsatirazi:

1. Gawo loyambirira, D7, limayang'anitsitsa kuona ngati malipiro a wantchito ali osachepera $ 30,000
2. Ngati ziri, gawo lapakati, $ D $ 3 * D7 , kuchulukitsa malipiro ndi kuchuluka kwa chiwongoladzanja cha 6%
3. Ngati palibe, yachiwiri IF igwira ntchito: IF (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * * D7) mayeso awiri ena:
   • D7> = 50000 , amayesa kuwona ngati malipiro a antchito akuposa kapena akufanana ndi $ 50,000
   • Ngati ziri, $ D $ 5 * D7 imachulukitsa malipiro ndi chiwongoladzanja cha 10%
   • Ngati sichoncho, $ D $ 4 * D7 ikuchulukitsa malipiro ndi chiwongoladzanja cha 8%

Kulowa Datorial Data

Lowani deta mu maselo C1 mpaka E6 a pepala la Excel monga momwe taonera pa chithunzi pamwambapa.

Deta yokha yomwe siinalembedwe pano ndi IF function yokha yomwe ili mu selo E7.

Kwa iwo omwe samawoneka ngati akuyimira, chidziwitso ndi malangizo omwe mukuzifanizira izo mu Excel zimapezeka pachigwirizano ichi.

Zindikirani: Malangizo okopera deta samaphatikizapo kupanga mapangidwe a tsamba.

Izi sizidzasokoneza kukwaniritsa maphunziro. Tsamba lanu la ntchito likhoza kuwoneka mosiyana ndi chitsanzo chowonetsedwa, koma ntchito ya IF ikakupatsani zotsatira zomwezo.

02 a 06

Kuyambira Ntchito Yothandizira IF

Ngakhale kuti n'zotheka kungolemba njira yonseyo

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

kulowa mu selo E7 ya worksheet ndikugwira ntchito, nthawi zambiri zimakhala zosavuta kugwiritsa ntchito bokosi la polojekiti ya ntchito kuti ikhale ndi zifukwa zofunika.

Kugwiritsira ntchito bokosili ndizowonjezera pokhapokha mutalowa chisa chifukwa ntchito yodetsedwa iyenera kuikidwa mkati. Bokosi lachiwiri lachidule silingatsegulidwe kuti lilowetse mndandanda wachiwiri wa zifukwa.

Kwa chitsanzo ichi, ntchito yodetsedwa ya IF ina idzalowa mu gawo lachitatu la bokosi la funso ngati Value_if_false argument.

• Popeza tsambali limapereka chiwongoladzanja chaka chilichonse kwa antchito angapo, njirayi idzayamba kulowa mu selo imodzi E7 pogwiritsa ntchito ma selo omwe amachokera pafupipafupi ndikuikopera ku maselo E8: E11.

Maphunziro Otsogolera

1. Dinani pa selo E7 kuti mupange selo yogwira ntchito. - malo amtundu wa IF IF.
2. Dinani pa Fomu tab ya riboni
3. Dinani pa chithunzi cha Logical chitsegule ntchitoyi.
4. Dinani ngati IF mu mndandanda akubweretsa bokosi lazokambirana.

Detayi inalowa mndandanda wosalongosola mu bokosi la zokambirana zomwe zimayambitsa mfundo za IF.

Zokambirana izi zimapangitsa ntchitoyi kukhala chikhalidwe choyesera ndi zomwe angachite ngati mkhalidwe uli woona kapena wabodza.

Njira Yotsatsa Njira Yophunzitsa

Kuti mupitirize ndi chitsanzo ichi, mungathe

• lowetsani ziganizo mu bokosi lakulankhulira monga momwe tawonetsera pa chithunzi pamwambapa ndiyeno tulukani ku sitepe yotsiriza yomwe ikuphimba kukopera njirayi mpaka 7 mpaka 10;
• kapena kutsatila masamba asanu otsatirawa omwe amapereka malangizo ndi ndondomeko zowonjezereka zowonjezera mfundo zitatu.

03 a 06

Kulowa mu Logical_test Kutsutsana

Kukangana kwa Logical_test nthawi zonse kufanana pakati pa zinthu ziwiri za deta. Deta iyi ikhoza kukhala nambala, mafotokozedwe a selo , zotsatira za malemba, kapena ngakhale deta.

Poyerekeza mfundo ziwiri, Logical_test amagwiritsa ntchito oyerekeza pakati pa mfundo.

Mu chitsanzo ichi, pali magawo atatu a malipiro omwe amaonetsetsa kuti wogwira ntchitoyo wapatsidwa pachaka.

• zosakwana $ 30,000
• pakati pa $ 30,000 ndi $ 49,999
• $ 50,000 kapena kuposa

Ntchito imodzi yokha ya IF ingafanane ndi magawo awiri, koma gawo lachitatu la malipiro likufuna kugwiritsa ntchito ntchito yachiwiri ya chisa cha IF.

Kuyerekeza koyamba kudzakhala pakati pa malipiro a pachaka a antchito, omwe ali mu selo D, ndi malire a $ 30,000.

Popeza cholinga chake ndi kudziwa ngati D7 ilibe ndalama zokwana madola 30,000, Ochepa kuposa amene amagwiritsa ntchito "<" amagwiritsidwa ntchito pakati pa miyezo.

Maphunziro Otsogolera

1. Dinani pa Logical_test mzere mu bokosi la dialog
2. Dinani pa selo D7 kuti muwonjezere gawo la seloyi ku Logical_test mzere
3. Onetsani makina osachepera "<" pamakinawo
4. Lembani 30000 pambuyo poyerekeza ndi chizindikiro
5. Mayeso omaliza omveka ayenera kuwerenga: D7 <30000

Dziwani: Musalowe chizindikiro cha dola ($) kapena wogawa wotsatsa (,) ndi 30000.

Uthenga wosayenerera wosayenerera udzawonekera kumapeto kwa Logical_test mzere ngati chimodzi mwa zizindikiro izi zalowa pamodzi ndi deta.

04 ya 06

Kulowa Phindu_ng_kutsutsana Kwowona

Mtengo_m_maganizo amodzi akuwuza ntchito IF ngati chochita pamene Logical_test ndi yoona.

Mtengo_m_mtsutso weniweni ukhoza kukhala ndondomeko, chipika cha malemba, mtengo , selo , kapena selo zingasiyidwe chopanda kanthu.

Mu chitsanzo ichi, pamene deta yomwe ili mu selo D7 imachepera $ 30,000. Excel imachulukitsa malipiro a wothandizira pachaka mu selo D7 ndi kuchuluka kwa chiwongoladzanja cha 6% chomwe chili mu selo D3.

Zotsatira Zake ndi Zomwe Zili M'ma Cell

Kawirikawiri, pamene cholembera chimakopizidwa ku maselo ena mafotokozedwe am'thupi omwe ali nawo mu kusintha kwasinthidwe kuti awonetse malo atsopanowo. Izi zimapangitsa kuti zikhale zosavuta kugwiritsa ntchito njira yomweyo mmalo osiyanasiyana.

Nthawi zina, kukhala ndi ma selo akusintha pamene ntchito ikukopeka kudzapangitsa zolakwika.

Kuti tipewe zolakwika izi, mafotokozedwe a selo angathe kupangidwa kukhala Mtheradi omwe amawaletsa kuti asasinthe pamene amalembedwa.

Mafotokozedwe osaphatikizapo a maselo amapangidwa powonjezera zizindikiro za dollar kuzungulira maselo, monga $ D $ 3 .

Kuwonjezera zizindikiro za dollar kumachitika mophweka mwa kukanikiza fini ya F4 pa khibhodi pambuyo powerenga selolololololololedwa mu bokosi la bokosi.

Mu chitsanzo, chiwerengero cha kuchepetsa chomwe chili mu selo D3 chatsekedwa monga mndandanda wa maselo mu Value_if_wowona mzere wa bokosi la bokosi.

Maphunziro Otsogolera

1. Dinani pa Value_if_njira yovuta mu bokosi la bokosi
2. Dinani pa selo D3 mu pepala lothandizira kuti muwonjezere chiwerengero cha seloyi ku Value_if_nthano yowona
3. Dinani fini F4 pa kibokosi kuti D3 ikhale yeniyeni yeniyeni ($ D $ 3)
4. Pewani makiyi a * asterisk ( * ) pa makiyi - asterisk ndi chizindikiro chochulukitsa mu Excel
5. Dinani pa selo D7 kuti muwonjezere gawo la seloli ku Value_if_wowona
6. Mzere wodalirika wa Value_if_true uyenera kuwerenga: $ D $ 3 * D7

Zindikirani : D7 siyinalowetsedwe ngati maselo oyenera chifukwa imayenera kusintha pamene malembawo akukopedwa ku maselo E8: E11 kuti apeze ndalama zoyenera kuchotsera kwa wogwira ntchito aliyense.

05 ya 06

Kulowa ntchito ya NESED Nested monga mtengo_if_fotokozera mkangano

Kawirikawiri, ndemanga ya Value_if_if_false imanena kuti ntchito ya IF iyenera kuchita chiyani pamene Logical_test ndi yabodza, koma pakadali pano, ntchito yothandizira ya IF isalowetsedwe ngati ndemanga iyi.

Pochita izi, zotsatira zotsatirazi zikuchitika:

• Mndandanda wa Logical_test in the Nested IF function ( D7> = 50000 ) kuyesa malipiro onse osachepera $ 30,000.
• Kwa malipiro awo akuluakulu kapena ofanana ndi $ 50,000, mfundo ya Value_if_nowonjezereka imawonjezereka ndi kuchuluka kwa chiwongoladzanja cha 10% chomwe chili mu selo D5.
• Kwa malipiro otsalira - omwe ali oposa $ 30,000 koma osachepera $ 50,000 - Kukambirana kwa_kufunika_kupindulitsa kumawonjezereka ndi kuchuluka kwa chiwongoladzanja cha 8% chiri mu selo D4.

Maphunziro Otsogolera

Monga tafotokozera kumayambiriro kwa phunzirolo, bokosi lachiwiri lachidwi silingatsegulidwe kuti lilowetse chisa chake kotero liyenera kuyimilidwa mu mndandanda wa Value_if_false.

Zindikirani: ntchito zadothi siziyambe ndi chizindikiro chofanana - koma ndi dzina la ntchito.

1. Dinani pa Value_if_false mzere mu bokosi la bokosi
2. Lowani zotsatirazi zotsatirazi IF
   Ngati (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7)
3. Dinani OK kuti mutsirize ntchito YAPI ndi kutseka bokosi la bokosi
4. Mtengo wa $ 3,678,96 uyenera kuwonekera mu selo E7 *
5. Mukasindikiza pa selo E7, ntchito yonse
   = IF (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7))
   imapezeka mu bar lamuzula pamwamba pa tsamba

* Popeza R. Holt amapereka ndalama zoposa $ 30,000 koma osachepera $ 50,000 pa chaka, $ 45,987 * 8% amagwiritsidwa ntchito powerenga kuchotsedwa kwake pachaka.

Ngati ndondomeko zonse zatsatiridwa, chitsanzo chanu chiyenera kufanana ndi chithunzi choyamba mu nkhaniyi.

Gawo lomaliza limaphatikizapo kukopera fomu ya IF ngati maselo E8 mpaka E11 pogwiritsira ntchito chodzaza kuti mutsirize pepala lolemba.

06 ya 06

Kujambula Ntchito za NFI zomwe zili ndi Nested pogwiritsa ntchito Dzanja Lodzaza

Pofuna kumaliza pepalali, fomu yomwe ili ndi ndondomeko ya chitukuko cha IF iyenera kukopera ku maselo E8 mpaka E11.

Pamene ntchitoyo imakopedwa, Excel idzasintha mafotokozedwe ofanana omwe amasonyeza kuti malowa ndi malo atsopano pamene akusunga selo lofanana.

Njira imodzi yosavuta kufotokozera mayina mu Excel ili ndi Ntchito Yodzaza.

Maphunziro Otsogolera

1. Dinani pa selo E7 kuti mupange selo yogwira ntchito .
2. Ikani pointeru ya mbewa pamtunda wakuda kumbali yakumanja ya selo yogwira ntchito. Pointer idzasintha ku chizindikiro chowonjezera "+".
3. Dinani batani lamanzere lachitsulo ndikukakaniza kulembetsa pansi pa selo E11.
4. Tulutsani batani la mouse. Maselo E8 mpaka E11 adzadzazidwa ndi zotsatira za fomu monga momwe zasonyezedwera mu chithunzi pamwambapa.