目的別
スプレッドシートで重複を削除・抽出する方法ま
重複データの扱いには、「見つける」「抽出する」「削除する」「そもそも入れない」の4つの目的があります。どれをやりたいかで手段が変わります。
このページでは5つの方法を目的別に整理します。
このページの中身（22項目）
- 目的別の早見表
- 1. UNIQUE ── 重複を除いた一覧を作る
- 2. COUNTIF ── 重複している行に印を付ける
- 3. 条件付き書式 ── 色で目立たせる
- 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で別の場所に一意の一覧を出せば、元データを壊さずに済みます。
「目的別」の関数を全体から見る
目的別はやりたいことから関数を選ぶための記事です。同じカテゴリの関数は、つまずく場所も似ています。この記事のほかに52本あります。
このカテゴリで共通するつまずき
関数名から探すより、条件から絞るほうが早く決まります。1つで理解した内容は、同じカテゴリの他の関数にもそのまま使えます。
どれを使うかの決め方
出したい結果を一文で書けば、関数はほぼ1つに決まります。関数の一覧を眺めて選ぼうとすると、どれも当てはまるように見えて決まりません。
迷ったときは
→ 全関数の一覧 に目的別の索引があります。
数式を書く前の手順
書き始める前に決めておくことがあります。ここが曖昧なまま書くと、途中で行き詰まります。
1. 出したい結果を一文で書く
「何を、どの条件で、どんな形で出したいか」。この一文が書ければ、関数はほぼ決まります。書けないなら、まだ要件が固まっていません。
2. 元データの型を確認する
関数名から探すより、条件から絞るほうが早く決まります。=ISNUMBER(A2) で数値かどうかを確認できます。見た目が同じでも、数値と文字列は別物です。
3. 空欄と0の扱いを決める
未入力なのか、実績が0なのか。表の意味が変わります。どちらとして扱うかを決めてから書いてください。
4. 条件の数を数える
出したい結果を一文で書けば、関数はほぼ1つに決まります。将来条件が増える可能性があるなら、最初から複数条件に対応する形にしてください。
5. 共有先の環境を確認する
新しい関数は、古い環境で開くとエラーになります。社外に渡すファイルでは、この一点で選択肢が変わります。
6. 1行だけ書いて確認する
全行にコピーする前に、1行分の結果が正しいかを見てください。間違ったまま広げると、直す手間が増えます。
どの関数でも共通する確認
関数を問わず、結果がずれる原因は限られています。この5つを確認すれば、大半が解決します。
参照を固定したか
下方向にコピーする数式では、動いてはいけない参照を固定します。上の数行だけ見て判断しないでください。ずれは下のほうで表面化します。
範囲の行数が揃っているか
複数の範囲を渡す関数では、行数がずれると結果が狂います。エラーにならず値が返るため、気づきにくい種類の間違いです。
型が揃っているか
=ISNUMBER(A2)数値として入っているかを判定します。システムから書き出したデータでは、これが原因のことが多くあります。
前後に空白が入っていないか
=LEN(A2)&" / "&LEN(TRIM(A2))数が違えば、余分な空白が入っています。見た目では判別できません。
別の方法で検算したか
同じ数字を別の関数でも出して、突き合わせてください。1つの結果だけを見ても、正しいかどうかは判断できません。
最終行まで確認したか
一番下までスクロールして、結果を見てください。途中から値が変わっていないかを確認します。
引き継ぐときに残すこと
数式は動いていても、意図は残りません。渡す前に、次の点をシート内にメモしてください。3分で終わります。
何を出している数式か
一行で構いません。読めば分かると思っても、数か月後の自分には分かりません。
どの表を参照しているか
参照先のシート名と、更新の担当。別ファイルなら、その場所も書いてください。
該当しないときの扱い
空欄にしているのか、文言を出しているのか、エラーのまま残しているのか。書いていないと、受け取った側は空欄をデータなしと読みます。
触ってはいけない場所
列を挿入すると壊れる数式がある場合、必ず書き残してください。この一行があるだけで、引き継ぎ後の事故が大きく減ります。
使った関数の対応環境
新しい関数を使っている場合、古い環境では開けません。共有の範囲が広がる可能性があるなら、明記してください。
確定した期間は値にする
締めた月の集計は、値貼り付けにしてください。参照元を消しても壊れなくなり、ファイルも軽くなります。
このサイトの収録状況
現在、201本の記事をカテゴリ別に整理しています。関数名が分からない場合は、カテゴリから探してください。
| カテゴリ | 記事数 |
|---|---|
| 目的別 | 51本 ←この記事 |
| 集計 | 47本 |
| 文字列 | 31本 |
| 日付 | 19本 |
| 検索・参照 | 15本 |
| 条件 | 15本 |
| 機能 | 12本 |
| 配列・抽出 | 10本 |
| 関数一覧 | 1本 |
探し方の順番
関数名が分かっているなら検索窓から。やりたいことだけ決まっているなら「目的別」から。近い関数を比べたいなら、同じカテゴリの一覧から入ってください。
記事の構成
どの記事も、書式・実例・つまずきやすいところ・似た関数との使い分けを同じ順番で並べています。1本読めば、他の記事も同じ場所を探せます。
よくある質問
Q. 削除したデータを戻せますか
直後なら取り消しで戻せます。時間が経つと戻せません。控えを取ってから実行してください。
Q. 一部の列だけで判定できますか
削除の機能では、判定に使う列を選べます。抽出の場合は、対象範囲をその列だけにします。
Q. 大文字と小文字は区別されますか
多くの場合、区別されません。区別が必要なら、厳密に比較する関数を使った判定が必要です。
Q. 前後の空白があると別扱いになりますか
なります。判定の前に空白を落としてください。
Q. 最新のものを残したいのですが
日付の降順に並べ替えてから削除を実行してください。上にあるものが残ります。
Q. 重複の件数だけ知りたいのですが
全体の件数から、重複を除いた件数を引いてください。
Q. 複数列の組み合わせで判定したいのですが
列を連結した作業列を作り、それを判定の対象にしてください。
Q. 入力の時点で防げますか
入力規則にカスタム数式を設定すれば、既存の値と重複する入力を弾けます。
この分野の他の記事
同じ「目的別」の記事です。目的が近いので、こちらで解決しない場合はこちらも見てください。
まとめ
- 一覧を作るなら UNIQUE（元データは変わらない）
- 印を付けるなら COUNTIF。2件目以降だけなら
$A$2:A2 - 複数列の判定はキーを
|で連結する - 元データから消すならメニュー機能。必ずコピーを取ってから
- 最も確実なのは入力規則で「入れない」こと
- 判定されないときはスペース・全角半角・型・改行を疑う