SUBTOTAL(109, ...)とSUBTOTAL(9, ...)が、隠し行を含むワークブックで同じ数字を返すなら、どちらかが間違っています。スプレッドシート計算エンジンを作るとき、最も落ち込みやすい罠の1つであり、Delphi と C++Builder 向けネイティブ Excel スプレッドシートコンポーネントである HotXLS は、計算エンジンにワークシートへ「この行は隠れているか」を尋ねさせることで、それを避けています
症状は、数式コードについての質問として届くことはめったにありません。届くのは不一致です。サーバー上のバッチジョブが合計を計算し、ユーザーが同じファイルをフィルタ適用済みで Excel で開くと、2つの数字は、フィルタで除外された行の合計ぶんだけズレています。集約関数を疑う者はいません。セルの数式文字列はどちらでも同一だからです。違いはすべて、評価器に見てよかったものの差にあります
SUBTOTAL 109が隠し行を含めてしまうのはなぜか？
理由は、ほとんどのエンジン設計では、数式を評価する層が行の可視性を知らないからです。素直な設計は、lxCalc.pasのTXLSGetValueコールバックのように、単一の値コールバックを通じてセル値に届きます。これは（シート、行、列）のトリプルに対して値だけを返し、それ以上は返しません。可視性は行レコードに保存されたプレゼンテーション属性であり、呼び出しチェーンを何も下りてこなければ、エンジンは1本の集約経路に落ち着きます。SUBTOTAL の関数番号テーブルの両半分が、そこへ解決されるのです。これは丸め誤差クラスの間違いではありません。隠し行ポリシーこそ、テーブルの後半が存在する理由の全部です。ISO/IEC 29500-1 として公開された ECMA-376 Part 1 は、SUBTOTALを数式関数定義（§18.17.7）の中で、内側の集約と隠し行ポリシーの両方を選ぶ第1引数つきで定義します。コード 1〜11 は AVERAGE、COUNT、COUNTA、MAX、MIN、PRODUCT、STDEV、STDEVP、SUM、VAR、VARP に対応し、手動で隠された行の値を含めます。コード 101〜111 は同じ11の集約を選び、それらを除外します。9の代わりに109と打つユーザーは、隠れたデータについて意図的な宣言をしており、その区別を潰すエンジンは、その宣言を黙って覆します
関数番号がエンジンの内側で何にマップされるか
HotXLS は SUBTOTAL の第1引数をCalcSubtotalFuncで解決します。ここはコード 101〜111 を、コード 1〜11 と同じ内側関数識別子へ正規化した上で、集約そのものでディスパッチします。ファミリーの大半は、SUM、COUNT、COUNTA、MIN、MAX、AVERAGE を扱う増分のExcelSumアキュムレータへ流れます。5つはそういきません。STDEV、VAR、STDEVP、VARP、PRODUCT はデータ上の閉形式のパスを要するので、CalcSubtotalFuncは内側コード 12、46、193、194、183 を別のリデューサーSubtotalReduceVarianceへ振ります。この分岐は、何かに手を付ける前にまず地図を引くべき最初の場所です。2本の独立した集約経路は2本の独立したセルウォークループを意味し、片方だけに加えた隠し行チェックは、最悪の結果を生み出します。同じ範囲でSUBTOTAL(109, ...)はフィルタを尊重するのにSUBTOTAL(107, ...)は尊重しない、という具合です。HotXLS には、AGGREGATE を入れると6本あります。範囲評価、素の範囲収集、そして3つの独立したリデューサーに散らばっています
なぜ6つの新しいシグネチャではなくスクラッチフィールドなのか
新しいパラメータを6つのセルウォーク関数と、それらを呼ぶすべてのものへ通すのは、1つの boolean のためにホットなコードパスへ広い変更を入れることになるからです。HotXLS には代替案の前例があります。GetRangeInfoが3D 参照が外部ワークブックに解決したことを記録するのに使うスクラッチフィールドと同じ精神の、計算機上の一時フィールドです。隠し行ゲートはその2つ目です。（SheetIndex、row）を引数に Boolean を返す関数として宣言されたコールバック型TXLSIsRowHidden、FIsRowHiddenに保存されたもの、そして一時のFIgnoreHiddenRowsフラグです。フラグは、関数コードが 101〜111 に落ちるときCalcSubtotalFuncの入口で、また、隠し行除外を選ぶ AGGREGATE のオプションコードのときCalcAggregateFuncの入口で armed になります。その後、すべてのセルウォークループがそれを検査し、セットされていれば1行をスキップします。1行ずつの代償で
// 6つのセル走査ループすべてで繰り返される形
for sh := s1 to s2 do
for rr := r1 to r2 do
begin
if FIgnoreHiddenRows and Assigned(FIsRowHidden) and FIsRowHidden(sh, rr) then
Continue;
for cc := c1 to c2 do
begin
// ... Cells[rr, cc]をアキュムレータに折り込む ...
end;
end;このセットするコードの中にある2つの詳細が、仕組み全体の正しさを支えている。フラグは単にセット・クリアされるのではなく、保存されてから復元される。SUBTOTALの引数は、外側の集計がまだスタック上にある間に自身の評価を実行する式を含むことがありうるため、そのネストした処理は外側のゲートを継承したり破壊したりしてはならないからだ。そして復元はfinallyブロックの中で行われる。CalcSubtotalFuncにはエラーコードのための複数の早期リターンがあり、エラーリターンの後もセットされたままのフラグは、再計算順序の中で次の無関係な数式を静かに壊してしまうからだ
prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
FIgnoreHiddenRows := True;
try
// Item.Child[2] .. Item.Child[ChildCount]に対して集計する
// 以下のすべてのExitパスはfinallyでカバーされる
finally
FIgnoreHiddenRows := prevIgnoreHidden;
end;このAssignedテストこそが、計算機 API の互換性を保っています。TXLSCalculatorのコンストラクタは nil をデフォルトとする第3引数を取るので、2引数呼び出しで計算機を組み立てるコードは変更なしでコンパイルされ、隠し行を含める振る舞いを得ます
隠し行のビットは実際にどこから来るのか
ワークシートから、そして2つの異なるソースからです。HotXLS は2つのワークブックエンジンを載せています。レガシー BIFF 側は、TXLSWorkbook.GetRowHiddenを通じて届くTXLSRowInfoList.GetHiddenから答えます。OOXML 側は、TXLSXWorkbook.GetCalcRowHiddenを通じて届くTXLSXWorksheet.GetRowHiddenから答えます。どちらも構築時に、それぞれが鏡写しするセル値コールバックと並んで、計算機へ配線されます。こうした橋が普段壊れるのは行の規約のところなので、明示しておく価値があります。計算機はコールバックに0ベースの行を渡します。TXLSGetValueがすでに使っている座標と一致します。XLSX ワークシートは行非表示マップを1ベースの行番号でキーづけします。Excel が行を数えるやり方そのものであり、公開プロパティRowHidden[ARow]が露出しているものでもあります。したがって XLSX 橋はルックアップの前に1を加え、BIFF 橋は加えません。TXLSRowInfoListはすでに0ベースだからです。両橋とも、有効範囲外のシートインデックスや行は可視として扱うので、範囲外の問い合わせはデータを落とす代わりに、隠し行を含める答えへ退化します
フィルタ済みワークブックで何が変わるか
隠し行ポリシーが最も効いてくるのはこのケースです。HotXLS でApplyAutoFilterにより AutoFilter を適用すると、列の条件を評価し、一致しないすべてのデータ行を隠します。ユーザーがフィルタのドロップダウンをクリックしたときに Excel がやっていることそのものです。それらの行はユーザーには見えず、計算エンジンが同じ行状態を参照するので、サーバー側のSUBTOTAL(109, ...)は、フィルタ前ではなくフィルタ後の合計を報告します
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
VisibleRows: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('orders.xlsx');
Sheet := Book.Sheets[0];
Sheet.SetAutoFilter('A1:E500');
Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
VisibleRows := Sheet.ApplyAutoFilter; // 一致しない行を隠す
Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
Book.Recalculate;
// セル値は、同じフィルタで Excel が見せるものと一致する。
// VisibleRows は、そこへ流れ込んだ行数を教えてくれる
Book.SaveAs('orders-filtered.xlsx');
finally
Book.Free;
end;
end;手動の非表示も同じやり方で動きます。RowHidden[ARow] := Trueは、フィルタが書くのと同じ状態だからです。この等価性は Excel では意図的なものであり、HotXLS でも成り立ちます。1つの帰結には、生成したワークブックに添えるドキュメントのどこかに一言書いておく価値があります。コード 109 で計算した合計は、見た目に依存する数字です。フィルタを解除した受取人はそれを変えてしまいます。レポートが、読み手が見た目に何をしても固定の数字を述べなければならないなら、正しい選択はコード 9 であり、昔からそうです。フィルタ、入力規則、テーブルは、データ入力規則、AutoFilter、テーブルの記事でまとめて扱っています。行を隠すことはどの数式にも触れないので、それ自体では依存グラフを dirty にもしません。大きなワークブックの応答性を保つためにdirty なサブグラフ上の増分再計算に頼っているなら、知っておく価値のあることです
AGGREGATEのオプションコード
AGGREGATE は第2のポリシー引数を持つ SUBTOTAL であり、HotXLS はCalcAggregateFuncで処理します。オプション引数は独立したスイッチを符号化します。範囲内の入れ子 SUBTOTAL と AGGREGATE 呼び出しをスキップするか、隠し行上の値をスキップするか、エラー値を伝播させずに抑制するか。HotXLS は共有の隠し行ゲートをオプションコード 2、3、6、7 で armed にし、オプションコード 4〜7 でエラー値を抑制します。関数番号引数はその後、分散、標準偏差、積が自分のリデューサーを通る経路も含め、SUBTOTAL とまったく同じやり方で集約を選びます。実際のワークブックは、SUBTOTAL 数式を、他の SUBTOTAL 数式が集約する範囲の外に置くのがほとんどです。ジェネレータが重なり合う集約範囲を組み立てるなら、内側の合計を外側の範囲から外しておいてください。重複排除をオプションコードに頼らないことです
同じディスパッチャにあるarityガード
同じディスパッチャは引数カウントのチェックも運んでおり、設計上の理由はスクラッチフィールドの背後にあるものと同じです。チェックは、1度だけ書ける場所に置くのです。およそ280個の組み込み関数本体があるとき、各本体にItem.ChildCountに対する自前の引数数検証を任せると、引数が多すぎるケースへの一貫した境界がありません。そして罠は見逃しやすい。=SIN(1,2)のような呼び出しは、第1引数を調べ、余分を無視し、もっともらしい数字を返す関数本体へ届きます。Excel が返すのは#VALUE!だというのに。HotXLS はすべての組み込みの宣言 arity を関数レジストリに保存し、SUM、IF、CONCAT のような可変長関数を -1 で示すTHashFunc.ArgsCntとして露出し、TXLSFormula.FuncArgsCntByPtgプロパティ経由で転送し、主ディスパッチャGetValueItemFuncの先頭の1つのゲートでチェックします
lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
lProvidedArgs := Item.ChildCount - 1; // Child[0] は関数ノード
if lProvidedArgs > lDeclaredArgs then
begin
Result := lxErrorValue; // =SIN(1,2) は #VALUE! になる
Exit;
end;
end;このガードは引数が多すぎる場合を拒否し、少なすぎる場合については意図的に何も言いません。末尾の省略可能な引数を省くのは、VLOOKUP、SUBSTITUTE、そのほか長いリストの関数で Excel では合法なので、対称なチェックは、間違った数式を捉えるために正しい数式を壊します。未知の識別子は可変長として報告され、ゲートを丸ごと素通りします。ユーザー定義関数がその波に巻き込まれないための仕組みです。自前の関数を登録する場合も、数式エンジンとカスタム関数のガイドで述べられる振る舞いは影響を受けません。少なすぎるケースは各関数本体に残ります。それぞれの本体が、自前のエラーコードのセマンティクスを持っているからです
ここで説明した計算エンジン、2つのワークブックファサード、そしてそれに供給するAutoFilterと行の可視性のAPIは、HotXLS Delphiスプレッドシートコンポーネントの一部であり、DelphiとC++Builder向けに完全なソースコード付きで提供され、実行するマシンにExcelのインストールを必要としない