SUMIF 排除隐藏行.小计?总和? [英] SUMIF excluding hidden rows. SUBTOTAL? SUMPRODUCT?

问题描述

我有 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]) 来获取总时间代码，但这包括隐藏的行.当我更改 tblData 上的过滤器时，tblSummary 不会更新.

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

我是 SUMPRODUCT 的新手，但我确实尝试过使用

=SUMPRODUCT(SUBTOTAL(109,tblData[Duration]),--(tblData[L2a]="EL"))

但收到了#VALUE!.我确实按了 Ctl + Shift + Enter.

解决方案

将

tblSummary 第一行的标准公式

<小时>

¹ _{Volatile 函数会在整个工作簿中的任何内容发生变化时重新计算，而不仅仅是当影响其结果的某些内容发生变化时.volatile 函数的示例是 间接，偏移, 今天，现在, 兰德 和 RANDBETWEEN.CELL<的一些子功能/a> 和 INFO 工作表函数也会使它们变得不稳定.}

I have 2 massive tables of events. The first table is a data table (tblData) that has dates, durations, and codes (to identify the event). The second table is a summary table (tblSummary) of the total time each event took.

How can I filter tblData and update tblSummary?

Below is a small part of tblData. Notice there are 3 columns for Level 2 codes (L2a,L2b,L2c); meaning if I am looking for total duration of Level 2 Code "EL", I have to check all 3 columns and add their Duration times. Given just the picture below, total "EL" time would be 9.9

Below is a piece of tblSummary.

For the Total column, I used SUMIF (tblData[L2a], [@[Lvl 2 Code]], tblData[Duration]) to get the total time of a code but this includes hidden rows. tblSummary does not update when I change the filter on tblData.

I believe using SUBTOTAL is a dead end. This would solve the hidden columns issue, but since there is no way to set criteria I cannot apply it to tblSummary.

I am new to SUMPRODUCT but I did try to use

=SUMPRODUCT(SUBTOTAL(109,tblData[Duration]),--(tblData[L2a]="EL"))

but received #VALUE!. And I did press Ctl + Shift + Enter.

解决方案

Coupling together the SUMIF function's column value condition while ignoring hidden rows means we have to force an extra layer of calculation on a SUBTOTAL function and collect the aggregate with the cyclic calculation of the SUMPRODUCT function. The extra 'layer' of processing will be accomplished through the OFFSET function¹.

    

The standard formula in the first row of the tblSummary structured table Total column (aka K15) is,

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

Fill down if the table does not perform this for you.

When we filter the tblData table's Date column to hide 24-Nov-2015, 27-Nov-2015 and 28-Nov-2015, the new totals show in the tblSummary table.

    

---

¹ _{Volatile functions recalculate whenever anything in the entire workbook changes, not just when something that affects their outcome changes. Examples of volatile functions are INDIRECT, OFFSET, TODAY, NOW, RAND and RANDBETWEEN. Some sub-functions of the CELL and INFO worksheet functions will make them volatile as well.}

这篇关于SUMIF 排除隐藏行.小计?总和?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！