Situasi klasik: Anda memiliki dua daftar yang perlu digabungkan menjadi satu. Selain itu, dalam daftar awal dapat ada elemen unik dan elemen yang cocok (baik di antara daftar dan di dalam), tetapi pada output Anda perlu mendapatkan daftar tanpa duplikat (pengulangan):
Mari kita secara tradisional melihat beberapa cara untuk memecahkan masalah umum seperti itu - dari primitif "di dahi" hingga yang lebih kompleks, tetapi elegan.
Metode 1: Hapus Duplikat
Anda dapat menyelesaikan masalah dengan cara paling sederhana – salin elemen kedua daftar secara manual menjadi satu dan kemudian terapkan alat ke set yang dihasilkan. Hapus Duplikat dari tab Data (Data — Hapus Duplikat):
Tentu saja, metode ini tidak akan berfungsi jika data dalam daftar sumber sering berubah – Anda harus mengulangi seluruh prosedur setelah setiap perubahan lagi.
Metode 1a. tabel pivot
Metode ini, pada kenyataannya, merupakan kelanjutan logis dari yang sebelumnya. Jika daftar tidak terlalu besar dan jumlah maksimum elemen di dalamnya diketahui sebelumnya (misalnya, tidak lebih dari 10), maka Anda dapat menggabungkan dua tabel menjadi satu dengan tautan langsung, tambahkan kolom dengan yang di sebelah kanan dan buat tabel ringkasan berdasarkan tabel yang dihasilkan:
Seperti yang Anda ketahui, tabel pivot mengabaikan pengulangan, sehingga pada output kita akan mendapatkan daftar gabungan tanpa duplikat. Kolom tambahan dengan 1 diperlukan hanya karena Excel dapat membuat tabel ringkasan yang berisi setidaknya dua kolom.
Ketika daftar asli diubah, data baru akan masuk ke tabel gabungan melalui tautan langsung, tetapi tabel pivot harus diperbarui secara manual (klik kanan – Perbarui & Simpan). Jika Anda tidak perlu menghitung ulang dengan cepat, maka lebih baik menggunakan opsi lain.
Metode 2: Rumus Array
Anda dapat memecahkan masalah dengan rumus. Dalam hal ini, penghitungan ulang dan pembaruan hasil akan terjadi secara otomatis dan instan, segera setelah perubahan dalam daftar asli. Untuk kenyamanan dan singkatnya, mari kita beri nama daftar kami. Daftar 1 и Daftar 2menggunakan Manajer nama tab rumus (Rumus — Manajer Nama — Buat):
Setelah penamaan, rumus yang kita butuhkan akan terlihat seperti ini:
Sepintas, terlihat menyeramkan, tetapi, pada kenyataannya, semuanya tidak begitu menakutkan. Biarkan saya memperluas rumus ini pada beberapa baris menggunakan kombinasi tombol Alt+Enter dan indentasi dengan spasi, seperti yang kita lakukan, misalnya di sini:
Logikanya di sini adalah sebagai berikut:
- Rumus INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) memilih semua elemen unik dari daftar pertama. Begitu habis, itu mulai memberikan kesalahan #N/A:
- Rumus INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) mengekstrak elemen unik dari daftar kedua dengan cara yang sama.
- Bersarang di satu sama lain dua fungsi IFERROR mengimplementasikan output pertama yang unik dari daftar-1, dan kemudian dari daftar-2 satu demi satu.
Perhatikan bahwa ini adalah rumus array, yaitu setelah mengetik, harus dimasukkan ke dalam sel yang tidak biasa Enter, tetapi dengan pintasan keyboard Ctrl+perubahan+Enter lalu salin (seret) ke bawah ke sel anak dengan margin.
Dalam versi bahasa Inggris Excel, rumus ini terlihat seperti:
=IFERROR(IFERROR(INDEX(Daftar1, MATCH(0, COUNTIF($E$1:E1, Daftar1), 0)), INDEX(Daftar2, MATCH(0, COUNTIF($E$1:E1, Daftar2), 0)) ), “”)
Kelemahan dari pendekatan ini adalah bahwa rumus array terasa memperlambat kerja file jika tabel sumber memiliki jumlah elemen yang besar (beberapa ratus atau lebih).
Metode 3. Power Query
Jika daftar sumber Anda memiliki sejumlah besar elemen, misalnya, beberapa ratus atau ribuan, maka alih-alih rumus larik lambat, lebih baik menggunakan pendekatan yang berbeda secara mendasar, yaitu alat add-in Power Query. Add-in ini dibangun ke dalam Excel 2016 secara default. Jika Anda memiliki Excel 2010 atau 2013, Anda dapat mengunduh dan menginstalnya secara terpisah (gratis).
Algoritma tindakan adalah sebagai berikut:
- Buka tab terpisah dari add-on yang diinstal Kueri Daya (jika Anda memiliki Excel 2010-2013) atau cukup buka tab Data (jika Anda memiliki Excel 2016).
- Pilih daftar pertama dan tekan tombol Dari Tabel/Rentang (Dari Rentang/Tabel). Ketika ditanya tentang membuat "tabel pintar" dari daftar kami, kami setuju:
- Jendela editor kueri terbuka, tempat Anda dapat melihat data yang dimuat dan nama kueri Tabel 1 (Anda dapat mengubahnya sendiri jika Anda mau).
- Klik dua kali pada header tabel (word Daftar 1) dan ganti namanya menjadi yang lain (misalnya Konsultan Ahli). Apa sebenarnya yang harus diberi nama tidak penting, tetapi nama yang ditemukan harus diingat, karena. itu harus digunakan lagi nanti saat mengimpor tabel kedua. Menggabungkan dua tabel di masa mendatang hanya akan berfungsi jika judul kolomnya cocok.
- Perluas daftar dropdown di sudut kiri atas tutup dan unduh Dan pilihlah Tutup dan muat di… (Tutup&Muat ke…):
- Di kotak dialog berikutnya (mungkin terlihat sedikit berbeda – jangan khawatir), pilih Buat saja koneksi (Hanya buat koneksi):
- Kami mengulangi seluruh prosedur (poin 2-6) untuk daftar kedua. Saat mengganti nama judul kolom, penting untuk menggunakan nama yang sama (Orang) seperti pada kueri sebelumnya.
- Di jendela Excel pada tab Data atau di tab Kueri Daya Pilih Dapatkan Data – Gabungkan Permintaan – Tambahkan (Dapatkan Data — Gabungkan Kueri — Tambahkan):
- Di kotak dialog yang muncul, pilih permintaan kami dari daftar drop-down:
- Akibatnya, kami akan mendapatkan kueri baru, di mana dua daftar akan terhubung satu sama lain. Tetap menghapus duplikat dengan tombol Hapus Baris – Hapus Duplikat (Hapus Baris — Hapus Duplikat):
- Kueri yang sudah selesai dapat diganti namanya di sisi kanan panel opsi, memberinya nama yang waras (ini akan menjadi nama tabel hasil sebenarnya) dan semuanya dapat diunggah ke lembar dengan perintah tutup dan unduh (Tutup & Muat):
Di masa mendatang, dengan perubahan atau penambahan apa pun pada daftar asli, cukup klik kanan untuk memperbarui tabel hasil.
- Cara mengumpulkan beberapa tabel dari file yang berbeda menggunakan Power Query
- Mengekstrak Item Unik dari Daftar
- Bagaimana membandingkan dua daftar satu sama lain untuk kecocokan dan perbedaan