LlosLab Software Development Blog面向 PDF、Delphi 和文档软件开发者的工程笔记

技术文章

HotXLS：在 Delphi 里算出精确的 Excel 趋势线公式标签

HotXLS Delphi Component

HotXLS 经 lxStandard 单元里的 XLSTrendlineEquationLabel 在 Delphi 里复现 Excel 的趋势线公式与 R 平方标签。该函数对 linear、exponential、logarithmic、polynomial（2 到 6 阶）、power 和 moving average 趋势线拟合所属系列的点，返回与 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 从散点图上 DataLabel.Text 的活体 Excel COM 读回冻结了这些约定，然后照着这个 oracle 写格式化器，而不是照着直觉

实践中会咬人的约定：

  • 指数在标签文本里是普通字符。polynomial 标签读作 x2，不是 x²；power 标签读作 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 的整数序数）、polynomial 阶数、带 X 与 Y 字段的 TXLSTrendlineLabelPoint 记录开放数组，以及两个显示开关。它经 out 参数以 WideString 返回公式与 R 平方字符串，开关关着的那个留空。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;

  // Linear：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);

  // Polynomial order 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);

  // Exponential：'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。logarithmic 拟合回来是 y = 4.7635ln(x) + 1.4589 配 R² = 0.9519，power 拟合是 y = 2.0719x0.9732 配 R² = 0.9978。同一份数据上你的输出与 Excel 不同，就是库与 oracle 不一致——那是值得上报的 bug，不是舍入偏好

Excel 怎么算 exponential 与 power 趋势线的 R 平方？

对趋势线标签而言，R 平方是拟合值与原始数据空间里实际 Y 值之间的 Pearson 相关系数平方，XLSTrendlineEquationLabel 同样这么算。exponential、logarithmic 与 power 拟合的系数仍来自对数变换数据上的普通最小二乘：exponential 是 ln(y) 对 x，logarithmic 是 y 对 ln(x)，power 是 ln(y) 对 ln(x)。只有拟合优度那个数是回到自然域量的

这是多数重新实现做错的细节，因为教科书做法在拟合所在的变换空间里量 R 平方。在 oracle 总体上，差异在第二位小数就显形：变换空间里的 exponential 拟合给 0.9477，而活体读回的 Excel 标签写着 R² = 0.9386——那是自然域的相关。复用自己变换空间回归统计量的标签生成器，会在用户比较的第一张图上打出错误的数

HotXLS 把 Excel 趋势线 R 平方算成原始数据空间里的 Pearson 相关系数平方，所以 exponential 标签读作 R 平方 0.9386，而不是变换空间的 0.9477
exponential、logarithmic 与 power 的系数在对数变换数据上拟合，但 Excel 回到自然域给拟合打分，HotXLS 跟进

两个特例改变公式。固定截距为零的 linear 趋势线用无中心残差形式：1 减残差平方和除以 Y 值平方和，与 Excel 在该配置下显示的一致。Y 值全相等的总体没有可解释的方差，结果是在 y = 5 这类常量公式旁边给出 R² = #N/A。非恒定总体、拟合碰巧零斜率的，则给 R² = 0

固定截距与第二个重载

XLSTrendlineEquationLabel 的第二个重载在输出参数之前加 InterceptSet: Boolean 和 Intercept: Double，对应趋势线的 c:intercept 元素和 TXLSXChartTrendline 的 InterceptSet 与 Intercept 属性。Excel 只对 linear、exponential 与 polynomial 趋势线提供固定截距，函数执行同一规则：logarithmic 或 power 拟合上的固定截距返回 False；exponential 拟合需要正截距，因为它对截距的对数做拟合

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 阶 polynomial 通常要 n + 1 个点，常数项被钉住时只要 n 个

Line 图趋势线实际用哪些 X 值？

普通 Line 图上的趋势线对着从 1 起计的类别位置拟合，不是对着类别标签——哪怕标签是数字。Excel 把 Line 图的水平轴当类别轴，所以画在 2019 到 2023 年份上的系列按 x = 1..5 拟合，公式的截距反映的也是这个。Scatter 与 Bubble 图不同：它们对每个点匹配到的数值 X 拟合

HotXLS 是吃过亏才学会的。语义快照投影的第一版把数值型类别文本当真实 x 读，产出一条数学上有效、Excel 却永远不会显示的公式。后续修复把 Line 拟合挪回每个类别——数字与否——的原始类别位置，并顺手确定了空档的处理：空白 Y 单元格被剔出拟合，但后面的点保留原始索引、不往前挪去填洞。类别位置与真实值的这个区分，也是 HotXLS 图表的日历精确日期轴刻度背后的原理

HotXLS 在 Delphi 里对从 1 起计的类别位置拟合 Line 图趋势线，类别标签 2019 到 2023 变成 x = 1 到 5；Scatter 与 Bubble 趋势线则用匹配到的数值 X
空档遵循同一规则：空白 Y 单元格剔出拟合，后面的点保留原始索引、不往前挪去填洞
uses
  Math, lxStandard, lxHandleX;

// Values 按类别顺序持有 Line 系列；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 属性时已按 plot 组应用这条规则，所以导入图表的快照带着 Excel 会显示的标签文本，与 导入 Excel 图表的保留 ChartML 并存

XLSTrendlineEquationLabel 什么时候返回 False？

拟合无定义时，XLSTrendlineEquationLabel 返回 False 并把两个字符串留空，而不是发布半个或猜出来的标签。函数从不出半条公式，所以 True 结果总意味着两个请求的字符串都完整

  • 点数少于二、趋势种类超出 0..5、或 polynomial 阶数超出 2..6
  • logarithmic 或 power 拟合遇到非正 X；exponential 或 power 拟合遇到非正 Y——两者都要取对数
  • NaN 或无穷输入，或任何量级超过 1E50 的 X、Y 或截距
  • 点数不足以支撑 polynomial 阶数；无固定截距的非 polynomial 拟合遇到全相等的 X；或 polynomial 方程组奇异
  • 拟合值越出有限范围（超过 1E100），或指数会溢出

moving average 是刻意的例外。即使存储的 dispEq 与 dispRSqr 开关都开着，Excel 也不为 moving average 趋势线暴露公式或 R 平方文本，所以函数返回 True、两个字符串留空——正确地告诉调用方没东西可画

数值稳定性与当前限制

polynomial 趋势线是朴素代码丢精度的地方。X 值上千的六阶拟合，原始正规方程严重病态，所以 HotXLS 先把 X 中心化并缩放再建方程组，用带部分主元的高斯-约当消元求解，然后才把系数展开回原变量。linear 拟合和 R 平方相关出于同样理由用增量均值与方差累积；相对数据只是浮点噪声的截距被吸附为零，标签结尾才不会出现 + 4E-17

老实的限制在格式化上。函数产出 Excel 的默认标签数字；趋势线标签上的自定义 LabelNumberFormat 会被 TXLSXChartTrendline 持久化，但不会应用到计算出的文本。渲染输出里指数的上标样式与自动标签摆放是另外的活；大偏移或近共线数据上的极高阶拟合仍可能以奇异被拒而不是解出。对一个立志与 Excel 一致的库，拒绝是更安全的失败方式

FAQ

XLSX 文件存趋势线公式文本吗

不存。c:trendline 元素存拟合类型、阶数或周期、可选截距、预测期与 c:dispEq、c:dispRSqr 开关；c:trendlineLbl 存标签的布局与格式。Excel 在渲染时才算公式与 R 平方文本——HotXLS 因此用 XLSTrendlineEquationLabel 来算

polynomial 标签为什么显示 x2 而不是上标

因为 Excel 的 DataLabel.Text 里就是它。字符串里指数是普通数字，上标格式是 Excel 画图时施加的。U+00B2 字符只出现在 R 平方行的 R² = 前缀里

经典 XLS 引擎建的图表能算标签吗

XLSTrendlineEquationLabel 住在 lxStandard，两个 HotXLS 引擎共享，任何你能提取出的点集都能用。标签文本自动投影进语义快照目前覆盖 OOXML 图表；其他来源请自己构建 TXLSTrendlineLabelPoint 数组，并为 Line 图遵循类别位置规则

趋势线标签计算、图表制作和 Excel 对象模型的其余部分都随 HotXLS，面向 XLS 与 XLSX 的原生 Delphi 表格组件发布，运行时不需要安装 Excel