【Excel】SUMPRODUCTで配列積を足し合わせる

【Excel】SUMPRODUCT関数

Excel関数において、連続して縦に並んでいたり横に並んでいたりする、ひとまとまりのデータを引数に指定すると、それは配列として扱われます。SUMPRODUCT は

=SUMPRODUCT(配列1,配列2)

のように記述して、配列1 と 配列2 のとなり同士の要素を掛け算し、さらにそれらを全て足し合わせます。たとえばセル A1 ～ A3 を配列1, B1 ～ B3 を配列2 として

=SUMPRODUCT(A1:A3,B1:B3)

と入力すると、内部では「A1 × B1 + A2 × B2 + A3 × B3」という計算をしていることになります。

SUMPRODUCTで仕入れ値の合計金額を計算します

それでは実践練習です。SUMPRODUCT を使って仕入れ値の合計金額を求めてみましょう。今回は美味しそうなパンをたくさん仕入れます。
 

商品ごとに「仕入れ値 × 数量」を求めて、それを全ての商品について足し合わせると合計金額を求められますね。こういうときに使うのが SUMPRODUCT なのです。B7 セルに

=SUMPRODUCT(B2:B6,C2:C6)

と入力してみると、合計金額 18400 がぱっと求められます。同じことを普通の SUM 関数でやろうとするとちょっと面倒です。エクセルの SUMPRODUCT は使い勝手の良い関数なので、おすすめです。うちの会社でも涼音さんがよく使っています。皆さんも『あとりえこばと出版』の本をぜひ買ってくださいね（唐突な宣伝）。

【VBA】WorksheetFunction.SumProduct

VBA のプロシージャで SUMPRODUCT 関数を呼び出すことができます。その場合、引数には配列を渡すようにします。

Option Base 1

Sub TestWsSumproduct1()

    Dim a(2) As Variant, b(2) As Variant
    Dim sp As Long

    '配列に値を格納
    a(1) = 1
    a(2) = 2
    b(1) = 10
    b(2) = 20

    'a()とb()の配列積1*2+10*20を計算
    sp = WorksheetFunction.SumProduct(a(), b())

    Debug.Print sp

End Sub

'TestWsSumproduct1()の実行結果：50

ワークシートのデータを使って配列積を計算したいときは、該当範囲を一旦配列に取り込んでから関数に渡してください。

Sub TestWsSumproduct2()

    Dim my_array As Variant
    Dim sp As Long

    'A1:A3に値をセット
    Range("A1").Value = 5
    Range("A2").Value = 10
    Range("B1").Value = 2
    Range("B2").Value = 8

    '配列変数にA1:A3を格納
    'シートから値を取り込んだ配列は二次元になることに注意
    array_1 = Range("A1:A2")
    array_2 = Range("B1:B2")

    'array_1とarray_2の配列積を計算
    sp = WorksheetFunction.SumProduct(array_1, array_2)

    Debug.Print sp

End Sub

'TestWsSumproduct2の実行結果：90