Kalandriye faktori nan Excel

Kalandriye pwodiksyon, sa vle di yon lis dat, kote tout jou travay ofisyèl ak jou ferye yo make kòmsadwa - yon bagay absoliman nesesè pou nenpòt itilizatè nan Microsoft Excel. Nan pratik, ou pa ka fè san li:

• nan kalkil kontablite (salè, longè sèvis, vakans ...)
• nan lojistik - pou detèminasyon ki kòrèk la nan tan livrezon, pran an kont wikenn ak jou ferye (sonje klasik "vini sou apre jou ferye yo?")
  
• nan jesyon pwojè - pou estimasyon ki kòrèk la nan tèm, pran an kont, ankò, jou k ap travay-ki pa travay
• nenpòt itilizasyon fonksyon tankou JOU TRAVAY (JOU TRAVAY) or PI TRAVAYÈ (JOU REZO), paske yo mande pou yon lis jou ferye kòm yon agiman
• lè w ap itilize fonksyon Time Intelligence (tankou TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, elatriye) nan Power Pivot ak Power BI
  
• … elatriye elatriye – anpil egzanp.
  

Li pi fasil pou moun ki travay nan sistèm ERP antrepriz tankou 1C oswa SAP, kòm kalandriye pwodiksyon an bati nan yo. Men, e itilizatè Excel yo?

Ou ka, nan kou, kenbe yon kalandriye konsa manyèlman. Men, Lè sa a, ou pral oblije mete ajou li omwen yon fwa pa ane (oswa menm pi souvan, tankou nan "jolly" 2020 la), ak anpil atansyon antre nan tout wikenn, transfè ak jou ki pa travay envante pa gouvènman nou an. Apre sa, repete pwosedi sa a chak ane pwochèn. Annwi.

Kouman sou ale yon ti kras fou epi fè yon kalandriye faktori "pètènèl" nan Excel? Youn ki mete ajou tèt li, pran done ki soti nan entènèt la epi toujou jenere yon lis ajou nan jou ki pa travay pou itilize ki vin apre nan nenpòt kalkil? Tantasyon?

Pou fè sa, an reyalite, se pa nan tout difisil.

Done sous

Kesyon prensipal la se ki kote yo ka resevwa done yo? Nan rechèch nan yon sous apwopriye, mwen te ale nan plizyè opsyon:

• Dekrè orijinal yo pibliye sou sit entènèt gouvènman an nan fòma PDF (isit la, youn nan yo, pou egzanp) epi yo disparèt imedyatman - enfòmasyon itil yo pa ka rale soti nan yo.
• A tempting option, at first glance, seemed to be the “Open Data Portal of the Federation”, where there is a corresponding data set, but, upon closer examination, everything turned out to be sad. The site is terribly inconvenient for importing into Excel, technical support does not respond (self-isolated?), and the data itself is outdated there for a long time – the production calendar for 2020 was last updated in November 2019 (disgrace!) and, of course, does not contain our “coronavirus ‘ and the ‘voting’ weekend of 2020, for example.

Dezapwente ak sous ofisyèl yo, mwen te kòmanse fouye moun ki pa ofisyèl yo. Gen anpil nan yo sou entènèt la, men pifò nan yo, ankò, yo konplètman inoporten pou enpòte nan Excel epi bay yon kalandriye pwodiksyon nan fòm lan nan bèl foto. Men, se pa pou nou pann li sou miray la, pa vre?

Ak nan pwosesis la nan rechèch, yon bèl bagay te aksidantèlman dekouvri - sit la http://xmlcalendar.ru/

San yo pa nesesè "frills", yon sit senp, lejè ak rapid, file pou yon sèl travay - bay tout moun yon kalandriye pwodiksyon pou ane a vle nan fòma XML. Ekselan!

Si, toudenkou, ou pa nan konnen an, Lè sa a, XML se yon fòma tèks ak kontni make moute ak espesyal . Lejè, pratik ak lizib pa pifò pwogram modèn, ki gen ladan Excel.

Jis nan ka, mwen kontakte otè yo nan sit la epi yo konfime ke sit la te egziste pou ane 7, done yo sou li yo toujou ap mete ajou (yo menm gen yon branch sou github pou sa a) epi yo pa pral fèmen li. Apre sa, mwen pa lide ditou ke ou menm ak mwen chaje done ki soti nan li pou nenpòt nan pwojè nou yo ak kalkil nan Excel. Se gratis. Li bon pou konnen ke gen moun toujou konsa! Respè!

Li rete pou chaje done sa yo nan Excel lè l sèvi avèk Power Query ajoute-an (pou vèsyon nan Excel 2010-2013 li ka telechaje gratis sou sit entènèt Microsoft la, ak nan vèsyon nan Excel 2016 ak pi nouvo li deja entegre pa default. ).

Lojik aksyon yo pral jan sa a:

1. Nou fè yon demann pou telechaje done ki soti nan sit la pou nenpòt ki ane
2. Vire demann nou an nan yon fonksyon
3. Nou aplike fonksyon sa a nan lis la nan tout ane ki disponib, kòmanse soti nan 2013 ak jiska ane aktyèl la - epi nou jwenn yon kalandriye pwodiksyon "pètènèl" ak aktyalizasyon otomatik. Voila!
   

Etap 1. Enpòte yon kalandriye pou yon ane

Premyèman, chaje kalandriye pwodiksyon an pou nenpòt ki ane, pou egzanp, pou 2020. Pou fè sa, nan Excel, ale nan tab la. Done (oswa Pouvwa rechèchsi ou enstale li kòm yon sipleman separe) epi chwazi Soti nan entènèt la (Soti nan entènèt). Nan fenèt la ki ouvè, kole lyen ki mennen nan ane korespondan an, kopye nan sit la:

Apre klike sou OK yon fenèt preview parèt, kote ou bezwen klike sou bouton an Konvèti Done (Transfòme done) or Pou chanje done yo (Edite done) epi nou pral rive nan fenèt editè demann Power Query, kote nou pral kontinye travay ak done yo:

Imedyatman ou ka san danje efase nan panèl dwat la Mande Paramèt (Anviwònman rechèch) etap kalite modifye (Kanje Kalite) Nou pa bezwen li.

Tablo ki nan kolòn jou ferye yo gen kòd ak deskripsyon jou ki pa travay yo - ou ka wè sa ki ladan l lè w "tonbe nan" li de fwa lè w klike sou mo vèt la. Tablo:

Pou tounen, ou pral oblije efase nan panèl dwat la tout etap sa yo ki te parèt tounen nan sous (Sous).

Dezyèm tablo a, ki ka jwenn aksè nan yon fason menm jan an, gen egzakteman sa nou bezwen - dat yo nan tout jou ki pa travay:

Li rete pou trete plak sa a, sètadi:

1. Filtre sèlman dat jou ferye yo (sa vle di yo) pa dezyèm kolòn nan Atribi: t

2. Efase tout kolòn eksepte premye a - pa klike sou tit premye kolòn nan epi chwazi kòmandman an Efase lòt kolòn yo (Retire Lòt Kolòn):

3. Divize premye kolòn pa pwen separeman pou mwa ak jou ak lòd Split Kolòn - Pa Delimiter tab Transfòmasyon (Transfòme — Divize kolòn — Pa delimiter):

4. Epi finalman kreye yon kolòn kalkile ak dat nòmal. Pou fè sa, sou tab la Ajoute yon kolòn klike sou bouton an Kolòn Custom (Ajoute Kolòn — Kolòn Custom) epi antre fòmil sa a nan fenèt ki parèt la:

=#date(2020, [#»Atribi:d.1″], [#»Atribi:d.2″])

Isit la, #date operatè a gen twa agiman: ane, mwa, ak jou, respektivman. Apre klike sou OK nou jwenn kolòn ki nesesè yo ak dat nòmal wikenn, epi efase kolòn ki rete yo tankou nan etap 2

Etap 2. Vire demann lan nan yon fonksyon

Pwochen travay nou an se konvèti rechèch ki te kreye pou 2020 la nan yon fonksyon inivèsèl pou nenpòt ane (nimewo ane a pral agiman li). Pou fè sa, nou fè bagay sa yo:

1. Agrandi (si pa deja elaji) panèl la Enquiries (Rekèt) sou bò gòch la nan fenèt la Power Query:

2. Apre konvèti demann lan nan yon fonksyon, kapasite nan wè etap ki fè demann lan epi fasil edite yo, malerezman, disparèt. Se poutèt sa, li fè sans pou fè yon kopi demann nou an ak frolic deja ak li, epi kite orijinal la an rezèv. Pou fè sa, klike sou dwa-klike nan fenèt gòch la sou demann kalandriye nou an epi chwazi lòd la Diplike.

Dwa-klike sou kopi kalandriye a ki kapab lakòz (2) pral chwazi kòmandman an Chanje non (Chanje non) epi antre yon nouvo non - kite l, pou egzanp, fxYear:

3. Nou louvri kòd sous rechèch la nan lang entèn Power Query (li rele "M") lè l sèvi avèk lòd la. Editè avanse tab Revizyon(View — Editè avanse) epi fè ti chanjman la pou fè demann nou an tounen yon fonksyon pou nenpòt ane.

Se te:

Apre:

Si w enterese nan detay yo, Lè sa a, isit la:

• (ane kòm nimewo) =>  – nou deklare ke fonksyon nou an pral gen yon agiman nimerik – yon varyab ane
• Kole varyab la ane nan lyen entènèt nan etap sous. Piske Power Query pa pèmèt ou kole nimewo ak tèks, nou konvèti nimewo ane a nan tèks sou vole lè l sèvi avèk fonksyon an. Number.ToText
• Nou ranplase varyab ane a pou 2020 nan avan dènye etap la #"Ajoute objè koutim«, kote nou te fòme dat la soti nan fragman yo.
  

Apre klike sou Fini demann nou an vin tounen yon fonksyon:

Etap 3. Enpòte kalandriye pou tout ane yo

Dènye bagay ki rete a se fè dènye rechèch prensipal la, ki pral telechaje done pou tout ane ki disponib epi ajoute tout dat jou ferye yo resevwa nan yon sèl tab. Pou sa:

1. Nou klike nan panèl la rechèch gòch nan yon espas gri ak bouton an sourit dwat epi chwazi sekans Nouvo demann – Lòt sous – Demann vid (Nouvo Rekèt — Soti nan lòt sous — Rekèt vid):

2. Nou bezwen jenere yon lis tout ane pou nou pral mande kalandriye, sa vle di 2013, 2014 ... 2020. Pou fè sa, nan ba fòmil la nan rechèch la vid ki parèt, antre lòd la:

Estrikti:

={NimewoA..NimewoB}

… nan Power Query jenere yon lis nonm antye ki soti nan A rive nan B. Pou egzanp, ekspresyon an

={1..5}

… ta pwodwi yon lis 1,2,3,4,5.

Oke, nan lòd yo pa dwe mare rijid nan 2020, nou itilize fonksyon an DateTime.LocalNow() – analòg nan fonksyon an Excel JODI A (JODI A) nan Power Query - ak ekstrè soti nan li, nan vire, ane aktyèl la pa fonksyon an Dat.Ane.

3. Seri ane ki kapab lakòz, byenke li sanble byen adekwat, se pa yon tab pou Power Query, men se yon objè espesyal - lis (Lis). Men, konvèti li nan yon tab se pa yon pwoblèm: jis klike sou bouton an Pou tab la (Pou Tablo) nan kwen anwo gòch:

4. Fini liy! Aplike fonksyon nou te kreye pi bonè a fxYear nan lis ki kapab lakòz ane yo. Pou fè sa, sou tab la Ajoute yon kolòn peze bouton an Rele fonksyon koutim (Ajoute Kolòn — Envoke Fonksyon Custom) epi mete sèl agiman li yo - kolòn nan Column1 pandan ane yo:

Apre klike sou OK fonksyon nou fxYear enpòtasyon an ap travay youn apre lòt pou chak ane epi n ap jwenn yon kolòn kote chak selil ap genyen yon tablo ak dat jou ki pa travay yo (sa ki nan tablo a vizib klèman si ou klike sou background nan selil la bò kote mo a Tablo):

Li rete pou elaji sa ki nan tab enbrike yo lè w klike sou ikòn ki gen doub flèch nan tèt kolòn lan. Dat (tik Sèvi ak non kolòn orijinal kòm prefiks li ka retire):

… epi apre klike sou OK nou jwenn sa nou te vle - yon lis tout jou ferye soti nan 2013 jiska ane aktyèl la:

Premye, kolòn ki deja nesesè, ka efase, epi pou dezyèm lan, mete kalite done a dat (Dat) nan lis deroule nan tit kolòn nan:

Rekèt la tèt li ka chanje non yon bagay ki gen plis sans pase Demann 1 ak Lè sa a, Upload rezilta yo nan fèy la nan fòm lan nan yon tab dinamik "entelijan" lè l sèvi avèk lòd la fèmen epi telechaje tab Akèy (Kay — Fèmen & Chaje):

Ou ka mete ajou kalandriye kreye a nan tan kap vini an lè w klike sou tab la oswa fè rechèch nan fenèt dwat la atravè kòmandman an. Mete ajou & Sove. Oswa itilize bouton an Rafrechi tout tab Done (Dat — rafrechi tout) oswa rakoursi klavye Ctrl+Alt+F5.

Se tout.

Koulye a, ou pa janm bezwen ankò gaspiye tan ak panse-gaz pou chèche ak mete ajou lis la nan jou ferye - kounye a ou gen yon kalandriye pwodiksyon "pètènèl". Nan nenpòt ka, osi lontan ke otè yo nan sit la http://xmlcalendar.ru/ sipòte pitit yo, ki, mwen espere, yo pral pou yon tan trè, trè long (mèsi a yo ankò!).

• Enpòte pousantaj bitcoin pou briye nan entènèt la atravè Power Query
• Jwenn pwochen jou ouvrab la lè l sèvi avèk fonksyon WORKDAY
  
• Jwenn entèseksyon entèval dat yo