excel - 忽略 Excel 公式中的隐藏表行

Question

晚上好。

我试图让我的公式忽略过滤表中的隐藏行。我尝试了此处显示的一些技巧，但没有成功。

我从 BigFix(网络管理工具)获得的 CSV 看起来像:

应用程序列列出了同一单元格中的应用程序。因此，当我执行计数函数来识别问题时，我必须使用通配符(我正在不同的 tab 中搜索结果)。

=COUNTIF('Input Data'!C:C,"*"&Results!A2&"*")

我希望能够在第一个选项卡上过滤表格，并让公式结果在第二个选项卡上准确显示。

有什么想法吗？

更新:我觉得更多信息会有帮助。我希望“结果”选项卡自动更新的原因是我计划根据该选项卡上的信息构建图表。我希望能够过滤“输入数据”中的表格以仅包含部门 A 并让图表自动更新为部门 A 的信息。

我正在使用的 Excel 文件有多达一千个条目，我正在尝试获取可以复制/粘贴的图表，以尽可能高效地放入演示文稿中。

Answer 1

=SUMPRODUCT(SUBTOTAL(3,OFFSET('Input Data'!C:C,ROW('Input Data'!C:C)-MIN(ROW('Input Data'!C:C)),,1))*(ISNUMBER(SEARCH(A2,'Input Data'!C:C))))

在上面的公式中，将 C:C 限制为您的实际数据范围或使用命名范围来标识实际数据范围。

Edit

以上公式应替换为 103，而不是 Jeep 指出的 3

=SUMPRODUCT(SUBTOTAL(103,OFFSET('Input Data'!C:C,ROW('Input Data'!C:C)-MIN(ROW('Input Data'!C:C)),,1))*(ISNUMBER(SEARCH(A2,'Input Data'!C:C))))

在上面的公式中，将 C:C 限制为您的实际数据范围或使用命名范围来标识实际数据范围。

Adding Explanation based on OP's Request.

在上面的公式中使用了 Sumproduct() Subtotal() Offset() Min() Row() IsNumber() Search() 函数组合来达到您的预期结果。

Row() - 将获取给定范围的行号

Min() - 将获取给定数字的最小值

Offset() - 用于将引用重定向到给定范围的每个单元格。

Subtotal() - 用于查找重定向引用的(未)隐藏状态。

Search() - 用于查找给定范围内的特定文本(不区分大小写)，将导致数字或错误。

IsNumber() 用于检查搜索是否返回数字或错误。因此 Isnumber 将返回 bool 值 True/False 作为结果。

C 列 D 列

过滤数据

一个1

b 1

一个1

一个2

一个2

以上数据从第一行开始，标题位于第一行。假设我用 1 过滤了 D 列。使用下面的公式将得到 2。

=SUMPRODUCT(SUBTOTAL(103,OFFSET('输入数据'!C2:C6,ROW('输入数据'!C2:C6)-MIN(ROW('输入数据'!C2:C6)),,1 ))*(ISNUMBER(SEARCH("a",'输入数据'!C2:C6))))

ROW('输入数据'!C2:C6) = {2;3;4;5;6}

MIN(ROW('输入数据'!C2:C6)) = 2

ROW('输入数据'!C2:C6)-MIN(ROW('输入数据'!C2:C6)) 应解读为{2;3;4;5;6}-2 = {0;1;2;3;4}

OFFSET('输入数据'!C2:C6,ROW('输入数据'!C2:C6)-MIN(ROW('输入数据'!C2:C6)),,1) = '输入数据'! C2,'输入数据'!C3,'输入数据'!C4,'输入数据'!C5,'输入数据'!C6

SUBTOTAL(103,OFFSET('输入数据'!C2:C6,ROW('输入数据'!C2:C6)-MIN(ROW('输入数据'!C2:C6)),,1)) < strong>应理解为

SUBTOTAL(103,'输入数据'!C2,'输入数据'!C3,'输入数据'!C4,'输入数据'!C5,'输入数据'!C6) = {1;1;1; 0;0}Subtotal() 到达每个(单元格)引用的可见状态。

SEARCH("a",'输入数据'!C2:C6) = {1;#VALUE!;1;1;1}

ISNUMBER(SEARCH("a",'输入数据'!C2:C6)) 应解读为ISNUMBER({1;#VALUE!;1;1;1}) = {TRUE;FALSE;TRUE;TRUE;TRUE}

SUBTOTAL(103,OFFSET('输入数据'!C2:C6,ROW('输入数据'!C2:C6)-MIN(ROW('输入数据'!C2:C6)),,1)) = {1;1;1;0;0}

(ISNUMBER(SEARCH("a",'输入数据'!C2:C6))) = {TRUE;FALSE;TRUE;TRUE;TRUE}

{1;1;1;0;0}*{TRUE;FALSE;TRUE;TRUE;TRUE} = {1;0;1;0;0}

求和({1;0;1;0;0}) = 2

使用 Sumproduct() 来避免数组输入并执行数组公式任务(循环遍历单元格范围)，Sumproduct() 将得出传递值的总和。