Excelдеги завод календары

Өндүрүш календары, башкача айтканда, бардык расмий иш күндөрү жана майрамдар тиешелүү түрдө белгиленген даталардын тизмеси – Microsoft Excel программасынын ар бир колдонуучусу үчүн өтө зарыл нерсе. Иш жүзүндө, ансыз кыла албайт:

• бухгалтердик эсептерде (эмгек акы, эмгек стажы, отпуска...)
• логистикада - дем алыш жана майрам күндөрүн эске алуу менен жеткирүү убактысын туура аныктоо үчүн (классикалык "майрамдардан кийин келеби?"
  
• Долбоорду башкарууда – мөөнөттөрдү туура баалоо үчүн, дагы бир жолу, жумуш эмес күндөрдү эске алуу менен
• сыяктуу функцияларды колдонуу ИШ КУНУ (жумуш күнү) or ТАЗА ЭМГЕКЧИЛЕР (NETWORKDAYS), анткени алар аргумент катары майрамдардын тизмесин талап кылат
• Power Pivot жана Power BIде Time Intelligence функцияларын (мисалы, TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR ж.б.) колдонууда
  
• … ж.б. ж.б. – көптөгөн мисалдар.
  

1С же SAP сыяктуу корпоративдик ERP системаларында иштегендер үчүн жеңилирээк, анткени өндүрүш календары аларга орнотулган. Бирок Excel колдонуучулары жөнүндө эмне айтууга болот?

Сиз, албетте, мындай календарды кол менен сактай аласыз. Бирок анда сиз аны жылына жок дегенде бир жолу жаңыртып турууга туура келет (же «көңүлдүү» 2020-жылдагыдай), биздин өкмөт ойлоп тапкан бардык дем алыш күндөрүн, которууларды жана жумуш эмес күндөрдү кылдаттык менен киргизиңиз. Анан кийинки жылы бул процедураны кайтала. зеригүү.

Бир аз жинди болуп, Excelде "түбөлүктүү" фабрика календарын түзсөңүз? Өзүн жаңыртып, Интернеттен маалыматтарды алып, ар дайым ар кандай эсептөөлөр үчүн кийинки колдонуу үчүн жумуш эмес күндөрдүн актуалдуу тизмесин түзүп турган бири? Азгырып жатабы?

Бул үчүн, чынында, кыйын эмес.

Маалымат булагы

Негизги суроо - маалыматтарды кайдан алуу керек? Ылайыктуу булакты издеп, мен бир нече варианттарды карап чыктым:

• Жарлыктардын түп нускасы өкмөттүн веб-сайтында PDF форматында жарыяланып (мисалы, бул жерде) жана дароо жок болот – алардан пайдалуу маалыматты алып салуу мүмкүн эмес.
• Бир караганда азгырыктуу вариант "Федерациянын ачык маалымат порталы" болуп көрүнгөн, анда тиешелүү маалыматтар топтому бар, бирок жакшыраак карап чыкканда баары кайгылуу болуп чыкты. Сайт Excelге импорттоо үчүн абдан ыңгайсыз, техникалык колдоо жооп бербейт (өзүнчө обочолонгонбу?) жана ал жерде маалыматтар узак убакыт бою эскирген - 2020-жылга өндүрүш календары акыркы жолу 2019-жылдын ноябрында жаңыртылган (уят!) жана , албетте, мисалы, биздин "коронавирус" жана 2020-жылдын "добуш берүү" дем алыш күндөрүн камтыбайт.

Расмий булактардан көңүлүм калып, бейрасмий булактарды казып баштадым. Интернетте алардын көбү бар, бирок алардын көбү Excelге импорттоого таптакыр жараксыз жана кооз сүрөттөр түрүндө өндүрүш календарын беришет. Бирок аны дубалга илип коюу биз үчүн эмес, туурабы?

Ал эми издөө процессинде кокусунан сонун нерсе табылды - http://xmlcalendar.ru/ сайты

Керексиз "бууроолорсуз", жөнөкөй, жеңил жана тез сайт, бир тапшырма үчүн курчутылган - ар бир адамга XML форматында каалаган жыл үчүн өндүрүш календарын берүү. Эң сонун!

Эгер күтүлбөгөн жерден сиз билбесеңиз, анда XML - бул атайын менен белгиленген мазмуну бар текст форматы . Жеңил, ыңгайлуу жана көпчүлүк заманбап программалар, анын ичинде Excel окуй алат.

Болгондо да, мен сайттын авторлору менен байланыштым, алар сайт 7 жылдан бери бар экенин тастыкташты, андагы маалыматтар тынымсыз жаңыланып турат (алардын бул үчүн githubда филиалы бар) жана алар аны жапкысы келбейт. Сиз экөөбүздүн Excelдеги долбоорлорубуз жана эсептөөлөрүбүз үчүн андан маалыматтарды жүктөгөнүбүзгө мен таптакыр каршы эмесмин. Бекер. Ушундай адамдар дагы бар экенин билүү жакшы! Урмат!

Бул маалыматты Power Query кошумчасы аркылуу Excelге жүктөө керек (Excel 2010-2013 версиялары үчүн аны Microsoft веб-сайтынан акысыз жүктөп алууга болот, ал эми Excel 2016 жана андан кийинки версияларында демейки боюнча орнотулган. ).

Иш-аракеттердин логикасы төмөнкүдөй болот:

1. Биз каалаган бир жыл үчүн сайттан маалыматтарды жүктөп алуу өтүнүчү менен кайрылабыз
2. Сурамыбызды функцияга айландыруу
3. Биз бул функцияны 2013-жылдан баштап жана ушул жылга чейинки бардык жеткиликтүү жылдардын тизмесине колдонобуз – жана биз автоматтык түрдө жаңыртуу менен “түбөлүктүү” өндүрүш календарын алабыз. Voila!
   

1-кадам. Бир жылга календарды импорттоо

Биринчиден, өндүрүш календарын каалаган бир жылга жүктөңүз, мисалы, 2020-жылга. Бул үчүн Excelде өтмөккө өтүңүз. маалыматтар (же күч сурооэгер сиз аны өзүнчө кошумча катары орноткон болсоңуз) жана тандаңыз Интернеттен (Интернеттен). Ачылган терезеде сайттан көчүрүлгөн тиешелүү жылга шилтемени чаптаңыз:

Кийин басуу боюнча OK алдын ала көрүү терезеси пайда болот, анда сиз баскычты басышыңыз керек Convert Data (Маалыматтарды которуу) or Дайындарды өзгөртүү үчүн (Маалыматтарды түзөтүү) жана биз Power Query суроо редакторунун терезесине келебиз, анда биз маалыматтар менен иштөөнү улантабыз:

Ошол замат сиз оң панелде коопсуз жок кыла аласыз Параметрлерди суроо (Суроо жөндөөлөрү) кадам өзгөртүлгөн түрү (Өзгөртүлгөн түрү) Бизге анын кереги жок.

Майрамдар тилкесиндеги таблицада иштебеген күндөрдүн коддору жана сыпаттамалары камтылган – анын мазмунун жашыл сөздү чыкылдатуу менен эки жолу “түшүп” көрө аласыз. стол:

Артка кайтуу үчүн, оң панелде кайра пайда болгон бардык кадамдарды жок кылышыңыз керек булак (Source).

Ушундай эле жол менен кирүүгө мүмкүн болгон экинчи таблицада бизге керектүү нерселердин бардыгы камтылган – бардык иштебеген күндөрдүн датасы:

Бул плитаны иштетүү үчүн, тактап айтканда:

1. Майрам күндөрүн (б.а.) экинчи тилке боюнча чыпкалаңыз Атрибут: т

2. Биринчи тилкеден башка бардык мамычаларды жок кылуу - биринчи тилкенин аталышын оң баскыч менен чыкылдатып, буйрукту тандоо Башка тилкелерди жок кылыңыз (Башка тилкелерди алып салуу):

3. Команда менен биринчи тилкени ай жана күн үчүн өзүнчө чекитке бөлүңүз Бөлүнүүчү тилке – Бөлүүчү боюнча табулатура кайра (Трансформация — Бөлүнүүчү тилке — Бөлүүчү боюнча):

4. Акыр-аягы, кадимки күндөр менен эсептелген тилкени түзүңүз. Бул үчүн, өтмөктө Колонка кошуу баскычын чыкылдатыңыз Ыңгайлаштырылган тилке (Мамыча кошуу — Ыңгайлаштырылган тилке) жана пайда болгон терезеге төмөнкү формуланы киргизиңиз:

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

Бул жерде #date операторунун үч аргументи бар: тиешелүүлүгүнө жараша жыл, ай жана күн. Баскандан кийин OK биз кадимки дем алыш күндөрү менен керектүү тилкени алабыз жана 2-кадамдагыдай калган мамычаларды жок кылабыз

Кадам 2. Сурамды функцияга айландыруу

Биздин кийинки милдет - 2020-жылга түзүлгөн суроону каалаган жыл үчүн универсалдуу функцияга айландыруу (жыл саны анын аргументи болот). Бул үчүн, биз төмөнкүлөрдү жасайбыз:

1. Панелди кеңейтүү (эгерде буга чейин кеңейтилбесе). суроолор (Суроо) Power Query терезесинде сол жакта:

2. Сурамды функцияга айландыргандан кийин, өтүнүчтү түзгөн кадамдарды көрүү жана аларды оңой оңдоо мүмкүнчүлүгү, тилекке каршы, жоголот. Ошондуктан, биздин өтүнүчүбүздүн көчүрмөсүн жасап, аны менен ойноп, оригиналын резервде калтыруу акылга сыярлык. Бул үчүн, биздин календарлык суроо-талаптын сол панелинде оң баскычты чыкылдатып, Көчүрмө буйругун тандаңыз.

Календардык (2) көчүрмөсүн кайра оң баскыч менен чыкылдатуу буйрукту тандайт Атын өзгөртүү (Атын өзгөртүү) жана жаңы ысымды киргизиңиз - мисалы, fxYear:

3. Биз буйруктун жардамы менен суроонун баштапкы кодун ички Power Query тилинде ачабыз (ал кыскача “M” деп аталат) Өркүндөтүлгөн редактор табулатура кароо(Көрүү — Өркүндөтүлгөн редактор) жана биздин өтүнүчүбүздү каалаган жыл үчүн функцияга айландыруу үчүн ал жерде кичинекей өзгөртүүлөрдү киргизиңиз.

Ал эле:

кийин:

Эгерде сизди чоо-жайы кызыктырса, анда бул жерде:

• (сан катары жыл)=>  – биздин функцияда бир сандык аргумент – өзгөрмө болот деп жарыялайбыз жыл
• Өзгөрмө чаптоо жыл кадам менен веб шилтемеси булак. Power Query сандарды жана текстти чаптоого жол бербегендиктен, функцияны колдонуп, жыл санын тез арада текстке айландырабыз. Number.ToText
• Биз акыркы кадамда жыл өзгөрмөсүн 2020-жылга алмаштырабыз #" Ыңгайлаштырылган объект кошулду«, анда биз фрагменттерден датаны түздүк.
  

Кийин басуу боюнча бүтүрүү биздин өтүнүчүбүз функцияга айланат:

3-кадам. Бардык жылдар үчүн календарларды импорттоо

Калган акыркы нерсе - акыркы негизги суроо, ал бардык мүмкүн болгон жылдар үчүн маалыматтарды жүктөйт жана бардык алынган майрам күндөрүн бир таблицага кошот. Бул үчүн:

1. Чычкандын оң баскычы менен боз бош мейкиндикте сол суроо панелин чыкылдатып, ырааттуу тандайбыз Жаңы суроо – Башка булактар ​​– Бош суроо (Жаңы суроо — Башка булактардан — Бош суроо):

2. Биз календарларды сурай турган бардык жылдардын тизмесин түзүшүбүз керек, б.а. 2013, 2014 … 2020. Бул үчүн, пайда болгон бош суроонун формула тилкесинде буйрукту киргизиңиз:

түзүлүшү:

={NumberA..NumberB}

… Power Query ичинде Адан Бге чейинки бүтүн сандардын тизмесин түзөт. Мисалы, туюнтма

={1..5}

... 1,2,3,4,5 тизмесин чыгарат.

Ооба, 2020-жылга катуу байланып калбоо үчүн, биз функцияны колдонобуз DateTime.LocalNow() – Excel функциясынын аналогу БҮГҮН (БҮГҮН) Power Queryде – жана андан үзүндү, өз кезегинде, функция боюнча учурдагы жыл Date.Year.

3. Жыйынтыгында жылдардын жыйындысы, ал абдан адекваттуу көрүнгөнү менен, Power Query үчүн таблица эмес, өзгөчө объект болуп саналат – тизме (Тизме). Бирок аны таблицага айландыруу көйгөй эмес: жөн гана баскычты чыкылдатыңыз Столго (Столго) жогорку сол бурчта:

4. Finish линиясы! Мурда түзүлгөн функцияны колдонуу fxYear жылдардын жыйынтыгы боюнча тизмеси. Бул үчүн, өтмөктө Колонка кошуу кнопкасын басып, Ыңгайлаштырылган функцияны чакырыңыз (Мамыча кошуу — Ыңгайлаштырылган функцияны чакыруу) жана анын бир гана аргументин койду - мамычаны Column1 жылдар бою:

Кийин басуу боюнча OK биздин функция fxYear импорт ар бир жыл үчүн өз кезегинде иштейт жана биз ар бир уячада иштебеген күндөрдүн даталары менен таблицаны камтыган тилкени алабыз (эгер сиз жанындагы уячанын фонунда чыкылдатсаңыз, таблицанын мазмуну ачык көрүнүп турат. сөз стол):

Мамычанын башындагы кош жебелери бар сөлөкөттү чыкылдатуу менен уя салынган таблицалардын мазмунун кеңейтүү керек. А-Я Даталар (белги Префикс катары түпнуска тилке атын колдонуңуз аны алып салууга болот):

… жана басылгандан кийин OK биз каалаган нерсени алабыз - 2013-жылдан азыркы жылга чейинки бардык майрамдардын тизмеси:

Биринчи, ансыз деле керексиз тилке жок кылынышы мүмкүн, ал эми экинчиси үчүн маалымат түрүн орнотуңуз дата (Дата) тилке башындагы ачылуучу тизмеде:

Сурамдын өзүн караганда маанисин өзгөртсө болот Сураныч 1 анан буйрукту колдонуп, натыйжаларды баракка динамикалык "акылдуу" таблица түрүндө жүктөңүз жабуу жана жүктөп алуу табулатура Home (Башкы бет — Жабуу жана жүктөө):

Келечекте сиз таблицаны оң баскыч менен чыкылдатуу же буйрук аркылуу оң панелдеги суроо менен түзүлгөн календарды жаңырта аласыз Жаңыртуу & Сактоо. Же баскычын колдонуңуз Баарын жаңыртуу табулатура маалыматтар (Күн — баарын жаңылоо) же баскычтоптун жарлыгы Ctrl+Alt+F5.

Баары болду.

Эми сиз эс алуунун тизмесин издөө жана жаңыртуу үчүн убакытты жана ой жүгүртүүнү текке кетирбешиңиз керек – эми сизде “түбөлүктүү” өндүрүш календары бар. Кандай болгон күндө да, http://xmlcalendar.ru/ сайтынын авторлору өздөрүнүн тукумун колдой турган болсо, бул, мен үмүттөнөм, бул абдан, абдан узак убакыт бою (алар үчүн дагы бир жолу рахмат!).

• Power Query аркылуу интернеттен биткоиндин курсун импорттоо
• WORKDAY функциясын колдонуу менен кийинки иш күнүн табуу
  
• Дата интервалдарынын кесилишин табуу