Excel。19種類の集計ができる。AGGREGATE関数をもっと使ってみよう！
＜AGGREGATE関数＞
SUBTOTAL関数をグレードアップというか、機能拡張した関数があります。
それが、AGGREGATE関数。
SUBTOTALは「小計」という意味ですね。
それに対して、AGGREGATEは「集計」という意味ですね。
なんで、SUBTOTAL関数があるのに、AGGREGATE関数を使うのと思うかもしれませんが、「小計」と「集計」ということで、似て非なる関数なわけです。
結果的に、19種類の集計を行うことができるというのが、AGGREGATE関数の強みとなったわけです。
AGGREGATE関数の読み方は「アグリゲイト」です。
AGGREGATE関数の引数も確認しておきましょう。
AGGREGATE（集計方法,オプション,参照1,…）
AGGREGATE（集計方法,オプション,配列,[順位])
19種類の集計を行えるということで、2種類の引数を持っているのが特徴です。
集計の種類で使い分けます。
そして、このAGGREGATE関数は、『手入力』で数式を作ることをおススメします。
その理由は、集計方法やオプションに集計の種類ごとなどに番号が割り振られています。
関数挿入ダイアログボックスだと、その番号がわかりません。
また、関数の分類は、「数学・三角」に所属しています。
次の表を使って確認してみましょう。
今回は、H1に、データが非表示になっても連動して、売上高の第3位を算出してみます。
関数を作る前に、G1の「3位」は、ユーザー定義をつかって、表示形式で「位」を表示させています。G1の内容は、3です。
ポイントなのは、上位何番目のデータを算出できる。
LARGE関数は行の非表示には対応していませんし、SUBTOTAL関数では、LARGE関数のような「上位何番目」を算出する方法はもっていません。
なぜならば、SUBTOTAL関数は「小計」を算出する関数ですから。
それでは、H1に入力していきましょう。
最初に設定する引数が、「集計方法」。今回は、LARGEの「14」をつかいます。
次が、「オプション」。
「非表示の行を無視します」をクリックすることで、行が非表示になった時でもリアクションしてくれるようになります。
配列には、売上高のD2:D11を設定し、順位はG1を設定します。
完成した数式は、
=AGGREGATE(14,5,D2:D11,G1)
結果を確認してみましょう。
上位3番目の数値が算出することができました。
では、データを非表示にしてみましょう。
このように、数値が連動して変わったことが確認できました。
用途によって、SUBTOTAL関数とAGGREGATE関数が使い分けられるようになるといいのかもしれませんが、算出結果があっていれば、どっちでもいいというのが、現場レベルというところなんでしょうね。
最後に、集計方法とオプションを紹介しておきます。
『集計方法』
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 何番目に大きな値
15 SMALL 何番目の小さな値
16 PERCENTILE.INC 百分位数 0%以上～100%以下
17 QUARTILE.INC 四分位数 0%以上～100%以下
18 PERCENTILE.EXC 百分位数 0%超～100%未満
19 QUARTILE.EXC 四分位数 0%超～100%未満
こんな番号。覚えられないよね。
『オプション』
0 集計値の無視
1 集計値の無視と非表示行の無視
2 集計値の無視とエラーの無視
3 集計値の無視と非表示の無視とエラーの無視
4 何も対象外にしない
5 非表示行の無視
6 エラーの無視
7 非表示行の無視とエラーの無視
