Googleスプレッドシートで顧客リストや在庫ログなどの重要データを管理している際、知らない間に「データの重複」が発生し、データのクレンジング作業に手間取ることは多いものです。大量の行を目視でスクロールしながら重複を探すと、見落としなどのミスも発生しやすくなります。
このような重複チェックを自動化するのに適しているのが、条件に一致するセルの個数を数える「COUNTIF（カウントイフ）関数」です。この関数を活用すれば、重複データを自動的に検出し、条件付き書式で色付けして整理する仕組みを簡単に作成できます。この記事では、重複データのチェック用数式の作り方から、2回目以降の重複だけをカウントする応用方法、さらには重複行全体に色を塗って視認性を高める設定手順まで、分かりやすく解説します。
記事のポイント
- データ重複が引き起こすビジネス上の深刻なリスクと業務効率の低下
- COUNTIF関数の基本構文と「重複＝カウント数1より大きい」の判定ロジック
- セル参照における絶対参照と相対参照の組み合わせの重要性
- 標準の「重複を削除」機能とCOUNTIF関数を用いた仕組みの比較
スプレッドシートでの重複データ問題とCOUNTIF関数の役割
複数人で同じスプレッドシートを共同編集していたり、Webフォームから送信されたデータをコピー＆ペーストでまとめたりしていると、どうしても発生してしまうのがデータの重複です。重複データは単にシートの見た目が少し不格好になるというレベルの問題ではなく、実務上の信頼性を脅かす大きな要因になります。
データの重複が引き起こす業務効率の低下とリスク
例えば、メルマガ配信やキャンペーン案内メールを送信する際、同じメールアドレスが重複して登録されていると、同一人物にまったく同じメールが何通も届くことになります。これを受け取ったお客様側としては、「この会社は個人情報の管理がしっかりできていないのではないか」と不信感を抱く大きなきっかけになりかねません。
また、売上ログに同じ取引が二重に計上されていたり、同一の顧客が別々の行で複数回カウントされていたりすると、正確な顧客数などの統計データがすべて歪んでしまいます。この歪んだデータを信じて経営判断やマーケティング施策を行ってしまうと、本来立てるべきだった戦略から大きく外れてしまう危険性すらあります。
こうした深刻な問題を防ぐために、データの「健康診断」として役立つのがCOUNTIF関数です。本格的な集計やグラフ作成に取り掛かる前に、この関数でサッとデータ全体のクリーンさを診断しておくことで、後から計算が合わないと頭を抱えるような悲劇を防ぐことができますよ。
COUNTIF関数の基本構文と重複判定の仕組み
この関数は、指定したセルの範囲の中から、特定の条件に合致するセルがいくつあるかを数えるためのものです。
COUNTIF関数の基本構文は =COUNTIF(範囲, 条件) となります。第1引数の範囲には検索の対象にしたいセルの範囲を指定し、第2引数の条件には調べたいセル自体を指定します。
実際に重複判定の数式を組む際は、条件にチェック対象のセルを指定し、それが「1より大きいか（=2つ以上存在するか）」を評価させることになります。具体的には、=COUNTIF ($A$2:$A$100, A2) >1 という書き方をします。
この数式において、範囲の始まりと終わりに「$」マークをつける絶対参照を使うのが非常に重要なテクニックです。数式を下のセルへとコピーしていった際、絶対参照で指定した「範囲」は固定されたまま、第2引数の「条件」だけが相対的にずれていってくれるからです。
単純に不要なデータを今すぐ一度に削除したいだけであれば標準機能の「重複を削除」が非常に手軽で効果的ですが、どのデータが重複しているのかを目視で精査しながら作業を進めたい場合には、COUNTIF関数を使ったアプローチが圧倒的に強力な解決策となりますよ。最新の仕様については公式サイトもご確認ください。
COUNTIF関数を使って重複データを一括チェック・抽出する基本手順
スプレッドシートで顧客管理用の名簿や商品の在庫マスターなどを扱っていると、知らない間に同じデータが何度も登録されてリストが散らかることがありますよね。標準機能の「重複を削除」を使えば一発で消去できますが、いきなりデータを削除するため、目視で確認したいという場面では不向きです。そこで大活躍するのが、COUNTIF関数を使った重複チェックの方法ですよ。
作業用の「重複チェック列」を作成してCOUNTIFを入力する手順
スプレッドシートで重複データを安全に探し出す最初のステップは、作業用の「重複チェック列」を作成することです。既存のデータ列に直接手を加えないため、元のデータが壊れる心配がなく安心ですよ。
作成した新しい列の2行目のセルに、=COUNTIF ($A$2:$A, A2) という数式を入力します。この数式は、重複をカウントする際の最も標準的な記述ですよ。
数式を入力したら、下方向にオートフィルでコピーします。コピーが完了すると、重複チェック列に数字が表示されます。表示された数値が「1」であれば唯一のユニークデータ、表示された数値が「2以上」であれば重複しているデータであることを示していますよ。
2回目以降の重複のみをピンポイントで抽出する範囲ロック（相対参照）の裏ワザ
基本的なCOUNTIF関数の使い方では、データが重複している場合、最初に出てきた行も2回目に出てきた行も、すべての結果が同じ数字になってしまいます。
そこで役立つのが、「2回目以降に登場する重複データだけをピンポイントで見つけ出す」という範囲ロック（相対参照）を応用した裏ワザ数式ですよ。入力する数式は以下の通りです。
=COUNTIF ($A$2:A2, A2)
先ほどの数式とほとんど同じに見えますが、コロンの後ろ側が「:$A」ではなく、ドルマークのない相対参照の「A2」になっているのが大きな違いです。これによって、「データの最上部（2行目）から、現在自分がいる行までの範囲」の中で、そのデータが何回出現したかを数えることができます。
条件付き書式とCOUNTIFを組み合わせて重複行全体に色を付ける応用手順
データ量が多くなってくると、視覚的にパッと見で見分けられたらもっと嬉しいですよね。そこで大活躍するのが「条件付き書式」という機能です。条件付き書式を使えば、重複しているデータが存在するセルや、そのデータが含まれる行全体に自動で好きな色を塗ることができます。
条件付き書式の「カスタム数式」にCOUNTIFを組み込む設定手順
まずは、特定の列の中で重複しているセルを見つけ出し、そのセル単体に自動で色を付ける基本の手順から進めていきましょう。対象となるセル範囲を選択し、メニューバーの「表示形式」→「条件付き書式」を選びます。
「書式ルール」を「カスタム数式」に設定し、以下の数式を入力します。
=COUNTIF ($A$2:$A, A2)>1
この数式のポイントは、検索範囲である「$A$2:$A」にドル記号（$）を付けて絶対参照にしている点です。これにより、どのセルを判定するときも検索する範囲が固定されます。
「重複行全体」に色を付けて視認性を高める設定方法
「重複行全体」に色を付けるためには、条件付き書式を設定する際の「範囲指定」と「数式の書き方」を少しだけ工夫する必要があります。
まず、色を塗りたいテーブル（表）の全体を選択します。例えば、「A2:G100」のように指定します。そして、入力するカスタム数式を以下のように変更してください。
=COUNTIF ($A$2:$A, $A2)>1
判定の基準となるセルの「A2」の部分が、「$A2」と列固定のドル記号付きになっているのがポイントです。これにより、B2セルを判定するときも、C2セルを判定するときも、常に「A列（$A）の2行目の値を基準にする」という動作になり、行全体にきれいに色が塗られるという仕組みなのです。ぜひあなたのスプレッドシートでも実際に手を動かして、この便利な自動色分け機能を試してみてくださいね。
