动态添加并计算表格1M Change、YTD Change列的技术需求

原始数据表格 #

需求概述 #

• 添加1m change列：计算最新日期值与前一个月日期值的差值
• 添加YTD change列：计算最新日期值与当年首个日期值的差值
• 公式需支持动态更新，新增日期列时自动适配

1. 1M Change 列公式（以Excel为例，单元格F2） #

使用INDEX+COLUMNS实现动态列引用：

=INDEX($B2:$D2, COLUMNS($B2:$D2)) - INDEX($B2:$D2, COLUMNS($B2:$D2)-1)

如果希望完全无需调整列范围，可改用整行引用版本（新增列后不用改公式）：

=INDEX($B2:$XFD2, MAX(IF(ISNUMBER($B2:$XFD2), COLUMN($B2:$XFD2), 0))) - INDEX($B2:$XFD2, MAX(IF(ISNUMBER($B2:$XFD2), COLUMN($B2:$XFD2), 0))-1)

动态逻辑：COLUMNS统计当前日期列数量，自动定位最后一列（最新日期）和倒数第二列（前一个月）；整行引用版本通过MAX+IF自动识别最后一个有数值的日期列，新增列后自动适配。

2. YTD Change 列公式（单元格G2） #

结合MAX+MATCH定位当年首个日期：

=INDEX($B2:$D2, COLUMNS($B2:$D2)) - INDEX($B2:$D2, MATCH(DATE(YEAR(MAX($B$1:$D$1)),1,1), $B$1:$D$1, 0))

整行引用版本：

=INDEX($B2:$XFD2, MAX(IF(ISNUMBER($B2:$XFD2), COLUMN($B2:$XFD2), 0))) - INDEX($B2:$XFD2, MATCH(DATE(YEAR(MAX($B$1:$XFD$1)),1,1), $B$1:$XFD$1, 0))

动态逻辑：MAX($B$1:$D$1)获取最新日期，提取年份后生成当年1月1日，再用MATCH找到表格中对应年份的首个日期列，新增日期列后自动覆盖范围。

最终效果示例 #

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