Фабрички календар у Екцел-у

Производни календар, односно листа датума, где су сви службени радни дани и празници означени у складу са тим – апсолутно неопходна ствар за сваког корисника Мицрософт Екцел-а. У пракси, не можете без тога:

• у рачуноводственим обрачунима (плата, радни стаж, годишњи одмори...)
• у логистици – за тачно одређивање рокова испоруке, узимајући у обзир викенде и празнике (сетите се класичног „ајде после празника?“)
  
• у управљању пројектима – за тачну процену термина, узимајући у обзир, опет, радне нерадне дане
• свака употреба функција као што су РАДНИ ДАН (РАДНИ ДАН) or ЧИСТИ РАДНИЦИ (МРЕЖНИ ДАНИ), јер као аргумент захтевају списак празника
• када користите функције Тиме Интеллигенце (као што су ТОТАЛИТД, ТОТАЛМТД, САМЕПЕРИОДЛАСТИЕАР, итд.) у Повер Пивот-у и Повер БИ-ју
  
• … итд. итд. – много примера.
  

Лакше је онима који раде у корпоративним ЕРП системима као што су 1Ц или САП, јер је производни календар уграђен у њих. Али шта је са корисницима Екцел-а?

Такав календар можете, наравно, држати ручно. Али тада ћете морати да га ажурирате најмање једном годишње (или још чешће, као у „весели“ 2020.), пажљиво уносећи све викенде, трансфере и нерадне дане које је измислила наша влада. И онда поновите ову процедуру сваке следеће године. Досада.

Шта кажете на то да мало полудите и направите „вечни“ фабрички календар у Екцел-у? Онај који се сам ажурира, узима податке са интернета и увек генерише ажурну листу нерадних дана за накнадну употребу у било каквим прорачунима? Примамљив?

У ствари, то није тешко учинити.

Извор података

Главно питање је где добити податке? У потрази за одговарајућим извором, прошао сам кроз неколико опција:

• Оригиналне уредбе се објављују на сајту владе у ПДФ формату (овде, једна од њих, на пример) и одмах нестају – из њих се не могу извући корисне информације.
• Примамљива опција, на први поглед, изгледао је „Портал отворених података Федерације“, где постоји одговарајући скуп података, али се, након детаљнијег разматрања, све испоставило тужно. Сајт је ужасно незгодан за увоз у Екцел, техничка подршка не реагује (самоизолован?), а сами подаци су тамо одавно застарели – календар производње за 2020. је последњи пут ажуриран у новембру 2019. (срамота!) и , наравно, не садржи наш „коронавирус“ и „гласачки“ викенд 2020, на пример.

Разочаран званичним изворима, почео сам да копам по незваничним. Има их много на Интернету, али већина их је, опет, потпуно неприкладна за увоз у Екцел и дају производни календар у облику прелепих слика. Али није на нама да га окачимо на зид, зар не?

И у процесу претраживања случајно је откривена дивна ствар – сајт хттп://кмлцалендар.ру/

Без непотребних „проблематика“, једноставан, лаган и брз сајт, изоштрен за један задатак – да свима пружи календар производње за жељену годину у КСМЛ формату. Одлично!

Ако изненада нисте упознати, онда је КСМЛ текстуални формат са садржајем означеним посебним . Лаган, згодан и читљив за већину савремених програма, укључујући Екцел.

За сваки случај, контактирао сам ауторе сајта и они су потврдили да сајт постоји већ 7 година, подаци о њему се стално ажурирају (чак имају грану на гитхуб-у за ово) и неће га затворити. И уопште ми не смета што ти и ја учитавамо податке из њега за било који наш пројекат и прорачуне у Екцел-у. Бесплатно је. Лепо је знати да оваквих људи још има! Респецт!

Остаје да ове податке учитате у Екцел помоћу додатка Повер Куери (за верзије Екцел 2010-2013 може се бесплатно преузети са Мицрософт веб локације, а у верзијама Екцел 2016 и новијим већ је подразумевано уграђен ).

Логика акција ће бити следећа:

1. Захтевамо преузимање података са сајта за било коју годину
2. Претварање нашег захтева у функцију
3. Ову функцију примењујемо на листу свих доступних година, почев од 2013. па до текуће године – и добијамо „вечни“ производни календар са аутоматским ажурирањем. Воила!
   

Корак 1. Увезите календар за једну годину

Прво учитајте календар производње за било коју годину, на пример, за 2020. Да бисте то урадили, у Екцел-у идите на картицу Датум (Или Повер Куериако сте га инсталирали као посебан додатак) и изаберите Од интернета (Са веба). У прозору који се отвори, налепите везу до одговарајуће године, копирану са сајта:

После клика на OK појављује се прозор за преглед у којем треба да кликнете на дугме Претвори податке (Трансформисање података) or Да бисте променили податке (Измени податке) и доћи ћемо до прозора Повер Куери уређивача упита, где ћемо наставити рад са подацима:

Одмах можете безбедно да обришете у десном панелу Параметри захтева (Подешавања упита) корак модификованог типа (промењен тип) Он нам не треба.

Табела у колони празници садржи шифре и описе нерадних дана – њен садржај можете видети тако што ћете је два пута „пропасти“ кликом на зелену реч Табела:

Да бисте се вратили, мораћете да избришете у десном панелу све кораке на које сте се вратили извор (Извор).

Друга табела, којој се може приступити на сличан начин, садржи управо оно што нам је потребно – датуме свих нерадних дана:

Остаје да се обради ова плоча, и то:

1. Филтрирајте само празнике (тј. оне) по другој колони Атрибут:т

2. Избришите све колоне осим прве – десним кликом на наслов прве колоне и избором команде Избришите друге колоне (Уклони друге колоне):

3. Поделите прву колону по тачку одвојено за месец и дан помоћу команде Раздвојите колону – помоћу граничника табулатор Трансформација (Трансформација — Подели колону — По граничнику):

4. И на крају креирајте израчунату колону са нормалним датумима. Да бисте то урадили, на картици Додавање колоне кликните на дугме Прилагођена колона (Додај колону — Прилагођена колона) и унесите следећу формулу у прозор који се појави:

=#датед(2020, [#»Аттрибуте:д.1″], [#»Аттрибуте:д.2″])

Овде оператор #дате има три аргумента: годину, месец и дан. Након што кликнете на OK добијамо потребну колону са нормалним датумима викенда и бришемо преостале колоне као у кораку 2

Корак 2. Претварање захтева у функцију

Наш следећи задатак је да конвертујемо упит креиран за 2020. у универзалну функцију за било коју годину (број године ће бити њен аргумент). Да бисмо то урадили, радимо следеће:

1. Проширивање (ако већ није проширено) панел Упити (Упита) са леве стране у прозору Повер Куери:

2. Након претварања захтева у функцију, могућност да се виде кораци који чине захтев и да се лако уређују, нажалост, нестаје. Зато има смисла направити копију нашег захтева и већ се забавити са њим, а оригинал оставити у резерви. Да бисте то урадили, кликните десним тастером миша у левом окну на наш захтев за календар и изаберите команду Дуплицате.

Поново кликните десним тастером миша на резултујућу копију календара(2) изабраће команду Преименовање (Преименуј) и унесите ново име – нека буде нпр. фкИеар:

3. Отварамо изворни код упита на интерном Повер Куери језику (сажето се зове „М“) користећи команду Напредни уређивач табулатор преглед(Приказ — Напредни уређивач) и направимо мале измене тамо да наш захтев претворимо у функцију за било коју годину.

Било је:

После:

Ако сте заинтересовани за детаље, онда овде:

• (година као број)=>  – изјављујемо да ће наша функција имати један нумерички аргумент – променљиву година
• Лепљење променљиве година до веб везе у кораку извор. Пошто вам Повер Куери не дозвољава да лепите бројеве и текст, конвертујемо број године у текст у ходу помоћу функције Нумбер.ТоТект
• Варијаблу године за 2020. замењујемо у претпоследњем кораку #”Додан прилагођени објекат«, где смо од фрагмената формирали датум.
  

После клика на завршити наш захтев постаје функција:

Корак 3. Увезите календаре за све године

Последње што је преостало је да направите последњи главни упит, који ће учитати податке за све расположиве године и додати све примљене датуме празника у једну табелу. За ово:

1. Десним тастером миша кликнемо на леву таблу за упит у сивом празном простору и бирамо узастопно Нови захтев – Други извори – Празан захтев (Нови упит — Из других извора — Празан упит):

2. Морамо да генеришемо листу свих година за које ћемо тражити календаре, односно 2013, 2014… 2020. Да бисте то урадили, у траку формуле празног упита који се појави унесите команду:

Структура:

={БројА..БројБ}

… у Повер Куерију генерише листу целих бројева од А до Б. На пример, израз

={1..5}

… би произвео списак од 1,2,3,4,5.

Па, да не бисмо били ригидно везани за 2020, користимо функцију ДатеТиме.ЛоцалНов() – аналог Екцел функције ДАНАС (ДАНАС) у Повер Куерију – и из њега извући, заузврат, текућу годину по функцији Датум.Година.

3. Добијени скуп година, иако изгледа сасвим адекватно, није табела за Повер Куери, већ посебан објекат – листа (Листа). Али конвертовање у табелу није проблем: само кликните на дугме За сто (До стола) у горњем левом углу:

4. Циљ! Примена функције коју смо раније креирали фкИеар на резултујућу листу година. Да бисте то урадили, на картици Додавање колоне притисните дугме Позовите прилагођену функцију (Додај колону — Позовите прилагођену функцију) и постави свој једини аргумент – колону ЦолумнКСНУМКС током година:

После клика на OK наша функција фкИеар увоз ће радити редом за сваку годину и добићемо колону у којој ће свака ћелија садржати табелу са датумима нерадних дана (садржај табеле је јасно видљив ако кликнете на позадину ћелије поред Реч Табела):

Остаје да проширите садржај угнежђених табела кликом на икону са дуплим стрелицама у заглављу колоне Датуми (квачица Користите оригинално име колоне као префикс може се уклонити):

… и након клика на OK добијамо оно што смо желели – списак свих празника од 2013. до текуће године:

Прву, већ непотребну колону, можете избрисати, а за другу подесити тип података датум (Датум) у падајућој листи у наслову колоне:

Сам упит се може преименовати у нешто смисленије од Захтев1 а затим учитајте резултате на лист у облику динамичке „паметне“ табеле користећи команду затворите и преузмите табулатор Početna (Почетна — Затвори и учитај):

Убудуће можете ажурирати креирани календар десним кликом на табелу или упитом у десном окну преко команде Ажурирајте и сачувајте. Или користите дугме Освежи све табулатор Датум (Датум — Освежи све) или пречица на тастатури Цтрл+други+F5.

То је све.

Сада више никада нећете морати да губите време и размишљање тражећи и ажурирајући листу празника – сада имате „вечни“ календар производње. У сваком случају, све док аутори сајта хттп://кмлцалендар.ру/ подржавају своје потомство, које ће, надам се, још јако, јако дуго (хвала им још једном!).

• Увезите стопу биткоина да бисте били бољи са интернета преко Повер Куери-ја
• Проналажење следећег радног дана помоћу функције ВОРКДАИ
  
• Проналажење пресека датумских интервала