こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

回答受付中の質問

ExcelVBAでSumProductでエラー13

Excel2010を使用しています。
ExcelVBAでSumProductを使って集計をしようとしましたが、うまくいかないのでご教授いただきたいです。
前提として、
シート"B"には日付・名前・単価・個数などが入力されており、シート"A"の列Aに日付を入力しています。
やりたいことは、
シート"B"のリストの中から、シート"A"の列Aの日付と一致するデータの単価×個数の和をシート"A"の列Bに入力させたいです。
実際のデータは異なりますが、イメージとしては、以下のようなデータになります。
<シート"A">      <シート"B">
行番号  列A   列B  行番号 列A  列B   列C  列D
 11   4/1       21  4/1 りんご  100.21  3
 12   4/2       22  4/1 りんご   98.26  2
 13   4/3        23  4/1 みかん  200.59  3
 14   4/4        24  4/1 ぶどう  150.36  5
              25  4/2 りんご  101.84  4
例えば、上の場合で、4/1のりんごの単価×個数の和を求めるため、以下のコードを実行したところ、「実行時エラー '13' 型が一致しません。」と表示され、うまくできません。Evaluateを使ったりもしてみましたが、うまくいきませんでした。
行番号に変数を使用している場合の解決方法は見つけたのですが、シート名を変数にして使用している場合の解決方法が分かりませんでした。
解決方法をご教授いただきたいので、よろしくお願いいたします。

Dim ASheet As Worksheet, BSheet As Worksheet
Dim HajimeA As Long, HajimeB As Long '各シートで計算する最初の行番号
Dim OwariA As Long, OwariB As Long '各シートの最終の行番号
Dim i As Long
Dim Kingaku As Double '単価×個数の和(日ごと)

Set ASheet = Worksheets("A")
Set BSheet = Worksheets("B")

HajimeA = 9
HajimeB = 12
OwariB = 1048576

With ASheet

'「A」シートで入力をする最終行を求める
OwariA = .Cells(Rows.Count, 1).End(xlUp).Row

For i = HajimeA To OwariA
Kingaku = WorksheetFunction.SumProduct((BSheet.Range("A" & HajimeB & ":A" & OwariB) = .Range("A" & i)) _
* (BSheet.Range("B" & HajimeB & ":B" & OwariB) = "りんご") _
, (BSheet.Range("C" & HajimeB & ":C" & OwariB)) * (BSheet.Range("D" & HajimeB & ":D" & OwariB)))

Debug.Print Kingaku
Next

End With

投稿日時 - 2018-04-14 20:12:38

QNo.9488603

困ってます

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.5

今更ですが、質問と回答も含め、1週間かけるよりも、「ピボットテーブル」でやれば、1分で、表が出来ます。
データ例 A1:D7 E列は下記関数の結果も、前もって上げます。
日付 品名 単価 数量 積
4月1日りんご100.213300.63
4月1日りんご98.262196.52
4月1日みかん200.593601.77
4月1日ぶどう150.365751.8
4月2日りんご101.844407.36
4月3日なし73.123219.36
積の列は関数式で、E2に=C2*D2
下方向に式を複写。
ーーー
A1:E7 を選択
挿入ーピボットーOK
「日付」の▢をクリック
列に「日付」結果が指定される
「積」の▢をクリック
Σ値に「積」がセットされる
結果のピボットの表
行ラベル合計 / 積
4月1日1850.72
4月2日407.36
4月3日219.36
総計2477.44
こんなに簡単。
ーー
ピボットの威力を感じてください。
この操作を、VBA化もできます。マクロの記録をとり修正するとか。

投稿日時 - 2018-04-18 11:08:36

ANo.4

補足有難うございます。
ーー
しかし「日別の売上額」のようなものが欲しいのだろうから、
質問の標題としては、「日別の」の表現が漏れていると思う。
ーー
参考
日付別でなくて、よいなら
Sub test01() 'OK
Set シートA = Worksheets("シートA")
Set シートB = Worksheets("シートB")
シートA.Range("B2") = Application.WorksheetFunction.SumProduct(Range("C2:C6"), Range("D2:D6"))
'あるWEB解説例 合計 = WorksheetFunction.SumProduct(Range("C3:C9").Value, Range("D3:D9").Value)
MsgBox シートA.Range("B2")
End Sub
ーー
確かにエクセルの関数では、SUMPRODUCT関数を使って、ある日付1日分の、売上額(らしきもの)を出す。
ーー
その後
日付けを聞くところで、VBAの式表現に手こずった。
ーーー
(1)関数では配列数式になるが
下記の例でB20に=SUM((A2:A8=$A20)*(C2:C8)*(D2:D8))
でSHIT+CTRL+ENTER
式は下方向に2行分複写する。
A20:B22
2018/4/11850.72
2018/4/2613.04
2018/4/3267
ーーー
そのため、VBAでの、SUMPRODUCT関数利用は、現状私の力では、うまく行かなかった。
別の方の回答が出ると良いと思う。
ーー
それで
(2)VBAで、「各行セルの繰り返し法」で演算する
などの方法をやってみました。
ーー
関数では
=SUMPRODUCT(($H$2:$H$6=1)*(($C$2:$C$6)))
のような書き方ができる。H列が「1」の行のみ積和。
ーー
(2)の方法は
Sub test06()
Set シートA = Worksheets("シートA")
Set シートB = Worksheets("シートB")
'---
lr = シートA.Cells(100000, "A").End(xlUp).Row
lr2 = シートB.Cells(100000, "A").End(xlUp).Row
MsgBox lr
MsgBox lr2
s = 0
'--
st = シートA.Cells(2, "A")
i = 2
MsgBox st
For j = 2 To lr2 ' 1000
If シートB.Cells(j, "A") = st Then
s = s + シートB.Cells(j, "c") * シートB.Cells(j, "D")
Else
シートA.Cells(i, "B") = s
i = i + 1
s = 0
s = s + シートB.Cells(j, "C") * シートB.Cells(j, "D")
st = シートB.Cells(j, "A")
MsgBox st

End If
MsgBox s
Next j

シートA.Cells(i, "B") = s
End Sub
ーー
結果データ 
シートA A1:B4 結果を含む 日別集計表
hiduke
2018/4/11850.72
2018/4/2613.04
2018/4/3267
ーー
元データ シートB 質問例に少々加えた。
A1:D8
hiduke 品名 単価 数量
2018/4/1りんご100.213
2018/4/1りんご98.262
2018/4/1みかん200.593
2018/4/1ぶどう150.365
2018/4/2りんご101.844
2018/4/2りんご102.842
2018/4/3りんご  893

投稿日時 - 2018-04-15 20:10:53

お礼

こちらこそご回答ありがとうございます。
確かに「日別の」の表現を入れるべきでした。ご指摘ありがとうございます。

SumProductを使わない方法も勉強になりましたが、Shift+Ctrl+Enterの配列数式というものも初めて知り、こちらについても勉強になりました。
どうもありがとうございました。

投稿日時 - 2018-04-18 00:42:30

ANo.3

 なんかこのプログラムはよくわかりません。
シートAが11から始まっているのに、なぜ HajimeA = 9 なのか
シートBが21から始まっているのに、なぜ HajimeB = 12 なのか
「りんご」だけを検索していますが、それでいいのか?
 このプログラムでは、1つの文で複雑なことをやりすぎ、どこでエラーが出ているかわかりません。解析も不可能です。多重括弧はできるだけ避けて、変数を使うべきです。
 シートAのB1に
=SUMPRODUCT((B!A$21:A$25=A11)*(B!B$21:B$25="りんご")*B!C$21:C$25*B!D$21:D$25)
と書いて、下にコピーする。まず、数式を作るべきです。

いろいろやった結果、WorksheetFunction.SumProductでは、引数に数式を使えないみたいです。(未確認ですが)
Evaluateでうまくいったので載せます。
'
Option Explicit
'
Sub Macro1()
'
  Dim HajimeB As Long
  Dim OwariB As Long
  Dim Row As Long
  Dim AWord As String
  Dim BWord As String
  Dim EWord As String
  
  Worksheets("A").Select
  With Worksheets("B")
'
  HajimeB = 21
  OwariB = .Cells(Rows.Count, 1).End(xlUp).Row
  AWord = "Sumproduct((B!A" & HajimeB & ":A" & OwariB & "="
  BWord = ")*(B!B" & HajimeB & ":B" & OwariB & "=""りんご"")*B!C" & _
    HajimeB & ":C" & OwariB & "*B!D" & _
    HajimeB & ":D" & OwariB & ")"
'
  For Row = 11 To Cells(Rows.Count, "A").End(xlUp).Row
    EWord = AWord & Cells(Row, "A") * 1 & BWord
    Cells(Row, "B") = Evaluate(EWord)
  Next Row
  End With
End Sub

直接
Evaluate(AWord & Cells(Row, "A") * 1 & BWord)
にすると、どこでエラーが出るかわからなくなります。
これなら、EWord の中身を見ればどこがおかしいかわかります。
実際は、「りんご」もいろいろな値に代わると思います。その時は、CWord を設けて、3つに分ければいいです。

投稿日時 - 2018-04-15 11:38:29

お礼

質問で仮定した内容とコードの内容が結びついておらず、分かりづらいコードで申し訳ないです。

でもおかげさまでEvaluateを使用してエラーを吐かずにできました。どうもありがとうございました。

投稿日時 - 2018-04-15 20:10:45

ANo.2

提示されたコードはお世辞にも読みやすいとは言えず、
また、
A,Bシートの9行目以下に何が埋まっているのかがわからず、
深く追えないので、上っ面なコメントになります。 m(_ _)m

承知と思いますが、
SumProduct関数に与える引数は、配列にする必要がありますね。

Kingaku = WorksheetFunction.SumProduct((BSheet.Range("A" & HajimeB & ":A" & OwariB) = .Range("A" & i)) _
* (BSheet.Range("B" & HajimeB & ":B" & OwariB) = "りんご") _
, (BSheet.Range("C" & HajimeB & ":C" & OwariB)) * (BSheet.Range("D" & HajimeB & ":D" & OwariB)))

この部分で、おそらく、
シートAのB列に埋める金額を計算しようとしているものと思います。

この計算式が1回目に実行されたとき(つまり、i=9の時)
どのようなコードになるのか予想してみました。
(便宜上 OwariB=100 としています。)
それが、↓です。

Kingaku =
WorksheetFunction.SumProduct
(
(BSheet.Range(A12:A100) = .Range(A9)) * (BSheet.Range(B12:B100) = "りんご") ,
(BSheet.Range(C12:C100)) * (BSheet.Range(D12:D100))
)

4行目が第一引数、5行目が第二引数です。

第二引数はC列とD列を掛け算した値の配列になりますが
第一引数は私には理解不能です。
VBAもこれが理解できず、
「実行時エラー '13' 型が一致しません。」
を返してくるものと思います。

投稿日時 - 2018-04-15 10:03:29

お礼

質問で仮定した内容とコードの内容が結びついておらず、分かりづらいコードで申し訳ないです。

>第一引数は私には理解不能です。
文字列を条件にする場合は、(BSheet.Range(B12:B100) = "りんご")のように括弧で括れば論理値TRUE/FALSEのため、値が1か0になるということを読んだので、そのように書きました。

投稿日時 - 2018-04-15 20:07:12

ANo.1

補足すること。質問文は、たぶん余分なことが書いてあるようで、長いが、聞いていることの状況がよくわからない。単価列と個数列は、どシートのどの列か。
模擬実例をしっかり書いて、説明すべきだ。VBAをやろうとする人がこれではね。
列の見出しを模擬実例に入れること。
単価と個数について、対応するセルを同数だけ、考える必要があるが、どうなっている。シートBでは単価金額と数量「らしい」ものがわかるが、シートAのデータはどう使えばよいのか?

投稿日時 - 2018-04-14 21:33:22

補足

各列の項目の補足です。
<シート"A">
列A:日付
列B:金額(単価×数量)←ここにシート"B"に記載された日付ごとの金額の和(この質問の場合はりんごの単価×数量の和→例えばシート"A"のセルB11にシート"B"のセルC21×セルD21+セルC22×セルD22)を入力したいです。
質問文中に記載したコードではセル(列B)に入力するまでのコードは書いていませんが、代わりにDebug.Print Kingakuで金額を表示するようにしています。

<シート"B">
列A:日付
列B:品名
列C:単価(通常は整数だと思いますが、あえて小数にしています)
列D:数量

要は条件に合ったものの中で掛け算(シート"B"の列C×列D)したものの和を求めたいのです。イメージしやすくなるようにと思って品名と単価と数量に例えて質問したら余計分かりづらくなってしまいました。
この補足文でもうまく伝えることができていないと思います。分かりづらくてすみません。

投稿日時 - 2018-04-15 03:30:57