Excel AGGREGATE関数 — エラーも無視できるSUBTOTALの上位互換

TL;DR — AGGREGATE は SUBTOTAL に2つの強化を加えたものです。19個の関数（LARGE, SMALL, MEDIAN, PERCENTILE, QUARTILE などを追加）と、エラー値・非表示行・ネストした集計を無視できる options 引数。構文は =AGGREGATE(function_num, options, range, [k])。最も使われていない実力は、#N/A を含む列を**IFERROR で掃除せずに合計・平均すること — =AGGREGATE(9, 6, range)。注意点は、SUBTOTAL が先頭引数1つなのに対して AGGREGATE は2つ**あること、そして 14〜19 の関数（LARGE/SMALL/PERCENTILE/ QUARTILE）には余分な k が必要なこと。Excel 2010以降で動作します。

=AGGREGATE(9, 6, Sales)         ' エラー値を無視してSUM
=AGGREGATE(14, 6, Sales, 1)     ' エラーを無視して最大値（末尾の k=1 がそれ）

SUBTOTAL のガイドを読んだなら、あなたはすでに AGGREGATE の 80%を知っています——同じ、フィルターを認識するアイデアだからです。残りの20%を学ぶ理由は、実データで絶え間なく出てくる1つのシナリオにあります。エラーが入っているのに、それでも合計しなければならない列。 SUBTOTAL は #N/A が1つあるだけでむせ返りますが、AGGREGATE は肩をすくめて計算してのけます。それだけで、道具箱に1席を確保するに値します。

この記事で学べること

メンタルモデル: SUBTOTAL にエラー対応などを足し、options で制御するもの
2引数の構造 — そしてなぜ人は options を忘れるのか
必殺技: #N/A だらけの列を掃除なしで合計する
options グリッド: 0〜7の各数字が何を無視するか
14〜19 の配列関数と、余分な k 引数
判断の分かれ目: AGGREGATE vs SUBTOTAL vs IFERROR ラッパー

メンタルモデル: コントロールダイヤルの付いたSUBTOTAL

SUBTOTAL はフィルターを認識する集計を与えてくれますが、決められるのは1つだけ——どの演算か。 AGGREGATE はそのフィルター認識を保ったまま、2つめのダイヤルを加えます。ほかに何をスキップするかを決める options 引数です。SUBTOTAL(operation, range) の真ん中に新しいつまみが生えたもの、と考えてください。

=SUBTOTAL(9, Sales)          ' SUM。フィルター済み行をスキップ
=AGGREGATE(9, 6, Sales)      ' SUM。フィルター済み行 と エラー値（オプション6）をスキップ

強化のもう半分は、対応範囲の広さです。SUBTOTAL は11の演算を備えますが、AGGREGATE は19——同じ統計セットに加えて MEDIAN, MODE.SNGL, LARGE, SMALL, PERCENTILE.INC/EXC, QUARTILE.INC/EXC が入ります。だからエラーを含むフィルター済み列の5番目に大きい値を、たった1つの関数で求められる——ほかのどの単一の数式もきれいにはやってのけないことです。

人がつまずく構造: 先頭に引数が2つ

AGGREGATE で最もよくあるミスは、SUBTOTAL のように扱って2つめの引数を忘れること。 SUBTOTAL(function_num, range) はコードが1つ、AGGREGATE(function_num, options, range) は2つです。

=AGGREGATE(9, Sales)         ' 間違い — Excelは "Sales" をoptionsと読んでエラーになる
=AGGREGATE(9, 6, Sales)      ' 正しい — 9 = SUM、6 = エラーを無視、そして範囲

順序を焼き付けてください。何を計算するか、何を無視するか、それからデータ。 これが体に染みつけば、あとは2つの小さな表を引くだけです。

必殺技: エラーを含む列を合計する

これが SUBTOTAL より AGGREGATE に手を伸ばす理由であり、ひどく使われていません。現実の列は雑然としています——ヒットしない VLOOKUP は #N/A を返し、比率はゼロで割られる。SUM も SUBTOTAL も、最初のエラーで倒れます。昔の対処は、作業列で各セルを IFERROR で包むか、Ctrl+Shift+Enterの配列数式を書くことでした。AGGREGATE はそれをインラインでやってのけます。

=AGGREGATE(9, 6, Margin)        ' マージンを合計、ただし #DIV/0! の行はスキップ
=AGGREGATE(4, 6, Lookups)       ' #N/A だらけの列のMAX — IFERROR不要
=AGGREGATE(1, 6, Margin)        ' AVERAGE。エラーは合計からも件数からも除外

オプション 6 は「エラー値を無視する」という意味です。掃除用の列も配列入力もなし——エラーは単純に計算から外されます。エラーを必ず含む生データから引くダッシュボードには、これがExcelの持つ最もきれいなツールです。

optionsグリッド

options 引数（0〜7）は3つのスイッチの組み合わせです。ネストしたSUBTOTAL/AGGREGATEを無視するか、非表示行を無視するか、エラーを無視するか。役に立つものは——

' 0 （または省略）: ネストした SUBTOTAL/AGGREGATE のみ無視
' 1 : 非表示行も無視
' 2 : エラー値も無視
' 3 : 非表示行 と エラー を無視
' 4 : 何も無視しない
' 5 : 非表示行を無視
' 6 : エラー値を無視        ← 最もよく使うもの
' 7 : 非表示行 と エラー を無視

実際には、6（エラー）と 3 または 7（非表示行 + エラー）にほぼ毎回手が伸びるでしょう。 SUBTOTAL との違いに注目してください。ここでは非表示行の無視がオプションフラグであって、関数番号に組み込まれてはいません。

14〜19の関数と余分なk

関数のうち6つは単純な集計ではありません。「どれ？」を問うので、末尾に余分な k 引数が必要です。

' 14 = LARGE, 15 = SMALL, 16 = PERCENTILE.INC, 17 = QUARTILE.INC,
' 18 = PERCENTILE.EXC, 19 = QUARTILE.EXC
=AGGREGATE(14, 6, Sales, 1)      ' 1番目に大きい値（最大）、エラーを無視
=AGGREGATE(15, 6, Sales, 2)      ' 2番目に小さい値、エラーを無視
=AGGREGATE(16, 6, Sales, 0.9)    ' 90パーセンタイル、エラーを無視

これらのどれかで k を忘れると #VALUE! になります。ただし見返りは本物です。エラーを無視する堅牢な MINは =AGGREGATE(15, 6, range, 1) ——列に #N/A があると素の MIN にはできないことです。（これらの統計を単独で使うだけなら、専用の LARGE, SMALL, PERCENTILE 関数のほうが簡単です。フィルター認識やエラースキップも合わせて必要なときに AGGREGATE に手を伸ばしましょう。）

判断の分かれ目: AGGREGATE vs SUBTOTAL vs IFERROR

日常的なフィルター済み合計、データがきれい → SUBTOTAL。こちらのほうが短く、テーブルの集計行がそのまま挿入するものです。スキップすべきエラーがないのに、わざわざ余分な options 引数を打つ理由はありません。
データにエラーを含むフィルター済み合計、または SUBTOTAL にない関数が必要（LARGE/SMALL/MEDIAN/PERCENTILE） → AGGREGATE。ここがその真骨頂です。
普通の数式でエラーを無害化したいだけ → IFERROR で単一の結果を包めば十分です。ただしエラーを飛び越えて合計するためだけに、範囲まるごとをCSE配列の中で IFERROR で包みたくなったら、それは遠回り——AGGREGATE(9, 6, range) なら関数1つ、配列入力なしです。

はっきり言いましょう。2026年において、エラーをスキップするためだけに書く Ctrl+Shift+Enter の {=SUM(IF(ISERROR(…)))} はもう過去の遺物です。AGGREGATE が何年も前にそれを置き換えました。きれいなフィルター済みリストには SUBTOTAL を取っておき、エラーやパーセンタイルが範囲に入ってきた瞬間に AGGREGATE へ格上げしましょう。

ExcelMasterの活用法

AGGREGATE の実力は2つの参照表——19個の関数コードと8個のオプション——の向こうに閉じ込められています。これこそ、人々がこの関数を使うのをやめさせる、まさにあの種の摩擦です。ExcelMaster は意図を受け取り ——この列を合計するけどルックアップのエラーはスキップとか、表示行の中で5番目に大きい値、空白とエラーは無視 とか——正しい function_num、正しい options、そして必要な場所には k 引数を付けて AGGREGATE を書きます。あなたはグリッドに触れることすらありません。

よくある質問

エラーが入っている列を合計するには？

=AGGREGATE(9, 6, range) を使います。関数 9 はSUM、オプション 6 は「エラー値を無視する」という意味なので、 #N/A や #DIV/0! の行は単純に外されます——IFERROR の作業列は不要です。

AGGREGATEとSUBTOTALの違いは？

AGGREGATE は上位互換です。SUBTOTALの11個に対して19個の関数を提供し（LARGE, SMALL, MEDIAN, PERCENTILE, QUARTILEを追加）、エラー値やネストした集計を無視できる options 引数を加えます。SUBTOTAL はそもそもエラーをスキップできません。きれいなフィルター済みリストには SUBTOTAL、エラーやそれら追加の関数が絡むときは AGGREGATE を使ってください。

AGGREGATEが#VALUE!を返すのはなぜ？

よくある原因は2つ。options 引数を忘れたか（範囲の前、2番目に来ます）、14〜19 の関数（LARGE/SMALL/PERCENTILE/QUARTILE）を末尾の k 引数なしで使ったか。 =AGGREGATE(14, 6, range, 1) には、その最後の 1 が必要です。

AGGREGATEのoptions番号は何を意味しますか？

何を無視するかを制御します。0 はネストした小計を無視、1/5 は非表示行を無視、2/6 はエラーを無視、 3/7 は両方を無視、4 は何も無視しません。最もよく使うのは 6（エラーを無視）と 7（非表示行とエラーを無視）です。

AGGREGATEはExcel 2016や2019で動きますか？

はい。AGGREGATE はExcel 2010で登場し、2016, 2019, 2021, 365を含むそれ以降のすべてのバージョンで動作します。これがないのはExcel 2007以前だけ——その場合は SUBTOTAL と IFERROR の掃除に戻りましょう。

検証環境

検証環境: Excel 365 (Windows 11) — 最終確認 2026-06-30。

関連ガイド: Excel SUBTOTAL · Excel SUMPRODUCT · Excel SUMIFS · Excel IFERROR · Excel FILTER