オンライン健康相談、gooドクター

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

gooドクター

A 回答 (7件)

こんばんは。

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

ご回答有難う御座います。
=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の判別の依頼に対するものとして理解しています。
ご迷惑を掛けまして申し訳けありませんでした。

お礼日時:2006/11/12 09:35

こんにちは。

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

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

お礼日時:2006/11/18 22:21

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


調べる対象を仮にB10:C20とします

問題となる値はNULLと0と""の3値でこの内0を検出したい場合
=ISNUMBER(B10:C20)*(B10:C20=0)
これで生成される配列の要素の内1(Trueと同値)のセルに0が入っています

同様にNULLを検出したい場合
=ISNUMBER(B10:C20)+ISTEXT(B10:C20)
これで生成される配列の要素の内0(Falseと同値)のセルにNULLが入っています

同様に""を検出したい場合
=ISTEXT(B10:C20)*(B10:C20="")
これで生成される配列の要素の内1(Trueと同値)のセルに""が入っています


""でない文字列が入っている場合
=ISNUMBER(B10:C20)+(B10:C20="")
これで生成される配列の要素の内0(Falseと同値)のセルに""でない文字列が入っています

0でない数字が入っている場合
=ISTEXT(B10:C20)+(B10:C20=0)
これで生成される配列の要素の内0(Falseと同値)のセルに0でない数字が入っています

お試し下さい
    • good
    • 0
この回答へのお礼

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

お礼日時:2006/11/11 17:12

こんばんは。


#2 のWendy02です。

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

ですから、

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

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

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

お礼日時:2006/11/11 16:37

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


少なくとも私の知る限りNULL値と""と0はほぼ同一視されます

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

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

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

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

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

となりますよ

余談ですが
セルの書式設定に#や""が設定されていた場合は0も見えなくなりますよね
    • good
    • 0
この回答へのお礼

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

お礼日時:2006/11/10 23:55

こんにちは。



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

   ↓

=SUMPRODUCT(B1:B10,C1:C10)

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

それとも、
配列数式で、

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

Ctrl + Shift → Enter で、[配列確定]をするっていうことでしょうか?
    • good
    • 0
この回答へのお礼

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

お礼日時:2006/11/10 23:29

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


ではダメですか?

この回答への補足

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

補足日時:2006/11/10 23:16
    • good
    • 0
この回答へのお礼

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

お礼日時:2006/11/10 23:13

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

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

gooドクター

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

人気Q&Aランキング