Գործարանային օրացույց Excel-ում

Արտադրության օրացույցը, այսինքն՝ ամսաթվերի ցանկը, որտեղ բոլոր պաշտոնական աշխատանքային օրերն ու արձակուրդները նշված են համապատասխանաբար, Microsoft Excel-ի ցանկացած օգտագործողի համար բացարձակապես անհրաժեշտ բան: Գործնականում դուք չեք կարող անել առանց դրա.

• հաշվապահական հաշվարկներում (աշխատավարձ, ստաժ, արձակուրդներ…)
• լոգիստիկայի մեջ՝ առաքման ժամանակները ճիշտ որոշելու համար՝ հաշվի առնելով հանգստյան օրերը և արձակուրդները (հիշում եք դասական «եկեք արձակուրդից հետո»)
  
• ծրագրի կառավարման մեջ՝ ժամկետների ճիշտ գնահատման համար՝ կրկին հաշվի առնելով աշխատանքային-ոչ աշխատանքային օրերը.
• գործառույթների ցանկացած օգտագործում, ինչպիսիք են ԱՇԽԱՏԱՆՔ (ԱՇԽԱՏԱՆՔԱՅԻՆ ՕՐ) or ՄԱՔՈՒՐ ԲԱՆՎՈՐՆԵՐ (ՑԱՆՑՕՐԵՐ), քանի որ որպես փաստարկ պահանջում են տոների ցանկ
• Power Pivot-ում և Power BI-ում Time Intelligence գործառույթներն օգտագործելիս (օրինակ՝ TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR և այլն):
  
• … և այլն և այլն – բազմաթիվ օրինակներ:
  

Ավելի հեշտ է նրանց համար, ովքեր աշխատում են կորպորատիվ ERP համակարգերում, ինչպիսիք են 1C կամ SAP, քանի որ դրանց մեջ ներկառուցված է արտադրության օրացույցը: Բայց ինչ վերաբերում է Excel օգտվողներին:

Դուք, իհարկե, կարող եք ձեռքով պահել նման օրացույց: Բայց հետո դուք ստիպված կլինեք թարմացնել այն առնվազն տարին մեկ անգամ (կամ նույնիսկ ավելի հաճախ, ինչպես «զվարճալի» 2020 թվականին)՝ զգուշորեն մուտքագրելով մեր կառավարության հորինած բոլոր հանգստյան օրերը, տրանսֆերներն ու ոչ աշխատանքային օրերը։ Իսկ հետո կրկնեք այս պրոցեդուրան յուրաքանչյուր հաջորդ տարի։ Ձանձրույթ.

Ի՞նչ կասեք մի փոքր խենթանալու և Excel-ում «հավերժական» գործարանային օրացույց ստեղծելու մասին: Մեկը, որն ինքն իրեն թարմացնում է, վերցնում է տվյալներ ինտերնետից և միշտ ստեղծում է ոչ աշխատանքային օրերի թարմացված ցանկ՝ հետագա օգտագործման համար որևէ հաշվարկում: Գայթակղիչ.

Դա անել, ըստ էության, ամենևին էլ դժվար չէ։

Տվյալների աղբյուր

Հիմնական հարցն այն է, թե որտեղի՞ց ստանալ տվյալները: Հարմար աղբյուր փնտրելու համար ես անցա մի քանի տարբերակ.

• Հրամանագրերի բնօրինակները հրապարակվում են կառավարության կայքում PDF ձևաչափով (այստեղ՝ դրանցից մեկը, օրինակ) և անմիջապես անհետանում են. դրանցից օգտակար տեղեկություններ չեն կարող հանվել:
• Առաջին հայացքից գայթակղիչ տարբերակ էր թվում «Ֆեդերացիայի բաց տվյալների պորտալը», որտեղ կա համապատասխան տվյալների հավաքածու, սակայն ավելի ուշադիր ուսումնասիրելուց հետո ամեն ինչ տխուր էր։ Կայքը սարսափելի անհարմար է Excel ներմուծման համար, տեխնիկական աջակցությունը չի արձագանքում (ինքնամեկուսացված?), և տվյալներն ինքնին այնտեղ վաղուց հնացած են. 2020 թվականի արտադրության օրացույցը վերջին անգամ թարմացվել է 2019 թվականի նոյեմբերին (խայտառակություն) և , իհարկե, չի պարունակում մեր «կորոնավիրուսը» և, օրինակ, 2020 թվականի «քվեարկության» շաբաթավերջը։

Պաշտոնական աղբյուրներից հիասթափվելով՝ սկսեցի փորել ոչ պաշտոնականները։ Ինտերնետում դրանք շատ են, բայց դրանցից շատերը, կրկին, բացարձակապես ոչ պիտանի են Excel ներմուծելու համար և թողարկում են արտադրական օրացույց՝ գեղեցիկ նկարների տեսքով։ Բայց պատից կախելը մեզ համար չէ, չէ՞:

Եվ որոնումների ընթացքում պատահաբար հայտնաբերվեց մի հրաշալի բան՝ կայքը http://xmlcalendar.ru/

Առանց ավելորդ «կեղծիքների», պարզ, թեթև և արագ կայք, որը սրված է մեկ առաջադրանքի համար՝ բոլորին XML ձևաչափով ցանկալի տարվա արտադրական օրացույց տալ: Գերազանց!

Եթե ​​հանկարծ դուք տեղյակ չեք, ապա XML-ը տեքստային ձևաչափ է, որի բովանդակությունը նշված է հատուկ . Թեթև, հարմար և ընթեռնելի ժամանակակից ծրագրերի մեծ մասի համար, ներառյալ Excel-ը:

Ամեն դեպքում կապվեցի կայքի հեղինակների հետ ու հաստատեցին, որ կայքը գոյություն ունի արդեն 7 տարի, դրա տվյալները անընդհատ թարմացվում են (նույնիսկ github-ում դրա համար մասնաճյուղ ունեն) ու չեն պատրաստվում փակել։ Եվ ես ամենևին դեմ չեմ, որ ես և դու դրանից տվյալներ ենք բեռնում Excel-ում մեր որևէ նախագծի և հաշվարկի համար։ Անվճար է։ Հաճելի է իմանալ, որ դեռ կան այսպիսի մարդիկ։ Հարգանք.

Մնում է բեռնել այս տվյալները Excel-ում՝ օգտագործելով Power Query հավելումը (Excel 2010-2013 տարբերակների համար այն կարելի է անվճար ներբեռնել Microsoft-ի կայքից, իսկ Excel 2016 և ավելի նոր տարբերակներում այն ​​արդեն ներկառուցված է լռելյայնորեն։ ).

Գործողությունների տրամաբանությունը կլինի հետևյալը.

1. Մենք խնդրում ենք ներբեռնել տվյալ կայքից ցանկացած մեկ տարվա համար
2. Մեր հարցումը վերածելով ֆունկցիայի
3. Մենք այս գործառույթը կիրառում ենք բոլոր հասանելի տարիների ցանկում՝ սկսած 2013 թվականից մինչև ընթացիկ տարի, և ստանում ենք «մշտական» արտադրության օրացույց՝ ավտոմատ թարմացմամբ: Voila!
   

Քայլ 1. Ներմուծեք օրացույց մեկ տարվա համար

Նախ, բեռնեք արտադրության օրացույցը ցանկացած մեկ տարվա համար, օրինակ՝ 2020թ.-ի համար: Դա անելու համար Excel-ում անցեք ներդիր Ամսաթիվ (Կամ Power հարցումեթե այն տեղադրել եք որպես առանձին հավելում) և ընտրեք Համացանցից (Վեբկայքից). Բացվող պատուհանում տեղադրեք կայքից պատճենված համապատասխան տարվա հղումը՝

Սեղմելուց հետո OK հայտնվում է նախադիտման պատուհան, որում պետք է սեղմել կոճակը Փոխարկել տվյալները (Տվյալների փոխակերպում) or Տվյալները փոխելու համար (Խմբագրել տվյալները) և մենք կհասնենք Power Query հարցման խմբագրիչի պատուհանին, որտեղ մենք կշարունակենք աշխատել տվյալների հետ.

Անմիջապես կարող եք ապահով կերպով ջնջել աջ վահանակում Հարցման պարամետրեր (Հարցման կարգավորումներ) քայլ փոփոխված տեսակը (Փոխված տեսակը) Նա մեզ պետք չէ։

Արձակուրդների սյունակի աղյուսակը պարունակում է ոչ աշխատանքային օրերի ծածկագրեր և նկարագրություններ. կարող եք տեսնել դրա բովանդակությունը՝ երկու անգամ «ընկնելով»՝ սեղմելով կանաչ բառի վրա։ Սեղան:

Հետ վերադառնալու համար դուք պետք է աջ վահանակից ջնջեք բոլոր այն քայլերը, որոնց հետ հայտնվել են Աղբյուր (Աղբյուր).

Երկրորդ աղյուսակը, որին կարելի է մուտք գործել նույն ձևով, պարունակում է հենց այն, ինչ մեզ անհրաժեշտ է՝ բոլոր ոչ աշխատանքային օրերի ամսաթվերը.

Մնում է մշակել այս ափսեը, մասնավորապես.

1. Զտեք միայն արձակուրդի ամսաթվերը (այսինքն՝ օրերը) ըստ երկրորդ սյունակի Հատկանիշ: t

2. Ջնջել բոլոր սյունակները, բացառությամբ առաջինի, աջ սեղմելով առաջին սյունակի վերնագրի վրա և ընտրելով հրամանը Ջնջել այլ սյունակներ (Հեռացնել այլ սյունակներ):

3. Հրամանով առաջին սյունակը կետ առ կետ բաժանեք ամսվա և օրվա համար Split Column – Ըստ սահմանազատողի ականջակալ Փոխակերպում (Փոխակերպում — Բաժանված սյունակ — ըստ սահմանազատողի):

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-ում ստեղծում է 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+ալտ+F5.

Դա բոլորը:

Այժմ դուք այլևս կարիք չեք ունենա ժամանակ և մտքի վառելիք կորցնել՝ փնտրելու և թարմացնելու տոների ցանկը. այժմ դուք ունեք «հավերժական» արտադրության օրացույց: Ամեն դեպքում, քանի դեռ http://xmlcalendar.ru/ կայքի հեղինակները աջակցում են իրենց սերունդներին, որոնք, հուսով եմ, կլինեն շատ ու շատ երկար (նորից շնորհակալություն նրանց):

• Ներմուծեք բիթքոինի տոկոսադրույքը ինտերնետից գերազանցելու համար Power Query-ի միջոցով
• Գտեք հաջորդ աշխատանքային օրը՝ օգտագործելով WORKDAY ֆունկցիան
  
• Գտեք ամսաթվերի միջակայքերի խաչմերուկը