☰

对过滤结果应用公式

tag-icon • tag-icon microsoft-excel
对过滤结果应用公式

我希望 Excel 能够处理一个数据集，其中一个source工作表包含我的数据集，另一个metrics工作表包含我的计算值。

我希望能够自动过滤我的源数据并仅在公式中引用过滤后的单元格。

例如，如果一个单元格metrics包含，=SUM(source!B:B)我希望它的值根据过滤器进行更新。

答案1

默认情况下，任何常规 Excel 函数都会访问引用的单元格值，而不考虑其视觉状态（数字格式或隐藏/过滤状态）。

因此，即使 Sheet1!A1 被隐藏或过滤，Sheet2!A1 仍然可以引用该值=Sheet1!A1。但是，您可以使用 SUBTOTAL 或自 2010 年以来使用改进的 AGGREGATE 执行多达 19 种不同的函数，并将其标记为忽略其他小计/聚合函数、错误和/或隐藏/过滤的行。

如果您碰巧使用通过 AGGREGATE 提供的 19 个功能中的任何一个，您可以相当轻松地按预期汇总您的数据，并且它会在过滤源时自动更新。

但是，如果您需要对所有单元格进行更复杂的公式，则可以生成链接克隆表，例如这但进行了修改，如果是数字则隐藏行为 0，如果是文本则隐藏行为“”：

=IF(ISBLANK(Source!A1),"",IF(AGGREGATE(3,5,Source!A1)=0,IF(ISNUMBER(Source!A1),0,""),Source!A1))

因此，基本上源中的每个单元格都单独引用/复制到摘要表，但如果为空白或被过滤/隐藏，则用 0 或空字符串“”替换。从语义上讲，上述公式如下：

If cell is blank, return empty string else
If cell is hidden and is a number, return 0 else
If cell is hidden and is text, return empty string else
Return the cell value.

相关内容