AGGREGATE関数でエラー・非表示行を除外した精密集計を実現する｜19種の集計番号と6オプション早見表・実務シナリオとMOS試験対策

「エラー値が混じったデータで平均を出したい」「フィルターで絞り込んだ可視行だけをRANK順位付けしたい」「非表示行は集計から除きつつ、さらにエラーも無視したい」――こうした複合的な条件の集計を1つの関数で実現できるのがAGGREGATE（アグリゲート）関数です。

AGGREGATEは、SUM・AVERAGE・COUNT・LARGE・PERCENTILEなど19種類の集計方法と、「エラーを無視する」「非表示行を無視する」「ネスト関数を無視する」といった6種類の除外オプションを組み合わせて使う、Excelの高機能集計関数です。Excel 2010以降・Excel 365で利用できます。

本記事では、AGGREGATEの基本構文・引数の意味・19種の集計番号と6つのオプションの全組み合わせ・実務シナリオ別の使い方・よく起こるエラーと対処法・MOS試験での出題ポイントを体系的に解説します。

AGGREGATE関数の基本構文

AGGREGATEには、参照形式と配列形式の2つの書き方があります。

参照形式（最もよく使う）

=AGGREGATE(集計方法, オプション, 参照1, [参照2], ...)

配列形式（k番目の値を求めるとき）

=AGGREGATE(集計方法, オプション, 配列, [k])

引数	説明	省略
集計方法	1～19の整数。どの関数で集計するかを指定する	必須
オプション	0～7の整数。何を除外するかを指定する	必須
参照1	集計対象のセル範囲（複数指定可）	必須
[参照2]以降	追加の集計対象範囲（最大253個まで指定可）	省略可
[k]	LARGE・SMALL・PERCENTILE・QUARTILEなど順位が必要な集計方法で使用	集計方法によって必要

第1引数：集計方法の番号一覧（19種類）

番号1～11はSUBTOTAL関数と対応しており、12番以降がAGGREGATE独自の集計関数です。LARGE・SMALL・PERCENTILEなどが使える点が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	k番目に大きい値（第4引数kが必要）
15	SMALL	k番目に小さい値（第4引数kが必要）
16	PERCENTILE.INC	百分位数・含む（第4引数kが必要）
17	QUARTILE.INC	四分位数・含む（第4引数kが必要）
18	PERCENTILE.EXC	百分位数・除く（第4引数kが必要）
19	QUARTILE.EXC	四分位数・除く（第4引数kが必要）

番号12以降（MEDIAN・MODE・LARGE・SMALL・PERCENTILE・QUARTILE）はSUBTOTAL関数では使えない、AGGREGATE独自の機能です。

第2引数：オプション番号の一覧（6種類）

オプションは「何を除外するか」を指定します。0～7の数値で指定しますが、実際に使われるのは主に0～6の組み合わせです。

オプション番号	ネスト関数を無視	非表示行を無視	エラー値を無視	主な用途
0	✓	—	—	標準（SUBTOTAL相当）。ネスト関数のみ除外
1	✓	✓	—	非表示行・ネスト関数を除外
2	✓	—	✓	エラー値・ネスト関数を除外
3	✓	✓	✓	非表示行・エラー・ネスト関数をすべて除外（最強）
4	—	—	—	何も除外しない（通常の集計と同じ）
5	—	✓	—	非表示行のみ除外
6	—	—	✓	エラー値のみ除外
7	—	✓	✓	非表示行・エラーを除外（ネスト関数は除外しない）

最もよく使うのはオプション3（非表示行・エラー・ネスト関数をすべて除外）と5（非表示行のみ除外）です。フィルター使用時に非表示行を除外するにはオプション5または1を使います。

実務シナリオ1：エラー値が混在するリストで平均・合計を求める

売上数量や単価のリストに#DIV/0!や#N/Aが含まれている場合、通常のAVERAGE・SUM関数ではエラーが返ります。AGGREGATEのオプション6（エラー値を無視）を使うと、エラーを除いた正常値だけで集計できます。

A2:A20に売上数量（一部に#DIV/0!あり）が並んでいる場合の例です。

' エラーを無視して平均を求める（集計方法=1:AVERAGE、オプション=6:エラー無視）
=AGGREGATE(1, 6, A2:A20)

' エラーを無視して合計を求める（集計方法=9:SUM、オプション=6:エラー無視）
=AGGREGATE(9, 6, A2:A20)

' エラーを無視して件数を求める（集計方法=2:COUNT、オプション=6:エラー無視）
=AGGREGATE(2, 6, A2:A20)

IFERROR関数で1つ1つのセルをラップする方法と比べて、AGGREGATEは元の数式を変更せずにエラーを除外できるため、他の担当者が作ったシートの集計にも使いやすいです。

実務シナリオ2：フィルター適用時に可視行だけを集計する

オートフィルターで特定条件の行だけを表示している際に、可視行だけを集計したい場面はよくあります。AGGREGATEのオプション5（非表示行を無視）を使います。

B列（B2:B500）に売上金額が入っており、オートフィルターで「東京支店」だけを表示している状況での集計例です。

' フィルター後の可視行だけ合計（集計方法=9:SUM、オプション=5:非表示行を無視）
=AGGREGATE(9, 5, B2:B500)

' フィルター後の可視行だけ件数（集計方法=2:COUNT、オプション=5）
=AGGREGATE(2, 5, B2:B500)

' フィルター後の可視行だけ最大値（集計方法=4:MAX、オプション=5）
=AGGREGATE(4, 5, B2:B500)

オプション5を使った場合、フィルターで非表示になった行は集計から除外されます。フィルターを解除すると全行が再び集計対象になるため、フィルター状態に連動した動的な集計行を作るのに便利です。

実務シナリオ3：非表示行・エラー値の両方を除外して最大値・最小値を求める

品質データや売上データには「測定エラー（#VALUE!）」と「非表示行（フィルター除外）」が同時に発生することがあります。オプション3（すべて除外）が最も安全です。

' 非表示行・エラー・ネスト関数を除外して最大値（集計方法=4:MAX、オプション=3）
=AGGREGATE(4, 3, C2:C300)

' 非表示行・エラー・ネスト関数を除外して最小値（集計方法=5:MIN、オプション=3）
=AGGREGATE(5, 3, C2:C300)

' 非表示行・エラー・ネスト関数を除外して中央値（集計方法=12:MEDIAN、オプション=3）
=AGGREGATE(12, 3, C2:C300)

MEDIANは中央値で、外れ値の影響を受けにくい集計指標です。品質管理・給与分析・配送時間分析など「外れ値を除いた代表値を見たい」場面でオプション3と組み合わせると非常に役立ちます。

実務シナリオ4：LARGE・SMALLでフィルター後のトップN位を抽出する

AGGREGATE最大の強みの1つが、LARGE・SMALL・PERCENTILEなどSUBTOTAL関数では使えない統計関数に対応している点です。フィルター後の可視行から「上位3位の売上額」を取り出す例を示します。

' フィルター後の可視行から1位（最大値）の売上額（集計方法=14:LARGE、オプション=5、k=1）
=AGGREGATE(14, 5, D2:D200, 1)

' フィルター後の可視行から2位の売上額（k=2）
=AGGREGATE(14, 5, D2:D200, 2)

' フィルター後の可視行から3位の売上額（k=3）
=AGGREGATE(14, 5, D2:D200, 3)

' フィルター後の可視行から下位1位（最小値近似、集計方法=15:SMALL）
=AGGREGATE(15, 5, D2:D200, 1)

第4引数のkは「何番目の大きさ/小ささ」を指定します。k=1が最大値（または最小値）に相当します。動的なランキング表を作る際、フィルター操作と連動して自動更新されるため、月次レポートや担当者別ランキングに活用できます。

実務シナリオ5：PERCENTILE・QUARTILEで分布分析を行う

テスト点数や給与データの分布を把握したい場合、PERCENTILE（百分位数）やQUARTILE（四分位数）が役立ちます。AGGREGATEを使うと、エラーや非表示行を除外しながらこれらの統計量を求めることができます。

' 可視行・エラー除外でスコアの中央値（50パーセンタイル）
=AGGREGATE(16, 5, E2:E100, 0.5)

' 可視行・エラー除外でスコアの第1四分位数（25パーセンタイル）
=AGGREGATE(16, 5, E2:E100, 0.25)

' 可視行・エラー除外でスコアの第3四分位数（75パーセンタイル）
=AGGREGATE(16, 5, E2:E100, 0.75)

' 四分位数を直接取得（集計方法=17:QUARTILE.INC、k=1が第1四分位数）
=AGGREGATE(17, 5, E2:E100, 1)

PERCENTILE・QUARTILEはSUBTOTAL関数では対応していない統計関数のため、フィルター後のデータで分布分析をしたい場面では必然的にAGGREGATEの出番になります。

SUBTOTAL関数との違い

AGGREGATEとSUBTOTALはどちらも「非表示行を無視できる」集計関数ですが、機能に大きな差があります。

比較項目	SUBTOTAL関数	AGGREGATE関数
使用可能バージョン	Excel 2003以降（全バージョン）	Excel 2010以降・Excel 365
集計関数の種類	11種類（SUM・AVERAGEなど基本関数）	19種類（MEDIAN・LARGE・SMALLなど追加）
非表示行を無視	◎ 対応（関数番号で切り替え）	◎ 対応（オプション5で指定）
エラー値を無視	✗ 非対応	◎ 対応（オプション6で指定）
ネスト関数の除外	◎ 自動的に除外	◎ オプション0/1/2/3で制御可能
手動非表示行の扱い	関数番号1～11は含む、101～111は除外	オプション5以上で除外（一元管理）
LARGE・SMALL対応	✗ 非対応	◎ 対応（番号14・15）
MEDIAN・MODE対応	✗ 非対応	◎ 対応（番号12・13）

基本的な合計・平均・件数の集計であればSUBTOTAL関数で十分です。「エラーを無視したい」「LARGE・SMALL・MEDIANを使いたい」「フィルター後のデータで統計分析をしたい」場合にAGGREGATEを選んでください。

エラーの原因と対処法

エラー	主な原因	対処法
#VALUE!	集計方法番号が1～19の範囲外、またはオプション番号が0～7の範囲外	第1・第2引数の番号を正しい範囲で指定し直す
#NUM!	LARGE・SMALLでkが範囲外（kが0以下、またはデータ件数を超えている）	kがデータの件数以内であることを確認する。AGGREGATE(2,5,範囲)で可視行件数を事前確認するとよい
#NAME?	Excel 2007以前で使用している	AGGREGATE関数はExcel 2010以降専用。旧バージョンではSUBTOTAL関数を使う
期待値と異なる結果	オプション番号を間違えている（非表示行を除外したつもりがエラー除外になっているなど）	オプションの表を確認して正しい番号を指定する。5=非表示行除外、6=エラー除外、3=両方除外
フィルター後も全行が集計される	オプションに4（何も除外しない）を指定している	フィルター後の可視行だけ集計したい場合はオプション5・1・3・7のいずれかを使う

オプション番号の選び方：早見フロー

どのオプション番号を使えばよいか迷ったときは、以下のフローで判断してください。

• フィルター非表示行を除外したい → Yes: オプション1・3・5・7のいずれか / No: オプション0・2・4・6のいずれか
• エラー値を除外したい → Yes: 上記でYesなら3または7、Noなら6
• ネスト集計関数（別のAGGREGATEなど）を除外したい → Yes: オプション0～3のいずれか（ネスト除外ON側）
• すべてを除外したい（最も安全） → オプション3

実務では「オプション5（非表示行除外）」または「オプション3（全部除外）」を使えば大半のケースに対応できます。

MOS試験でのAGGREGATEの出題ポイント

MOS Excel 365試験の「数式と関数」「データ管理」スキル領域では、集計関数の使い分けが問われます。AGGREGATEは比較的新しく試験での比重はまだ大きくありませんが、SUBTOTAL関数との関係を整理した上で、エラー処理・非表示行対応の文脈で理解しておくと確実です。

• SUBTOTAL関数：フィルター後の可視行集計の代名詞。関数番号1～11と101～111の違いを押さえる
• AGGREGATE関数：SUBTOTALの上位互換。エラー除外・LARGE・SMALLなど統計関数対応が差別点
• IFERROR関数：エラー処理の定番。AGGREGATE（オプション6）との使い分けを理解する
• LARGE・SMALL関数：AGGREGATEなしで使う場合はエラーや非表示行の影響を受けることを理解する

MOS試験 AGGREGATE関数チェックリスト

確認ポイント	操作内容	難易度
基本構文の入力	=AGGREGATE(集計方法, オプション, 参照)を正しく入力できる	★☆☆
集計方法番号の選択	SUM=9、AVERAGE=1、COUNT=2、MAX=4、MIN=5の番号を即答できる	★☆☆
オプション番号の選択	非表示行除外=5、エラー除外=6、両方除外=3の番号を即答できる	★★☆
LARGE・SMALLの活用	=AGGREGATE(14, 5, 範囲, 1)でフィルター後の最大値を求める式を書ける	★★☆
MEDIAN・MODEの活用	=AGGREGATE(12, 3, 範囲)で中央値を求める式を書ける	★★☆
SUBTOTALとの違い	AGGREGATE独自の機能（エラー除外、LARGE/SMALL/MEDIAN対応）を説明できる	★★☆
エラー#NUMの対処	LARGEのkがデータ件数を超えた場合の原因と解決方法を説明できる	★★★

まとめ：AGGREGATEはエラーと非表示行の「除外の複合指定」が強み

本記事のポイントをまとめます。

• AGGREGATEの役割：集計方法（19種類）×除外オプション（8種類）の組み合わせで、エラー・非表示行・ネスト関数を自在に除外しながら集計できるExcel 2010以降の高機能集計関数
• 集計方法番号：1=AVERAGE、9=SUM、2=COUNT、4=MAX、5=MIN、12=MEDIAN、14=LARGE、15=SMALL。SUBTOTAL関数にはないMEDIAN・LARGE・SMALLが使える点がAGGREGATEの最大の強み
• オプション番号：5=非表示行除外、6=エラー除外、3=全部除外（最安全）が実務でよく使われる。迷ったらオプション3を選ぶ
• フィルター対応集計：オプション5以上でフィルター後の可視行だけを集計できる。LARGE・SMALLと組み合わせて動的ランキング表も作れる
• 統計分析への活用：PERCENTILE・QUARTILEでエラーや非表示行を除外しながら分布分析が可能。品質管理・給与分析・成績分析に役立つ
• SUBTOTALとの使い分け：基本集計（合計・件数・平均）でエラーが出ないならSUBTOTAL、エラー除外やLARGE・SMALL・MEDIANが必要なときはAGGREGATEを選ぶ
• MOS試験対策：集計方法番号とオプション番号の早見表を暗記し、LARGE・SMALLでの第4引数kの使い方・SUBTOTALとの比較を重点的に理解する

エラーが混在するデータを扱う場面、フィルターを多用した業務シート、中央値・ランキングを動的に計算したい場面で、AGGREGATEは即戦力になります。オプション番号の組み合わせを一度覚えてしまえば、エラー処理をIFERRORで一つひとつ書く手間が大幅に削減できます。Excel集計スキルのワンランク上を目指す方は、ぜひAGGREGATEを実務に組み込んでみてください。