Bagaimana menggabungkan data dari beberapa workbook Excel. Katakanlah Anda memiliki folder yang penuh dengan file Excel. Masing-masing memiliki satu lembar kerja dengan jumlah baris yang berbeda. Anda bisa mengimpor semua file di folder ke satu buku kerja Excel.
Menonton video
- Mengunjungi kembali Bersihkan Data dengan podcast Power Query # 2037
- Power Query sekarang bisa menggabungkan semua file Excel dalam sebuah folder.
- Peningkatan: Mereka secara otomatis menghapus header dari semua kecuali file pertama.
- Anda memilih file mana yang akan digunakan sebagai file Contoh.
- Pilih Lembar Kerja, Tabel, Rentang Bernama mana yang akan diimpor
- Gunakan editor kueri untuk melakukan transformasi apa pun
- Tutup & Muat untuk menggabungkan semua file
- Nanti, segarkan kueri untuk memperbaruinya
Transkrip Video
Pelajari Excel dari Podcast, Episode 2077: Gabungkan Buku Kerja
Hai, selamat datang kembali di netcast, saya Bill Jelen. Hanya 40 episode yang lalu, berbicara tentang membersihkan data dengan Power Query, cara mengambil banyak file CSV dan membawanya ke dalam satu file Excel. Nah, fitur ini menjadi jauh lebih baik.
Jadi yang saya miliki di sini hari ini adalah sebuah folder dan di dalam folder ini ada serangkaian file Excel bulanan, 22 di antaranya selama hampir dua tahun. Saya perlu menggabungkan semua file ini. Sekarang, ini semua memiliki tata letak kolom yang sama, oke? Jadi semuanya memiliki 9 kolom, semuanya memiliki tajuk yang sama dan seterusnya, oke. Jadi kami akan menggabungkan semua ini tanpa makro.
Jika Anda menggunakan Excel 2016, Anda akan masuk ke tab Data untuk Dapatkan & Transformasi data. Jika Anda menggunakan Excel 2010 atau 2013, Anda akan keluar dan mengunduh produk hebat dari Microsoft yang disebut Power Query. Anda dapat mengunduh semuanya dan menginstalnya dalam waktu kurang dari satu menit, oke.
Jadi, kami akan datang ke sini untuk Mendapatkan Data, Dari File, tetapi tidak dari satu buku kerja. Kami ingin mendapatkannya Dari Folder seperti ini. Baiklah, ini dia. Mereka meminta Anda untuk melihat-lihat folder tetapi saya hanya akan mengklik di sana dan menempelkan Ctrl + V karena saya menempelkan direktori, klik OK. Jadi mereka menunjukkan pratinjau data yang saya miliki. Saya membuka menu drop-down Combine kecil ini di sini dan memilih Combine & Edit. Baiklah, dan kemudian langkahnya di sini adalah kita akan memilih satu file yang merupakan file perwakilan yang bagus, oke, dan mereka hanya memilih yang pertama yang kebetulan sesuai abjad. Tidak masalah mana yang Anda pilih. Dan Contoh Kueri yang kami buat dari file pertama yang kami pilih, dalam hal ini, saya ingin Sheet1.Jika Anda memiliki beberapa lembar, Anda bisa mengatakan Sheet2 atau lembar disebut Laporan Pendapatan atau Rentang Nama atau tabel. Dan mereka menunjukkan kepada saya seperti apa tampilannya dan saya klik OK.
Sekarang, ada banyak hal yang terjadi di layar ini. Apa yang sebenarnya telah mereka lakukan di sini adalah mereka telah membuat Kueri Sampel, ahh Kueri Sampel, lalu membuatnya sebagai fungsi dan menjalankan fungsi tersebut terhadap semua yang ada di folder. Mereka menambahkan kolom baru yang memberi tahu saya dari mana file itu berasal dan dalam kasus khusus ini saya sudah tahu dari mana asalnya karena kami memiliki tanggal yang diisi di sini. Jadi saya tidak membutuhkannya, saya akan klik kanan dan Hapus itu. Dan mungkin ada beberapa data di sana yang tidak saya butuhkan. Mungkin data ini akan pergi ke pelanggan jadi saya tidak ingin menunjukkan Keuntungan jadi saya bisa menghapus kolom itu dan bahkan menghapus kolom Biaya, dan mungkin ada beberapa hal yang mengganggu seperti, Anda tahu, mereka menggunakan header yang buruk dan saya ingin mengganti nama header ini. Jadi klik kanan dan Ubah nama, kami akan menyebutnya QTY untuk Kuantitas.Mungkin urutannya salah, mungkin saya ingin mengambil item ini dan memindahkannya ke tempat lain, oke? Hanya semua hal kecil yang mengganggu ini yang mungkin Anda miliki dan, tentu saja di sini, mereka melakukan semua langkah yang diterapkan dan kemudian Anda dapat terus melakukan hal-hal Power Query yang perlu Anda lakukan. Tapi dalam kasus ini, itu sederhana saja. Saya hanya ingin mendapatkan datanya. Jadi saya akan Tutup & Muat, beberapa detik berlalu dan itu dia. Saya memiliki final saya. Mari kita urutkan di sini berdasarkan tanggal; Anda lihat di sana kita pergi dari Januari 2018 hingga Oktober 2019. Betapa kerennya itu.kembali melakukan semua langkah yang diterapkan lalu Anda bisa terus melakukan hal-hal Power Query yang perlu Anda lakukan. Tapi dalam kasus ini, itu sederhana saja. Saya hanya ingin memasukkan datanya. Jadi saya akan Menutup & Memuat, beberapa detik berlalu dan itu dia. Saya memiliki final saya. Mari kita urutkan di sini berdasarkan tanggal; Anda lihat di sana kita pergi dari Januari 2018 hingga Oktober 2019. Betapa kerennya itu.kembali melakukan semua langkah yang diterapkan lalu Anda bisa terus melakukan hal-hal Power Query yang perlu Anda lakukan. Tapi dalam kasus ini, itu sederhana saja. Saya hanya ingin memasukkan datanya. Jadi saya akan Menutup & Memuat, beberapa detik berlalu dan itu dia. Saya memiliki final saya. Mari kita urutkan di sini berdasarkan tanggal; Anda lihat di sana kita pergi dari Januari 2018 hingga Oktober 2019. Betapa kerennya itu.
Inilah hal yang luar biasa dan menakjubkan tentang ini. Katakanlah ada sesuatu yang berubah di folder kita. Jadi kembali ke sini di file Bulan, saya mendapatkan dua file baru, Tempel, dan sekarang ada dua catatan lagi di sini, dua file Excel lagi yang harus saya gabungkan. Cukup kembali ke file Excel saya yang saat ini memiliki 516 baris, temukan MonthFiles dan klik tombol Refresh. Dan secepat itu saya sekarang memiliki rekor baru dari November dan Desember. Power Query adalah alat baru yang luar biasa gratis untuk Excel 2010, gratis untuk Excel 2013. Sangat indah saat mereka bisa menggabungkan file CSV tapi sekarang mereka juga bisa menggabungkan file Excel, sungguh fenomenal.
Jadi, saya telah memperbarui buku saya, Power Excel dengan, Edisi 2017, menyertakan Power Query, Power BI, dan semua alat baru. Coba lihat. Klik "i" di pojok kanan atas.
Baiklah, tutup episode ini: Kami mengunjungi kembali Clean Data dengan podcast Power Query # 2037 tempat kami menggabungkan beberapa file CSV. Kami sekarang dapat menggunakan alat yang sama, versi yang lebih baik dari alat itu, untuk menggabungkan semua file Excel dalam sebuah folder. Peningkatan lainnya, perubahan dari 2037, mereka secara otomatis menghapus header dari semua kecuali file pertama dan mempromosikan header tersebut di file pertama. Anda memilih file mana yang akan digunakan sebagai file Contoh. Dalam kasus saya, saya menggunakan Januari. Kerja- Pilih Lembar Kerja, Tabel, atau Rentang Bernama mana yang akan Diimpor, lalu lakukan transformasi apa pun di editor kueri. Kami klik Close & Load, itu akan menggabungkan semua file. Dan kemudian, jika Anda mendapatkan lebih banyak file yang jatuh ke folder atau beberapa file dihapus atau beberapa file diubah, segarkan kueri dan itu akan melakukan semua langkah itu lagi. Power Query,alat yang benar-benar luar biasa.
Saya ingin mengucapkan terima kasih telah mampir. Sampai jumpa di lain waktu untuk netcast lain dari.
Unduh berkas
Unduh file contoh di sini: Podcast2077.xlsm
