SUBTOTAL と AGGREGATE の違い — 「見えている行だけ」を集計する

一覧にフィルターを掛けて商品を 1 つだけに絞り、合計を見る。数字が変わっていない。 SUM はフィルターの存在を知らないので、範囲の中の行を、見えていようがいまいが全部足します。画面に出ている行と合計が食い違ったまま、その画面はスクリーンショットで報告書に貼られます。

Excel の答えが SUBTOTAL、その後から出てきた上位版が AGGREGATE です。この 2 つだけが「そのセルがなぜそこに無いのか」を見ています。フィルターで消えたのか、手で隠したのか、エラー値なのか、それとも既に数えた小計なのか。

ReoGrid V5 はこの 2 つに対応しました。この記事では両者の違いを整理し、C# アプリの中で動かすところまで——フィルター、アウトライン、そして引っかかりやすいヘッドレスの場合まで見ていきます。

この記事は表計算の関数シリーズの一部です。「値を引く」 VLOOKUP / HLOOKUP / XLOOKUP、「位置を返す」 MATCH / XMATCH、「条件で絞って集計する」 SUMIF / COUNTIF と合わせてどうぞ。

まず結論

要点は 2 つです。

1. SUBTOTAL は「見えているものの合計」。 フィルターで消えた行は数えず、番号に 100 を足すと手で隠した行も数えなくなる
2. AGGREGATE は SUBTOTAL ＋「エラーを飛ばす」＋ 8 種類の関数追加。 第 2 引数のために存在するようなものです

SUBTOTAL — 見えている行の集計

SUBTOTAL(集計方法, 範囲1, [範囲2], …)。第 1 引数でどの集計を行うかを選びます。

実務で書くのはほぼ SUBTOTAL(9, …)、つまり合計です。

1〜11 と 101〜111 — たいてい逆に覚えられている

同じ 11 個の関数が 1〜11 と 101〜111 の 2 組あります。「100 番台は非表示行を無視する」と説明されることが多く、そこから「SUBTOTAL(9, …) はフィルターで消えた行も数える」と思われがちですが、そうではありません。

フィルターで消えた行はどちらの番号でも除外されます。 100 番台が追加でやることは 1 つだけ——ユーザーが手で隠した行も除外する、です。

つまり選択の幅は狭く、判断基準は意図です。9 は「今のフィルター条件での合計」——行を手で隠すのは表示上の都合であって、数字を動かすべきではない、という立場。109 は「経緯はどうあれ画面に出ている行の合計」で、アウトラインを畳んで使うときに欲しいのはこちらです。

列は最初から関係ありません。列を隠しても何も除外されません。ReoGrid でも Excel でも、対象は行だけです。

総計が小計を二重に数えない理由

グループごとに小計を並べ、いちばん下に総計を置く。素直に SUM で書くと、値は「自分自身」と「所属グループの小計」の 2 回数えられて倍になります。

SUBTOTAL はこれを自分で処理します。範囲の中にある SUBTOTAL / AGGREGATE のセルは無視されるからです。

A1  1
A2  2
A3  =SUBTOTAL(9,A1:A2)     → 3
A4  4
A5  5
A6  =SUBTOTAL(9,A4:A5)     → 9
A7  =SUBTOTAL(9,A1:A6)     → 12   ← 24 にはならない
A8  =SUM(A1:A6)            → 24   ← 素の SUM は全部見る

小計が式の一部に埋まっていても同じです。=SUBTOTAL(9,A1:A2)*2 のセルも小計セルとして扱われ、やはり無視されます。判定されるのはセルであって、式の形ではありません。

AGGREGATE — 同じ仕事＋エラーの扱い

AGGREGATE(集計方法, オプション, 範囲1, [範囲2], …)。順位系の関数では AGGREGATE(集計方法, オプション, 配列, k) の形を取ります。

使える関数は 11 種ではなく 19 種です。

14〜19 は末尾に k が要ります。AGGREGATE(14, 6, A1:A100, 2) で「エラーを無視して 2 番目に大きい値」です。

オプションは真理値表

第 2 引数はモード選択ではなく、独立した 3 つのスイッチを 1 つの数字に詰めたものです。

ビットとして読めば暗記は要りません。0〜3 は入れ子の小計を無視、奇数は手で隠した行を無視、2/3/6/7 はエラーを無視。フィルターで消えた行はこの表の外です——どのオプションでも除外されます。SUBTOTAL と同じです。

使いたくなる理由

列のどこか 1 セルに #DIV/0! があると、その列の SUM は #DIV/0! になります。正しい挙動ですが、取り込んだ 1000 行のうち 3 行が数量ゼロで割っていただけ、という場面では役に立ちません。

=SUM(D2:D1000)              → #DIV/0!
=SUBTOTAL(9,D2:D1000)       → #DIV/0!   （こちらもエラーを伝播する）
=AGGREGATE(9,6,D2:D1000)    → 計算できた行の合計

代替案は全セルを IFERROR で包むことですが、3 行のために 1000 個の数式を書き換えることになります。AGGREGATE は、その判断を本当に必要としている 1 つのセルに寄せられます。

使い分け

• フィルター下の合計 → SUBTOTAL(9, …)。短く、表計算に慣れた人がそこにあると期待する式でもあります
• アウトラインを畳んだ状態の合計 → SUBTOTAL(109, …)
• 範囲にエラーが混ざっていて飛ばしたい → AGGREGATE(9, 6, …)
• 絞り込んだデータの中央値・3 番目に大きい値・四分位数 → AGGREGATE。SUBTOTAL は 11 種で止まります
• 常に全部数えたい → 素の SUM。AGGREGATE(9, 4, …) でも同じ結果になりますが、次に読む人には意図が逆に伝わります

C# アプリの中で動かす — ReoGrid V5

どちらもコア側の実装なので、以下のコードは WinForms / WPF / Avalonia で同一、UI を持たないヘッドレスでもそのまま動きます。

using unvell.ReoGrid.Core;
using unvell.ReoGrid.Core.Filtering;

var workbook = new Workbook();
var sheet = workbook.AddWorksheet("Orders");

sheet.SetText(0, 0, "商品");    sheet.SetText(0, 1, "数量");
sheet.SetText(1, 0, "りんご");  sheet.SetNumber(1, 1, 1);
sheet.SetText(2, 0, "バナナ");  sheet.SetNumber(2, 1, 2);
sheet.SetText(3, 0, "りんご");  sheet.SetNumber(3, 1, 3);
sheet.SetText(4, 0, "さくらんぼ"); sheet.SetNumber(4, 1, 4);

sheet.SetFormula(5, 1, "SUBTOTAL(9,B2:B5)");   // 見えている行の合計
sheet.SetFormula(6, 1, "SUM(B2:B5)");          // 比較用

// 「りんご」で絞り込む
var filter = sheet.CreateAutoFilter(RangePosition.Parse("A1:B5"));
filter.SetColumnFilter(0, ["りんご"]);
filter.Apply();

double visible = sheet.GetValue(5, 1).AsNumber;   // 4  (1 + 3)
double all     = sheet.GetValue(6, 1).AsNumber;   // 10

filter.ClearAll();                                // B6 は 10 に戻る

SetFormula に渡す式に先頭の = は付けません。"SUBTOTAL(9,B2:B5)" であって "=SUBTOTAL(...)" ではない、ということです。= は数式バーの入力記号であって式の一部ではありません。

Apply() は集計式の再計算まで面倒を見ます。ClearAll()、アウトラインの開閉、Undo も同様です。

覚えておく 1 つの呼び出し — SyncAggregateFormulas()

行の表示状態はセルの編集ではないので、依存グラフには乗りません。行を隠す操作は、どこから見ても「値が変わった」ようには見えないのです。画面のあるアプリではこれは見えません——手で隠した行は次の再描画で反映されます。ヘッドレスには再描画が無いので、自分で伝えます。

sheet.SetFormula(6, 1, "SUBTOTAL(109,B2:B5)");   // 100 番台

sheet.Rows.SetHidden(2, true);      // 「バナナ」を手で隠す
sheet.SyncAggregateFormulas();      // 再描画は来ない — 明示的に更新する

double onScreen = sheet.GetValue(6, 1).AsNumber;   // 8  (10 − 2)

表示状態が実際に動いていなければ何もしないので、まとめて隠したあとに 1 回呼ぶ、で十分です。

アウトラインと組み合わせる

var group = sheet.GroupRows(1, 4);       // データ行 4 行をグループ化
sheet.RowOutlines.Collapse(group);

sheet.GetValue(5, 1).AsNumber;           // 10 — SUBTOTAL(9) は隠れた行も数える
sheet.GetValue(6, 1).AsNumber;           //  4 — SUBTOTAL(109) は残っている行だけ

109 が存在する理由がこれで、2 つの番号帯が交換可能ではないことがいちばん分かりやすく出る場面でもあります。

シートをまたぐ

SUBTOTAL は別シートを参照でき、そのとき見るのは参照先シートの非表示行です。

var summary = workbook.AddWorksheet("Summary");
summary.SetFormula(0, 0, "SUBTOTAL(109,Orders!B2:B5)");

sheet.Rows.SetHidden(2, true);
summary.SyncAggregateFormulas();         // 更新するのは「読む側」のシート

呼び出す相手に注意してください。式があるのは Summary なので、更新が要るのも Summary です。

Excel 互換ゆえの細かい挙動

• MAX / MIN は対象が無いと 0 を返します（エラーではありません）。Excel と同じです。AVERAGE は #DIV/0!
• MODE.SNGL は重複が 1 つも無いと #N/A。 同数のときは先に現れた値を返します
• PERCENTILE.EXC と QUARTILE.EXC は構造上、両端に届きません。QUARTILE.EXC(…, 0) は最小値ではなく #NUM! です
• 表に無い集計方法や 0〜7 の外のオプションは #VALUE!、データの範囲を超えた k は #NUM!
• リテラル引数も使えます（SUBTOTAL(9, 1, 2, 3) は 6）。型変換は SUM と同じ規則です
• SUBTOTAL にエラーを無視する手段はありません。 必要なら答えは AGGREGATE であって、大きい番号ではありません

V4 について

この 2 つは V5 のみです。ReoGrid V4 は 4.5 / 4.6 を含めて SUBTOTAL / AGGREGATE を持っていません。使っているブックは読み込めますが、V4 の評価器は知らない関数に対して何も返さないため、そのセルはエラーではなく空欄になります。#NAME? と違い、静かに空欄になった合計はレビューで見落とされます。V4 のアプリで「一覧を絞り込んで下の合計を見る」使われ方をしているなら、移行を検討する理由になります。

まとめ

• SUM はフィルターを知りません。 絞り込んだ一覧の下に置く合計は SUBTOTAL か AGGREGATE にする
• フィルターで隠れた行はどちらでも除外され、AGGREGATE はどのオプションでも除外します。101〜111 が足すのは手で隠した行だけ
• 小計セルは自動的に無視されるので、列全体を範囲にした総計がそのまま正しくなります。小計行を手で除く必要はありません
• AGGREGATE の第 2 引数は 3 つのスイッチ（入れ子の小計・非表示行・エラー）。**AGGREGATE(9, 6, …) が、いつも欲しくなる「計算できた行の合計」**です
• MEDIAN MODE.SNGL LARGE SMALL PERCENTILE QUARTILE も使えます。SUBTOTAL には無かった統計関数です
• ReoGrid V5 なら WinForms / WPF / Avalonia アプリの中でも、ヘッドレスでも同じ式が動きます。自分のコードで行を隠したときは SyncAggregateFormulas() を呼んでください

V5 の新機能を見る / 30 日間の評価版を試す

関連記事

• 組込関数リファレンス — 全関数と Excel との差異
• フィルター — 値リスト、条件フィルター、2 種類の非表示行
• アウトライン — グループ化、レベル、集計行の位置
• MATCH / XMATCH の違い — 「位置」を返す検索関数
• VLOOKUP / HLOOKUP / XLOOKUP の違いと使い分け
• 壊れない入力シートを作る — 入力規則・メモ・シート保護