【Googleスプレッドシート】重複データを抽出して表示!COUNTIFと条件付き書式の併用

Googleスプレッドシートでデータ管理をしていると、同じ値が複数回入力されている重複データに悩まされることがあります。顧客リストや商品一覧など、重複を放置すると集計結果がずれたり、重複削除の手間が発生したりします。この記事では、COUNTIF関数と条件付き書式を組み合わせて、重複データを抽出して視覚的に表示する方法を解説します。これらのテクニックを使えば、重複を瞬時に発見し、データの品質を向上させることができます。

COUNTIF関数と条件付き書式で重複データを抽出する仕組み

COUNTIF関数は、指定した範囲内で条件に一致するセルの個数を数える関数です。この関数を使って、各セルがその列の中で何回出現するかを計算します。その結果が2以上であれば、そのセルは重複していると判断できます。一方、条件付き書式は、セルの値に応じて自動的に書式(色やフォントなど)を変更する機能です。カスタム数式を使用してCOUNTIFの結果を評価すれば、重複セルだけに色を付けることが可能です。この2つを組み合わせることで、重複データを一覧からすぐに見つけられるようになります。

重複データを抽出する具体的な手順

COUNTIF関数で重複を判定する

1. 判定用の列を追加する
   重複を確認したい列(例:A列)の隣に新しい列を挿入します。例えばA列にデータがある場合、B列を挿入します。
2. COUNTIFの数式を入力する
   B2セルに =COUNTIF(A:A, A2)>1 と入力します。この数式は、A列全体の中でA2の値が2回以上出現する場合にTRUEを返します。
3. 数式をコピーする
   B2セルの右下のフィルハンドルをダブルクリックするか、データの最終行までドラッグして数式をコピーします。各行が重複しているかどうかがTRUEまたはFALSEで表示されます。
4. 表示をカスタマイズする(オプション)
   TRUE/FALSEではなく「重複」「ユニーク」などの文字列で表示したい場合は、=IF(COUNTIF(A:A, A2)>1, "重複", "ユニーク") とします。

条件付き書式で重複を色付けする

1. データ範囲を選択する
   重複を色付けしたいデータ範囲(例:A1:A100)を選択します。見出し行がある場合は見出しを含めても構いません。
2. 条件付き書式を開く
   メニューから「表示形式」→「条件付き書式」をクリックします。右側に「条件付き書式ルール」パネルが表示されます。
3. カスタム数式を設定する
   「セルの書式設定条件」を「カスタム数式」に変更します。「値または数式」の欄に =COUNTIF($A:$A, A1)>1 と入力します。範囲は絶対参照($A:$A)に、基準セルは選択範囲の先頭セル(A1)に合わせます。
4. 書式を選択する
   「書式設定スタイル」で塗りつぶしの色を選びます。例えば黄色や赤色を選択すると、重複セルが目立つようになります。
5. ルールを保存する
   「完了」をクリックしてルールを保存します。選択範囲内の重複セルが指定した色で塗られます。

抽出したデータをフィルタで表示する

1. データ範囲を選択する
   COUNTIFの結果が入った列(B列)を含めて、データ全体を選択します。
2. フィルタを作成する
   メニューから「データ」→「フィルタを作成」をクリックします。各列のヘッダーにフィルタアイコンが表示されます。
3. 重複行のみ表示する
   B列のフィルタアイコンをクリックし、「TRUE」または「重複」のチェックボックスだけをオンにします。これで重複している行のみが表示されます。
4. 必要に応じて処理する
   表示された重複行をコピーして別シートに貼り付けたり、削除したりできます。フィルタを解除するには、再度フィルタアイコンをクリックして「すべて選択」をオンにします。

重複データ抽出でよくある失敗と対処法

COUNTIFの範囲指定がずれてしまう

原因は、範囲を絶対参照にしていないことです。数式をコピーするときに範囲が相対参照だと、各行で範囲がずれて正しくカウントされません。対処法として、範囲を $A:$A のように絶対参照にします。例えば =COUNTIF($A:$A, A2)>1 とします。条件付き書式のカスタム数式でも同様に絶対参照を使います。

条件付き書式が正しく適用されない

原因は、適用範囲と数式内のセル参照の不一致です。適用範囲を正しく設定し、数式内の範囲は絶対参照($A:$A)、基準セルは適用範囲の先頭セルに相対参照(A1)で指定します。また、データがセル結合されている場合は、結合を解除するか、各セルに個別に数式を設定する必要があります。

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

COUNTIF関数は大文字と小文字を区別しません。例えば「abc」と「ABC」は同じ値とみなされます。区別したい場合は、EXACT関数とSUMPRODUCT関数を組み合わせます。具体例として、=ARRAYFORMULA(SUM(--EXACT(A$2:A2, A2)))>1 という数式を使うと、大文字小文字を区別した重複判定が可能です。この数式は各行で累積出現回数を計算し、2回目以降を重複として表示します。

重複データ抽出方法の比較

まとめ

この記事では、COUNTIF関数と条件付き書式を組み合わせて重複データを抽出・表示する方法を解説しました。COUNTIFで重複を判定し、条件付き書式で色を付ければ、データの重複を素早く発見できます。さらにフィルタを使えば、重複行だけを抽出して処理することも可能です。これらのテクニックは日常のデータ管理に非常に役立ちますので、ぜひ実際のシートで試してみてください。QUERY関数を使ったより高度な抽出方法にも挑戦してみるとよいでしょう。