Féilire mhonarcha i excel

Féilire táirgeachta, ie liosta dátaí, ina ndéantar gach lá oibre oifigiúil agus laethanta saoire a mharcáil dá réir - rud atá fíor-riachtanach d'aon úsáideoir Microsoft Excel. Go praiticiúil, ní féidir leat a dhéanamh gan é:

• i ríomhanna cuntasaíochta (tuarastal, fad seirbhíse, saoire ...)
• i lóistíocht - chun amanna seachadta a chinneadh i gceart, ag cur san áireamh an deireadh seachtaine agus laethanta saoire (cuimhnigh an clasaiceach "tar éis na laethanta saoire?")
  
• i mbainistíocht tionscadail – chun téarmaí a mheas i gceart, ag cur san áireamh, arís, laethanta oibre nach laethanta oibre
• aon úsáid a bhaintear as feidhmeanna mar OBAIR (LÁ OIBRE) or OIBRITHE PURE (LÍNE LÍNE), toisc go dteastaíonn liosta laethanta saoire uathu mar argóint
• agus feidhmeanna Time Intelligence á n-úsáid (cosúil le TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, etc.) i Power Pivot agus Power BI
  
• … srl. srl. – go leor samplaí.
  

Tá sé níos éasca dóibh siúd a oibríonn i gcórais ERP corparáideacha mar 1C nó SAP, toisc go bhfuil an féilire táirgeachta ionsuite iontu. Ach cad faoi úsáideoirí Excel?

Is féidir leat, ar ndóigh, féilire den sórt sin a choinneáil de láimh. Ach ansin beidh ort é a thabhairt cothrom le dáta uair sa bhliain ar a laghad (nó níos minice fós, mar atá sa “jolly” 2020), ag dul isteach go cúramach gach deireadh seachtaine, aistrithe agus laethanta neamh-oibre a cheap ár rialtas. Agus ansin déan an nós imeachta seo arís gach bliain eile. Leamhas.

Cad é faoi dul beagán craiceáilte agus féilire mhonarcha “síoraí” a dhéanamh in Excel? Ceann a nuashonraíonn é féin, a thógann sonraí ón Idirlíon agus a ghineann i gcónaí liosta cothrom le dáta de laethanta neamh-oibre le húsáid ina dhiaidh sin in aon ríomhaireachtaí? Tempting?

Chun seo a dhéanamh, i ndáiríre, nach bhfuil ar chor ar bith deacair.

Foinse sonraí

Is í an phríomhcheist ná cá háit a bhfaighidh tú na sonraí? Agus foinse oiriúnach á lorg agam, chuaigh mé trí roinnt roghanna:

• Foilsítear na foraitheanta bunaidh ar shuíomh Gréasáin an rialtais i bhformáid PDF (anseo, ceann acu, mar shampla) agus imíonn siad láithreach – ní féidir eolas úsáideach a bhaint astu.
• Ar an gcéad amharc, ba chosúil gurbh í “Tairseach Sonraí Oscailte na Cónaidhme” a bhí ina rogha mhealltach, áit a bhfuil tacar sonraí comhfhreagracha, ach, tar éis scrúdú níos géire a dhéanamh, d’éirigh gach rud amach a bheith brónach. Tá an láithreán thar a bheith deacair le hiompórtáil isteach i Excel, ní fhreagraíonn tacaíocht theicniúil (féin-aonraithe?), Agus tá na sonraí féin as dáta ann le fada an lá - nuashonraíodh féilire táirgeachta 2020 i mí na Samhna 2019 (náire!) agus , ar ndóigh, níl ár “coronavirus’ agus an deireadh seachtaine ‘vótála’ in 2020, mar shampla.

Díomite le foinsí oifigiúla, thosaigh mé ag tochailt cinn neamhoifigiúla. Tá go leor acu ar an Idirlíon, ach tá an chuid is mó acu, arís, go hiomlán mí-oiriúnach chun iad a allmhairiú isteach i Excel agus féilire táirgeachta a thabhairt amach i bhfoirm pictiúir áille. Ach níl sé dúinne é a chrochadh ar an mballa, ceart?

Agus sa phróiseas cuardaigh, thángthas ar rud iontach trí thimpiste – an suíomh http://xmlcalendar.ru/

Gan “frills” gan ghá, suíomh simplí, éadrom agus tapa, géaraithe le haghaidh tasc amháin – chun féilire táirgeachta don bhliain atá ag teastáil a thabhairt do gach duine i bhformáid XML. Ar fheabhas!

Más rud é, go tobann, nach bhfuil tú ar an eolas, ansin is formáid téacs é XML le hábhar marcáilte le speisialta . Éadrom, áisiúil agus inléite ag formhór na gclár nua-aimseartha, lena n-áirítear Excel.

Ar eagla na heagla, rinne mé teagmháil le húdair an tsuímh agus dheimhnigh siad go bhfuil an láithreán ann le 7 mbliana, déantar na sonraí air a nuashonrú i gcónaí (tá brainse acu fiú ar github le haghaidh seo) agus níl siad chun é a dhúnadh. Agus is cuma liom ar chor ar bith go lódálann tú féin agus mé sonraí uaidh le haghaidh aon cheann dár dtionscadail agus dár ríomhanna in Excel. Tá saor. Is deas a fhios go bhfuil daoine mar seo fós! Meas!

Tá sé fós chun na sonraí seo a luchtú isteach in Excel ag baint úsáide as an breiseán Power Query (le haghaidh leaganacha de Excel 2010-2013 is féidir é a íoslódáil saor in aisce ó láithreán gréasáin Microsoft, agus i leaganacha de Excel 2016 agus níos nuaí tá sé ionsuite cheana féin de réir réamhshocraithe ).

Beidh loighic na ngníomhartha mar seo a leanas:

1. Déanaimid iarratas sonraí a íoslódáil ón suíomh ar feadh bliana ar bith
2. Ár n-iarratas a iompú ina fheidhm
3. Cuirimid an fheidhm seo i bhfeidhm ar an liosta de na blianta atá ar fáil, ag tosú ó 2013 agus suas go dtí an bhliain reatha - agus faigheann muid féilire táirgeachta “síoraí” le nuashonrú uathoibríoch. Voila!
   

Céim 1. Iompórtáil féilire ar feadh bliana

Gcéad dul síos, luchtaigh an féilire táirgeachta d'aon bhliain amháin, mar shampla, do 2020. Chun seo a dhéanamh, in Excel, téigh go dtí an táb Dáta (nó Ceist Chumhachtamá shuiteáil tú é mar bhreiseán ar leith) agus roghnaigh Ón Idirlíon (Ón nGréasán). Sa fhuinneog a osclaíonn, greamaigh an nasc chuig an mbliain chomhfhreagrach, cóipeáilte ón suíomh:

Tar éis duit cliceáil ar OK feictear fuinneog réamhamhairc, ina gcaithfidh tú cliceáil ar an gcnaipe Tiontaigh Sonraí (Athraigh sonraí) or Chun na sonraí a athrú (Cuir sonraí in eagar) agus bainfimid amach an fhuinneog eagarthóir ceisteanna Power Query, áit a leanfaimid ag obair leis na sonraí:

Láithreach is féidir leat a scriosadh go sábháilte sa phainéal ceart Iarraidh Paraiméadair (Socruithe ceiste) chéim cineál modhnaithe (Cineál Athraithe) Níl gá againn leis.

Tá cóid agus cur síos ar laethanta neamh-oibre sa tábla sa cholún saoire – is féidir leat a bhfuil ann a fheiceáil trí “titim tríd” faoi dhó trí chliceáil ar an bhfocal glas tábla:

Chun dul ar ais, beidh ort na céimeanna go léir a bhfuil an chuma ar ais orthu a scriosadh sa phainéal ar dheis Foinse (Foinse).

Sa dara tábla, ar féidir teacht air ar an mbealach céanna, tá a bhfuil de dhíth orainn go díreach – dátaí na laethanta neamhoibre go léir:

Tá sé fós chun an pláta seo a phróiseáil, eadhon:

1. Scag dátaí saoire amháin (ie cinn) faoin dara colún Tréith: t

2. Scrios na colúin go léir ach amháin an chéad cheann - trí chliceáil ar dheis ar cheannteideal an chéad cholúin agus roghnaigh an t-ordú Scrios colúin eile (Bain Colúin Eile):

3. Roinn an chéad cholún le ponc ar leithligh don mhí agus don lá leis an ordú Colún Scoilte - De réir Teorainneora tab Claochlú (Athraigh — colún scoilte — De réir teorannóir):

4. Agus ar deireadh cruthaigh colún ríofa le dátaí gnáth. Chun seo a dhéanamh, ar an táb Ag cur colún leis cliceáil ar an gcnaipe Colún saincheaptha (Cuir Colún leis - Colún Saincheaptha) agus cuir isteach an fhoirmle seo a leanas san fhuinneog atá le feiceáil:

=#dátaithe(2020, [#»Aitreabúid:d.1″], [#»Attribute:d.2″])

Anseo, tá trí argóint ag an oibreoir #date: bliain, mí, agus lá, faoi seach. Tar éis cliceáil ar OK faighimid an colún riachtanach le gnáthdátaí deireadh seachtaine, agus scriostar na colúin atá fágtha mar atá i gcéim 2

Céim 2. Ag casadh an iarratais i bhfeidhm

Is é an chéad tasc eile atá againn ná an cheist a chruthaítear do 2020 a thiontú ina fheidhm uilíoch d’aon bhliain (is é uimhir na bliana a argóint). Chun seo a dhéanamh, déanaimid na nithe seo a leanas:

1. An painéal a leathnú (mura bhfuil sé leathnaithe cheana féin). Fiosrúcháin (Ceisteanna) ar chlé sa bhfuinneog Power Query:

2. Tar éis an t-iarratas a thiontú go feidhm, imíonn an cumas na céimeanna a bhaineann leis an iarratas a fheiceáil agus iad a chur in eagar go héasca, ar an drochuair, imithe. Mar sin, tá sé ciallach cóip dár n-iarratas a dhéanamh agus frolic a dhéanamh leis cheana féin, agus an bunchóip a fhágáil mar chúlchiste. Chun seo a dhéanamh, deaschliceáil sa phána clé ar ár n-iarratas féilire agus roghnaigh an t-ordú Dúblach.

Roghnóidh cliceáil ar dheis arís ar an gcóip den fhéilire mar thoradh air (2) an t-ordú Athainmnigh (Athainmnigh) agus cuir isteach ainm nua – bíodh, mar shampla, fxBliain:

3. Osclaímid cód foinse an iarratais sa teanga inmheánach Power Query (tugtar “M go hachomair air”) ag baint úsáide as an ordú Ardeagarthóir tab Athbhreithniú(Amharc - Ardeagarthóir) agus athruithe beaga a dhéanamh ansin chun ár n-iarratas a dhéanamh ina fheidhm d’aon bhliain.

Bhí sé:

Tar éis:

Má tá suim agat sna sonraí, ansin anseo:

• (bliain mar uimhir)=>  – dearbhaímid go mbeidh argóint uimhriúil amháin ag ár bhfeidhm – athróg bliain
• An athróg a ghreamú bliain chuig nasc gréasáin i gcéim Foinse. Ós rud é nach gceadaíonn Power Query duit uimhreacha agus téacs a ghliúáil, déanaimid uimhir na bliana a thiontú go téacs ar an eitilt ag baint úsáide as an bhfeidhm Uimhir.ToText
• Déanaimid athróg na bliana do 2020 a ionadú sa chéim dheiridh #”Réad saincheaptha curtha leis«, áit a ndearnamar an dáta ó na blúirí.
  

Tar éis duit cliceáil ar Críochnaigh déantar feidhm dár n-iarratas:

Céim 3. Iompórtáil féilirí do gach bliain

Is é an rud deireanach atá fágtha ná an príomhcheist dheireanach a dhéanamh, a uaslódálfaidh sonraí do na blianta go léir atá ar fáil agus a chuirfidh na dátaí saoire a fuarthas in aon tábla amháin. Dó seo:

1. Déanaimid cliceáil ar an bpainéal ceisteanna ar chlé i spás folamh liath leis an gcnaipe luiche ar dheis agus roghnaigh muid go seicheamhach Iarratas nua – Foinsí eile – Iarratas folamh (Iarratas Nua — Ó fhoinsí eile — Ceist bhán):

2. Ní mór dúinn liosta a ghiniúint de na blianta go léir a n-iarrfaimid féilirí ina leith, .i.

Struchtúr:

={Uimhir A..UimhirB}

… in Power Query gineann liosta slánuimhreacha ó A go B. Mar shampla, an slonn

={1..5}

… liosta de 1,2,3,4,5 a tháirgeadh.

Bhuel, d'fhonn gan a bheith ceangailte go docht le 2020, úsáidimid an fheidhm DátaTime.LocalNow() – analóg den fheidhm Excel TODAY (INNIU) in Power Query – agus sliocht as, ar a seal, an bhliain reatha ag an bhfeidhm Dáta.Bliain.

3. Cé go bhfuil cuma leordhóthanach ar an tsraith blianta atá mar thoradh air, ní tábla é don Cheist Cumhachta, ach réad speisialta - liosta (Liosta). Ach ní fadhb é é a thiontú go tábla: níl le déanamh ach cliceáil ar an gcnaipe Chun boird (Chun Tábla) sa chúinne uachtarach ar chlé:

4. Críochnaigh líne! An fheidhm a chruthaigh muid níos luaithe a chur i bhfeidhm fxBliain leis an liosta blianta dá bharr. Chun seo a dhéanamh, ar an táb Ag cur colún leis brúigh an cnaipe Glaoigh ar fheidhm saincheaptha (Cuir Colún leis - agairt Feidhm Chustaim) agus socraigh a argóint amháin – an colún Column1 i gcaitheamh na mblianta:

Tar éis duit cliceáil ar OK ár bhfeidhm fxBliain oibreoidh an t-iompórtáil ar a seal do gach bliain agus gheobhaimid colún ina mbeidh tábla i ngach cill le dátaí na laethanta neamhoibre (tá inneachar an tábla le feiceáil go soiléir má chliceálann tú i gcúlra na cille in aice le an focal tábla):

Tá sé fós le hábhar na dtáblaí neadaithe a leathnú trí chliceáil ar an deilbhín le saigheada dúbailte i gceanntásc an cholúin Dátaí (cuir tic Úsáid ainm bunaidh an cholúin mar réimír is féidir é a bhaint):

... agus tar éis cliceáil ar OK faigheann muid an rud a bhí uainn – liosta de na laethanta saoire ar fad ó 2013 go dtí an bhliain reatha:

Is féidir an chéad cholún, nach bhfuil gá leis cheana féin, a scriosadh, agus don dara ceann, socraigh an cineál sonraí sonraí (Dáta) sa liosta anuas i gceannteideal an cholúin:

Is féidir an cheist féin a athainmniú rud éigin níos brí ná Iarratas1 agus ansin na torthaí a uaslódáil chuig an mbileog i bhfoirm tábla dinimiciúil “cliste” ag baint úsáide as an ordú dhúnadh agus a íoslódáil tab Baile (Baile - Dún & Luchtaigh):

Is féidir leat an féilire cruthaithe a nuashonrú amach anseo trí chliceáil ar an tábla ar dheis nó trí cheist a chur sa phána ar dheis tríd an ordú Nuashonraigh & Sábháil. Nó bain úsáid as an gcnaipe Athnuaigh go léir tab Dáta (Dáta - Athnuaigh Gach Rud) nó aicearra méarchláir Ctrl+Eile+F5.

Sin é an méid.

Anois ní gá duit am agus breosla machnaimh a chur amú arís ag cuardach agus ag nuashonrú an liosta laethanta saoire - anois tá féilire táirgeachta “síoraí” agat. In aon chás, chomh fada agus a thacaíonn údair an tsuímh http://xmlcalendar.ru/ a sliocht, a bheidh, tá súil agam, ar feadh tréimhse an-fhada (buíochas leo arís!).

• Iompórtáil ráta bitcoin chun barr feabhais a bhaint as an Idirlíon trí Power Query
• An chéad lá gnó eile a aimsiú ag baint úsáide as an bhfeidhm WORKDAY
  
• Trasnaíonn eatraimh dáta a aimsiú