以下公式给出了过滤范围内的最后一个可见数据:
=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)
将显示最后一个非零数字,无论是否可见。这也不是我想要的。
完成此操作的方法之一是将上面的公式添加到索引匹配公式中。它看起来像这样
=索引(R5:R200,(查找(2,1/小计(2,偏移(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 包含日期和 I' m 查找最后一个非零值出现的日期。我尝试将其更改为 BG2:BG367 并提供了最后一个非零值。
我希望这会有所帮助,布拉德
对我有用的是这个:
它返回过滤范围内最后一个非空单元格的行号。
这是它的工作原理:
SUBTOTAL 函数返回一个由 0 和 1 组成的数组,0 表示不可见单元格,1 表示可见单元格。R5:R200 是 R5:R200 中值的数组。当这两个相乘时,R5:R200 中的 0 与 SUBTOTAL 中的 1 或 0 相乘得到 0。如果 a) 单元格不可见(过滤掉)或 b) 单元格内容为 0,则结果数组元素为 0。因此,结果数组包含一堆 0,除了那些可见且值不为 0 的单元格。