日々の事務作業やデータ整理で、エクセルの重複チェックを関数で行いたいと思う場面は意外と多いですよね。標準機能の重複の削除を使うと元のデータが消えてしまいそうで怖いですし、条件付き書式で色付けをするだけでは、後から重複データだけをフィルターで絞り込んだり集計したりするのが少し不便かなと思います。
そこで今回は、元の表を一切傷つけずに安全に確認できるCOUNTIF関数を使った重複の数え方から、IF関数を組み合わせて重複の箇所にわかりやすいマークや印を表示させる手順まで、私自身が普段の作業で重宝しているやり方を詳しくまとめてみました。すべての重複を消してしまうのではなく、1つ目を残す形で2回目以降の不要なデータだけを特定するコツも紹介しています。

また、単一の列だけでなく、複数列や2列の複数条件を使って同姓同名の別人を誤って消さないように判定する作業列の作り方や、離れた2つの列の比較、別シートや別ブックとの突合をVLOOKUPやXLOOKUP、MATCH関数などと比較しながら進める方法にも触れています。さらに、UNIQUE関数やFILTER関数を使って重複を除く一意のユニークな一覧を別シートに抽出するテクニックや、条件付き書式に数式を入れて行全体を目立たせる方法、そして15桁や16桁以上の数字、255文字制限、不要なスペースや表記ゆれ、エラーが原因で関数がうまくできない・効かない・結果が0になるといったトラブルの解決策まで幅広くカバーしました。この記事を読めば、手元のリスト整理がぐっと楽になるはずですので、ぜひ参考にしてみてくださいね。
- COUNTIFやIF関数で元データを残したまま重複回数や目印を表示する基本手順
- 最初の1件目を保護して2回目以降の重複や複数列の一致だけを正確に判定する応用式
- 2つのリストや別シート・別ブック間でエラーを出さずにデータを比較・突合する方法
- UNIQUEやFILTER関数での一覧抽出と15桁制限や空白で関数が効かない時の対処法
エクセルで重複チェックを行う関数の基本と複数列判定
まずは、1つのリストの中で同じデータがいくつあるのかを調べたり、複数の項目を組み合わせてチェックしたりする基本的な関数の使い方から見ていきましょう。作業用の列を1つ作って数式を入れるだけで、元の大切なデータを消してしまうリスクをゼロにできるので、とても安心感がありますよ。
COUNTIFによる重複の数え方と基本の式
エクセルで同じ列の中に重複しているデータがあるかどうかを調べる際、すべての基本になるのがCOUNTIF(カウントイフ)関数です。この関数を使うと、「指定した範囲の中に、特定のデータが何個入っているか」を数字でパッと数えてくれます。
たとえば、A列(A2セルからA100セルまで)に顧客名や商品コードが並んでいて、隣のB列を作業列(チェック用の列)として使う場合を考えてみましょう。B2セルに以下の数式を入力して、下に向かってオートフィルでコピーします。
基本の数式(範囲を指定する場合):=COUNTIF($A$2:$A$100, A2)
基本の数式(列全体を指定する場合):=COUNTIF(A:A, A2)
この数式を入れると、リストの中にそのデータが1つしかなければ「1」と表示され、同じデータが2つあれば「2」、3つあれば「3」というように出現回数がそのまま数字で出力されます。つまり、結果が「1」なら重複なしの一意なデータ、「2」以上なら重複しているデータだと一目で判断できるわけですね。
範囲指定ではF4キーでの絶対参照が必須
ここで一番気をつけたいポイントが、1つ目の引数である検索範囲に「$(ドルマーク)」を付けて絶対参照($A$2:$A$100)にしておくことです。範囲を選択した直後にキーボードのF4キーを1回押すと、自動的に「$」が付きます。
もしこの固定を忘れて普通の相対参照(A2:A100)のまま数式を下にコピーしてしまうと、3行目ではA3:A101、4行目ではA4:A102というように、調べる範囲まで一緒に1行ずつ下へズレていってしまいます。そうすると、自分の行より上にあるデータがカウントされなくなり、正確な重複チェックができなくなってしまうんですね。エクセルの計算式でセルを固定する仕組みについてもう少し詳しくおさらいしておきたい方は、エクセルの計算式でセルを固定してズレを防ぐ基本と応用もあわせて読んでおくと、なぜ「$」が必要なのかがスッキリ理解できるかなと思います。
なお、2つ目の式のように=COUNTIF(A:A, A2)と列全体を指定すれば、「$」を付けなくても範囲がズレず、後から表の下にデータを追加しても自動で判定してくれるのでとても手軽です。ただ、表の下の方にメモ書きや合計欄などがあると、それも一緒に数えてしまって誤判定になることがあるので、シートの作り方に合わせて使い分けるのがおすすめです。
IF関数の組み合わせで重複にマークや印を表示
COUNTIF関数だけでも重複の回数はわかりますが、何百行もあるような大きな表だと、「1」「2」「1」「1」「3」と数字が並んでいるだけでは、パッと見でどこが重複しているのか少し探しにくいですよね。
そんなときは、IF(イフ)関数と組み合わせて、重複している行にだけ「重複」や「★」「!」といったわかりやすい文字(ラベル)を表示させ、重複していない行は空欄(空白)にする方法が便利です。
重複している行にだけマークを表示する数式:=IF(COUNTIF($A$2:$A$100, A2)>1, "重複", "")
この数式は、「もしA2セルのデータの出現回数が1より大きければ(つまり2回以上なら)『重複』と表示し、そうでなければ空白("")にする」という命令になっています。「重複」の部分はお好みで「★」や「要確認」などに書き換えても大丈夫です。
こうして重複していない大部分の行を空欄にしておけば、見た目がスッキリしてノイズがなくなりますし、あとからオートフィルター機能を使って「重複」と表示された行だけを絞り込むのも一瞬で終わります。もしIF関数を書くのが少し面倒だなと感じる場合は、=COUNTIF(A:A, A2)>1とだけ入力する裏技もあります。これだと重複している行には「TRUE」、重複していない行には「FALSE」が直接表示されるので、数式を短く済ませたいときには便利ですよ。
2回目以降の重複のみを判定し1つ目を残す方法
実務で名簿やリストの整理をしていて一番やってしまいがちな失敗が、先ほどの=IF(COUNTIF($A$2:$A$100, A2)>1, "重複", "")で「重複」と出た行をフィルターで絞り込み、そのまま全部選択して削除してしまうことです。
これをやってしまうと、1回目に出てきた元のデータも、2回目以降に出てきたダブりのデータもすべて同じ「重複」というマークが付いているため、該当する人のデータが表から完全に消滅してしまうんですね。本来の目的は「最初の1件目は正しいデータとして残し、2回目以降の余分なデータだけを特定して消すこと」かなと思います。
そこで大活躍するのが、検索範囲の「最初のセルだけを固定し、後ろのセルは固定しない」という複合参照(拡張範囲)を使ったテクニックです。
1件目を残し、2回目以降の重複だけにマークを付ける数式:=IF(COUNTIF($A$2:A2, A2)>1, "重複", "")
先ほどの数式と見比べると、範囲の指定が$A$2:$A$100ではなく、$A$2:A2になっているのがわかりますでしょうか。このようにスタート地点の$A$2にだけ「$」を付けておくと、数式を下にコピーしたときに、3行目では$A$2:A3、4行目では$A$2:A4、5行目では$A$2:A5というように、「常に先頭の行から自分自身の行まで」へと調べる範囲が1行ずつ伸びていくようになります。
すると、上から順番にチェックしていったとき、初めてそのデータが登場した行ではまだ自分自身の1件しか範囲内にないのでカウントが「1」となり、空白が表示されます。そして、さらに下の行で同じデータが2回目、3回目と登場したときに初めてカウントが「2」以上になり、「重複」というマークが付く仕組みです。これなら、フィルターで「重複」だけを絞り込んで削除しても、最初の1件目はしっかり表に残るのでとても安全ですね。
豆知識:一番下の最新データを残して、上の古いデータを消したい場合は?
「下に追加された行ほど情報が新しいから、最後の1件だけを残して上にある古い重複行にマークを付けたい」というケースもありますよね。その場合は、逆に「自分の行から一番下の行まで」を範囲にする=IF(COUNTIF(A2:$A$100, A2)>1, "重複", "")という数式に変えるだけで、一番下の最新行だけを空白(対象外)にして保護することができますよ。
複数列や2列の複数条件で同姓同名を見分ける式
顧客リストなどで「氏名」の1列だけを見て重複チェックを行うと、たまたま同じ漢字・同じ読み方の「同姓同名の別人」まで重複として扱ってしまう危険があります。氏名が同じでも、電話番号やメールアドレス、生年月日などが違えば別人として残さなければいけませんよね。
このように「氏名+電話番号」や「商品名+型番」など、2列以上の複数条件がすべて一致したときだけ重複と判定したい場合は、主に3つのやり方があります。
| 判定のやり方 | 数式の入力例(2行目に入力する場合) | 特徴とメリット |
|---|---|---|
| COUNTIFS関数を使う | 全件にマーク:=IF(COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2)>1, "重複", "")2回目以降のみマーク: =IF(COUNTIFS($A$2:A2, A2, $B$2:B2, B2)>1, "2件目以降", "") | 文字をつなげる作業列を別に作らなくても、1つの式で複数列の条件をまとめてチェックできます。 |
| 作業列で文字を結合(&)+COUNTIF | 結合用の列(D2):=A2&"_"&B2判定用の列(E2): =IF(COUNTIF($D$2:$D$100, D2)>1, "重複", "") | 3列や4列など項目が増えても数式が複雑にならず、目で見て何が結合されているか確認しやすいです。 |
| テーブル機能(構造化参照)を使う | =COUNTIFS([氏名], [@氏名], [電話番号], [@電話番号]) | 表をテーブル(Ctrl + T)にしておけば、「$」を付ける手間がなく、行を追加しても自動で数式が反映されます。 |
1. COUNTIFS関数で直接複数列をチェックする
複数の条件を同時に満たすものを数えるには、末尾に「S」が付いたCOUNTIFS(カウントイフス)関数を使います。=COUNTIFS(範囲1, 条件1, 範囲2, 条件2, ...)という形で、A列(氏名)とB列(電話番号)の両方が一致する行をカウントします。こちらも範囲を$A$2:A2のように複合参照にすれば、複数列が一致したデータの「2回目以降」だけに印を付けることができます。
注意点として、COUNTIFS関数では指定するすべての範囲の行数(長さ)をピッタリ揃える必要があります。たとえばA列の範囲を$A$2:$A$100にしたのに、B列の範囲をうっかり$B$2:$B$99のように1行でもズラしてしまうと、その場で#VALUE!エラーになってしまうので気をつけてくださいね。
2. 作業列で「&」を使って文字をつなげる方法
もしチェックしたい項目が3列、4列とたくさんある場合は、いったん作業列を作って「&(アンパサンド)」で文字を1つにガッチャンコしてから、普通のCOUNTIF関数で数える方がシンプルでわかりやすいかなと思います。
このとき、単に=A2&B2とつなげるのではなく、=A2&"_"&B2のように間にアンダースコアなどの区切り文字を挟むのがちょっとしたコツです。もし区切り文字を入れないと、たとえばA列が「12」でB列が「3」のデータと、A列が「1」でB列が「23」のデータが、つなげた後にどちらも「123」になってしまい、違うデータなのに重複だと誤判定されてしまうことがあるからです。
3. テーブル機能を使う場合の事前チェック
表を「Ctrl + T」でテーブルに変換している場合は、[列名]を使った数式でスッキリ書けますが、テーブル化する前に「1行目の見出し名に同じ名前がダブっていないか」だけ確認しておきましょう。テーブルは同じ名前の見出しを許してくれないため、重複があると勝手に「項目2」のように名前を書き換えられてしまい、後から数式を組むときに混乱する原因になります。
2つの列の比較や別シート間でデータを突合する
ここまで見てきたのは「1つのリストの中でダブりがないか」を調べる方法でしたが、実務では「先月の名簿(A列)と今月の名簿(B列)を見比べて、共通する人や新しく増えた人を探したい」「別シートのマスター一覧に載っているか照合したい」といった2つのリストの比較・突合もよくありますよね。
別の列や別シートと比較するときに一番気をつけたいのは、判定の基準が「>1」ではなく「>0(または>=1)」に変わるという点です。同じリスト内を調べるときは自分自身が必ず1回カウントされるので2回以上(>1)が重複でしたが、よそのリストを調べるときは「相手のリストに1件でもあれば重複(一致)」になるからですね。
A列のデータがB列にもあるか調べる式(片方向チェック):=IF(COUNTIF($B$2:$B$100, A2)>0, "重複あり", "なし")
B列のデータがA列にもあるか調べる式(逆方向チェック):=IF(COUNTIF($A$2:$A$100, B2)>0, "重複あり", "なし")
別シート(Sheet2)のリストと照合する式:=IF(COUNTIF(Sheet2!$A$2:$A$100, A2)>0, "重複", "")
2つのリストを突き合わせるときは、A列の値がB列にあるか調べるだけでなく、逆にB列の値がA列にあるかも調べる「両方向チェック」を行うのがおすすめです。片方からしか見ないと、「B列にだけ新しく追加された未登録データ」を見落としてしまうことがあるためです。また、3つの列(A列・B列・C列)すべてに共通するデータを探したい場合は、=IF(AND(COUNTIF(A:A, C2), COUNTIF(B:B, C2)), "共通", "")のようにAND関数で 묶ってあげると見つけられますし、今のシートと別シートの出現回数を合算したいなら=COUNTIF($A$2:$A$50, A2)+COUNTIF(Sheet2!$A$2:$A$50, A2)のように足し算するのもアリですね。
COUNTIFとVLOOKUP・XLOOKUP・MATCHはどう使い分ける?
2つの表を照合するとき、「VLOOKUP関数やXLOOKUP関数、MATCH関数を使うのと何が違うの?」と疑問に思う方もいるかもしれません。それぞれの特徴を表にまとめてみました。
| 関数名 | わかること(返ってくる値) | 重複・突合チェックでの向き・不向き |
|---|---|---|
| COUNTIF / COUNTIFS | 一致するデータの個数(0, 1, 2…) | 一番手軽でおすすめ。相手リストになくても「0」になるだけでエラー(#N/A)が出ず、相手側で何件ダブっているかも同時にわかります。 |
| VLOOKUP | 一致した行の指定した列のデータ | 全バージョンで使えます。「存在チェックと同時に、相手リストにある金額や担当者名も引っ張ってきたい」というときに便利です。 |
| XLOOKUP | 一致した行のデータ(左右の列どちらも可) | Excel 2021やMicrosoft 365以降で使えます。VLOOKUPより壊れにくく、見つからないときの文字(「未登録」など)も関数内で直接指定できます。 |
| MATCH | 相手リストの何行目にあるか(位置の数字) | 「相手リストのどこにあるか」を特定したいときに向いています。存在しないと#N/Aエラーになるので、ISNUMBER関数などと組み合わせます。 |
このように、「ただ相手のリストに存在するかどうか(重複しているか)を知りたいだけ」なら、エラー処理の手間がいらないCOUNTIF関数が一番シンプルで使いやすいかなと思います。
別ブック参照でエラーが出る原因と回避する数式
別シートではなく、「別のエクセルファイル(外部ブック)」にあるリストとCOUNTIF関数で重複チェックをしたときに、とても厄介なトラブルが起きることがあります。
作業中は相手のファイルも開いているので普通に数字が表示されるのですが、参照元の別ファイルを閉じて、自分のファイルを開き直したり再計算(EnterやF9キー)したりした瞬間に、すべてのセルが一斉に「#VALUE!」というエラーに変わってしまう現象です。
なぜ別ファイルを閉じるとエラーになるの?
これは数式の書き間違いではなく、エクセルの仕様によるものです。実はCOUNTIFやCOUNTIFS(他にもSUMIFなど)は、「閉じている別のブックのセル範囲を直接読み取ることができない」という制限を持っています。
毎回相手のファイルを開きながら作業するのは大変ですので、ファイルを閉じたままでもエラーを出さずに別ブックと重複チェックをするには、以下の2つの方法のどちらかを使うのが確実です。
解決策1:SUMPRODUCT(サムプロダクト)関数に書き換える
単一条件の場合:=SUMPRODUCT(([Book1.xlsx]Sheet1!$A$2:$A$100=A2)*1)
複数条件(COUNTIFSの代わり)の場合:=SUMPRODUCT(([Book1.xlsx]Sheet1!$A$2:$A$100=A2)*([Book1.xlsx]Sheet1!$B$2:$B$100=B2))
解決策2:自分のファイル内に作業用シートを作ってリンクコピーする
自分のファイルに新しいシートを増やし、そこに=[Book1.xlsx]Sheet1!A2という普通の「=」だけの参照式を入れて別ファイルの表を映し出してから、そのシートに対してCOUNTIF関数を使います。
1つ目のSUMPRODUCT関数を使う方法は、閉じた外部ブックの参照に対応しているため、相手のファイルを開いていなくてもエラーにならずに一致する件数を数えてくれます。(範囲=条件)*1と書くことで、一致したら「1」、違ったら「0」に変換して合計してくれる仕組みです。別ファイルをまたいで管理台帳を作るときには本当に助かる書き方なので、ぜひ覚えておいてくださいね。
エクセルの重複チェック関数によるデータ抽出と注意点

ここからは、重複チェックをしたあとに「重複を取り除いたキレイな一覧表を別の場所に作りたい」「重複している問題のある行だけを抜き出したい」といったデータ抽出のテクニックや、条件付き書式での色付け、そして関数がうまく動かないときの原因と対策について詳しく見ていきましょう。
UNIQUE関数で重複を除く一意のリスト抽出
日々の売上データや申込履歴の中から、ダブりを省いて「どんな商品があるか」「誰が申し込んだか」という重複のない一意(ユニーク)なマスター一覧を作りたいときは、Excel 2021やExcel 2024、Microsoft 365以降で使えるUNIQUE(ユニーク)関数が圧倒的に便利です。
この関数は、リストを表示させたい先頭のセルに1つ数式を入れるだけで、下のセルに向かって結果が自動的にザーッと展開(スピル)されます。元の表にデータが追加されれば、抽出されたリストもリアルタイムで自動更新されるので、毎回作り直す手間がかかりません。
| やりたいこと | 入力する数式 | 仕組みとポイント |
|---|---|---|
| 重複を除いて1つずつ一覧にする | =UNIQUE(A2:A100) | 何回出てきても最初の1つだけを残し、重複を除いたリストを自動で作ります(複数列A2:B100の指定もOK)。 |
| 1回しか出てこないデータだけ抜く | =UNIQUE(A2:A100, , TRUE) | 3つ目の引数にTRUEを入れると、2回以上ダブっていたデータをすべて除外し、最初から1件しかなかったデータだけを抽出します。 |
| 抽出と同時にあいうえお順に並べる | =SORT(UNIQUE(A2:A100)) | SORT関数で包むことで、重複を省きながら昇順(五十音順・数字順)に自動で並べ替えてくれます。 |
| 複数列のデータを縦1列にまとめる | =UNIQUE(TOCOL(B3:D10)) | TOCOL関数で複数列に散らばったデータを縦1列に変換してから、重複を取り除きます。 |
| 抽出した一覧の横に出現回数を出す | F2セル:=UNIQUE(A2:A100)G2セル: =COUNTIF(A2:A100, F2#) | F2セルの後ろに「#」を付けると、UNIQUE関数で広がった範囲全体の出現回数を一発でカウントできます。 |
UNIQUE関数を使うときの3つの注意点
とても便利なUNIQUE関数ですが、実際に使ってみると「あれ?」と戸惑うポイントが3つあります。
- 空白セルがあると「0」が表示される:選んだ範囲の中に空っぽのセルが混ざっていると、抽出結果に「0」が出てきてしまいます。これを防ぐには、数式の最後に
&""を付けて=UNIQUE(A2:A100)&""と書けば、0ではなくキレイな空白として表示されます。 - 日付がただの数字(シリアル値)になる:日付の列をUNIQUE関数で抜き出すと「45000」のような数字で表示されるため、出力先のセルの書式設定を「日付」に変更してあげてください。
- 大文字・小文字や全角・半角は同じものとみなされる:たとえば「ABC」と「abc」、全角の「タナカ」と半角の「タナカ」は同じデータとして扱われ、表の上にある方の表記だけが1つ残る仕様になっています。
補足:重複を除いた「データの種類の数」だけを数えたいときは?
一覧表を作るのではなく、「結局、全部で何種類のデータがあるのか」という個数だけを調べたい場合、古いエクセルも含めたすべてのバージョンで使える定番の数式が=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))です。2回出てくるデータは「1/2+1/2=1」、3回出てくるデータは「1/3+1/3+1/3=1」というように割り算の合計で計算してくれる、とても賢い式ですよ。
FILTER関数で重複行のみを別シートに抽出
重複を除いた一覧ではなく、逆に「重複してしまっている問題のある行だけを別シートや別の表にまとめて抜き出したい」という場面もありますよね。そんなときは、FILTER(フィルター)関数の中にCOUNTIF関数を組み込むことで、作業列を一切作らずに重複データだけを自動抽出できます。
重複しているすべての行を別表に抜き出す数式:=FILTER(A2:B100, COUNTIF(A2:A100, A2:A100)>1, "重複なし")
重複を起こしている項目名だけを1つずつにまとめて抜き出す数式:=UNIQUE(FILTER(A2:A100, COUNTIF(A2:A100, A2:A100)>1, "重複なし"))
1つ目の数式は、「A2:A100の中で2回以上登場する(>1)行だけを、A2:B100の表から丸ごと抜き出す(もし1件もなければ『重複なし』と表示する)」という動きをします。さらに2つ目の数式のように、その外側を先ほどのUNIQUE関数で包んであげれば、「どのデータが重複トラブルを起こしているのか」という原因リストをダブりなしでスッキリ一覧化できるので、データの点検作業がすごく捗りますよ。
Excel 2019以前の環境で別シートに抽出したい場合
もしお使いのエクセルがExcel 2019以前でUNIQUE関数やFILTER関数が使えない場合は、元の表の横に=IF(COUNTIF($A$2:A2, A2)>1, "重複", "一意")という判定列を作ったうえで、[データ]タブにある「詳細設定(フィルターの詳細設定)」を使うと別シートへ抜き出せます。
このときの最大のコツは、元のシートではなく「データを抜き出したい別シート側」を開いて、抽出先の見出しセルをクリックした状態から[詳細設定]ボタンを押すことです。「指定した範囲」にチェックを入れ、リスト範囲に元の表、検索条件範囲に別シートに作った条件(「重複」や「一意」と書いたセル)、抽出範囲に別シートの見出しを指定すれば、関数がなくてもキレイに転記できますよ。
値のみ貼り付けと重複の削除で安全に整理する
COUNTIF関数で重複行を特定したあと、その行を並べ替えたり削除したりして元の表をキレイに整理したいとき、関数の数式が入ったままの状態で並べ替え(ソート)や行削除をしてしまうと思わぬトラブルが起きることがあります。
数式が生きたまま並べ替えをすると参照先が変わってしまって順番がグチャグチャになったり、重複の1行目を消した瞬間に、残った行のCOUNTIF関数が再計算されて「重複」の文字が消えてしまったりするんですね。そのため、データを動かしたり消したりする前には、以下の手順で「数式をただの値(文字や数字)に確定させる作業」を挟むのが安全です。
関数でチェックした後に安全に重複行を整理・削除する手順:
- 作業列に
=IF(COUNTIF($A$2:A2, A2)>1, "重複", "")を入力し、2回目以降の重複行にマークを付ける。 - 作業列全体を選択してコピー(Ctrl + C)し、そのまま同じ場所で右クリックして「貼り付けのオプション」から「値(123のアイコン)」を選んで貼り付ける。
- 数式バーを見て、中身が数式ではなく「重複」という文字になっていることを確認する。
- 表にフィルター(Ctrl + Shift + L)をかけ、作業列の「▼」から「重複」だけにチェックを入れて絞り込む。
- 表示された行をまとめて選択し、右クリックから「行の削除」を実行する。
この「値のみ貼り付け」をワンクッション挟むだけで、並べ替えをしても削除をしても結果が勝手に変わることがなくなり、ミスを確実に防げます。
標準機能「重複の削除(Alt + A + M)」を使うときの注意点
[データ]タブにある「重複の削除」ボタンを使うと、関数を使わずに一瞬で2件目以降の重複行を消すことができます。ただ、この機能は元のデータを直接書き換えて消してしまうため、後から「やっぱり戻したい」と思っても復元が大変です。また、常に「上にある行を残して、下にある行を消す」という動きをするため、一番下に最新データがある表でそのまま実行すると最新情報が消えてしまいます。実行する前には必ずシートやファイルのバックアップを取っておくようにしてくださいね。
条件付き書式と数式の連携で行全体に色付けする
「作業列を増やして表のレイアウトを変えたくないけれど、重複している場所は色で目立たせたい」というときは、条件付き書式の出番です。[ホーム]タブ>[条件付き書式]>[セルの強調表示ルール]>[重複する値]を選べば、数クリックで重複セルに色を塗ることができます。基本的な設定手順を一から確認したい場合は、Excelでセルの値や文字列が重複する場合、特定の書式を適用するの記事で画像付きの手順をチェックしてみてください。
ただ、この標準の「重複する値」ルールだけでは、「1件目のセルも含めて全部塗られてしまう」「重複したセル1個しか色が付かず、行全体を塗れない」「複数列の組み合わせで判定できない」という弱点があります。そこで、[条件付き書式]>[新しいルール]>[数式を使用して、書式設定するセルを決定]を選び、これまで紹介したCOUNTIFやCOUNTIFSの数式を書き込むと、思い通りの色付けができるようになります。
| 色付けしたいパターン | 最初に選択する範囲の例 | 条件付き書式に入力する数式 |
|---|---|---|
| 2回目以降の重複セルだけ色を塗る | A2:A100 | =COUNTIF($A$2:A2, A2)>1 |
| B列が重複していたら「行全体」に色を塗る | A2:E100(表全体を選択) | =COUNTIF($B$2:$B$100, $B2)>1 |
| 2つの列(B列とC列)が両方一致した時だけ塗る | A2:E100(またはB2:C100) | =COUNTIFS($B$2:$B$100, $B2, $C$2:$C$100, $C2)>1 |
| 別のリスト(B列)にあるA列のセルを塗る | A2:A100 | =COUNTIF($B:$B, A2)>0 |
| 横方向(同じ行のA列〜N列)の重複を塗る | A2:N100 | =COUNTIF($A2:$N2, A2)>1 |
ここでの一番のポイントは、B列が重複したときに表の左から右まで「行全体」を塗りつぶす数式で、2つ目の引数を$B2(列のBの前だけに「$」を付ける)にしている点です。こうすることで、C列やD列の色を塗るかどうか判定するときも、エクセルが常に「B列の値($B2)」を見に行ってくれるため、見事に1行まるごと色が付くんですね。
なお、最初から「新しいルール」で数式を書くのが少し難しく感じるときは、いったん普通のルールを適当に作ってから、[条件付き書式]>[ルールの管理]>[ルールの編集]を開いて数式ルールに切り替えると、適用範囲のズレが起きにくいのでおすすめですよ。
15桁制限やスペースで関数が効かない時の対策
「数式は絶対に合っているはずなのに、なぜか重複が検出されずに0や1になってしまう」「逆に、全然違う番号なのに全部『重複』と出てしまう!」というトラブルは、実務で本当によく遭遇します。これにはエクセルの隠れた仕様や、目に見えないデータのゴミが関係しています。原因と解決策を6つのパターンに分けて整理しました。
1. 16桁以上の数字(IDやカード番号など)がすべて重複になる「15桁問題」
会員番号や追跡番号、製品の識別コードなど、16桁以上の長い数字に対してCOUNTIFやCOUNTIFS関数を使うと、最初の15桁目までが同じであれば、16桁目以降の数字が違っていてもすべて「同じ数字(重複)」だと誤判定されてしまいます。
これは、エクセルが一度に計算できる数字の桁数が最大15桁までという仕様(精度制限)になっており、たとえセルの表示形式を「文字列」にしていても、COUNTIF関数は内部で勝手に「数字の文字列」を「数値」に変換して16桁目以降をすべて「0」に切り捨ててしまうために起こります。
16桁以上の数字や255文字を超える長文を正確に重複チェックする数式:=SUMPRODUCT((EXACT($A$2:$A$100, A2))*1)
または=SUMPRODUCT(($A$2:$A$100=A2)*1)
このようにCOUNTIF関数を使わず、SUMPRODUCT関数とEXACT(イグザクト)関数(または「=」)を組み合わせれば、文字列を勝手に数値へ変換することなく、末尾の20桁目以降まで1文字ずつ厳密に比べてくれるので、長いIDでもピタッと正確に判定できます。
2. 255文字を超える長いテキストで「#VALUE!」エラーが出る
長いURLや備考欄の文章、あるいは複数列を「&」でたくさんつなげた作業列など、セルの文字数が255文字を超えるとCOUNTIF関数は#VALUE!エラーを出して止まってしまう仕様があります。この場合も、すぐ上で紹介した=SUMPRODUCT((EXACT($A$2:$A$100, A2))*1)の数式に変えることで、文字数制限を気にせずチェックできるようになります。
3. 大文字・小文字の区別や、全角・半角のズレ
COUNTIF関数は、アルファベットの大文字と小文字(「ABC」と「abc」)を区別せず、同じものとして数えてしまいます。もしパスワードやシステムIDなどで大文字・小文字を厳密に別のものとして区別したい場合も、大文字・小文字を見分けるEXACT関数を使った=SUMPRODUCT((EXACT($A$2:$A$100, A2))*1)が活躍します。
逆に、全角と半角(「1」と「1」、「A」と「A」など)については、COUNTIF関数は「まったく別の文字」として区別するため、手入力の表だと表記ゆれで重複が見逃される原因になります。JIS関数(全角に統一)やASC関数(半角に統一)を使って、あらかじめ文字の形式を揃えておくと確実ですね。
4. 見えないスペースや改行コードが混ざっている
システムから出力したCSVやWebページからコピペした表で、「見た目はまったく同じ『田中太郎』なのに重複にならない」という場合、文字の後ろに目に見えない空白(スペース)や改行コードが紛れ込んでいるケースがほとんどです。まずは怪しい2つのセルの文字数を=LEN(A2)で比べてみて、数字が違えばゴミが入っているとわかります。その場合は、作業列で以下のクレンジング関数を使ってキレイに掃除してからCOUNTIFをかけましょう。
- 前後の余分なスペースを消す:
=TRIM(A2) - 見えない改行や制御文字を消す:
=CLEAN(A2)(=TRIM(CLEAN(A2))と両方組み合わせるのがおすすめです) - 苗字と名前の間のスペースもすべて消す:TRIM関数は単語の間のスペースを1つ残す仕様なので、「田中 太郎」と「田中太郎」を同じにしたいときは、SUBSTITUTE関数を使って
=SUBSTITUTE(SUBSTITUTE(A2, " ", ""), " ", "")のように全角・半角スペースを空文字("")に置き換えて完全に消してしまうのが一番確実です。
5. 「数値」と「文字列の数字」が混在している・式の書き方ミス
同じ「100」というデータでも、片方が数値、もう片方が文字列として入っていると、他の関数と組み合わせたときにうまく一致しないことがあります。=VALUE(A2)で数値に揃えるか、=TEXT(A2, "0")で文字列に揃えておくと安心です。また、COUNTIFの検索条件にうっかり"=A2"のように余計なイコールやダブルクォーテーションを付けてしまうと動かなくなるので、単純にA2とセル番地だけを指定してくださいね。
6. 部分一致(「株式会社」の有無など)で重複候補を探したい
「『〇〇商事』と『株式会社〇〇商事』のように、一部でも同じ文字が含まれていたら重複候補としてチェックしたい」という場合は、前後にワイルドカードの「*(アスタリスク)」をつなげて=COUNTIF($A$2:$A$100, "*"&A2&"*")と書くことで、部分一致でのカウントが可能になります。詳しいワイルドカードの使い方は、エクセル複数セル内で特定の文字をカウントする全手順でも解説されていますので、表記ゆれが多いリストを扱う方はぜひチェックしてみてください。
エクセルの重複チェック関数の用途別使い分けまとめ
ここまで、エクセルで重複チェックを行うためのさまざまな関数やテクニックをお伝えしてきました。やりたいことや手元のデータの状態によってベストな数式が変わるので、最後に目的別の早見表として全体を振り返っておきましょう。
| やりたいこと・目的 | 使う関数・数式のまとめ |
|---|---|
| 1列の中で重複すべてに印を付ける | =IF(COUNTIF($A$2:$A$100, A2)>1, "重複", "") |
| 最初の1件目を残し、2回目以降だけ印を付ける | =IF(COUNTIF($A$2:A2, A2)>1, "重複", "") |
| 複数列(氏名+電話番号など)の一致を調べる | =IF(COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2)>1, "重複", "") |
| 別の列や別シートに同じデータがあるか照合する | =IF(COUNTIF(Sheet2!$A$2:$A$100, A2)>0, "重複あり", "なし") |
| 閉じた別ファイルのリストとエラーなしで照合する | =SUMPRODUCT(([Book1.xlsx]Sheet1!$A$2:$A$100=A2)*1) |
| 重複を取り除いた一意のリストを自動で作る | =UNIQUE(A2:A100)(※Excel 2021 / 365以降) |
| 重複している行だけを別の場所に抜き出す | =FILTER(A2:B100, COUNTIF(A2:A100, A2:A100)>1, "なし") |
| 16桁以上の数字や大文字・小文字を厳密に判定する | =SUMPRODUCT((EXACT($A$2:$A$100, A2))*1) |
エクセルで重複チェックをする関数は、まず基本となるCOUNTIF関数と「$」を使った範囲固定の仕組みさえ押さえてしまえば、そこから複数列の判定や条件付き書式での色付け、別シートとの突合までどんどん応用を効かせることができます。いきなり「重複の削除」機能を使って大切なデータを消してしまう前に、まずは作業列にこれらの関数を入れて、どのデータがダブっているのかを目で見て確認する習慣をつけると、作業のミスがグッと減って安心感が増すかなと思います。
データ処理におけるご注意と免責事項
本記事で紹介している関数の仕様や文字数・桁数の制限(15桁精度や255文字制限など)は、一般的なエクセルの動作基準に基づく目安となります。お使いのExcelのバージョンやアップデート状況、社内システムの環境によって使える関数や挙動が異なる場合がありますので、正確な情報はMicrosoftの公式サイトをご確認ください。また、業務上の重要な顧客名簿や財務データなどを整理・削除する際は、万が一のデータ消失を防ぐために必ず事前にバックアップを取得し、最終的な判断はシステム管理者や専門家にご相談のうえ自己責任にて作業を進めていただきますようお願いいたします。
手元のリストに合わせて、まずはコピペで使える基本の数式から一つずつ試してみてくださいね。毎日のデータ整理が少しでもスムーズで快適になれば嬉しいです!
