☰

索引/Sumproduct 计算错误

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
索引/Sumproduct 计算错误

我已经花了 24 小时来解决一个 Excel 问题。我有一个公式几乎可以实现我想要的功能，但它并没有返回我想象中应该返回的内容：

=INDEX(Data!D:D,SUMPRODUCT((Data!C:C=Total!A5)*(Data!A:A="Total")*(Data!B:B="Total")*(ROW(Data!D:D))))

我想象数据！工作表中索引 D 列的行是匹配发生的实际行，但对于除第一个值之外的每个值，返回的值都来自其下方的 3 行。

是的，因此对于我应用该公式的第一个单元格，我从 Data! 的 D 列中获取了正确的值。但是，对于每个后续使用该公式的单元格，我都会获取匹配项下方 3 行的 Data! 的 D 列中的值。

我尝试了从公式中减去 3 的每种组合（以返回正确的行），但无济于事。但这仍然无法解释为什么应用公式的第一个单元格会返回预期值！

当然，我们非常感谢任何帮助......

答案1

我不太清楚你是否想找到第一、第二等匹配 Total!A5 或者如果您尝试找到第一个匹配 A5 的，那么第一个匹配 A6 的，等等。此建议的解决方案适用于前者。

我更喜欢使用数学排除法来捕获匹配的多个结果。如果你排除不匹配的部分，那么剩下的一定是匹配的结果集。我也很不耐烦，觉得全列引用的计算滞后很烦人，SUMPRODUCT所以我把你的范围引用缩减到前 1K 行左右。

=IFERROR(INDEX(Data!$D$2:$D$999, SMALL(INDEX(ROW($1:$998)+((Data!$C$2:$C$999<>Total!$A$5)+(Data!$A$2:$A$999<>"Total")+(Data!$B$2:$B$999<>"Total"))*1E+99,,),ROW(1:1))),"")

这是一个标准（非数组) 公式。根据需要填写以捕获所有匹配的返回值。当您出于自己的目的转录此内容时，请记住这ROW(1:998)是位置在 Data!D2:D999，而不是工作表上的实际行号。ROW(1:1)只是一个随着您向下填充而递增的计数器。有时 aCOUNTIF更适合于此，可用于捕获多个查找值的多个匹配返回。

相关内容