EXCEL VBA 積の計算・合計の集計（単価×個数）「SumProduct関数」の使い方

2019年9月22日

EXCEL VBA 積の計算・合計の集計（単価×個数）「SumProduct関数」の使い方

●はじめに

EXCELでデータ一覧を作成した際に、「単価」×「個数」などの積の計算が必要な場合、通常の計算式では「単価」×「個数」の計算式を入力しますが、「SumProduct関数」を利用する事で、簡単なステップで積の計算を実行しデータを集計する事が出来ます。それでは、「SumProduct」関数の使用方法をサンプルプログラムを交えて説明いたします。

●書式の説明　(SumProduct)

WorksheetFunction.SumProduct(範囲１,範囲２,範囲３‥‥)

※セル範囲で選択されたデータの積を計算して値を返します。

使用例：　Ans = WorksheetFunction.SumProduct(Range(“B2:B7”), Range(“C2:C7”))※注意：SumProduct(範囲１,範囲２,範囲３…)で指定する範囲は、全て同じ範囲個数に合わせなければなりません。

指定したセルの範囲同士で、積の計算を行い総合計を返します。「２つのデータの積」（SumProduct）

●プログラム説明　（サンプルプログラム①）

下記のプログラムは、SumProductを使ったサンプルプログラムになります。下表のデータ一覧から「単価」×「個数」の計算をまとめて算出します。単価と個数の範囲を指定する事て、積が求められます。結果、セルF2に「金額合計」が代入されます。

Sub SumProduct01()

    Dim Gokei As Double  '結果の値が大きくなっても対応できる様に変数の指定を「倍精度浮動小数点数型」にする。
    
    Gokei = WorksheetFunction.SumProduct(Range("C2:C25"), Range("D2:D25"))
            'C列（C2～25）まで「単価」×　D列（D2～D25）まで「個数」を計算し、計算結果を「Gokei」に代入する。
    
    Range("F2") = Gokei '総合計を表示する
  
End Sub

Sub SumProduct01()

Dim Gokei As Double '結果の値が大きくなっても対応できる様に変数の指定を「倍精度浮動小数点数型」にする。

Gokei = WorksheetFunction.SumProduct(Range("C2:C25"), Range("D2:D25"))

'C列（C2～25）まで「単価」×　D列（D2～D25）まで「個数」を計算し、計算結果を「Gokei」に代入する。

Range("F2") = Gokei '総合計を表示する

End Sub

●実行前~実行後　※実行後、全てのデータの「単価」×「個数」が計算されて「金額合計」F2に計算結果が代入されました。
（画面クリックして拡大）

指定したセルの範囲同士で、積の計算を行い総合計を返します。「３つのデータの積」（SumProduct）

●プログラム説明　（サンプルプログラム②）

下記のプログラムは、SumProductを使ったサンプルプログラムになります。下表のデータ一覧から「単価」×「個数」×「軽減税率８％」の計算をまとめて算出します。単価と個数及び税率８％の範囲を指定する事て、３つのデータの積を同時に求められます。セルH2に「税抜き価格」の合計値・セルH3に「消費税」の合計値・セルH4に「合計額」の合計値が求められます。

Sub SumProduct02()


    Dim Gokei, JTAX As Double'結果の値が大きくなっても対応できる様に変数の指定を「倍精度浮動小数点数型」にする。
    Dim lRow As Long
    
    
    lRow = Cells(Rows.Count, "A").End(xlUp).Row  'A列の最終行（品目）
    
    JTAX = WorksheetFunction.SumProduct(Range("C2:C" & lRow), Range("D2:D" & lRow), Range("E2:E" & lRow))  '「単価」×「個数」×「消費税」の積合計を消費税「JTAX」に代入
    Gokei = WorksheetFunction.SumProduct(Range("C2:C" & lRow), Range("D2:D" & lRow))  '「単価」×「個数」の積合計を合計「Gokei」に代入
    
    Range("H3") = Int(JTAX) '消費税額合計の小数点以下を切り捨てて、「消費税」に代入する。
    Range("H4") = Gokei '単価×個数の総額を「合計額」に代入する。

    Range("H2") = Gokei - Int(JTAX) '税抜額（H2）＝　合計額　－　消費税を計算し税抜額を算出する。


End Sub

Sub SumProduct02()

Dim Gokei, JTAX As Double'結果の値が大きくなっても対応できる様に変数の指定を「倍精度浮動小数点数型」にする。

Dim lRow As Long

lRow = Cells(Rows.Count, "A").End(xlUp).Row 'A列の最終行（品目）

JTAX = WorksheetFunction.SumProduct(Range("C2:C" & lRow), Range("D2:D" & lRow), Range("E2:E" & lRow)) '「単価」×「個数」×「消費税」の積合計を消費税「JTAX」に代入

Gokei = WorksheetFunction.SumProduct(Range("C2:C" & lRow), Range("D2:D" & lRow)) '「単価」×「個数」の積合計を合計「Gokei」に代入

Range("H3") = Int(JTAX) '消費税額合計の小数点以下を切り捨てて、「消費税」に代入する。

Range("H4") = Gokei '単価×個数の総額を「合計額」に代入する。

Range("H2") = Gokei - Int(JTAX) '税抜額（H2）＝　合計額　－　消費税を計算し税抜額を算出する。

End Sub

●実行前~実行後　※実行後、全てのデータの「単価」×「個数」×「軽減税率８％」の３つのデータの計算されて「税抜き価格」・「消費税」・「合計額」の計算結果がH２～H4に計算結果が代入されました。
（画面クリックして拡大）

指定したセルの範囲同士で、積の計算を行い総合計を返します。「日付範囲指定」（SumProduct）

●プログラム説明　（サンプルプログラム③）

下記のプログラムは、SumProductを使ったサンプルプログラムになります。下表のデータ一覧から「単価」×「個数」の計算をまとめて算出しますが、今回は、日付の範囲を指定する事により、その範囲内の「金額合計」の合計値や「集計件数」などに算出されます。

Sub SumProduct03()

    
    Dim Gokei, SubGokei As Double
    Dim lRow, mRow, I, Kcount As Long
    Dim Hdate As String
    
    lRow = Cells(Rows.Count, "A").End(xlUp).Row  'A列の最終行（品目）　データの最終行を把握する
    
    Gokei = 0
    Kcount = 0
    
    Do
    
        Hdate = InputBox("2019/10/1～何日までの集計を行いますか(YYYY/MM/DDで入力)")  '～までの日付を入力する。
    
    Loop Until IsDate(Hdate) '日付を正しく入力するまで繰り返す。
    
    
    For I = 2 To lRow  'データの2行目からA列の最終行（データの最終行）まで繰り返す。
    
        If Cells(I, "A") <= Hdate Then   '入力された日付の範囲以内か確認する。
                SubGokei = WorksheetFunction.SumProduct(Range("C" & I & ":C" & I), Range("D" & I & ":D" & I))   '「単価」×「個数」の積合計を合計「SubGokei」に代入
              
                Gokei = Gokei + SubGokei  '計算された「SubGokei」を「Gokei」に加算する。
                Kcount = Kcount + 1
        End If
        
    Next I
    
    Range("F2") = Gokei '総合計を表示する
    Range("G2") = Kcount '集計件数を表示する
            
End Sub