Google Sheets中基于列标题反向水平查找匹配项并重启序列计数

场景与当前问题 #

在表格中，当在第9行（K9及右侧单元格）输入Station ID后，第11行对应单元格需自动生成序列号：规则是先通过Data工作表匹配该Station ID对应的单个字母，再加上递增序号。

当前使用的公式（输入K11后可复制到整行）：

=IFERROR(CONCATENATE(VLOOKUP(K$9,Data!$A$2:$B$7,2,FALSE),(TEXT(COUNTA(FILTER($K$9:K$9,LEFT($K$9:K$9,4)=LEFT(K$9,4))),"0"))),)

现有公式的问题：当手动在某单元格输入自定义序列号（例如Q11手动输入M1）后，同Station的下一个单元格（如W11）会继续累计之前的计数生成M5，而非预期的M2。需要实现反向水平查找，以最近的手动输入节点为起点重启计数。

效果对比 #

• 当前公式运行效果：
  
• 预期效果：
  

解决方案公式 #

替换原有公式为以下内容（输入K11后复制到整行即可）：

=IFERROR(
  LET(
    station_char, VLOOKUP(K$9, Data!$A$2:$B$7, 2, FALSE),
    current_col, COLUMN(),
    left_range, $K$11:INDEX($11:$11, current_col - 1),
    last_custom, XLOOKUP(station_char, LEFT(left_range, 1), left_range,, 0, -1),
    seq_num, IF(ISBLANK(last_custom), 1, VALUE(RIGHT(last_custom, LEN(last_custom) - 1)) + 1),
    CONCAT(station_char, seq_num)
  ),
  ""
)

公式逻辑说明 #

1. station_char：通过VLOOKUP匹配当前Station ID对应的字母标识；
2. current_col：获取当前单元格的列号，用于定位左侧需要查找的区域；
3. left_range：定义当前单元格左侧从K11开始到前一列的区域，作为反向查找范围；
4. last_custom：用XLOOKUP从右往左反向查找，找到左侧最近的、以对应字母开头的手动输入序列号；
5. seq_num：如果没有找到手动输入的节点，序号从1开始；否则提取已有序号的数字部分加1；
6. 最后拼接字母和序号，生成目标序列号。

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