どうもasです。
本日はExcelのSUBTOTAL関数について解説していきます。
それではいってみましょう～。
SUBTOTAL関数
用途
数える、合計する、平均する等の11種類の集計を行います。
同じデータで様々な視点で分析したいときに利用すると便利です。
基本形
=SUBTOTAL(集計方法,参照1[,参照2,…,参照N])※N＝1~254
ワンポイント
参照値は最大254個の数値を指定できます。
使用例
さまざまな集計をする
上図のようにSUBTOTALには様々な集計方法があり、同じデータから様々な数値を求める事ができます。
参照値は同じ範囲を指定して、集計方法の数字を変えるだけで集計方法を切り替えることができます。
集計方法
集計方法を指定する数字は1～11、または101～111です。それぞれ同じ機能を持つ関数が存在します。
また、非表示セルとは参照値を指定した範囲内のセルに非表示となっているセルが含まれている場合を指します。
参照値を指定した範囲内に非表示セルが無い場合は1～11、101～111どちらの集計方法を使っても同じ結果になります。
集計方法は下記のとおりです。
| 集計方法 | 参照Nに対する 集計内容 | 対応関数 （ ）内は2007の表記 | |
| 非表示セルの集計 | |||
| する | しない | ||
| 1 | 101 | 平均 | AVRAGE |
| 2 | 102 | 数値の個数 | COUNT |
| 3 | 103 | 空白以外の個数 | COUNTA |
| 4 | 104 | 最大値 | MAX |
| 5 | 105 | 最小値 | MIN |
| 6 | 106 | 掛け算（積） | PRODUCT |
| 7 | 107 | 標本標準偏差 | STDEV.S(STDEV) |
| 8 | 108 | 標準偏差 | STDEV.P(STDEVP) |
| 9 | 109 | 合計 | SUM |
| 10 | 110 | 不偏分散 | VAR.S(VAR) |
| 11 | 111 | 分散 | VAR.P(VARP) |
非表示セル
1～11、101～111を使った際にセル表示の非表示の有無による結果の違いが下図のとおりです。
下図では例として9と109の『SUM』を使っています。
- 非表示が無いパターンです。この場合、いずれの集計方法も参照値として指定した範囲内全ての数値を集計します。
- 行を右クリックで非表示にするかExcel上部のリボンの『書式』から行を非表示にした場合です。この場合、1～11の集計方法は参照値として指定した範囲内全ての数値を集計しますが、101～111の集計方法では非表示にしたセル内の数値は集計しません。
- フィルター機能を使って行を非表示にした場合です。この場合、1～11、101～111の集計方法のいずれも非表示にしたセル内の数値は集計しません。
ワンポイント
列を非表示にした場合は1～11、101～111の集計方法いずれも結果は変わらず参照値として指定した範囲内の全ての数値を集計します。
小計値を除外する
上図の左は小計にSUBTOTAL関数を使った場合で、右が使わなかった場合です。
このようにSUBTOTAL関数の参照値に指定した範囲内に別のSUBTOTAL関数が含まれている場合は、集計から除外します。
小計にSUMを使用した場合は除外されませんので、状況に応じて使い分けましょう。
集計方法の1～11と101～111はどちらを使うべきか
結論としては101～111をお勧めします。
101～111であれば、非表示にする方法に例無く全て集計から除外してくれるためです。
いちいち例外を考慮する必要が無く覚えやすいですね。
まとめ記事
Excel関数のまとめ記事です。
是非参考にしてください(^-^)
