Google Sheets中如何查找目标数值列并计算返回原值的尝试次数

问题说明 #

需求为统计从指定初始数值出发，按照「当前位置存储的数值即为下一跳转位置」的规则，回到初始值需要的总跳转次数。
此前调用=FIND、=HLOOKUP运行失败的核心原因：

• FIND是文本子串位置查找函数，本身就不适用单元格位置索引跳转的业务场景
• HLOOKUP是横向值匹配函数，遇到重复值会默认返回首个匹配结果，完全不符合按固定位置取跳转目标的逻辑，计算结果必然出错。

计算规则参考示例：

• 初始值为1时，经过3次跳转回到自身，统计结果为3
• 初始值为2时，经过17次跳转回到自身，统计结果为17

无脚本公式实现 #

Google Sheets已支持递归LAMBDA能力，无需编写代码即可完成计算。
假设跳转映射数据存储在A列，行号对应位置编号，单元格值对应该位置的跳转目标：

• 单个初始值计算：将公式中n替换为需要统计的初始数值即可，比如统计初始值为1的跳转次数就把n替换为1

=LET(
  loop,LAMBDA(self,cur,start,cnt,
    IF(cur=start,cnt,self(self,INDEX(A:A,cur),start,cnt+1))
  ),
  loop(loop,INDEX(A:A,n),n,1)
)

• 批量计算所有初始值的跳转次数：直接在目标列第一行输入以下公式，会自动按行生成所有位置的循环长度结果

=MAP(SEQUENCE(ROWS(A:A)),LAMBDA(n,
  LET(
    loop,LAMBDA(self,cur,start,cnt,
      IF(cur=start,cnt,self(self,INDEX(A:A,cur),start,cnt+1))
    ),
    loop(loop,INDEX(A:A,n),n,1)
  )
))

注意：如果跳转映射数据不是存储在A列，将公式中A:A替换为实际的数据列范围即可，必须保证范围中第k行对应该位置k的跳转目标值。

自定义函数实现（兼容旧版本表格） #

如果使用的表格版本不支持递归LAMBDA，可以通过Google Apps Script注册自定义函数实现：

1. 点击表格顶部菜单栏「扩展程序」-「Apps Script」打开脚本编辑器
2. 粘贴以下代码后点击保存，完成脚本授权

function CYCLELENGTH(mapRange, startVal) {
  const mapArr = mapRange.flat();
  let current = mapArr[startVal - 1];
  let count = 1;
  const maxLimit = mapArr.length;
  // 增加死循环阈值，避免非置换格式数据导致运行超时
  while (current !== startVal && count <= maxLimit) {
    current = mapArr[current - 1];
    count++;
  }
  return count > maxLimit ? "数据格式错误" : count;
}

3. 回到表格即可像内置函数一样调用，语法为=CYCLELENGTH(跳转映射列范围, 初始值)，比如计算初始值2的跳转次数就输入=CYCLELENGTH(A:A,2)，返回结果与示例预期一致。

随机数寻回问题讲解视频
计算示例截图
测试用表格

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