ExcelのAGGREGATE関数は、より柔軟で高度な集計機能を提供する強力な関数です。通常のSUMやAVERAGE関数と違い、AGGREGATE関数はフィルタ後のデータや、エラーを含むデータも無視して集計できるため、データの分析や集計作業を効率化することができます。
この記事では、AGGREGATE関数の基本的な使い方から、実用的な応用方法までを詳しく解説します。
AGGREGATE関数とは？
AGGREGATE関数は、指定した集計方法に基づいて、データを集計したり計算したりするための関数です。AGGREGATE関数の特徴は、エラーや非表示のデータを無視するオプションがあり、フィルタを使ったデータ分析や、エラー処理を行いたい場合に非常に便利です。
AGGREGATE関数の基本的な構文
=AGGREGATE(集計方法, オプション, 範囲1, [範囲2, …])
- 集計方法: 実行したい集計の種類を指定します（例：合計、平均、最大値など）。
- オプション: 集計時に無視する要素を指定します（例：エラー、非表示セルなど）。
- 範囲: 集計したいデータの範囲を指定します。
集計方法のコード一覧
AGGREGATE関数は、様々な集計方法に対応しています。以下に、主な集計方法のコードを示します。
| コード | 集計方法 |
| 1 | AVERAGE（平均） |
| 2 | COUNT（個数） |
| 3 | COUNTA（非空セルの個数） |
| 4 | MAX（最大値） |
| 5 | MIN（最小値） |
| 6 | PRODUCT（積） |
| 7 | STDEV.S（標本標準偏差） |
| 9 | SUM（合計） |
| 11 | MEDIAN（中央値） |
| 12 | MODE（最頻値） |
オプションのコード一覧
オプションコードを使って、集計時に無視したい要素を指定します。例えば、エラーを無視したい、手動で非表示にされたセルを無視したい、などの選択が可能です。
| コード | 無視する要素 |
| 0 | 無視しない |
| 1 | 非表示セルを無視 |
| 2 | エラー値を無視 |
| 3 | 非表示セルとエラー値を無視 |
| 4 | フィルタで非表示の行を無視 |
| 5 | フィルタで非表示の行とエラー値を無視 |
| 6 | 非表示セルを無視して、フィルタを無視 |
| 7 | 非表示セルとエラー値を無視し、フィルタを無視 |
実際の例
例えば、以下のような売上データがあったとします。
| A | B |
| 商品名 | 売上金額 |
| 商品A | 100 |
| 商品B | 200 |
| 商品C | #N/A |
| 商品D | 150 |
このデータで、エラー値（#N/A）を無視して売上金額の合計を計算したい場合、AGGREGATE関数を次のように使います。
=AGGREGATE(9, 2, B2:B5)
ここでは、集計方法に「9」（合計）、オプションに「2」（エラー値を無視）を指定しています。この場合、#N/Aエラーを無視して「450」（100 + 200 + 150）が返されます。
AGGREGATE関数の応用例
フィルタ後のデータを無視して合計を計算
フィルタ機能を使って特定のデータを非表示にした際、表示されているデータだけを集計することが可能です。例えば、フィルタを使って商品Aと商品Bだけを表示した場合、それらの売上金額だけを合計したいときには次のようにします。
=AGGREGATE(9, 4, B2:B5)
オプション「4」を使うことで、フィルタで非表示になっている行を無視して、表示されているデータだけを集計できます。
エラーと非表示セルを無視して平均を計算
次に、エラー値や手動で非表示にされたデータを無視しつつ、残りのデータの平均を計算したい場合、次のようにします。
=AGGREGATE(1, 3, B2:B5)
ここでは、集計方法「1」（平均）とオプション「3」（エラー値と手動で非表示にしたセルを無視）を指定しています。この式は、#N/Aエラーや非表示セルを無視して、表示されているデータの平均を計算します。
フィルタされたデータの中央値を計算
フィルタで非表示にされたデータを無視して中央値を求めることも可能です。例えば、売上データの中央値を計算したい場合は、次のようにします。
=AGGREGATE(11, 4, B2:B5)
この式では、集計方法「11」（中央値）とオプション「4」（フィルタで非表示の行を無視）を指定しています。
最大値や最小値を計算
AGGREGATE関数を使えば、フィルタ後のデータやエラー値を無視して最大値や最小値を簡単に求めることができます。例えば、エラーを無視して最大値を求めたい場合は次のように入力します。
=AGGREGATE(4, 2, B2:B5)
この場合、エラーを無視してB列の最大値「200」が返されます。
AGGREGATE関数の便利な豆知識
フィルタ機能との相性抜群
AGGREGATE関数は、フィルタで非表示にされたデータを無視して集計できるため、大量のデータをフィルタで絞り込んだ場合でも、必要なデータだけを簡単に集計できます。これにより、データを部分的に表示しているときでも正確な結果を得ることができます。
通常のSUM関数やAVERAGE関数の代替として
SUMやAVERAGE関数を使うと、フィルタで非表示になっているデータやエラー値も含まれてしまうことがあります。AGGREGATE関数を使えば、特定の要素を無視して正確な集計が行えるため、通常のSUMやAVERAGE関数よりも柔軟に対応できる場面が多いです。
ネストした集計も可能
AGGREGATE関数を他の関数と組み合わせることで、さらに複雑な集計が可能です。例えば、IF関数と組み合わせて、特定の条件に基づく集計を行うこともできます。
=IF(A2 = “商品A”, AGGREGATE(9, 2, B2:B5), “該当なし”)
この式では、「商品A」が条件に該当する場合にのみ、売上金額の合計が計算されます。
AGGREGATE関数のよくあるエラーと対処法
#VALUE!エラー
AGGREGATE関数で#VALUE!エラーが発生する場合、集計方法やオプションに誤りがある可能性があります。集計方法とオプションコードが正しく指定されているか確認し、適切なコードを使用しているかどうかをチェックしましょう。
エラーが無視されない
AGGREGATE関数を使用しているにもかかわらずエラー値が無視されない場合、オプションコードが正しく設定されていない可能性があります。エラー値を無視するには、オプションコードに「2」または「3」を指定する必要があります。
まとめ
AGGREGATE関数は、通常のSUMやAVERAGE関数に比べ、フィルタ機能やエラー値の扱いが非常に柔軟で、データの集計を効率化するための強力なツールです。特にフィルタ後のデータや、手動で非表示にしたデータ、エラーを含むデータを無視したい場合に非常に役立ちます。
AGGREGATE関数を使いこなして、Excelでの集計作業をさらに効率的に進めましょう！
