HotXLSは、XLSXワークシートに行や列を挿入または削除した際、数式参照を自動的に調整します。エンジンの InsertRows、DeleteRows、InsertCols、および DeleteCols メソッドは、構造的な編集が行われた後も、生き残ったすべての数式を書き換えて、そのA1参照（相対参照、絶対参照、および範囲参照）が同じデータを指し続けるようにします。また、削除されたブロックへの参照は、Excelの動作と一致して #REF! （参照エラー）になります。
この機能が防ぐバグは、レポート生成において最も気付きにくいものの1つです。ジェネレーターが C2:C9 に日次データを書き込み、その下に =SUM(C2:C9) を記述し、後続のステップで最上部にヘッダー行を挿入したとします。もしエンジンがセルの値のみを移動させ、数式テキストをそのままにしていた場合、データは C3:C10 に移動しているにもかかわらず、SUM関数は依然として C2:C9 を読み取り続けます。その結果、最終日のデータが計算から漏れ、さらにヘッダーが二重にカウントされるといった問題が警告なしに発生します。例外は発生せず、ファイルも正常に開きますが、数値は単に誤っています。バージョン 2.160 より前は、HotXLS XLSXエンジンは構造的な編集時に数式を処理しませんでしたが、2.160以降、書き換えは自動的に行われ、特別なフラグの設定も不要です。
Excelで行を挿入した際、数式に何が起きるか
Excelのルールは、「参照はアドレスではなくデータに従う」というものです。行が挿入されると、挿入ポイント以下の行インデックスを持つすべての参照が、挿入された行数分だけ下に移動します。挿入ポイントより上に完全にある参照は影響を受けません。行の削除は、同じルールが逆方向に機能します。削除されたブロックより下の参照は上にスライドし、削除されたブロック自体への参照は、そのセルが存在しなくなったため #REF! に変わります。列ももう一方の軸でまったく同じように動作します。スプレッドシートライブラリが、Excelユーザーに渡されても問題のない出力を生成するためには、この仕組みを正確に再現する必要があります。なぜなら、ユーザーは意識することなく、これらの前提に基づいて数式を設計しているからです。
開発者を驚かせるのは、絶対参照（$ 付きの参照）も移動するという点です。$B$2 のような $ アンカーは、数式が他のセルにコピーまたはオートフィルされた際の動作を制御するものであり、構造的な編集時には機能しません。行2の上に行を挿入すると、Excelは $B$2 を $B$3 に書き換えます（ダラー記号は維持されます）。これは、数式が依存している値が物理的に行3に移動したためです。もし相対参照のみをシフトさせるエンジンであったなら、ユーザーが最も意図的に固定したはずの数式が破損してしまいます。HotXLSは両方の形式をシフトし、書き換えられたテキスト内でも $ マーカーを保持します。
HotXLSが数式参照を自動的にシフトする仕組み
TXLSXWorksheet の4つの構造的編集メソッドはすべて、単一のジオメトリエンジン ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta) に委譲されます。InsertRows(BeforeRow, Count) は正の行デルタでこれを呼び出し、DeleteRows(StartRow, Count) は負のデルタで呼び出します。列メソッドも列軸で同様に動作します。このルーチンは、まずセル自体の位置を再配置し（削除されたブロック内に入ったセルを破棄します）、次に生き残ったすべての数式セルを走査して、そのテキストを XlsxAdjustFormulaRowColRefs に通します。これはA1スタイルの参照を検出し、その行および列コンポーネントをシフト量に応じて書き換えるスキャナーです。同じパスで、結合範囲、ハイパーリンク、コメント、画像、グラフ、条件付き書式、データ入力規則、およびテーブル範囲も再配置されるため、シート全体が1つのユニットとして移動します。
// Layout before the edit:
// C2..C9 daily figures
// C10 =SUM(C2:C9)
Sheet.InsertRows(2, 1); // one blank row before row 2
// Layout after the call:
// C3..C10 daily figures
// C11 =SUM(C3:C10) -- the range moved with the dataスキャナーの仕様についていくつか知っておくべき詳細があります。XlsxAdjustFormulaRowColRefs は、4つのアンカー形式（A1、$A1、A$1、$A$1）すべての単一セル参照、および A1:B3 のような2箇所の範囲参照を認識し、それぞれの終点を独立して調整します。以前の編集によるエラーマーカーである # で始まる数式テキストは、再スキャンされることなくスキップされます。また、InsertCols は、新しく挿入された列が左隣の列幅を継承するという、Excelの「シートの列の挿入」コマンドと同じ実用的な仕様を備えています。
削除された参照が#REF!になる条件
単一の行または列インデックスのシフトルールには、3つの結果があります。編集ポイントより前のインデックスは変更されません。編集ポイント以降のインデックスはデルタ分だけ移動します。そして削除において、削除されたブロック内に入ったインデックスには、意味のある新しい値が存在しない（セルが消滅した）ため、スキャナーは参照全体を #REF! に書き換えます。範囲参照の場合、両方のエンドポイントに対して同じルールが適用され、どちらか一方のエンドポイントでも削除されたブロック内に落ちた場合は、一部だけを有効なまま残すのではなく、範囲全体が #REF! に書き換えられます。
// A12 holds =A4+A6+A10
Sheet.DeleteRows(5, 3); // delete rows 5..7
// The formula, now in A9, reads =A4+#REF!+A7
// A4 : above the deleted block, unchanged
// A6 : inside rows 5..7, gone -> #REF!
// A10 : below the block, slides up -> A7無言で別のセルを再ターゲットするのではなく、エラーであることが明確な #REF! を出力するのが正しいトレードオフであり、Excelもそのように動作します。もし入力データが削除された後に、隣のセルを勝手に指すようになった数式があれば、それらしい数値を返し続けてしまいます。#REF! は依存する他の数式にも伝播し、最初の動作検証ですぐに表面化します。列軸の削除でも同じ変換が行われます。
// E1 holds =B1*$C$1
Sheet.DeleteCols(3, 1); // remove column C
// The formula, now in D1, reads =B1*#REF!
// The absolute anchor did not protect $C$1 -- the cell itself is gone書き換えられない参照形式
スキャナーは、明示的な列アルファベットと行番号の形状を持つ同一シート内のA1参照を対象とします。それ以外の対象外となるものについて明確にしておきます。A:A のような列全体の参照や 1:1 のような行全体の参照は、2つのコンポーネントの片方が欠けているため、スキャナーは書き換えずにそのまま残します。構造化テーブル参照（Table1[Amount]）も同様に処理されません。また、この書き換えはA1表記のみを対象としています。もしコードがR1C1スタイルの数式を構築している場合は、構造的編集の前にA1に変換してください。これについては、姉妹記事のDelphiでのR1C1数式表記に関する記事を参照してください。
シートをまたぐ参照やワークブックレベルの構造は、セルテキストスキャナーではなく、個別のパスによって処理されます。編集されたシートを調整した後、ShiftSheetGeometry は、その編集されたシートを参照している他のシートの数式、グラフの系列範囲、内部ハイパーリンクのターゲット、および定義された名前にその変更を伝播させます。定義された名前は、シートのライフサイクルレベルでも保護されます。バージョン 2.150 以降、ワークシートを削除すると、定義された名前の数式内にある SheetN! 修飾子がすべて #REF! に書き換えられ、シート名を変更すると修飾子も新しい名前に書き換えられるため、存在しないシートを指したままになるのを防ぎます。名前とシートをまたぐ数式がどのように連携するかについては、定義された名前とシートをまたぐ数式に関する記事で説明されています。
シフト後の再計算
参照の調整は数式のテキストを書き換えるものであり、結果を再計算するものではありません。構造的編集の後、数式に保存されているキャッシュ値は古い状態のままになっているため、信頼できる手順は、「すべての挿入と削除を完了した後に再計算を1回実行し、その後保存する」というものです。再計算の前にシフトを実行しておくことで、依存関係の正確さも維持されます。書き換えられたすべての参照がその真の先行ノードを指すため、インクリメンタル再計算エンジンと依存関係グラフが必要とする、影響を受ける最小限のセルセットを正しく再計算できます。もし書き換えられた数式に #REF! が含まれるようになった場合、再計算によって古い値の代わりに即座にエラー値が表面化します。
数式参照の調整は、DelphiおよびC++Builder向けのHotXLS Delphi Excel ComponentのXLSXエンジンに標準動作として搭載されています。製品ページには、ここで示した挿入および削除メソッドを含む、ワークシート編集APIの完全なリファレンスが掲載されています。