Excel如何设置固定公式自动计算最后两周状态差异？

针对你需要自动抓取每行最后两列周数据生成状态缩写的需求，结合最新版Excel的功能，提供以下几种可行方案：

方案1：INDEX + COLUMNS组合（通用型，兼容多数新版Excel） #

这个方案无需依赖动态数组，适合所有支持INDEX/COLUMNS的Excel版本（2019及以上）。

假设数据从A列（ID列）开始，表头在第1行，数据行从第2行起，在需要生成结果的单元格输入公式：

=LEFT(INDEX($2:$2, COLUMNS($2:$2)-1), 1)&"-"&LEFT(INDEX($2:$2, COLUMNS($2:$2)), 1)

公式说明： #

• COLUMNS($2:$2)：返回当前行的总列数
• INDEX($2:$2, 总列数-1)：定位到当前行的倒数第二列（上一周的状态）
• LEFT(...,1)：提取状态的首字母作为缩写（如果你的缩写规则不是首字母，看下面的映射版）

如果状态和缩写是固定映射（比如Newbie→N、Promise→P），可以先做一个映射表（比如放在Z1:AA区域），再用XLOOKUP替换LEFT部分：

=XLOOKUP(INDEX($2:$2, COLUMNS($2:$2)-1), $Z$1:$Z$5, $AA$1:$AA$5)&"-"&XLOOKUP(INDEX($2:$2, COLUMNS($2:$2)), $Z$1:$Z$5, $AA$1:$AA$5)

方案2：TAKE动态数组函数（Excel 365/2021专属，更简洁） #

如果你用的是Excel 365或2021，TAKE函数可以直接截取最后N列，写法更直观：

=LEFT(TAKE($2:$2, -2, 1), 1)&"-"&LEFT(TAKE($2:$2, -1, 1), 1)

公式说明： #

• TAKE($2:$2, -2, 1)：取当前行倒数第二列的单个值
• TAKE($2:$2, -1, 1)：取当前行最后一列的单个值
  同样，需要映射的话替换LEFT为XLOOKUP即可。

方案3：修正OFFSET函数的用法（解决你之前的失败问题） #

你之前用OFFSET没成功，大概率是基准单元格的偏移量计算错误。正确的写法如下：

=LEFT(OFFSET($A2, 0, COLUMNS($2:$2)-2), 1)&"-"&LEFT(OFFSET($A2, 0, COLUMNS($2:$2)-1), 1)

关键修正点： #

• 基准单元格用$A2（当前行的ID列），列偏移量是总列数-2（因为A是第1列，倒数第二列的列号是总列数-1，所以偏移量要减2）
• 如果之前直接用OFFSET的固定偏移，就会在新增列后失效，用COLUMNS动态计算偏移量才能自动适配新增列

额外注意事项 #

• 确保Wk列是连续的，没有空列插在中间，否则COLUMNS会把空列计入总列数，导致取错数据。如果右侧有非Wk列，把公式里的$2:$2改成实际数据范围（比如$A2:$X2）
• 可以用IFERROR包裹公式处理空值情况，避免出现错误提示：

=IFERROR(LEFT(TAKE($2:$2, -2, 1), 1)&"-"&LEFT(TAKE($2:$2, -1, 1), 1), "无有效数据")

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