過去にも「Excel 関数内で離れた範囲を指定」記事などでも登場していた「SUBTOTAL関数」や「AGGREGATE関数」ですが、いずれも集計などをするときに使われる関数です。
ここでは、少しだけ詳しくそれぞれの特徴や違いなどをご紹介します。
今回は「SUBTOTAL関数」について述べ、次回に「AGGREGATE関数」についてお話します。
下図の表を使って「SUBTOTAL関数」の動作を説明します。
B列「入金」には収入金額が、C列「出金」には支出金額を入力します。
そして、ある区切り、例えば日ごとの小計を、「小計」と書かれた行に表示するものとし、「合計」と書かれた最終行には、収入金額の合計と支出金額の合計を求めて表示するものとします。
まず、B5セルには「=SUBTOTAL(9,B2:B4)」、C5セルには「=SUBTOTAL(9,C2:C4)」と入力されています。
B2～B4セル範囲の合計、C2～C4セル範囲の合計を求めるので「SUM」関数を使っても良さそうですが、後での集計操作のため、ここでは「SUBTOTAL」関数を使います。
同様に、B9セルには「=SUBTOTAL(9,B6:B8)」、C9セルには「=SUBTOTAL(9,C6:C8)」、B12セルには「=SUBTOTAL(9,B9:B11)」、C12セルには「=SUBTOTAL(9,C9:C11)」が、それぞれ入力されています。
最終行の B13セルには「=SUBTOTAL(9,B2:B12)」、C13セルには「=SUBTOTAL(9,C2:C12)」と、ここでも「SUBTOTAL」関数を使って集計しています。
これを「=SUM(B2:B12)」のように SUM関数を使うと、小計として求めた B5、B9、B12セルの値も含めて合計され、二重に加算されることになります。
つまり、2倍の金額になってしまいます。
もちろん、「=SUM(B5, B9, B12)」というように 3つの小計の値を合計して求めてもよいのですが、その後、日にちが追加されたりするたびに SUM関数の引数を変更しなければならず、“うっかりミス”の温床となります。
このように、集計作業するとき、「SUBTOTAL」関数の範囲内に「SUBTOTAL」関数で求めた値のセルが含まれるときは、それを無視して集計してくれるので、「SUBTOTAL」関数がよく利用されます。
「SUBTOTAL」関数の書式は次のようになります：
SUBTOTAL(集計方法, 範囲 1, [範囲 2], ...)
第1引数「集計方法」は、1～11 または 101～111 の番号を指定し、それぞれに対応して使用する関数が次のように決められています：
| 非表示の行も含める | 非表示の行を無視する | 関数 | 集計の方法 |
|---|---|---|---|
| 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 | 標本分散 |
集計方法として 101～111 の番号を指定したときは「非表示の行を無視する」集計をします。
行を非表示にしたり、アウトライン表示で折り畳んだときに、隠れている行の値については集計対象から外すという動作になります。
ただし、上図の例では、「小計」と書かれた行を非表示としても「合計」金額は変わりません。
また、「フィルタ」機能を使って絞り込んだときは、「集計方法」として 1～11 または 101～111 の番号のいずれを指定しても同じ結果となります。
