excel - 需要 Excel SUBTOTAL 以有条件地使用来自不同列的总和

Question

我有一个相当简单的三列 Excel 2007 工作表；名称(A 列)、Value1(B 列)和 Value2(C 列)。此工作表通常会按名称列自动过滤。

在第 2 列的底部，我想执行 B 列的条件小计，遵守任何过滤器，规则如下:如果对于给定的行，B 列为 0，则使用 C 列中该行的值, 否则，应忽略 C 列。

      A         B         C       
1   Name     Value1    Value2
2  Bob         100         6   <-- use 100, ignore 6 in Col C because B is non-zero
3  Bob         200             <-- use 200
4  Bob           0        50   <-- Col B=0, use C, thus 50 (and so on)
5  Bob         300
6  Ralph        10
7  Ralph        20         1   <-- use 20 from col B, ignore col 6.
8  Ralph         0        60
9  Ralph        50
10 Mary       1000
11 Mary       1200
12 Mary          0       250
13 Mary       1040       
14 Subtotal   4280

现在，我可以用公式得到我想要的总值:

=产品总和(--(B2:B13=0),C2:C13)+小计(9,B2:B13)

但是使用 SUMPRODUCT 会阻止它满足隐藏/过滤单元格的要求，例如，过滤掉 Mary 不会导致总数下降 3690。并且 SUBTOTAL 不能使用 (B2:B13=0) 数组引用。所以我大脑的很大一部分试图告诉我我可以接近，但真的无法到达那里。但我(也？)固执地想很快放弃 :)

我的一部分是想说如果不深入研究 VBA 就不能直接完成，对于此解决方案我希望尽可能避免。我认为值得就此问题征求其他人的意见，以努力指出一个我只是看不到的(可能非常简单的)解决方案。

编辑 1: 我应该指出的一个明显的解决方案是简单地添加带有简单 IF 表达式的第四列，然后对其进行小计。这当然有效，但我无法适应这种情况。此处所示的表格只是从一个更大的、高度结构化的工作表中提取的代表性摘录，因此不能添加任意列。

编辑 2: 我提供的样本数据允许一个简单的推断，即所有列的直接小计可以解决问题，但从中提取该数据的真实数据可能包括值对于 C 列，即使 B 列不为零。在这种情况下，必须忽略 C 列 - B 列(如果存在)始终优先。我已经相应地修改了原始数据表。

Answer 1

要用公式做到这一点，诀窍是使用 OFFSET 返回“范围数组”，每个范围一个单元格，然后我们可以使用 SUBTOTAL 来单独查询每个单元格，即使用这个“数组公式”

编辑 - 根据 Daniel Otykier 的评论 - 最初没有看到......

=SUM(IF(SUBTOTAL(2,OFFSET(B2,ROW(B2:B13)-ROW(B2),0)),IF(B2:B13=0,C2:C13,B2:B13 )))

使用 CTRL+SHIFT+ENTER 确认

假设 B2:B13 已按照给定的示例完全填充

编辑:你也可以使用这个不需要“数组输入”的版本

=SUMPRODUCT(SUBTOTAL(9,OFFSET(C2,ROW(C2:C13)-ROW(C2),0)),(B2:B13=0)+0)+SUBTOTAL(9,B2: B13)

SUBTOTAL/OFFSET 的这种用法是由 Laurent Longre 开发的 - 参见 here