WorksheetFunction.LinEst(Object, Object, Object, Object) 方法

定义

命名空间:

Microsoft.Office.Interop.Excel

程序集:

Microsoft.Office.Interop.Excel.dll

使用最小二乘法对已知数据进行最佳直线拟合，并返回描述此直线的数组。 因为此函数返回数值数组，所以必须以数组公式的形式输入。

public object LinEst (object Arg1, object Arg2, object Arg3, object Arg4);

Public Function LinEst (Arg1 As Object, Optional Arg2 As Object, Optional Arg3 As Object, Optional Arg4 As Object) As Object

参数

Arg1

Object

Known_y's - 关系表达式 y = mx + b 中已知的 y 值集合。

Arg2

Object

Known_x's - 关系表达式 y = mx + b 中可能已知的可选 x 值集合。

Arg3

Object

Const - 一个逻辑值，用于指定是否强制常量 b 等于 0。

Arg4

Object

Stats - 一个逻辑值，指定是否返回附加回归统计值。

返回

Object

注解

直线的公式为：

y = mx + b 或者

y = m1x1 + m2x2 + ... + b（如果有多个区域的 x 值）

式中，因变量 y 是自变量 x 的函数值。 M 值是与每个 x 值相对应的系数，b 为常量。 注意 y、x 和 m 可以是向量。 LinEst 返回的数组为 {mn，mn-1,...,m1，b}。 LinEst 还可以返回其他回归统计信息。

如果数组 known_y's 在单独一列中，则 known_x's 的每一列被解释为一个独立的变量。

如果数组 known_y's 在单独一行中，则 known_x's 的每一行被解释为一个独立的变量。

数组 known_x's 可以包含一组或多组变量。 如果只用到一个变量，只要 known_y's 和 known_x's 维数相同，它们就可以是任何形状的区域。 如果用到多个变量，则 known_y's 必须为向量（即必须为一行或一列）。

如果省略 known_x's，则假定该数组为 {1,2,3,...}，其大小与 known_y's 相同。

如果 const 为 true 或省略，则正常计算 b。

如果 const 为 false，则 b 设置为 0，并将 m 值调整为适合 y = mx。

如果统计信息为 truuue，LinEst 将返回其他回归统计信息，因此返回的数组为 {mn，mn-1,...,m1，b; sen，sen-1,...,se1，seb;r2，sey;F，df;ssreg，ssresid}。

如果统计信息为 false 或省略， LinEst 仅返回 m 系数和常量 b。

附加回归统计值如下：

se1，se2,...,sen	系数 m1,m2,...,mn 的标准误差值。
Seb	当 const 为 false 时，常量 b (seb = #N/A 的标准错误值) 。
R 2	判定系数。 Y 的估计值与实际值之比，范围在 0 到 1 之间。 如果为 1，则样本有很好的相关性，Y 的估计值与实际值之间没有差别。 如果判定系数为 0，则回归公式不能用来预测 Y 值。
sey	y 估计值的标准误差。
F	F 统计或 F 观察值。 使用 F 统计可以判断因变量和自变量之间是否偶尔发生过可观察到的关系。
Df	自由度。 用于在统计表上查找 F 临界值。 将表中的值与 LinEst 返回的 F 统计信息进行比较，以确定模型的置信度级别。
ssreg	回归平方和。
ssresid	残差平方和。

下面的图示显示了附加回归统计值返回的顺序。

图 1：工作表

可以使用斜率和 y 截距来描述任何直线：

斜率 (m) ：

若要查找通常写为 m 的直线的斜率，请在直线上取两个点， (x1，y1) ， (x2，y2) ：斜率等于 (y2 - y1) / (x2 - x1) 。

Y-intercept (b) ：

线条的 y 截距（通常写为 b）是该直线与 y 轴交叉点处的 y 值。

直线的公式为 y = mx + b。 知道 m 和 b 的值后，可以通过将 y 值或 x 值插入到该公式中来计算直线上的任意点。 还可以使用 Trend(Object, Object, Object, Object) 函数。

如果只有一个独立的 x 变量，则可以使用以下公式直接获取斜率和 y 截距值：

边坡：

=INDEX (LINEST (known_y，known_x 的) ,1)

Y 截距：

=INDEX (LINEST (known_y，known_x 的) ,2)

LineEst 计算的线条的准确性取决于数据中的散点程度。 数据线性越长， LineEst 模型越准确。 LineEst 使用最小二乘法来确定数据的最佳拟合度。 如果只有一个独立的 x 变量，则 m 和 b 的计算基于以下公式：

图 2：公式

图 3：公式

其中 x 和 y 是样本平均值，即 x = AVERAGE (已知 x 的) ，y = AVERAGE (known_y 的) 。

直线和曲线拟合函数 LineEst ， LogEst(Object, Object, Object, Object) 可以计算适合数据的最佳直线或指数曲线。 但是，你必须确定两个结果中哪一个最适合你的数据。 可以计算直线的 TREND (known_y、known_x) 或指数曲线的 GROWTH (known_y known_x) 。 这些函数在没有new_x参数的情况下，返回沿实际数据点沿该直线或曲线预测的 y 值的数组。 然后，可以将预测值与实际值进行比较。 你可能想要为两者绘制图表，以便进行视觉比较。

回归分析时，Microsoft Excel 计算每一点的 y 的估计值和实际值的平方差。 这些平方差之和称为残差平方和 (ssresid)。 然后 Microsoft Excel 计算总平方和 (sstotal)。 当 const = true 或省略时，平方的总和是实际 y 值与 y 值的平均值之间的平方差之和。 当 const = false 时，平方和是实际 y 值 (的平方和，而不从每个 y 值) 中减去平均 y 值。 回归平方和 (ssreg) 可通过公式 ssreg = sstotal - ssresid 计算出来。 残差平方和与总平方和的比值越小，判定系数 r2 的值就越大，r2 是表示回归分析公式的结果反映变量间关系的程度的标志。 r2 等于 ssreg/sstotal。

某些情况下，一个或多个 X 列（假设 Y's 和 X's 位于列中）在有其他 X 列的情况下可能没有附加的预测值。 换句话说，删除一个或多个 X 列可能并不影响 Y 预测值的精度。 在这种情况下，应该从回归模型中略去这些冗余的 X 列。 这种现象被称为“共线性”，因为任何冗余的 X 列都可以表示为多个非冗余的 X 列的和。 LinEst 检查共线，并在识别出它们时从回归模型中删除任何冗余的 X 列。 删除的 X 列可在 LinEst 输出中识别为系数为 0 和 0 se。 如果一个或多个列被作为冗余列删除，则 df 会受到影响，因为 df 取决于实际用于预测的 X 列的个数。 如果由于删除冗余的 X 列而改变了 df，则 sey 和 F 的值也会受到影响。 实际上，共线性应该相对稀有。 但是，很有可能出现共线性的一种情况是，某些 X 列仅包含 0's 和 1's 作为某个实验中的对象是否属于某个组的指示器。 如果 const = true 或省略， LinEst 会有效插入所有 1 的附加 X 列来为截距建模。 如果有一个列（如果为男性）为 1，则为 0;如果不是，则每个主题都有一个为 1 的列;如果为女性，则为 0，则后一列是多余的，因为可以通过从 LineEst 添加的所有 1 的附加列中的条目中减去“男性指示器”列中的条目来获取。

当由于共线性而没有从模型中删除 X 列时，df 的计算方式如下：如果存在known_x 且 const = true 或省略的 k 列，则 df = n – k – 1。 如果 const = false，则 df = n - k。 在这两种情况下，只要因共线性删除一个 X 列，df 就会加 1。

对于返回结果为数组的公式，必须以数组公式的形式输入。 当输入一个数组常量（如 known_x's）作为参数时，用逗号来分隔同一行中的值，用分号来分隔不同的行。 分隔符可能因“控制面板”中的“区域和语言选项”中区域设置的不同而有所不同。

注意，如果 y 的回归分析预测值超出了用来计算公式的 y 值的范围，它们可能是无效的。 LinEst 函数中使用的基础算法不同于 和 Intercept(Object, Object) 函数中使用的Slope(Object, Object)基础算法。 这些算法间的差异可能在共线数据不确定时导致不同的结果。 例如，如果 known_y's 参数的数据点个数为 0，而 known_x's 参数的数据点个数为 1：

LineEst 返回值 0。 LinEst 算法旨在返回共线数据的合理结果，在这种情况下，至少可以找到一个答案。 Slope(Object, Object) 并 Intercept(Object, Object) 返回 #DIV/0！ 错误。 Slope(Object, Object)和 Intercept(Object, Object) 算法旨在查找一个且仅一个答案，在这种情况下，可以有多个答案。