Excel二次回归LINEST/INDEX函数解析及分步计算问询

一、G1/G2/G3的INDEX+LINEST运作逻辑 #

先看核心公式LINEST(E$2:E$6; D$2:D$6^{0,1,2}; 0; 0)：

• 这里的分号是欧洲版Excel的参数分隔符（国内常用逗号），E$2:E$6是要拟合的Y值数据集，D$2:D$6^{0,1,2}是给每个X值生成三个维度的数据：X⁰（恒为1）、X¹（原X值）、X²（X的平方），相当于给二次方程y=ax²+bx+c搭建自变量矩阵。
• 后面两个0：第一个0表示不强制回归线过原点，第二个0表示只返回回归系数，不输出方差、R²这类额外统计量。

LINEST计算后会返回一个1行3列的数组，顺序是x²的系数a、x的系数b、常数项c（注意是从高次项到低次项的顺序）。

而INDEX(数组; ROW())的作用就是把这个数组拆到单个单元格：

• G1的ROW()返回1，取数组第1个元素（a）；
• G2的ROW()返回2，取数组第2个元素（b）；
• G3的ROW()返回3，取数组第3个元素（c）。

二、二次回归参数的分步计算（手动还原） #

二次回归用的是最小二乘法，核心是找到a、b、c让所有样本的残差平方和（实际Y值与预测Y值的差的平方和）最小。整个过程不需要计算方差、相关性（这些是LINEST开启额外统计时才会输出的内容，这里公式里关了），步骤如下：

1. 先算所有求和项 #

假设X数据是D2到D6（记为x₁到x₅），Y数据是E2到E6（记为y₁到y₅），先算这些基础值：

• S₀ = 样本数量（这里是5）
• S₁ = x₁+x₂+x₃+x₄+x₅（所有X的和）
• S₂ = x₁²+x₂²+x₃²+x₄²+x₅²（所有X平方的和）
• S₃ = x₁³+x₂³+x₃³+x₄³+x₅³（所有X三次方的和）
• S₄ = x₁⁴+x₂⁴+x₃⁴+x₄⁴+x₅⁴（所有X四次方的和）
• T₀ = y₁+y₂+y₃+y₄+y₅（所有Y的和）
• T₁ = x₁y₁+x₂y₂+x₃y₃+x₄y₄+x₅y₅（X*Y的和）
• T₂ = x₁²y₁+x₂²y₂+x₃²y₃+x₄²y₄+x₅²y₅（X平方*Y的和）

2. 构建正规方程组 #

根据最小二乘法的推导，得到以下三元一次方程组：

S₄*a + S₃*b + S₂*c = T₂
S₃*a + S₂*b + S₁*c = T₁
S₂*a + S₁*b + S₀*c = T₀

3. 解方程组得系数 #

用消元法或者矩阵求逆的方式解上面的方程组，得到的a就是G1的值，b是G2的值，c是G3的值——这就是LINEST函数在后台做的计算。

三、G9公式的作用 #

G9是把三个系数格式化成可读性强的二次方程字符串：

• 用TEXT函数根据系数的正负、零值调整显示格式：正系数自动带+，负系数显示-，系数为0时直接隐藏对应项；
• 最后用&把各个部分拼接成y = ax² + bx + c的标准形式。

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