Indlela Yokusebenzisa Imithetho Yokufometha Kwemibandela Yokwezifiso Kwezinsuku ku-Excel

Ukwengeza ukufometha okunemibandela esitokisini ku-Excel kukuvumela ukuthi usebenzise izinketho ezahlukene zokufometha, njengombala, uma idatha kuleso seli ihlangabezana nezimo ozibekile.

Ukusebenzisa ukufometha okunemibandela kulula kunezinketho zangaphambi kokusetha ezitholakalayo ezifaka izimo ezivame ukusetshenziswa, njenge:

• Izinsuku
• Idatha ephindaphindiwe
• Imilinganiselo ngenhla noma ngezansi kwezindleko ezijwayelekile ebangeni lamaseli

Ngokuqondene nezinsuku, izinketho zokubeka kuqala zikwenza kube lula ukuhlola idatha yakho ngezinsuku eziseduze nosuku lwamanje, njengezolo, kusasa, ngesonto eledlule noma ngenyanga ezayo.

Uma ufuna ukuhlola izinsuku ezingenazo izinketho ezifakwe ohlwini, noma kunjalo, ungenza ngokwezifiso ukufometha okunemibandela ngokungeza ifomula yakho usebenzisa eyodwa noma ngaphezulu imisebenzi yosuku lwe-Excel.

01 ka-06

Ukuhlola izinsuku ezingu-30, 60, nezinsuku ezingu-90 ezidlule

Ukwenza ngokwezifiso ukufometha okunemibandela usebenzisa amafomula kwenziwa ngokubeka umthetho omusha u-Excel olandela uma uhlola idatha esitokisini.

Isibonelo sinyathelo ngesinyathelo lapha sichaza imithetho emisha enemibandela emisha yokufometha ezohlola ukuze ubone ukuthi izinsuku ezifakelwe ebangeni elikhethiwe lamaseli zingadluli izinsuku ezingu-30, ezinsukwini ezingama-60 ezedlula, noma ezinsukwini ezingu-90 ezedlula.

Amafomu asetshenziswe kule mithetho asusa inombolo ethile yezinsuku kusukela ngosuku lwamanje kumaseli C1 kuya ku-C4.

Usuku lwamanje lubalwa ngokusebenzisa umsebenzi we- DAY .

Ukuze lolu hlelo lusebenze kufanele ufake izikhathi eziwela ngaphakathi kwemingcele ebalulwe ngenhla.

Qaphela : I- Excel isebenza ngokufometha okunemibandela ngokulandelana, phezulu kuya phansi, ukuthi imithetho ihlelwe ebhokisini lebhokisi lengxoxo yeMenenja yeMifula Yokufometha Ngemibandela njengoba kuboniswe esithombeni ngenhla.

Ngisho noma imithetho eminingi ingasebenza kumaseli athile, umthetho wokuqala ohlangabezana nesimo usetshenziswa kumaseli.

02 ka 06

Ukuhlola Izinsuku 30 Izinsuku Ezidlulile

1. Gqamisa amaseli C1 kuya ku-C4 ukuze ukhethe. Lona ibanga esizosebenzisa ngalo imithetho yokufometha enemibandela
2. Chofoza ithebhu yaseKhaya yemenyu ye- ribbon .
3. Chofoza isithonjana sokufometha okunemibandela ukuze uvule imenyu yokudonsela phansi.
4. Khetha inketho entsha. Lokhu kuvulwa ibhokisi lokungena le-New Formatting Rule.
5. Chofoza ukusebenzisa i-Formula ukucacisa ukuthi yiliphi amangqamuzana ukufometha okukhethwa kukho.
6. Faka ifomula elandelayo ebhokisini elingezansi kwamanani wefomathi lapho leli xabiso liyiqiniso langempela engxenyeni engezansi yebhokisi lengxoxo:
   = NAMUHLA () - C1> 30
   Leli fomula lihlola ukuhlola ukuthi amadethi amaseli C1 kuya ku-C4 angaphezu kwezinsuku ezingu-30 ezedlule
7. Chofoza inkinobho yefomathi ukuze uvule ibhokisi lengxoxo yefomathi yefomathi.
8. Chofoza ithebhu yokugcwalisa ukuze ubone okukhethwa kukho kombala wokugcwalisa.
9. Khetha umbala wokugcwalisa kwangemuva-ukufanisa isibonelo kulesi sifundo, khetha ukukhanya okukhanyayo.
10. Chofoza ithebhu yefonti ukuze ubone okukhethwa kukho kwefomethi yefonti
11. Ngaphansi kwesigaba sombala, setha umbala wefonti ube mhlophe ukufanisa lesi sifundo.
12. Chofoza OK ngokuphindwe kabili ukuvala ibhokisi lengxoxo bese ubuyela ekhasini lokusebenzela.
13. Umbala wangemuva wamaseli C1 kuya ku-C4 uzoguqula umbala ogcwalisiwe okhethiwe, yize kungekho idatha kumaseli.

03 ka 06

Ukwengeza Umthetho Wezinsuku Ngaphezu kwezinsuku ezingu-60 okudlule

Ukusebenzisa indlela yokuphatha Imithetho

Esikhundleni sokuphinda zonke izinyathelo ezingenhla ukuze wengeze imithetho emibili elandelayo, sizosebenzisa ukukhethwa kweMithetho yeMithetho ezosenza sikwazi ukwengeza imithetho eyengeziwe ngesikhathi esisodwa.

1. Qinisekisa amaseli C1 kuya ku-C4, uma kunesidingo.
2. Chofoza kuthebhu yaseKhaya yemenyu ye-ribbon.
3. Chofoza kusithonjana sokufometha okunemibandela ukuvula imenyu ehlaselwa phansi.
4. Khetha okukhethwa kukho Imithetho Yokulawula ukuvula ibhokisi lengxoxo yeMenenja yeMifula Yokufometha Ngemibandela.
5. Chofoza kwiNew Rule inketho ekhoneni elingakwesokunxele lebhokisi lebhokisi
6. Chofoza ukusebenzisa i-Formula ukucacisa ukuthi yimaphi amangqamuzana ukufometha okukhethwa kukho ohlwini olusenhla ebhokisini lengxoxo.
7. Faka ifomula elandelayo ebhokisini elingezansi kwamanani wefomathi lapho leli xabiso liyiqiniso langempela engxenyeni engezansi yebhokisi lengxoxo :
   = NAMUHLA () - C1> 60

   Leli fomula lihlola ukuhlola ukuthi amadethi amaseli C1 kuya ku-C4 angaphezu kwezinsuku ezingu-60 ezedlule.

8. Chofoza inkinobho yefomathi ukuze uvule ibhokisi lengxoxo yefomathi yefomathi.
9. Chofoza ithebhu yokugcwalisa ukuze ubone okukhethwa kukho kombala wokugcwalisa.
10. Khetha umbala wokugcwalisa ngemuva; ukufanisa isibonelo kulesi sifundo, khetha u-yellow.
11. Chofoza ku- KULUNGILE kabili ukuvala ibhokisi lengxoxo bese ubuyela ebhokisini lebhokisi lengxoxo yeMenenja yeMifula yokuFometha.

04 ka 06

Ukwengeza Umthetho Wezinsuku Ngaphezu kwezinsuku ezingu-90 okudlule

1. Phinda izinyathelo ezingu-5 ukuya ku-7 ngenhla ukuze ungeze umthetho omusha.
2. Ukusetshenziswa kwefomula:
   = NAMUHLA () - C1> 90
3. Khetha umbala wokugcwalisa ngemuva; ukufanisa isibonelo kulesi sifundo, khetha i-orange.
4. Setha umbala wefonti ube mhlophe ukufanisa lesi sifundo.
5. Chofoza ku- KULUNGILE kabili ukuvala ibhokisi lengxoxo bese ubuyela ebhokisini lebhokisi lengxoxo yeMenenja yeMifula yokuFometha
6. Chofoza u- Kulungile futhi ukuvala leli bhokisi lebhokisi bese ubuyela ekhasini lokusebenzela .
7. Umbala wangemuva wamaseli C1 kuya ku-C4 uzoguqula kumbala wokugcina ogcotshiwe okhethiwe.

05 ka 06

Ukuhlola Imithetho Yokufometha Ngemibandela

Njengoba singabonakala esithombeni sokufundisa, singahlola imithetho yokufometha enemibandela kumaseli C1 kuya ku-C4 ngokufaka izinsuku ezilandelayo:

• Usuku lwangoku-iseli kufanele lishintshe esizinda sangasese esimhlophe ngombhalo omnyama, ngoba imithetho yefomethi yokufaka imithetho ayikho.
• Izinsuku ezingu-40 ngaphambi kosuku lwamanje-iseli kufanele lishintshe esizinda esibuhlaza obumhlophe ngombhalo omhlophe.
  
  (Qaphela: Uma unenkinga yokubala usuku olunjalo, njengoba kuboniswe esithombeni ngenhla, ifomula = TODAY () - 40 izofaka usuku 40 izinsuku ngaphambi kosuku lwamanje.)
• Izinsuku ezingu-70 ngaphambi kosuku lwamanje-iseli kufanele lishintshe esihlokweni esiphuzi ngombhalo omhlophe.
• Izinsuku eziyi-100 ngaphambi kosuku lwamanje-iseli kufanele lishintshe emlandweni obomvu obomvu ngombhalo omhlophe.

06 ka-06

Imithetho Yokufometha Eminye Yemibandela

Uma ishidi lakho lokusebenzela selivele libonisa usuku lwamanje-futhi ama-worksheet amaningi enza-ifomu ehlukile kulabo abangenhla angasebenzisa ireferensi yeseli esitokisini lapho usuku lwamanje luboniswa khona kunokusebenzisa umsebenzi we-TODAY.

Isibonelo, uma usuku luboniswa esitokisini B4, ifomula ingenwe njengomthetho osukwini lokufometha okwesikhashana ezingaphezu kwezinsuku ezingu-30 ezidlule ngenxa yokuthi kungaba:

= $ B $ 4> 30

Izibonakaliso ze dollar ($) ezungeze ireferensi yeseli ye-B4 zivimbela ukubhekisela kweseli ukuthi kungashintshi uma umthetho wokufometha okunemibandela ukopishwa kwamanye amaseli ekhasini lokusebenzela.

Izibonakaliso ze dollar zakha lokho okuyaziwa ngokuthi inkomba yeselula ngokuphelele .

Uma izimpawu ze-dollar zingavunyelwe futhi umthetho wokufometha okunemibandela ukopishwe, iseli noma indawo yamaseli okuphela cishe izobonisa i- #REF! umlayezo wephutha.