☰

Excel：查找过滤范围内的最后一个非零、非空单元格

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
Excel：查找过滤范围内的最后一个非零、非空单元格

以下公式给出过滤范围内的最后可见数据：

=LOOKUP(2,1/SUBTOTAL(2,OFFSET(R5,ROW(R5:R200)-ROW(R5),0)),R5:R200)

但是，如果最后一个可见单元格包含零，它将返回零。 有没有办法过滤掉零并返回最后一个大于零的单元格？

以下是一个简化的示例。日期和运动项目均已过滤。例如，可以更改过滤器，以便显示六月和篮球。

接下来的两列是两种货币的余额。我需要一个公式在货币的底部分别显示 193 和 525。我在网上找到的查找公式显示 193 和 0，其中 0 是货币过滤数据的最后一个数字。我想要显示的是 525，而不是 0。顺便说一句，零本身不是零。零表示没有交易。它实际上应该是空白的。但是，我不能将它们留空，因为它们涉及计算。在空白上加一个数字会产生错误。

这是月度资产负债表报告。我需要报告 193 和 525 作为 7 月份的期末余额。

2019/07/01  tennis  775           0
2019/07/11  tennis  925           0
2019/07/11  tennis  0           125
2019/07/13  tennis  0           105
2019/07/13  tennis  0           525
2019/07/25  tennis  193           0

另一个公式：

LOOKUP(2,1/(R5:R200<>0),R5:R200)

将显示最后一个非零数字，无论是否可见。这也不是我想要的。

答案1

实现此目的的方法之一是将上面的公式添加到索引匹配公式中。它看起来像这样

=INDEX(R5:R200,(LOOKUP(2,1/SUBTOTAL(2,OFFSET(R5,ROW(R5:R200)-ROW(R5),0)),R5:R200),1)

我使用类似的公式；INDEX(Entry20!D2:D367,(LOOKUP(2,1/(Entry20!BG2:BG367<>0),ROW(Entry20!BG2:BG367))),1) 在我的例子中，D 列包含日期，我正在寻找最后一个非零值出现的日期。我尝试将其更改为 BG2:BG367，它提供了最后一个非零值。

我希望这能有所帮助，布拉德

答案2

对我有用的是这个：

LOOKUP(2,1/INDEX(SUBTOTAL(2,OFFSET(R5,ROW(R5:R200)-ROW(R5),0))*R5:R200,0),ROW(R5:R200))

它返回过滤范围内最后一个非空单元格的行号。

工作原理如下：

SUBTOTAL 函数返回一个由 0 和 1 组成的数组，0 表示不可见单元格，1 表示可见单元格。R5:R200 是 R5:R200 中值的数组。将这两个值相乘时，R5:R200 中的 0 与 SUBTOTAL 中的 1 或 0 相乘，结果为 0。如果 a) 单元格不可见（已过滤掉）或 b) 单元格内容为 0，则结​​果数组元素为 0。因此，结果数组包含一堆 0，但可见且值不为 0 的单元格除外。

相关内容