Kalender pabrik di Excel

Kalender produksi, yaitu daftar tanggal, di mana semua hari kerja dan hari libur resmi ditandai – hal yang mutlak diperlukan bagi setiap pengguna Microsoft Excel. Dalam praktiknya, Anda tidak dapat melakukannya tanpanya:

• dalam perhitungan akuntansi (gaji, masa kerja, liburan ...)
• dalam logistik - untuk penentuan waktu pengiriman yang benar, dengan mempertimbangkan akhir pekan dan hari libur (ingat klasik "ayo setelah liburan?")
  
• dalam manajemen proyek – untuk perkiraan istilah yang benar, dengan mempertimbangkan, sekali lagi, hari kerja-non-kerja
• penggunaan fungsi seperti HARI KERJA (HARI KERJA) or PEKERJA MURNI (HARI JARINGAN), karena mereka membutuhkan daftar hari libur sebagai argumen
• saat menggunakan fungsi Time Intelligence (seperti TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, dll.) di Power Pivot dan Power BI
  
• … dll dll – banyak contoh.
  

Lebih mudah bagi mereka yang bekerja di sistem ERP perusahaan seperti 1C atau SAP, karena kalender produksi sudah terpasang di dalamnya. Tapi bagaimana dengan pengguna Excel?

Anda tentu saja dapat menyimpan kalender semacam itu secara manual. Tetapi kemudian Anda harus memperbaruinya setidaknya setahun sekali (atau bahkan lebih sering, seperti pada "riang" 2020), dengan hati-hati memasuki semua akhir pekan, transfer, dan hari-hari tidak bekerja yang diciptakan oleh pemerintah kita. Dan kemudian ulangi prosedur ini setiap tahun berikutnya. Kebosanan.

Bagaimana kalau menjadi sedikit gila dan membuat kalender pabrik "abadi" di Excel? Salah satu yang memperbarui dirinya sendiri, mengambil data dari Internet dan selalu menghasilkan daftar hari tidak bekerja terbaru untuk penggunaan selanjutnya dalam perhitungan apa pun? Menggoda?

Untuk melakukan ini, sebenarnya, sama sekali tidak sulit.

Sumber data

Pertanyaan utamanya adalah dari mana mendapatkan data tersebut? Untuk mencari sumber yang cocok, saya melewati beberapa opsi:

• Keputusan asli diterbitkan di situs web pemerintah dalam format PDF (di sini, salah satunya, misalnya) dan segera menghilang – informasi yang berguna tidak dapat ditarik darinya.
• A tempting option, at first glance, seemed to be the “Open Data Portal of the Federation”, where there is a corresponding data set, but, upon closer examination, everything turned out to be sad. The site is terribly inconvenient for importing into Excel, technical support does not respond (self-isolated?), and the data itself is outdated there for a long time – the production calendar for 2020 was last updated in November 2019 (disgrace!) and, of course, does not contain our “coronavirus ‘ and the ‘voting’ weekend of 2020, for example.

Karena kecewa dengan sumber resmi, saya mulai menggali sumber tidak resmi. Ada banyak dari mereka di Internet, tetapi kebanyakan dari mereka, sekali lagi, sama sekali tidak cocok untuk diimpor ke Excel dan memberikan kalender produksi dalam bentuk gambar yang indah. Tapi bukan untuk kita menggantungnya di dinding, kan?

Dan dalam proses pencarian, hal yang luar biasa secara tidak sengaja ditemukan – situs http://xmlcalendar.ru/

Tanpa "embel-embel" yang tidak perlu, situs yang sederhana, ringan dan cepat, dipertajam untuk satu tugas – untuk memberi semua orang kalender produksi untuk tahun yang diinginkan dalam format XML. Bagus sekali!

Jika, tiba-tiba, Anda tidak mengetahuinya, maka XML adalah format teks dengan konten yang ditandai dengan khusus . Ringan, nyaman, dan mudah dibaca oleh sebagian besar program modern, termasuk Excel.

Untuk jaga-jaga, saya menghubungi penulis situs dan mereka mengonfirmasi bahwa situs tersebut telah ada selama 7 tahun, data di dalamnya terus diperbarui (mereka bahkan memiliki cabang di github untuk ini) dan mereka tidak akan menutupnya. Dan saya tidak keberatan sama sekali bahwa Anda dan saya memuat data darinya untuk setiap proyek dan perhitungan kami di Excel. Bebas. Senang mengetahui bahwa masih ada orang seperti ini! Menghormati!

Tetap memuat data ini ke dalam Excel menggunakan add-in Power Query (untuk versi Excel 2010-2013 dapat diunduh secara gratis dari situs web Microsoft, dan di versi Excel 2016 dan yang lebih baru sudah bawaan secara default ).

Logika tindakannya adalah sebagai berikut:

1. Kami membuat permintaan untuk mengunduh data dari situs selama satu tahun
2. Mengubah permintaan kami menjadi sebuah fungsi
3. Kami menerapkan fungsi ini ke daftar semua tahun yang tersedia, mulai dari 2013 dan hingga tahun ini – dan kami mendapatkan kalender produksi “abadi” dengan pembaruan otomatis. Voila!
   

Langkah 1. Impor kalender untuk satu tahun

Pertama, muat kalender produksi untuk satu tahun, misalnya, untuk tahun 2020. Untuk melakukannya, di Excel, buka tab Data (Atau Kueri Dayajika Anda menginstalnya sebagai add-on terpisah) dan pilih Dari internet (Dari Web). Di jendela yang terbuka, rekatkan tautan ke tahun yang sesuai, disalin dari situs:

Setelah mengklik OK jendela pratinjau muncul, di mana Anda perlu mengklik tombol Konversi Data (Transformasi data) or Untuk mengubah data (Mengedit data) dan kita akan masuk ke jendela editor kueri Power Query, tempat kita akan terus bekerja dengan data:

Segera Anda dapat menghapus dengan aman di panel kanan Parameter Permintaan (Setelan kueri) langkah tipe yang dimodifikasi (Tipe Berubah) Kami tidak membutuhkan dia.

Tabel di kolom hari libur berisi kode dan deskripsi hari-hari tidak bekerja – Anda dapat melihat isinya dengan “menjatuhkannya” dua kali dengan mengklik kata hijau tabel:

Untuk kembali, Anda harus menghapus di panel kanan semua langkah yang muncul kembali sumber (Sumber).

Tabel kedua, yang dapat diakses dengan cara yang sama, berisi persis apa yang kita butuhkan – tanggal semua hari tidak bekerja:

Tetap memproses pelat ini, yaitu:

1. Filter hanya tanggal liburan (yaitu yang) dengan kolom kedua Atribut: t

2. Hapus semua kolom kecuali yang pertama – dengan mengklik kanan pada judul kolom pertama dan memilih perintah Hapus kolom lain (Hapus Kolom Lainnya):

3. Pisahkan kolom pertama dengan titik secara terpisah untuk bulan dan hari dengan perintah Pisahkan Kolom – Dengan Pembatas tab Transformasi (Transform — Pisahkan kolom — Dengan pembatas):

4. Dan akhirnya buat kolom terhitung dengan tanggal normal. Untuk melakukan ini, pada tab Menambahkan kolom klik tombolnya Kolom khusus (Tambahkan Kolom — Kolom Kustom) dan masukkan rumus berikut di jendela yang muncul:

=#bertanggal(2020, [#»Atribut:d.1″], [#»Atribut:d.2″])

Di sini, operator #date memiliki tiga argumen: tahun, bulan, dan hari, masing-masing. Setelah mengklik OK kami mendapatkan kolom yang diperlukan dengan tanggal akhir pekan normal, dan menghapus kolom yang tersisa seperti pada langkah 2

Langkah 2. Mengubah permintaan menjadi fungsi

Tugas kita selanjutnya adalah mengonversi kueri yang dibuat untuk tahun 2020 menjadi fungsi universal untuk tahun apa pun (nomor tahun akan menjadi argumennya). Untuk melakukan ini, kami melakukan hal berikut:

1. Memperluas (jika belum diperluas) panel Pertanyaan (Permintaan) di sebelah kiri di jendela Power Query:

2. Setelah mengonversi permintaan menjadi fungsi, kemampuan untuk melihat langkah-langkah yang menyusun permintaan dan mengeditnya dengan mudah, sayangnya, menghilang. Oleh karena itu, masuk akal untuk membuat salinan permintaan kami dan bermain-main dengannya, dan meninggalkan yang asli sebagai cadangan. Untuk melakukan ini, klik kanan di panel kiri pada permintaan kalender kami dan pilih perintah Duplikat.

Mengklik kanan lagi pada salinan kalender yang dihasilkan (2) akan memilih perintah Rename (Ganti nama) dan masukkan nama baru – biarlah, misalnya, fxTahun:

3. Kami membuka kode sumber kueri dalam bahasa Power Query internal (secara ringkas disebut “M”) menggunakan perintah Editor Tingkat Lanjut tab ULASAN(Lihat — Editor Lanjutan) dan buat perubahan kecil di sana untuk mengubah permintaan kami menjadi fungsi untuk tahun apa pun.

Dulu:

Setelah:

Jika Anda tertarik dengan detailnya, maka di sini:

• (tahun sebagai angka)=>  – kita mendeklarasikan bahwa fungsi kita akan memiliki satu argumen numerik – sebuah variabel tahun
• Menempelkan variabel tahun ke tautan web dalam langkah sumber. Karena Power Query tidak memungkinkan Anda untuk merekatkan angka dan teks, kami mengonversi nomor tahun menjadi teks dengan cepat menggunakan fungsi Nomor.ToText
• Kami mengganti variabel tahun untuk 2020 di langkah kedua dari belakang #”Menambahkan objek khusus«, tempat kami membentuk tanggal dari fragmen.
  

Setelah mengklik Finish permintaan kami menjadi fungsi:

Langkah 3. Impor kalender untuk semua tahun

Hal terakhir yang tersisa adalah membuat kueri utama terakhir, yang akan mengunggah data untuk semua tahun yang tersedia dan menambahkan semua tanggal liburan yang diterima ke dalam satu tabel. Untuk ini:

1. Kami mengklik di panel kueri kiri di ruang kosong abu-abu dengan tombol kanan mouse dan memilih secara berurutan Permintaan baru – Sumber lain – Permintaan kosong (Kueri Baru — Dari sumber lain — Kueri kosong):

2. Kita perlu membuat daftar semua tahun yang akan kita minta kalendernya, yaitu 2013, 2014 ... 2020. Untuk melakukan ini, di bilah rumus dari kueri kosong yang muncul, masukkan perintah:

Struktur:

={NomorA..NomorB}

… di Power Query menghasilkan daftar bilangan bulat dari A ke B. Misalnya, ekspresi

={1}

… akan menghasilkan daftar 1,2,3,4,5.

Nah, agar tidak terikat dengan kaku pada tahun 2020, kami menggunakan fungsi TanggalWaktu.LokalSekarang() – analog dari fungsi Excel HARI INI (HARI INI) di Power Query – dan ekstrak darinya, pada gilirannya, tahun berjalan dengan fungsi Tanggal.Tahun.

3. Kumpulan tahun yang dihasilkan, meskipun terlihat cukup memadai, bukanlah tabel untuk Power Query, tetapi objek khusus – daftar (Daftar). Tetapi mengubahnya menjadi tabel bukanlah masalah: cukup klik tombol Ke meja (Ke meja) di pojok kiri atas:

4. Garis akhir! Menerapkan fungsi yang kita buat sebelumnya fxTahun ke daftar tahun yang dihasilkan. Untuk melakukan ini, pada tab Menambahkan kolom tekan tombolnya Panggil fungsi khusus (Tambahkan Kolom — Aktifkan Fungsi Kustom) dan atur satu-satunya argumennya – kolom Column1 selama bertahun-tahun:

Setelah mengklik OK fungsi kami fxTahun impor akan bekerja secara bergantian untuk setiap tahun dan kami akan mendapatkan kolom di mana setiap sel akan berisi tabel dengan tanggal hari tidak bekerja (isi tabel terlihat jelas jika Anda mengklik di latar belakang sel di sebelah kata tabel):

Tetap memperluas konten tabel bersarang dengan mengklik ikon dengan panah ganda di tajuk kolom Tanggal (kutu Gunakan nama kolom asli sebagai awalan dapat dihapus):

… dan setelah mengklik OK kami mendapatkan apa yang kami inginkan – daftar semua liburan dari tahun 2013 hingga tahun ini:

Kolom pertama, yang sudah tidak perlu, dapat dihapus, dan untuk yang kedua, atur tipe datanya tanggal (Tanggal) di daftar dropdown di judul kolom:

Kueri itu sendiri dapat diganti namanya menjadi sesuatu yang lebih bermakna daripada Permintaan1 lalu unggah hasilnya ke lembar dalam bentuk tabel "pintar" dinamis menggunakan perintah tutup dan unduh tab Beranda (Beranda — Tutup & Muat):

Anda dapat memperbarui kalender yang dibuat di masa mendatang dengan mengklik kanan tabel atau kueri di panel kanan melalui perintah Perbarui & Simpan. Atau gunakan tombol Segarkan semua tab Data (Tanggal — Segarkan Semua) atau pintasan keyboard Ctrl+lain+F5.

Itu saja.

Sekarang Anda tidak perlu lagi membuang waktu dan pikiran untuk mencari dan memperbarui daftar hari libur – sekarang Anda memiliki kalender produksi “abadi”. Bagaimanapun, selama penulis situs http://xmlcalendar.ru/ mendukung keturunan mereka, yang, saya harap, akan bertahan untuk waktu yang sangat, sangat lama (terima kasih kepada mereka lagi!).

• Impor tingkat bitcoin untuk unggul dari internet melalui Power Query
• Menemukan hari kerja berikutnya menggunakan fungsi WORKDAY
  
• Menemukan persimpangan interval tanggal