エクセルを使い始めて最初に覚える関数はと聞いたら恐らくSUM関数を挙げる人が少なくないのではないかと思います。
1. SUM関数とは
SUM関数は数値を自動的に合計する関数です。SUM は英語で合計という意味です。式も簡単なので理解しやすいと思います。下の例ではC列の小計部分でそれぞれ使用されています。
2. SUBTOTAL関数とは
エクセルを使い続けていると SUBTOTALという関数を目にする機会も出てくると思います。Subtotal は英語で小計という意味だというのはご存知だと思います。ちなみにSUBTOTAL 関数は数字を足すだけでなく平均値などを出す場合にも使うことが出来ますが、実務では小計を求める時に使うケースがほとんどでないかと思います。
SUM関数とSUBTOTAL関数の違い
この２つの関数は似ていて非なる部分も持ち合わせています。それを知っているかいないかで、使い勝手も場合によっては変わってくることもあります。
1. SUBTOTAL関数はSUBTOTAL関数で求められた小計を加算しない
上の例はラーメン屋さんのとある日の売上と思ってください。
ラーメンの売上計（小計）とご飯ものの売上計（小計）をそれぞれ出して、最後に総計を求めています。総計は31,500です。そして、そのやり方としてC列とF列でSUM関数を使った場合と、SUBTOTAL 関数を使った場合を示しています。
C列ではラーメン売上とご飯ものの売上をそれぞれSUM関数で小計を求め、最後に2つの小計を足して総計を出しています。特に問題や論点はないと思います。
一方、F列では小計も総計もSUBTOTAL関数を使っていますが、総計の式を見てください。 F1からF9までの数値を全て合計しています。 その中には小計も含まれているにも関わらず、総計の数字はC列と同様に31,500です。
そうです。SUBTOTAL関数は途中のSUBTOTAL関数で計算された小計を加算しないように設定されています。これはとても便利です。上の例では小計が2つしかないからあまりピンとこないかもしれませんが、数値が数百行にまたがっていて、その途中に小計が何十個もあるような長いデータの時に、いちいち上の例のC列のようなやり方でやっていたら時間がかかって仕様がありません。SUBTOTAL関数のようにデータの頭からおしりまで一気に範囲を選択しても小計が加算されなければ、短時間で総計を求められます。
2. SUBTOTAL関数はオートフィルターと組み合わせると可視セルのみを合計する
次の例です。商品別の売上と購入者の性別のデータがあり、合計にはSUBTOTAL関数を使用した場合（B列）とSUM関数を使用した場合（F列）が使用されています。合計金額は2,760です。また、分析のためにオートフィルタが設定されています。
オートフィルターで商品Aだけを表示させるとSUBTOTAL関数を使用した場合の合計金額は600に変わりました。しかし、SUM関数を使用した場合の合計は2,760のままです。
そして更に女性の顧客への売上に絞り込むとSUBTOTAL関数の合計は200に変わり、SUM関数の合計は相変わらず2,760のままです。
そうです。SUBTOTAL 関数の２つめの特徴はオートフィルターを使うと可視セルのみしか合計しないということです。SUM関数だと隠れたセルに数字が入っているとそれも合計してしまうのです。
本来のSUBTOTAL関数の集計方法
上の例ではSUBTOTAL関数の式は 「=SUBTOTAL(9,」となっています。この「9」の部分は集計方法を示す式となっています。9は数字の合計を意味します。他には平均値や個数などを求めることも出来ます。
さて、ご存知の方もいると思いますが、SUBTOTAL関数の集計方法には9ではなく109が使用される場合もあります。どちらも数値の合計に変わりはないのですが、違いは隠された行を合計するかしないかの違いです。
先ほどの例を使用して、今度はF列にSUBTOTAL(109)で合計してB列のSUBTOTAL(9)との比較をします。合計は両者とも2,760です。そして今回はオートフィルターを解除しました。
今回は商品Aだけを表示するのにオートフィルターでなく手作業で商品A以外の行を隠しました。B列の合計＝SUBTOTAL(9)は2,760ですが、F列の合計＝SUBTOTAL(109）は600となっています。
オートフィルターを設定した場合は9、109どちらの集計方法でも結果は同じですが、オートフィルターを設定してない場合は、合計の額が変わってくるので注意が必要です。
ちなみに、オートフィルターを設定した状態で行を手動で隠した場合も合計金額は変わってきます（9,109ともに）。