Ukusebenza ngesikhathi ku-Excel

Emsebenzini wochwepheshe ngamaspredishithi, akuvamile ukusebenzisana namadethi nezikhathi. Ngeke ukwazi ukwenza ngaphandle kwayo. Ngakho-ke, uNkulunkulu ngokwakhe wayala ukufunda indlela yokusebenzisa idatha yalolu hlobo. Lokhu kuzokongela isikhathi esiningi futhi kuvimbele amaphutha amaningi ngenkathi usebenza namaspredishithi.

Ngeshwa, abasaqalayo abaningi abazi ukuthi idatha icutshungulwa kanjani. Ngakho-ke, ngaphambi kokucabangela leli klasi lemisebenzi, kuyadingeka ukuqhuba uhlelo lwezemfundo olunemininingwane eyengeziwe.

Idethi imelelwa kanjani ku-Excel

Ulwazi lwedethi lucutshungulwa njengenani lezinsuku kusukela ngo-January 0, 1900. Yebo, awuphosisi. Eqinisweni, kusuka ku-zero. Kodwa lokhu kuyadingeka ukuze kube khona indawo yokuqala, ukuze uJanuwari 1 usuvele ubhekwa njengenombolo 1, njalonjalo. Inani ledethi ephezulu esekelwe ngu-2958465, yona enguDisemba 31, 9999.

Le ndlela yenza kube nokwenzeka ukusebenzisa izinsuku zokubala namafomula. Ngakho-ke, i-Excel yenza kube nokwenzeka ukunquma inani lezinsuku phakathi kwamadethi. Uhlelo lulula: owesibili ukhishwa enombolweni eyodwa, bese inani eliwumphumela liguqulwa libe ifomethi yedethi.

Ukuze uthole ukucaca okwengeziwe, nali ithebula elibonisa izinsuku ezinamavelu ezinombolo ahambisanayo.

Ukuze unqume inani lezinsuku ezidlulile kusukela kudethi A ukuya kudethi engu-B, udinga ukususa eyokuqala kokugcina. Esimweni sethu, lena ifomula =B3-B2. Ngemva kokuwufaka, umphumela uba okulandelayo.

Kubalulekile ukuqaphela ukuthi inani ngelezinsuku ngoba sikhethe ifomethi ehlukile yeseli kunedethi. Ukube besiqale sakhetha ifomethi ethi “Idethi”, umphumela ubuyoba yilo.

Kubalulekile ukunaka leli phuzu ekubaleni kwakho.

Okusho ukuthi, ukuze ubonise inombolo ye-serial elungile ehambisana ngokugcwele nedethi, kufanele usebenzise noma iyiphi ifomethi ngaphandle kosuku. Ngokulandelayo, ukuze uguqule inombolo ibe idethi, kufanele usethe ifomethi efanelekile. 

Isikhathi simelelwa kanjani ku-Excel

Indlela isikhathi esimelelwe ngayo ku-Excel ihluke kancane kunedethi. Usuku luthathwa njengesisekelo, futhi amahora, imizuzu, imizuzwana yizingxenye zalo eziyingxenye. Okusho ukuthi, amahora angu-24 ngu-1, futhi noma yiliphi inani elincane libhekwa njengengxenye yalo. Ngakho-ke, ihora elingu-1 lingu-1/24 wosuku, umzuzu ongu-1 ngu-1/1140, kanti isekhondi elingu-1 ngu-1/86400. Iyunithi yesikhathi encane kunazo zonke etholakala ku-Excel yi-millisecond engu-1.

Ngokufanayo nezinsuku, le ndlela yokumelela yenza kube nokwenzeka ukwenza izibalo ngokuhamba kwesikhathi. Yiqiniso, kunento eyodwa engalungile lapha. Ngemva kokubala, sithola ingxenye yosuku, hhayi inani lezinsuku.

Isithombe-skrini sibonisa amanani ngefomethi yezinombolo kanye nefomethi ethi "Isikhathi".

Indlela yokubala isikhathi iyafana nosuku. Kuyadingeka ukususa isikhathi sangaphambilini kusukela esikhathini sakamuva. Esimweni sethu, lena ifomula =B3-B2.

Njengoba iseli B4 iqale yaba nefomethi Ejwayelekile, bese ekupheleni kokwethulwa kwefomula, ishintsha ngokushesha ibe "Isikhathi". 

I-Excel, lapho isebenza ngokuhamba kwesikhathi, yenza imisebenzi ye-arithmetic evamile ngezinombolo, ezibuye zihunyushwe ngefomethi yesikhathi esijwayelene nathi. 

Ifomethi yosuku nesikhathi

Ngokwazi kwethu, izinsuku nezikhathi zingagcinwa ngamafomethi ahlukene. Ngakho-ke, udinga ukwazi ukuthi uwafaka kanjani ngendlela efanele ukuze ukufometha kunembile. 

Kunjalo, ungasebenzisa inombolo ye-serial yosuku noma ingxenye yosuku lapho ufaka idethi nesikhathi, kodwa le ndlela ayisebenzi kahle. Ngaphezu kwalokho, kuzodingeka usebenzise njalo ifomethi ethile esitokisini, okwandisa kuphela ukungakhululeki.

Ngakho-ke, i-Excel ikuvumela ukuthi ucacise isikhathi nosuku ngezindlela ezahlukene. Uma usebenzisa enye yazo, uhlelo luguqula ngokushesha ulwazi lube inombolo efanele futhi lusebenzise ifomethi elungile kuseli.

Bheka ithebula elingezansi ukuze uthole uhlu lwezindlela zokufaka zedethi nesikhathi ezisekelwa yi-Excel. Ikholomu yesobunxele ibala amafomethi angenzeka, futhi ikholomu yesokudla ibonisa ukuthi azovezwa kanjani ku-Excel ngemva kokuguqulwa. Kubalulekile ukuqaphela ukuthi uma unyaka ungacacisiwe, owamanje, osethwe ohlelweni lokusebenza, unikezwa ngokuzenzakalelayo.

Eqinisweni, ziningi izindlela zokubonisa. Kodwa lezi zanele. Futhi, inketho yedethi ethile yokuqopha ingase yehluke kuye ngezwe noma isifunda, kanye nezilungiselelo zesistimu yokusebenza.

Ukufometha Ngokwezifiso

Ngenkathi esebenza namaseli, umsebenzisi angakwazi ukunquma ukuthi ifomethi izoba yini. Angakwenza ukuze kuboniswe isikhathi, inyanga, usuku, nokunye kuphela. Kungenzeka futhi ukulungisa indlela idethi eyakhiwe ngayo, kanye nezihlukanisi.

Ukuze ufinyelele iwindi lokuhlela, udinga ukuvula ithebhu "Inombolo", lapho ungathola khona iwindi lenketho "Fometha Amaseli". Ebhokisini lengxoxo elivulayo, kuzoba nesigaba esithi “Idethi” lapho ungakhetha khona ifomethi yedethi efanele.

Uma ukhetha isigaba "Isikhathi", khona-ke, ngokufanelekile, uhlu olunezinketho zokubonisa isikhathi luzovela.

Ukuze usebenzise inketho ethile yokufometha kuseli, kufanele ukhethe ifomethi oyifunayo bese uchofoza okuthi KULUNGILE. Ngemva kwalokho, umphumela uzosetshenziswa. Uma engekho amafomethi anele anikezwa yi-Excel, ungathola isigaba "Wonke amafomethi". Kunezinketho eziningi laphaya futhi.

Uma kungekho inketho efanelekile, khona-ke kuhlale kungenzeka ukuthi udale eyakho. Kulula kakhulu ukwenza lokhu. Udinga nje ukukhetha amafomethi asethiwe njengesampula bese ulandela lezi zinyathelo:

1. Khetha iseli ofuna ukushintsha ifomethi yalo.
2. Vula ibhokisi lengxoxo elithi “Fometha Amaseli” bese uthola ithebhu “Inombolo”.
3. Okulandelayo, isigaba esithi “Wonke amafomethi” siyavuleka, lapho sithola khona inkambu yokufaka ethi “TYPE”. Lapho udinga ukucacisa ikhodi yefomethi yenombolo. Ngemuva kokuthi uyifakile, chofoza okuthi "OK".
4. Ngemva kwalezi zinyathelo, iseli izobonisa usuku nolwazi lwesikhathi ngefomethi yangokwezifiso.

Ukusebenzisa imisebenzi enezinsuku nezikhathi

Lapho usebenza nezinsuku nezikhathi, umsebenzisi angasebenzisa imisebenzi engaphezu kuka-20 ehlukene. Futhi nakuba lesi samba singase sibe sikhulu kakhulu kumuntu, zonke zingasetshenziswa ukufeza imigomo ethile.

Ukuze ufinyelele yonke imisebenzi engenzeka, kufanele uye esigabeni esithi “Idethi nesikhathi” seqembu elithi “Ilabhulali Yemisebenzi”. Sizocabangela kuphela eminye yemisebenzi eyinhloko eyenza kube nokwenzeka ukukhipha amapharamitha ahlukahlukene kusukela kumadethi nezikhathi.

YEAR ()

Inikeza ikhono lokuthola unyaka ohambisana nedethi ethile. Njengoba wazi kakade, leli nani lingaba phakathi kuka-1900 no-9999.

Iseli 1 libonisa idethi ngefomethi ethi DDDD DD.MM.YYYY hh:mm:ss. Le yifomethi esiyidale ngaphambilini. Ake sithathe njengesibonelo ifomula enquma ukuthi mingaki iminyaka edlulile phakathi kwamadethi amabili.

Ngesikhathi esifanayo, uma ubheka eduze, kuvela ukuthi umsebenzi awuzange ubale umphumela olungile ngokuphelele. Isizathu siwukuthi isebenzisa kuphela izinsuku ezibalweni zayo.

MONTH ()

Ngalo msebenzi, ungagqamisa inombolo yenyanga ehambisana nedethi ethile. Ibuyisela umphumela osuka ku-1 kuye ku-12. Le nombolo ihambisana nenombolo yenyanga.

USUKU()

Ngokufanayo nemisebenzi yangaphambilini, lena ibuyisela inombolo yosuku ngedethi ethile. Umphumela wokubala ungasukela ku-1 kuye ku-31.

ISIKHATHI()

Njengoba igama liphakamisa, lo msebenzi ubuyisela inombolo yehora, esukela ku-0 kuye ku-23.

MINUTES()

Umsebenzi obuyisela inombolo yamaminithi kuseli ethile. Amanani angaba khona abuyiswayo asuka ku-0 kuye ku-59.

IMIZUZWANA()

Lo msebenzi ubuyisela amanani afanayo nangaphambili, ngaphandle kokuthi ubuyisela imizuzwana.

USUKU()

Ngalo msebenzi, ungathola inombolo yosuku lwesonto olusetshenziswe kulolu suku. Amanani angenzeka asuka ku-1 kuye ku-7, kodwa khumbula ukuthi ukubala phansi kuqala kusukela ngeSonto, hhayi ngoMsombuluko, njengoba sivame ukwenza.

Nokho, usebenzisa impikiswano yesibili, lo msebenzi ikuvumela ukuba wenze ngendlela oyifisayo ifomethi. Isibonelo, uma uphase inani 2 njengepharamitha yesibili, ungasetha ifomethi ukuze inombolo 1 isho uMsombuluko esikhundleni seSonto. Lokhu kulula kakhulu kumsebenzisi wasekhaya.

Uma sibhala u-2 ku-agumenti yesibili, khona-ke esimweni sethu umsebenzi uzobuyisela inani elingu-6, elihambisana noMgqibelo.

NAMUHLA()

Lo msebenzi ulula kakhulu: azikho izimpikiswano ezidingekayo ukuze usebenze. Ibuyisela inombolo ye-serial yedethi esethwe kukhompuyutha. Uma sisetshenziswa kuseli okusethelwe lona Ifomethi Okujwayelekile, lapho-ke izoguqulelwa ngokuzenzakalelayo kufomethi "Yosuku".

I-TATA ()

Lo msebenzi futhi awudingi noma yiziphi izimpikiswano. Isebenza ngendlela efanayo neyangaphambili, kuphela ngedethi nesikhathi. Isetshenziswa uma kudingekile ukufaka kuseli idethi yamanje nesikhathi okusethwe kukhompuyutha. Futhi njengasemisebenzini yangaphambilini, lapho usebenzisa lena, iseli liguqulwa ngokuzenzakalelayo libe yifomethi yedethi nesikhathi, inqobo nje uma ifomethi ethi “Okuvamile” yayisethwa ngaphambili.

Kokubili umsebenzi wangaphambilini nalo msebenzi kushintsha ngokuzenzakalelayo isikhathi ngasinye lapho ishidi libalwa kabusha, okwenza kube nokwenzeka ukuveza isikhathi nosuku lwakamuva kakhulu. 

Isibonelo, ifomula enjalo inganquma isikhathi samanje.

=NAMUHLA()-NAMUHLA() 

Kulokhu, ifomula izonquma ingxenye yosuku ngefomethi yedesimali. Yiqiniso, kuzodingeka usebenzise ifomethi yesikhathi kuseli okubhalwe kulo ifomula, uma ufuna ukubonisa isikhathi esiqondile, hhayi inombolo.

IDETHI()

Lo msebenzi unezimpikiswano ezintathu, ngayinye okufanele ifakwe. Ngemva kokubala, lo msebenzi ubuyisela inombolo ye-serial yedethi. Iseli liguqulwa ngokuzenzakalela libe ifomethi ethi “Idethi” uma belikade linefomethi ethi “Okuvamile” ngaphambilini.

Impikiswano yoSuku noma Yenyanga ingaba yinhle noma ibe yimbi. Esimweni sokuqala, usuku luyanda, kanti okwesibili, luyancipha.

Ungasebenzisa futhi ukusebenza kwezibalo kuma-agumenti omsebenzi othi DATE. Isibonelo, le fomula yengeza unyaka ongu-1 izinyanga ezi-5 nezinsuku eziyi-17 osukwini olukuseli A1.

Futhi ifomula enjalo yenza kube nokwenzeka ukuguqula iyunithi yezinhlamvu yombhalo ibe idethi egcwele yokusebenza, engasetshenziswa kweminye imisebenzi.

ISIKHATHI()

Njengomsebenzi nje IDETHI(), lo msebenzi unemingcele emithathu edingekayo - amahora, imizuzu namasekhondi. Ngemva kokuyisebenzisa, inombolo yedesimali izovela kuseli eliwumphumela, kodwa iseli ngokwalo lizofomethwa ngefomethi ethi “Isikhathi” uma belikade linefomethi ethi “Okuvamile” ngaphambili.

Ngomgomo wayo wokusebenza, umsebenzi ISIKHATHI() и IDETHI() izinto eziningi ezifanayo. Ngakho-ke, akunangqondo ukugxila kukho. 

Kubalulekile ukuqaphela ukuthi lo msebenzi awukwazi ukubuyisela isikhathi esikhulu kuno-23:59:59. Uma umphumela mkhulu kunalokhu, umsebenzi uhlelwa kabusha ngokuzenzakalelayo ube uziro.

imisebenzi IDETHI() и ISIKHATHI() ingasetshenziswa ndawonye.

Kulesi sithombe-skrini, iseli D1, esebenzise yomibili le misebenzi, inefomethi yesikhathi sosuku. 

Idethi nesikhathi sokubala imisebenzi

Sekukonke kunemisebenzi emi-4 ekuvumela ukuthi wenze imisebenzi yezibalo nosuku nesikhathi.

I-DAMES()

Usebenzisa lo msebenzi, ungathola inombolo ye-ordinal yedethi engemuva kwenombolo eyaziwayo yezinyanga (noma ngaphambi kwenikeziwe). Lo msebenzi uthatha izimpikiswano ezimbili: idethi yokuqala kanye nenani lezinyanga. I-agumenti yesibili ingaba enhle noma embi. Inketho yokuqala kufanele icaciswe uma ufuna ukubala idethi yesikhathi esizayo, kanti eyesibili - uma eyedlule.

EOMONTH()

Lo msebenzi wenza kube nokwenzeka ukunquma inombolo ye-ordinal yosuku lokugcina lwenyanga engemuva noma ngaphambi kwedethi enikeziwe. Inezimpikiswano ezifanayo nezangaphambilini.

USUKU LOMSEBENZI()

Kuyafana nomsebenzi I-DAMES(), ukubambezeleka kuphela noma kusengaphambili okwenzeka ngenani elithile lezinsuku zokusebenza. I-syntax iyafana.

Yomithathu le misebenzi ibuyisela inombolo. Ukuze ubone idethi, udinga ukuguqula iseli libe yifomethi efanele. 

SULA()

Lo msebenzi olula unquma inani lezinsuku zebhizinisi phakathi kosuku loku-1 kanye nosuku 2.