Excel（エクセル）で小計以外、表示している項目のみ集計する方法/Subtotal関数の使い方

ひとつの関数で複数の計算をまかなえる謎関数、Subtotalのご紹介です。ここではSumとSubtotalの違いについて説明しますが、これがわかるとこの関数の存在意義がわかるかと思います。

Subtotal関数の使い方
- Subtotalと他の関数の違い①非表示項目を集計しない
- Subtotalと他の関数の違い②小計を計算結果に含めない
その他の集計方法

Subtotal関数の使い方

指定した計算方法で範囲を集計
Subtotal(集計方法,範囲)

こんな感じの表があるものとします（数字の意味は特に考えてません）。

数字の小計を求めたいので、=sum(C1:C7)と入力します。
ちゃんと数字の合計が表示されました。

今度は、Subtotal関数で小計を求めて見ます。=subtotal(と入力すると、何やらプルダウンが表示されます。

この1,2,3,4..と数字が割り振られているのが集計方法で、Subtotal関数では他にも最大値とか平均とか、色々な計算をこなすことができます。今回は加算をしたいので、「Sum」と書かれた9をダブルクリックします（または9,と入力）

あとはSum関数同様範囲を指定し、=SUBTOTAL(9,C1:C7)となった状態でEnterを押します。

無事に計算が実行されました。
さて、この関数はSumとどう違うのでしょう？　わざわざ覚えて使う意味あるんでしょうか？

Subtotalと他の関数の違い①非表示項目を集計しない

表に対し、挿入タブ→テーブルでテーブル化をしてみます。

さらにフィルターをかけ、東京の項目だけが表示されるようにしてみます。

小計は先ほど打ちこんだSubtotal関数のままですが、表示された項目だけが合計されていることがわかります。

しかしこれをSum関数に打ちなおすと、非表示の項目も合計値に含まれていることがわかります。

Subtotalと他の関数の違い②小計を計算結果に含めない

テーブル化を解除し、今度は最初から県と県の間で小計を求めておきます。
計算はsum関数で行います。

全ての小計を打ちこみ、これを一番下の合計で合算します。
=sum(C1:C10)のように入れると、小計も合計に含まれ、数字が実際のものより大きくなります。
なので=sum(C4,C7,C10)のようにして、小計を合計するといったやりかたが一般的です。しかし47都道府県全てとなると、かなり視認性の悪い式となります。
一か所間違えただけで直すのも大変ですし、何かの拍子にズレたらまたひとつずつ打ちなおしです。

それは少々スマートではないので、今度は小計のSum関数をSubtotal関数に打ちなおしていきます。

合計もSubtotal関数で求めます。小計も含め、=subtotal(9,C1:C10)と入力します。

小計を抜いた数値＝正しい数値が集計されました。
Subtotal関数の参照範囲にSubtotal関数がある場合、これは無視されます。
Sum関数でSubtotal関数を含んだ範囲を含んだ場合、その逆の場合は普通に間違えた結果が出ます。

このように、最初から集計を見越した作りになっているのがSubtotal関数の特徴です（ちなみにSubtotal＝「小計」という意味）。
集計方法がたくさんあるのも、ひとつずつ同様の機能を持ったSubsum関数とか、Subaverage関数とかそういうのを作りたくなかったんだろうな……と類推します。