如何用公式实现员工月度工时汇总？已尝试XLOOKUP+SUMIFS未果
2026-6-20
问题描述
现有员工周工时记录表格，需计算每位员工的月度总工时并填充到月度汇总表中。尝试组合XLOOKUP与SUMIFS公式未成功，寻求可行的公式方案。
周工时数据表格
| 资源姓名 | 资源职级 | 7/22/2024 | 7/29/2024 | 8/5/2024 | 8/12/2024 | 8/19/2024 | 8/26/2024 | 9/2/2024 | 9/9/2024 |
|---|---|---|---|---|---|---|---|---|---|
| Rob | Manager | 40 | 20 | 37 | 29 | 23 | 31 | 33 | 32 |
| Tom | Analyst | 30 | 25 | 26 | 27 | 19 | 39 | 19 | 31 |
| Jessica | Senior Analyst | 20 | 34 | 30 | 35 | 34 | 30 | 29 | 24 |
| Julia | Business Analyst | 15 | 34 | 28 | 22 | 27 | 36 | 38 | 19 |
月度工时汇总表格
| 资源姓名 | 资源职级 | 7月 | 8月 | 9月 |
|---|---|---|---|---|
| Rob | Manager | |||
| Tom | Analyst | |||
| Jessica | Senior Analyst | |||
| Julia | Business Analyst |
解决方案
方法1：SUMPRODUCT公式（兼容多数Excel版本）
假设周工时表数据范围为A1:I5（表头在第1行），在月度汇总表Rob的7月单元格（C2）输入以下公式：
=SUMPRODUCT((A$2:A$5=$A2)*(MONTH(B$1:I$1)=7)*B$2:I$5)
说明：
(A$2:A$5=$A2)：匹配当前行的员工姓名(MONTH(B$1:I$1)=7)：筛选出7月对应的列B$2:I$5：提取对应工时数据，最终相乘求和得到该员工7月总工时
将公式下拉填充至其他员工行，横向拖动时把公式中的7改为8、9即可计算8月、9月工时。
方法2：SUM+INDEX/MATCH组合公式
以Rob的7月单元格为例，公式如下：
=SUM(INDEX(B$2:I$5,MATCH($A2,A$2:A$5,0),MATCH(TRUE,MONTH(B$1:I$1)=7,0)):INDEX(B$2:I$5,MATCH($A2,A$2:A$5,0),MATCH(TRUE,MONTH(B$1:I$1)=7,1)))
说明：
MATCH($A2,A$2:A$5,0)：定位当前员工在周工时表中的行号MATCH(TRUE,MONTH(B$1:I$1)=7,0)/MATCH(TRUE,MONTH(B$1:I$1)=7,1)：找到7月列的起始、结束位置INDEX锁定对应行的7月工时范围，最终用SUM求和
方法3：动态数组公式（Excel 365/2021适用）
在月度汇总表C2单元格输入公式，回车后自动填充所有员工的7月数据，横向拖动可生成8月、9月数据：
=BYROW(A2:A5,LAMBDA(name,SUM(FILTER(INDEX(B2:I5,MATCH(name,A2:A5,0),:),MONTH(B1:I1)=COLUMN()-2))))
说明：
BYROW遍历每个员工姓名INDEX+MATCH定位该员工的工时行FILTER筛选对应月份的工时数据并求和COLUMN()-2自动匹配当前列对应的月份（C列=3→3-2=7，对应7月）
内容的提问来源于stack exchange，提问作者Alytas
相关产品推荐
相关产品推荐
