Googleスプレッドシートで重複する値だけ色を付けたい|COUNTIFと条件付き書式の設定

事務職・OA・業務改善

Googleスプレッドシートで同じ受付番号が入ったセルだけに色を付けるには、条件付き書式の[カスタム数式]でCOUNTIFを使います。番号が2件以上あるとその番号のセルすべてに色が付きます。空欄を除く条件も加えれば入力済みの番号だけを確認できます。

A列で重複する受付番号に色を付ける

共同編集の台帳は[ファイル]→[コピーを作成]で作業用のコピーを用意して試しましょう。同姓同名の別人もいるため、応募者名簿では氏名より受付番号などの一意に割り当てた列を使います。

ここではA1が見出しでA2からA100までに受付番号が入る表を例にします。パソコンでGoogleスプレッドシートを開いて次の順に操作してください。

  1. 色を付けたい範囲 A2:A100 を選ぶ。
  2. [表示形式]→[条件付き書式]を開く。右側の[範囲に適用]が A2:A100 になっていることを確認する。
  3. [セルの書式設定の条件]で[カスタム数式]を選び、次の式を入力する。
=AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1)

[書式設定のスタイル]で背景色を選んで[完了]を押します。たとえば下の表では 受付-101 が2件あるためA2とA4に色が付きます。

セル 受付番号の例 色付け
A2 受付-101 色が付く
A3 受付-102 色が付かない
A4 受付-101 色が付く
A5 空欄 色が付かない

COUNTIFの範囲と空欄を除く条件の読み方

基本の式は =COUNTIF($A$2:$A$100,A2)>1 です。COUNTIF がA2と同じ値を範囲内で数えます。自分自身も1件に含まれるので >1 が「同じ値が2件以上」の条件になります。

  • $A$2:$A$100 は数える範囲です。どの行を判定するときもA2からA100までを調べるように $ で固定します。
  • A2 は今見ているセルです。適用範囲の先頭をA2にすると次の行ではA3、その次はA4を基準に判定します。
  • A2<>"" はセルが空欄でないという条件です。AND で重複の条件と組み合わせると空欄に色が付きません。

データがA3から始まる表なら適用範囲と数える範囲を3行目からにして、式の判定セルも A3 に合わせます。最初のセルがずれると見たい行とは別の行の値で判定します。

この設定では最初に出てきた番号にも色が付きます。2件目以降だけ見つけたい場合は COUNTIF($A$2:A2,A2) のように先頭から現在の行までを数えます。店舗コードと受付番号の両方が一致する行を探す場合はCOUNTIFS などで両方の列を条件にします。

行を追加したときは適用範囲と数える範囲をそろえる

A101以降に受付番号を追記すると A2:A100 のままでは新しい行を判定できません。300行目まで使うなら[範囲に適用]を A2:A300 にして、式の数える範囲も $A$2:$A$300 に広げます。色を付ける場所と重複を数える場所を一緒に見直しましょう。

条件付き書式は対象が広いほど計算が増えます。行数が多い台帳では当面使う行数を目安に範囲を決めてください。

色が付いた番号を直す前に確認すること

COUNTIF は大文字と小文字を区別しないため AB-101 と ab-101 も同じ番号として数えます。取り込んだ番号の前後にスペースが残っていると別の値として扱われることがあります。スペースだけが入ったセルも空欄に見えるため、気になるセルは数式バーで中身を確認してください。

先頭に0が付く番号では見た目と実際の値も確認します。表示形式で数値の 12 を 0012 と見せても値は 12 です。数字だけの文字列もCOUNTIFで数値として比較されることがあります。12 と 0012 を別番号として扱う台帳では両方を作業用のコピーに入れて判定を確かめてください。

色が付いたら申込記録や受注データと照合し、二重入力なのかを確かめてから台帳を直します。確認中に元の台帳の値を誤って上書きした場合は、変更したセルを確認してから編集履歴をたどってください。

Googleスプレッドシートを上書きした|セルの編集履歴と過去の版から戻す方法
Googleスプレッドシートのセルを誤って上書きしたときの戻し方。セルの編集履歴で以前の値と編集者を確認し、過去版をコピーして一部だけ復旧する方法と全体復元の注意点を解説。

参考資料

公式資料の確認日 2026年11月6日