【Googleスプレッドシート】重複セルだけ色を変える!=COUNTIF条件の応用

スプレッドシートで大量のデータを扱っていると、同じ値が複数箇所に含まれていることに気づかないことがあります。手作業で一つずつ確認するのは時間がかかり、見落としも発生しやすいでしょう。この記事では、COUNTIF関数と条件付き書式を組み合わせて、重複しているセルだけを自動で色分けする方法を解説します。この操作を覚えれば、一瞬で重複データを視覚的に発見できるようになります。

COUNTIFで重複を検出する仕組み

COUNTIF関数は、指定した範囲の中で条件に合うセルの個数を数えます。例えば「=COUNTIF(A1:A10, “りんご”)」と書けば、A1からA10の中で「りんご」が何回出現するかを返します。この性質を利用して、範囲全体の中で自分自身を含めて同じ値が2回以上出現する場合、つまり「重複」と判断できます。

条件付き書式でこの数式をカスタムルールとして設定するときは、カウント範囲を絶対参照($記号で固定)にすることが重要です。絶対参照にしないと、ルールが適用されるセルごとにカウント範囲がずれてしまい、正しく重複を検出できません。基準セルは相対参照(例:A1)にすることで、各セルが自分の値を基準に判定できます。

重複セルに色を付ける具体的な手順

1. 重複をチェックしたい範囲を選択する
   まず、重複を判定したいデータ範囲全体をマウスで選択します。範囲は単一列でも複数列でも構いませんが、後述の数式で使用するため、範囲の先頭セルを覚えておきましょう。
2. 条件付き書式のメニューを開く
   メニューバーから「表示形式」→「条件付き書式」をクリックします。画面右側に条件付き書式の設定パネルが表示されます。
3. カスタム数式ルールを追加する
   「セルの書式設定の条件」プルダウンで「カスタム数式」を選択します。表示された入力欄に、以下の数式を入力します。
   =COUNTIF($A$1:$A$100, A1)>1
   ここではA1:A100が対象範囲、A1が範囲の先頭セルです。実際の範囲に合わせて書き換えてください。
4. 書式スタイルを設定する
   「書式設定のスタイル」で、重複セルに適用したい色を選びます。塗りつぶしの色やフォント色を変更できます。例えば薄い赤の背景色を選ぶと目立ちます。
5. ルールを保存して結果を確認する
   「完了」ボタンをクリックすると、選択範囲内の重複セルだけが設定した色で塗りつぶされます。データを追加しても自動的に色が更新されるため、リアルタイムで重複チェックができます。

上記の手順ではA列だけを対象にしましたが、B列やC列など別の列でも同じ方法で色を付けられます。複数の列にルールを追加する場合は、それぞれの列に対して別の条件付き書式ルールを作成してください。

複数列の組み合わせで重複を検出する方法

姓と名など、複数の列の値がすべて一致する場合に重複とみなしたいことがあります。その場合はCOUNTIFS関数を使って、複数条件を指定します。数式は次のようになります。
=COUNTIFS($A$1:$A$100, $A1, $B$1:$B$100, $B1)>1
この数式を先ほどと同じ手順で条件付き書式に設定すれば、A列とB列の両方が一致する行だけが色付けされます。各列の範囲は絶対参照、基準セルは相対参照にする点は変わりません。

重複チェックでよく起きるトラブルとその対策

範囲指定を間違えて正しく判定されない

カスタム数式で指定する範囲が選択範囲と一致していないと、期待通りの結果になりません。例えば選択範囲がA1:A100なのに数式でA1:A50しか指定しないと、51行目以降は判定されません。範囲は必ずデータ全体をカバーするように設定しましょう。

絶対参照を忘れて色がずれる

カウント範囲を絶対参照($A$1:$A$100)にしないと、ルールが適用されるセルごとに範囲が1行ずつずれてしまいます。その結果、正しい重複判定ができなくなり、色が正しく付かない原因になります。数式を入力するときは、F4キーを押して絶対参照に切り替えると便利です。

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

COUNTIF関数は大文字と小文字を区別しません。そのため「Apple」と「apple」は同じものとして扱われます。区別したい場合は、EXACT関数とSUMPRODUCTを組み合わせる必要があります。簡易的には、代わりにQUERY関数やFILTER関数を使う方法もありますが、条件付き書式との相性はやや複雑になります。

空白セルが重複と判定されるのを防ぐ

範囲に空白セルがあると、すべての空白セルが同じ値(空白)としてカウントされ、色が付いてしまうことがあります。これを避けるには、数式に空白を除外する条件を追加します。例えば「=AND(COUNTIF($A$1:$A$100, A1)>1, A1<>“”)」とすれば、空白セルは無視されます。

大量データで処理が重くなる

数万行を超えるデータでは、条件付き書式の計算に時間がかかり、スプレッドシートの動作が遅くなることがあります。その場合は、重複チェック専用の列を用意してCOUNTIF関数を直接セルに書き込み、その列をフィルタで絞り込む方法が軽量です。条件付き書式は必要な範囲だけに適用するようにしましょう。

重複チェック方法の比較

まとめ

COUNTIF関数と条件付き書式を組み合わせることで、重複セルだけを自動的に色付けできるようになりました。この方法なら、目視で探す手間を省き、見落としを防止できます。次は、色を付けるだけでなく、重複データを別シートに抽出したり、削除する操作にも挑戦してみてください。また、条件を「>1」から「=1」に変えれば、重複していないユニークなデータだけを強調表示することも可能です。スプレッドシートの条件付き書式とCOUNTIFの組み合わせは、データの品質管理に欠かせない強力なテクニックです。