Power query menggabungkan file dengan nama lembar kerja yang berbeda

Saya baru-baru ini mendapat pertanyaan dari pembaca tentang menggabungkan beberapa lembar kerja di buku kerja yang sama menjadi satu lembar kerja

Saya memintanya untuk menggunakan Power Query untuk menggabungkan lembar yang berbeda, tetapi kemudian saya menyadari bahwa untuk seseorang yang baru menggunakan Power Query, melakukan ini bisa jadi sulit

Jadi saya memutuskan untuk menulis tutorial ini dan menunjukkan langkah-langkah yang tepat untuk menggabungkan beberapa lembar menjadi satu tabel menggunakan Power Query

Di bawah video tempat saya menunjukkan cara menggabungkan data dari beberapa lembar/tabel menggunakan Power Query

Di bawah ini adalah instruksi tertulis tentang cara menggabungkan beberapa lembar (jika Anda lebih suka teks tertulis daripada video)

Catatan. Power Query dapat digunakan sebagai add-in di Excel 2010 dan 2013, dan merupakan fitur bawaan dari Excel 2016 dan seterusnya. Berdasarkan versi Anda, beberapa gambar mungkin terlihat berbeda (pengambilan gambar yang digunakan dalam tutorial ini berasal dari Excel 2016)

Gabungkan Data dari Beberapa Lembar Kerja Menggunakan Power Query

Saat menggabungkan data dari lembar berbeda menggunakan Power Query, data harus ada dalam Tabel Excel (atau setidaknya dalam rentang bernama). Jika data tidak ada dalam Tabel Excel, metode yang ditampilkan di sini tidak akan berfungsi

Misalkan Anda memiliki empat lembar berbeda – Timur, Barat, Utara, dan Selatan

Masing-masing lembar kerja ini memiliki data dalam Tabel Excel, dan struktur tabelnya konsisten (mis. e. , headernya sama)

Klik di sini untuk mengunduh data dan ikuti bersama

Jenis data ini sangat mudah digabungkan menggunakan Power Query (yang berfungsi sangat baik dengan data di Tabel Excel)

Agar teknik ini bekerja dengan baik, lebih baik memiliki nama untuk Tabel Excel Anda (berfungsi tanpa itu juga, tetapi lebih mudah digunakan saat tabel diberi nama)

Saya telah memberikan tabel nama berikut. Data_Timur, Data_Barat, Data_Utara, dan Data_Selatan

Berikut adalah langkah-langkah untuk menggabungkan beberapa lembar kerja dengan Tabel Excel menggunakan Power Query

1. Buka tab Data
2. Di grup Get & Transform Data, klik opsi ‘Get Data’
3. Buka opsi 'Dari Sumber Lain'
4. Klik opsi 'Kueri Kosong'. Ini akan membuka editor Power Query
5. Di editor kueri, ketik rumus berikut di bilah rumus. = Unggul. Buku Kerja Saat Ini(). Perhatikan bahwa rumus Power Query peka terhadap huruf besar/kecil, jadi Anda harus menggunakan rumus yang sama persis seperti yang disebutkan (jika tidak, Anda akan mendapatkan kesalahan).  
6. Tekan tombol Enter. Ini akan memperlihatkan kepada Anda semua nama tabel di seluruh buku kerja (itu juga akan menunjukkan kepada Anda rentang dan/atau koneksi yang dinamai jika ada di buku kerja)
7. [Langkah Opsional] Dalam contoh ini, saya ingin menggabungkan semua tabel. Jika Anda ingin menggabungkan Tabel Excel tertentu saja, maka Anda dapat mengklik ikon drop-down di header nama dan memilih yang ingin Anda gabungkan. Demikian pula, jika Anda memiliki rentang atau koneksi bernama, dan Anda hanya ingin menggabungkan tabel, Anda juga dapat menghapus rentang bernama tersebut
8. Di sel header Konten, klik panah runcing ganda
9. Pilih kolom yang ingin Anda gabungkan. Jika Anda ingin menggabungkan semua kolom, pastikan (Pilih Semua Kolom) dicentang
10. Hapus centang opsi 'Gunakan nama kolom asli sebagai awalan'
11. Klik Oke

Langkah-langkah di atas akan menggabungkan data dari semua lembar kerja menjadi satu tabel tunggal

Jika Anda melihat lebih dekat, Anda akan menemukan kolom terakhir (paling kanan) memiliki nama tabel Excel (Data_Timur, Data_Barat, Data_Utara, dan Data_Selatan). Ini adalah pengidentifikasi yang memberi tahu kami catatan mana yang berasal dari mana Tabel Excel. Ini juga alasan saya mengatakan lebih baik memiliki nama deskriptif untuk tabel Excel

Berikut beberapa modifikasi yang dapat Anda lakukan pada data gabungan di Power Query itu sendiri

1. Seret dan tempatkan kolom Nama ke awal
2. Hapus "_Data" dari kolom nama (sehingga Anda tinggal dengan Timur, Barat, Utara, dan Selatan di kolom nama). Untuk melakukan ini, klik kanan pada tajuk Nama dan klik Ganti Nilai. Di kotak dialog  Ganti Nilai, ganti _Data dengan kosong
3. Ubah kolom Data untuk hanya menampilkan tanggal (dan bukan waktu). Untuk melakukan ini, klik tajuk kolom Tanggal, buka tab 'Transformasi' dan ubah Jenis data menjadi Tanggal
4. Ganti nama Kueri menjadi ConsolidatedData

Sekarang setelah Anda memiliki data gabungan dari semua lembar kerja di Power Query, Anda bisa memuatnya di Excel – sebagai tabel baru di lembar kerja baru

Untuk melakukan ini. ikuti langkah-langkah di bawah ini

1. Klik tab 'File'
2. Klik Tutup dan Muat Ke
3. Di kotak dialog Impor Data, pilih opsi Tabel dan Lembar kerja baru
4. Klik Oke

Langkah-langkah di atas akan menggabungkan data dari semua lembar kerja dan memberi Anda data gabungan tersebut dalam lembar kerja baru

Satu Masalah yang Harus Anda Selesaikan saat Menggunakan Metode Ini

Jika Anda telah menggunakan metode di atas untuk menggabungkan semua tabel di buku kerja, kemungkinan besar Anda akan menghadapi masalah

Lihat jumlah baris data gabungan – 1304 (yang benar)

Sekarang, jika saya menyegarkan kueri, jumlah baris berubah menjadi 2607. Segarkan lagi dan itu akan berubah menjadi 3910

Inilah masalahnya

Setiap kali Anda me-refresh kueri, semua catatan dalam data asli akan ditambahkan ke data gabungan

Catatan. Anda akan menghadapi masalah ini hanya jika Anda telah menggunakan Power Query untuk menggabungkan SEMUA TABEL EXCEL di buku kerja. Jika Anda memilih tabel tertentu untuk digabungkan, Anda tidak akan menghadapi masalah ini

Mari kita pahami penyebab masalah ini dan cara memperbaikinya

Saat Anda menyegarkan kueri, kueri akan kembali dan mengikuti semua langkah yang kami ambil untuk menggabungkan data

Pada langkah di mana kami menggunakan rumus =Excel. CurrentWorkbook(), itu memberi kami daftar semua tabel. Ini berfungsi dengan baik pertama kali karena hanya ada empat meja

Tapi saat Anda menyegarkan, ada lima tabel di buku kerja – termasuk tabel baru yang disisipkan Power Query di mana kami memiliki data gabungan

Jadi setiap kali Anda me-refresh kueri, selain dari empat Tabel Excel yang ingin kami gabungkan, itu juga menambahkan tabel kueri yang ada ke data yang dihasilkan.

Ini disebut rekursi

Inilah cara mengatasi masalah ini

Setelah Anda memasukkan =Excel. CurrentWorkbook() di bilah rumus Power Query dan tekan enter, Anda akan mendapatkan daftar Tabel Excel. Untuk memastikan Anda hanya bisa menggabungkan tabel dari lembar kerja, Anda perlu memfilter hanya tabel ini yang ingin Anda gabungkan dan menghapus yang lainnya

Berikut adalah langkah-langkah untuk memastikan Anda hanya memiliki tabel yang diperlukan

1. Klik tarik-turun dan arahkan kursor pada Filter Teks
2. Klik pada opsi Berisi
3. Di kotak dialog Filter Baris, masukkan _Data di bidang di sebelah opsi 'berisi'
4. Klik Oke

Anda mungkin tidak melihat perubahan apa pun pada data, tetapi melakukan hal ini akan mencegah tabel yang dihasilkan ditambahkan lagi saat kueri disegarkan

Perhatikan bahwa pada langkah-langkah di atas kami telah menggunakan "_Data" untuk memfilter saat kami menamai tabel dengan cara itu. Tetapi bagaimana jika tabel Anda tidak diberi nama secara konsisten. Bagaimana jika semua nama tabel acak dan tidak memiliki kesamaan

Inilah cara untuk menyelesaikannya – gunakan filter 'tidak sama dengan' dan masukkan nama Kueri (yang akan menjadi ConsolidatedData dalam contoh kami). Ini akan memastikan bahwa semuanya tetap sama dan tabel kueri hasil yang dibuat disaring

Terlepas dari kenyataan bahwa Power Query membuat seluruh proses menggabungkan data dari lembar yang berbeda (atau bahkan lembar yang sama) cukup mudah, manfaat lain menggunakannya adalah membuatnya dinamis. Jika Anda menambahkan lebih banyak catatan ke salah satu tabel dan menyegarkan Power Query, itu akan secara otomatis memberi Anda data gabungan

Catatan penting. Dalam contoh yang digunakan dalam tutorial ini, headernya sama. Jika header berbeda, Power Query akan menggabungkan dan membuat semua kolom di tabel baru. Jika data tersedia untuk kolom itu, itu akan ditampilkan, jika tidak maka akan ditampilkan nol

Anda Mungkin Juga Menyukai Tutorial Power Query Berikut

• Menggabungkan Data dari Beberapa Buku Kerja di Excel (menggunakan Power Query)
• Cara Unpivot Data di Excel menggunakan Power Query (alias Get & Transform)
• Dapatkan Daftar Nama File dari Folder dan Subfolder (menggunakan Power Query)
• Menggabungkan Tabel di Excel Menggunakan Power Query
• Bagaimana Membandingkan Dua Lembar/File Excel
• Bagaimana Cara Beralih Antar Lembar di Excel?

29 pemikiran pada “Menggabungkan Data Dari Beberapa Lembar Kerja Menjadi Satu Lembar Kerja Di Excel”

1. yoel

   Terima kasih. penjelasan yang bagus
   membuatku bahagia

2. Vijay

   Satu masalah yang saya miliki adalah menghilangkan baris kosong dari tabel sumber (dari beberapa file excel). Setiap tabel memiliki sekitar 500 baris yang telah ditentukan sebelumnya, tetapi data saat ini sekitar 100 baris dan terus bertambah. Jadi file gabungan saya memiliki sekitar 5000 baris (kosong diimpor sebagai "null") dari 10 file berbeda sedangkan data sebenarnya sekitar 600 baris semuanya. Bagaimana cara membatasi kueri daya saya untuk memuat hanya baris yang sah yang mengabaikan baris kosong. Terima kasih atas bantuannya

3. GRC

   Pak – video ini benar-benar sempurna. Terima kasih banyak atas penjelasannya yang mudah diikuti. Sangat membantu saya

4. gwen

   Diikuti langkah demi langkah hingga memasukkan rumus =Excel. CurrentWorkbook()…yang tidak mengembalikan tabel di buku kerja yang ingin saya tambahkan ke dalam satu tabel…sebenarnya hanya dua kesalahan yang dikembalikan
   bingung

5. AJ

   Dokumen ini membantu permintaan saya. Terima kasih

6. PG

   Ini hanya menghemat banyak waktu saya di tempat kerja. Terima kasih banyak

7. roslim

   Hi Sobat, terima kasih banyak untuk panduan Anda dalam tutorial ini. Tapi saya mengalami masalah untuk menggabungkan file data saya. Semuanya baik-baik saja, sampai =Excel. Buku Kerja Saat Ini. Padahal itu menunjukkan "Kesalahan" di bawah tajuk Konten, dan tidak ada Panah Titik Ganda. Bisakah Anda membantu saya terkait masalah ini?

8. Mohammad Samir

   Bagaimana cara membuat kode untuk Tabel Pivot menggunakan excel VBA?

9. Priyanka

   Bagaimana dengan langkah-langkah di Excel 2016. Mendapatkan kesalahan format tanggal saat menggunakan opsi biner gabungan

10. AP

    Hai sobat, terima kasih atas bantuan Anda, ini berguna. Saya mengalami masalah saat mengimpor semua tabel sesuai instruksi Anda, tetapi menyegarkan kueri tidak berfungsi. Apakah Anda punya tip?

11. Michael Wagener

    Sangat membantu… sayang sekali MS tidak menyediakan fungsionalitas yang sama di Mac seperti di Windows…

12. Becky

    Ini sangat membantu. Terima kasih atas bimbingannya

13. Rinardo Henderson

    Rinardo

    Ini sangat membantu. Meskipun saya memiliki pertanyaan, saya mencoba menggabungkan 4 lembar kerja menjadi satu tabel dan ketiganya memiliki kolom berlabel "Gudang", "Hitung Tanggal", dan "ABS ($ VARIANS"). Informasi dari semua 4 lembar ditarik ke tabel gabungan saya dengan pengecualian beberapa tanggal penghitungan. Saat saya melihat kembali kueri saya, beberapa baris mengembalikan nilai "null" untuk beberapa tanggal, sehingga membuat sel kosong di tabel gabungan saya. Apakah Anda dapat memberikan wawasan tentang bagaimana saya dapat memperbaiki masalah ini?

14. JLT

    Penjelasan yang bagus dengan langkah-langkah yang jelas. Hargai bantuan Anda

15. Annie

    Ini bagus terima kasih
    Saya memerlukan bantuan untuk menggabungkan hanya info tertentu dari Lembar Kerja ke dalam satu buku kerja – apakah Anda juga memiliki tutorial tentang itu?

16. Maria

    Hai. Ini sangat informatif. Saya punya pertanyaan, saya menghapus satu baris dari data sumber tetapi baris tersebut tetap berada di tabel gabungan karena baris yang ditambahkan dengan nama tabel. Tahukah Anda bagaimana saya menghapus baris sepenuhnya dalam tabel gabungan saat menghapus baris dari salah satu tabel sumber?

    Terima kasih banyak
    Maria

17. Mehdi

    fantastis

18. KELLY

    Ini luar biasa. Persis seperti yang saya butuhkan dan berfungsi seperti yang dijelaskan. Saya memiliki versi excel yang berbeda tetapi cukup mudah menyesuaikannya. Ini akan menjadi penghemat waktu yang sangat besar bagi saya

19. shamsher khan

    itu bagus tapi saya punya excel 2016 Jadi saya menghadapi masalah. Ada langkah yang berbeda

    • shamsher khan

      bagaimana bekerja dengan excel 2016

20. Yakobus

    Apa yang saya butuhkan – sangat mudah diikuti dan bekerja seperti mimpi – terima kasih

21. cgall

    Ini sangat membantu dan saya melakukan tes di komputer saya – bekerja dengan sangat baik. Saya bekerja untuk perusahaan yang menggunakan google drive – apakah ada cara untuk melakukannya dengan google sheets?

22. penerima manfaat yang berterima kasih

    Wow - barang bagus. Memiliki excel 2010 dan harus mengunduh add-on kueri tetapi berfungsi
    Namun – agak merepotkan mencoba mencari cara untuk benar-benar melakukan ini. Dengan add-on, itu tidak akan menampilkan "buku kerja saat ini" setidaknya tidak pada percobaan pasangan saya. Jadi, alih-alih memuat "kueri kosong", saya mulai dengan "mulai kueri dari file" dan kemudian memilih file yang saya inginkan. ” Di "NAVIGATOR" saya memilih seluruh folder (mis. e. buku kerja) bukan lembar individual

    Dari sana, saya harus membuka “EDIT QUERY. ” Kemudian, saya harus memikirkan cara menghapus kolom dalam kueri untuk mendapatkan dua kolom yang sama ("KONTEN" (tabel) dan "Nama" (nama lembar). Dari sana, saya mengklik kanan pada "panah ganda" seperti yang dijelaskan di atas. Itu memuat semua kolom dan saya menekan “OK. ” Sekarang saya dapat dengan mudah "mengganti nama" setiap kolom dengan tajuk yang saya butuhkan dari setiap lembar. Kemudian saya mengklik "Tutup dan Muat" dan selesai

    Terima kasih lagi untuk posting ini

23. Prad

    terlalu rumit .. tidak berhasil, tetapi kemudian saya mengalami keadaan darurat, mungkin jika saya meluangkan waktu, bisa berhasil

24. Aneh

    Metode ini berfungsi dengan baik, sampai saya akan memuatnya ke lembar baru, lalu Excel mogok. Bisakah saya mengatasi ini, saya bertanya-tanya?

25. Robert Thomas

    Hai, butuh beberapa saat tetapi akhirnya saya berhasil, meskipun tidak serapi Anda. Untuk beberapa alasan nama kolom saya berubah menjadi Data. Kolom1, Data. Kolom2 dll. sementara header reguler saya menjadi teks dan tercampur dalam data saya sekarang. Ketika saya mengubah nama dari DataColumn1 dll ke nama aslinya dan menyimpannya, mereka akan mundur kembali ke DataColumn1,2,3…dll. segera setelah saya menyegarkan. Ada ide?

    • penerima manfaat yang berterima kasih

      Anda dapat mengganti nama setiap nama untuk "DataColumn1" dll. dengan mengedit kueri dan mengklik kanan nama kolom. Lihat ke bagian bawah menu konteks pop-up. Klik "Ganti nama" dengan nama tajuk yang Anda suka dan voila – selesai. Kemudian, setelah Anda menutup dan memuat lembar, Anda cukup menghapus baris 1-2 yang kemungkinan merupakan baris yang tidak Anda inginkan/butuhkan. Harapan yang membantu

      Bagaimana cara menggabungkan file Excel dengan nama sheet yang berbeda?

      Gabungkan menurut posisi .

      Buka setiap lembar sumber dan pastikan data Anda berada di posisi yang sama di setiap lembar

      Di lembar tujuan Anda, klik sel kiri atas area tempat Anda ingin menampilkan data gabungan. .

      Pada tab Data, di bawah Alat, klik Konsolidasi

      Bagaimana cara menggabungkan beberapa lembar dari banyak file di Power Query?

      Menggabungkan dan Mengubah Data Untuk menggabungkan semua file dengan kueri, lalu meluncurkan Power Query Editor, pilih Menggabungkan > Menggabungkan dan Mengubah Data Gabungkan dan Muat.

      Bisakah Power Query menggabungkan buku kerja yang berbeda dengan kolom yang berbeda?

      Power Query dapat sangat membantu saat Anda ingin menggabungkan beberapa buku kerja menjadi satu buku kerja tunggal . Misalnya, Anda memiliki data penjualan untuk berbagai wilayah (Timur, Barat, Utara, dan Selatan). Anda bisa menggabungkan data ini dari buku kerja yang berbeda ke dalam satu lembar kerja menggunakan Power Query.

      Bagaimana cara menggabungkan Excel dengan header berbeda dalam daftar Power Query yang terakumulasi?

      Anda dapat melakukannya dengan mudah di kueri edit power bi. Ubah nama kolom pada tabel 2 sesuai kebutuhan Anda dan atur ulang urutan kolom. Setelah itu, di Edit query --> Home --> Klik Append Queries. Klik pada dua tabel dan pilih table1