Excelのデータ集計、皆さんはどうされていますか？
SUM関数やAVERAGE関数など、基本的な関数は使いこなせているけれど、もっと複雑な条件で集計したい、エラー値を無視したい、非表示行を除外したいと思ったことはありませんか？
そんな時に役立つのが、今回ご紹介するAGGREGATE関数です。
AGGREGATE関数は、さまざまな集計関数（SUM, AVERAGE, COUNTなど）の機能を持ち合わせながら、非表示行やエラー値を無視して計算できる、まさに「集計のオールラウンダー」とも言える関数です。
AGGREGATE関数の基本的な使い方から、知っておくと便利なオプション、具体的な使用例まで、徹底的に解説していきます。
AGGREGATE関数とは？ 他の集計関数との違い
まずはじめに、AGGREGATE関数がどのような関数なのか、そしてSUM関数やAVERAGE関数といった一般的な集計関数と何が違うのかを理解しましょう。
1. AGGREGATE関数の基本構文
AGGREGATE関数は、以下の2つの書式があります。
- 配列形式:=AGGREGATE(集計方法, オプション, 配列, [k])
- 参照形式:=AGGREGATE(集計方法, オプション, 参照1, [参照2], …)
「集計方法」はどのような集計を行うかを指定する番号（SUMなら9、AVERAGEなら1など）、「オプション」は非表示行やエラー値をどう扱うかを指定する番号です。
2. 他の集計関数との比較：AGGREGATE関数の強み
従来の集計関数（SUM, AVERAGEなど）は、範囲内にエラー値が含まれていると計算結果もエラーになったり、非表示になっている行の値も集計対象になったりします。
しかし、AGGREGATE関数は「オプション」を指定することで、これらを柔軟にコントロールできるのが最大の強みです。
テストの点数の平均値を出す状況を仮定してみます。
マークシートなどで入力や取り込みエラーで佐藤さんの国語の点数にエラーが生じた場合にSUM関数の場合エラーが含まれていると結果もエラーになってしまいます。
しかし、AGGREGATE関数であればエラーを無視して算出できるためとても便利な関数になっています。
AGGREGATE関数の主要な引数とオプション
AGGREGATE関数を使いこなす上で、特に重要な「集計方法」と「オプション」について詳しく見ていきましょう。
1. 「集計方法」の種類
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：指定した順位（降順）の大きい方からN番目の値
- 15: SMALL：指定した順位（昇順）の小さい方からN番目の値
- 16: PERCENTILE.INC：指定したパーセンタイル値（0と1を含む）
- 17: QUARTILE.INC：指定した四分位数（0、25、50、75、100パーセンタイル）
- 18: PERCENTILE.EXC：指定したパーセンタイル値（0と1を含まない）
- 19: QUARTILE.EXC：指定した四分位数（0と100パーセンタイルを含まない）
2. 「オプション」で集計範囲を制御
AGGREGATE関数の真骨頂とも言えるのが「オプション」です。このオプションを設定することで、集計から除外したい要素を細かく指定できます。
- 0: ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視する
- 1: 非表示行、ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視する
- 2: エラー値、ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視する
- 3: 非表示行、エラー値、ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視する
- 4: 何も無視しない（すべてを考慮する）
- 5: 非表示行を無視する
- 6: エラー値を無視する
- 7: 非表示行とエラー値を無視する
AGGREGATE関数の具体的な使用例
それでは、実際にAGGREGATE関数を使った具体的な例を見ていきましょう。
1. エラー値を無視して合計を出す
データの中に#DIV/0!などのエラー値が含まれている場合でも、AGGREGATE関数を使えばエラーを無視して合計を算出できます。
これは先ほど例をあげた内容になります。
2. フィルターされたデータのみを合計する
オートフィルターなどでデータを絞り込んだ際に、表示されているデータだけを合計したい場合にAGGREGATE関数が威力を発揮します。
売上表を上記の様に作成し、売り上げ合計を作成したとします。
売上表のうち、店舗AだけにフィルターをかけてもSUM関数の合計は25100から変わりませんが、我々が知りたいのは店舗Aの売り上げ合計なのでAGGREGATE関数を使用します。
合計を知りたいのでSUM関数にあたる9番、非表示行を無視するオプションの5番を選択し、合計範囲を選択します。
これで店舗Aに絞り込むと合計値がSUM関数と変わっていることが分かります。
3. 条件付きの集計（LARGE/SMALL関数と組み合わせて）
AGGREGATE関数は、LARGE関数やSMALL関数の機能も内包しているため、N番目に大きい/小さい値をエラーや非表示行を考慮して求めることができます。
1番目に大きい、小さいだけ知れれば良い場合はMAX、MINを使用した方が良い場合もあります。
今回は売上トップ３を求めてみます。
集計ミスでエラー値も入っている状態で売上上位を求めます。
=AGGREGATE(14,7,G3:G11,1)
14番はLARGE関数、7番はエラーと非表示行を無視する。
算出範囲を今回はG3:G11とし、1位を求めたいので最後に１を入力しています。
AGGREGATE関数を使う際の注意点
非常に便利なAGGREGATE関数ですが、いくつか注意しておきたい点があります。
1. オプションの指定ミスに注意
オプションの番号を間違えると、意図しない結果になる可能性があります。特に、何を含めて何を無視するかを明確にしておくことが重要です。
2. 大量のデータでのパフォーマンス
非常に大量のデータを扱う場合、AGGREGATE関数が再計算に時間を要する可能性があります。
あえて自動計算をオフにすることによって操作性を向上させる手法もあります。
まとめ：AGGREGATE関数を使いこなしてデータ集計を効率化しよう！
AGGREGATE関数は、Excelでのデータ集計をより柔軟に、そして効率的に行うための強力なツールです。
エラー値の無視、非表示行の除外など、これまでの集計関数では難しかったことが、AGGREGATE関数を使えば簡単に実現できます。
この記事を参考に、ぜひAGGREGATE関数を使いこなして、日々のデータ分析やレポート作成に役立ててみてください。
次のステップ：さらにAGGREGATE関数を深掘り！
AGGREGATE関数には、今回紹介しきれなかったさらに高度な使い方や、他の関数との組み合わせによる活用法もたくさんあります。
コメント