SUBTOTAL関数の意外と知られていない便利な2つの使い方 | エクセルテクニック応用編

SUBTOTAL関数の意外と知られていない便利な2つの使い方 | エクセルテクニック応用編 1024 667 Biz Tips Collection

SUBTOTAL関数は、知名度が意外と低いが、とても便利な関数だ。知っていても、2つのうちの1つの使い方しか知らない人も多い。SUBTOTAL関数を使えば、様々な操作を簡単に実施できるので、覚えて損はないだろう。

そもそもSUBTOTAL関数とは?

SUBTOTALは、データを集計する関数だ。
入力方法は、以下の通りだ。

=SUBTOTAL( 集計方法 , セル範囲 )

集計方法は、以下を指定できる。一番よく使うのは、SUM(合計)だろう。
1=AVERAGE
2=COUNT
3=COUNTA
4=MAX
5=MIN
6=PRODUCT
7=STDEV
8=STDEVP
9=SUM
10=VAR
11=VARP

そして、特徴として以下の2点がある。
①フィルターされたセルを計算しない
②SUBTOTAL関数同士を計算しない

それぞれ以下で解説していく。

SUBTOTALの使い方① フィルターを排除した計算結果を簡単に出せる

SUBTOTAL関数の特性の1つは、フィルターしたセルを計算しないことだ。
つまり、属性が複数ある際、フィルターをちょっといじるだけで、すぐ合計などの集計値を出すことが可能だ。

例を見てみよう。

上記の通り、例えば「A社」でフィルターすると、自動的に今表示されているセルだけで合計を出してくれる。
SUMIF関数などを属性ごとに作るよりも、よっぽど楽だ。複数の属性があればあるほど、より便利だろう。

SUBTOTALの使い方② 簡単に小計を記載できる。

SUBTOTAL関数のもう1つの特性は、SUBTOTAL関数は他のSUBTOTAL関数の数値を無視して集計するということだ。
以下の図を見ればわかりやすいだろう。

以上の図では、「合計売上」と各「小計」にSUBTOTAL関数を使っている。
「合計売上」のSUBTOTAL関数の選択範囲は、D6:D15(D6~D15全て)だ。SUBTOTAL関数は他のSUBTOTAL関数を計算しないため、このシンプルな選択範囲で合計値を算出可能だ。
もしこれをSUM関数を利用して実行しようとすると、選択範囲を「D6:D8,D10:D12,D14」と選択しなければならない。これは選択するのも面倒であれば、ちょっと表が変わっただけでいちいち調整しなければならない。SUBTOTAL関数を使えば、これが簡単に設定できるのである。

SUBTOTAL関数の注意点

SUBTOTAL関数を使う上で、一点注意がある。それは、フィルターされたセルは計算しないが、通常、手動で非表示されたセルは計算に含まれることだ。
これは実は、手動で非表示されたセルも計算に含まないように設定することも可能だ。その場合、集計方法として始めに紹介した数値でなく、以下の数値を入れればよい。

集計方法(手動で非表示にしたセルを計算しない場合)
101=AVERAGE
102=COUNT
103=COUNTA
104=MAX
105=MIN
106=PRODUCT
107=STDEV
108=STDEVP
109=SUM
110=VAR
111=VARP

SUBTOTALで集計表の作成を簡単に!

今回は意外と便利な関数であるSUBTOTAL関数を紹介した。
SUBTOTAL関数は、以下の2つの特徴があるデータ集計の関数だ。
①フィルターされたセルを計算しない
②SUBTOTAL関数同士を計算しない
このように、便利な関数を1つずつ覚えていくのが、エクセルを使いこなすための近道だ。