Bagaimana cara menggunakan pemformatan bersyarat untuk duplikat di google sheets?

Jika Anda bekerja secara teratur dalam spreadsheet, mentransfer berbagai data dari satu sel ke sel lainnya, Anda mungkin berakhir dengan data duplikat di seluruh kolom dan/atau baris. Jika sel Anda berisi formula, kemungkinan konten duplikat ini juga berdampak negatif pada perhitungan Anda. Akibatnya, data Anda tidak lagi akurat, dan akan sangat memakan waktu untuk menyorot dan menghapus setiap nilai duplikat secara manual

Artikel ini akan menjelaskan cara menyorot duplikat di Google Sheets menggunakan fitur pemformatan bersyarat bawaan serta rumus =UNIQUE. Anda kemudian akan mempelajari cara menghapus duplikat dengan aman dari rentang mana pun di kumpulan data Anda, baik yang muncul dalam satu atau dua kolom

Mulailah Dengan Lapisan Sekarang

Bagikan bagian Google Spreadsheet Anda, pantau, tinjau, dan setujui perubahan, serta sinkronkan data dari berbagai sumber – semuanya dalam hitungan detik

Dengan sedikit pengetahuan, Anda dapat menggunakan Google Sheets untuk menyorot duplikat. Anda dapat menyesuaikan cara Spreadsheet merepresentasikan kecocokan duplikat sehingga masuk akal bagi orang yang melihat datanya

Misalnya, Google Spreadsheet dapat menyorot seluruh baris data jika ada nilai duplikat di kolom tertentu

Alternatifnya, Spreadsheet dapat menyorot sel mana saja dengan nilai yang muncul lebih dari sekali di halaman spreadsheet

Panduan berikut menjelaskan cara menyorot duplikat di Google Sheets (dijelaskan dengan beberapa contoh)

Artikel ini Meliputi

Sorot Duplikat di Google Sheets dalam Satu Kolom

Misalkan Anda memiliki kumpulan data seperti yang ditunjukkan di bawah ini dan Anda ingin menyorot semua nilai duplikat di kolom A

Untuk menyorot sel duplikat dengan pemformatan bersyarat, ikuti langkah-langkah berikut

1. Pilih rentang. Dalam kasus contoh, kami ingin menggunakan A2. A15 (gambar 1, kotak)
   
2. Buka jendela pemformatan bersyarat dengan membuka menu drop "Format" dan memilih "Pemformatan bersyarat" dari daftar
   
3. Klik opsi "+ Tambahkan aturan lain" di panel pemformatan bersyarat yang terbuka. Jika Anda sudah menerapkan pemformatan bersyarat, opsi ini akan berada di bagian bawah (di bawah aturan yang ada)
   
4. Setel "Format sel jika ..." menjadi "Rumus khusus adalah" dan masukkan rumus pemeriksaan duplikat "=countif(Range,Criteria)>1" dan tekan "Selesai" untuk mengembalikan respons "benar/salah". Dalam kasus contoh kami, kami menggunakan rumus “=countif($A$2. $A$15,A2)>1” untuk memeriksa duplikat
   

Google Sheets sekarang menyoroti duplikat di daftar belanjaan kami

Perhatikan bahwa menggunakan metode ini menyoroti semua contoh sel duplikat. Jadi jika suatu item diulangi dua atau tiga kali, semua sel yang memiliki item/teks ini akan disorot

Fungsi COUNTIF yang digunakan dalam pemformatan bersyarat menghitung berapa kali string teks sel muncul dalam daftar. Jika lebih dari 1, rumus mengembalikan BENAR dan sel-sel tersebut disorot

Catatan. Gunakan bagian "Formatting style" untuk mengubah warna penyorotan dan gaya font. Ini sangat membantu jika Anda perlu menyorot lebih dari satu warna sekaligus

Sorot Duplikat di Beberapa Kolom Google Sheets

Saat bekerja dengan spreadsheet yang lebih besar dengan banyak kolom, Anda mungkin ingin menyorot seluruh kolom saat ada duplikat, bukan hanya sel.

Ini memudahkan untuk menemukan duplikat saat sel duplikat tidak ada di layar

Proses ini berbeda dalam dua cara

• Rentang harus menyertakan semua baris (A2. A15 sekarang menjadi A2. F15)
• Bagian kriteria perlu menggunakan nilai absolut untuk kolom (A2 sekarang menjadi $A2)

Mari gunakan daftar belanjaan kita sebelumnya, tetapi sekarang kita telah menambahkan di mana Anda menemukan setiap produk di toko sebagai kolom baru (seperti yang ditunjukkan di bawah)

Ikuti langkah-langkah ini untuk menyorot seluruh baris untuk sel duplikat dalam satu kolom

1. Pilih rentang semua kolom dan baris yang ingin Anda sorot, bukan hanya kolom yang Anda telusuri kecocokannya. Dalam contoh, pilih A2. B15 bukan A2. A15
   
2. Buka fitur "Pemformatan bersyarat" dan centang rentang baru (gambar 2, kotak atas)
3. Di tab "Pemformatan bersyarat", atur "Aturan format" ke "Rumus khusus adalah ..." dan masukkan rumus pencarian duplikat dengan kriteria yang disesuaikan untuk kolom absolut. Ini berarti menambahkan "$" sebelum "A2" dalam contoh kita. Jadi “=hitung($A$2. $A$15,A2)>1” sekarang menjadi “=countif($A$2. $A$15,$A2)>1”
   
4. Tekan "Selesai" untuk menerapkan pemformatan baru (gambar 2, kotak bawah)

Sekarang kami telah menyoroti seluruh rentang baris untuk kolom dengan duplikat

Sorot Seluruh Baris jika Duplikat Berada dalam Satu Kolom

Katakanlah kita ingin menyorot seluruh baris jika ada duplikat di kolom B. Dalam contoh kami di bawah ini, kami dapat menerapkan rumus berikut ke menu pemformatan bersyarat untuk mencapainya

=COUNTIF($B$2. $B$7,$B2)>1

Sorot Duplikat Tapi Abaikan Instance Pertama

Seperti yang saya sebutkan sebelumnya, saat Anda menggunakan pemformatan bersyarat untuk menyorot duplikat di Google Sheets, ini akan menyorot semua contoh sel duplikat

Tetapi bagaimana jika Anda ingin menyorot hanya contoh duplikat (dan bukan yang pertama kali muncul dalam daftar)

Anda dapat menyesuaikan rumusnya sehingga Google Sheets hanya akan menyorot contoh duplikat kedua atau yang lebih baru dengan mengubah rumusnya

Misalkan Anda memiliki dataset seperti yang ditunjukkan di bawah ini dan Anda hanya ingin menyorot sel yang memiliki item duplikat

Kita perlu mengubah “=countif($A$2. $A$15,$A2)>1” rumus (ini yang kita gunakan di bagian sebelumnya)

• Sesuaikan nilai rentang akhir dari $A$15 hingga $A2. Sekarang setelah kita menyetel kolom ke absolute di bagian akhir, tetapi sekarang baris, setiap baris hanya akan melihat baris di atasnya sendiri untuk duplikat
• Formula baru kita akan terlihat seperti ini. ”= countif($A$2. $A2,$A2)>1”

Dalam contoh kami, perubahan pada rumus (panah) ini sekarang hanya menyoroti contoh kedua di mana duplikat kami muncul (kotak)

Sorot Duplikat Baris Lengkap di Google Sheets

Sekarang sebagai gantinya, anggaplah Anda hanya ingin menyorot baris jika setiap kolom dalam tabel benar-benar cocok. Kita dapat melakukannya dengan menggunakan fungsi ARRAYFORMULA untuk menggabungkan data menjadi satu string sebelum menerapkan fungsi COUNTIF

Ini akan menerapkan pemformatan bersyarat Google Spreadsheet untuk duplikat ke sel yang sesuai.

Kami menggunakan rumus berikut dalam contoh di bawah ini untuk mendapatkan hasil yang diinginkan. Anda hanya perlu mengubah referensi sel agar sesuai dengan tabel Anda

=COUNTIF(ArrayFormula($A$2:$A$9&$B$2:$B$9&$C$2:$C$9),$A2&$B2&$C2)>1

Pintasan untuk Menghapus Semua Duplikat

Google Sheets memiliki alat praktis untuk menghapus duplikat alih-alih menyorotnya

Kami akan menggunakan daftar belanjaan kami dengan data lokasi produk dari sebelumnya untuk menunjukkan cara kerjanya

Berikut cara menemukan duplikat di Google Sheets dengan alat ini

1. Pilih rentang sel yang ingin Anda analisis
2. Buka menu “Data” dan pilih “Hapus Duplikat”
   
3. Dari pop-up "Hapus duplikat", centang "Data memiliki baris tajuk" jika data memiliki fitur ini dalam pilihan Anda, lalu pilih kolom yang ingin Anda periksa untuk nilai duplikat dari daftar periksa. Dalam contoh kami, kami hanya ingin membandingkan nilai kolom A
4. Pilih "Hapus duplikat" untuk menerapkan perubahan
   

Spreadsheet sekarang hanya memiliki baris tanpa sel duplikat dari pilihan kami

Soroti Duplikat dengan Kriteria Tambahan

Google Spreadsheet dapat memeriksa penyorotan bersyarat dengan kriteria tambahan. Anda dapat menyetel program untuk menyorot hanya baris dengan nilai duplikat di beberapa kolom atau hanya duplikat dengan nilai tertentu

Rumus harus menggunakan operator “*” (dan) untuk menerapkan kedua kondisi. Contoh rumus untuk menemukan duplikat di Google Sheets dengan lebih banyak kriteria akan terlihat seperti ini.

=(countif(Range,Criteria)>1) * (New Condition) )

Misalnya, kita dapat memiliki contoh daftar belanjaan kita dari sebelumnya hanya menyorot produk duplikat yaitu buah atau sayuran. Kami tidak peduli dengan produk yang muncul dua kali di toko roti atau lorong mana pun

Spreadsheet kami sekarang menampilkan daftar semua buah dan sayuran berbeda yang tersedia di toko

Dalam contoh kami, kami ingin menyorot nilai di kolom C dan D jika nilai baris muncul dua kali di kolom C dan sama sekali di kolom A dan B

Konstruksi formula kita akan terlihat seperti ini

• Mulailah dengan rumus lama kami, sekarang dimodifikasi untuk mencentang kolom C, bukan A. =hitung($C$2. $C$15,$C2)>1
• Bungkus rumus dalam tanda kurung untuk menambahkan kondisi kedua. =(hitung($C$2. $C$15,$C2)>1))
• Tambahkan operator “and/*” dan tanda kurung kedua untuk kondisi kedua
• =(hitung($C$2. $C$15,$C2)>1)*())
• Kondisi kedua kami memeriksa apakah kecocokan muncul di kolom A dan B, sehingga akan terlihat sedikit berbeda. Alih-alih memeriksa "lebih besar dari 1", itu akan memeriksa "lebih besar dari 0" dan terlihat seperti ini. countif($A$2. $B$15,$C2)>0
• Formula baru kita akan terlihat seperti ini. =(hitung($C$2. $C$15,$C2)>1)*(hitung($A$2. $B$15,$C2)>0)

Menerapkan rentang baru dan formula baru ke kumpulan data kami akan menyoroti semua buah dan sayuran yang merupakan duplikat. Itu tidak akan menyorot bagel yang muncul dua kali karena tidak ada di kolom buah atau sayuran

Beberapa Tips Saat Menyoroti Duplikat di Google Sheets

Berikut adalah beberapa hal yang perlu diingat saat bekerja menyoroti duplikat di Google Sheets

Edit atau Hapus Aturan Pemformatan Bersyarat

Untuk menghapus atau mengedit pemformatan bersyarat, Anda hanya perlu melakukannya

1. Klik sel mana pun yang saat ini diterapkan pemformatan
2. Arahkan ke Format > Pemformatan bersyarat
3. Klik aturan di menu untuk melakukan perubahan atau klik ikon tempat sampah untuk menghapus

Buat Daftar Sel Unik (Alih-alih menyorot duplikat)

Terkadang melalui proses penyorotan data untuk menemukan duplikat bukanlah cara tercepat untuk mengetahui apa yang Anda cari

Jika Anda hanya ingin membuat daftar nilai unik untuk memvisualisasikan berapa banyak duplikat yang sedang Anda kerjakan, rumus "=unique()" mungkin cocok untuk Anda

Untuk menggunakan fitur ini. masukkan “=unik(rentang mulai. range end)” di sel yang Anda inginkan berada di bagian atas daftar Anda

Dalam contoh daftar belanjaan ini, kita memasukkan “=unique(A2. A15)” di sel B2. Saat diterapkan, itu mengisi semua sel di bawahnya yang diperlukan untuk menghasilkan daftar nilai unik

Pangkas Spasi Putih untuk Mengurasi Data

Menemukan ruang tambahan sebelum dan sesudah data adalah hal yang biasa saat Anda mengimpor informasi ke Google Spreadsheet dari sumber lain

Misalnya, sebuah email mungkin masuk sebagai “me@mysite. tld” dan “me@mysite. tld “ dengan spasi di akhir versi kedua. Ini adalah duplikat tetapi tidak akan muncul karena ruang kosong itu membingungkan Google Sheets

Anda dapat memangkas spasi putih dari data Anda dengan yang berikut ini

• Pilih data yang ingin Anda pangkas (bßox kiri)
• Buka menu drop "Data" (panah)
• Pilih "Potong spasi" dari daftar

Jika data yang dipilih memiliki spasi kosong yang tidak terlihat, Google Spreadsheet akan menghapusnya. Data tidak akan terlihat berbeda bagi Anda, tetapi sekarang berbeda untuk analisis data

FAQ Pemformatan Bersyarat Duplikat Google Spreadsheet

Bisakah Google Sheets Menyoroti Duplikat?

Ya, berikut cara mengidentifikasi duplikat di Google Spreadsheet .

1. Sorot rentang yang ingin Anda periksa duplikatnya
2. Arahkan ke Format > Pemformatan bersyarat
3. Terapkan rumus COUNTIF setelah memilih Rumus khusus dari menu aturan

Apa Formula untuk Menyoroti Duplikat di Google Sheets?

Anda harus menggabungkan rumus COUNTIF dengan menu Pemformatan bersyarat untuk menyorot duplikat

Bagaimana Saya Menemukan Duplikat dalam Dua Kolom di Google Sheets?

Anda harus menggunakan nilai absolut sebagai referensi sel dalam rumus Kustom di dalam menu Pemformatan bersyarat. Berikut dan contoh cara memeriksa duplikat di Google Spreadsheet di dua kolom.

Bagaimana Saya Membandingkan Spreadsheet Google yang Berbeda untuk Duplikat?

Untuk menyorot sel duplikat di seluruh lembar, Anda harus menggunakan rumus kompleks seperti ini

“=AND(A2=INDIRECT("Sheet1!A2:A"),B2=INDIRECT("Sheet1!B2:B"), C2=INDIRECT("Sheet1!C2:C"))"

Kami memiliki panduan lengkap di situs saudari kami

Apakah Mungkin Mengecualikan Kosong untuk Sorotan Duplikat di Baris yang Sama?

Ya, Anda dapat menggunakan kombinasi fungsi AND dan COUNTIFS untuk mengecualikan spasi dengan rumus yang mirip dengan ini

=AND(COUNTIFS(B:B,B1)>1,B1"")

Fungsi DAN di sini memastikan sel hanya dihitung jika ada duplikat dan berisi teks yang ditandai dengan ""

Menyoroti duplikat di Google Sheets adalah alat yang berharga untuk membuat informasi lebih mudah dipahami orang

Spreadsheet menawarkan berbagai pilihan penyesuaian untuk menyorot duplikat dengan berbagai kondisi

Saya harap tutorial ini membantu Anda lebih memahami cara menggunakan fitur yang bermanfaat ini

Ingin menjadi pakar Google Spreadsheet?

Puncak

Pakar Spreadsheet di Tempat Produktivitas . Situs web . + postingan

Google Spreadsheet dan Pakar Microsoft Excel

• Puncak

  https. //titik produktivitas. com/penulis/sumitbansal23/

  3 Cara Mudah Mengatur Area Cetak di Google Sheets

• Puncak

  https. //titik produktivitas. com/penulis/sumitbansal23/

  Cara Membuat Grafik Garis di Google Sheets (Langkah demi Langkah)

• Puncak

  https. //titik produktivitas. com/penulis/sumitbansal23/

  Cara Membagi Teks menjadi Kolom di Google Sheets (dengan Contoh)

• Puncak

  https. //titik produktivitas. com/penulis/sumitbansal23/

  Bagaimana Mengonversi Waktu ke Format Waktu Militer di Google Sheets?

postingan populer

8 Aplikasi Jurnal Terbaik untuk iPhone, iOS, Android, dan Windows pada tahun 2023

Bagaimana Memformat Nomor Telepon Di Google Sheets?

Cara Menggandakan Lembar Google. Panduan Cepat & Mudah

Cara Berbagi Status Fokus di iOS [Panduan 4 Langkah Sederhana]

2 pemikiran tentang “Cara Menyoroti Duplikat Di Google Sheets (Langkah Mudah)”

1. Malaikat Andujar

   Artikel yang luar biasa. Sekarang, setiap kali saya memasukkan item "baru", ia akan memberi tahu saya apakah saya sudah memilikinya atau belum. Luar biasa. Terima kasih

2. Nancy

   Saya mencoba menyalin apa yang Anda lakukan di sini untuk menemukan duplikat di kolom. Mengapa Anda menggunakan A2 sebagai kriteria Anda dan mengapa Anda menggunakan semua tanda $ itu?

   Bagaimana cara mewarnai duplikat di Google Sheets?

   Langkah demi Langkah. Cara Menyoroti Duplikat di Google Sheets (Dengan Gambar) .

   Langkah 1. Buka spreadsheet Anda. .

   Langkah 2. Sorot data yang ingin Anda periksa. .

   Langkah 3. Di bawah "Format", pilih "Pemformatan Bersyarat. ”.

   Langkah 4. Pilih “Rumus khusus adalah. ”.

   Langkah 5. Masukkan rumus pemeriksaan duplikat khusus

   Bisakah Anda menemukan nilai duplikat menggunakan pemformatan bersyarat?

   Pada tab Beranda, di grup Gaya, klik Pemformatan Bersyarat > Sorot Aturan Sel > Nilai Duplikat… Jendela dialog Nilai Duplikat akan terbuka dengan format Light Red Fill dan Dark Red Text yang dipilih secara default. Untuk menerapkan format default, cukup klik OK

   Bagaimana cara memformat duplikat bersyarat di Google Sheets?

   Sorot sel duplikat dalam satu kolom Google Spreadsheet .

   terapkan aturan ke rentang sel Anda — A2. A10 dalam contoh saya

   pilih rumus Kustom dari drop-down dengan kondisi dan masukkan rumus berikut. =COUNTIF($A$2. $A$10,$A2)>1. .

   pilih warna apa pun dari gaya Pemformatan untuk menyorot duplikat tersebut

   klik Selesai

   Bagaimana cara menerapkan duplikat dalam pemformatan bersyarat?

   Temukan dan hapus duplikat .

   Pilih sel yang ingin Anda periksa duplikatnya. .

   Klik Beranda > Pemformatan Bersyarat > Sorot Aturan Sel > Nilai Duplikat

   Dalam kotak di samping nilai, pilih pemformatan yang ingin Anda terapkan ke nilai duplikat, lalu klik OK