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

Technical Article

DelphiでExcelの数式評価をステップバイステップでトレースする

HotXLS Component

Excelは、見えやすい場所に小さなデバッガーを隠し持っています。セルを選択し、「数式」を開いて「数式の検証」をクリックすると、ダイアログに1つの部分式（サブエクスプレッション）に下線が引かれた数式が表示されます。「検証」を押すと、その部分式がその値に折りたたまれ、次に別の部分式に下線が引かれます。こうして、長い式が1回の簡約ごとに1つの数値に縮小していくのを見ることができます。これは、ネストされたIFのどの分岐が実際に実行されたか、またはどの参照が誤った合計を供給したかを見つけるための最も速い方法です。HotXLSはTXLSFormulaTracerを通じてその正確な動作を再現するため、DelphiやC++Builderプログラムは、ワークブックの監査、生成された数式のデバッグ、あるいは結果がなぜそのようになったかを誰かに教えるために、同じステップリストをレンダリングできます。記録された各ステップには、部分式のテキストと、それが簡約される値が含まれます

簡約エンジンが式をどのように辿るか

トレーサーは計算エンジンの内部には干渉しません。数式をトークン化し、再帰下降パーサーで解析した後、ツリーを深さ優先で、最も内側の評価可能な部分式から順に簡約します。ノードが値に簡約されると、その値はリテラルとして周囲の式に代入され直し、エンジンは実際の計算機により単純になった式を再計算するように要求します。すべてのステップはプライベートなショートカットではなく、ワークシートのパブリックなCalculateメソッドを通じて評価されるため、各ステップはセルの完全な再計算が生成するものと正確に一致します。パーサーは設計上非侵襲的であり、これによってワークシートの状態を乱すことなく、任意のワークシートに対して実行できるのです

パーサーは演算子の優先順位の段階に従い、優先順位の帯域ごとに1つの再帰レベルを持ちます。最も結合が弱いものから強いものへ、帯域は次のようになります。レベル0：比較（=、<>、<、>、<=、>=）、レベル1：文字列の連結（&）、レベル2：加算と減算、レベル3：乗算と除算、レベル4：べき乗、そして最後に、それより下の単項プラスとマイナスです。各レベルはそのオペランドのために1つ上のレベルを解析するため、より高い帯域がより強く結合します。これはExcelが適用するのと同じ優先順位であり、A1*B1+A2*B1が合計の前に2つの積を簡約する理由はここにあります。乗算はレベル3、加算はレベル2に位置するため、乗算はツリーのより深くにあり、最初に簡約されるのです

数式のトレースとステップの巡回

使用方法は、同梱されているデモのDemo/Delphi/FormulaTrace/FormulaTrace.dprを反映しています。ワークシートを構築（または既存のワークブックを開く）し、シートに対してトレーサーを構築し、Traceを呼び出して、返された配列を反復処理します。各TXLSFormulaStepは、インデント用のDepth、元の部分式用のSource、オペランドがすでに代入された部分式用のExpression、およびステップの結果用のValueを公開します

uses
  SysUtils, Variants, lxHandle, lxHandleX, lxFormulaTrace;

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Tracer: TXLSFormulaTracer;
  Steps: TXLSFormulaStepArray;
  Final: Variant;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Order');
    Sheet.Cells[1, 1].Value := 10;    // A1 units
    Sheet.Cells[1, 2].Value := 25;    // B1 unit price
    Sheet.Cells[1, 3].Value := 0.08;  // C1 tax rate

    Tracer := TXLSFormulaTracer.Create(Sheet);
    try
      Final := Tracer.Trace('A1*B1*(1+C1)', Steps);
      for I := 0 to High(Steps) do
        Writeln(StringOfChar(' ', Steps[I].Depth * 2),
                Steps[I].Source, ' -> ', Steps[I].Expression,
                ' = ', VarToStr(Steps[I].Value));
      Writeln('result = ', VarToStr(Final));
    finally
      Tracer.Free;
    end;
  finally
    Book.Free;
  end;
end;

セル参照が最初に解決されてそれ自身のステップとして表示され、次に積が簡約され、次に括弧で囲まれた税の係数が計算され、最後の乗算で締めくくられます。Depthフィールドを使用するとインデントが可能になり、Excelが外側の項の前に最も内側の項に下線を引くのとまったく同じように、最も内側の簡約が視覚的に最も深い位置に配置されます

ロケールに依存しないリテラルの罠

このスキーム全体で最も危険な詳細は、英語圏のマシンでは見えませんが、ドイツ語圏のマシンでは大きく破綻します。計算された数値を数式のテキストに代入し直す際、文字列として記述した上で計算エンジンによって再解析される必要がありますが、エンジンは.を小数点として扱います。もし代入にシステムロケールが使用された場合、ドイツ語のTFormatSettingsは税の係数として1,08と書き込み、カンマは引数の区切り文字として読み取られ、A1*B1*1,08の再計算は誤った形状で解析されるか、完全に失敗するでしょう

トレーサーは、構築時に固定するプライベートなTFormatSettingsを通じてすべての数値リテラルをフォーマットすることでこれを回避します。これにはDecimalSeparatorが.に強制され、ThousandSeparatorが#0に設定されているため、グループ化文字が出力されることはありません。これにより、FloatToStrは、操作者の地域設定に関係なく、エンジンが常に読み戻せるリテラルを生成します

// Conceptually what the tracer pins once, at construction
FFloatFmt := FormatSettings;
FFloatFmt.DecimalSeparator := '.';
FFloatFmt.ThousandSeparator := #0;
// every reduced number is written with: FloatToStr(Double(V), FFloatFmt)

これは、作者自身のテストでは決して現れず、別のロケールの顧客が同じコードを実行したときにのみ表面化する類のバグであるため、はっきりと述べておく価値があります。数式のテキストを介して値をラウンドトリップさせることはシリアライゼーションの問題であり、シリアライゼーションはロケールに依存しないものでなければなりません

ブール値は1と0に簡約される

関連する代入の決定として、論理値に関するものがあります。部分式がブール値として評価されると、トレーサーはそれをTRUEやFALSEとしてではなく、1または0として書き戻します。その理由は、簡約されたリテラルは、それを取り巻く文脈がどのようなものであってもきれいに再解析されなければならず、算術演算がその厳しいケースとなるからです。もしA1>A2のような比較がテキストのTRUEに簡約され、そのテキストがTRUE*B1の中に配置された場合、再計算はエンジンが乗算における素のブールキーワードを受け入れるかどうかに依存することになります。1を代入すればこの問題は完全に回避できます。なぜなら1*B1はどのような算術位置においても曖昧さがないからです。また、数値を期待された瞬間にTRUEが1として、FALSEが0として振る舞うという、Excel自身の強制的な型変換（コウーション）とも一致します

関数呼び出しはアトミックに簡約される

素朴なステップエンジンであれば、関数の引数を最初に簡約し、次に呼び出しを簡約するでしょう。それはExcelにとっては間違いであり、トレーサーは意図的にそれを行いません。関数呼び出しは、元のテキストから全体として1つのステップで評価されます。その理由は短絡（ショートサーキット）セマンティクスです。IF、CHOOSE、およびIFERRORは選択した分岐のみを評価するため、最初に引数を簡約すると、Excelが触れることのない分岐を計算するようにエンジンに強制することになります。典型的な被害は、IF(B1=0,0,A1/B1)のようなゼロ除算のガードです。もしトレーサーがIFを評価する前にA1/B1を簡約した場合、ガードが不発に終わり、まさにそれを防ぐために存在するエラーを発生させてしまいます。呼び出し全体をアトミックに評価することで、トレーサーはそのようなガードを機能させる遅延評価を保持します

// IF is one atomic step; only the selected branch is evaluated
Final := Tracer.Trace('IF(A1>A2,A1*B1,A2*B1)', Steps);
// A1>A2 is true, so the step records A1*B1 as the chosen result;
// A2*B1 is never computed, exactly as Excel would do it.

トレードオフとして、関数呼び出しの内部を個別のステップとして見ることはできませんが、それが正しい動作です。Excelが決して実行しない引数の簡約を表示することは、呼び出しを実際の単一の評価単位として扱うことよりも、さらに誤解を招くトレースになるでしょう

引数の区切り文字と完全な範囲

再計算を正確に保つための正規化がさらに2つあります。計算エンジンのコンパイラは関数の引数の区切り文字として;を想定しているため、トレーサーが解析されたツリーから関数呼び出しを再構築する際、ユーザーが最初に,を入力していたとしても、引数を;で結合します。SUM(A1,A2,A3)と記述された数式はSUM(A1;A2;A3)として再計算され、エンジンはこれを受け入れます。この再構築を必要にするのは値の代入であり、再構築を解析可能にするのは区切り文字を正しく設定することです

範囲参照はもう1つのケースです。A1:A3のような範囲はスカラーではなく、それを消費する関数が範囲の引数を期待しているため、3つの個別の値に分割してはなりません。トレーサーは範囲を元のテキストのまま完全に保持し、囲んでいる関数全体を簡約させます。SUM(A1:A3)*B1において、範囲は完全なままであり、SUM(A1:A3)が1つのアトミックなステップで1つの数値に簡約され、その後初めて外側の乗算が実行されます。これはExcelが、範囲オペランドとそれが最終的に寄与するスカラーとの間に引くのと同じ境界です

// The range A1:A3 is never split; SUM is one atomic reduction,
// then the product with B1 reduces on top of it.
Final := Tracer.Trace('SUM(A1:A3)*B1', Steps);
for I := 0 to High(Steps) do
  Writeln(Steps[I].Source, ' = ', VarToStr(Steps[I].Value));

まとめると、これらのルールはステップリストをExcelの「数式の検証」コマンドの近似ではなく、忠実な鏡にします。簡約はExcelが実行する順序で行われ、代入されたリテラルはどのロケールでも生き残り、ブール値はExcelが強制変換するのと同じように変換され、遅延関数は遅延したままです。独自の関数を使用してエンジンをさらに推し進めたい場合は、数式エンジンとカスタム関数に関する記事でその登録方法を説明しています。また、より重い数値処理については、Delphiでの統計分布関数に関する記事で、トレーサーが評価する組み込みライブラリをカバーしています。これらはすべて、このブログの他の場所で取り上げている読み取り、書き込み、フォーマット、および計算のAPIとともに、DelphiおよびC++Builder向けのHotXLS spreadsheet componentの一部として提供されています