Excel - LINEST 函数

描述

LINEST 函数使用“最小二乘法”计算最佳拟合数据的直线统计数据，然后返回描述该直线的数组。

您还可以将 LINEST 与其他函数结合使用，以计算其他类型的线性未知参数模型的统计数据，包括多项式、对数、指数和幂级数。

由于此函数返回一个值数组，因此必须将其作为数组公式输入。

语法

LINEST (known_y's, [known_x's], [const], [stats])

参数

参数	描述	必填/可选
已知_y值	您已在关系式 y = mx + b 中知道的 y 值集合。如果已知_y值范围位于单列中，则已知_x值的每一列都被解释为一个单独的变量。如果已知_y值范围位于单行中，则已知_x值的每一行都被解释为一个单独的变量。	必填
已知_x值	您可能已在关系式 y = mx + b 中知道的 x 值集合。已知_x值范围可以包含一个或多个变量集。如果只使用一个变量，则已知_y值和已知_x值可以是任何形状的范围，只要它们具有相同的维度。如果使用多个变量，则已知_y值必须是向量（即，高度为一行或宽度为一列的范围）。如果省略已知_x值，则假定它与已知_y值大小相同的数组 {1,2,3,...}	可选
常数	指定是否强制常数 b 等于 0 的逻辑值。如果常数为 TRUE 或省略，则 b 按常规计算。如果常数为 FALSE，则 b 设置为 0，并且调整 m 值以适合 y = mx。	可选
统计数据	指定是否返回附加回归统计数据的逻辑值。如果统计数据为 TRUE，则 LINEST 返回附加回归统计数据。结果，返回的数组为 {mn, mn-1 ,..., m1, b; sen ,sen-1, ..., se1, seb; r2, sey; F, df; ssreg, ssresid}。如果统计数据为 FALSE 或省略，则 LINEST 仅返回 m 系数和常数 b。附加回归统计数据如以下表格所示。	可选

附加回归统计数据

序号	统计数据和说明
1	se1,se2,...,sen 系数 m1,m2,...,mn 的标准误差值。
2	seb 常数 b 的标准误差值 (当常数为 FALSE 时，seb = #N/A)。
3	r2 决定系数。比较估计的和实际的 y 值，其值范围为 0 到 1。如果为 1，则样本中存在完美的相关性——估计的 y 值与实际 y 值之间没有差异。在另一个极端，如果决定系数为 0，则回归方程对预测 y 值没有帮助。有关如何计算 r2 的信息，请参见下面的注释。
4	sey y 估计的标准误差。
5	F F 统计量或 F 观测值。使用 F 统计量确定因变量和自变量之间观察到的关系是否偶然发生。
6	df 自由度。使用自由度可以帮助您在统计表中查找 F 临界值。将您在表中找到的值与 LINEST 返回的 F 统计量进行比较，以确定模型的置信水平。有关如何计算 df 的信息，请参见下面的注释。
7	ssreg 回归平方和。
8	ssreg 残差平方和。有关如何计算 ssreg 和 ssresid 的信息，请参见下面的注释。

注释

线的方程式为：

y = mx + b

或

y = m1x1 + m2x2 + ... + b
如果存在多个 x 值范围，其中因变量 y 值是自变量 x 值的函数，则：
- m 值是对应于每个 x 值的系数，b 是一个常数值。
- 请注意，y、x 和 m 可以是向量。
LINEST 函数返回的数组为 {mn, mn-1… m1, b}。
LINEST 还可以返回其他回归统计数据
您可以使用斜率和 y 截距来描述任何直线：
- 斜率 (m)：
  
  要找到直线的斜率（通常写为 m），请取直线上的两点 (x1,y1) 和 (x2,y2)。斜率等于
  
  (y2 - y1)/(x2 - x1)。
- Y 截距 (b)：
  
  直线的 y 截距（通常写为 b）是直线与 y 轴相交点的 y 值。

直线的方程为 y = mx + b。一旦知道 m 和 b 的值，就可以通过将 y 值或 x 值代入该方程来计算线上的任何点。您还可以使用 TREND 函数。
当您只有一个自变量 x 时，您可以使用以下公式直接获得斜率和 y 截距值：
- 斜率：
  
  =INDEX(LINEST(已知_y值,已知_x值),1)
- Y 截距：
  
  =INDEX(LINEST(已知_y值,已知_x值),2)
LINEST 函数计算出的直线的精度取决于数据的分散程度。数据越线性，LINEST 模型就越准确。
LINEST 使用最小二乘法确定数据的最佳拟合。当您只有一个自变量 x 时，m 和 b 的计算基于以下公式：

$$m=\frac{\sum \left ( x-\bar{x} \right )\left ( y-\bar{y} \right )}{\sum \left ( x- \bar{x}\right )^2}$$

其中 x 和 y 是样本均值，即

x = AVERAGE(已知_x值)

y = AVERAGE(已知_y值)
线性和曲线拟合函数 LINEST 和 LOGEST 可以计算最适合数据的最佳直线或指数曲线。但是，您必须确定哪两个结果最适合您的数据。您可以为直线计算 TREND(已知_y值,已知_x值)，或为指数曲线计算 GROWTH(已知_y值,已知_x值)。这些函数（省略已知_x值参数）返回沿该直线或曲线在实际数据点预测的一组 y 值。然后，您可以将预测值与实际值进行比较。您可能希望将两者都绘制成图表以进行视觉比较。
在回归分析中，Excel 为每个点计算估计点与其实际 y 值之间的平方差。这些平方差之和称为残差平方和，ssresid。然后，Excel 计算总平方和，sstotal。当常数参数 = TRUE 或省略时，总平方和是实际 y 值与 y 值平均值之间的平方差之和。
当常数参数 = FALSE 时，总平方和是实际 y 值的平方和（不从每个单独的 y 值中减去平均 y 值）。然后，可以从以下公式中找到回归平方和，ssreg：ssreg = sstotal - ssresid。残差平方和与总平方和相比越小，决定系数 r2 的值就越大，r2 是回归分析得到的方程解释变量之间关系好坏的指标。r2 的值等于 ssreg/sstotal。
在某些情况下，一个或多个 X 列（假设 Y 和 X 在列中）在其他 X 列存在的情况下可能没有额外的预测值。即，消除一个或多个 X 列可能会导致预测的 Y 值同样准确。在这种情况下，应从回归模型中省略这些冗余的 X 列。这种现象称为“共线性”，因为任何冗余的 X 列都可以表示为非冗余 X 列倍数的总和。
LINEST 函数检查共线性，并在识别冗余的 X 列时将其从回归模型中删除。删除的 X 列在 LINEST 输出中可以识别为除了 0 se 值之外还具有 0 系数。如果一个或多个列被删除为冗余，则 df 会受到影响，因为 df 取决于实际用于预测目的的 X 列的数量。
如果由于删除了冗余的 X 列而更改了 df，则 sey 和 F 的值也会受到影响。在实践中，共线性应该相对较少见。但是，更有可能出现共线性的一种情况是，某些 X 列仅包含 0 和 1 值，作为实验对象是否属于特定组的指示符。如果 const = TRUE 或被省略，则 LINEST 函数有效地插入一个包含所有 1 值的附加 X 列来模拟截距。
当存在 k 列已知_x值并且由于共线性没有从模型中删除任何 X 列时，df 的值如下计算：
- 如果常数 = TRUE 或省略，则 df = n – k – 1
- 如果常数 = FALSE，则 df = n – k
在这两种情况下，由于共线性而删除的每个 X 列都会使 df 的值增加 1。
当输入数组常量（如已知_x值）作为参数时，请使用逗号分隔同一行中包含的值，并使用分号分隔行。分隔符字符可能因您的区域设置而异。
请注意，如果回归方程预测的 y 值超出了用于确定方程的 y 值范围，则这些值可能无效。
LINEST 函数中使用的底层算法与 SLOPE 和 INTERCEPT 函数中使用的底层算法不同。当数据未确定且共线性时，这些算法之间的差异会导致不同的结果。
除了使用LOGEST计算其他回归类型的统计数据外，还可以使用LINEST通过将x和y变量的函数作为LINEST的x和y序列来计算一系列其他回归类型。例如，以下公式：

=LINEST (y值, x值^COLUMN($A:$C))

当您有一列y值和一列x值需要计算三次（3阶多项式）近似值时，此公式有效：

y = m1*x + m2*x^2 + m3*x^3 + b

您可以调整此公式来计算其他类型的回归，但在某些情况下，它需要调整输出值和其他统计数据。
LINEST函数返回的F检验值与FTEST函数返回的F检验值不同。LINEST返回F统计量，而FTEST返回概率。
如果已知x数组的长度与已知y数组的长度不同，LINEST将返回#REF!错误值。
如果提供的已知x或已知y数组中的任何值不是数字（这可能包括数字的文本表示形式，因为LINEST函数不将这些识别为数字），LINEST将返回#VALUE!错误值。
如果const或stats参数都不能计算为TRUE或FALSE，LINEST将返回#VALUE!错误值。

适用性

Excel 2007、Excel 2010、Excel 2013、Excel 2016

示例

advanced_excel_statistical_functions.htm

打印页面