Ukuze uxazulule ezinye izinkinga lapho udala itafula, udinga ukucacisa inani lezinyanga enyangeni ehlukile noma ngaphakathi kwefomula ukuze uhlelo lwenze izibalo ezidingekayo. Ku-Excel kunamathuluzi aklanyelwe ukwenza lo msebenzi. Ake sibheke izindlela ezehlukene zokusebenzisa lesi sici.
Bala inani lezinsuku
Inombolo yezinsuku ngenyanga ku-Excel ingabalwa ngokusebenzisa opharetha abakhulu bezigaba. "Usuku Nesikhathi". Ukuze uthole ukuthi iyiphi inketho engcono kakhulu ukusebenzisa, okokuqala kudingeka ubeke imigomo yokusebenza. Kuye ngalokhu, umphumela wokubala ungaboniswa esakhiweni esihlukile eshidi, futhi singasetshenziswa ngaphakathi kwesinye ifomula.
Indlela yoku-1: inhlanganisela yama-operators DAY kanye ne-CARTON
Indlela elula yokuxazulula le nkinga ingxube ye-opharetha DAY futhi I-CRAFT.
Umsebenzi DAY kungokwamaqembu opharetha "Usuku Nesikhathi". Ukhomba inombolo ethile kusuka 1 kuze kube 31. Esimweni sethu, umsebenzi walokhu opharetha kuzoba ukucacisa usuku lokugcina lenyanga usebenzisa umsebenzi owakhiwe njengengxabano I-CRAFT.
I-syntax ye-Operator DAY okulandelayo:
= DAY (idatha_format)
Yilokho, ukuphikisana okuphela kwalokhu msebenzi kuwukuthi "Usuku ngefomethi yenombolo". Izobekwa opharetha I-CRAFT. Kumelwe kuthiwa usuku olufomathi lwezinombolo luhlukile kwifomethi evamile. Isibonelo, usuku 04.05.2017 ngesimo sezinombolo kuzobukeka 42859. Ngakho-ke, i-Excel isebenzisa le fomethi kuphela imisebenzi yangaphakathi. Akuvamile ukusetshenziselwa ukuboniswa kumaseli.
I-Operator I-CRAFT kuhloswe ukukhombisa inombolo ye-ordinal yosuku lokugcina lenyanga, okuyinto inombolo ecacisiwe yezinyanga phambili noma emuva kusukela ngosuku olucacisiwe. I-syntax yalo msebenzi ilandelayo:
= I-CONMS (i-start_date; inombolo_months)
I-Operator "Usuku Lokuqala" iqukethe usuku okubalwa ngalo ukubalwa, noma ukubhekisela kweseli lapho itholakala khona.
I-Operator "Inani lezinyanga" ikhombisa inani lezinyanga okufanele libalwe kusukela ngosuku olunikeziwe.
Manje ake sibone ukuthi lokhu kusebenza kanjani nesibonelo esithile. Ukuze wenze lokhu, thatha i-Excel sheet, kwelinye lamaseli lapho inombolo ethile yekhalenda efakiwe. Kubalulekile ngosizo lwesethi ngenhla ye-opharetha ukucacisa ukuthi zingaki izinsuku ezinyangeni zenyanga lapho le nombolo ibhekisela kuyo.
- Khetha iseli kushidi lapho umphumela uzoboniswa khona. Chofoza inkinobho "Faka umsebenzi". Le nkinobho itholakala ngakwesobunxele kwibha yefomula.
- Iwindi iqala Amakhosi omsebenzi. Yiya esigabeni "Usuku Nesikhathi". Thola futhi uqokomise irekhodi "USUKU". Chofoza inkinobho. "Kulungile".
- Iwindi lempikiswano ye-Operator ivuleka DAY. Njengoba ungabona, iqukethe insimu eyodwa kuphela - "Usuku ngefomethi yenombolo". Ngokuvamile, inombolo noma isixhumanisi eseliliqukethe isethwe lapha, kodwa sizoba nomsebenzi kule nsimu. I-CRAFT. Ngakho-ke, setha isikhombisi ensimini, bese uchofoza isithonjana ngendlela yesonxantathu ngakwesobunxele kwibha yefomula. Uhlu lwamagaretha asanda kusetshenziswa luvula. Uma uthola kulo igama "IZIKHATHI"bese uchofoza ngokushesha ukuze uhambe efasiteleni lezimangalo zalo msebenzi. Uma ungawutholi leli gama, chofoza indawo "Ezinye izici ...".
- Iqala futhi Umsebenzi weWebhu futhi futhi sithuthela eqenjini elifanayo lama-opharetha. Kodwa manje sibheke igama. "IZIKHATHI". Ngemuva kokugqamisa igama elichaziwe, chofoza inkinobho. "Kulungile".
- Ifasitelana lokuphikisana lomqhubi liqalisiwe. I-CRAFT.
Ensimini yakhe yokuqala, kubizwa "Usuku Lokuqala", udinga ukusetha inombolo esinawo esitokisini esithile. Yinani lezinsuku esikhathini esizobhekisela kuzo esizozinquma. Ukuze usethe ikheli leseli, faka isikhombisi ensimini, bese uchofoze ngokuchofoza kuso kushidi ngenki yegundane lesokunxele. Izixhumanisi zizobonakala ngokushesha efasiteleni.
Ensimini "Inani lezinyanga" setha inani "0", njengoba sidinga ukunquma ubude besikhathi esifanele lapho inombolo ekhonjisiwe ibhekisela kuyo.
Emva kwalokho chofoza inkinobho "Kulungile".
- Njengoba ubona, ngemuva kwesenzo sokugcina, inani lezinsuku ngenyanga okuyiyona eyakhelwe ngayo inombolo iboniswa esitokeni kushidi.
Ifomula jikelele sithatha ifomu elilandelayo:
= DAY (CRAIS) (B3; 0))
Kule fomula, inani eliguquguqukayo kuphela ikheli leseli (B3). Ngakho-ke, uma ungafuni ukwenza inqubo Amakhosi omsebenzi, ungafaka le fomula kunoma yiluphi uhlamvu lweshidi, ubeke esikhundleni ikheli leseli eliqukethe inombolo nelokuthi kuleso simo. Umphumela uzofana.
Isifundo: I-Excel umsebenzi wizard
Indlela 2: Ukuzimisela okuzenzakalelayo kwenani lezinsuku
Manje ake sibheke omunye umsebenzi. Kudingeka ukuthi inombolo yezinsuku iboniswe hhayi nenombolo yekhalenda enikeziwe, kodwa yiyo yamanje. Ngaphezu kwalokho, ukuguqulwa kwezikhathi kuzokwenziwa ngokuzenzakalelayo ngaphandle kokubamba iqhaza komsebenzisi. Nakuba kubonakala kungavamile, kodwa lo msebenzi kulula kunangaphambili. Ukuxazulula ngisho nokuvula Umsebenzi weWebhu Akudingekile, ngoba ifomula eyenza lo msebenzi ayiqukethe amanani aguquguqukayo noma izinkomba zamaseli. Ungamane ushayela esitokisini sephepha lapho ufuna khona ukuboniswa, ifomula elandelayo ngaphandle kokushintsha:
= USUKU (IKHAYA (NAMUHLA (); 0))
Umsebenzi owakhiwe namhlanje, esiwufake kulolu cala, ubonisa inombolo yamanje futhi awunayo imibono. Ngakho-ke, inani lezinsuku kule nyanga yamanje lizohlala liboniswa esitokisini sakho.
Indlela 3: Bala inani lezinsuku ozisebenzisayo emafomulini eziyinkimbinkimbi
Kulezi zibonelo ezingenhla, sibonise ukuthi singenza kanjani ukubalwa kwenani lezinsuku ngenyanga enombhaleni ecacisiwe noma ngokuzenzakalelayo ngenyanga yamanje kanye nomphumela oboniswe kuseli elihlukile. Kodwa ukuthola lokhu kubalulekile kungadingeka ukubala ezinye izinkomba. Kulesi simo, ukubalwa kwenani lezinsuku kuzokwenziwa ngaphakathi kwefomula eyinkimbinkimbi futhi ngeke kuboniswe kuseli elihlukile. Ake sibone ukuthi singakwenza kanjani lokhu ngesibonelo.
Sidinga ukuqinisekisa ukuthi inani lezinsuku ezisele kuze kube sekupheleni kwenyanga yamanje kuboniswa esitokisini. Njengendlela yokwedlule, le nketho ayidingi ukuvulwa Amakhosi omsebenzi. Ungakwazi ukuhambisa nje inkulumo elandelayo esitokisini:
= DAY (i-CRAEMY (NAMUHLA (); 0)) - DAY (NAMUHLA ())
Ngemuva kwalokho, iseli elibonisiwe lizobonisa inombolo yezinsuku kuze kube sekupheleni kwenyanga. Nsuku zonke, umphumela uzovuselelwa ngokuzenzakalelayo, futhi kusukela ekuqaleni kwesikhathi esisha, i-Countdown izoqala kabusha. Kuvela uhlobo lwe-countdown timer.
Njengoba ungabona, le fomula inezingxenye ezimbili. Eyokuqala yalezi yinkomba yokubala inani lezinsuku ngenyanga esazijwayele yona:
= USUKU (IKHAYA (NAMUHLA (); 0))
Kodwa engxenyeni yesibili, inombolo yamanje isusiwe kule nkomba:
-DAY (Namuhla ())
Ngakho-ke, lapho wenza lokhu kubalwa, ifomu lokubala inani lezinsuku liyingxenye ebalulekile yefomula eyinkimbinkimbi.
Indlela 4: Ifomula Ehlukile
Kodwa, ngeshwa, izinguqulo zohlelo ngaphambi kwe-Excel 2007 azikho opharetha I-CRAFT. Ungaba kanjani labo abasebenzisi abasebenzisa inguqulo endala yesicelo? Kubo, lokhu kungenzeka ngeyomunye umshini omkhulu kakhulu kunalokho okuchazwe ngenhla. Ake sibone ukuthi singakanani inani lezinsuku enyangeni ngenombolo yekhalenda enikeziwe besebenzisa lolu khetho.
- Khetha iseli ukubonisa umphumela bese uya efasiteleni lokuphikisana ne-opharetha DAY usujwayele kithi indlela. Beka isikhombisi emkhakheni owodwa wale window futhi chofoza kunxantathu ephendukile ngakwesokunxele kwebha yefomula. Yiya esigabeni "Ezinye izici ...".
- Ewindini Amakhosi omsebenzi eqenjini "Usuku Nesikhathi" khetha igama "DATE" bese uchofoza inkinobho "Kulungile".
- Iwindi le-Operator iqalisa DATE. Lo msebenzi uguqula usuku kusukela kufomethi evamile kuze kube yinani lezinombolo, okumele umqhubi abese esekucubungula. DAY.
Iwindi evulekile inezinkambu ezintathu. Ensimini "Usuku" ungafaka ngokushesha inombolo "1". Lokhu kuzoba yisenzo esifanayo kuzo zonke izimo. Kodwa ezinye izinsimu ezimbili kuzodingeka zenze kahle.
Setha isikhombisi emkhakheni "Unyaka". Okulandelayo, iya ekukhethweni kwama-opharetha ngokusebenzisa unxantathu owaziwayo.
- Konke kusigaba esifanayo Amakhosi omsebenzi khetha igama "YEAR" bese uchofoza inkinobho "Kulungile".
- Iwindi lempikiswano ye-Operator iqala. Unyaka. Lichaza unyaka ngenombolo ecacisiwe. Ebhokisini elilodwa lebhokisi "Usuku ngefomethi yenombolo" ucacise isixhumanisi esitokisini esinezinsuku zokuqala okudingayo ukuze unqume inani lezinsuku. Ngemva kwalokho, ungasheshi ukuchofoza inkinobho "Kulungile", bese uchofoza igama "DATE" kubha yefomula.
- Bese sibuyela ewindini lokuphikisana futhi. DATE. Setha isikhombisi emkhakheni "Inyanga" bese uya ekukhethweni kwemisebenzi.
- Ngaphakathi Umsebenzi wizard chofoza egameni "INYANGA" bese uchofoza inkinobho "Kulungile".
- Iwindi lempikiswano yokusebenza iqala. MONTH. Imisebenzi yayo ifana nomqhubi wangaphambilini, kuphela ekhombisa ukubaluleka kwenombolo yenyanga. Endaweni yodwa yale festile usetha ukufana okufanayo nenombolo yokuqala. Bese kubha yefomula chofoza igama "USUKU".
- Siphindela efasiteleni yezimpikiswano. DAY. Lapha kufanele senze nje ukuthinta okuncane nje. Esikhathini esisodwa sefasitela lapho idatha isivele ikhona khona, sengeza inkulumo ekupheleni kwefomula "-1" ngaphandle izingcaphuno, futhi ufake "+1" ngemuva kopharetha MONTH. Emva kwalokho chofoza inkinobho "Kulungile".
- Njengoba ubona, inamba yezinsuku enyangeni lapho inombolo ecacisiwe ingabonakaliswa khona kuseli elikhethiwe ngaphambilini. Ifomula jikelele kanje:
= DAY (DATE (YEAR (D3); MONTH (D3) +1; 1) -1)
Imfihlo yale fomula ilula. Siyisebenzisela ukucacisa usuku lolokuqala lwenyanga elandelayo, bese sisusa usuku olulodwa kulo, sithola inombolo yezinsuku enyangeni ecacisiwe. Okuguquguqukayo kule fomula kuyinkomba yeseli. D3 ezindaweni ezimbili. Uma uyibeka esikhundleni ngekheli leseli lapho ilanga lisekuqaleni kwakho, khona-ke ungakwazi ukuhambisa le nkulumo kunoma yiluphi uhlamvu lweshidi ngaphandle kosizo Amakhosi omsebenzi.
Isifundo: Idethi nesikhathi somsebenzi we-Excel
Njengoba ubona, kunezinketho eziningana zokuthola inombolo yezinsuku ngenyanga ku-Excel. Ngumuphi wabo okumele awisebenzise kuncike emgomweni wokugcina womsebenzisi, kanye nokuthi yiluphi uhlobo lwehlelo ayisebenzisayo.
