Excel（エクセル）活用コミュのCOUNTA関数について

はじめまして。いのっちです。
次の場合の対処方法がわからず困っています。

例えば、最初にA1 B1 C1 D1 に何もデータが入っていません。E1に　COUNTA(A1:D1)という関数を設定すると、0と表示されます。

そして別のシートに次のような関数が設定されています。
A1 B1 C1　には何もデータが入っていません。D1に IF(SUM(A3:C3)=0,"",SUM(A3:C3)) という関数が入っています。見かけ上は、A1 B1 C1 D1 は何も表示されません。

そこで、上の別シートのA1 B1 C1 D1をコピーして、最初のA1 B1 C1 D1に値貼り付けすると、E1は「1」と表示されてしまいます。

質問は、関数が入っていて、IF関数で空白表示をさせるようになっているセルをコピーして値貼り付けしたときに、COUNTAが0 となるようにするにはどうしたらよいのでしょうか。

よろしくお願いします。

コメント(16)

最初
全て
最新の40件

[1] mixiユーザー 05月21日 05:45

>質問は、関数が入っていて、IF関数で空白表示をさせるようになっている
>セルをコピーして値貼り付けしたときに、COUNTAが0 となるようにするには
>どうしたらよいのでしょうか。

COUNTA関数の仕様自体がそういうものなので、関数仕様自体を変えないと
そのままでは無理でしょうね。
(参考)
　http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu/count.htm

ただ、強引に見た目は空白のセルを計数対象としない計数式なら作れそうです。

E1=COUNTA(A1:D1)-SUMPRODUCT(1-ISBLANK(A1:D1)*1,(A1:D1="")*1)
　入力のあるセル数-(入力済かつ見た目が空文字列""のセル数)

[3] mixiユーザー 05月21日 08:08

=COLUMNS(A1:D1)-COUNTBLANK(A1:D1)
=SUMPRODUCT((LEN(A1:D1)>0)*1)
とかではどうでしょう

[4] mixiユーザー 05月21日 08:22

>　=SUMPRODUCT((LEN(A1:D1)>0)*1)
これ、考え方も表現もスマートで良いですね

やっぱり同じ対象範囲を複数回記述する式はどうにも好きになれないんで…

[5] mixiユーザー 05月21日 09:37

「空白セル」の扱いは、悩ましいところなんですよね。
「""」は「長さ0の文字列」として扱われ、未入力セルとは異なります。値貼り付けしたあと、D1セルを選択して、［Delete］キーでクリアすれば未入力セルになるのはもちろんですが、［F2］キーで編集して［Enter］キーで入力し直しても未入力セルになります。

カウントする関数のほうで対処する場合、A1～C1セルのデータが数値なら、

=COUNTIF(A1:D1,">0")

とかでも大丈夫です。マイナスの値が含まれる場合なら、2番目の引数を「">=-100"」とか適当に変更すれば対処できます。

[6] mixiユーザー 05月21日 12:47

若干話題がカウント関連の関数からは脱線してしまうのですが、

５
>「空白セル」の扱いは、悩ましいところ
確かに悩ましいです。「""」の正体を日本語で説明するのも大変です。ケチをつけるわけではないのですが、

>「""」は「長さ0の文字列」として扱われ
エクセルの挙動を見ていると、必ずしもそればかりでもなさそうです。
出力結果という点では確かにその通りなのですが、例えばこれをIf関数の条件として使うと、ちゃんと「空白セル」を認識してくれます。

=if(分母="","",割り算)というパターンの式の挙動を見るとその様子がよく分かりますね。

[7] mixiユーザー 05月21日 13:03

データは何を扱うの?

[8] mixiユーザー 05月21日 13:14

「空白セル」に関し、COUNTA「だけ」誤動作するという話をしてるのに…

=SUMPRODUCT((A1:D1<>"")*1)

[9] mixiユーザー 05月21日 13:22

根拠無しですが、Lotus1-2-3やマルチプランあたりとの互換性を取ろうとした結果、
関数によっては空白セルと空文字列の扱いに一貫性が無くなってしまったのかも
知れませんねぇ。
(自社だけでクローズできるならレビューなりテストなりで気付くでしょうし…)

[10] mixiユーザー 05月21日 13:28

>>8 たつや.com さん
シンプルで素晴らしい！！

気付きませんでしたが、最初にそれを試すべきですよね。。。

ただ、COUNTAの振る舞いは誤動作では無く明確に定義された仕様です。

[11] mixiユーザー 05月21日 13:33

>「空白セル」に関し、COUNTA「だけ」誤動作するという話をしてるのに…

『使い方を誤っている』だけだろ

>質問は、関数が入っていて、IF関数で空白表示をさせるようになっているセルをコピーして値貼り付けしたときに、COUNTAが0 となるようにするにはどうしたらよいのでしょうか。

できません。

[12] mixiユーザー 05月21日 13:48

>[6] 八神かかし　さん

そもそも「空白セル」というのをどのように定義するかということではないでしょうか？
「空白セル」を「未入力セル」と定義するなら、「A1=""」では厳密な判定ができていないです。一般には、「A1=""」とすれば十分な場合が多いですが、「未入力セル」として判定するなら「ISBLANK(A1)」とする必要があります。

たとえば、今回の例で、値に変換したD1セルに対して「=ISTEXT(D1)」とすると、「TRUE」が返されます。COUNTA関数に限らず、こういう挙動についても知っておいたほうがいいのではないでしょうか。

[13] mixiユーザー 05月22日 07:06

いのっちです。みなさん、丁寧にコメントありがとうございました。

…　空白セル「""」は「長さ0の文字列」として扱われ、未入力セルとは異なります。
という説明、目からうろこおちでした。

COUNTA　で　空白セル　を　指定すると、　１　と　表示される。

LEN 　あるいは　LENB で　空白セル　を指定すると、　0 と　表示される。

ということで、LEN　を　使用して　解決できました。ありがとうございました。

[14] mixiユーザー 05月22日 14:33

いのっちです。勉強のために、次のことができるか、教えてください。

問題の「空白セル」を、置換して未入力セルにすることはできますか。
これができれば、COUNTA関数を使っていてもＯＫなのです。
関連質問で、お願いします。

[15] mixiユーザー 05月23日 11:37

>問題の「空白セル」を、置換して未入力セルにすることはできますか。

「長さ0」っていうのは、「何もない」ということなので、［置換］コマンドなどで、ある文字を別の文字に「置換」するという観点ではできないと思います。

「問題の『空白セル』」は、「あるのにない」から悩ましい。［F5］キーでダイアログを表示して、［セルの選択］ボタンから［空白セル］を選択しても、選択できるのは「未入力セル」だけで、この「問題の『空白セル』」は選択できません。
VBAで対処する以外のローテクでは、D1セルを選択して、［F2］キー→［Enter］キーを繰り返し押して、貼り付けた値を全部入力し直すという方法で、「問題の『空白セル』」を未入力セルに変換する方法はありますけど。

なので、提示されたように集計する関数のほうで工夫したほうが簡単なのでは？　SUMPRODUCT関数を使うなら、[8]のたつや.comさんの数式のほうが、条件の意味合いを理解しやすいように思います。まぁ、ここら辺は、好き好きだと思いますが。

[16] mixiユーザー 05月23日 12:11

>>14いのっちさん
「勉強のため」なら、自力で頑張って解決してみて下さい。
ヒントは十分に出ていますし、色々試してみて
巧くいった結果をここで報告して頂ければ同様の問題で
困っている方の助けにもなり得ます。

やるならVBAがお手軽そうですね。

ログインすると、残り1件のコメントが見れるよ