Cara menggabungkan beberapa file excel menjadi satu tabel akses

Power query bisa 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

Jika Anda memiliki buku kerja ini di lokasi/folder yang berbeda, sebaiknya pindahkan semua ini ke dalam satu folder (atau buat salinan dan letakkan salinan buku kerja itu di folder yang sama)

Jadi untuk memulainya, saya memiliki empat buku kerja dalam satu folder (seperti yang ditunjukkan di bawah)

Sekarang, dalam tutorial ini, saya membahas tiga skenario di mana Anda bisa menggabungkan data dari buku kerja yang berbeda menggunakan Power Query

• Setiap buku kerja memiliki data dalam Tabel Excel, dan semua nama tabelnya sama
• Setiap buku kerja memiliki data dengan nama lembar kerja yang sama. Ini bisa terjadi ketika ada lembar bernama 'ringkasan' atau 'data' di semua buku kerja, dan Anda ingin menggabungkan semua ini
• Setiap buku kerja memiliki banyak lembar dan tabel, dan Anda ingin menggabungkan tabel/lembar tertentu. Metode ini juga dapat membantu saat Anda ingin menggabungkan tabel/lembar yang tidak memiliki nama yang konsisten

Mari kita lihat cara menggabungkan data dari buku kerja ini di setiap kasus

Tutorial ini Mencakup

Setiap buku kerja memiliki data dalam Tabel Excel dengan struktur yang sama

Teknik di bawah ini akan bekerja ketika Tabel Excel Anda telah disusun dengan cara yang sama (nama kolom yang sama)

Jumlah baris di setiap tabel bisa berbeda-beda

Jangan khawatir jika beberapa Tabel Excel memiliki kolom tambahan. Anda dapat memilih salah satu Tabel sebagai templat (atau sebagai 'kunci' sebagaimana Power Query menyebutnya), dan Power Query akan menggunakannya untuk menggabungkan semua Tabel Excel lainnya dengannya

Jika ada kolom tambahan di tabel lain, kolom tersebut akan diabaikan dan hanya kolom yang ditentukan di template/kunci yang akan digabungkan. Misalnya, jika templat/tabel kunci yang Anda pilih memiliki 5 kolom, dan salah satu tabel di buku kerja lain memiliki 2 kolom tambahan, kolom tambahan tersebut akan diabaikan

Sekarang saya memiliki empat buku kerja dalam satu folder yang ingin saya gabungkan

Di bawah ini adalah snapshot dari tabel yang saya miliki di salah satu buku kerja

Berikut adalah langkah-langkah untuk menggabungkan data dari buku kerja ini menjadi satu buku kerja (sebagai satu tabel)

1. Buka tab Data
2. Di grup Get & Transform, klik drop down New Query
3. Arahkan kursor Anda pada 'Dari File' dan klik 'Dari Folder'
4. Di kotak dialog Folder, masukkan jalur file dari folder yang berisi file, atau klik Telusuri dan temukan folder tersebut
5. Klik Oke
6. Di kotak dialog yang terbuka, klik tombol gabungkan
7. Klik 'Gabungkan & Muat'
8. Di kotak dialog 'Gabungkan File' yang terbuka, pilih Tabel di panel kiri. Perhatikan bahwa Power Query menampilkan Tabel dari file pertama. File ini akan bertindak sebagai template (atau kunci) untuk menggabungkan file lainnya. Power Query sekarang akan mencari 'Tabel 1' di buku kerja lain dan menggabungkannya dengan yang ini
9. Klik Oke

Ini akan memuat hasil akhir (gabungan data) ke dalam lembar kerja aktif Anda

Perhatikan bahwa bersama dengan data, Power Query secara otomatis menambahkan nama buku kerja sebagai kolom pertama dari gabungan data. Ini membantu dalam melacak data apa yang berasal dari buku kerja mana

Jika Anda ingin mengedit data terlebih dahulu sebelum memuatnya ke Excel, pada Langkah 6, pilih 'Gabungkan dan Edit'. Ini akan membuka hasil akhir di editor Power Query tempat Anda dapat mengedit data

Beberapa hal yang perlu diketahui

• Jika Anda memilih Tabel Excel sebagai templat (di Langkah 7), Power Query akan menggunakan nama kolom dalam Tabel ini untuk menggabungkan data dari Tabel lain. Jika Tabel lain memiliki kolom tambahan, itu akan diabaikan. Jika Tabel lain tersebut tidak memiliki kolom, yang ada di Tabel Templat Anda, Power Query hanya akan mencantumkan 'null' untuk itu
• Kolom tidak perlu dalam urutan yang sama karena Power Query menggunakan header kolom untuk memetakan kolom
• Karena Anda telah memilih Tabel1 sebagai kuncinya, Power Query akan mencari Tabel1 di semua buku kerja, dan menggabungkan semua ini. Jika tidak menemukan Tabel Excel dengan nama yang sama (Tabel1 dalam contoh ini), Power Query akan memberi Anda kesalahan

Menambahkan File Baru ke Folder

Sekarang mari luangkan waktu sebentar dan pahami apa yang kita lakukan dengan langkah-langkah di atas (yang hanya membutuhkan waktu beberapa detik)

Kami menggabungkan data dari empat buku kerja berbeda dalam satu tabel dalam beberapa detik bahkan tanpa membuka buku kerja apa pun

Tapi itu belum semuanya

KEKUATAN sebenarnya dari Power Query adalah sekarang saat Anda menambahkan lebih banyak file ke folder, Anda tidak perlu mengulangi langkah-langkah ini

Yang perlu Anda lakukan adalah memindahkan buku kerja baru di folder, menyegarkan kueri, dan secara otomatis menggabungkan data dari semua buku kerja di folder itu

Misalnya, dalam contoh di atas, jika saya menambahkan buku kerja baru – ‘Mid-West. xlsx' ke folder, dan segarkan kueri, itu akan langsung memberi saya kumpulan data gabungan baru

Inilah cara Anda menyegarkan kueri

• Klik kanan pada Tabel Excel yang Anda muat di lembar kerja dan klik Segarkan
• Klik kanan pada Query di panel 'Workbook Query' dan klik Refresh
• Buka tab Data dan klik Segarkan

Setiap buku kerja memiliki data dengan nama lembar kerja yang sama

Jika Anda tidak memiliki data di Tabel Excel, tetapi semua nama sheet (yang ingin Anda gabungkan datanya) adalah sama, maka Anda dapat menggunakan metode yang ditunjukkan di bagian ini

Ada beberapa hal yang perlu Anda waspadai jika hanya berupa data tabular dan bukan Tabel Excel

• Nama lembar kerja harus sama. Ini akan membantu Power Query menelusuri buku kerja Anda dan menggabungkan data dari lembar kerja yang memiliki nama yang sama di setiap buku kerja
• Power Query peka terhadap huruf besar/kecil. Ini berarti lembar kerja bernama 'data' dan 'Data' dianggap berbeda. Demikian pula, kolom dengan tajuk 'Store' dan kolom dengan 'store' dianggap berbeda
• Meskipun penting untuk memiliki tajuk kolom yang sama, tidak penting untuk memiliki urutan yang sama. Jika kolom 2 di kolom 'Timur. xlsx' adalah kolom 4 di 'West. xlsx', Power Query akan mencocokkannya dengan benar dengan memetakan header

Sekarang mari kita lihat cara menggabungkan data dengan cepat dari buku kerja yang berbeda dengan nama lembar kerja yang sama

Dalam contoh ini, saya memiliki folder dengan empat file

Di setiap buku kerja, saya memiliki lembar kerja dengan nama 'Data' yang berisi data dalam format berikut (perhatikan bahwa ini bukan Tabel Excel)

Berikut adalah langkah-langkah untuk menggabungkan data dari beberapa workbook menjadi satu lembar kerja tunggal

1. Buka tab Data
2. Di grup Get & Transform, klik drop down New Query
3. Arahkan kursor Anda pada 'Dari File' dan klik 'Dari Folder'
4. Di kotak dialog Folder, masukkan jalur file dari folder yang berisi file, atau klik Telusuri dan temukan folder tersebut
5. Klik Oke
6. Di kotak dialog yang terbuka, klik tombol gabungkan
7. Klik 'Gabungkan & Muat'
8. Di kotak dialog 'Gabungkan File' yang terbuka, pilih 'Data' di panel kiri. Perhatikan bahwa Power Query memperlihatkan nama lembar kerja dari file pertama. File ini akan bertindak sebagai kunci/template untuk menggabungkan file lain. Power Query akan menelusuri setiap buku kerja, menemukan lembar bernama 'Data, dan menggabungkan semua ini.  
9. Klik Oke. Sekarang Power Query akan menelusuri setiap buku kerja, cari lembar kerja bernama 'Data' di dalamnya, lalu gabungkan semua kumpulan data ini

Ini akan memuat hasil akhir (gabungan data) ke dalam lembar kerja aktif Anda

Jika Anda ingin mengedit data terlebih dahulu sebelum memuatnya ke Excel, pada Langkah 6, pilih 'Gabungkan dan Edit'. Ini akan membuka hasil akhir di editor Power Query tempat Anda dapat mengedit data

Setiap Buku Kerja memiliki data dengan Nama Tabel atau Nama Lembar yang Berbeda

Terkadang, Anda mungkin tidak mendapatkan data yang terstruktur dan konsisten (seperti Tabel dengan nama yang sama atau lembar kerja dengan nama yang sama)

Misalnya, Anda mendapatkan data dari seseorang yang membuat kumpulan data ini tetapi menamai lembar kerja sebagai Data Timur, Data Barat, Data Utara, dan Data Selatan

Atau, orang tersebut mungkin telah membuat tabel Excel, tetapi dengan nama yang berbeda

Dalam kasus seperti itu, Anda masih bisa menggunakan Power Query, tetapi Anda perlu melakukannya dengan beberapa langkah tambahan

1. Buka tab Data
2. Di grup Get & Transform, klik drop down New Query
3. Arahkan kursor Anda pada 'Dari File' dan klik 'Dari Folder'
4. Di kotak dialog Folder, masukkan jalur file dari folder yang berisi file, atau klik Telusuri dan temukan folder tersebut
5. Klik Oke
6. Di kotak dialog yang terbuka, klik tombol Edit. Ini akan membuka editor Power Query di mana Anda akan melihat detail semua file di folder
7. Tahan tombol Control dan pilih kolom 'Konten' dan 'Nama', klik kanan dan pilih 'Hapus Kolom Lain'. Ini akan menghapus semua kolom lain kecuali kolom yang dipilih
8. Di pita Editor Kueri, klik 'Tambahkan kolom', lalu klik 'Kolom Kustom'
9. Di kotak dialog Tambahkan Kolom Kustom, beri nama kolom baru sebagai 'Impor Data' dan gunakan rumus berikut = Excel. Buku kerja([KONTEN]). Perhatikan bahwa rumus ini peka terhadap huruf besar-kecil dan Anda harus memasukkannya persis seperti yang telah saya tunjukkan di sini
10. Sekarang Anda akan melihat kolom baru yang memiliki Tabel tertulis di dalamnya. Sekarang izinkan saya menjelaskan apa yang terjadi di sini. Anda memberikan Power Query nama buku kerja, dan Power Query telah mengambil objek seperti lembar kerja, tabel, dan rentang bernama dari setiap buku kerja (yang berada di sel Tabel seperti sekarang). Anda dapat mengklik ruang putih di sebelah Tabel teks dan Anda akan melihat informasi di bagian bawah. Dalam hal ini, karena kita hanya memiliki satu tabel dan satu lembar kerja di setiap buku kerja, Anda hanya dapat melihat dua baris.   
11. Klik ikon panah ganda di bagian atas kolom 'Impor Data'
12. Di kotak data kolom yang terbuka, hapus centang pada 'Gunakan kolom asli sebagai awalan', lalu klik OK
13. Sekarang Anda akan melihat tabel yang diperluas di mana Anda melihat satu baris untuk setiap objek dalam tabel. Dalam hal ini, untuk setiap buku kerja, objek lembar dan objek tabel dicantumkan secara terpisah
14. Di kolom Jenis, filter daftar untuk hanya menampilkan Tabel
15. Tahan tombol kontrol dan pilih kolom Nama dan Data. Sekarang, klik kanan dan hapus semua kolom lainnya
16. Di kolom Data, klik ikon panah ganda di kanan atas Header Data
17. Di kotak data kolom yang terbuka, klik OK. Ini akan menggabungkan data di semua tabel dan ditampilkan di Power Query
18. Sekarang Anda dapat melakukan transformasi apa pun yang Anda butuhkan, lalu buka tab Beranda dan klik Tutup & Muat

Sekarang izinkan saya mencoba dan dengan cepat menjelaskan apa yang kami lakukan di sini. Karena tidak ada konsistensi dalam nama sheet atau nama tabel, kami menggunakan =Excel. Rumus buku kerja untuk mengambil semua objek buku kerja di Power Query. Objek ini dapat menyertakan lembar, tabel, dan rentang bernama. Setelah kami memiliki semua objek dari semua file, kami memfilternya untuk hanya mempertimbangkan Tabel Excel. Kemudian kami memperluas data dalam tabel dan menggabungkan semua ini

Dalam contoh ini, kami memfilter data untuk hanya menggunakan Tabel Excel (pada Langkah 13). Jika Anda ingin menggabungkan lembar dan bukan tabel, Anda dapat memfilter lembar

Catatan – teknik ini akan memberi Anda data gabungan meskipun ada ketidakcocokan dalam nama kolom. Misalnya, jika di Timur. xlsx, Anda memiliki kolom yang salah eja, Anda akan mendapatkan 5 kolom. Power Query akan mengisi data dalam kolom jika menemukannya, dan jika tidak dapat menemukan kolom, Power Query akan melaporkan nilainya sebagai 'null'

Demikian pula, jika Anda memiliki beberapa kolom tambahan di salah satu lembar kerja tabel, ini akan disertakan dalam hasil akhir

Sekarang jika Anda mendapatkan lebih banyak buku kerja dari mana Anda perlu menggabungkan data, cukup salin-tempel ke folder dan segarkan Power Query

Anda mungkin juga menyukai Tutorial Excel Berikut

• Dapatkan Daftar Nama File dari Folder dan Subfolder (menggunakan Power Query)
• Menggabungkan Tabel di Excel Menggunakan Power Query
• Cara Menggabungkan Beberapa File Excel Menjadi Satu Buku Kerja Excel
• Gabungkan Data Dari Beberapa Lembar Kerja menjadi Satu Lembar Kerja di Excel
• Cara Cepat Menggabungkan Sel di Excel
• Cara Memilih Setiap Baris Ketiga di Excel (atau memilih setiap Baris ke-N)
• Pisahkan Setiap Lembar Excel Menjadi File Terpisah
• Cara Mengimpor File XML ke Excel. Konversikan XML ke Excel

17 pemikiran pada “Menggabungkan Data dari Beberapa Buku Kerja di Excel (menggunakan Power Query)”

1. Taruchit Goyal

   Yang terhormat,

   Terima kasih telah berbagi tutorialnya. Itu bekerja dengan baik untuk saya kecuali pada satu titik

   Saya memiliki kolom di buku kerja yang memiliki data seperti 0. 5, 1, 2. 5

   Namun, setelah digabungkan dengan langkah-langkah di atas, hasilnya menjadi 0. 5 sebagai 0, 2. 5 sebagai 2

   Dengan demikian, akan sangat membantu jika Anda dapat membagikan solusi untuk masalah tersebut

2. Pankaj kumar

   Pak saya punya satu buku kerja excel dimana saya telah membuat 5 lembar wilayah timur, barat, utara, selatan dan tengah dan dalam data ini telah ditambahkan
   Saya ingin menggabungkan 5 lembar data excel dari 1 buku kerja excel dengan power query tolong beri tahu saya pak

3. Vinod Reddy

   Sumut yang terhormat,
   Pertama-tama, saya ingin mengucapkan terima kasih banyak atas tips dan tutorial excelnya

   Saya mencari bantuan dalam skenario berikut

   Saya memiliki sekitar 50 buku kerja. Mantan. Perusahaan 1, Perusahaan 2, 3…dan seterusnya…. dan buku kerja baru akan ditambahkan setiap minggu

   Dan setiap buku kerja memiliki "Tabel" yang disebut "ProductData"

   Saya memiliki buku kerja lain yang disebut "Data Master"

   Jika saya mengetik "Perusahaan 1" atau "Perusahaan 1. xlsx" di sel 'A1' dari "Data Master", tabel "ProductData" dari "perusahaan 1" perlu diimpor dalam rentang sel mulai dari 'A2'

   Itu harus sumber "ProductData" dari nama file yang disebutkan di 'A1'
   Itu harus Dinamis
   Itu harus bekerja tanpa membuka file sumber
   Itu juga harus bersumber dari buku kerja baru yang mungkin ditambahkan ke folder yang sama nanti

   Secara sederhana “Fungsi INDIRECT tanpa membuka file sumber”

   Tolong bantu saya dalam memecahkan ini

   Mohon beri tahu saya jika Anda memerlukan informasi lebih lanjut

   Saya sangat menantikan kabar dari Anda

   Terima kasih banyak lagi untuk pelajaran excel yang luar biasa

4. Hariprasad

   terima kasih. Itu berhasil dan menghemat waktu saya

5. Mohammad Nahedh

   Ketika saya me-refresh semua data pada file gabungan dan file sumber sudah terbuka, saya akan melihat pesan kesalahan yang mengharuskan untuk menutup semua file sumber, dapatkah saya menyelesaikan masalah ini tanpa menutup file

6. Gunasekaran

   Halo semua,

   Tolong bantu saya, saya memiliki empat folder berbeda seperti Amerika, Asia, Eropa, Pune, Setiap folder memiliki file berbeda tetapi Nama yang Sama dan semua kumpulan file untuk dua kali waktu Wilayah disegarkan

   Contoh – Nama File – Integrasi laporan log Co. 989 16:00 & Integrasi laporan log Co. 989 08 AM, urutan yang sama Amerika, Eropa dan Wilayah Pune untuk semua Folder

   Cara menggabungkan semua file folder dalam satu file. jika memungkinkan untuk mengambil file terbaru akan baik-baik saja,

   Hargai bantuan Anda

7. Sam

   Itu bekerja dengan sempurna
   Saya memiliki banyak file, semuanya dengan nama yang berbeda, tetapi nama sheet yang sama, dan saya hanya ingin satu sheet dari setiap file, dan metode ini memungkinkan saya untuk memadatkan semuanya menjadi satu sheet yang sama.
   Cemerlang, kamu adalah bintang
   Terimakasih banyak,
   Sam

8. Bukit Bernadette

   Terima kasih banyak. Sayangnya saya sudah menyalin data secara manual dari 21 file, tetapi mencarinya nanti dan mencobanya dan saya dapat menghemat lebih banyak waktu. Tutorial yang bagus

9. CK Meena

   Pak bagus, sudah selesai. Terima kasih banyak. Satu masalah adalah setelah digabungkan, lembar kueri tidak dapat diedit sepenuhnya?

10. Semaj

    Apa batasan jumlah file yang bisa berada di folder?

    Bagaimana cara mengimpor banyak file ke Access?

    Impor semua file excel di folder ke tabel di Microsoft Access .

    Klik Telusuri

    Pilih Folder tempat file yang ingin Anda impor berada

    Klik Impor

    Impor Pesan Berhasil

    Tabel dibuat dari file

    Tabel menyertakan bidang yang menyatakan nama file

    Bagaimana Anda menggabungkan file di Access?

    Anda membuat gabungan dalam dengan menyeret bidang dari satu sumber data ke bidang di sumber data lain . Access menampilkan garis di antara dua bidang untuk menunjukkan bahwa gabungan telah dibuat. Nama tabel tempat record digabungkan.