Don magance wasu matsalolin lokacin ƙirƙirar tebur, kana buƙatar ƙayyade yawan kwanakin a cikin wata a cikin tantanin tantanin halitta ko a cikin wata maƙala don don shirin don aiwatar da lissafin da ake bukata. A cikin Excel akwai kayan aikin da aka tsara don yin wannan aiki. Bari mu dubi hanyoyi daban-daban don amfani da wannan alama.
Kira yawan kwanakin
Yawan kwanakin a cikin wata ɗaya a Excel za a iya ƙididdigewa ta yin amfani da kamfanoni na musamman. "Rana da lokaci". Don gano ko wane zaɓi zai fi dacewa don amfani, dole ne ka fara buƙatar aukuwa don aiki. Dangane da wannan, ana iya nuna sakamakon lissafi a cikin rabuwa daban a kan takardar, kuma za'a iya amfani dashi a cikin wani tsari.
Hanyar 1: haɗuwa da masu aiki DAY da CARTON
Hanyar mafi sauki ta magance wannan matsala ita ce haɗuwa da masu aiki DAY kuma CRAFT.
Yanayi DAY yana cikin ƙungiyar masu aiki "Rana da lokaci". Ta nuna wani lamba daga 1 har zuwa 31. A halinmu, aikin wannan afaretan zai ƙayyade ranar ƙarshe na watan ta amfani da aikin ginawa a matsayin hujja CRAFT.
Mai amfani da haɗin gwiwar DAY gaba:
= DAY (data_format)
Wato, kawai hujjar wannan aikin shine "Kwanan wata a cikin tsari mai lamba". Za a saita shi ta mai aiki CRAFT. Dole ne a faɗi cewa kwanan wata a cikin tsarin lambobi ya bambanta da tsari na al'ada. Alal misali, kwanan wata 04.05.2017 a cikin nau'in lambobi zai kama 42859. Sabili da haka, Excel yana amfani da wannan tsari kawai don ayyukan ciki. An yi amfani da ita don nunawa cikin sel.
Mai sarrafawa CRAFT An tsara shi don nuna lambar lamba na rana ta ƙarshe ta watan, wanda shine ƙayyadadden watanni masu zuwa ko baya daga kwanan wata. Haɗin aikin shine kamar haka:
= CONMS (start_date; number_months)
Mai sarrafawa "Ranar farawa" ya ƙunshi kwanan wata daga abin da aka ƙidaya ƙididdiga, ko kuma ƙira zuwa tantanin halitta inda aka samo shi.
Mai sarrafawa "Yawan watanni" ya nuna yawan watanni da ya kamata a kidaya daga kwanan da aka ba.
Yanzu bari mu ga yadda wannan yayi aiki tare da misali. Don yin wannan, ɗauki takardar Excel, a ɗaya daga cikin sel wanda an shigar da lambar kalandar. Dole ne tare da taimakon mai aiki na sama don ƙayyade kwanaki nawa a cikin kowane wata wanda wannan lambar tana nufin.
- Zaɓi tantanin halitta akan takardar da za'a nuna sakamakon. Danna maballin "Saka aiki". Wannan maballin yana hagu zuwa hagu na tsari.
- Ginin yana farawa Ma'aikata masu aiki. Je zuwa sashen "Rana da lokaci". Nemo da kuma nuna alamar rikodin "DAY". Danna maballin. "Ok".
- Ma'aikatar kulawa ta buɗewa ta buɗe DAY. Kamar yadda ka gani, yana ƙunshe ne kawai filin guda - "Kwanan wata a cikin tsari mai lamba". Yawancin lokaci, da dama ko hanyar haɗi zuwa tantanin halitta dauke da shi an saita a nan, amma zamu sami aiki a cikin wannan filin. CRAFT. Sabili da haka, saita siginan kwamfuta a filin, sannan ka danna gunkin a cikin nau'i na triangle a gefen hagu na tsari. Jerin ayyukan amfani da kwanan nan ya buɗe. Idan ka sami sunan da shi "CRAFTS"to, nan da nan danna kan shi don zuwa wannan maɓallin muhawarar wannan aikin. Idan ba ku sami wannan sunan ba, sannan danna kan matsayin "Sauran fasali ...".
- Fara farawa Wizard aikin kuma sake komawa zuwa rukuni na masu aiki. Amma wannan lokacin muna neman sunan. "CRAFTS". Bayan ya nuna sunan da aka ƙayyade, danna kan maballin. "Ok".
- An kaddamar da matakan maganganu. CRAFT.
A filinsa na farko, ana kira "Ranar farawa", kana buƙatar saita lambar da muke da shi a cikin tantanin salula. Yawan adadin kwanaki a cikin lokacin da ya danganta cewa za mu ƙayyade. Domin saita adireshin salula, sanya siginan kwamfuta a filin, sa'an nan kawai danna danna kan takarda tare da maɓallin linzamin hagu. Za a nuna matakan nan gaba a cikin taga.
A cikin filin "Yawan watanni" saita darajar "0", tun da muna bukatar mu ƙayyade tsawon lokacin daidai da lambar da aka nuna.
Bayan wannan latsa maɓallin "Ok".
- Kamar yadda ka gani, bayan aikin karshe, yawan kwanakin a watan da aka zaɓa lambar da aka zaɓa an nuna a cikin tantanin halitta akan takardar.
Tsarin dabarar muka dauki nau'i na gaba:
= DAY (CRAIS) (B3; 0))
A cikin wannan tsari, nauyin da ya dace shine kawai adireshin cell (B3). Saboda haka, idan ba ku so kuyi aikin ta hanyar Ma'aikata masu aiki, zaku iya shigar da wannan tsari a kowane ɓangaren takardar, kawai maye gurbin adireshin tantanin halitta dauke da lambar tare da wanda yake dacewa a cikin akwati na musamman. Sakamakon zai kasance kama.
Darasi: Maɓallin aiki na Excel
Hanyar 2: Tabbatacce ta atomatik na yawan kwanakin
Yanzu bari mu dubi wani aiki. Ana buƙatar yawan kwanakin da aka nuna ba ta lambar kalandar ba, amma ta yanzu. Bugu da ƙari, za a sauya canjin lokaci ta atomatik ba tare da mai amfani ba. Ko da yake yana da alama, amma wannan aikin ya fi sauƙi. Don warware shi ko da bude Wizard aikin Ba lallai ba ne, saboda tsarin da yake aiwatar da wannan aiki ba ya ƙunshi dabi'u mai tsayi ko alamomin sel. Kuna iya sauƙaƙa zuwa cikin tantanin halitta na takardar inda kake so sakamakon da za'a nuna, wannan tsari ba tare da canje-canje ba:
= DAYA (KUMA (TODAY (); 0))
Ayyukan da aka gina a yau, wanda muka yi amfani da shi a wannan yanayin, yana nuna halin yanzu kuma ba shi da wata hujja. Saboda haka, yawancin kwanaki a cikin wannan watan za a nuna su kullum a cikin tantanin ku.
Hanyar 3: Yi ƙidayar yawan kwanakin da za a yi amfani da su a cikin tsari mai mahimmanci
A cikin misalai a sama, mun nuna yadda za mu yi lissafi na yawan kwanakin a cikin wata a kan lambar kalandar da aka ƙayyade ko ta atomatik a wannan watan tare da sakamakon da aka nuna a cikin tantanin halitta. Amma gano wannan darajar zai zama wajibi don lissafin wasu alamomi. A wannan yanayin, ana yin lissafi na adadin kwanaki a cikin wani tsari mai mahimmanci kuma ba za a nuna shi a cikin tantanin salula ba. Bari mu ga yadda za muyi haka ta misali.
Muna buƙatar tabbatar cewa yawan kwanakin da aka bar har zuwa karshen wannan watan yana nuna a cikin tantanin halitta. Kamar yadda aka rigaya, wannan zaɓi bai buƙatar budewa ba Ma'aikata masu aiki. Kuna iya fitar da wadannan kalmomin cikin tantanin halitta:
= DAYA (KUMA (TODAY (); 0)) - DAY (YANAYA ())
Bayan wannan, cell din da aka nuna zai nuna yawan kwanakin har zuwa karshen watan. Kowace rana, sakamakon za a sabunta ta atomatik, kuma tun daga farkon sabon zamani, ƙidayawa za ta sake farawa. Yana juya wani irin lokaci mai ƙididdigewa.
Kamar yadda kake gani, wannan tsari ya ƙunshi sassa biyu. Na farko daga cikin waɗannan shine bayanin don ƙidaya yawan kwanakin a cikin wata da ya saba da mu:
= DAYA (KUMA (TODAY (); 0))
Amma a bangare na biyu, an cire lambar ta yanzu daga wannan alamar:
-DAY (TODAY ())
Saboda haka, lokacin yin wannan lissafi, ma'anar lissafin adadin kwanakin wata ƙungiya ce ta wani tsari mai mahimmanci.
Hanyar 4: Sauran Formula
Amma, da rashin alheri, sassan shirin a baya fiye da Excel 2007 ba shi da wani afareta CRAFT. Yaya za a kasance masu amfani da suke amfani da tsohon version of the application? A gare su, wannan yiwuwar ta samuwa ta hanyar wata mahimmanci wanda ya fi kwarewa fiye da yadda aka bayyana a sama. Bari mu ga yadda za a tantance yawan kwanakin a cikin wata don lambar karamar da aka bayar ta amfani da wannan zaɓi.
- Zaɓi tantanin salula don nuna sakamakon kuma je zuwa maɓallin ƙwaƙwalwar mai aiki DAY saba wa hanya sosai. Sanya mai siginan kwamfuta a cikin filin kawai na wannan taga kuma danna maɓallin triangle inverted a gefen hagu na tsari. Je zuwa sashen "Sauran fasali ...".
- A cikin taga Ma'aikata masu aiki a cikin rukuni "Rana da lokaci" zaɓi sunan "DATE" kuma danna maballin "Ok".
- Mai gudanarwa yana fara DATE. Wannan aikin ya canza kwanan wata daga sabaccen tsarin zuwa darajar lambobi, wanda dole ne mai aiki ya sarrafa. DAY.
Gidan da aka buɗe yana da sau uku. A cikin filin "Ranar" zaka iya shigar da lambar nan da nan "1". Wannan zai zama daidai wannan mataki don kowane hali. Amma sauran wurare guda biyu za su yi sosai.
Saita siginan kwamfuta a filin "Shekara". Na gaba, je zuwa zabi na masu aiki ta hanyar maƙallin triangle.
- Duk a cikin jinsi guda Ma'aikata masu aiki zaɓi sunan "SHEKARA" kuma danna maballin "Ok".
- Maƙallin bayanin mai aiki ya fara. Shekara. Yana bayyana shekara ta lambar da aka ƙayyade. A cikin akwati guda "Kwanan wata a cikin tsari mai lamba" saka haɗin zuwa ga tantanin halitta dauke da ranar asalin da kake buƙatar ƙayyade yawan kwanakin. Bayan haka, kada ku rush don danna maballin "Ok", kuma danna sunan "DATE" a cikin tsari.
- Sa'an nan kuma mu koma maimaita bayani a sake. DATE. Saita siginan kwamfuta a filin "Watan" kuma je zuwa zaɓi na ayyuka.
- A cikin Mai sarrafa aiki danna sunan "MUTANE" kuma danna maballin "Ok".
- Gidan gwajin aikin ya fara. MONTH. Ayyukanta suna kama da mai aiki na baya, kawai yana nuna darajar watan mai lamba. A cikin filin kawai na wannan taga saita wannan maƙasudin zuwa lambar asali. Sa'an nan kuma a cikin wannan tsari bar a kan sunan "DAY".
- Mu koma cikin taga na muhawarar. DAY. A nan dole mu yi kawai karamin taɓawa. A cikin filin kawai na taga inda aka riga an samo bayanai, muna ƙara bayanin zuwa karshen wannan tsari "-1" ba tare da fadi ba, kuma ya sanya "+1" bayan mai aiki MONTH. Bayan wannan latsa maɓallin "Ok".
- Kamar yadda ka gani, yawan kwanakin a cikin watan wanda aka sanya lambar da aka ƙayyade aka nuna a cikin cell da aka zaba. Gaba ɗaya shine kamar haka:
= DAY (DATE (SHE (D3); MONTH (D3) +1; 1) -1)
Asirin wannan tsari yana da sauki. Muna amfani da shi don sanin kwanan wata na farko na mako mai zuwa, sannan mu cire rana ɗaya daga gare ta, karɓar yawan kwanakin a cikin watan da aka ƙayyade. Ƙarfin a cikin wannan tsari shine tantancewar salula. D3 a wurare biyu. Idan ka maye gurbin shi tare da adireshin tantanin halitta wanda kwanan wata ke cikin shari'arka, to, kawai zaka iya fitar da wannan furci a cikin kowane ɓangaren takardar ba tare da taimakon ba Ma'aikata masu aiki.
Darasi: Kwanan rana da ayyukan lokaci na Excel
Kamar yadda kake gani, akwai zaɓuɓɓuka masu yawa don gano yawan kwanakin a cikin wata a Excel. Wanne daga cikinsu ya yi amfani da shi ya dogara ne da burin mai amfani, har ma da wane ɓangaren shirin da yake amfani da su.
