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)
Salah satu kemampuan fitur ini adalah menampilkan atau mengekstrak unique records dari suatu data. Cara mengaktifkan fitur ini :
Bagian :
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 :
Bagian :
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.
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 :
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 :
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 sensitivemenggunakan 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) :
Formula untuk kolom ID record di kolom D pada baris 5, selengkapnya adalah :
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).
source :http://fathanqorib.blogspot.com/2013/03/menghilangkan-duplikasi-data-pada-excel.html
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
- 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 bagianCopy 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.
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
- 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
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.
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.
Pada contoh formula berikut :
- =Find("JECK","jeck")
- hasil : error value#VALUE!
- =Find("jeck","jeck")
- hasil : numerik 1 (posisi teks tersebut diketemukan)
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
- Ketika kalkulasi bagian kondisi "JECK"="jeck" menghasilkan :
- Nilai TRUE
- maka bagian IsNumber(Find("JECK","jeck")) akan dikalkulasi, dengan urutan langkah :
- bagian Find("JECK","jeck")
- kemudian bagian IsNumber(hasil no. 1)
- bernilai TRUE jika hasil no. 1 berupa angka
- bernilai FALSE jika hasil no. 1 adalah error value
- 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 sensitivemenggunakan 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.
Pada penggunaan fitur Remove Duplicates, bagian Column yang dicentang atau yang menjadi karakteristik keunikan adalah kolom ID record (lihat gambar berikut).
source :http://fathanqorib.blogspot.com/2013/03/menghilangkan-duplikasi-data-pada-excel.html
0 Response