目标：在 Excel 中进行 Kappa-Sigma 剪辑
我想在 Excel 中实现选择性线性回归公式（避免使用 VBA）。我有一个需要应用线性回归的数据样本。但我的数据有一些结构异常值。我想使用卡帕-西格玛剪裁基本上，这只是进行线性回归，计算每个数据点的误差，如果某个数据点的误差较大（某个常数 [kappa] 乘以所有误差的标准差 [sigma]），则它将在下一次迭代中被拒绝。然后，再次对剪辑的数据集应用线性回归。
我目前拥有的
我有一张包含虚拟数据（y = 3 * x + 一些误差）的 Excel 表。三个数据点（x = 6、x = 10 和 x = 16）存在结构性（测量）误差。因此，我拟合数据（A25:C29），计算误差（D 列），并检查误差是否大于 kappa（1）乘以误差的标准差（B31）。结果在 E 列中。您可以看到，具有结构性误差的三个数据点被整齐地识别出来。
我被困的地方
我想再次对 x 和 y 进行线性回归没有应剪辑的数据点。在最理想的情况下，我想将条件（D2:D22<$B$31*$B$32）直接输入LINEST公式中，这样我就可以获得结果而无需使用中间列（现在在 E:G 中）。我很确定可以使用某些条件和数组函数来完成此操作，但我无法让它工作。
答案1
啊哈！这是你的怪物阵列公式：
{=LINEST(N(OFFSET(B2:B22, SMALL(IF(ABS(D2:D22)<=B31, ROW(B2:B22)-ROW(B2)), ROW(INDIRECT("1:"&COUNTIFS(D2:D22,"<="&B31,D2:D22,">=-"&B31)))), 0, 1)), N(OFFSET(A2:A22, SMALL(IF(ABS(D2:D22)<=B31, ROW(B2:B22)-ROW(B2)), ROW(INDIRECT("1:"&COUNTIFS(D2:D22,"<="&B31,D2:D22,">=-"&B31)))), 0, 1)))}
它看起来像评论中的一些内容，但我不得不对其进行一些调整。 主要的是评论用于ISNUMBER确定我们是否要使用一个值以及COUNT确定我们总共需要多少个值。 我将它们更改为error使用 来评估与 Std Dev 值相比的字段IF(D2:D22<=B31，然后使用 根据相同的标准对它们进行计数COUNTIF(D2:D22,"<="&B31)。
请注意，我根据您的工作表使用单元格引用编写了公式，因此如果这是一些模型并且您的实际工作表具有不同范围内的值，请小心。
2.798424149= 正确值（基于您剪切的范围）
2.798424149= 我的公式给出的值
2.825623377= 不忽略高误差值的公式给出的值
顺便说一句，您尝试的公式一直给出结果的原因0.329988513是您将 X 和 Y 引用颠倒了。我假设您从哪里复制了公式，其工作表的设置顺序与您自己的相反。
附加想法：如果您真的非常想要，您可以删除a*x+b和error字段，而其他字段除外，这样这个公式就可以调整为仍然有效。不过，您可能出于其他原因想要保留这些字段，在这种情况下，保持公式原样就可以了。
如果你想删除该error字段：
{=LINEST(N(OFFSET(B2:B22, SMALL(IF(ABS(B2:B22-C2:C22)<=B31, ROW(B2:B22)-ROW(B2)), ROW(INDIRECT("1:"&SUMPRODUCT(1*((B2:B22-C2:C22)<=B31)*((B2:B22-C2:C22)>=-B31))))), 0, 1)), N(OFFSET(A2:A22, SMALL(IF(ABS(B2:B22-C2:C22)<=B31, ROW(B2:B22)-ROW(B2)), ROW(INDIRECT("1:"&SUMPRODUCT(1*((B2:B22-C2:C22)<=B31)*((B2:B22-C2:C22)>=-B31))))), 0, 1)))}
如果你想放弃a*x+b 和田野error：
{=LINEST(N(OFFSET(B2:B22, SMALL(IF(ABS(B2:B22-(B24*A2:A22+C24))<=B31, ROW(B2:B22)-ROW(B2)), ROW(INDIRECT("1:"&SUMPRODUCT(1*((B2:B22-(B24*A2:A22+C24))<=B31)*((B2:B22-(B24*A2:A22+C24))>=-B31))))), 0, 1)), N(OFFSET(A2:A22, SMALL(IF(ABS(B2:B22-(B24*A2:A22+C24))<=B31, ROW(B2:B22)-ROW(B2)), ROW(INDIRECT("1:"&SUMPRODUCT(1*((B2:B22-(B24*A2:A22+C24))<=B31)*((B2:B22-(B24*A2:A22+C24))>=-B31))))), 0, 1)))}
蟹老板，您现在有这种感觉了吗？
答案2
我建议在右侧添加另外两列，以便以紧凑的形式提取您需要的值，末尾没有任何空格。使用您的示例，x 和 y 的公式将是：
=IFERROR(SMALL($F$2:$F$22,ROW()-1),NA())
=INDEX($G$2:$G$22,MATCH($H2,$F$2:$F$22,0))
这将提取所有未剪切的 x 和 y 值，中间没有任何空格，底部的额外单元格显示错误。然后，使用以下公式为 x 和 y 创建两个命名范围：
=OFFSET(Sheet1!$H$1,1,0,MATCH(MAX(Sheet1!$F$2:$F$22),Sheet1!$H$2:$H$22,0))
=OFFSET(Sheet1!$I$1,1,0,MATCH(MAX(Sheet1!$F$2:$F$22),Sheet1!$H$2:$H$22,0))
这将选择这两个紧凑部分中的所有记录，但不会选择底部的错误单元格。然后，您可以将这两个命名范围用于LINEST其他任何您喜欢的内容。
这会创建稍微大一点的电子表格，但它更容易调试，而且我总是更喜欢更短的公式而不是更短的电子表格。
