Мундариҷа
Тақвими истеҳсолӣ, яъне рӯйхати санаҳо, ки дар он ҳама рӯзҳои корӣ ва идҳои расмӣ мувофиқи он қайд карда мешаванд – чизи комилан зарурӣ барои ҳар як истифодабарандаи Microsoft Excel. Дар амал, шумо бе он кор карда наметавонед:
- дар ҳисобҳои муҳосибӣ (маош, собиқаи корӣ, рухсатӣ ...)
- дар логистика - барои дуруст муайян кардани вақти интиқол бо назардошти рӯзҳои истироҳат ва ид (дар хотир доред, ки классикии "пас аз ид биёед?").
- дар идоракунии лоиха — барои дуруст бахо доданн мухлатхо бо назардошти боз хам рузхои корй-гай-рикорй
- ҳама гуна истифодаи функсияҳо ба монанди РУЗИ КОР (РУЗИ КОР) or КОРГАРОНИ ПОК (РӮЗҲО), зеро онҳо рӯйхати идҳоро ҳамчун далел талаб мекунанд
- ҳангоми истифодаи функсияҳои Time Intelligence (ба монанди TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR ва ғайра) дар Power Pivot ва Power BI
- … ва ғайра – мисолҳои зиёд.
Барои онҳое, ки дар системаҳои корпоративии ERP ба монанди 1C ё SAP кор мекунанд, осонтар аст, зеро тақвими истеҳсолӣ дар онҳо сохта шудааст. Аммо дар бораи корбарони 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 то соли ҷорӣ татбиқ мекунем ва мо тақвими истеҳсолии "абадӣ" бо навсозии худкор мегирем. Войла!
Қадами 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) фармонро интихоб мекунад Rename (Номи дигар) ва номи нав ворид кунед - бигзор он, масалан, fxYear:
3. Мо бо истифода аз фармон коди сарчашмаи дархостро бо забони дохилии Power Query мекушоем (он ба таври мухтасар “M” номида мешавад) Муҳаррири пешрафта ҷадвали Шарҳи(Намоиш — Муҳаррири пешрафта) ва дар он ҷо тағироти хурде ворид кунед, то дархости моро ба функсия барои ҳар сол табдил диҳед.
Буд:
Баъд аз:
Агар шумо ба тафсилот таваҷҷӯҳ дошта бошед, дар ин ҷо:
- (сол ҳамчун рақам) => - мо эълон мекунем, ки функсияи мо як аргументи ададӣ дорад - тағирёбанда сол
- Ҷойгир кардани тағирёбанда сол ба истиноди веб дар қадам сарчашма. Азбаски Power Query ба шумо имкон намедиҳад, ки рақамҳо ва матнро часпонед, мо бо истифода аз функсия рақами солро ба матн табдил медиҳем. Number.ToText
- Мо тағирёбандаи солро барои соли 2020 дар қадами пеш аз ниҳоӣ иваз мекунем #"Объекти фармоишӣ илова карда шудааст«, ки мо санаро аз пораҳо ташкил кардем.
Баъд аз пахш кардан тамом кардан дархости мо ба функсия табдил меёбад:
Қадами 3. Тақвимҳоро барои ҳама солҳо ворид кунед
Охирин чизе, ки боқӣ мондааст, анҷом додани дархости охирини асосӣ аст, ки он маълумотро барои тамоми солҳои дастрас бор мекунад ва ҳама санаҳои ҷашни гирифташударо ба як ҷадвал илова мекунад. Барои ин:
1. Мо дар панели чапи дархост дар фазои холии хокистарӣ бо тугмаи рости муш пахш мекунем ва пайдарпай интихоб мекунем. Дархости нав – Сарчашмаҳои дигар – Дархости холӣ (Дархости нав - Аз дигар манбаъҳо - Дархости холӣ):
2. Мо бояд рӯйхати ҳамаи солҳоро, ки барои онҳо тақвимҳо дархост мекунем, тавлид кунем, яъне солҳои 2013, 2014 … 2020. Барои ин дар сатри формулаи дархости холие, ки пайдо мешавад, фармонро ворид кунед:
Сохтор:
={РақамиА..РақамиB}
… дар 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
- Ҷустуҷӯи буриши фосилаҳои сана