Excel中Const=False且数值<1E-7时LINEST函数返回异常结果求助

问题重现 #

• 在A1单元格输入9.1E-8，A2输入9.2E-8，选中两单元格下拉自动填充至A25
• 在B1输入公式=1.1*A1并向下复制，得到25行的两列数据
• 使用公式=LINEST(A1:A25,B1:B25,FALSE,TRUE)返回异常结果
• 将参数Const设为TRUE时，函数正常返回斜率0.909（即1/1.1）
• 临时解决办法：将所有数值乘以1E9，但希望找到其他方案

问题原因 #

Excel基于IEEE 754标准处理浮点数，当使用LINEST强制截距为0（Const=FALSE）时，纳级极小值的数值范围过窄，会触发算法的精度丢失，导致计算异常；而允许截距存在时，算法的数值稳定性更高，能输出正确结果。

替代解决方案 #

方案1：用手动公式计算强制截距的斜率 #

直接基于最小二乘法的数学定义，绕过LINEST的精度问题：
在单元格输入公式：
=SUMPRODUCT(A1:A25,B1:B25)/SUMPRODUCT(B1:B25,B1:B25)
该公式可直接计算出强制截距为0时的正确斜率（约0.909）。

方案2：标准化数据后再用LINEST计算 #

1. 计算A列的平均值=AVERAGE(A1:A25)和标准差=STDEV.P(A1:A25)，生成标准化A列：=(A1-AVERAGE(A1:A25))/STDEV.P(A1:A25)，下拉填充
2. 同样处理B列，生成标准化B列：=(B1-AVERAGE(B1:B25))/STDEV.P(B1:B25)，下拉填充
3. 对标准化数据使用LINEST：=LINEST(标准化A列区域,标准化B列区域,FALSE,TRUE)
4. 将得到的标准化斜率转换为原始数据斜率：标准化斜率 × (原始A列标准差 / 原始B列标准差)

方案3：调整Excel计算精度设置 #

1. 打开Excel选项 → 公式 → 勾选「将精度设为所显示的精度」
2. 先将A、B列单元格格式设置为15位小数，再应用该设置
   注意：此设置会影响整个工作簿的数值精度，完成计算后建议改回默认设置。

内容的提问来源于stack exchange，提问作者blablubbb