Perkongsian kemahiran Excel Praktikal: Gunakan Power Query untuk menggabungkan buku kerja dalam folder

Sebelum ini saya telah memperkenalkan anda tentang penggunaan fungsi baharu EXCEL Power Query untuk meringkaskan lembaran kerja dalam buku kerja, tetapi fungsi Power Query jauh lebih daripada itu. Hari ini saya akan memperkenalkannya kepada anda. Teknik penggabungan yang lebih maju: Gunakan Power Query untuk menggabungkan buku kerja dalam folder.

Seperti yang ditunjukkan di bawah, terdapat data jualan untuk empat wilayah dalam folder "Jualan" pada desktop. Nama tajuk dalam setiap buku kerja adalah konsisten dan susunannya mungkin berbeza. Nilai lajur bandar dalam setiap buku kerja ialah nama buku kerja, yang sesuai untuk melihat kesan gabungan kemudian.

Operasi adalah seperti berikut:

Tutup fail dalam folder, buat buku kerja baharu, klik pada data tab, [Dapatkan dan Ubah] kumpulan "Pertanyaan Baharu" --- "Dari Fail" --- "Dari Folder".

Masukkan laluan folder, atau semak imbas untuk memilih lokasi folder dan klik OK.

Antara muka ini menyenaraikan semua buku kerja dalam folder, klik Edit.

Masukkan antara muka editor Power Query. Bahagian atas ialah bar menu, bahagian tengah ialah kawasan jadual dan data yang akan dikembalikan ke lembaran kerja pada penghujung Tetingkap tetapan pertanyaan di sebelah kanan memaparkan rekod operasi Power Query.

Seperti yang diperkenalkan sebelum ini, fon hijau dalam lajur "Kandungan" bermaksud sel ini mengandungi fail. Klik pada sel untuk melihat kandungannya. (Nota: Apabila melihat pratonton kandungan dalam sel, anda harus meletakkan tetikus pada ruang kosong dalam sel, bukan pada teks. Mengklik pada teks akan terus membuka fail dalam sel) Sejak fail dibuka terus daripada folder Fail yang diekstrak semuanya dalam format binari, jadi buku kerja dalam format binari muncul dalam anak tetingkap pratonton di bawah.

Binari sebagai fail binari tidak boleh dipaparkan dalam jadual, jadi apa yang kita perlu lakukan ialah menukarnya ke dalam format jadual dan kemudian mengembangkan data ke dalam jadual di dalam. Padamkan lajur lain yang tidak diperlukan terlebih dahulu.

Pilih lajur "Kandungan", klik "Padam Lajur" - "Padam Lajur Lain" dalam kumpulan [Urus Lajur] di bawah tab Laman Utama.

Dengan cara ini, semua lajur kecuali lajur "Kandungan" dipadamkan.

Kemudian cara menukar fail binari kepada jadual biasa memerlukan penggunaan bahasa pengaturcaraan khas Power Query - bahasa M. Di sini saya akan memperkenalkan anda kepada fungsi yang biasa digunakan.

Klik "Lajur Tersuai" dalam kumpulan [Umum] di bawah pilihan Tambah Lajur.

Dalam tetingkap Lajur Tersuai, masukkan =Excel.Workbook([Kandungan],true) dalam "Formula Lajur Tersuai", di mana "[ Kandungan]" Anda boleh mengklik "Kandungan" dalam lajur yang tersedia di sebelah kanan, dan kemudian klik pada penjuru kanan sebelah bawah untuk memasukkan (Nota: huruf besar formula mestilah tidak salah).

Analisis formula:

Excel.Workbook

Fungsi: Kembalikan rekod lembaran kerja daripada buku kerja Excel.

Parameter: Excel.Workbook(buku kerja sebagai binari, penggunaan pilihanPengepala sebagai logik boleh batal,Jenis kelewatan pilihan sebagai boleh batal logik) sebagai jadual

Fungsi ini mengembalikan jadual buku kerja parameter pertama dalam format binari, dan parameter kedua ialah nilai logik parameter pilihan benar menggunakan tajuk jadual asal sebagai jadual baharu Tajuk, lalai adalah palsu, bermaksud menggantikan tajuk lembaran kerja asal dengan nama lajur baharu. Jangan risau tentang parameter ketiga.

Di sini kami masih menggunakan tajuk asal borang, jadi isikan dengan benar. Ini menjimatkan langkah seterusnya untuk menambah baik baris pertama tajuk.

Lajur baharu berjaya ditambahkan pada salah satu sel Apa yang ditunjukkan di bawah ialah buku kerja gaya jadual. Ini boleh terus dilanjutkan ke meja.

Lajur "Data" memaparkan jadual dalam format Jadual, termasuk data dalam jadual Di sini kita hanya perlu mengekstrak lajur ini. Klik butang kembangkan di penjuru kanan sebelah atas lajur tersuai, pilih lajur yang dikembangkan "Data" dan nyahtanda "Gunakan nama lajur asal sebagai awalan".

Nama lajur menjadi "Data". Pada masa ini, kami pratonton data dalam "Data", dan apa yang dipaparkan di bawah ialah data asal dalam jadual. Kemudian ekstrak semua data di bawah.

Begitu juga klik butang kembangkan di bahagian atas sebelah kanan lajur tersuai, pilih untuk mengembangkan semua lajur dan jangan tandai "Gunakan nama lajur asal sebagai awalan".

Dengan cara ini, kami memperoleh data dalam lembaran kerja dengan menggerudi lapisan demi lapisan.

Akhir sekali padam lajur "Kandungan". Pilih lajur "Kandungan" dan klik kanan untuk memadamkannya.

Akhir sekali, muat naik sahaja jadual ini ke borang.

Klik "Tutup dan Muat Naik" dalam kumpulan [Tutup] di bawah tab Laman Utama.

Dengan cara ini, data diringkaskan dalam lembaran kerja.

Apabila anda mengklik butang penapis dalam lajur "Bandar", anda boleh melihat bahawa data dalam empat buku kerja semuanya berada dalam jadual.

Apakah yang berlaku apabila terdapat satu lagi buku kerja dalam folder? Cuba letakkan buku kerja baharu "Xi'an" dalam folder ini.

Kembali ke jadual di mana anda baru membuat statistik, klik [ di bawah tab data "Refresh All" dalam kumpulan Connection].

Terdapat "Xi'an" tambahan dalam lajur bandar, yang mewakili data buku kerja baharu ini telah ditambahkan.

Ringkasan: Power Query menggabungkan folder asalkan tajuk dalam setiap lembaran kerja adalah sama, anda boleh menggabungkan dan meringkaskannya tidak mengambil berat tentang folder Sebarang bilangan buku kerja boleh digabungkan. Dan sebarang perubahan data boleh dikemas kini dengan satu klik melalui semua muat semula.

Power Query ialah alat yang berkuasa untuk analisis data EXCEL Melalui operasi grafik yang mudah, digabungkan dengan bahasa Mnya sendiri dan melalui perakam operasi, ia membantu kami mengendalikan lebih banyak data dalam cara yang bersatu dan menyelesaikan pemprosesan data dengan cepat. dan pengoptimuman. Selain itu, ia adalah lebih cepat untuk bermula dan lebih mudah untuk dikendalikan daripada VBA, dan operasi grafik boleh memenuhi kebanyakan keperluan kami. Semua orang, cepat dan belajar!

Cadangan pembelajaran berkaitan: tutorial cemerlang

Atas ialah kandungan terperinci Perkongsian kemahiran Excel Praktikal: Gunakan Power Query untuk menggabungkan buku kerja dalam folder. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!