ExcelのAGGREGATE関数の使い方｜SUBTOTAL関数との違い・エラー無視・フィルタ後の集計を完全解説
Excelで集計作業をしていると、「フィルタで非表示にした行が合計に含まれてしまう」「#N/Aエラーがあると合計が計算できない」といった問題が起きることがあります。
そんなときに役立つのがAGGREGATE（アグリゲート）関数です。AGGREGATE関数は、エラー値・非表示行・フィルタ後の行を自由に無視して集計できる高機能な集計関数です。
この記事では、AGGREGATE関数の基本から、よく使うSUBTOTAL関数との違い・集計方法コード一覧・実際の使い方まで、初心者にもわかりやすく解説します。
・AGGREGATE関数の構文と引数の意味
・SUBTOTAL関数との違い（何ができてSUBTOTALにできないか）
・集計方法コード（1〜19）とオプションコード（0〜7）の一覧
・エラー値を無視して合計・平均を計算する方法
・フィルタ後のデータだけを集計する方法
・LARGE/SMALL関数の代わりにAGGREGATEを使う応用
AGGREGATE関数とは？SUBTOTAL関数との違い
AGGREGATE関数を理解するには、まず似た関数であるSUBTOTAL関数と比較するとわかりやすいです。
| 機能 | SUBTOTAL関数 | AGGREGATE関数 |
|---|---|---|
| フィルタで非表示の行を無視 | ◎ できる | ◎ できる |
| 手動で非表示にした行を無視 | ◎ できる（コード101〜111） | ◎ できる（オプション1・3など） |
| エラー値（#N/A, #DIV/0!等）を無視 | × できない | ◎ できる（オプション2・3など） |
| 使える集計の種類 | 11種類（合計・平均・最大値など） | 19種類（LARGE・SMALL・MEDIAN等も含む） |
| 利用可能なExcelバージョン | 全バージョン | Excel 2010以降 |
まとめると、SUBTOTAL関数で対応できない「エラー値の無視」や「LARGE/SMALL/MEDIANによる集計」が必要なときにAGGREGATE関数を使う、というのが基本的な使い分けです。
AGGREGATE関数の構文
AGGREGATE関数の書き方は以下のとおりです。
=AGGREGATE(集計方法, オプション, 範囲1, [範囲2, …])
- 集計方法：1〜19の数字で集計の種類を指定（例：9なら合計）
- オプション：0〜7の数字で無視したいデータを指定（例：2ならエラー値を無視）
- 範囲1：集計対象のセル範囲
集計方法コード一覧（1〜19）
集計方法の番号と対応する集計の種類の一覧です。
| コード | 集計方法 | 説明 |
|---|---|---|
| 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 | k番目に大きい値 |
| 15 | SMALL | k番目に小さい値 |
| 16 | PERCENTILE.INC | 百分位数（境界含む） |
| 17 | QUARTILE.INC | 四分位数（境界含む） |
| 18 | PERCENTILE.EXC | 百分位数（境界除く） |
| 19 | QUARTILE.EXC | 四分位数（境界除く） |
コード12〜19は通常のSUBTOTAL関数では使えないため、AGGREGATE関数ならではの機能です。特に14（LARGE）・15（SMALL）・12（MEDIAN）は実務でよく使います。
オプションコード一覧（0〜7）
| コード | 無視する対象 |
|---|---|
| 0 | 何も無視しない（通常の集計と同じ） |
| 1 | 手動で非表示にした行を無視 |
| 2 | エラー値を無視 |
| 3 | 手動で非表示にした行とエラー値を無視 |
| 4 | フィルタで非表示の行を無視 |
| 5 | フィルタで非表示の行とエラー値を無視 |
| 6 | 手動で非表示にした行・フィルタで非表示の行を無視（エラーは含む） |
| 7 | 手動で非表示の行・フィルタで非表示の行・エラー値を無視（最も厳格） |
よく使うパターン①：エラー値を無視して合計する
データにエラー（#N/A・#DIV/0!・#VALUE!など）が混在していると、SUM関数もSUBTOTAL関数もエラーを返してしまいます。AGGREGATE関数ならオプション「2」を指定するだけでエラーを無視して集計できます。
例：B2:B6の範囲にエラーが含まれていても合計したい
=AGGREGATE(9, 2, B2:B6)
- 9 = SUM（合計）
- 2 = エラー値を無視
エラーセルをスキップして、数値のセルだけを合計します。
平均を出したい場合：
=AGGREGATE(1, 2, B2:B6)
エラーを無視した残りのデータの平均が計算されます。
よく使うパターン②：フィルタ後のデータだけを集計する
オートフィルタで特定の行を絞り込んだ後、表示されているデータだけを集計したい場合はオプション「4」を使います。
例：フィルタ後に表示されているB列の合計を出す
=AGGREGATE(9, 4, B2:B100)
フィルタで非表示になっている行は無視され、見えている行だけが合計されます。
=AGGREGATE(9, 5, B2:B100)（フィルタで非表示の行とエラー値を両方無視）
よく使うパターン③：LARGE/SMALLをエラー無視で使う
LARGE関数は「k番目に大きい値」を返す関数ですが、エラー値があるとエラーになってしまいます。AGGREGATE関数の集計方法「14」（LARGE相当）を使えば、エラーを無視してk番目の値を取得できます。
例：B2:B10のうちエラーを除いて2番目に大きい値を取得
=AGGREGATE(14, 2, B2:B10, 2)
- 14 = LARGE（k番目に大きい値）
- 2 = エラー値を無視
- 最後の「2」 = 2番目（k の値）
同様にSMALL（k番目に小さい値）の場合：
=AGGREGATE(15, 2, B2:B10, 1)
エラーを無視して最小値（1番目に小さい値）を取得します。MIN関数の代わりに使えます。
よくあるエラーと対処法
#VALUE! エラーが出る場合
集計方法コードまたはオプションコードが正しくない場合に発生します。コードが1〜19（集計方法）・0〜7（オプション）の範囲内に収まっているか確認してください。
エラーが無視されない場合
オプションコードが「0」（何も無視しない）のままになっている可能性があります。エラーを無視したい場合はオプションを「2」（エラーのみ無視）または「3」（非表示+エラー無視）に変更してください。
フィルタしても結果が変わらない場合
オプションコードが「4」以上になっているか確認してください。「0」〜「3」はフィルタ後の非表示行を無視しません。フィルタ後の集計にはオプション「4」〜「7」が必要です。
LARGE/SMALLでエラーになる場合
集計方法「14」（LARGE）または「15」（SMALL）を使う場合、第4引数（k）を忘れると#VALUE!エラーになります。必ず第4引数にk（何番目か）を指定してください。
まとめ：AGGREGATE関数の使い分け
| やりたいこと | 集計方法 | オプション |
|---|---|---|
| エラーを無視して合計 | 9（SUM） | 2 |
| エラーを無視して平均 | 1（AVERAGE） | 2 |
| フィルタ後のデータだけ合計 | 9（SUM） | 4 |
| フィルタ後＋エラー無視で合計 | 9（SUM） | 5 |
| エラーを無視してk番目に大きい値 | 14（LARGE） | 2 |
| エラーを無視してk番目に小さい値 | 15（SMALL） | 2 |
| フィルタ後の中央値 | 12（MEDIAN） | 4 |
AGGREGATE関数は引数の組み合わせが多く最初は難しく感じるかもしれませんが、「集計方法（何を計算するか）」と「オプション（何を無視するか）」の2つを決めるだけです。特にエラーが含まれるデータの集計・フィルタ後の合計という場面では、SUBTOTAL関数よりも確実に対処できます。ぜひ実務で活用してみてください。
