Excel Web App跨工作簿计算员工指定时段请假天数求助

问题说明 #

• 环境：浏览器端Excel Web App，涉及两个工作簿
• 《Production Planning 1》的Production Planning工作表规则：
  • C4、D4填写项目起止日期，F4通过NETWORKDAYS.INTL计算总人天，G4=F4×8计算总人时
  • 需求：在C11输入员工姓名后，D11自动从《Leave Tracker》工作簿统计该员工在C4-D4日期范围内的**Unpaid Leave(UL)+Paid Leave(PL)**总请假天数；E11=F4-D11为扣减请假后的可用人天
• 示例：当项目起止日期为2023年4月1日至4月18日时，James Ferrell的总请假天数应为2（1天UL+1天PL）
• 问题：尝试用SUMPRODUCT公式时出现#REF!错误，需D11的正确公式，仅可操作《Production Planning 1》中的C4、D4、C11-C13单元格

正确公式及使用说明 #

场景1：请假为单日记录（单条记录对应1天请假） #

假设《Leave Tracker》的Leave Data工作表（替换为实际表名）中：

• A列=员工姓名，B列=请假类型（"UL"/"PL"），C列=请假日期，D列=请假天数（单日为1）

D11输入以下公式：

=SUMIFS('[Leave Tracker.xlsx]Leave Data'!$D:$D,'[Leave Tracker.xlsx]Leave Data'!$A:$A,C11,'[Leave Tracker.xlsx]Leave Data'!$B:$B,"UL",'[Leave Tracker.xlsx]Leave Data'!$C:$C,">="&$C$4,'[Leave Tracker.xlsx]Leave Data'!$C:$C,"<="&$D$4)+SUMIFS('[Leave Tracker.xlsx]Leave Data'!$D:$D,'[Leave Tracker.xlsx]Leave Data'!$A:$A,C11,'[Leave Tracker.xlsx]Leave Data'!$B:$B,"PL",'[Leave Tracker.xlsx]Leave Data'!$C:$C,">="&$C$4,'[Leave Tracker.xlsx]Leave Data'!$C:$C,"<="&$D$4)

公式解析

• 用两个SUMIFS分别统计UL和PL的请假天数，再求和得到总天数
• 核心条件：姓名匹配C11、请假类型为UL/PL、请假日期在项目起止范围内

场景2：请假为跨多天记录（单条记录有起始/结束日期） #

假设《Leave Tracker》的Leave Data工作表中：

• A列=员工姓名，B列=请假类型（"UL"/"PL"），C列=请假起始日期，D列=请假结束日期

D11输入以下公式（自动计算请假期间的工作日天数）：

=SUMPRODUCT(([Leave Tracker.xlsx]Leave Data!$A:$A=C11)*(([Leave Tracker.xlsx]Leave Data!$B:$B="UL")+([Leave Tracker.xlsx]Leave Data!$B:$B="PL"))*NETWORKDAYS.INTL([Leave Tracker.xlsx]Leave Data!$C:$C,[Leave Tracker.xlsx]Leave Data!$D:$D,1))

公式解析

• SUMPRODUCT多条件判断：姓名匹配、请假类型为UL/PL
• NETWORKDAYS.INTL计算单次请假的工作日天数（参数1代表周末为周六周日，可根据实际调整参数）

避免#REF!错误的关键 #

• 确保《Leave Tracker》工作簿在Excel Web App中处于打开状态，跨工作簿引用需要源工作簿激活
• 核对《Leave Tracker》中的列位置、表名与公式一致，比如请假类型列不是B列的话，要替换成对应列标
• 不要删除或重命名《Leave Tracker》中的目标工作表，否则会触发引用错误

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