Saya mencari formula untuk google spreadsheet sorot sel jika nilai duplikat di kolom yang sama
ada yang bisa tolong membantu saya untuk query ini?
Coba ini:
Custom formula
=countif(A:A,A1)>1
(atau perubahan A
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:
=COUNTIF($A$1:$C$50, INDIRECT(ADDRESS(ROW(), COLUMN(), 4))) > 1
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
, pilih Custom formula
pada dropdown.Mengapa tidak bekerja?
COUNTIF(range, kriteria)
, akan membandingkan setiap sel dalam range
untuk kriteria
, 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 untuk ROW()
, dan 2. untuk KOLOM ()
).
Maka kita menggunakan: ALAMAT(baris, kolom, [absolute_relative_mode])
untuk menerjemahkan angka-turut dan kolom untuk sel referensi (seperti B3
. 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, dan 4
mengembalikan format tidak 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 bidang Custom formula
A
dengan kolom anda sendiri.Dari "Teks Berisi" dropdown menu pilih "Custom formula adalah:", dan menulis: "=countif(A:A, A1) > 1" (tanpa tanda kutip)
Aku melakukan persis seperti zolley diusulkan, tetapi tidak harus dilakukan koreksi kecil: gunakan "Custom formula" bukan "Teks Berisi". Dan kemudian bersyarat rendering akan bekerja.
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 :
function findDuplicates() {
// List the columns you want to check by number (A = 1)
var CHECK_COLUMNS = [1];
// Get the active sheet and info about it
var sourceSheet = SpreadsheetApp.getActiveSheet();
var numRows = sourceSheet.getLastRow();
var numCols = sourceSheet.getLastColumn();
// Create the temporary working sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var newSheet = ss.insertSheet("FindDupes");
// Copy the desired rows to the FindDupes sheet
for (var i = 0; i < CHECK_COLUMNS.length; i++) {
var sourceRange = sourceSheet.getRange(1,CHECK_COLUMNS[i],numRows);
var nextCol = newSheet.getLastColumn() + 1;
sourceRange.copyTo(newSheet.getRange(1,nextCol,numRows));
}
// Find duplicates in the FindDupes sheet and color them in the main sheet
var dupes = false;
var data = newSheet.getDataRange().getValues();
for (i = 1; i < data.length - 1; i++) {
for (j = i+1; j < data.length; j++) {
if (data[i].join() == data[j].join()) {
dupes = true;
sourceSheet.getRange(i+1,1,1,numCols).setBackground("red");
sourceSheet.getRange(j+1,1,1,numCols).setBackground("red");
}
}
}
// Remove the FindDupes temporary sheet
ss.deleteSheet(newSheet);
// Alert the user with the results
if (dupes) {
Browser.msgBox("Possible duplicate(s) found and colored red.");
} else {
Browser.msgBox("No duplicates found.");
}
};
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.
=COUNTIF(C:C, C1) > 1
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 seperti tidak 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 kolom C
dan jika ada lebih dari 1 dari mereka (yaitu nilai duplikat) maka: menerapkan sorot (karena formula, secara keseluruhan, mengevaluasi ke BENAR
).
=AND(COUNTIF(C:C, C1) > 1, COUNTIF(C$1:C1, C1) = 1)
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's BENAR
hanya jika apa yang ada di C1
memiliki duplikat. (Ingat bahwa C1
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 baris 1
, 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.
=AND(COUNTIF(C:C, C1) > 1, NOT(COUNTIF(C$1:C1, C1) = 1), COUNTIF(C1:C, C1) >= 1)
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
di C1:C
). Maka itu hanya mengevaluasi untuk BENAR
(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).