VBAのSUBPRODUCT関数の引数について

質問

ＶＢＡに詳しい方へ

　部品単価積み上げなどで　部品単価×分子員数／分母員数の合計を計算するときにSUBPRODUCT関数を使いますがマクロ記録すると

Range("D3").Select
ActiveCell.FormulaR1C1 = "=SUMPRODUCT(R5C2:R9C2,1/R5C3:R9C3,R5C4:R9C4)"

となります。
　2列に員数分子　3列に員数分母、4列に部品単価が　5行から下に部品ごとに記入されています。
D3セルに　関数として入力されます。
これでは　部品の追加に対処できないので　セルを変数にして表現したいのです。

これと同じことを　ＶＢＡで行数可変に対応すると　分母員数の逆数を受け付けず
実行時エラー13：型が一致しない　と表示されます。

Option Explicit

Dim 分子群 As Range
Dim 分母群 As Range
Dim 単価群 As Range

Sub Macro1()
'
Range("D3").Select
ActiveCell.FormulaR1C1 = "=SUMPRODUCT(R5C2:R9C2,1/R5C3:R9C3,R5C4:R9C4)"
'---(1)　計算可能だが動的に対処不可能

End Sub

Sub Macro2()

Range("D3").Select
Set 分子群 = Range(Cells(5, 2), Cells(Cells(5, 2).End(xlDown).Row, 2))
Set 分母群 = Range(Cells(5, 3), Cells(Cells(5, 3).End(xlDown).Row, 3))
Set 単価群 = Range(Cells(5, 4), Cells(Cells(5, 4).End(xlDown).Row, 4))
ActiveCell = Application.SumProduct(分子群, 分母群, 単価群)
'---(2)　エラー発生なし
ActiveCell = Application.SumProduct(分子群, 1 / 分母群, 単価群)
'---(3)　エラー発生　(1)と同じ表現（分母）にできない。

End Sub

これ以上のセル列は使わず、簡潔にD3セルへ入力するにはどうすればよろしいでしょうか？
結果数値だけでなく、関数が入力されるのが希望です。
基本的知識が乏しく、恐縮ですが　よろしくご回答をお待ちしております。------以上

回答No.4

tom04 · ベストアンサー

またまたお邪魔します。

＞実際の計算は　数千行×数千列なので時間がかかりすぎるので・・・

SUMPRODUCT関数そのものが配列数式になりますので、データ量が極端に多い場合
PCにかなりの負担をかけ、結果的にはFor～Nextでループさせた方が早い場合もあります。

そこで一案ですが、ループさせるのではなく、使っていない列（仮にE列だとします）を
作業用の列として各行の計算をし、その合計をD3セルに表示させてみてはどうでしょうか？
この場合も計算結果しか表示されませんので、メッセージボックスに計算範囲を表示させてみました。

Sub Sample2()
Dim endRow As Long
endRow = Cells(Rows.Count, "B").End(xlUp).Row
If endRow > 4 Then
Range(Cells(5, "E"), Cells(endRow, "E")).Formula = "=B5/C5*D5"
Range("D3") = WorksheetFunction.Sum(Range("E:E"))
Range("E:E").ClearContents
MsgBox "B5セル～D" & endRow & "の計算結果", vbOKOnly
End If
End Sub

※　おそらくループさせるよりもはやいと思います。
※　列も数千列！というコトですがどのような計算方法になるのか判らないので
最初の質問通りの3列のみとしています。m（_ _）m

お礼

tom04様

いただいたスクリプトを拝借して、次のようにしました。
　・1列　増設することはやむなしとして　6列目に分母の逆数を計算し
　・関数はSUBPRODUCTを使って可変行数に　対応できました。
ありがとうございました。勉強になりました。

Option Explicit
Dim i As Long
Sub macro1()
i = Cells(Rows.Count, 2).End(xlUp).Row
Range(Cells(5, 6), Cells(i, 6)).FormulaR1C1 = "=1/RC[-3]"
Cells(3, 4).FormulaR1C1 = "=SUMPRODUCT(R5C2:R" & i & "C2,R5C6:R" & i & "C6,R5C4:R" & i & "C)"
End Sub

補足

tom04様

　回答ありがとうございます。
いただいたコードを走らせ、結果が正しいことを確認しました。

1列　ダミーで　必要になるのが気になりますが、
Forループより早そうなので実際のリストで今夜確認します。

Range(Cells(5, "E"), Cells(endRow, "E")).Formula = "=B5/C5*D5"
という式は知りませんでした。

右辺が絶対番地での計算式が　5行目以外にも相対的に適用されるのが不思議です。
私は　数千行・列を扱うのでR1C1形式で考えたいのですが
このヒントでやってみて、うまくいったらまたご報告いたします。

確かにSUBPRODUCT関数にこだわる必要はないということですね。

回答No.3

tom04

続けておじゃまします。

＞同様の計算を簡潔には表現できないものでしょうか？
の件について・・・

最終行の取得がネックになっていますね。
特に関数で行う場合は前回の数式のような感じで行うしかないと思います。

これらを考慮すると今回の場合は数式の表示はあきらめて、結果だけをVBAで表示するのが一番簡単だと思います。

一例としては

Sub Sample1()
Dim i As Long, endRow As Long, vL As Variant
endRow = Cells(Rows.Count, "B").End(xlUp).Row
If endRow > 4 Then
For i = 5 To endRow
vL = vL + Cells(i, "B") / Cells(i, "C") * Cells(i, "D")
Next i
Range("D3") = vL
End If
End Sub

こんな感じでしょうかね！

※　実はVBAでSUMPRODUCT関数が使用できないか↓のようなコードも考えてみました。

Dim i As Long
i = Cells(Rows.Count, "B").End(xlUp).Row
If i > 4 Then
Range("D3") = WorksheetFunction.SumProduct(Range("B5:B" & i) / Range("C5:C" & i) * Range("D5:D" & i))
End If

これを実行してみると「型が一致しません」となり色々試行錯誤してもダメだったので
結局あきらめました。m（_ _）m

補足

tom04様

　色々考えて実行までして、いただいてありがとうございます。
前半部分のお答えのように
　Forループで　変数をVBA側で持って計算させるのはできますが
実際の計算は　数千行×数千列なので時間がかかりすぎるので回避したいところです。
様々な時間がかかっている集計の内の一部を取り出して質問しているので
時間短縮が必要です。
また　数値結果だけだと、後から見る人が検証できないというのが嫌らしいところです。
間違えていても発見できない。

後半部分がまさにやりたいことです。
ワークシートのSUMPRODUCTでは　引数に　(範囲１，1/範囲２、範囲３）とできるのに
ＶＢＡでこの関数を呼び出すと　できないのが本当にそうなのか、なにか私の使い方が違うのか
もう少し　どなたかのご指摘をお待ちします。

回答No.2

tom04

No.1です！

＞セルを変数にして表現したいのです

を見逃していました。
結局今後データが増えた場合は最終行を取得し、そのセル番地そのものを数式に入れ表示したい！
というコトですよね？

色々やってみましたが仮に最終行が20行目の場合
＞=SUMPRODUCT(B5:B20/C5:C20*D5:D20)
といった数式がD3セル入り、その計算結果が表示されるのがご希望のようですが
結構難しいように思えます。

関数で行うにしてもD3セルに
=SUMPRODUCT(INDIRECT("B5:B"&MAX(IF(B1:B1000<>"",ROW(A1:A1000))))/INDIRECT("C5:C"&MAX(IF(B1:B1000<>"",ROW(A1:A1000))))*INDIRECT("D5:D"&MAX(IF(B1:B1000<>"",ROW(A1:A1000)))))

（上記数式は配列数式となりますので、Shift＋Ctrl＋Enterで確定する必要があります。）
といった感じの数式になってしまいますので、お望み通りの数式にはならないですねぇ～～！

一番簡単なのは、VBAで5行目～最終行までの各行の計算をコツコツプラスしていく方法ではないでしょうか？
（この場合は計算結果しか表示されませんが・・・）

この程度でごめんなさいね。m（_ _）m

回答No.1

tom04

こんばんは！

結局B5～D9セルの範囲で

B列÷C列×D列　の各行の結果を
5～9行まで合計すればよい訳ですよね？
それでよいのであればごく単純に

Range("D3").Formula = "=SUMPRODUCT(B5:B9/C5:C9*D5:D9)"

だけで良いと思うのですが・・・m（_ _）m

お礼

tom04様

行き違いで　No2.の回答いただきました。　
ありがとうございます。

INDIRECT関数については　存じませんでした。勉強してみます。

同様の計算を簡潔には表現できないものでしょうか？

補足

tom04様

早速の回答ありがとうございます。

ActiveCell.FormulaR1C1 = "=SUMPRODUCT(R5C2:R9C2,1/R5C3:R9C3,R5C4:R9C4)"は

Range("D3").Formula = "=SUMPRODUCT(B5:B9/C5:C9*D5:D9)"ともかけるのですね。

　ただ質問の趣旨は　9行目までに制約されないで　任意の入力最後尾行まで　【つまり.end(xldown)】
の計算についてなので　違う回答をお願します。

ActiveCell = Application.SumProduct(分子群, 1/分母群, 単価群) も

ActiveCell = Application.SumProduct(分子群 / 分母群 * 単価群)　と書けるのかと

思って実行してみましたが、エラー発生し、通りませんでした。