excel - 返回连续倒数第二个数字的公式

Question

我有一个公式:

=INDEX(K14:V14,MATCH(9.99999999999999E+307,K14:V14))  

它提供列范围中最后一个数值数据点的结果。不过，我还希望有一个公式可以报告倒数第二个数据点。

Answer 1

尽管我更喜欢更简洁的1E+99，但您的原始公式非常适合查找连续的最后一个数字。您只需 reshape MATCH function使用的范围即可。使用 INDEX function 到比最后一个数字少一列使用类似的 MATCH 函数限制要查看的列。

        

K12中的公式是，

=INDEX(K14:V14, MATCH(1E+99, K14:INDEX(K14:V14, MATCH(1E+99, K14:V14)-1)))

这也可以使用较新的 AGGREGATE¹ function 来执行如果您重视跟上趋势。

=INDEX(K$14:V$14, AGGREGATE(14, 6, COLUMN($1:$12)/SIGN(LEN(K$14:V$14)), 2))

K14:V14 系列有 12 列。通过强制任何带有空白的列出现 #DIV/0! 错误(并忽略以 6 作为 option 参数的错误)，我们可以检索第二个(中的 2) k 参数)最后一个数字。

这种公式的优点是 AGGREAGATE 的 14 个子函数模仿了标准工作表 LARGE function 。将 2 替换为 ROW(1:1) 并向下填充，您将返回一系列从右到左的非空白数字。 (请参阅示例图像中的 w10:W13)。

¹ _{AGGREGATE function是随 Excel 2010 引入的。它在早期版本中不可用。}