Cara membandingkan dua kolom di Excel dan menghapus duplikat (sorot, warnai, pindahkan)

Artikel ini akan membawa Anda sekitar 10 menit untuk membaca. Dalam 5 menit berikutnya, Anda dapat dengan mudah membandingkan dua kolom di Excel dan mencari tahu apakah ada duplikat di dalamnya, menghapusnya atau menyorotnya dengan warna. Jadi, waktunya telah tiba!

Excel adalah aplikasi yang sangat kuat dan sangat keren untuk membuat dan memproses data dalam jumlah besar. Jika Anda memiliki beberapa buku kerja dengan data (atau hanya satu tabel besar), maka Anda mungkin ingin membandingkan 2 kolom, menemukan nilai duplikat, lalu melakukan sesuatu dengannya, misalnya, menghapus, menyorot, atau menghapus konten . Kolom-kolom tersebut dapat berada pada tabel yang sama, dapat bersebelahan atau tidak bersebelahan, dapat terletak pada 2 lembar yang berbeda atau bahkan pada buku yang berbeda.

Bayangkan kita memiliki 2 kolom dengan nama orang – 5 nama per kolom A dan 3 nama dalam satu kolom B. Anda perlu membandingkan nama di dua kolom ini dan menemukan duplikatnya. Seperti yang Anda pahami, ini adalah data fiktif, yang diambil hanya sebagai contoh. Dalam tabel nyata, kita berhadapan dengan ribuan atau bahkan puluhan ribu record.

Opsi A: kedua kolom berada pada lembar yang sama. Misalnya kolom A dan kolom B.

Opsi B: Kolom berada di lembar yang berbeda. Misalnya kolom A di atas lembaran Sheet2 dan kolom A di atas lembaran Sheet3.

Excel 2013, 2010 dan 2007 memiliki alat bawaan Hapus Duplikat (Hapus Duplikat) tetapi tidak berdaya dalam situasi ini karena tidak dapat membandingkan data dalam 2 kolom. Selain itu, itu hanya dapat menghapus duplikat. Tidak ada pilihan lain seperti menyorot atau mengubah warna. Dan titik!

Selanjutnya, saya akan menunjukkan cara yang mungkin untuk membandingkan dua kolom di Excel, yang memungkinkan Anda menemukan dan menghapus catatan duplikat.

• Bandingkan 2 kolom dan cari duplikat menggunakan rumus
  
  • Opsi A: kedua kolom berada di lembar yang sama
  • Opsi B: Kolom berada di lembar yang berbeda atau di buku kerja yang berbeda
• Pemrosesan duplikat yang ditemukan
  
  • Tampilkan hanya baris duplikat di kolom A
  • Ubah warna atau sorot duplikat yang ditemukan
  • Hapus duplikat dari kolom pertama

Bandingkan 2 kolom di Excel dan temukan entri duplikat menggunakan rumus

Opsi A: kedua kolom berada di lembar yang sama

1. Di sel kosong pertama (dalam contoh kami, ini adalah sel C1), kami menulis rumus berikut:

   =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")

   

   Dalam rumus kami A1 ini adalah sel pertama dari kolom pertama yang akan kita bandingkan. $B$1 и $B$10000 ini adalah alamat sel pertama dan terakhir dari kolom kedua, yang dengannya kami akan melakukan perbandingan. Perhatikan referensi absolut – huruf kolom dan nomor baris didahului dengan tanda dolar ($). Saya menggunakan referensi absolut sehingga alamat sel tetap sama saat menyalin rumus.

   Jika Anda ingin menemukan duplikat di kolom B, ubah referensi sehingga rumus terlihat seperti ini:

   =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")

   Alih-alih "Hanya"Dan"Duplikat» Anda dapat menulis label Anda sendiri, misalnya, «Tidak ditemukan"Dan"Ditemukan“, atau pergi saja”Duplikat' dan masukkan karakter spasi alih-alih nilai kedua. Dalam kasus terakhir, sel yang tidak ditemukan duplikatnya akan tetap kosong, dan, saya yakin, representasi data ini paling sesuai untuk analisis lebih lanjut.

2. Sekarang mari kita salin rumus kita ke semua sel di kolom C, sampai ke baris bawah, yang berisi data di kolom A. Untuk melakukan ini, gerakkan penunjuk mouse ke sudut kanan bawah sel C1, pointer akan berbentuk crosshair hitam, seperti terlihat pada gambar di bawah ini:Klik dan tahan tombol kiri mouse dan seret batas bingkai ke bawah, sorot semua sel tempat Anda ingin menyisipkan rumus. Ketika semua sel yang diperlukan dipilih, lepaskan tombol mouse:

   

olymp trade indonesiaTip: Dalam tabel besar, menyalin rumus akan lebih cepat jika Anda menggunakan pintasan keyboard. Sorot sel C1 dan tekan Ctrl + C (untuk menyalin rumus ke clipboard), lalu klik Ctrl + Shift + Akhir (untuk memilih semua sel yang tidak kosong di kolom C) dan terakhir tekan Ctrl + V (untuk menyisipkan rumus ke semua sel yang dipilih).

3. Hebat, sekarang semua nilai duplikat ditandai sebagai “Duplikat":

Opsi B: dua kolom berada di lembar yang berbeda (dalam buku kerja yang berbeda)

1. Di sel pertama kolom kosong pertama di lembar kerja Sheet2 (dalam kasus kami ini adalah kolom B) masukkan rumus berikut:

   =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")

   Sini Sheet3 adalah nama lembar tempat kolom ke-2 berada, dan $ A $ 1: $ A $ 10000 adalah alamat sel dari 1 hingga terakhir di kolom ke-2 ini.

2. Salin rumus ke semua sel dalam kolom B (sama seperti opsi A).
3. Kami mendapatkan hasil ini:

Pemrosesan duplikat yang ditemukan

Bagus, kami telah menemukan entri di kolom pertama yang juga ada di kolom kedua. Sekarang kita perlu melakukan sesuatu dengan mereka. Menelusuri semua catatan duplikat dalam tabel secara manual cukup tidak efisien dan memakan terlalu banyak waktu. Ada cara yang lebih baik.

Tampilkan hanya baris duplikat di kolom A

Jika kolom Anda tidak memiliki header, maka Anda perlu menambahkannya. Untuk melakukan ini, letakkan kursor pada nomor yang mewakili baris pertama, dan itu akan berubah menjadi panah hitam, seperti yang ditunjukkan pada gambar di bawah ini:

Klik kanan dan pilih dari menu konteks Insersi (Memasukkan):

Beri nama pada kolom, misalnya, “Nama"Dan"Duplikat?» Kemudian buka tab Data (Data) dan tekan Filter (Saring):

Setelah itu klik panah abu-abu kecil di sebelah “Duplikat?« untuk membuka menu filter; hapus centang semua item dalam daftar ini kecuali Duplikat, dan tekan OK.

Itu saja, sekarang Anda hanya melihat elemen-elemen kolom itu А, yang digandakan di kolom В. Hanya ada dua sel seperti itu di tabel pelatihan kami, tetapi, seperti yang Anda pahami, dalam praktiknya akan ada lebih banyak lagi.

Untuk menampilkan semua baris kolom lagi А, klik simbol filter di kolom В, yang sekarang terlihat seperti corong dengan panah kecil, dan pilih Pilih semua (Pilih Semua). Atau Anda dapat melakukan hal yang sama melalui Ribbon dengan mengklik Data (Data) > Pilih & Filter (Urutkan & Filter) > Hapus (Hapus) seperti yang ditunjukkan pada tangkapan layar di bawah ini:

Ubah warna atau sorot duplikat yang ditemukan

Jika catatan “Duplikat” tidak cukup untuk tujuan Anda dan Anda ingin menandai sel duplikat dengan warna font yang berbeda, warna isian atau metode lain…

Dalam hal ini, filter duplikat seperti yang ditunjukkan di atas, pilih semua sel yang difilter dan klik Ctrl + 1untuk membuka dialog Format Cells (format sel). Sebagai contoh, mari kita ubah warna isian sel dalam baris dengan duplikat menjadi kuning cerah. Tentu saja, Anda dapat mengubah warna isian dengan alat ini Mengisi (Isi Warna) tab Beranda (Beranda) tetapi keuntungan kotak dialog Format Cells (Format Sel) di mana Anda dapat mengonfigurasi semua opsi pemformatan secara bersamaan.

Sekarang Anda pasti tidak akan melewatkan sel apa pun dengan duplikat:

Menghapus nilai duplikat dari kolom pertama

Filter tabel sehingga hanya sel dengan nilai duplikat yang ditampilkan, dan pilih sel tersebut.

Jika 2 kolom yang Anda bandingkan berada di lembar yang berbeda, yaitu, di tabel yang berbeda, klik kanan rentang yang dipilih dan pilih Hapus Baris (Hapus baris):

Tekan OKketika Excel meminta Anda untuk mengonfirmasi bahwa Anda benar-benar ingin menghapus seluruh baris lembar dan kemudian menghapus filter. Seperti yang Anda lihat, hanya baris dengan nilai unik yang tersisa:

Jika 2 kolom berada pada lembar yang sama, berdekatan satu sama lain (adjacent) atau tidak saling berdekatan (not berdekatan), maka proses menghilangkan duplikat akan sedikit lebih rumit. Kami tidak dapat menghapus seluruh baris dengan nilai duplikat, karena ini juga akan menghapus sel dari kolom kedua. Jadi hanya menyisakan entri unik di kolom А, melakukan hal ini:

1. Filter tabel untuk hanya menampilkan nilai duplikat dan pilih sel tersebut. Klik kanan pada mereka dan pilih dari menu konteks Isi yang jelas (isi yang jelas).
2. Bersihkan filter.
3. Pilih semua sel dalam kolom А, mulai dari sel A1 sampai ke bawah berisi data.
4. klik Data (Data) dan tekan Urutkan A sampai Z (Urut dari A sampai Z). Di kotak dialog yang terbuka, pilih Lanjutkan dengan pilihan saat ini (Urutkan dalam pilihan yang ditentukan) dan klik tombol Black (Penyortiran):
5. Hapus kolom dengan rumus, Anda tidak akan membutuhkannya lagi, mulai sekarang Anda hanya memiliki nilai unik.
6. Itu dia, sekarang kolomnya А hanya berisi data unik yang tidak ada di kolom В:

Seperti yang Anda lihat, menghapus duplikat dari dua kolom di Excel menggunakan rumus tidaklah sulit.