SUBTOTAL 関数
【書式】
SUBTOTAL関数には、3つの特徴があります。
- いろいろな計算ができる
- 自分自身は計算しない
- 見えているセルだけ計算する
いろいろな計算ができる
一般的なワークシート関数は、ひとつの計算しかできません。SUM関数は合計を求めることしかできませんし、MAX関数は最大値を求めることしかできません。しかし、SUBTOTAL関数は11種類の計算ができます。SUBTOTAL関数でどんな計算をするかは、引数「集計方法」に番号で指定します。
| 集計方法 | 計算 |
|---|---|
| 1または101 | 平均値を求める |
| 2または102 | 数値の個数を求める |
| 3または103 | データの個数を求める |
| 4または104 | 最大値を求める |
| 5または105 | 最小値を求める |
| 6または106 | 積を求める |
| 7または107 | 不偏標準偏差を求める |
| 8または108 | 標本標準偏差を求める |
| 9または109 | 合計値を求める |
| 10または110 | 不偏分散を求める |
| 11または111 | 標本分散を求める |
たとえば、引数「集計方法」に3または103を指定すると、データの個数をカウントするCOUNTA関数と同じ働きをします。引数に指定する「1～11」など1番台と「101～111」など100番台の違いは後述します。
ひとつで、いろいろな計算をするという変わり種のSUBTOTAL関数は、[データ]タブにある[小計]機能のために作られた関数です。
自分自身は計算しない
表の中で、一部を「小計」として計算し、最後に「合計」を求めるようなとき、普通のSUM関数などを使うと次のようになります。
B列の一番下(セルB10)で求める総合計は、その上のセル範囲B2:B9を単純に足してしまうと、上図のように望む結果になりません。セルB10のSUM関数で計算している範囲に、別のSUM関数が使われていても、SUM関数は律儀にすべて合計してしまいます。これをSUBTOTAL関数でやると、次のようになります。
セルB10のSUBTOTAL関数が計算している範囲(B2:B9)に、別のSUBTOTAL関数が存在すると、セルB10のSUBTOTAL関数は、別のSUBTOTAL関数を除外して計算を行います。つまり、このような小計と合計を求めるときに、とても便利な関数だということです。
見えているセルだけ計算する
上図のような表に「1列目が"田中"である」というオートフィルタを設定します。
この見えているB列だけの合計を求めたいとき、SUM関数ではうまくいきません。
こんなときこそ、SUBTOTAL関数です。
このようにSUBTOTAL関数は、オートフィルタで絞り込んだ結果をカウントしたり、合計したりするときに威力を発揮します。
1番台と100番台の違い
SUBTOTAL関数は、同じ計算の種類に、1番台と100番台の2つの番号が割り当てられています。
両者は、上述「見えているセルだけ計算する」の"見えている"セルが、どのような方法で見えているのかによって動作が異なります。先のように、オートフィルタの結果"見えている"セルでは、1番台と100番台は同じ結果になります。
では、オートフィルタではなく、手動で行を非表示にしてみましょう。
1番台と100番台の違いは、SUBTOTAL関数が「計算範囲の中で何を除外するか」です。オートフィルタで非表示になったセルだけを計算から除外し、手動操作で非表示になったセルは計算するのが1番台です。一方の100番台は、オートフィルタの結果であろうが、手動操作であろうが、とにかく非表示になっているセルはすべて計算から除外します。一般的にSUBTOTAL関数は、オートフィルタの結果に対して使われることが多いです。であれば、どちらを指定しても同じことです。