excel subtotalの活用：【画像付き】エクセルのサブトータル関数と、その使い方やよくある疑問の解答－SUM関数との差分は何ですか？

さまざまな計算ができるSUBTOTAL関数を使えていますか？
この関数を使えばいいということは分かるけど、使い方が分からないし難しいと悩んでいませんか？

SUBTOTAL関数は合計や平均、データの個数や最大値など複数の計算ができる関数です。
特にこの関数はフィルターと組み合わせて使うことができる関数です。

ここではそんなSUBTOTAL関数の基本から、この関数の疑問や便利な計算方法を解説しています。
この記事を読んで、SUBTOTAL関数を使えるようになっていただければ幸いです。

1.SUBTOTAL関数の基本

SUBTOTAL関数はさまざまな種類の集計をすることができる関数です。
たとえば似ている関数としてSUM関数がありますが、SUM関数は合算するだけに対し、SUBTOTAL関数は条件次第で合算も、積算も平均も求められます。

実にSUBTOTAL関数だけで11種類の集計が可能です。

1-1.SUM関数との違い

SUM関数との違いといえば、SUBTOTAL関数はSUBTOTAL関数の結果を計算に含めないということです。

下の図を見てください。※C列の式がB列に入っています。

赤字の合計と青字の合計があり、最後にSUBTOTAL関数で間のSUBTOTAL関数の計算結果を含む合計を出したものと、同じ範囲をSUM関数で合計したものです。

SUBTOTAL関数の場合は「10」で、表の間にあるSUBTOTAL関数の計算結果は含んでいません。
対してSUM関数はSUBTOTAL関数の結果も合算しています。

SUBTOTAL関数が含まない式結果はSUBTOTAL関数で計算した結果だけで、たとえばSUM関数で計算した結果はSUBTOTAL関数であっても計算に含みます。

1-2.SUBTOTAL関数の使い方

SUBTOTAL関数は以下のように書きます。

集計する参照先は範囲参照でも個別でもかまいません。
個別に指定する場合は254個まで設定することが可能です。

集計方法は1～11、または101～111の数値を書きます。

A1からA5の合計したい場合は「=SUBTOTAL(9, A1:A5)」と書きます。
A1とB3の平均を出したい場合は「=SUBTOTAL(1, A1, B3)」と書きます。

集計方法番号が1～11と100番台の101～111の2種類ありますが、その違いは「手動で非表示にしたセルを計算に含むか含まないか」です。

たとえば1行目と3行目を非表示にした表を見てください。※C列の式がB列に入っています。

1行目と3行目には「2」が入っています。
集計方法はどちらも合算ですが、非表示行を含むか含まないかで結果が違いますね。

このような100番台の非表示を含まない計算は、対象セルを「手動で非表示にした場合のみ」適用されます。
フィルターを使ってセルを非表示にした場合は、非表示であっても計算に含みますのでご注意ください。

1-3.フィルターと組み合わせて使う

SUBTOTAL関数で便利な機能といえば、「フィルターをしたときに非表示セルを含まず計算すること」です。

手動で非表示にした場合は、100番台のみ非表示セルの内容を含みませんでしたが、フィルターの場合100番台でなくても計算に含みません。
つまり集計方法が9でも109でも同じ結果になるということです。

下図の右側の表は左側の表の品目にフィルターをかけています。
合計1は集計方法9、合計2は集計方法109ですが、フィルターをかける前も後も同じ結果ですね？

しかしフィルターをかけると、フィルターで非表示となった行は合計に含まれていません。

このようにフィルター後の計算ができるので、フィルターで抽出された数値だけを指定したいときは、SUBTOTAL関数を使うとスマートに計算できるでしょう。

2.SUBTOTAL関数の疑問の解消法

SUBTOTAL関数の基本の使い方は理解できたでしょうか。
集計方法番号だけ覚えるには数がありますが、よく使うのは「合算の9」や「平均の1」じゃないでしょうか。

次はSUBTOTAL関数を使用していて、「こんなときはどうするんだろう？」といった疑問を解決します。

2-1. SUBTOTAL関数の結果を計算するには？

SUBATOTLA関数の結果をSUBTOTAL関数では計算に含むことができないことは、前述の冒頭で触れました。

繰り返しになりますが、下図の最後の2行の合計結果を見てください。

SUBTOTAL関数で合計した場合は、間のSUBTOTAL関数の合計結果を含んでいませんが、SUM関数だと含んでいます。

結果、SUBTOTAL関数の結果を含んだ計算の場合はSUM関数を使うことが望ましいでしょう。

同じように平均の場合はAVERAGE関数を使うとよいでしょうし、個数の場合はCOUNT関数などを使います。
つまりそれぞれSUBTOTAL関数の集計方法の元となる関数を使用すると、SUBTOTAL関数の結果を含んだ計算を行うことができます。

（例）平均、数値の個数の場合

2-2.フィルターの条件指定を使いこなすには？

SUBTOTAL関数を使いこなすための補足ですが、フィルターの方法をおさらいしましょう。

フィルターにはさまざまな条件指定ができます。
基本的には項目をフィルターするのではないでしょうか。

他にも表に色がついている場合は、色フィルターも使用できます。

他にテキストフィルターには「指定の値を含む/含まない」や「指定の値以上/以下」など細かい設定も可能です。

エクセルのフィルターを使いこなすことで、さらにSUBTOTAL関数を活かすことができますのでぜひ色フィルターもテキストフィルターの内容も覚えてくださいね。

2-3.一気に小計から合計まで計算するには？

でも小計がたくさんあって、何度もSUBTOTAL関数を書いて計算していくのは少し面倒ですよね。

そんなときは「オートSUM」機能を活用しましょう。
これを覚えていればSUBTOTAL関数をたくさん書かなくて済みます。

「オートSUM」は[ホーム]タブの編集にある「Σ」アイコンです。

アイコン横の三角マークで開くと、機能リストが表示されます。
見て分かる通り、「オートSUM」と言いながら合計だけでなく、平均や数値の個数などあらゆる計算が可能です。

使い方は簡単で、結果を入れたい空白セルをCtrlキーで複数選択して、この機能リストから計算方法を選ぶだけです。

実際に図を見て行ってみましょう。
今回は小計と合計を求めます。
まず小計と合計の空白セルをそれぞれ選択していきます。

そして「オートSUM」の「合計」を選択すると、一気にそれぞれ値が挿入されます。

ここでそれぞれのセルに入ったのは値ではなく式です。
上から「=SUM(B1:B2)」「=SUM(B4:B5)」「=SUM(B6,B3)」と入っています。

さらに下の表のような総計を出すことも可能です。

挿入される式は自動的にエクセルが判定しているので、式の確認は必要かもしれませんがこれならSUBTOTAL関数を何度も書く必要はありません。

ただしフィルターと組み合わせては使えませんし、合計だけ横に出したいというような表の規則性を崩すような使い方はできません。

使えるケースであればとても便利な機能ですので、この機会にぜひ使ってみてください。

3.まとめ

SUBTOTAL関数は便利ですが、SUM関数など単純な関数と比べて複雑です。
それはこの関数が1つで複数の機能を持つためです。

扱いに気をつければ頼りがいのある関数ですが、他の関数と混じって使用していると計算が合わずに作業が滞ってしまいかねない式になることもあります。
複雑な表や計算で使う場合は、十分この関数の特性を理解して使用してください。