カウントイフで空白以外だけ数えるには？正規表現と代替式

表の集計で一番つまずきやすいのが空白の扱いです。
空白に見えても実は空文字やスペースだけのセルが混ざっており、思ったようにカウントできないことが多いです。
本記事では、カウントイフで空白以外を数える正攻法と、空白に見える特殊ケースを潰す実務向けレシピを体系的に整理します。
ワイルドカードと正規表現の違い、COUNTIFSやSUMPRODUCTの使い分け、可視行のみの集計など、すぐに使える最新情報です。

カウントイフで空白以外を数える基本と落とし穴

まずは基本の狙いと注意点を整理します。
カウントイフは条件に合致するセルの個数を返しますが、空白の定義を誤解すると結果がずれます。
空白とは未入力の空セルと、数式が返す空文字の両方が関係します。
加えて、スペースだけのセルは見た目が空白でも非空白として扱われます。

COUNTIFの基本構文と判定の仕組み

基本構文は範囲と条件の二要素です。
非空白を数えるとき、演算子の組み合わせで判定を作ります。
カウントイフはワイルドカードを解釈しますが、条件が文字列である点に留意が必要です。
数式が返す空文字は、カウントブランクやカウントイフの判定で空として扱われます。

ダイレクトに非空白を数える安定解は、文字数で見る方法です。
範囲の文字数がゼロより大きいかで判定すれば、演算子の文字列化を避けられます。
例として次の式が実務で堅牢です。
=SUMPRODUCT(--(LEN(A2:A100)>0))

<>と空文字の違いと推奨記法

非空白の条件は不等号の組み合わせを使うのが定石です。
ただし条件は文字列であり、直接的な記述では記号を文字列化する必要があります。
ダブルクォーテーションを式に直書きしない記法として、次の組み立てが安全です。
=COUNTIF(A2:A100, UNICHAR(60)&UNICHAR(62)&CHAR(34)&CHAR(34))

上記は不等号の組み合わせと空文字を文字コードで構築しています。
演算子と空文字の双方を関数で生成するため、文字の直書きに依存しません。
関数の意味は、UNICHARが記号、CHARが制御文字を返すものです。
扱いに迷う場合はLEN方式を推奨します。

空白に見えるが空白ではないケース

以下は非空白として数えられやすい落とし穴です。
セルに半角スペースのみ、全角スペースのみ、改行のみ、ノーブレークスペース、不可視制御文字が入っている場合です。
これらは見た目が空でも値は存在するため、非空白として判定されます。
除外したい場合は、スペース系の除去を組み合わせたLEN判定が必要です。

ワイルドカードと正規表現の違いを理解する

パターン一致で混乱しやすいのが、ワイルドカードと正規表現の違いです。
両者は別物で、カウントイフが理解するのはワイルドカードのみです。
高度なパターンは別手段と組み合わせるのが現実解です。

Excelのワイルドカードの挙動

アスタリスクは任意の文字列、クエスチョンは任意の一文字、波形記号はエスケープに使います。
ワイルドカードは文字列マッチに対して働くため、数値は対象外になりやすい点に注意します。
文字列としてのアスタリスクはCHARで作れます。
例として、テキストを含むセルを数えるには次のようにします。
=COUNTIF(A2:A100, CHAR(42))

ただしこの手法は数値セルを含めた総数ではなく、テキストのみのカウントです。
非空白の総数を狙う場合はLEN方式のほうが確実です。
要件に応じて使い分けてください。

COUNTIFで正規表現は使えるか

カウントイフは正規表現を解釈しません。
正規表現が必要な場合は、別の関数や機能と組み合わせる必要があります。
例えば、FILTERやSEARCH、TEXTSPLIT、LETなどで事前に対象を絞る構成が有効です。
VBAやPower Queryを使うと正規表現が扱えますが、式のみで完結したい場合は代替ロジックに寄せます。

正規表現が必要なときの現実的な代替

正規表現の多くはLENとSUBSTITUTEの組み合わせで代用可能です。
特定の記号やスペースを除去してから長さで判定するのが基本です。
また、数値とテキストの型判定を織り込むと精度が上がります。
たとえば、スペースのみの見せかけ入力を除外した非空白カウントは次で実現します。
=SUMPRODUCT(--(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A2:A100),CHAR(32),$Z$1),UNICHAR(12288),$Z$1),CHAR(160),$Z$1))>0))
上式ではZ1を空セルとしておき、置換の新文字列に使います。

空白以外かつ条件付きで数える応用レシピ

現場では非空白に加えて更なる条件を課す場面が多いです。
ここではよくある要件をそのまま使える式でまとめます。

数値も文字列も対象にする

単純な非空白の総数には次を使います。
=SUMPRODUCT(--(LEN(A2:A100)>0))
文字列と数値の両方を自然に拾います。
数式が返す空文字は長さゼロとなるためカウントされません。

空白とゼロを除外する

未入力や空文字、さらにゼロを除外したい場合は複合条件にします。
COUNTIFSの構成例です。
=COUNTIFS(A2:A100, UNICHAR(60)&UNICHAR(62)&CHAR(34)&CHAR(34), A2:A100, UNICHAR(60)&UNICHAR(62)&0)
上式は非空白かつゼロ以外を数えます。
文字列のゼロと数値のゼロを分けたい場合は、ISNUMBERやISTEXTとSUMPRODUCTを組み合わせます。

SUMPRODUCTでの記述例です。
=SUMPRODUCT(--(LEN(A2:A100)>0), --(A2:A100<>0))
数値でゼロのセルを除外しつつ、空文字は長さで弾きます。

全角スペースや不可視文字を除外

全角スペース、ノーブレークスペース、制御文字を取り除いてから判定します。
TRIMとCLEANだけでは不足することがあるため、置換を追加します。
=SUMPRODUCT(--(LEN(CLEAN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A2:A100),UNICHAR(12288),$Z$1),CHAR(160),$Z$1),CHAR(32),$Z$1)))>0))
Z1は空セルです。
これで見た目の空白だけのセルを除外できます。

複数列で一行でも入力がある行数を数える

B列からD列のいずれかに入力があれば行をカウントする例です。
配列の行ごと合計で判定します。
=SUMPRODUCT(--(MMULT(--(LEN(B2:D100)>0), TRANSPOSE(COLUMN(B2:D2)^0))>0))
各行の非空白セル数が一つ以上かを判定して合計します。

フィルター後の可視行だけを数える

オートフィルターや手動で非表示にした行を除いて、見えている行だけを対象にします。
SUBTOTALの103とOFFSETを組み合わせるのが定番です。
=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2, ROW(A2:A100)-ROW(A2), 0)), --(LEN(A2:A100)>0))
SUBTOTALの103はCOUNTA相当の可視判定で、LENによる非空白条件と掛け合わせています。

COUNTIFS/COUNTA/COUNTBLANKとの使い分け

非空白の集計は複数の関数で達成できます。
それぞれの定義の違いを理解すると誤差の原因を切り分けやすくなります。

COUNTAとCOUNTIFの違い

COUNTAは空でないセルの個数を返します。
カウントイフで非空白を表現するのとほぼ同義ですが、条件追加がしにくい点で劣ります。
複数条件をかけたいならCOUNTIFSやSUMPRODUCTが適します。

COUNTBLANKが数える空白の定義

COUNTBLANKは未入力セルと空文字を数えます。
ISBLANKと異なり、数式が返す空文字も空白として扱う点が重要です。
非空白の数を求めたい場合、総行数からCOUNTBLANKを引く方法もあります。
ただしスペースのみのセルは空白ではないため、この方法では除外されません。

COUNTIFSで複合条件と空白除外

COUNTIFSは複数の条件を同じ範囲に重ねられます。
非空白かつ数値のみ、非空白かつ一定の範囲などを作れます。
演算子の文字列化はUNICHARを用いて避けられます。
例として、非空白かつ一桁以上の数値を含むセル数は次のように表現できます。
=SUMPRODUCT(--(LEN(A2:A100)>0), --ISNUMBER(A2:A100))

Googleスプレッドシートでの空白以外カウント

スプレッドシートでも考え方は同じです。
COUNTIFはワイルドカードを解釈しますが、正規表現は別関数で行います。
LENやREGEXMATCHと組み合わせると柔軟です。

シートのCOUNTIFの仕様

スプレッドシートのCOUNTIFも条件は文字列です。
非空白の総数はLEN方式が簡潔で高速です。
=SUMPRODUCT(--(LEN(A2:A100)>0))
ワイルドカードの利用はExcelと同様ですが、数値判定はISTEXTやISNUMBERを併用すると明確です。

REGEXMATCHと組み合わせる方法

正規表現はREGEXMATCHで判定し、FILTERやBYROWと組み合わせます。
正規表現パターンはセルに置くと再利用と管理がしやすくなります。
例えばB1に改行や空白のみを表す正規表現として、先頭末尾が空白の行を示すパターンを入力します。
次のようにB1のパターンを参照して非空白行を数えます。
=SUM(--(BYROW(A2:A100, LAMBDA(r, NOT(REGEXMATCH(IFNA(TO_TEXT(r), ""), $B$1))))))
パターンの管理をセル参照に逃がすことで、式の可読性が上がります。

Apps Scriptを使わない即戦力の式

スクリプト不要でできる範囲は広いです。
空白に見える文字を除外した非空白カウントは、置換と長さで対応します。
=SUMPRODUCT(--(LEN(REGEXREPLACE(TO_TEXT(A2:A100), "s|　", ""))>0))
正規表現が使える環境では空白類の除去が一行で記述でき、保守が容易です。

トラブルシューティングと検証のコツ

期待と違う結果になったときの切り分けはパターン化しておくと効率的です。
チェックポイントを順に確認し、再現用の小さなデータで検証します。

期待と結果がズレるときのチェックリスト

• 空白の定義は未入力と空文字とスペースのどこまで含むか
• 対象範囲に数式が含まれていないか
• 全角スペースやノーブレークスペースが混在していないか
• 数値と文字列の混在による型違いがないか
• フィルター等で非表示行が紛れていないか

サンプルデータでの再現手順

小さな表を別シートに作り、未入力、空文字、半角スペース、全角スペース、数値、テキストを各1件ずつ配置します。
各セルに対してLEN、ISTEXT、ISNUMBER、CODEを併記して状態を可視化します。
その上で候補の式を並べて結果が意図と一致するかを確認します。
再現性が取れた式を本番の範囲に展開します。

式の汎用化と命名のテクニック

置換に使う空セルはZ1のような固定セルを用意し、名前定義でBlankと名付けます。
式中では$Z$1の代わりにBlankと書けるため、表が広がっても参照が壊れません。
同様に演算子を作るUNICHAR(60)&UNICHAR(62)は、NotEqualという名前で定義しておくと再利用が容易です。
名前定義は保守性を高め、属人化を防ぎます。

よくある質問

現場で頻出する疑問に先回りで回答します。
理解のギャップを素早く埋めることができます。

空白の定義はどれが正しいのか

正しい定義は要件依存です。
未入力のみを空白とするのか、空文字を含めるのか、スペースのみを含めるのかで式が変わります。
境界の取り方を先に合意し、検証用の最小データで一致を確認してから本番に適用してください。

ダッシュやNAは空白扱いか

ダッシュやNAといった記号やエラーは値が存在するため、空白ではありません。
除外したい場合は追加条件を課します。
例えば非空白かつエラーを除くなら、ISERRORを用いた除外やIFERRORでの前処理が有効です。
LENベースに加え、NOT(ISERROR())の掛け合わせで対処できます。

絶対参照と範囲の拡張の注意

配列計算を含む式は行や列の追加で参照がずれると結果が変わります。
範囲はテーブル化や名前定義で管理し、絶対参照に固定するのが安全です。
動的配列を使う場合はアンカーとなる先頭セルを明確にし、見出し行を含めないように注意します。

まとめ

非空白のカウントは、要件に合わせて手段を選ぶのが最短です。
単純な総数ならLENでの長さ判定、条件追加はCOUNTIFS、柔軟性が必要ならSUMPRODUCTが有効です。
空白に見える文字を除外したい場合は、スペース類の除去を前処理として組み込みます。
ワイルドカードは便利ですが、正規表現は別機能で補完するのが現実的です。
本稿のレシピをテンプレート化すれば、集計の再現性と保守性が大幅に向上します。