Menggabungkan dua senarai tanpa pendua

Situasi klasik: anda mempunyai dua senarai yang perlu digabungkan menjadi satu. Selain itu, dalam senarai awal boleh terdapat kedua-dua elemen unik dan yang sepadan (kedua-duanya di antara senarai dan di dalam), tetapi pada output anda perlu mendapatkan senarai tanpa pendua (pengulangan):

Mari kita lihat secara tradisional beberapa cara untuk menyelesaikan masalah biasa seperti itu - daripada primitif "di dahi" kepada yang lebih kompleks, tetapi elegan.

Kaedah 1: Alih Keluar Pendua

Anda boleh menyelesaikan masalah dengan cara yang paling mudah – salin secara manual elemen kedua-dua senarai menjadi satu dan kemudian gunakan alat itu pada set yang terhasil. Keluarkan Duplikat daripada tab Tarikh (Data — Alih Keluar Pendua):

Sudah tentu, kaedah ini tidak akan berfungsi jika data dalam senarai sumber sering berubah – anda perlu mengulangi keseluruhan prosedur selepas setiap perubahan sekali lagi. 

Kaedah 1a. jadual pangsi

Kaedah ini, sebenarnya, kesinambungan logik dari yang sebelumnya. Jika senarai tidak begitu besar dan bilangan maksimum elemen di dalamnya diketahui terlebih dahulu (contohnya, tidak lebih daripada 10), maka anda boleh menggabungkan dua jadual menjadi satu dengan pautan langsung, tambah lajur dengan satu di sebelah kanan dan bina jadual ringkasan berdasarkan jadual yang terhasil:

Seperti yang anda ketahui, jadual pangsi mengabaikan pengulangan, jadi pada output kami akan mendapat senarai gabungan tanpa pendua. Lajur tambahan dengan 1 diperlukan hanya kerana Excel boleh membina jadual ringkasan yang mengandungi sekurang-kurangnya dua lajur.

Apabila senarai asal ditukar, data baharu akan pergi ke jadual gabungan melalui pautan terus, tetapi jadual pangsi perlu dikemas kini secara manual (klik kanan – Kemas kini & Simpan). Jika anda tidak memerlukan pengiraan semula dengan cepat, maka lebih baik menggunakan pilihan lain.

Kaedah 2: Formula Tatasusunan

Anda boleh menyelesaikan masalah dengan formula. Dalam kes ini, pengiraan semula dan pengemaskinian keputusan akan berlaku secara automatik dan serta-merta, serta-merta selepas perubahan dalam senarai asal. Untuk kemudahan dan ringkas, mari berikan nama senarai kami. Senaraikan 1 и Senaraikan 2menggunakan Pengurus Nama tab formula (Formula — Pengurus Nama — Buat):

Selepas penamaan, formula yang kita perlukan akan kelihatan seperti ini:

Pada pandangan pertama, ia kelihatan menyeramkan, tetapi, sebenarnya, semuanya tidak begitu menakutkan. Biar saya mengembangkan formula ini pada beberapa baris menggunakan kombinasi kekunci Alt+Enter dan inden dengan ruang, seperti yang kita lakukan, contohnya di sini:

Logiknya di sini adalah seperti berikut:

• Formula INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) memilih semua elemen unik daripada senarai pertama. Sebaik sahaja ia habis, ia mula memberikan ralat #N/A:

  

• Formula INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) mengekstrak elemen unik daripada senarai kedua dengan cara yang sama.
• Bersarang dalam satu sama lain dua fungsi IFERROR melaksanakan output pertama daripada yang unik dari senarai-1, dan kemudian dari senarai-2 satu demi satu.

Ambil perhatian bahawa ini adalah formula tatasusunan, iaitu selepas menaip, ia mesti dimasukkan dalam sel yang bukan biasa Masukkan, tetapi dengan pintasan papan kekunci Ctrl+Beralih+Masukkan dan kemudian salin (seret) ke bawah ke sel anak dengan margin.

Dalam versi bahasa Inggeris Excel, formula ini kelihatan seperti:

=IFERROR(IFERROR(INDEX(Senarai1, MATCH(0, COUNTIF($E$1:E1, List1), 0)), INDEX(Senarai2, MATCH(0, COUNTIF($E$1:E1, List2), 0))) ), “”) 

Kelemahan pendekatan ini ialah formula tatasusunan ketara memperlahankan kerja dengan fail jika jadual sumber mempunyai bilangan elemen yang besar (beberapa ratus atau lebih). 

Kaedah 3. Power Query

Jika senarai sumber anda mempunyai sejumlah besar elemen, contohnya, beberapa ratus atau beribu-ribu, maka daripada formula tatasusunan perlahan, lebih baik menggunakan pendekatan yang berbeza secara asas, iaitu alat tambah Power Query. Alat tambah ini terbina dalam Excel 2016 secara lalai. Jika anda mempunyai Excel 2010 atau 2013, anda boleh memuat turun dan memasangnya secara berasingan (secara percuma).

Algoritma tindakan adalah seperti berikut:

1. Buka tab berasingan bagi alat tambah yang dipasang Kuasa Kuasa (jika anda mempunyai Excel 2010-2013) atau hanya pergi ke tab Tarikh (jika anda mempunyai Excel 2016).
2. Pilih senarai pertama dan tekan butang Daripada Jadual/Julat (Dari Julat/Jadual). Apabila ditanya tentang membuat "jadual pintar" daripada senarai kami, kami bersetuju:

   

3. Tetingkap editor pertanyaan terbuka, di mana anda boleh melihat data yang dimuatkan dan nama pertanyaan Jadual 1 (anda boleh menukarnya kepada anda sendiri jika anda mahu).
4. Klik dua kali pada pengepala jadual (word Senaraikan 1) dan menamakan semula kepada yang lain (contohnya orang). Apa sebenarnya untuk menamakan tidak penting, tetapi nama yang dicipta mesti diingat, kerana. ia perlu digunakan semula kemudian apabila mengimport jadual kedua. Menggabungkan dua jadual pada masa hadapan hanya akan berfungsi jika tajuk lajurnya sepadan.
5. Kembangkan senarai juntai bawah di penjuru kiri sebelah atas tutup dan muat turun Dan pilih Tutup dan muatkan… (Tutup&Muat ke…):

   

6. Dalam kotak dialog seterusnya (ia mungkin kelihatan sedikit berbeza – jangan risau), pilih Buat sambungan sahaja (Hanya buat sambungan):

   

7. Kami mengulangi keseluruhan prosedur (mata 2-6) untuk senarai kedua. Apabila menamakan semula tajuk lajur, adalah penting untuk menggunakan nama yang sama (Orang) seperti dalam pertanyaan sebelumnya.
8. Dalam tetingkap Excel pada tab Tarikh atau pada tab Kuasa Kuasa Pilih Dapatkan Data – Gabungkan Permintaan – Tambah (Dapatkan Data — Gabungkan Pertanyaan — Tambah):

   

9. Dalam kotak dialog yang muncul, pilih permintaan kami daripada senarai juntai bawah:

   

10. Akibatnya, kami akan mendapat pertanyaan baharu, di mana dua senarai akan disambungkan di bawah satu sama lain. Ia kekal untuk mengalih keluar pendua dengan butang Padam Baris – Alih Keluar Pendua (Padam Baris — Padam Pendua):

    

11. Pertanyaan siap boleh dinamakan semula di sebelah kanan panel pilihan, memberikannya nama yang waras (ini akan menjadi nama jadual hasil sebenarnya) dan semuanya boleh dimuat naik ke helaian dengan arahan tutup dan muat turun (Tutup&Muatkan):

    

Pada masa hadapan, dengan sebarang perubahan atau penambahan pada senarai asal, cukup dengan hanya klik kanan untuk mengemas kini jadual hasil.

• Cara mengumpul berbilang jadual daripada fail berbeza menggunakan Power Query
• Mengeluarkan Item Unik daripada Senarai
• Cara membandingkan dua senarai antara satu sama lain untuk padanan dan perbezaan