[Excel] SUMPRODUCT関数で掛け算と足し算を一気に実行

今回は、Excel(エクセル)のSUMPRODUCT(サムプロダクト)関数を使って、複数の商品単価と個数を掛け算し、合計値を求める方法を確認します。SUMPRODUCT関数のメリットは、条件式と配列を組み合わせることで高度な計算を容易に行えること。応用範囲の広い関数なので、まずは基本的な使い方を覚えましょう。

乗算と加算を同時に行えるSUMPRODUCT関数

たとえば、複数の商品単価と売上個数を入力した表の場合、すべての項目の合計値(すなわち売上の合計金額)を求めるには、各商品ごとに単価×個数を算出し、それらの金額を足すのが基本です。単価と個数を入力したセルの右に売上金額を計算するセルを用意すれば、SUM関数で合計金額を表示できますが、この方法では売上金額を求める掛け算と合計金額を求める足し算の2つの数式を入力する必要があります。これを1つの数式で実行できるのがSUMPRODUCT関数です。

基本の式は、=SUMPRODUCT([配列1],[配列2])

SUMPRODUCTは、その名のとおり足し算(SUM)と掛け算(PRODUCT)をまとめて実行できる関数です。まずは、単価×個数で求めた売上金額の合計を計算してみましょう。計算結果を入力したいセル(画面の例ではB8)を選択したら(図1)、数式バーに「=SUMPRODUCT(」と入力(図2)。単価を入力したセル(画面の例ではC2～C6)をドラッグして数式に反映させます(図3)。

• (図1)まずは数式を入力したいセル(B8)をクリックして選択します

• (図2)数式バーに「=SUMPRODUCT(」と入力します

• (図3)C2～C6セルをドラッグして数式に反映させます

続いて「,」(カンマ)で区切りを入れたら個数を入力したセル(画面の例ではD2～D6)をドラッグして数式に反映させ、「)」で閉じて数式を完成させます(図4)。[Enter]キーで数式を実行すると、C2×D2～C6×D6の合計値が算出されます(図5)。このように、SUMPRODUCT関数を使えば掛け算とその和をまとめて計算できます。

• (図4)「=SUMPRODUCT(C2:C6,D2:D6)」という数式を完成させます

• (図5)実行すると足し算と掛け算がまとめて行われて結果が表示されます

条件式を設定することも可能

SUMPRODUCT関数では条件式を設定することも可能です。たとえば今回の例ならば、特定の商品名(商品A)で特定の担当者(佐藤)の売上合計を簡単に求めることができます。

数式を入力したいセルを選択したら、数式バーに「=SUMPRODUCT((」と入力し、商品名を入力したセル(画面の例ではA2～A6)をドラッグして数式に反映(図6)、続けて「="商品A")」と入力して「商品A」という条件を設定します(図7)。「*」(アスタリスク)で区切ったら、「(B2:B6="佐藤")」と入力し、B2～B6セルで「佐藤」という条件を設定(図8)。「,」で区切って単価(C2～C6セル)を選択(図9)、さらに個数(D2～D6セル)を選択して数式を完成させます(図10)。

• (図6)「=SUMPRODUCT((」と入力して、A1～A6セルを選択します

• (図7)「=SUMPRODUCT((A2:A6="商品A")」と入力します

• (図8)「*」で区切って「(B2:B6="佐藤")」と入力します

• (図9)「,」で区切り、C2～C6セルをドラッグして数式に反映させます

• (図10)同様に、D2~D6セルも選択し「=SUMPRODUCT((A2:A6="商品A")*(B2:B6="佐藤"),C2:C6,D2:D6)」という数式を完成させます

[Enter]キーを押して実行すると、商品名が「商品A」で担当が「佐藤」と入力された行(3と6)の単価×個数の合計値が表示されます(図11)。このように条件を指定することで、複雑な計算も簡単に行うことができます。

• (図11)「商品A」「佐藤」の条件に合致した行の単価×個数の合計値が表示されます

条件式を付ける際の注意事項

なお、SUMPRODUCT関数で条件式を設定する場合は「=SUBTOTAL(([条件式1])*[(条件式2]),[配列1],[配列2])」とする必要があります。このため、今回の例で「商品A」だけを条件に設定したい場合でも、「=SUMPRODUCT((A2:A6="商品A"),C2:C6,D2:D6)」では実行できません。同じ条件式を2つ入れて数式を構成することで、正しい値を表示できます(図12)(図13)。条件式を使ううえでのコツとして覚えておくと便利です。

• (図12)「=SUMPRODUCT((A2:A6="商品A")*(A2:A6="商品A),C2:C6,D2:D6)」と、同じ条件を2回入力して数式を作成します

• (図13)設定した条件で計算が実行されました