SUMPRODUCT関数の使い道

先日、こんなツイートをしたところ、思いのほか反応があったので、今日は不肖サカイが考えるSUMPRODUCT関数の使い道について書いていこうと思います。

酒井たかま

これがなかなか奥の深い関数なんですよ。

SUMPRODUCT関数の基本的な使い方

まず、基本的な使い方から見てみましょう。

SUMPRODUCT関数で出来ることは、列×列の合計を求めることです。

例えばこの表から合計金額を出すには、この2段階で計算すると思います。

1. 単価×数量を計算
2. SUM関数で集計

これを一度にやってしまうのがSUMPRODUCT関数です。

関数の使い方はこうです。

=SUMPRODUCT(B2:B4,C2:C4)

ここでは単価の列（B2:B4）と数量の列（C2:C4)を掛けて合計しています。

結果がこちら。

ここまでがSUMPRODUCT関数の概要です。

では、実際にどんな使い道があるのかを見ていきましょう。

貸借対照表データの検算に使いたいとき

例えば財務データを入力する場合、こんな感じで入力したとします。

この入力値が正しいかどうかは、下の図のように左右の合計が一致しているかを確認し、差があればどこが間違っているのかを探せばいいのです。

このように、左右の合計が一致していることを「貸借一致」と言ったりします。

ではデータベース形式の場合どうするかというと、貸借区分（いわゆる右左区分）列を作り、左は１、右は-1とし、金額とかけて合計してゼロになれば貸借一致となっていることを確認できます。

ここまでくれば、あとは貸借区分列と2020年3月列をSUMPRODUCT関数で集計すれば、パッと検算できるのです。

=SUMPRODUCT(B2:B11,C2:C11)

そして結果がこちら。

資産項目はプラス、負債項目はマイナスとして集計し、結果ゼロとなってOKの状態です。

こんな感じで貸借対照表の検算に使ってみた事例でした。

損益計算書データから段階損益を計算したいとき

次に、損益計算書の段階損益をSUMPRODUCT関数で計算してみましょう。

こちらはちょっと応用編です。

データ例はこちらです。ここからSUMPRODUCT関数で段階損益を出していきます。

ステップ１：科目番号（例ではID）と貸借区分の追加

今回の例では売上から順に科目番号を付番します。下に行くほど大きくしてください。

そして貸借区分も。今回は収入（貸方）を１、費用（借方）をｰ1とします。

ステップ２：各段階損益で集計する項目範囲の確認

IDと貸借区分を設定したら、次に各段階損益を算出するための集計範囲を確認します。

例えば営業利益だと上から順に「売上高」「売上原価」「販管費」の合計を使うといった感じです。

そして、その場合各段階損益の計算対象の中で一番大きいIDを使います。

ここでは10行目がそれです。

ステップ３：SUMPRODUCT関数で算出

売上総利益：　=SUMPRODUCT(($B$2:$B$9<=C14)*1,$D$2:$D$9,$E$2:$E$9)
営業利益　：　=SUMPRODUCT(($B$2:$B$9<=C15)*1,$D$2:$D$9,$E$2:$E$9)
経常利益　：　=SUMPRODUCT(($B$2:$B$9<=C16)*1,$D$2:$D$9,$E$2:$E$9)
税前利益　：　=SUMPRODUCT(($B$2:$B$9<=C17)*1,$D$2:$D$9,$E$2:$E$9)
当期利益　：　=SUMPRODUCT(($B$2:$B$9<=C18)*1,$D$2:$D$9,$E$2:$E$9)

各段階損益ごとに組んだ関数がこちらです。

これで各段階損益が計算できました。

大丈夫か気になって電卓叩いたけど大丈夫でした。やるな、SUMPRODUCT。笑

目眩がしそう・・・、な内容ですが、3つの列を掛けてるだけなので、引数の対応関係を確認した上でこの数式の意味を見ていきましょう。

因数の対応関係

引数の対応関係はこのようになってます。

これを踏まえたうえで、一番重要なところがこちら↓です。

$B$2:$B$9<=C14は何を意味するのか

今回のパターンで一番重要なのはここです。

SUMPRODUCT関数では選択範囲の各々に対して条件判定することができるのです。

この条件判定の意味するところはこうです。

そして「*1」（1を乗ずる）ことで、TRUEを１に、FALSEを０に変換するのです。

これはTRUEと1が、FALSEと０が対応している仕組みを使います。

そして、この条件分岐で１か０に変換した上で、金額列・貸借区分列を乗算・集計して段階損益を出すのです。

ここまでで貸借対照表・損益計算書をデータベース形式で入力した場合の検算・段階損益の計算方法でした。

次は、集計対象を自由自在に操りたいときのSUMPRODUCT関数の使い方を見てみましょう。

集計対象を自由自在に変えたいとき

例えばこんな表。内訳が同列に入力された表ってたまにありますよね。

これをまとめてSUMで集計するとだめで、集計する対象を個別に選ばなきゃいけないけどめんどくさい。

そんな時にSUMPRODUCT関数を使って集計対象を自在に変えて集計できれば楽だよねって話です。

どうするかというと、加算対象列を追加して１か0を入れ、この加算対象列と金額列をSUMPRODUCT関数で乗算集計するだけです。

これだけ？

これだけです。

ちなみに０は入力してもしなくてもいいのですが、入力漏れが無いことを示すうえでも0と入力した方がいいでしょうね。

ちなみに、損益計算書の段階損益を出すという話、この方法を使うこともできます。

これは、各段階損益の集計範囲を１で指定してSUMPRODUCTで乗算集計する方法で、OFFSET関数で段階損益に応じて集計範囲を動かしています。

ややこしいのでOFFSET関数の説明は省きますが、こういう使い方もあるという一例です。

いずれにしても、1か０を使って対象に含める含めないを決めるというのはSUMPRODUCT関数を使う上で重要な考え方かもしれませんね。

ここまでが使い方で、次に注意点をさらっとまとめておきます。

SUMPRODUCT関数の注意点

列の範囲がずれるとエラーになる

あくまでも列と列を掛けるので、その対応関係が崩れると動きません。

こんな場合はダメです。

なので対象範囲が合ってるかは必ず確認しましょう。

SUMPRODUCT関数とテーブル化で行追加も怖くない

で、例えばデータ数が増えるのが想定されるのなら、テーブル化した上でSUMPRODUCT関数を使うことをお勧めします。

なぜなら、通常は行を追加しても引用範囲は変わらないからです。

でも、テーブル化した上でデータ追加するとテーブル範囲が自動的に追加され、引用もテーブルの列範囲に沿って引用するのでデータ追加が自動的に反映されるのです。

ちなみにテーブル化した表を使ってSUMPRODUCT関数を使うとこんな感じです。

引っ張っているところが「テーブル2[貸借区分]」とか 「テーブル2[2020年3月]」となっているところに注目してください。

これは「テーブル２の[貸借区分]列」を参照するという意味です。

この状態でデータを追加しても自動的に範囲指定されるので、参照範囲がずれるといったエラーも防ぐことができます。

まとめ

ここまで書いて気が付いたことですが、これって全部SUMIF関数使えばできるよね。。。（遅いｗｗ）

今となっては複数条件あってもSUMIFS関数でいいわけですしね。

せつない。。。。

ま、SUMPRODUCT関数も選択肢になったことでご容赦を！

最後までお付き合いいただきありがとうございました！