エクセルで重複を抽出する方法（消さずに見つける）
※写真はイメージです
やり方はこれだけ
=COUNTIF($A$2:$A$100, A2)重複を消さずに、どれが重複しているかを見つけたいなら、COUNTIFで「何回出てくるか」を数えるのがいちばん確実です。2以上なら重複です。色で見たいだけなら「条件付き書式」→「重複する値」、重複している値だけを別の場所に一覧で出したいなら、Microsoft 365の FILTER＋UNIQUE が使えます。
この図の表をExcelファイルでダウンロード（問題と答えのシートつき）
やりたいことで、やり方を選ぶ
| やりたいこと | やり方 |
|---|---|
| 重複に色を付けて見る | 範囲を選んで「ホーム」→「条件付き書式」→「セルの強調表示ルール」→「重複する値」 |
| 重複に印を付けて、フィルターで絞る | =IF(COUNTIF($A$2:$A$100,A2)>1,"重複","") → 印の列でフィルター |
| 2回目以降だけに印（1回目は残す） | =IF(COUNTIF($A$2:A2,A2)>1,"2回目以降","") |
| 重複している値だけを一覧に（Microsoft 365） | =UNIQUE(FILTER(A2:A100,COUNTIF(A2:A100,A2:A100)>1)) |
| 1回しか出てこない値だけを一覧に（Microsoft 365） | =UNIQUE(A2:A100,,TRUE) |
3つ目の「2回目以降だけ」は、範囲の始まりだけ $ で固定して、終わりは固定しないのがポイントです。下にコピーすると範囲が1行ずつ伸びるので、「ここまでに何回出てきたか」を数えられます。消す前に、残すほう・消すほうを目で確かめたいときに便利です。
2つの列の組み合わせで重複を見る
名前だけだと同姓同名の別の人まで重複になってしまいます。名前と電話番号のように、2つがどちらも同じときだけ重複にしたいなら、COUNTIFSです。
| やりたいこと | 式 |
|---|---|
| 名前と電話が両方同じ行に印 | =IF(COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1,"重複","") |
見つけたあと、消すなら
消すところまでやるなら、「データ」→「重複の削除」です。消したものは元に戻しにくいので、先にこのページのやり方で印を付けて、中身を確かめてからのほうが安全だと思います。
重複を削除する
名簿（無料テンプレート）うまくいかないとき
1同じに見えるのに重複にならない
前後に空白がある、全角と半角が違う、などで別の値になっています。
直し方TRIMで空白を消すか、表記をそろえてから数えます。
2全部「重複」になる
COUNTIFの範囲に $ が付いておらず、コピーしたときに範囲がずれています。
直し方範囲を $A$2:$A$100 のように固定します。
3#CALC! になる（FILTER）
重複している値が1つもありません。
直し方FILTER(…,…,"なし") のように3つ目を入れておきます。
サンプルファイルで試す
黄色のセルに式を入れて、「答え」のシートと比べてください。
chofuku-chushutsu.xlsx をダウンロード（無料・登録なし）
よくある質問
エクセルで重複を抽出するには？
=COUNTIF($A$2:$A$100,A2) で何回出てくるかを数え、2以上なら重複です。色で見るなら「条件付き書式」→「重複する値」です。
重複している値だけを一覧にするには？
Microsoft 365なら =UNIQUE(FILTER(A2:A100,COUNTIF(A2:A100,A2:A100)>1)) で出せます。
2回目以降の重複だけに印を付けるには？
=IF(COUNTIF($A$2:A2,A2)>1,"2回目以降","") のように、範囲の始まりだけを固定します。
2つの列が両方同じときだけ重複にするには？
COUNTIFSで、2つの列を条件にして数えます。
一緒に覚えたい関数・操作
このページについて
操作の説明は Microsoft 365 の日本語版Excel（Windows）に合わせています。図はこのサイトで作ったもので、表の会社名・商品名・人名は架空です。運営者情報