如何动态计算列中最后一个数值与指定单元格的差值

核心思路 #

通过函数自动定位每列最后一个非空的「Amount」单元格，将其与「Yearly Difference」单元格的数值做差，且当列下方新增内容时自动更新目标单元格。

公式实现 #

方案1：使用XLOOKUP（Excel 365/2021及以上版本）

假设「Yearly Difference」位于单元格B2，「Amount」数据在C列，计算差值的公式为：

=B2 - XLOOKUP(TRUE, NOT(ISBLANK(C:C)), C:C, , 0, -1)

• 逻辑说明：XLOOKUP的最后一个参数-1表示从下往上查找，匹配第一个满足NOT(ISBLANK(C:C))（非空）的单元格，返回对应数值；当C列下方新增非空内容时，公式会自动定位到新的最后非空单元格。

方案2：使用INDEX+MATCH兼容旧版Excel

如果使用的是不支持XLOOKUP的旧版Excel，可使用数组公式：

=B2 - INDEX(C:C, MATCH(TRUE, NOT(ISBLANK(C:C)), 0))

• 注意事项：旧版Excel需按Ctrl+Shift+Enter组合键确认输入（新版Excel自动识别数组公式）。

多列适配 #

针对不同月份的列（如D列对应2月、E列对应3月等），只需将公式中的列引用（如C:C）替换为对应月份的列即可，每列独立应用公式后，会各自追踪本列的最后非空单元格。

额外注意事项 #

• 确保列中没有仅包含空格的单元格，这类单元格会被判定为非空，影响结果；可使用TRIM函数配合处理：NOT(ISBLANK(TRIM(C:C)))
• 若使用Excel表格（List Object），建议用结构化引用替代列标（如Table1[Amount]），提升公式稳定性。

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