数组公式混淆

这个数组公式似乎有效:

=SUM((IFERROR(MATCH(A1:F1,I1:K1,0),0)>0)*A2:F2)

可能有多个更好的公式可以实现相同的目标。

但要谈谈这一点失败的原因:

这是因为 OFFSET function 返回引用而不是值。在这个数组公式中,它返回一个引用数组 {B2,D2,E2} 而不是值数组 {20,40,50} 这导致了问题。

如果您正在使用:

=SUMPRODUCT(OFFSET(A2,0,MATCH($I$1:$K$1,$A$1:$F$1,0)-1))

SUMPRODUCT({#VALUE,#VALUE,#VALUE})

在下一个到最后一个步骤中,结果为0。所以 OFFSET 导致错误值,因为它返回的引用数组不会自动取消引用,因此将成为 #VALUE 每个错误。

如果您正在使用

=SUMPRODUCT(N(OFFSET(A2,0,MATCH($I$1:$K$1,$A$1:$F$1,0)-1)))

然后它工作并返回110。所以 N 取消引用每个 抵消 所以整个公式得出了一个值数组 {20,40,50} 总之。

{=SUM(N(OFFSET(A2,0,MATCH($I$1:$K$1,$A$1:$F$1,0)-1)))}

同样有效。

这些问题是使用函数出现的,例如 抵消 和 INDIRECT ,它在数组公式中返回引用而不是值。并在 抵消 或 间接 解决问题。

这与:

=SUMPRODUCT(INDIRECT("R2C"&MATCH(I1:K1,A1:F1,0),FALSE))

对

=SUMPRODUCT(N(INDIRECT("R2C"&MATCH(I1:K1,A1:F1,0),FALSE)))