excel - 数组公式 : nested ifs and same row calculation

Question

有 2 个输入:A1和 B1 .

在 D 列中，有多种类型的对象 A。
在 B 列中，有多种类型的对象 B。

这是公式应该做的事情:
如果 (D2 是 'A1' 并且 G2 是 'B1') 那么，如果 (E2 大于 F2)，则减去 E2 和 F2(在本例中为 5 - 4)，否则将 F2 减去 E2(就像行中发生的那样12)。
如果没有匹配，什么也不做，直接跳过该行。

我想把它作为一个数组公式(Ctrl+Shift+Enter)来做，所以它最终会总结所有的东西。

在此示例中，输出将为 -1，因为 sum(and(5-4)(2-4)) 。

到目前为止，我有以下内容:

{=SUM(IF((D2:D12="A1")+(G2:G12="B1");E2:E12-F2:F12;0))} 

但它不能正常工作，因为我不确定 Excel 如何读取减法部分。我希望能够减去找到组合的行的值。

Answer 1

如果您只需要在所有匹配项中将 E 列减去 F 列，请考虑以下数组公式:

=SUM((D2:D12=$B$2)*(G2:G12=$B$3)*(E2:E12-F2:F12))

(如果需要，可以通过对要减去的内容进行额外检查来更新)

这将 SUM所有包含与您的输入匹配的减法(E 列)-(F 列)。

这是分割:
D2:D12=$B$2和 G2:G12=$B$3将生成包含 1 表示匹配和 0 表示不匹配的数组:

{A1,A2,A3, -,A1, -, -,A4,A5,A1,A1}  {B1, -,B1, -,B4, -, -,B6,B5,B2,B1}
 V           V              V  V     V     V                       V
{1, 0, 0, 0, 1, 0, 0, 0, 0, 1, 1 }  {1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1 }

E2:E12-F2:F12将产生一个由减去的值组成的第三个数组:

 {5, 5, 3, 1, 3, 3, 7, 3, 9, 7, 4}
-{4, 3, 4, 5, 6, 5, 9, 6, 7, 8, 2}
={1, 2,-1,-4,-3,-2,-2,-3, 2,-1, 2}

将它们全部相乘将得到如下结果:

 {1, 0, 0, 0, 1, 0, 0, 0, 0, 1, 1}
x{1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1}
x{1, 2,-1,-4,-3,-2,-2,-3, 2,-1, 2}
={1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2}

然后当然是 SUM会做它的工作:

SUM({1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2}) = 3