Categories: VBA基礎

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

●実行前~実行後　※実行後、全てのデータの「単価」×「個数」が計算されて「金額合計」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

●実行前~実行後　※実行後、全てのデータの「単価」×「個数」×「軽減税率８％」の３つのデータの計算されて「税抜き価格」・「消費税」・「合計額」の計算結果が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

●実行前～実行後　※実行後、日付の範囲を指定するメッセージが表示され日付を入力します、その指定した範囲で「金額合計」と「集計件数」が計算され、結果が表示されました。
（画面クリックして拡大）

最後まで、ご覧いただきまして誠に有難うございました。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。

AKIRA