如果您知道其中的窍门,这将变得“微不足道”。
要将 LINEST 与缺失值结合使用,您需要照常创建 X 矩阵(r 行 x c 列)和 Y 向量(r 行 x 1 列)。您还需要在 X 矩阵中创建一个附加列作为指示变量。将此列放置在 X 矩阵的左侧。因此,如果 X 矩阵从 B 列开始,请将附加列放入 A 列。对于要省略的每一行,将此指示符值设置为零。对于要包含的每一行,将此指示符值设置为 1。将 X 矩阵和 Y 向量中的每隔一列乘以该指示变量。将这个新的增广 X 矩阵和新的 Y 向量放在电子表格的其他位置。现在,您应该有一个新的 X 矩阵(r 行 x c+1 列)和 Y 向量,其中每行要省略的行都包含直行的零。这很关键!
现在像平常一样使用 LINEST 函数,指定整个 Y 向量和扩展的 r×(c+1) X 矩阵(其中包含指标列作为前两个函数参数,“False”(即零)为第三个参数和“TRUE”(即一)或“FALSE”(即零)作为第四个函数参数。正确的参数估计值出现在 LINEST 输出的第一行中。所有其他 LINEST 输出值如果您指定“TRUE”来获取统计信息,则除了第五行和第二列中的值(残差平方和)之外,其他值都是错误的。
如果将第四个函数参数指定为“TRUE”以获取统计信息,则需要更正输出中的不正确值。扩展输出的第 2,3 和 4 行中的值不正确;第 5 行第 1 列中的值也不正确。你需要修复它们。
将 LINEST 输出的第一行复制到工作表中的其他位置。如果您为统计数据指定“TRUE”,则在此副本下方保留四个空白行。将第 5 行第 2 列值从原始 LINEST 输出复制到新输出空间的第 5 行第 2 列
第一步:计算正确的自由度数以替换 LINEST 输出第 4 行第 2 列中的值。求模型中参数的数量;这是c+1。您可以使用 COUNT 函数来计算扩展 X 矩阵中的列数。接下来将 X 矩阵的指标列中的所有值相加。假设四行的值全部为零。使用 SUM 函数:这给出 r – 4 = 指示符列中包含“1”的行数。正确的自由度是差值:SUM(指标列)- COUNT(增强的 X 矩阵列)。这是应放置在新输出空间的第 4 行第 2 列中的值。
第二步:修复第2行和第3行第2列。划分错误的d.f。 (第 4 行第 2 列)在原始 LINEST 输出中由正确的 d.f. (第 4 行第 2 列)在新的输出空间中。取该商的平方根。将原始 LINEST 输出空间中第 2 行和第 3 行第 2 列的值乘以该校正因子,即可得到参数的正确标准误和 Y 的正确标准误。
第三步:修正回归平方和。由于未对输出第 5 行第 1 列的平均值进行回归校正,原始 LINEST 输出具有平方和值;我们希望根据平均值校正回归平方和。我们需要计算平均值的校正。这是 Y 向量值的平方和除以指标列值的总和。从原始 LINEST 输出的第 5 行第 1 列中的值减去该值,并将答案放入新输出空间的第 5 行第 1 列中。
第四步:修正第 4 行第 1 列中的 F 比。我们需要计算由于回归和残差而产生的均方。回归产生的均方(F 比率中的分子)是新输出空间第 5 行第 1 列中的值除以 c(增强前原始 X 矩阵中的列数)。残差产生的均方(F 比中的分母)是新输出空间的第 5 行第 2 列除以新输出空间的第 4 行第 2 列。根据这两个中间值计算 F 比,并将结果放入新输出空间的第 4 行第 1 列中。
第五步:使用新输出空间中的值,更正第 3 行第 1 列中的 R 方值。这是 1 -(第 5 行第 2 列除以第 5 行第 1 列和第 5 行第 2 列之和)。
检查您的工作:在电子表格的其他位置复制增强的 X 矩阵和 Y 向量。对于指示符变量中为零的行,将所有条目替换为零。删除带有零的行中的所有单元格,并将单元格向上移动。您现在应该有一个行数较少但没有缺失值的 X 矩阵和 Y 向量。删除指标栏。现在使用 LINEST 对这组减少的数据建立回归模型,但这次将第三个参数设置为 TRUE(包括一个常量)。这些结果应该与新输出空间中的结果相同。