如何在Google Sheet中创建循环直至找到C8、B14单元格最优解？

问题背景 #

我有一份Google表格，需要找出单元格C8和B14的最优值。其中C8的查找逻辑为：从B8初始值8.8开始，每次给B8增加0.01（生成序列8.8, 8.81, 8.82,...），直到单元格O8的值≥0时停止，此时的B8值即为C8的目标最优值。

我自行编写了以下公式，但无法正常运行，无法实现自动递增值到C8的效果：

=IF(O8<0, B8+0.01) + IF(O8>=0, C8)

解决方案 #

Google Sheets普通公式无法直接实现循环迭代逻辑，推荐两种可行方案：

方法1：启用迭代计算+公式实现 #

1. 先开启迭代计算功能：
   • 点击顶部菜单栏「文件」→「设置」→「计算」
   • 勾选「启用迭代计算」，设置「最大迭代次数」为足够大的数值（如1000），「迭代精度」设为0.01
2. 在C8单元格输入以下公式：

=IF(O8<0, B8+0.01, B8)

每次迭代时，若O8<0，C8会自动更新为B8+0.01；当O8≥0时，C8将锁定当前B8值，即满足条件的最优值。

方法2：用Apps Script实现精准控制 #

如果需要更灵活的逻辑控制，可以编写脚本：

1. 打开表格，点击「扩展程序」→「Apps脚本」
2. 替换默认代码为以下内容：

function findOptimalC8() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let b8Value = sheet.getRange("B8").getValue();
  let o8Value = sheet.getRange("O8").getValue();
  
  while (o8Value < 0) {
    b8Value += 0.01;
    sheet.getRange("B8").setValue(b8Value);
    SpreadsheetApp.flush(); // 强制刷新计算
    o8Value = sheet.getRange("O8").getValue();
  }
  
  sheet.getRange("C8").setValue(b8Value);
}

3. 保存脚本后点击运行，即可自动计算出C8的最优值。

内容的提问来源于stack exchange，提问作者Ranjan Kumar Singh