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

技術記事

HotXLSとDelphiで動的配列のスピル数式を扱う

HotXLS Delphi Component

HotXLSはExcel 365の動的配列スピル数式を、DelphiとC++Builderでネイティブに評価します。セルにXLOOKUPやFILTERを抱えたブックを渡せば、その数式エンジンが結果の集合を計算し、固定した出力の矩形へスピルさせます。値はExcelが出すものと同じです。HotXLS Delphi Excelコンポーネントはスピル数式を、計算を所有する1つのルートセルと、そこから読み戻す従属セルの塊としてモデル化します。コードを1行書く前に必要な全体像はこれで尽きています

このモデルが重要なのは、日々の仕事が抽象的ではないからです。顧客がExcel 365で作った.xlsxを送ってきて、そのシートは=FILTER(...)と=XLOOKUP(...)で埋まっており、あなたのサービスはExcelの入っていないマシンで、画面なしにまったく同じ数値を再現し、それからスピルした値を読むか、自分で新しいスピル領域を書き込まなければなりません。動的配列は計算の約束事を「1つの数式に1つのセル」から「1つの数式にセルの矩形」へ変えました。HotXLSのエンジンは、あらかじめ展開したグリッドで真似るのではなく、その約束事に従います

DelphiでXLOOKUPとFILTERをどう評価するか

HotXLSはすべての動的配列関数を、lxCalc.pasのCalcDynArrayFuncという1つの評価器の入り口へ振り分けるので、この一族はスピルのコード経路を共有します。対応する集合はXLOOKUPとFILTER（最初の2つ）に、XMATCH、SORT、UNIQUE、SEQUENCEが加わったものです。どれもスカラーではなく2次元のvariant配列を返します。SEQUENCE(3;2;1;1)は3行2列のグリッドを生み、SORTは任意のキー列で昇順または降順に行を並べ替え、UNIQUEは重複する行をたたんで最初の出現を残し、XMATCHはXLOOKUPと同じ完全一致、ワイルドカード、次に小さい値、次に大きい値の照合モードのもとで、値の1起点の位置を報告します。1回の呼び出しで1つの数値を返す統計分布の関数とは違い、これらの関数は形そのものを返します

こうした数式を既に抱えているブックを計算するには、開いてRecalculateを一度呼び、スピルが着地したセルを読みます。TXLSXWorkbook.Recalculateは依存関係のグラフを歩き、汚れた数式をトポロジカル順に評価するので、スピルのルートは一度だけ計算され、その要素はメンバーのセルへ直接書き込まれます

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  r, c: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('from-customer.xlsx');
    Book.Recalculate;                 // スピルも含めすべての数式を評価する
    Sheet := Book.Sheets[1];          // Sheetsは1起点
    // XLOOKUPやFILTERがスピルした塊、たとえばD2:F9を読み戻す
    for r := 2 to 9 do
      for c := 4 to 6 do
        Writeln(Sheet.Cells[r, c].Text);
  finally
    Book.Free;
  end;
end;

エンジンに同梱されていない関数がブックに必要なら、同じ評価器が独自のワークシート関数のフックを通じて自前のロジックを差し込ませてくれますし、独自の関数はvariant配列を返して構わないので、組み込みの関数とまったく同じようにスピルします

SetArrayFormulaでスピル範囲を固定する

HotXLSはスピルの大きさを推測しません。出力の矩形を名指しすれば、TXLSRange.SetArrayFormulaがそこへ数式を固定します。このメソッドは数式を一度コンパイルし、構文木を固定した範囲とともに左上のルートセルへ格納し、矩形の残りのセルにはルートを弱参照する軽量な数式を与えます。再計算のときルートは一度だけ評価され、その行列の要素が各メンバーのセルへ直接配られます。これは増分再計算の依存関係グラフでスピルのルートが1つのノードとして現れる仕組みでもあります。スピル領域を読み出すのではなくファイルへ書き戻すのが仕事なら、使うべきはこの経路です

DelphiにおけるHotXLSの動的配列スピルの図。SetArrayFormulaが=SEQUENCE(3;2;1;1)をA1:B3へ固定し、ルートセルがコンパイル済みの数式を所有し、メンバーのセルが1から6までの値を受け取る様子
HotXLSはSetArrayFormulaでスピルを固定します。ルートセルがコンパイル済みの木を格納し、メンバーのセルはそれを弱参照します
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('report.xlsx');
    Sheet := Book.Sheets[1];
    // 結果に合わせて矩形の大きさを決める：3行2列のグリッド
    if Sheet.Range['A1:B3'].SetArrayFormula('=SEQUENCE(3;2;1;1)') > 0 then
      Book.Recalculate;               // A1:B3を行方向に1から6で埋める
    Book.SaveAs('report-out.xlsx');
  finally
    Book.Free;
  end;
end;

明示的に固定することの帰結は、はっきり述べておく価値のある境界です。HotXLSは対話的なExcelのようにスピルを自動で伸縮させません。Excelは入力が変わるとスピル領域を広げたり縮めたりし、対象のセルが埋まっていれば#SPILL!を出します。HotXLSではSetArrayFormulaへ渡した矩形がそのまま得られる矩形なので、期待する結果に合わせて大きさを決めることになり、関数が固定した行数より多くの行を生んだ場合、余りには着地する場所がありません

HotXLSのDelphiにおけるスピルが固定した矩形をちょうど埋める様子と、結果がSetArrayFormulaへ渡した範囲より大きい場合に何が起きるかを示す判断の流れ図
Excelの自動で広がるスピルや#SPILL!エラーとは違い、HotXLSは固定した矩形をちょうど埋めます

演算子をまたぐ要素単位の配列ブロードキャスト

HotXLSは、演算のどちらかの側が配列であるとき、算術演算子を要素ごとに適用します。補助ルーチンのApplyArrayBinaryOpは1次元と2次元のvariant配列に対して+、-、*、/、^を覆います。スカラーの被演算子はすべての要素へ配られ、2つの配列は形が一致していなければ演算が拒まれ、ゼロ除算はクラッシュせずエラーとして伝播します。スカラーの算術経路には手が入っていないので、これが働くのは少なくとも一方の被演算子が本当に配列であるとき、たとえばスピルした範囲や行列関数の結果のときだけです。つまり列に対して固定した=D2:D13*1.1のような数式は各要素を順に掛け、=D2:D13*E2:E13は同じ形の2つの列を位置ごとに掛けます

Delphi向けHotXLSにおける要素単位の配列ブロードキャストの図。D2:D13のすべての要素へ適用されるスカラーの乗数と、位置ごとに掛け合わされる同じ形の2つの範囲
ApplyArrayBinaryOpはスカラーをすべての要素へ配り、同じ形の配列を位置ごとに掛け合わせます
// スカラーのブロードキャスト：固定範囲の各セルがD(n) * 1.1を受け取る
Sheet.Range['F2:F13'].SetArrayFormula('=D2:D13*1.1');
// 同じ形の2つの配列は要素ごとに掛け合わされる
Sheet.Range['G2:G13'].SetArrayFormula('=D2:D13*E2:E13');
Book.Recalculate;

@演算子は従来のCSE配列と何が違うのか

HotXLSは明示的な@を、現行のExcelにおける行を選ぶ暗黙の交差ではなく、参照の交差演算子として読みます。エンジンの中でA1:A3 @ B1:B3は2つの範囲が交わる1つのセルを返し、この演算子は^より強く%より弱く結合します。それに添える正直な注記が1つ。空白で区切る暗黙の交差の形はまだトークン化されていません。字句解析器が今も空白を読み飛ばすからで、交差が得られるのは明示的な@記号を通じてだけです

より深い区別は、従来のCSE配列数式と現代の動的配列のあいだにあり、HotXLSではどちらも同じ固定の機構を流れます。古典的な配列数式は、あらかじめ大きさを決めた選択範囲にCtrl+Shift+Enterで入力する{=...}の塊で、すべてのセルが1つのコンパイル済み数式を共有していました。動的配列は、結果が自分の形を決める単一の数式です。HotXLSはこれらをSetArrayFormulaのもとで統一します。数式が旧来の行列式であれ新しいSORTであれ、常に矩形を名指しし、ルートがコンパイル済みの木を所有し、メンバーがそれを参照します。決して得られないのは、領域が黙って再び広がるExcelの自動的な波及であり、この一線を頭に置いておけば2つの心の模型がにじまずに済みます

動的配列の対応が止まるところ

境目を知っておくと午後1回分のデバッグが浮きます。SORTはキーごとの方向を尊重し、既定は昇順、順序の引数が負なら降順です。比較の方向は自分のキー列をExcelと突き合わせて試す価値のある箇所です。比較が反転していても失敗せず、黙って逆順の行を返すからです。UNIQUEは個々の異なる行の最初の出現を残します。エンジンは生の重複数を信じるのではなく、それ以前の行を走査して確かめています。TABLE関数（What-If分析のデータテーブル）は認識されるのでファイルを往復しますが、評価結果はプレースホルダーです。本当の代入結果はExcelが既に格納したキャッシュ値であり、HotXLSはWhat-Ifのグリッドを回し直さないからです

名指しすべき最も鋭い限界はLETです。HotXLSはLETを登録し、その評価経路も持っていますが、パーサーは既に定義された名前でない裸の名前を拒むので、=LET(x;10;x)は評価器へ届く前の解析の段階で失敗します。パーサーが束縛名のスコープを理解するようになるまで、LETは非対応として扱ってください。移植性についてもう1つ小さな注記を。これらの例の数式文字列はエンジンのリスト区切り記号であるセミコロンを使っているので、数式のテキストを組み立てるときは自分のビルドが期待する区切り記号に合わせてください。ここで扱ったXLOOKUPとFILTERからSORT、UNIQUE、SEQUENCE、XMATCHまでの動的配列関数はHotXLS Delphi Excelコンポーネントに同梱されており、その数式リファレンスには関数の全カタログと、関数ごとに対応する引数のモードが載っています