Paragraf
Formulasi masalah
Ayo goleki solusi sing apik kanggo salah sawijining kahanan standar sing umume pangguna Excel diadhepi cepet utawa mengko: sampeyan kudu kanthi cepet lan kanthi otomatis ngumpulake data saka akeh file menyang siji tabel pungkasan.
Contone, kita duwe folder ing ngisor iki, sing ngemot sawetara file kanthi data saka kutha cabang:
Jumlah file ora masalah lan bisa diganti ing mangsa ngarep. Saben file duwe sheet jenenge Salesing ngendi tabel data dumunung:
Jumlah larik (pesenan) ing tabel, mesthi, beda, nanging set kolom standar nang endi wae.
Tugas: kanggo ngumpulake data saka kabeh file menyang siji buku kanthi nganyari otomatis sakteruse nalika nambah utawa mbusak file kutha utawa larik ing tabel. Miturut tabel gabungan final, banjur bakal bisa kanggo mbangun laporan apa wae, tabel poros, data filter-urut, lan liya-liyane. Sing utama yaiku bisa ngumpulake.
Kita milih senjata
Kanggo solusi kasebut, kita butuh versi paling anyar saka Excel 2016 (fungsi sing dibutuhake wis dibangun kanthi standar) utawa versi Excel 2010-2013 sadurunge kanthi tambahan gratis sing diinstal. Pitakonan Daya saka Microsoft (download kene). Power Query minangka alat sing fleksibel lan super kuat kanggo ngunggah data menyang Excel saka njaba, banjur ngudani lan ngolah. Power Query ndhukung meh kabeh sumber data sing ana - saka file teks menyang SQL lan malah Facebook 🙂
Yen sampeyan ora duwe Excel 2013 utawa 2016, sampeyan ora bisa maca luwih lanjut (mung guyon). Ing versi Excel lawas, tugas kasebut mung bisa ditindakake kanthi program makro ing Visual Basic (sing angel banget kanggo pamula) utawa kanthi nyalin manual sing monoton (sing butuh wektu suwe lan nggawe kesalahan).
Langkah 1. Impor siji file minangka sampel
Pisanan, ayo ngimpor data saka siji buku kerja minangka conto, supaya Excel "njupuk ide". Kanggo nindakake iki, gawe buku kerja kosong anyar lan…
- yen sampeyan duwe Excel 2016, banjur bukak tab Data banjur Gawe Pitakonan - Saka File - Saka Buku (Data - Pitakonan Anyar- Saka file - Saka Excel)
- yen sampeyan duwe Excel 2010-2013 kanthi tambahan Power Query diinstal, banjur bukak tab Pitakonan Daya lan pilih ing Saka file - Saka buku (Saka file - Saka Excel)
Banjur, ing jendhela sing mbukak, pindhah menyang folder kita kanthi laporan lan pilih file kutha (ora ketompo sing endi, amarga kabeh iku khas). Sawise sawetara detik, jendhela Navigator kudu katon, ing ngendi sampeyan kudu milih sheet sing dibutuhake (Penjualan) ing sisih kiwa, lan isine bakal ditampilake ing sisih tengen:
Yen sampeyan ngeklik tombol ing pojok tengen ngisor jendela iki download (Muat), banjur tabel bakal langsung diimpor menyang sheet ing wangun asli. Kanggo file siji, iki apik, nanging kita kudu mbukak akeh file kasebut, supaya kita bakal rada beda lan klik tombol kasebut. Koreksi (Edit). Sawise iku, editor pitakon Power Query kudu ditampilake ing jendela sing kapisah karo data saka buku kasebut:
Iki alat banget kuat sing ngijini sampeyan kanggo "rampung" meja kanggo tampilan kita kudu. Malah gambaran sing entheng babagan kabeh fungsine bakal njupuk kira-kira satus kaca, nanging, yen sedhela, nggunakake jendhela iki sampeyan bisa:
- nyaring data sing ora perlu, garis kosong, garis kanthi kasalahan
- ngurutake data kanthi siji utawa luwih kolom
- nyingkirake ulangan
- dibagi teks lengket miturut kolom (dening pembatas, jumlah karakter, lsp.)
- atur teks (mbusak spasi ekstra, huruf cilik sing bener, lsp.)
- Ngonversi jinis data kanthi cara apa wae (ngowahi nomer kaya teks dadi nomer normal lan kosok balene)
- transpose (puteran) tabel lan nggedhekake loro-dimensi salib-tabel menyang flat
- nambah kolom tambahan kanggo meja lan nggunakake rumus lan fungsi ing wong nggunakake basa M dibangun menyang Power Query.
- ...
Contone, ayo nambah kolom kanthi jeneng teks sasi menyang tabel, supaya mengko luwih gampang kanggo nggawe laporan tabel pivot. Kanggo nindakake iki, klik-tengen ing judhul kolom tanggallan pilih printah Kolom duplikat (Kolom Duplikat), banjur klik-tengen ing header kolom duplikat sing katon banjur pilih Commands Transform - Wulan - Jeneng Wulan:
Kolom anyar kudu dibentuk kanthi jeneng teks sasi kanggo saben baris. Kanthi ngeklik kaping pindho ing judhul kolom, sampeyan bisa ngganti jeneng saka Tanggal salinan kanggo luwih nyaman Wulan, contone
Yen ing sawetara kolom, program kasebut ora ngerteni jinis data kanthi bener, mula sampeyan bisa mbantu kanthi ngeklik lambang format ing sisih kiwa saben kolom:
Sampeyan bisa ngilangi garis kanthi kesalahan utawa garis kosong, uga manajer utawa pelanggan sing ora perlu, nggunakake saringan sing prasaja:
Kajaba iku, kabeh transformasi sing dileksanakake dipasang ing panel tengen, ing ngendi bisa digulung maneh (salib) utawa ngganti paramèter (gear):
Cahya lan elegan, ta?
Langkah 2. Ayo dadi ndandani request kita menyang fungsi
Supaya bisa mbaleni kabeh transformasi data sing digawe kanggo saben buku sing diimpor, kita kudu ngowahi panjaluk sing digawe dadi fungsi, sing banjur bakal ditrapake, ing kabeh file. Kanggo nindakake iki pancen prasaja banget.
Ing Editor Query, pindhah menyang tab View banjur klik tombol Editor Lanjut (Deleng - Editor Lanjut). Jendhela kudu mbukak ing ngendi kabeh tumindak sadurunge bakal ditulis ing wangun kode ing basa M. Wigati dimangerteni manawa path menyang file sing diimpor kanggo conto kasebut hardcoded ing kode:
Saiki ayo nggawe sawetara pangaturan:
Tegesipun prasaja: baris pisanan (filepath) => ngowahi prosedur kita dadi fungsi kanthi argumen filepath, lan ing ngisor iki kita ngganti path tetep menyang nilai variabel iki.
Kabeh. Klik ing Rampung lan kudu ndeleng iki:
Aja wedi yen data wis ilang - nyatane, kabeh OK, kabeh kudu katon kaya iki 🙂 Kita wis kasil nggawe fungsi khusus, ing ngendi kabeh algoritma kanggo ngimpor lan ngolah data dieling-eling tanpa diikat menyang file tartamtu. . Iku tetep kanggo menehi jeneng sing luwih bisa dingerteni (contone getData) ing panel sisih tengen ing lapangan jeneng ngarep lan sampeyan bisa reap Ngarep - Nutup lan download (Ngarep - Nutup lan Muat). Wigati dimangerteni manawa path menyang file sing diimpor kanggo conto kasebut hardcoded ing kode kasebut. Sampeyan bakal bali menyang jendhela utama Microsoft Excel, nanging panel karo sambungan digawe kanggo fungsi kita kudu katon ing sisih tengen:
Langkah 3. Nglumpukake kabeh file
Kabeh sing paling angel ana ing mburi, sing nyenengake lan gampang tetep. Pindhah menyang tab Data - Nggawe Pitakonan - Saka File - Saka Folder (Data - Pitakonan Anyar - Saka file - Saka folder) utawa, yen sampeyan duwe Excel 2010-2013, padha karo tab Pitakonan Daya. Ing jendhela sing katon, nemtokake folder ing ngendi kabeh file kutha sumber dumunung lan klik OK. Langkah sabanjure kudu mbukak jendhela ing ngendi kabeh file Excel sing ditemokake ing folder iki (lan subfolder) lan rincian kanggo saben file bakal kadhaptar:
Klik Ganti (Edit) lan maneh kita mlebu menyang jendhela editor query sing akrab.
Saiki kita kudu nambah kolom liyane menyang meja kanthi fungsi sing digawe, sing bakal "narik" data saka saben file. Kanggo nindakake iki, pindhah menyang tab Tambah Column - Custom Column (Tambah Kolom - Tambah Kolom Kustom) lan ing jendhela sing katon, ketik fungsi kita getData, nemtokake minangka argumen path lengkap kanggo saben file:
Sawise ngeklik OK kolom digawe kudu ditambahake menyang meja kita ing sisih tengen.
Saiki ayo mbusak kabeh kolom sing ora perlu (kaya ing Excel, nggunakake tombol mouse tengen - mbusak), ninggalake mung kolom ditambahaké lan kolom karo jeneng berkas, amarga jeneng iki (luwih tepat, kutha) bakal migunani kanggo duwe ing total data kanggo saben baris.
Lan saiki "wow wayahe" - klik lambang kanthi panah dhewe ing pojok tengen ndhuwur kolom sing ditambahake kanthi fungsi kita:
… busak centhang Gunakake jeneng kolom asli minangka awalan (Gunakake jeneng kolom asli minangka awalan)lan klik OK. Lan fungsi kita bakal mbukak lan ngolah data saka saben file, miturut algoritma sing direkam lan ngumpulake kabeh ing tabel umum:
Kanggo kaendahan lengkap, sampeyan uga bisa mbusak ekstensi .xlsx saka kolom pisanan kanthi jeneng file - kanthi panggantos standar "ora ana apa-apa" (klik-tengen ing header kolom - Ganti) lan ganti jeneng kolom iki dadi City. Lan uga mbenerake format data ing kolom kanthi tanggal.
Kabeh! Klik ing Ngarep - Nutup lan Muat (Ngarep - Tutup & Muat). Kabeh data sing diklumpukake dening pitakon kanggo kabeh kutha bakal diunggah menyang lembar Excel saiki ing format "tabel cerdas":
Sambungan sing digawe lan fungsi perakitan kita ora perlu disimpen kanthi kapisah kanthi cara apa wae - padha disimpen bebarengan karo file saiki kanthi cara biasa.
Ing mangsa ngarep, kanthi owah-owahan ing folder (nambah utawa mbusak kutha) utawa ing file (ngganti jumlah baris), cukup klik-tengen langsung ing meja utawa pitakon ing panel tengen banjur pilih dhawuh Nganyari & Simpen (Refresh) - Power Query bakal "mbangun maneh" kabeh data maneh ing sawetara detik.
PS
Amandemen. Sawise nganyari Januari 2017, Power Query sinau cara ngumpulake buku kerja Excel dhewe, yaiku ora perlu nggawe fungsi sing kapisah maneh - kedadeyan kanthi otomatis. Mangkono, langkah kapindho saka artikel iki ora perlu maneh lan kabeh proses dadi luwih gampang:
- Pilih Gawe Panjaluk - Saka File - Saka Folder - Pilih Folder - OK
- Sawise dhaptar file katon, penet Ganti
- Ing jendhela Query Editor, nggedhekake kolom Binary kanthi panah pindho lan pilih jeneng sheet sing bakal dijupuk saka saben file.
Lan iku kabeh! Lagu!
- Ngrancang ulang tab silang dadi flat sing cocog kanggo mbangun tabel pivot
- Nggawe grafik gelembung animasi ing Power View
- Macro kanggo ngumpulake lembaran saka macem-macem file Excel dadi siji