AGGREGATE関数の使い方｜エラーを無視した集計
AGGREGATE関数は、エラー値を無視した集計や非表示行の除外など、柔軟な条件で集計を行える高機能な関数です。SUBTOTAL関数の上位互換として、19種類の集計方法と7つのオプションを組み合わせて使用します。Excel 2010以降で利用できます。
AGGREGATE関数の基本構文
AGGREGATE関数には2つの構文形式があります。使用する集計方法によって形式が異なります。
形式1：参照形式（集計方法1〜13）
=AGGREGATE(集計方法, オプション, 参照1, [参照2], ...)形式2：配列形式（集計方法14〜19）
=AGGREGATE(集計方法, オプション, 配列, [順位])引数の説明
| 引数 | 説明 | 必須/任意 |
|---|---|---|
| 集計方法 | 使用する集計関数を指定する番号（1〜19） | 必須 |
| オプション | 除外する値の種類を指定する番号（0〜7） | 必須 |
| 参照/配列 | 集計対象のセル範囲 | 必須 |
| 順位 | LARGE・SMALL等で使用する順位番号 | 集計方法14〜19で必須 |
19種類の集計方法
AGGREGATE関数で指定できる集計方法の一覧です。
基本集計（1〜13）
| 番号 | 集計関数 | 説明 |
|---|---|---|
| 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〜19）
| 番号 | 集計関数 | 説明 |
|---|---|---|
| 14 | LARGE | n番目に大きい値 |
| 15 | SMALL | n番目に小さい値 |
| 16 | PERCENTILE.INC | 百分位数（境界値を含む） |
| 17 | QUARTILE.INC | 四分位数（境界値を含む） |
| 18 | PERCENTILE.EXC | 百分位数（境界値を含まない） |
| 19 | QUARTILE.EXC | 四分位数（境界値を含まない） |
SUBTOTAL関数にはない12〜19の集計方法が、AGGREGATE関数の大きな特徴です。
7つのオプション
オプション番号で、除外する値の種類を指定します。
| オプション | 除外する内容 |
|---|---|
| 0 | ネストされたSUBTOTALとAGGREGATEの値を除外 |
| 1 | 非表示行、ネストされたSUBTOTALとAGGREGATEの値を除外 |
| 2 | エラー値、ネストされたSUBTOTALとAGGREGATEの値を除外 |
| 3 | 非表示行、エラー値、ネストされたSUBTOTALとAGGREGATEの値を除外 |
| 4 | 何も除外しない |
| 5 | 非表示行を除外 |
| 6 | エラー値を除外 |
| 7 | 非表示行とエラー値を除外 |
よく使うオプションの組み合わせ
| 目的 | 推奨オプション |
|---|---|
| エラーを無視して集計 | 6 |
| フィルター非表示行を除外して集計 | 5 |
| エラーと非表示行の両方を除外 | 7 |
| SUBTOTAL関数と同様の動作 | 0 |
エラーを無視した合計
AGGREGATE関数の最も実用的な機能は、エラー値を無視して集計できることです。
SUM関数との比較
以下のデータにエラーが含まれている場合を想定します。
| A列：商品名 | B列：売上金額 |
|---|---|
| 商品A | 150000 |
| 商品B | #N/A |
| 商品C | 280000 |
| 商品D | 320000 |
| 商品E | #VALUE! |
# SUM関数（エラーセルがあるとエラーになる）
=SUM(B2:B6) → #N/A
# AGGREGATE関数（エラーを無視して合計）
=AGGREGATE(9, 6, B2:B6) → 750000AGGREGATE関数はエラーセルを除外し、150000 + 280000 + 320000 = 750000を返します。
IFERROR関数との比較
従来はSUMIFや配列数式でエラーを回避していましたが、AGGREGATE関数ならシンプルに記述できます。
# 従来の方法（配列数式）
=SUM(IFERROR(B2:B6, 0))
# AGGREGATE関数
=AGGREGATE(9, 6, B2:B6)AGGREGATE関数の方が数式が短く、処理速度も速い傾向があります。
さまざまな集計でエラーを無視する
合計以外の集計でも同様にエラーを無視できます。平均はAGGREGATE(1, 6, 範囲)、最大値はAGGREGATE(4, 6, 範囲)、最小値はAGGREGATE(5, 6, 範囲)、数値セルの件数はAGGREGATE(2, 6, 範囲)で求めます。オプション5を指定すれば非表示行を除外でき、オプション7ならエラーと非表示行の両方を除外できます。
集計方法14と15を使えば、エラーを無視してn番目の値を取得することも可能です。
エラーを無視して上位の値を取得
以下のデータから、エラーを除外した上で上位3位の売上を取得します。
| A列：担当者 | B列：売上 |
|---|---|
| 田中 | 5200000 |
| 佐藤 | #N/A |
| 山田 | 3800000 |
| 鈴木 | 4100000 |
| 高橋 | #VALUE! |
| 伊藤 | 6300000 |
=AGGREGATE(14, 6, B2:B7, 1) → 6300000（1位）
=AGGREGATE(14, 6, B2:B7, 2) → 5200000（2位）
=AGGREGATE(14, 6, B2:B7, 3) → 4100000（3位）通常のLARGE関数ではエラーセルがあるとエラーになりますが、AGGREGATE関数ならエラーを無視して順位を取得できます。
エラーを無視した最小値の取得
=AGGREGATE(15, 6, B2:B7, 1) → 3800000（最小値）エラーを無視した中央値・最頻値
SUBTOTAL関数にはない中央値と最頻値の集計も可能です。中央値はAGGREGATE(12, 6, 範囲)、最頻値はAGGREGATE(13, 6, 範囲)で求めます。
実務での活用例
VLOOKUP結果の集計
VLOOKUP関数の結果にはしばしば#N/Aエラーが含まれます。AGGREGATE関数を使えば、エラーを気にせず集計できます。
# B列にVLOOKUPの結果が入っている場合
=AGGREGATE(9, 6, B2:B500)エラーセルを除外した合計が返されます。IFERROR関数で個別にエラー処理する必要がありません。
データ品質が不安定なシートの集計
外部システムから取り込んだデータは、エラーや欠損値を含むことがあります。AGGREGATE関数を使うと、データのクリーニングなしに集計を実行できます。
# エラーと非表示行を除外した集計ダッシュボード
# 合計
=AGGREGATE(9, 7, C2:C1000)
# 件数
=AGGREGATE(2, 7, C2:C1000)
# 平均
=AGGREGATE(1, 7, C2:C1000)
# 最大値
=AGGREGATE(4, 7, C2:C1000)
# 中央値
=AGGREGATE(12, 7, C2:C1000)在庫管理での異常値除外
在庫データから、エラーセルを除外した在庫数量の合計や平均を求めます。
# 在庫数量の合計（エラー除外）
=AGGREGATE(9, 6, D2:D300)
# 在庫金額の合計（エラー除外）
=AGGREGATE(9, 6, E2:E300)よくあるエラーと対処法
#VALUE!エラー
集計方法やオプションの番号が範囲外の場合に発生します。集計方法は1〜19、オプションは0〜7の範囲で指定してください。
配列形式での順位引数の忘れ
集計方法14〜19では第4引数（順位やパーセンタイル値）が必須です。省略するとエラーになります。
# エラー（順位が未指定）
=AGGREGATE(14, 6, B2:B100)
# 正しい
=AGGREGATE(14, 6, B2:B100, 1)3D参照は使用できない
AGGREGATE関数は、複数シートにまたがる3D参照（Sheet1:Sheet3!A1:A10のような参照）には対応していません。
まとめ
AGGREGATE関数は、エラー値や非表示行を柔軟に制御して集計を行える高機能な関数です。
- 19種類の集計方法と7つのオプションを組み合わせて使用する
- エラー値を無視した集計が最大の特徴（オプション6または7）
- LARGE・SMALLに相当するn番目の値の取得もエラーを無視して実行できる
- 中央値や最頻値の集計はAGGREGATE関数でのみ対応
- SUBTOTAL関数の上位互換として、より複雑な集計に対応する
- Google スプレッドシートでは利用できない（IFERROR+SUM等で代替が必要）
エラーを含むデータの集計が必要な場面で威力を発揮する関数です。シンプルなフィルター連動集計であればSUBTOTAL関数でも対応できますが、エラー処理が必要な場合はAGGREGATE関数を選びましょう。