AGGREGATE関数とは
AGGREGATE関数はSUBTOTAL関数の上位互換の関数でです。
と言うと、SUBTOTAL関数ってなに？って言われる方も多いかと思います。
簡単にいうと SUM とか AVERAGE などの関数に二重集計回避の機能が付いたものが SUBTOTAL 関数です。
SUBTOTAL 関数にエラー回避や非表示行の除外に8種類の関数が対応したのが AGGREGATE 関数です。
SUBTOTAL 関数を利用するぐらいなら AGGREGATE 関数を利用することとお勧めします。
又、Excel 2010 から使える為、バージョン混在環境でもさほど気にしなくても問題ないと思います。
AGGREGATE 関数で何ができる？
主な機能をあげると下記3つの機能があります
- 二重集計の回避
- エラー値の無視
- 非表示行の無視
2重集計の回避
下記サンプルにて説明します。
SUM でまとめてしまった場合、小計も含んでしまい合計が間違った値になってしまいます。
合計を個別に足せば済む話ですか項目が多くなれば手間もかかり間違えも発生します。
代わりに SUBTOTAL と AGGREGATE を利用すれば小計を除外して合計を出す事が出来ます。
エラー値の無視
下記サンプルにて説明します。
VLOOKUP などで単価を取得しているとして、商品B2が検索先で抜けてる場合、N/A エラーが返されます。
エラーが含まれる計算式の結果は基本エラーになりますが AGGREGATE に関してはエラーを無視して計算することが出来ます。
非表示行の無視
下記サンプルにて説明します。
商品A2の行を非表示にした場合でも、SUM 及び SUBTOTAL では非表示の行を含んだ結果が返されます。
AGGREGATE では非表示行を除外した結果が返されている事が分かると思います。
AGGREGATE 関数の使い方
一番よく使うのは SUM になると思います。 その為、細かいことは置いておいて
AGGREGATE(9, 3, 範囲)
と覚えておけば事足ります。
細かな使い方は下記の通りです。
AGGREGATE(集計方法, オプション, 範囲 1, [範囲 2], …)
集計方法には該当の関数の番号を入力します（SUM の場合は 9)。
関数の種類は以下の通りです。
1 AVERAGE(平均)
2 COUNT(数値を含むセルの個数)
3 COUNTA(空白でないセルの個数)
4 MAX(最大値)
5 MIN(最小値)
6 PRODUCT(積)
7 STDEV.S(標準偏差の推定値)
8 STDEV.P(母集団の標準偏差)
9 SUM(合計)
10 VAR.S(不偏分散)
11 VAR.P(分散)
12 MEDIAN(中央値)
13 MODE.SNGL(最頻値)
14 LARGE(n番目に大きい値)
15 SMALL(n番目に小さい値)
16 PERCENTILE.INC(百分位数)
17 QUARTILE.INC(四分位数)
18 PERCENTILE.EXC(0%と100%を除いた範囲の百分位数)
19 QUARTILE.EXC(0%と100%を除いた範囲の四分位数)
オプションに関しては下記の通りです。
細かく指定する事も出来ますが基本、全部入りの指定しか使わないと思いますので 3 以外使うことはほぼありません。
0 ネストされた SUBTOTAL と AGGREGATE を無視します。
1 非表示の行、ネストされた SUBTOTAL と AGGREGATE を無視します。
2 エラー値、ネストされた SUBTOTAL と AGGREGATE を無視します。
3 非表示の行、エラー値、ネストされた SUBTOTAL と AGGREGATE を無視します。
4 何も無視しません。
5 非表示の行を無視します。
6 エラー値を無視します。
7 非表示の行とエラー値を無視します。
![Excel関数＋組み合わせ術 ［実践ビジネス入門講座］【完全版】 第2版 作業効率とクオリティがいっきに高まる究極の使いこなしテクニック 【Excel 2021/2019/Microsoft 365対応】 [実践ビジネス入門講座]](https://m.media-amazon.com/images/I/51+MAKCjOaL._SL500_.jpg "Excel関数＋組み合わせ術 ［実践ビジネス入門講座］【完全版】 第2版 作業効率とクオリティがいっきに高まる究極の使いこなしテクニック 【Excel 2021/2019/Microsoft 365対応】 [実践ビジネス入門講座]")
