Googleスプレッドシート条件付き書式で重複セル（同じ値）に色付けをする｜行全体・複数条件も（AmazonのASINを例に）

2023.06.29 　/　UPDATED 2026.10.24 PC

Googleスプレッドシートで指定した列の中に重複した値（同じ値・同じ文字）があった場合に、そのセルの背景色に色付けする方法をご紹介したいと思います。

今回は、ASINを元に、重複したASINのセルの背景色に色が付くよう書式を設定していきます。

ちなみに、AmazonのASINなどをスプレッドシートに自動で取り込みたい場合は、IMPORTXML関数を使ったスクレイピングが便利です。

「同じASINを2回登録していないか」「同じ名前が入っていないか」など、データの被り・ダブりをパッと見て分かるようにしたいときに便利だと思います。

条件付き書式を設定する

それでは、実際に設定をしていきます。

まず、色を付けたい列（今回はA列）の列番号「A」をクリックして、列全体を選んでおきます。

その状態で、シートの上の方にあるタブの表示形式 > 条件付き書式をクリックします。

画面の右に条件付き書式設定ルールと出ます。範囲に適用には、選んでおいたA列が「A1:A1000」のように自動で入ります（A列の1行目から1000行目まで、という意味です）。ほかの範囲に色を付けたいときは、ここを書き換えればOKです。

次に、セルの書式設定の条件の欄を開いて、一番下にあるカスタム数式をクリックします。

カスタム数式の欄に、=COUNTIF($A:$A, A1)>1 を入力します。

この式の意味について詳しく知りたい場合は、この記事の最後にまとめましたのでそちらをご覧ください。

最後に、書式設定のスタイルで背景色の設定や文字の色やスタイルの設定を行い、完了をクリックします。

無事、A列で重複した値のセルの背景色が変わりました！

列全体を範囲にすると、何も入力していないセルも「空白」という同じ値として数えられ、空白部分まで色が付く場合があります。その場合は、空白を除外する条件を加えた次の式を使います。

=AND(A1<>"", COUNTIF($A:$A, A1)>1)

A1<>”” は「A1が空白ではない」という意味です。データが2行目から始まる場合は、式のA1をA2に変更してください。

もし、A列とB列で重複した値のセルの背景色を変更したい場合は、範囲に適用をA列とB列の両方（A1:B1000）にして、=COUNTIF($A:$B, A1)>1と入力します。

背景色を違う色にしたい場合は、書式設定のスタイルにある塗りつぶしの色ボタンから変更しましょう。

ASINが重複しているときに、そのセルだけではなく、行全体 に色を付けたいこともあると思います。

その場合は、範囲に適用を色を付けたい列まで広げて（今回はA列からB列なので「A1:B1000」）、カスタム数式に次の式を入力します。

=COUNTIF($A:$A, $A1)>1

ポイントは、A1の前にも$を付けて$A1にしているところです。こうすると列がA列に固定されるので、B列のセルでも「A列の値」を見て判定されて、行ごと色が付きます。

ASINは同じでもカラーが違えば別の商品、というように、2つの列が両方とも同じ ときだけ重複として色を付けたい場合もあると思います。

その場合はCOUNTIFS関数を使います。範囲に適用を「A1:B1000」にして、カスタム数式に次の式を入力します。

=COUNTIFS($A:$A, $A1, $B:$B, $B1)>1

COUNTIFSは、COUNTIFの「条件を複数付けられる版」です。「A列が同じ」かつ「B列も同じ」行を数えて、2つ以上あれば色が付きます。

2行目と4行目はASINが同じですが、カラーが「ホワイト」と「ブラック」で違うので色は付きません。ひとつ前の「行全体に色を付ける」と見比べると、違いが分かりやすいと思います。

反対に、A列とB列を1行ずつ見比べて、値が違うセル に色を付けたいこともあると思います。先月と今月の一覧を比べて、変わったところだけ目立たせたい、といったときです。

範囲に適用を「A1:B1000」にして、カスタム数式に次の式を入力します。

=$A1<>$B1

<> は「等しくない」という意味です。同じ行のA列とB列を比べて、違っていれば色が付きます。

何も入っていない行は、A列もB列も空欄で「同じ」扱いになるので、色は付きません。

さて、重複に色を付けることが出来ましたがどうせならこの数式の意味を理解して使いこなしたいですよね？

それでは、画像を用いて解説させていただきます。

条件式 =COUNTIF($A:$A, A1)>1 は、セルA1の値が範囲A列全体に2回以上出現するかどうかを判定するための式です。

具体的に解説すると以下のようになります：

COUNTIF($A:$A, A1) は、範囲A列全体においてセルA1の値と一致するセルの数を数える関数です。$A:$A は列A全体を指定しており、A1 は比較対象のセルを指定しています。
$ は絶対参照を表し、$A:$A と表記することで、列A全体が指定されます。$ を使用することで、セル範囲の列が固定され、条件式がコピーまたはドラッグされた場合に列の参照が変わらなくなります。つまりどのセルにこのカスタム数式が適用されてもA列がカウントの範囲として指定されます。
A1 はセルの参照を表します。この数式では、セルA1の値を条件として使用します。条件式が他のセルにコピーまたはドラッグされた場合、参照セルも自動的に変更されます。例えば、条件式がB2にコピーされると、B2 が参照されるようになります。
>1は「1より大きい」を表します。