![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?e8efa67)
教えてください!
=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ベストアンサー
- 回答日時:
例えば、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
- 回答日時:
#3です。
本当に何度もすみません。引数が足りない、というのは変ですね。当方は大丈夫なのですが・・・
それから、空白(スペース)が入っている時はこれで大丈夫だと思うのですが、逆に空白は空白でも何にも入っていない空白(デリートキーを押した時のような空白)のときにエラーになってしまうみたいです・・・。
お役に立てずにすみません。
No.3
- 回答日時:
#1です。
補足を見ました。=SUMPRODUCT((sheet1!A1:A100="α")*(sheet2A1:A100="β")*(sheet2!B1:B100="γ")*
(sheet3!A1:A100="δ")*SUBSTITUTE((sheet2!C1:C100),"",0))
最後の括弧閉じ ) が一つ足りないような気がしますが大丈夫でしょうか。
それから最後のほうの""は間に空白(スペース)が入ります。" "です。
No.2
- 回答日時:
>上記でVALLE!が出てしまいました。
提示された関数式には誤りが見られません。
各配列を評価できるように=SUMPRODUCT((配列1="文字列1")*1,(配列2="文字列2")*1,・・・・)のようにされると誤りが発見し易いと思います。
>どうしたらいいでしょうか?
結果が同じであれば判断すべきブロックを小さくすると点検しやすくなります。
途中経過を評価することも必要かと思います。
No.1
- 回答日時:
おっしゃるとおり、sheet2のC列に空白(スペース)が入っているのでエラーとなります。
substitute関数を使ってスペースを0に置き換えればよいと思います。
(sheet2!C1:C100)
を、
SUBSTITUTE((sheet2!C1:C100)," ",0)
のようにします。
なお、こうするとsheet2のC列が空白の場合は0が返り値となります。
この回答への補足
ありがとうございます!
早速やってみました。
=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しかでませんでした。
どうしたらいいでしょうか?
よろしくお願いします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・歩いた自慢大会
- ・許せない心理テスト
- ・字面がカッコいい英単語
- ・これ何て呼びますか Part2
- ・人生で一番思い出に残ってる靴
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・初めて自分の家と他人の家が違う、と意識した時
- ・単二電池
- ・チョコミントアイス
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
IF関数で空欄("")の時、Null...
-
ピボットテーブルで空白セルの...
-
エクセルでCSVを編集するとき、...
-
Excel > ピボットテーブル「(空...
-
「データ要素を線で結ぶ」がチ...
-
エクセル 連番が途切れていると...
-
空白セル内の数式を残したまま...
-
Excel:関数が入っているセルに...
-
数式による空白を無視して最終...
-
【Excel】 csvの作成時、空白セ...
-
Excelで、入力文字の後に自動で...
-
エクセル セルのコピー元が空...
-
エクセル VLOOKUP関数 日付は日...
-
excel2010 空白セルにのみ貼り...
-
エクセルのグラフで式や文字列...
-
関数TRANSPOSEで空白セルを0に...
-
エクセルで、「複数のセルの中...
-
VBA スペースが入力されて...
-
エクセルで数式の入ったセルの...
-
Excelで"0"を空白に変換する方法
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
IF関数で空欄("")の時、Null...
-
エクセルでCSVを編集するとき、...
-
数式による空白を無視して最終...
-
「データ要素を線で結ぶ」がチ...
-
Excel > ピボットテーブル「(空...
-
関数TRANSPOSEで空白セルを0に...
-
ピボットテーブルで空白セルの...
-
空白セル内の数式を残したまま...
-
【Excel】 csvの作成時、空白セ...
-
色つき行の一括削除は?
-
excel2010 空白セルにのみ貼り...
-
エクセルで上の行の値を自動的...
-
Excel:関数が入っているセルに...
-
Excelで、入力文字の後に自動で...
-
エクセル 連番が途切れていると...
-
SUMIFS関数で「計算式による空...
-
【Excel】 Ctrl+方向キー で空...
-
《Excel2000》SUMPRODUCT関数で...
-
形式貼り付けの「空白を無視す...
-
エクセル VLOOKUP関数 日付は日...
おすすめ情報