目的別に4つの方法を使い分ける
顧客リストや申込の一覧に同じ人が 2 回入っていると、二重に連絡したり、件数を多く数えたりする原因になります。Google スプレッドシートには重複を見つける方法がいくつかあり、「見るだけか」「消すか」で選ぶ方法が変わります。
重複チェックの方法の選び方
| やりたいこと | 方法 | 元のデータ |
|---|---|---|
| 重複したセルに色を付けて見る | 条件付き書式 + COUNTIF | 変わらない |
| 重複の行に「重複」と目印を付ける | COUNTIF (COUNTIFS) | 変わらない |
| 重複を除いた一覧を別の場所に出す | UNIQUE | 変わらない |
| 重複した行を消す | 重複を削除の機能 | 行が消える |
この記事では、A 列にメールアドレス、B 列に氏名が入った、2 行目から 500 行目までの顧客リストを例にします。範囲は自分のシートに合わせて書き換えてください。
方法1: 条件付き書式で重複に色を付ける
重複がどこにあるかを一目で見たいときの方法です。公式ヘルプでも、重複する値をハイライトする例として同じ形の式が紹介されています。
範囲を選ぶ
A2 から A500 までを選びます。
条件付き書式を開く
上部のメニューの [表示形式] > [条件付き書式] を押すと、右側に設定の画面が開きます。
カスタム数式を入れる
[セルの書式設定の条件] で [カスタム数式] を選び、次の式を入れます。
=COUNTIF($A$2:$A$500,A2)>1色を選んで完了
書式のスタイルで背景色を選び、[完了] を押します。2 回以上出てくるメールアドレスのセルに色が付きます。
式の $A$2:$A$500 は $ を付けて範囲を固定し、A2 は $ を付けずに 1 行ずつずれるようにしています。「A 列全体のうち、この行と同じ値がいくつあるか」を数え、2 つ以上なら色を付けるという意味です。
行全体に色を付けたいとき
範囲を A2 から B500 までにして、式を =COUNTIF($A$2:$A$500,$A2)>1 に変えます。$A2 のように列だけを固定すると、B 列のセルでも A 列の値で判定されるため、重複した行がまるごと色付きになります。
方法2: COUNTIFで「重複」の目印を付ける
色だけでは並べ替えや絞り込みに使えないので、件数を数えたいときや、重複の行だけをフィルタで抜き出したいときは、空いている列に目印を出します。C2 に次の式を入れ、C500 までコピーします。
=IF(COUNTIF($A$2:$A$500,A2)>1,"重複","")
この式は、重複しているすべての行に「重複」と出します。1 件目は残して、2 件目以降だけに目印を付けたいときは、数える範囲を「先頭からその行まで」に変えます。
=IF(COUNTIF($A$2:A2,A2)>1,"2件目以降","")
$A$2:A2 は下の行へコピーすると $A$2:A3、$A$2:A4 と広がるので、上にすでに同じ値がある行だけに目印が付きます。目印が付いた行を消せば、各メールアドレスの 1 件目だけが残ります。
全部に目印
- 範囲は
$A$2:$A$500で固定 - 重複したすべての行が対象
- どれを残すか人が選びたいとき
2件目以降に目印
- 範囲は
$A$2:A2で広がる - 1 件目は目印が付かない
- 古い方を残して後から来た分を消すとき
複数の列の組み合わせで判定する
「同じ会社でも担当者が違えば別の人」のように、複数の列がそろって同じときだけ重複とみなすなら、条件を複数指定できる COUNTIFS を使います。
=IF(COUNTIFS($A$2:$A$500,A2,$B$2:$B$500,B2)>1,"重複","")
COUNTIFS では、条件をかける範囲どうしの行数と列数をそろえる必要があります。上の式のように、どの範囲も 2 行目から 500 行目までにしておきます。
方法3: UNIQUEで重複を除いた一覧を出す
元のリストはそのままに、重複を除いた一覧を別の場所に作りたいときは UNIQUE 関数 を使います。空いている列の先頭のセルに式を 1 つ入れるだけで、結果が下へ広がって表示されます。
UNIQUE の使い方の例
| 式 | 結果 |
|---|---|
=UNIQUE(A2:A500) | メールアドレスの重複を除いた一覧 |
=UNIQUE(A2:B500) | メールアドレスと氏名の組み合わせで重複を除いた一覧 |
=UNIQUE(A2:A500,FALSE,TRUE) | 1 回だけ出てくるメールアドレスだけの一覧 |
重複を除いた件数だけを知りたいときは、=COUNTA(UNIQUE(A2:A500)) のように COUNTA で数えます。UNIQUE は元のデータが変わると結果も自動で変わるため、毎週追記されるリストの集計にも向いています。
方法4: 重複を削除の機能でまとめて消す
重複を消してしまってよいなら、関数を使わずにメニューから消せます。公式ヘルプの手順は次のとおりです。
- 重複を削除したいデータの範囲を選ぶ
- [データ] > [データ クリーンアップ] > [重複を削除] を押す
- 判定に使う列と、データに見出しの行があるかを選ぶ
- [重複を削除] を押す
公式ヘルプによると、大文字と小文字、書式、数式が違っていても、値自体が同じなら重複とみなされます。
重複のはずなのに見つからないとき
同じに見えるのに重複と判定されない、または逆に別の値が重複とされるときは、次の点を確かめます。
判定がずれる主な原因と対処
| 原因 | 起きること | 対処 |
|---|---|---|
| 前後に空白が入っている | 「田中」と「田中 」が別の値になる | [データ] > [データ クリーンアップ] > [空白文字を削除] |
| 大文字と小文字の違い | COUNTIF は区別しないので同じとみなす | 区別したいなら EXACT 関数などで別に判定する |
値に * や ? が入っている | COUNTIF がワイルドカードとして読む | 文字そのものを探すには前に ~ を付ける |
COUNTIF の公式ヘルプには、大文字と小文字を区別しないことと、? が任意の 1 文字、* が任意の文字の並びとして扱われることが書かれています。
また、空白の削除の機能では、改行なしのスペース ( など) は削除されないと公式ヘルプに書かれています。Web からコピーしたデータで判定がずれるときは、この種類の空白を疑ってください。
自分で作るのが難しいときは
重複チェックは、1 つのシートの中で済むうちは関数で十分です。ただ、「複数の部署のシートを毎日まとめて重複を除きたい」「フォームから来た申込をすでにある顧客と照合したい」となると、関数だけではシートが重くなったり、式が壊れやすくなったりします。
そうした作業は GAS で自動にしたり、専用の仕組みに置き換えたりした方が楽になることがあります。自分で組むのが難しい、どこから手を付けるか決めかねている、という場合は、シートナビの相談フォームからお気軽にご相談ください。
よくある質問
別のシートにあるリストと重複しているかを調べられますか？
関数なら、=COUNTIF('既存顧客'!$A$2:$A$1000,A2)>0 のようにシート名を付けて別のシートの範囲を数えられます。ただし条件付き書式のカスタム数式では、公式ヘルプのとおり別のシートを直接参照できないため、INDIRECT("'既存顧客'!A2:A1000") のように INDIRECT 関数を使って範囲を指定します。
重複を削除した後に元に戻せますか？
実行した直後なら、元に戻す操作 (Windows は Ctrl + Z、Mac は Command + Z) で戻せます。時間がたってからでも、[ファイル] > [変更履歴] から前の版を開けますが、ほかの変更も一緒に戻るため、実行前にシートを複製しておくのが確実です。
行が多くて重くなったときはどうすればよいですか？
条件付き書式や COUNTIF を何千行にもかけると、計算に時間がかかります。重複を消してよいなら、関数を残さず重複を削除の機能で一度だけ処理する方が軽く済みます。毎日のように重複を除く作業があるなら、GAS で決まった時刻に処理する形にすると、シートに式を置かずに済みます。
