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

技術記事

DelphiでのHotXLSを使用したR1C1数式表記

HotXLS Component

HotXLSは、TXLSCalculator.GetCompiledFormulaR1C1 を介して、R1C1参照表記で記述された数式をコンパイルします。このメソッドは、R2C3 （絶対参照）、R[-1]C[2] （相対オフセット）、および RC （現在のセル）などの形式を受け入れ、数式が配置されるセルの行と列に応じてA1表記に変換し、通常のA1数式を処理するのと同じコンパイラに結果を渡します。何百行にもわたって同じ数式を生成するDelphiおよびC++Builderのコードにおいて、このメソッドは文字列結合に伴うバグを完全に排除します。

このバグのクラスは、プログラムから列に値を入力したことがある人なら誰でも馴染みがあるものです。行をループ処理し、各行に対して Format('D%d*E%d', [Row, Row]) を使用してA1数式文字列を構築します。反復のたびに行番号がテキストにスプライスされ、行番号だけが変化します。オフセットを一度でも誤る、0から始まるループカウンターと1から始まるA1行番号を混同する、あるいはヘッダー行によってデータブロックがシフトすると、列内のすべての数式が1行ずれてしまいます。例外は発生せず、数値は単に誤っています。実際に意図した数式「左側にある2つのセルを掛け合わせる」には行番号は一切登場しません。R1C1表記を使用すれば、まさにそのように記述できます。

R1C1表記とは何か、いつ使用すべきか

R1C1表記は、列のアルファベットと行番号の代わりに、行番号と列番号でセルを指定し、相対参照を数式セルからの明示的なオフセットとしてマークします。R2C3 は行2、列3の絶対セルであり、A1では $C$2 と記述します。R[-1]C[2] は、数式が配置されているセルから1行上、2列右のセルを指します。RC は数式セル自体を指します。ブラケット（大括弧）付きのオフセットがポイントです。R1C1における相対参照は、どのセルに配置されても同じ記述になりますが、A1表記ではその同じ参照のレンダリング結果が行ごとに変化します。

この表記法が本領を発揮するのは、データ領域のすべての行に同じ相対数式を配置するテンプレート生成という、非常によくあるシナリオです。A1では、行ごと数式テキストをレンダリングし直す必要があります。R1C1では、テキストは定数です。これはスプレッドシートのファイルフォーマットが内部で考えている方法にも近いです。共有数式レコードは、相対参照をホストセルからの行・列オフセットとして保存するため、行ごとのA1文字列は、コードがパーサーのために合成しているに過ぎず、パーサーはそれを再びオフセットに分解しています。R1C1はこの往復処理をスキップします。人間が作成するインタラクティブな数式においてはA1が自然な選択肢であるため、HotXLSでは常にA1がデフォルトのままになっています。

HotXLSがR1C1数式をコンパイルする仕組み

HotXLSはv2.175.0でこの機能を2つのレベルで公開しました。多くのコードが呼び出すのは TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula です。これは、通常のA1数式用の GetCompiledFormula と同様に評価可能なコンパイル済み数式を返しますが、数式が属するセルの0から始まる行と列を指定する2つのパラメータが追加されています。その下のレイヤーでは、TXLSFormula.GetCompiledR1C1 が生の構文木を生成し、スタンドアロン関数 R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String が実際の表記変換を実行します。パイプラインは意図的にシンプルです。ホストセルの座標を使用してR1C1テキストを同等のA1テキストに変換し、そのA1テキストを既存のエンジン（定義された名前とシートをまたぐ参照を解決し、カスタムワークシート関数をディスパッチするのと同じエンジン）でコンパイルします。

変換はコンパイルの前に実行されるため、下流のすべての処理は、A1数式を自分で記述した場合とまったく同じように動作します。R1C1ToA1('R2C3', 4, 3) は、両方の座標が絶対参照なので、ホストセルに関係なく '$C$2' を返します。R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3) は、CurRow = 4 および CurCol = 3 （D5セルに相当、0から始まるため）で実行された場合、'SUM(D2:D4)' を返します。オフセットが行5、列Dに対して解決され、プレーンな相対A1参照として出力されます。範囲参照に特別な処理は不要で、コロン（:）はそのまま通過し、各エンドポイントは独立して変換されます。

// The formula lives in D5: CurRow = 4, CurCol = 3 (both 0-based)
S := R1C1ToA1('R2C3', 4, 3);
// S = '$C$2'  (absolute row and column)

S := R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3);
// S = 'SUM(D2:D4)'  (offsets resolved against D5)

S := R1C1ToA1('ROUND(R[-1]C[0], 2)', 4, 3);
// S = 'ROUND(D4, 2)'  (the R in ROUND is left alone)

1つの相対数式で列全体に入力する

効果はループ処理で現れます。反復のたびに数式テキストをレンダリングし直すA1バージョンと、数式は定数のままでホスト座標のみが移動するR1C1バージョンを比較してください。どちらも TXLSCalculator を介してコンパイルされ、GetValue で評価されます。計算機は構築時にセルプロバイダーのコールバックを受け取るため、エバリュエーターはデータソースからセル値を取得できます。

// A1 style: a different formula string for every row
for Row := 1 to 500 do
begin
  FormulaText := Format('D%d*E%d', [Row + 1, Row + 1]);  // 1-based A1 rows
  Compiled := Calc.GetCompiledFormula(FormulaText, 0);
  // ... evaluate, store, free ...
end;
const
  AmountFormula = 'RC[-2]*RC[-1]';  // two cells to the left, same row
var
  Calc: TXLSCalculator;
  Compiled: TXLSCompiledFormula;
  Value: Variant;
  Row: Integer;
begin
  Calc := TXLSCalculator.Create(nil, Provider.GetValue);
  try
    for Row := 1 to 500 do
    begin
      Compiled := Calc.GetCompiledFormulaR1C1(AmountFormula, 0, Row, 5);
      try
        if Calc.GetValue(0, Compiled, Row, 5, Value, 1) = lxOk then
          StoreResult(Row, 5, Value);
      finally
        Compiled.Free;
      end;
    end;
  finally
    Calc.Free;
  end;
end;

R1C1のループでは、数式テキストに行の演算が一切含まれていません。'RC[-2]*RC[-1]' は、行2でも行500でも同様に「同じ行の2列左、掛ける同じ行の1列左」を意味し、後でデータブロックがヘッダー行によって下に移動しても、数式定数は変更されず、ループの境界のみが変更されます。A1バージョンには + 1 の調整を誤る可能性のある箇所が2つありますが、R1C1バージョンにはゼロです。

コンバーターが受け入れるR1C1形式

R1C1ToA1 コンバーターは、v2.175.0でドキュメント化された形式を認識します。これには、任意の方向への相対オフセットを示す R[n]C[m]、絶対行・列を示す RnCm、負のオフセットを持つ R[-n]C[m]、数式セル自体を示すプレーンな RC、および R1C1:R3C3 や R[-1]C:R[1]C などの範囲参照が含まれます。行と列の部分は独立して解析されるため、相対行と絶対列を組み合わせた R[1]C3 のような混合形式も動作します。また、文字は大文字小文字を区別しないため、r[-1]c[2] も大文字の形式と同様にコンパイルされます。ブラケット付きの部分はアンカーなし（相対参照）のA1座標になり、数値のみの部分は $ アンカー付きの絶対参照になります。

興味深いのは、RとCが非常に一般的な文字であるにもかかわらず、コンバーターが数式内の他の文字を破壊するのをどのように防いでいるかという点です。その識別ルールはトークンに基づいています。R は、前に他の文字がない場合にのみ参照の開始候補とみなされ、さらにその候補が完全に解析（オプションの行部分、必須の C、オプションの列部分）できる必要があります。解析できない場合はテキストは変更されずに残ります。そのため、ROUND(R[-1]C[0], 2) では内部の参照のみが変換されます。ROUND の R の後ろは数値やブラケット、あるいは C ではなく O なので、解析は失敗し、関数名はそのまま通過します。同じロジックが ROW() も保護し、C で始まる関数名は、R で始まる参照の候補にすらなりません。v2.175.0のリリースノートには、文字列リテラルや識別子もスキップされると記載されていますが、それでも数式内の引用符で囲まれたリテラルにR1C1参照とまったく同じ形状のテキストが含まれている場合は、本番環境で使用する前に一度コンパイル出力を確認する価値があります。

知っておくべき座標基準とアンカーの詳細

このAPIの内部では2つの仕様が交差しており、これらを混同しないようにすることで罠を避けることができます。GetCompiledFormulaR1C1 の CurRow および CurCol パラメータは、計算機APIに従って0から始まりますが、表記自体の内部の数値は、Excelの表示と一致して1から始まります。すなわち、R2C3 は行2、列3であり、$D$3 ではなく $C$2 を指します。ループカウンターがすでに0から始まっている場合は、そのまま CurRow として渡します。+ 1 の処理はコードではなくコンバーターの内部で行われます。

コンパイルされた数式が、それがコンパイルされたセルよりも長生きする場合（再利用される場合）、もう1つの詳細が重要になります。行または列の部分を省略した場合（RC[-1] や R[2]C などの形式）、省略された座標はホストセルに解決され、変換後のA1テキストでは絶対参照の $ アンカー付き座標として出力されます。評価時にはどちらでも値は同じなので、これは表面上は見えません。しかし、相対参照か絶対参照のアンカーであるかは、後で行や列が挿入・削除された際、数式参照がどのようにシフトするかに影響します（姉妹記事の数式参照の調整に関する記事を参照）。構造的編集を行っても座標を相対参照のまま維持したい場合は、省略形 RC[-1] ではなく、オフセットを明示的に R[0]C[-1] と記述してください。これにより、コンバーターはアンカーなしのA1参照を出力します。

R1C1コンパイルは、A1コンパイラ、再計算グラフ、および上記の評価APIと並んで、HotXLS Delphi Excel Componentの数式エンジンの一部として提供されています。もしコードが列の下に向かってループで数式をビルドしてスプレッドシートを構築しているなら、それらのループを文字列スライスされたA1から単一のR1C1定数に移行することは、最も低コストで信頼性を向上できるアップグレードの1つです。