Excelデータ分析の基本ワザ (21) フィルター利用時は関数SUBTOTALで合計を求める

今回は、フィルターを使って抽出したデータから合計や平均などを求める方法を解説していこう。通常、合計を求めるときは関数SUM()、平均を求めるときは関数AVERAGE()を使用するが、フィルターを利用するときは関数SUBTOTAL()を使うのが基本となる。

フィルター利用時の関数SUM()の挙動

今回も、前回と同じ「Tシャツの販売実績をまとめた表」を使って解説を進めていこう。

Tシャツの販売実績をまとめた表

前回と異なる点は、各列の合計を求めるために「関数SUM()を入力した行」が用意されていることだ。合計を求める行は「データ行」として扱われないように、空白行を間に挟んで配置してある。

各列の合計を求める行

もちろん、このような場合もフィルターによりデータを自由に抽出することが可能である。ただし、関数SUM()により算出される合計は、抽出条件を指定しても何も変化しない。

データを抽出した表（合計は変わらない）

せっかくフィルターを活用するのであれば、抽出したデータについてのみ「合計」や「平均」を算出したいと思うはず。そうすれば、さまざまな角度からデータを分析できるようになる。ただし、関数SUM()や関数AVERAGE()では思い通りの結果を得ることはできない。これらの関数により算出される値は、フィルターの抽出条件に関係なく、常に「引数に指定したセル範囲」の計算結果となってしまう。

関数SUBTOTAL()を使った集計

このような場合に活用できるのが関数SUBTOTAL()である。関数SUBTOTAL()は、データベースの集計に利用できる関数で、「合計」をはじめ、「平均値」や「分散」などの指標も算出することが可能となっている。

まずは、関数SUBTOTAL()の使い方から紹介していこう。

■関数SUBTOTAL()の書式
　　＝SUBTOTAL(集計方法, 集計するセル範囲)

関数SUBTOTAL()の第1引数には「集計方法」を指定する。ここに指定できる値は1～11（または101～111）の数値で、それぞれ以下の集計方法を指定するものとなっている。なお、カッコ内に記した関数は、同様の計算を行う関数を示している。

■関数SUBTOTAL()の第1引数に指定できる数値

数値	内容
1（または101）	平均値（AVERAGE）
2（または102）	数値の個数（COUNT）
3（または103）	データの個数（COUNTA）
4（または104）	最大値（MAX）
5（または105）	最小値（MIN）
6（または106）	積（PRODUCT）
7（または107）	不偏標準偏差（STDEV、STDEV.S）
8（または108）	標準偏差（STDEVP、STDEV.P）
9（または109）	合計（SUM）
10（または110）	不偏分散（VARS、VAR.S）
11（または111）	分散（VARP、VAR.P）

たとえば、合計を算出したい場合は「9」を、平均値を算出したい場合は「1」を第1引数に指定すればよい。

第2引数には、データを集計するセル範囲を指定する。この部分の記述は関数SUM()や関数AVERAGE()と同じで、複数のセル範囲をカンマで区切って列記することも可能となっている。

今回の例では、各列の3行目から101行目までデータが入力されているので、合計を求めたい場合は関数SUBTOTAL()を以下のように記述すればよい。

合計を求める関数SUBTOTAL()の入力

同様に、各列に関数SUBTOTAL()を入力していくと（またはオートフィルでコピーすると）、各列の合計を求めることができる。

関数SUBTOTAL()により算出された合計（抽出条件なし）

関数SUBTOTAL()の利点は、「フィルターにより抽出したデータ」に応じて合計などを再計算してくれることにある。以下は、色が「赤」の条件を指定してデータを抽出した場合の例となる。表示されたデータに応じて、合計の計算結果が変化することを確認できるだろう。

関数SUBTOTAL()により算出された合計（抽出条件あり）

このように関数SUBTOTAL()を使うと、そのときどきの状況に合わせて合計などを求めることが可能となる。フィルターと非常に相性のよい関数なので、ぜひ使い方をマスターしておこう。

Excelらしい使い方をすると・・・

関数SUBTOTAL()の第1引数に指定する「集計方法」は、セル参照でも指定できる。よって、以下の図のように「Excelらしい使い方」をすることも可能である。この例では、D105セルで集計方法を指定することにより、「平均値」「最大値」「最小値」「合計」を切り替えられるように工夫してある。

集計方法をセル参照にした関数SUBTOTAL()

たとえば、D105セルの値を「1」を変更すると、（抽出されたデータについて）各列の平均値を求めることが可能となる。

集計方法の変更

このように工夫することで、さまざまな角度からデータを分析できるようになり、便利に活用できそうに見える。しかし、実は、これは最適な手法とはいえない。というのも、いちいち集計方法の数値で指定しなければならないし、「1は平均値、9は合計、・・・」などを覚えておくのも面倒になるからである。

「平均値」「最大値」「最小値」「合計」といった4つ程度の指標を求めるのであれば、以下の図のように各指標を求める関数SUBTOTAL()を個別に用意して、いちどに示した方が使い勝手は格段に向上する。

集計用の行を複数用意した例

第1引数をセル参照にする手法は、「Excelを使いこなせます」と見栄を張っているようなもので、実際の使い勝手はあまり良くない。もっとシンプルに考えて、必要な指標を並べて示したほうが、効率よくデータ分析を進められるだろう。「どの指標が表示されているのか？」を確認するために、集計方法（D105セル）の数値を確認する手間もなくなる。

なお、サンプル調査を分析する場合などは、「最大値」や「最小値」の代わりに「不偏分散」や「不偏標準偏差」などの指標を表示しておくのも効果的である。

テーブルの集計行を利用する

抽出したデータに合わせて「合計」や「平均」などの値を算出したいときは、「テーブルとして書式変換」の「集計行」も活用できる。この機能の使い方は、第19回の連載で詳しく解説しているので、あわせて参照しておくとよいだろう。

表をテーブルに変換

「集計行」の追加

各列の集計結果を表示するときは、「集計行」にあるセルを選択し、▼ボタンから集計方法を指定すればよい。

集計方法の指定

あとはフィルター機能を使ってデータを抽出するだけ。すると、抽出したデータに応じて集計結果が変化するようになる。

フィルターによりデータを抽出した場合

もちろん、集計方法をあとから変更することも可能だ。この場合は、「集計行」にあるセルをクリックし、▼ボタンから集計方法を選択しなおせばよい。

集計方法の変更

ただし、集計方法をまとめて変更する方法は用意されていないようで、以下の図のように「平均」を算出するには、各列ごとに▼ボタンの操作を行わなければいけない。これは、かなり面倒な作業であり、正直、使いやすいとは言えない。

平均を算出した場合

さらに、集計行は1行しか表示できないため、「合計」と「平均値」を常に表示しておく、といった使い方にも対応しない。データ分析をスムーズに進めたいのであれば、「集計行」を利用するのではなく、関数SUBTOTAL()を自分で入力した方が使い勝手は向上するだろう。このあたりは、実際に試してみながら、各自の使いやすい方法を見つけていく必要があるだろう。

なお、次回の連載では、フィルターの抽出条件をより手軽に指定する方法を紹介する予定だ。こちらもフィルターを活用する際に便利な機能となるので、ぜひ使い方を覚えておこう。