仕事でデータ整理をしていると、スプレッドシートの重複データに頭を悩ませることってありますよね。リストの中で同じ名前やメールアドレスが何度も出てきたりして、どれが正しいデータなのか分からなくなってしまうこともあるかもしれません。私も日々のデータ管理で、重複箇所をパッと色付けして確認したいなと思ったり、いらない行を一気に削除したいなと悩むことがよくあります。さらに、A列とB列のように複数列を条件にしたり、別シートのデータと見比べたり、大文字小文字を区別したりと、やりたいことはどんどん複雑になっていくんですよね。関数を入れすぎるとスプレッドシートが重い状態になってしまって、作業が進まないなんてことも。そこで今回は、そんなスプレッドシートの重複に関するお悩みをスッキリ解決するための方法をまとめてみました。手動での整理から、関数を使った抽出、そして日々の作業を楽にする自動化のアイデアまで、私が普段の作業で試行錯誤しながら学んだコツをたっぷりとご紹介します。この記事を読めば、きっとあなたのデータ整理もグッとスムーズになるかなと思います。
- 条件付き書式を使った重複箇所のわかりやすい色付け方法
- 不要な重複データを一括で安全に削除する手順
- 関数を活用した重複のないクリーンなデータの抽出術
- 動作を軽く保ちながら重複を未然に防ぐ設定や自動化のコツ
スプレッドシートの重複データを処理する
まずは、既に入力されてしまったスプレッドシートの重複データをどうにかしたいという時によく使う、実践的なテクニックから見ていきましょう。見た目でパッとわかるようにする方法から、いらないデータを綺麗にお掃除する方法まで、状況に合わせて使い分けられると便利ですね。
条件付き書式で色付けして視覚化する
「いきなりデータを消してしまうのは怖いから、まずはどこが重複しているか目で見えるようにしたい」と思うこと、ありますよね。スプレッドシートにはボタン一つで重複をハイライトする機能はないので、条件付き書式と関数を組み合わせて使います。
よく使うのは、最初に入力したデータには色を付けず、誤って2回目以降に追加してしまったデータだけに警告の色を付ける設定です。条件付き書式のカスタム数式に =AND(A2<>"", COUNTIF($A$2:A2, A2)>1) と入力します。この式のポイントは、検索する範囲のスタートを「$A$2」と絶対参照でしっかり固定して、終わりを「A2」と相対参照で動きを持たせているところです。行が下へ進むにつれて検索範囲が広がり、2回目に出てきた時だけ色がつく仕組みになっています。
もし、最初に出たものも含めて重複しているグループ全体を色付けしたい場合は、=COUNTIF($A$2:$A$100, A2)>1 のように範囲を完全に固定してあげればOKです。
重複データを一括で完全に削除する
色付けして確認できたら、次は不要な行をまとめてお掃除していくステップです。手軽にできるのが、メニューの「データ」にある「データクリーンアップ」から重複を削除する機能を使う方法ですね。
でもその前に、気をつけておきたいことがあります。パッと見は同じでも、見えないスペースが文字の後ろに入っていると、スプレッドシートは違うデータだと判定してしまうんです。なので、削除を実行する前に同じくデータクリーンアップの中にある「空白文字を削除（トリム）」をして、データを綺麗な状態に整えておくのがおすすめです。
複数列の条件を同時に満たす行の判定
実際の仕事のデータだと、「都道府県」の列単独で被っているかではなく、「都道府県」と「商品名」の両方が完全に一致した場合だけを重複とみなしたい、というケースも頻繁にあるかなと思います。
そういう時は、複数列の条件を同時にチェックできるCOUNTIFS関数を使います。カスタム数式に =COUNTIFS($A$2:$A$100, $A2, $B$2:$B$100, $B2) のように書くことで、A列とB列の両方の条件を満たす行を見つけ出せます。
=A2&B2 と書いてA列とB列の文字を合体させてしまい、その結合したC列に対してシンプルなCOUNTIF関数を使うというのも、後から見て分かりやすくて保守性の高い良い裏技ですよ。別シートのデータと照合し確認する
「マスターのシートに登録されているIDが、日報のシートに入力されたら色を変えたい」という、シートをまたいだ照合もすごくよくある悩みですよね。
ただ、スプレッドシートの仕様上、条件付き書式の中で直接別のシートを読もうとするとエラーになって機能してくれません。ここで活躍するのがINDIRECT関数です。
数式の中に =COUNTIF(INDIRECT("'マスター'!$A$2:$A$100"), A2)>0 のように、シート名と範囲をダブルクォーテーションで囲んで文字としてINDIRECT関数に読み込ませることで、別シートにあるデータを無事に検索対象として認識させることができます。さらに、参照元の範囲に「名前付き範囲」を設定して呼び出すようにすると、後でシート名が変わってもエラーで壊れることがないので安心です。
大文字小文字を厳密に区別して比較する
システムから出力したデータをまとめている時など、「Apple」と「apple」をきっちり別のものとして扱いたい場面もありますよね。スプレッドシートの普段の関数（COUNTIFやVLOOKUPなど）では、大文字と小文字、それに全角半角は区別されないという特性があるんです。
厳密に見分けたい時は、2つの文字が完全に同じかをTRUEかFALSEで判定してくれるEXACT関数の出番です。配列数式を使って =SUMPRODUCT((EXACT(A:A, A1))*1) と記述することで、大文字小文字まで完全に一致するものだけを数え上げることができます。
関数を使った事前のデータクレンジング（正規化）に役立つものを、下の表にまとめてみました。
| 関数の目的 | 使う関数 | どんな効果があるか |
|---|---|---|
| 余分なスペースを消す | TRIM | 文字の前後や、単語間の連続した無駄な空白をキレイにします |
| 全角・半角をそろえる | ASC / JIS | ASCで半角に、JISで全角に変換して規格のばらつきを防ぎます |
| 大文字・小文字をそろえる | UPPER / LOWER | すべて大文字、またはすべて小文字に統一します |
| 見えない文字を消す | CLEAN | 他システムから持ってきた時に混ざる改行コードなどを消去します |
スプレッドシートの重複管理と最適化
ここからは、ただ消すだけではなく、元データを活かしたまま必要な情報を引き出したり、今後のトラブルを防いだりする応用編に入っていきますね。関数を使った少しステップアップした方法や、重いファイルを軽くするための工夫も合わせてご紹介します。
関数を利用して重複のないリストを抽出
元のデータを上書きしてしまうのはリスクがあるから、別の場所にキレイなリストを作りたいという時は、元データを傷つけないUNIQUE関数がすごく便利で洗練されています。
=UNIQUE(A2:A100) と入力するだけで、指定した範囲から重複が取り除かれた一意のリストが自動的に展開されます。複数列を指定すれば、行全体を1つのセットとして重複判定してくれますし、離れた列同士を波括弧 {} とセミコロンでくくって縦につなげて、ひとつのリストを作ることもできる優れものです。
=FILTER(A2:A100, COUNTIF(A2:A100, A2:A100)>1) とすることで、出現回数が2回以上のものだけをピックアップできますよ。動作が重い時のパフォーマンス改善手法
関数をたくさん入れたり、データが増えたりすると、「文字を打つたびに画面が数秒固まる……」といったパフォーマンスの低下に直面することがありますよね。
スプレッドシートが重い状態になる原因の多くは、再計算の負担が大きすぎることです。例えば、条件付き書式やCOUNTIF関数で、検索範囲を「A:A」のように列全体（無限範囲）にしてしまうと、下の方の何もない行まで計算リソースを使ってしまうので要注意です。
データの入力規則で重複を未然に防ぐ
今まで紹介したのは、入力されてしまった後の事後対処法でした。でも、データ管理で一番理想的なのは、そもそも間違って重複データが混入しないようにシステム側でブロックしてしまうことですよね。
これには「データの入力規則」を使います。社員番号などの列を選択し、ルールの条件をカスタム数式にして =COUNTIF($A$2:$A$100, A2)=1 と設定します。そして詳細設定で「データが無効の場合」に「入力を拒否」を必ず選びます。
こうしておけば、誰かがすでにあるデータと同じ値を打ち込んだ瞬間にエラーダイアログが出て、強制的にキャンセルされます。警告を出すだけでは見落とされることも多いので、絶対に重複させたくない列ではこの設定をしておくのが強力な抑止力になりますよ。
GASを用いたバッチ処理による自動化
毎日大量のデータがフォームなどからどんどん追記されるようなシートだと、手作業でチェックしたり、重い関数をずっと動かしておくのも限界が来ますよね。
そんな本格的な運用になってきた時は、Google Apps Script（GAS）を使った自動化も視野に入ってきます。GASを使えば、標準で用意されているメソッドを呼び出して、指定した範囲の重複行を高速で削除する処理をプログラムで組むことができます。さらに「毎日夜中の3時に実行する」といった時間主導型トリガーを設定しておけば、管理者がいなくても朝には常にデータベースがクレンジングされた状態になります。
スプレッドシートの重複対策まとめ
ここまで、スプレッドシートの重複に関する課題を解決するための様々なアプローチを見てきました。
その場ですぐに確認したいだけの時は「条件付き書式」で色付け、分析用のクリーンな元データを作りたい時は「UNIQUE関数」での抽出など、自分の目的に合わせて機能を選ぶことが大切ですね。そして、根本からデータの品質を守るためには「データの入力規則」によるガバナンス強化が欠かせません。関数を使う際も、表記ゆれへの対策や、重くならないための範囲指定といった細部の工夫が、正確な処理に繋がります。
今回ご紹介した内容が、少しでも皆さんの日々のデータ整理の負担を減らすヒントになれば嬉しいです。なお、ここで紹介した関数の挙動や制限、最大行数などの数値データは、システムのアップデートにより変更されることもあり、あくまで一般的な目安となります。安全で確実な運用のためにも、正確な情報は公式サイトをご確認くださいね。あなたのデータ管理がもっと快適になりますように！
