【Excel】エクセルで重複が3つ以上のデータに色を付ける方法（複数条件）

エクセルで名簿、受注一覧、商品リストなどを管理していると、同じデータが何度も登録されている行だけを見つけたい場面があります。

特に、重複が2件ではなく3件以上になっているデータだけを色付けしたい場合、通常の重複チェックでは条件に合わないことがあります。

条件付き書式とCOUNTIF関数、COUNTIFS関数を組み合わせれば、指定した列の値や複数列の組み合わせを数え、3回以上出現するデータを自動で目立たせることが可能です。

重複が3つ以上のデータに色を付ける基本の考え方です。

・1列だけを判定する場合はCOUNTIF関数を使います。

・複数条件を判定する場合はCOUNTIFS関数を使います。

・条件付き書式の数式では、判定対象の先頭セルを基準に指定します。

この記事では、1行目が見出しのサンプル表を使いながら、3件以上の重複を抽出して色を付ける手順を詳しく解説します。

入力後にデータが増えても自動判定されるため、確認作業を効率化したい場合にも役立つ方法です。

エクセルで重複が3つ以上のデータに色を付ける方法

それではまず、1つの列に同じ値が3回以上あるセルへ色を付ける基本操作について解説していきます。

商品コード	商品名	担当者
A-101	ノート	田中
B-205	ペン	佐藤
A-101	ノート	鈴木
A-101	ノート	高橋

条件付き書式を設定する範囲の選択

最初に、重複を確認したい商品コードの範囲を選択します。

この例では見出しが1行目にあるため、A2からA100のようにデータが始まる2行目以降だけを選択します。

見出しのA1まで選択しても数式自体は動作しますが、見出し文字が判定に含まれる可能性があるため、実務ではデータ部分だけを範囲指定するほうが安全です。

エクセルで重複が3つ以上のデータに色を付ける方法 - 条件付き書式を設定する範囲の選択

範囲を選択したら、ホームタブにある条件付き書式をクリックします。

続けて新しいルールを選び、数式を使用して、書式設定するセルを決定を選択しましょう。

【操作のポイント】選択範囲の先頭をA2にした場合、数式内でも最初の判定セルはA2を使います。

COUNTIF関数による3件以上の判定式

数式の入力欄には、次の数式を入力します。

=COUNTIF($A$2:$A$100,A2)>=3

COUNTIF関数は、指定した範囲の中にA2と同じ値がいくつあるかを数える関数です。

数式の最後にある>=3は、数えた結果が3以上ならTRUE、2以下ならFALSEにする条件です。

最初の引数である$A$2:$A$100は検索範囲なので、コピーされても変化しないように列と行の両方を固定します。

一方、2番目のA2は各行ごとにA3、A4のように変化させる必要があるため、絶対参照にはしません。

エクセルで重複が3つ以上のデータに色を付ける方法 - COUNTIF関数による3件以上の判定式

数式を入力した後に書式ボタンをクリックし、塗りつぶしタブから黄色や赤系の見やすい色を選択します。

【操作のポイント】検索範囲は実際の最大行より少し余裕を持たせると、後からデータを追加するときに調整しやすくなります。

適用結果と空白セルを除外する数式

設定を確定すると、A-101のように3回出現した値だけが自動的に色付き表示になります。

この方法では、同じ商品コードが4回や10回出現した場合も、該当するすべてのセルが強調されます。

ただし、対象範囲に空白セルが3つ以上ある場合、空白も同じ値として数えられ、不要に色が付くことがあります。

空白を除外したいときは、次の数式へ変更してください。

=AND(A2<>””,COUNTIF($A$2:$A$100,A2)>=3)

AND関数を追加すると、A2が空白ではなく、かつ出現回数が3回以上である場合だけ書式が適用されます。

空白行を含む管理表では、空白除外の式を最初から使うと誤判定を防ぎやすくなります。

【操作のポイント】色が付かない場合は、適用先の範囲とCOUNTIF関数の検索範囲がずれていないかを確認しましょう。

複数条件で重複が3つ以上のデータを判定する方法

続いては、商品コードだけでなく、店舗や担当者などの複数条件が同じ行を3件以上として判定する方法を確認していきます。

店舗	商品コード	数量
東京店	A-101	2
大阪店	A-101	1
東京店	A-101	3
東京店	A-101	1

複数列を同時に選択する範囲指定

店舗と商品コードの組み合わせが3件以上ある行全体を色付けしたい場合は、A2からC100のように表全体を選択します。

このように設定すると、判定に使う列だけでなく、数量など同じ行にある情報もまとめて見やすくできます。

行全体を塗りつぶす設定は、一覧表から対象レコードを探すときに便利です。

複数列を選択しても、数式は左上のセルであるA2を起点に作成します。

判定用の店舗列がA列、商品コード列がB列という位置関係を確認してから進めることが大切です。

【操作のポイント】表全体を選ぶ場合でも、見出し行は除き、データの先頭行から選択します。

COUNTIFS関数で店舗と商品コードを数える数式

複数条件を使う場合は、COUNTIF関数ではなくCOUNTIFS関数を使用します。

条件付き書式の数式欄には、次のように入力します。

=COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2)>=3

この数式は、A列の店舗名が現在の行と同じであり、さらにB列の商品コードも現在の行と同じレコードを数えます。

$A2と$B2では列を固定し、行番号だけを相対参照にしています。

そのため、表全体にルールが適用されたときも、各行で店舗と商品コードの組み合わせを正しく判定できます。

複数条件で重複が3つ以上のデータを判定する方法 - COUNTIFS関数で店舗と商品コードを数える数式

同じA-101でも大阪店が1件だけなら色は付きませんが、東京店のA-101が3件あれば、その3行が強調されます。

【操作のポイント】複数条件では、条件範囲と条件セルの列を間違えると別の組み合わせを数えてしまうため、A列とB列の対応を確認します。

担当者や日付を追加した三つの条件

店舗、商品コード、担当者の3項目がすべて同じデータだけを判定したい場合も、COUNTIFS関数に条件を追加すれば対応できます。

=COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2,$C$2:$C$100,$C2)>=3

条件範囲と条件は必ず2つで1組になるため、追加したい列ごとに範囲とセルを続けて指定します。

日付を条件に含める場合は、表示形式が同じでも実際の値が文字列と日付に分かれていないか注意が必要です。

たとえば2026年10月1日という見た目でも、片方が文字列なら別データと判定されるかもしれません。

複数条件の判定は、重複入力だけを精密に見つけたい帳票で特に効果を発揮します。

【操作のポイント】条件を増やすほど一致の範囲は狭くなるため、何を同一データと見なすかを先に決めておきましょう。

条件付き書式の数式と適用先を確認する手順

続いては、作成したルールを確認し、数式と適用先を正しく調整する手順を確認していきます。

行	店舗	商品コード	判定
2	東京店	A-101	3件目
3	大阪店	A-101	1件
4	東京店	A-101	3件目

ルールの管理画面を開く操作

条件付き書式が期待どおりに動かない場合は、ルールの管理画面を開いて設定内容を確認します。

ホームタブから条件付き書式をクリックし、ルールの管理を選択します。

表示する書式ルールの対象を現在の選択範囲にすると、該当するルールを見つけやすくなります。

数式欄にはCOUNTIFまたはCOUNTIFSを使った式が表示され、適用先にはルールが反映されるセル範囲が表示されます。

ここで数式の先頭セルと適用先の左上セルが対応しているかを見ることが重要です。

【操作のポイント】不要なルールが複数残っていると色が競合するため、使わないルールは管理画面で削除します。

数式バーと適用先を再現した確認画面

数式を設定する際は、選択範囲の先頭セルを基準にして参照が作られているかを確認します。

重複チェック.xlsx – Excel − □ ×

ファイル　ホーム　挿入　ページレイアウト　数式　データ　校閲　表示

貼り付け
B
罫線
条件付き書式

fx　=COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2)>=3

	A	B	C
1	店舗	商品コード	数量
2	東京店	A-101	2
3	大阪店	A-101	1

赤枠の先頭行を基準にします

➤

上の画面では、適用先がA2からC100であり、数式も2行目のA2とB2を基準にしています。

この対応関係が崩れると、1行ずれた判定や意図しないセルへの色付けにつながります。

【操作のポイント】数式のセル番地を修正したら、適用先も同じ開始行になっているか必ず確認します。

絶対参照と相対参照を使い分ける考え方

条件付き書式の数式では、ドル記号の付け方が判定精度を左右します。

$A$2:$A$100のような検索範囲は、どのセルを判定しても同じ範囲を数える必要があるため絶対参照です。

一方で、A2や$A2は各行の値を見比べるための参照であり、下方向へ移動すると行番号が変わる必要があります。

検索範囲は固定します。

判定する現在行のセルは行番号を固定しません。

表全体を色付けする場合は、判定用の列だけを固定する$A2の形がよく使われます。

行全体を選択しているのにA2だけで判定したい場合は、列記号Aの前にドル記号を付けることがポイントです。

【操作のポイント】F4キーを押すと参照形式を切り替えられるため、数式を作るときの入力負担を減らせます。

重複データだけを色付けする対象範囲の設定

続いては、セルだけを目立たせる方法と、該当行全体を色付けする方法の違いについて確認していきます。

注文番号	顧客名	商品コード
1001	山田商店	A-101
1002	青木商会	A-101
1003	木村産業	A-101

判定列だけに書式を適用する方法

商品コードだけを確認できればよい場合は、C2からC100だけを選択して条件付き書式を設定します。

この設定では、重複が3件以上ある商品コードのセルだけが塗りつぶされます。

ほかの列の情報を見やすく保ったまま、チェック対象だけに注意を向けられる点が利点です。

式は単一列の判定であれば、=COUNTIF($C$2:$C$100,C2)>=3を利用できます。

色付けする列とCOUNTIF関数で数える列を同じC列にすることで、構造がわかりやすくなります。

【操作のポイント】コード、メールアドレス、社員番号など、重複を確認するキー項目だけを選ぶと表が見やすくなります。

該当する行全体に色を付ける方法

重複した商品コードがある注文の詳細まで追いたい場合は、A2からC100のように行全体を選択します。

数式は商品コードがC列なら、次のように列だけを固定します。

=COUNTIF($C$2:$C$100,$C2)>=3

この数式をA2からC100へ適用すると、C列の値を基準に、該当行の注文番号と顧客名も含めて着色されます。

セル範囲を横方向にコピーするときも、$C2のC列が固定されるため、A列やB列で誤った値を数える心配がありません。

一覧から重複レコードの内容を調査する場合は、行全体の色付けが向いています。

【操作のポイント】数式中の$C2の列記号は、実際に重複を判定したい列へ置き換えます。

テーブル機能を使った範囲の拡張

データが日々追加される表では、通常のセル範囲ではなくテーブル機能を利用すると管理しやすくなります。

一覧内のセルを選択して挿入タブからテーブルを選ぶと、データ範囲がテーブルとして設定されます。

テーブルの最終行に新しいレコードを入力すると、条件付き書式が自動的に引き継がれるケースがあります。

ただし、既存の条件付き書式の適用先が固定範囲のままなら、新しい行にルールが及ばないこともあります。

追加後はルールの管理画面を開き、適用先に新しい行が含まれているか確認しましょう。

【操作のポイント】毎月増える台帳では、あらかじめ十分な行数まで適用先を広げる方法も実用的です。

色が付かない場合の原因と重複判定の注意点

続いては、数式を入れたのに色が付かない場合や、想定外のセルが色付けされる場合の原因を確認していきます。

入力値	表示	判定上の注意
A-101	A-101	通常の文字列
A-101	A-101	末尾スペースの可能性
０１２３	０１２３	全角数字

空白やスペースが含まれるデータ

見た目が同じ値でも、セルの先頭や末尾に半角スペース、全角スペースが含まれると別の値として扱われます。

そのため、A-101とA-101の後ろにスペースがある値は、COUNTIF関数では一致しないことがあります。

入力データを整えるには、別列でTRIM関数を使って余分な半角スペースを取り除く方法があります。

=TRIM(A2)

全角スペースも混ざる場合は、SUBSTITUTE関数で全角スペースを空文字へ置換してからTRIM関数を使う方法が有効です。

判定式を複雑にする前に、元データの表記ゆれを整えることが、正確な重複チェックへの近道です。

【操作のポイント】スペースが疑われるセルでは、数式バーを見て文字の後ろに余分な空白がないか確認します。

数値と文字列が混在している場合

社員番号や商品番号に先頭ゼロがある場合、数値と文字列が混在すると重複判定の結果がわかりにくくなることがあります。

たとえば0123を数値として入力すると123になり、文字列の0123とは表示も値も異なります。

コード類を扱う列は、入力前にセルの表示形式を文字列へ変更しておくと、先頭ゼロを保持できます。

すでに混在しているデータは、TEXT関数や文字列への変換を使い、形式を統一してから条件付き書式を設定しましょう。

表示形式だけを変えても元の値が変わらない場合があるため、値そのものが統一されているかを見ることが大切です。

【操作のポイント】JANコードや郵便番号などは計算用の数値ではなく、文字列として管理するとトラブルを減らせます。

条件付き書式の優先順位と停止条件

同じセルに複数の条件付き書式が設定されていると、優先順位によって表示色が変わることがあります。

たとえば赤色のルールと黄色のルールが同時に真になった場合、ルールの管理画面で上にあるルールが優先される設定です。

ルールの管理画面では、上下矢印を使って順番を変更できます。

条件を満たす場合は停止するという設定が有効になっていると、下位のルールが実行されない点にも注意しましょう。

重複3件以上のルールを最優先で見せたい場合は、一覧の上部に配置すると確認しやすくなります。

【操作のポイント】色のルールを増やした後は、管理画面で適用先と優先順位をまとめて点検しましょう。

まとめエクセルで重複が3つ以上のデータに色を付ける方法（複数条件）

エクセルで重複が3つ以上のデータだけに色を付けたい場合は、条件付き書式の数式ルールを使う方法が便利です。

判定したい内容	使用する関数	基本の考え方
1列の値が3件以上	COUNTIF	同じ値を数えます。
2列以上の組み合わせが3件以上	COUNTIFS	すべての条件が一致する行を数えます。
空白を除外したい	ANDとCOUNTIF	空白ではない条件を追加します。