Bagaimana untuk menyorot sel jika nilai duplikat di kolom yang sama untuk google spreadsheet?
Saya mencari formula untuk google spreadsheet sorot sel jika nilai duplikat di kolom yang sama
ada yang bisa tolong membantu saya untuk query ini?
251
7
Coba ini:
Custom formula
=countif(A:A,A1)>1
(atau perubahanA
untuk anda memilih kolom)A1:A100
).Apa pun yang ditulis di A1:A100 sel-sel akan diperiksa, dan jika ada duplikat (terjadi lebih dari sekali) maka'll menjadi berwarna.
Untuk akomodasi yang tepat menggunakan koma (
,
) sebagai pemisah desimal, argumen pemisah kemungkinan besar semi-colon (;
). Artinya, coba:=countif(A:A;A1)>1
, bukan.Untuk beberapa kolom, gunakan
countifs
.Sementara zolley's jawaban ini sangat tepat untuk pertanyaan tersebut, berikut ini's yang lebih umum solusi untuk setiap range, plus penjelasan:
Harap dicatat bahwa dalam contoh ini saya akan menggunakan berbagai
A1:C50
. Parameter pertama ($A$1:$C$50
) harus diganti dengan kisaran di mana anda ingin menyoroti duplikat!untuk menyoroti duplikat:
Format
>Conditional formatting...
Berlaku untuk range
, pilih rentang yang aturan harus diterapkan.Format sel jika
, pilihCustom formula
pada dropdown.Mengapa tidak bekerja?
COUNTIF(range, kriteria)
, akan membandingkan setiap sel dalamrange
untukkriteria
, yang diolah demikian pula dengan rumus. Jika tidak ada operator khusus yang disediakan, hal ini akan membandingkan setiap sel dalam range dengan sel yang diberikan, dan mengembalikan jumlah sel-sel yang ditemukan sesuai aturan (dalam hal ini, perbandingan). Kami menggunakan berbagai tetap (dengan$
tanda-tanda) sehingga kita selalu melihat berbagai.Blok kedua,
tidak LANGSUNG(ALAMAT(ROW(), KOLOM(), 4))
, akan kembali saat sel's konten. Jika ini ditempatkan di dalam sel, dokumen akan menangis tentang ketergantungan melingkar, tetapi dalam kasus ini, rumus dievaluasi seolah-olah itu adalah dalam sel, tanpa mengubah itu.ROW()
dan KOLOM()
akan kembali baris jumlah dan kolom ** jumlah sel yang diberikan masing-masing. Jika tidak ada parameter yang disediakan, saat ini sel akan dikembalikan (ini adalah 1-based, misalnya,B3
akan kembali 3 untukROW()
, dan 2. untuk KOLOM()
).Maka kita menggunakan:
ALAMAT(baris, kolom, [absolute_relative_mode])
untuk menerjemahkan angka-turut dan kolom untuk sel referensi (sepertiB3
. Ingat, saat kita berada di dalam sel's konteks, kita don't know it's alamat ATAU konten, dan kita membutuhkan konten dalam rangka untuk membandingkan dengan). Ketiga parameter mengurus untuk pemformatan, dan4
mengembalikan formattidak LANGSUNG()
suka.Tidak LANGSUNG()
, akan mengambil sel referensi dan kembali isinya. Dalam kasus ini, sel saat ini's konten. Kemudian kembali ke awal,COUNTIF()
akan menguji setiap sel dalam rentang terhadap kita, dan kembali menghitung.Langkah terakhir adalah membuat formula kami kembali boolean, dengan membuat ekspresi logis:
COUNTIF(...) > 1
. The> 1
digunakan karena kita tahu ada's setidaknya satu sel sama dengan kita. Yang's sel kita, yang berada dalam jangkauan, dan dengan demikian akan dibandingkan untuk dirinya sendiri. Jadi untuk menunjukkan duplikat, kita perlu menemukan 2 atau lebih sel-sel yang cocok untuk kita.Sumber:
Jawaban dari @zolley adalah benar. Hanya menambahkan Gif dan langkah-langkah untuk referensi.
Format > pemformatan Bersyarat..
Format sel jika..
=countif(A:A,A1)>1
di bidangCustom formula
A
dengan kolom anda sendiri.Aku melakukan persis seperti zolley diusulkan, tetapi tidak harus dilakukan koreksi kecil: gunakan "Custom formula" bukan "Teks Berisi". Dan kemudian bersyarat rendering akan bekerja.
Juga, anda dapat menginstal add-on Menghapus Duplikat (highlight) atau menghapus duplikat nilai-nilai dalam kolom atau lembaran.
Saya mencoba semua pilihan dan tidak bekerja.
Hanya google app script membantu saya.
sumber : https://ctrlq.org/code/19649-find-duplicate-rows-in-google-sheets
Di bagian atas dokumen anda
1.- pergi ke alat > script editor
2.- mengatur nama anda script
3.- paste kode ini :
4.- simpan dan jalankan
Dalam waktu kurang dari 3 detik, saya duplikat baris berwarna. Hanya copy-past script.
Jika anda don't tahu tentang google apps script , link ini bisa membantu anda:
https://zapier.com/learn/google-sheets/google-apps-script-tutorial/
https://developers.google.com/apps-script/overview
Saya harap ini membantu.
Sorot duplikat (di kolom C):
Penjelasan:
C1
di sini doesn't mengacu pada baris pertama di C. Karena formula ini dievaluasi oleh bersyarat format aturan, sebaliknya, bila rumus diperiksa untuk melihat jika itu berlaku,C1
secara efektif mengacu ke mana-turut saat ini sedang dievaluasi untuk melihat apakah puncak harus diterapkan. (Sehingga's lebih sepertitidak LANGSUNG(C &ROW())
, jika itu berarti sesuatu untuk anda!). Pada dasarnya, ketika mengevaluasi bersyarat format formula, apa pun yang mengacu pada baris 1 adalah evaluasi terhadap baris yang rumus sedang dijalankan terhadap. (Dan ya, jika anda menggunakan C2 kemudian anda meminta aturan untuk memeriksa status dari baris langsung di bawah salah satu yang sedang dievaluasi.)Jadi, ini mengatakan, menghitung kejadian dari apa yang ada di
C1
(sel saat ini sedang dievaluasi) yang ada di seluruh kolomC
dan jika ada lebih dari 1 dari mereka (yaitu nilai duplikat) maka: menerapkan sorot (karena formula, secara keseluruhan, mengevaluasi keBENAR
).Sorot rangkap pertama hanya:
Penjelasan: Ini hanya menyoroti jika kedua
COUNTIF ini adalah
BENAR(mereka muncul dalam sebuah
DAN()`).Istilah pertama untuk dievaluasi (yang
COUNTIF(C:C, C1) > 1
) adalah sama persis seperti di contoh pertama; it'sBENAR
hanya jika apa yang ada diC1
memiliki duplikat. (Ingat bahwaC1
secara efektif mengacu pada baris saat ini sedang diperiksa untuk melihat jika itu harus disorot).Istilah kedua (
COUNTIF(C$1:C1, C1) = 1
) terlihat mirip tetapi memiliki tiga perbedaan penting:Itu doesn't pencarian seluruh kolom
C
(seperti yang pertama tidak:C:C
) tapi bukan itu mulai pencarian dari baris pertama:C$1
(the$
pasukan itu terlihat benar-benar pada baris1
, tidak di mana baris yang sedang dievaluasi).Dan kemudian menghentikan pencarian pada baris saat ini sedang dievaluasi
C1
.Akhirnya ia mengatakan
= 1
.Jadi, itu hanya akan menjadi
TRUE
jika tidak ada duplikat di atas baris yang saat ini sedang dievaluasi (yang berarti itu harus menjadi yang pertama dari duplikat).Dikombinasikan dengan yang pertama istilah (yang hanya akan menjadi
TRUE
jika baris ini memiliki duplikat) ini berarti hanya kejadian pertama akan disorot.Sorot kedua dan seterusnya duplikat:
Penjelasan: ekspresi pertama adalah sama seperti biasa (
TRUE
jika saat ini dievaluasi baris duplikat sama sekali).Kedua istilah ini sama persis seperti terakhir kecuali itu's menegasikan: memiliki
TIDAK()
di sekitar itu. Sehingga mengabaikan pertama kejadian.Akhirnya ketiga istilah ini mengambil duplikat 2, 3 dll.
COUNTIF(C1:C, C1) >= 1
mulai mencari berkisar pada saat ini dievaluasi turut (C1
diC1:C
). Maka itu hanya mengevaluasi untukBENAR
(berlaku sorot) jika ada satu atau lebih duplikat di bawah ini (termasuk satu ini):>= 1
(ini harus menjadi>=
bukan hanya>
jika tidak, lalu duplikat diabaikan).