エクセル(Excel)表計算でフィルタかけて表示されている数値のみ集計【関数ワザ】

エクセル(Excel)表計算でフィルタかけて表示されている数値のみ集計

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

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

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

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

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

 

目次

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

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

 

AGGREGATE関数

SUBTOTAL関数(11種類の集計方法)が19種類に増えます。
[オプション]の指定により、リスト内のエラーや非表示行を無視した集計が可能になります。

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

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

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

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

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


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


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

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

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

 

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

 

サンプル

 

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

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

 

SUM関数

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

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

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

 

SUBTOTAL関数

 

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

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

 

SUBTOTAL関数 オートフィルタ

 

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

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

 

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

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

 

行の非表示

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

 

集計方法「109」を指定

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

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

 

「SUBTOTAL」 関数の引数の設定

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

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

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

引数「集計方法」

フィルタのみで
非表示のセルを除外
フィルタと行の非表示で
非表示のセルを除外
求められる
関数
集計内容
1101AVERAGE平均を求める
2102COUNT数値のセルを数える
3103COUNTA空白でないセルを数える
4104MAX最大値を取得する
5105MIN最小値を取得する
6106PRODUCT掛け算で合計を求める
7107STDEV母集団の標本から標準偏差を求める
8108STDEVP母集団の全体から標準偏差を求める
9109SUM合計を求める
10110VAR母集団の標本から不偏分散を求める
11111VARP母集団の全体から不偏分散を求める

 

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

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

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

 

おわりに

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

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

コメント

コメントする

目次