Gabungkan Berdasarkan Kolom Umum - Tips Excel

David dari Florida mengajukan pertanyaan hari ini:

Saya memiliki dua buku kerja. Keduanya memiliki data yang sama di kolom A, tetapi kolom lainnya berbeda. Bagaimana saya bisa menggabungkan dua buku kerja itu?

Saya bertanya kepada David apakah mungkin satu buku kerja memiliki lebih banyak catatan daripada yang lain. Dan jawabannya adalah ya. Saya bertanya kepada David apakah bidang kunci hanya muncul sekali di setiap file. Jawabannya juga ya. Hari ini, saya akan menyelesaikannya dengan Power Query. Alat Power Query ditemukan di versi Windows Excel 2016+ di bagian Dapatkan & Transformasi pada tab Data. Jika Anda memiliki versi Windows Excel 2010 atau Excel 2013, Anda bisa mengunduh add-in Power Query untuk versi tersebut.

Ini adalah buku kerja David 1. Ini memiliki Produk dan kemudian tiga kolom data.

Buku kerja pertama

Ini adalah buku kerja David 2. Ini memiliki Kode Produk dan kemudian kolom lainnya. Dalam contoh ini, ada produk tambahan di buku kerja2, tetapi solusi akan berfungsi jika salah satu buku kerja memiliki kolom tambahan.

Buku kerja kedua

Berikut langkah-langkahnya:

  1. Pilih Data, Dapatkan Data, Dari File, Dari Buku Kerja:

    Muat data dari sebuah file
  2. Telusuri ke buku kerja pertama dan klik OK
  3. Dalam dialog Navigator, pilih lembar kerja di sebelah kiri. (Meskipun hanya ada satu lembar kerja, Anda harus memilihnya.) Anda akan melihat data di sebelah kanan.
  4. Pada dialog Navigator, buka dropdown Load dan pilih Load To…
  5. Pilih Only Create a Connection dan tekan OK.

  6. Ulangi langkah 1-5 untuk buku kerja kedua.

    Buat koneksi ke buku kerja

    Jika Anda telah menyelesaikan kedua buku kerja, Anda akan melihat dua koneksi pada Panel Kueri & Koneksi di sebelah kanan layar Excel Anda.

    Koneksi ke kedua buku kerja

    Lanjutkan dengan langkah-langkah untuk menggabungkan buku kerja:

  7. Data, Dapatkan Data, Gabungkan Kueri, Gabungkan.

    Gabungkan dua kueri dengan kolom yang berbeda
  8. Dari drop-down atas di dialog Gabung, pilih kueri pertama.
  9. Dari drop-down kedua dalam dialog Gabung, pilih kueri kedua.
  10. Klik pada judul Produk di pratinjau atas (ini adalah bidang kunci. Perhatikan bahwa Anda dapat memilih dua atau lebih bidang kunci dengan Ctrl + Mengklik)
  11. Klik pada judul Kode Produk di pratinjau kedua.

  12. Buka Tipe Gabungan dan pilih Luar Penuh (Semua Baris Dari Keduanya)

    Langkah 8 - 12 diilustrasikan di sini

  13. Klik OK. Pratinjau data tidak menampilkan baris tambahan dan hanya menampilkan "Tabel" berulang kali di kolom terakhir.

    Ini tidak terlihat menjanjikan
  14. Perhatikan ada ikon "Perluas" di judul untuk DavidTwo. Klik ikon itu.

  15. Opsional, tetapi saya selalu membatalkan pilihan "Gunakan Nama Kolom Asli Sebagai Awalan". Klik OK.

    Perluas bidang dari buku kerja 2

    Hasilnya ditunjukkan dalam pratinjau ini:

    Semua rekaman dari salah satu buku kerja
  16. Di Power Query, gunakan Beranda, Tutup & Muat.

Berikut ini fitur cantiknya: jika data yang mendasari di salah satu buku kerja berubah, Anda bisa mengklik ikon Refresh untuk menarik data baru ke buku kerja hasil.

Ulangi Langkah 1-16 dengan mengklik ikon Segarkan ini.

Catatan

Ikon Refresh biasanya tersembunyi. Seret tepi kiri panel Queries & Connections ke kiri untuk menampilkan ikon.

Menonton video

Transkrip Video

Belajar Excel dari Podcast, Episode 2216: Gabungkan Dua Buku Kerja Berdasarkan Kolom Umum.

Hai, selamat datang kembali di netcast, saya Bill Jelen. Pertanyaan hari ini dari David, yang berada di seminar saya di Melbourne, Florida, untuk Space Coast Chapter IIA.

David memiliki dua buku kerja berbeda di mana Kolom A sama di antara keduanya. Jadi, inilah Buku Kerja 1, ini Buku Kerja 2-- keduanya memiliki kode produk. Yang ini memiliki item yang tidak dimiliki oleh yang pertama, atau sebaliknya, dan David ingin menggabungkan semua kolom. Jadi, kami memiliki tiga kolom di sini dan empat kolom di sini. Saya meletakkan keduanya di buku kerja yang sama, jika Anda mengunduh buku kerja untuk dikerjakan. Ambil masing-masing, pindahkan ke buku kerjanya sendiri dan simpan.

Baiklah, untuk menggabungkan file ini, kita akan menggunakan Power Query. Power Query sudah terpasang di Excel 2016. Jika Anda menggunakan Windows versi 10 atau 13, Anda bisa keluar ke Microsoft dan mengunduh Power Query. Anda bisa memulai dari buku kerja kosong baru dengan lembar kerja kosong. Anda akan menyimpan file ini-- Simpan sebagai, Anda tahu, mungkin Buku Kerja, untuk menampilkan hasil gabungan file .xlsx. Baik? Dan yang akan kami lakukan adalah, kami akan melakukan dua kueri. Kami akan pergi ke Data, Dapatkan Data, Dari File, Dari Buku Kerja, lalu kami akan memilih file pertama. Dalam pratinjau, pilih lembar yang berisi data Anda, dan kami tidak perlu melakukan apa pun pada data ini. Jadi buka saja kotak muat dan pilih Muat Ke, Hanya Buat Koneksi, klik OK. Sempurna. Sekarang, kita akan mengulanginya untuk item kedua-- Data, From File,Dari Buku Kerja, pilih DavidTwo, pilih nama lembar, lalu buka muat, Muat Ke, Hanya Buat Koneksi. Anda akan melihat di sini di panel ini, kami memiliki kedua koneksi yang ada. Baik.

Sekarang pekerjaan aktual-- Data, Dapatkan Data, Gabungkan Kueri, Gabungkan, lalu dalam dialog Gabung, pilih DavidOne, DavidTwo, dan langkah selanjutnya ini sama sekali tidak intuitif. Anda harus melakukan ini. Pilih kolom atau kolom yang sama-- jadi Produk dan Produk. Baik. Dan kemudian, berhati-hatilah di sini dengan tipe gabungan. Saya ingin semua baris dari keduanya karena satu mungkin memiliki baris tambahan dan saya perlu melihatnya, lalu kita klik OK. Baik. Dan inilah hasil awalnya. Sepertinya itu tidak berhasil; sepertinya itu tidak menambahkan item tambahan yang ada di file 2. Dan kami memiliki kolom ini 5-- sekarang null. Saya akan mengklik kanan kolom 5 dan berkata, Hapus kolom itu. Jadi buka ikon luaskan ini dan hapus centang pada kotak ini untuk Gunakan nama kolom asli sebagai awalan, dan BAM! berhasil. Jadi item tambahan yang ada di File 2, yang tidak ada di File 1,memang muncul.

Baik. Sekarang di file hari ini, sepertinya kolom Kode Produk ini lebih baik dari kolom Produk ini, karena memiliki baris tambahan. Namun mungkin akan ada hari di masa depan di mana Buku Kerja 1 memiliki hal-hal yang tidak dimiliki Buku Kerja 2. Jadi saya akan meninggalkan keduanya di sana, dan saya tidak akan menghilangkan nol karena, seperti, meskipun baris di bagian bawah ini tampaknya benar-benar nol, mungkin ada situasi di masa depan di mana kami memiliki beberapa null di sini karena ada sesuatu yang hilang. Baik? Jadi, akhirnya, Tutup & Muat, dan kami memiliki enam belas baris kami.

Sekarang, di masa depan, katakanlah ada sesuatu yang berubah. Baiklah, jadi kita akan kembali ke salah satu dari dua file itu dan saya akan mengubah kelas untuk Apple menjadi 99, dan mari kita bahkan memasukkan sesuatu yang baru dan menyimpan buku kerja ini. Baik. Dan kemudian, jika kita ingin file gabungan kita diperbarui, datang ke sini-- sekarang, hati-hati, saat Anda melakukan ini pertama kali, Anda tidak dapat melihat ikon Segarkan-- Anda harus mengambil bilah ini dan menyeretnya ke atas . Dan kita akan melakukan Refresh, dan 17 baris dimuat, semangka muncul, Apple berubah menjadi 99-- itu hal yang indah. Sekarang, apakah Anda ingin mempelajari Power Query? Beli buku ini oleh Ken Puls dan Miguel Escobar, M adalah untuk (DATA) MONKEY. Aku akan mempercepatnya.

Penutupan hari ini: David dari Florida memiliki dua workbook yang ingin dia gabungkan; keduanya memiliki bidang yang sama di Kolom A, tetapi kolom lainnya semuanya berbeda; satu buku kerja mungkin memiliki item tambahan yang tidak ada di yang lain dan David menginginkannya; tidak ada duplikat di salah satu file; kita akan menggunakan power query untuk menyelesaikan ini, jadi mulailah dengan buku kerja kosong baru di lembar kerja kosong; Anda akan melakukan tiga kueri, yang pertama-- Data, Dari File, Buku Kerja, lalu Muat ke hanya Koneksi yang Dibuat; hal yang sama untuk buku kerja kedua, lalu Data, Dapatkan Data, Gabungkan, pilih dua koneksi, pilih kolom yang umum di keduanya - dalam kasus saya, Produk-- lalu dari Tipe Gabungan, Anda ingin bergabung penuh semua dari File 1, semua dari File 2. Dan yang indah adalah jika data yang mendasarinya berubah,Anda bisa menyegarkan kueri.

Untuk mengunduh buku kerja dari video hari ini, kunjungi URL di deskripsi YouTube.

Baiklah, saya ingin seperti David untuk muncul di seminar saya, saya ingin mengucapkan terima kasih telah mampir. Sampai jumpa di lain waktu untuk netcast lain dari.

Unduh File Excel

Untuk mengunduh file excel: gabungkan-based-on-common-column.xlsx

Power Query adalah alat luar biasa di Excel.

Pemikiran Excel Hari Ini

Saya telah meminta saran dari teman-teman Master Excel saya tentang Excel. Hari ini pemikiran untuk direnungkan:

"Selalu tekan F4 saat Anda membaca rentang atau matriks dalam suatu fungsi"

Tanja Kuhn

Artikel yang menarik...