今回は「SUMPRODUCT」という関数の応用的な使い方を紹介していこう。関数SUMPRODUCTは(単価)×(数量)などの「合計」を自動算出してくれる、一見すると便利そうな関数だ。しかし、実際に使用される機会は意外と少ないようである。その理由と、関数SUMPRODUCTの少し応用的な使い方を紹介していこう。
関数SUMPRODUCTの基本的な使い方
まずは「関数SUMPRODUCT」の基本的な使い方から紹介していこう。この関数は、SUM(合計)とPRODUCT(掛け算)を組み合わせた関数で、(単価)×(数量)の「合計」を手軽に算出したい場合などに活用できる。
具体的な例で説明していこう。以下の図は、ある公演におけるチケットの販売状況を集計したものだ。チケットは「前売り券」と「当日券」の2種類があり、座席の場所(S席、A席、B席)に応じて「チケット単価」が変化する仕組みになっている。さらに、当日券のA席、B席には「学割」や「シニア割」も用意されている。
この場合、各行で「チケット単価」×「販売数」を計算して、それを関数SUMで「合計」すると、売上の合計金額を求めることができる。
このように、それぞれの行で(単価)×(数量)などの「掛け算」を計算し、それを「合計」する、といった場合に活用できるのが関数SUMPRODUCTだ。
関数SUMPRODUCTを使用するときは、あらかじめ(単価)×(数量)を計算しておく必要はない。引数に「各列のセル範囲」を指定するだけで、「単価×数量の合計」を求めることが可能となる。
「Enter」キーを押して関数SUMPRODUCTを実行すると、冒頭に示した図と同じ「\1,046,900」という結果が表示される。
このように、あらかじめ(単価)×(数量)を計算しておかなくても、合計金額を手軽に算出できるのが関数SUMPRODUCTの利点となる。もちろん、各行のデータは(単価)と(数量)でなくても構わない。「掛け算」して処理する数値データなら何でもOKだ。
ただし、実務で使用するとなると、関数SUMPRODUCTは微妙な存在になってしまう。というのも、上図に示したような表は「状況を把握しにくい表」になってしまうからだ。
唐突に合計金額だけを表示するのではなく、最初に示した図のように(単価)×(数量)の計算結果も表示した方が、「一般的で見やすい表になる」と感じる方は多いのではないだろうか?
Excelに慣れている方なら、(単価)×(数量)の数式を入力して、それをオートフィルでコピーする、といった作業はたいした手間にならないはずだ。この手間を省略した結果「わかりづらい表」になってしまうくらいなら、普通に処理した方が無難である。このように考えると、関数SUMPRODUCTは無用の長物になってしまう。
関数SUMPRODUCTの応用的な使い方(1)
ということで、続いては、関数SUMPRODUCTを少し応用的に活用した例を紹介していこう。関数SUMPRODUCTを便利に活用するための豆知識として、参考にして頂ければ幸いだ。
たとえば、「仮に、それぞれのチケットをプラス80円で販売していたら、合計金額はいくらになっていただろうか?」をExcelでシミュレーションする場合を考えてみよう。
この場合、それぞれの「チケット単価」をプラス80円した数値に修正してあげる必要がある。具体的には、「\4,800」を「\4,880」に修正、「\5,500」を「\5,580」に修正……といった作業を以下の図に示した10個のセルで行わなければならない。
このような場合に関数SUMPRODUCTを活用すると、すべての「チケット単価」を手軽にプラス80円することが可能となる。その方法は、「チケット単価」のセル範囲(E4:E13)をカッコで囲み、その後に「+80」の数式を追加するだけ。
これで、それぞれの「チケット単価」をプラス80円にした場合の合計金額を算出できる。今回の例では「\1,070,900」という結果が表示された。
このように、「各列のデータ」を一律にプラス(マイナス)して計算したい場合に、関数SUMPRODUCTが便利に活用できる。
関数SUMPRODUCTの応用的な使い方(2)
続いては、関数SUMPRODUCTを使って「条件付きの合計」を算出する方法を紹介していこう。まずは、条件を指定するためのセルを用意する。ここでは、区分が「前売り」のチケットについてのみ、売上の合計金額を求めてみよう。
次は、関数SUMPRODUCTの入力だ。まずは、第1引数に「条件」を記述する。この記述は「セル範囲=条件値」となる。今回の例では、前売り/当日の区分が「B4:B13」のセル範囲に入力されているので、「B4:B13=D16」と条件を記述すればよい。
続いて、「掛け算」するセル範囲を指定していく。「チケット単価」のセル範囲(E4:E13)は、条件の後に「掛け算」する形で記述する。「販売数」のセル範囲(F4:F13)は、そのまま第2引数として記述すればよい。
このように関数SUMPRODUCTを記述すると、条件に合うデータについてのみ、(単価)×(数量)の合計を求めることが可能となる。今回の例では、区分が「前売り」のデータについてのみ、「チケット単価」×「販売数」の合計を求めることができる。
条件を「当日」に変更した例も紹介しておこう。この場合は「\710,100」という計算結果が表示された。
念のため、「なぜ条件付きの合計を算出できるのか?」について補足説明しておこう。「セル範囲=条件値」で指定した条件式の結果は、TRUE(真)またはFALSE(偽)になる。条件に合致する場合はTRUE、合致しない場合はFALSEとなる。ここで注目すべきポイントは、TRUEは「数値の1」、FALSEは「数値の0」として扱われることだ。
TRUEの場合は、第1引数で「数値の1」×「チケット単価」という計算が行わる。この結果は「チケット単価」と同じ数値になる。さらに、第2引数の「販売数」を掛け算した値が「合計する数値」として加算されていく。
一方、FALSEの場合は、第1引数で「数値の0」×「チケット単価」という計算が行わる。この結果は必ず0(ゼロ)になる。ここに第2引数の「販売数」を掛け算しても、結果は0(ゼロ)のまま変わらない。つまり、「合計する数値」としては加算されない、ということになる。
このような処理が各行で行われた結果、条件に合うデータについてのみ(単価)×(数量)の値が加算されていき、「条件付きの合計」が求められる、という仕組みだ。
条件を2つに増やして、複数条件に対応させることも可能だ。たとえば、以下のように関数SUMPRODUCTを記述すると、「区分」と「種類」の2つを条件に、(単価)×(数量)の合計を求められるようになる。
もちろん、条件を変更すると、それに応じた計算結果が表示される。以下の図は、種類の条件を「学割」に変更した場合の例だ。
今度は、区分の条件を「前売り」に変更した例を紹介しておこう。この場合、「前売り」かつ「学割」というチケットは存在しないので、その合計金額は「\0」という結果になる。
このように「条件付きの合計」を算出したい場合にも関数SUMPRODUCTが活用できる。少し強引な応用例かもしれないが、このような使い方があることを知っておいても損はないだろう。
なお、「わざわざ関数SUMPRODUCTを使わなくても、関数SUMで代用できる」と考えることも可能だ。この場合は、「条件」と「セル範囲」をすべて掛け算で処理してあげればよい。先ほど示した例の場合、以下のように関数SUMを記述しても同様の結果を得ることができる。
=SUM((B4:B13=D16)(D4:D13=D17)E4:E13*F4:F13)
今回の連載は「関数SUMPRODUCTの効果的な使い方」とは言い難い側面もあるが、このような関数があることを知っておくと、いずれ役に立つかもしれない。Excel関数を活用するときの参考にしていただければ幸いだ。