HotXLS は lxStandard ユニットの XLSTrendlineEquationLabel を通じて、Delphi で Excel の近似曲線の数式ラベルと決定係数ラベルを再現します。この関数は、線形、指数、対数、多項式（次数 2〜6）、べき乗、移動平均の近似曲線に対して所有系列の点をフィットし、DataLabel.Text に Excel が報告するのと同じテキストをコードポイント単位で返します。フィットが不定のときは False を返します
これに関数が必要な理由は、ラベルがファイルの中にないからです。XLSX チャートは c:trendline 要素に c:trendlineType、c:dispEq と c:dispRSqr のスイッチ、位置と書式のための任意の c:trendlineLbl を保存しますが、ECMA-376 DrawingML Charts はラベルに固有のテキストを与えません。Excel はチャートを描くたびに y = 1.96x + 0.14 を再計算します。Excel を起動せずにそのテキストを表示、エクスポート、検索、比較したい Delphi コードは、これも再計算しなければならず、しかも Excel のやり方で、マイナス記号の周りの空白に至るまで再計算しなければなりません
回帰係数を自分で書式化すればよいのではないか
手書きの書式化係数が Excel ラベルと一致することはほとんどありません。ラベルは見逃しやすく、照合しやすい小さな慣習をいくつも踏むからです。HotXLS は散布図の DataLabel.Text をライブの Excel COM で読み戻してそれらの慣習を凍結し、フォーマッタを直観に対してではなくそのオラクルに対して書きました
実務で効いてくる慣習は次のとおりです
- 指数はラベルテキストでは素の文字です。多項式ラベルは
x²ではなくx2と読み、べき乗ラベルはx0.9732と読みます。上付きの見た目はチャートのレンダリング属性であって、文字列の一部ではありません - 唯一の非 ASCII 文字は
R² =接頭辞の U+00B2 上付き 2 です - 正確に 1 か -1 の係数は落とされます。Excel は
y = 1x + 3ではなくy = x + 3と書きます - 項は符号でスペース付き（
+と-）に結合されますが、先頭の負の係数は素のマイナスを取ります。y = -0.0429x2 + 2.2171x - 0.16のようにです - Excel が両方の行を表示するとき、
DataLabel.Textは CR/LF ペアではなく U+000B、垂直タブで結合します
数値の書式化にも上限があります。XLSTrendlineEquationLabel はゼロを 0 と出力し、絶対値が 0.0001 未満か 1000000 以上のとき 2E+06 のような 1 桁の指数表記に切り替え、それ以外は小数 4 桁までの固定表記（99.9999 まで）で、以降は整数桁 1 つごとに 1 桁減らし、末尾のゼロを刈り込みます。1.96 が 1.9600 ではなく 1.96 として現れるのはこのためです
Delphi から XLSTrendlineEquationLabel を呼ぶ
XLSTrendlineEquationLabel は、TXLSXChartTrendlineType の整数序数としての近似曲線の種類、多項式の次数、X と Y フィールドを持つ TXLSTrendlineLabelPoint レコードのオープン配列、2 つの表示スイッチを取ります。数式と決定係数の文字列を out パラメータで WideString として返し、スイッチがオフの文字列は空のままにします。order 引数は xlsxTrendPolynomial でのみ意味があり、他の種類では 0 を渡します
uses
lxStandard, lxHandleX;
procedure ShowFiveTrendlines;
const
YS: array[0..4] of Double = (2.1, 3.9, 6.2, 8.1, 9.8);
var
Points: array[0..4] of TXLSTrendlineLabelPoint;
Eq, R2: WideString;
I: Integer;
begin
for I := 0 to 4 do
begin
Points[I].X := I + 1;
Points[I].Y := YS[I];
end;
// 線形：Eq = 'y = 1.96x + 0.14'、R2 = 'R' + #$00B2 + ' = 0.9976'
if XLSTrendlineEquationLabel(Ord(xlsxTrendLinear), 0, Points,
True, True, Eq, R2) then
Writeln(Eq, ' | ', R2);
// 多項式次数 2：'y = -0.0429x2 + 2.2171x - 0.16'、R2 0.9983
if XLSTrendlineEquationLabel(Ord(xlsxTrendPolynomial), 2, Points,
True, True, Eq, R2) then
Writeln(Eq, ' | ', R2);
// 指数：'y = 1.6767e0.3812x'、R2 0.9386
if XLSTrendlineEquationLabel(Ord(xlsxTrendExponential), 0, Points,
True, True, Eq, R2) then
Writeln(Eq, ' | ', R2);
// 両方の行を表示するとき Excel が DataLabel.Text に入れるのと同じテキスト
Eq := Eq + #$000B + R2;
end;サンプルの 5 つの値は、HotXLS テストスイートが全ファミリで確認する凍結された Excel オラクルです。対数フィットは R² = 0.9519 付きで y = 4.7635ln(x) + 1.4589 と返り、べき乗フィットは R² = 0.9978 付きで y = 2.0719x0.9732 と返ります。同じデータで自分の出力が Excel と違うなら、ライブラリとオラクルが食い違っており、それは丸めの好みではなく報告する価値のあるバグです
Excel は指数とべき乗の近似曲線の決定係数をどう計算するのか
近似曲線ラベルにとって、決定係数はフィット値と元のデータ空間での実際の Y 値の間のピアソン相関の二乗で、XLSTrendlineEquationLabel も同じやり方で計算します。指数、対数、べき乗フィットの係数は、やはり対数変換データ上の通常の最小二乗から来ます。指数は x に対する ln(y)、対数は ln(x) に対する y、べき乗は ln(x) に対する ln(y) です。適合度の数値だけが自然ドメインで測り直されます
これがほとんどの再実装が間違える細部です。教科書的なアプローチは、線がフィットされた変換空間で決定係数を測るからです。オラクルの母集団では差はすでに 2 桁目に現れます。変換空間の指数フィットは 0.9477 を出す一方、ライブで読み戻した Excel のラベルは R² = 0.9386、つまり自然ドメインの相関です。変換空間の回帰統計を再利用するラベル生成器は、ユーザーが最初に比較するチャートで間違った数値を印刷します
2 つの特殊ケースが式を変えます。固定切片 0 の線形近似曲線は、非中心化残差形式、残差平方和を Y 値の二乗和で割ったものの 1 引き、を使い、その構成で Excel が表示するものと一致します。Y 値がすべて等しい母集団には説明すべき分散がないため、y = 5 のような定数方程式の隣に R² = #N/A が来ます。フィットがたまたまゼロ傾きになる非定数母集団は、代わりに R² = 0 になります
固定切片と 2 番目のオーバーロード
2 番目の XLSTrendlineEquationLabel オーバーロードは、出力パラメータの前に InterceptSet: Boolean と Intercept: Double を加え、近似曲線の c:intercept 要素と TXLSXChartTrendline の InterceptSet と Intercept プロパティを映します。Excel が固定切片を提供するのは線形、指数、多項式の近似曲線だけで、関数は同じ規則を強制します。対数かべき乗フィットへの固定切片は False を返し、指数フィットはフィットがその対数で働くため正の切片が要ります
procedure LabelForTrendline(Trend: TXLSXChartTrendline;
const Points: array of TXLSTrendlineLabelPoint);
var
Eq, R2: WideString;
begin
if not XLSTrendlineEquationLabel(Ord(Trend.TrendlineType), Trend.Order,
Points, Trend.DispEq, Trend.DispRSqr, Trend.InterceptSet,
Trend.Intercept, Eq, R2) then
begin
// 不定のフィット：ラベルをでっち上げない
Writeln('No label for this trendline');
Exit;
end;
if Eq <> '' then Writeln(Eq);
if R2 <> '' then Writeln(R2);
end;
procedure AddQuadraticThroughOrigin(Series: TXLSXChartSeries);
var
Trend: TXLSXChartTrendline;
begin
Trend := Series.AddTrendline(xlsxTrendPolynomial);
Trend.Order := 2;
Trend.DispEq := True;
Trend.DispRSqr := True;
Trend.InterceptSet := True;
Trend.Intercept := 0;
end;近似曲線自身のプロパティをオーバーロードに直接流し込むと、ラベルがブックが Excel に描かせるものと歩調を合わせ続けます。InterceptSet が False のとき Intercept 値は無視されるため、プロパティを無条件に渡すのは安全です。固定切片は点の要件も 1 つ下げます。次数 n の多項式は通常 n + 1 点を要しますが、定数項が固定されていれば n で足ります
折れ線チャートの近似曲線は実際にどの X 値を使うのか
普通の折れ線チャートの近似曲線は、カテゴリラベルではなく 1 起点のカテゴリ位置に対してフィットします。ラベルが数字であってもです。Excel は折れ線チャートの横軸をカテゴリ軸として扱うため、2019 年から 2023 年にわたって描かれた系列は x = 1..5 としてフィットされ、方程式の切片もそれを反映します。散布図とバブルチャートは別で、各点の対応する数値 X 値に対してフィットします
HotXLS はこれを痛い思いで学びました。セマンティックスナップショット投影の最初の版は、数値のカテゴリテキストを実際の x として読み、Excel が決して表示しない数学的には有効な方程式を生みました。後続の修正は、数値かどうかにかかわらず、すべてのカテゴリについて折れ線のフィットを元のカテゴリ位置に移し、隙間の扱いも決めました。空の Y セルはフィットから外れますが、後続の点は穴を埋めるためにずり下がる代わりに元のインデックスを保ちます。カテゴリ位置と実値のこの区別は、HotXLS チャートの暦正確な日付軸の目盛りの背後にもあります
uses
Math, lxStandard, lxHandleX;
// Values はカテゴリ順の折れ線系列を保持。NaN が空セルを示す
function LinePointsFromCategories(const Values: array of Double):
TArray<TXLSTrendlineLabelPoint>;
var
I, N: Integer;
begin
SetLength(Result, Length(Values));
N := 0;
for I := 0 to High(Values) do
begin
if IsNan(Values[I]) then
Continue; // 空セルを飛ばし、後続のインデックスを保つ
Result[N].X := I + 1; // 1 起点のカテゴリ位置
Result[N].Y := Values[I];
Inc(N);
end;
SetLength(Result, N);
end;このステップを間違えると、本記事の他の慣習はすべて無駄になります。ラベルは間違った回帰に対して正しく書式化されたテキストになるからです。HotXLS のブックセマンティックスナップショットは、EquationText と RSquaredText プロパティを出すとき、プロットグループごとにこの規則をすでに適用するため、インポートされたチャートのスナップショットは、インポートされた Excel チャートの保存済み ChartML の残りと並んで、Excel が表示するはずのラベルテキストを運びます
XLSTrendlineEquationLabel はいつ False を返すのか
XLSTrendlineEquationLabel は、フィットが不定のときは常に False を返して両文字列を空のままにし、部分的あるいは推測のラベルを公開しません。関数は半分の方程式を決して出力しないため、True の結果は常に両方の要求文字列が完全であることを意味します
- 点が 2 つ未満、近似種類が 0..5 の外、多項式の次数が 2..6 の外
- 対数かべき乗フィットでの非正の X、指数かべき乗フィットでの非正の Y。どちらも対数が要るためです
- NaN や無限の入力、または絶対値が 1E50 を超える X、Y、切片
- 多項式の次数に対して点が少なすぎる、固定切片なしの非多項式フィットで X 値がすべて等しい、多項式系が特異
- フィット値が有限範囲を出る（1E100 超）、またはオーバーフローする指数
移動平均は意図的な例外です。Excel は、保存された dispEq と dispRSqr スイッチがオンでも、移動平均の近似曲線に数式も決定係数テキストも公開しないため、関数は両文字列を空にして True を返します。呼び出し元に描くべきものがないと正しく告げます
数値安定性と現行の限界
多項式近似曲線は、素朴なコードが桁を失う場所です。数千に達する X 値に対する 6 次フィットの生の正規方程式はひどく悪条件なので、HotXLS は系を組み立てる前に X を中心化とスケールし、部分ピボット選択付きガウス・ジョルダン消去で解き、それから係数を元の変数へ展開し戻します。線形フィットと決定係数の相関は同じ理由で増分的な平均と分散の累積を使い、データに対して単なる浮動小数点ノイズに過ぎない切片はゼロへスナップされ、ラベルが + 4E-17 で終わらないようにします
正直な限界は書式化にあります。関数は Excel の既定のラベル数値を作ります。近似曲線ラベルのカスタム LabelNumberFormat は TXLSXChartTrendline が保存しますが、計算されたテキストには適用されません。描画出力での指数の上付きスタイリングと自動ラベル配置は別の仕事で、大きなオフセットやほぼ共線のデータ上の超高次フィットは、解かれる代わりに特異として拒否され得ます。Excel と一致することを目指すライブラリにとって、拒否の方が安全な失敗です
FAQ
XLSX ファイルは近似曲線の数式テキストを保存するのか
いいえ。c:trendline 要素はフィットタイプ、次数か期間、任意の切片、予測、c:dispEq と c:dispRSqr スイッチを保存し、c:trendlineLbl はラベルのレイアウトと書式を保存します。Excel は描画時に数式と決定係数テキストを計算するため、HotXLS も XLSTrendlineEquationLabel で計算するのです
多項式ラベルが上付きではなく x2 と表示する理由
Excel の DataLabel.Text がそう入っているからです。指数は文字列の中では素の数字で、Excel がチャートを描くときに上付き書式を適用します。U+00B2 文字が現れるのは決定係数行の R² = 接頭辞だけです
クラシック XLS エンジンで作ったチャートのラベルも計算できるか
XLSTrendlineEquationLabel は lxStandard に住み、両方の HotXLS エンジンで共有されるため、抽出できるどんな点集合でも機能します。ラベルテキストのセマンティックスナップショットへの自動投影は現在 OOXML チャートをカバーします。他のソースでは、TXLSTrendlineLabelPoint 配列を自分で組み立て、折れ線チャートのカテゴリ位置規則に従ってください
近似曲線ラベルの計算、チャート作成、Excel オブジェクトモデルの残りは、実行時に Excel のインストールを要しない XLS と XLSX 用のネイティブ Delphi スプレッドシートコンポーネント HotXLS に同梱されています