Bandingkan dua kolom untuk menemukan duplikat di Excel (Panduan lengkap)
Mengidentifikasi nilai duplikat di dua kolom di Excel adalah tugas umum dalam analisis data. Ini membantu dalam menentukan kesalahan entri data, catatan duplikat, atau untuk tujuan pembersihan data. Artikel ini akan memandu Anda tentang cara mengidentifikasi duplikat atau kecocokan dalam dua kolom secara efisien dan akurat.
Bandingkan dua kolom untuk menemukan nilai duplikat
- Sorot duplikat dalam dua kolom dengan Conditional Formatting
- Pilih dan sorot duplikat dalam dua kolom dengan alat canggih – Kutools
- Temukan dan Ekstrak duplikat dalam dua kolom dengan rumus
- Pilih duplikat dalam dua kolom dengan kode VBA
Bandingkan dua kolom untuk menemukan nilai duplikat
Untuk menemukan nilai duplikat di antara dua kolom, ada beberapa metode yang tersedia bergantung pada kebutuhan Anda, seperti menyorot duplikat untuk gambaran visual atau mengekstraknya untuk analisis mendalam. Di bagian ini, kami akan memperkenalkan beberapa trik cepat untuk menyelesaikan tugas ini di Excel.
Sorot duplikat dalam dua kolom dengan Conditional Formatting
Menyoroti duplikat di dua kolom di Excel adalah cara efisien untuk mengidentifikasi data berulang, terutama dalam kumpulan data besar yang tidak memungkinkan peninjauan manual. Dalam hal ini, Pemformatan Bersyarat adalah fitur yang berguna untuk menyelesaikan tugas ini.
Langkah 1: Pilih data di kolom tempat Anda ingin menyorot duplikatnya
Dalam contoh ini, saya akan memilih A2:A10, lihat tangkapan layar:
Langkah 2: Terapkan fitur Pemformatan Bersyarat
- Klik Beranda > Format Bersyarat > Aturan baru, lihat tangkapan layar:
- Dalam majalah Aturan Pemformatan Baru kotak dialog, lakukan operasi berikut:
- 2.1 Pilih Gunakan rumus untuk menentukan sel mana yang akan diformat dari Pilih Jenis Aturan kotak daftar;
- 2.2 Ketik rumus berikut ke dalam Memformat nilai yang rumus ini benar kolom tulisan;
=COUNTIF($B$2:$B$10, A2)>0
- Note: Dalam rumus di atas, B2: B10 mewakili daftar data yang ingin Anda bandingkan, A2 adalah sel pertama dari kolom tempat Anda ingin menyorot duplikatnya. Rumus ini memeriksa apakah nilai di sel A2 ditemukan di mana saja di kolom B. Ubah referensi sel agar sesuai dengan data Anda.
- 2.3 Lalu, klik dibentuk .
- Dalam pop-out Format Cells kotak dialog, tentukan satu warna yang ingin menyorot item duplikat. Dan klik OK.
- Ketika itu mengembalikan Aturan Pemformatan Baru kotak dialog, klik OK .
Hasil:
Sekarang, nilai yang merupakan duplikat di kolom A dan B kini disorot di kolom A seperti gambar berikut yang ditampilkan:
- Aturan duplikat di Format Bersyarat is tidak peka huruf besar/kecil. Jadi, Apple dan apple akan ditandai sebagai duplikat.
- Jika Anda ingin menyorot duplikat dari kolom B, Anda hanya perlu memilih kolom B terlebih dahulu, lalu terapkan rumus berikut ke dalam Format Bersyarat:
=COUNTIF($A$2:$A$10, B2)>0
Pilih dan sorot duplikat dalam dua kolom dengan alat canggih – Kutools
Terkadang, Anda mungkin perlu tidak hanya menyorot tetapi juga memilih duplikat untuk disalin dan ditempelkan ke lokasi lain di buku kerja Anda. Dalam kasus seperti itu, Kutools untuk Excel'S Pilih Sel yang Sama & Berbeda fitur adalah pilihan ideal. Itu dapat mengidentifikasi nilai duplikat atau unik dengan menyorot dan memilih sel yang Anda perlukan.
Klik Kutools > Pilih > Pilih Sel yang Sama & Berbeda, Dalam Pilih Sel yang Sama & Berbeda kotak dialog, lakukan operasi berikut:
- Pilih sumber data dan bandingkan data di Temukan nilai dalam dan Menurut kotak secara terpisah;
- Pilih Setiap baris bawah Berdasarkan bagian;
- Pilih Nilai yang sama dari Menemukan bagian;
- Tentukan warna latar belakang untuk menyorot nilai duplikat di bawah Memproses hasil bagian;
- Terakhir, klik OK .
Hasil:
Sekarang, nilai yang merupakan duplikat di kolom A dan B disorot dan dipilih di kolom A, siap untuk Anda salin dan tempel ke sel mana pun yang diinginkan. Lihat tangkapan layar:
- Fitur ini mendukung peka huruf besar-kecil perbandingan sambil mencentang Hal - hal sensitif kotak centang di Pilih Sel yang Sama & Berbeda dialog;
- Jika Anda ingin memilih duplikat dari kolom B, Anda hanya perlu menukar dua kolom yang dipilih di Temukan nilai dalam dan Menurut kotak dari Pilih Sel yang Sama & Berbeda kotak dialog;
- Untuk menerapkan fitur ini, silakan unduh dan instal Kutools untuk Excel pertama.
Temukan dan Ekstrak duplikat dalam dua kolom dengan rumus
Untuk menemukan dan mengekstrak duplikat di antara dua kolom, Anda dapat menggunakan rumus untuk menentukan dan mengeluarkan duplikat tersebut.
Silakan salin dan tempel rumus berikut ke dalam sel kosong tempat Anda ingin meletakkan hasilnya, lalu seret gagang isian ke bawah kolom untuk menerapkan rumus ini ke sel lain.
=IF(ISERROR(MATCH(A2,$B$2:$B$10,0)),"",A2)
Note: Dalam rumus di atas, A2 adalah sel pertama dari kolom tempat Anda ingin menemukan duplikatnya; B2: B10 mewakili daftar data yang ingin Anda bandingkan.
Hasil:
Seperti yang Anda lihat, jika data di Kolom A ada di Kolom B, nilainya akan ditampilkan; jika tidak, sel akan dibiarkan kosong.
Pilih duplikat dalam dua kolom dengan kode VBA
Bagian ini akan memandu Anda melalui langkah-langkah membuat kode VBA yang mengidentifikasi dan memilih nilai duplikat di antara dua kolom.
Langkah 1: Buka editor modul VBA dan salin kodenya
- Tekan Alt + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.
- Di jendela yang terbuka, klik Menyisipkan > Modul untuk membuat modul kosong baru.
- Kemudian, salin dan tempel kode di bawah ini ke modul kosong.
Kode VBA: Temukan dan pilih nilai duplikat di antara dua kolomSub Compare() 'Update by Extendoffice Dim Range1 As Range, Range2 As Range, Rng1 As Range, Rng2 As Range, outRng As Range xTitleId = "KutoolsforExcel" On Error Resume Next Set Range1 = Application.Selection Set Range1 = Application.InputBox("Range1 :", xTitleId, Range1.Address, Type:=8) Set Range2 = Application.InputBox("Range2:", xTitleId, Type:=8) Application.ScreenUpdating = False For Each Rng1 In Range1 xValue = Rng1.Value For Each Rng2 In Range2 If xValue = Rng2.Value Then If outRng Is Nothing Then Set outRng = Rng1 Else Set outRng = Application.Union(outRng, Rng1) End If End If Next Next outRng.Select Application.ScreenUpdating = True End Sub
Langkah 2: Jalankan kode VBA ini
- Setelah menempelkan kode ini, harap tekan F5 kunci untuk menjalankan kode ini. Di kotak prompt pertama, pilih daftar data yang ingin Anda pilih duplikatnya. Lalu, klik OK.
- Di kotak prompt kedua, pilih daftar data yang ingin Anda bandingkan, lalu klik OK, lihat tangkapan layar:
Hasil:
Sekarang, nilai duplikat dari Kolom A dan B dipilih di Kolom A, memungkinkan Anda mengisi sel dengan warna atau menyalin dan menempelkannya sesuai kebutuhan.
- Kode VBA ini adalah hal - hal sensitif;
- Jika Anda ingin memilih duplikat dari kolom B, Anda hanya perlu menukar dua kolom yang dipilih saat memilih rentang data.
Bandingkan dua kolom untuk kecocokan baris demi baris
Di Excel, membandingkan dua kolom baris demi baris sering kali diperlukan untuk memeriksa kecocokan, yang membantu dalam tugas seperti memeriksa catatan atau menganalisis tren data. Excel memiliki berbagai cara untuk melakukan hal ini, mulai dari rumus mudah hingga fitur khusus, sehingga Anda bisa memilih yang terbaik untuk kebutuhan data Anda. Mari kita lihat beberapa metode sederhana untuk menyelesaikan pekerjaan ini secara efektif.
Bandingkan dua kolom di baris yang sama dengan rumus
Rumus Excel menawarkan pendekatan yang lugas namun ampuh untuk perbandingan data lintas kolom. Inilah cara Anda dapat menggunakannya. Misalkan Anda memiliki data di Kolom A dan Kolom B, untuk memeriksa apakah data di kedua kolom tersebut cocok, rumus berikut dapat membantu Anda:
- Menggunakan operator Sama Dengan (=): cara paling sederhana untuk membandingkan dua sel
- Fungsi JIKA: untuk membuat perbandingan Anda lebih informatif
- Fungsi TEPAT: perbandingan peka huruf besar-kecil
Menggunakan operator Sama Dengan (=):
Silakan masukkan atau salin rumus berikut, tekan Enter kunci lalu seret gagang isian ke bawah untuk mendapatkan semua hasil. Ini akan mengembalikan TRUE jika nilai-nilai di baris yang sama pada Kolom A dan B identik, dan FALSE jika tidak. Lihat tangkapan layar:
=A2=B2
JIKA Fungsi:
Jika Anda ingin membuat perbandingan lebih informatif, Anda dapat menggunakan JIKA berfungsi untuk menampilkan pesan khusus.
Silakan masukkan atau salin rumus di bawah ini, tekan Enter kunci lalu seret gagang isian ke bawah untuk mendapatkan semua hasil. Ini akan mengembalikan Match ketika nilainya sama dan No Match ketika nilainya berbeda. Lihat tangkapan layar:
=IF(A2=B2, "Match", "No Match")
Fungsi TEPAT:
Jika Anda memerlukan perbandingan peka huruf besar/kecil, Fungsi PERSIS adalah cara untuk pergi.
Silakan masukkan atau salin rumus berikut, tekan Enter kunci lalu seret gagang isian ke bawah untuk mendapatkan semua hasil. Ini akan mengembalikan Match ketika nilainya sama persis dan No Match ketika nilainya berbeda. Lihat tangkapan layar:
=IF(EXACT(A2,B2), "Match", "No match")
Pilih dan sorot kecocokan di baris yang sama dengan alat yang berguna - Kutools
Jika Anda perlu memilih dan menaungi kecocokan antara dua kolom baris demi baris alih-alih mendapatkan hasilnya di kolom terpisah, Kutools for Excel's Bandingkan Sel fitur akan menjadi pilihan yang sangat baik. Ini memungkinkan Anda dengan cepat memilih dan menerapkan warna isian ke sel yang cocok atau berbeda nilainya dalam setiap baris.
Klik Kutools > Bandingkan Sel, Dalam Bandingkan Sel kotak dialog, lakukan operasi berikut:
- Pilih data dari dua kolom di Temukan nilai dalam dan Menurut kotak secara terpisah;
- Pilih Sel yang sama bawah Menemukan bagian;
- Tentukan warna latar belakang untuk menyorot kecocokan di bawah Memproses hasil bagian;
- Terakhir, klik OK .
Hasil:
Sekarang, kecocokan di baris yang sama disorot dan dipilih di kolom A, membuatnya tersedia untuk Anda salin dan tempel ke sel mana pun yang diinginkan. Lihat tangkapan layar:
- Fitur ini mendukung peka huruf besar-kecil perbandingan jika Anda memeriksa Hal - hal sensitif pilihan dalam Bandingkan Sel kotak dialog;
- Jika Anda ingin memilih kecocokan dari kolom B, Anda hanya perlu menukar dua kolom yang dipilih di kolom tersebut Temukan nilai dalam dan Menurut kotak dari Bandingkan Sel kotak dialog;
- Untuk menerapkan fitur ini, silakan unduh dan instal Kutools untuk Excel pertama.
Bandingkan dua kolom dan sorot kecocokan di baris yang sama
Membandingkan dua kolom dan menyorot kecocokan di baris yang sama dapat dilakukan secara efisien menggunakan Pemformatan Bersyarat di Excel. Berikut adalah panduan untuk mengidentifikasi dan menyorot kecocokan baris:
Langkah 1: Pilih rentang data
Pilih rentang data yang ingin Anda soroti kecocokan barisnya.
Langkah 2: Terapkan fitur Pemformatan Bersyarat
- Klik Beranda > Format Bersyarat > Aturan baru. di Aturan Pemformatan Baru kotak dialog, lakukan operasi berikut:
- 2.1 Pilih Gunakan rumus untuk menentukan sel mana yang akan diformat dari Pilih Jenis Aturan kotak daftar;
- 2.2 Ketik rumus di bawah ini ke dalam Memformat nilai yang rumus ini benar kolom tulisan;
=$B2=$A2
- 2.3 Lalu, klik dibentuk .
- Dalam pop-out Format Cells kotak dialog, tentukan satu warna yang ingin menyorot item duplikat. Dan klik OK.
- Ketika itu mengembalikan Aturan Pemformatan Baru kotak dialog, klik OK .
Hasil:
Sekarang, nilai yang cocok di baris yang sama disorot sekaligus, lihat tangkapan layar:
- Rumus dalam Conditional Formatting adalah tidak peka huruf besar/kecil.
- Jika Anda ingin menyorot sel dengan nilai berbeda, terapkan rumus berikut:
=$B2<>$A2
Bandingkan dua kolom dan tarik data yang cocok
Saat Anda berurusan dengan dua kumpulan data di Excel dan perlu menemukan item umum dari satu daftar di daftar lainnya, rumus pencarian adalah solusi tepat Anda untuk mengambil kecocokan ini.
Di Excel, jika Anda memiliki daftar buah-buahan di Kolom A dan angka penjualannya di Kolom B, sekarang Anda ingin mencocokkannya dengan pilihan buah-buahan di Kolom D untuk menemukan penjualan yang sesuai. Bagaimana Anda bisa mengembalikan nilai relatif dari kolom B di Excel?
Silakan terapkan salah satu rumus berikut yang Anda perlukan, lalu seret gagang isian ke bawah untuk menerapkan rumus ini ke sel lainnya.
- Semua versi Excel:
=VLOOKUP(D2, $A$2:$B$6, 2, FALSE)
- Excel 365 dan Excel 2021:
=XLOOKUP(D2, $A$2:$A$6, $B$2:$B$6)
Hasil:
Semua nilai yang sesuai akan ditampilkan jika kecocokan ditemukan, jika tidak, kesalahan #N/A akan dikembalikan, lihat tangkapan layar:
- Dengan rumus di atas, jika ada buah di Kolom D yang tidak cocok di Kolom A, maka akan menghasilkan error. Untuk membuat kesalahan ini lebih mudah dipahami, Anda bisa menggabungkan rumus Anda dengan fungsi IFERROR:
- Semua versi Excel:
=IFERROR(VLOOKUP(D2,$A$2:$B$10,2,FALSE), "No match found")
- Excel 365 dan Excel 2021:
=IFERROR(XLOOKUP(D2, $A$2:$A$10, $B$2:$B$10),"No match found")
- Semua versi Excel:
- Bagi mereka yang tertarik pada formula pencarian lanjutan, Kutools untuk Excel menyediakan rangkaian rumus pencarian lanjutan yang mengesankan yang membawa fungsi VLOOKUP tradisional ke tingkat yang lebih tinggi, memberi Anda presisi dan efisiensi yang tak tertandingi dalam tugas pengelolaan data Anda.
Kutools untuk Excel membanggakan koleksi lebih dari 300 alat praktis yang dirancang untuk meningkatkan produktivitas Anda. Rasakan kecanggihan penuh dengan uji coba gratis selama 30 hari dan tingkatkan spreadsheet Anda hari ini! Dapatkan itu sekarang!
Artikel Terkait:
- Temukan dan sorot baris duplikat dalam suatu rentang
- Terkadang, mungkin ada beberapa rekaman duplikat dalam rentang data lembar kerja Anda, dan sekarang Anda ingin menemukan atau menyorot baris duplikat dalam rentang seperti yang diperlihatkan tangkapan layar berikut. Tentu saja Anda dapat menemukannya satu demi satu dengan memeriksa barisnya. Tetapi ini bukan pilihan yang baik jika ada ratusan baris. Di sini, saya akan berbicara tentang beberapa cara yang berguna bagi Anda untuk menangani tugas ini.
- Sorot nilai duplikat dalam warna berbeda
- Di Excel, kita dapat dengan mudah menyorot nilai duplikat dalam kolom dengan satu warna dengan menggunakan Format Bersyarat, tetapi, terkadang, kita perlu menyorot nilai duplikat dalam warna berbeda untuk mengenali duplikat dengan cepat dan mudah seperti gambar berikut yang ditampilkan. Bagaimana Anda bisa menyelesaikan tugas ini di Excel?
- Temukan, sorot, filter, hitung, hapus duplikat di Excel
- Di Excel, data duplikat terjadi dari waktu ke waktu saat kami merekam data secara manual, menyalin data dari sumber lain, atau karena alasan lain. Terkadang, duplikat itu perlu dan berguna. Namun, terkadang nilai duplikat menyebabkan kesalahan atau kesalahpahaman. Di sini, artikel ini akan memperkenalkan metode untuk mengidentifikasi, menyorot, memfilter, menghitung, menghapus duplikat dengan rumus, aturan pemformatan bersyarat, add-in pihak ketiga, dll. Di Excel dengan cepat.
- Hapus duplikat dan ganti dengan sel kosong
- Biasanya ketika Anda menerapkan perintah Hapus Duplikat di Excel, ini akan menghapus seluruh baris duplikat. Namun terkadang, Anda ingin sel kosong menggantikan nilai duplikat, dalam situasi ini, perintah Hapus Duplikat tidak akan berfungsi. Artikel ini akan memandu Anda menghapus duplikat dan menggantinya dengan sel kosong di Excel.
Alat Produktivitas Kantor Terbaik
Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu. Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...
Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah
- Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
- Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
- Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
Daftar Isi
- Bandingkan dua kolom untuk menemukan nilai duplikat
- Dengan Pemformatan Bersyarat
- Dengan alat yang ampuh – Kutools
- Dengan formula
- Dengan kode VBA
- Bandingkan dua kolom untuk kecocokan baris demi baris
- Dengan formula
- Dengan alat yang berguna - Kutools
- Dengan Pemformatan Bersyarat
- Bandingkan dua kolom dan tarik data yang cocok
- Artikel terkait
- Alat Produktivitas Kantor Terbaik
- komentar