змест
Вытворчы каляндар, гэта значыць спіс дат, дзе адпаведным чынам пазначаны ўсе афіцыйныя працоўныя і святочныя дні - абсалютна неабходная рэч для любога карыстальніка Microsoft Excel. На практыцы без гэтага не абысціся:
- у бухгалтарскіх разліках (заработная плата, стаж, водпускі…)
- у лагістыцы – для правільнага вызначэння тэрмінаў пастаўкі з улікам выхадных і святочных дзён (памятаеце класічнае «давай пасля святаў?»)
- у кіраванні праектамі – для правільнай ацэнкі тэрмінаў з улікам, зноў жа, працоўных і непрацоўных дзён
- любое выкарыстанне такіх функцый, як ПРАЦОЎНІК (РАБОЧЫ ДЗЕНЬ) or ЧЫСТЫЯ РАБОЧЫЯ (СЕТКАВЫЯ ДНІ), бо патрабуюць у якасці аргументу пералік святаў
- пры выкарыстанні функцый Time Intelligence (напрыклад, TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR і інш.) у Power Pivot і Power BI
- … і г. д. і г. д. – шмат прыкладаў.
Прасцей тым, хто працуе ў карпаратыўных ERP-сістэмах, такіх як 1С або SAP, бо ў іх убудаваны вытворчы каляндар. Але як наконт карыстальнікаў Excel?
Вы, вядома, можаце весці такі каляндар ўручную. Але тады яго прыйдзецца абнаўляць хоць бы раз на год (а то і часцей, як у «вясёлым» 2020-м), старанна ўпісваючы ўсе выхадныя, перасадкі і непрацоўныя дні, прыдуманыя нашай уладай. А затым паўтараць гэтую працэдуру кожны наступны год. Нуда.
Як наконт таго, каб крыху звар'яцець і зрабіць «вечны» фабрычны каляндар у Excel? Такі, які абнаўляецца сам, бярэ даныя з Інтэрнэту і заўсёды стварае абноўлены спіс непрацоўных дзён для наступнага выкарыстання ў якіх-небудзь разліках? Спакушальна?
Зрабіць гэта, на самай справе, зусім не складана.
Крыніца дадзеных
Галоўнае пытанне - дзе ўзяць дадзеныя? У пошуках падыходнага крыніцы я перабраў некалькі варыянтаў:
- Арыгіналы пастаноў публікуюцца на сайце ўрада ў фармаце PDF (вось, напрыклад, адна з іх) і адразу знікаюць – з іх немагчыма выцягнуць карысную інфармацыю.
- 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.
Расчараваўшыся ў афіцыйных крыніцах, я пачаў капаць неафіцыйныя. Іх шмат у інтэрнэце, але большасць з іх, зноў жа, зусім непрыдатныя для імпарту ў Excel і выдаюць вытворчы каляндар у выглядзе прыгожых малюнкаў. Але не нам гэта на сцяну вешаць, праўда?
І ў працэсе пошуку выпадкова адкрылася цудоўная рэч – сайт http://xmlcalendar.ru/
Без лішніх «наваротаў», просты, лёгкі і хуткі сайт, заточаны пад адну задачу — раздаць кожнаму вытворчы каляндар на патрэбны год у фармаце XML. Выдатна!
Калі раптам вы не ў курсе, то XML - гэта тэкставы фармат з змесцівам, пазначаным спец
На ўсялякі выпадак я звязаўся з аўтарамі сайта і яны пацвердзілі, што сайт існуе ўжо 7 гадоў, звесткі на ім пастаянна абнаўляюцца (у іх нават для гэтага ёсць філіял на github) і закрываць яго не збіраюцца. І я зусім не супраць, каб мы з вамі загружалі з яго дадзеныя для любых нашых праектаў і разлікаў у Excel. Бясплатна. Прыемна ўсведамляць, што яшчэ ёсць такія людзі! Павага!
Засталося загрузіць гэтыя дадзеныя ў Excel з дапамогай надбудовы Power Query (для версій Excel 2010-2013 яе можна спампаваць бясплатна з сайта Microsoft, а ў версіях Excel 2016 і навей яна ўжо ўбудаваная па змаўчанні ).
Логіка дзеянняў будзе наступная:
- Робім запыт на загрузку дадзеных з сайта за любы год
- Ператварэнне нашага запыту ў функцыю
- Прымяняем гэтую функцыю да спісу ўсіх даступных гадоў, пачынаючы з 2013 і заканчваючы бягучым - і атрымліваем «вечны» вытворчы каляндар з аўтаматычным абнаўленнем. Вуаля!
Крок 1. Імпартуйце каляндар на адзін год
Спачатку загрузіце вытворчы каляндар на любы год, напрыклад, на 2020. Для гэтага ў Excel перайдзіце на ўкладку Дата (Або Запыт на харчаваннекалі вы ўсталявалі яго як асобны дадатак) і абярыце З Інтэрнэту (З Інтэрнэту). У якое адкрылася акне ўстаўце спасылку на адпаведны год, скапіяваную з сайта:
Пасля націску на кнопку OK з'явіцца акно папярэдняга прагляду, у якім трэба націснуць кнопку Пераўтварэнне дадзеных (Пераўтварэнне дадзеных) or Каб змяніць дадзеныя (Рэдагаваць дадзеныя) і мы трапім у акно рэдактара запытаў Power Query, дзе працягнем працу з дадзенымі:
Адразу ж вы можаце бяспечна выдаліць на правай панэлі Параметры запыту (Налады запыту) крок мадыфікаваны тып (Зменены тып) Ён нам не патрэбны.
Табліца ў калонцы «Святы» змяшчае коды і апісанне непрацоўных дзён – азнаёміцца з яе змесцівам можна двойчы «праляцеўшы», націснуўшы на зялёнае слова табліца:
Каб вярнуцца назад, вам трэба будзе выдаліць на правай панэлі ўсе крокі, якія з'явіліся назад крыніца (Крыніца).
Другая табліца, да якой можна зайсці аналагічным чынам, змяшчае менавіта тое, што нам трэба - даты ўсіх непрацоўных дзён:
Засталося апрацаваць гэтую пліту, а менавіта:
1. Фільтраваць толькі святочныя даты (г.зн. адзінкі) па другім слупку Атрыбут: т
2. Выдаліць усе слупкі, акрамя першага, пстрыкнуўшы правай кнопкай мышы на загалоўку першага слупка і выбраўшы каманду Выдаліць іншыя слупкі (Выдаліць іншыя слупкі):
3. Раздзяліць першы слупок па кропках асобна для месяца і дня з дапамогай каманды Раздзяліць слупок – па падзельніку таб Пераўтварэнне (Пераўтварэнне — Раздзяліць слупок — Па раздзяляльніку):
4. І, нарэшце, стварыце вылічальны слупок са звычайнымі датамі. Для гэтага на таб Даданне слупка націсніце на кнопку Карыстальніцкі слупок (Дадаць слупок — Карыстальніцкі слупок) і ўвядзіце наступную формулу ў якое з'явілася акне:
=#датаваны(2020, [#»Атрыбут:d.1″], [#»Атрыбут:d.2″])
Тут аператар #date мае тры аргументы: год, месяц і дзень адпаведна. Пасля націску на OK мы атрымліваем неабходны слупок са звычайнымі датамі выхадных і выдаляем астатнія слупкі, як у кроку 2
Крок 2. Ператварэнне запыту ў функцыю
Наступная наша задача - пераўтварыць запыт, створаны для 2020 года, ва ўніверсальную функцыю для любога года (яе аргументам будзе нумар года). Для гэтага робім наступнае:
1. Пашырэнне (калі яшчэ не разгорнута) панэлі роспыты (Запыты) злева ў акне Power Query:
2. Пасля пераўтварэння запыту ў функцыю магчымасць бачыць крокі, якія складаюць запыт, і лёгка іх рэдагаваць, на жаль, знікае. Таму ёсць сэнс зрабіць копію нашага запыту і ўжо з ёй пагарэзаваць, а арыгінал пакінуць пра запас. Для гэтага пстрыкніце правай кнопкай мышы на левай панэлі нашага запыту календара і абярыце каманду Дублікат.
Зноў пстрыкнуўшы правай кнопкай мышы на выніковай копіі календара(2), будзе выбрана каманда Перайменаваць (Перайменаваць) і ўвядзіце новае імя - хай будзе, напрыклад, fxYear:
3. Адкрываем зыходны код запыту на ўнутранай мове Power Query (яна лаканічна называецца «М») з дапамогай каманды Пашыраны рэдактар таб агляд(Прагляд — пашыраны рэдактар) і ўнесці невялікія змены, каб ператварыць наш запыт у функцыю на любы год.
Гэта было:
Пасля:
Калі вас цікавяць падрабязнасці, то сюды:
- (год як нумар)=> – мы заяўляем, што наша функцыя будзе мець адзін лікавы аргумент – зменную год
- Устаўка зменнай год на вэб-спасылку ў кроку крыніца. Паколькі Power Query не дазваляе склейваць лічбы і тэкст, мы пераўтвараем нумар года ў тэкст на хаду з дапамогай функцыі Number.ToText
- Мы падстаўляем зменную года для 2020 на перадапошнім кроку #”Дададзены карыстальніцкі аб’ект«, дзе з фрагментаў мы склалі дату.
Пасля націску на кнопку Заканчэнне наш запыт становіцца функцыяй:
Крок 3. Імпартуйце календары на ўсе гады
Засталося зрабіць апошні асноўны запыт, які загрузіць дадзеныя за ўсе даступныя гады і звядзе ўсе атрыманыя святочныя даты ў адну табліцу. Для гэтага:
1. Пстрыкаем у левай панэлі запытаў на шэрым пустым месцы правай кнопкай мышы і паслядоўна выбіраем Новы запыт – Іншыя крыніцы – Пусты запыт (Новы запыт — З іншых крыніц — Пусты запыт):
2. Нам неабходна сфармаваць спіс усіх гадоў, для якіх мы будзем запытваць календары, гэта значыць 2013, 2014 ... 2020. Для гэтага ў радку формул пустога запыту, які з'явіўся, увядзіце каманду:
структура:
={ЛікА..ЛікБ}
… у Power Query стварае спіс цэлых лікаў ад A да B. Напрыклад, выраз
={1..5}
… будзе ствараць спіс з 1,2,3,4,5.
Ну а каб не прывязвацца жорстка да 2020 года, выкарыстоўваем функцыю DateTime.LocalNow() – аналаг функцыі Excel СЁННЯ (СЁННЯ) у Power Query – і здабываць з яго, у сваю чаргу, бягучы год функцыяй Дата.Год.
3. Атрыманы набор гадоў, хоць і выглядае цалкам адэкватна, уяўляе сабой не табліцу для Power Query, а адмысловы аб'ект – спіс (Спіс). Але пераўтварыць яго ў табліцу не праблема: проста націсніце кнопку Да стала (Да стала) у левым верхнім куце:
4. Фінішная лінія! Прымяненне функцыі, якую мы стварылі раней fxYear да атрыманага спісу гадоў. Для гэтага на таб Даданне слупка націсніце кнопку Выклік карыстальніцкай функцыі (Дадаць слупок — выклікаць карыстальніцкую функцыю) і ўсталяваць яго адзіны аргумент - слупок Column1 на працягу многіх гадоў:
Пасля націску на кнопку OK наша функцыя fxYear імпарт будзе працаваць па чарзе для кожнага года і мы атрымаем слупок, дзе ў кожнай ячэйцы будзе табліца з датамі непрацоўных дзён (змесціва табліцы добра відаць, калі націснуць на фоне ячэйкі побач з слова табліца):
Застаецца разгарнуць змесціва ўкладзеных табліц, націснуўшы на значок з падвойнымі стрэлкамі ў загалоўку слупка Даты (галачка Выкарыстоўвайце зыходнае імя слупка ў якасці прэфікса яго можна выдаліць):
… і пасля націску на OK атрымліваем тое, што хацелі – спіс усіх святаў з 2013 па бягучы год:
Першы, ужо непатрэбны слупок, можна выдаліць, а для другога задаць тып дадзеных дата (Дата) у выпадальным спісе ў загалоўку слупка:
Сам запыт можна перайменаваць у нешта больш значнае, чым Запыт1 а затым загрузіць вынікі на ліст у выглядзе дынамічнай «разумнай» табліцы з дапамогай каманды зачыніць і спампаваць таб Галоўная (Галоўная — Зачыніць і загрузіць):
Вы можаце абнавіць створаны каляндар у будучыні, пстрыкнуўшы правай кнопкай мышы на табліцы або зрабіўшы запыт у правай панэлі праз каманду Абнавіць і захаваць. Ці скарыстайцеся кнопкай Абнавіць усё таб Дата (Дата — Абнавіць усё) або спалучэнне клавіш Ctrl+Alt+F5.
Гэта ўсё.
Цяпер вам больш не трэба марнаваць час і энергію думак на пошук і абнаўленне спісу святаў - цяпер у вас ёсць «вечны» вытворчы каляндар. У любым выпадку, пакуль аўтары сайта http://xmlcalendar.ru/ падтрымліваюць сваё дзецішча, якое, спадзяюся, будзе яшчэ вельмі-вельмі доўга (яшчэ раз ім дзякуй!).
- Імпартуйце курс біткойнаў, каб атрымаць поспех з Інтэрнэту праз Power Query
- Пошук наступнага працоўнага дня з дапамогай функцыі WORKDAY
- Знаходжанне перасячэння інтэрвалаў дат