SUBTOTAL関数をご存じですか?
SUMやVLOOKUPと比べると、使用頻度はそれほど高くないかもしれませんが、じつはなかなか便利な関数です。
大量の生データをエクセルに入力し、いろいろ異なった切り口で集計する時に使います。
これまで、おもにデータ解析に利用することの多かった関数ですが、今後、ビジネス現場での利用頻度が高まりそうです。
なぜなら、(1)消費税率が変わり(10%と軽減税率8%の2本立て)、かつ(2)2023年10月から、インボイス制度(適格請求書等保存方式)が導入されるからです。
この記事では、請求書テンプレートを新仕様へ作りかえる時に、SUBTOTAL関数を使うとどのように便利なのか、という点についてご紹介いたします。
SUBTOTAL関数とは?
英単語のsubtotalには、「小計する」という意味があります(totalが「合計する」ですから、それより小くくり(サブ)の計算を行う、ということですね)。
そのため、ExcelのSUBTOTAL関数も、小計に使うことが最も多いですが、それ以外にも「平均」「データ個数」「最大値」「最小値」など、様々なデータ集計の場面で利用できます。
Excel 2010/2013/2016以降のバージョンでは、集計機能がさらに強化されたAGGREGATE関数も使えますので、その活用方法については、後日、別記事で解説します。
さて、SUBTOTAL関数の構文は、
=SUBTOTAL(集計方法,参照範囲1,［参照範囲2］...)となります。「集計方法」は1-111までの数字を必ず入力することが必要で、各数字ごとの意味は下記のリスト通りとなります。
「集計方法」のところに、1-11までの数字を入力した場合には、たとえばフィルタなどで隠したデータも集計対象に含めますが、101-111までの数字とした場合には、非表示セルは集計対象には含めません。
適格請求書には、なぜSUBTOTAL関数が良いのでしょうか?
それでは、なぜSUBTOTAL関数が、適格請求書のテンプレートを作成するのに適しているのでしょうか? それは、消費税率10%対象商品と、軽減税率8%対象商品、それぞれの「小計」を必ず計算することが、適格請求書の要件だからです。
基本的には「足し算」の積み上げ作業となりますので、SUM関数で代用することも可能です。しかし、小計を含む集計表をSUMだけで作ろうとすると、作り替えるたびにメンテナンスが必要であったり、計算ミスを誘発する可能性も増えます。そこで、SUMの替わりにSUBTOTALを用いるのです。
これは、消費税の軽減税率8%商品と、税率10%商品を1枚の請求書で列記した事例ですが、適格請求書では、必ずそれぞれの「金額内訳」「税額内訳」を明示する必要があります。
そのため、税率ごとに「小計」欄を設けて、
=SUBTOTAL(9,”軽減税率8%商品の本体価格(または税額)”)
=SUBTOTAL(9,”税率10%商品の本体価格(または税額)”)という関数を入れることで、各税率ごとの「本体価格小計」「税額小計」を算出します。
最後に、明細の最下行に「全商品 合計」の欄を設けますが、ここへ入力する関数もSUMではなく、
=SUBTOTAL(9,”全商品の本体価格(または税額)”)とするのです。全商品合計を計算する関数の参照範囲には、2件(8% or 10%)の小計セルも含みますが、ここへもSUBTOTAL関数を使用することで、参照範囲内に含まれている小計セル(SUBTOTAL関数が入力されたセル)は集計からスキップされて、それ以外の数値の合計が返されるのです。
注意;このように、小計をSUBTOTAL関数で算出する「小計+合計」の集計表を作成する場合には、必ず、全ての集計行をSUBTOTAL関数で記述しなければいけません。合計行だけをSUM関数で計算してしまうと、小計(SUBTOTAL関数の計算結果)を無視しない集計結果となり、実勢数値2倍の計算結果が得られてしまう計算ミスとなります。
まとめ
複数条件の集計表をミスなく、効率的に計算するには、SUM関数よりSUBTOTAL関数の方が便利です
冒頭で構文をご紹介したように、本来はデータ個数や標準偏差なども求めることができる集計関数で、おもに分析や研究の場面などに活用されることが多かったSUBTOTAL。
しかし、消費税率変更やインボイス制度導入など、世の中の仕組みが複雑になってきているため、ビジネス現場でも応用できることが増えてきました。
適格請求書テンプレートを作る以外にも、販売実績データの集計・分析や、顧客リストの整理などに応用すれば、今まで気づけなかった傾向を見つけ出すキッカケが得られるかもしれません。
「SUBTOTALとSUMを混在させない」という鉄則だけはキッチリ守って、まずはこれまでSUMで作ってきた書類のいくつかを、一度SUBTOTAL関数に置き換えてみてください。
ひとつの生データから集計・分析できる応用範囲が、大きく広がりますよ。
