[Googleスプレッドシート] 条件付き書式で重複セルに色を付ける
Office※ 本記事はアフィリエイト広告(Amazonアソシエイト)を含みます。
リストの重複チェック、目で追うのは限界があるので条件付き書式に任せます。
基本: カスタム数式で COUNTIF
- 色を付けたい範囲(例:
A1:A)を選択 - 表示形式 → 条件付き書式
- 「書式ルール」で カスタム数式 を選び、次の式を入れる
=COUNTIF($A:$A,$A1)>1
A列の中に、そのセルと同じ値が2つ以上あれば色が付きます。
ℹ️ 以前は
=COUNTIF(A:A,A:A)>1と書いていましたが、数える範囲は$A:$A(固定)、比べる値は$A1(適用範囲の先頭セル)と書き分けておく方が、範囲を変えたときに崩れにくく、読み返したときにも意味が分かります。
ポイント: 適用範囲と式の「先頭」を合わせる
条件付き書式のカスタム数式は、適用範囲の左上のセルを基準に書いた式が、範囲内の各セルにずらしながら適用されます。
| 適用範囲 | 式で書くセル |
|---|---|
A1:A | $A1 |
A2:A(見出しを除く) | $A2 |
適用範囲を A2:A にしたのに式が $A1 のままだと、1行ずれた判定になって「重複していないのに色が付く」状態になります。一番ハマりやすいところ。
空白セルを除外する
空白セル同士も「同じ値」として数えられるので、空行が多いと空白に色が付きます。空でないことを条件に足します。
=AND($A1<>"",COUNTIF($A:$A,$A1)>1)
2回目以降だけ色を付ける
「最初の1つは残して、ダブっている2つ目以降を目立たせたい」なら、数える範囲を先頭から自分の行までにします。
=COUNTIF($A$1:$A1,$A1)>1
$A$1:$A1 は下の行にいくほど範囲が伸びていくので、「自分より上に同じ値があるか」の判定になります。削除候補を探すときはこちらが便利です。
複数列をまとめてチェックする
A〜C列のどこかに同じ値があれば色を付けたいときは、範囲を広げて、比べるセルは相対参照にします。
=COUNTIF($A$1:$C$100,A1)>1
適用範囲は A1:C100。ここだけ A1 に $ を付けないのがポイントです(列方向にもずらしたいので)。
色を付けるだけでなく消したいなら
重複を見つけるだけでなく、削除までしたいなら データ → データクリーンアップ → 重複を削除 が早いです。ただし元に戻しにくいので、条件付き書式で一度目視してから消すのが安全。
まとめ
| やりたいこと | カスタム数式 |
|---|---|
| 重複をすべて色付け | =COUNTIF($A:$A,$A1)>1 |
| 空白は除外 | =AND($A1<>"",COUNTIF($A:$A,$A1)>1) |
| 2回目以降だけ | =COUNTIF($A$1:$A1,$A1)>1 |
| 複数列 | =COUNTIF($A$1:$C$100,A1)>1 |
適用範囲の先頭セルと、式の中のセルを合わせる。これさえ守れば、だいたい思った通りに色が付きます。
GoogleSpreadsheet