【Googleスプレッドシート】重複データを赤く表示!COUNTIFを使った重複検出の書式

スプレッドシートでデータを管理していると、同じ値が複数箇所に含まれていることに気付く場面があります。手作業で探すのは時間がかかるだけでなく、見落としも発生しやすいものです。この記事では、COUNTIF関数と条件付き書式を組み合わせて、重複データを自動的に赤色でハイライト表示する方法を解説します。数式を正しく設定することで、データ入力と同時に重複を視覚的に把握できるようになります。

設定は非常に簡単で、初心者の方でも数分で完了します。スプレッドシートの基本的な操作がわかれば、すぐに試せる内容です。この記事を最後まで読めば、重複チェックの手間を大幅に削減できるでしょう。

COUNTIFと条件付き書式で重複を検出する仕組み

条件付き書式では、設定した数式がTRUE(真)となるセルに指定の書式(フォント色や背景色など)が適用されます。COUNTIF関数は指定された範囲内で、条件に合うセルの個数を返す関数です。重複データの検出には、「その値が範囲内に2つ以上存在する」という条件を使います。つまり、COUNTIF(範囲, 値) > 1 がTRUEになれば、その値は重複していると判定できます。

重要なのは、条件付き書式の「カスタム数式」でこの数式を記述する点です。通常のセルに数式を入力するのではなく、書式ルールとして設定します。これにより、判定結果を表示するのではなく、条件に合ったセルに直接書式を適用できます。また、範囲の指定方法に絶対参照と相対参照を適切に組み合わせることで、すべてのセルで正しく判定が行われます。

重複データを赤く表示する具体的な手順

1. 対象範囲を選択する
   重複をチェックしたい列またはセル範囲を選択します。例えばA列全体ならA列をクリック、特定範囲ならドラッグして選択します。範囲の先頭行がデータの開始行であることを確認してください。
2. 条件付き書式のルールを開く
   メニューバーの「書式」→「条件付き書式」をクリックします。右側に条件付き書式ルールのパネルが表示されます。
3. カスタム数式を設定する
   「セルの書式設定条件」で「カスタム数式」を選択します。数式入力欄に「=COUNTIF(A:A, A1)>1」と入力します。ここでA:Aはチェック範囲、A1は選択範囲の先頭セルを表します。選択範囲がA1:A100の場合はA1を使います。
4. 書式スタイルを指定する
   「書式設定スタイル」で「塗りつぶし」を選び、赤色など目立つ色を選択します。フォントの色を変えても構いません。プレビューで見た目を確認してから「完了」をクリックします。
5. ルールの適用範囲を確認する
   「範囲」欄に最初に選択した範囲が正しく表示されているか確認します。必要に応じて編集できます。また、他のルールと競合しないように優先順位を調整します。

うまくいかないときの注意点と応用テクニック

重複が検出されない場合の数式の確認

よくある原因は、カスタム数式内の参照が正しく設定されていないことです。選択範囲がA2:A100の場合、数式は「=COUNTIF(A$2:A$100, A2)>1」と書きます。範囲の開始行を絶対参照($A$2:$A$100)にし、条件のセルを相対参照(A2)にすると正しく動作します。範囲を間違えると、すべてのセルが重複扱いになったり、まったく適用されなかったりします。

空白セルが赤くなってしまう問題

空白セルはCOUNTIFでカウントされませんが、条件付き書式では空白セルに数式が適用されないことがあります。もし空白セルが赤くなる場合は、数式に「AND(セル<>“”, COUNTIF…)」のように空白を除外する条件を追加してください。例えば、「=AND(A2<>“”, COUNTIF(A$2:A$100, A2)>1)」とします。

大文字と小文字の区別をしたい場合

COUNTIFは大文字と小文字を区別しません。もし区別したい場合は、EXACT関数と組み合わせます。ただし、条件付き書式で配列数式を使う必要があるため、少し複雑になります。別の方法として、B列に「=EXACT(A2, A3)」のような補助列を用意し、その結果に条件付き書式を適用する方法もあります。

複数列で重複を判定したい場合

複数の列を組み合わせて重複を判定するには、COUNTIFS関数を使います。例えば、A列とB列の組み合わせが重複している場合、「=COUNTIFS(A$2:A$100, A2, B$2:B$100, B2)>1」と設定します。この数式も同様に絶対参照と相対参照を適切に使い分けてください。

数値と文字列の混合データでの注意

COUNTIFは数値と文字列を区別しますが、見た目が同じでも型が異なると別の値とみなされることがあります。例えば、文字列としての”123″と数値の123は別の値です。データの一貫性を保つため、入力時に書式を統一することをおすすめします。

重複検出方法の比較:条件付き書式 vs UNIQUE関数 vs FILTER関数

まとめ

この記事では、COUNTIF関数と条件付き書式を使って重複データを赤色でハイライトする方法を解説しました。カスタム数式に「=COUNTIF(範囲, 先頭セル)>1」と入力するだけで、スプレッドシートが自動的に重複を検出し、視覚的に教えてくれます。このテクニックを覚えておけば、データの入力ミスや重複登録をすぐに見つけられるようになります。

さらに、複数列の重複判定にはCOUNTIFSを、大文字小文字を区別したい場合にはEXACTや補助列を活用すると便利です。条件付き書式のルールは複数設定できるので、データの状態に応じて色分けを変える応用も可能です。ぜひ実際のシートで試してみてください。