Daptar eusi
Kalender produksi, nyaéta daptar tanggal, dimana sadaya dinten kerja sareng liburan resmi ditandaan sasuai - hal anu penting pisan pikeun pangguna Microsoft Excel. Dina prakték, anjeun moal tiasa ngalakukeun tanpa éta:
- dina itungan akuntansi (gaji, panjang jasa, liburan ...)
- dina logistik - pikeun tekad anu leres tina waktos pangiriman, ngiringan sabtu minggu sareng liburan (inget klasik "hayu saatos liburan?")
- dina manajemén proyék - pikeun estimasi bener tina istilah, nyokot kana akun, deui, digawé-non-kerja poé
- sagala pamakéan fungsi kawas POÉ KERJA (Poé gawé) or Pagawe murni (NETWORKDAYS), sabab merlukeun daptar libur salaku argumen
- nalika nganggo fungsi Time Intelligence (sapertos TTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, jsb.) dina Power Pivot sareng Power BI
- ... jsb jsb - loba conto.
Éta langkung gampang pikeun anu damel di sistem ERP perusahaan sapertos 1C atanapi SAP, sabab kalénder produksi diwangun kana éta. Tapi kumaha upami pangguna Excel?
Anjeun tiasa, tangtosna, nyimpen kalénder sapertos sacara manual. Tapi teras anjeun kedah ngapdet sahenteuna sakali sataun (atanapi langkung sering, sapertos dina "jolly" 2020), kalayan ati-ati lebet sadayana sabtu minggu, transfer sareng dinten henteu damel anu diciptakeun ku pamaréntah urang. Lajeng ngulang prosedur ieu unggal taun hareup. Bosenan.
Kumaha upami rada gélo sareng ngadamel kalénder pabrik "perpetual" dina Excel? Hiji anu ngamutahirkeun sorangan, nyokot data tina Internet sarta salawasna ngahasilkeun daptar up-to-date poé non-kerja pikeun pamakéan saterusna dina itungan naon? Ngagoda?
Jang ngalampahkeun ieu, kanyataanna, teu pisan hésé.
Sumber data
Patarosan utama nyaéta dimana kéngingkeun data? Pikeun milarian sumber anu cocog, kuring ngalangkungan sababaraha pilihan:
- Kaputusan asli diterbitkeun dina situs wéb pamaréntah dina format PDF (di dieu, salah sahijina, contona) sareng langsung ngaleungit - inpormasi anu mangpaat henteu tiasa ditarik kaluar.
- Hiji pilihan pikabitaeun, dina glance kahiji, sigana "Open Data Portal of Féderasi", dimana aya set data pakait, tapi, sanggeus ujian ngadeukeutan, sagalana tétéla hanjelu. Situs éta teu pikaresepeun pisan pikeun ngimpor kana Excel, dukungan téknis henteu ngaréspon (ngasingkeun diri?), Sareng datana nyalira tos lami di dinya - kalénder produksi pikeun 2020 terakhir diropéa dina bulan Nopémber 2019 (aib!) , tangtosna, henteu ngandung "coronavirus' kami sareng sabtu minggu 'voting' taun 2020, contona.
Kuciwa ku sumber resmi, kuring mimiti ngagali anu henteu resmi. Aya loba di antarana dina Internét, tapi kalobaannana, deui, sagemblengna teu cocog pikeun importing kana Excel tur masihan kaluar kalénder produksi dina bentuk gambar geulis. Tapi sanés pikeun urang ngagantung dina témbok, sanés?
Sareng dina prosés milarian, hiji hal anu éndah teu kahaja kapanggih - situs http://xmlcalendar.ru/
Tanpa perlu "frills", situs basajan, lampu jeung gancang, diasah pikeun hiji tugas - pikeun masihan dulur kalénder produksi pikeun sataun dipikahoyong dina format XML. alus teuing!
Upami, ujug-ujug, anjeun henteu terang, maka XML mangrupikeun format téks sareng eusi anu ditandaan khusus
Ngan bisi, kuring ngahubungi pangarang loka sarta aranjeunna dikonfirmasi yén situs geus eksis pikeun 7 taun, data dina eta terus diropéa (aranjeunna malah boga cabang on github pikeun ieu) jeung maranéhna teu bade nutup eta. Sareng kuring henteu kapikiran yén anjeun sareng kuring ngamuat data tina éta pikeun proyék-proyék sareng itungan kami dina Excel. Gratis. Ieu nice uninga yén aya kénéh jalma kawas kieu! Hormat!
Tetep ngamuat data ieu kana Excel nganggo tambihan Power Query (pikeun vérsi Excel 2010-2013 tiasa diunduh gratis tina situs wéb Microsoft, sareng dina vérsi Excel 2016 sareng anu langkung énggal éta parantos dipasang sacara standar. ).
Logika tindakan bakal kieu:
- Kami nyuhunkeun pikeun ngaunduh data tina situs éta salami sataun
- Ngarobih pamundut urang kana fungsi
- Urang nerapkeun pungsi ieu kana daptar sadaya taun sadia, mimitian ti 2013 jeung nepi ka taun ayeuna - sarta kami meunang kalénder produksi "perpetual" kalawan apdet otomatis. Voila!
Lengkah 1. Impor kalénder pikeun sataun
Mimiti, muatkeun kalénder produksi kanggo sataun mana waé, contona, pikeun 2020. Jang ngalampahkeun ieu, dina Excel, buka tab data (atawa Patarosan Kakuatanlamun dipasang salaku tambihan-on misah) tur pilih Tina Internét (Ti Wéb). Dina jandéla anu muka, témpélkeun tautan ka taun anu cocog, ditiron tina situs:
Sanggeus ngaklik OK jandela tilik nembongan, dimana anjeun kedah klik tombol Ngarobah Data (Transformasi data) or Pikeun ngarobah data (Édit data) sarta kami bakal meunang ka Power Query jandela redaktur query, dimana urang bakal neruskeun gawé bareng data:
Langsung anjeun tiasa ngahapus aman dina panel katuhu Paraméter Nyungkeun (Setélan pamundut) lengkah tipe dirobah (Tipe Robah) Kami henteu peryogi anjeunna.
Tabél dina kolom libur ngandung kodeu sareng déskripsi ngeunaan dinten-dinten henteu damel - anjeun tiasa ningali eusina ku "ngaliwatan" dua kali ku ngaklik kecap héjo. meja:
Pikeun uih deui, anjeun kedah ngahapus dina panel katuhu sadaya léngkah anu parantos muncul deui sumber (Sumber).
Tabél kadua, anu tiasa diaksés ku cara anu sami, ngandung persis naon anu urang peryogikeun - tanggal sadaya dinten henteu damel:
Tetep ngolah piring ieu, nyaéta:
1. Saring ngan tanggal libur (ie leuwih) ku kolom kadua Atribut: t
2. Pupus sadaya kolom kecuali anu kahiji - ku ngaklik katuhu dina judul kolom kahiji sareng milih paréntahna Pupus kolom séjén (Pupus Kolom Lain):
3. Bagikeun kolom kahiji ku titik sacara misah pikeun bulan sareng dinten kalayan paréntah Kolom pamisah - Ku Delimiter tab transformasi (Transformasi - Pisah kolom - Ku delimiter):
4. Sarta pamustunganana nyieun kolom diitung kalawan kaping normal. Jang ngalampahkeun ieu, dina tab Nambahkeun kolom pencét dina tombol kolom custom (Tambahkeun Kolom - Kolom Khusus) sareng lebetkeun rumus ieu dina jandela anu nembongan:
=#tanggal(2020, [#»Atribut:d.1″], [#»Atribut:d.2″])
Di dieu, operator #date gaduh tilu argumen: taun, bulan, sareng dinten masing-masing. Saatos ngaklik OK kami nampi kolom anu diperyogikeun sareng tanggal sabtu minggu normal, sareng ngahapus kolom sésana sapertos dina léngkah 2
Lengkah 2. Ngahurungkeun pamundut kana fungsi
Tugas kami salajengna nyaéta ngarobih pamundut anu diciptakeun pikeun 2020 janten fungsi universal pikeun taun naon waé (nomer taun bakal janten argumenna). Jang ngalampahkeun ieu, urang ngalakukeun di handap:
1. Ngalegaan (upami teu acan dimekarkeun) panel inquiries (Patanyaan) di kénca dina jandela Power Query:
2. Saatos ngarobih pamundut ka fungsi, kamampuan ningali léngkah-léngkah anu nyusun pamundut sareng gampang ngéditna, hanjakalna, ngaleungit. Ku alatan éta, ngajadikeun rasa nyieun salinan pamundut urang jeung frolic geus kalawan eta, sarta ninggalkeun aslina dina cadangan. Jang ngalampahkeun ieu, klik katuhu dina jandela kénca on pamundut kalénder urang tur pilih paréntah Duplikat.
Ngaklik katuhu deui dina salinan almenak (2) bakal milih paréntah ngaganti ngaran (Ganti ngaran) sareng lebetkeun nami énggal - antosan, contona, fxTaun:
3. Kami muka kode sumber query dina basa Power Query internal (sacara ringkes disebut "M") nganggo paréntah. Éditor canggih tab tingalian(Témbongkeun - Éditor Canggih) sarta jieun perobahan leutik di dinya pikeun ngarobah pamundut urang kana fungsi pikeun taun mana wae.
Tadina:
saatos:
Upami anjeun resep kana detilna, teras di dieu:
- (taun salaku angka) => - urang nyatakeun yén fungsi urang bakal gaduh hiji argumen numerik - variabel taun
- Nempelkeun variabel taun kana tautan wéb dina léngkah sumber. Kusabab Power Query teu ngidinan Anjeun pikeun lem angka na téks, urang ngarobah angka taun kana téks dina laleur ngagunakeun fungsi. Number.ToText
- Kami ngagantikeun variabel taun pikeun 2020 dina léngkah penultimate #"Tambihkeun objék khusus«, dimana urang ngawangun tanggal tina fragmen.
Sanggeus ngaklik ngabereskeun pamundut urang janten fungsi:
Lengkah 3. Impor almenak pikeun sakabéh taun
Hal terakhir anu tinggaleun nyaéta ngadamel pamundut utama anu terakhir, anu bakal unggah data pikeun sadaya taun anu sayogi sareng nambihan sadaya tanggal liburan anu ditampi kana hiji méja. Kanggo ieu:
1. Urang klik dina panel query kénca dina spasi kosong abu kalayan tombol mouse katuhu tur pilih sequentially Paménta anyar - Sumber séjén - Paménta kosong (Parios Anyar - Tina sumber sanés - Paménta kosong):
2. Urang kedah ngahasilkeun daptar sadaya taun dimana urang bakal nyuhunkeun kalénder, nyaéta 2013, 2014 ... 2020. Jang ngalampahkeun ieu, dina bar rumus tina query kosong anu muncul, lebetkeun paréntah:
struktur:
={JumlahA..JumlahB}
... dina Power Query ngahasilkeun daptar integer ti A ka B. Contona, éksprési
= {1..5}
... bakal ngahasilkeun daptar 1,2,3,4,5.
Nya, supados henteu kabeungkeut ka 2020, kami nganggo fungsina DateTime.LocalNow() - analog tina fungsi Excel Dinten ieu (POE) dina Power Query - sareng sari tina éta, giliran taun ayeuna ku fungsina Tanggal.Tahun.
3. Hasil set taun, sanajan kasampak cukup nyukupan, sanes tabel pikeun Power Query, tapi hiji objek husus - daptar (Daptar). Tapi ngarobah kana tabel teu jadi masalah: ngan klik tombol Ka méja (Ka Méja) di belah kénca juru luhur:
4. Garis bérés! Ngalarapkeun fungsi anu urang jieun tadi fxTaun kana daptar hasil taun. Jang ngalampahkeun ieu, dina tab Nambahkeun kolom pencét tombol Telepon fungsi custom (Tambahkeun Kolom - Ngajantenkeun Fungsi Kustom) tur nyetel hijina argumen - kolom Column1 leuwih taun:
Sanggeus ngaklik OK fungsi urang fxTaun impor bakal dianggo dina gilirannana pikeun tiap taun sarta kami bakal meunang kolom dimana unggal sél bakal ngandung hiji méja kalayan tanggal poé non-kerja (eusi tabel jelas katempo lamun klik dina latar tukang sél gigireun kecap meja):
Tetep dilegakeun eusi tabel nested ku ngaklik ikon sareng panah ganda dina lulugu kolom kaping (ceuk Paké ngaran kolom aslina salaku awalan bisa dihapus):
… sareng saatos ngaklik OK urang kéngingkeun naon anu dipikahoyong - daptar sadaya liburan ti 2013 dugi ka taun ayeuna:
Kahiji, kolom geus teu perlu, bisa dihapus, sarta kadua, nyetel tipe data kaping (Kaping) dina daptar dropdown dina judul kolom:
The query sorangan bisa diganti ngaranna hal leuwih bermakna ti Paménta1 lajeng unggah hasilna kana lambaran dina wangun tabel dinamis "pinter" maké paréntah nutup tur ngundeur tab imah (Imah - Tutup & Muat):
Anjeun tiasa ngamutahirkeun kalénder anu diciptakeun dina mangsa nu bakal datang ku ngaklik katuhu dina méja atawa query dina jandela katuhu ngaliwatan paréntah. Apdet & Simpen. Atawa ngagunakeun tombol Refresh sadayana tab data (Kaping - Refresh Sadaya) atawa potong kompas keyboard Ctrl+Alt+F5.
Éta hungkul.
Ayeuna anjeun henteu kedah deui nyéépkeun waktos sareng bahan bakar pikeun milarian sareng ngapdet daptar liburan - ayeuna anjeun gaduh kalénder produksi "perpetual". Dina sagala hal, salami pangarang situs http://xmlcalendar.ru/ ngarojong turunan maranéhanana, nu, abdi miharep, bakal pikeun lila pisan (hatur nuhun ka aranjeunna deui!).
- Impor laju bitcoin pikeun unggul tina internét ngalangkungan Power Query
- Milarian dinten bisnis salajengna nganggo fungsi WORKDAY
- Manggihan simpang tina interval tanggal