David dari Florida menanyakan soalan hari ini:
Saya mempunyai dua buku kerja. Kedua-duanya mempunyai data yang sama di lajur A, tetapi lajur yang tersisa berbeza. Bagaimana saya boleh menggabungkan kedua-dua buku kerja tersebut?
Saya bertanya kepada David adakah mungkin satu buku kerja mempunyai lebih banyak rekod daripada yang lain. Dan jawapannya adalah Ya. Saya bertanya kepada David adakah bidang utama hanya muncul sekali dalam setiap fail. Jawapannya juga ya. Hari ini, saya akan menyelesaikannya dengan Power Query. Alat Kuasa Kuasa terdapat dalam versi Windows Excel 2016+ di bahagian Dapatkan & Transform tab Data. Sekiranya anda mempunyai versi Windows 2010 Excel 2010 atau Excel 2013, anda boleh memuat turun tambahan Kuasa Kuasa untuk versi tersebut.
Inilah buku kerja David 1. Ia mempunyai Product dan kemudian tiga lajur data.
Inilah buku kerja David 2. Ia mempunyai Kod Produk dan lajur lain. Dalam contoh ini, terdapat produk tambahan dalam buku kerja2, tetapi penyelesaiannya akan berfungsi sekiranya buku kerja mempunyai lajur tambahan.
Berikut adalah langkah-langkahnya:
-
Pilih Data, Dapatkan Data, Dari Fail, Dari Buku Kerja:
Muatkan data dari fail - Semak imbas ke buku kerja pertama dan klik OK
- Dalam dialog Navigator, pilih lembaran kerja di sebelah kiri. (Walaupun hanya ada satu lembar kerja, anda harus memilihnya.) Anda akan melihat data di sebelah kanan.
- Dalam dialog Navigator, buka dropdown Load dan pilih Load To…
- Pilih Hanya Buat Sambungan dan tekan OK.
-
Ulangi langkah 1-5 untuk buku kerja kedua.
Buat sambungan ke buku kerja Sekiranya anda telah membuat kedua-dua buku kerja, anda akan melihat dua sambungan di Panel Pertanyaan & Sambungan di sebelah kanan skrin Excel anda.
Sambungan ke kedua buku kerja Teruskan dengan langkah-langkah menggabungkan buku kerja:
-
Data, Dapatkan Data, Gabungkan Pertanyaan, Gabungan.
Gabungkan dua pertanyaan dengan lajur yang berbeza - Dari drop down atas dalam dialog Gabungan, pilih pertanyaan pertama.
- Dari drop-down kedua dalam dialog Gabungan, pilih pertanyaan kedua.
- Klik pada tajuk Produk di pratonton atas (ini adalah bidang utama. Perhatikan bahawa anda boleh memilih dua atau lebih bidang utama dengan Ctrl + Mengklik)
- Klik pada tajuk Kod Produk pada pratonton kedua.
-
Buka Jenis Gabungan dan pilih Penuh Luar (Semua Baris Dari Keduanya)
Langkah 8 - 12 digambarkan di sini -
Klik OK. Pratonton data tidak menunjukkan baris tambahan dan hanya menunjukkan "Jadual" berulang kali di lajur terakhir.
Ini tidak kelihatan menjanjikan - Perhatikan ada ikon "Luaskan" di tajuk DavidTwo. Klik ikon itu.
-
Pilihan, tetapi saya selalu memilih "Gunakan Nama Lajur Asal Sebagai Awalan". Klik OK.
Luaskan bidang dari buku kerja 2 Hasilnya ditunjukkan dalam pratonton ini:
Semua rekod dari kedua-dua buku kerja - Dalam Kueri Kuasa, gunakan Laman Utama, Tutup & Muatkan.
Inilah ciri indahnya: jika data yang mendasari di mana-mana buku kerja berubah, anda boleh mengklik ikon Segarkan untuk menarik data baru ke dalam buku kerja hasil.
Catatan
Ikon untuk Refresh biasanya disembunyikan. Seret tepi kiri panel Pertanyaan & Sambungan ke kiri untuk menampakkan ikon.
Tonton Video
Transkrip Video
Belajar Excel dari Podcast, Episod 2216: Gabungkan Dua Buku Kerja Berdasarkan Lajur Umum.
Hei, selamat datang kembali ke netcast, saya Bill Jelen. Soalan hari ini dari David, yang berada dalam seminar saya di Melbourne, Florida, untuk Space Coast Bab IIA.
David mempunyai dua buku kerja yang berbeza di mana Kolom A mempunyai persamaan antara kedua-duanya. Jadi, inilah Buku Kerja 1, inilah Buku Kerja 2 - kedua-duanya mempunyai kod produk. Yang ini mempunyai item yang tidak dimiliki oleh yang pertama, atau sebaliknya, dan David ingin menggabungkan semua lajur. Jadi, kami mempunyai tiga lajur di sini dan empat lajur di sini. Saya memasukkan kedua-duanya ke dalam buku kerja yang sama, sekiranya anda memuat turun buku kerja untuk berfungsi bersama. Ambil masing-masing, pindahkan ke buku kerjanya sendiri dan simpan.
Baiklah, untuk menggabungkan fail-fail ini, kita akan menggunakan Power Query. Kuasa Kuasa terbina dalam Excel 2016. Sekiranya anda menggunakan versi Windows 10 atau 13, anda boleh pergi ke Microsoft dan memuat turun Kueri Kuasa. Anda boleh bermula dari buku kerja kosong baru dengan lembaran kerja kosong. Anda akan menyimpan fail ini - Simpan seperti, anda tahu, mungkin Buku Kerja, untuk menunjukkan hasil gabungan fail .xlsx. Baiklah? Dan apa yang akan kita lakukan ialah, kita akan melakukan dua pertanyaan. Kita akan pergi ke Data, Dapatkan Data, Dari Fail, Dari Buku Kerja, dan kemudian kita akan memilih fail pertama. Dalam pratonton, pilih helaian yang mempunyai data anda, dan kami tidak perlu melakukan apa-apa terhadap data ini. Oleh itu, buka kotak muat dan pilih Muat Ke, Buat Sambungan sahaja, klik OK. Sempurna. Sekarang, kita akan mengulanginya untuk item kedua - Data, Dari Fail,Dari Buku Kerja, pilih DavidTwo, pilih nama helaian, dan kemudian buka beban, Muat Ke, Buat Buat Sambungan sahaja. Anda akan melihat di sini di panel ini, kami mempunyai kedua-dua sambungan. Baiklah.
Sekarang kerja sebenar - Data, Dapatkan Data, Gabungkan Pertanyaan, Gabungkan, dan kemudian dalam dialog Gabungan, pilih DavidOne, DavidTwo, dan langkah seterusnya ini sama sekali tidak menarik. Anda mesti melakukan ini. Pilih lajur atau lajur yang sama - jadi Produk dan Produk. Baiklah. Dan kemudian, berhati-hati di sini dengan jenis gabung. Saya mahu semua baris dari kedua-duanya kerana satu barisan mungkin mempunyai baris tambahan dan saya perlu melihatnya, dan kemudian kita klik OK. Baiklah. Dan inilah hasil awalnya. Nampaknya ia tidak berfungsi; nampaknya ia tidak menambahkan item tambahan yang terdapat dalam fail 2. Dan kami mempunyai ruangan 5 ini - kini tidak ada. Saya akan klik kanan lajur 5 dan katakan, Buang lajur itu. Oleh itu, buka ikon pengembangan ini dan hapus centang kotak ini untuk Gunakan nama lajur asli sebagai awalan, dan BAM! ianya berfungsi. Jadi item tambahan yang ada di Fail 2, yang tidak ada di Fail 1,memang muncul.
Baiklah. Sekarang dalam fail hari ini, nampaknya lajur Kod Produk ini lebih baik daripada lajur Produk ini, kerana mempunyai baris tambahan. Tetapi mungkin ada hari di masa depan di mana Workbook 1 mempunyai perkara yang tidak dimiliki Workbook 2. Oleh itu, saya akan meninggalkan mereka berdua di sana, dan saya tidak akan menyingkirkan sebarang nol kerana, seperti, walaupun baris ini di bahagian bawah nampaknya benar-benar batal, mungkin ada masa depan keadaan di mana kami mempunyai beberapa nol di sini kerana ada sesuatu yang hilang. Baiklah? Jadi, akhirnya, Tutup & Muat, dan kami mempunyai enam belas baris kami.
Sekarang, pada masa akan datang, katakan bahawa sesuatu berubah. Baiklah, jadi kami akan kembali ke salah satu daripada dua fail tersebut dan saya akan menukar kelas untuk Apple menjadi 99, dan mari kita memasukkan sesuatu yang baru dan menyimpan buku kerja ini. Baiklah. Dan kemudian, jika kami mahu fail penggabungan kami dikemas kini, datang ke sini - sekarang, berhati-hatilah, apabila anda melakukan ini pada kali pertama, anda tidak dapat melihat ikon Segarkan - anda perlu mengambil bar ini dan menyeretnya ke atas . Dan kita akan melakukan Refresh, dan 17 baris dimuat, semangka muncul, Apple berubah menjadi 99-- itu adalah perkara yang indah. Sekarang, hey, adakah anda ingin mengetahui mengenai Kueri Kuasa? Beli buku ini oleh Ken Puls dan Miguel Escobar, M adalah untuk (DATA) MONKEY. Saya akan mempercepat anda.
Penyelesaian hari ini: David dari Florida mempunyai dua buku kerja yang ingin digabungkannya; mereka berdua mempunyai bidang yang sama di Lajur A, tetapi lajur yang lain semuanya berbeza; satu buku kerja mungkin mempunyai item tambahan yang tidak ada di dalamnya dan David menginginkannya; tidak ada pendua dalam mana-mana fail; kita akan menggunakan pertanyaan kuasa untuk menyelesaikannya, jadi mulakan dengan buku kerja kosong baru pada lembaran kerja kosong; anda akan melakukan tiga pertanyaan, yang pertama - Data, Dari Fail, Buku Kerja, dan kemudian Muatkan ke Sambungan yang Dibuat sahaja; perkara yang sama untuk buku kerja kedua, dan kemudian Data, Dapatkan Data, Gabungkan, pilih dua sambungan, pilih lajur yang biasa terdapat pada kedua - dalam kes saya, Produk - dan kemudian dari Jenis Sambungan, anda mahu bergabung sepenuhnya semua dari Fail 1, semua dari Fail 2. Dan yang indah adalah jika data yang mendasarinya berubah,anda boleh memuatkan semula pertanyaan.
Untuk memuat turun buku kerja dari video hari ini, lawati URL dalam keterangan YouTube.
Hei, saya mahu seperti David kerana hadir ke seminar saya, saya ingin mengucapkan terima kasih kerana berhenti. Sampai jumpa lagi untuk siaran net lain dari.
Muat turun Fail Excel
Untuk memuat turun fail excel: menggabungkan-berdasarkan-umum-lajur.xlsx
Kuasa Kuasa adalah alat yang luar biasa di Excel.
Pemikiran Excel Hari Ini
Saya telah meminta nasihat rakan Excel saya mengenai Excel. Pemikiran hari ini untuk merenungkan:
"Sentiasa tekan F4 ketika anda membaca julat atau matriks dalam fungsi"
Tanja Kuhn
