Bagaimana Anda menarik data secara otomatis dari beberapa buku kerja excel?

Jika Anda bertanya kepada orang yang bekerja dengan data, Anda akan mengetahui bahwa menggabungkan file Excel atau menggabungkan buku kerja adalah bagian dari pekerjaan sehari-hari mereka.

Setuju?

Sebuah contoh sederhana. Katakanlah Anda ingin membuat laporan penjualan dan Anda memiliki data empat zona berbeda dalam empat file berbeda

Sekarang

Hal pertama yang perlu Anda lakukan adalah menggabungkan file-file itu dalam satu buku kerja dan baru kemudian Anda dapat membuat laporan lebih lanjut

Intinya adalah. Anda harus memiliki metode yang dapat Anda gunakan untuk menggabungkan file-file ini. Katakan "YA" di bagian komentar jika Anda ingin mengetahui metode terbaik untuk ini

Hari ini di posting ini, saya akan berbagi dengan Anda cara terbaik untuk menggabungkan data dari beberapa file Excel ke dalam satu buku kerja.

Tapi, inilah kickernya

Posting ini akan mengajarkan Anda sesuatu yang perlu Anda pelajari untuk digunakan dalam masalah data dunia nyata jadi pastikan untuk membaca seluruh posting

Cara Terbaik untuk Menggabungkan File Excel dengan Menggabungkan data ke dalam SATU Buku Kerja - POWER QUERY

Power query adalah cara terbaik untuk menggabungkan atau menggabungkan data dari beberapa file Excel dalam satu file. Anda perlu menyimpan semua file dalam satu folder dan kemudian menggunakan folder tersebut untuk memuat data dari file tersebut ke editor kueri daya. Ini juga memungkinkan Anda untuk mengubah data tersebut bersama dengan penggabungan

Ia bekerja seperti ini

1. Menyimpan Semua File ke dalam Satu Folder
2. Menggabungkannya menggunakan Power Query
3. Menggabungkan Data ke dalam Tabel Tunggal

Pastikan untuk mengunduh file contoh ini dari sini untuk mengikuti dan lihat tutorial ini untuk mempelajari kueri daya

Catatan. Untuk menggabungkan data dari file Excel yang berbeda, data Anda harus disusun dengan cara yang sama. Itu berarti jumlah kolom dan urutannya harus sama

Untuk menggabungkan file, Anda dapat menggunakan langkah-langkah berikut

1. Pertama-tama, ekstrak semua file dari folder sampel dan simpan folder itu di desktop (atau di mana pun Anda ingin menyimpannya)
   
2. Sekarang, hal selanjutnya adalah membuka buku kerja Excel baru dan membuka "POWER Query"
3. Untuk ini, buka Tab Data ➜ Dapatkan & Transformasikan Data ➜ Dapatkan Data ➜ Dari File ➜ Dari Folder
   
4. Di sini Anda perlu mencari folder tempat Anda menyimpan file
   
5. Pada akhirnya, klik OK, dan setelah Anda mengklik OK, Anda akan mendapatkan jendela yang mencantumkan semua file dari folder tersebut, seperti di bawah ini
   
6. Sekarang, Anda perlu menggabungkan data dari file-file ini dan untuk ini klik "Gabungkan & Edit"
   
7. Dari sini, hal selanjutnya adalah memilih tabel di mana Anda memiliki data di semua buku kerja dan ya, Anda akan mendapatkan pratinjaunya di samping jendela
   
8. Setelah Anda memilih tabel, klik OK. Pada titik ini, Anda telah menggabungkan data dari semua file ke dalam editor kueri daya dan, jika Anda melihat lebih dekat, Anda dapat melihat kolom baru dengan nama buku kerja tempat data diekstraksi
   
9. Jadi, klik kanan pada tajuk kolom dan pilih "Ganti Nilai"
   
10. Di sini, di "Nilai untuk Diganti" masukkan teks ". xlsx" dan biarkan "Ganti Dengan" kosong (di sini idenya adalah menghapus ekstensi file dari nama buku kerja)
    
11. Setelah itu, klik dua kali pada header dan pilih “Rename” untuk memasukkan nama kolom i. e. Daerah
    .
12. Pada titik ini, data gabungan Anda sudah siap dan yang Anda butuhkan hanyalah memuatnya ke dalam buku kerja baru Anda. Jadi, buka Tab Beranda dan klik tombol "Tutup dan Muat"
    

Sekarang Anda memiliki data gabungan (dari semua buku kerja) ke dalam satu buku kerja

Inilah momen JOY, tulis “Joy” di kolom komentar jika Anda suka menggunakan “Power Query untuk menggabungkan data dari banyak file”

Poin penting

Pada langkah-langkah di atas, kami telah menggunakan nama tabel untuk menggabungkan data dari semua file dan menambahkan semuanya ke dalam satu buku kerja. Tetapi tidak setiap saat Anda akan memiliki nama tabel yang sama di semua file Excel dan pada saat itu, Anda dapat menggunakan nama lembar kerja sebagai kunci untuk meringkas semua data tersebut.

Satu hal lagi

Seperti yang saya katakan, Anda dapat menggunakan nama lembar kerja untuk menggabungkan data dengan kueri daya, tetapi ada beberapa hal lagi yang ingin saya bagikan dengan Anda dan Anda harus mengurusnya. Power Query peka terhadap huruf besar/kecil, jadi saat menggabungkan file, pastikan untuk memiliki nama lembar kerja di semua buku kerja dengan huruf yang sama

Hal berikutnya adalah, memiliki nama yang sama untuk tajuk kolom, tetapi di sini penendang. Urutan kolom tidak masalah. Jika kolom1 di utara. xlsx adalah kolom2 di barat. xlsx, Power Query akan mencocokkannya, tetapi Anda harus memiliki nama kolom yang sama

Jadi sekarang, saat menggabungkan file menggunakan power query Anda bisa menggunakan nama lembar kerja alih-alih nama tabel, dan di sini Anda memiliki "SalesData" sebagai nama lembar kerja di semua file

Anda memilihnya dan mengklik "Gabungkan & Edit" dan ikuti semua langkah yang telah saya sebutkan dalam metode di atas

Mengapa Power Query adalah Cara Terbaik untuk Menggabungkan Data ke dalam Satu File?

Ya, saya penggemar berat Power Query dan Anda akan menyukainya jika mengetahui hal ini

Ini Waktu Nyata

Setelah Anda membuat kueri yang menggabungkan data dari beberapa buku kerja menjadi satu, Anda tidak perlu melakukannya lagi dan lagi

Katakanlah

Jika Anda menambahkan file baru ke folder Anda, Anda hanya perlu me-refresh tabel,

atau, segarkan kueri,

atau, gunakan tombol segarkan

Dan kueri daya akan memperbarui tabel Anda dan Anda akan memasukkan semua data ke dalam tabel dari semua buku kerja yang Anda miliki di folder itu

Itu cepat

Jika menggabungkan file secara manual atau bahkan dengan metode lain maka butuh waktu bagi Anda untuk menyalin-tempel data ke dalam satu tabel

Tetapi power query melakukannya di latar belakang dan Anda tidak perlu menghabiskan waktu ekstra

Menggabungkan Data dari Beberapa Buku Kerja Bila Anda tidak memiliki Nama yang Sama untuk Lembar Kerja dan data dalam Tabel

Inilah kebenaran yang sulit…

…bahwa dalam beberapa situasi, Anda tidak akan memiliki nama yang sama untuk lembar kerja dan tidak semua data dalam tabel sepanjang waktu

Sekarang, apa yang harus Anda lakukan dalam kasus itu?

Sehat…

…dalam hal ini, Anda harus tahu bagaimana Anda dapat menggabungkan data dari semua file dan saya tidak ingin ketinggalan untuk membagikan hal ini kepada Anda

Pastikan untuk mengunduh file sampel ini untuk mengikuti dan menyimpannya di desktop Anda

jadi tanpa basa-basi lagi, mari kita mulai

• Pertama-tama, buka kotak dialog “Dari Folder” untuk menemukan folder tempat Anda menyimpan semua file.
• Sekarang di kotak dialog ini, cari foldernya dan klik OK.

• Setelah itu, klik “Edit” untuk mengedit tabel.

• Pada titik ini, Anda akan memiliki tabel seperti di bawah ini di editor kueri daya Anda

• Selanjutnya, pilih dua kolom pertama tabel dan klik “Hapus Kolom Lainnya” dari menu klik kanan.

Dari sini, kita perlu menambahkan kolom khusus untuk mengambil data dari lembar kerja buku kerja.

• Untuk ini, buka Tambahkan Tab Kolom dan klik tombol “Kolom Kustom” . Ini akan membuka kotak dialog "Kolom Kustom".

• Di kotak dialog, masukkan =Excel. Buku Kerja([Konten]) dan klik OK.

… saat ini Anda memiliki kolom baru di tabel tetapi selanjutnya, Anda perlu mengekstrak data darinya

• Sekarang, buka filter dari kolom ubahsuaian yang baru ditambahkan dan klik OK untuk meluaskan semua data ke dalam tabel.

• Di sini Anda memiliki tabel yang baru diperluas dengan beberapa kolom baru
• Sekarang dari tabel baru ini, hapus semua kolom kecuali kolom ketiga dan keempat

• Jadi, buka filter untuk kolom “Custom. Data” untuk meluaskannya dan klik OK.

Saat Anda mengklik OK, Anda akan mendapatkan semua data dari semua file ke dalam satu tabel…

… Anda perlu membuat beberapa perubahan agar SEMPURNA.

Jika Anda perhatikan, semua tajuk kolom menjadi data itu sendiri

jadi Anda perlu menambahkan judul kolom

• Untuk melakukan ini, Anda perlu mengklik dua kali pada header dan menambahkan nama, atau Anda dapat mengklik kanan dan memilih ganti nama

Selanjutnya Anda perlu mengecualikan judul yang Anda miliki di tabel data

• Sekarang buka opsi filter kolom apa pun dan batalkan pilihan nama tajuk yang Anda miliki di data kolom dan klik OK setelah itu

Sekarang data kita siap dimuat ke lembar kerja, jadi, buka Tab Beranda dan klik tutup dan muat

Selamat. Anda baru saja menggabungkan data dari buku kerja yang berbeda (dengan nama lembar kerja yang berbeda dan tanpa tabel apa pun).

Ini juga penting

Pada titik ini, Anda telah menggabungkan data ke dalam satu tabel

Tapi ada satu hal yang perlu Anda lakukan…

… dan itu menerapkan beberapa pemformatan padanya dan memastikan bahwa itu tidak akan hilang saat Anda memperbarui data Anda

Inilah yang perlu Anda lakukan…

• Pertama-tama, pilih kolom di mana Anda memiliki tanggal (seperti yang diformat sebagai angka sekarang) dan format sebagai tanggal
• Setelah itu, lebarkan semua kolom sesuai dengan data yang Anda miliki di dalamnya
• Di sini Anda juga dapat memformat jumlah dan harga sebagai “Mata Uang”.  

Tetapi hal selanjutnya adalah memperbaiki pemformatan ini

• Untuk ini, buka "Tab Desain", dan buka properti
• Hapus centang "Sesuaikan Kolom" dan centang tanda "Pertahankan Pemformatan Sel"
• Ya, itu dia

Sekarang Anda memiliki kueri di buku kerja Anda yang dapat menggabungkan data dari beberapa file

dan menggabungkannya menjadi satu buku kerja

meskipun nama lembar kerja tidak sama atau jika Anda tidak memiliki tabel

Dan ya, Anda juga telah memperbaiki pemformatan. ?

Pada akhirnya,

Seperti yang saya katakan, POWER QUERY itu nyata dan jika Anda sering menggunakan untuk menggabungkan data dari banyak file maka Anda harus menggunakan metode ini…

… karena ini adalah penyiapan SATU KALI

Yang paling penting adalah Anda ketika menggunakan power query Anda bahkan dapat membersihkan data dari file-file itu juga

Saya harap tutorial ini akan membantu Anda Menjadi Lebih Baik di Excel. Tapi sekarang, kau perlu memberitahuku satu hal

Metode apa yang Anda gunakan untuk menggabungkan data dari banyak file?

Pastikan untuk berbagi pandangan Anda dengan saya di bagian komentar, saya akan senang mendengar dari Anda. Dan tolong, jangan lupa untuk membagikan postingan ini dengan teman-teman Anda, saya yakin mereka akan menghargainya

Bagikan0

Tweet0

Anda harus Baca ini Selanjutnya

• Gabungkan Data Dari Beberapa Lembar Kerja. Opsi ini dapat membantu Anda menggabungkan data dari beberapa lembar kerja menjadi satu
• Unpivot Data menggunakan Power Query. Dalam situasi ini, Anda perlu berusaha dan menghabiskan waktu berharga Anda untuk membuatnya dapat digunakan kembali
• Buat Pivot dari Banyak File. Dalam posting ini, saya ingin menunjukkan kepada Anda proses 3 langkah untuk membuat tabel pivot dengan menggunakan data dari banyak

tentang Penulis

Puneet menggunakan Excel sejak masa kuliahnya. Dia membantu ribuan orang untuk memahami kekuatan spreadsheet dan mempelajari Microsoft Excel. Anda dapat menemukannya online, menge-tweet tentang Excel, di jalur lari, atau terkadang mendaki gunung

Bagaimana cara menarik data dari beberapa lembar kerja di Excel berdasarkan kriteria?

Bagaimana cara mentransfer data secara otomatis dari satu file Excel ke file lainnya?

Bagaimana cara menarik data secara otomatis di Excel?