重複データの扱いには、「見つける」「抽出する」「削除する」「そもそも入れない」の4つの目的があります。どれをやりたいかで手段が変わります。
このページでは5つの方法を目的別に整理します。
目的別の早見表
| やりたいこと | 使うもの |
|---|---|
| 重複を除いた一覧を作る | UNIQUE |
| 重複している行に印を付ける | COUNTIF |
| 重複を色で目立たせる | 条件付き書式 |
| 元データから重複を消す | メニューの「重複を削除」 |
| そもそも重複を入れない | データの入力規則 |
最後の「入れない」が最も確実です。 後から掃除するより、入力時に弾くほうが手間もリスクも小さくなります。
1. UNIQUE ── 重複を除いた一覧を作る
=UNIQUE(A2:A100)元データには一切手を触れず、重複を除いた一覧を別の場所に表示します。
範囲を大きく取ると空欄も1つの値として含まれるので、FILTER で除きます。
=UNIQUE(FILTER(A2:A1000, A2:A1000<>""))この形が実用上の基本形です。
並べ替えるなら SORT を重ねます。
=SORT(UNIQUE(FILTER(A2:A1000, A2:A1000<>"")))複数列での重複判定
範囲を複数列にすると、行全体が同じものだけを重複と見なします。
=UNIQUE(A2:C1000)「支店 × 商品」の組み合わせ一覧が作れます。
件数を数える
=COUNTA(UNIQUE(FILTER(A2:A1000, A2:A1000<>"")))2. COUNTIF ── 重複している行に印を付ける
すべての重複行に印
=IF(COUNTIF(A:A, A2) > 1, "重複", "")A列全体で自分と同じ値を数え、2件以上あれば重複と判定します。下方向にコピーするだけで使えます。
この書き方だと、重複しているものがすべて「重複」になります。
2件目以降だけに印
1件目は残して2件目以降を消したい場合、範囲を「自分の行まで」に限定します。
=IF(COUNTIF($A$2:A2, A2) > 1, "削除対象", "")範囲の開始だけを絶対参照（$A$2）にするのがポイントです。下にコピーすると範囲が $A$2:A3、$A$2:A4 と伸びていき、「ここまでに自分と同じ値が何件あったか」を数えます。
この列でフィルタをかければ、削除対象だけを選んで消せます。
複数列での判定
複数の列を組み合わせて重複を判定したい場合、キーを連結します。
作業列D2: =A2 & "|" & B2
判定E2: =IF(COUNTIF($D$2:D2, D2) > 1, "削除対象", "")区切り文字（|）を挟むのが重要です。挟まないと AB + C と A + BC が同じキーになってしまいます。
COUNTIFS を使う方法もあります。
=IF(COUNTIFS($A$2:A2, A2, $B$2:B2, B2) > 1, "削除対象", "")3. 条件付き書式 ── 色で目立たせる
範囲を選択して「表示形式 → 条件付き書式 → カスタム数式」に次を入れます。
=COUNTIF($A$2:$A$1000, $A2) > 1列だけを $ で固定するのがポイントです。行を固定しないことで、各行が個別に判定されます。
行全体に色を付けたいなら、範囲を A2:D1000 にして同じ数式を入れてください。
削除する前に、目で確認できるのがこの方法の価値です。
4. メニューの「重複を削除」── 元データから消す
データ → データクリーンアップ → 重複を削除
- – 判定に使う列を選べます
- – 「データにヘッダー行が含まれている」のチェックを忘れずに
- – 実行すると元に戻せません（Ctrl+Z は効きますが、確実ではありません）
必ず実行前にコピーを取ってください。 シートを複製しておくのが最も安全です。
どの行が残るかは制御できません。「最新の行を残したい」といった要件がある場合は使えません。 その場合は SORT で並べ替えてから UNIQUE を使うか、COUNTIFで印を付けて手動で消してください。
5. データの入力規則 ── そもそも入れない
最も確実な方法です。
対象の列を選択して「データ → データの入力規則 → カスタム数式」に次を入れます。
=COUNTIF(A:A, A1) = 1「入力を拒否」に設定すると、既に存在する値を入力できなくなります。
後から掃除する必要がなくなるので、新しく表を作るときは最初にこれを設定しておくことを推奨します。
重複しているように見えて別物のケース
「同じに見えるのに重複と判定されない」場合、原因はほぼ次の4つです。
| 原因 | 確認方法 | 対処 |
|---|---|---|
| 前後のスペース | =LEN(A2) で文字数 |
TRIM |
| 全角と半角 | 目視 | ASC |
| 数値と文字列 | セルの寄せ方向 | VALUE |
| 見えない改行 | =LEN(A2) |
SUBSTITUTE(A2, CHAR(10), "") |
まとめて整えるなら次の形です。
=ARRAYFORMULA(IF(A2:A="", "", TRIM(ASC(SUBSTITUTE(A2:A, CHAR(10), "")))))作業列に整形済みの値を出してから、そちらで重複判定するのが確実です。
なお、COUNTIF と UNIQUE は大文字小文字を区別しません。 "abc" と "ABC" は同じ値として扱われます。区別が必要なら EXACT を使った判定に置き換えてください。
重複している値のほうを一覧にする
UNIQUE は重複を「除く」関数なので、逆はできません。組み合わせて書きます。
=UNIQUE(FILTER(A2:A1000, COUNTIF(A2:A1000, A2:A1000) > 1))2回以上出現している値だけの一覧が出ます。
出現回数も一緒に見たいなら QUERY が便利です。
=QUERY(A:A, "select A, count(A) where A is not null group by A order by count(A) desc", 1)多い順に並んだ集計表が1行で作れます。
まとめ
- – 一覧を作るなら UNIQUE（元データは変わらない）
- – 印を付けるなら COUNTIF。2件目以降だけなら
$A$2:A2 - – 複数列の判定はキーを
|で連結する - – 元データから消すならメニュー機能。必ずコピーを取ってから
- – 最も確実なのは入力規則で「入れない」こと
- – 判定されないときはスペース・全角半角・型・改行を疑う
コメントを残す