Zamkatimu
Mu ntchito yaukadaulo ndi ma spreadsheets, sizachilendo kuyanjana ndi masiku ndi nthawi. Simungathe kuchita popanda izo. Chotero, Mulungu mwiniyo analamula kuphunzira mmene angagwiritsire ntchito deta ya mtundu umenewu. Izi zidzakupulumutsirani nthawi yochuluka ndikupewa zolakwika zambiri pamene mukugwira ntchito ndi spreadsheets.
Tsoka ilo, ambiri oyamba sadziwa momwe deta imapangidwira. Chifukwa chake, musanayambe kuganizira za kalasi iyi yantchito, ndikofunikira kuchititsa pulogalamu yophunzirira mwatsatanetsatane.
Momwe tsiku limayimiridwa mu Excel
Chidziwitso cha deti chimakonzedwa ngati chiwerengero cha masiku kuyambira Januware 0, 1900. Inde, simukulakwitsa. Inde, kuyambira zero. Koma izi ndizofunikira kuti pakhale poyambira, kotero kuti Januwale 1 amaonedwa kuti ndi nambala 1, ndi zina zotero. Tsiku lothandizira kwambiri ndi 2958465, lomwenso ndi December 31, 9999.
Njirayi imatheketsa kugwiritsa ntchito masiku powerengera ndi mafomu. Chifukwa chake, Excel imathandizira kudziwa kuchuluka kwa masiku pakati pa madeti. Chiwembucho ndi chosavuta: chachiwiri chimachotsedwa ku nambala imodzi, ndiyeno mtengo wake umasinthidwa kukhala mtundu wa tsiku.
Kuti mumve zambiri, nali tebulo lomwe likuwonetsa masiku omwe ali ndi manambala ofanana.
Kuti mudziwe kuchuluka kwa masiku omwe adutsa kuchokera pa deti A mpaka pano B, muyenera kuchotsa loyamba kuchokera lomaliza. Kwa ife, iyi ndi ndondomeko =B3-B2. Mukalowamo, zotsatira zake ndi izi.
Ndikofunikira kudziwa kuti mtengowo ndi wamasiku chifukwa tasankha mtundu wina wa selo kuposa tsiku. Ngati poyamba tikadasankha mtundu wa "Date", ndiye kuti zotsatira zake zikanakhala izi.
Ndikofunika kulabadira mfundo iyi pakuwerengera kwanu.
Ndiye kuti, kuti muwonetse nambala yolondola yomwe ikugwirizana ndi tsikulo, muyenera kugwiritsa ntchito mtundu wina uliwonse kupatula tsikulo. Kenako, kuti musinthe nambala kukhala deti, muyenera kukhazikitsa mtundu woyenera.
Momwe nthawi imayimiridwa mu Excel
Momwe nthawi imayimiridwa mu Excel ndiyosiyana pang'ono ndi tsikulo. Tsiku limatengedwa ngati maziko, ndipo maola, mphindi, masekondi ndi magawo ake ang'onoang'ono. Ndiye kuti, maola 24 ndi 1, ndipo mtengo uliwonse wocheperako umatengedwa ngati gawo lake. Choncho, ola limodzi ndi 1/1 pa tsiku, mphindi imodzi ndi 24/1, ndipo sekondi imodzi ndi 1/1140. Gawo laling'ono kwambiri la nthawi lomwe likupezeka mu Excel ndi 1 millisecond.
Mofanana ndi masiku, njira iyi yoyimira imapangitsa kuti zitheke kuwerengera ndi nthawi. Zowona, pali chinthu chimodzi chovuta apa. Pambuyo powerengera, timapeza gawo la tsiku, osati chiwerengero cha masiku.
Chithunzicho chikuwonetsa zomwe zili mumtundu wa manambala ndi mtundu wa "Nthawi".
Njira yowerengera nthawi ndi yofanana ndi deti. Ndikofunikira kuchotsa nthawi yoyambirira kuchokera nthawi yamtsogolo. Kwa ife, iyi ndi ndondomeko =B3-B2.
Popeza selo B4 poyamba anali General mtundu, ndiye kumapeto kwa chiyambi cha chilinganizo, nthawi yomweyo kusintha "Nthawi".
Excel, pogwira ntchito ndi nthawi, imapanga masamu anthawi zonse ndi manambala, omwe amamasuliridwa mu nthawi yomwe timaidziwa bwino.
Mtundu wa tsiku ndi nthawi
Monga tikudziwira, masiku ndi nthawi zitha kusungidwa m'njira zosiyanasiyana. Chifukwa chake, muyenera kudziwa momwe mungawalowetse bwino kuti masanjidwewo akhale olondola.
Zachidziwikire, mutha kugwiritsa ntchito nambala yatsiku kapena gawo latsiku polowa tsiku ndi nthawi, koma njira iyi ndiyovuta kwambiri. Kuphatikiza apo, muyenera kugwiritsa ntchito nthawi zonse mtundu wina ku cell, zomwe zimangowonjezera kusapeza.
Chifukwa chake, Excel imakupatsani mwayi wofotokozera nthawi ndi tsiku m'njira zosiyanasiyana. Ngati mugwiritsa ntchito imodzi mwazo, ndiye kuti pulogalamuyo imasintha nthawi yomweyo chidziwitsocho kukhala nambala yoyenera ndikuyika mawonekedwe olondola ku selo.
Onani tebulo ili m'munsimu kuti mupeze mndandanda wa njira zolembera za tsiku ndi nthawi zothandizidwa ndi Excel. Mzere wakumanzere umatchula mafomu omwe angatheke, ndipo gawo lakumanja likuwonetsa momwe adzasonyezedwe mu Excel pambuyo pa kutembenuka. Ndikofunika kuzindikira kuti ngati chaka sichinatchulidwe, chomwe chilipo, chomwe chimayikidwa mu dongosolo la opaleshoni, chimangoperekedwa.
M'malo mwake, pali njira zambiri zowonetsera. Koma izi ndi zokwanira. Komanso, nthawi yeniyeni yojambulira njira ingasiyane kutengera dziko kapena dera, komanso zoikamo za opareshoni.
Kusintha Kwamakonda
Pogwira ntchito ndi ma cell, wogwiritsa ntchito amatha kudziwa mtundu womwe udzakhale. Akhoza kupangitsa kuti nthawi, mwezi, tsiku, ndi zina zotero zisonyezedwe. N'zothekanso kusintha ndondomeko yomwe tsikulo limapangidwira, komanso olekanitsa.
Kufikira kusintha zenera, muyenera kutsegula "Nambala" tabu, kumene mungapeze njira "Format Maselo" zenera. M'bokosi la zokambirana lomwe limatsegulidwa, padzakhala gulu la "Date" momwe mungasankhire mtundu woyenera wa tsiku.
Mukasankha gulu la "Nthawi", ndiye kuti, mndandanda womwe uli ndi zosankha zowonetsera nthawi udzawonekera.
Kuti mugwiritse ntchito njira yosinthira pa selo, muyenera kusankha mtundu womwe mukufuna ndikudina OK. Pambuyo pake, zotsatira zake zidzagwiritsidwa ntchito. Ngati palibe mitundu yokwanira yomwe Excel imapereka, ndiye kuti mutha kupeza gulu la "Mawonekedwe Onse". Palinso njira zambiri kunja uko.
Ngati palibe njira yoyenera, ndiye kuti nthawi zonse ndizotheka kupanga nokha. Ndikosavuta kuchita izi. Inu muyenera kusankha preset akamagwiritsa monga chitsanzo ndi kutsatira ndondomeko izi:
- Sankhani selo lomwe mukufuna kusintha mawonekedwe ake.
- Tsegulani bokosi la "Format Cells" ndikupeza "Nambala" tabu.
- Kenako, gulu la "Mawonekedwe Onse" limatsegulidwa, pomwe timapeza gawo lolowera "TYPE". Pamenepo muyenera kufotokoza nambala yamtundu wamtundu. Mukalowa, dinani "Chabwino".
- Pambuyo pa masitepe awa, seloyo idzawonetsa tsiku ndi nthawi muzojambula.
Kugwiritsa ntchito ndi masiku ndi nthawi
Pogwira ntchito ndi masiku ndi nthawi, wogwiritsa ntchito amatha kugwiritsa ntchito zinthu zopitilira 20. Ndipo ngakhale ndalamazi zingakhale zochulukira kwa wina, zikhoza kugwiritsidwa ntchito kukwaniritsa zolinga zina.
Kuti mupeze ntchito zonse zomwe zingatheke, muyenera kupita ku gulu la "Date and Time" la gulu la "Functions Library". Tikambirana zina mwazofunikira zomwe zimathandizira kuchotsa magawo osiyanasiyana kuchokera pamasiku ndi nthawi.
YEAR ()
Amapereka mwayi wopeza chaka chomwe chikugwirizana ndi tsiku linalake. Monga mukudziwa kale, mtengo uwu ukhoza kukhala pakati pa 1900 ndi 9999.
Selo loyamba likuwonetsa tsiku mumtundu wa DDDD DD.MM.YYYY hh:mm:ss. Ili ndiye mawonekedwe omwe tidapanga kale. Tiyeni titenge mwachitsanzo chilinganizo chomwe chimatsimikizira zaka zingati zadutsa pakati pa masiku awiri.
Panthawi imodzimodziyo, ngati muyang'anitsitsa kwambiri, zimakhala kuti ntchitoyi sinawerengere zotsatira zolondola. Chifukwa chake ndi chakuti imangogwiritsa ntchito masiku powerengera.
MONTH ()
Ndi ntchitoyi, mutha kuwunikira nambala ya mwezi yomwe ikugwirizana ndi tsiku linalake. Imabweza zotsatira zoyambira 1 mpaka 12. Nambalayi nayonso imagwirizana ndi nambala ya mweziwo.
TSIKU()
Mofanana ndi ntchito zam'mbuyomu, iyi imabwezera chiwerengero cha tsiku mu deti loperekedwa. Zotsatira zowerengera zimatha kuyambira 1 mpaka 31.
NTHAWI ()
Monga momwe dzinalo likusonyezera, ntchitoyi imabwezera nambala ya ola, yomwe imachokera ku 0 mpaka 23.
MINUTES()
Ntchito yomwe imabweza kuchuluka kwa mphindi mu cell inayake. Zomwe zingatheke zomwe zabwezedwa zikuchokera ku 0 mpaka 59.
SECONDS ()
Ntchitoyi imabweretsanso zikhalidwe zomwezo monga zam'mbuyomu, kupatula kuti imabwereranso masekondi.
TSIKU()
Ndi ntchitoyi, mutha kudziwa kuchuluka kwa tsiku la sabata lomwe likugwiritsidwa ntchito lero. Zomwe zingatheke zimachokera ku 1 mpaka 7, koma kumbukirani kuti kuwerengera kumayambira Lamlungu, osati Lolemba, monga momwe timachitira.
Komabe, pogwiritsa ntchito mkangano wachiwiri, ntchitoyi imakupatsani mwayi wosintha mawonekedwewo. Mwachitsanzo, ngati mudutsa mtengo wa 2 ngati gawo lachiwiri, mutha kukhazikitsa mawonekedwe kuti nambala 1 itanthauza Lolemba m'malo mwa Lamlungu. Izi ndizosavuta kwambiri kwa ogwiritsa ntchito apakhomo.
Ngati tilemba 2 mkangano wachiwiri, ndiye kuti ntchitoyo idzabwezera mtengo wa 6, womwe umagwirizana ndi Loweruka.
LERO()
Ntchitoyi ndiyosavuta: palibe zotsutsana zomwe zimafunikira kuti zigwire ntchito. Imabweza nambala ya seri ya tsiku lomwe layikidwa pa kompyuta. Ngati ikugwiritsidwa ntchito ku selo yomwe General format yakhazikitsidwa, ndiye kuti idzasinthidwa kukhala mtundu wa "Date".
TATA ()
Ntchito imeneyinso sikutanthauza mikangano iliyonse. Zimagwira ntchito mofanana ndi yapitayi, pokhapokha ndi tsiku ndi nthawi. Amagwiritsidwa ntchito ngati kuli kofunikira kuyika mu selo tsiku ndi nthawi yomwe yaikidwa mu kompyuta. Ndipo monga momwe zinalili m'mbuyomu, mukamagwiritsa ntchito iyi, selo imasinthidwa kukhala tsiku ndi nthawi, malinga ngati mtundu wa "General" udakhazikitsidwa kale.
Ntchito zonse zam'mbuyomu ndi ntchitoyi zimasinthidwa zokha nthawi iliyonse yomwe tsambalo liwerengedweranso, zomwe zimapangitsa kuti zizitha kuwonetsa nthawi ndi tsiku laposachedwa kwambiri.
Mwachitsanzo, ndondomeko yotereyi imatha kudziwa nthawi yomwe ilipo.
=LERO()-LERO()
Pankhaniyi, chilinganizo chidzatsimikizira gawo la tsiku mumtundu wa decimal. Zowona, muyenera kugwiritsa ntchito mtundu wa nthawi ku cell yomwe fomulayo idalembedwa, ngati mukufuna kuwonetsa nthawi yeniyeni, osati nambala.
TSIKU()
Ntchitoyi ili ndi mfundo zitatu, zomwe ziyenera kulowetsedwa. Pambuyo powerengera, ntchitoyi imabwezeretsa nambala ya serial ya tsikulo. Selo imasinthidwa kukhala mtundu wa "Date" ngati inali ndi "General" m'mbuyomu.
Mkangano wa Tsiku kapena Mwezi ukhoza kukhala wabwino kapena woipa. Poyamba, tsikulo likuwonjezeka, ndipo chachiwiri, limachepa.
Mutha kugwiritsanso ntchito masamu pamakangano a DATE ntchito. Mwachitsanzo, fomulayi imawonjezera chaka chimodzi miyezi isanu ndi masiku 1 ku tsiku la cell A5.
Ndipo ndondomeko yotereyi imapangitsa kuti zitheke kusintha chingwe cha malemba kukhala tsiku logwira ntchito, lomwe lingagwiritsidwe ntchito pazinthu zina.
NTHAWI ()
Monga ntchito TSIKU(), ntchitoyi ili ndi magawo atatu ofunikira - maola, mphindi ndi masekondi. Mukaigwiritsa ntchito, nambala ya decimal idzawonekera mu selo lomwe likubwera, koma selo lokha lidzasinthidwa mu "Nthawi" ngati linali ndi "General" kale.
Mwa mfundo yake ya ntchito, ntchito NTHAWI () и TSIKU() zinthu zambiri zofanana. Choncho, n'zosamveka kuika maganizo ake pa izo.
Ndikofunikira kudziwa kuti ntchitoyi siyingabweretse nthawi yoposa 23:59:59. Ngati zotsatira zake ndi zazikulu kuposa izi, ntchitoyi imasinthidwa kukhala ziro.
Nchito TSIKU() и NTHAWI () angagwiritsidwe ntchito palimodzi.
Pachithunzichi, cell D1, yomwe idagwiritsa ntchito zonsezi, ili ndi mawonekedwe anthawi.
Ntchito Zowerengera Tsiku ndi Nthawi
Pazonse pali ntchito 4 zomwe zimakulolani kuchita masamu ndi tsiku ndi nthawi.
DATAMES()
Pogwiritsa ntchito ntchitoyi, mutha kudziwa nambala ya ordinal ya deti yomwe ili kumbuyo kwa miyezi yodziwika (kapena patsogolo pa ina yoperekedwa). Ntchitoyi imatenga zifukwa ziwiri: tsiku loyambira ndi chiwerengero cha miyezi. Mtsutso wachiwiri ukhoza kukhala wabwino kapena woipa. Njira yoyamba iyenera kufotokozedwa ngati mukufuna kuwerengera tsiku lamtsogolo, ndipo yachiwiri - ngati yapitayi.
EOMONTH()
Ntchitoyi imapangitsa kuti zitheke kudziwa nambala ya ordinal ya tsiku lomaliza la mwezi womwe uli kumbuyo kapena patsogolo pa tsiku lomwe laperekedwa. Lili ndi zotsutsana zofanana ndi zam'mbuyomo.
TSIKU LA NTCHITO()
Zofanana ndi ntchito DATAMES(), kuchedwa kokha kapena kutsogola kumachitika ndi masiku angapo ogwira ntchito. Ma syntax ndi ofanana.
Zonse zitatuzi zimabweretsa nambala. Kuti muwone tsikulo, muyenera kusintha selo kukhala mawonekedwe oyenera.
CHONTHA ()
Ntchito yosavuta iyi imatsimikizira kuchuluka kwa masiku a bizinesi pakati pa deti 1 ndi tsiku la 2.