Google Sheets OKR 追踪表搭建指南：场景路由与公式

在 Google Sheets 中搭建可用的 OKR 追踪表，核心是三页签结构：主追踪页、假设参数页、场景切换页。一个置信度下拉单元格——保守、基准、乐观——配合 CHOOSE 公式，让下游所有计算自动加载对应的假设参数；当前值列从系统导出时变成文本字符串，需要 SUBSTITUTE 处理 CHAR(160) 不间断空格，而不是 TRIM。

以下内容完整覆盖：页签结构与场景路由机制、基线校正进度计算，以及脏数据清洗——包括那个让整季度 #VALUE! 报错的不间断空格问题。

让 OKR 追踪表运转起来的页签结构

三个页签，职责明确不重叠。

OKR_Tracker — 每天查看的主页签：

Revenue_Assumptions — 3 列场景参数，每行对应一个业务驱动因子：

Scenario — 单个单元格 B1，数据验证设置为下拉列表：保守,基准,乐观。

任意计算单元格中的 CHOOSE 公式读取该下拉值，自动路由到对应列：

=CHOOSE(MATCH(Scenario!$B$1,{"保守","基准","乐观"},0),
  Revenue_Assumptions!B2,
  Revenue_Assumptions!C2,
  Revenue_Assumptions!D2)

为什么用 CHOOSE+MATCH，而不用命名范围配合 INDIRECT？

根据 Google Workspace 官方 INDIRECT 函数文档，INDIRECT 接受的是运行时构建的字符串引用，这意味着它无法在编译期校验引用路径是否有效。在页签被重命名或工作簿被复制时，INDIRECT 配合命名范围的方案会静默断裂——公式继续显示数值，但实际上引用的是旧范围或错误位置，不报错，也不提示。CHOOSE+MATCH 的引用是硬编码路径，页签改名会立即报错，反而更容易发现。假设参数页结构搭好之后，把 CHOOSE 接线到各个计算单元格大约需要 20 分钟。

基线校正进度：4 个字符的差异

最简单的进度公式是 =F2/E2，给出一个数字，但这个数字在撒谎。

如果基线是 420 万元，目标是 910 万元，当前值是 706 万元，=F2/E2 算出目标完成率 77.6%。但起点是 420 万，不是零。你实际需要弥合的差距是 490 万（910-420），目前已弥合 286 万（706-420），真实进度是 58.4%：

=(F2-D2)/(E2-D2) = (706-420)/(910-420) = 58.4%

77.6% 与 58.4%——对于一条季末 KR，两个数字对管理层的行动判断完全不同。正确的分母只需要多写 4 个字符。

将以下公式写入 OKR_Tracker 的 H2 单元格：

=IFERROR((F2-D2)/(E2-D2), "—")

IFERROR 不可省略。新目标尚未设置目标值、直接沿用基线数字的情况很常见，此时 E2=D2，分母为零，会触发 #DIV/0! 错误。没有这层包裹，整个进度列在季度汇报前会一片狼藉；加了之后，显示一个干净的破折号。

Google Sheets OKR 追踪表的脏数据处理

当前值列（F 列）是 OKR 追踪表在实践中最容易出问题的地方。手动录入通常没有问题。从用友、金蝶、Salesforce、或任何生成 CSV 导出的系统导入，你会反复碰到这种情形：

单元格显示 1087000，但 =F2-D2 返回 #VALUE!。

这个单元格是文本字符串，看起来像数字，实际不是。快速验证：=ISNUMBER(F2) 返回 FALSE。

CHAR(160)：TRIM 处理不掉的那个字符

大多数数据清洗教程到这里会说"用 TRIM 去空格"，然后跳开。这个方案会在特定场景下失效，而且失效时没有任何提示。

根据 Google Sheets 官方文档，TRIM 只删除标准 ASCII 空格（字符编码 32）。不间断空格（Non-Breaking Space，字符编码 160）TRIM 无法识别，会直接穿透。这个字符在单元格里肉眼看起来与普通空格毫无区别，但所有数学运算都会因此失败。

只要你的数据从任何生成 Unicode 输出的系统导入——财务 ERP、销售 CRM、报表工具——这个问题大概率已经在悄悄发生。正确的清洗方式是先把 CHAR(160) 替换掉，再做 TRIM：

=VALUE(TRIM(SUBSTITUTE(F2, CHAR(160), " ")))

针对混合格式、空行、带尾部空格的百分比字符串（如 "58 %"）等完整情形，可以使用以下封装：

=IFERROR(
  VALUE(
    TRIM(
      SUBSTITUTE(
        SUBSTITUTE(F2, CHAR(160), ""),
        "%", ""
      )
    )
  ) / IF(ISNUMBER(SEARCH("%", F2&"")), 100, 1),
  ""
)

这个公式处理：含空格的百分比字符串、不间断空格、空白单元格，以及普通数值。建议单独放在辅助列（命名为 Current_Clean），进度公式直接引用该列而非原始导入列。

置信度为空时的路由兜底

对于置信度尚未填写的行——新增目标的 G2 为空——CHOOSE 路由会报错。加一层包裹处理：

=IFERROR(
  CHOOSE(
    MATCH(G2, {"保守","基准","乐观"}, 0),
    Revenue_Assumptions!B2,
    Revenue_Assumptions!C2,
    Revenue_Assumptions!D2
  ),
  Revenue_Assumptions!C2
)

未设置置信度时默认回退到基准场景——半填充的追踪表不会因为某行数据缺失而导致下方所有行连锁报错。

Google Sheets OKR 追踪表完整公式组合

OKR_Tracker 中一行生产级数据的完整公式：

H2 — 基线校正进度：

=IFERROR((Current_Clean-D2)/(E2-D2), "—")

场景路由目标值（目标值因场景而异时）：

=IFERROR(
  CHOOSE(MATCH(G2,{"保守","基准","乐观"},0),
    Targets!B2, Targets!C2, Targets!D2),
  Targets!C2
)

与目标的金额缺口：

=IFERROR(E2 - Current_Clean, "—")

这些 IFERROR 包裹不是防御性编程的摆设——它们决定了追踪表是"静默失败"（显示破折号）还是"高声报错"（在季度汇报前的 40 行里蔓延错误）。

截至 2026 年 4 月，CHOOSE 配合 MATCH 的场景路由方案，是在 Google Sheets 中实现多情景切换最简洁的原生方式，无需 Apps Script，也不存在 INDIRECT 方案重命名页签即断裂的隐患。

ModelMonkey 能解决哪个环节

上述公式大约需要一小时搭建完成。真正消耗时间的是后续维护：更新实际值、确认 Current_Clean 列在新 CSV 格式导入后仍能正确解析、季中新增目标行——每季度会再吃掉 4 到 6 小时。

如果你的 OKR 追踪表在 Google Sheets 中，ModelMonkey 可以直接读取 OKR_Tracker 和 Revenue_Assumptions 页签，完成实际值更新、新增目标项，或在导入格式变更时重写 Current_Clean 公式——输入"从这张表更新第二季度实际值"并粘贴导出数据，它处理公式解析，不需要每次重新描述表格结构。

→ 免费14天体验，同时支持 Google Sheets 和 Excel。

常见问题

Google Sheets OKR 追踪表最少需要几个页签？

可运转的最小结构是 3 个页签：主追踪页（OKR_Tracker）、假设参数页（Revenue_Assumptions）、场景切换页（Scenario）。Scenario 页签可以精简为单个下拉单元格，其他两个页签承载所有实质内容。

为什么从 ERP 导出的数字在 Google Sheets 里会变成文本？

最常见原因有两个：一是数字带有千位分隔符或货币符号（如"¥1,087,000"），VALUE 函数无法直接解析；二是单元格中包含不间断空格（CHAR(160)），这个字符 TRIM 无法去除，必须先用 SUBSTITUTE 替换掉。用 =ISNUMBER(F2) 可以立即判断单元格是否为真实数值。

CHOOSE+MATCH 场景路由和 INDIRECT+命名范围有什么实际区别？

INDIRECT 配合命名范围在页签重命名或工作簿复制后可能静默断裂——公式不报错，但实际引用的数据已经错误。CHOOSE+MATCH 使用硬编码路径，引用断裂时会立即报错，更容易发现和修复。对于需要长期维护的 OKR 追踪表，CHOOSE+MATCH 的可靠性更高。

OKR 追踪表的进度应该如何正确计算？

应使用基线校正公式：=(当前值-基线值)/(目标值-基线值)，而非简单的 =当前值/目标值。后者默认起点为零，当基线不为零时会高估完成率。基线越高、目标增幅越小，两种算法的偏差越大。

场景路由公式应该放在哪里最合理？

建议将场景路由公式集中放在 Revenue_Assumptions 页签的引用层，主追踪页各行只引用经过路由后的结果值，而非在每个单元格中重复写 CHOOSE 逻辑。这样当场景结构调整时，只需修改一处。