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

技術記事

HotXLSのAGGREGATEオプションと入れ子ゲート

HotXLS Delphi Component

セル4つで十分です。自作評価器で =AGGREGATE(9,7,A1:A4) が 20 を返すのに、Excel は 40 を示します。A1 は 10、A2 は非表示行の 20、A3 は =1/0、そして A4 は値 30 のグループ小計 =SUBTOTAL(9,A1:A2) です。オプション 7 は、非表示行をスキップし、エラーをスキップし、入れ子の小計を値として数えることを意味するので、Excel は 10 と 30 を足します。20 が出るのは、外側の呼び出しの「非表示行をスキップ」設定が ON のまま A4 が自分自身の値を計算し、A4 が 10 に縮むときです。40 を得るには2つのことが要ります。3つのオプションビットを正しい位置でデコードすること、そして参照される数式が、それを参照した数式の options を決して見ないようにすることです

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

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

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

0〜7 の外のコードはすべて #VALUE! で拒否されます。Excel の動作と同じです

デコードが間違うのはなぜ1ビットのコードなのか

表そのものではなく表の言い換えから書かれたデコードは、たいてい次のような形になります。コード4以上でエラーを無視、コード2・3・6・7で非表示行をスキップ、入れ子のポリシーはまるごと忘れる。この版は本番環境で長く生き延びられます。多くの人が実際に使う組み合わせ、非表示行＋エラーは、どちらの読み方でもコード3と7に着地するので、取り違えが顔を出すのは1ビットのコードだけです。すると AGGREGATE(9,1,A1:A4) はフィルタなしの合計を返し、AGGREGATE(9,2,...) は非表示行をスキップしつつ #DIV/0! をそのまま伝播させます。実運用のワークブックがコード1と2を単独で使うことはめったにありません。だからこそ、顧客のファイルを待つより、公開表と突き合わせた見直しのほうが多くを拾います

HotXLS は options を、表の1列につき1つの集合所属テストという形で3つにデコードし、AGGREGATE 呼び出しの開始時に3つの結果をすべて代入し、終了時に前に有効だった値へ戻します。代入が無条件なのは意図的です。「オプションが求めるときだけフラグを ON にする」形のデコードでは、SUBTOTAL(109, ...) の内側にあるコード4の AGGREGATE が、外側の隠し行設定をクリアせずに引き継いでしまいます。自分の評価器を書く人にもレビューする人にも、同じ規則を示したのが下のスケッチです

// 自作評価器向けのデコード例：TAggPolicy と
// DecodeAggregateOptions は自分で定義する名前で、HotXLS の API ではない
type
  TAggPolicy = record
    IgnoreHidden, IgnoreErrors, SkipNested: Boolean;
  end;

function DecodeAggregateOptions(OptCode: Integer; out P: TAggPolicy): Boolean;
begin
  Result := (OptCode >= 0) and (OptCode <= 7);   // それ以外は #VALUE!
  if not Result then
    Exit;
  P.IgnoreHidden := OptCode in [1, 3, 5, 7];       // ビット0
  P.IgnoreErrors := OptCode in [2, 3, 6, 7];       // ビット1
  P.SkipNested   := OptCode in [0, 1, 2, 3];       // ビット2がクリア
end;
2つの AGGREGATE options デコードの比較：言い換えベースのデコードはコード 2・3・6・7 で非表示行ゲートを有効にし、4以上ではエラーを無視して入れ子ポリシーを持たない。一方 HotXLS のデコードは、非表示行を 1・3・5・7、エラーを 2・3・6・7、入れ子スキップを 0〜3 で試験する
取り違えが顔を出すのは1ビットのコードだけです。定番の非表示行＋エラーの組み合わせはどちらの表でもコード 3 と 7 に着地し、0〜7 の外のコードは Excel が拒否するのと同じく #VALUE! を返します

フィルタされた行と手動で非表示にした行の違いを含め、同じ隠し行規則の SUBTOTAL 側についてはSUBTOTAL と AGGREGATE の非表示行に関する記事で扱っています

外側のAGGREGATEはなぜ参照先の数式に手を伸ばすのか

「非表示行をスキップ」を6つか7つのセル走査ループへ渡す自然な方法が、計算機オブジェクトのフィールドだからです。1つの計算機が再計算パスで全部の数式を評価します。フィールドがセットされている間に走るすべてが、セットした集約自身の仕事である限り、この設計は正しく保たれます。崩れるのは1つの特定の瞬間、セルの取り出しです。ウォーカーがセル値を要求し、そのセルがまだキャッシュ結果のない数式を抱えていると、ワークブックはその場で数式をコンパイルして評価します。同じ計算機の上で、外側の設定が有効なままです

冒頭の例をこの種のエンジンで歩いてみます。コード7の AGGREGATE が非表示行スキップを ON にして A1 から始めます。A1 は 10、A2 は非表示なのでスキップ、A3 はエラーなのでスキップ。そしてウォーカーが、まだ計算されていない A4 に届きます。A4 の SUBTOTAL(9,A1:A2) が走ります。コード101〜111でのみ非表示行スキップを ON にする SUBTOTAL 実装（まったくまともな書き方です）は、コード9のときにそれを OFF にしません。設定がすでに ON になっているのを見つけ、A2 をスキップし、10 を返します。外側の合計は 10 + 10 = 20 になります。A4 の数式のどこにも、非表示行に触れる部分はありません

絶縁なしで外側の AGGREGATE が参照先へ漏れる仕組み：コード7で非表示行スキップが ON のまま、ウォークが A1:A2 に SUBTOTAL 9 を抱えるキャッシュなしの A4 に届き、セルの取り出しが同じ計算機の上でそれを評価し、入れ子の SUBTOTAL が設定を引き継いで 30 ではなく 10 を返す。合計は Excel の 40 に対して 20 を報告する
入れ子の設定は逆向きにも漏れます。出口で見つけた値を戻さずに入れ子セルスキップを OFF にする SUBTOTAL は、ウォークの途中で届いたキャッシュなしの小計より後ろのすべてのセルについて、外側のポリシーを解除します

入れ子集約の設定は、どちらの方向にも漏れます。コード0〜3では ON になっており、これを尊重する汎用の範囲ウォーカーは、B2 にたまたま SUBTOTAL が入っているだけで、=SUM(B1:B3) という参照先が B2 を落とします。逆方向では、先の状態を復元せずに終了時に入れ子スキップを OFF にする SUBTOTAL が、その後のウォーク全体で外側の AGGREGATE のポリシーを無効にします。一時的なフィールドでコンテキストを再帰的なウォークへ運ぶ評価器は、どれもこの形を持ちます。フィールドはセットしたフレームには正しく、引き継ぐすべてのフレームには間違いです

取り出し境界でポリシーを切断する

HotXLS は、AGGREGATE が自分で計算していない値を読むすべての地点に境界を置きます。取り出しの前に両方の設定を保存してクリアし、取り出しの後に finally ブロックで復元します。外側の集約は、取り出したばかりのセルには引き続き自前のポリシーを適用します。非表示行と入れ子セルのテストは、取り出しを囲むウォーカー側で起こるからです。参照先の数式は何のポリシーもなしで走ります。Excel と同じです。3つのウォーカーファミリーはすべてこの境界を通ります。SUM ファミリー（SUM、AVERAGE、COUNT、COUNTA、MAX、MIN）、閉形式の縮約（PRODUCT、STDEV、VAR）、そして k 引数を取る形式（MEDIAN、LARGE、SMALL、百分位数と四分位数）です

// 自作評価器向けのウォーカー例。FPolicy、FetchCell、
// IsRowHidden、IsSubtotalCell は自分で定義するメンバー
function TMyEvaluator.FetchForAggregate(Sheet, Row, Col: Integer;
  var V: Variant): Integer;
var
  Saved: TAggPolicy;
begin
  Saved := FPolicy;
  FPolicy := Default(TAggPolicy);     // 参照先の数式は自分のポリシーを持つ
  try
    Result := FetchCell(Sheet, Row, Col, V);
  finally
    FPolicy := Saved;
  end;
end;

// 範囲ウォークの内側：外側のポリシーは取り出しの周りで適用する
for R := R1 to R2 do
begin
  if FPolicy.IgnoreHidden and IsRowHidden(Sheet, R) then
    Continue;
  for C := C1 to C2 do
  begin
    if FPolicy.SkipNested and IsSubtotalCell(Sheet, R, C) then
      Continue;
    Code := FetchForAggregate(Sheet, R, C, V);
    // ... Code / V にエラーポリシーを適用し、それから蓄積する ...
  end;
end;
HotXLS の絶縁：各セルの取り出しの前に両方の選択設定を保存してクリアし、値を読み、finally ブロックで両方を復元する。参照先の数式はポリシーなしで評価され、外側のウォーカーは取り出しの前後で非表示行と入れ子セルのテストを適用し続ける
計算された配列引数も同じ境界を受け、失敗したセルごとに要素単位のエラーを保持します。リソース制限の結果は、エラー無視のオプションの下でも無視可能なエラーとして扱われることはありません

素の範囲ではない引数には、フラグのクリア以上の仕事が必要です。A1:A4/(B1:B4-20) のような引数は計算された配列で、オプション6がゼロ除算した要素だけを落とせるよう、要素の形が生き延びなければなりません。HotXLS は各参照範囲を同じ境界経由で2次元配列にし、失敗したセルは中断ではなくエラー要素として格納し、算術・比較の各演算子を要素ごとに適用します。複数シートにまたがる範囲や、角が逆転した範囲は #VALUE! を返します。TXLSCalculator.FormulaArrayMemoryLimit（既定 64 MB）に大きすぎる範囲はリソース制限の結果を返し、その結果は、オプション 2・3・6・7 がスキップできるエラーの集合の外に意図的に置かれています。数式が #N/A の無視を求めたからといって、エンジン自身のメモリ不足シグナルを呑み込めば、自信たっぷりに間違った数字が出来上がります。だから数式は失敗するのです

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

出会った最初のエラーを、そのままの形で返します。すべてのエラーを #VALUE! に潰すのは手軽な近道ですが、これをやると、#DIV/0! セルの上での AGGREGATE(9,4,A1:A3) が、#DIV/0! を素通しさせる Excel と食い違います。HotXLS は本物のエラー Variant と7つのエラー文字列（#NULL!、#DIV/0!、#VALUE!、#REF!、#NAME?、#NUM!、#N/A）の両方を、それぞれ固有のコードへ対応させ、各ウォーカーは記録した最初のコードを返します。数式が一度も計算されていないセルは、キャッシュ値ではなく取り出しからのリターンコードとしてエラーを届けますが、伝播のされ方はキャッシュ済みのものとまったく同じです

2つのカウント関数は、SUM ではなく SUBTOTAL に合わせます。関数2の COUNT では、options コードが何であれ、エラーセルは数えられず伝播もしません。COUNT は数しか数えないからです。関数3の COUNTA では、エラーセルは空でない値であり、options コードがエラーをスキップしない限り 1 として数えられます。この非対称性は、Excel が AGGREGATE の外で COUNT と COUNTA を扱うやり方と同じで、「エラーなら伝播」という一本槍の規則は2つとも間違えます

8つのコードすべてを1枚のワークシートで確認する

冒頭の4セルは、全コードにわたる手ごろなチェックになります。コード0〜7のそれぞれについて、SUM 形式と MEDIAN 形式を A1:A4 に評価し、手で計算した値と比べます。コード 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 と報告するのが、まさにこのケースです。このチェックは、AGGREGATE が走るとき A4 がキャッシュされていなければはじめて意味を持ちます。各ケースを、参照先を計算済みにして1回、しないままでもう1回評価してください

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       20 なら設定が A4 に漏れている
    Sheet.Cells[10, 1].Formula := '=AGGREGATE(12,6,A1:A4)'; // 20     10, 20, 30 の MEDIAN
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

保存したファイルを Excel で開いて Ctrl+Alt+F9 を押すと、同じセルに対する別の意見が得られます。より広めのチェックとしては、関数番号1から19までを、8つのコードすべてに対して走らせます

計算機を自分で組み立てるときのコールバック配線

両方の設定は2つのコールバックに依存します。TXLSCalculator.Create は値ゲッターの後に TXLSIsRowHidden と TXLSIsSubtotalCell を受け取り、どちらも既定は nil です。片方でも欠けると、表の対応する列は決して適用されず、すべての options コードが非表示行や入れ子集約、あるいはその両方を含めてしまいます。XLSX とクラシック XLS のワークブックオブジェクトは、コンストラクタで自前の実装を渡します。自分のデータソースの上で TXLSCalculator を手で組み立てるコードは、両方を用意しなければなりません。ワークブックは、SUBTOTAL(、AGGREGATE(、_xlfn.AGGREGATE( のいずれかで始まる数式によって入れ子集約を認識します。先頭の等号の有無は問いません。カスタムソースも同じ規則を使うべきです

// TMySheetSource と FormulaAt は自分の型。3つのメソッドシグネチャは
// TXLSGetValue、TXLSIsRowHidden、TXLSIsSubtotalCell に一致させる
// （SheetIndex は 1 起点、Row と Col はワークブックが渡すとおり 0 起点）
function TMySheetSource.IsSubtotalCell(SheetIndex, Row, Col: Integer): Boolean;
var
  F: string;
begin
  F := FormulaAt(SheetIndex, Row, Col);
  if (F <> '') and (F[1] = '=') then
    Delete(F, 1, 1);
  Result := SameText(Copy(F, 1, 9), 'SUBTOTAL(') or
            SameText(Copy(F, 1, 10), 'AGGREGATE(') or
            SameText(Copy(F, 1, 16), '_xlfn.AGGREGATE(');
end;

// Extern はホストがすでに持っているシート名テーブル
Calc := TXLSCalculator.Create(Extern, Source.GetCellValue,
  Source.IsRowHidden, Source.IsSubtotalCell);

プレフィックス規則には、生成するワークブックへの含意があります。=ROUND(SUBTOTAL(9,B2:B9),2) を書き出す生成器は、プレフィックステストが認識できないセルを作ります。その結果、コード0〜3はそれを数え、グループが総計に2回乗ります。SUBTOTAL や AGGREGATE の呼び出しは最も外側に置き、丸めは表示形式に任せてください。完全な Recalculate は依存先より先に参照先を評価するので、集約は通常キャッシュ済みの値を読みます。この順序は、依存グラフ上の増分再計算でも同じです。合計がまだおかしく見えて数式テキストは正しいように見えるときは、評価を一歩一歩トレースすることで、参照先がウォークの途中でオンデマンドに評価されたのか、それともコールバックがそもそも接続されていなかったのかが分かります

ここで述べたオプションデコーダー、取り出し境界、コールバックフックは、HotXLS Delphi spreadsheet component に付属します。マシンに Excel をインストールすることなく、Delphi と C++Builder のコードから XLS・XLSX・ODS のワークブックを読み、書き、再計算します