Daptar eusi
Rumusan masalah
Hayu urang tingali solusi anu saé pikeun salah sahiji kaayaan standar anu paling sering disanghareupan ku pangguna Excel: anjeun kedah gancang sareng otomatis ngumpulkeun data tina sajumlah ageung file kana hiji méja ahir.
Anggap urang gaduh folder di handap ieu, anu ngandung sababaraha file sareng data ti kota cabang:
Jumlah file henteu masalah sareng tiasa robih ka hareup. Unggal file boga lambar ngaranna penjualandimana tabel data ayana:
Jumlah baris (urutan) dina tabél, tangtu, béda, tapi susunan kolom téh baku madhab.
Tugas: ngumpulkeun data tina sadaya file kana hiji buku sareng ngamutahirkeun otomatis saatosna nalika nambihan atanapi ngahapus file kota atanapi baris dina tabel. Numutkeun tabel konsolidasi final, mangka bakal mungkin mun ngawangun sagala laporan, tabel pangsi, data filter-diurutkeun, jsb Hal utama anu bisa ngumpulkeun.
Urang milih pakarang
Pikeun solusina, urang peryogi versi panganyarna tina Excel 2016 (fungsi anu dipikabutuh parantos diwangun kana éta sacara standar) atanapi versi sateuacana tina Excel 2010-2013 kalayan tambihan gratis anu dipasang. Patarosan Kakuatan ti Microsoft (unduh di dieu). Power Query mangrupikeun alat anu super fleksibel sareng super kuat pikeun ngamuat data kana Excel ti dunya luar, teras nyabut sareng ngolahna. Power Query ngadukung ampir sadaya sumber data anu aya - tina file téks ka SQL komo Facebook 🙂
Upami anjeun teu gaduh Excel 2013 atanapi 2016, maka anjeun moal tiasa maca langkung jauh (ngan ukur bercanda). Dina vérsi Excel anu langkung lami, tugas sapertos kitu ngan ukur tiasa dilaksanakeun ku program makro dina Visual Basic (anu hésé pisan pikeun pamula) atanapi ku cara niron manual anu monoton (anu peryogi waktos anu lami sareng ngahasilkeun kasalahan).
Lengkah 1. Impor hiji file salaku sampel
Mimiti, hayu urang ngimpor data tina hiji buku kerja sabagé conto, supados Excel "nyokot ideu". Jang ngalampahkeun ieu, jieun buku kerja kosong anyar sareng…
- upami anjeun gaduh Excel 2016, teras buka tabna data teras salajengna Jieun Query - Tina File - Tina Buku (Data - Paménta Anyar- Tina file - Tina Excel)
- upami anjeun gaduh Excel 2010-2013 sareng tambihan Power Query dipasang, teras buka tab Patarosan Kakuatan tur pilih di dinya Tina file - Tina buku (Ti file - Tina Excel)
Teras, dina jandela anu muka, angkat ka polder kami kalayan laporan sareng pilih salah sahiji file kota (henteu masalah anu mana, sabab sadayana khas). Saatos sababaraha detik, jandela Navigator kedah muncul, dimana anjeun kedah milih lambaran anu urang peryogikeun (Penjualan) di sisi kénca, sareng eusina bakal ditingalikeun di sisi katuhu:
Upami anjeun ngaklik tombol di pojok katuhu handap jandela ieu download (Muatan), teras tabél bakal langsung diimpor kana lambaran dina bentuk aslina. Pikeun file tunggal, ieu saé, tapi urang kedah ngamuat seueur file sapertos kitu, janten urang bakal rada béda sareng klik tombolna. hal nu dimenerkeun (Édit). Saatos éta, pangropéa pamundut Power Query kedah ditampilkeun dina jandela anu misah sareng data kami tina buku:
Ieu mangrupikeun alat anu kuat anu ngamungkinkeun anjeun "ngabéréskeun" méja kana pandangan anu urang peryogikeun. Malah katerangan deet sadaya fungsina bakal nyandak sakitar saratus halaman, tapi, upami sakedap, nganggo jandela ieu anjeun tiasa:
- nyaring kaluar data teu perlu, garis kosong, garis kalawan kasalahan
- nyortir data ku hiji atawa leuwih kolom
- nyingkirkeun pangulangan
- ngabagi téks caket ku kolom (ku delimiters, jumlah karakter, jsb)
- susun téks dina urutan (hapus spasi tambahan, kasus bener, jsb)
- ngarobah tipe data dina sagala cara mungkin (ngarobah angka kawas téks kana angka normal jeung sabalikna)
- transpose (muterkeun) tabel sarta dilegakeun dua diménsi cross-tabel jadi datar
- tambahkeun kolom tambahan kana tabél sareng nganggo rumus sareng fungsina nganggo basa M anu diwangun kana Power Query.
- ...
Salaku conto, hayu urang tambahkeun kolom kalayan nami téks bulan ka méja urang, supados engké bakal langkung gampang ngawangun laporan tabel pangsi. Jang ngalampahkeun ieu, klik katuhu dina judul kolom kapingtur pilih paréntah Kolom duplikat (Duplikat Kolom), teras klik-katuhu dina header kolom duplikat anu muncul sareng pilih Paréntah Transformasi - Bulan - Ngaran Bulan:
Kolom anyar kedah dibentuk kalayan nami téks bulan pikeun unggal baris. Ku ganda-klik dina judul kolom, Anjeun bisa ngaganti ngaran tina Tanggal salinan mun leuwih nyaman sasih, misalna.
Upami dina sababaraha kolom program éta henteu leres-leres ngakuan jinis data, anjeun tiasa ngabantosan ku ngaklik ikon format di sisi kénca unggal kolom:
Anjeun tiasa ngaluarkeun garis anu aya kasalahan atanapi garis kosong, kitu ogé manajer atanapi palanggan anu teu perlu, nganggo saringan saderhana:
Leuwih ti éta, sagala transformasi dipigawé dibereskeun dina panel katuhu, dimana maranéhna salawasna bisa digulung deui (cross) atawa ngarobah parameter maranéhanana (gear):
Lampu sareng elegan, sanés?
Lengkah 2. Hayu urang transformasi pamundut urang kana fungsi
Dina raraga salajengna ngulang sagala transformasi data dijieun pikeun tiap buku diimpor, urang kudu ngarobah pamundut urang dijieun kana fungsi, nu lajeng bakal dilarapkeun ka sakabéh file urang. Jang ngalampahkeun ieu sabenerna basajan pisan.
Dina Editor Query, buka tab View teras klik tombolna Éditor canggih (Témbongkeun - Éditor Canggih). Jandéla kedah dibuka dimana sadaya tindakan urang sateuacana bakal ditulis dina bentuk kode dina basa M. Punten dicatet yén jalur ka file anu kami impor pikeun conto ieu hardcoded dina kode:
Ayeuna hayu urang ngadamel sababaraha pangaluyuan:
Harti maranéhanana basajan: baris kahiji (filepath) => ngarobah prosedur urang kana fungsi kalawan argumen filepath, sarta di handap urang ngarobah jalur tetep kana nilai variabel ieu.
Sadayana. Klik dina ngabereskeun sareng kedah ningali ieu:
Tong sieun yén datana parantos ngaleungit - kanyataanna, sadayana OKÉ, sadayana kedah sapertos kieu 🙂 Kami parantos suksés nyiptakeun fungsi khusus urang, dimana sadayana algoritma pikeun ngimpor sareng ngolah data émut tanpa dihijikeun kana file khusus. . Tetep masihan nami anu langkung kaharti (contona getData) dina panel katuhu dina widang ngaran hareup jeung anjeun bisa metik Imah - Tutup sareng unduh (Imah - Tutup sareng Muat). Punten dicatet yén jalur ka file anu kami impor pikeun conto ieu hardcoded dina kode. Anjeun bakal uih deui ka jandela utama Microsoft Excel, tapi panel sareng sambungan anu diciptakeun kana fungsi urang kedah muncul di sisi katuhu:
Lengkah 3. Ngumpulkeun kabéh payil
Sadaya bagian anu paling hese aya di tukangeun, bagian anu pikaresepeun sareng gampang tetep. Pindah ka tab Data - Jieun Query - Tina File - Tina Polder (Data - Paménta Anyar - Tina file - Tina folder) atawa, lamun boga Excel 2010-2013, sarua jeung tab Patarosan Kakuatan. Dina jandela anu nembongan, sebutkeun polder dimana sadaya file kota sumber urang aya teras klik OK. Léngkah salajengna kedah muka jandela dimana sadaya file Excel anu aya dina folder ieu (sareng subfolderna) sareng detil pikeun masing-masing bakal didaptarkeun:
Klik ngarobah (Édit) jeung deui urang asup kana jandela editor query akrab.
Ayeuna urang kedah nambihan kolom sanés kana méja kami kalayan fungsi anu didamel, anu bakal "narik" data tina unggal file. Jang ngalampahkeun ieu, buka tab Tambahkeun Kolom - Kolom Adat (Tambahkeun Kolom - Tambihkeun Kolom Adat) sarta dina jandela nu nembongan, asupkeun fungsi urang getData, nangtukeun pikeun eta salaku argumen jalur pinuh pikeun tiap file:
Sanggeus ngaklik OK kolom dijieun kudu ditambahkeun kana tabel kami di katuhu.
Ayeuna hayu urang mupus sadaya kolom anu teu perlu (sapertos dina Excel, nganggo tombol beurit katuhu - mindahkeun), ngan nyésakeun kolom anu ditambahan sareng kolom kalayan nami file, sabab nami ieu (leuwih tepatna, kota) bakal aya mangpaatna dina total data pikeun unggal baris.
Sareng ayeuna "wow moment" - klik dina ikon kalayan panah sorangan di pojok katuhu luhur kolom anu ditambah kalayan fungsi kami:
… pupus centang Paké ngaran kolom aslina salaku awalan (Paké ngaran kolom aslina salaku awalan)teras klik OK. Sareng fungsi kami bakal ngamuat sareng ngolah data tina unggal file, nuturkeun algoritma anu dirékam sareng ngumpulkeun sadayana dina méja umum:
Pikeun kaéndahan lengkep, anjeun ogé tiasa nyabut ekstensi .xlsx tina kolom kahiji kalayan nami file - ku ngagantian standar sareng "euweuh" (klik katuhu dina lulugu kolom - ngagentos) jeung ngaganti ngaran kolom ieu jadi parasaan. Sareng ogé ngabenerkeun format data dina kolom sareng tanggalna.
Sadayana! Klik dina Imah - Tutup sareng Beban (Imah - Tutup & Muat). Sadaya data anu dikumpulkeun ku pamundut pikeun sadaya kota bakal diunggah kana lambaran Excel anu ayeuna dina format "tabel pinter":
Sambungan anu diciptakeun sareng fungsi rakitan urang henteu kedah disimpen sacara misah dina cara naon waé - aranjeunna disimpen babarengan sareng file anu ayeuna ku cara biasa.
Dina mangsa nu bakal datang, kalayan sagala parobahan dina folder (nambahkeun atawa miceun kota) atawa dina file (ngarobah jumlah garis), éta bakal cukup pikeun klik katuhu langsung dina tabel atawa dina pamundut dina panel katuhu tur pilih paréntah Apdet & Simpen (Refresh) - Power Query bakal "ngawangun deui" sadaya data dina sababaraha detik.
PS
Amandemen. Saatos apdet Januari 2017, Power Query diajar kumaha carana ngumpulkeun buku kerja Excel nyalira, nyaéta henteu kedah ngadamel fungsi anu misah deui - éta bakal otomatis. Janten, léngkah kadua tina tulisan ieu henteu diperyogikeun deui sareng sadayana prosésna janten langkung saderhana:
- milih Jieun Request - Ti File - Ti Polder - Pilih Polder - OK
- Saatos daptar file nembongan, pencét ngarobah
- Dina jandela Query Editor, dilegakeun kolom Binary kalayan panah ganda sareng pilih nami lambar anu bakal dicandak tina unggal file.
Sareng éta sadayana! Lagu!
- Redesign tina crosstab kana hiji datar cocog pikeun ngawangun tabel pangsi
- Ngawangun bagan gelembung animasi dina Power View
- Macro pikeun ngumpul lambaran tina file Excel anu béda jadi hiji