関数SUMIFで軽減税率の計算を簡単にする方法

前回の連載で紹介したCOUNTIFよりも利用頻度の高い関数として、SUMIFという関数も用意されている。こちらは「条件付きの合計」を算出する関数となる。上級者への第一歩と言われることが多い「関数SUMIF」の使い方をマスターしておけば、それだけデータ分析やデータ処理のスキルも高くなるはずだ。この機会に、ぜひ覚えておこう。

条件付きで「合計」を算出できる関数SUMIF

関数COUNTIFと同じxxxIF系の関数の中で最も利用頻度が高いのが「関数SUMIF」である。こちらは「条件付きの合計」を算出してくれる関数となる。その使い方は、関数COUNTIFと似ている部分もあれば、少し違う部分もある。勘違いしやすいポイントでもあるので、この機会に使い方を確認しておくとよいだろう。

• 

  条件付きで合計を算出する関数SUMIFの使い方

今回も具体的な例を使って「関数SUMIF」の使い方を紹介していこう。以下の図は、あるネットショップが受注した注文の「商品代金」と「実際にかかった送料」をまとめたものだ。

• 

  「商品代金」と「送料」をまとめた表

このネットショップでは送料の割引サービスを行っており、「商品代金」が5,000円以上になると「送料無料」で注文を受ける仕組みになっている。このような場合に少し気になるのが、「どれくらいの割合を送料無料で販売しているか？」という問題。これを関数SUMIFで調べてみよう。

関数COUNTIFと同様に、関数SUMIFも第2引数に「条件」を指定するだけで「条件付きの合計」を簡単に求めることができる。今回は「商品代金が5,000円以上」を条件に指定したいので、第2引数に">=5000"と記述すればよい。

• 

  5,000以上を条件に合計を算出する場合

「Enter」キーを押して結果を確認すると、4万2,280円という数値が表示された。つまり、4万2,280円分を「送料無料」で販売していた、ということになる。

• 

  関数SUMIFにより算出された合計

すべての「商品代金」の合計は5万1,410円なので、そのうちの約82％（4万2,280／5万1,410）が「送料無料」として販売されていた、という計算になる。では、実際にショップ側で負担した送料はいくらになるのだろうか？　続いては、「無料にした送料」を関数SUMIFで調べてみよう。

「条件とするセル範囲」と「合計するセル範囲」を別にするには？

「無料にした送料」を調べる場合も「商品代金のセル範囲」が条件になる。一方、実際に合計するのは「送料のセル範囲」となる。このように、「条件とするセル範囲」と「合計するセル範囲」が異なる場合であっても、関数SUMIFで「条件付きの合計」を算出することが可能だ。

「条件とするセル範囲」と「合計するセル範囲」を別に指定するときは、以下の書式で関数SUMIFを記述する。

■関数SUMIFの書式
　=SUMIF(条件とするセル範囲, 条件, 合計するセル範囲)

ここでのポイントは、合計するセル範囲を「第3引数」として指定すること。関数SUMIFに慣れていない方は、第1引数と第3引数を逆に記述してしまうケースも多いようなので、間違えないように注意しておこう。

今回の例では、「条件とするセル範囲」はE3:E12、「合計するセル範囲」はF3:F12になる。条件は先ほどと同じで">=5000"となる。

• 

  「条件とするセル範囲」と「合計するセル範囲」を別に指定する場合

このように関数SUMIFを記述すると、「商品代金」が5,000円以上の行についてのみ「送料」を合計する、という計算を実行できる。今回の例では、4,810円という結果が表示された。つまり、ショップ側で負担した送料は4,810円ということになる。

• 

  関数SUMIFにより算出された合計

もちろん、「文字」を条件にして関数SUMIFを利用することも可能だ。今度は、「PCサイト」と「モバイルサイト」について、それぞれの売上（商品代金）の合計を関数SUMIFで調べてみよう。

まずは、「PCサイト」で購入された商品代金の合計を求めてみよう。この場合、「条件とするセル範囲」はD3:D12で、条件は"=PCサイト"、「合計するセル範囲」はE3:E12になる。よって、関数SUMIFの記述は以下のようになる。

• 

  「文字」を条件に合計を算出する場合

この計算結果は、3万1,810円という数値が表示された。

• 

  関数SUMIFにより算出された合計

同様に、「モバイルサイト」で購入された商品代金の合計を関数SUMIFで求めることも可能だ。なお、「★★に等しい」を条件にするときは、「=」（イコール）の記述を省略してもよいルールになっている。よって、以下のように関数SUMIFを記述しても構わない。

• 

  "="を省略した関数SUMIFの記述

こちらは、1万9,600円という計算結果が表示された。

• 

  関数SUMIFにより算出された合計

両者を比較すると、「PCサイト」のほうが「モバイルサイト」より5割ほど高い金額になる。一方、それぞれの注文回数は「PCサイト」が4回、「モバイルサイト」が6回となっており、「モバイルサイト」のほうが多い。

これらを考慮すると、「PCサイト」のほうが高い商品を購入してくれる（もしくは多くの商品を購入してくれる）、と分析することができるだろう。

今回の例はデータ数が少なく、その信頼性は極めて低いが、関数SUMIFを使った分析手法の一例として参考にして頂ければ幸いだ。

10%と8%（軽減税率）が混在した消費税の計算

最後に、消費税の計算に関数SUMIFを活用した例を紹介していこう。以下に示した図は、企業研修をサポートする会社がExcelで見積書を作成した例となる。この会社では、研修に使う教材や備品だけでなく、昼食や飲料なども手配して配達する業務を担っている。

このように消費税率が10%の商品と、8%の商品（食料・飲料など）が混在している場合は、あらかじめ「それぞれの合計」を算出してから消費税を求めなければならない。

• 

  消費税率の異なる商品が混在した見積書

そこで、軽減税率の対象になる商品の先頭に「★」の記号を付けることで、各商品の消費税率を区別できるように工夫した。よって、「品名が★で始まるか？」をSUMIFの条件に指定すると、それぞれの合計金額を求めることができる。

まずは、軽減税率（8%）の対象となる商品の合計金額を関数SUMIFで求めてみよう。「条件とするセル範囲」はB5:B13で、条件は「品名が★で始まる」なので"★*"となる（※）。一方、「合計するセル範囲」はE5:E13となる。よって、以下のように関数SUMIFを記述すればよい。

（※）「*」（ワイルドカード）の詳しい使い方は前回の連載を参照。

• 

  「軽減税率の対象になる金額」の合計の算出

続いて、この金額に0.08を掛け算すると、軽減税率になる商品の消費税（8%）を算出できる。

• 

  消費税（8%）の計算

同様に、通常の消費税率（10%）となる商品の合計金額を求めていこう。こちらは「品名が★で始まらない」を条件に指定すればよいので、第2引数の記述は"<>★*"となる。

• 

  「軽減税率の対象にならない金額」の合計の算出

あとは、関数SUMIFで算出した小計に0.10を掛け算するだけ。これで通常の消費税率になる商品の消費税（10%）を算出できる。

• 

  消費税（10%）の計算

最後に、「小計」と「10%対象の消費税」、「8%対象の消費税」を足し算すると、税込みの合計金額を求められる。

• 

  「税込み合計金額」の計算

• 

  算出された「税込み合計金額」

このように、データを分類して「合計」を算出しなければならないケースは意外と多い。このとき、関数SUMIFの使い方を知っている場合とそうでない場合では、作業効率に雲泥の差がでるだろう。Excelを活用するうえで必須の関数となるので、この機会に関数SUMIFの使い方を必ずマスターしておこう。