能否将Google Sheets休假追踪公式改为按起止日期而非日期列？

一、重构LEAVE标签页的结构 #

• 删除原A列的date单列，新增两列：Start Date（A列）和End Date（B列）
• 原Name列（原B列）移至C列，后续的Type、Status、Notes等列依次后移，最终列布局：
  • A: Start Date（休假开始日期）
  • B: End Date（休假结束日期）
  • C: Name（员工姓名）
  • D: Type（休假类型）
  • E: Status（状态）
  • F: Notes（备注）
• 合并重复的员工休假记录：比如某员工1月1日到1月3日休假，只需保留一行，填入对应的起止日期、姓名和休假类型即可，无需逐日期重复录入

二、替换主日历表的公式 #

将你当前使用的公式替换为以下公式（应用到Calendar标签页的D5及对应单元格区域）：

=IF($B5="","",IFERROR(VLOOKUP(INDEX(LEAVE!$D:$D,MATCH(1,($B5=LEAVE!$C:$C)*(D$4>=LEAVE!$A:$A)*(D$4<=LEAVE!$B:$B),0)),Lookups!$A:$B,2,FALSE),""))

公式逻辑说明： #

1. $B5=LEAVE!$C:$C：匹配当前行的员工姓名
2. D$4>=LEAVE!$A:$A 且 D$4<=LEAVE!$B:$B：判断当前列的日期是否落在该员工的休假区间内
3. MATCH(1,...0)：定位同时满足姓名匹配+日期在区间内的休假记录行
4. INDEX(LEAVE!$D:$D,...)：提取该行的休假类型
5. 最后通过VLOOKUP匹配Lookups表中的对应标识（比如颜色或状态值）

三、验证与注意事项 #

• 确保所有日期单元格设置为日期格式，避免因文本格式导致的匹配错误
• 测试时可先录入一条休假记录（比如Start Date=2024/1/1，End Date=2024/1/3，Name=测试员工，Type=Annual Leave），检查日历表中对应日期的单元格是否正常显示结果
• 若出现#N/A错误，检查姓名拼写是否一致、日期区间是否正确，或是否存在重复的休假记录

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