Contents [show]
Kalendar pengeluaran, iaitu senarai tarikh, di mana semua hari bekerja dan cuti rasmi ditandakan dengan sewajarnya – perkara yang sangat diperlukan untuk mana-mana pengguna Microsoft Excel. Dalam amalan, anda tidak boleh melakukannya tanpanya:
- dalam pengiraan perakaunan (gaji, tempoh perkhidmatan, cuti…)
- dalam logistik - untuk penentuan masa penghantaran yang betul, dengan mengambil kira hujung minggu dan hari cuti (ingat klasik "datang selepas cuti?")
- dalam pengurusan projek – untuk anggaran terma yang betul, dengan mengambil kira, sekali lagi, hari bekerja-tidak bekerja
- sebarang penggunaan fungsi seperti HARI KERJA (HARI KERJA) or PEKERJA MURNI (NETWORKDAYS), kerana mereka memerlukan senarai cuti sebagai hujah
- apabila menggunakan fungsi Perisikan Masa (seperti TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, dll.) dalam Power Pivot dan Power BI
- … dsb. dsb. – banyak contoh.
Lebih mudah bagi mereka yang bekerja dalam sistem ERP korporat seperti 1C atau SAP, kerana kalendar pengeluaran terbina di dalamnya. Tetapi bagaimana dengan pengguna Excel?
Anda boleh, sudah tentu, menyimpan kalendar sedemikian secara manual. Tetapi kemudian anda perlu mengemas kininya sekurang-kurangnya sekali setahun (atau lebih kerap, seperti dalam "meriang" 2020), dengan berhati-hati memasuki semua hujung minggu, pemindahan dan hari tidak bekerja yang dicipta oleh kerajaan kita. Dan kemudian ulangi prosedur ini setiap tahun depan. Kebosanan.
Bagaimana pula dengan menjadi sedikit gila dan membuat kalendar kilang "kekal" dalam Excel? Yang mengemas kini sendiri, mengambil data daripada Internet dan sentiasa menjana senarai terkini hari tidak bekerja untuk kegunaan seterusnya dalam sebarang pengiraan? Menggoda?
Untuk melakukan ini, sebenarnya, tidak sukar sama sekali.
Sumber data
Persoalan utama ialah di mana untuk mendapatkan data? Untuk mencari sumber yang sesuai, saya melalui beberapa pilihan:
- Dekri asal diterbitkan di laman web kerajaan dalam format PDF (di sini, salah satu daripadanya, sebagai contoh) dan hilang serta-merta – maklumat berguna tidak boleh dikeluarkan daripadanya.
- Pilihan yang menarik, pada pandangan pertama, nampaknya adalah "Portal Data Terbuka Persekutuan", di mana terdapat set data yang sepadan, tetapi, setelah diperiksa lebih dekat, semuanya ternyata menyedihkan. Tapak ini amat menyusahkan untuk mengimport ke dalam Excel, sokongan teknikal tidak bertindak balas (mengasingkan diri?), dan data itu sendiri sudah lapuk di sana untuk masa yang lama – kalendar pengeluaran untuk 2020 kali terakhir dikemas kini pada November 2019 (memalukan!) dan , sudah tentu, tidak mengandungi "coronavirus' kami dan hujung minggu 'pengundian' 2020, contohnya.
Kecewa dengan sumber rasmi, saya mula menggali yang tidak rasmi. Terdapat banyak daripada mereka di Internet, tetapi kebanyakannya, sekali lagi, sama sekali tidak sesuai untuk mengimport ke dalam Excel dan memberikan kalendar pengeluaran dalam bentuk gambar yang cantik. Tapi bukan untuk kita gantung kat dinding kan?
Dan dalam proses mencari, satu perkara yang menarik telah ditemui secara tidak sengaja - tapak http://xmlcalendar.ru/
Tanpa "tambahan" yang tidak perlu, tapak yang ringkas, ringan dan pantas, dipertajam untuk satu tugas - untuk memberikan semua orang kalendar pengeluaran untuk tahun yang diingini dalam format XML. Cemerlang!
Jika, tiba-tiba, anda tidak tahu, maka XML ialah format teks dengan kandungan yang ditandai dengan khas
Sekiranya berlaku, saya menghubungi pengarang laman web dan mereka mengesahkan bahawa tapak itu telah wujud selama 7 tahun, data mengenainya sentiasa dikemas kini (mereka juga mempunyai cawangan di github untuk ini) dan mereka tidak akan menutupnya. Dan saya tidak kisah sama sekali bahawa anda dan saya memuatkan data daripadanya untuk mana-mana projek dan pengiraan kami dalam Excel. Percuma. Seronok tau masih ada lagi orang macam ni! Hormat!
Ia kekal untuk memuatkan data ini ke dalam Excel menggunakan alat tambah Power Query (untuk versi Excel 2010-2013 ia boleh dimuat turun secara percuma dari tapak web Microsoft, dan dalam versi Excel 2016 dan lebih baharu ia sudah terbina dalam secara lalai ).
Logik tindakan adalah seperti berikut:
- Kami membuat permintaan untuk memuat turun data dari tapak untuk mana-mana satu tahun
- Mengubah permintaan kami menjadi fungsi
- Kami menggunakan fungsi ini pada senarai semua tahun yang tersedia, bermula dari 2013 dan sehingga tahun semasa - dan kami mendapat kalendar pengeluaran "kekal" dengan pengemaskinian automatik. Voila!
Langkah 1. Import kalendar untuk satu tahun
Mula-mula, muatkan kalendar pengeluaran untuk mana-mana satu tahun, contohnya, untuk 2020. Untuk melakukan ini, dalam Excel, pergi ke tab Tarikh (Atau Kuasa Kuasajika anda memasangnya sebagai alat tambah yang berasingan) dan pilih Dari Internet (Daripada Web). Dalam tetingkap yang terbuka, tampal pautan ke tahun yang sepadan, yang disalin daripada tapak:
Selepas mengklik OK tetingkap pratonton muncul, di mana anda perlu mengklik butang Tukar Data (Ubah data) or Untuk menukar data (Edit data) dan kami akan sampai ke tetingkap editor pertanyaan Power Query, di mana kami akan terus bekerja dengan data:
Dengan serta-merta anda boleh memadam dengan selamat di panel kanan Permintaan Parameter (Tetapan pertanyaan) langkah jenis yang diubah suai (Jenis Ditukar) Kita tak perlukan dia.
Jadual dalam lajur cuti mengandungi kod dan perihalan hari tidak bekerja – anda boleh melihat kandungannya dengan "melalui" dua kali dengan mengklik pada perkataan hijau Jadual:
Untuk kembali, anda perlu memadam dalam panel kanan semua langkah yang telah muncul semula Source (Sumber).
Jadual kedua, yang boleh diakses dengan cara yang sama, mengandungi apa yang kami perlukan - tarikh semua hari tidak bekerja:
Ia tetap memproses plat ini, iaitu:
1. Tapis tarikh cuti sahaja (iaitu tarikh) mengikut lajur kedua Atribut:t
2. Padam semua lajur kecuali yang pertama – dengan mengklik kanan pada tajuk lajur pertama dan memilih arahan Padamkan lajur lain (Alih Keluar Lajur Lain):
3. Pisahkan lajur pertama dengan titik secara berasingan untuk bulan dan hari dengan arahan Lajur Pisah – Mengikut Pembatas tab Transformasi (Ubah — Lajur Pisah — Mengikut pembatas):
4. Dan akhirnya buat lajur yang dikira dengan tarikh biasa. Untuk melakukan ini, pada tab Menambah lajur klik pada butang Lajur tersuai (Tambah Lajur — Lajur Tersuai) dan masukkan formula berikut dalam tetingkap yang muncul:
=#bertarikh(2020, [#»Attribut:d.1″], [#»Attribute:d.2″])
Di sini, pengendali #date mempunyai tiga hujah: tahun, bulan dan hari, masing-masing. Selepas klik pada OK kami mendapat lajur yang diperlukan dengan tarikh hujung minggu biasa, dan memadamkan lajur yang tinggal seperti dalam langkah 2
Langkah 2. Mengubah permintaan menjadi fungsi
Tugas kami seterusnya ialah menukar pertanyaan yang dibuat untuk 2020 kepada fungsi universal untuk mana-mana tahun (nombor tahun akan menjadi hujahnya). Untuk melakukan ini, kami melakukan perkara berikut:
1. Mengembangkan (jika belum dikembangkan) panel Pertanyaan (Pertanyaan) di sebelah kiri dalam tetingkap Power Query:
2. Selepas menukar permintaan kepada fungsi, keupayaan untuk melihat langkah-langkah yang membentuk permintaan dan mengeditnya dengan mudah, malangnya, hilang. Oleh itu, masuk akal untuk membuat salinan permintaan kami dan bermain-main dengannya, dan biarkan yang asal dalam simpanan. Untuk melakukan ini, klik kanan pada anak tetingkap kiri pada permintaan kalendar kami dan pilih arahan Duplikat.
Klik kanan sekali lagi pada salinan kalendar(2) yang terhasil akan memilih arahan Namakan semula (Namakan semula) dan masukkan nama baharu – biarlah, sebagai contoh, fxYear:
3. Kami membuka kod sumber pertanyaan dalam bahasa Power Query dalaman (ia ringkas dipanggil "M") menggunakan arahan Editor Lanjutan tab ULASAN(Lihat — Editor Lanjutan) dan buat perubahan kecil di sana untuk mengubah permintaan kami menjadi fungsi untuk mana-mana tahun.
Ia adalah:
Selepas:
Jika anda berminat dengan butiran, maka di sini:
- (tahun sebagai nombor)=> – kami mengisytiharkan bahawa fungsi kami akan mempunyai satu hujah angka – pembolehubah tahun
- Menampal pembolehubah tahun ke pautan web mengikut langkah Source. Memandangkan Power Query tidak membenarkan anda melekatkan nombor dan teks, kami menukar nombor tahun kepada teks dengan cepat menggunakan fungsi Nombor.ToText
- Kami menggantikan pembolehubah tahun untuk 2020 dalam langkah kedua terakhir #”Menambahkan objek tersuai«, di mana kami membentuk tarikh daripada serpihan.
Selepas mengklik Selesai permintaan kami menjadi fungsi:
Langkah 3. Import kalendar untuk semua tahun
Perkara terakhir yang tinggal ialah membuat pertanyaan utama terakhir, yang akan memuat naik data untuk semua tahun yang tersedia dan menambah semua tarikh cuti yang diterima ke dalam satu jadual. Untuk ini:
1. Kami mengklik dalam panel pertanyaan kiri dalam ruang kosong kelabu dengan butang kanan tetikus dan pilih secara berurutan Permintaan baharu – Sumber lain – Permintaan kosong (Pertanyaan Baharu — Daripada sumber lain — Pertanyaan kosong):
2. Kami perlu menjana senarai semua tahun yang kami akan meminta kalendar, iaitu 2013, 2014 … 2020. Untuk melakukan ini, dalam bar formula pertanyaan kosong yang muncul, masukkan arahan:
Struktur:
={NomborA..NomborB}
… dalam Power Query menjana senarai integer daripada A hingga B. Contohnya, ungkapan
={1..5}
… akan menghasilkan senarai 1,2,3,4,5.
Nah, untuk tidak terikat dengan 2020, kami menggunakan fungsi tersebut DateTime.LocalNow() – analog fungsi Excel TODAY (HARI INI) dalam Power Query – dan ekstrak daripadanya, seterusnya, tahun semasa mengikut fungsi Tarikh.Tahun.
3. Set tahun yang terhasil, walaupun ia kelihatan agak mencukupi, bukanlah jadual untuk Power Query, tetapi objek khas - senarai (Senarai). Tetapi menukarnya kepada jadual tidak menjadi masalah: cuma klik butang Ke meja (Ke Jadual) di sudut kiri atas:
4. Garisan penamat! Menggunakan fungsi yang kami buat sebelum ini fxYear kepada senarai tahun yang terhasil. Untuk melakukan ini, pada tab Menambah lajur tekan butang Panggilan fungsi tersuai (Tambah Lajur — Guna Fungsi Tersuai) dan tetapkan satu-satunya hujahnya - lajur Column1 selama bertahun:
Selepas mengklik OK fungsi kami fxYear import akan berfungsi secara bergilir untuk setiap tahun dan kami akan mendapat lajur di mana setiap sel akan mengandungi jadual dengan tarikh hari tidak bekerja (kandungan jadual kelihatan jelas jika anda mengklik di latar belakang sel di sebelah perkataan itu Jadual):
Ia kekal untuk mengembangkan kandungan jadual bersarang dengan mengklik pada ikon dengan anak panah berganda dalam pengepala lajur tarikh (tandakan Gunakan nama lajur asal sebagai awalan ia boleh dikeluarkan):
… dan selepas mengklik pada OK kami mendapat apa yang kami mahu – senarai semua cuti dari 2013 hingga tahun semasa:
Lajur pertama, yang sudah tidak diperlukan, boleh dipadamkan, dan untuk yang kedua, tetapkan jenis data tarikh (Tarikh) dalam senarai juntai bawah dalam tajuk lajur:
Pertanyaan itu sendiri boleh dinamakan semula sebagai sesuatu yang lebih bermakna daripada Permintaan1 dan kemudian muat naik hasil ke helaian dalam bentuk jadual "pintar" dinamik menggunakan arahan tutup dan muat turun tab Laman Utama (Laman Utama — Tutup & Muatkan):
Anda boleh mengemas kini kalendar yang dibuat pada masa hadapan dengan mengklik kanan pada jadual atau pertanyaan di anak tetingkap kanan melalui arahan Kemas kini & Simpan. Atau gunakan butang Muat semula semua tab Tarikh (Tarikh — Muat semula Semua) atau pintasan papan kekunci Ctrl+Alt+F5.
Itu sahaja.
Kini anda tidak perlu lagi membuang masa dan bahan api berfikir untuk mencari dan mengemas kini senarai cuti - kini anda mempunyai kalendar pengeluaran "kekal". Walau apa pun, selagi pengarang laman web http://xmlcalendar.ru/ menyokong keturunan mereka, yang, saya harap, akan menjadi sangat lama (terima kasih kepada mereka sekali lagi!).
- Import kadar bitcoin untuk cemerlang daripada internet melalui Power Query
- Mencari hari perniagaan seterusnya menggunakan fungsi WORKDAY
- Mencari persilangan selang tarikh