Google Sheets考勤模块：休假数据按月按日期精准展示的函数问题

问题背景 #

• 需实现考勤模块跨工作表展示多名员工全月不同类型休假数据，要求按日期自动填充，月份变更时数据同步更新
• 原INDEX MATCH及尝试的XLOOKUP公式无法精准匹配数据
• 原尝试公式：

  =ArrayFormula(XLOOKUP(1,(D$7>=Leaves!$D:$D)*($AJ$6<=Leaves!$E:$E)*(Leaves!$B:$B=$C8),Leaves!$I:$I,""))
  

问题分析 #

原公式逻辑错误：用当月最后一天（$AJ$6）判断休假结束日期，导致无法匹配单日期或跨部分日期的休假记录，应该针对每个单元格对应的日期，判断其是否处于休假的开始（Leaves!D:D）和结束（Leaves!E:E）日期区间内。

修正方案 #

单个日期单元格公式 #

以Attendance表D8单元格（对应员工C8、日期D7）为例，使用以下公式：

=XLOOKUP(1,(D7>=Leaves!$D:$D)*(D7<=Leaves!$E:$E)*(Leaves!$B:$B=$C8),Leaves!$I:$I,"")

整月批量填充数组公式 #

若要一次性填充某员工（如C8）全月（D8到AJ8）的休假数据，使用数组公式：

=ArrayFormula(XLOOKUP(1,(D7:AJ7>=Leaves!$D:$D)*(D7:AJ7<=Leaves!$E:$E)*(Leaves!$B:$B=$C8),Leaves!$I:$I,""))

动态适配月份的优化公式 #

为避免跨月份数据干扰，同时提升查询效率，可结合年月匹配条件（假设Attendance表A1为当月标识，如"2024年5月"）：

=ArrayFormula(IFERROR(XLOOKUP(1,(D7:AJ7>=Leaves!$D$2:$D)*(D7:AJ7<=Leaves!$E$2:$E)*(Leaves!$B$2:$B=$C8)*(YEAR(Leaves!$D:$D)=YEAR(A1))*(MONTH(Leaves!$D:$D)=MONTH(A1)),Leaves!$I$2:$I,""),""))

核心优化点 #

• 替换原公式中错误的日期判断逻辑，改为当前单元格日期是否在休假区间内
• 限制查询范围为实际数据行（而非全列），减少不必要的计算
• 增加年月匹配条件，确保仅加载当月的休假记录

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