利用ecel进行线性回归分析

1、LINEST 函数通过使用“最小二乘法”计算最符合您的数据的直线来计 算直线的统计值，并返回描述该直线的 数组 。因为此 函数返回数值数组，所以必须以 数组公式 的形式输入直线的公式为 y = mx + b 或 y = m1x1 + m2x2 + . + b (如果 x 值包含多个区域)，其中因变量 y 值是自变量 x值的函数。m值是与每个x指数值相对应的系数，b 为常量值。请注意， y、x 和 m 可以是向量。 LINEST 函数返回的数组为 m n,m n-1,.,m 1,b。 LINEST 函数 还会返回附加回归统计值。语法LINEST(known_ys, known_xs, const

2、, stats )以下图示显示了附加回归统计值返回的顺序。可以使用斜率和 y 轴截距描述任何直线：如果需要计算斜率(通常记为m),则选取直线上 的两点(x1,y1)和(x2,y2)；斜率等于(y2 - y1)/(x2 - x1)。直线的y轴截距(通常记为b)为直线与y轴交点 的 y 值。 .直线的公式为 y = mx + b 。如果知道 m 值和 b 值， 可以将 y 值或 x 值代入公式来计算直线上的任意点 还可以使用 TREND 函数。如果只有一个自变量X,可以使用以下公式直接算出斜率和 y 轴截距值：计算斜率,使用 =INDEX(LINEST(known_ys, known_xs), 1

3、 )计算 Y 轴截距,使用 =INDEX(LINEST(known_ys, known_xs), 2 ) LINEST 计算出的直线的精确度取决于数据的离散 程度。数据越接近线性, LINEST 模型就越精确。 LINEST 使用最小二乘法确定最适合数据的直线。只有一个自变量 X 时, m 和 b 可根据以下公式算出m =其中 X 和 y 是样本平均值,即, X = AVERAGE(known xs) 和 y = AVERAGE(known_ys)。 直线拟合和曲线拟合函数 LINEST 和 LOGEST 可计 算最适合您的数据的最佳直线或指数曲线。但是, 您必须确定两个结果中哪个最适合您的数

4、据。可以 使用 TREND(known_ys, known_xs ) 计算直线, 或使用 GROWTH(known_ys, known_xs ) 计算 指数曲线。这些不含new_xs参数的函数，返冋根 据您的实际数据点、沿着直线或曲线计算的一组 y 值。然后可以比较计算出的值与实际值。也可以通 过图表方式直观地进行比较。在冋归分析中，Excel计算每个点的y计算值与实 际值之间的平方差。这些平方差之和称为残差平方 和ss resid。然后，Excel计算总平方和ss total。 当const = TRUE或被省略时，总平方和为y的实 际值与平均值的平方差之和。当 const = FALSE

5、时 总平方和为y的实际值的平方和（不需要从每个y 值中减去平均值）。冋归平方和 ss reg 可通过公式 算出：ss reg = ss total - ss resid。残差平方和与总 平方和的比值越小，判定系数 r 2的值就越大，该 系数是表示冋归分析公式的结果反映变量之间关系 的程度的指标。 r 2 = ss reg/ss total。 在某些情况下，一个或多个 x 列（假设 ys 和 xs位于列中）可能没有其他x列中出现的计算值。换 言之，删除一个或多个x列可能不会影响y计算值 的精确度。在这种情况下，这些多余的x列应该从 冋归模型中删除。这种现象称为“共线”，因为任何 多余的x列可表示

6、为多个非多余x列之和。 LINEST将检查是否存在共线，并在识别出之后从 冋归模型中删除任何多余的x列。由于包含0系数 和0 ses，因此LINEST输出中能够识别已删除的x 列。如果一个或多个多余的列被删除，则将影响 d f,因为d f取决于实际用于计算目的的x列的数 量。有关计算d f的详细信息，请参阅下面的示例 4。如果由于删除多余的x列而使d f发生更改，则 还会影响 se y 和 F 的值。实际应用中,共线应相对 极少发生。但是，当某些x列仅包含0s和1s作 为实验中的对象是否属于某个特定组的指标时,很 可能引起共线。如果 const = TRUE 或被删除，则 LINEST将有效地插入所有1s的其他x列以便模 拟截距。如果某列中的 1 对应每个男性对象，0 对 应非男性对象，而另一中的 1 对应每个女性对象， 0 对应非女性对象，则后一列为多余列，因为其中 的项可通过从所有1s （由LINEST添加）的另一 列中减去“男性标志”列中的项来获得。.如果x列由于共线而未从模型中删除，则df的计 算公式如下：如果 known_xs 存在 k 列且 const = TRUE 或忽略，那么 d f = n - k - 1。如果 co