[Googleスプレッドシート] 条件付き書式で重複セルに色を付ける

Office

※ 本記事はアフィリエイト広告(Amazonアソシエイト)を含みます。

[Googleスプレッドシート] 条件付き書式で重複セルに色を付ける

リストの重複チェック、目で追うのは限界があるので条件付き書式に任せます。

基本: カスタム数式で COUNTIF

  1. 色を付けたい範囲(例: A1:A)を選択
  2. 表示形式 → 条件付き書式
  3. 「書式ルール」で カスタム数式 を選び、次の式を入れる
=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