Google Sheets：在指定表格列中查找最后及倒数第二个非空值

问题背景 #

我有一个包含多个独立表格的Google Sheet，各表格起始于不同行。需要在特定表格（行89至行141）的F列（Value列）中查找最后和倒数第二个非空值，该表格上下均有共享E列（Date列）和F列的其他表格。

示例需求：

• 最后一个非空值：3（对应日期2/4/2024）
• 倒数第二个非空值：14（对应日期1/28/2024）

原公式问题 #

尝试的公式因未排除F列空值对应的日期，导致返回空白：

• 最后一个非空值：=filter(F90:F110,E90:E110=large(E90:E110,1))
• 倒数第二个非空值：=filter(F90:F111,E90:E111=large(E90:E111,2))

问题根源：原公式仅筛选了日期排序后的前N大值，但未排除F列空值的行，若最大日期对应的F列是空值，就会返回空白。

修正后的公式 #

1. 获取最后一个非空值 #

方法一（INDEX+FILTER+COUNTA）

=INDEX(FILTER(F89:F141, NOT(ISBLANK(F89:F141))), COUNTA(FILTER(F89:F141, NOT(ISBLANK(F89:F141)))))

方法二（LOOKUP简洁写法）

=LOOKUP(2, 1/(NOT(ISBLANK(F89:F141))), F89:F141)

2. 获取倒数第二个非空值 #

方法一（INDEX+FILTER+COUNTA）

=INDEX(FILTER(F89:F141, NOT(ISBLANK(F89:F141))), COUNTA(FILTER(F89:F141, NOT(ISBLANK(F89:F141)))) - 1)

方法二（LOOKUP变种）

=LOOKUP(2, 1/(NOT(ISBLANK(F89:F141))), OFFSET(F89:F141, 0, 0, COUNTA(F89:F141)-1))

公式说明 #

• 核心逻辑：先过滤出指定区域内F列的所有非空值，再通过索引定位目标位置；
• LOOKUP(2,1/(条件),区域)：利用LOOKUP会查找最后一个满足条件的匹配项的特性，这里的1/(NOT(ISBLANK(...)))会将非空行转为1，空行转为错误值，LOOKUP会忽略错误值并定位到最后一个1对应的F列值；
• 若需要获取对应日期，只需将公式中的F89:F141替换为E89:E141即可。

内容的提问来源于stack exchange，提问作者PineNuts0