エクセルでスペース入力セルをカウントしない方法

counta関数で、セルに何らかの文字、数字が入力されているセルの個数をカウントできますが、スペースが入力されているセルもカウントされてしまいます。
スペース（複数回入力されている場合も）が入力されているセルをカウント対象外とする方法はありますか？

No.1 回答者： van62097 回答日時： 2013/04/24 19:07

簡単に求めるなら、スペースのセルをカウントして、全体のカウント数から引くと結果が得られますよ。

No.2 回答者： pidum 回答日時： 2013/04/24 19:38

下記式で、半角スペース、全角スペースを含むセルをカウントから外せます。

=COUNTA(A1:A7)-COUNTIF(A1:A7,"* *")-COUNTIF(A1:A7,"*　*")

No.3 回答者： MackyNo1 回答日時： 2013/04/24 22:47

スペースを含む文字列をすべて削除すると「姓　名」のようなスペース入りの文字列も削除対象になります。

半角および全角のスペース（連続を含む）のみカウントから除外したいなら、以下のような数式で対応する必要があります。

=SUMPRODUCT((TRIM(データ範囲)<>"")*1)

この回答へのお礼

不特定多数の人が入力するエクセルシートで、ある基準に合致する場合にそのセルにレ点を入れてもらうことを想定し、その件数をカウントしたいと思っています。
その日その日の発生件数により、１ページで済む場合もあれば、複数ページにわたる場合もあり、A列ならA列に入力されるレ点全てをカウントしたいのです。
ミス入力した場合、そのセルをdeleteで消してくれれば問題ないのですが、パソコンに不慣れな人ってスペースキーを押下して入力した文字を消したりしますよね。
そうするとカウントがくるってしまうので、何とか回避する方法はないものかと思いお尋ねした次第です。
もう少し研究してみますが、何かアイデアがあったらお願いします。
ありがとうございました。

お礼日時：2013/04/25 09:00

No.4 回答者： KURUMITO 回答日時： 2013/04/25 07:25

SUMPRODUCT関数を使うこともできますが計算に負担がかかりますね。

COUNTA関数は見かけ上空白のセルを数えてしまうことです。完全に空白のセルは数えません。式などが入っていてそれにより空白になっているセルなどは数えることになります。
ところでＣＯＵＮＴ関数は数値のみが入力されているセルの数を数えることができます。文字列や式で空白となっているセルは数えません。
一方、COUNTIF関数は文字列の入力されたセルの数を数えることができます。数値や式で空白となっているセルの数は加須ません。
例えばＡ１セルからＡ１０セルの範囲で文字や数値の入力されたセルの数を数えるのでしたら次のような式になります。

=COUNT(A1:A10)+COUNTIF(A1:A10,"?*")

一般には数値のみか文字列のみかの場合が多いので上の式が多く使われることはないでしょう。COUNT関数かＣＯＵＮＴＩＦ関数のどちらかを使いますね。

No.5 回答者： tom04 回答日時： 2013/04/25 08:38

こんにちは！

極端にデータ数が多い場合はオススメできない方法ですが・・・

仮にA1～A100セルの範囲をカウントする場合は
=SUMPRODUCT((SUBSTITUTE(ASC(A1:A100)," ","")<>"")*1)
といった感じにしてみてはどうでしょうか？

上記数式は100行まで対応の数式ですが、1000行程度であれば問題ないと思います。

※　半角・全角スペース両方に対応できるようにしてみました。m（_ _）m

No.6 回答者： tom04 回答日時： 2013/04/25 08:42

No.5です。

投稿後気づきました。
No.3さんの方がすっきりスマートです。

どうも失礼しました。m（_ _）m

No.7 回答者： tom04 回答日時： 2013/04/25 11:15

No.5・6です。

何度もごめんなさい。
データ量がかなり多いというコトですので・・・
VBAになってしまいますが一例です。
（作業用の列を設けてよいのであれば関数だかで可能だと思います）

A列全体をカウントする場合です。
画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, k As Long
i = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
Range("A:A").Insert
With Range(Cells(1, "A"), Cells(i, "A"))
.Formula = "=LEN(TRIM(B1))"
.Value = .Value
End With
k = WorksheetFunction.CountIf(Range("A:A"), ">0")
Range("A:A").Delete
Application.ScreenUpdating = True
MsgBox k & "件" '←タイトル行がある場合はタイトル行分をマイナスする！
End Sub 'この行まで

こんな感じではどうでしょうか？m（_ _）m

No.8 回答者： tsubuyuki 回答日時： 2013/04/25 16:55

> ある基準に合致する場合にそのセルにレ点を入れてもらうことを想定し、

> その件数をカウントしたい
のであれば、素直に「レ（全角）・レ（半角）」を数えたほうが早いのでは？
　　例：=COUNTIF(範囲,"*レ*")+COUNTIF(範囲,"*レ*")


準備が面倒ですが「チェックボックス」を使う手段もあります。
各行にチェックボックスを配置（例えばＡ列）、
それぞれ各行の特定列（例えばＢ列）にリンクさせ、
この特定列（Ｂ列）を対象に「TRUE」の数を数えてやればＯＫ。
　　例：=COUNTIF(範囲,"TRUE")
これでＢ列を非表示にしておけば、視覚的にも邪魔にはなりません。



後者の手段なら、おっしゃる
> スペースキーを押下して入力した文字を消したりする
> パソコンに不慣れな人
にとっても、マウスでのエントリーのみで操作できるのですから、使う側にとっては楽チンです。

No.9 回答者： MackyNo1 回答日時： 2013/04/25 18:30

>不特定多数の人が入力するエクセルシートで、ある基準に合致する場合にそのセルにレ点を入れてもらうことを想定し、その件数をカウントしたいと思っています。

このような特定の文字しか入力しないケースでは手入力するのではなく、ドロップダウンリストから選択させる（特定の値しか入力できないようにする）ことをお勧めします。

「データ」「データの入力規則」で入力値の種類で「リスト」を選択し、元の値の欄に、例えば「レ」と入力します。
ちなみに、2つ以上の入力項目があるときは元の値の欄に「一致,不一致」のようにカンマで区切って入力してください。


なお、キーボード操作でドロップダウンリストから候補を選択するときはAlt＋↓キーの操作をしてください。