Indlela Yokukhipha Imisebenzi Eningiyo ye-IF ku-Excel

01 ka-06

Indlela Imisebenzi Ye-IF Ephumelelayo Isebenza

Ukubaluleka komsebenzi we-IF kungadluliselwa ngokufaka noma ukukhiphela ama-IF amaningi imisebenzi phakathi komunye nomunye.

Imisebenzi e-Nested IF ekhulisa inani lemibandela engenzeka ukuthi ingahlolwa futhi yandise inani lezenzo ezingathathwa ukubhekana nalezi ziphumo.

Izinguqulo zakamuva zamuva ze-Excel zivumela imisebenzi engu-64 ye-IF ukuba ibe nendlu ngaphakathi komunye, kuyilapho i-Excel 2003 nangaphambilini ivunyelwe kuphela eziyisikhombisa kuphela.

Ukuhlalisa i-IF Imisebenzi Yokufundisa

Njengoba kuboniswe esithombeni esingenhla, le tutorial isebenzisa imisebenzi emibili nje ye-IF ukudala ifomula elandelayo ebala inani lemali yokudonsa ngonyaka kubasebenzi ngokusekelwe emalini abo ngonyaka.

Ifomula elisetshenziswe kusibonelo iboniswa ngezansi. Umsebenzi we-IF owakhiwe usebenza njenge-value_if_false ingxabano yomsebenzi wokuqala we-IF.

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

Izingxenye ezahlukene zefomula zihlukaniswa ngama-commas futhi zifeze imisebenzi elandelayo:

1. Ingxenye yokuqala, i- D7, ihlola ukuqinisekisa ukuthi umholo wesisebenzi ungaphansi kuka-$ 30,000
2. Uma kunjalo, ingxenye ephakathi, $ D $ 3 * D7 , yandisa umholo ngenani lokudonsa lika-6%
3. Uma kungenjalo, umsebenzi wesibili we-IF: IF (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * * D7) ihlola izimo ezimbili ezengeziwe:
   • I-D7> = 50000 , ihlola ukuqinisekisa ukuthi umholo wesisebenzi ungaphezu noma ulingana no-$ 50,000
   • Uma kunjalo, u-$ D $ 5 * D7 ukwandisa umholo ngezinga lokudonsa lika-10%
   • Uma kungenjalo, u-$ D $ 4 * D7 ukwandisa umholo ngesilinganiso sokudonsa esingu-8%

Ukufaka idatha yokufundisa

Faka idatha kumaseli C1 kuya ku-E6 kwiphepha lokusebenzela le-Excel njengoba kuboniswa esithombeni ngenhla.

Idatha kuphela engangenwa kuleli phuzu yilona umsebenzi we-IF ngokwawo otholakale esitokisini E7.

Kulabo abangacabangi ukuthayipha, idatha kanye nemiyalelo yokuyikopisha ku-Excel iyatholakala kule sixhumanisi.

Qaphela: Imiyalo yokukopisha idatha ayifaki ukufomatha izinyathelo zephepha lokusebenzela.

Lokhu ngeke kuphazamise ukuqedela ukufundisa. Iphepha lakho lokusebenzela lingase libukeke lihlukile kunalokhu kubonisiwe, kodwa umsebenzi we-IF uzokunika imiphumela efanayo.

02 ka 06

Ukuqala ukusebenza komsebenzi we-IF

Nakuba kungenzeka ukufaka nje ifomula ephelele

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

ungene esitokisini E7 sephepha lokusebenzela bese uyasebenza, kuvame ukulula ukusebenzisa ibhokisi lengxoxo lomsebenzi ukufaka izingxabano ezidingekayo.

Ukusebenzisa ibhokisi lengxoxo kungumsebenzi omncane lapho ufaka imisebenzi ehlelekile ngoba umsebenzi ocebile kumele ufakwe kuwo. Ibhokisi lengxoxo yesibili alikwazi ukuvulwa ukuze ufake isethi yesibili yezimpikiswano.

Kulesi sibonelo, umsebenzi we-IF wezintambo uzofakwa engxenyeni yesithathu yebhokisi lebhokisi njenge- Value_if_false argument.

• Njengoba ishidi lokusebenza libala inani lokubanjwa kwabasebenzi abaningana, ifomula izofakwa kuqala esitokisini esisodwa se-E7 isebenzisa ama-cell reference ngokuphelele amazinga okudonsa bese ikopishwa kuwo amaseli E8: E11.

Izinyathelo Zokufundisa

1. Chofoza kuseli E7 ukuze wenze iseli elisebenzayo. - indawo yefomu ye-IF eyakhiwe.
2. Chofoza kuthebhu yefomathi yebhande
3. Chofoza ku-icon ye- Logical uvula uhlu lokudonsa umsebenzi.
4. Chofoza ku- IF ohlwini ukuze ukhulise ibhokisi lengxoxo lomsebenzi.

Idatha efakwe emigqeni engenalutho ebhokisini lebhokisi lezingxoxo izimpikiswano zomsebenzi we-IF.

Lezi zimpikiswano zitshela umsebenzi isimo sokuhlola nokuthi yiziphi izenzo okufanele zithathe uma isimo siyiqiniso noma singamanga.

Option Isinqamuleli Sokufundisa

Ukuqhubeka nalesi sibonelo, unga

• faka izingxabano ebhokisini lebhokisi njengoba kuboniswa esithombeni ngenhla bese usuqa esiteji sokugcina esihlanganisa ukukopisha ifomula kumigqa engu-7 ukuya ku-10;
• noma ulandele emakhasini amahlanu alandelayo enikeza imiyalelo eningiliziwe nezincazelo zokungena ezimpikiswaneni ezintathu.

03 ka 06

Ukufaka i-Logical_test Argument

I-argical Logical_test ihlale iwuqhathanisa phakathi kwezinto ezimbili zedatha. Le datha ingaba izinombolo, izingqikithi zeseli , imiphumela yamafomula, noma ngisho nedatha yombhalo.

Ukuze uqhathanise amanani amabili, i-Logical_test isebenzisa u- opharetha wokuqhathanisa phakathi kwamanani.

Kulesi sibonelo, kunamazinga amathathu omholo okwehlukanisa ukukhishwa kwonyaka.

• ngaphansi kuka-$ 30,000
• phakathi kuka- $ 30,000 no-$ 49,999
• $ 50,000 noma ngaphezulu

Umsebenzi ongashadile we-IF ungaqhathanisa amazinga amabili, kodwa izinga lesithathu leholo lidinga ukusetshenziswa komsebenzi wesibili we-IF ombotshwe.

Ukuqhathaniswa kokuqala kuzoba phakathi kweholo lomsebenzi wonyaka, esesitokisini D, nomholo wesikhwama se-$ 30,000.

Njengoba umgomo ukucacisa ukuthi uma i-D7 ingaphansi kwezingu-30,000, i-Less Than operator "<" isetshenziswa phakathi kwamagugu.

Izinyathelo Zokufundisa

1. Chofoza kulayini we- Logical_test ebhokisini lengxoxo
2. Chofoza kuseli D7 ukuze ungeze le nkomba yeseli kumugqa we- Logical_test
3. Cindezela ngaphansi kwekhiye "<" kukhibhodi
4. Thayipha u- 30000 ngemuva kokungaphansi kwesimboli
5. Ukuhlolwa okuphelele okuphelele kufanele kufundwe: D7 <30000

Qaphela: Ungangenisi isibonakaliso se-dollar ($) noma i-separator comma (,) ne-30000.

Umlayezo wephutha ongavumelekile uzovela ekugcineni komugqa we- Logical_test uma enye yalezi zimpawu ingeniswa nedatha.

04 ka 06

Ukufaka ukungena kwe-Value_if_krue

I- argument Value_if_yiqiniso iyatshela umsebenzi we-IF okufanele ukwenze uma i-Logical_test iqinisile.

I-argument Value_if_yiqiniso ingaba ifomula, ibhokisi lombhalo, inani , isithenjwa seseli , noma iseli lingashiywa lingenalutho.

Kulesi sibonelo, uma idatha eselilini D7 ingaphansi kwezingu-30,000 zamaRandi. I-Excel yandisa umholo wonyaka wesisebenzi kumakhalekhukhwini D7 ngenani lokudonsa lika-6% elisesitokisini D3.

Ama-Relative and Absolute Cell References

Ngokuvamile, lapho ifomula likopishwa kwamanye amaseli izikhombisi zeseli ezihambelana noshintsho lwefomula ukubonisa indawo entsha yefomula. Lokhu kwenza kube lula ukusebenzisa ifomula efanayo ezindaweni eziningi.

Nokho, ngezikhathi ezithile, ukubhekwa kwamangqamuzana lapho kushicilelwe umsebenzi kuzoholela ekupheni.

Ukuze uvimbele la maphutha, izingqikithi zeseli zingenziwa Absolute ezibavimbela ekushintsheni lapho zikopishwa.

Izinkomba ezingenalutho zeseli zidalwa ngokungeza izibonakaliso zeDola ezizungeze isithenjwa seselula njalo, njenge $ D $ 3 .

Ukwengeza izimpawu ze-dollar kwenziwa kalula ngokucindezela ukhiye we- F4 ekhibhodi ngemuva kokuba ireferensi yeseli ingenwe ebhokisini lengxoxo.

Kulesi sibonelo, izinga lokudonsa elisesitokisini se-D3 lifakwe njengesithenjwa seliphelele ngokuphelele ku-Value_if_krue line yebhokisi lengxoxo.

Izinyathelo Zokufundisa

1. Chofoza ku- Value_if_layini umugqa ebhokisini lengxoxo
2. Chofoza kuseli D3 kushidi lokusebenzela ukuze ungeze le nkomba yeselula kulayini le- Value_if_krue
3. Cindezela ukhiye we- F4 ekhibhodi ukwenza i-D3 isithenjwa seliphelele ($ D $ 3)
4. Cindezela inkinobho ye-asterisk ( * ) ekhibhodi - i-asterisk uphawu lokuphindaphinda ku-Excel
5. Chofoza kuseli D7 ukuze ungeze le nkomba yeselula kulayini le- Value_if_krue
6. Umugqa we-Value_if_true ophelile kufanele ufunde: $ D $ 3 * D7

Qaphela : I- D7 ayifakiwe njengenombolo yeselula ngokuphelele ngoba idinga ukushintsha uma ifomula ikopishwa kumaseli E8: E11 ukuze uthole imali efanele yokudonsa isisebenzi ngasinye.

05 ka 06

Ukufaka i-Nested Function njenge-Value_if_false argument

Ngokuvamile, i-Value_if_false argument ithela umsebenzi we-IF okufanele ukwenze uma i-Logical_test ingamanga, kodwa kulokhu, umsebenzi we-IF wezintambo ufakiwe njengale mpikiswano.

Ngokwenza njalo, imiphumela elandelayo ivela:

• Ukuphikisana kwe-Logical_test kumsebenzi we-IF wezinambuzane ( D7> = 50000 ) uvivinya wonke amaholo angaphansi kwama-$ 30,000.
• Kule miholo emikhulu noma elingana no-$ 50,000 i-argument Value_if_yiqiniso ibaphindisela ngokulinganiselwa kokunciphisa kwe-10% esesitokisini se-D5.
• Ngamaholo asele - lawo angaphezulu kuka-$ 30,000 kodwa angaphansi kuka-$ 50,000 - i-argument Value_if_false iwaphindaphinda ngokulinganiselwa kwe-8% esesitokisini se-D4.

Izinyathelo Zokufundisa

Njengoba kukhulunywe ekuqaleni kokufundiswa, ibhokisi lengxoxo yesibili alikwazi ukuvulwa ukuze ufake umsebenzi ocebile ngakho kumele kufakwe ohlwini lwe-Value_if_false.

Qaphela: imisebenzi ehlelekile ayiqali ngesibonakaliso esilinganayo - kodwa esikhundleni segama lomsebenzi.

1. Chofoza ku- Value_if_false line ebhokisini lengxoxo
2. Faka umsebenzi olandelayo we-IF
   IF (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7)
3. Chofoza OK ukuze uqedele umsebenzi we-IF bese uvala ibhokisi lengxoxo
4. Inani lama $ 3,678.96 kufanele livele esitokisini E7 *
5. Uma uchofoza ku-cell E7, umsebenzi ophelele
   = IF (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7))
   ibonakala kubha yefomula ngaphezulu kwephepha lokusebenzela

* Njengoba uRoy Holt ethola imali engaphezu kwezingama-dollar 30,000 kodwa engaphansi kuka-$ 50,000 ngonyaka, ifomula engu-$ 45,987 * 8% isetshenziselwa ukubala ukukhishwa kwonyaka.

Uma zonke izinyathelo zilandelwe, isibonelo sakho kufanele silingane nesithombe sokuqala kulesi sihloko.

Isinyathelo sokugcina sifaka ukukopisha ifomu le-IF kumaseli E8 kuya ku-E11 usebenzisa isibambo sokugcwalisa ukuqedela iphepha lokusebenzela.

06 ka-06

Ukukopisha Imisebenzi ye-IF ekhonjiwe usebenzisa i-Handle Fill

Ukuqedela iphepha lokusebenzela, ifomula equkethe umsebenzi we-IF wezinyosi idinga ukukopishwa kumaseli E8 kuya ku-E11.

Njengoba umsebenzi ukopishwa, i-Excel izobuyekeza izikhombisi zeseli ezihambisanayo ukuze zibonise indawo entsha yomsebenzi ngenkathi kugcinwa ireferensi yeseli ngokuphelele.

Enye indlela elula yokukopisha amafomula ku-Excel i-Handle Fill.

Izinyathelo Zokufundisa

1. Chofoza kuseli E7 ukuze wenze iseli elisebenzayo .
2. Beka isikhombisi segundane phezu kwesikwele esimnyama ekhoneni elingezansi elingakwesokudla seligcini elisebenzayo. I-pointer izoshintsha ibe nesibonakaliso esengeziwe "+".
3. Chofoza inkinobho yegundane lesokunxele bese uhudula isibambo sokugcwalisa phansi esitokisini E11.
4. Khipha inkinobho yegundane. Amaseli E8 kuya ku-E11 azogcwaliswa ngemiphumela yefomula njengoba kuboniswe esithombeni ngenhla.