Pofuna kuthana ndi mavuto pamene mukupanga tebulo, muyenera kufotokoza chiwerengero cha masiku mumwezi mu selo losiyana kapena mkati mwa ndondomeko kuti pulogalamuyo ikhale yowerengera yofunikira. Mu Excel pali zida zogwiritsidwa ntchito kuti izichita. Tiyeni tiwone njira zosiyanasiyana zomwe tingagwiritsire ntchito mbali iyi.
Terengani chiwerengero cha masiku
Chiwerengero cha masiku pamwezi mu Excel chikhoza kuwerengedwa pogwiritsira ntchito opanga gulu lapadera. "Tsiku ndi Nthawi". Kuti mudziwe njira yabwino yogwiritsira ntchito, choyamba muyenera kukhazikitsa zolinga za opaleshoni. Malingana ndi izi, zotsatira za chiwerengerocho zikhoza kuwonetsedwa mu chinthu chosiyana pa pepala, ndipo chingagwiritsidwe ntchito mkati mwa njira ina.
Njira 1: kuphatikiza kwa otsogolera DAY ndi CARTON
Njira yosavuta kuthetsera vutoli ndi kuphatikiza kwa ogwira ntchito TSIKU ndi CRAFT.
Ntchito TSIKU ndi wa gulu la opaleshoni "Tsiku ndi Nthawi". Akulongosola ku nambala yapadera kuchokera 1 mpaka 31. Kwa ife, ntchito ya wogwiritsira ntchitoyi idzakhala yeniyeni tsiku lomaliza la mwezi pogwiritsa ntchito ntchito yomangidwira ngati mkangano CRAFT.
Opaleshoni yamagetsi TSIKU lotsatira:
= DAY (data_format)
Ndicho, kukangana kokha kwa ntchitoyi ndi "Tsiku la zowerengeka". Idzaikidwa ndi woyendetsa CRAFT. Ziyenera kunenedwa kuti tsiku lokhala ndi chiwerengero chosiyana ndi losiyana ndi kachitidwe kameneka. Mwachitsanzo, tsiku 04.05.2017 mu mawonekedwe anga adzawoneka ngati 42859. Chifukwa chake, Excel imagwiritsa ntchito mtundu umenewu pokhapokha pazinthu zochitika mkati. Sagwiritsidwa ntchito kawirikawiri m'maselo.
Woyendetsa CRAFT cholinga chake chiwonetsere nambala ya ordinal ya tsiku lotsiriza la mwezi, womwe uli miyezi yambiri yomwe ikutsogolera kapena kubwerera kumbuyo kuchokera tsiku lomwe lidatsimikiziridwa. Chidule cha ntchitoyi ndi ichi:
= CONMS (start_date; number_months)
Woyendetsa "Tsiku Loyamba" ili ndi tsiku limene chiŵerengerocho chapangidwa, kapena kutanthauza selo limene liripo.
Woyendetsa "Chiwerengero cha miyezi" imasonyeza chiwerengero cha miyezi yomwe iyenera kuwerengedwera kuchokera pa tsiku lopatsidwa.
Tsopano tiyeni tiwone momwe izi zimagwirira ntchito ndi chitsanzo chapadera. Kuti muchite izi, tengani pepala la Excel, mumodzi mwa maselo omwe muli nambala ya kalendala ina. Ndikofunika mothandizidwa ndi gulu lapamwambali la ogwira ntchito kuti mudziwe masiku angati pa mwezi uliwonse womwe chiwerengerochi chikutanthauza.
- Sankhani selo pa pepala yomwe zotsatira zake ziwonetsedwe. Dinani pa batani "Ikani ntchito". Bulu ili lili kumanzere kwa bar.
- Foda ikuyamba Oyang'anira ntchito. Pitani ku gawoli "Tsiku ndi Nthawi". Pezani ndikutsindika mbiri "TSIKU". Dinani pa batani. "Chabwino".
- Fayilo yotsutsana ndi otsogolera imatsegula TSIKU. Monga mukuonera, ili ndi munda umodzi - "Tsiku la zowerengeka". Kawirikawiri, chiwerengero kapena kulumikiza kwa selo yomwe ili nayo imayikidwa pano, koma tidzakhala ndi ntchito mu gawo ili. CRAFT. Choncho, ikani malonda mmunda, ndiyeno dinani pa chithunzicho mwa mawonekedwe a katatu kumanzere kwa bar. Mndandanda wa oyendetsa ntchito atsopano wagwiritsidwa ntchito. Ngati mutapeza dzinali "ANTHU"ndiye nthawi yomweyo dinani pa izo kuti mupite kuwindo la zokhudzana ndi ntchitoyi. Ngati simukupeza dzina ili, ndiye dinani pamalo "Zina ...".
- Inayambanso Mlaliki Wachipangizo ndipo kachiwiri timasamukira ku gulu lomwelo la ogwira ntchito. Koma nthawi ino tikufunafuna dzina. "ANTHU". Pambuyo pofotokoza dzina lopatsidwa, dinani pa batani. "Chabwino".
- Wowonjezera zotsutsana zenera akuyambitsidwa. CRAFT.
Kumunda wake woyamba, wotchedwa "Tsiku Loyamba", muyenera kukhazikitsa chiwerengero chomwe tili nacho mu selo losiyana. Ndi chiwerengero cha masiku mu nthawi yomwe chikukhudzana ndi zomwe tidzasankha. Kuti muyike adilesi yeniyeni, ikani cholozera mmunda, ndipo pangani pakani pa pepala ndi batani lamanzere. Makonzedwewo adzawonetsedwa pomwepo pazenera.
Kumunda "Chiwerengero cha miyezi" ikani mtengo "0", popeza tikufunikira kudziwa nthawi yomwe ndondomekoyo imasonyezera.
Pambuyo pake dinani pa batani "Chabwino".
- Monga mukuonera, mutatha ntchito yomaliza, chiwerengero cha masiku mu mwezi umene muli nambala yosankhidwa ikuwonetsedwa mu selo pa pepala.
Njira yowonjezera tinatenga mawonekedwe otsatirawa:
= DAY (CRAIS) (B3; 0))
Muyeso ili, mtengo wosiyanasiyana ndi adilesi ya selo (B3). Choncho, ngati simukufuna kuchita ndondomekoyi Oyang'anira ntchito, mukhoza kuyikapo ndondomekoyi mu gawo lililonse la pepala, pokhapokha mutengere adiresi ya selo yomwe ili ndi chiwerengero ndi imodzi yomwe ili yoyenera pazochitika zanu. Zotsatira zidzakhala zofanana.
Phunziro: Excel ntchito wizara
Njira 2: Kutsimikizika moyenera kwa chiwerengero cha masiku
Tsopano tiyeni tiwone ntchito ina. Zimayenera kuti chiwerengero cha masiku sichiwonetsedwe ndi nambala ya kalendala, koma ndi yomwe ilipo. Kuonjezerapo, kusintha kwa nthawi kungapangidwe mosavuta popanda wogwiritsa ntchito. Ngakhale izo zikuwoneka zodabwitsa, koma ntchito iyi ndi yophweka kuposa yomwe yapitayo. Kuthetsa izo ngakhale kutseguka Mlaliki Wachipangizo Sikofunika, chifukwa njira yomwe imagwira ntchitoyi ilibe malingaliro osiyana kapena mafotokozedwe a maselo. Mukhoza kungoyendetsa mu selo la pepala kumene mukufuna kuti zotsatira ziwonetsedwe, ndondomeko zotsatirazi popanda kusintha:
= DAY (CRAEMY (MASIKU ano (); 0))
Ntchito yomangidwira MASIKU ano, yomwe tagwiritsira ntchito pa nkhaniyi, ikuwonetsa nambala yamakono ndipo ilibe zifukwa. Choncho, chiwerengero cha masiku mumwezi wamakono chidzawonetsedwa nthawi zonse mu selo yanu.
Njira 3: Terengani chiwerengero cha masiku omwe mungagwiritse ntchito m'mawonekedwe ovuta
Mu zitsanzo pamwambapa, tasonyeza momwe tingachitire mawerengedwe a chiwerengero cha masiku mu mwezi pa kalendala yeniyeni yeniyeni kapena mwadzidzidzi mwezi womwe ulipo ndi zotsatira zomwe zasonyezedwa mu selo losiyana. Koma kupeza phindu limeneli kungakhale kofunikira kuwerengera zizindikiro zina. Pachifukwa ichi, kuwerengera kwa chiwerengero cha masiku kudzapangidwa mkati mwa machitidwe ovuta ndipo sikudzawonetsedwa mu selo losiyana. Tiyeni tiwone momwe tingachitire izi mwachitsanzo.
Tiyenera kutsimikiza kuti chiwerengero cha masiku otsala mpaka kumapeto kwa mwezi wamakono chikuwonetsedwa mu selo. Monga mwa njira yapitayi, chisankho ichi sichifuna kutsegula Oyang'anira ntchito. Mukhoza kungotulutsa mawu otsatirawa mu selo:
= DAY (CRAEMY (MASIKU ano (); 0)) - DAY (MASIKU ANA ())
Pambuyo pake, selo yosonyezedwa liwonetsa chiwerengero cha masiku mpaka kumapeto kwa mweziwo. Tsiku lililonse, zotsatirazo zidzasinthidwa, ndipo kuyambira pachiyambi cha nthawi yatsopano, chiwerengerochi chidzayambanso. Ikukhala mtundu wa timeri yowerengera.
Monga mukuonera, ndondomekoyi ili ndi magawo awiri. Choyamba cha izi ndizowerengetsera chiwerengero cha masiku mu mwezi omwe amadziwika kale ndi ife:
= DAY (CRAEMY (MASIKU ano (); 0))
Koma m'gawo lachiwiri, nambala yeniyeni ikuchotsedwera kuchoka pa chizindikiro ichi:
-DAYANI (TSIKU ()
Choncho, pochita izi, chiwerengero chowerengera chiwerengero cha masiku ndi gawo lofunika kwambiri.
Njira 4: Njira Zina
Koma, mwatsoka, mapulogalamu oyambirira kuposa Excel 2007 alibe wogwira ntchito CRAFT. Kodi mungagwiritse ntchito bwanji anthu omwe amagwiritsira ntchito mawonekedwe akalewo? Kwa iwo, kuthekera kumeneku kulipo mwa njira ina yomwe ili yaikulu kuposa yomwe yanenedwa pamwambapa. Tiyeni tiwone momwe tingawerengere chiwerengero cha masiku mu mwezi kwa nambala ya kalendala yomwe mwawapatsa potsata njirayi.
- Sankhani selo kuti muwonetse zotsatirazo ndi kupita kuwindo lawongolera TSIKU Tidziwa kale. Ikani chithunzithunzi pamtunda wokha pawindo ili ndipo dinani pang'onopang'ono yosandulika kumanzere kwa bar. Pitani ku gawoli "Zina ...".
- Muzenera Oyang'anira ntchito mu gulu "Tsiku ndi Nthawi" sankhani dzina "DATE" ndipo dinani pa batani "Chabwino".
- Wowonjezera mawindo akuyamba DATE. Ntchitoyi imasintha tsikulo kuchokera ku mtundu wokhazikika kupita ku mtengo wamtengo wapatali, womwe woyendetsa ntchitoyo ayenera kuchita. TSIKU.
Firiji lotseguka liri ndi minda itatu. Kumunda "Tsiku" mungathe kulowa mwamsanga mwamsanga "1". Izi zidzakhala zofanana pazochitika zonse. Koma minda ina iwiri iyenera kuchita bwino.
Ikani cholozera mmunda "Chaka". Chotsatira, pitani ku chisankho cha opareshoni kupyolera mu katatu wodziwika.
- Onse ali m'gulu lomwelo Oyang'anira ntchito sankhani dzina "YEAR" ndipo dinani pa batani "Chabwino".
- Fesito yotsutsana ndi otsogolera imayambira. Chaka. Limatanthawuza chaka ndi nambala yeniyeni. M'bokosi limodzi la bokosi "Tsiku la zowerengeka" tchulani kulumikiza kwa selo lokhala ndi tsiku loyambirira lomwe muyenera kudziwa chiwerengero cha masiku. Pambuyo pake, musathamangire kuti muchoke pa batani "Chabwino", ndipo dinani pa dzina "DATE" mu bar.
- Ndiye tibwereranso kuwindo latsutsano. DATE. Ikani cholozera mmunda "Mwezi" ndipo pitani ku ntchito yosankhidwa.
- Mu Wizard ntchito dinani pa dzina "MONTH" ndipo dinani pa batani "Chabwino".
- Ntchito yotsutsana zenera ikuyamba. MONTH. Ntchito zake ndizofanana ndi oyendetsa ntchito, koma zimasonyeza kufunika kwa nambala ya mwezi. Pawindo lokha lawindo ili likuwonetseranso zofanana ndi chiyambi choyambirira. Kenaka mu bar lachondapu dinani pa dzina "TSIKU".
- Timabwerera kuwindo lazitsutsano. TSIKU. Pano tikuyenera kugwira pang'ono chabe. Pokha pawindo pazenera kumene deta ili kale, timayonjezera mawu kumapeto kwa fomu "-1" popanda ndemanga, komanso ikani "+1" pambuyo pa wogwira ntchitoyo MONTH. Pambuyo pake dinani pa batani "Chabwino".
- Monga momwe mukuonera, chiwerengero cha masiku mu mwezi umene chiwerengero chomwe chilipo chikuwonetsedwa mu selo losankhidwa kale. Njira yowonjezera ili motere:
= DAY (DATE (YEAR (D3); MONTH (D3) +1; 1) -1)
Chinsinsi cha njirayi ndi chophweka. Timagwiritsa ntchito kudziwa tsiku la tsiku loyamba lachidziwitso, kenako timachotsapo tsiku limodzi, kulandira chiwerengero cha masiku mu mwezi watchulidwa. Zosinthika muzondomekoyi ndi selo lotanthauzira. D3 m'malo awiri. Ngati mumalowetsamo ndi adiresi ya selo limene tsikulo liri pa inu, ndiye kuti mutha kuyendetsa mawuwa mulimonse kalikonse pa pepala popanda thandizo Oyang'anira ntchito.
Phunziro: Tsiku la Excel ndi nthawi zimagwira ntchito
Monga mukuonera, pali njira zingapo kuti mudziwe chiwerengero cha masiku mumwezi mu Excel. Mmodzi wa iwo amene angagwiritse ntchito akudalira cholinga chachikulu cha wogwiritsa ntchito, komanso momwe pulogalamuyo amagwiritsira ntchito.
