【Excel】ｴｸｾﾙ 表計算でフィルタかけて表示されている数値のみ集計【関数ﾜｻﾞ】

「フィルタを掛けたとき」や「非表示にした際」に、表示されているセルの数値のみを集計する方法の紹介です。

エクセルで「SUM関数」を使った集計ではオートフィルタによる条件抽出や、行の非表示を行うと表示された値のみでの自動再計算はされません。

「SUBTOTAL関数」を使用することで、作成した集計リストを頻繁に修正する場合に有効な使い方が出来ます。

リスト形式をなしていないデータの集計にも利用できます。

1つの関数を入力するだけで１１種類もの関数が実現出来ます。

　

「SUBTOTAL」関数を使った集計方法

表示されている数値の合計・カウントを行うには、SUBTOTAL関数やAGGREGATE関数が使用できます。

　

表示されている数値の合計・カウントを行うだけであれば、SUBTOTAL関数で簡単に求めることができます。

ここでは「SUBTOTAL関数」で表示されている数値（セル）のみ「合計」・「カウント」する方法を使った方法の紹介です。

合計　　：数値を足します。
カウント：数値のセルを数えます。

表示されたセルだけ計算できる「SUBTOTAL」関数とは？

オートフィルタなどを使って見たいデータだけ表示させることができますよね。

その際に「SUM関数」で集計を設定していると、表示されていない数値も集計されてしまいます。

SUBTOTAL関数を使えば、表示された値のみを集計することができます。

SUBTOTAL(集計方法, 範囲1)で指定します。

詳細は「SUBTOTAL 関数の引数で設定」で説明します。

　

参考にサンプルを使って説明です。

　

　

集計に「SUM」関数を設定した場合

小計や合計に「SUM」関数を使っています。

　

オートフィルタで10月を非表示にしました。SUM関数を使って集計した場合、10月分も含んだ集計のまま計算されています。

合計の集計に「SUBTOTAL」関数を設定した場合

小計が含まれたセル範囲の合計を計算する際に、SUBTOTAL関数を用いると連続したセル範囲で引数を指定して、小計を求めるSUBTOTAL関数を使ったセルがある場合は、それらのセルは除外して合計されます。

　

　

３Q、４Qの集計、合計に「SUBTOTAL」関数を設定。引数の「集計方法」で「9」を指定します。

集計方法「９」で、オートフィルタの場合は非表示された数値は除外されますが、「行の非表示」にした際は、除外されません。

　

　

オートフィルタで11月を非表示にすると集計が見えている10月、12月の値だけが集計されています。

このように見えているセルの数値だけが集計されました。

　

「行の非表示」でも見えている値だけを集計

オートフィルタを使わず「行の非表示」を行った場合でも集計させることが出来ます。

　

３Q、４Qの集計、合計の「SUBTOTAL」関数の引数の「集計方法」で「109」を指定します。

　

集計方法を「109」に指定すると、行の非表示設定を行っても見えている数値だけの集計が出来ます。

このように表の作成内容や、集計方法によって「集計方法」の指定を変えることで思う様な集計ができるようになります。

　

「SUBTOTAL」 関数の引数の設定

「SUBTOTAL」 関数は合計以外にも、「個数」、「平均」、「最大値」、「最小値」など１１の「集計方法」が指定できます。

SUBTOTAL(集計方法, 範囲1)
SUBTOTAL(集計方法, 範囲1, 範囲2 …)
指定した集計方法ですべての範囲を集計します。

引数「集計方法」：集計に使用する関数を番号で指定します。
　　　　　　　　　行が非表示になっているセルを含めるか選択できます。
引数「範囲」　　：セルの範囲を指定できます。

引数「集計方法」

フィルタのみで 非表示のセルを除外	フィルタと行の非表示で 非表示のセルを除外	求められる 関数	集計内容
1	101	AVERAGE	平均を求める
2	102	COUNT	数値のセルを数える
3	103	COUNTA	空白でないセルを数える
4	104	MAX	最大値を取得する
5	105	MIN	最小値を取得する
6	106	PRODUCT	掛け算で合計を求める
7	107	STDEV	母集団の標本から標準偏差を求める
8	108	STDEVP	母集団の全体から標準偏差を求める
9	109	SUM	合計を求める
10	110	VAR	母集団の標本から不偏分散を求める
11	111	VARP	母集団の全体から不偏分散を求める

　

引数の集計方法には、「非表示のセル」を「含める」場合と、「含めない」場合の指定が可能です。

使い分けをすることでご自分がやりたい表を作成します。

SUBTOTAL関数とAGGREGATE関数共に、横方向の合計で列が非表示になっている場合は除外することができません。
縦方向の集計で表は作ります。　

　

おわりに

表示された数だけの計算をさせる「SUBTOTAL」関数の紹介でした。

この関数は、引数で集計方法を指定することで１１種類の集計が出来ます。