Eterayyade yawan kwanakin a cikin wata guda a Microsoft Excel

Don magance wasu matsaloli lokacin ƙirƙirar tebur, kuna buƙatar ƙididdige yawan kwanakin a cikin wata a cikin rayayyen tantanin halitta ko cikin dabara don shirin ya aiwatar da lissafin da ya dace. Excel yana da kayan aikin da aka tsara don yin wannan aikin. Bari mu bincika hanyoyi daban-daban don amfani da wannan fasalin.

Lissafin yawan kwanakin

Kuna iya lissafin adadin ranakun wata guda a cikin Excel ta amfani da nau'ikan kwastomomi na musamman "Kwanan wata da lokaci". Don gano wane zaɓi ne yafi dacewa, dole ne a fara tabbatar da maƙasudin aikin. Ya danganta da wannan, za a iya nuna sakamakon lissafin a wani sashi na daban akan takardar, ko kuma za'a iya amfani dashi a cikin wata dabara.

Hanyar 1: haɗuwa tsakanin masu aiki DAY da MONTHS

Hanya mafi sauki don magance wannan matsalar ita ce amfani da haɗakar masu aiki RANAR da MATA.

Aiki RANAR nasa ne ga rukuni na masu aiki "Kwanan wata da lokaci". Yana nuna takamaiman lamba daga 1 a da 31. A cikin lamarinmu, aikin wannan mai aikin zai kasance yana nuna ranar ƙarshe ta watan ta yin amfani da ginanniyar aikin a matsayin hujja MATA.

Syntax mai aiki RANAR mai zuwa:

= DAY (day_in_numeric_format)

Wannan shine, kawai hujja ga wannan aikin shine "Kwanan wata a lamba. Mai sarrafawa zai saita shi MATA. Dole ne in faɗi cewa kwanan wata a cikin lambobi suna bambanta da tsarin da aka saba. Misali, kwanan wata 04.05.2017 zai iya zama kamar lamba 42859. Saboda haka, Excel yana amfani da wannan tsari don ayyukan ciki kawai. Ba a cika amfani dashi don nunawa a sel ba.

Mai aiki MATA da nufin nuna adadin adadin watannin ƙarshe na watan, wanda ke ƙayyadadden adadin watanni na gaba ko baya daga takamaiman kwanan wata. Gwanin aikin shine kamar haka:

= MONTH (fara_date; lamba_months)

Mai aiki "Fara kwanan wata" ya ƙunshi ranar da aka yi lissafin, ko kuma hanyar haɗi zuwa tantanin da yake.

Mai aiki "Yawan watanni" yana nuna adadin watanni da za a ƙididdige daga ranar da aka bayar.

Yanzu bari mu ga yadda wannan ke aiki a kan ingantaccen misali. Don yin wannan, ɗauki takaddara na Excel, a ɗayan ɗayan sel waɗanda aka sanya takaddun kalanda. Yin amfani da saiti na masu aiki da ke sama, ya zama dole a ƙayyade yawan kwanakin cikin watan da adadin wannan ya shafi.

1. Zaɓi tantanin akan takarda wanda za'a nuna sakamakon sa. Latsa maballin "Saka aikin". Wannan maɓallin tana can hagu na masarar dabara.



2. Window yana farawa Wizards na Aiki. Je zuwa sashin "Kwanan wata da lokaci". Nemo kuma zaɓi rakodi RANAR. Latsa maballin. "Ok".



3. Wurin Shaida Mai aiki Yana buɗewa RANAR. Kamar yadda kake gani, ya ƙunshi filin guda ɗaya kawai - "Kwanan wata a lamba. Yawancin lokaci suna sanya lamba ko hanyar haɗi zuwa tantanin da ke ɗauke da shi, amma za mu sami aiki a wannan filin MATA. Saboda haka, mun saita siginan kwamfuta a cikin filin, sannan danna kan gunki a cikin nau'in alwatika zuwa hagu na layin dabarun. Jerin masu aiki da aka yi amfani da su kwanan nan ya buɗe. Idan kaga suna a ciki "SAURARA", sa’annan danna shi kai tsaye domin zuwa taga hujjojin wannan aikin. Idan baku sami wannan sunan ba, to danna kan abun "Sauran sifofin ...".



4. Zai fara sake Mayan fasalin kuma mun sake motsawa zuwa wannan rukunin masu aiki guda. Amma a wannan karon muna neman suna "SAURARA". Bayan nuna alama da sunan da aka ƙayyade, danna kan maɓallin "Ok".



5. Shafin Hujja Mai Furewa Mai Aiki MATA.

   A cikin filin sa na farko, ake kira "Fara kwanan wata", kuna buƙatar saita lambar da ke cikin sel ɗinmu daban. Yana da adadin kwanakin a cikin abin da ya shafi abin da za mu ƙayyade. Domin saita adireshin wayar, sanya siginan kwamfuta a cikin filin, sannan kuma kawai danna kan shi a kan takardar tare da maɓallin linzamin kwamfuta na hagu. Za a nuna kwararrun hanzari a cikin taga.

   A fagen "Yawan watanni" saita darajar "0", tunda muna buƙatar tantance tsawon lokacin da adadin abin da aka nuna ya ƙunsa.

   Bayan haka, danna maɓallin "Ok".



6. Kamar yadda kake gani, bayan aikin da ya gabata, yawan kwanakin watan da lambar da aka zaɓa ya bayyana a cikin tantanin da ke kan takardar.

Maganar gaba ɗaya ta ɗauki wannan tsari:

= RANAR (BAYAN (B3; 0))

A wannan dabara, adireshin tantanin halitta kawai (B3) Saboda haka, idan ba ka son yin aikin ta hanyar Wizards na Aiki, zaka iya saka wannan dabara a cikin kowane sashi na takarda, kawai musanya adireshin tantanin da ke ɗauke da lamba tare da wanda ya dace a cikin yanayinka. Sakamakon zai zama irin wannan.

Darasi: Mayan Maɗaukaki

Hanyar 2: gano yawan kwanakin

Yanzu bari mu kalli wani aiki. Ana buƙatar yawan kwanakin ba bisa ga lambar kalanda da aka bayar ba, amma gwargwadon yanzu. Bugu da kari, canjin lokaci zai gudana ta atomatik ba tare da shigarwar mai amfani ba. Kodayake wannan alama baƙon abu bane, wannan aikin yana da sauƙi fiye da wanda ya gabata. Don warware shi, har ma a buɗe Mayan fasalin ba lallai ba ne, saboda dabara da ke yin wannan aikin ba ya ƙunshi kyawawan dabi'u ko nassoshi na kwayar halitta. Zaku iya fitar da sauƙaƙe zuwa cikin kwayar takarda inda kuke son sakamakon ya nuna tsarin da yake bi ba tare da canje-canje ba:

= RANAR (BAYAN (TODAY (); 0))

Aikin ginannun yau da muke amfani dashi a wannan yanayin yana nuna lambar yau kuma basu da hujja. Don haka, yawan ranakun watan mai zuwa za a nuna kullun a cikin ƙwayoyinku.

Hanyar 3: lissafta yawan kwanakin da za ayi amfani dasu cikin cakudaddun tsari

A cikin misalan da ke sama, mun nuna yadda ake ƙididdige yawan kwanakin a cikin wata ta lambar ƙayyadadden kalandar ko ta atomatik ta watan yanzu tare da sakamakon da aka nuna a cikin sel daban. Amma gano wannan ƙimar ana iya buƙatar ƙididdige sauran alamun. A wannan halin, lissafin yawan kwanakin za'a aiwatar dashi a cikin hadaddun tsari kuma baza a nuna shi a cikin sel din wani ba. Bari mu ga yadda ake yin wannan tare da misali.

Muna buƙatar sanya tantanin halitta adadin kwanakin da suka rage har zuwa ƙarshen watan yanzu. Kamar yadda a cikin hanyar da ta gabata, wannan zabin baya buƙatar buɗewa Wizards na Aiki. Kuna iya fitar da wannan magana a cikin tantanin halitta:

= RANAR (BAYAN (TODAY (); 0))) - DAY (TODAY ())

Bayan haka, yawan kwanakin har zuwa ƙarshen watan za'a nuna a cikin allon da aka nuna. Kowace rana, za a sabunta sakamakon ta atomatik, kuma daga farkon sabon zamani, kirgawa zai fara sabo. Ya zama wani nau'in ƙididdige lokacin ƙidaya.

Kamar yadda kake gani, wannan tsari ya kunshi bangarori biyu. Na farkon su magana ce da muka riga muka san ta ƙididdige yawan kwanakin cikin wata ɗaya:

= RANAR (BAYAN (TODAY (); 0))

Amma a kashi na biyu, an cire lambar yanzu daga wannan alamar:

-DAY (TODAY ())

Don haka, yayin aiwatar da wannan ƙididdigar, dabarar don ƙididdige yawan kwanakin wani sashe ne mai haɓaka mahimmin tsari.

Hanyar 4: Dabarar Madadin

Amma, abin takaici, ayoyin da suka gabata na Excel 2007 ba su da sanarwa MATA. Me game da waɗancan masu amfani waɗanda suke amfani da tsofaffin sigogin aikin? A gare su, wannan yiwuwar ta wanzu ne ta wani tsari, wanda ya fi girma fiye da yadda aka bayyana a sama. Bari mu ga yadda za a ƙididdige yawan kwanakin a cikin wata don lambar kalanda da aka bayar ta amfani da wannan zaɓi.

1. Zaɓi wayar don nuna sakamakon kuma je zuwa taga mai muhawara na mai aiki RANAR mun rigaya mun saba da mu ta hanya. Mun sanya siginan kwamfuta a cikin kawai filin wannan taga kuma danna kan maɓallin allan da aka juya zuwa hagu na masarar dabara. Je zuwa sashin "Sauran sifofin ...".



2. A cikin taga Wizards na Aiki a cikin rukunin "Kwanan wata da lokaci" zaɓi sunan DARIYA kuma danna maballin "Ok".



3. Ana fara aikin mai kunnawa DARIYA. Wannan aikin yana sauya kwanan wata daga tsari na yau da kullun zuwa lambobi masu ƙima, wanda ma'aikaci zai kasance zai aiwatar RANAR.

   Tagan da zai bude yana da filaye uku. A fagen "Ranar" zaka iya shigar lamba nan da nan "1". Zai zama matakin da ba za a taɓa ɗauka ba don kowane yanayi. Amma sauran filayen guda biyu zasu yi sosai.

   Sanya siginan kwamfuta a cikin filin "Shekara". Na gaba, za mu ci gaba zuwa zaɓin masu aiki ta hanyar alwatiƙar da aka saba.



4. Dukkansu iri ɗaya ne Wizards na Aiki zaɓi sunan "SHEKARA" kuma danna maballin "Ok".



5. Shafin Hujja Mai Furewa Mai Aiki SHEKARA. Yana ƙayyade shekara ta lambar da aka ƙayyade. A cikin akwatin taga guda "Kwanan wata a lamba saka hanyar haɗi zuwa tantanin da ke ɗauke da ainihin ranar da kake so ka ƙayyade yawan kwanakin. Bayan haka, kar a ruga don danna maballin "Ok", kuma danna sunan DARIYA a cikin dabarar dabara.



6. Sannan mun sake komawa taga muhawara DARIYA. Sanya siginan kwamfuta a cikin filin "Watan" da kuma matsa zuwa zaɓi na ayyuka.



7. A Mayen aiki danna sunan MATA kuma danna maballin "Ok".



8. Farashin muhawara na aiki yana farawa MATA. Ayyukanta sunyi kama da na mai aiki na baya, kawai tana nuna darajar lambar watan. A cikin kawai filin wannan taga, saita haɗin haɗi ɗaya zuwa lambar asali. To, a cikin hanyar mashaya, danna kan sunan RANAR.



9. Koma baya taga muhawara RANAR. Anan dole ne muyi karamin bugun jini guda daya. A cikin kawai taga na bayanan da bayanan bayanan ya kasance, ƙara magana zuwa ƙarshen dabarar "-1" ba tare da kwatancen ba, kuma saka "+1" bayan mai aiki MATA. Bayan haka, danna maɓallin "Ok".



10. Kamar yadda kake gani, a cikin tantanin da aka riga aka zaɓa, yawan kwanakin cikin watan wanda adadin lamba nasa ya bayyana. Babban tsari shine kamar haka:

    = RANAR (DARIYA (SHEKARA (D3); MONTH (D3) +1; 1) -1)

Sirrin wannan dabara yana da sauki. Muna amfani dashi don tantance ranar farkon ranar farko, sannan mu rage wata rana daga gare ta, da samun adadin ranakun da aka ambata. Sauyi a cikin wannan dabara shine batun tantanin halitta D3 a wurare biyu. Idan ka maye gurbinsa da adireshin tantanin halitta wanda kwanan wata yake a cikin takamaiman yanayinka, zaka iya fitar da wannan magana cikin kowane bangare na takardar ba tare da taimako ba. Wizards na Aiki.

Darasi: Sakamakon kwanan wata da ayyukan lokaci

Kamar yadda kake gani, akwai zaɓuɓɓuka da yawa don gano adadin kwanakin a cikin wata a cikin Excel. Wanne daga cikinsu zai yi amfani da shi ya dogara da babban maƙasudin mai amfani, da kuma irin nau'in shirin da yake amfani da shi.