スプレッドシートで重複に色を付ける方法｜COUNTIFの条件付き書式

名簿やリストに、同じ名前が2回入っていないか。見落としを減らすために、重複しているセルに自動で色が付くようにする方法を、COUNTIF関数と条件付き書式で紹介します。

■ 完成イメージ

A列に会社名が並んでいて、同じ名前が2回以上出てくる行に、黄色の色が付く表を作ります。色を付けるだけで、データは消しません。

使うのは、条件付き書式と、COUNTIF関数だけです。

■ 手順1：色を付けたい範囲を選ぶ

A2からA100のように、会社名が入っている範囲を選びます（見出しの行は含めません）。

■ 手順2：条件付き書式を開く

メニューの「表示形式」から「条件付き書式」を開きます。右側に設定の画面が出ます。

■ 手順3：カスタム数式を入れる

「セルの書式設定の条件」を「カスタム数式」にして、次の式を入れます。

=AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1)

そのあと、書式のスタイルで、塗りつぶしの色（たとえば黄色）を選び、「完了」を押します。

■ 式の意味

・COUNTIF($A$2:$A$100,A2) は、A2からA100の中に、A2と同じ内容が何個あるかを数えます。

・>1 は、「2個以上ある」という意味です。2個以上なら、その行は重複です。

・A2<>"" は、「A2が空白ではない」という意味です。この条件を付けないと、空白のセルどうしが重複と見なされることがあるため、空白を除いています。

・$A$2:$A$100 の $ は、式がほかのセルに広がっても、数える範囲がずれないようにするための記号です。A2 には $ を付けません。行ごとに、自分のセルを数えるためです。

■ うまく色が付かないとき：前後の空白や、表記のちがい

見た目は同じでも、「山田商事」と「山田商事 」（後ろに空白が1つ）は、COUNTIFでは別のものとして数えられます。重複しているのに色が付かないときは、まず前後の空白を疑ってみてください。

対処は、空白を取り除いた列を作ることです。B2に次の式を入れて、下の行までコピーします。

=TRIM(A2)

TRIM関数は、前後の余分な空白を取り除きます。そのあと、色を付ける範囲をB2からB100に変えて、式を次のようにします。

=AND(B2<>"",COUNTIF($B$2:$B$100,B2)>1)

これで、前後の空白だけが違うデータも、重複として色が付きます。

■ 重複を消したいときは

色を付けるだけでなく、重複そのものを消したい場合は、範囲を選んだうえで、メニューの「データ」にある「データ クリーンアップ」から「重複を削除」を使います。

削除したあとに戻すには、元に戻す操作か、変更履歴を使うことになります。先にシートのコピーを取っておくと安心です。

■ 「株式会社」と「(株)」のような表記のちがいは

COUNTIFは、書かれている文字が同じかどうかで数えます。「株式会社山田商事」と「(株)山田商事」のような表記のちがいは、別のものとして扱われます。こうした表記ゆれは、先に表記を統一してから、重複を調べる必要があります。

自分の表で、重複の確認や表記の統一がうまくいかない場合は、下のサービスから、状況をお知らせください。