excel - SUMPRODUCT 公式中的逻辑求值问题

Question

我使用的公式基于 SUMPRODUCT、SUBTOTAL 和 OFFSET。仅根据条件启用可见行的计数。我在一个简单的示例数据上进行了尝试，如下所示。数据从范围 B4:B12 header B3:

中的 B4 开始

B Column
HD
2
2
4
6
2
1
8
9
2

公式为:

=SUMPRODUCT((B4:B12=B4)*(SUBTOTAL(103,OFFSET(B4,ROW(B4:B12)-MIN(ROW(B4:B12)),0))))

对于 2 的值，它给出了 4 次计数的正确结果。我对公式进行了评估，以充分理解其逻辑。我可以理解其逻辑的主要部分，但某些步骤对我来说不太清楚。我将通过我的评论重现下面的评估步骤。

步骤-1

=SUMPRODUCT(({2;2;4;6;2;1;8;9;2}=2)*(SUBTOTAL(103,OFFSET(B4,ROW(B4:B12)-MIN(ROW(B4:B12)),0))))

确定

步骤-2

=SUMPRODUCT(({TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE})*(SUBTOTAL(103,OFFSET(B4,ROW(B4:B12)-MIN(ROW(B4:B12)),0))))

确定

第三步

=SUMPRODUCT(({TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE})*(SUBTOTAL(103,OFFSET(B4,ROW(B4:B12)-MIN(ROW(B4:B12)),0))))

确定

第四步

=SUMPRODUCT(({TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE})*(SUBTOTAL(103,OFFSET($B$4,{4;5;6;7;8;9;10;11;12}-MIN({4;5;6;7;8;9;10;11;12}),0))))

确定

第五步

=SUMPRODUCT(({TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE})*(SUBTOTAL(103,OFFSET($B$4,{4;5;6;7;8;9;10;11;12}-4),0))))

确定

第6步

=SUMPRODUCT({TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}*(SUBTOTAL(103,OFFSET($B$4,{0;1;2;3;4;5;6;7;8},0))))

为什么{0;1;2;3;4;5;6;7;8} ??

步骤7

=SUMPRODUCT({TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}*(SUBTOTAL(103,{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;})))

为什么{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;}？ ？步骤8

=SUMPRODUCT({TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}*({1;1;1;1;1;1;1;1;1}))

如何用 1 代替 #VALUE!步骤 9

=SUMPRODUCT({1;1;0;0;1;0;0;0;1})

确定

步骤-10

 4

确定

我对以下几点不太清楚

第6步:为什么{0;1;2;3;4;5;6;7;8}

第 7 步:为什么{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;}

第 8 步:如何用 1 代替 #VALUE!

希望有人帮助澄清这些提到的点背后的逻辑。请原谅我在如此琐碎的问题上询问清楚。

Answer 1

第 6 步:为什么{0;1;2;3;4;5;6;7;8}

因为 {4;5;6;7;8;9;10;11;12}-4 的计算结果为 {4-4;5-4;6-4 ;7-4;8-4;9-4;10-4;11-4;12-4} 即 {0;1;2;3;4;5;6;7 ;8}

第 7 步:为什么{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

公式计算器无法从通过 OFFSET($B$4,{0;1;2;3;4;5;6;7;8},0)< 获取的 9 个单元格引用中获取值 = {$B$​​4;$B$5;$B$6;$B$7;$B$8;$B$9;$B$10;$B$11;$B$12} 在数组中语境。但这并不重要，因为:

第 8 步:如何用 1 代替 #VALUE!

SUBTOTAL(103,... 是一个 COUNTA 小计，对于步骤 7 中获得的 9 个单元格引用中的每个单元格引用，如果满足，则计数 1不隐藏，否则为 0。因此单元格值是否被计算并不重要。

顺便说一句:使用同样可以实现

=SUMPRODUCT((B4:B12=B4)*(SUBTOTAL(103,INDIRECT("B"&ROW(B4:B12)))))

注释:这些公式是反复试验的结果。我怀疑任何 Excel 程序员是否能够预测他们实现的函数的所有用法。 Excel 函数的用法有很多，超出了他们最初的想象。

<小时/>

奖金:

=SUMPRODUCT(OFFSET(B4,ROW(B4:B12)-MIN(ROW(B4:B12)),0))

使用 B4:B12 中的值得出 0。

此处，公式计算器也无法从通过 OFFSET($B$4,{0;1;2;3;4;5;6;7;8},0 获取的 9 个单元格引用中获取值) = {$B$​​4;$B$5;$B$6;$B$7;$B$8;$B$9;$B$10;$B$11;$B$12}在数组上下文中。结果为 {#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}。但现在这很重要，因为我们需要值(value)观。

在这种情况下，我们可以使用 N 函数强制获取值

=SUMPRODUCT(N(OFFSET(B4,ROW(B4:B12)-MIN(ROW(B4:B12)),0)))

结果为 36，即 B4:B12 中的值的总和。