Mündəricat
İstehsal təqvimi, yəni bütün rəsmi iş günləri və bayramların müvafiq olaraq qeyd olunduğu tarixlərin siyahısı – hər hansı bir Microsoft Excel istifadəçisi üçün tamamilə zəruri bir şeydir. Praktikada onsuz edə bilməzsiniz:
- mühasibat hesablamalarında (əmək haqqı, iş stajı, məzuniyyətlər...)
- logistikada – həftə sonları və bayram günləri nəzərə alınmaqla çatdırılma vaxtlarının düzgün müəyyən edilməsi üçün (klassik “bayramdan sonra gəlmisiniz?”)
- layihənin idarə edilməsində – yenidən iş-qeyri-iş günləri nəzərə alınmaqla müddətlərin düzgün qiymətləndirilməsi üçün
- kimi funksiyaların hər hansı istifadəsi İŞ GÜNÜ (İŞ GÜNÜ) or SAF İŞLƏR (ŞƏBƏKƏ GÜNLƏRİ), çünki onlar arqument kimi bayramların siyahısını tələb edirlər
- Power Pivot və Power BI-da Time Intelligence funksiyalarından (TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR və s.) istifadə edərkən
- … və s. və s. – çoxlu misallar.
1C və ya SAP kimi korporativ ERP sistemlərində işləyənlər üçün daha asandır, çünki istehsal təqvimi onların içərisindədir. Bəs Excel istifadəçiləri haqqında nə demək olar?
Əlbəttə ki, belə bir təqvimi əl ilə saxlaya bilərsiniz. Ancaq sonra hökumətimiz tərəfindən icad edilən bütün həftə sonlarını, köçürmələri və qeyri-iş günlərini diqqətlə daxil edərək, ildə ən azı bir dəfə (və ya daha tez-tez, "şən" 2020-də olduğu kimi) yeniləməli olacaqsınız. Və sonra bu proseduru hər gələn il təkrarlayın. Cansıxıcılıq.
Bir az dəli olmağa və Excel-də "əbədi" zavod təqvimi yaratmağa nə deyirsiniz? Özünü yeniləyən, İnternetdən məlumat götürən və hər hansı hesablamalarda sonrakı istifadə üçün həmişə qeyri-iş günlərinin aktual siyahısını yaradan biri? Cazibədar?
Bunu etmək, əslində, heç də çətin deyil.
Verilənlər mənbəyi
Əsas sual məlumatları haradan əldə etməkdir? Uyğun mənbə axtarışında bir neçə variantdan keçdim:
- Orijinal fərmanlar hökumətin saytında PDF formatında dərc olunur (məsələn, onlardan biri burada) və dərhal yoxa çıxır - faydalı məlumatları onlardan çıxarmaq mümkün deyil.
- Cazibədar bir seçim, ilk baxışdan, müvafiq məlumat dəstinin olduğu "Federasiyanın Açıq Məlumat Portalı" kimi görünürdü, lakin daha yaxından araşdırdıqda hər şey kədərli oldu. Sayt Excel-ə idxal etmək üçün olduqca əlverişsizdir, texniki dəstək cavab vermir (özünü təcrid edir?) və məlumatların özü orada uzun müddət köhnəlmişdir - 2020-ci il üçün istehsal təqvimi sonuncu dəfə 2019-cu ilin noyabrında yenilənmişdir (rüsvayçılıq!) və , əlbəttə ki, məsələn, 2020-ci ilin "koronavirus" və "səsvermə" həftəsonunu ehtiva etmir.
Rəsmi mənbələrdən məyus olub qeyri-rəsmi mənbələri qazmağa başladım. İnternetdə bunların çoxu var, lakin əksəriyyəti yenə də Excel-ə idxal etmək üçün tamamilə yararsızdır və gözəl şəkillər şəklində istehsal təqvimi verir. Amma onu divardan asmaq bizim üçün deyil, hə?
Axtarış zamanı təsadüfən gözəl bir şey aşkar edildi - http://xmlcalendar.ru/ saytı
Lazımsız "fırfırlar" olmadan, sadə, yüngül və sürətli sayt, bir tapşırıq üçün kəskinləşdi - hər kəsə XML formatında arzu olunan il üçün istehsal təqvimi vermək. Əla!
Əgər birdən xəbəriniz yoxdursa, XML xüsusi ilə işarələnmiş məzmunlu mətn formatıdır
Hər halda, saytın müəllifləri ilə əlaqə saxladım və onlar saytın 7 ildir mövcud olduğunu təsdiqlədilər, oradakı məlumatlar daim yenilənir (bunun üçün hətta github-da filialları var) və onu bağlamaq fikrində deyillər. Mən sizin və mənim hər hansı bir layihəmiz və Excel-də hesablamalarımız üçün ondan məlumat yükləməyimizə qətiyyən etiraz etmirəm. Pulsuzdur. Hələ də belə insanların olduğunu bilmək xoşdur! Hörmət!
Power Query əlavəsindən istifadə edərək bu məlumatı Excel-ə yükləmək qalır (Excel 2010-2013 versiyaları üçün onu Microsoft veb saytından pulsuz yükləmək olar və Excel 2016 və daha yeni versiyalarında artıq defolt olaraq quraşdırılmışdır. ).
Hərəkətlərin məntiqi aşağıdakı kimi olacaq:
- Biz istənilən bir il üçün saytdan məlumat endirmək üçün sorğu veririk
- Sorğumuzu funksiyaya çevirmək
- Biz bu funksiyanı 2013-cü ildən başlayaraq cari ilə qədər bütün mövcud illərin siyahısına tətbiq edirik – və avtomatik yenilənmə ilə “əbədi” istehsal təqvimi əldə edirik. Voila!
Addım 1. Bir il üçün təqvimi idxal edin
Birincisi, istehsal təqvimini istənilən bir il üçün, məsələn, 2020-ci il üçün yükləyin. Bunu etmək üçün Excel-də sekmeye keçin. Tarix (Və ya Güc sorğusuonu ayrıca əlavə kimi quraşdırmısınızsa) və seçin İnternetdən (Vebdən). Açılan pəncərədə, saytdan kopyalanan müvafiq ilə keçidi yapışdırın:
Klikdən sonra OK düyməni basmalı olduğunuz bir önizləmə pəncərəsi görünür Data çevirmək (Məlumatların çevrilməsi) or Məlumatları dəyişdirmək üçün (Məlumatları redaktə et) və biz verilənlərlə işləməyə davam edəcəyimiz Power Query sorğu redaktoru pəncərəsinə keçəcəyik:
Dərhal sağ paneldə təhlükəsiz şəkildə silə bilərsiniz Sorğu Parametrləri (Sorğu parametrləri) addım dəyişdirilmiş növü (Dəyişən Növ) Ona ehtiyacımız yoxdur.
Bayramlar sütunundakı cədvəldə qeyri-iş günlərinin kodları və təsvirləri var – siz yaşıl sözə klikləməklə iki dəfə “düşməklə” onun məzmununu görə bilərsiniz. Masa:
Geri qayıtmaq üçün sağ paneldə geri görünən bütün addımları silməli olacaqsınız mənbə (Mənbə).
Bənzər şəkildə əldə edilə bilən ikinci cədvəldə tam olaraq bizə lazım olan şey var - bütün qeyri-iş günlərinin tarixləri:
Bu lövhəni emal etmək qalır, yəni:
1. İkinci sütun üzrə yalnız bayram tarixlərini (yəni olanları) süzün Atribut:t
2. Birinci sütundan başqa bütün sütunları silin - birinci sütunun başlığına sağ klikləyin və əmri seçin Digər sütunları silin (Digər Sütunları Sil):
3. Komanda ilə birinci sütunu ay və gün üçün ayrıca nöqtələrə bölün Ayrılmış Sütun - Ayırıcı ilə nişanı Transformasiya (Transform - Ayrılmış sütun - Ayırıcı ilə):
4. Və nəhayət, normal tarixləri olan hesablanmış sütun yaradın. Bunu etmək üçün nişanda Sütun əlavə edilməsi düyməsini sıxın Fərdi sütun (Sütun əlavə et - Fərdi Sütun) və görünən pəncərədə aşağıdakı formula daxil edin:
=#tarixli(2020, [#»Atribut:d.1″], [#»Atribut:d.2″])
Burada #date operatorunun üç arqumenti var: müvafiq olaraq il, ay və gün. Tıkladıqdan sonra OK normal həftə sonu tarixləri ilə tələb olunan sütunu alırıq və 2-ci addımda olduğu kimi qalan sütunları silirik
Addım 2. Sorğunun funksiyaya çevrilməsi
Növbəti vəzifəmiz 2020-ci il üçün yaradılmış sorğunu istənilən il üçün universal funksiyaya çevirməkdir (il nömrəsi onun arqumenti olacaq). Bunu etmək üçün aşağıdakıları edirik:
1. Panelin genişləndirilməsi (artıq genişləndirilməyibsə). Sorğular (Sorğular) Power Query pəncərəsində solda:
2. Sorğunu funksiyaya çevirdikdən sonra sorğunu təşkil edən addımları görmək və onları asanlıqla redaktə etmək imkanı təəssüf ki, yox olur. Buna görə də, istəyimizin bir nüsxəsini çıxarmaq və onunla artıq əylənmək və orijinalı ehtiyatda saxlamaq məntiqlidir. Bunu etmək üçün təqvim sorğumuzda sol paneldə sağ klikləyin və Dublikat əmrini seçin.
Təqvimin (2) əldə edilən nüsxəsinə yenidən sağ klikləməklə əmr seçiləcək Təzə ad vermək (Adını dəyişdir) və yeni ad daxil edin - məsələn, fxYear:
3. Biz əmrdən istifadə edərək sorğunun mənbə kodunu daxili Power Query dilində (qısaca “M” adlanır) açırıq. Qabaqcıl redaktor nişanı baxış(Bax - Qabaqcıl Redaktor) və sorğumuzu istənilən il funksiyaya çevirmək üçün orada kiçik dəyişikliklər edin.
Bu idi:
Sonra:
Təfərrüatlarla maraqlanırsınızsa, burada:
- (nömrə kimi il)=> – bildiririk ki, funksiyamızın bir ədədi arqumenti olacaq – dəyişən il
- Dəyişənin yapışdırılması il addım-addım veb linkinə mənbə. Power Query rəqəmləri və mətni yapışdırmağa imkan vermədiyi üçün biz funksiyadan istifadə edərək il nömrəsini tez mətnə çeviririk. Nömrə.Text
- Sondan əvvəlki addımda il dəyişənini 2020-ci ilə əvəz edirik #"Fərdi obyekt əlavə edildi«, burada fraqmentlərdən tarixi formalaşdırdıq.
Klikdən sonra finiş sorğumuz funksiyaya çevrilir:
Addım 3. Bütün illər üçün təqvimləri idxal edin
Qalan son şey, bütün mövcud illər üçün məlumatları yükləyən və bütün qəbul edilmiş tətil tarixlərini bir cədvələ əlavə edən son əsas sorğunu etməkdir. Bunun üçün:
1. Sağ siçan düyməsi ilə boz boş yerdə sol sorğu panelinə klikləyirik və ardıcıl olaraq seçirik. Yeni sorğu – Digər mənbələr – Boş sorğu (Yeni Sorğu — Digər mənbələrdən — Boş sorğu):
2. Təqvimləri tələb edəcəyimiz bütün illərin siyahısını yaratmalıyıq, yəni 2013, 2014 … 2020. Bunu etmək üçün görünən boş sorğunun düstur sətrində əmri daxil edin:
Quruluş:
={NömrəA..NömrəB}
… Power Query-də A-dan B-yə qədər tam ədədlərin siyahısını yaradır. Məsələn, ifadə
={1..5}
… 1,2,3,4,5-dən ibarət bir siyahı çıxaracaq.
Yaxşı, 2020-ci ilə möhkəm bağlanmamaq üçün funksiyadan istifadə edirik DateTime.LocalNow() – Excel funksiyasının analoqu TODAY (BUGÜN) Power Query-də – və ondan öz növbəsində funksiya ilə cari ili çıxarın Tarix.İl.
3. Nəticədə ortaya çıxan illər dəsti, kifayət qədər adekvat görünsə də, Power Query üçün cədvəl deyil, xüsusi obyektdir – siyahı (siyahı). Ancaq onu cədvələ çevirmək problem deyil: sadəcə düyməni basın Masaya (Cədvəl üçün) yuxarı sol küncdə:
4. Finiş xətti! Əvvəllər yaratdığımız funksiyanın tətbiqi fxYear nəticədə illərin siyahısına. Bunu etmək üçün nişanda Sütun əlavə edilməsi Düyməyə bas Fərdi funksiyaya zəng edin (Sütun əlavə et - Xüsusi funksiyanı çağır) və onun yeganə arqumentini - sütunu təyin edin Column1 illər ərzində:
Klikdən sonra OK bizim funksiyamız fxYear idxal hər il üçün növbə ilə işləyəcək və biz sütun alacağıq ki, burada hər bir hüceyrədə qeyri-iş günlərinin tarixləri olan bir cədvəl var (əgər yanındakı xananın fonunda klikləsəniz, cədvəlin məzmunu aydın görünür. söz Masa):
Sütun başlığında ikiqat oxlu ikona klikləməklə iç içə daxil edilmiş cədvəllərin məzmununu genişləndirmək qalır. Tarix (qeyd Orijinal sütun adından prefiks kimi istifadə edin çıxarıla bilər):
... və kliklədikdən sonra OK biz istədiyimizi alırıq – 2013-cü ildən cari ilə qədər bütün bayramların siyahısı:
Birinci, artıq lazımsız sütun silinə bilər, ikincisi üçün məlumat tipini təyin edin tarix (Tarix) sütun başlığında açılan siyahıda:
Sorğunun özünü daha mənalı bir şey adlandırmaq olar Sorğu 1 və sonra əmrdən istifadə edərək nəticələri dinamik "ağıllı" cədvəl şəklində vərəqə yükləyin bağlayın və yükləyin nişanı Əsas səhifə (Ev - Bağla və Yüklə):
Yaradılmış təqvimi gələcəkdə masanın üzərinə sağ klikləməklə və ya əmr vasitəsilə sağ paneldə sorğu ilə yeniləyə bilərsiniz. Yeniləyin və Saxlayın. Və ya düyməni istifadə edin Hamısını yeniləyin nişanı Tarix (Tarix - Hamısını Yeniləyin) və ya klaviatura qısa yolu Ctrl+Dayandırmaq+F5.
Bütün bunlar.
İndi heç vaxt bayramların siyahısını axtarmağa və yeniləməyə vaxt və enerji sərf etməməlisiniz – indi sizin “əbədi” istehsal təqviminiz var. Hər halda, http://xmlcalendar.ru/ saytının müəllifləri öz nəslini dəstəklədikcə, ümid edirəm ki, çox, çox uzun müddət olacaq (bir daha onlara təşəkkür edirəm!).
- Power Query vasitəsilə internetdən üstün olmaq üçün bitcoin dərəcəsini idxal edin
- WORKDAY funksiyasından istifadə edərək növbəti iş gününün tapılması
- Tarix intervallarının kəsişməsinin tapılması