Yadda za a yi Nest Multiple IF ayyuka a Excel

01 na 06

Yaya Ayyukan Shafuka na Nested Aiki suke

Amfani da aikin IF yana iya kara ta hanyar sakawa ko yin amfani da ayyukan IF da yawa a cikin juna.

Nested IF ayyuka ƙãra yawan yiwuwar yanayi da za a iya gwada don kuma ƙara yawan ayyuka da za a iya dauka don magance wadannan sakamakon.

Sanyoyin Excel na kwanan nan sun bada izinin ayyukan 64 IF a cikin ɗayan, yayin da Excel 2003 da baya sun yarda da bakwai kawai.

Nesting IF aikace-aikace Tutorial

Kamar yadda aka nuna a cikin hoton da ke sama, wannan koyaswar yana amfani da ƙididdiga biyu na IF don ƙirƙirar ƙirar da ta ƙididdige adadin kuɗi na shekara-shekara don ma'aikata bisa ga albashi na shekara.

Ma'anar da aka yi amfani da shi a cikin misali an nuna a kasa. Ayyukan aikin IF wanda aka haɓaka aiki a matsayin ƙimar ƙimar ƙimar don aikin farko na IF.

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

Ana rarraba sassa daban-daban na dabarar ta tarho kuma gudanar da ayyuka masu biyowa:

1. Sashi na farko, D7, yana duba idan ma'aikacin ma'aikata ba shi da kasa da $ 30,000
2. Idan haka ne, tsakiyar ɓangaren, $ D $ 3 * D7 , ya haɓaka albashi ta raguwar kashi 6%
3. Idan ba haka bane, aikin na biyu na IF: IF (D7> = 50000, $ D $ 5 * D7, D $ D $ 4 * D7) na gwada gwaji guda biyu:
   • D7> = 50000 , dubawa idan albashin ma'aikaci ya fi ko kuma daidai da $ 50,000
   • Idan haka ne, $ D $ 5 * D7 yana haɓaka albashi ta hanyar raguwar kashi 10%
   • Idan ba haka bane, $ D $ 4 * D7 yana haɓaka albashi ta hanyar ragu na 8%

Shigar da Bayanan Tutorial

Shigar da bayanai a cikin sel C1 zuwa E6 na aikin aiki na Excel kamar yadda aka gani a cikin hoto a sama.

Bayanan da ba a shigar ba a wannan lokaci shine aikin IF wanda yake cikin tantanin halitta E7.

Ga wadanda ba sa son bugawa, bayanai da umarnin don kwashe shi zuwa Excel suna samuwa a wannan haɗin.

Lura: Umurni don kwashe bayanan ba su haɗa da matakan tsarawa don aikin aiki ba.

Wannan ba zai dame shi ba tare da kammala tutorial. Kayan aikinku yana iya bambanta da misalin da aka nuna, amma aikin IF zai ba ku sakamakon wannan.

02 na 06

Fara Ayyukan IF wanda aka Nested

Ko da yake yana yiwuwa don kawai shigar da cikakken tsari

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

cikin cikin cell E7 na takardun aiki kuma yana aiki, sau da yawa sauƙaƙa don amfani da maganganun maganganun don shigar da muhawarar da ake bukata.

Yin amfani da akwatin maganganu yana da mahimmanci lokacin shigar da ayyuka da aka haɓaka saboda aikin da aka haɓaka dole ne a buga shi. Ba za'a iya buɗe akwatin zance ta biyu ba don shigar da saiti na biyu na muhawara.

Domin wannan misali, aikin IF wanda aka kafa shi ne zai shiga cikin layi na uku na akwatin maganganun a matsayin darajar Value_if_false .

• Tun da takardun aiki ya ƙayyade ƙididdigar shekara-shekara ga ma'aikata da yawa, za'a fara yin amfani da wannan ƙirar zuwa cikin ɗakunan E7 guda daya ta amfani da cikakkun bayanai akan layin da aka cire don cirewa sannan kuma a kwafa shi a cikin kwayoyin E8: E11.

Tutorial Steps

1. Danna kan wayar E7 don sa shi tantanin halitta mai aiki. - wurin da aka samo asali na IF.
2. Danna kan Rubutun shafin shafin rubutun
3. Danna kan alamar Abubuwan da ke cikin maɓallin keɓaɓɓen jerin abubuwan da aka saukar.
4. Danna IF a cikin jerin don kawo akwatin maganganun aikin.

Bayanan da aka shiga cikin layi a cikin akwatin maganganu ya ƙunshi muhawarar aikin IF.

Wadannan muhawara suna nuna aikin yanayin da ake gwadawa kuma abin da za a dauki idan yanayin ya kasance gaskiya ne ko karya.

Zaɓin Ƙarin gajeren Hanya

Don ci gaba da wannan misali, za ka iya

• shigar da muhawara a cikin akwatin maganganu kamar yadda aka nuna a cikin hoton da ke sama sannan ka yi tsalle zuwa mataki na ƙarshe wanda ya kayyade kallon wannan tsari zuwa layuka 7 zuwa 10;
• ko bi bayan shafuka biyar masu zuwa waɗanda ke ba da cikakken bayani da bayani don shigar da muhawara uku.

03 na 06

Shigar da Magana na Logical_test

Shawarar Logical_test a koyaushe kwatanta tsakanin abubuwa biyu na bayanai. Wannan bayanan na iya zama lambobi, tantancewar sel , sakamakon samfurori, ko ma bayanan rubutu.

Don kwatanta dabi'u biyu, Logical_test yana amfani da mai daidaitawa tsakanin masu kirkiro.

A cikin wannan misali, akwai matakai uku na albashi da ke ƙayyade ƙididdigar shekara-shekara na ma'aikaci.

• kasa da $ 30,000
• tsakanin $ 30,000 da $ 49,999
• $ 50,000 ko fiye

Ayyukan guda guda IF suna iya kwatanta matakan biyu, amma matakin uku na uku shine buƙatar amfani da aikin IF na biyu.

Samun farko zai kasance tsakanin albashin ma'aikaci na shekara-shekara, wanda yake cikin tantanin halitta D, tare da albashin kofa na $ 30,000.

Tun da manufar shine don ƙayyade idan D7 ba kasa da $ 30,000 ba, ana amfani da "<" ana amfani da ƙananan aiki "tsakanin" tsakanin dabi'un.

Tutorial Steps

1. Danna maɓallin Logical_test cikin akwatin maganganu
2. Danna kan D7 DD don ƙara wannan tantanin halitta zuwa layin Logical_test
3. Latsa ƙasa da maɓallin "<" a kan keyboard
4. Rubuta 30000 bayan kasa da alama
5. Gwajin gwaji na kammala ya kamata ya karanta: D7 <30000

Lura: Kada ku shiga alamar dollar ($) ko mai raba takamaiman (,) tare da 30000.

Saƙon kuskure mara inganci zai bayyana a ƙarshen hanyar Logical_test idan an shigar da waɗannan alamomin tare da bayanan.

04 na 06

Shigar da Magana na Value_if_true

Shaidar Value_if_true ta nuna aikin IF game da abin da za a yi a lokacin da Logical_test gaskiya ne.

Tambayar Value_if_true na iya zama wata maƙirafi, wani toshe na rubutu, darajar , tantancewar salula , ko tantanin halitta za'a iya barin barci.

A cikin wannan misali, lokacin da bayanai a cikin tantanin halitta D7 ba kasa da $ 30,000 ba. Excel ta ninka aikin albashi na ma'aikaci a cikin tantanin halitta D7 ta hanyar raguwar kashi 6% cikin cell D3.

Abokan zumunta da vs Sakamakon Sakamakon Ƙarshe

Yawancin lokaci, idan aka kofe wata takarda zuwa wasu kwayoyin jikokin da ke tattare da su a cikin wannan tsari ya canza don yin la'akari da sabon tsarin. Wannan yana sa sauƙin amfani da wannan tsari a wurare masu yawa.

Lokaci-lokaci, duk da haka, yana da fassarar layi lokacin da aka kwafi aikin zai haifar da kurakurai.

Don hana wadannan kurakurai, ana iya sanya ma'anar tantanin halitta a matsayin cikakke wanda ya hana su canzawa lokacin da aka kofe su.

Ana danganta cikakkun labaran ƙwayoyin salula ta hanyar ƙara alamomin alamomi a kusa da tantanin halitta na yau da kullum, kamar $ D $ 3 .

Ƙara ƙarin alamun dollar ana sauƙaƙe ta latsa maɓallin F4 a kan keyboard bayan an shigar da tantanin halitta a cikin akwatin maganganu.

A cikin misalin, ragowar cirewa a cikin tantanin halitta D3 an shigar da shi azaman cikakkiyar tantancewar kwayar halitta cikin darajar Value_if_true na akwatin maganganu.

Tutorial Steps

1. Danna maɓallin Value_if_true a cikin akwatin maganganu
2. Danna kan tantanin D3 a cikin takardun aiki don ƙara wannan tantanin halitta zuwa darajar Value_if_true
3. Latsa maɓalli F4 a kan keyboard don yin D3 cikakkiyar ƙididdigar ƙira ($ D $ 3)
4. Danna maɓallin alama ( * ) a kan keyboard - da alama shine alamar ƙaddamarwa a Excel
5. Danna kan D7 na D7 don ƙara wannan tantancewar salula zuwa darajar Value_if_true
6. Lissafi na Value_if_true ya cika: $ D $ 3 * D7

Lura : D7 ba'a shiga a matsayin cikakkiyar tantancewar sel ba saboda yana buƙatar canzawa lokacin da aka kofe takaddar zuwa jujjuya E8: E11 don samun adadin haɓaka daidai ga kowane ma'aikaci.

05 na 06

Shigar da Ayyukan NI na Nested kamar darajar Value_if_false

Yawanci, ƙimar Value_if_false ta nuna aikin IF game da abin da za a yi lokacin da Logical_test yayi ƙarya, amma a wannan yanayin, ana shigar da aikin IF wanda aka shigar a matsayin wannan gardama.

Ta hanyar yin hakan, sakamakon da ke faruwa yana faruwa:

• Shawarar Logical_test a aikin aikin IF ( D7> = 50000 ) ya gwada duk albashin da ba su da kasa da $ 30,000.
• Don wadanda albashi mafi girma ko kuma daidai da $ 50,000 da ƙimar Value_if_true ta ninka su ta hanyar raguwar kashi 10% cikin cell D5.
• Ga sauran albashin da suka rage - wadanda suka fi $ 30,000 amma kasa da $ 50,000 - hujjar Value_if_false ta ninka su ta hanyar raguwar kashi 8% cikin sel D4.

Tutorial Steps

Kamar yadda aka ambata a farkon tutorial, ba za a iya bude akwatin maganganu na biyu don shigar da aikin da aka saka ba saboda haka dole ne a danna shi cikin darajar Value_if_false.

Lura: ayyukan da aka haɓaka ba su fara da alamar daidai ba - amma tare da sunan aikin.

1. Danna maɓallin Value_if_false cikin akwatin maganganu
2. Shigar da aikin IF din nan
   IF (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7)
3. Danna Ya yi don kammala aikin IF kuma rufe akwatin maganganu
4. Darajar $ 3,678.96 ya kamata ya bayyana a cell E7 *
5. Lokacin da ka danna kan tantanin halitta E7, cikakken aikin
   = IF (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7))
   ya bayyana a cikin maɓallin tsari a sama da takardun aiki

* Tun lokacin da R. Holt ya sami fiye da $ 30,000 sai dai kasa da $ 50,000 a kowace shekara, ana amfani da tsarin $ 45,987 * 8% don ƙididdige ƙididdigar shekara-shekara.

Idan an bi dukkan matakai, misali ɗinka ya dace a halin yanzu ya dace da siffar farko a cikin wannan labarin.

Mataki na karshe ya bukaci yin kwafin tsari na IF zuwa kwayoyin E8 zuwa E11 ta yin amfani da maƙallan cika don kammala aikin aiki.

06 na 06

Kashe Ayyukan Nasu na Nested tare da Amfani da Cikawa

Don kammala aikin aiki, dole ne a kwafi tsarin da ke dauke da aikin IF wanda aka shigar da shi a cikin kwayoyin E8 zuwa E11.

Yayin da aka kwafi aikin, Excel zai sabunta hankulan dangin dan adam don nuna alamar sabon aikin yayin kula da cikakkiyar tantancewar kwayar halitta.

Ɗaya hanya mai sauƙi don kwafe takardu a cikin Excel yana tare da Fill Handle.

Tutorial Steps

1. Danna kan wayar E7 don sa shi tantanin halitta mai aiki .
2. Sanya maƙallan linzamin kwamfuta a kan kusurwar baki a cikin kusurwar dama na ɓangaren mai aiki. Maɗin zai canza zuwa alamar "+".
3. Danna maɓallin linzamin hagu kuma jawo ƙoshin da aka cika a cikin cell E11.
4. Saki da maɓallin linzamin kwamfuta. Za a cika sassan E8 zuwa E11 tare da sakamakon wannan tsari kamar yadda aka nuna a cikin hoto a sama.