Excelの重複チェック関数早見表 COUNTIF・UNIQUEと2列比較

Excelの重複チェックは、「同じ値が範囲の中に何個あるか」を COUNTIF で数え、2以上なら重複と判定するのが基本です。 そこから先は目的で式が変わります。印を付けたいのか、2件目以降だけ消したいのか、重複のない一覧がほしいのか、重複している行だけ取り出したいのかを先に決めると、使う関数が1つに絞れます。

この記事では、目的別の数式をコピーして使える形で並べ、同じサンプル表に入れたときの結果を表で示します。2列を比べる条件付き書式用の数式、複数列をキーにする COUNTIFS、全角半角や空白で判定がずれる例と直し方も扱います。

確認環境：数式の結果は LibreOffice Calc 24.2.7.2（Linux、ヘッドレス実行）で xlsx ファイルを計算した値です（確認日 2026-10-05）。UNIQUE と FILTER は Calc 24.2 に含まれないため、Microsoft サポートの関数ページの仕様どおりの処理を Python 3.11 で行った値を載せ、「※」を付けています。

この記事の結論

• 印を付ける：=IF(COUNTIF($A$2:$A$11,A2)>1,"重複","")
• 2件目以降だけ消す：範囲の終わりを伸ばす =COUNTIF($A$2:A2,A2)>1 で印を付け、フィルターで絞って削除
• 重複のない一覧を作る：=UNIQUE(A2:A11)（Excel 2021 以降）
• 重複している行だけ取り出す：=FILTER(A2:B11,COUNTIF(A2:A11,A2:A11)>1)（Excel 2021 以降）
• 2列を比べる：=COUNTIF($B$2:$B$6,$A2)=0 で「A列にあってB列に無い」

重複チェックの目的別早見表

セル範囲は、データが A2:A11（1行目は見出し）にある前提です。

サンプル表と COUNTIF の結果

備品の一覧を例にします。A列が備品名、B列が保管場所です。次のタブ区切りを A1 に貼ると同じ表になります。

備品名 保管場所
ノートPC   2F
モニター    3F
ノートPC   2F
マウス 2F
キーボード   3F
モニター    2F
ノートPC   3F
ヘッドセット  2F
マウス 2F
Webカメラ  3F

C2〜E2 に次の3つの式を入れ、11行目までコピーします。

C2: =COUNTIF($A$2:$A$11,A2)
D2: =IF(COUNTIF($A$2:$A$11,A2)>1,"重複","")
E2: =IF(COUNTIF($A$2:A2,A2)>1,"2件目以降","")

範囲の書き方で結果が変わる理由

D列とE列の違いは、範囲の終わりに $ を付けるかどうかだけです。

「どれを残すか」を決めたいときは、範囲を伸ばす書き方を使います。 E列が空の行が「最初の1件」なので、E列でフィルターをかけて「2件目以降」の行だけを削除すれば、1件目が残ります。

もう1点、範囲の先頭行と条件のセルの行をそろえることが大切です。範囲が $A$2 から始まるなら、条件も同じ2行目の A2 にします。条件を A1（見出しのセル）にすると、1行ずれた値を数えることになり、印の位置が1行ずつずれます。

複数列をキーにする COUNTIFS

「備品名と保管場所の両方が同じ行」を重複としたい場合は、COUNTIFS に範囲と条件の組を並べます。

F2: =COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,B2)
G2: =IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)>1,"2件目以降","")

A列だけで見ると「モニター」は2件ありましたが、保管場所まで含めると 3F と 2F で別の行になり、個数は1です。キーにする列を増やすほど、重複と判定される行は減ります。

列をつないだ作業列を作って COUNTIF で数える方法でも、同じ結果になりました。

L2: =A2&"|"&B2
M2: =COUNTIF($L$2:$L$11,L2)

つなぐときは、間に区切り文字を入れます。区切りが無いと「AB」と「C」、「A」と「BC」がどちらも「ABC」になり、別の組み合わせが同じ値として数えられます。

重複のない一覧を作る UNIQUE と、以前のバージョン向けの式

UNIQUE（Excel 2021 以降）

UNIQUE は、範囲から重複を除いた一覧を返す関数です。Microsoft サポートのページでは、構文は次のとおりです。

=UNIQUE(array,[by_col],[exactly_once])

サンプル表での結果です（※）。

結果は式を入れたセルから下（複数列なら右にも）へ自動で広がります。この広がりを「スピル」と呼びます。広がる先のセルに値が入っていると #SPILL! になるので、下を空けておきます。

Excel 2019 以前でも動く一覧の式

UNIQUE が無いバージョンでは、INDEX・MATCH・COUNTIF を組み合わせます。H1 を見出し（空でも可）にして H2 に次の式を入れ、下へコピーします。動的配列に対応していないバージョンでは、Ctrl+Shift+Enter で確定する配列数式として入力します（Microsoft サポートの配列数式のページの案内）。

H2: =IFERROR(INDEX($A$2:$A$11,MATCH(0,COUNTIF($H$1:H1,$A$2:$A$11),0)),"")

仕組みは、「すでに上に書き出した値（$H$1:H1）の中に、元データの各値が何個あるか」を COUNTIF で数え、0個（まだ書き出していない）の最初の位置を MATCH で探す、というものです。

種類の数だけ知りたいとき

=SUMPRODUCT(1/COUNTIF(A2:A11,A2:A11))

サンプル表では 6 になりました。3件ある値は 1/3 が3回、2件ある値は 1/2 が2回足されるので、1種類につき合計が1になります。範囲に空のセルがあると 0 で割ることになり #DIV/0! になりました。空のセルが混じる範囲では、次の式にすると空のセルを除いた種類の数が出ます（「a、b、空、a」の4セルで 2）。

=SUMPRODUCT((A2:A11<>"")/COUNTIF(A2:A11,A2:A11&""))

重複している行だけ取り出す FILTER+COUNTIF

FILTER は、条件に合う行だけを取り出す関数です。Microsoft サポートのページでは構文は =FILTER(array, include, [if_empty]) で、条件に COUNTIF の結果を渡すと重複行だけを取り出せます。

=FILTER(A2:B11,COUNTIF(A2:A11,A2:A11)>1,"重複なし")

サンプル表での結果です（※）。

第3引数（if_empty）は、条件に合う行が1つも無いときに返す値です。Microsoft サポートの FILTER のページには、省略して該当が無いと #CALC! になると書かれています。重複が無いことも起こり得る表では、第3引数を入れておくと表示が落ち着きます。

重複している値を1つずつにまとめたいときは UNIQUE で包みます。

=UNIQUE(FILTER(A2:A11,COUNTIF(A2:A11,A2:A11)>1,"重複なし"))

結果は「ノートPC、モニター、マウス」の3行です（※）。

Excel 2019 以前でも動く取り出しの式

I2: =IFERROR(INDEX($A$2:$A$11,SMALL(IF(COUNTIF($A$2:$A$11,$A$2:$A$11)>1,ROW($A$2:$A$11)-1),ROW(A1))),"")

下へコピーすると、I2 から順に「ノートPC、モニター、ノートPC、マウス、モニター、ノートPC、マウス」と並び、8件目以降は空になりました。上の FILTER の備品名の列と同じ並びです。ROW($A$2:$A$11)-1 の「-1」は、データが2行目から始まるぶんのずれを引いています。こちらも動的配列に対応していないバージョンでは Ctrl+Shift+Enter で確定します。

2列を比べて重複を色付けする

申込者リスト（A列）と参加者リスト（B列）のように、2つの列を比べる場面です。

申込者 参加者
佐藤  鈴木
鈴木  田中
高橋  山本
田中  渡辺
伊藤  中村
渡辺  

条件付き書式に入れる数式

設定の手順は、Microsoft サポートの条件付き書式のページに沿うと次のとおりです。

1. 色を付けたい範囲を選択する
2. 「ホーム」タブ →「条件付き書式」→ 新しいルールを作り、ルールの種類で「数式を使用して、書式設定するセルを決定」を選ぶ
3. 上の数式を入力し、書式（塗りつぶしの色など）を決める

同じページには、数式は等号（=）で始めること、結果が TRUE か FALSE になる式にすることが書かれています。数式は「選択範囲の左上のセル」に対する式として書き、行番号に $ を付けないでおくと、2行目以降は自動で $A3、$A4 と読み替えられます。

数式の結果を表で確認

条件付き書式の数式をそのままセルに入れると、TRUE（色が付く）か FALSE かを確かめられます。

=ISNA(MATCH($A2,$B$2:$B$6,0)) も「A列にあってB列に無い」で TRUE になり、COUNTIF の式と同じ結果でした。別のシートと比べるときは、範囲を Sheet2!$A$2:$A$100 のようにシート名つきで書きます。

「重複の削除」・UNIQUE・条件付き書式の違いと選び方

「重複する値」の強調表示と「重複の削除」は、Excel に用意されている機能です。Microsoft サポートのページでは、強調表示は「ホーム」→「条件付き書式」→「セルの強調表示ルール」→「重複する値」、削除は「データ」→「重複の削除」と案内されています。

Microsoft サポートのページには、「重複の削除」で消したデータは完全に削除されるので、先に別のシートへコピーしておくこと、アウトラインや小計は先に解除することが書かれています。

条件から選ぶ

判定がずれる例と直し方

見た目は同じなのに重複と判定されない、あるいは別の値なのに重複と判定されるときは、次の4つを確かめます。

全角半角・前後の空白・大文字小文字

A2:A7 に、見た目が似た6つの値を入れて数えました。

（␣は半角の空白、□は全角の空白を表します）

• COUNTIF は大文字と小文字を区別しません。2行目と5行目は同じ値として数えられ、2になりました。区別したいときは EXACT を使った式にします。
• 全角と半角、空白の有無は別の値として数えられます。
• LEN で文字数を出すと、空白が混じっている行だけ 8 になるので見つけやすくなります。

表記をそろえる作業列

C2: =TRIM(ASC(A2))
G2: =TRIM(SUBSTITUTE(ASC(A2),"　"," "))

ASC は全角の英数字を半角に、TRIM は前後の半角の空白を取り除きます。TRIM だけでは全角の空白が残るため、7行目は C列では別の値のままでした。SUBSTITUTE で全角の空白を半角に置き換えてから TRIM をかけると、6行すべてが同じ値として数えられます。

15桁を超える数字

Microsoft サポートの「Excel の仕様と制限」では、数値の精度は 15 桁とされています。桁数の多い番号を数値として入れると、末尾が違う番号どうしが同じ値になります。17桁の数値を3つ入れて確かめました。

3つとも別の番号ですが、数値としては同じ値になり、3件とも重複と数えられました。桁数の多い番号は、数値ではなく文字列として持たせると、そのままの桁で保持されます。同じ3つを文字列のセルとして入れ直した結果です。

数字だけの文字列を比べるときは、文字列どうしを = で直接比べる SUMPRODUCT の式も用意しておくと、COUNTIF の結果と突き合わせて確かめられます。

確かめて分かったこと

• 範囲を $A$2:A2 と書くだけで「2件目以降」に切り替わる：式の形は同じで、$ の位置が違うだけでした。
• COUNTIF は大文字小文字を区別しない：「ABC-100」と「abc-100」は同じ値として 2 と数えられました。
• TRIM は全角の空白を取り除かない：全角の空白は SUBSTITUTE で置き換える必要がありました。
• 数値として入れた17桁の番号は、3つとも同じ値として数えられた：文字列として入れ直すと、それぞれ 1 になりました。
• A列だけで2件あった値が、列を足した COUNTIFS では1件になった：キーにする列の決め方で、重複の件数そのものが変わります。
• UNIQUE と、INDEX+MATCH+COUNTIF の式は同じ並びの一覧になった：どちらも最初に出てきた順に並びます。

よくある質問

Q. Excelで重複をチェックする関数はどれですか？ A. COUNTIF です。=COUNTIF($A$2:$A$11,A2) で同じ値の個数が出るので、2以上なら重複です。IF と組み合わせて =IF(COUNTIF($A$2:$A$11,A2)>1,"重複","") とすると印を付けられます。

Q. COUNTIF と UNIQUE の違いは何ですか？ A. COUNTIF は各行に「何個あるか」を返し、UNIQUE は重複を除いた一覧そのものを返します。行ごとに判定して絞り込みや削除をしたいなら COUNTIF、一覧がほしいなら UNIQUE を選びます。UNIQUE は Excel 2021 以降で使えます。

Q. 重複しているのに COUNTIF で 1 になるときは？ A. 全角と半角の違いか、前後の空白が混じっていることが多いです。=LEN(A2) で文字数を確かめ、=TRIM(SUBSTITUTE(ASC(A2),"　"," ")) の作業列で表記をそろえてから数えます。

Q. 2列を比較して、片方にしか無い値に色を付けるには？ A. 条件付き書式の数式に =COUNTIF($B$2:$B$6,$A2)=0 を入れます。A列の範囲を選んでから設定すると、B列に無い値のセルだけに色が付きます。

Q. 1件目を残して重複を削除するには？ A. =IF(COUNTIF($A$2:A2,A2)>1,"2件目以降","") の作業列を作り、フィルターで「2件目以降」の行だけを表示して削除します。「重複の削除」機能を使う場合は、消したデータが残らないので、先にシートをコピーしておきます。

まとめ

• 重複の判定は COUNTIF で個数を数え、2以上かどうかを見るのが基本です。
• 範囲を $A$2:$A$11 にすると重複すべて、$A$2:A2 にすると2件目以降だけに印が付きます。
• 複数列の組み合わせは COUNTIFS、2列の比較は相手の列を範囲にした COUNTIF で判定します。
• 一覧は UNIQUE、重複行の取り出しは FILTER+COUNTIF（Excel 2021 以降）。2019 以前は INDEX を使った式で同じ結果になります。
• 「重複の削除」は元データを書き換えるので、実行前にコピーを取ります。
• 全角半角・空白・15桁を超える数字は判定がずれる原因になるため、表記をそろえる作業列と文字列での入力で備えます。

参考・出典

• Microsoft サポート「UNIQUE 関数」https://support.microsoft.com/ja-jp/office/unique-%E9%96%A2%E6%95%B0-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e
• Microsoft サポート「FILTER function」https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759
• Microsoft サポート「Find and remove duplicates」https://support.microsoft.com/en-us/office/find-and-remove-duplicates-00e35bea-b46a-4d5d-b28e-66a552dc138d
• Microsoft サポート「条件付き書式を使用して Excel 内の情報を強調表示する」https://support.microsoft.com/ja-jp/excel/use-conditional-formatting-to-highlight-information-in-excel
• Microsoft サポート「Guidelines and examples of array formulas」https://support.microsoft.com/en-us/office/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7
• Microsoft サポート「Excel specifications and limits」https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。