Menghilangkan Duplikasi Data pada Excel

Muatan :

• Kilasan tentang duplikasi dan fitur Excel
• Fitur Advanced Filter
• Fitur Remove Duplicates
• Dampak spasi berlebih di sebelum, di sesudah, atau di dalam teks
• Menghilangkan duplikasi data dengan mempertimbangkan case sensitive

---

Kilasan

Umumnya suatu data tidak dikehendaki adanya duplikasi record yang tidak sesuai dengan kenyataan. Contohnya adalah data nasabah. Tidak tepat jika terdapat lebih dari satu record yang dimiliki seorang nasabah dengan seluruh nilai kolomnya sama. Sedangkan untuk transaksi yang dilakukan nasabah dengan mengabaikan waktu transaksi, sangat mungkin terjadi lebih dari satu transaksi dengan nilai transaksi yang sama. Untuk jenis seperti data transaksi tersebut, duplikasi dihilangkan disertai dengan penjumlahan nilai transaksi. Data transaksi seperti ini bisa saja disimpan apa adanya. Pada data referensi seperti data nasabah, proses entry data memungkinkan terjadinya duplikasi, tetapi penyimpanan data tetaplah dituntut untuk unique records.

Excel memiliki fitur yang dapat dimanfaatkan untuk menghilangkan duplikasi data. Fitur tersebut adalah Advanced Filter (pada seluruh versi Excel) dan Remove Duplicates (mulai Excel 2007). Kedua fitur ini bekerja dengan tidak mempedulikan huruf kapital atau bukan, yang sering disebut case insensitive. Record yang diambil adalah yang pertama kali ditemukan. Data Tabel berikut adalah contoh data yang akan digunakan pada kasus ini. (terimakasih kepada Pak Fauzi yang telah menyediakan data ini)

warna hijau muda menunjukkan adanya spasi di sebelum atau sesudah teks

---

Fitur Advanced Filter

Salah satu kemampuan fitur ini adalah menampilkan atau mengekstrak unique records dari suatu data. Cara mengaktifkan fitur ini :

• Excel 2003 ke bawah
  Data -> Filter -> Advanced Filter
• Excel 2007 ke atas
  Data -> bagian Sort & Filter -> Advanced

Akan muncul dialog box :

Bagian :

• Action
  Bagian ini memiliki 2 (dua) pilihan, yaitu :
• Filter the list, in-place
  Akan menghasilkan list terfilter (perhatikan nomor baris)
  
• Copy to another location
  Opsi ini membutuhkan input lokasi hasil filter yang harus diisi pada bagian Copy to. Hasil akan berupa hasil ekstraksi data ke lokasi yang ditentukan tersebut.
  
• List range
  Adalah data yang akan di-Advanced Filter
• Criteria range
  Adalah range yang berisi kriteria filter (untuk kasus ini, bagian kriteria tidak perlu diisi)
• Copy to
  Adalah range lokasi hasil ekstraksi yang harus diisi jika bagian Action dipilih Copy to another location.
• Unique records only
  Berupa checkbox yang harus dicentang jika ingin menghasilkan unique records dari data pada bagian List range.

Jadi, untuk kasus ini, bagian unique records only harus dicentang.

---

Fitur Remove Duplicates (Excel 2007 ke atas)

Fitur ini secara muncul mulai Excel 2007. Ditujukan untuk memberi keleluasaan pemilihan fields utama yang menjadi dasar keunikan sebuah tabel yang akan diproses. Cara mengaktifkan fitur ini :

• Blok seluruh data
• Pada ribbon
  Data -> bagian Data Tools -> Remove Duplicates

Akan muncul dialog box :

Bagian :

• My data has headers
  Adalah checkbox pernyataan yang harus dicentang jika data memiliki header
• Column
  Adalah daftar nama kolom dari data yang diblok. Beri centang pada kolom-kolom yang menjadi kriteria keunikan sebuah record

Jika data tidak memiliki header, maka nama kolom Excel yang digunakan. Fitur ini hanya bekerja pada range yang diblok saja. Hasil dari proses akan mengubah data yang diblok.

---

Dampak spasi di sebelum atau sesudah teks

Entry data bisa menghasilkan data isian yang memiliki spasi di sebelum atau sesudah teks isian yang digunakan. Spasi adalah sebuah karakter, maka spasi akan mempengaruhi sama atau tidaknya sebuah teks dengan teks lain layaknya huruf. Jadi antara teks-teks 'Aku suka' , ' Aku suka' , 'Aku suka ' , 'Aku  suka' tidak ada yang terduplikasi akibat keberadaan karakter spasi.

Contoh yang nyata adalah pada hasil proses pengeliminasian data terduplikasi di atas, yang menunjukkan kesan masih adanya record yang tampak sama atau terduplikasi. Jika diamati cells yang diberi warna hijau muda, maka tampaklah dampak dari karakter spasi. Selain karakter spasi, perlu diwaspadai juga tentang keberadaan karakter tersembunyi. Fungsi Clean bisa membantu hal ini, meskipun tidak seluruhnya.

Data berikut adalah data di atas yang sudah dihilangkan karakter spasi di sebelum atau sesudahnya. Proses penghilangan spasi berlebih ini adalah menggunakan fungsi Trim. Fungsi Trim juga menunggalkan karakter spasi ditengah teks. Fungsi Trim bekerja hanya untuk karakter spasi dengan kode ASCII 32 dan tidak untuk karakter spasi berkode ASCII 160.

Berikut ini hasil proses pengeliminasian data terduplikasi.

Hasil menunjukkan bahwa data baris 2 dan baris 16 berstatus terduplikasi, sehingga hanya tersisa baris 2 saja. Hal ini membuktikan bahwa fitur Advanced Filter maupun Remove Duplicates bersifat case insensitive.

---

Bagaimana dengan data yang harus case sensitive ?

Data pada sub bagian 3 dapat digunakan untuk membahas hal ini lebih jauh. Tentu saja dibutuhkan sebuah kolom bantu yang menjadi ID record. Untuk record yang memiliki kesamaan secara case sensitive haruslah memiliki nomor ID record yang sama.

Langkah pertama yang dilakukan adalah melakukan pengurutan data (sort) berdasar fields utama yang menjadi karakteristik keunikan. Dalam hal ini, dilakukan sort multi kolom berdasar kolom [marketing],[klien],[cabang]. Proses sort juga bersifat case insensitive. Gambar berikut adalah hasil sort.

Sifat komparasi Excel terhadap teks adalah case insensitive juga. Contohnya pada data kolom [klien] antara nilai 'JECK' dan 'jeck'. Sebuah formula komparasi seperti ="JECK"="jeck" akan menghasilkan nilai TRUE. Maka dibutuhkan fungsi Find yang bersifat case sensitive untuk membedakan antara karakter kapital dan non-kapital.

Pada contoh formula berikut :

=Find("JECK","jeck")
hasil : error value#VALUE!

=Find("jeck","jeck")
hasil : numerik 1 (posisi teks tersebut diketemukan)

huruf kapital dapat diketahui dengan fungsi Find yang menghasilkan error value. Tentu saja diperlukan pengubahan error value menjadi suatu numerik yang mewakili tidak diketemukannya melalui fungsi Find. Misalkan, error value diganti dengan numerik 0 atau setidaknya kondisi FALSE. Kondisi FALSE setara dengan numerik 0 jika dikonversikan menjadi numerik. Fungsi IsNumber dapat digunakan untuk konversi nilai error value ini. Contohnya adalah susunan formula Find seperti =IsNumber(Find("JECK","jeck")) akan menghasilkan FALSE.

Perlu diwaspadai juga kemungkinan proses fungsi Find seperti pada formula =Find("JECK","bukan JECK") yang menghasilkan numerik 7. Untuk mengantisipasi hal demikian, maka komparasi Excel terhadap teks dapat dijadikan alat cek sebelum dilakukan kalkulasi fungsi Find.

Pada formula :
=IF("JECK"="jeck",IsNumber(Find("JECK","jeck")),0)
hasil dapat bernilai :

• FALSE atau nilai 0
  berarti kedua teks berbeda secara case sensitive
• TRUE
  berarti kedua teks adalah sama secara case sensitive

Jika dibedah, maka langkah kalkulasinya adalah :

Ketika kalkulasi bagian kondisi "JECK"="jeck" menghasilkan :
• Nilai TRUE
  
  maka bagian IsNumber(Find("JECK","jeck")) akan dikalkulasi, dengan urutan langkah :
  1. bagian Find("JECK","jeck")
  2. kemudian bagian IsNumber(hasil no. 1)
  Hasilnya adalah :
  • bernilai TRUE jika hasil no. 1 berupa angka
  • bernilai FALSE jika hasil no. 1 adalah error value
  dan hasil ini menjadi hasil akhir formula.
• Nilai FALSE
Formula akan mengambil nilai bagian FALSE dari fungsi IF, yaitu nilai 0, yang menjadi hasil akhir formula.

Untuk kasus tabel di atas, maka IF memiliki 3 (tiga) kondisi, yaitu komparasi masing-masing kolom dalam hubungan AND, karena ketiga kolom tersbut adalah syarat mutlak yang menjadi karakteristik keunikan sebuah record. Data yang telah di-sort dapat dikomparasi antara 2 (dua) record berurutan. Misal untuk baris 5 (record ke-4), maka kondisi IF adalah :
And(A4=A5,B4=B5,C4=C5) atau (A4=A5)*(B4=B5)*(C4=C5)
Ketika hasil kondisi adalah TRUE, maka diperlukan pengecekan case sensitive menggunakan formula IsNumber(Find()). Formula pengecekan ini adalah :
IsNumber(Find(A5,A4)) * IsNumber(Find(B5,B4)) * IsNumber(Find(C5,C4))
Hasil operasi aritmatika perkalian (*) terhadap suatu nilai kondisi (TRUE atau FALSE) akan menghasilkan nilai 1 atau 0. Operator perkalian bersifat setara dengan AND. Jadi hasil bernilai 1 menunjukkan adanya kesamaan case, dan nilai 0 berarti berbeda case.

Sampai disini, sebagian besar formula untuk kolom bantu ID record telah tersusun. Misalkan, kolom ID record ini diletakkan pada kolom D. Masalah berikutnya yang harus diselesaikan adalah pemberian ID record. Nomor baris Excel dapat digunakan menjadi ID record, dengan memanfaatkan fungsi Row.

Pola data yang telah ter-sort per lu diperhatikan, yaitu (contoh untuk baris 5) :

jika hasil pengecekan kondisi IF berupa (A4=A5)*(B4=B5)*(C4=C5) adalah
• FALSE berarti baris ini adalah record baru yang diberi ID menggunakan formula Row().
• TRUE berarti baris ini harus melakukan pengecekan terhadap case menggunakan formula IsNumber(Find()) untuk semua kolom dalam susunan
  IsNumber(Find(A5,A4)) * IsNumber(Find(B5,B4)) * IsNumber(Find(C5,C4))
  Jika hasil pengecekan kondisi ini adalah :
  • FALSE berarti baris ini adalah record baru yang diberi ID menggunakan formula Row().
  • TRUE berarti baris ini adalah record terduplikasi yang diberi ID seperti ID record baris sebelumnya, yaitu D4.

Formula untuk kolom ID record di kolom D pada baris 5, selengkapnya adalah :

=IF((A4=A5)*(B4=B5)*(C4=C5) ,IF(IsNumber(Find(A5,A4))*IsNumber(Find(B5,B4))*IsNumber(Find(C5,C4)), D4,Row()) ,Row())

Formula tersebut di-copy ke seluruh baris data pada kolom D. Gambar berikut adalah tabel data dengan ID record.

Record yang terduplikasi ditunjukkan dengan warna, yaitu memiliki ID record yang sama. Proses eliminasi data terduplikasi sekarang dapat dilakukan menggunakan fitur Advanced Filter atau Remove Duplicates. Seluruh range data, termasuk kolom ID record, harus terblok sebagai data yang akan diproses oleh fitur Advanced Filter atau Remove Duplicates.

Pada penggunaan fitur Remove Duplicates, bagian Column yang dicentang atau yang menjadi karakteristik keunikan adalah kolom ID record (lihat gambar berikut).

Hasil menggunakan fitur Advanced Filter adalah sebagai berikut :

Hasil menggunakan fitur Remove Duplicates (Excel 2007 ke atas) adalah sebagai berikut :

Jika data sangat banyak dan kalkulasi formula terasa berat, maka formula pada kolom ID record bisa di-copy-paste-values. Andaikan hasil akhir tidak menghendaki adanya kolom ID record, maka kolom ini bisa dihapus atau disembunyikan (column hide).

Sumber : http://excel-mr-kid.blogspot.com/search/label/duplikasi