Cynnwys
Calendr cynhyrchu, hy rhestr o ddyddiadau, lle mae'r holl ddyddiau gwaith swyddogol a gwyliau wedi'u nodi'n unol â hynny - peth cwbl angenrheidiol i unrhyw ddefnyddiwr Microsoft Excel. Yn ymarferol, ni allwch wneud hebddo:
- mewn cyfrifiadau cyfrifo (cyflog, hyd gwasanaeth, gwyliau ...)
- mewn logisteg - ar gyfer pennu amseroedd dosbarthu yn gywir, gan ystyried penwythnosau a gwyliau (cofiwch y clasur "dewch ymlaen ar ôl y gwyliau?")
- ym maes rheoli prosiect – i gael yr amcangyfrif cywir o delerau, gan ystyried, unwaith eto, diwrnodau gwaith nad ydynt yn rhai gwaith
- unrhyw ddefnydd o swyddogaethau fel GWAITH (DYDD GWAITH) or GWEITHWYR PUR (DYDDIAU RHWYDWAITH), oherwydd bod angen rhestr o wyliau arnynt fel dadl
- wrth ddefnyddio swyddogaethau Time Intelligence (fel TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, ac ati) yn Power Pivot a Power BI
- … ac ati ac ati – llawer o enghreifftiau.
Mae'n haws i'r rhai sy'n gweithio mewn systemau ERP corfforaethol fel 1C neu SAP, gan fod y calendr cynhyrchu wedi'i ymgorffori ynddynt. Ond beth am ddefnyddwyr Excel?
Gallwch, wrth gwrs, gadw calendr o'r fath â llaw. Ond yna bydd yn rhaid i chi ei ddiweddaru o leiaf unwaith y flwyddyn (neu hyd yn oed yn amlach, fel yn y “jolly” 2020), gan fynd i mewn yn ofalus ar yr holl benwythnosau, trosglwyddiadau a diwrnodau di-waith a ddyfeisiwyd gan ein llywodraeth. Ac yna ailadrodd y weithdrefn hon bob blwyddyn nesaf. Diflastod.
Beth am fynd ychydig yn wallgof a gwneud calendr ffatri “parhaol” yn Excel? Un sy'n diweddaru ei hun, yn cymryd data o'r Rhyngrwyd ac sydd bob amser yn cynhyrchu rhestr gyfredol o ddiwrnodau di-waith i'w defnyddio wedyn mewn unrhyw gyfrifiadau? Temtio?
Nid yw gwneud hyn, mewn gwirionedd, yn anodd o gwbl.
Ffynhonnell data
Y prif gwestiwn yw ble i gael y data? Wrth chwilio am ffynhonnell addas, es i trwy sawl opsiwn:
- Mae'r archddyfarniadau gwreiddiol yn cael eu cyhoeddi ar wefan y llywodraeth ar ffurf PDF (yma, un ohonynt, er enghraifft) ac yn diflannu ar unwaith - ni ellir tynnu gwybodaeth ddefnyddiol ohonynt.
- Roedd yn ymddangos mai opsiwn demtasiwn, ar yr olwg gyntaf, oedd “Porth Data Agored y Ffederasiwn”, lle mae set ddata gyfatebol, ond, o edrych yn agosach, trodd popeth yn drist. Mae'r wefan yn hynod anghyfleus ar gyfer mewnforio i Excel, nid yw cymorth technegol yn ymateb (hunan ynysu?), Ac mae'r data ei hun wedi dyddio yno ers amser maith - diweddarwyd y calendr cynhyrchu ar gyfer 2020 ddiwethaf ym mis Tachwedd 2019 (gwarth!) a , wrth gwrs, nid yw’n cynnwys ein “coronafeirws’ a phenwythnos ‘pleidleisio’ 2020, er enghraifft.
Wedi fy dadrithio â ffynonellau swyddogol, dechreuais gloddio rhai answyddogol. Mae yna lawer ohonynt ar y Rhyngrwyd, ond mae'r rhan fwyaf ohonynt, unwaith eto, yn gwbl anaddas ar gyfer mewnforio i Excel a rhoi calendr cynhyrchu ar ffurf lluniau hardd. Ond nid ein lle ni yw ei hongian ar y wal, iawn?
Ac yn y broses o chwilio, darganfuwyd rhywbeth rhyfeddol yn ddamweiniol - y wefan http://xmlcalendar.ru/
Heb “frills” diangen, gwefan syml, ysgafn a chyflym, wedi'i hogi ar gyfer un dasg - i roi calendr cynhyrchu i bawb ar gyfer y flwyddyn a ddymunir mewn fformat XML. Ardderchog!
Os, yn sydyn, nad ydych chi'n gwybod, yna mae XML yn fformat testun gyda chynnwys wedi'i farcio ag arbennig
Rhag ofn, cysylltais ag awduron y wefan a chadarnhawyd bod y wefan wedi bodoli ers 7 mlynedd, mae'r data arno'n cael ei ddiweddaru'n gyson (mae ganddyn nhw gangen ar github ar gyfer hyn hyd yn oed) ac nid ydyn nhw'n mynd i'w chau. Ac nid oes ots gennyf o gwbl eich bod chi a minnau'n llwytho data ohono ar gyfer unrhyw un o'n prosiectau a'n cyfrifiadau yn Excel. Yn rhad ac am ddim. Mae'n braf gwybod bod yna bobl fel hyn o hyd! Parch!
Mae'n dal i fod i lwytho'r data hwn i Excel gan ddefnyddio'r ategyn Power Query (ar gyfer fersiynau o Excel 2010-2013 gellir ei lawrlwytho am ddim o wefan Microsoft, ac mewn fersiynau o Excel 2016 a mwy newydd mae eisoes wedi'i ymgorffori yn ddiofyn ).
Bydd rhesymeg y camau gweithredu fel a ganlyn:
- Rydym yn gwneud cais i lawrlwytho data o'r wefan am unrhyw flwyddyn
- Troi ein cais yn swyddogaeth
- Rydyn ni'n cymhwyso'r swyddogaeth hon i'r rhestr o'r holl flynyddoedd sydd ar gael, gan ddechrau o 2013 a hyd at y flwyddyn gyfredol - ac rydyn ni'n cael calendr cynhyrchu “parhaol” gyda diweddariad awtomatig. Ystyr geiriau: Voila!
Cam 1. Mewnforio calendr am flwyddyn
Yn gyntaf, llwythwch y calendr cynhyrchu ar gyfer unrhyw flwyddyn, er enghraifft, ar gyfer 2020. I wneud hyn, yn Excel, ewch i'r tab Dyddiad (neu Ymholiad Pweros gwnaethoch ei osod fel ychwanegiad ar wahân) a dewiswch O'r Rhyngrwyd (O'r We). Yn y ffenestr sy'n agor, gludwch y ddolen i'r flwyddyn gyfatebol, wedi'i chopïo o'r wefan:
Ar ôl clicio ar OK mae ffenestr rhagolwg yn ymddangos, lle mae angen i chi glicio ar y botwm Trosi Data (Trawsnewid data) or I newid y data (Golygu data) a byddwn yn cyrraedd ffenestr golygydd ymholiad Power Query, lle byddwn yn parhau i weithio gyda'r data:
Ar unwaith gallwch chi ddileu yn ddiogel yn y panel cywir Paramedrau Cais (Gosodiadau ymholiad) camu math wedi'i addasu (Math wedi'i Newid) Nid oes ei angen arnom.
Mae’r tabl yn y golofn gwyliau yn cynnwys codau a disgrifiadau o ddiwrnodau di-waith – gallwch weld ei gynnwys drwy “syrthio” ddwywaith trwy glicio ar y gair gwyrdd Tabl:
I fynd yn ôl, bydd yn rhaid i chi ddileu yn y panel iawn yr holl gamau sydd wedi ymddangos yn ôl i ffynhonnell (Ffynhonnell).
Mae’r ail dabl, y gellir ei gyrchu mewn ffordd debyg, yn cynnwys yn union yr hyn sydd ei angen arnom – dyddiadau pob diwrnod nad yw’n ddiwrnod gwaith:
Mae angen prosesu'r plât hwn o hyd, sef:
1. Hidlo dyddiadau gwyliau yn unig (hy rhai) erbyn yr ail golofn Priodol: t
2. Dileu pob colofn ac eithrio'r gyntaf - trwy dde-glicio ar bennawd y golofn gyntaf a dewis y gorchymyn Dileu colofnau eraill (Dileu Colofnau Eraill):
3. Rhannwch y golofn gyntaf â dot ar wahân am fis a diwrnod gyda gorchymyn Colofn Hollti - Yn ôl Amffinydd tab Trawsnewid (Trawsnewid - Colofn hollti - Yn ôl amffinydd):
4. Ac yn olaf creu colofn wedi'i chyfrifo gyda dyddiadau arferol. I wneud hyn, ar y tab Ychwanegu colofn cliciwch ar y botwm Colofn personol (Ychwanegu Colofn - Colofn Custom) a rhowch y fformiwla ganlynol yn y ffenestr sy'n ymddangos:
=#dyddiedig(2020, [#»Priodoledd:d.1″], [#» Priodoledd:d.2″])
Yma, mae gan y gweithredwr #date dair dadl: blwyddyn, mis, a diwrnod, yn y drefn honno. Ar ôl clicio ar OK rydym yn cael y golofn ofynnol gyda dyddiadau penwythnos arferol, ac yn dileu'r colofnau sy'n weddill fel yng ngham 2
Cam 2. Troi'r cais yn swyddogaeth
Ein tasg nesaf yw trosi’r ymholiad a grëwyd ar gyfer 2020 yn swyddogaeth gyffredinol ar gyfer unrhyw flwyddyn (rhif y flwyddyn fydd ei ddadl). I wneud hyn, rydym yn gwneud y canlynol:
1. Ehangu (os nad yw wedi'i ehangu eisoes) y panel Ymholiadau (Ymholiadau) ar y chwith yn y ffenestr Power Query:
2. Ar ôl trosi'r cais i swyddogaeth, mae'r gallu i weld y camau sy'n rhan o'r cais a'u golygu'n hawdd, yn anffodus, yn diflannu. Felly, mae'n gwneud synnwyr i wneud copi o'n cais a frolic eisoes gydag ef, a gadael y gwreiddiol wrth gefn. I wneud hyn, de-gliciwch yn y cwarel chwith ar ein cais calendr a dewiswch y gorchymyn Dyblyg.
Bydd clicio ar y dde eto ar y copi canlyniadol o galendr (2) yn dewis y gorchymyn Ailenwi (Ailenwi) a rhowch enw newydd - gadewch iddo fod, er enghraifft, fxBlwyddyn:
3. Rydym yn agor cod ffynhonnell yr ymholiad yn yr iaith Power Query fewnol (fe'i gelwir yn gryno "M") gan ddefnyddio'r gorchymyn Uwch Olygydd tab adolygiad(Gweld - Golygydd Uwch) a gwneud mân newidiadau yno i droi ein cais yn swyddogaeth ar gyfer unrhyw flwyddyn.
Yr oedd:
Ar ôl:
Os oes gennych ddiddordeb yn y manylion, yna yma:
- (blwyddyn fel rhif) => – rydym yn datgan y bydd gan ein swyddogaeth un ddadl rifiadol – newidyn flwyddyn
- Gludo'r newidyn flwyddyn i ddolen gwe fesul cam ffynhonnell. Gan nad yw Power Query yn caniatáu ichi ludo rhifau a thestun, rydym yn trosi rhif y flwyddyn i destun ar y hedfan gan ddefnyddio'r swyddogaeth Rhif.ToText
- Rydym yn amnewid y newidyn blwyddyn ar gyfer 2020 yn y cam olaf ond un #”Ychwanegwyd gwrthrych personol«, lle y ffurfiwyd y dyddiad o'r darnau.
Ar ôl clicio ar Gorffen daw ein cais yn swyddogaeth:
Cam 3. Mewnforio calendrau ar gyfer pob blwyddyn
Y peth olaf sydd ar ôl yw gwneud y prif ymholiad olaf, a fydd yn uwchlwytho data ar gyfer yr holl flynyddoedd sydd ar gael ac yn ychwanegu'r holl ddyddiadau gwyliau a dderbyniwyd mewn un tabl. Ar gyfer hyn:
1. Rydym yn clicio yn y panel ymholiad chwith mewn lle gwag llwyd gyda botwm dde'r llygoden a dewis yn olynol Cais newydd – Ffynonellau eraill – Cais gwag (Ymholiad Newydd — O ffynonellau eraill — Ymholiad gwag):
2. Mae angen i ni gynhyrchu rhestr o'r holl flynyddoedd y byddwn yn gofyn am galendrau ar eu cyfer, hy 2013, 2014 … 2020. I wneud hyn, ym mar fformiwla'r ymholiad gwag sy'n ymddangos, rhowch y gorchymyn:
Strwythur:
={RhifA..NumberB}
Mae … yn Power Query yn cynhyrchu rhestr o gyfanrifau o A i B. Er enghraifft, y mynegiad
={1..5}
… yn cynhyrchu rhestr o 1,2,3,4,5.
Wel, er mwyn peidio â chael ein clymu'n gaeth i 2020, rydym yn defnyddio'r swyddogaeth DyddiadTime.LocalNow() - analog o'r swyddogaeth Excel HEDDIW (HEDDIW) yn Power Query – a detholiad ohono, yn ei dro, y flwyddyn gyfredol gan y swyddogaeth Dyddiad.Blwyddyn.
3. Er ei bod yn edrych yn eithaf digonol, nid tabl ar gyfer Power Query mo'r set o flynyddoedd dilynol, ond gwrthrych arbennig - rhestr (Rhestr). Ond nid yw ei drosi i dabl yn broblem: cliciwch ar y botwm I bwrdd (I'r Bwrdd) yn y gornel chwith uchaf:
4. Llinell derfyn! Cymhwyso'r swyddogaeth a grëwyd gennym yn gynharach fxBlwyddyn i'r rhestr o flynyddoedd dilynol. I wneud hyn, ar y tab Ychwanegu colofn pwyswch y botwm Ffoniwch swyddogaeth arferiad (Ychwanegu Colofn - Galw Swyddogaeth Custom) a gosod ei unig ddadl – y golofn Column1 dros y blynyddoedd:
Ar ôl clicio ar OK ein swyddogaeth fxBlwyddyn bydd y mewngludo yn gweithio yn ei dro ar gyfer pob blwyddyn a byddwn yn cael colofn lle bydd pob cell yn cynnwys tabl gyda dyddiadau dyddiau di-waith (mae cynnwys y tabl i'w weld yn glir os cliciwch yng nghefndir y gell nesaf at y gair Tabl):
Mae angen ehangu cynnwys tablau nythu trwy glicio ar yr eicon gyda saethau dwbl ym mhennyn y golofn Dyddiadau (tic Defnyddiwch enw'r golofn wreiddiol fel rhagddodiad gellir ei ddileu):
… ac ar ôl clicio ar OK rydyn ni'n cael yr hyn rydyn ni ei eisiau - rhestr o'r holl wyliau o 2013 i'r flwyddyn gyfredol:
Gellir dileu'r golofn gyntaf, sydd eisoes yn ddiangen, ac ar gyfer yr ail, gosodwch y math o ddata dyddiad (Dyddiad) yn y gwymplen ym mhennawd y golofn:
Gellir ailenwi'r ymholiad ei hun yn rhywbeth mwy ystyrlon na Cais1 ac yna uwchlwythwch y canlyniadau i'r ddalen ar ffurf tabl “clyfar” deinamig gan ddefnyddio'r gorchymyn cau a llwytho i lawr tab Hafan (Cartref - Cau a Llwyth):
Gallwch chi ddiweddaru'r calendr a grëwyd yn y dyfodol trwy dde-glicio ar y bwrdd neu ymholiad yn y cwarel dde trwy'r gorchymyn Diweddaru ac Arbed. Neu defnyddiwch y botwm Adnewyddu'r cyfan tab Dyddiad (Dyddiad - Adnewyddu Pawb) neu lwybr byr bysellfwrdd Ctrl+Alt+F5.
Dyna i gyd.
Nawr does dim angen i chi byth eto wastraffu amser a meddwl yn chwilio am a diweddaru'r rhestr o wyliau - nawr mae gennych chi galendr cynhyrchu "parhaol". Beth bynnag, cyn belled â bod awduron y wefan http://xmlcalendar.ru/ yn cefnogi eu hepil, a fydd, rwy'n gobeithio, am amser hir iawn, iawn (diolch iddyn nhw eto!).
- Mewnforio cyfradd bitcoin i ragori o'r rhyngrwyd trwy Power Query
- Dod o hyd i'r diwrnod busnes nesaf gan ddefnyddio'r swyddogaeth DYDD GWAITH
- Darganfod croestoriad cyfyngau dyddiad