M365Con 2027 – konferensi online gratis · 11–22 Jan 2027 · 120 sessions→ m365con.net

Blog · Mirko Peters

Cara Menghapus Duplikat di Excel: Perintah, Rumus, Power Query

By · · Updated

Cara menghapus duplikat di Excel: gunakan Data, Remove Duplicates, atau pertahankan data asli dengan UNIQUE, rumus, dan Power Query. Langkah, batasan, dan kesalahan umum.

Automatically translated with AI from the original post. Read the English original

Untuk menghapus duplikat di Excel, pilih data Anda, buka Data > Remove Duplicates, centang kolom yang menentukan apa yang dianggap duplikat, lalu pilih OK. Excel menghapus setiap baris yang nilainya berulang di kolom-kolom tersebut dan mempertahankan baris pertama. Artikel ini ditujukan bagi pengguna Excel yang membersihkan daftar, ekspor, dan laporan, serta ingin memilih metode yang tepat. Anda akan menemukan perintahnya terlebih dahulu, kemudian cara memeriksa duplikat sebelum menghapusnya, opsi yang menjaga data sumber tetap utuh, dan terakhir beberapa rute otomatisasi untuk pekerjaan yang berulang.

Poin Penting

  • Pilih data Anda, buka Data, lalu Remove Duplicates, centang kolom yang menentukan duplikat, dan pilih OK. Excel menghapus seluruh baris dan mempertahankan kecocokan pertama.
  • Penghapusan bersifat permanen, jadi salin data ke lembar kerja lain terlebih dahulu. Ctrl+Z hanya berfungsi sampai Anda melakukan tindakan lain.
  • Excel membandingkan apa yang ditampilkan di sel, sehingga 3/8/2006 dan 8 Mar 2006 dianggap sebagai nilai yang berbeda. Rapikan format terlebih dahulu.
  • Jika Anda ingin memeriksa duplikat sebelum menghapusnya, sorot dengan conditional formatting atau tandai dengan kolom bantu COUNTIF.
  • UNIQUE dan Power Query menghasilkan daftar yang bersih tanpa mengubah data sumber, dan Power Query mengulang proses pembersihan setiap kali data disegarkan.

Excel memiliki lebih dari satu alat untuk ini karena "duplikat" bisa berarti hal yang berbeda. Terkadang yang dimaksud adalah baris yang sepenuhnya identik. Terkadang yang dimaksud adalah ID pelanggan yang sama dengan nomor telepon yang berbeda. Tentukan terlebih dahulu duplikat yang Anda maksud sebelum mengklik apa pun.

Menghapus duplikat dengan perintah pada tab Data

Ini adalah rute tercepat dan yang paling sering dibutuhkan. Langkah ini mengikuti artikel Microsoft tentang memfilter nilai unik atau menghapus nilai duplikat.

  1. Cadangkan data. Salin rentang ke lembar kerja atau buku kerja lain. Microsoft menegaskan bahwa duplikat dihapus secara permanen.
  2. Pilih rentang. Klik sel mana pun dalam data Anda, atau dalam Excel Table, atau pilih rentang secara manual. Hanya rentang atau tabel yang dipilih yang akan diubah. Sel di luar area tersebut tetap pada tempatnya.
  3. Buka perintah. Buka Data lalu pilih Remove Duplicates di grup Data Tools.
  4. Pilih kolom. Di kotak dialog, centang kolom yang harus dibandingkan Excel. Anda dapat menggunakan Select All atau Unselect All. Jika baris pertama berisi nama kolom, biarkan My data has headers tetap dicentang.
  5. Pilih OK. Excel menampilkan pesan yang berisi jumlah nilai duplikat yang dihapus dan jumlah nilai unik yang tersisa.

Perhatikan langkah 4, karena di sinilah banyak orang keliru. Sebuah baris hanya dianggap duplikat jika semua kolom yang Anda centang cocok. Ambil daftar dengan ID, Nama, dan Harga. Jika Anda mencentang ID dan Nama tetapi tidak Harga, dua baris dengan ID dan nama yang sama tetap dianggap duplikat meskipun harganya berbeda, dan Excel menghapus seluruh baris kedua, termasuk harganya. Jika Anda ingin mempertahankan setiap baris yang memiliki perbedaan, centang semua kolom. Jika ID saja yang menentukan sebuah rekaman, centang hanya ID, dan pahami data apa yang ikut terbuang.

Tekan Ctrl+Z segera setelah perintah dijalankan jika hasilnya tampak keliru. Jangan mengandalkannya nanti.

Baris mana yang tetap: urutkan terlebih dahulu

Microsoft menyatakan bahwa kemunculan pertama dipertahankan dan baris identik berikutnya dihapus. Artinya, urutan baris menentukan hasilnya. Jika Anda memiliki daftar kontak dengan beberapa entri per orang dan ingin entri yang paling baru, urutkan berdasarkan tanggal, dari yang terbaru, lalu jalankan Remove Duplicates. Jika Anda ingin baris yang alamat emailnya terisi, urutkan sehingga baris tersebut berada di urutan teratas. Pengurutan adalah bagian yang wajar dari pembersihan data di sini, bukan tambahan opsional.

Apa yang dianggap identik

Excel membandingkan apa yang ditampilkan di sel, bukan apa yang tersimpan. Contoh dari Microsoft: 3/8/2006 dan Mar 8, 2006 diperlakukan sebagai nilai yang berbeda, meskipun tanggalnya sama. Hal yang sama berlaku untuk angka dengan format berbeda atau teks dengan spasi di akhir. Artikel tentang mencari dan menghapus duplikat juga mencatat bahwa jumlah yang ditampilkan Excel setelahnya dapat mencakup sel kosong atau spasi.

Sebelum menghapus apa pun, samakan tampilan sel. Terapkan satu format tanggal, bersihkan spasi yang tidak perlu (fungsi TRIM melakukannya), dan pastikan angka tidak tersimpan sebagai teks. Halaman Microsoft tidak menyebutkan apakah perintah ini mengabaikan huruf besar dan kecil, jadi uji pada rentang kecil jika hal itu penting bagi Anda.

Ketika perintah menolak dijalankan

Anda tidak dapat menghapus duplikat dari data yang memiliki outline atau subtotal. Hapus outline dan subtotal terlebih dahulu, lalu jalankan perintah.

Periksa duplikat sebelum menghapusnya

Saran Microsoft adalah memfilter atau menyorot terlebih dahulu, baru kemudian menghapus. Langkah ini hanya memakan waktu semenit dan menghindarkan sebagian besar penyesalan.

Menyorot duplikat dengan pemformatan bersyarat

  1. Pilih sel yang ingin Anda periksa.
  2. Pada tab Home, buka Conditional Formatting, lalu Highlight Cells Rules, lalu Duplicate Values.
  3. Pilih gaya sorotan, lalu pilih OK.

Setiap nilai yang berulang kini diberi warna. Metode ini tidak dapat diterapkan pada area Values di PivotTable. Metode ini memeriksa satu kolom sekaligus, sehingga baris yang hanya berulang di beberapa kolom tidak akan tersorot. Untuk itu, Anda memerlukan kolom bantu.

Menandai baris duplikat dengan kolom bantu

Kolom bantu menampilkan TRUE atau FALSE di samping setiap baris. Kolom ini memungkinkan Anda memfilter, meninjau, lalu menghapus.

  1. Sisipkan kolom di samping data Anda, lalu tambahkan judul seperti "Duplicate?".
  2. Jika Anda memeriksa kolom A, masukkan rumus berikut di B2: =COUNTIF(A$2:A2,A2)>1
  3. Salin rumus ke bawah. Kemunculan pertama sebuah nilai menghasilkan FALSE. Setiap kemunculan berikutnya menghasilkan TRUE.
  4. Filter kolom bantu untuk TRUE. Tinjau baris-baris tersebut. Hapus jika memang benar duplikat.

Karena rentang A$2:A2 bertambah saat rumus disalin ke bawah, hanya entri berikutnya yang ditandai dan entri pertama dibiarkan. Hal ini sesuai dengan cara kerja Remove Duplicates.

Untuk memeriksa beberapa kolom sekaligus, buat kolom kunci yang menggabungkannya: =A2&"|"&B2&"|"&C2. Jalankan COUNTIF pada kolom kunci tersebut. Gunakan pemisah seperti tanda pipa di antara setiap field. Tanpa pemisah, "12" ditambah "34" dan "1" ditambah "234" sama-sama menjadi "1234" dan terlihat seperti rekaman yang sama.

Pemfilteran juga memiliki kelemahan. Jika Anda menghapus baris yang difilter dengan memilihnya, pastikan hanya baris yang terlihat dan ditandai yang terpilih, dan lakukan di salinan data. Jika ragu, urutkan kolom bantu dan hapus blok baris TRUE sekaligus.

Mendapatkan daftar tanpa duplikat dan tetap menyimpan sumbernya

Terkadang Anda tidak ingin menghapus apa pun. Anda hanya membutuhkan daftar yang bersih untuk drop-down, ringkasan, atau impor. Ada tiga opsi yang cocok untuk keperluan ini.

Fungsi UNIQUE

Di Excel for Microsoft 365, Excel 2024, dan Excel 2021, =UNIQUE(A2:A100) mengembalikan setiap nilai yang berbeda dari rentang tersebut, dan hasilnya tertumpah (spill) ke sel-sel di bawahnya. Microsoft mencantumkan fungsi ini juga tersedia di Excel for the web dan aplikasi seluler. Sintaksnya adalah =UNIQUE(array,[by_col],[exactly_once]):

  • array adalah rentang yang akan diperiksa. Jika rentang tersebut berisi beberapa kolom, UNIQUE membandingkan seluruh baris.
  • by_col bernilai TRUE jika Anda ingin membandingkan kolom, bukan baris. Biarkan kosong untuk perbandingan baris yang biasa.
  • exactly_once bernilai TRUE jika Anda hanya ingin baris yang muncul satu kali dalam data. Jika dibiarkan kosong, Anda mendapatkan setiap baris yang berbeda.

Hasilnya diperbarui saat sumber berubah. Jika sumbernya adalah Excel Table dan Anda menggunakan referensi terstruktur, rentang tumpahan akan menyesuaikan ukurannya saat Anda menambah atau menghapus baris. Untuk daftar yang terurut, bungkus dengan fungsi lain: =SORT(UNIQUE(A2:A100)).

Pasangan argumen kedua lebih penting daripada yang sering disadari orang. =UNIQUE(A2:A100,,TRUE) menghasilkan nilai yang muncul tepat satu kali, yang berbeda dengan pertanyaan "hapus salinan tambahan". Tanyakan dulu mana yang Anda butuhkan.

Ada satu batasan: versi Excel yang lebih lama (2019 dan sebelumnya) tidak memiliki UNIQUE. Halaman Microsoft hanya mencantumkan versi yang disebutkan di atas.

Advanced Filter

Cara ini berfungsi di setiap versi desktop dan menyalin hasilnya ke lokasi lain.

  1. Pilih data Anda.
  2. Buka Data > Advanced di grup Sort & Filter.
  3. Pilih Copy to another location.
  4. Masukkan sel di kotak Copy to dan centang Unique records only.
  5. Pilih OK.

Data asli Anda tetap di tempatnya, dan baris-baris unik muncul di lokasi baru.

Pola rumus versi lama

Jika Anda tidak dapat menggunakan UNIQUE, masukkan =IF(COUNTIF($A$2:A2,A2)=1,A2,"") di kolom baru lalu salin ke bawah. Setiap nilai muncul sekali dan pengulangan berubah menjadi string kosong. Salin kolom tersebut dan tempel sebagai nilai, lalu saring sel kosongnya. Caranya memang merepotkan, tetapi berfungsi di semua versi.

Menangani kecocokan parsial dan perbedaan huruf besar-kecil

Data nyata jarang berupa salinan yang bersih. Ada tiga situasi yang terus berulang.

Duplikat persis. Setiap kolom yang dicentang cocok. Perintah ini menanganinya dengan baik.

Kecocokan parsial. Kolom kunci berulang tetapi kolom lain berbeda, misalnya ID pelanggan yang sama dengan dua alamat yang berbeda. Di sini Anda harus memutuskan versi mana yang benar. Urutkan agar versi yang benar berada di posisi pertama, lalu hapus duplikat hanya pada kolom kunci. Atau tandai pengulangan dengan kolom bantu dan tinjau secara manual. Tidak ada alat yang bisa memberi tahu alamat mana yang benar.

Perbedaan huruf besar-kecil. Menurut dokumentasi Microsoft, Power Query peka terhadap huruf besar-kecil, sehingga "Apple" dan "apple" tidak dianggap duplikat di sana. Solusi yang disarankan adalah mengubah kolom menjadi huruf besar atau huruf kecil dengan transformasi sebelum menghapus duplikat. Namun, langkah ini mengubah nilai yang tersimpan, jadi simpan kolom asli jika Anda masih memerlukan ejaan yang persis.

Otomatisasi: Power Query dan VBA

Untuk pembersihan sekali jalan, perintah ini sudah cukup. Untuk file yang Anda bersihkan setiap minggu, siapkan sesuatu yang dapat berulang.

Power Query

Jika data Anda berasal dari file CSV, database, atau buku kerja lain, hapus duplikat saat impor. Power Query mencatat langkah tersebut dan menjalankannya lagi pada setiap penyegaran.

  1. Muat data ke Power Query Editor. Jika data sudah ada di lembar kerja, pilih sel di dalam data lalu pilih Query > Edit, atau gunakan Data > Get Data untuk memuat sumber baru.
  2. Klik header kolom untuk memilih kolom. Tahan Shift atau Ctrl untuk memilih lebih banyak. Kolom-kolom tersebut tidak harus berdampingan.
  3. Buka Home > Remove Rows > Remove Duplicates.
  4. Tutup dan muat hasilnya.

Halaman Microsoft tentang menyimpan atau menghapus baris duplikat di Power Query mencantumkan langkah-langkah berikut. Panduannya tentang duplikat patut Anda ketahui: kolom yang dipilih menentukan apa yang dianggap duplikat, dan Power Query tidak menjamin bahwa baris pertama dari sekelompok duplikat akan tetap dipertahankan. Jika baris mana yang tersisa itu penting, jangan hanya mengandalkan pengurutan. Microsoft merujuk ke panduan "Preserve sort" dalam artikel masalah umum untuk Power Query. Opsi terkait, Keep Duplicates, bekerja sebaliknya: opsi ini hanya menyimpan baris yang nilainya muncul lebih dari sekali, yang merupakan cara cepat untuk mengaudit sebuah daftar.

Jika Anda menggabungkan dua tabel, pikirkan di mana deduplikasi sebaiknya dilakukan. Menghapus duplikat dari setiap sumber sebelum penggabungan membuat hasil gabungan lebih kecil dan lebih dapat diprediksi. Menghapusnya setelah penggabungan membersihkan duplikat yang justru dihasilkan oleh penggabungan. Pilih berdasarkan asal duplikat Anda, lalu periksa jumlah baris di pratinjau sebelum Anda memuat data.

Makro VBA

Jika Anda mengulang pembersihan yang sama pada buku kerja yang Anda buka setiap hari, makro bisa menjadi pilihan. Objek Range memiliki metode RemoveDuplicates, yang didokumentasikan dalam referensi VBA Microsoft. Metode ini menerima kolom yang akan dibandingkan dan menentukan apakah baris pertama berisi header. Contoh resmi Microsoft adalah:

ActiveSheet.Range("A1:C100").RemoveDuplicates Columns:=Array(1,2), Header:=xlYes

Baris itu membandingkan dua kolom pertama pada rentang A1:C100 dan memperlakukan baris pertama sebagai header. Untuk menggunakannya, tekan Alt+F11, sisipkan modul, tempelkan baris tersebut di dalam Sub, lalu simpan file sebagai buku kerja dengan makro (.xlsm). Jalankan dahulu pada salinan. Makro menghapus sama permanennya dengan perintah biasa, dan Ctrl+Z tidak akan mengembalikan baris yang terhapus. Tim TI Anda juga mungkin memblokir makro, jadi periksa dulu sebelum membuat sesuatu yang tidak bisa dijalankan rekan kerja Anda. Jika ingin mempelajari bahasanya lebih lanjut, halaman VBA mengumpulkan materi terkait.

Excel untuk web dan seluler

Artikel Microsoft tentang penghapusan duplikat hanya mencantumkan versi desktop Excel (Microsoft 365, 2024, 2021, 2019, dan 2016) dan tidak membahas aplikasi web maupun seluler. Microsoft tidak menjelaskan perintah Remove Duplicates untuk versi-versi tersebut, jadi jangan membangun proses di atasnya. Yang didokumentasikan Microsoft untuk web dan seluler adalah fungsi UNIQUE. Gunakan fungsi itu untuk mendapatkan daftar yang bersih di perangkat tersebut, dan lakukan penghapusan permanen di desktop.

Kesalahan umum

  • Menjalankan perintah tanpa cadangan. Begitu file ditutup, baris-barisnya hilang untuk selamanya.
  • Mencentang kolom yang salah. Mengabaikan sebuah kolom akan mengubah baris mana yang dianggap duplikat, dan seluruh baris akan dihapus, bukan hanya sel yang berulang.
  • Lupa mengurutkan terlebih dahulu, lalu bertanya-tanya mengapa data lama tetap tersisa, bukan data yang baru.
  • Membandingkan sel yang tampak sama tetapi diformat berbeda, misalnya tanggal yang ditulis dengan dua cara atau teks dengan spasi di akhir.
  • Mencoba menjalankan perintah pada data yang memiliki outline atau subtotal. Hapus dahulu struktur tersebut.
  • Menganggap UNIQUE dengan exactly_once bernilai TRUE sebagai cara untuk menghapus data berlebih. Fungsi ini hanya mengembalikan nilai yang muncul sekali, sehingga nilai yang berulang hilang sepenuhnya dari hasil.

Topik terkait untuk dipelajari

  • Sorot duplikat dengan pemformatan bersyarat dan tandai baris yang berulang dengan kolom bantu COUNTIF sebelum Anda menghapus apa pun. Keduanya sudah dibahas di atas.
  • Bersihkan data saat impor. Langkah Power Query menjaga agar duplikat tidak muncul di setiap penyegaran, dan cocok untuk file CSV maupun sistem lain.
  • Cegah duplikat saat entri data. Validasi data dengan rumus kustom dapat diatur untuk mencegah pengguna mengetik nilai yang sudah ada. Uji dulu pada lembar kecil.
  • Otomatisasi pekerjaan yang berulang dengan makro, dan pelajari cara menjalankannya dengan aman pada salinan.
  • Menggabungkan data dari beberapa sumber, tempat duplikat sering berasal. Rencanakan urutan pembersihan dan penggabungan.

Langkah berikutnya

Sebelum pembersihan berikutnya, salin data, sorot duplikat dengan pemformatan bersyarat, dan lihat berapa jumlahnya. Lalu jalankan Remove Duplicates pada salinan, dan bandingkan jumlahnya dengan angka yang dilaporkan Excel.

Sumber

Entities in this article

Microsoft Excel Power Query Visual Basic for Applications (VBA)

Related articles

RSS