重複データの扱いには、「見つける」「抽出する」「削除する」「そもそも入れない」の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関数の使い方
重複している行だけを見つける
重複を除くのではなく、どれが重複しているかを知りたい場合もあります。名簿の二重登録を見つける、入力ミスを洗い出す、といった用途です。
考え方は、各行の値が範囲全体に何回現れるかを数え、二以上なら重複と判定することです。作業列に件数を出せば、重複している行が一目で分かります。
さらに一歩進めて、何件目かを出すこともできます。範囲の開始行だけを固定して終了行を相対参照にすると、その行までに何回現れたかが返ります。一なら初出、二以上なら二回目以降です。この列で絞り込めば、初出だけを残すことも、重複分だけを取り出すこともできます。
複数列の組み合わせで判定したい場合は、列を連結した作業列を作り、それを数える対象にします。氏名と生年月日をつないだ値が二回現れれば、同一人物の可能性が高いと判断できます。
条件付き書式で色を付ける方法もあります。目で確認しながら判断したい場合に向いています。ただし、行数が多いと動作が重くなります。
そもそも重複させない仕組み
重複を後から掃除するより、入力の時点で防ぐほうが確実です。
入力規則にカスタムの数式を設定し、その値が既に存在する場合は入力を弾く、という設定ができます。件数を数える関数で、一より大きくなる入力を拒否する形です。
この設定を入れておけば、二重登録そのものが起こりません。掃除の工程が不要になります。
注意点として、貼り付けの操作では入力規則が働かないことがあります。大量のデータを貼り付ける運用では、防ぎきれません。この場合は、貼り付け後に重複の件数を確認する仕組みを併せて用意してください。
もうひとつ、既存のデータに重複が残っている状態で規則を設定しても、既存分は弾かれません。先に掃除してから設定してください。
入力規則の設定手順は、プルダウンの記事にまとめています。同じ画面から設定できます。→ プルダウンの作り方
重複を消す前に確認すること
重複の削除は元に戻しにくい操作です。実行する前に確認しておく点があります。
何をもって重複とするかを決めてください。 全列が一致した行だけなのか、特定の列が一致すれば重複とみなすのか。ここが曖昧なまま実行すると、必要な行まで消えます。
表記のゆれを先に揃えてください。 全角と半角、前後の空白、大文字と小文字。見た目が同じでも別の値として扱われます。TRIMやSUBSTITUTEで揃えてから実行してください。
残す行を決めてください。 重複のうち、最初の行を残すのか、最新の行を残すのか。日付順に並べ替えてから実行すると、意図した行が残ります。
元データを別シートに複製しておいてください。 削除後に「消しすぎた」と気づいても、元がなければ戻せません。
件数を先に数えておいてください。 COUNTIFやCOUNTAで実行前の件数を控え、実行後と比較すれば、想定通りかを確認できます。
関数で抽出する方法も検討してください。 UNIQUEで別の場所に一意の一覧を出せば、元データを壊さずに済みます。
よくある質問
Q. 削除したデータを戻せますか
直後なら取り消しで戻せます。時間が経つと戻せません。控えを取ってから実行してください。
Q. 一部の列だけで判定できますか
削除の機能では、判定に使う列を選べます。抽出の場合は、対象範囲をその列だけにします。
Q. 大文字と小文字は区別されますか
多くの場合、区別されません。区別が必要なら、厳密に比較する関数を使った判定が必要です。
Q. 前後の空白があると別扱いになりますか
なります。判定の前に空白を落としてください。
Q. 最新のものを残したいのですが
日付の降順に並べ替えてから削除を実行してください。上にあるものが残ります。
Q. 重複の件数だけ知りたいのですが
全体の件数から、重複を除いた件数を引いてください。
Q. 複数列の組み合わせで判定したいのですが
列を連結した作業列を作り、それを判定の対象にしてください。
Q. 入力の時点で防げますか
入力規則にカスタム数式を設定すれば、既存の値と重複する入力を弾けます。
まとめ
- 一覧を作るなら UNIQUE（元データは変わらない）
- 印を付けるなら COUNTIF。2件目以降だけなら
$A$2:A2 - 複数列の判定はキーを
|で連結する - 元データから消すならメニュー機能。必ずコピーを取ってから
- 最も確実なのは入力規則で「入れない」こと
- 判定されないときはスペース・全角半角・型・改行を疑う