Anda dapat menemukan dan menyorot entri duplikat di antara dua kolom menggunakan fitur Pemformatan Bersyarat di Google Spreadsheet.
Saat bekerja di Google Spreadsheet dengan kumpulan data besar, Anda mungkin mengalami masalah di mana Anda harus berurusan dengan banyak nilai duplikat. Sementara beberapa entri duplikat ditempatkan dengan sengaja sementara yang lain adalah kesalahan. Ini terutama benar ketika Anda berkolaborasi di lembar yang sama dengan sebuah tim.
Dalam hal menganalisis data di Google Spreadsheet, kemampuan untuk memfilter duplikat bisa menjadi penting dan nyaman. Meskipun Google Spreadsheet tidak memiliki dukungan asli untuk menemukan duplikat di sheet, Google Sheets menawarkan beberapa cara untuk membandingkan, mengidentifikasi, dan menghapus data duplikat dalam sel.
Terkadang, Anda ingin membandingkan setiap nilai dalam kolom dengan kolom lain dan menemukan apakah ada duplikat di dalamnya dan sebaliknya. Di Google Sheets, Anda dapat dengan mudah menemukan duplikat di antara dua kolom dengan bantuan fitur pemformatan bersyarat. Pada artikel ini, kami akan menunjukkan cara membandingkan dua kolom di Google Spreadsheet dan menemukan duplikat di antara keduanya.
Temukan Entri Duplikat Antara Dua Kolom menggunakan Pemformatan Bersyarat
Pemformatan bersyarat adalah fitur di Google Spreadsheet yang memungkinkan pengguna untuk menerapkan pemformatan tertentu seperti warna font, ikon, dan bilah data ke sel atau rentang sel berdasarkan kondisi tertentu.
Anda dapat menggunakan pemformatan bersyarat ini untuk menyorot entri duplikat di antara dua kolom, baik dengan mengisi sel dengan warna atau mengubah warna teks. Anda perlu membandingkan setiap nilai dalam kolom dengan kolom lain dan menemukan apakah ada nilai yang berulang. Agar ini berfungsi, Anda harus menerapkan pemformatan bersyarat ke setiap kolom secara terpisah. Ikuti langkah-langkah ini untuk melakukannya:
Buka spreadsheet yang ingin Anda periksa duplikatnya di Google Spreadsheet. Pertama, pilih kolom pertama (A) untuk dicentang dengan kolom B. Anda dapat menyorot seluruh kolom dengan mengklik huruf kolom di atasnya.
Kemudian, klik menu 'Format' dari bilah menu dan pilih 'Pemformatan bersyarat'.
Menu Pemformatan Bersyarat terbuka di sisi kanan lembar google. Anda dapat mengonfirmasi rentang sel yang Anda pilih di bawah opsi 'Terapkan ke rentang'. Jika Anda ingin mengubah rentang, klik 'ikon rentang' dan pilih rentang yang berbeda.
Kemudian, klik drop-down di bawah 'Format rules' dan pilih opsi 'Custom formula is'.
Sekarang, Anda perlu memasukkan formula khusus di kotak 'Nilai atau rumus'.
Jika Anda memilih seluruh kolom (B:B), masukkan rumus COUNTIF berikut ke dalam kotak 'Nilai atau rumus' di bawah Aturan format:
=countif($B:$B,$A2)>0Atau,
Jika Anda memilih rentang sel dalam kolom (misalnya seratus sel, A2:A30), gunakan rumus ini:
=COUNTIF($B$2:$B$30, $A2)>0Saat Anda memasukkan rumus, pastikan untuk mengganti semua contoh huruf 'B' dalam rumus dengan huruf kolom yang Anda soroti. Kami menambahkan tanda '$' sebelum referensi sel untuk menjadikannya rentang absolut, sehingga tidak mengubah kami menerapkan rumus.
Di bagian Gaya pemformatan, Anda dapat memilih gaya pemformatan untuk menyorot item duplikat. Secara default, ini akan menggunakan warna isian hijau.
Anda dapat memilih salah satu gaya pemformatan prasetel dengan mengeklik 'Default' di bawah opsi 'Gaya pemformatan', lalu memilih salah satu prasetel.
Atau, Anda dapat menggunakan salah satu dari tujuh alat pemformatan (Tebal, Miring, Garis Bawah, Dicoret, Warna teks, Warna isian) di bawah bagian 'Gaya pemformatan' untuk menyorot duplikat.
Di sini, kami memilih warna isian untuk sel duplikat dengan mengklik ikon 'Isi warna' dan memilih warna 'kuning'.
Setelah Anda memilih pemformatan, klik 'Selesai' untuk menyorot sel.
Fungsi COUNTIF menghitung berapa kali setiap nilai sel di 'Kolom A' muncul di 'Kolom B'. Jadi jika item muncul bahkan sekali di kolom B, rumusnya mengembalikan TRUE. Kemudian item tersebut akan disorot di 'Kolom A' berdasarkan format yang Anda pilih.
Ini tidak menyoroti duplikat, melainkan menyoroti item yang memiliki duplikat di Kolom B. Itu berarti setiap item yang disorot kuning memiliki duplikat di Kolom B.
Sekarang, kita harus menerapkan pemformatan bersyarat ke Kolom B menggunakan rumus yang sama. Untuk melakukannya, pilih kolom kedua (B2:B30), buka menu 'Format', dan pilih 'Pemformatan bersyarat'.
Atau, klik tombol 'Tambahkan aturan lain' di bawah panel 'Aturan format bersyarat'.
Selanjutnya, konfirmasikan rentang (B2:B30) di kotak 'Terapkan ke rentang'.
Kemudian, atur opsi 'Format sel jika..' menjadi 'Rumus khusus adalah' dan masukkan rumus di bawah ini di kotak rumus:
=COUNTIF($A$2:$A$30, $B2)>0Di sini, kami menggunakan rentang kolom A ($A$2:$A$30) di argumen pertama dan '$B2' di argumen kedua. Rumus ini akan memeriksa nilai sel di 'kolom B' terhadap setiap sel di kolom A. Jika kecocokan (duplikat) ditemukan, maka pemformatan bersyarat akan meninggikan item tersebut di 'kolom B'
Kemudian, tentukan pemformatan di opsi 'Gaya pemformatan' dan klik 'Selesai'. Di sini, kami memilih warna oranye untuk kolom B.
Ini akan menyorot item kolom B yang memiliki duplikat di kolom A. Sekarang, Anda telah menemukan dan menyorot item duplikat di antara dua kolom.
Anda mungkin memperhatikan, meskipun ada duplikat untuk 'Arcelia' di kolom A, itu tidak disorot. Hal ini dikarenakan nilai duplikatnya hanya pada satu kolom (A) bukan antar kolom. Makanya, tidak ditonjolkan.
Sorot Duplikat Antara Dua kolom di Baris yang Sama
Anda juga dapat menyorot baris yang memiliki nilai (duplikat) yang sama di antara dua kolom menggunakan pemformatan bersyarat. Aturan pemformatan bersyarat dapat memeriksa setiap baris dan menyoroti baris yang memiliki data yang cocok di kedua kolom. Inilah cara Anda melakukannya:
Pertama, pilih kedua kolom yang ingin Anda bandingkan, lalu buka menu ‘Format’ dan pilih ‘Pemformatan bersyarat’.
Di panel Aturan format bersyarat, konfirmasikan rentang di kotak 'Terapkan ke rentang' dan pilih 'Rumus khusus adalah' dari tarik-turun 'Sel rumus jika..'.
Kemudian, masukkan rumus di bawah ini di kotak 'Nilai atau rumus':
=$A2=$B2Rumus ini akan membandingkan dua kolom baris demi baris dan menyorot baris yang memiliki nilai identik (duplikat). Seperti yang Anda lihat, rumus yang dimasukkan di sini hanya untuk baris pertama dari rentang yang dipilih, tetapi rumus akan diterapkan secara otomatis ke semua baris dalam rentang yang dipilih oleh fitur pemformatan bersyarat.
Kemudian, tentukan pemformatan dari opsi 'Gaya pemformatan' dan klik 'Selesai'.
Seperti yang Anda lihat, hanya baris yang memiliki data yang cocok (duplikat) antara dua kolom yang akan disorot dan semua duplikat lainnya akan diabaikan.
Sorot Sel Duplikat di Beberapa Kolom
Saat bekerja dengan spreadsheet yang lebih besar dengan banyak kolom, Anda mungkin ingin menyorot semua duplikat yang muncul di beberapa kolom, bukan hanya satu atau dua kolom. Anda masih dapat menggunakan pemformatan bersyarat untuk menyorot duplikat di beberapa kolom.
Pertama, pilih rentang semua kolom dan baris yang ingin Anda cari duplikatnya, bukan hanya satu atau dua kolom. Anda dapat memilih seluruh kolom dengan menahan tombol Ctrl, lalu mengklik huruf di bagian atas setiap kolom. Atau, Anda juga dapat mengklik sel pertama dan terakhir dalam jangkauan Anda sambil menahan tombol Shift untuk memilih beberapa kolom sekaligus.
Dalam contoh, kami memilih A2:C30.
Kemudian, klik opsi 'Format' di menu dan pilih 'Pemformatan bersyarat'.
Dalam aturan format Bersyarat, atur aturan Format ke 'Rumus khusus adalah', lalu masukkan rumus berikut di kotak 'Nilai atau Rumus':
=countif($A$2:$C$30,A2)>Kami menambahkan tanda '$' sebelum referensi sel untuk menjadikannya kolom absolut, sehingga tidak mengubah kami menerapkan rumus. Anda juga dapat memasukkan rumus tanpa tanda '$', ini berfungsi dengan baik.
Kemudian, pilih pemformatan di mana Anda ingin menyorot sel duplikat menggunakan opsi 'Gaya pemformatan'. Di sini, kami memilih warna isian 'Kuning'. Setelah itu, klik ‘Selesai’.
Ini akan menyorot duplikat di semua kolom yang Anda pilih, seperti yang ditunjukkan di bawah ini.
Setelah menerapkan pemformatan bersyarat, Anda dapat mengedit atau menghapus aturan pemformatan bersyarat kapan pun Anda mau.
Jika Anda ingin mengedit aturan pemformatan bersyarat saat ini, pilih sel mana pun dengan pemformatan bersyarat, buka 'Format' pada menu, dan pilih 'Pemformatan bersyarat'.
Ini akan membuka panel 'Aturan format bersyarat' di sebelah kanan dengan daftar aturan format yang diterapkan ke pilihan saat ini. Saat Anda mengarahkan mouse ke aturan, itu akan menampilkan tombol hapus, klik tombol hapus untuk menghapus aturan. Atau, jika Anda ingin mengedit aturan yang sedang ditampilkan, klik aturan itu sendiri.
Jika Anda ingin menambahkan pemformatan bersyarat lain di atas aturan saat ini, klik tombol 'Tambahkan aturan lain'.
Hitung Duplikat Antara Dua Kolom
Terkadang, Anda ingin menghitung berapa kali nilai dalam satu kolom berulang di kolom lain. Itu dapat dengan mudah dilakukan dengan menggunakan fungsi COUNTIF yang sama.
Untuk menemukan berapa kali nilai di kolom A ada di kolom B, masukkan rumus berikut di sel di kolom lain:
=COUNTIF($B$2:$B$30,$A2)Masukkan rumus ini di sel C2. Rumus ini menghitung berapa kali nilai di sel A2 ada di kolom (B2:B30) dan mengembalikan hitungan di sel C2.
Saat Anda mengetik rumus dan menekan Enter, fitur IsiOtomatis akan muncul, klik 'Tanda centang' untuk mengisi rumus ini secara otomatis ke sel lainnya (C3:C30).
Jika fitur pengisian otomatis tidak muncul, klik kotak biru di sudut kanan bawah sel C2 dan seret ke bawah untuk menyalin rumus di sel C2 ke sel C3:C30.
Kolom 'Perbandingan 1' (C) sekarang akan menunjukkan kepada Anda berapa kali setiap nilai yang sesuai di kolom A muncul di kolom B. Misalnya, nilai A2, atau "Franklyn" tidak ditemukan di kolom B, jadi, Fungsi COUNTIF mengembalikan "0". Dan nilai "Loreta" (A5) ditemukan dua kali di kolom B, sehingga mengembalikan "2".
Sekarang, kita harus mengulangi langkah yang sama untuk menemukan jumlah duplikat kolom B. Untuk melakukannya, masukkan rumus berikut di sel D2 di kolom D (Perbandingan 2):
=COUNTIF($A$2:$A$30,$B2)Dalam rumus ini, ganti rentang dari '$B$2:$B$30' menjadi '$A$2:$A$30' dan '$B2' menjadi '$A2'. Fungsi menghitung berapa kali nilai di sel B2 ada di kolom A (A2:A30) dan mengembalikan hitungan di sel D2.
Kemudian, isi rumus secara otomatis ke sel lainnya (D3:D30) di kolom D. Sekarang, 'Perbandingan 2' akan menunjukkan berapa kali setiap nilai yang sesuai di kolom B muncul di kolom A. Misalnya , nilai B2, atau "Stark" ditemukan dua kali di kolom A, jadi, fungsi COUNTIF mengembalikan "2".
Catatan: Jika Anda ingin menghitung duplikat di semua kolom atau beberapa kolom, Anda hanya perlu mengubah rentang dalam argumen pertama fungsi COUNTIF menjadi beberapa kolom, bukan hanya satu kolom. Misalnya, ubah rentang dari A2:A30 ke A2:B30, yang akan menghitung semua duplikat dalam dua kolom, bukan hanya satu.
Itu dia.