隠し行を含むワークブックでSUBTOTAL(109, ...)とSUBTOTAL(9, ...)が同じ数値を返すなら、そのどちらかは間違っている。DelphiとC++Builder向けのネイティブExcelスプレッドシートコンポーネントであるHotXLSは、バージョン2.197.0までまさにそのように振る舞っていた。計算エンジンには、あるワークシートに対して特定の行が隠されているかどうかを問い合わせる手段が一切なかったからだ
この症状が数式コードに関するバグ報告として届くことはめったにない。それは不一致として届く:サーバー上のバッチジョブが合計を計算し、ユーザーがフィルタを適用した状態で同じファイルをExcelで開くと、2つの数値はフィルタで除外された行が合計していたであろう分だけ食い違う。誰も集計関数を疑わない。セル内の数式文字列はどちらの場所でも同一だからだ。違いは、評価器が見ることを許されていたものの中に完全に潜んでいる
なぜSUBTOTAL 109は隠し行を含んでしまうのか
ほとんどのエンジン設計では、数式を評価する層が行の可視性について一切知ることがないからだ。HotXLSは教科書どおりの例だった:lxCalc.pas内の計算エンジンは、(シート、行、列)の三つ組に対する値だけを返す単一のTXLSGetValueコールバックを通じてセルの値に到達していた。可視性は行レコードに保存される表示上の属性であり、そのレコードのどの部分も呼び出しチェーンを下ってくることはなかった。そのためエンジンは集計経路を1つしか持たず、SUBTOTAL関数番号テーブルの両半分がその同じ経路に解決されていた。これは丸め誤差のクラスの不具合ではない。それはこのテーブルの後半部分が存在する理由そのものである。ISO/IEC 29500-1として発行されたECMA-376 Part 1は、その数式関数の定義(§18.17.7)の中でSUBTOTALを定義しており、その第一引数は内側の集計と隠し行に対する方針の両方を選択する。コード1から11はAVERAGE、COUNT、COUNTA、MAX、MIN、PRODUCT、STDEV、STDEVP、SUM、VAR、VARPにマップされ、手動で隠された行の値を含める。コード101から111は同じ11個の集計を選ぶが、それらを除外する。9ではなく109と入力するユーザーは、隠されたデータについて意図的な表明を行っているのであり、その区別を潰してしまうエンジンはその表明を静かに覆してしまう
関数番号がエンジンの内側で何にマップされるか
HotXLSはSUBTOTALの第一引数を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, ...)はそうしない、という具合にだ。AGGREGATEを含めてこのループを数えると、範囲評価、単純な範囲収集、そして3つの別個のリデューサーに散らばった6つが見つかった
なぜ6つの新しいシグネチャではなくスクラッチフィールドなのか
新しいパラメータを6つのセル走査関数とそれらを呼び出すすべての箇所に通すことは、たった1つのブール値のためにホットなコードパスに広範な変更を加えることになるからだ。HotXLSにはすでに代替手段の先例があった:計算機上の一時的なフィールドであり、3D参照が外部ワークブックへと解決されたときにそれを記録するためにGetRangeInfoが使うスクラッチフィールドと同じ精神である。バージョン2.197.0は2つ目のそれを追加した。エンジンは(SheetIndex, row)の関数としてBooleanを返すTXLSIsRowHiddenというコールバック型を獲得し、それはFIsRowHiddenに保存され、さらに一時的なFIgnoreHiddenRowsフラグも加わった。このフラグは、関数コードが101から111の範囲にあるときにCalcSubtotalFuncの入り口で、また隠し行の除外を選択するAGGREGATEのオプションコードに対してCalcAggregateFuncの入り口でセットされる。その後すべてのセル走査ループがこれを調べ、セットされていれば1行をスキップする。それぞれに1行を加えるだけである
// The shape repeated in all six cell-walk loops
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
// ... fold Cells[rr, cc] into the accumulator ...
end;
end;このセットするコードの中にある2つの詳細が、仕組み全体の正しさを支えている。フラグは単にセット・クリアされるのではなく、保存されてから復元される。SUBTOTALの引数は、外側の集計がまだスタック上にある間に自身の評価を実行する式を含むことがありうるため、そのネストした処理は外側のゲートを継承したり破壊したりしてはならないからだ。そして復元はfinallyブロックの中で行われる。CalcSubtotalFuncにはエラーコードのための複数の早期リターンがあり、エラーリターンの後もセットされたままのフラグは、再計算順序の中で次の無関係な数式を静かに壊してしまうからだ
prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
FIgnoreHiddenRows := True;
try
// aggregate over Item.Child[2] .. Item.Child[ChildCount]
// every Exit path below is covered by the finally
finally
FIgnoreHiddenRows := prevIgnoreHidden;
end;この変更を後方互換に保っているのはAssignedのチェックである。HotXLSは計算機のコンストラクタをデフォルトでnilになる3つ目のパラメータで拡張したため、古い2引数の呼び出しでTXLSCalculatorを構築するどんなコードも、依然としてコンパイルが通り、依然としてレガシーな隠し行を含める挙動を得られる。既存のAPIの形は何も変わっていない
隠し行のビットは実際にどこから来るのか
ワークシートから、2つの異なる源を通じてである。HotXLSは2つのワークブックエンジンを抱えているからだ。レガシーなBIFF側はTXLSWorkbook.GetRowHiddenを通じて到達するTXLSRowInfoList.GetHiddenから答える。OOXML側はTXLSXWorkbook.GetCalcRowHiddenを通じて到達するTXLSXWorksheet.GetRowHiddenから答える。どちらも構築時に、それらが鏡写しにしているセル値コールバックとともに計算機に組み込まれる。行の規約は、この種の橋渡しが普通は間違える場所であり、明示的に述べておく価値がある。計算機はコールバックに0始まりの行番号を渡す。これはTXLSGetValueがすでに使っている座標と一致する。XLSXのワークシートは、Excelが行番号を数えるのとまったく同じように、そして公開されているRowHidden[ARow]プロパティが公開しているのとまったく同じように、行の隠し状態のマップを1始まりの行番号でキーにしている。したがってXLSXの橋渡しはルックアップの前に1を加え、BIFFの橋渡しはそうしない。TXLSRowInfoListはすでに0始まりだからだ。どちらの橋渡しも、有効範囲外のシートインデックスや行を可視として扱う。したがって範囲外の問い合わせは、データを落とすのではなく古い「隠し行を含める」という答えに退化する
フィルタ済みワークブックで何が変わるか
これがサポートチケットを生むケースである。HotXLSでApplyAutoFilterを通じてAutoFilterを適用すると、列の条件を評価して一致しないすべてのデータ行を隠す。これはユーザーがフィルタのドロップダウンをクリックしたときにExcelが行うこととまったく同じである。v2.197.0より前は、これらの隠された行はユーザーには見えず、計算エンジンには完全に見えていたため、サーバー側の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; // hides the non-matching rows
Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
Book.Recalculate;
// The cell value now agrees with what Excel shows for the same filter,
// and VisibleRows tells you how many rows fed into it
Book.SaveAs('orders-filtered.xlsx');
finally
Book.Free;
end;
end;手動での非表示化も同じように動く。RowHidden[ARow] := Trueはフィルタが書き込むのと同じ状態だからだ。この等価性はExcelにおいて意図的なものであり、今ではHotXLSでも成り立つ。1つの帰結は、生成するワークブックに添えるどんな文書にも書き留めておく価値がある:コード109で計算された合計はビューに依存する数値であり、フィルタを解除した受け手はそれを変えてしまう。読み手がビューに何をしようとも変わらない固定値をレポートが示す必要があるなら、コード9が正しい選択であり、それは常にそうだった。フィルタ、検証、テーブルはデータ検証・AutoFilter・テーブルに関する記事でまとめて扱っている。行を隠すことはどの数式にも触れないため、それ自体では依存グラフを汚さない。これは、大きなワークブックの応答性を保つために汚れたサブグラフに対するインクリメンタル再計算に頼っているなら知っておく価値がある
AGGREGATEのオプションコードと、まだ残っている1つの限界
AGGREGATEは2つ目のポリシー引数を持つSUBTOTALであり、HotXLSはCalcAggregateFuncでこれを扱う。オプション引数は独立したスイッチをエンコードする:範囲内のネストしたSUBTOTALやAGGREGATEの呼び出しをスキップするかどうか、隠し行の値をスキップするかどうか、そしてエラー値を伝播させるのではなく抑制するかどうかである。HotXLSはオプションコード2、3、6、7に対して共有の隠し行ゲートをセットし、オプションコード4から7に対してエラー値を抑制する。その後、関数番号の引数はSUBTOTALとまったく同じように集計を選択し、分散、標準偏差、積をそれぞれのリデューサーへ振り分けることも含む。1つ、文書化された隙間が残っており、これは本番環境で発見されるよりもここで述べておくほうがいい:低いオプションコードに紐づくネストしたSUBTOTALを無視するという意味論は、HotXLSでは実装されていない。参照範囲の内側にネストしたSUBTOTALを検出するには、評価器の再帰状態にマークを付けて内側の集計が外側にそれを通知できるようにする必要があり、これは隠し行のゲートよりも大きな変更になる。実務上この露出は小さい。実際のワークブックはほとんど常に、他のSUBTOTAL数式が集計する範囲の外にSUBTOTAL数式を置くからだ。もしあなたのジェネレータが重なり合う集計範囲を実際に作るなら、低いオプションコードがそれを重複排除してくれることに頼ってはならない
それと一緒に出荷された引数個数のガード
バージョン2.197.0は同じディスパッチャの中にあった検証の隙間も1つ塞いだ。その設計上の理由は、スクラッチフィールドを動機づけたものと同じである:チェックは一度だけ書ける場所に置くこと。およそ280個の組み込み関数本体それぞれが、自分自身の引数個数をItem.ChildCountに対して検証していた。これは引数が多すぎるケースに対して一貫した境界を残していなかった。=SIN(1,2)のような呼び出しは、最初の引数だけを調べ、余分を無視し、Excelなら#VALUE!を返すところでもっともらしい数値を返す関数本体に到達していた。HotXLSはすでにすべての組み込み関数の宣言された引数個数をその関数レジストリに保持しており、THashFunc.ArgsCntとして公開していた。-1はSUM、IF、CONCATのような可変長引数の関数を示す。バージョン2.197.0はこれを新しいTXLSFormula.FuncArgsCntByPtgプロパティを通じて転送し、メインのディスパッチャであるGetValueItemFuncの先頭に1つのゲートを追加した
lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
lProvidedArgs := Item.ChildCount - 1; // Child[0] is the function node
if lProvidedArgs > lDeclaredArgs then
begin
Result := lxErrorValue; // =SIN(1,2) now yields #VALUE!
Exit;
end;
end;このガードは引数が多すぎる場合を拒否し、意図的に少なすぎる場合については何も言わない。末尾の任意引数を省略することは、VLOOKUP、SUBSTITUTE、その他長いリストの関数にとってExcelでは合法であるため、対称的なチェックは、誤った数式を捕まえるために正しい数式を壊してしまうことになる。未知の識別子は可変長引数として報告され、このゲートを完全にスキップする。これがユーザー定義関数をこの仕組みの影響から外している理由である。もし自分自身の関数を登録しているなら、数式エンジンとカスタム関数のガイドで説明した挙動には影響がない。少なすぎる場合を一元化するのはまた別の仕事だ。それら280個の本体それぞれが独自のエラーコードの意味論を持っており、想定するのではなく1つずつレビューしなければならないからだ
ここで説明した計算エンジン、2つのワークブックファサード、そしてそれに供給するAutoFilterと行の可視性のAPIは、HotXLS Delphiスプレッドシートコンポーネントの一部であり、DelphiとC++Builder向けに完全なソースコード付きで提供され、実行するマシンにExcelのインストールを必要としない