求跳过空白周末的Excel动态百分比计算公式

需求说明 #

需要自动计算B列工作日数据的环比增长率：

• 周一：(周一数值 - 上周五数值) / 上周五数值
• 周二至周五：(当日数值 - 前一工作日数值) / 前一工作日数值
  自动跳过周末B列空白的行，无需手动调整。

方案1：适用于Excel 365/2021（支持XLOOKUP函数） #

在目标单元格（如C2，对应A2日期、B2数据）输入以下公式，下拉填充即可：

=IF(WEEKDAY(A2,2)=1,(B2-XLOOKUP(1,(WEEKDAY(A$2:A2,2)=5)*(B$2:B2<>""),B$2:B2,,0,-1))/XLOOKUP(1,(WEEKDAY(A$2:A2,2)=5)*(B$2:B2<>""),B$2:B2,,0,-1),(B2-XLOOKUP(1,(B$2:B2<>""),B$2:B2,,0,-1))/XLOOKUP(1,(B$2:B2<>""),B$2:B2,,0,-1))

公式逻辑：

• WEEKDAY(A2,2)：将日期转换为1（周一）到7（周日）的数字，无需依赖D列的星期文本
• 周一逻辑：用XLOOKUP从当前行往上查找最近的、B列有数据的周五数值做对比
• 其他工作日逻辑：用XLOOKUP从当前行往上查找最近的、B列非空白的工作日数值做对比

方案2：兼容旧版Excel（用INDEX+MATCH组合） #

如果你的Excel版本不支持XLOOKUP，用以下公式：

=IF(WEEKDAY(A2,2)=1,(B2-INDEX(B$2:B2,MATCH(2,1/(WEEKDAY(A$2:A2,2)=5)*(B$2:B2<>""))))/INDEX(B$2:B2,MATCH(2,1/(WEEKDAY(A$2:A2,2)=5)*(B$2:B2<>""))),(B2-INDEX(B$2:B2,MATCH(2,1/(B$2:B2<>""))))/INDEX(B$2:B2,MATCH(2,1/(B$2:B2<>""))))

公式逻辑：

• MATCH(2,1/(条件))：通过反向查找，定位到满足条件的最后一个有效数据行位置
• 同样区分周一和其他工作日的对比逻辑，自动跳过空白行

注意事项 #

• 公式从第二行开始输入（假设第一行为表头），下拉后会自动适配每一行的日期和数据
• 若工作日B列存在空白（非周末），公式也会自动跳过，取最近的有数据的工作日计算
• 可以设置单元格格式为百分比，方便查看增长率结果

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