怎样用Excel计算线性回归方程？

LINEST使用最小二乘法对已知数据进行最佳直线拟合，并返回描述此直线的数组。因为此函数返回数值数组，所以必须以数组公式的形式输入。语法LINEST(known_y's,known_x's,const,stats)Known_y's
是关系表达式
y
=
mx
+
b
中已知的
y
值集合。如果数组
known_y's
在单独一列中，则
known_x's
的每一列被视为一个独立的变量。
如果数组
known-y's
在单独一行中，则
known-x's
的每一行被视为一个独立的变量。
Known_x's
是关系表达式
y
=
mx
+
b
中已知的可选
x
值集合。数组
known_x's
可以包含一组或多组变量。如果只用到一个变量，只要
known_y's
和
known_x's
维数相同，它们可以是任何形状的区域。如果用到多个变量，则
known_y's
必须为向量（即必须为一行或一列）。
如果省略
known_x's，则假设该数组为
{1,2,3,}，其大小与
known_y's
相同。
Const
为一逻辑值，用于指定是否将常量
b
强制设为
0。如果
const
为
TRUE
或省略，b
将按正常计算。
如果
const
为
FALSE，b
将被设为
0，并同时调整
m
值使
y
=
mx。
Stats
为一逻辑值，指定是否返回附加回归统计值。如果
stats
为
TRUE，则
LINEST
函数返回附加回归统计值，这时返回的数组为
{mn,mn-1,,m1,b;sen,sen-1,,se1,seb;r2,sey;F,df;ssreg,ssresid}。
如果
stats
为
FALSE
或省略，LINEST
函数只返回系数
m
和常量
b。
附加回归统计值如下：统计值说明se1,se2,,sen系数
m1,m2,,mn
的标准误差值。seb常量
b
的标准误差值（当
const
为
FALSE时，seb
=
#N/A）r2判定系数。Y
的估计值与实际值之比，范围在
0
到
1
之间。如果为
1，则样本有很好的相关性，Y
的估计值与实际值之间没有差别。如果判定系数为
0，则回归公式不能用来预测
Y
值。有关计算
r2
的方法的详细信息，请参阅本主题后面的“说明”。seyY
估计值的标准误差。FF
统计或
F
观察值。使用
F
统计可以判断因变量和自变量之间是否偶尔发生过可观察到的关系。df自由度。用于在统计表上查找
F
临界值。所查得的值和
LINEST
函数返回的
F
统计值的比值可用来判断模型的置信度。有关如何计算
df，请参阅在此主题中后面的“说明”。示例
4
说明了
F
和
df
的使用。ssreg回归平方和。ssresid残差平方和。有关计算
ssreg
和
ssresid
的方法的详细信息，请参阅本主题后面的“说明”。下面的图示显示了附加回归统计值返回的顺序。说明可以使用斜率和
y
轴截距描述任何直线：
斜率
(m)：
通常记为
m，如果需要计算斜率，则选取直线上的两点，(x1,y1)
和
(x2,y2)；斜率等于
(y2
-
y1)/(x2
-
x1)。Y
轴截距
(b)：
通常记为
b，直线的
y
轴的截距为直线通过
y
轴时与
y
轴交点的数值。直线的公式为
y
=
mx
+
b。如果知道了
m
和
b
的值，将
y
或
x
的值代入公式就可计算出直线上的任意一点。还可以使用
TREND
函数。当只有一个自变量
x
时，可直接利用下面公式得到斜率和
y
轴截距值：
斜率：
=INDEX(LINEST(known_y's,known_x's),1)Y
轴截距：
=INDEX(LINEST(known_y's,known_x's),2)数据的离散程度决定了
LINEST
函数计算的精确度。数据越接近线性，LINEST
模型就越精确。LINEST
函数使用最小二乘法来判定最适合数据的模型。当只有一个自变量
x
时，m
和
b
是根据下面的公式计算出的：
其中
x
和
y
是样本平均值，例如
x
=
AVERAGE(known
x's)
和
y
=
AVERAGE(known_y's)。直线和曲线函数
LINEST
和
LOGEST
可用来计算与给定数据拟合程度最高的直线或指数曲线。但需要判断两者中哪一个更适合数据。可以用函数
TREND(known_y's,known_x's)
来计算直线，或用函数
GROWTH(known_y's,
known_x's)
来计算指数曲线。这些不带参数
new_x's
的函数可在实际数据点上根据直线或曲线来返回
y
的数组值，然后可以将预测值与实际值进行比较。还可以用图表方式来直观地比较二者。
回归分析时，Microsoft
Excel
计算每一点的
y
的估计值和实际值的平方差。这些平方差之和称为残差平方和
(ssresid)。然后
Microsoft
Excel
计算总平方和
(sstotal)。当
const
=
TRUE
或被删除时，总平方和是
y
的实际值和平均值的平方差之和。当
const
=
FALSE
时，总平方和是
y
的实际值的平方和（不需要从每个
y
值中减去平均值）。回归平方和
(ssreg)
可通过公式
ssreg
=
sstotal
-
ssresid
计算出来。残差平方和与总平方和的比值越小，判定系数
r2
的值就越大，r2
是表示回归分析公式的结果反映变量间关系的程度的标志。r2
等于
ssreg/sstotal。
在某些情况下，一个或多个
X
列可能没有出现在其他
X
列中的预测值（假设
Y's
和
X's
位于列中）。换句话说，删除一个或多个
X
列可能导致同样精度的
y
预测值。在这种情况下，这些多余的
X
列应该从回归模型中删除。这种现象被称为“共线”，原因是任何多余的
X
列被表示为多个非多余
X
列的和。LINEST
将检查是否存在共线，并在识别出来之后从回归模型中删除任何多余的
X
列。由于包含
0
系数以及
0
se's，所以已删除的
X
列能在
LINEST
输出中被识别出来。如果一个或多个多余的列被删除，则将影响
df，原因是
df
取决于被实际用于预测目的的
X
列的个数。有关计算
df
的详细信息，请参阅下面的示例
4。如果由于删除多余的
X
列而更改了
df，则也会影响
sey
和
F
的值。实际上，共线应该相对很少发生。但是，很可能引起共线的情况是，当某些
X
列仅包含
0's
和
1's
作为一个实验中的对象是否属于某个组的指示器。如果
const
=
TRUE
或被删除，则
LINEST
可有效地插入所有
1's
的其他
X
列以便模型化截取。如果有一列，1
对应于每个男性的对象，0
对应于非男性对象，还有一列，1
对应于每个女性对象，0
对应于非女性对象，那么后一列就是多余的，原因是其中的项可通过从所有
1's（由
LINEST
添加）的另一列中减去“男性指示器”列中的项来获得。
df
的计算方法，如下所示（没有
X
列由于共线而从模型中被删除）：如果存在
known_x's
的
k
列和
const
=
TRUE
或被删除，那么
df
=
n
–
k
–
1。如果
const
=
FALSE，那么
df
=
n
-
k。在这两种情况下，每次由于共线而删除一个
X
列都会使
df
加
1。
对于返回结果为数组的公式，必须以数组公式的形式输入。
当需要输入一个数组常量（如
known_x's）作为参数时，以逗号作为同一行中数据的分隔符，以分号作为不同行数据的分隔符。分隔符可能因“区域设置”中或“控制面板”的“区域选项”中区域设置的不同而有所不同。
注意，如果
y
的回归分析预测值超出了用来计算公式的
y
值的范围，它们可能是无效的。
示例
1
斜率和
Y
轴截距如果您将示例复制到空白工作表中，可能会更易于理解该示例。 *** 作方法创建空白工作簿或工作表。
请在“帮助”主题中选取示例。不要选取行或列标题。
从“帮助”中选取示例
按
Ctrl+C。
在工作表中，选中单元格
A1，再按
Ctrl+V。
若要在查看结果和查看返回结果的公式之间切换，请按
Ctrl+`（重音符），或在“工具”菜单上，指向“公式审核”，再单击“公式审核模式”。
12345AB已知
y已知
x10945273公式公式=LINEST(A2:A5,B2:B5,,FALSE)
注释
示例中的公式必须以数组公式输入。在将公式复制到一张空白工作表后，选择以公式单元格开始的区域
A7:B7。按
F2，再按
Ctrl+Shift+Enter。如果公式不是以数组公式输入，则返回单个结果值
2。当以数组输入时，将返回斜率
2
和
y
轴截距
1。示例
2
简单线性回归如果您将示例复制到空白工作表中，可能会更易于理解该示例。 *** 作方法创建空白工作簿或工作表。
请在“帮助”主题中选取示例。不要选取行或列标题。
从“帮助”中选取示例
按
Ctrl+C。
在工作表中，选中单元格
A1，再按
Ctrl+V。
若要在查看结果和查看返回结果的公式之间切换，请按
Ctrl+`（重音符），或在“工具”菜单上，指向“公式审核”，再单击“公式审核模式”。
1234567AB月销售131002450034400454005750068100公式说明（结果）=SUM(LINEST(B2:B7,
A2:A7){9,1})估算第
9
个月的销售值
(11000)通常，SUM({m,b}{x,1})
等于
mx
+
b，即给定
x
值的
y
的估计值。也可以使用
TREND
函数。
123456789101112ABCDE底层面积
(x1)办公室的个数
(x2)入口个数
(x3)办公楼的使用年数
(x4)办公楼的评估值
(y)23102220142,00023332212144,000235631533151,00023793243150,00024022353139,00024254223169,000244821599126,00024712234142,90024943323163,00025174455169,00025402322149,000公式=LINEST(E2:E12,A2:D12,TRUE,TRUE)

A1:A10为Y值\x0d\B1:B10为X值\x0d\C1输入\x0d\=slope(A1:A10,B1:B10) 为斜率\x0d\D1输入\x0d\=intercept(A1:A10,B1:B10) 为截距

---