Kalendala ya Factory mu Excel

Kalendala yopanga, mwachitsanzo, mndandanda wamasiku, pomwe masiku onse ogwira ntchito ndi maholide amalembedwa moyenerera - chinthu chofunikira kwambiri kwa aliyense wogwiritsa ntchito Microsoft Excel. Pochita, simungathe kuchita popanda izo:

• kuwerengera ndalama (malipiro, kutalika kwa ntchito, tchuthi ...)
• mu mayendedwe - kuti mudziwe bwino nthawi yobweretsera, poganizira kumapeto kwa sabata ndi tchuthi (kumbukirani zachikale "zobwera pambuyo pa tchuthi?")
  
• mu kayendetsedwe ka polojekiti - pakuwerengera kolondola kwa mawu, poganizira, kachiwiri, masiku ogwira ntchito osagwira ntchito
• kugwiritsa ntchito kulikonse ngati TSIKU LA NTCHITO (TSIKU LA NTCHITO) or NTCHITO WOYERA (NETWORKDAYS), chifukwa amafuna mndandanda wa maholide monga mkangano
• mukamagwiritsa ntchito Time Intelligence (monga TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, ndi zina zotero) mu Power Pivot ndi Power BI
  
• … etc. etc. – zitsanzo zambiri.
  

Ndizosavuta kwa iwo omwe amagwira ntchito mu machitidwe a ERP monga 1C kapena SAP, monga momwe kalendala yopangira imapangidwira. Koma bwanji za ogwiritsa ntchito a Excel?

Mukhoza, ndithudi, kusunga kalendala yotere pamanja. Koma ndiye kuti muyenera kusintha kamodzi pachaka (kapena nthawi zambiri, monga mu "chisangalalo" 2020), kulowa mosamala kumapeto kwa sabata, kusamutsidwa ndi masiku osagwira ntchito opangidwa ndi boma lathu. Ndiyeno kubwereza ndondomeko chaka chamawa chilichonse. Kutopa.

Nanga bwanji kupenga pang'ono ndikupanga kalendala ya fakitale "yosatha" ku Excel? Imodzi yomwe imadzisintha yokha, imatenga deta kuchokera pa intaneti ndipo nthawi zonse imapanga mndandanda wamasiku omwe sakugwira ntchito kuti agwiritsidwe ntchito powerengera? Kuyesa?

Kuchita izi, kwenikweni, sikovuta konse.

Gwero la data

Funso lalikulu ndi komwe mungapeze deta? Pofunafuna gwero labwino, ndinadutsa njira zingapo:

• Zolemba zoyambirira zimasindikizidwa patsamba la boma mumtundu wa PDF (apa, imodzi mwazo, mwachitsanzo) ndikuzimiririka nthawi yomweyo - zambiri zothandiza sizingatulutsidwe mwa iwo.
• Njira yoyesera, poyang'ana koyamba, inkawoneka ngati "Open Data Portal of the Federation", pomwe pali deta yofananira, koma, poyang'anitsitsa, chirichonse chinakhala chachisoni. Tsambali ndilovuta kwambiri kuitanitsa ku Excel, chithandizo chaukadaulo sichimayankha (kudzipatula?), Ndipo deta yokhayo idachoka pamenepo kwanthawi yayitali - kalendala yopanga 2020 idasinthidwa komaliza mu Novembala 2019 (manyazi!) , zachidziwikire, mulibe "coronavirus" yathu komanso sabata la 'kuvota' la 2020, mwachitsanzo.

Chifukwa chokhumudwa ndi magwero a boma, ndinayamba kukumba nkhani zosavomerezeka. Pali ambiri aiwo pa intaneti, koma ambiri aiwo, nawonso, ndi osayenera konse kuitanitsa ku Excel ndikupereka kalendala yopanga mawonekedwe azithunzi zokongola. Koma si kwa ife kuyipachika pakhoma eti?

Ndipo mkati mofufuza, chinthu chodabwitsa chinapezeka mwangozi - tsamba la http://xmlcalendar.ru/

Popanda "zozizira" zosafunikira, tsamba losavuta, lopepuka komanso lofulumira, lokonzedwa kuti ligwire ntchito imodzi - kupatsa aliyense kalendala yopangira chaka chomwe akufuna mu mtundu wa XML. Zabwino kwambiri!

Ngati, mwadzidzidzi, simukudziwa, ndiye kuti XML ndi mtundu wamawu wokhala ndi zolembedwa zapadera . Zopepuka, zosavuta komanso zowerengeka ndi mapulogalamu amakono, kuphatikiza Excel.

Zikatero, ndinalumikizana ndi olemba malowa ndipo adatsimikizira kuti malowa akhalapo kwa zaka 7, deta yomwe ili payo imasinthidwa nthawi zonse (iwo ali ndi nthambi pa github pa izi) ndipo sadzatseka. Ndipo sindisamala konse kuti inu ndi ine timayika deta kuchokera pamenepo pamapulojekiti athu aliwonse ndi kuwerengera mu Excel. Ndi mfulu. Ndizosangalatsa kudziwa kuti anthu adakali otere! Ulemu!

Ikutsalirabe kuyika izi mu Excel pogwiritsa ntchito Power Query add-in (kwa mitundu ya Excel 2010-2013 itha kutsitsidwa kwaulere patsamba la Microsoft, ndipo m'mitundu ya Excel 2016 ndi yatsopano idamangidwa kale mwachisawawa. ).

logic ya zochita adzakhala motere:

1. Timapempha kuti titsitse deta kuchokera pa webusaitiyi kwa chaka chimodzi
2. Kutembenuza pempho lathu kukhala ntchito
3. Timagwiritsa ntchito ntchitoyi pamndandanda wazaka zonse zomwe zilipo, kuyambira 2013 mpaka chaka chino - ndipo timapeza kalendala yopangira "yosatha" yokhala ndi zosintha zokha. Voila!
   

Gawo 1. Tengani kalendala kwa chaka chimodzi

Choyamba, tsegulani kalendala yopanga chaka chilichonse, mwachitsanzo, cha 2020. Kuti muchite izi, mu Excel, pitani ku tabu. Deta (kapena Kufunsa Mphamvungati mudayiyika ngati chowonjezera chosiyana) ndikusankha Kuchokera pa intaneti (Kuchokera pa Webusaiti). Pazenera lomwe limatsegulidwa, ikani ulalo wa chaka chofananira, chojambulidwa patsambalo:

Pambuyo pang'anani OK zenera lowonera likuwonekera, momwe muyenera dinani batani Sinthani Data (Sinthani data) or Kusintha deta (Sinthani data) ndipo tifika pawindo la Power Query query editor, kumene tidzapitiriza kugwira ntchito ndi deta:

Yomweyo mukhoza bwinobwino kuchotsa mu gulu lamanja Pemphani Parameters (Zokonda zamafunso) sitepe mtundu wosinthidwa (Mtundu Wosinthidwa) Ife sitikumufuna iye.

Gome lomwe lili pagulu la tchuthi lili ndi ma code ndi mafotokozedwe a masiku osagwira ntchito - mutha kuwona zomwe zili mkati mwake mwa "kugwa" kawiri podina mawu obiriwira. Table:

Kuti mubwerere, muyenera kufufuta mu gulu lamanja masitepe onse omwe adawonekera gwero (Chitsime).

Gome lachiwiri, lomwe litha kupezeka mwanjira yofananira, lili ndi zomwe tikufuna - masiku amasiku onse osagwira ntchito:

Zimatsalira kukonza mbale iyi, yomwe ndi:

1. Sankhani masiku atchuthi okha (ie awo) ndi gawo lachiwiri Khalidwe:t

2. Chotsani mizati yonse kupatula yoyamba - podina kumanja pamutu wagawo loyamba ndikusankha lamulo Chotsani zigawo zina (Chotsani zigawo Zina):

3. Gawani gawo loyamba ndi dontho padera pa mwezi ndi tsiku ndi lamulo Gawani Mzere - Wolemba Delimiter tsamba Transformation (Sinthani - Gawani ndime - Mwa delimiter):

4. Ndipo pomaliza pangani gawo lowerengeka lomwe lili ndi masiku abwinobwino. Kuti muchite izi, dinani pa tabu Kuwonjezera ndime dinani batani Mzati mwamakonda (Onjezani Mgawo - Mzere Wamakonda) ndipo lowetsani fomula ili pawindo lomwe likuwoneka:

=#deti(2020, [#»Mawonekedwe:d.1″], [#»Mawonekedwe:d.2″])

Apa, wogwiritsa ntchito #date ali ndi zotsutsana zitatu: chaka, mwezi, ndi tsiku, motsatana. Pambuyo kuwonekera pa OK timapeza gawo lofunikira lomwe lili ndi masiku abwino a sabata, ndikuchotsa magawo otsala monga momwe zilili mu gawo 2

Gawo 2. Kutembenuza pempho kukhala ntchito

Ntchito yathu yotsatira ndikutembenuza funso lomwe lapangidwa mu 2020 kukhala ntchito yapadziko lonse lapansi chaka chilichonse (chiwerengero cha chaka chizikhala mkangano wake). Kuti tichite izi, timachita izi:

1. Kukulitsa (ngati sikunakulitsidwe kale) gulu Mafunso (Mafunso) kumanzere pawindo la Power Query:

2. Pambuyo potembenuza pempholo kukhala ntchito, kuthekera kuwona masitepe omwe amapanga pempho ndikusintha mosavuta, mwatsoka, kutha. Chifukwa chake, ndizomveka kupanga kope la pempho lathu ndikusewera nalo kale, ndikusiya choyambiriracho posungira. Kuti muchite izi, dinani kumanja pagawo lakumanzere pa pempho lathu lakalendala ndikusankha Duplicate lamulo.

Kudinanso pomwe pachotsatira cha kalendala(2) kudzasankha lamulo Sinthaninso (Tchulaninso) ndipo lowetsani dzina latsopano - zikhale, mwachitsanzo, fxYear:

3. Timatsegula kachidindo kameneko m'chinenero cha Power Query (chotchedwa "M") pogwiritsa ntchito lamulo. Advanced Editor tsamba Review(Onani - Advanced Editor) ndi kupanga zosintha zazing'ono kumeneko kuti tisinthe pempho lathu kukhala ntchito ya chaka chilichonse.

Zinali:

Pambuyo pake:

Ngati mukufuna zambiri, nayi:

• (chaka monga nambala)=>  - timalengeza kuti ntchito yathu idzakhala ndi mkangano umodzi wa nambala - kusintha chaka
• Kuyika kusintha chaka ku ulalo wapaintaneti mu sitepe gwero. Popeza Power Query sikukulolani kumata manambala ndi zolemba, timatembenuza nambala ya chaka kuti ikhale yolemba pa ntchentche pogwiritsa ntchito ntchitoyi. Nambala.ToText
• Timasintha chaka cha 2020 mu gawo lomaliza #"Anawonjezera chinthu chokhazikika«, kumene tinapanga tsiku kuchokera ku zidutswa.
  

Pambuyo pang'anani chitsiriziro pempho lathu limakhala ntchito:

Gawo 3. Tengani makalendala kwa zaka zonse

Chomaliza chomwe chatsala ndikupanga funso lalikulu lomaliza, lomwe lidzakwezera deta yazaka zonse zomwe zilipo ndikuwonjezera masiku onse olandiridwa patchuthi patebulo limodzi. Za ichi:

1. Timadina pagawo lakumanzere lafunso pamalo opanda imvi ndi batani lakumanja la mbewa ndikusankha motsatizana Pempho latsopano - Zochokera kwina - Pempho lopanda kanthu (Funso Latsopano - Kuchokera kuzinthu zina - Funso lopanda kanthu):

2. Tiyenera kupanga mndandanda wazaka zonse zomwe tidzapemphe makalendala, mwachitsanzo, 2013, 2014 … 2020. Kuti tichite izi, mu bar ya fomula ya funso lopanda kanthu lomwe likuwoneka, lowetsani lamulo:

Makhalidwe:

={NambalaA..NambalaB}

… mu Power Query amapanga mndandanda wa manambala kuyambira A mpaka B. Mwachitsanzo, mawuwa

={1..5}

… angatulutse mndandanda wa 1,2,3,4,5.

Chabwino, kuti tisamangike mwamphamvu ku 2020, timagwiritsa ntchito ntchitoyi DateTime.LocalNow() - analogue ya ntchito ya Excel TODAY (LERO) mu Power Query - ndikuchotsamo, nayenso, chaka chino ndi ntchitoyo Tsiku.Chaka.

3. Zaka zomwe zimatsatira, ngakhale zikuwoneka zokwanira, si tebulo la Power Query, koma chinthu chapadera - mndandanda (Mndandanda). Koma kuyisintha kukhala tebulo si vuto: ingodinani batani Ku tebulo (Ku Table) pakona yakumanzere:

4. Malizitsani mzere! Kugwiritsa ntchito zomwe tapanga kale fxYear ku mndandanda wazaka zotsatira. Kuti muchite izi, dinani pa tabu Kuwonjezera ndime kanikizani batani Imbani makonda ntchito (Onjezani Mgawo - Itanitsani Ntchito Yachizolowezi) ndi kukhazikitsa mkangano wake wokhawokha - ndime Column1 pazaka:

Pambuyo pang'anani OK ntchito yathu fxYear kulowetsako kudzagwira ntchito motsatira chaka chilichonse ndipo tidzapeza mzati pomwe selo lililonse limakhala ndi tebulo lokhala ndi masiku osagwira ntchito (zomwe zili patebulo zimawoneka bwino mukadina kumbuyo kwa selo pafupi ndi mawu Table):

Ikutsalirabe kukulitsa zomwe zili m'matebulo okhala zisa podina chizindikirocho ndi mivi iwiri pamutu madeti (maka Gwiritsani ntchito dzina lazagawo loyambirira ngati choyambirira ikhoza kuchotsedwa):

... ndipo pambuyo kuwonekera OK timapeza zomwe timafuna - mndandanda wa tchuthi chonse kuyambira 2013 mpaka chaka chino:

Yoyamba, yomwe ili kale yosafunikira, ikhoza kuchotsedwa, ndipo yachiwiri, ikani mtundu wa data tsiku (Tsiku) pamndandanda wotsikira patsamba lamutu:

Funso lokha litha kusinthidwanso kuti likhale latanthauzo kuposa Pempho 1 ndiyeno kwezani zotsatira pa pepala mu mawonekedwe amphamvu "anzeru" tebulo ntchito lamulo kutseka ndikutsitsa tsamba Kunyumba (Kunyumba - Tsekani & Katundu):

Mutha kusintha kalendala yomwe idapangidwa m'tsogolomu podina kumanja patebulo kapena funsani patsamba lakumanja kudzera mu lamulo. Sinthani & Sungani. Kapena gwiritsani ntchito batani Tsitsani zonse tsamba Deta (Tsiku - Tsitsani Zonse) kapena njira yachidule ya kiyibodi Ctrl+alt+F5.

Ndizomwezo.

Tsopano simudzafunikanso kutaya nthawi ndi malingaliro-mafuta kufunafuna ndikusintha mndandanda watchuthi - tsopano muli ndi kalendala "yosatha" yopanga. Mulimonsemo, malinga ngati olemba malo http://xmlcalendar.ru/ amathandizira ana awo, omwe, ndikuyembekeza, adzakhala kwa nthawi yayitali kwambiri (zikomo kwa iwo kachiwiri!).

• Lowetsani mtengo wa bitcoin kuti mupambane kuchokera pa intaneti kudzera pa Power Query
• Kupeza tsiku lotsatira lantchito pogwiritsa ntchito WORKDAY
  
• Kupeza mphambano ya nthawi zamasiku