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)
- Buka tab Data
- Di grup Get & Transform, klik drop down New Query
- Arahkan kursor Anda pada 'Dari File' dan klik 'Dari Folder'
- Di kotak dialog Folder, masukkan jalur file dari folder yang berisi file, atau klik Telusuri dan temukan folder tersebut
- Klik Oke
- Di kotak dialog yang terbuka, klik tombol gabungkan
- Klik 'Gabungkan & Muat'
- 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
- 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
- Buka tab Data
- Di grup Get & Transform, klik drop down New Query
- Arahkan kursor Anda pada 'Dari File' dan klik 'Dari Folder'
- Di kotak dialog Folder, masukkan jalur file dari folder yang berisi file, atau klik Telusuri dan temukan folder tersebut
- Klik Oke
- Di kotak dialog yang terbuka, klik tombol gabungkan
- Klik 'Gabungkan & Muat'
- 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.
- 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
- Buka tab Data
- Di grup Get & Transform, klik drop down New Query
- Arahkan kursor Anda pada 'Dari File' dan klik 'Dari Folder'
- Di kotak dialog Folder, masukkan jalur file dari folder yang berisi file, atau klik Telusuri dan temukan folder tersebut
- Klik Oke
- Di kotak dialog yang terbuka, klik tombol Edit. Ini akan membuka editor Power Query di mana Anda akan melihat detail semua file di folder
- 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
- Di pita Editor Kueri, klik 'Tambahkan kolom', lalu klik 'Kolom Kustom'
- 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
- 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.
- Klik ikon panah ganda di bagian atas kolom 'Impor Data'
- Di kotak data kolom yang terbuka, hapus centang pada 'Gunakan kolom asli sebagai awalan', lalu klik OK
- 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
- Di kolom Jenis, filter daftar untuk hanya menampilkan Tabel
- Tahan tombol kontrol dan pilih kolom Nama dan Data. Sekarang, klik kanan dan hapus semua kolom lainnya
- Di kolom Data, klik ikon panah ganda di kanan atas Header Data
- Di kotak data kolom yang terbuka, klik OK. Ini akan menggabungkan data di semua tabel dan ditampilkan di Power Query
- 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
BUKU EXCEL GRATIS
Dapatkan 51 Ebook Tips Excel untuk meningkatkan produktivitas Anda dan menyelesaikan pekerjaan lebih cepat
Nama
Surel
YA - KIRIM SAYA EBOOK
17 pemikiran pada “Menggabungkan Data dari Beberapa Buku Kerja di Excel (menggunakan Power Query)”
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
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 pakVinod Reddy
Sumut yang terhormat,
Pertama-tama, saya ingin mengucapkan terima kasih banyak atas tips dan tutorial excelnyaSaya 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 nantiSecara 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
Hariprasad
terima kasih. Itu berhasil dan menghemat waktu saya
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
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
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,
SamBukit 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
CK Meena
Pak bagus, sudah selesai. Terima kasih banyak. Satu masalah adalah setelah digabungkan, lembar kueri tidak dapat diedit sepenuhnya?
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 TelusuriPilih Folder tempat file yang ingin Anda impor beradaKlik ImporImpor Pesan BerhasilTabel dibuat dari fileTabel menyertakan bidang yang menyatakan nama fileBagaimana 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.