LlosLabソフトウェア開発ブログPDF、Delphi、およびドキュメントソフトウェア開発者向けのエンジニアリングノート。

技術記事

Delphi版HotXLSにおけるAGGREGATEオプション行列とゲート漏れ

HotXLS Delphi Component

DelphiとC++Builder向けのネイティブExcelスプレッドシートコンポーネントであるHotXLSは、2026年9月にAGGREGATEに関する関連する2つの修正を出荷しました。バージョン2.382.0はoptions引数を修正し、コード1/3/5/7が非表示行を無視し、2/3/6/7がエラーを無視し、0〜3が入れ子のSUBTOTALセルとAGGREGATEセルを無視するようにしました。Microsoftのドキュメントどおりです。続いてバージョン2.382.3は、それらの選択フラグが、関数が参照しているまさにそのセルの評価へ漏れ出すのを止めました。1つ目の不具合は、表の書き写しのバグがいつもそうであるように、気まずいものです。ビット位置が入れ替わっていたので、非ゼロのoptionsコードを使ったすべての数式が、作者が求めていないポリシーを受け取っていました。2つ目のほうが面白く、一時的なフィールドで再帰的な走査に文脈を渡すあらゆる評価器で出会う形のものです。外側の集計がフラグを立て、範囲を走査し、まだ計算されていない数式を持つセルを引っ張ってきます。その数式は同じ計算器の上で走り、同じ立ったフラグを見て、黙って間違った行を集計し、数式のテキストだけからは誰も説明できない分だけずれた数を生みます

AGGREGATEのoptions 0〜7は実際に何を選ぶのか

AGGREGATEのoptions引数は3ビットの行列であり、3つのビットは独立しています。ビット0（値1）は非表示行を無視することを意味し、ビット1（値2）はエラー値を無視することを意味し、ビット2（値4）は入れ子のSUBTOTALセルとAGGREGATEセルを無視するのをやめることを意味します。低いコードではそれらをスキップするのが既定だからです。このうち2つの点は逆に覚えやすいものです。非表示行のビットは下位ビットであって真ん中のビットではないので、AGGREGATE(9,1,...)がフィルタ後の合計の形であり、AGGREGATE(9,2,...)がエラーを許容する形です。そして入れ子集計のポリシーは他の2つに対して反転しています。自分の数式がSUBTOTALかAGGREGATEであるセルを普通の値として扱うのは、コード4〜7だけです。ECMA-376 Part 1 §18.17.7は、SUBTOTALを、コード1〜11と101〜111にまたがる同じ「非表示行を含めるか除外するか」の分割付きで定義しており、OOXMLファイルでは_xlfn.プレフィックスの下に格納されるAGGREGATEが、その分割をoptions引数へ一般化しています。したがってMicrosoftがAGGREGATE関数について公開している表は、便利な目安ではなく、エンジンが満たさなければならない契約です

オプション非表示行エラー値入れ子のSUBTOTAL / AGGREGATE
0含める伝播する無視する
1無視する伝播する無視する
2含める無視する無視する
3無視する無視する無視する
4含める伝播する含める
5無視する伝播する含める
6含める無視する含める
7無視する無視する含める

HotXLSのAGGREGATEオプションはなぜ逆だったのか

元のTXLSCalculator.CalcAggregateFuncが、表そのものではなく表の言い換えから書かれていたからです。このコードはignoreErrors := (optCode >= 4) and (optCode <= 7)を計算し、コード2、3、6、7で非表示行のゲートを立てていましたが、入れ子集計のポリシーはまったく実装されていませんでした。SUBTOTALとAGGREGATEの非表示行についての以前の記事は、その欠落を未解決の制限として挙げ、当時出荷されていたとおりの古い対応付けを記述していました。その記述はコードについては正確で、Excelについては間違っていました。そして長いあいだ誰も気づきませんでした。多くの人が組み合わせる2つのポリシー、非表示行とエラーは、どちらの表でもコード3と7に着地するからです。入れ替わりを露見させたのは単一ビットのコードだけでした。AGGREGATE(9,1,A1:A4)はフィルタされていない合計を返し、AGGREGATE(9,2,...)は#DIV/0!を伝播させたまま非表示行をスキップしていました。この不具合は、顧客のファイルからではなく、lxCalc.pasの静的レビューから表面化し、プロジェクトの既知問題レジストリにHXLS-008として記録されました。これは、単一ビットのコードが本番のワークブックにどれほど稀にしか現れないかを物語っています。バージョン2.382.0はデコードを3つの集合帰属テストとして書き直し、入れ子ポリシー用の2つ目のゲートを追加しました。これはワークブックがTXLSIsRowHiddenと並べて提供する新しいTXLSIsSubtotalCellコールバックを通して配線されています

v2.382.0前後でのHotXLSのAGGREGATEオプションのデコード。元のCalcAggregateFuncはコード2、3、6、7で非表示行ゲートを立て、4以上でエラーを無視し、入れ子ポリシーを持ちませんでした。修正後は非表示行を1、3、5、7で、エラーを2、3、6、7で、入れ子のスキップを0〜3で検査します
よく使われる非表示行＋エラーの組み合わせはどちらの表でもコード3と7に着地するので、入れ替わりを露見させたのは単一ビットのコードだけでした。0〜7の外のコードはExcelが拒否するのと同じくlxErrorValueを返すようになっています
// TXLSCalculator.CalcAggregateFunc、v2.382.3の形
if (optCode < 0) or (optCode > 7) then
begin
  Result := lxErrorValue;            // Excelは0..7の外のコードを拒否する
  Exit;
end;
ignoreErrors := optCode in [2, 3, 6, 7];
prevIgnoreHidden := FIgnoreHiddenRows;
prevIgnoreSubtotal := FIgnoreSubtotalCells;
FIgnoreHiddenRows := (optCode in [1, 3, 5, 7]) and Assigned(FIsRowHidden);
FIgnoreSubtotalCells := (optCode in [0, 1, 2, 3]) and Assigned(FIsSubtotalCell);
try
  // ... function_numを内側のiftabに対応付け、ref1..refNをたどる ...
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
  FIgnoreSubtotalCells := prevIgnoreSubtotal;
end;

2つのフラグが、オプションが要求したときだけ設定されるのではなく、無条件に代入されている点に注目してください。v2.382.0のバージョンはまだif ... then FIgnoreHiddenRows := Trueを使っており、つまりSUBTOTAL(109, ...)の中に入れ子になったコード4のAGGREGATEが、外側の非表示行ゲートをクリアするのではなく継承していました。入口でデコード済みの値を代入し、finallyブロックで以前の値を復元することで、各AGGREGATE呼び出しは自分の走査の間だけ自分のポリシーを所有し、それ以上は持ちません。バージョン2.382.0は配列形式も正直にしました。引数が1次元または2次元のVariant配列に評価される場合、CalcAggregateFuncはすべての要素をたどり、要素ごとにエラーポリシーを適用します。古いコードはNaNのdoubleかどうかだけを検査し、それ以外は配列全体をExcelSumに渡していました

外側のAGGREGATEが参照先の数式へ漏れるのはなぜか

FIgnoreHiddenRowsとFIgnoreSubtotalCellsが計算器のフィールドであり、計算器は1回の再計算の間に評価されるすべての数式で共有されるからです。このゲートがスクラッチフィールドとして設計されたのは、6つのセル走査ループがあらゆるシグネチャにパラメータを通さずにこれを参照できるようにするためであり、ゲートが立っている間に走るすべてのものがそれを立てた集計に属しているかぎり、この設計は健全です。この前提が崩れるのは1つの特定の地点です。FGetValueです。走査側がワークブックにセルの値を求め、そのセルがキャッシュされた結果を持たない数式を保持している場合、ワークブックはその場でその数式をコンパイルして評価します。同じTXLSCalculatorの上で、外側のゲートが立ったままです。HotXLS.WorkbookApiTests.pasのリグレッションフィクスチャが、4つのセルでこの失敗を示します。A1は10、非表示行のA2は20、A3は=1/0、A4は=SUBTOTAL(9,A1:A2)を保持し、その正しい値は30です。ここで=AGGREGATE(9,7,A1:A4)を評価します。非表示行を無視し、エラーを無視し、入れ子の小計を値として数えます。Excelは10 + 30 = 40を返します。A4がキャッシュされていない状態で、2.382.3より前のエンジンは非表示行ゲートを立ててA4まで歩き、その評価を誘発し、コード9のCalcSubtotalFuncが立てられたゲートを継承しました。この関数はコード101〜111でしかフラグを設定せず、クリアすることは決してないからです。A4は30ではなく10に評価され、外側の合計は20として返ってきました。間違った数を生み出した経路のどちらの数式にも、非表示行の話はどこにも出てきません

外側のHotXLS AGGREGATEが参照先へ漏れる仕組み。コード7でFIgnoreHiddenRowsが立つと、走査はA1:A2に対するSUBTOTAL 9を保持する未キャッシュのA4に到達し、FGetValueが同じ計算器でそれを評価し、CalcSubtotalFuncがゲートを継承して30ではなく10を返すため、合計はExcelの40ではなく20を報告します
入れ子のゲートは逆方向にも漏れ、CalcSubtotalFuncは終了時にFIgnoreSubtotalCellsを復元せずリセットしていたので、走査の途中で未キャッシュの小計に到達すると、それ以降のすべてのセルで外側のポリシーが解除されていました

入れ子集計のゲートも、逆方向に同じ形で漏れました。コード0〜3ではFIgnoreSubtotalCellsが立ち、GetValueItemRangeの汎用範囲走査がそれに従うので、=SUM(B1:B3)という数式を持つ参照先は、B2がたまたまSUBTOTALを含んでいれば黙ってB2を落としていました。さらに悪いことに、CalcSubtotalFuncは終了時にFIgnoreSubtotalCellsを以前の値に戻すのではなくFalseにリセットしていたので、走査の途中で到達したキャッシュされていないSUBTOTALの参照先が、それ以降のすべてのセルに対して外側のゲートを解除していました。プロジェクトの既知問題レジストリはこれをHXLS-008の下に「入れ子になった選択状態の漏れ」として記録しており、これはこの種のバグの正しい名前です。自分で設定したフレームでは正しく、継承したすべてのフレームでは間違っている、グローバルな一時フラグです

AggregateGetCellValueとAggregateGetItemValueが走査をどう隔離するか

v2.382.3の修正は、AGGREGATEが自分で計算していない値を読むすべての地点の周りに境界を置きます。TXLSCalculator.AggregateGetCellValueは生のFGetValue呼び出しを包みます。両方のフラグを退避し、クリアし、取得を実行し、finallyブロックで復元します。外側の集計は、たったいま取得したセルに対して自分のポリシーを依然として適用します。非表示行と入れ子セルの検査は取得の前後を囲む走査側で行われるからです。しかし参照先の数式そのものはポリシーなしで走ります。Excelがそうしているからです

HotXLS v2.382.3の隔離。AggregateGetCellValueが両方のゲートフラグを退避してクリアし、FGetValueで取得してfinallyブロックで復元するので、参照先の数式はポリシーなしで評価され、外側の走査側は取得の前後で非表示行と入れ子セルの検査を依然として適用します
AggregateGetItemValueは計算された配列引数に対しても同じことをして取得エラーをVarAsErrorに対応付け、リソース制限のコードはエラー無視のオプションの下でも意図的に無視可能なエラーとして扱われません
function TXLSCalculator.AggregateGetCellValue(SheetIndex, Row, Col: Integer;
  var Value: Variant; var OutOfRange: Boolean): Integer;
var
  Hidden, Nested: Boolean;
begin
  Hidden := FIgnoreHiddenRows;
  Nested := FIgnoreSubtotalCells;
  FIgnoreHiddenRows := False;        // 参照先の数式は自分のポリシーを持つ
  FIgnoreSubtotalCells := False;
  try
    Result := FGetValue(SheetIndex, Row, Col, Value, OutOfRange);
  finally
    FIgnoreHiddenRows := Hidden;
    FIgnoreSubtotalCells := Nested;
  end;
end;

AggregateGetItemValueは範囲以外の引数に対して同じことをしますが、フラグをクリアするだけでは足りません。A1:A4/(B1:B4-20)のような引数は計算された配列であり、その要素の形が生き残らなければならないからです。このラッパーは、素の範囲をAggregateGetCellValueを通して2次元Variant配列に実体化し、エラーコードを返したセルをVarAsErrorに対応付けて、要素ごとにエラーポリシーを適用できるようにします。また二項と単項の演算子ノード（SA_ADD、SA_DIV、SA_UNARMINUSなど）をApplyArrayBinaryOpとApplyArrayUnaryOpで再帰的にたどり、それ以外は通常のGetValueItemへ落ちます。実体化の手前には2つのガードがあります。EffectiveFormulaArrayMemoryLimitより大きい範囲はlxErrorResourceLimitを返し、複数シートにまたがる範囲や逆転した範囲は#VALUE!を返します。リソース制限のコードは、オプション2/3/6/7の下でも意図的に無視可能なセルエラーとして扱いません。ユーザーが#N/Aをスキップするよう求めたからといって、自分のメモリ不足の合図を飲み込むエンジンは嘘をついていることになるからです。AGGREGATEの3つの走査側、SUM系のためのAggregateCollectRange、STDEV・VAR・PRODUCTのためのAggregateReduceVariance、そしてMEDIANと分位点系のためのAggregateReduceWithKは、すべてFGetValueとGetValueItemからこの2つのラッパーへ切り替えられ、それぞれがFIsSubtotalCellを通した入れ子セルの検査を得ました

エラーを無視しないとき、AGGREGATEはどのエラーを返すのか

v2.382.3以降は、元のエラーです。バージョン2.382.0はエラーセルを正しく検出しましたが、そのすべてをlxErrorValueに押し潰していたので、#DIV/0!のセルに対してAGGREGATE(9,4,A1:A3)が#VALUE!を返していました。Excelは最初に出会ったエラーをそのまま伝播させます。置き換えられたヘルパーAggregateErrorCodeは、Variantが本物のvarErrorであれ7つのエラー文字列の1つであれ、対応するlxError*コードに対応付けます。そしてAggregateValueIsErrorは、結果が非ゼロかどうかの検査にすぎなくなりました。各走査側は最初に見たエラーコードを記録してそれを返します。これはつまり、数式が一度も計算されておらず、したがってエラーがキャッシュされたVariantとしてではなくFGetValueの戻りコードとして届くセルも、キャッシュされたものと同じように伝播するということです。AggregateCollectRangeの中では2つの計数関数が特別扱いを受け、その扱いはSUMではなくSUBTOTALに合わせています。内側の関数0、COUNTでは、エラーセルはoptionsコードに関係なく決して数えられず、決して伝播されません。COUNTは数値だけを数えるからです。内側の関数169、COUNTAでは、エラーセルは空でない値であり、optionsコードがエラーを無視しないかぎり1として数えられ、無視する場合はスキップされます。この非対称性は、AGGREGATEの外でもExcelがCOUNTとCOUNTAを扱う方法であり、汎用の「エラーなら伝播」ルールが黙って間違える類の細部です

8オプションのリグレッション行列が検証するもの

上で説明したフィクスチャはAggregateFunc_OptionMatrixCoversHiddenErrorsAndNestedAggregatesで完全な行列として実行されます。0〜7の各optionsコードについて、A1:A4に対するSUM形式とMEDIAN形式の両方を評価し、結果を手で導いた期待値と突き合わせます。コード0、1、4、5はA3からの#DIV/0!を伝播させなければなりません。どれもエラーを無視しないからです。コード2はSUM 30とMEDIAN 15を与えます。入れ子のA4をスキップした10と20からです。コード3は10と10を与えます。コード6は60と20を与えます。A4の30がいま数えられるからです。コード7は40と20を与えます。漏れの修正前は20を返していたケースです。既知問題レジストリに記録されているより広い受け入れテストは、19の関数番号すべてを8つのコードすべてに対して、各参照先がキャッシュ済みの場合と未キャッシュの場合の両方でカバーし、Win32とWin64で304のシナリオを実行します

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    Sheet.Cells[1, 1].Value := 10;
    Sheet.Cells[2, 1].Value := 20;
    Sheet.Cells[3, 1].Formula := '=1/0';
    Sheet.Cells[4, 1].Formula := '=SUBTOTAL(9,A1:A2)';   // グループ小計 = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  非表示はスキップ、エラーは伝播
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       非表示＋エラー＋入れ子をスキップ
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       エラーだけをスキップ
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       v2.382.3より前は20だった
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

境界はまだどこにあるのか

これを土台にする前に知っておくべき3つの制限があります。1つ目は、入れ子集計の述語がテキストベースであることです。TXLSXWorkbook.GetCalcIsSubtotalCellと、そのクラシックエンジン側の双子は、セルの数式がSUBTOTAL(、AGGREGATE(、または_xlfn.AGGREGATE(で始まるときにTrueを返します（先頭の等号の有無は問いません）。したがって=IF(C1,SUBTOTAL(9,B1:B9),0)や=SUBTOTAL(9,B1:B9)*2のような数式は入れ子として認識されず、Excelならスキップする場面でコード0〜3によって二重計上されます。計算された小計を出力する生成側は、集計の呼び出しを数式の先頭に置くべきです。2つ目は、隔離がAGGREGATEの3つの走査側に宿っていることです。CalcSubtotalFuncは依然としてGetValueItemRange、CollectRangeValues、SubtotalReduceVarianceを通して走査し、これらはFGetValueを直接呼びます。したがって範囲にキャッシュされていない参照先の数式を含むSUBTOTAL(109, ...)は、依然として自分の非表示行ゲートをその参照先へ渡してしまいます。完全なRecalculateは依存先より先に参照元を評価するので、キャッシュされた経路が取られ、ゲートが継承されることはありません。露出するのはCalculateを通したその場の評価と、キャッシュ値なしで読み込まれたワークブックに限られます。大きなモデルの応答性を保つために依存グラフ上のインクリメンタル再計算に頼っているなら、この漏れを眠らせておくのも同じ順序の保証です。3つ目は、両方のゲートがAssigned(FIsRowHidden)とAssigned(FIsSubtotalCell)を条件としていることです。どちらのワークブックファサードもコンストラクタでコールバックを配線しますが、元の2つの引数だけでTXLSCalculatorを手で組み立てるコードは、すべてのoptionsコードに対して黙って旧来の「すべて含める」振る舞いを得ます。合計が間違って見えるのに数式のテキストは正しく見えるとき、評価を1ステップずつ追跡するのが、参照先が継承されたゲートの下で評価されたのか、コールバックがそもそも一度も取り付けられていなかったのかを見る最短の方法です

ここで説明した計算エンジン、オプションのデコーダ、隔離された取得のラッパー、そしてそれらを固定するリグレッション行列は、すべてソースとしてHotXLS Delphi spreadsheet componentに含まれています。これはDelphiとC++Builderで、ExcelのインストールなしにXLS、XLSX、ODSのワークブックを読み、書き、再計算します