Мазмұны
Өндіріс күнтізбесі, яғни барлық ресми жұмыс күндері мен мерекелер сәйкес белгіленген күндер тізімі – кез келген Microsoft Excel пайдаланушысы үшін өте қажет нәрсе. Іс жүзінде сіз онсыз жасай алмайсыз:
- бухгалтерлік есептерде (еңбекақы, еңбек өтілі, демалыстар...)
- логистикада – демалыс және мереке күндерін ескере отырып жеткізу уақытын дұрыс анықтау үшін («мерекеден кейін келе ме?» классикалық сөзін есте сақтаңыз)
- жобалық менеджментте – тағы да жұмыс емес күндерді ескере отырып, мерзімдерді дұрыс бағалау үшін
- сияқты функцияларды кез келген пайдалану ЖҰМЫС КҮНІ (ЖҰМЫС КҮНІ) or ТАЗА ЖҰМЫСШЫЛАР (ЖЕЛІ КҮНДЕРІ), себебі олар дәлел ретінде мерекелер тізімін талап етеді
- Power Pivot және Power BI жүйелерінде Time Intelligence функцияларын (мысалы, TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, т.б.) пайдаланған кезде
- … т.б. т.б. – көптеген мысалдар.
1C немесе SAP сияқты корпоративтік ERP жүйелерінде жұмыс істейтіндер үшін оңайырақ, өйткені олардың ішінде өндірістік күнтізбе бар. Бірақ Excel пайдаланушылары туралы не деуге болады?
Сіз, әрине, мұндай күнтізбені қолмен жүргізе аласыз. Бірақ содан кейін оны кем дегенде жылына бір рет (немесе «көңілді» 2020-дағыдай жиі) жаңартуға тура келеді, біздің үкімет ойлап тапқан барлық демалыс күндерін, аударымдарды және жұмыс емес күндерді мұқият енгізіңіз. Содан кейін бұл процедураны келесі жыл сайын қайталаңыз. Жалығу.
Кішкене ақылсыз болып, Excel бағдарламасында «мәңгілік» зауыт күнтізбесін жасауға қалай қарайсыз? Өзін-өзі жаңартып, Интернеттен деректерді алатын және кез келген есептеулерде кейіннен пайдалану үшін әрқашан жұмыс істемейтін күндердің жаңартылған тізімін жасайтын біреуі? Еліктірер ме?
Мұны істеу, шын мәнінде, қиын емес.
Деректер көзі
Негізгі сұрақ - деректерді қайдан алуға болады? Қолайлы дереккөзді іздеуде мен бірнеше нұсқаны қолдандым:
- Түпнұсқа қаулылар үкіметтің веб-сайтында PDF форматында жарияланады (мұнда, мысалы, олардың бірі) және бірден жоғалып кетеді – олардан пайдалы ақпаратты алып тастау мүмкін емес.
- Бір қарағанда қызықты нұсқа «Федерацияның ашық деректер порталы» болып көрінді, онда сәйкес деректер жинағы бар, бірақ мұқият зерттегенде бәрі қайғылы болды. Сайт Excel бағдарламасына импорттау үшін өте ыңғайсыз, техникалық қолдау жауап бермейді (өзін-өзі оқшауланған ба?), және деректердің өзі ұзақ уақыт бойы ескірген - 2020 жылға арналған өндірістік күнтізбе соңғы рет 2019 жылдың қарашасында жаңартылған (масқара!) және , әрине, біздің «коронавирус» және мысалы, 2020 жылғы «дауыс беру» демалыс күндерін қамтымайды.
Ресми дереккөздерден көңілім қалғандықтан, бейресми дереккөздерді қазуға кірістім. Интернетте олардың көпшілігі бар, бірақ олардың көпшілігі Excel бағдарламасына импорттауға мүлдем жарамсыз және әдемі суреттер түрінде өндірістік күнтізбе береді. Бірақ оны қабырғаға ілу біз үшін емес, солай ма?
Іздеу барысында кездейсоқ тамаша нәрсе табылды – http://xmlcalendar.ru/ сайты
Қажетсіз «бұрқыстар»сыз, қарапайым, жеңіл және жылдам сайт, бір тапсырмаға арналған - барлығына XML форматында қалаған жылға өндірістік күнтізбе беру. Өте жақсы!
Егер сіз кенеттен білмесеңіз, онда XML - бұл арнайы деп белгіленген мазмұны бар мәтін пішімі
Қалай болғанда да, мен сайттың авторларына хабарластым және олар сайттың 7 жыл бойы бар екенін растады, ондағы деректер үнемі жаңартылып отырады (олардың бұл үшін тіпті github-та филиалы бар) және олар оны жапқысы келмейді. Мен сіз бен біздің Excel бағдарламасындағы кез келген жобаларымыз бен есептеулеріміз үшін одан деректерді жүктеп алатынымызға мүлдем қарсы емеспін. Тегін. Осындай адамдардың әлі де бар екенін білу жақсы! Құрмет!
Бұл деректерді Power Query қондырмасы арқылы Excel бағдарламасына жүктеу қалады (Excel 2010-2013 нұсқалары үшін оны Microsoft веб-сайтынан тегін жүктеп алуға болады, ал Excel 2016 және одан кейінгі нұсқаларында ол әдепкі бойынша ендірілген. ).
Іс-әрекеттердің логикасы келесідей болады:
- Біз кез келген бір жылға сайттан деректерді жүктеп алу туралы өтініш жасаймыз
- Сұранысты функцияға айналдыру
- Біз бұл функцияны 2013 жылдан бастап ағымдағы жылға дейінгі барлық қолжетімді жылдар тізіміне қолданамыз және автоматты түрде жаңартылатын «мәңгілік» өндірістік күнтізбе аламыз. Voila!
1-қадам. Бір жылға арналған күнтізбені импорттау
Алдымен, кез келген бір жылға, мысалы, 2020 жылға арналған өндіріс күнтізбесін жүктеңіз. Мұны істеу үшін Excel бағдарламасында қойындыға өтіңіз. мәліметтер (немесе Қуат сұрауыегер сіз оны бөлек қондырма ретінде орнатқан болсаңыз) және таңдаңыз Интернеттен (Интернеттен). Ашылған терезеде сайттан көшірілген тиісті жылға сілтемені қойыңыз:
Басқаннан кейін OK алдын ала қарау терезесі пайда болады, онда түймені басу керек Деректерді түрлендіру (Деректерді түрлендіру) or Деректерді өзгерту үшін (Деректерді өңдеу) және біз Power Query сұрау өңдегішінің терезесіне кіреміз, онда деректермен жұмысты жалғастырамыз:
Оң жақ панельде бірден қауіпсіз жоюға болады Параметрлерді сұрау (Сұрау параметрлері) қадам өзгертілген түрі (Өзгертілген түрі) Бізге ол керек емес.
Мереке бағанындағы кестеде жұмыс істемейтін күндердің кодтары мен сипаттамалары бар - оның мазмұнын жасыл сөзді басу арқылы екі рет «түсу» арқылы көруге болады. үстел:
Кері оралу үшін оң жақ панельде қайтып келген барлық қадамдарды жою керек қайнар көз (Көз).
Осыған ұқсас жолмен қол жеткізуге болатын екінші кестеде бізге қажет нәрсе бар - барлық жұмыс істемейтін күндердің күндері:
Бұл пластинаны өңдеу қалады, атап айтқанда:
1. Екінші баған бойынша тек мереке күндерін (яғни бір күндерді) сүзіңіз Атрибут: t
2. Біріншіден басқа барлық бағандарды жойыңыз – бірінші бағанның тақырыбын тінтуірдің оң жақ түймешігімен басып, пәрменді таңдау Басқа бағандарды жою (Басқа бағандарды жою):
3. Пәрмен арқылы бірінші бағанды ай мен күнге бөлек нүктеге бөліңіз Бөлінген баған – Бөлгіш бойынша қойындысы трансформация (Transform — Бөлу баған — Бөлгіш бойынша):
4. Соңында қалыпты күндермен есептелген бағанды жасаңыз. Мұны істеу үшін қойындыда Баған қосу түймешігін басыңыз Реттелетін баған (Баған қосу — теңшелетін баған) және пайда болған терезеге келесі формуланы енгізіңіз:
=#күні(2020, [#»Атрибут:d.1″], [#»Атрибут:d.2″])
Мұнда #date операторының үш аргументі бар: сәйкесінше жыл, ай және күн. Басқаннан кейін OK біз қалыпты демалыс күндерімен қажетті бағанды аламыз және 2-қадамдағыдай қалған бағандарды жоямыз
Қадам 2. Сұранысты функцияға айналдыру
Біздің келесі міндетіміз - 2020 жылға жасалған сұрауды кез келген жыл үшін әмбебап функцияға түрлендіру (жыл саны оның аргументі болады). Ол үшін келесі әрекеттерді орындаймыз:
1. Панельді кеңейту (егер әлі кеңейтілмеген болса). Сұраулар (Сұраулар) Power Query терезесінің сол жағында:
2. Сұрауды функцияға түрлендіруден кейін сұрауды құрайтын қадамдарды көру және оларды оңай өңдеу мүмкіндігі, өкінішке орай, жоғалады. Сондықтан, біздің өтінішіміздің көшірмесін жасап, онымен көңіл көтеру және түпнұсқаны резервте қалдыру мағынасы бар. Мұны істеу үшін күнтізбе сұрауындағы сол жақ тақтаны тінтуірдің оң жақ түймешігімен басып, Көшірме пәрменін таңдаңыз.
Күнтізбенің (2) алынған көшірмесін тінтуірдің оң жақ түймешігімен қайтадан басу пәрменді таңдайды Атын өзгерту (Атын өзгерту) және жаңа атауды енгізіңіз – мысалы, fxYear:
3. Пәрменді пайдаланып сұраудың бастапқы кодын ішкі Power Query тілінде ашамыз (ол қысқаша «M» деп аталады) Жетілдірілген редактор қойындысы шолу(Қарау — Кеңейтілген редактор) және біздің сұрауымызды кез келген жылға функцияға айналдыру үшін сол жерге шағын өзгерістер енгізіңіз.
Ол болды:
Кейін:
Егер сізді егжей-тегжейлері қызықтырса, мына жерде:
- (сан ретінде жыл)=> – функциямызда бір сандық аргумент – айнымалы болатынын мәлімдейміз жыл
- Айнымалыны қою жыл веб-сілтемеге қадамда қайнар көз. Power Query сандар мен мәтінді желімдеуге мүмкіндік бермейтіндіктен, функцияны пайдаланып жыл нөмірін жылдам мәтінге түрлендіреміз. Сан.Мәтінге
- Біз соңғы қадамда жыл айнымалысын 2020 жылға ауыстырамыз #"Арнаулы нысан қосылды«, онда біз үзінділерден күнді қалыптастырдық.
Басқаннан кейін Аяқтау біздің сұрауымыз функцияға айналады:
3-қадам. Барлық жылдарға арналған күнтізбелерді импорттау
Соңғысы - барлық қолжетімді жылдардағы деректерді жүктеп салатын және барлық алынған мереке күндерін бір кестеге қосатын соңғы негізгі сұрауды жасау. Бұл үшін:
1. Тінтуірдің оң жақ түймешігімен сұр бос кеңістікте сол жақ сұрау тақтасын шертіп, дәйекті түрде таңдаймыз. Жаңа сұрау – Басқа көздер – Бос сұрау (Жаңа сұрау — басқа көздерден — бос сұрау):
2. Біз күнтізбелерді сұрайтын барлық жылдардың тізімін жасауымыз керек, яғни 2013, 2014 … 2020. Ол үшін пайда болған бос сұраудың формула жолына пәрменді енгізіңіз:
Құрылым:
={Асаны..Б саны}
… Power Query бағдарламасында А-дан В-ге дейінгі бүтін сандар тізімін жасайды. Мысалы, өрнек
={1..5}
... 1,2,3,4,5 тізімін жасайды.
2020 жылға қатып қалмау үшін біз функцияны қолданамыз DateTime.LocalNow() – Excel функциясының аналогы БҮГІН (БҮГІН) Power Query ішінде – және одан өз кезегінде ағымдағы жылды функция бойынша шығарып алыңыз Күн.Жыл.
3. Нәтижедегі жылдар жиынтығы, ол жеткілікті түрде көрінсе де, Power Query кестесі емес, арнайы нысан – тізім (Тізім). Бірақ оны кестеге түрлендіру қиын емес: жай ғана түймені басыңыз Үстелге (Кестеге) жоғарғы сол жақ бұрышта:
4. Мәре сызығы! Бұрын жасалған функцияны қолдану fxYear нәтижелі жылдар тізіміне. Мұны істеу үшін қойындыда Баған қосу түймесін басыңыз Теңшелетін функцияны шақыру (Баған қосу — теңшелетін функцияны шақыру) және оның жалғыз аргументін орнатыңыз – баған Column1 жылдар бойы:
Басқаннан кейін OK біздің функциямыз fxYear импорт әр жыл үшін кезекпен жұмыс істейді және біз әр ұяшықта жұмыс істемейтін күндер көрсетілген кестені қамтитын бағанды аламыз (жанындағы ұяшықтың фонында бассаңыз, кестенің мазмұны анық көрінеді. сөз үстел):
Баған тақырыбындағы қос көрсеткілері бар белгішені басу арқылы кірістірілген кестелердің мазмұнын кеңейту қалады. күндер (белгі Бастапқы баған атауын префикс ретінде пайдаланыңыз жоюға болады):
… және басқаннан кейін OK біз қалағанымызды алдық – 2013 жылдан ағымдағы жылға дейінгі барлық мерекелердің тізімі:
Бірінші, қазірдің өзінде қажет емес бағанды жоюға болады, ал екіншісі үшін деректер түрін орнатуға болады дата (Күні) баған тақырыбындағы ашылмалы тізімде:
Сұраудың өзін одан да мағыналырақ атаумен өзгертуге болады Сұраныс 1 содан кейін нәтижелерді пәрменді пайдаланып динамикалық «ақылды» кесте түрінде параққа жүктеңіз жабыңыз және жүктеңіз қойындысы Home (Үйге — Жабу және жүктеу):
Жасалған күнтізбені болашақта пәрмен арқылы кестені немесе оң жақ тақтадағы сұрауды тінтуірдің оң жақ түймешігімен басу арқылы жаңартуға болады. Жаңарту және сақтау. Немесе түймені пайдаланыңыз Барлығын жаңарту қойындысы мәліметтер (Күні — Барлығын жаңарту) немесе пернелер тіркесімі Ctrl+Alt+F5.
Бар болғаны.
Енді мерекелер тізімін іздеуге және жаңартуға енді уақыт пен көп ой жұмсаудың қажеті жоқ – енді сізде «мәңгілік» өндірістік күнтізбе бар. Қалай болғанда да, http://xmlcalendar.ru/ сайтының авторлары өз ұрпақтарын қолдайтын болса, бұл өте ұзақ уақыт болады деп үміттенемін (оларға тағы да рахмет!).
- Power Query арқылы интернеттен биткоин бағамын импорттаңыз
- WORKDAY функциясы арқылы келесі жұмыс күнін табу
- Күн аралықтарының қиылысуын табу