Агуулга
Үйлдвэрлэлийн хуанли, өөрөөр хэлбэл албан ёсны бүх ажлын өдрүүд, амралтын өдрүүдийг тэмдэглэсэн огнооны жагсаалт нь Microsoft Excel-ийн аль ч хэрэглэгчдэд зайлшгүй шаардлагатай зүйл юм. Практикт та үүнгүйгээр хийх боломжгүй:
- нягтлан бодох бүртгэлийн тооцоонд (цалин, ажилласан хугацаа, амралт ...)
- логистикийн хувьд - амралтын өдрүүд, амралтын өдрүүдийг харгалзан хүргэх хугацааг зөв тодорхойлоход зориулагдсан ("баярын дараа ирэх үү?" Сонгодог үгийг санаарай).
- төслийн менежментэд - ажлын бус өдрүүдийг харгалзан хугацааг зөв тооцоолохын тулд
- гэх мэт функцүүдийн аливаа хэрэглээ АЖЛЫН ӨДӨР (АЖЛЫН ӨДӨР) or ЦЭВЭР АЖИЛЧИД (СҮЛЖЭЭНИЙ ӨДӨР), учир нь тэд амралтын өдрүүдийн жагсаалтыг аргумент болгон шаарддаг
- Power Pivot болон Power BI дээр Time Intelligence функцуудыг (TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR гэх мэт) ашиглах үед
- … гэх мэт – олон жишээ.
1С эсвэл SAP гэх мэт корпорацийн ERP системд ажилладаг хүмүүст үйлдвэрлэлийн хуанли суулгагдсан байдаг тул энэ нь илүү хялбар байдаг. Гэхдээ Excel-ийн хэрэглэгчид яах вэ?
Мэдээжийн хэрэг та ийм календарийг гараар хөтөлж болно. Гэхдээ дараа нь та үүнийг жилд дор хаяж нэг удаа (эсвэл "баяр хөөртэй" 2020 оных шиг) шинэчлэх шаардлагатай болно, манай засгийн газрын зохион бүтээсэн бүх амралтын өдрүүд, шилжүүлэг, ажлын бус өдрүүдийг анхааралтай оруулах хэрэгтэй. Дараа нь энэ процедурыг дараа жил бүр давтана. Уйтгартай.
Жаахан галзуурч, Excel дээр "мөнхийн" үйлдвэрийн календарь хийвэл ямар вэ? Өөрийгөө шинэчилж, интернетээс мэдээлэл авч, ажлын бус өдрүүдийн шинэчилсэн жагсаалтыг байнга гаргаж, дараа нь аливаа тооцоололд ашигладаг уу? Сонирхолтой юу?
Үүнийг хийх нь үнэндээ тийм ч хэцүү биш юм.
Өгөгдлийн эх сурвалж
Хамгийн гол асуулт бол өгөгдлийг хаанаас авах вэ? Тохиромжтой эх сурвалжийг хайж олохын тулд би хэд хэдэн сонголтыг хийсэн:
- Анхны тогтоолуудыг засгийн газрын вэбсайтад PDF форматаар нийтэлсэн (жишээ нь, тэдгээрийн нэг нь энд) бөгөөд тэр даруй алга болдог - хэрэгтэй мэдээллийг тэднээс гаргаж авах боломжгүй.
- Анхны харцаар харгалзах өгөгдлийн багц байдаг "Холбооны Нээлттэй Мэдээллийн Портал" нь сонирхол татахуйц сонголт мэт санагдаж байсан ч сайтар судалж үзэхэд бүх зүйл гунигтай болов. Энэ сайт нь Excel-д импортлоход маш тохиромжгүй, техникийн дэмжлэг үзүүлэхгүй (өөрөө тусгаарлагдсан уу?), мөн өгөгдөл нь удаан хугацааны туршид хуучирсан - 2020 оны үйлдвэрлэлийн хуанли хамгийн сүүлд 2019 оны 2020-р сард шинэчлэгдсэн (гутамшиг!) ба Мэдээжийн хэрэг, жишээ нь XNUMX оны "коронавирус" болон "санал өгөх" амралтын өдрүүдийг агуулаагүй болно.
Албан ёсны эх сурвалжид урам хугарсан би албан бус мэдээллийг ухаж эхлэв. Интернет дээр тэдгээрийн олонх нь байдаг, гэхдээ ихэнх нь 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. Эхний баганыг сар, өдрөөр тус тусад нь цэгээр хуваана Баганыг хуваах - Хязгаарлагчаар таб Хувиргах (Өөрчлөх - Багана хуваах - Хязгаарлагчаар):
4. Эцэст нь ердийн огноотой тооцоолсон багана үүсгэ. Үүнийг хийхийн тулд таб дээр Багана нэмж байна товчин дээр дарна уу Тусгай багана (Багана нэмэх - Тусгай багана) гарч ирэх цонхонд дараах томъёог оруулна уу.
=#огноо(2020, [#»Атрибут:d.1″], [#»Атрибут:d.2″])
Энд #date оператор нь жил, сар, өдөр гэсэн гурван аргументтай. Дарсны дараа OK Бид шаардлагатай баганыг ердийн амралтын өдрүүдээр авч, 2-р алхамын адил үлдсэн багануудыг устгана
Алхам 2. Хүсэлтийг функц болгон хувиргах
Бидний дараагийн ажил бол 2020 онд үүсгэсэн асуулгыг аль ч жилийн бүх нийтийн функц болгон хувиргах явдал юм (жилийн тоо нь түүний аргумент байх болно). Үүнийг хийхийн тулд бид дараахь зүйлийг хийнэ.
1. Самбарыг өргөжүүлэх (хэрэв өмнө нь өргөжүүлээгүй бол). Лавлагаа (Асуулт) Power Query цонхны зүүн талд:
2. Хүсэлтийг функц болгон хөрвүүлсний дараа хүсэлтийг бүрдүүлж буй алхмуудыг харж, тэдгээрийг хялбархан засах боломж харамсалтай нь алга болдог. Тиймээс бидний хүсэлтийн хуулбарыг хийж, түүнтэй аль хэдийн зугаацаж, эх хувийг нөөцөд үлдээх нь зүйтэй болов уу. Үүнийг хийхийн тулд манай хуанлийн хүсэлтийн зүүн талд хулганы баруун товчийг дараад Давхардсан командыг сонгоно уу.
Календарийн (2) хуулбар дээр хулганы баруун товчийг дахин дарснаар командыг сонгоно Нэрийг нь өөрчил (Нэр өөрчлөх) болон шинэ нэр оруулна уу - жишээ нь, fxYear:
3. Бид тушаалыг ашиглан асуулгын эх кодыг дотоод Power Query хэлээр нээнэ (үүнийг товчоор "M" гэж нэрлэдэг) Нарийвчилсан редактор таб тойм(Харах — Нарийвчилсан редактор) мөн бидний хүсэлтийг аль ч жилийн функц болгон хувиргахын тулд тэнд жижиг өөрчлөлтүүдийг хий.
Байсан:
Дараа нь:
Хэрэв та дэлгэрэнгүй мэдээллийг сонирхож байвал эндээс:
- (тоогоор жил)=> – Манай функц нэг тоон аргумент буюу хувьсагчтай байх болно гэдгийг бид тунхаглаж байна Жил
- Хувьсагчийг буулгаж байна Жил вэб холбоос руу алхам алхмаар Эх сурвалж. Power Query нь тоо болон текстийг наах боломжийг олгодоггүй тул бид функцийг ашиглан жилийн тоог шууд текст болгон хөрвүүлдэг. Тоо.Текст
- Бид эцсийн шатанд 2020 оны хувьсагчийг орлуулна #"Захиалгат объект нэмсэн«, бид хэлтэрхийнээс огноог үүсгэсэн.
Үргэлжлүүлсний дараа Finish бидний хүсэлт функц болж хувирна:
Алхам 3. Бүх жилийн хуанли импортлох
Хамгийн сүүлчийн зүйл бол боломжтой бүх жилийн өгөгдлийг байршуулж, хүлээн авсан бүх амралтын өдрүүдийг нэг хүснэгтэд нэмэх сүүлчийн үндсэн асуулга хийх явдал юм. Үүний тулд:
1. Бид хулганы баруун товчийг саарал хоосон зайд зүүн асуулгын самбар дээр дараад дарааллаар сонгоно. Шинэ хүсэлт – Бусад эх сурвалж – Хоосон хүсэлт (Шинэ асуулга - Бусад эх сурвалжаас - Хоосон асуулга):
2. Бид 2013, 2014 … 2020 онуудад хуанли хүсэх бүх жилийн жагсаалтыг гаргах хэрэгтэй. Үүнийг хийхийн тулд гарч ирэх хоосон асуулгын томьёоны мөрөнд дараах тушаалыг оруулна уу:
Бүтэц:
={ДугаарA..ДугаарB}
… Power Query нь А-аас В хүртэлх бүхэл тоонуудын жагсаалтыг үүсгэдэг. Жишээ нь, илэрхийлэл
={1..5}
... 1,2,3,4,5 гэсэн жагсаалтыг гаргана.
За, 2020 онтой хатуу уялдуулахгүйн тулд бид функцийг ашигладаг DateTime.LocalNow() - Excel функцийн аналог TODAY (ӨНӨӨДӨР) Power Query-д – мөн үүнээс эргээд тухайн жилийг функцээр нь гаргаж авна Огноо. Жил.
3. Үр дүнд хүрсэн жилүүдийн багц нь нэлээд хангалттай мэт харагдаж байгаа ч Power Query-д зориулсан хүснэгт биш, харин тусгай объект юм. жагсаалт (Жагсаалт). Гэхдээ үүнийг хүснэгт болгон хөрвүүлэх нь асуудал биш юм: зүгээр л товчлуур дээр дарна уу Ширээ рүү (Хүснэгт рүү) зүүн дээд буланд:
4. Барианы шугам! Бидний өмнө нь үүсгэсэн функцийг ашиглаж байна fxYear жилийн жагсаалтад . Үүнийг хийхийн тулд таб дээр Багана нэмж байна товчийг дарна уу Захиалгат функцийг дуудах (Багана нэмэх - Тусгай функцийг дуудах) ба түүний цорын ганц аргумент болох баганыг тохируулна уу Column1 жилийн сүүлчээр:
Үргэлжлүүлсний дараа OK бидний функц fxYear импорт нь жил бүр ээлжлэн ажиллах бөгөөд бид нүд бүр нь ажлын бус өдрүүдийн огноо бүхий хүснэгтийг агуулсан багана авах болно (хэрэв та хажуугийн нүдний арын дэвсгэр дээр дарвал хүснэгтийн агуулга тодорхой харагдах болно. үг Хүснэгт):
Баганын толгой дээрх давхар сумтай дүрс дээр дарж, үүрлэсэн хүснэгтүүдийн агуулгыг өргөжүүлэхэд үлддэг. огноо (шалз Эх баганын нэрийг угтвар болгон ашигла устгаж болно):
… мөн дарсны дараа OK Бид хүссэн зүйлээ олж авлаа - 2013 оноос энэ жил хүртэлх бүх баяруудын жагсаалт:
Эхнийх нь аль хэдийн шаардлагагүй баганыг устгаж болох бөгөөд хоёр дахь нь өгөгдлийн төрлийг тохируулна он сар өдөр (Огноо) Баганын гарчиг дахь унадаг жагсаалтад:
Асуулга нь өөрөө илүү утга учиртай нэрээр нэрлэгдэж болно Хүсэлт 1 дараа нь тушаалыг ашиглан үр дүнг динамик "ухаалаг" хүснэгт хэлбэрээр хуудсанд байршуулна уу хааж татаж аваарай таб Нүүр хуудас (Нүүр хуудас — Хаах, ачаалах):
Хүснэгт дээр хулганы баруун товчийг дарж, эсвэл командаар дамжуулан баруун самбарт асуулга хийснээр та үүсгэсэн календарийг ирээдүйд шинэчлэх боломжтой. Шинэчлэх & Хадгалах. Эсвэл товчлуурыг ашиглана уу Бүгдийг нь сэргээ таб Өгөгдөл (Огноо - Бүгдийг шинэчлэх) эсвэл гарын товчлол Ctrl+Alt+F5.
Тэгээд л болоо.
Одоо та амралтын өдрүүдийн жагсаалтыг хайж, шинэчлэхийн тулд цаг хугацаа, дэмий хоосон зүйл хийх шаардлагагүй боллоо - одоо та "мөнхийн" үйлдвэрлэлийн хуанлитай боллоо. Ямар ч байсан, http://xmlcalendar.ru/ сайтын зохиогчид үр удмаа дэмжиж байгаа л бол энэ нь маш удаан хугацаанд үргэлжлэх болно гэж найдаж байна (тэдэнд дахин баярлалаа!).
- Power Query-ээр дамжуулан интернетээс биткойны ханшийг импортлох
- WORKDAY функцийг ашиглан дараагийн ажлын өдрийг олох
- Огнооны интервалуудын огтлолцлыг олох