Mundarija
Ishlab chiqarish taqvimi, ya'ni barcha rasmiy ish kunlari va bayramlar tegishli ravishda belgilangan sanalar ro'yxati - bu har qanday Microsoft Excel foydalanuvchisi uchun juda zarur narsa. Amalda, siz usiz qilolmaysiz:
- buxgalteriya hisoblarida (ish haqi, ish staji, ta'tillar ...)
- logistikada - dam olish kunlari va bayramlarni hisobga olgan holda etkazib berish vaqtlarini to'g'ri aniqlash uchun ("bayramdan keyin kelasizmi?" Klassikni eslaysizmi)
- loyihani boshqarishda - yana ishlamaydigan kunlarni hisobga olgan holda muddatlarni to'g'ri baholash uchun
- kabi funksiyalardan har qanday foydalanish ISH KUNI (ish kuni) or SOZ MEHNATORLAR (TARMOQ KUNLARI), chunki ular argument sifatida bayramlar ro'yxatini talab qiladi
- Power Pivot va Power BI’da Time Intelligence funksiyalaridan (masalan, TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR va boshqalar) foydalanganda
- … va hokazo. – ko‘plab misollar.
1C yoki SAP kabi korporativ ERP tizimlarida ishlaydiganlar uchun osonroqdir, chunki ishlab chiqarish taqvimi ularga o'rnatilgan. Ammo Excel foydalanuvchilari haqida nima deyish mumkin?
Siz, albatta, bunday taqvimni qo'lda saqlashingiz mumkin. Ammo keyin siz uni yiliga kamida bir marta yangilashingiz kerak bo'ladi (yoki "quvnoq" 2020 yildagi kabi), hukumatimiz tomonidan o'ylab topilgan barcha dam olish kunlari, pul o'tkazmalari va ishlamaydigan kunlarni diqqat bilan kiriting. Va keyin har yili bu protsedurani takrorlang. Zerikish.
Bir oz aqldan ozish va Excelda "abadiy" zavod kalendarini yaratish haqida nima deyish mumkin? O'zini yangilaydigan, Internetdan ma'lumotlarni oladigan va har qanday hisob-kitoblarda keyingi foydalanish uchun har doim ishlamaydigan kunlarning dolzarb ro'yxatini yaratadigan biri? Jozibalimi?
Buni amalga oshirish, aslida, unchalik qiyin emas.
Ma'lumotlar manbai
Asosiy savol - ma'lumotlarni qaerdan olish kerak? Tegishli manba izlab, men bir nechta variantni ko'rib chiqdim:
- Asl qarorlar hukumat veb-saytida PDF formatida e'lon qilinadi (masalan, ulardan biri) va darhol yo'qoladi - ulardan foydali ma'lumotlarni chiqarib bo'lmaydi.
- Bir qarashda jozibali variant "Federatsiyaning ochiq ma'lumotlar portali" bo'lib tuyuldi, bu erda tegishli ma'lumotlar to'plami mavjud, ammo diqqat bilan o'rganib chiqqach, hamma narsa achinarli bo'lib chiqdi. Sayt Excelga import qilish uchun juda noqulay, texnik yordam javob bermaydi (o'z-o'zidan izolyatsiya qilinganmi?) va ma'lumotlarning o'zi u erda uzoq vaqt eskirgan - 2020 yil uchun ishlab chiqarish taqvimi oxirgi marta 2019 yil noyabr oyida yangilangan (sharmanda!) va , albatta, bizning "koronavirusimiz" va 2020 yilgi "ovoz berish" dam olish kunlarini o'z ichiga olmaydi, masalan.
Rasmiy manbalardan hafsalasi pir bo‘lib, norasmiy manbalarni qazishni boshladim. Internetda ularning ko'plari bor, lekin ularning aksariyati yana Excelga import qilish uchun mutlaqo yaroqsiz va chiroyli rasmlar ko'rinishidagi ishlab chiqarish taqvimini beradi. Lekin uni devorga osib qo'yish biz uchun emas, to'g'rimi?
Va qidiruv jarayonida tasodifan ajoyib narsa topildi - http://xmlcalendar.ru/ sayti
Keraksiz "frills"siz, oddiy, engil va tezkor sayt, bitta vazifa uchun aniqlangan - har kimga XML formatida kerakli yil uchun ishlab chiqarish kalendarini berish. Ajoyib!
Agar siz to'satdan bilmagan bo'lsangiz, XML - bu maxsus bilan belgilangan kontentga ega matn formati
Har holda, men sayt mualliflari bilan bog'landim va ular sayt 7 yildan beri mavjud ekanligini tasdiqladilar, undagi ma'lumotlar doimiy ravishda yangilanadi (hatto buning uchun githubda filiallari ham bor) va ular uni yopmoqchi emaslar. Va men siz va men Excelda biron bir loyihamiz va hisob-kitoblarimiz uchun undan ma'lumotlarni yuklashimizga qarshi emasman. Bepul. Hali ham shunday odamlar borligini bilish juda yoqimli! Hurmat!
Ushbu ma'lumotlarni Power Query plaginidan foydalanib Excelga yuklash qoladi (Excel 2010-2013 versiyalari uchun uni Microsoft veb-saytidan bepul yuklab olish mumkin va Excel 2016 va undan keyingi versiyalarida u sukut bo'yicha allaqachon o'rnatilgan. ).
Harakatlar mantig'i quyidagicha bo'ladi:
- Biz har qanday bir yil uchun saytdan ma'lumotlarni yuklab olish uchun so'rov yuboramiz
- Bizning so'rovimizni funktsiyaga aylantirish
- Biz ushbu funktsiyani 2013 yildan boshlab joriy yilga qadar mavjud bo'lgan barcha yillar ro'yxatiga qo'llaymiz - va biz avtomatik yangilanadigan "abadiy" ishlab chiqarish kalendarini olamiz. Voila!
Qadam 1. Taqvimni bir yilga import qiling
Birinchidan, ishlab chiqarish taqvimini istalgan bir yil uchun, masalan, 2020 yil uchun yuklang. Buning uchun Excel-da yorlig'iga o'ting. ma'lumotlar (yoki Quvvat so'roviagar siz uni alohida qo'shimcha sifatida o'rnatgan bo'lsangiz) va tanlang Internetdan (Internetdan). Ochilgan oynada saytdan ko'chirilgan tegishli yilga havolani joylashtiring:
Tugmasini bosgandan so'ng OK oldindan ko'rish oynasi paydo bo'ladi, unda siz tugmani bosishingiz kerak Ma'lumotlarni aylantirish (Ma'lumotlarni o'zgartirish) or Ma'lumotlarni o'zgartirish uchun (Ma'lumotlarni tahrirlash) va biz Power Query so'rovlari muharriri oynasiga o'tamiz, u erda ma'lumotlar bilan ishlashni davom ettiramiz:
Darhol o'ng panelda xavfsiz o'chirishingiz mumkin So'rov parametrlari (So'rov sozlamalari) qadam o'zgartirilgan turi (O'zgartirilgan tur) Bizga u kerak emas.
Bayramlar ustunidagi jadvalda ishlamaydigan kunlarning kodlari va tavsiflari mavjud - uning mazmunini yashil so'zni bosish orqali ikki marta "tushib" ko'rishingiz mumkin. stol:
Orqaga qaytish uchun siz o'ng panelda qaytib kelgan barcha qadamlarni o'chirishingiz kerak bo'ladi manba (Manba).
Shunga o'xshash tarzda kirish mumkin bo'lgan ikkinchi jadvalda bizga kerak bo'lgan narsa - barcha ishlamaydigan kunlarning sanalari mavjud:
Ushbu plastinkani qayta ishlash uchun qoladi, xususan:
1. Ikkinchi ustun bo'yicha faqat bayram sanalarini (ya'ni birlarni) filtrlang Atribut: t
2. Birinchi ustundan tashqari barcha ustunlarni o'chiring - birinchi ustunning sarlavhasini o'ng tugmasini bosing va buyruqni tanlang Boshqa ustunlarni o'chiring (Boshqa ustunlarni olib tashlash):
3. Buyruq bilan birinchi ustunni oy va kun uchun alohida nuqtaga ajrating Ajratish ustuni - cheklovchi bo'yicha tab O'tkazish (O'zgartirish - Ustunni ajratish - ajratuvchi bo'yicha):
4. Va nihoyat, oddiy sanalar bilan hisoblangan ustunni yarating. Buning uchun yorliqda Ustun qo'shish tugmasini bosing Maxsus ustun (Ustun qo'shish - maxsus ustun) va paydo bo'lgan oynada quyidagi formulani kiriting:
=#sana(2020, [#»Atribut:d.1″], [#»Atribut:d.2″])
Bu yerda #date operatori uchta argumentga ega: mos ravishda yil, oy va kun. Bosgandan keyin OK biz odatdagi dam olish kunlari bilan kerakli ustunni olamiz va qolgan ustunlarni 2-bosqichda bo'lgani kabi o'chirib tashlaymiz
Qadam 2. So'rovni funksiyaga aylantirish
Bizning keyingi vazifamiz - 2020 yil uchun yaratilgan so'rovni istalgan yil uchun universal funktsiyaga aylantirish (yil raqami uning argumenti bo'ladi). Buning uchun biz quyidagilarni bajaramiz:
1. Panelni kengaytirish (agar hali kengaytirilmagan bo'lsa). Savollar (So'rovlar) Power Query oynasining chap tomonida:
2. So'rovni funktsiyaga aylantirgandan so'ng, so'rovni tashkil etuvchi bosqichlarni ko'rish va ularni osongina tahrirlash qobiliyati, afsuski, yo'qoladi. Shuning uchun, so'rovimizning nusxasini yaratish va u bilan allaqachon hazil qilish va asl nusxani zaxirada qoldirish mantiqan. Buni amalga oshirish uchun kalendar so'rovimizdagi chap panelda sichqonchaning o'ng tugmachasini bosing va "Dublicate" buyrug'ini tanlang.
Olingan kalendar (2) nusxasini yana sichqonchaning o'ng tugmasi bilan bosish buyruqni tanlaydi Nomni o'zgartiring (Nomini o'zgartirish) va yangi nom kiriting - masalan, shunday bo'lsin fxYear:
3. Biz buyruq yordamida so'rovning manba kodini ichki Power Query tilida ochamiz (u qisqacha "M" deb ataladi) Murakkab muharrir tab Review(Koʻrish — Kengaytirilgan muharrir) va bizning so'rovimizni istalgan yil uchun funktsiyaga aylantirish uchun u erda kichik o'zgarishlar kiriting.
Bu bo'lgandi:
Keyin:
Agar siz tafsilotlarga qiziqsangiz, bu erda:
- (raqam sifatida yil)=> - biz e'lon qilamizki, bizning funktsiyamiz bitta raqamli argumentga ega bo'ladi - o'zgaruvchi yil
- O'zgaruvchini joylashtirish yil bosqichma-bosqich veb-havolaga manba. Power Query raqamlar va matnlarni yopishtirishga imkon bermagani uchun biz funktsiyadan foydalanib, yil raqamini tezda matnga aylantiramiz. Raqam.Text
- Biz oxirgi bosqichda yil o'zgaruvchisini 2020 yilga almashtiramiz #"Maxsus ob'ekt qo'shildi«, bu erda biz parchalardan sanani hosil qildik.
Tugmasini bosgandan so'ng tugatmoq bizning so'rovimiz funktsiyaga aylanadi:
Qadam 3. Barcha yillar uchun kalendarlarni import qiling
Qolgan oxirgi narsa - barcha mavjud yillar uchun ma'lumotlarni yuklaydigan va barcha qabul qilingan bayram sanalarini bitta jadvalga qo'shadigan oxirgi asosiy so'rovni qilish. Buning uchun:
1. Biz sichqonchaning o'ng tugmasi bilan kulrang bo'sh joyda chap so'rovlar panelini bosamiz va ketma-ket tanlaymiz. Yangi so'rov - Boshqa manbalar - Bo'sh so'rov (Yangi so'rov - Boshqa manbalardan - Bo'sh so'rov):
2. Biz kalendarlarni talab qiladigan barcha yillar roʻyxatini yaratishimiz kerak, yaʼni 2013, 2014 … 2020. Buning uchun paydo boʻlgan boʻsh soʻrovning formulalar qatoriga quyidagi buyruqni kiriting:
Tuzilishi:
={RaqamA..RaqamB}
… Power Query dasturida A dan B gacha butun sonlar roʻyxati hosil boʻladi. Masalan, ifoda
={1..5}
... 1,2,3,4,5 ro'yxatini ishlab chiqaradi.
Xo'sh, 2020 yilga qattiq bog'lanmaslik uchun biz funktsiyadan foydalanamiz DateTime.LocalNow() – Excel funksiyasining analogi BUGUN (BUGUN) Power Query da - va undan, o'z navbatida, funksiya bo'yicha joriy yilni ajratib oling Sana. Yil.
3. Olingan yillar to'plami, garchi u juda adekvat ko'rinsa ham, Power Query uchun jadval emas, balki maxsus ob'ekt - ro'yxat (Ro'yxat). Lekin uni jadvalga aylantirish muammo emas: tugmani bosish kifoya Stolga (Jadvalga) yuqori chap burchakda:
4. Tugatish chizig'i! Oldindan yaratilgan funktsiyani qo'llash fxYear yillarning natijasi ro'yxatiga. Buning uchun yorliqda Ustun qo'shish tugmasini bosing Maxsus funktsiyani chaqirish (Ustun qo'shish - maxsus funktsiyani chaqirish) va uning yagona argumentini - ustunni o'rnating Column1 yillar davomida:
Tugmasini bosgandan so'ng OK bizning funktsiyamiz fxYear import har bir yil uchun navbat bilan ishlaydi va biz ustunni olamiz, unda har bir katakda ishlamaydigan kunlar sanalari ko'rsatilgan jadval mavjud (agar siz yonidagi katakchaning fonida bossangiz, jadval mazmuni aniq ko'rinadi. so'z stol):
Ustun sarlavhasidagi ikkita strelkali belgini bosish orqali ichki o'rnatilgan jadvallar tarkibini kengaytirish qoladi. Xurmo (belgi Prefiks sifatida asl ustun nomidan foydalaning uni olib tashlash mumkin):
... va bosgandan keyin OK biz xohlagan narsani olamiz - 2013 yildan joriy yilga qadar barcha bayramlar ro'yxati:
Birinchi, allaqachon keraksiz ustun o'chirilishi mumkin, ikkinchisi uchun ma'lumotlar turini o'rnating sana (Sana) ustun sarlavhasidagi ochiladigan ro'yxatda:
So'rovning o'zi undan mazmunliroq narsaga o'zgartirilishi mumkin So'rov 1 va keyin buyruq yordamida natijalarni varaqqa dinamik "aqlli" jadval ko'rinishida yuklang yoping va yuklab oling tab Bosh sahifa (Uy — Yopish va yuklash):
Siz yaratgan kalendarni kelajakda sichqonchaning o'ng tugmasi bilan jadval yoki buyruq orqali o'ng paneldagi so'rovni yangilashingiz mumkin. Yangilash va saqlash. Yoki tugmani ishlating Hammasini yangilang tab ma'lumotlar (Sana - Hammasini yangilash) yoki klaviatura yorlig'i Ctrl+Alt+F5.
Hammasi shu.
Endi siz hech qachon dam olish kunlarini qidirish va yangilash uchun vaqt va o'ylangan yoqilg'ini behuda sarflamasligingiz kerak - endi sizda "abadiy" ishlab chiqarish taqvimi mavjud. Qanday bo'lmasin, http://xmlcalendar.ru/ saytining mualliflari o'z avlodlarini qo'llab-quvvatlaguncha, umid qilamanki, bu juda uzoq vaqt davom etadi (yana ularga rahmat!).
- Power Query orqali internetdan ustunlik qilish uchun bitcoin kursini import qiling
- WORKDAY funksiyasidan foydalanib keyingi ish kunini topish
- Sana oraliqlarining kesishishini topish