Contents
Kalanda samarwa, watau jerin kwanakin, inda duk ranakun aiki da hutu ana yiwa alama daidai - abu ne mai mahimmanci ga kowane mai amfani da Microsoft Excel. A aikace, ba za ku iya yin ba tare da shi ba:
- a lissafin lissafin kudi (albashi, tsawon sabis, hutu ...)
- a cikin dabaru - don ƙayyadaddun ƙayyadaddun lokutan isarwa, la'akari da ƙarshen mako da hutu (tuna da classic "zuwa bayan hutu?")
- a cikin gudanar da aikin - don ƙididdigar daidaitattun sharuddan, yin la'akari, sake, kwanakin aiki ba aiki
- duk wani amfani da ayyuka kamar RANAR AIKI (RANAR AIKI) or MA'AIKATA TSAFTA (KWANAKI NETWORK), saboda suna buƙatar jerin biki a matsayin hujja
- Lokacin amfani da ayyukan Intelligence Time (kamar TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, da sauransu) a cikin Power Pivot da Power BI
- … da sauransu. da dai sauransu – da yawa na misalai.
Ya fi sauƙi ga waɗanda ke aiki a cikin tsarin ERP na kamfanoni kamar 1C ko SAP, kamar yadda aka gina kalandar samarwa a cikin su. Amma menene game da masu amfani da Excel?
Kuna iya, ba shakka, kiyaye irin wannan kalanda da hannu. Amma a lokacin dole ne ku sabunta shi aƙalla sau ɗaya a shekara (ko ma sau da yawa, kamar a cikin “jolly” 2020), shigar da hankali a duk ƙarshen mako, canja wuri da ranakun da ba aiki da gwamnatinmu ta ƙirƙira. Sannan maimaita wannan hanya kowace shekara mai zuwa. Rashin gajiya.
Ta yaya game da yin ɗan hauka da yin kalandar masana'anta "har abada" a cikin Excel? Wanda ke sabunta kanta, yana ɗaukar bayanai daga Intanet kuma koyaushe yana haifar da jerin abubuwan yau da kullun na kwanakin marasa aiki don amfani na gaba a cikin kowane lissafin? Gwaji?
Don yin wannan, a gaskiya, ba shi da wahala ko kaɗan.
Tushen bayanai
Babban tambaya shine a ina ake samun bayanan? Don neman tushen da ya dace, na bi ta hanyoyi da yawa:
- Ana buga ainihin hukunce-hukuncen a gidan yanar gizon gwamnati a cikin tsarin PDF (a nan, ɗaya daga cikinsu, alal misali) kuma suna ɓacewa nan da nan - ba za a iya fitar da bayanai masu amfani daga cikinsu ba.
- Wani zaɓi mai ban sha'awa, da kallo na farko, ya zama kamar "Open Data Portal of the Federation", inda akwai daidaitattun bayanai, amma, bayan jarrabawar kusa, duk abin ya zama bakin ciki. Shafin yana da matukar damuwa don shigo da shi cikin Excel, tallafin fasaha baya amsawa (keɓe kai?), Kuma bayanan da kansu sun ƙare a can na dogon lokaci - kalandar samarwa na 2020 an sabunta ta ƙarshe a cikin Nuwamba 2019 (abin kunya!) , ba shakka, ba ya ƙunshi “coronavirus” da kuma ‘ƙiri’a’ na karshen mako na 2020, alal misali.
Cikin rashin gamsuwa da majiyoyin hukuma, na fara tono wadanda ba na hukuma ba. Akwai da yawa daga cikinsu akan Intanet, amma yawancinsu, kuma, ba su dace da shigo da su cikin Excel ba kuma suna ba da kalandar samarwa a cikin kyawawan hotuna. Amma ba a gare mu mu rataya shi a bango ba, ko?
Kuma a cikin bincike, an gano wani abu mai ban mamaki da gangan - shafin http://xmlcalendar.ru/
Ba tare da "frills" da ba dole ba, wuri mai sauƙi, haske da sauri, mai kaifi don ɗawainiya ɗaya - don ba kowa da kowa kalandar samarwa don shekarar da ake so a cikin tsarin XML. Madalla!
Idan, ba zato ba tsammani, ba ku cikin sani ba, to, XML tsarin rubutu ne tare da abun ciki mai alama da na musamman
Kawai idan na tuntubi marubutan shafin kuma sun tabbatar da cewa shafin ya wanzu tsawon shekaru 7, ana sabunta bayanan da ke cikinsa koyaushe (har ma suna da reshe akan github don wannan) kuma ba za su rufe shi ba. Kuma ban damu ba ko kadan ni da ku muna loda bayanai daga gare ta don kowane ayyukanmu da lissafin mu a cikin Excel. Yana da kyauta Yana da kyau a san cewa akwai sauran mutane irin wannan! Girmamawa!
Ya rage don loda wannan bayanan zuwa cikin Excel ta amfani da add-in Query Query (na nau'ikan Excel 2010-2013 ana iya sauke shi kyauta daga gidan yanar gizon Microsoft, kuma a cikin nau'ikan Excel 2016 da sababbi an riga an gina shi ta tsohuwa. ).
Hankalin ayyukan zai kasance kamar haka:
- Muna yin buƙatar zazzage bayanai daga rukunin yanar gizon na kowace shekara guda
- Juya bukatar mu zuwa aiki
- Muna amfani da wannan aikin zuwa jerin duk shekarun da ake da su, farawa daga 2013 har zuwa shekara ta yanzu - kuma muna samun kalandar samarwa "har abada" tare da sabuntawa ta atomatik. Voila!
Mataki 1. Shigo da kalanda na shekara guda
Na farko, loda kalandar samarwa na kowace shekara, misali, don 2020. Don yin wannan, a cikin Excel, je zuwa shafin. data (ko Tambayar .arfiidan kun shigar dashi azaman add-on daban) kuma zaɓi Daga Intanet (Daga Yanar Gizo). A cikin taga da ke buɗewa, liƙa hanyar haɗi zuwa shekara mai dacewa, kofe daga rukunin yanar gizon:
Bayan danna kan OK taga preview ya bayyana, wanda a cikinsa kuna buƙatar danna maɓallin Maida Bayanai (Canja bayanai) or Don canza bayanai (gyara bayanai) kuma za mu je taga editan tambaya ta Power, inda za mu ci gaba da aiki da bayanan:
Nan da nan za ku iya sharewa cikin aminci a cikin rukunin dama Neman Sigogi (Saitunan tambaya) mataki nau'in gyarawa (An Canja Nau'in) Ba ma bukatarsa.
Tebur a cikin ginshiƙi na bukukuwa ya ƙunshi lambobi da kwatancin kwanakin da ba aiki ba - za ku iya ganin abubuwan da ke ciki ta hanyar "fadi" sau biyu ta danna kan kalmar kore Table:
Don komawa baya, dole ne a goge duk matakan da suka bayyana a dama a cikin sashin dama source (Source).
Teburi na biyu, wanda za'a iya shiga ta irin wannan hanya, ya ƙunshi ainihin abin da muke buƙata - kwanakin duk kwanakin da ba aiki:
Ya rage don sarrafa wannan farantin, wato:
1. Tace kwanakin hutu kawai (watau su) ta shafi na biyu Sifa:t
2. Share duk ginshiƙai ban da na farko - ta danna-dama a kan taken shafi na farko kuma zaɓi umarnin Share wasu ginshiƙai (Cire Wasu Rukunoni):
3. Raba shafi na farko da digo daban na wata da rana tare da umarni Rarraba Rukunin - Ta Delimiter tab Sake Kama (Canza - Raba shafi - Ta hanyar iyakance):
4. Kuma a ƙarshe ƙirƙirar shafi mai ƙididdigewa tare da kwanakin al'ada. Don yin wannan, a kan tab Ƙara ginshiƙi danna maballin Rukunin al'ada (Ƙara Rukunin - Shagon Custom) kuma shigar da dabara mai zuwa a cikin taga wanda ya bayyana:
=# kwanan wata(2020, [#"Sifa:d.1″], [#"Sifa:d.2″])
Anan, ma'aikacin # kwanan wata yana da hujja guda uku: shekara, wata, da rana, bi da bi. Bayan danna kan OK muna samun ginshiƙin da ake buƙata tare da kwanakin ƙarshe na yau da kullun, kuma muna share ragowar ginshiƙan kamar a mataki na 2
Mataki 2. Juya bukatar zuwa aiki
Ayyukanmu na gaba shine musanya tambayar da aka ƙirƙira don 2020 zuwa aikin duniya na kowace shekara (lambar shekarar zata zama hujjarsa). Don yin wannan, muna yin haka:
1. Fadada (idan ba a riga an faɗaɗa ba) panel Tambayoyi (Tambayoyi) a hagu a cikin Tagar Query:
2. Bayan canza buƙatun zuwa aiki, ikon ganin matakan da suka haɗa da buƙatar da sauƙin gyara su, abin takaici, yana ɓacewa. Sabili da haka, yana da ma'ana don yin kwafin buƙatarmu kuma a riga an yi la'akari da shi, kuma a bar ainihin a ajiye. Don yin wannan, danna dama a cikin sashin hagu akan buƙatar kalandarmu kuma zaɓi umarnin Kwafi.
Danna dama akan sakamakon kwafin kalanda(2) zai zaɓi umarnin Sake suna (Sake suna) kuma shigar da sabon suna - bari ya zama, misali, fxYear:
3. Muna buɗe lambar tushen tambaya a cikin yaren Query na ciki (ana kiranta a takaice "M") ta amfani da umarnin Babban Edita tab review(Duba - Babban Edita) da yin ƙananan canje-canje a can don juya buƙatarmu zuwa aiki na kowace shekara.
Ya kasance:
Bayan:
Idan kuna sha'awar cikakkun bayanai, to anan:
- (shekara a matsayin lamba)=> - mun bayyana cewa aikinmu zai sami hujjar lamba ɗaya - mai canzawa shekara
- Manna da m shekara zuwa mahaɗin yanar gizo a mataki source. Tun da Query Query baya ƙyale ku manna lambobi da rubutu, muna canza lambar shekara zuwa rubutu akan tashi ta amfani da aikin Lamba.ToText
- Muna canza canjin shekara don 2020 a cikin babban mataki #”An ƙara abu na al'ada«, inda muka kafa kwanan wata daga guntu.
Bayan danna kan Gama bukatar mu ta zama aiki:
Mataki 3. Shigo da kalanda na duk shekaru
Abu na ƙarshe da ya rage shi ne yin tambaya ta ƙarshe, wacce za ta loda bayanai don duk shekarun da ake da su kuma ya ƙara duk kwanakin hutu da aka karɓa cikin tebur ɗaya. Don wannan:
1. Mun danna a cikin ɓangaren tambaya na hagu a cikin sarari mai launin toka mai launin toka tare da maɓallin linzamin kwamfuta na dama kuma zaɓi bi da bi Sabuwar buƙatu - Wasu tushe - Buƙatun fanko (Sabuwar Tambaya - Daga wasu tushe - tambaya mara kyau):
2. Muna buƙatar samar da jerin duk shekarun da za mu buƙaci kalanda, watau 2013, 2014 … 2020. Don yin wannan, a cikin madaidaicin ma'anar tambayar mara komai da ta bayyana, shigar da umarnin:
Tsarin:
={LambarA..NumberB}
… a cikin Power Query yana haifar da jerin lambobi daga A zuwa B. Misali, furcin
={1..5}
… zai samar da jerin sunayen 1,2,3,4,5.
Da kyau, don kada a ɗaure da ƙarfi zuwa 2020, muna amfani da aikin DateTime.LocalNow() - Analog na aikin Excel TODAY (YAU) a cikin Query Query - kuma cire daga gare ta, bi da bi, shekara ta yanzu ta aikin Kwanan wata.Shekara.
3. Sakamakon saitin shekaru, ko da yake yana da kyau sosai, ba tebur ba ne don Tambayar Wuta, amma abu ne na musamman - list (Jeri). Amma canza shi zuwa tebur ba matsala ba ne: danna maɓallin kawai Zuwa tebur (Zuwa Tebur) a kusurwar hagu na sama:
4. Gama layin! Aiwatar da aikin da muka ƙirƙira a baya fxYear zuwa sakamakon jerin shekaru. Don yin wannan, a kan tab Ƙara ginshiƙi danna maɓallin Kira aikin al'ada (Ƙara Shagon - Kira Aiki na Musamman) kuma saita hujjarsa kawai - shafi Column1 tsawon shekaru:
Bayan danna kan OK aikin mu fxYear shigo da zai yi aiki bi da bi na kowace shekara kuma za mu sami ginshiƙi inda kowane tantanin halitta zai ƙunshi tebur tare da kwanakin kwanakin da ba aiki ba (abin da ke cikin tebur yana bayyane a fili idan kun danna bayan tantanin halitta kusa da shi. kalmar Table):
Ya rage don faɗaɗa abubuwan da ke cikin tebur ɗin gida ta danna gunkin da ke da kibau biyu a cikin taken shafi. Dates (cika Yi amfani da sunan ginshiƙi na asali azaman prefix ana iya cire shi):
... da kuma bayan danna kan OK mun sami abin da muke so - jerin duk bukukuwa daga 2013 zuwa wannan shekara:
Shafin farko, wanda ba dole ba ne, ana iya share shi, kuma na biyu, saita nau'in bayanai date (Kwanan wata) a cikin jerin zaɓuka a cikin taken shafi:
Tambayar kanta za a iya sake suna wani abu mai ma'ana fiye da Tambaya1 sa'an nan kuma loda sakamakon zuwa takardar a cikin nau'i na tebur "mai hankali" mai ƙarfi ta amfani da umarnin rufe da saukewa tab Gida (Gida - Rufe & Load):
Kuna iya sabunta kalanda da aka ƙirƙira a nan gaba ta danna dama akan tebur ko tambaya a cikin dama ta hanyar umarnin Sabunta & Ajiye. Ko amfani da maɓallin Wartsake duka tab data (Kwanan Wata - Refresh All) ko gajeriyar hanyar keyboard Ctrl+alt+F5.
Shi ke nan.
Yanzu ba kwa buƙatar sake ɓata lokaci da tunani-mai neman da sabunta jerin bukukuwan - yanzu kuna da kalanda na samarwa "har abada". A kowane hali, idan dai marubutan shafin http://xmlcalendar.ru/ sun goyi bayan zuriyarsu, wanda, ina fata, zai kasance na dogon lokaci (na gode musu kuma!).
- Shigo da ƙimar bitcoin don yin fice daga intanet ta hanyar Query Query
- Nemo ranar kasuwanci ta gaba ta amfani da aikin AIKI
- Nemo mahadar tazarar kwanan wata