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

calculator IT

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

Advertisements

Subtotal関数の使い方

指定した計算方法で範囲を集計
Subtotal(集計方法,範囲)
subtotal1
こんな感じの表があるものとします(数字の意味は特に考えてません)。
subtotal2
数字の小計を求めたいので、=sum(C1:C7)と入力します。
ちゃんと数字の合計が表示されました。
subtotal3
今度は、Subtotal関数で小計を求めて見ます。=subtotal(と入力すると、何やらプルダウンが表示されます。
subtotal4
この1,2,3,4..と数字が割り振られているのが集計方法で、Subtotal関数では他にも最大値とか平均とか、色々な計算をこなすことができます。今回は加算をしたいので、「Sum」と書かれた9をダブルクリックします(または9,と入力)
subtotal5
あとはSum関数同様範囲を指定し、=SUBTOTAL(9,C1:C7)となった状態でEnterを押します。
subtotal6
無事に計算が実行されました。
さて、この関数はSumとどう違うのでしょう? わざわざ覚えて使う意味あるんでしょうか?

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

subtotal7
表に対し、挿入タブテーブルでテーブル化をしてみます。
subtotal8
さらにフィルターをかけ、東京の項目だけが表示されるようにしてみます。
subtotal9
小計は先ほど打ちこんだSubtotal関数のままですが、表示された項目だけが合計されていることがわかります。
subtotal10
しかしこれをSum関数に打ちなおすと、非表示の項目も合計値に含まれていることがわかります。

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

subtotal11
テーブル化を解除し、今度は最初から県と県の間で小計を求めておきます。
計算はsum関数で行います。
subtotal12
全ての小計を打ちこみ、これを一番下の合計で合算します。
=sum(C1:C10)のように入れると、小計も合計に含まれ、数字が実際のものより大きくなります。
なので=sum(C4,C7,C10)のようにして、小計を合計するといったやりかたが一般的です。しかし47都道府県全てとなると、かなり視認性の悪い式となります。
一か所間違えただけで直すのも大変ですし、何かの拍子にズレたらまたひとつずつ打ちなおしです。
subtotal13
それは少々スマートではないので、今度は小計のSum関数をSubtotal関数に打ちなおしていきます。
subtotal14
合計もSubtotal関数で求めます。小計も含め、=subtotal(9,C1:C10)と入力します。
subtotal15
小計を抜いた数値=正しい数値が集計されました。
Subtotal関数の参照範囲にSubtotal関数がある場合、これは無視されます。
Sum関数でSubtotal関数を含んだ範囲を含んだ場合、その逆の場合は普通に間違えた結果が出ます。

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

その他の集計方法

subtotal16
集計値に「1」を指定すると、Average(平均)を求めます。
subtotal22
「2」と「3」はCount(数値個数)およびCountA(データ個数)、
subtotal17
「4」はMax(最大値)、
subtotal18
「5」はMin(最小値)、
subtotal19
「6」はProduct(積)
subtotal20
「7」と「8」はStdev(不偏標準偏差)およびStdevP(標準偏差)、
subtotal21
「10」と「11」はVar(不偏分散)およびVarP(分散)となります。

他に101~もありますが、ちょっと特殊なのでまたの機会に。
Subtotal関数の使い方については以上です。

やりたいことから方法を探すエクセルExcel操作・関数・VBA(マクロ)逆引きまとめ
逆引き(やりたいことから探す)Excel記事まとめ
関数・演算子・メソッド・プロパティ名から探すExcel/VBA(マクロ)使い方・組み合わせ方まとめ
こちらはExcelやメソッドの諸機能を、機能の名称から探せるまとめ記事です。

コメント