Cara conditional formatting duplikat di spreadsheet

Saat bekerja dengan data di Google Sheets, cepat atau lambat, Anda akan menemukan masalah duplikat data. Ini benar-benar dapat merusak spreadsheet Anda. Bukankah lebih baik jika ada cara untuk menemukan semua duplikatnya sekaligus? . Baca terus untuk menguasainya

Daftar isi

Cara Menyoroti Duplikat di Google Sheets – Sel dalam Kolom

Situasi paling umum yang Anda perlukan Google Sheets untuk menyorot duplikat adalah ketika Anda memiliki kumpulan data dalam kolom dan Anda ingin dengan cepat ingin menyorot duplikat

Misalnya, Anda memiliki kumpulan data seperti yang ditunjukkan di bawah ini di mana Anda ingin menyorot semua nama yang berulang di kolom A

Di bawah ini adalah langkah-langkah untuk menyorot duplikat di kolom

1. Pilih nama dataset [tidak termasuk header]
2. Klik opsi Format di menu
3. Di opsi yang muncul, klik Pemformatan bersyarat. Ini akan membuka panel Aturan format bersyarat di sisi kanan
4. Klik opsi 'Tambahkan aturan lain'
5. Pastikan rentang [di mana kita perlu menyorot duplikat] sudah benar. Jika tidak, Anda dapat mengubahnya dari bagian 'Terapkan ke rentang'
6. Klik pada tarik-turun 'Format sel jika' dan kemudian klik opsi 'Rumus khusus adalah'
7. Di kolom di bawah ini, masukkan rumus berikut.

   =COUNTIF[$A$2:$A$10,A2]>1

8. Dari opsi 'Formatting style', tentukan format di mana Anda ingin menyorot sel duplikat. Secara default, ini akan menggunakan warna hijau, tetapi Anda dapat menentukan warna serta gaya lain seperti tebal atau miring
9. Klik Selesai

Langkah-langkah di atas akan menyorot semua sel dengan nama duplikat dalam warna yang ditentukan

Satu hal hebat tentang pemformatan bersyarat adalah dinamis. Artinya, jika Anda mengubah data di salah satu sel, pemformatan akan diperbarui secara otomatis. Misalnya, jika Anda menghapus salah satu nama yang memiliki duplikat, sorotan dari nama itu [di sel lain] akan hilang karena sekarang menjadi unik.

Baca lebih banyak. Cara Menyalin Pemformatan Bersyarat Di Google Sheets

Bagaimana cara kerjanya?

Saat Anda menggunakan rumus khusus dalam pemformatan Bersyarat, setiap sel akan diperiksa menggunakan rumus yang ditentukan untuk mencari duplikat di Google Spreadsheet .

Jika rumus mengembalikan TRUE untuk sel, itu disorot dalam format yang ditentukan, dan jika mengembalikan FALSE, itu tidak

Dalam contoh di atas, setiap sel dicentang dan jika namanya muncul lebih dari satu kali dalam rentang, hasilnya BENAR untuk rumus COUNTIF, dan sel disorot. Lain, itu tetap tidak berubah

Perhatikan juga bahwa saya telah menggunakan kisaran $A$2. $A$10 [di mana ada tanda dolar sebelum alfabet kolom dan nomor baris]. Ini sangat penting, karena memastikan bahwa saat rumus berpindah ke sel berikutnya [pada baris di bawah], rentang keseluruhan yang diperiksa untuk jumlah nama tetap sama.

Jika Anda ingin menghapus sel yang disorot, Anda harus menghapus pemformatan bersyarat. Untuk melakukan ini, pilih sel yang menerapkan pemformatan, klik opsi Format, klik Pemformatan Bersyarat, dan hapus aturan dari panel yang terbuka di sebelah kanan

Temukan Duplikat di Google Sheets dengan Menyorotinya di Beberapa Kolom

Dalam contoh di atas, kami memiliki semua nama dalam satu kolom

Tapi bagaimana jika nama ada di beberapa kolom untuk melakukan pemformatan bersyarat untuk duplikat di Google Sheets [seperti yang ditunjukkan di bawah]

Anda masih dapat menggunakan pemformatan bersyarat untuk menyorot nama duplikat [yang akan menjadi nama yang muncul lebih dari sekali di ketiga kolom yang digabungkan

Di bawah ini adalah langkah-langkah untuk menyorot duplikat di beberapa kolom

1. Pilih nama dataset [tidak termasuk header]
2. Klik opsi Format di menu
3. Di opsi yang muncul, klik Pemformatan bersyarat
4. Klik opsi 'Tambahkan aturan lain'
5. Pastikan rentang [di mana kita perlu menyorot duplikat] sudah benar. Jika tidak, Anda dapat mengubahnya dari bagian 'Terapkan ke rentang'
6. Klik pada tarik-turun 'Format sel jika' dan kemudian klik opsi 'Rumus khusus adalah'
7. Di kolom di bawah ini, masukkan rumus berikut.

   =COUNTIF[$A$2:$C$10,A2]>1

   _

8. Dari opsi 'Formatting style', tentukan format di mana Anda ingin menyorot sel duplikat. Secara default, ini akan menggunakan warna hijau, tetapi Anda dapat menentukan warna serta gaya lain seperti tebal atau miring
9. Klik Selesai

Langkah-langkah di atas akan menyorot sel jika namanya muncul lebih dari sekali dalam gabungan ketiga kolom yang dipilih

Bagaimana cara kerjanya?

Yang ini juga berfungsi seperti yang terakhir untuk pemformatan duplikat bersyarat Google Spreadsheet.

Dalam rumus COUNTIF, kami telah mencakup semua sel dalam tiga kolom. Jadi setiap sel dalam rentang diperiksa menggunakan rumus yang ditentukan dan mengembalikan BENAR atau SALAH. Rumus COUNTIF mirip dengan rumus duplikat untuk Google Sheets

Jika ada nama yang berulang di salah satu kolom, itu akan disorot dalam format yang ditentukan

Sekali lagi, perhatikan bahwa saya telah menggunakan kisaran $A$2. $C$10 [di mana ada tanda dolar sebelum alfabet kolom dan nomor baris]. Ini sangat penting, karena memastikan rentang tetap sama sementara pemformatan bersyarat memeriksa jumlah nama dalam sel

Cara Menyoroti Nilai Duplikat Di Google Sheets [Temukan Baris Duplikat]

Yang ini agak rumit

Misalkan Anda memiliki kumpulan data seperti yang ditunjukkan di bawah ini dan Anda ingin Google Sheets menemukan semua duplikat melalui penyorotan

Dalam hal ini, record akan menjadi duplikat jika memiliki nilai yang sama persis di setiap sel dalam baris [seperti baris 2 dan 7 pada contoh di atas]

Alasannya agak rumit adalah karena Anda tidak perlu memeriksa sel satu per satu sekarang. Anda harus memeriksa seluruh baris dan hanya menyorot baris di mana semua sel berulang

Tapi jangan khawatir, itu tidak terlalu sulit untuk dilakukan

Di bawah ini adalah langkah-langkah untuk menyorot baris duplikat menggunakan pemformatan bersyarat

1. Pilih set data [tidak termasuk header]
2. Klik opsi Format di menu
3. Di opsi yang muncul, klik Pemformatan bersyarat
4. Klik opsi 'Tambahkan aturan lain'
5. Klik pada tarik-turun 'Format sel jika' dan kemudian klik opsi 'Rumus khusus adalah'
6. Di kolom di bawah ini, masukkan rumus berikut.

   =COUNTIF[ARRAYFORMULA[$A$2:$A$10&$B$2:$B$10&$C$2:$C$10],$A2&$B2&$C2]>1

7. Dari opsi 'Formatting style', tentukan format di mana Anda ingin menyorot sel duplikat. Secara default, ini akan menggunakan warna hijau, tetapi Anda dapat menentukan warna serta gaya lain seperti tebal atau miring
8. Klik Selesai

Langkah-langkah di atas akan menyorot semua catatan yang berulang dalam kumpulan data [seperti yang ditunjukkan di bawah ini]

Bagaimana cara kerjanya?

Yang ini bekerja dengan cara yang sama seperti contoh pertama kami [di mana kami hanya menyorot sel di kolom yang memiliki duplikat]

Tapi karena ada seluruh baris yang perlu kita bandingkan dengan semua baris lainnya, kita menggabungkan konten dari semua baris dan membuat satu string untuk setiap baris

Bagian berikut dari rumus membuat array string di mana semua konten sel dalam satu baris digabungkan [melakukan penggabungan menggunakan tanda ampersand]

ARRAYFORMULA[$A$2:$A$10&$B$2:$B$10&$C$2:$C$10]

Larik ini digunakan dalam rumus Countif, dan kondisi yang digunakan lagi-lagi berupa string gabungan yang memiliki semua nilai dalam satu baris. Hal ini dilakukan dengan menggunakan berikut sebagai kriteria

$A2&$B2&$C2

Ini sekarang telah diubah menjadi konstruk tipe kolom sederhana di mana fungsi COUNTIF memeriksa berapa kali string gabungan ini berulang dalam larik string yang kita buat

Ini pada akhirnya akan menyoroti semua catatan yang merupakan duplikat

Tampilkan Duplikat di Google Sheets Dengan Kriteria Tambahan

Lembaran Google juga dapat menggunakan sebagian besar kriteria tambahan yang dapat Anda pikirkan untuk menyorot data rangkap. Misalnya, Anda dapat menyetel sistem agar hanya menyorot duplikat untuk nilai tertentu

Sintaks harus menggunakan operator “*” [dan] sehingga COUNTIF akan menggunakan kedua kriteria tersebut. Sintaksnya akan terlihat seperti ini

=[COUNTIF[Range,Criteria]>1] * [New Condition] ]

Kami dapat menggunakan data karyawan kami dari sebelumnya untuk menunjukkan cara kerjanya. Anggap saja kami menemukan bahwa sebenarnya ada dua Henry dalam pemasaran, jadi kami memperbarui nomor ID salah satunya

Kami masih ingin menonjolkan karyawan duplikat, sehingga kami dapat menambahkan syarat kedua untuk dipenuhi seperti itu

1. Menavigasi aturan pemformatan bersyarat seperti pada contoh sebelumnya di atas
2. Masukkan bagian pertama rumus dengan rentang dan format yang harus kita ketahui sekarang dari =[countif[Range,Criteria]>1] jadi contoh kita adalah

=[Countif[$A$2:$C$10,$A2]>1]

Ini akan menunjukkan jika ada duplikat di baris A tetapi tidak akan mengecualikan id Henry kedua yang sekarang tidak cocok sehingga Anda memerlukan langkah-langkah tambahan ini

1. Gunakan perintah “*” [dan] setelah rumus pertama
2. Tambahkan kondisi kedua ke sintaks [COUNTIF[Range,Criteria]>1] tetapi pastikan Anda menangani baris lainnya – baris C dalam kasus ini. Seluruh formula akan terlihat seperti ini

=[Countif[$A$2:$C$10,$A2]>1]*[countif[$A$2:$C$10,$C2]>1]

Tentu saja dengan contoh yang tepat ini, Anda bisa saja menggunakan bagian kedua dari rumus untuk mendapatkan hasil yang sama, tetapi ini hanya gambaran bagaimana menambahkan kondisi kedua dapat digunakan. Tergantung pada kebutuhan Anda, Anda juga bisa

• Gunakan yang berbeda setelah kriteria yaitu >0 atau Pemformatan bersyarat
• Klik simbol tempat sampah di sebelah aturan yang ada

Potensi Masalah dengan Solusi Sederhana saat Menyoroti Sel Duplikat di Google Sheets

Terkadang, mungkin saja Anda mengikuti semua langkah di atas dan menggunakan rumus yang sama, tetapi Google Sheets tetap tidak menyorot duplikatnya. Jika tidak disorot, bagaimana cara menghapus duplikatnya?

Berikut adalah beberapa kemungkinan alasan yang dapat Anda periksa

Ruang Ekstra di Sel

Apakah ada spasi ekstra [karakter spasi awal atau akhir] dalam teks dalam satu sel dan bukan yang lain?

Karena kita sedang mencari pencocokan tepat untuk dua sel atau lebih untuk dianggap duplikat, jika ada karakter spasi ekstra di dalam sel, hal ini akan menyebabkan ketidakcocokan

Jadi meskipun Anda dapat melihat bahwa ada duplikat, itu mungkin tidak disorot

Untuk menghilangkannya, Anda dapat menggunakan fungsi TRIM [dan fungsi CLEAN] untuk menghilangkan semua karakter spasi ekstra

Referensi salah

Ada tiga jenis referensi di Google Sheets

1. Referensi mutlak [contoh – $A$1]
2. Referensi relatif [contoh – A1]
3. Referensi campuran [contoh – $A1 atau A$1]

Jika suatu rumus membutuhkan satu jenis referensi dan Anda akhirnya menggunakan yang lain, kemungkinan besar Anda akan mengalami masalah

Jadi periksa referensi untuk memastikan Google Sheets menyorot duplikat seperti yang diharapkan

Beberapa Tips Saat Menyoroti Duplikat di Google Sheets

• Pastikan Anda tidak memiliki aturan pemformatan bersyarat lainnya untuk sel yang diinginkan yang dapat memberikan hasil yang salah
• Pastikan Anda tidak kehilangan ruang dari pencarian Anda
• Jangan pilih header saat menggunakan ARRAYFORMULA untuk menyorot duplikat di Google Sheets

Cara Memeriksa Duplikat di Google Sheets Menggunakan UNIQUE

Rumus ini jauh lebih mudah digunakan saat bekerja dengan kumpulan data yang lebih kecil dan Anda hanya perlu melihat berapa banyak potongan data unik yang ada di spreadsheet Anda. Meskipun, itu tidak memberikan duplikat Google Sheets pemformatan bersyarat.

Sintaksnya sederhana

=UNIQUE[Range]

_

Jadi, dalam contoh kita sebelumnya, yang harus kita lakukan hanyalah menemukan semua nilai unik di dalam is

1. Pilih sel kosong [D2 dalam kasus kami] dan tekan enter
2. Ketik =UNIQUE dan klik dan seret sel yang ingin Anda periksa data uniknya
   
3. Tekan enter dan daftar setiap nilai unik akan ditampilkan di kolom
   

Menyoroti Sel Dengan FAQ Pemformatan Bersyarat

Bisakah Google Sheets Menyoroti Duplikat?

Ya, Anda dapat menyorot duplikat. Anda harus menggunakan kombinasi rumus COUNTIF dan pemformatan bersyarat. Anda dapat menyetel pemformatan bersyarat untuk hanya menyorot sel jika rumus COUNTIF mengembalikan nilai BENAR

Apa Formula untuk Menyoroti Duplikat di Google Sheets?

Berikut cara mengidentifikasi duplikat di Google Spreadsheet. Anda hanya perlu menggunakan rumus COUNTIF di dalam menu pemformatan bersyarat. Rumus COUNTIF menggunakan sintaks berikut.

=COUNTIF[range, criterion]

_

Jadi, Anda harus memasukkan rentang yang ingin Anda soroti dan >1 sebagai kriteria. Seperti, jika ada lebih dari satu contoh, sorot sel. Ikuti panduan di atas lebih lanjut tampilan yang lebih mendalam

Bagaimana Saya Mengelompokkan Duplikat di Google Sheets?

Untuk mengelompokkan duplikat, Anda dapat menggunakan fungsi SORT atau Anda dapat menggunakan pintasan pengurutan di bilah alat. Berikut cara menemukan duplikat di Google Spreadsheet dengan menu Sort.

1. Pilih kolom yang ingin Anda kelompokkan duplikat
2. Arahkan ke Data > Urutkan lembar

Jika Anda telah menggunakan metode kami di atas untuk memformat duplikat secara kondisional. Anda juga dapat mengurutkan berdasarkan warna untuk menghindari pengurutan sel yang tidak duplikat

Bagaimana Saya Melihat Duplikat di Google Sheets?

Anda dapat melihat duplikat dengan menggunakan pemformatan bersyarat di menu format dan rumus COUNTIF. Kemudian pilih warna yang ingin Anda soroti duplikatnya

Bagaimana Saya Menyoroti Sel yang Sama Dengan Duplikat di Google Sheets?

Sayangnya, Anda tidak dapat mencari duplikat dalam satu sel

Bagaimana Saya Membandingkan Dua Kolom Di Google Sheets untuk Menemukan Duplikat?

Anda dapat menggunakan rumus COUNTIF sebagai rumus khusus di dalam menu pemformatan bersyarat untuk menemukan duplikat dan membandingkan dua kolom. Cukup sertakan referensi sel dalam rumus dan gunakan >1 sebagai kriteria

Bagaimana Anda Menyoroti Nilai Duplikat Kecuali untuk Instance Pertama di Google Sheets?

Ikuti metode di atas untuk menggunakan sel penyorotan kecuali gunakan >2 sebagai kriteria. Ini berarti penyorotan hanya akan terjadi jika ada dua atau lebih dari dua contoh nilai, yaitu, = mengabaikan kecocokan pertama

Bagaimana Saya Membandingkan Spreadsheet Google yang Berbeda untuk Duplikat?

Anda dapat melakukan ini dengan rumus atau skrip yang rumit, tetapi cara paling sederhana adalah dengan menambahkan lembar yang diperlukan ke dalam satu spreadsheet total dan kemudian membandingkan kedua lembar tersebut. Untuk melakukan ini

1. Klik kanan nama sheet di bagian bawah halaman
2. Arahkan kursor ke Salin ke
3. Klik Spreadsheet yang ada dan pilih spreadsheet yang diinginkan
4. Kemudian Anda dapat menggunakan referensi lembar dalam rumus COUNTIF misalnya jika Anda ingin menggunakan rentang sel dari Lembar1 Anda dapat mengetikkan =Sheet1

Gandakan Pembelajaran Anda

Jadi ini adalah cara Anda dapat menyorot duplikat di Google Sheets menggunakan pemformatan bersyarat

Saya harap Anda menemukan tutorial ini bermanfaat

Tutorial Google Spreadsheet lain yang mungkin Anda sukai

• Terapkan Pemformatan Bersyarat berdasarkan Nilai Sel Lain di Google Sheets
• Cara Menghitung Sel dengan Teks Tertentu Di Google Sheets
• Cara Membandingkan Dua Kolom di Google Sheets [untuk Kecocokan dan Perbedaan]
• Cara Mencari di Google Sheets
• Cara Menggunakan Fungsi UNIK
• Cara Menyoroti Nilai Tertinggi atau Terendah di Google Sheets

Postingan Terpopuler

5 Cara Sederhana untuk Menyoroti Duplikat di Google Sheets

JIKA BERISI Formula Google Sheets [2 Opsi Pintar]

Cara Menerapkan Formula ke Seluruh Kolom di Google Sheets

Cara VLOOKUP Dari Lembar Lain di Google Lembar

Puncak

Sumit adalah Pakar Google Spreadsheet dan Microsoft Excel. Dia memberikan pelatihan spreadsheet kepada perusahaan dan telah dianugerahi penghargaan bergengsi Excel MVP oleh Microsoft atas kontribusinya dalam berbagi pengetahuan Excel dan membantu orang.

7 pemikiran pada “5 Cara Sederhana untuk Menyoroti Duplikat di Google Sheets”

1. Satya

   Saya menjelajahi begitu banyak situs web tetapi gagal mendapatkan hasil yang benar
   Tapi di sini saya mendapatkan hasil saya. Penjelasan yang bagus
   Terima kasih

2. Jusuf

   Terima kasih banyak untuk ini

3. Yuri

   Hai, mulai 21/02/2022, rumus ini tampaknya telah berhenti berfungsi di google sheets

   Saya juga mencoba menggunakan ";" . dan tidak dapat mengklik "Selesai" untuk menerapkan format bersyarat

   Tolong, ada saran?

4. Michael

   Bagaimana kalau kriterianya bisa dari sel mana saja?

   Jika ada 20 sel, dan saya perlu memeriksa apakah ada entri duplikat pada sel yang tidak bersebelahan itu, apakah ada cara untuk memeriksa apakah kriteria dapat berupa entri apa pun dari daftar drop-down?

5. andrew

   Orang lain tampaknya menyalin satu sama lain tetapi Anda jelas tahu apa yang Anda tulis
   Ini adalah artikel komprehensif yang pernah saya lihat tentang menyoroti duplikat di Google Sheets, sehingga saya hanya perlu menulis dan mengatakan selesai dengan baik

6. Muhammad

   Hei, saya menghadapi situasi yang sedikit lebih rumit. Saya mengerjakan terlalu banyak kata kunci untuk situs web saya. Saya memiliki website penjualan jaket. Saya melakukan penelitian kata kunci dan menemukan beberapa kata kunci yang bagus. Namun ada banyak kata kunci yang berulang tetapi tidak semua kata kunci yang berulang. Hal parsial dari kata kunci berulang seperti
   Jaket Bomber Ranboo, Jaket Varsity Ranboo dan Jaket Letterman Ranboo. Ini adalah 3 kata kunci tetapi bukan 3 produk. Ini adalah 3 nama berbeda dari 1 produk. Ini seperti BBQ dan Bar B Que, Kids and Children. Maksud saya, kata kunci yang berbeda dengan arti yang sama. Jadi, dalam hal ini saya tidak dapat menghapus duplikat ini. Bagaimana cara membuat kata kunci saya unik?

   • Christopher Daniel

     Anda dapat mencari seluruh frase atau hanya sebagian dari frase. Misalnya jika Anda hanya ingin mencari duplikat dengan kata kunci "Ranboo", Anda dapat menggunakan itu dan kemudian * wildcard seperti Ranboo*