Excel函数IF/INDEX/MATCH使用困惑：租户收支追踪表需求

看起来你已经把租赁台账的基础框架搭得很清晰了——12个月度工作表按物业追踪信息，核心痛点就是同一物业换租户时，余额不能继承上一租户的遗留数据，得从新租户当月重新计算对吧？我之前帮朋友处理过类似的租赁管理表，给你几个实用的公式方案，适配不同的电子表格工具：

核心逻辑先理清楚 #

不管用什么公式，核心都是两个判断逻辑：

• 同一物业地址下，当前租户和上月租户是否一致？
• 如果不一致：应付余额 = 当月费用 - 付款金额（完全重置计算）
• 如果一致：应付余额 = 上月结转余额 + 当月费用 - 付款金额（正常结转）

公式方案（分工具适配） #

方案1：通用版（兼容Excel 2019及更早、WPS表格） #

假设你的月度工作表（比如1月、2月...）列结构统一为：

• A列：物业地址
• B列：租户姓名/唯一ID（必须准确，用来识别租户变更）
• C列：当月费用
• D列：付款金额
• E列：应付余额（待计算）

在2月工作表的E2单元格输入以下公式，下拉填充即可：

=IF(ISERROR(MATCH(A2,'1月'!$A:$A,0)), 
    C2-D2, 
    IF(B2<>INDEX('1月'!$B:$B,MATCH(A2,'1月'!$A:$A,0)), 
        C2-D2, 
        INDEX('1月'!$E:$E,MATCH(A2,'1月'!$A:$A,0)) + C2 - D2
    )
)

公式拆解：

1. ISERROR(MATCH(...))：先检查上月有没有这个物业地址，如果是新增物业，直接按「当月费用-付款金额」计算余额
2. 找到上月同地址的租户，和当前租户对比：
   • 租户不一样？直接重置余额为当月费用-付款金额
   • 租户一致？用上月的结转余额加上当月费用再减付款金额

方案2：简洁版（Excel 365/Google Sheets 支持XLOOKUP） #

如果你的工具支持XLOOKUP和LET函数，公式会更易读，后期维护也更方便：

=LET(
  last_tenant, XLOOKUP(A2, '1月'!$A:$A, '1月'!$B:$B, ""),
  last_balance, XLOOKUP(A2, '1月'!$A:$A, '1月'!$E:$E, 0),
  IF(last_tenant="" OR last_tenant<>B2, C2-D2, last_balance + C2 - D2)
)

公式拆解：

用LET定义了两个变量：last_tenant（上月同地址租户）、last_balance（上月同地址余额），然后直接判断：要么是新物业，要么租户换了，就重置余额；否则正常结转上月数据计算。

方案3：可复用版（一键切换上月工作表） #

如果不想每个月手动修改公式里的1月这类工作表名，可以把上月工作表名存在每个工作表的固定单元格（比如F1），然后用INDIRECT引用：

=IF(ISERROR(MATCH(A2,INDIRECT(F1&"!$A:$A"),0)), 
    C2-D2, 
    IF(B2<>INDEX(INDIRECT(F1&"!$B:$B"),MATCH(A2,INDIRECT(F1&"!$A:$A"),0)), 
        C2-D2, 
        INDEX(INDIRECT(F1&"!$E:$E"),MATCH(A2,INDIRECT(F1&"!$A:$A"),0)) + C2 - D2
    )
)

比如2月的F1输入1月，3月的F1输入2月，以此类推，公式不用修改，直接下拉填充即可。

关键注意事项 #

• 租户标识（B列）一定要准确！建议用租户ID或者全名，别用简称，避免拼写错误导致公式误判
• 第一个月（比如1月）的余额直接用=C2-D2就行，没有上月数据不需要复杂公式
• 如果是Google Sheets，公式里的引号、逗号都要使用英文格式，和Excel通用

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