LlosLab 軟體開發部落格面向 PDF、Delphi 與文件軟體開發者的工程筆記

技術文章

HotXLS：在 Delphi 精確重現 Excel 趨勢線方程式標籤

HotXLS Delphi Component

HotXLS 透過 lxStandard 單元裡的 XLSTrendlineEquationLabel，在 Delphi 中重現 Excel 的趨勢線方程式與 R 平方標籤。這個函式為線性、指數、對數、多項式（2 到 6 階）、乘冪與移動平均趨勢線擬合所屬數列的資料點，回傳與 Excel 放進 DataLabel.Text 的文字逐碼點一致的內容，擬合無定義時回傳 False

這件事之所以需要一個函式，是因為標籤文字不在檔案裡。XLSX 圖表儲存一個帶 c:trendlineType 的 c:trendline 元素、c:dispEq 與 c:dispRSqr 開關，以及用於位置與格式的可選 c:trendlineLbl，但 ECMA-376 DrawingML Charts 沒有給標籤自己的文字。Excel 每次畫圖都重算一遍 y = 1.96x + 0.14。想在不起動 Excel 的前提下顯示、匯出、搜尋或比對那段文字的 Delphi 代碼，同樣得重算，而且得按 Excel 的方式重算，連減號周圍的空格都不放過

HotXLS 的 XLSTrendlineEquationLabel 在 Delphi 中重算 Excel 趨勢線方程式與 R 平方標籤，因為 XLSX 的 c:trendline 元素只儲存擬合類型與 dispEq、dispRSqr 開關
儲存的圖表部件帶著配方，數列資料點餵給擬合，回傳文字與 Excel 的 DataLabel.Text 逐碼點一致，否則函式回傳 False

為什麼不自己格式化迴歸係數就好？

手排格式的係數幾乎永遠對不上 Excel 標籤，因為標籤遵循好幾條容易漏掉、卻也容易對照驗證的小慣例。HotXLS 從活的 Excel COM 讀回散佈圖上的 DataLabel.Text，把這些慣例凍結下來，然後對著這個 oracle 寫格式化器，而不是憑直覺

實務中會咬人的慣例：

  • 指數在標籤文字裡是普通字元。多項式標籤讀作 x2 而不是 x²，乘冪標籤讀作 x0.9732；上標外觀是圖表的渲染屬性，不是字串的一部分
  • 唯一的非 ASCII 字元是 R² = 前綴裡的 U+00B2 上標二
  • 恰好為 1 或 -1 的係數會被省略，所以 Excel 寫 y = x + 3，不是 y = 1x + 3
  • 各項透過正負號以空格相連（ + 與 - ），而開頭的負係數用裸減號，如 y = -0.0429x2 + 2.2171x - 0.16
  • Excel 同時顯示兩行時，DataLabel.Text 以 U+000B（垂直定位字元）串接，不是 CR/LF 對

數字格式化也有邊界。XLSTrendlineEquationLabel 把零印成 0，數量級低於 0.0001 或達到 1000000 時切換成一位整數的科學記號如 2E+06，其餘情況用固定記號：99.9999 以下四位小數，之後每多一位整數少一位小數，並修剪尾隨零。這就是 1.96 顯示為 1.96 而不是 1.9600 的原因

從 Delphi 呼叫 XLSTrendlineEquationLabel

XLSTrendlineEquationLabel 收趨勢線類型（TXLSXChartTrendlineType 的整數序數）、多項式階數、帶 X 與 Y 欄位的 TXLSTrendlineLabelPoint 記錄開放陣列，以及兩個顯示開關。它透過 out 參數以 WideString 回傳方程式與 R 平方字串，開關關閉時對應字串留空。階數參數只對 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);

  // 二階多項式：'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;

範例裡那五組值就是 HotXLS 測試套件對每個家族核對的凍結 Excel oracle。對數擬合回傳 y = 4.7635ln(x) + 1.4589 配 R² = 0.9519，乘冪擬合回傳 y = 2.0719x0.9732 配 R² = 0.9978。如果您的輸出與 Excel 在同一份資料上不一致，那是程式庫與 oracle 打架，是值得回報的 bug，不是捨入偏好

Excel 怎麼為指數與乘冪趨勢線算 R 平方？

就趨勢線標籤而言，R 平方是擬合值與原始資料空間中實際 Y 值之間的 Pearson 相關平方，XLSTrendlineEquationLabel 也這麼算。指數、對數與乘冪擬合的係數仍然來自對數變換後資料上的普通最小平方法：指數以 ln(y) 對 x，對數以 y 對 ln(x)，乘冪以 ln(y) 對 ln(x)。只有配適度這個數字是換回自然域量的

這是多數重新實作會搞錯的細節，因為教科書做法是在擬合直線所在的變換空間裡量 R 平方。在 oracle 母體上，差異在第二位小數就現形。變換空間裡的指數擬合給 0.9477，而活讀回來的 Excel 標籤說 R² = 0.9386，這才是自然域的相關。一個重用變換空間迴歸統計量的標籤產生器，會在使用者拿來比較的第一張圖上就印錯數字

HotXLS 把 Excel 趨勢線 R 平方算成原始資料空間中的 Pearson 相關平方，所以指數標籤讀作 R 平方 0.9386，而不是變換空間的 0.9477
指數、對數與乘冪係數在對數變換資料上擬合，Excel 卻把配適度換回自然域評分，HotXLS 照做

兩個特殊情況會改變公式。固定截距為零的線性趨勢線用未置中的殘差形式：1 減去殘差平方和除以 Y 值平方和，與 Excel 在該配置下顯示的一致。Y 值全相等的母體沒有變異可解釋，結果是常數方程式（如 y = 5）旁邊掛著 R² = #N/A。非常數母體的擬合恰好斜率為零時，則得到 R² = 0

固定截距與第二個重載

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 值被忽略，所以無條件傳屬性是安全的。固定截距還會把資料點需求降一：n 階多項式通常需要 n + 1 個點，常數項釘住時只要 n 個

折線圖的趨勢線實際用哪些 X 值？

普通折線圖上的趨勢線對從 1 起算的類別位置擬合，而不是類別標籤，即使標籤是數字。Excel 把折線圖的水平軸當類別軸，所以 2019 到 2023 年的數列按 x = 1..5 擬合，方程式截距反映的就是這個。散佈圖與泡泡圖不同：它們對每個點配對的數值 X 擬合

HotXLS 是吃過虧才學會的。語意快照投影的第一版把數值類別文字讀成實際 x，產出一條數學上成立、Excel 卻永遠不會顯示的方程式。後續修復把折線擬合搬到每個類別的原始類別位置——不管是數字還是文字——並順手釐清了空缺的處理：空白的 Y 儲存格不參與擬合，但後面的點保留原始索引，不會往前滑補洞。類別位置與真實值的這個區分，也是HotXLS 圖表的日曆精準日期軸刻度背後的原理

HotXLS 對 Delphi 折線圖趨勢線按從 1 起算的類別位置擬合，類別標籤 2019 到 2023 變成 x = 1 到 5，而散佈圖與泡泡圖趨勢線使用配對的數值 X
空缺遵循同一規則：空白 Y 儲存格退出擬合，後面的點保留原始索引，不會往前滑補洞
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 會顯示的標籤文字，與其餘匯入 Excel 圖表的保留 ChartML 並列

XLSTrendlineEquationLabel 什麼時候回傳 False？

擬合無定義時，XLSTrendlineEquationLabel 回傳 False 並把兩個字串留空，而不是發布半個或猜測的標籤。函式從不輸出半條方程式，所以 True 結果永遠代表兩個被要求的字串都完整

  • 資料點少於兩個、趨勢類型超出 0..5，或多項式階數超出 2..6
  • 對數或乘冪擬合出現非正 X，或指數與乘冪擬合出現非正 Y，因為兩者都需要對數
  • NaN 或無限大輸入，或任何 X、Y、截距的數量級超過 1E50
  • 資料點對多項式階數太少、非多項式且無固定截距的擬合中所有 X 相等，或多項式系統奇異
  • 擬合值超出有限範圍（高於 1E100），或指數會溢位

移動平均是刻意的例外。即使儲存的 dispEq 與 dispRSqr 開關都開著，Excel 也不為移動平均趨勢線提供方程式或 R 平方文字，所以函式回傳 True 且兩個字串皆空，正確告訴呼叫方沒有東西可畫

數值穩定性與目前的限制

多項式趨勢線是天真的代碼丟失精度的地方。X 值在數千量級時，六階擬合的原始正規方程式病態嚴重，所以 HotXLS 在建系統之前先把 X 置中與縮放，用帶部分樞軸的高斯－約當消去法求解，最後才把係數展開回原變數。線性擬合與 R 平方相關基於同樣理由使用遞增的平均值與變異數累加，而相對於資料只是浮點雜訊的截距會被歸零，標籤因此不會以 + 4E-17 收尾

誠實的限制在格式化這一側。函式產出 Excel 的預設標籤數字；趨勢線標籤上的自訂 LabelNumberFormat 會被 TXLSXChartTrendline 持久化，但不套用到計算出的文字。渲染輸出裡指數的上標樣式與標籤自動擺放是另立的工作，而大偏移或近共線資料上的極高階擬合仍可能以奇異為由被拒絕而不是被解。對一個立志與 Excel 一致的程式庫來說，拒絕是更安全的失敗方式

FAQ

XLSX 檔案儲存趨勢線方程式文字嗎

不儲存。c:trendline 元素存擬合類型、階數或週期、可選截距、預測與 c:dispEq、c:dispRSqr 開關，c:trendlineLbl 存標籤版面與格式。Excel 在渲染時計算方程式與 R 平方文字，這正是 HotXLS 用 XLSTrendlineEquationLabel 計算它的原因

為什麼多項式標籤顯示 x2 而不是上標

因為 Excel 的 DataLabel.Text 裝的就是這個。指數在字串裡是普通數字，Excel 畫圖時才套上標格式。U+00B2 字元只出現在 R 平方那一行的 R² = 前綴

可以為傳統 XLS 引擎建的圖表計算標籤嗎

XLSTrendlineEquationLabel 住在 lxStandard，兩個 HotXLS 引擎共用，所以對任何取得出的資料點集合都可用。標籤文字自動投影進語意快照目前涵蓋 OOXML 圖表；其他來源請自行組 TXLSTrendlineLabelPoint 陣列，折線圖記得遵循類別位置規則

趨勢線標籤計算、圖表製作與其餘 Excel 物件模型都隨HotXLS，XLS 與 XLSX 的原生 Delphi 試算表元件提供，執行時不需要安裝 Excel