Excelで空白に見えるのにCOUNTBLANKで0になる・空白判定されない原因と対処法

スポンサーリンク

「明らかに空白なのにCOUNTBLANKで0が返る」「ISBLANKがTRUEにならない」――そんな経験はありませんか。

Excelの「空白」には、見た目では区別できない5つのパターンがあります。これを知らないまま集計すると、件数のズレや空白判定ミスが発生してしまいます。

この記事では、空白に見えるのに空白判定されない5つの原因と、それぞれの対処法をまとめて紹介します。LEN関数とCODE関数を使った診断手順、判定数式の早見表、VBAでの一括クレンジングコードまで一気通貫で解説します。

  1. Excelで空白に見えるのにCOUNTBLANKで0になる5つの原因
    1. 原因1: 数式が返す空文字列（””）
    2. 原因2: 半角スペースや全角スペース
    3. 原因3: セル内改行（Alt+Enter）だけが入っている
    4. 原因4: ハードスペース（NBSP / Chr(160)）
    5. 原因5: 文字列として入力された見えない文字
  2. 「本当の空白」と「見た目だけの空白」の違いを理解する
    1. ISBLANKは「中身が完全に何もない」だけTRUE
    2. COUNTBLANKは「ISBLANK + 空文字列」もカウント対象
    3. COUNTAは「空文字列を返す数式」も非空とみなす
    4. 判定基準の違いを一覧表で整理
  3. 原因を特定する3つの診断手順
    1. 手順1: LEN関数で文字数を表示する
    2. 手順2: CODE関数で先頭1文字の文字コードを確認する
    3. 手順3: 数式バーで実体を直接見る
  4. 原因別の対処法
    1. 対処1: 数式の空文字列””を消すには
    2. 対処2: スペースを削除するTRIM関数
    3. 対処3: 改行を削除するCLEAN関数
    4. 対処4: NBSPを削除するSUBSTITUTE関数
    5. 対処5: 一括クレンジングする組み合わせ式
  5. 空白判定数式の早見表
    1. 5パターン × 5判定数式のクロス表
    2. 状況別の推奨判定式
    3. 空白セルの件数を正しく数える
  6. VBAで不可視空白を一括クレンジングするコード
    1. 選択範囲の全セルから不可視空白を除去するマクロ
    2. 実行時の注意点
  7. よくあるトラブルとQ&A
    1. TRIMを使っても空白が消えない
    2. フィルターで「空白セル」が出てこない
    3. 数式バーには何も見えないのにISBLANKでFALSEになる
    4. COUNTBLANKが想定より多くカウントされる
    5. 列幅が狭いと「####」と表示される
  8. まとめ

Excelで空白に見えるのにCOUNTBLANKで0になる5つの原因

最初に、空白に見えるのに空白扱いされない5つのパターンを整理します。

パターン中身文字コード主な発生源
① 空文字列""（長さ0の文字列）–=IF(...,"OK","") のような数式
② 半角スペーススペース1個以上32手入力・前処理ミス
③ 全角スペーススペース1個以上12288日本語入力モードでの誤入力
④ セル内改行LF10Alt+Enter による改行
⑤ NBSP（ハードスペース）ノーブレークスペース160Webサイトからのコピペ

それぞれ詳しく見ていきましょう。

原因1: 数式が返す空文字列（””）

もっとも頻発するのが、数式が返す空文字列です。

=IF(A1>0,"OK","") のように「該当しないときは空白を表示したい」という設計で広く使われています。このセルは見た目には何もないものの、内部的には「長さ0の文字列」が入っている状態です。

"" は数式の結果として表示が空に見えるだけで、セルそのものは「空ではない」状態です。

原因2: 半角スペースや全角スペース

セルに半角スペース " " や全角スペース "　" が1つでも入っていると、それは「文字が入っている」状態になります。

手入力でうっかり入力したり、CSV取り込み時に余分なスペースが付いてきたりと、発生源は多岐にわたります。同僚から受け取ったデータに混ざっていることも珍しくありません。

半角スペースは文字コード32、全角スペースは文字コード12288です。どちらも見た目は「何もないように見える」状態です。

原因3: セル内改行（Alt+Enter）だけが入っている

セル内で Alt+Enter を押すと改行（LF・文字コード10）が挿入されます。

文字を入れずに改行だけ入力してしまうと、セルは空白に見えても改行コードが残っている状態です。

特に住所欄や備考欄でセル内改行を活用するワークブックに多いパターンで、見た目では絶対に判別できません。

原因4: ハードスペース（NBSP / Chr(160)）

NBSP（Non-Breaking Space・文字コード160）は「改行で区切られない特殊なスペース」です。

Webサイトの表をコピペしたり、Wordから貼り付けたりしたデータに頻繁に混入します。見た目は半角スペースとまったく同じですが、TRIM関数で除去できないので非常に厄介です。

原因5: 文字列として入力された見えない文字

タブ文字（文字コード9）やキャリッジリターン（文字コード13）など、ASCII制御文字が紛れているケースもあります。

これも見た目では判別できず、原因2〜4と同じく「空白に見えるが文字データが入っている」状態を作ります。

「本当の空白」と「見た目だけの空白」の違いを理解する

5つのパターンを知ったところで、なぜCOUNTBLANKとISBLANKで結果が変わるのか整理します。判定基準の違いを確認しましょう。

ISBLANKは「中身が完全に何もない」だけTRUE

ISBLANK関数は、対象のセルが「真の空白」のときだけTRUEを返します。

数式の結果として "" を返しているセルや、スペース・改行が入っているセルは、すべてFALSE扱いです。「本当に何も入っていない」セルだけを抽出したいときに使う関数だと覚えておいてください。

詳しい仕様は ExcelのISBLANK関数の使い方 で解説しています。

COUNTBLANKは「ISBLANK + 空文字列」もカウント対象

COUNTBLANKの判定はISBLANKより少し広めです。「真の空白」と「="" を返している数式セル」の両方をカウント対象として扱います。

つまり原因1（空文字列）はCOUNTBLANKの対象内、原因2〜5（スペース・改行・NBSP）は対象外という挙動です。

NOTE

Microsoft公式のCOUNTBLANK関数仕様でも、「空のセルの数を返す」と説明されつつ、"" を返す数式セルも含まれることが明記されています。
出典: COUNTBLANK 関数

COUNTAは「空文字列を返す数式」も非空とみなす

COUNTAは「空白以外のセルの数」を数える関数です。"" を返す数式セルも「空白ではない」と扱う点に注意が必要です。

真の空白だけを除外し、それ以外（空文字列も含む）はすべてカウントする、というのがCOUNTAの仕様です。

判定基準の違いを一覧表で整理

代表的な5パターンと、5つの判定方法の組み合わせを表にまとめました。

セルの状態ISBLANKCOUNTBLANKCOUNTA=A1=""LEN(A1)=0
① 真の空白TRUEカウント非カウントTRUETRUE
② 空文字列 ""FALSEカウントカウントTRUETRUE
③ 半角スペースFALSE非カウントカウントFALSEFALSE
④ 全角スペースFALSE非カウントカウントFALSEFALSE
⑤ 改行のみ Chr(10)FALSE非カウントカウントFALSEFALSE
⑥ NBSP Chr(160)FALSE非カウントカウントFALSEFALSE

この表を見ると、「自分はどの判定基準で数えたいのか」を意識することの重要性が見えてきます。

原因を特定する3つの診断手順

「空白に見えるけど中身が分からない」セルにぶつかったとき、まずやるべき診断手順を3つ紹介します。

手順1: LEN関数で文字数を表示する

もっともシンプルな診断方法が、ExcelのLEN関数の使い方 を使った文字数チェックです。

空いている列に次の数式を入れます。

=LEN(A1)

返ってくる値の意味は次の通りです。

  • 0: 真の空白、または "" を返す数式
  • 1 以上: 何か文字（スペース・改行・NBSP含む）が入っている

LEN関数が「0以外」を返したら、見えない何かがそのセルに入っているサインです。次の手順2に進みましょう。

手順2: CODE関数で先頭1文字の文字コードを確認する

LEN関数で「0以外」が返ったセルに対して、次は ExcelのCODE関数の使い方 で先頭1文字の文字コードを取得します。

=CODE(A1)

返ってきた数値で、何が入っているかが分かります。

戻り値正体
9タブ
10改行（LF）
13キャリッジリターン
32半角スペース
160NBSP（ハードスペース）
12288全角スペース

たとえば 160 が返ってきたら、原因はNBSPだと一発で特定できます。

手順3: 数式バーで実体を直接見る

少しアナログな方法ですが、診断対象のセルを選択し、数式バーをクリックしてカーソルを置く方法も有効です。

矢印キーで左右にカーソルを動かすと、改行やスペースが1文字ずつ存在するのが感覚的に分かります。改行（Alt+Enter）の場合は、カーソルが「下の行」に移動するため、改行の存在をすぐに確認できます。

原因別の対処法

原因が特定できたら、それぞれに合った対処法で空白を整えていきます。

対処1: 数式の空文字列””を消すには

数式が "" を返しているセルを「真の空白」にするには、数式そのものを削除するしかありません。

ただし、集計だけが目的なら次のような工夫で対応できます。

  • COUNTBLANK で数える → "" も空白扱いなので問題なし
  • ISBLANK で判定 → =OR(ISBLANK(A1), A1="") のように複合条件にする
  • LEN で判定 → =LEN(A1)=0 を使う

「数式の出力を空白として扱いたい」という設計なら、COUNTBLANKやLEN=0で対応するのが現実的です。

対処2: スペースを削除するTRIM関数

半角スペースや全角スペースを除去するには、ExcelのTRIM関数の使い方 が便利です。

=TRIM(A1)

TRIM関数は「前後のスペース」と「文字間の連続スペース」を取り除きます。半角・全角どちらにも対応していて、データクレンジングの定番関数です。

WARNING

TRIM関数はNBSP（文字コード160）を除去できません。半角スペース（32）・全角スペース（12288）が対象で、NBSPには別の対処が必要です。

対処3: 改行を削除するCLEAN関数

セル内改行（Chr(10)）や、その他のASCII制御文字を除去するには ExcelのCLEAN関数の使い方 が便利です。

=CLEAN(A1)

CLEAN関数は、印刷できない制御文字（ASCII 0〜31）をまとめて消してくれます。改行・タブ・キャリッジリターンに対応しているので、CSV取り込み時のクレンジングにも役立ちます。

対処4: NBSPを削除するSUBSTITUTE関数

NBSP（Chr(160)）はTRIMでもCLEANでも除去できません。専用に ExcelのSUBSTITUTE関数の使い方 を使って置換します。

=SUBSTITUTE(A1, CHAR(160), "")

CHAR(160) でNBSPを表現し、空文字列に置き換えるという発想です。Webからコピペしたデータの前処理で必須のテクニックです。

対処5: 一括クレンジングする組み合わせ式

5つのパターンをまとめて処理する万能クレンジング式が、次の3つの関数の組み合わせです。

=TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), "")))

処理の流れは次の通りです。

  1. SUBSTITUTE で NBSP を除去
  2. CLEAN で改行・タブなどの制御文字を除去
  3. TRIM で半角・全角スペースを除去

この順序にすることで、NBSPを先に消してから他の空白も除去できます。漏れなく不可視文字を取り除けるので覚えておきましょう。

空白判定数式の早見表

「自分の目的にはどの数式を使えばいいのか」を、早見表で整理します。

5パターン × 5判定数式のクロス表

各判定式で、5パターンがどう扱われるかを再掲します。

やりたいこと推奨数式
真の空白だけ判定=ISBLANK(A1)
真の空白 + 空文字列を空白扱い=LEN(A1)=0 または =A1=""
真の空白 + 空文字列 + スペースのみを空白扱い=LEN(TRIM(A1))=0
真の空白 + 空文字列 + 改行も含めて空白扱い=LEN(TRIM(CLEAN(A1)))=0
真の空白 + 空文字列 + すべての不可視文字を空白扱い=LEN(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160),""))))=0

状況別の推奨判定式

  • 「未入力チェックだけしたい」: =ISBLANK(A1)
  • 「数式の空白も含めて未入力扱いしたい」: =LEN(A1)=0
  • 「コピペデータのクレンジングで空白判定したい」: =LEN(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160),""))))=0

空白セルの件数を正しく数える

スペースや改行も含めた「実質空白」の件数を数えたいときは、次の数式が便利です。

=SUMPRODUCT(--(LEN(TRIM(CLEAN(SUBSTITUTE(A1:A10,CHAR(160),""))))=0))

ExcelのCOUNTBLANK関数の使い方 や ExcelのCOUNTA関数の使い方 と組み合わせれば、用途別の正確な件数が出せます。

VBAで不可視空白を一括クレンジングするコード

大量のデータを一気に整えたいときは、VBAでマクロを組むのが効率的です。

選択範囲の全セルから不可視空白を除去するマクロ

選択範囲にこのコードを実行すると、5パターンの不可視空白がすべて削除されます。

Sub CleanBlanks()

    Dim rng As Range   '--- 範囲ループ用 ---
    Dim v As String    '--- 一時保持文字列 ---

    For Each rng In Selection
        If VarType(rng.Value) = vbString Then
            v = rng.Value
            v = Replace(v, Chr(160), "")  '--- NBSPを削除 ---
            v = Replace(v, Chr(10), "")   '--- LF（改行）を削除 ---
            v = Replace(v, Chr(13), "")   '--- CRを削除 ---
            v = Replace(v, Chr(9), "")    '--- タブを削除 ---
            v = Trim(v)                   '--- 前後の半角スペースを削除 ---

            If v = "" Then
                rng.ClearContents          '--- 完全に空にして真の空白へ ---
            Else
                rng.Value = v
            End If
        End If
    Next rng

    MsgBox "クレンジング完了"

End Sub

VBAの空白削除関連は ExcelVBA 空白削除まとめ でもまとめて解説しています。応用したい方は併せてご覧ください。

実行時の注意点

  • VarType で文字列セルのみ判定しているので、数式セルや数値セルはスキップされます
  • 全角スペースは Trim では除去されません。除去したい場合は v = Replace(v, "　", "") を追加してください
  • 元データを残したい場合は、実行前にシートをコピーしておくことをおすすめします

よくあるトラブルとQ&A

最後に、現場でよく聞かれる質問にQ&A形式で答えていきます。

TRIMを使っても空白が消えない

→ 原因はNBSP（文字コード160）の可能性が高いです。CODE関数で文字コードを確認してください。160が返ってきたら =SUBSTITUTE(A1, CHAR(160), "") で置換します。

フィルターで「空白セル」が出てこない

→ Excelのオートフィルターは「空白セル」をISBLANK基準で判定します。そのため "" を返す数式セルや、スペースの入ったセルはヒットしません。

先に万能クレンジング式 =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160),""))) を作業列に作成し、その結果が空のセルをフィルターしてください。

数式バーには何も見えないのにISBLANKでFALSEになる

→ Chr(160) や Chr(10) などの不可視文字が入っている可能性があります。LEN関数で文字数を確認し、0以外ならCODE関数で文字コードを特定してください。

COUNTBLANKが想定より多くカウントされる

→ =IF(...,"...","") のような空文字列を返す数式セルがCOUNTBLANKに含まれているからです。真の空白だけ数えたいときは =SUMPRODUCT(--ISBLANK(A1:A10)) を使ってください。

列幅が狭いと「####」と表示される

→ こちらは空白判定とは別の問題です。列幅不足のときに発生します。詳細は Excelのセルに「####」が表示される原因と直し方 で解説しています。

まとめ

Excelで「空白に見えるのにCOUNTBLANKで0になる」原因は、次の5つに集約されます。

  1. 数式が返す空文字列 ""
  2. 半角スペース・全角スペース
  3. セル内改行（Alt+Enter）
  4. ハードスペース（NBSP / Chr(160)）
  5. その他の制御文字（タブ・キャリッジリターンなど）

これらの正体を見抜くには、LEN関数で文字数を測り、CODE関数で文字コードを特定するのが鉄則です。

対処は原因別に使い分けます。

  • 空文字列 → 集計式の側で LEN=0 判定
  • スペース → TRIM
  • 改行・制御文字 → CLEAN
  • NBSP → SUBSTITUTE + CHAR(160)
  • 一括クレンジング → =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160),"")))

VBAを使えば、選択範囲の不可視空白をまとめて掃除することもできます。

「明らかに空白なのに件数が合わない」と感じたら、まずはLEN関数で文字数チェック。そこから先は今回紹介した手順で、原因を一つずつ潰していきましょう。

データクレンジングの基本としてとても役立つ知識なので、ぜひブックマークしてご活用ください。

スポンサーリンク
タイトルとURLをコピーしました