Werrej
Kalendarju tal-produzzjoni, jiġifieri lista ta’ dati, fejn il-ġranet tax-xogħol uffiċjali u l-vaganzi kollha huma mmarkati kif xieraq – ħaġa assolutament meħtieġa għal kull utent ta’ Microsoft Excel. Fil-prattika, ma tistax tagħmel mingħajrha:
- fil-kalkoli tal-kontabilità (salarju, tul tas-servizz, ferjat...)
- fil-loġistika - għad-determinazzjoni korretta tal-ħinijiet tal-kunsinna, b'kont meħud tal-weekends u l-vaganzi (ftakar il-klassika "idejja wara l-vaganzi?")
- fil-ġestjoni tal-proġett – għall-istima korretta tat-termini, filwaqt li jitqiesu, għal darb'oħra, ġranet tax-xogħol u mhux tax-xogħol
- kwalunkwe użu ta 'funzjonijiet simili JUM XOGĦOL (JUR XOGĦOL) or ĦADDIEMA PURA (ĠRAIET NETWORK), għax jeħtieġu lista ta’ vaganzi bħala argument
- meta tuża funzjonijiet Time Intelligence (bħal TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, eċċ.) f'Power Pivot u Power BI
- … eċċ eċċ. – ħafna eżempji.
Huwa aktar faċli għal dawk li jaħdmu f'sistemi ERP korporattivi bħal 1C jew SAP, peress li l-kalendarju tal-produzzjoni huwa mibni fihom. Imma xi ngħidu dwar l-utenti ta 'Excel?
Tista', ovvjament, iżżomm kalendarju bħal dan manwalment. Imma mbagħad ikollok taġġornaha mill-inqas darba fis-sena (jew saħansitra aktar ta 'spiss, bħal fil-"jolly" 2020), billi ddaħħal bir-reqqa l-weekends, it-trasferimenti u l-ġranet mhux tax-xogħol ivvintati mill-gvern tagħna. U mbagħad irrepeti din il-proċedura kull sena d-dieħla. Dwejjaq.
Kif dwar li tmur xi ftit miġnun u tagħmel kalendarju tal-fabbrika "perpetwu" f'Excel? Waħda li taġġorna lilha nnifisha, tieħu data mill-Internet u dejjem tiġġenera lista aġġornata ta' ġranet mhux tax-xogħol għall-użu sussegwenti fi kwalunkwe kalkoli? Jitħajjar?
Biex tagħmel dan, fil-fatt, mhu diffiċli xejn.
Sors tad-dejta
Il-mistoqsija ewlenija hija fejn tikseb id-data? Fit-tfittxija ta 'sors xieraq, għaddejt minn diversi għażliet:
- Id-digrieti oriġinali huma ppubblikati fuq il-websajt tal-gvern f'format PDF (hawn, wieħed minnhom, pereżempju) u jisparixxu immedjatament - informazzjoni utli ma tistax tinġibed 'il barra minnhom.
- Għażla li tħajjar, mal-ewwel daqqa t'għajn, dehret li kienet "Portal tad-Data Miftuħa tal-Federazzjoni", fejn hemm sett ta 'dejta korrispondenti, iżda, wara eżami aktar mill-qrib, kollox irriżulta li kien imdejjaq. Is-sit huwa terriblement inkonvenjenti għall-importazzjoni f'Excel, l-appoġġ tekniku ma jirrispondix (iżolat waħdu?), U d-dejta nnifisha hija skaduta hemm għal żmien twil - il-kalendarju tal-produzzjoni għall-2020 ġie aġġornat l-aħħar f'Novembru 2019 (dishonor!) U , ovvjament, ma fihx il-"coronavirus" tagħna u l-weekend tal-"votazzjoni" tal-2020, pereżempju.
Diżilluż bis-sorsi uffiċjali, bdejt inħaffer dawk mhux uffiċjali. Hemm ħafna minnhom fuq l-Internet, iżda ħafna minnhom, għal darb'oħra, huma kompletament mhux adattati għall-importazzjoni f'Excel u jagħtu kalendarju ta 'produzzjoni fil-forma ta' stampi sbieħ. Imma mhux għalina li ndendluha mal-ħajt, hux?
U fil-proċess tat-tiftix, ġiet skoperta aċċidentalment ħaġa mill-isbaħ - is-sit http://xmlcalendar.ru/
Mingħajr "frills" bla bżonn, sit sempliċi, ħafif u veloċi, imsaħħaħ għal kompitu wieħed - biex jagħti lil kulħadd kalendarju ta 'produzzjoni għas-sena mixtieqa f'format XML. Eċċellenti!
Jekk, f'daqqa waħda, ma tkunx taf, allura XML huwa format ta 'test b'kontenut immarkat b'speċjali
Fil-każ, ikkuntattjajt lill-awturi tas-sit u kkonfermaw li s-sit ilu jeżisti għal 7 snin, id-dejta dwaru hija aġġornata kontinwament (saħansitra għandhom fergħa fuq github għal dan) u mhux se jagħlquha. U ma niddejjaq xejn li jien u int tagħbija data minnha għal kwalunkwe proġett u kalkoli tagħna f'Excel. Huwa b'xejn. Tajjeb li tkun taf li għad hemm nies bħal dawn! Rispett!
Jibqa' biex tagħbija din id-dejta f'Excel bl-użu tal-Power Query add-in (għall-verżjonijiet ta' Excel 2010-2013 tista' titniżżel b'xejn mill-websajt ta' Microsoft, u f'verżjonijiet ta' Excel 2016 u aktar ġodda hija diġà inkorporata awtomatikament). ).
Il-loġika tal-azzjonijiet se tkun kif ġej:
- Aħna nagħmlu talba biex iniżżlu d-dejta mis-sit għal kull sena
- Nibdlu t-talba tagħna f'funzjoni
- Aħna napplikaw din il-funzjoni għal-lista tas-snin kollha disponibbli, li jibdew mill-2013 u sas-sena kurrenti - u jkollna kalendarju ta 'produzzjoni "perpetwu" b'aġġornament awtomatiku. Voila!
Pass 1. Importa kalendarju għal sena
L-ewwel, tagħbija l-kalendarju tal-produzzjoni għal kull sena waħda, pereżempju, għall-2020. Biex tagħmel dan, f'Excel, mur fit-tab Data (Jew Mistoqsija dwar l-Enerġijajekk installajt bħala add-on separat) u agħżel Mill-Internet (Mill-Web). Fit-tieqa li tiftaħ, waħħal il-link għas-sena korrispondenti, ikkupjata mis-sit:
Wara li tikklikkja fuq OK tidher tieqa ta 'previżjoni, li fiha għandek bżonn tikklikkja l-buttuna Ikkonverti Data (Trasforma data) or Biex tibdel id-data (Editja d-dejta) u se naslu fit-tieqa tal-editur tal-mistoqsijiet tal-Power Query, fejn se nkomplu naħdmu bid-dejta:
Immedjatament tista 'tħassar b'mod sikur fil-pannell tal-lemin Parametri tat-Talba (Settings tal-mistoqsijiet) pass tip modifikat (Tip Mibdul) M’għandniex bżonnu.
It-tabella fil-kolonna tal-vaganzi fiha kodiċijiet u deskrizzjonijiet ta’ ġranet mhux tax-xogħol – tista’ tara l-kontenut tagħha billi “taqa’ minnha” darbtejn billi tikklikkja fuq il-kelma ħadra Tabella:
Biex tmur lura, ser ikollok tħassar fil-pannell tal-lemin il-passi kollha li dehru lura sors (Sors).
It-tieni tabella, li tista’ tiġi aċċessata b’mod simili, fiha eżattament dak li neħtieġu – id-dati tal-ġranet kollha mhux tax-xogħol:
Fadal biex tipproċessa din il-pjanċa, jiġifieri:
1. Iffiltra biss id-dati tal-vaganzi (jiġifieri dawk) mit-tieni kolonna Attribut:t
2. Ħassar il-kolonni kollha ħlief l-ewwel - billi tikklikkja bil-lemin fuq l-intestatura tal-ewwel kolonna u tagħżel il-kmand Ħassar kolonni oħra (Neħħi Kolonni Oħrajn):
3. Aqsam l-ewwel kolonna b'tikka separatament għal xahar u jum bil-kmand Kolonna maqsuma - Permezz tad-Delimitatur tab Trasformazzjoni (Trasforma — Kolonna maqsuma — Permezz ta' delimitatur):
4. U fl-aħħar oħloq kolonna kkalkulata b'dati normali. Biex tagħmel dan, fuq it-tab Żieda ta 'kolonna ikklikkja fuq il-buttuna Kolonna tad-dwana (Żid Kolonna — Kolonna Personalizzata) u daħħal il-formula li ġejja fit-tieqa li tidher:
=#datat(2020, [#»Attribut:d.1″], [#»Attribut:d.2″])
Hawnhekk, l-operatur #date għandu tliet argumenti: sena, xahar, u jum, rispettivament. Wara li tikklikkja fuq OK nikseb il-kolonna meħtieġa b'dati normali ta 'weekend, u ħassar il-kolonni li jifdal bħal fil-pass 2
Pass 2. Dawwar it-talba f'funzjoni
Il-kompitu li jmiss tagħna huwa li nikkonverti l-mistoqsija maħluqa għall-2020 f'funzjoni universali għal kull sena (in-numru tas-sena se jkun l-argument tagħha). Biex tagħmel dan, nagħmlu dan li ġej:
1. Tespandi (jekk mhux diġà estiż) il-panel Inkjesti (Mistoqsijiet) fuq ix-xellug fit-tieqa tal-Power Query:
2. Wara li tikkonverti t-talba għal funzjoni, l-abbiltà li tara l-passi li jiffurmaw it-talba u teditjahom faċilment, sfortunatament, tisparixxi. Għalhekk, jagħmel sens li nagħmlu kopja tat-talba tagħna u niftakru diġà magħha, u nħallu l-oriġinal fir-riżerva. Biex tagħmel dan, ikklikkja bil-lemin fil-pannell tax-xellug fuq it-talba tal-kalendarju tagħna u agħżel il-kmand Duplikat.
Meta tikklikkja bil-lemin mill-ġdid fuq il-kopja li tirriżulta tal-kalendarju(2) tagħżel il-kmand Semmi (Semmi mill-ġdid) u daħħal isem ġdid - ħalli jkun, pereżempju, fxYear:
3. Aħna niftħu l-kodiċi tas-sors tal-mistoqsija fil-lingwa interna tal-Power Query (jissejjaħ fil-qosor "M") billi tuża l-kmand Editur Avvanzat tab reviżjoni(Ara — Editur Avvanzat) u nagħmlu bidliet żgħar hemmhekk biex nibdlu t-talba tagħna f’funzjoni għal kull sena.
Kienet:
Wara:
Jekk inti interessat fid-dettalji, allura hawn:
- (is-sena bħala numru) => – niddikjaraw li l-funzjoni tagħna se jkollha argument numeriku wieħed – varjabbli sena
- Twaħħal il-varjabbli sena għal link tal-web fil-pass sors. Peress li l-Power Query ma jippermettilekx twaħħal in-numri u t-test, aħna nikkonverti n-numru tas-sena għal test fuq il-fly billi tuża l-funzjoni Numru.ToText
- Nissostitwixxu l-varjabbli tas-sena għall-2020 fil-pass ta' qabel tal-aħħar #"Oġġett tad-dwana miżjud«, fejn iffurmajna d-data mill-frammenti.
Wara li tikklikkja fuq Finatura it-talba tagħna ssir funzjoni:
Pass 3. Importa kalendarji għas-snin kollha
L-aħħar ħaġa li fadal hija li tagħmel l-aħħar mistoqsija prinċipali, li se ttella 'data għas-snin kollha disponibbli u żżid id-dati kollha tal-vaganzi riċevuti f'tabella waħda. Għal din:
1. Aħna nikklikkjaw fil-pannell tal-mistoqsija tax-xellug fi spazju vojt griż bil-buttuna tal-lemin tal-maws u nagħżlu b'mod sekwenzjali Talba ġdida – Sorsi oħra – Talba vojta (Mistoqsija Ġdida — Minn sorsi oħra — Mistoqsija vojta):
2. Għandna bżonn niġġeneraw lista tas-snin kollha li għalihom se nitolbu kalendarji, jiġifieri 2013, 2014 ... 2020. Biex tagħmel dan, fil-bar tal-formula tal-mistoqsija vojta li tidher, daħħal il-kmand:
Struttura:
={NumruA..NumruB}
… f'Power Query jiġġenera lista ta' numri interi minn A sa B. Pereżempju, l-espressjoni
={1..5}
… jipproduċi lista ta' 1,2,3,4,5.
Ukoll, sabiex ma nkunux marbuta b'mod riġidu mal-2020, nużaw il-funzjoni DateTime.LocalNow() – analogu tal-funzjoni Excel ILLUM (ILLUM) fil-Power Query – u estratt minnha, min-naħa tagħha, is-sena kurrenti mill-funzjoni Data.Sena.
3. Is-sett ta' snin li jirriżulta, għalkemm jidher pjuttost adegwat, mhuwiex tabella għal Power Query, iżda oġġett speċjali - lista (Lista). Iżda l-konverżjoni tagħha għal tabella mhix problema: ikklikkja biss il-buttuna Għat-tabella (Għat-Tabella) fir-rokna ta' fuq tax-xellug:
4. Finish line! L-applikazzjoni tal-funzjoni li ħloqna qabel fxYear għal-lista ta' snin li tirriżulta. Biex tagħmel dan, fuq it-tab Żieda ta 'kolonna agħfas il-buttuna Sejħa funzjoni tad-dwana (Żid Kolonna — Invoka Funzjoni Custom) u waqqaf l-uniku argument tagħha – il-kolonna Column1 matul is-snin:
Wara li tikklikkja fuq OK il-funzjoni tagħna fxYear l-importazzjoni se taħdem imbagħad għal kull sena u se nġibu kolonna fejn kull ċellola se jkun fiha tabella bid-dati tal-ġranet mhux tax-xogħol (il-kontenut tat-tabella jidher b'mod ċar jekk tikklikkja fl-isfond taċ-ċellula ħdejn il-kelma Tabella):
Fadal biex tespandi l-kontenut tat-tabelli nested billi tikklikkja fuq l-ikona bi vleġeġ doppji fl-intestatura tal-kolonna Dati (immarka Uża l-isem tal-kolonna oriġinali bħala prefiss jista' jitneħħa):
… u wara li tikklikkja fuq OK niksbu dak li ridna – lista tal-vaganzi kollha mill-2013 sas-sena kurrenti:
L-ewwel kolonna, diġà bla bżonn, tista 'titħassar, u għat-tieni, issettja t-tip tad-dejta data (Data) fil-lista dropdown fl-intestatura tal-kolonna:
Il-mistoqsija nnifisha tista 'tissejjaħ xi ħaġa aktar sinifikanti minn Talba1 u mbagħad ittella 'r-riżultati fuq il-folja fil-forma ta' tabella dinamika "intelliġenti" bl-użu tal-kmand agħlaq u niżżel tab home (Id-Dar — Agħlaq u Tagħbija):
Tista 'taġġorna l-kalendarju maħluq fil-futur billi tikklikkja bil-lemin fuq it-tabella jew mistoqsija fil-pannell tal-lemin permezz tal-kmand Aġġorna u Issejvja. Jew uża l-buttuna Aġġorna kollox tab Data (Data — Aġġorna Kollha) jew shortcut tat-tastiera Ctrl+Alt+F5.
Dak kollox.
Issa qatt ma terġa' teħtieġ li taħli ħin u fjuwil tal-ħsieb tfittex u taġġorna l-lista ta 'vaganzi - issa għandek kalendarju ta' produzzjoni "perpetwu". Fi kwalunkwe każ, sakemm l-awturi tas-sit http://xmlcalendar.ru/ jappoġġjaw lill-frieħ tagħhom, li, nittama, se jkun għal żmien twil ħafna, ħafna (grazzi lilhom mill-ġdid!).
- Importa rata tal-bitcoin biex teċċella mill-internet permezz tal-Power Query
- Sib il-ġurnata tax-xogħol li jmiss bl-użu tal-funzjoni WORKDAY
- Sib l-intersezzjoni ta 'intervalli ta' data