IF関数でEmpty値を設定する方法。

Question

こんにちは。
IF関数でEmpty値の設定方法を知りたいと思っています。
ゼロと値なしを分けるため、セルに対して値なしで有れば
IF関数にて""をセットしていますが、SUMPRODUCTで集計を
掛けた場合にエラーとなります。
そこで""ではなくEmpty値をセットしたいのですが、
方法をご存知の方いらっしゃれば宜しくお願い致します。

（例としては下記です。）
=if(A1>5,"",A1)をB1にセットし、
これを複数セル(B2:B10)に複写します。
(A1:A10)は0から9までの連番を入れます。
また、(C1:C10)には0から9までの連番が入っています。
B列には結果として0,1,2,3,4,5,空白となります。

これをSUMPRODUCTにて集計を掛けると
=SUMPRODUCT((C1:C10)*(B1:B10))
#VALUEエラーとなります。
しかしながら、B7:B10を削除してEmpty値とすると
正常に計算されます。

Excel2000/Excel2002です。
「Empty値」と「""」と「０」の値の件では度々このようなミスをしていましたので、この際解決したいと思っています。
尚、条件としてゼロは値として必要で、表示しなければなりません。
値なしは値なしとして空白を表示しなければなりません。
ご存知の方宜しくお願い申し上げます。

Wendy02 · Accepted Answer

こんばんは。Wendy02です。

=SUMPRODUCT(B1:B10,C1:C10)
>条件で抽出する必要があるため、配列を分けられてはダメです。

意味が良く分かりません。単なる合計計算を出したいのではありませんか？以下で、例をつけて、もう一度、説明しなおします。

それで、#5 さんの回答で解決をついているのでしたら、こちらは、これ以上は、何も言えません。

ただ、Null という説明は、ワークシートではエラー値 「#NULL!」 としかないもので、それ以外には、Excelのワークシートでは事実上使いません。

Access やOffice VBAで使われるものです。それ以外は、現在のMicrosoft Office では使われないと思います。英語では、Nullというのは、元はゼロの意味を表しますが、VBAなどでは、明示的に Null と使うもの以外には存在しないものです。意味は、値が存在しないか、不定だということです。それに、「""」 は、長さ0の文字列と呼びます。Empty というのは、何も入れてない状態で、これも、VBAで使われるものですが、英語では、Empty は、空の状態 Blankと同義で、VBAのIsEmpty() は、ワークシート関数の ISBLANK()と同じ働きを持っています。

しかし、その説明の延長上に解答があるとは私には思えないのですが、それとも、私が質問を理解していないのかもしれません。ただ、今までのパターンからすると、そのような例というのは、覚えがありません。今回、私の書いた数式の仕様は、あまり、みなさんが知らないことだとは思います。

-----------------------------------------
　　A　　　B　　C
1　　8　　　　　0
2　　7　　　　　1
3　　6　　　　　2
4　　7　　0　　3
5　　6　　　　　4
6　　4　　4　　5
7　　5　　5　　6
8　　9　　　　　7
9　　8　　　　　8
10　 9　　　　　9

B1 ～フィルダウン・コピー 10 行目まで
=IF(A1>5,"",A1)
ただ、B4 は、手入力で、0 を入れた。
-----------------------------------------

この状態で、実際は、上記のような表の場合、(0 *3) + (4 * 5) + (5 * 6 )　で、「50」 になる、というようなことではないのかと、最初のご質問で解釈しました。

=SUMPRODUCT((C1:C10)*(B1:B10))　で、"" が途中で入れば、エラーが出ます。これは、SUMPRODUCT の引数の中の計算から始めるという、数式の特徴があるからです。

#1 さんの　=SUMPRODUCT(IF(B2:B10<>"",B2:B10,0)*(C2:C10))　

は、これは、SUMPRODUCT 自体が働きませんので、そのままでは、#VALUE!エラーが出ます。[回答への補足]は、[配列確定]をしたら、エラーが出なかった、ということではないか、と読みました。

こういう状態なら、=SUMPRODUCT((C1:C10),(B1:B10)) 

は、もともと、SUM関数と同じ仕様を持つこの関数は、「""」もEmpty(ブランク)も、文字列も同様に省きますから、数値だけを拾って計算をします。例えば、「""」の代わりに、「"なし"」としても、解は同じです。SUMに関係する関数には、数値でないという条件(節)を必要としません。他には、PRODUCT という関数があります。

これは、試してみれば一目で分かることです。上記の例の場合は、「50」という解が出ます。それをお分かりになっているのでしょうか？

>条件で抽出する必要があるため、配列を分けられてはダメです。
これが、一体、その後に、何の条件が付加されるのですか？こちらでは読み取れません。他に条件があるということでしょうか？

「0 」を数えるなら、
=COUNTIF(B1:B10,0)
ですし、

「""(長さ0の文字列) 」を数えるなら、
=COUNTIF(B1:B10,"")
または、
=COUNTBLNAK(B1:B10)

Empty＝ブランクセルを探すなら、

=SUMPRODUCT(ISBLANK(B1:B10)*1)

しかし、これらの値のないセルに対して、それぞれの処理が変わるとしても、それは、いずれにしても、数値の掛け算の合計しかありえないはずです。

>ついては、[配列確定]を使いたくなかったので、テストしていません。

それに、他の人たちのは[配列確定]しなければ出ない解がほとんどですね。なのに、こちらには、なぜか[配列確定]は使いたくないという理由だけで、ダメにしてしまうのですね。他の人のは良いというわけですか？きちんと、条件を提示してくださったほうが、他の回答者さんのためでもあると思います。

私は、配列数式自体をお勧めするわけではありませんが、もし、配列数式を使いたくないのでしたら、補助の列を使って計算されたらどうですか？SUMPRODUCTの引数自体が、配列を使っていますので、配列確定をする数式だけが、配列数式ではありません。配列数式はとかく複雑になりやすいので、あまり手が掛かるようだった、最初からしないほうがよいです。

Wendy02 · Answer

こんにちは。Wendy02です。

もともと、SUMPRODUCT関数 は、製作者の意図する本来の使い方とは違う使い方をされて、広まっているようなのです。それと、私が調べた結果では、SUMPRODUCT関数は、前回書いたように、独特の癖があって、

・中にある演算から計算をし始める

のです。当たり前のような気がしますが、この引数は、バラメータ配列という仕組みになっているからなのです。しかし、そのパラメータ配列の個々の配列要素を、どのように処理するか、というのは作者の考え方です。そして、これは、SUM関数の古い初期の頃の名残りを踏襲していると思っています。なぜ、そうしたかという理由は、ご質問のように、セルに文字を書き入れるためだそうです。

私は、時折、関数の仕様や歴史を調べています。そして、今、私は、VBAで本当に自分が思ったとおりに再現できるかを、時々練習しています。

以下の場合は、中にある演算から計算をし始める、という原則からすると、文字列や"" が入ると、エラーが出てしまいますね。

=SUMPRODUCT((B1:B10)*(C1:C10)*(A1:A10<=5)) 

以下の場合は、
=SUMPRODUCT(B1:B10,C1:C10,(A1:A10<=5))
　　↓
=SUMPRODUCT(B1:B10,C1:C10,(A1:A10<=5)*1)
とすると、おそらく計算が可能になるはずです。

これ以上複雑になっても、裏技的な解決方法はありますが、それは、もし、ご質問があれば考えていきます。あまり複雑なものは、VBAのユーザー定義関数で作ったほうが簡単なこともあります。配列数式は、Excel のVersion によっては、配列データ数が、5,500程度を超えると、エラーを出すことがあります。ただ、実務上は、前回書いたように、スペースが許す限り、数式は分解し補助列に出したほうがよいです。数式の修正が行いやすいからです。私も、配列数式は使いますが、自分で作っておいて、修正が出来なくなることが時々あります。

Nouble · Answer

こういうのはどうでしょうか？
調べる対象を仮にB10:C20とします

問題となる値はNULLと０と""の３値でこの内０を検出したい場合
=ISNUMBER(B10:C20)*(B10:C20=0)
これで生成される配列の要素の内１(Trueと同値)のセルに０が入っています

同様にNULLを検出したい場合
=ISNUMBER(B10:C20)+ISTEXT(B10:C20)
これで生成される配列の要素の内０(Falseと同値)のセルにNULLが入っています

同様に""を検出したい場合
=ISTEXT(B10:C20)*(B10:C20="")
これで生成される配列の要素の内１(Trueと同値)のセルに""が入っています


""でない文字列が入っている場合
=ISNUMBER(B10:C20)+(B10:C20="")
これで生成される配列の要素の内０(Falseと同値)のセルに""でない文字列が入っています

０でない数字が入っている場合
=ISTEXT(B10:C20)+(B10:C20=0)
これで生成される配列の要素の内０(Falseと同値)のセルに０でない数字が入っています

お試し下さい

Wendy02 · Answer

こんばんは。
#2 のWendy02です。

>その様な方法をお伺いしたいと思っていますが、・・・・・！

ですから、

=SUMPRODUCT(B1:B10,C1:C10)
または、
=SUM(IF(B1:B10<>"",B1:B10*C1:C10,0))
Ctrl + Shift → Enter で、[配列確定]をする

では、ダメなのでしょうか、と反語的に私は、書いています。意味が通じないのでしょうか？ダメならダメで仕方がないのですが、結果も出されないまま、同じ質問を切り替えされるのは、ご遠慮ください。

Nouble · Answer

エンプティーとは恐らくNULLのことを仰っているのだと思いますが
少なくとも私の知る限りNULL値と""と0はほぼ同一視されます

エクセルの関数HELP中にはNULLとは出てきますが
エンプティーと記載している箇所は記憶に覚束無いです

しかし見分け方が無いわけではありませんよ
対象のセルを
=ISBLANK(**)
=ISTEXT(**)
=ISNUMBER(**)
で検査してください

本当のNULL値なら
ISBLANKがTrueで後はFalse

""なら
ISTEXTがTrueでISBLANKとISNUMBERがFalse

0ならば
ISNUMBERがTrueでISBLANKとISTEXTがFalse

となりますよ

余談ですが
セルの書式設定に＃や""が設定されていた場合は0も見えなくなりますよね

Wendy02 · Answer

こんにちは。

もし、"" が、Empty に替えて、以下の数式と同じ値になるのなら、
=SUMPRODUCT((B1:B10)*(C1:C10))

　　　↓

=SUMPRODUCT(B1:B10,C1:C10)

ということではないでしょうか？

それとも、
配列数式で、

=SUM(IF(B1:B10<>"",B1:B10*C1:C10,0))

Ctrl + Shift → Enter で、[配列確定]をするっていうことでしょうか？

mshr1962 · Answer

=SUMPRODUCT(IF(B2:B10<>"",B2:B10,0)*(C2:C10))
ではダメですか？

IF関数でEmpty値を設定する方法。

こんばんは。

こんにちは。

こういうのはどうでしょうか？

こんばんは。

エンプティーとは恐らくNULLのことを仰っているのだと思いますが

こんにちは。

=SUMPRODUCT(IF(B2:B10<>"",B2:B10,0)*(C2:C10))

この回答への補足

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング