excel - SUMIF 不包括隐藏行。小计？总和？

Question

我有 2 个大型事件表。第一个表是一个数据表 (tblData)，其中包含日期、持续时间和代码(用于标识事件)。第二个表是每个事件花费的总时间的汇总表 (tblSummary)。

如何筛选 tblData并更新 tblSummary ?

以下是tblData的一小部分.请注意，2 级代码有 3 列(L2a、L2b、L2c)；这意味着如果我正在寻找 2 级代码“EL”的总持续时间，我必须检查所有 3 列并添加它们的 Duration次。仅给出下图，总“EL”时间将为9.9


下面是一段tblSummary .



对于Total专栏，我用了SUMIF (tblData[L2a], [@[Lvl 2 Code]], tblData[Duration])获取代码的总时间，但这包括隐藏行。 tblSummary当我更改 tblData 上的过滤器时不更新.

我相信使用 SUBTOTAL是死胡同。这将解决隐藏列问题，但由于无法设置标准，我无法将其应用于 tblSummary .

我是 SUMPRODUCT 的新手但我确实尝试使用
=SUMPRODUCT(SUBTOTAL(109,tblData[Duration]),--(tblData[L2a]="EL"))
但收到#VALUE! .我确实按 Ctl + Shift + Enter .

Answer 1

将 SUMIF function 耦合在一起的列值条件同时忽略隐藏行意味着我们必须在 SUBTOTAL function 上强制执行额外的计算层。并通过 SUMPRODUCT function 的循环计算收集聚合.额外的“层”处理将通过OFFSET function¹ 完成。 .



第一行的标准公式tbl总结 structured table 总计 列(又名 K15)是，

=SUMPRODUCT((SUBTOTAL(103, OFFSET(tblData, ROW(tblData)-ROW(INDEX(tblData, 1, 0)), 0, 1, 1))=1)*(tblData[L2a]=[@[Lvl 2 Code]]), tblData[Duration])

如果表格不为您执行此操作，请填写。

当我们过滤 数据 table 日期列隐藏 2015 年 11 月 24 日、2015 年 11 月 27 日和 2015 年 11 月 28 日，新的总数显示在 tbl总结 table 。



¹ 可变函数会在整个工作簿中的任何内容发生变化时重新计算，而不仅仅是在影响其结果的某些内容发生变化时重新计算。 的示例 volatile 函数是 INDIRECT , OFFSET , TODAY , NOW , RAND和 RANDBETWEEN . CELL的一些子功能和 INFO工作表函数也会使它们不稳定。