我们正在处理多元线性回归模型:f = a·V + b·Si% + c·T + d,需要用Excel的LINEST函数拟合系数a,b,c,d。
根据LINEST函数的文档,对于多元回归(多个x变量),LINEST的输入规则是:
具体步骤:
示例(假设数据在A:D列,A:f, B:V, C:Si%, D:T):
在E1单元格输入:=LINEST(A1:A264, B1:D264, TRUE, FALSE)
然后按Ctrl+Shift+Enter,得到4个值,分别是a(V的系数)、b(Si%的系数)、c(T的系数)、d(截距)。
注意:如果数据行数不是固定的264行,要确保选择的区域正确(包括标题行?一般选实际数据区域,不含标题)。
另外,如果出现错误,检查:
现在,假设用户的数据在Sheet1的A2:D265(假设有264行数据,标题行在A1:D1),则公式应为:
在E2单元格输入:=LINEST(A2:A265, B2:D265, TRUE, FALSE)
然后按Ctrl+Shift+Enter,E2:H2将分别显示a, b, c, d。
如果用户希望在一个单元格中看到拟合公式,可以拼接字符串,但LINEST本身返回数值数组。
所以,具体操作步骤:
注意:如果数据有标题行,那么数据区域应该从标题行的下一行开始,比如A2:A265(共264行)。
因此,根据用户的数据表(假设标题行在第1行,数据从第2行到第265行,共264行),公式应为:
在E2单元格输入:=LINEST(A2:A265, B2:D265, TRUE, FALSE)
然后按Ctrl+Shift+Enter,得到a, b, c, d。
最后,得到的系数可以用于预测:f_pred = aV + bSi% + c*T + d。
现在,我们以步骤化的方式呈现给用户:
假设你的出铁场除尘数据表中,列顺序为:
在空白单元格(如 E2
)输入以下公式:
known_y's
:A2:A265
→ 风机频率 f 的实际值区域(因变量)。known_x's
:B2:D265
→ 铁水流速 V、硅含量 Si%、温度 T 的区域(3个自变量,需连续排列)。const
:TRUE
→ 强制计算截距 d(模型中需要 d,若设为 FALSE
则 d=0,不符合需求)。stats
:FALSE
→ 仅返回回归系数(不返回附加统计值,如 R2、标准误差等)。由于LINEST返回数值数组(长度为「自变量个数+1」,这里为4个值:a,b,c,d),需按以下方式输入:
E2:H2
,共4个单元格)。=LINEST(A2:A265, B2:D265, TRUE, FALSE)
。Ctrl + Shift + Enter
(旧版Excel必备;新版Excel 365+ 可能支持直接按 Enter
,但为兼容性建议用数组方式)。公式执行后,E2:H2
将依次显示拟合系数:
E2
:系数 a(铁水流速 V 的回归系数)F2
:系数 b(铁水硅含量 Si% 的回归系数)G2
:系数 c(铁水温度 T 的回归系数)H2
:截距 d误差检查:
若结果出现 #VALUE!
错误,检查:
B2:D265
)是否包含非数值内容(如文本、空单元格)。A2:A265
)是否为单一列(若 f
分散在多列,需调整布局)。精度提升:
若需返回附加统计值(如 R2、标准误差、F统计量等),将公式第四个参数改为 TRUE
,并选择 5行×4列 的区域(如 E2:H6
),再按 Ctrl + Shift + Enter
。返回结果的结构为:
假设某3行数据如下(仅作逻辑演示):
行 | f(Hz) | V(吨/分钟) | Si%(%) | T(℃) |
---|---|---|---|---|
2 | 35 | 2.0 | 0.2 | 1300 |
3 | 40 | 3.0 | 0.3 | 1320 |
4 | 45 | 4.0 | 0.4 | 1340 |
在 E2:H2
输入公式 =LINEST(A2:A4, B2:D4, TRUE, FALSE)
并按 Ctrl + Shift + Enter
后,可能得到:
通过以上步骤,即可在Excel中利用LINEST函数完成多元线性回归,得到风机频率与多参数的拟合系数,为后续节能调控提供量化依据。