データをリスト形式にまとめた一覧表にはたいてい数値の合計やデータの件数などの集計が表示されています。一覧表を見る際にはまずオートフィルタで特定の条件を満たすデータだけを表示するものですが、SUM関数などで普通に集計をしていると表示分だけの集計にはなりません。すべてのデータが集計されてしまいます。ここはやはり表示されているデータだけの集計結果を表示させたいものですね。
そんなオートフィルタ利用時の集計に必須のSUBTOTAL関数を、Excel2013を使ってご紹介します。
オートフィルタで表示されているデータだけを集計したい
以下の「7月販売実績表」ではSUM関数を用いて全体の販売金額の合計である「168300」をG1に表示しています。
担当のオートフィルタのプルダウンメニューから佐藤さんのデータだけを表示すると…残念ながら表示は「168300」のまま変わりません。
ここに佐藤さんだけの合計である「19700」を自動的に表示させたいですね。佐々木さんを選べば佐々木さんの合計が、小林さんを選べば小林さんの合計が自動的に表示されてこそ「使える」表というものです。
それを簡単に実現してくれるのがSUBTOTAL関数です。SUBTOTAL関数をG1に用いると、オートフィルタで佐藤さんを選んだ際のG1の表示が「19700」になりました。
SUBTOTAL関数の使い方
今回のケースではG1に「=SUBTOTAL(9,G4:G24)」に記述すればOKです。これを例にSUBTOTAL関数の書き方を解説していきます。
構文
=SUBTOTAL(集計方法,参照範囲1[,参照範囲2,・・・]）
パラメータ
- 集計方法
「9」を設定することで「合計」を算出してくれます
- 参照範囲
集計対象のセルを設定します(複数設定もできます)
オートフィルタの条件を変えるたびに表示されているデータだけを自動で再計算してくれます。sumif関数のようにオートフィルタの条件や表示に合わせて集計条件や参照範囲を設定しなおす必要はありません。
合計だけじゃない！様々な集計ができる
SUBTOTAL関数の集計方法においては「9」は合計を意味します。では「9」以外の数字を設定したらどうなるのでしょう？
実は、SUBTOTAL関数は集計方法を以下のように指定することによってさまざまな集計系関数の機能を扱えるのです。
集計方法 | 相当する関数 | 集計機能 |
|---|---|---|
| 1 | average関数 | 平均 |
| 2 | count関数 | 数値の個数 |
| 3 | counta関数 | データの個数 |
| 4 | max関数 | 最大値 |
| 5 | min関数 | 最小値 |
| 6 | product関数 | 積 |
| 7 | stdev関数 | 普遍標準偏差 |
| 8 | stdevp関数 | 標本標準偏差 |
| 10 | var関数 | 不偏分散 |
| 11 | varp関数 | 標本分散 |
例えば、集計方法に「1」を設定すれば平均が表示されます。同様に、集計方法のパラメータを変えるだけで最大値や最小値の表示、データの個数のカウントなどができるのです。
オートフィル利用時には必ずSUBTOTAL関数を使おう
同じようなことはピボットテーブルでもできますが、集計結果と元データの一覧表を同時に見ながらの作業や、条件別に一覧表を出力・印刷する際などではSUBTOTAL関数のメリットが大きくなります。
一覧表の利用には基本的な集計欄とオートフィルタがつきものです。これにSUBTOTAL関数を用いればなんの変哲もない一覧表がぐっと使いやすく、アピール力を持つようになります。
