Googleスプレッドシートで条件付き書式を使って「重複しているセルに色をつけたい」という時、 =COUNTIF(A:A, A1)>1という式を設定するのはよくあるパターンです。
しかし、この式を設定したところ、 なぜか「あ」だけに色をつけたいのに、「ア」や「あいう」まで色がついてしまう…こんな事起きたことないですか？
本記事は、そのような「意図しない色付け」が起きる原因と、完全一致で判定するための式への修正方法を解説します。
条件付き書式でCOUNTIFを使うと意図しないセルに色がつく現象
たとえば A 列に一斉の文字を一覧し、=COUNTIF(A:A, A1)>1と条件付き書式を設定します。
すると、本来重複しているはずのない
- カタカナ (ア)
- 複数文字の文字列 (あいう)
- 正体文字ではないが一見同じに見える文字 (は + ゛, ZWSP 含むあなど)
にも色がついてしまいます。
このような「意図しない色付け」が起こると、スプレッドシートに奇妙なバグがあるのでは？と思われがちです。
なお、以下の画像では一部のセルが色付けされていますが、たとえば23行目の「あ」には ゼロ幅スペース（ZWSP） が後ろに含まれています。 見た目では判別できませんが、これも COUNTIF の条件付き書式では「一致している」とみなされ、色がついてしまっています。こうした不可視文字の存在も、条件付き書式の誤判定の一因となることがあります。
COUNTIFが部分一致してしまう理由とは？曖昧一致の正体
Google スプレッドシートの COUNTIF 関数は、条件として正確な文字列を依須したはずのところ、 条件価を補完的に「部分一致」として解釈する場合があります。
そのため
あはあいうやああに中に含まれているため、色がつくあはア(カタカナ)やｧあ(ゼロ幅スペース付き)と同一視される
など、想定していないものまで色つきの対象として扱われます。
COUNTIFで誤判定を防ぐには？完全一致で色付けする方法【EXACT関数】
これを固く防ぐには COUNTIF を使わず、EXACT を使う方法が最も確実です！
=AND(A1<>"", SUM(ARRAYFORMULA(--EXACT(A:A, A1))) > 1)EXACTで「文字コードまで完全一致」を判定--は TRUE/FALSE を 1/0 に変換SUMで同じ文字の個数を集計
これによって、本当に同じ文字のみを色付け対象にできます。
ExcelのCOUNTIFはなぜ問題が起きないのか？スプレッドシートとの違いを比較
同じ式を Microsoft Excel で設定した場合は、このような誤色付けは起きません。
COUNTIFは完全一致で判定される- 部分一致は
"*xxx*"のように明示的にワイルドカードを指定したときのみ
これはすなわち
「Googleスプレッドシート特有の振る舞い」
と言える現象です。
おまけ：条件付き書式のバグ？セルが選択状態に見える謎現象の正体
条件付き書式を設定したあと、一部のセル（例：5行目の「ああ」など）が「選択されているように見える」ことがあります。
これは実際には選択されているのではなく、スプレッドシート内部で条件付き書式の描画順がずれ、塗りつぶしが最前面に浮いてしまっているだけの視覚的バグのような挙動です。
- 実際のアクティブセルが別列にあるのに、特定のセルが強調表示されるように見える
- スプレッドシートでは時々発生する仕様寄りの現象
このようなときは、一度条件付き書式を削除して再設定すると多くの場合で改善します。
条件付き書式を削除したのに色が残るときの対処法
条件付き書式を削除したのに色が残る場合、以下のようなケースが考えられます。
- 一時的にスプレッドシートの描画キャッシュが残っている
- セルに手動で塗りつぶしが設定されていた
- 条件付き書式の影響がUI上でリセットされていない
✔ 対処法
- セルを選択 →「塗りつぶしなし」で明示的にリセット
- シートを閉じて再度開き直す（またはブラウザ再読込）
- 新しいシートに値だけコピーして確認（
Ctrl + Shift + V）
まとめ
- Googleスプレッドシートの
COUNTIFは条件価を更新する際に、一部の文字で部分一致のような挙動をしてしまうことがある - 文字コードの違いや長さ、不可視文字(ZWSP)なども無視される可能性
- これを固く防ぐには
EXACT関数 +ARRAYFORMULA+SUMで「本当の重複」を判定しよう - Excelとの違いも知っておくと、現象に悲しまずに済む
この記事は、Googleスプレッドシートで一斉文字データや委託データを扱う中で実際に発生した現象をもとに、実験して分かったことを簡潔にまとめています。
同じような現象に誤魔している人の手助けになれば幸いです。
