Cara Menemukan Duplikat di Excel: Sorot, Hitung, dan Daftarkan
By Mirko Peters · · Updated
Temukan duplikat di Excel dengan pemformatan bersyarat, kolom bantu COUNTIF, UNIQUE, Power Query, dan pemeriksaan lintas lembar, serta cara mengenali kecocokan mirip sebelum Anda menghapus data.
Automatically translated with AI from the original post. Read the English original
Untuk menemukan duplikat di Excel tanpa menghapus apa pun, pilih data Anda lalu gunakan Beranda > Pemformatan Bersyarat > Aturan Sorotan Sel > Nilai Duplikat. Excel akan memberi warna pada setiap nilai yang muncul lebih dari sekali. Jika Anda ingin angka, bukan warna, tambahkan kolom bantu dengan rumus COUNTIF. Artikel ini ditujukan bagi pengguna Excel yang mewarisi daftar yang berantakan dan perlu melihat masalahnya sebelum memperbaikinya. Anda akan mendapatkan metode sorotan, rumus penghitungan dan penandaan, cara mendaftar hanya nilai yang berulang, opsi Power Query, pemeriksaan lintas lembar, serta rencana untuk kecocokan mirip.
Poin Penting
- Cara tercepat menemukan duplikat di Excel adalah Beranda, Pemformatan Bersyarat, Aturan Sorotan Sel, Nilai Duplikat. Cara ini memberi warna pada nilai yang berulang dan tidak menghapus apa pun.
- Kolom bantu COUNTIF memberi tahu Anda seberapa sering setiap nilai muncul, dan dengan COUNTIFS Anda dapat memeriksa beberapa kolom sekaligus.
- COUNTIF mengabaikan huruf besar-kecil dan memperlakukan ? serta * sebagai karakter pengganti, sehingga jumlahnya bisa berbeda dari yang Anda perkirakan.
- Untuk membandingkan dua lembar, hitung setiap nilai terhadap daftar lainnya dengan COUNTIF, dan buka kedua buku kerja jika daftar kedua berada di file lain.
- Kecocokan mirip seperti Jon dan John tidak akan muncul sebagai duplikat. Bersihkan spasi dan huruf besar-kecil terlebih dahulu, lalu tinjau sisanya secara manual atau dengan add-in Fuzzy Lookup.
Menemukan harus dilakukan sebelum menghapus. Jika Anda sudah tahu baris mana yang ingin dihapus, panduan pendamping kami tentang cara menghapus duplikat di Excel membahas langkah penghapusannya. Di sini tujuannya adalah memeriksa terlebih dahulu di Excel, agar Anda sendiri yang memutuskan apa yang dianggap duplikat dan apa yang disimpan.
Sorot duplikat dengan pemformatan bersyarat
Ini adalah pemeriksaan tercepat, dan Microsoft merekomendasikannya sebagai langkah tinjauan sebelum Anda menghapus apa pun. Langkah ini mengikuti artikel Microsoft tentang memfilter nilai unik atau menghapus nilai duplikat.
- Pilih sel, kolom, atau rentang yang ingin Anda periksa. Biasanya satu kolom sudah cukup.
- Pada tab Beranda, buka Pemformatan Bersyarat.
- Pilih Aturan Sorotan Sel, lalu Nilai Duplikat.
- Pilih format, misalnya isian merah muda, lalu pilih OK.
Setiap sel yang nilainya muncul lebih dari sekali kini diberi warna. Gulir lembar kerja, atau filter kolom berdasarkan warna untuk hanya melihat sel yang disorot. Untuk membatalkannya, buka Pemformatan Bersyarat dan hapus aturan dari rentang tersebut.
Ada cara kedua yang memberi Anda kontrol lebih besar. Buka Pemformatan Bersyarat > Kelola Aturan, buat Aturan Baru, lalu pilih Format hanya nilai unik atau duplikat. Pada daftar Format semua, Anda dapat memilih unik atau duplikat, serta mengatur format angka, font, bingkai, atau isian. Cara ini berguna jika Anda ingin memberi warna pada nilai yang hanya muncul sekali dan membiarkan nilai yang berulang tetap apa adanya.
Ada dua batasan yang perlu diketahui. Microsoft menyebutkan bahwa Excel tidak dapat menyorot duplikat di area Values PivotTable. Selain itu, aturan ini hanya memeriksa satu kolom dalam satu waktu saat Anda memilih satu kolom. Baris yang hanya terulang saat Anda melihat beberapa kolom sekaligus tidak akan tersorot. Untuk itu, Anda memerlukan rumus.
Menghitung dan menandai duplikat dengan COUNTIF
Kolom bantu memberi Anda angka yang dapat diurutkan dan difilter. Cara ini juga tepat digunakan saat dua baris hanya dianggap duplikat jika dua atau tiga kolom cocok.
Menandai setiap pengulangan
Misalkan nilai Anda berada di A2:A100. Di B2, masukkan:
=COUNTIF($A$2:$A$100,A2)
Salin rumus ke bawah. Hasil 1 berarti nilai tersebut unik. Hasil 2 berarti nilai tersebut muncul dua kali, dan seterusnya. Setiap salinan nilai yang berulang mendapat angka yang sama, termasuk yang pertama. Untuk mengubah jumlah tersebut menjadi TRUE atau FALSE, tambahkan perbandingan: =COUNTIF($A$2:$A$100,A2)>1.
Hanya menandai salinan berikutnya
Saat Anda berencana menghapus baris, biasanya Anda ingin mempertahankan yang pertama. Gunakan rentang yang berkembang sebagai gantinya:
=COUNTIF(A$2:A2,A2)>1
Rentang A$2:A2 akan bertambah saat rumus disalin ke bawah. Saat nilai pertama kali muncul, Anda mendapat FALSE. Setiap kemunculan berikutnya mendapat TRUE. Cara ini sesuai dengan perilaku perintah Remove Duplicates di Excel, karena perintah tersebut mempertahankan kemunculan pertama.
Memeriksa beberapa kolom dengan COUNTIFS
Untuk data yang hanya dianggap duplikat jika, misalnya, nama depan dan nama belakang sama-sama cocok, gunakan COUNTIFS dengan satu pasang rentang dan kriteria untuk setiap kolom:
=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)
Hasil di atas 1 berarti kombinasi tersebut muncul lebih dari sekali. Tambahkan lebih banyak pasangan untuk lebih banyak kolom. Jangan mencoba membuat pemeriksaan yang sama dengan menggabungkan rentang di dalam COUNTIF, seperti COUNTIF($A$2:$A$100&$B$2:$B$100,A2&B2). COUNTIF mengharapkan rentang sungguhan sebagai argumen pertamanya, sehingga rumus tersebut tidak bekerja seperti yang tampak. COUNTIFS adalah jawaban yang tepat.
Perilaku COUNTIF yang sering mengejutkan
Halaman COUNTIF dari Microsoft mencantumkan beberapa perilaku yang penting saat Anda mencari duplikat:
- Pencocokan teks mengabaikan huruf besar dan kecil. "apples" dan "APPLES" dihitung sebagai nilai yang sama. Excel memperlakukannya sebagai duplikat di sini.
- Karakter
?dan*berfungsi sebagai wildcard. Jika data Anda memuatnya, misalnya pada kode produk, COUNTIF mungkin mencocokkan lebih banyak dari yang Anda perkirakan. Tambahkan tanda tilde di depan karakter tersebut untuk mencarinya secara harfiah. - COUNTIF menghasilkan hasil yang tidak tepat untuk string yang lebih dari 255 karakter.
- Spasi di awal atau akhir, tanda kutip lurus dan melengkung yang bercampur, serta karakter nonprinting dapat membuat hitungan meleset. Microsoft menyarankan fungsi TRIM dan CLEAN untuk membersihkan data terlebih dahulu.
Poin terakhir inilah sumber sebagian besar keluhan "Excel melewatkan duplikat saya". Dua sel yang tampak identik bisa berbeda karena satu spasi di akhir. Bersihkan dulu, baru hitung.
Memfilter dan meninjau
Setelah kolom bantu terpasang, filter untuk TRUE, atau untuk nilai di atas 1, lalu baca barisnya. Anda dapat mengurutkan berdasarkan kolom bantu agar semua baris yang berulang berkumpul. Periksa sampel sebelum menghapus apa pun. Sering kali "duplikat" ternyata dua catatan asli yang kebetulan memiliki nama yang sama.
Hanya menampilkan nilai duplikat
Terkadang Anda hanya ingin daftar singkat dari nilai yang berulang, bukan penanda di setiap baris.
Di Excel untuk Microsoft 365, 2024, dan 2021, fungsi UNIQUE dapat melakukan pekerjaan ini. Menurut halaman UNIQUE dari Microsoft, =UNIQUE(array,[by_col],[exactly_once]) mengembalikan nilai berbeda dari sebuah rentang, dan jika exactly_once diatur ke TRUE, hanya nilai yang muncul sekali yang dikembalikan. Jadi, =UNIQUE(A2:A100) menampilkan setiap nilai satu kali, dan =UNIQUE(A2:A100,,TRUE) menampilkan nilai yang sama sekali tidak terduplikasi. Untuk hanya nilai yang berulang, gabungkan UNIQUE dengan FILTER dan COUNTIF:
=UNIQUE(FILTER(A2:A100,COUNTIF(A2:A100,A2:A100)>1))
Rumus ini menyimpan baris dengan hitungan di atas 1 dan menampilkan setiap nilai tersebut satu kali. Rumus ini memerlukan versi Excel yang mendukung dynamic array. Uji dulu pada beberapa baris sebelum Anda mengandalkannya pada daftar yang besar.
Di versi yang lebih lama, gunakan kolom bantu. Masukkan =IF(COUNTIF($A$2:$A$100,A2)>1,A2,"") lalu salin ke bawah. Kolom tersebut sekarang menampilkan setiap nilai yang berulang dan membiarkan sel kosong untuk sisanya. Saring sel kosong tersebut. Anda akan melihat setiap nilai yang berulang sebanyak kemunculannya, dan itu cukup untuk keperluan peninjauan.
Dengan Advanced Filter, Anda dapat menyalin hanya rekaman unik ke lokasi baru. Buka Data > Advanced, pilih Copy to another location, masukkan sel di kotak Copy to, lalu centang Unique records only. Membandingkan daftar ini dengan data asli akan menunjukkan mana yang terulang.
Gunakan Power Query untuk hanya menyimpan duplikat
Jika data Anda besar atau masuk setiap minggu, biarkan Power Query yang menemukan duplikat. Dokumentasi Power Query dari Microsoft menjelaskan perintah Keep duplicates, kebalikan dari Remove duplicates. Perintah ini memfilter tabel hanya ke baris yang nilainya pada kolom terpilih muncul lebih dari sekali. Berbeda dengan Remove duplicates, perintah ini mengembalikan semua baris yang cocok, bukan satu per nilai.
Artikel Microsoft tentang menyimpan atau menghapus baris duplikat memuat langkah-langkah untuk sisi penghapusan. Langkah awalnya sama untuk keduanya:
- Pilih sel di dalam data Anda, lalu pilih Query > Edit, atau muat dengan Data > From Table/Range.
- Klik judul kolom untuk memilihnya. Tahan Shift atau Ctrl untuk memilih lebih banyak kolom.
- Terapkan perintah tersebut. Langkah Microsoft untuk penghapusan menggunakan Home > Remove Rows > Remove Duplicates. Cari perintah Keep Duplicates yang sepadan bersama perintah baris lainnya pada tab Home, lalu periksa pratinjaunya.
Microsoft juga mencatat bahwa Power Query peka huruf besar-kecil, sehingga "Apple" dan "apple" dianggap nilai yang berbeda di sana, tidak seperti di COUNTIF. Ini perbedaan nyata antara kedua metode. Jika tujuan Anda adalah pemeriksaan mingguan yang dapat diulang, tentukan satu perilaku dan konsisten dengannya, misalnya dengan mengubah kolom menjadi huruf kapital terlebih dahulu.
Mencari duplikat antara dua lembar atau daftar
Tugas yang umum adalah memeriksa apakah sebuah daftar pada satu lembar juga muncul di lembar lain, misalnya daftar pelanggan baru dibandingkan dengan daftar lama, atau pesanan bulan ini dibandingkan dengan pesanan bulan lalu.
- Letakkan kedua daftar di lembar terpisah, misalnya Sheet1 dan Sheet2, dengan nilainya di kolom A.
- Di Sheet1, pada B2, masukkan
=COUNTIF(Sheet2!A:A,A2)>0. - Salin rumus ke bawah. TRUE berarti nilai tersebut juga muncul di Sheet2.
- Filter untuk TRUE guna melihat yang tumpang tindih, atau untuk FALSE guna melihat yang baru.
Untuk dua kolom yang harus cocok secara bersamaan, gunakan COUNTIFS dengan pasangan untuk setiap kolom dan rentang dari lembar lain.
Jika daftar kedua berada di buku kerja lain, Microsoft memperingatkan bahwa rumus yang merujuk ke buku kerja tertutup dapat menghasilkan kesalahan #VALUE!. Biarkan kedua buku kerja terbuka selama Anda bekerja, atau bawa daftar kedua ke buku kerja pertama dengan Power Query. Setelah pemeriksaan, ingat bahwa hasilnya bergantung pada data pada saat itu. Jika salah satu daftar berubah, penandanya ikut berubah.
Kecocokan mendekati: Jon Smith dan John Smith
Semua yang dijelaskan di atas hanya menemukan kecocokan persis, selain perbedaan huruf besar-kecil. Daftar nyata juga berisi salah ketik dan ejaan yang berbeda. Excel tidak memiliki fungsi bawaan yang menilai seberapa mirip dua nilai teks, sehingga kasus ini ditangani dalam dua langkah.
Langkah pertama: hapus perbedaan semu. Gunakan TRIM untuk menghapus spasi berlebih dan CLEAN untuk menghapus karakter yang tidak dapat dicetak, lalu bangun pemeriksaan berdasarkan nilai yang sudah dibersihkan. Di kolom bantu, =TRIM(A2) menampilkan teks yang sudah dibersihkan. Hitung duplikat pada kolom tersebut, bukan pada data asli. Karena COUNTIF mengabaikan huruf besar-kecil, Anda tidak perlu mengubah huruf di sana. Di Power Query, tempat huruf besar-kecil berpengaruh, ubah kolom menjadi huruf kapital atau huruf kecil.
Langkah kedua: periksa sisanya. Urutkan daftar yang sudah dibersihkan secara alfabetis dan baca data yang bersebelahan. Kecocokan yang hampir sama, misalnya "Jon Smith" dan "John Smith", akan berdampingan sehingga manusia dapat melihatnya. Untuk daftar yang lebih besar, Microsoft Research merilis Fuzzy Lookup add-in for Excel. Add-in ini dapat menemukan baris duplikat yang mirip dalam satu tabel atau mencocokkan baris yang serupa di antara dua tabel, dan memberi setiap kecocokan skor kemiripan. Periksa persyaratannya sebelum Anda merencanakan penggunaannya: halaman unduhan mencantumkan Windows, dengan Excel 2007, 2010, 2013, dan 2016, serta rilis dari 2020. Add-in ini mungkin tidak dapat dipasang pada penyiapan Microsoft 365 saat ini, dan tidak tersedia di Mac.
Apa pun alatnya, tinjau hasil fuzzy secara manual. Dua orang dengan nama yang mirip belum tentu orang yang sama.
Kesalahan umum
- Hanya memeriksa satu kolom padahal duplikat ditentukan oleh dua kolom. Nama belakang yang sama saja tidak menjadikan sebuah record sebagai duplikat.
- Mempercayai hitungan COUNTIF pada data yang memiliki spasi berlebih atau karakter aneh. Bersihkan data dengan TRIM dan CLEAN terlebih dahulu.
- Lupa bahwa COUNTIF mengabaikan huruf besar-kecil sementara Power Query memperhatikannya, sehingga hasil keduanya berbeda.
- Menggunakan
COUNTIF($A$2:$A$100,A2)>1lalu menghapus setiap baris yang bernilai TRUE. Rumus itu juga menandai salinan pertama, sehingga semua salinan ikut terhapus. Gunakan rentang yang berkembang untuk hanya menandai salinan berikutnya. - Mencari di seluruh kolom untuk string yang lebih dari 255 karakter, yang membuat COUNTIF memberikan hasil yang salah.
- Langsung menghapus setelah menyorot data. Salin lembar kerja terlebih dahulu, karena Microsoft menyatakan bahwa duplikat yang dihapus akan dihapus secara permanen.
Topik terkait untuk dieksplorasi
- Hapus duplikat yang sudah ditemukan. Perintah Remove Duplicates pada tab Data mempertahankan kecocokan pertama dan menghapus sisanya. Urutkan terlebih dahulu untuk mengontrol baris mana yang tetap dipertahankan.
- Pertahankan record terbaik, bukan yang pertama. Urutkan berdasarkan tanggal atau nilai agar baris yang Anda inginkan berada di urutan pertama, lalu tandai salinan berikutnya dengan COUNTIF rentang yang berkembang.
- Bandingkan daftar antar-file. Power Query dapat menggabungkan beberapa workbook menjadi satu tabel sehingga Anda dapat memeriksanya bersama-sama.
- Cegah duplikat baru. Validasi data dengan rumus kustom dapat memblokir nilai yang sudah ada. Coba terapkan pada lembar uji.
- Simpan jejak audit. Biarkan kolom bantu tetap ada di salinan lembar kerja agar Anda nanti dapat menunjukkan apa yang ditandai dan alasannya.
Langkah berikutnya
Pilih satu kolom pada file Anda saat ini, sorot duplikatnya dengan conditional formatting, dan hitung berapa banyak sel yang menyala. Lalu tambahkan kolom bantu COUNTIF dan bandingkan angkanya. Jika berbeda, Anda baru saja menemukan spasi tersembunyi atau perbedaan huruf besar-kecil, dan kini Anda tahu cara menemukan duplikat di Excel sebelum menghapus apa pun.