Kuenzanisa misi muExcel

Kazhinji, vashandisi vespredishiti mupepeti vanofanirwa kuita maitiro akaoma sekuenzanisa mazuva. Ichi chiito chinogona kuitwa nenzira dzakasiyana-siyana. Muchinyorwa, tichaongorora zvakadzama nzira dzese dzinokutendera kuti uenzanise misi mupepeti yespredishiti.

Nguva yekugadzirisa mupepeti yespredishiti

Mupepeti wespredishiti anobata nguva uye zuva sedhata renhamba. Purogiramu inoshandura ruzivo urwu nenzira yekuti rimwe zuva rakaenzana ne 1. Somugumisiro, chiratidzo chenguva chikamu cheimwe. Somuenzaniso, 12.00 ndiyo 0.5. Mupepeti wespredishiti anoshandura zviratidzo zvemazuva kuita nhamba inokosha, iyo yakaenzana nenhamba yemazuva kubva muna Ndira 1, 1900 kusvika pazuva rakatarwa. Semuenzaniso, kana mushandisi akashandura zuva 14.04.1987/31881/31881, ipapo richava nemutengo 2. Mune mamwe mazwi, mazuva XNUMX apfuura kubva pachiratidzo chepakutanga. Iyi makanika inoshandiswa pakuverenga kukosha kwenguva. Kuti uverenge huwandu hwemazuva pakati pemazuva XNUMX, zvinodikanwa kubvisa diki chiratidzo chenguva kubva pane yakakura nguva chiratidzo.

Uchishandisa DATE statement mune tafura mupepeti

Maonero ese emushandisi anoita seizvi: DATE(gore, mwedzi, zuva). Imwe neimwe yenharo inodiwa kuti inyorwe mushandisi. Pane nzira mbiri dzekumisa nharo. Mutoo wekutanga unosanganisira kugadzwa kwenhamba dzenhamba. Yechipiri nzira inosanganisira kupinda mukati mezvirongwa zvemasero umo ruzivo rwakakosha rwenhamba. Nharo yekutanga inhamba kubva 1900 kusvika 9999. Nharo yechipiri inhamba kubva pa1 kusvika 12. Nharo yechitatu inhamba kubva pa1 kusvika pa31.

Semuenzaniso, kana iwe ukatsanangura kukosha kwenhamba kunopfuura 31 sezuva, ipapo zuva rekuwedzera richaenda kune mumwe mwedzi. Kana mushandisi akapinda mazuva makumi matatu nemaviri munaKurume, anozopedzisira nekutanga kwaApril.

Muenzaniso wekushandisa mushandisi unotaridzika seizvi:

Muenzaniso wekutsanangura nhamba yakakura yemazuva munaJune:

Muenzaniso unoratidza kushandiswa kwesero coordinates senharo:

Kushandisa RAZDAT opareta mune spreadsheet editor

Mushandi uyu anodzoka pakati pe2 date values. Maonero ese emushandisi anoita seizvi: RAZDAT(kutanga_date; last_date; code_for_designation_of_count_units). Mhando dzekuverenga dzenguva dzepakati pakati pezviratidzo zvemazuva maviri akatsanangurwa:

• "d" - inoratidza chiratidzo chekupedzisira mumazuva;
• "m" - inoratidza huwandu mumwedzi;
• "y" - inoratidza huwandu mumakore;
• "ym" - inoratidza huwandu mumwedzi, kusasanganisa makore;
• "md" - inoratidza huwandu hwemazuva, kusasanganisa makore nemwedzi;
• "yd" - inoratidza huwandu hwemazuva, kusasanganisa makore.

Mune dzimwe shanduro dzespredishiti mupepeti, kana uchishandisa zvakanyanyisa 2 nharo, mushandisi anogona kuratidza kukanganisa. Muchiitiko ichi, zvakakosha kushandisa mamwe maitiro.

Muenzaniso unoratidza kushanda kwemushandisi:

Mune 2007 mupepeti wespredishiti, mushandisi uyu haasi mureferensi, asi unogona kuishandisa.

Kushandisa YEAR opareta mupepeti yespredishiti

Mushandi uyu anokubvumira kudzorera gore sehuwandu hunoenderana nezuva rakatarwa. Huwandu hwenhamba hunoratidzwa muchikamu kubva 1900 kusvika 9999. Mafomu ese emutengi YEAR ane 1 nharo. Nharo izuva renhamba. Inofanirwa kunyorwa uchishandisa DATE opareta, kana kuburitsa chiratidzo chekupedzisira chekuverenga chero mamwe mafomula. Muenzaniso unoratidza kushanda kwemushandisi:

Uchishandisa MONTH opareta mupepeti yespredishiti

Mushandi uyu anokubvumira kudzorera mwedzi sehuwandu hunoenderana nezuva rakatarwa. Huwandu hwenhamba hunoratidzwa kubva pa1 kusvika pa12. Mafomu ese emushandisi weMONTH ane 1 nharo. Nharo izuva remwedzi, rakanyorwa senhamba. Inofanirwa kunyorwa uchishandisa DATE opareta, kana kuburitsa chiratidzo chekupedzisira chekuverenga chero mamwe mafomula. Zvakakosha kuziva kuti mwedzi wakanyorwa mumutauro wezvinyorwa hauzogadziriswi nemazvo nemupepeti wespredishiti. Muenzaniso unoratidza kushanda kwemushandisi:

Mienzaniso yekushandisa ZUVA, WEEKDAY, uye WEEKDAY vashandisi mupepeti yespredishiti

Mushandi uyu anotendera iwe kudzosa zuva sehuwandu hunoenderana nezuva rakatarwa. Hwaro hwenhamba hunoratidzwa kubva pa1 kusvika pa31. Mafomu ese emushandisi weDAY ane 1 nharo. Nharo izuva rezuva, rakanyorwa senhamba. Inofanirwa kunyorwa uchishandisa DATE opareta, kana kuburitsa chiratidzo chekupedzisira chekuverenga chero mamwe mafomula. Muenzaniso unoratidza kushanda kwemushandisi:

Mushandi, ane zita rekuti WEEKDAY, anobvumidza iwe kudzosa nhamba yezuva revhiki yezuva rakapihwa. Nekutadza, mushandisi anotora Svondo sezuva rekutanga revhiki. Muenzaniso unoratidza kushanda kwemushandisi:

Mushandisi, ane zita rekuti NOMWEEK, anotendera iwe kuti uratidze iyo ordinal nhamba yevhiki mune yakapihwa zuva. Muenzaniso unoratidza kushanda kwemushandisi:

Semuenzaniso, Chivabvu 24.05.2015, XNUMX ivhiki yemakumi maviri nemaviri yegore. Sezvazvakanyorwa pamusoro, purogiramu inotora Svondo sezuva rokutanga revhiki.

Nharo yechipiri ndeye 2. Izvi zvinobvumira mupepeti wespredishiti kuti atarise Muvhuro sekutanga kwevhiki (chete mukati mefomula iyi).

Iyo TODAY opareta inoshandiswa kuseta zuva razvino. Mushandisi uyu haana nharo. Iyo TDATE () mushandisi inoshandiswa kuratidza yazvino zuva uye nguva.

Mhedziso uye mhedziso pamusoro pekuenzanisa misi mupepeti yespredishiti

Takaona kuti kune nzira dzakawanda uye vashandisi vekuenzanisa mazuva maviri mupepeti yespredishiti. Inonyanya kusarudzwa ndeye kushandisa RAZNDATA operator, iyo inokubvumira kudzorera musiyano pakati pemazuva maviri. Pamusoro pezvo, iwe unogona kushandisa mafomula akafanana kudzorera zuva, mwedzi, uye gore kukosha. Wese mushandisi anogona kuzvisarudzira akazvimiririra nzira iri nyore yekuenzanisa misi mupepeti yespredishiti.