LlosLab Software Development BlogEngineering notes for PDF, Delphi, and document software developers.

技術記事

HotXLSを使用したDelphiでの動的配列スピル数式

HotXLS Component

HotXLSは、DelphiおよびC++BuilderでExcel 365の動的配列スピル数式をネイティブに評価します。XLOOKUPやFILTERを含むセルを持つワークブックを渡すと、数式エンジンが結果セットを計算し、配置された出力矩形領域にスピル（流し込み）します。これはExcelが生成する値と全く同じです。HotXLS Delphi Excelコンポーネントは、スピル数式を、計算を担当する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次元のバリアント配列を返します。たとえば、SEQUENCE(3;2;1;1)は3行2列のグリッドを生成し、SORTは任意のキー列を基準に昇順または降順で行を並べ替え、UNIQUEは重複する行を折りたたんで最初のデータのみを残し、XMATCHはXLOOKUPと同じ完全一致、ワイルドカード、次に小さい値、次に大きい値の一致モードで値の1ベースの位置を返します。1回の呼び出しで1つの数値を返すスカラーの統計分布関数とは異なり、これらの関数は形状データ全体を返します

これらの数式がすでに含まれているワークブックを計算するには、ワークブックを開き、Recalculateを1回呼び出し、スピルが出力されたセルを読み取ります。TXLSXWorkbook.Recalculateは依存関係グラフを走査し、トポロジカル順序で各ダーティ数式を評価するため、スピルのルートは1回だけ計算され、その要素がメンバーセルに直接書き込まれます

ワークブックがエンジンに搭載されていない関数を必要とする場合、同じ評価エンジンを使用して、カスタムワークシート関数のフックを介して独自のロジックをプラグインできます。カスタム関数はバリアント配列を自由に返せるため、組み込み関数と全く同じようにスピルさせることができます

SetArrayFormulaによるスピル範囲の配置

HotXLSはスピルがどのくらいの大きさになるかを推測しません。開発者が出力矩形を指定し、TXLSRange.SetArrayFormulaが数式をその領域に配置します。このメソッドは数式を1回コンパイルし、配置された範囲とともに左上のルートセルに構文ツリーを保存し、矩形内の他のすべてのセルにルートを弱参照する軽量な数式を割り当てます。再計算時、ルートが1回評価され、そのマトリックス要素が各メンバーセルに直接ブロードキャストされます。これは、スピルのルートが増分再計算依存関係グラフで1つのノードとして表示される仕組みでもあります。これは、ファイルを読み取るのではなく、スピル領域をファイルに書き戻すタスクを実行する際に使用する手法です

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('report.xlsx');
    Sheet := Book.Sheets[1];
    // Size the rectangle to the result: a 3-row by 2-column grid.
    if Sheet.Range['A1:B3'].SetArrayFormula('=SEQUENCE(3;2;1;1)') > 0 then
      Book.Recalculate;               // fill A1:B3 with 1..6 down the rows
    Book.SaveAs('report-out.xlsx');
  finally
    Book.Free;
  end;
end;

明示的に配置を行うことによる影響として、明確にしておくべき制限があります。HotXLSは、インタラクティブなExcelのようにスピル領域を自動的にリサイズしません。Excelは入力の変化に応じてスピル領域を拡大・縮小し、対象セルが占有されている場合は#SPILL!を発生させます。HotXLSでは、SetArrayFormulaに渡す矩形領域がそのまま出力領域となるため、期待する結果に合わせてサイズを指定する必要があります。もし関数が配置した領域よりも多くの行を生成した場合、余剰分は出力されません

演算子による要素ごとの配列ブロードキャスト

HotXLSは、演算のいずれかの側が配列である場合、算術演算子を要素ごとに適用します。ヘルパー関数ApplyArrayBinaryOpは、1次元および2次元のバリアント配列に対して+、-、*、/、および^をカバーします。スカラーのオペランドはすべての要素にブロードキャストされ、2つの配列は形状が一致する必要があり、一致しない場合は演算が拒否されます。ゼロによる除算はクラッシュするのではなくエラーとして伝播します。スカラー算術パスは変更されないため、スピル範囲や行列関数の結果など、少なくとも1つのオペランドが真に配列である場合にのみ有効になります。これは、列全体に配置された=D2:D13*1.1のような数式が各要素を順番に乗算し、=D2:D13*E2:E13が同じ形状の2つの列の位置ごとに乗算することを意味します

// Scalar broadcast: every cell of the anchor gets D(n) * 1.1.
Sheet.Range['F2:F13'].SetArrayFormula('=D2:D13*1.1');
// Two arrays of identical shape multiply element by element.
Sheet.Range['G2:G13'].SetArrayFormula('=D2:D13*E2:E13');
Book.Recalculate;

@演算子と従来のCSE配列の動作の違い

HotXLSは、明示的な@を、Excelのような行選択の暗黙的な共通部分（Implicit Intersection）としてではなく、参照の共通部分（Reference Intersection）演算子として読み取ります。エンジン内では、A1:A3 @ B1:B3は2つの範囲が交差する単一のセルを返し、この演算子は^よりも強く結合し、%よりも弱く結合します。注意点として、レキサー（字句解析器）が現在空白をスキップするため、スペースで区切られた暗黙的な共通部分の形式はまだトークン化されていません。そのため、共通部分は明示的な@記号を介してのみ機能します

より本質的な違いは、従来のCSE配列数式と最新の動的配列の違いであり、HotXLSではどちらも同じ配置メカニズムを介して処理されます。従来の配列数式は、あらかじめサイズ調整された選択範囲に対してCtrl+Shift+Enterで入力される{=...}ブロックであり、すべてのセルが1つのコンパイルされた数式を共有していました。動的配列は、結果によって独自の形状が決定される単一の数式です。HotXLSはこれらをSetArrayFormulaの下で統合します。数式が古い形式の行列式であるか、新しいSORTであるかに関係なく、常に矩形領域を指定し、ルートがコンパイルされたツリーを所有し、メンバーがそれを参照します。Excelのように領域が暗黙的に再成長する自動的な伝播（Ripple）は行われません。この明確な違いを意識することで、2つのメンタルモデルの混同を防ぐことができます

動的配列サポートの限界

制限事項を把握しておくことで、デバッグの時間を大幅に削減できます。SORTはキーごとの方向（デフォルトは昇順、並べ替え順引数が負の場合は降順）に従います。比較方向は、逆順の比較が失敗するのではなく、反対の順序で行を暗黙的に返すため、Excelと突き合わせて独自のキー列をテストする価値がある部分です。UNIQUEは個別の行の最初のデータのみを残します。エンジンは単純な重複カウントを信頼するのではなく、前の行をスキャンすることでこれを確認します。TABLE関数（What-If分析データテーブル）は認識されるため、ファイルを介してラウンドトリップ可能ですが、実際の置換結果はExcelがすでに保存したキャッシュ値であり、HotXLSはWhat-Ifグリッドを再実行しないため、プレースホルダーとして評価されます

最も顕著な制限はLETです。HotXLSはLETを登録し、その評価パスを持っていますが、パーサーはすでに定義されている名前ではない単一名の変数を拒否するため、=LET(x;10;x)は評価エンジンに到達する前のパース段階で失敗します。パーサーがバインドされた名前のスコープを認識できるようになるまでは、LETは非サポートとして扱ってください。また、移植性に関する細かい注意点として、これらの例の数式文字列はエンジンのリスト区切り文字であるセミコロンを使用しているため、数式テキストを組み立てる際は、ビルド環境が想定している区切り文字と一致させてください。ここで説明したXLOOKUP、FILTER、SORT、UNIQUE、SEQUENCE、XMATCHなどの動的配列関数は、Delphi用のHotXLS Delphi Excel Componentに含まれており、その数式リファレンスには全関数リストと各関数がサポートする引数モードが記載されています