Tanggalan pabrik ing Excel

Tanggalan produksi, yaiku dhaptar tanggal, ing ngendi kabeh dina kerja lan preian resmi ditandhani - iku pancen perlu kanggo pangguna Microsoft Excel. Ing laku, sampeyan ora bisa nindakake tanpa:

• ing petungan akuntansi (gaji, suwene layanan, preian ...)
• ing logistik - kanggo nemtokake wektu pangiriman sing bener, njupuk akhir minggu lan preian (elinga klasik "teka sawise preian?")
  
• ing manajemen proyek - kanggo ngira-ngira istilah sing bener, njupuk menyang akun, maneh, dina kerja-non-kerja
• sembarang nggunakake fungsi kaya KERJA (WORKDAY) or PEKERJA MURNI (NETWORKDAYS), amarga padha mbutuhake dhaptar preian minangka argumentasi
• nalika nggunakake fungsi Time Intelligence (kayata TTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, lsp.) ing Power Pivot lan Power BI
  
• … etc. etc. – akeh contone.
  

Luwih gampang kanggo wong sing kerja ing sistem ERP perusahaan kayata 1C utawa SAP, amarga tanggalan produksi wis dibangun. Nanging kepiye pangguna Excel?

Sampeyan bisa, mesthi, nyimpen tanggalan kuwi kanthi manual. Nanging sampeyan kudu nganyari paling ora setaun (utawa luwih asring, kaya ing "jolly" 2020), kanthi ati-ati mlebu kabeh akhir minggu, transfer lan dina ora kerja sing diciptakake dening pamrentah kita. Banjur baleni prosedur iki saben taun sabanjuré. Bosen.

Kepiye carane dadi rada edan lan nggawe tanggalan pabrik "langgeng" ing Excel? Siji sing nganyari dhewe, njupuk data saka Internet lan tansah njedulake dhaftar up-to-date dina non-kerja kanggo nggunakake sakteruse ing petungan sembarang? nggodha?

Kanggo nindakake iki, nyatane, ora angel.

Sumber data

Pitakonan utama yaiku ing ngendi entuk data? Kanggo nggoleki sumber sing cocog, aku ngliwati sawetara pilihan:

• Dekrit asli diterbitake ing situs web pemerintah ing format PDF (kene, salah sijine, umpamane) lan langsung ilang - informasi sing migunani ora bisa ditarik metu.
• 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.

Disillusioned karo sumber resmi, aku wiwit ndudhuk sing ora resmi. Ana akeh sing ana ing Internet, nanging umume, maneh, ora cocog kanggo ngimpor menyang Excel lan menehi tanggalan produksi ing bentuk gambar sing apik. Nanging ora kanggo kita nggantung ing tembok, ta?

Lan ing proses telusuran, ana sing ora sengaja ditemokake - situs http://xmlcalendar.ru/

Tanpa "embel-embel" sing ora perlu, situs sing prasaja, entheng lan cepet, diasah kanggo siji tugas - kanggo menehi kabeh tanggalan produksi kanggo taun sing dikarepake ing format XML. Banget!

Yen, dumadakan, sampeyan ora ngerti, banjur XML minangka format teks kanthi isi sing ditandhani khusus . Entheng, trep lan bisa diwaca dening umume program modern, kalebu Excel.

Yen ngono, aku ngubungi penulis situs kasebut lan dheweke ngonfirmasi manawa situs kasebut wis ana 7 taun, data kasebut terus dianyari (malah duwe cabang ing github kanggo iki) lan dheweke ora bakal nutup. Lan aku ora mikir yen sampeyan lan aku mbukak data saka iku kanggo proyek lan petungan ing Excel. Gratis. Iku apik kanggo ngerti sing isih ana wong kaya iki! Respect!

Iku tetep kanggo mbukak data iki menyang Excel nggunakake Power Query add-in (kanggo versi Excel 2010-2013 bisa diundhuh gratis saka situs web Microsoft, lan ing versi Excel 2016 lan anyar wis dibangun ing standar. ).

Logika tumindak bakal kaya ing ngisor iki:

1. We nggawe panjalukan kanggo ndownload data saka situs kanggo siji taun
2. Nguripake panjaluk kita dadi fungsi
3. Kita aplikasi fungsi iki kanggo dhaptar kabeh taun kasedhiya, wiwit saka 2013 lan nganti taun saiki - lan kita njaluk "langgeng" tanggalan produksi karo nganyari otomatis. Voila!
   

Langkah 1. Impor tanggalan kanggo setahun

Pisanan, muat tanggalan produksi sajrone setaun, umpamane, kanggo 2020. Kanggo nindakake iki, ing Excel, pindhah menyang tab Data (utawa Pitakonan Dayayen sampeyan nginstal minangka tambahan kapisah) banjur pilih Saka Internet (Saka Web). Ing jendhela sing mbukak, tempel link menyang taun sing cocog, disalin saka situs kasebut:

Sawise ngeklik OK jendhela pratinjau katon, ing ngendi sampeyan kudu klik tombol Ngonversi Data (Transformasi data) or Kanggo ngganti data (Sunting data) lan kita bakal tekan jendhela editor query query Power, ing ngendi kita bakal terus nggarap data:

Langsung sampeyan bisa mbusak kanthi aman ing panel tengen Parameter Panjaluk (Setelan pitakon) langkah jinis dipunéwahi (Jenis sing diganti) Kita ora butuh dheweke.

Tabel ing kolom preian ngemot kode lan katrangan babagan dina ora kerja - sampeyan bisa ndeleng isine kanthi "mudhun" kaping pindho kanthi ngeklik tembung ijo Tabel:

Kanggo bali, sampeyan kudu mbusak ing panel tengen kabeh langkah sing wis katon maneh sumber (Sumber).

Tabel kapindho, sing bisa diakses kanthi cara sing padha, ngemot persis apa sing kita butuhake - tanggal kabeh dina sing ora kerja:

Iku tetep kanggo ngolah piring iki, yaiku:

1. Nyaring mung tanggal preian (yaiku tanggal) miturut kolom kapindho Atribut: t

2. Mbusak kabeh kolom kajaba sing pisanan - kanthi ngeklik tengen ing judhul kolom pisanan lan milih printah kasebut Mbusak kolom liyane (Busak Kolom Liyane):

3. Pisah kolom pisanan kanthi titik kanthi kapisah kanggo sasi lan dina kanthi printah Pisah Column - Miturut Delimiter tab transformasi (Transform - Pisah kolom - Miturut delimiter):

4. Lan pungkasane nggawe kolom sing diwilang kanthi tanggal normal. Kanggo nindakake iki, ing tab Nambah kolom klik ing tombol Kolom khusus (Tambah Kolom - Kolom Kustom) lan ketik rumus ing ngisor iki ing jendhela sing katon:

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

Ing kene, operator #date duwe telung argumen: taun, sasi, lan dina. Sawise ngeklik OK kita entuk kolom sing dibutuhake karo tanggal akhir minggu normal, lan mbusak kolom sing isih ana kaya ing langkah 2

Langkah 2. Nguripake request menyang fungsi

Tugas sabanjure yaiku ngowahi pitakon sing digawe kanggo 2020 dadi fungsi universal kanggo taun apa wae (nomer taun bakal dadi argumen). Kanggo nindakake iki, kita nindakake ing ngisor iki:

1. Ngembangake (yen durung ditambahi) panel Enquiries (Pitakon) ing sisih kiwa ing jendela Power Query:

2. Sawise ngowahi panjalukan menyang fungsi, kemampuan kanggo ndeleng langkah-langkah sing nggawe panjalukan lan gampang nyunting, sayangé, ilang. Mulane, iku ndadekake pangertèn kanggo nggawe salinan request kita lan frolic wis karo, lan ninggalake asli ing cadangan. Kanggo nindakake iki, klik-tengen ing panel kiwa ing panyuwunan tanggalan lan pilih printah Duplikat.

Klik-tengen maneh ing salinan asil tanggalan (2) bakal milih printah Ganti jeneng (Ganti jeneng) lan ketik jeneng anyar - umpamane, fxTaun:

3. Kita mbukak kode sumber pitakon ing basa Power Query internal (sing diarani "M") nggunakake perintah kasebut. Editor Lanjut tab review(Deleng - Editor Lanjut) lan nggawe owah-owahan cilik ana kanggo nguripake request kita menyang fungsi kanggo taun sembarang.

Yaiku:

Sawise:

Yen sampeyan kasengsem ing rincian, banjur kene:

• (taun minangka nomer) =>  - kita ngumumake yen fungsi kita bakal duwe siji argumen numerik - variabel taun
• Nempelake variabel taun kanggo link web ing langkah sumber. Amarga Power Query ora ngidini sampeyan nglebokake nomer lan teks, kita ngowahi nomer taun dadi teks kanthi cepet nggunakake fungsi kasebut. Nomer.ToTeks
• Kita ngganti variabel taun kanggo 2020 ing langkah penultimate #"Nambahake obyek khusus«, ngendi kita kawangun tanggal saka pecahan.
  

Sawise ngeklik Rampung panyuwunan kita dadi fungsi:

Langkah 3. Impor tanggalan kanggo kabeh taun

Sing terakhir yaiku nggawe pitakon utama pungkasan, sing bakal ngunggah data kanggo kabeh taun sing kasedhiya lan nambah kabeh tanggal liburan sing ditampa dadi siji tabel. Kanggo iki:

1. Kita klik ing panel query kiwa ing papan kosong abu-abu karo tombol mouse tengen lan pilih sequentially Panjaluk anyar - Sumber liyane - Panjaluk kosong (Panjaluk Anyar - Saka sumber liya - Pitakonan kosong):

2. Kita kudu nggawe dhaptar kabeh taun sing bakal dijaluk tanggalan, yaiku 2013, 2014 ... 2020. Kanggo nindakake iki, ing garis rumus pitakon kosong sing katon, ketik printah:

Struktur:

={NumberA..NumberB}

... ing Power Query ngasilake dhaptar integer saka A nganti B. Contone, ekspresi

= {1..5}

... bakal ngasilake dhaptar 1,2,3,4,5.

Ya, supaya ora diikat kanthi kaku ing 2020, kita nggunakake fungsi kasebut DateTime.LocalNow() - analog saka fungsi Excel TODAY (DINI) ing Power Query - lan extract saka iku, ing siji, taun saiki dening fungsi Tanggal.Taun.

3. Set taun sing diasilake, sanajan katon cukup nyukupi, dudu tabel kanggo Power Query, nanging obyek khusus - dhaftar (Daftar). Nanging Ngonversi menyang Tabel ora masalah: mung klik tombol Kanggo meja (Kanggo Tabel) ing pojok kiwa ndhuwur:

4. Garis rampung! Nglamar fungsi sing digawe sadurunge fxTaun menyang dhaptar asil taun. Kanggo nindakake iki, ing tab Nambah kolom penet tombol Telpon fungsi khusus (Tambah Kolom - Nyetel Fungsi Kustom) lan nyetel argumen mung - kolom Column1 liwat taun:

Sawise ngeklik OK fungsi kita fxTaun impor bakal mlaku saben taun lan kita bakal entuk kolom ing ngendi saben sel bakal ngemot tabel kanthi tanggal dina sing ora bisa digunakake (isi tabel katon kanthi jelas yen sampeyan ngeklik ing latar mburi sel ing jejere tembung Tabel):

Iku tetep kanggo nggedhekake isi tabel nested kanthi ngeklik ing lambang karo panah pindho ing header kolom tanggal (tik Gunakake jeneng kolom asli minangka awalan iku bisa dibusak):

… lan sawise ngeklik OK kita entuk apa sing dikarepake - dhaptar kabeh preian saka 2013 nganti taun saiki:

Pisanan, kolom sing wis ora perlu, bisa dibusak, lan sing kapindho, nyetel jinis data tanggal (Tanggal) ing dhaptar gulung mudhun ing judhul kolom:

Pitakonan kasebut bisa diganti jeneng sing luwih penting tinimbang Panyuwunan1 banjur upload asil menyang sheet ing wangun tabel dinamis "pinter" nggunakake printah nutup lan download tab Home (Ngarep - Tutup & Muat):

Sampeyan bisa nganyari tanggalan sing digawe ing mangsa ngarep kanthi ngeklik tengen ing meja utawa pitakon ing panel tengen liwat printah Nganyari & Simpen. Utawa nggunakake tombol Refresh kabeh tab Data (Tanggal - Refresh Kabeh) utawa trabasan keyboard Ctrl+Alt+F5.

Sing kabeh.

Saiki sampeyan ora perlu maneh mbuwang wektu lan mikir-bahan bakar nggoleki lan nganyari dhaptar preian - saiki sampeyan duwe tanggalan produksi "langgeng". Ing kasus apa wae, anggere penulis situs http://xmlcalendar.ru/ ndhukung turunane, sing, muga-muga, bakal suwe banget (matur nuwun maneh!).

• Ngimpor tarif bitcoin kanggo unggul saka internet liwat Power Query
• Temokake dina bisnis sabanjure nggunakake fungsi WORKDAY
  
• Nemokake persimpangan interval tanggal