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

Question

ＶＢＡに詳しい方へ

部品単価積み上げなどで　部品単価×分子員数／分母員数の合計を計算するときに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セルへ入力するにはどうすればよろしいでしょうか？
結果数値だけでなく、関数が入力されるのが希望です。
基本的知識が乏しく、恐縮ですが　よろしくご回答をお待ちしております。------以上

tom04 · Accepted Answer

またまたお邪魔します。

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

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 · Answer

続けておじゃまします。

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

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

これらを考慮すると今回の場合は数式の表示はあきらめて、結果だけを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 · Answer

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

tom04 · Answer

こんばんは！

結局B5～D9セルの範囲で

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

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

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

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

またまたお邪魔します。

この回答への補足

続けておじゃまします。

この回答への補足

No.1です！

こんばんは！

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング