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

質問

こんにちは。
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値」と「""」と「０」の値の件では度々このようなミスをしていましたので、この際解決したいと思っています。
尚、条件としてゼロは値として必要で、表示しなければなりません。
値なしは値なしとして空白を表示しなければなりません。
ご存知の方宜しくお願い申し上げます。

回答No.7

Wendy02

こんにちは。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程度を超えると、エラーを出すことがあります。ただ、実務上は、前回書いたように、スペースが許す限り、数式は分解し補助列に出したほうがよいです。数式の修正が行いやすいからです。私も、配列数式は使いますが、自分で作っておいて、修正が出来なくなることが時々あります。

お礼

ご回答有難う御座います。

回答No.6

Wendy02

こんばんは。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の引数自体が、配列を使っていますので、配列確定をする数式だけが、配列数式ではありません。配列数式はとかく複雑になりやすいので、あまり手が掛かるようだった、最初からしないほうがよいです。

お礼

ご回答有難う御座います。
=SUMPRODUCT(B1:B10,C1:C10)
答え=50を確認しました。
私の勘違いでした。
以前やったように記憶したので、解がでないと思い込んでいました。
Wendy02さんのご回答は、私の質問文面に対する回答としては完全だと思います。

逆に言えば私の質問に落ち度があったと思います。
条件として範囲指定をして使いたいのが目的、かつ、配列確定を使わない方法というような言い回しをすべきだったでしょう。

条件とは例えば
=SUMPRODUCT(B1:B10,C1:C10,(A1:A10<=5))
とか、=SUMPRODUCT((B1:B10)*(C1:C10)*(A1:A10<=5))と言った使い方です。

また、Noubleさんのご回答はNULL値と""と0の判別の依頼に対するものとして理解しています。
ご迷惑を掛けまして申し訳けありませんでした。

回答No.5

Nouble

こういうのはどうでしょうか？
調べる対象を仮に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と同値)のセルに０でない数字が入っています

お試し下さい

お礼

有難う御座います。
大変勉強になりました。

回答No.4

Wendy02

こんばんは。
#2 のWendy02です。

>その様な方法をお伺いしたいと思っていますが、・・・・・！

ですから、

=SUMPRODUCT(B1:B10,C1:C10)
または、
=SUM(IF(B1:B10<>"",B1:B10*C1:C10,0))
Ctrl + Shift → Enter で、[配列確定]をする

では、ダメなのでしょうか、と反語的に私は、書いています。意味が通じないのでしょうか？ダメならダメで仕方がないのですが、結果も出されないまま、同じ質問を切り替えされるのは、ご遠慮ください。

お礼

ご回答有難う御座います。
=SUMPRODUCT(B1:B10,C1:C10)ではダメです。
例として=SUMPRODUCT((C1:C10)*(B1:B10))と
記載しましたが、条件で抽出する必要があるため、
配列を分けられてはダメです。
=SUM(IF(B1:B10<>"",B1:B10*C1:C10,0))に
ついては、[配列確定]を使いたくなかったので、
テストしていません。

回答No.3

Nouble

エンプティーとは恐らくNULLのことを仰っているのだと思いますが
少なくとも私の知る限りNULL値と""と0はほぼ同一視されます

エクセルの関数HELP中にはNULLとは出てきますが
エンプティーと記載している箇所は記憶に覚束無いです

しかし見分け方が無いわけではありませんよ
対象のセルを
=ISBLANK(**)
=ISTEXT(**)
=ISNUMBER(**)
で検査してください

本当のNULL値なら
ISBLANKがTrueで後はFalse

""なら
ISTEXTがTrueでISBLANKとISNUMBERがFalse

0ならば
ISNUMBERがTrueでISBLANKとISTEXTがFalse

となりますよ

余談ですが
セルの書式設定に＃や""が設定されていた場合は0も見えなくなりますよね

お礼

ご回答有難う御座います。
おっしゃる通りNULL値(Empty)と""と0はほぼ同じ様に
解釈されていると思います。
実際の運用では（仮にですが）
出荷数１００に対して不良０件は０件と表示できますが
出荷数０件に対しては不良０件ではなく「なし」と表示する必要が生じることがあります。
この表現を容易にしたいと思っています。

回答No.2

Wendy02

こんにちは。

もし、"" が、Empty に替えて、以下の数式と同じ値になるのなら、
=SUMPRODUCT((B1:B10)*(C1:C10))

　　　↓

=SUMPRODUCT(B1:B10,C1:C10)

ということではないでしょうか？

それとも、
配列数式で、

=SUM(IF(B1:B10<>"",B1:B10*C1:C10,0))

Ctrl + Shift → Enter で、[配列確定]をするっていうことでしょうか？

お礼

ご回答有難う御座います。
逆にご質問を頂きましたが、
=SUMPRODUCT((C1:C10)*(B1:B10))の時に""の空白があった場合の
エラー表示を解決する方法をお伺いしたいと思っています。
データ的には""が０またはEmptyである場合にはエラーが出ないことは
確認しています。
その様な方法をお伺いしたいと思っていますが、・・・・・！

回答No.1

mshr1962

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

お礼

ご回答有難う御座います。
テストをしてみましたが、
#VALUEエラー
となりました。

補足

補足します。
配列にした場合には#VALUEエラーは無くなりました。