SUMPRODUCT関数のエラー

質問

教えてください！

=SUMPRODUCT((sheet1!A1:A100="α")*(sheet2!A1:A100="β")*(sheet2!B1:B100="γ")*
(sheet3!A1:A100="δ")*(sheet2!C1:C100))

上記でVALLE!が出てしまいました。

sheet2C1:C100が空白と数字でできているためかと思い、

=SUMPRODUCT((sheet1!A1:A100="α")*(sheet2!A1:A100="β")*(sheet2!B1:B100="γ")*
(sheet3!A1:A100="δ")*1,(sheet2!C1:C100))

と、してみたところ・・エラーは出ないのですが、sheet2C1:C100に数字が入っていても0になってしまいます。

ちなみにsheet2C1:C100以外は全て文字列で問題なく反映されています。

どうしたらいいでしょうか？

よろしくお願い致します。

回答No.6

MarcoRossiItaly · ベストアンサー

例えば、Sheet2 C 列のセルの書式が「文字列」となっているところに数を記入しても、SUMPRODUCT 関数はそれを数値とみなしてくれません。この状態を直すには、「文字列」以外の書式に設定した後、1 列全体を選択して「データ > 区切り位置 > 完了ボタン」と進むと、SUMPRODUCT の返す値が変化していると思います。

他にも、実は各列の行番号が勘違いで少しずれていた、ということはあり得ますね。あるいは、「α」などの文字列中に余計なスペースなどが混入していれば当然、想定どおりの計算結果にはなりません。

試しに、どこか適当な 1 行を選んで、同じ行番号の 3 シート 4 セルに手入力で「α」などを記入していってみましょう。SUMPRODUCT に変化はあるでしょうか？

どこに原因があるのか探るため、お示しの数式が記入されているセルにカーソルを置いた状態で、数式タブの「数式の検証」の「検証」ボタンを何回か押してみることをお勧めします。そこで表示される配列を見ると、数えてほしい行については、同じ行番号の 3 シート 4 セルについて、全て TRUE となっているはずです。数式に問題が含まれていないとすればですが。しかし実際は「0」という計算結果になっているなら、4 セル中、少なくとも 1 つは FALSE と表示されているはずです。注意点は、行数が多すぎると表示するのに非常に時間がかかったり、小さなダイアログでは見づらいといった点が不便なので、10 行などの少なくした数式で試してみることです。

とりあえずお示しの 2 式に、数式の記法上の誤りは含まれていないようです。なお 2 本目の数式については、「*1」や「( )」を次のとおり書かなくて大丈夫です。

=sumproduct((sheet1!a1:a100="α")*(sheet2!a1:a100="β")*(sheet2!b1:b100="γ")*
(sheet3!a1:a100="δ"),sheet2!c1:c100)

「=」が含まれている各論理式には「*」を付けますが、既に付いていますよね。だから「*1」は不要です。また、「sheet2!c1:c100」の部分には「=」がないため、「*」も「( )」も不要です。

＞sheet2C1:C100が空白と数字でできているためかと思い、

理由はそんな感じです。見た目が空白っぽい、空文字列が記入されているセルは文字列なので、掛け算ができないために「#VALUE!」のエラーになりますね。本当の空白（未記入）のセルなら、「0」として扱われるため、エラーにはなりません。

回答No.5

MSZ006

＃３です。本当に何度もすみません。
引数が足りない、というのは変ですね。当方は大丈夫なのですが・・・
それから、空白（スペース）が入っている時はこれで大丈夫だと思うのですが、逆に空白は空白でも何にも入っていない空白（デリートキーを押した時のような空白）のときにエラーになってしまうみたいです・・・。
お役に立てずにすみません。

回答No.4

MSZ006

＃３です。
すみません。括弧の数は合ってましたね。m(__)m

回答No.3

MSZ006

＃１です。補足を見ました。

=SUMPRODUCT((sheet1!A1:A100="α")*(sheet2A1:A100="β")*(sheet2!B1:B100="γ")*
(sheet3!A1:A100="δ")*SUBSTITUTE((sheet2!C1:C100),"",0))

最後の括弧閉じ　）　が一つ足りないような気がしますが大丈夫でしょうか。
それから最後のほうの""は間に空白（スペース）が入ります。" "です。

回答No.2

bunjii

>上記でVALLE!が出てしまいました。
提示された関数式には誤りが見られません。
各配列を評価できるように=SUMPRODUCT((配列1="文字列1")*1,(配列2="文字列2")*1,・・・・)のようにされると誤りが発見し易いと思います。

>どうしたらいいでしょうか？
結果が同じであれば判断すべきブロックを小さくすると点検しやすくなります。
途中経過を評価することも必要かと思います。

回答No.1

MSZ006

おっしゃるとおり、sheet2のC列に空白（スペース）が入っているのでエラーとなります。

substitute関数を使ってスペースを0に置き換えればよいと思います。

(sheet2!C1:C100)
を、
SUBSTITUTE((sheet2!C1:C100)," ",0)
のようにします。

なお、こうするとsheet2のC列が空白の場合は０が返り値となります。

補足

ありがとうございます！

早速やってみました。

=SUMPRODUCT((sheet1!A1:A100="α")*(sheet2A1:A100="β")*(sheet2!B1:B100="γ")*
(sheet3!A1:A100="δ")*SUBSTITUTE((sheet2!C1:C100),"",0))

としてみましたが、引数が足りないと出てしまいました。

また、

=SUMPRODUCT((sheet1!A1:A100="α")*
(sheet2!A1:A100="β")*(sheet2!B1:B100="γ")*
(sheet3!A1:A100="δ")*1,SUBSTITUTE((sheet2!C1:C100),"",0))

にすると、やはり0しかでませんでした。

どうしたらいいでしょうか？

よろしくお願いします。