求Google Sheets中定位首个≥当前行值的行数公式

问题说明 #

我在Google Sheets中有对应数据集，需要一个公式计算从当前行开始，找到首个≥当前行值所需的行数。

示例：

• 第一行值为10，找到首个≥10的值需4行，结果为4；
• 第二行值为9，找到首个≥9的值需3行，以此类推。

我目前尝试的公式如下，但存在问题：
=IFERROR(MATCH(TRUE, A1:A5 >= 10, 0), COUNTIF(A1:A5, "<10") + 1)

解决方案 #

数组公式（一次性计算所有行） #

以数据范围为A1:A5为例，使用以下公式可以批量生成所有行的结果：
=BYROW(A1:A5, LAMBDA(current_val, IFERROR(MATCH(TRUE, OFFSET(current_val, 0, 0, ROWS(A1:A5)-ROW(current_val)+1)>=current_val, 0), ROWS(A1:A5)-ROW(current_val)+1)))

单行列公式（逐行下拉） #

如果需要在每行单独输入公式，可使用以下公式（输入第一行后下拉即可）：
=IFERROR(MATCH(TRUE, INDIRECT("A"&ROW()&":A5")>=INDIRECT("A"&ROW()), 0), 5-ROW()+1)

公式说明 #

• BYROW + LAMBDA：遍历指定数据范围的每一行，将当前行的值赋值给current_val，实现逐行计算
• OFFSET/INDIRECT：生成从当前行开始到数据末尾的动态范围，确保只查找当前行及之后的内容
• MATCH(TRUE, ...>=current_val, 0)：定位动态范围内第一个满足“≥当前行值”的位置，返回对应的行数
• IFERROR：处理范围内无符合条件值的情况，此时返回当前行到末尾的总行数

原公式问题分析 #

原公式硬编码了判断条件>=10，没有引用当前行的动态值；同时查找范围固定为A1:A5，没有从当前行开始，因此无法适配每一行的个性化需求。

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