好的,就上下文而言,这个链接让我开始使用,但我现在试图将它应用到我的用例中: Excel - 根据与另一个表中的另一列匹配的列求和值。
我有一个小时日志,用于输入我的工作,包括日期、项目和任务信息,以及工作时间的开始和结束时间,它可以计算出从输入的时间起我已经工作了多少小时(四舍五入到最接近的 15 分钟)。
以下是包含示例数据的日志(“timeLog”)的(大致):
A | 乙 | C | 德 | 埃 | |
---|---|---|---|---|---|
1 | 日期 | 项目与任务 | 开始时间 | 时间结束 | 小时 |
2 | 2023 年 1 月 1 日 | 项目 1:任务 1 | 8:00 AM | 10:00 AM | 2 |
3 | 2023 年 1 月 1 日 | 项目 2:任务 1 | 10:00 AM | 4:00 PM | 6 |
4 | 2023 年 1 月 6 日 | 节假日营业时间 | 8:00 AM | 4:00 PM | 8 |
5 | 2023 年 1 月 7 日 | 病假 | 8:00 AM | 12:00 PM | 4 |
6 | 2023 年 2 月 6 日 | 项目 1:任务 1 | 8:00 AM | 4:00 PM | 8 |
[转载自这个图片。
在另一张工作表上,我有一个表格,其中列出了所有项目和相关任务(“项目和任务 ID”列)。每个项目都有一个相关类型(即工作时间、假期、病假或休假)。
项目信息表(“projectTable”)如下所示:
A | 乙 | |
---|---|---|
1 | 项目和任务 ID | 类型 ID |
2 | 项目 1:任务 1 | 工作 |
3 | 项目 2:任务 1 | 工作 |
4 | 节假日营业时间 | 假期 |
5 | 病假 | 生病的 |
[转载自这个图片。
为了计算我累积的病假/休假时长,我需要找出我在给定计费周期内工作了多少小时。但是,我还需要将我提交的总小时数(包括病假/休假/假日)保留在小时日志中,因为我会用它们来计算总 FTE。
以下是我目前使用的方法,用于对一个计费周期 (12/26/22-1 / 25/23) 内与类型 ID 为“工作”的项目相关的所有小时数进行汇总:
=SUM(IFERROR(IF(COUNTIFS(projectTable[Project & Task ID],FILTER(timeLog[Project and Task],(timeLog[Date]<=DATE(2023,1,25))*(timeLog[Date]>DATE(2022,12,25)),0),projectTable[Type ID],"Worked")>0,timeLog[Hours],0),0))
首先,我使用 FILTER 获取指定期间的所有表条目。
FILTER(timeLog[Project and Task],(timeLog[Date]<=DATE(2023,1,25))*(timeLog[Date]>DATE(2022,12,25)),0)
然后,COUNTIFS 检查是否有任何项目属于“已工作”TypeID:
COUNTIFS(projectTable[Project & Task ID],FILTER(timeLog[Project and Task],(timeLog[Date]<=DATE(2023,1,25))*(timeLog[Date]>DATE(2022,12,25)),0),projectTable[Type ID],"Worked")
然后,IF 返回每一行的小时数值,IFERROR“修复”我一直收到的错误,SUM 计算该期间的总“工作”小时数。
但是,这似乎不是最好的方法——它很脆弱,返回我意想不到的错误,总的来说,我感觉我的方法不对。有什么更好的方法吗?
答案1
我没有研究过你的解决方案,但乍一看,我同意它似乎不必要地复杂。我建议你在每小时日志中添加第六列,其中包含
=VLOOKUP(B2, projectTable!$A$2:$B$5, 2, FALSE)
这会将每小时日志中的“项目和任务”转换为项目信息表中的“类型 ID”,如下所示:
A | 乙 | C | 德 | 埃 | F | |
---|---|---|---|---|---|---|
1 | 日期 | 项目与任务 | 开始时间 | 时间结束 | 小时 | 类型 ID |
2 | 2023 年 1 月 1 日 | 项目 1:任务 1 | 8:00 AM | 10:00 AM | 2 | 工作 |
3 | 2023 年 1 月 1 日 | 项目 2:任务 1 | 10:00 AM | 4:00 PM | 6 | 工作 |
4 | 2023 年 1 月 6 日 | 节假日营业时间 | 8:00 AM | 4:00 PM | 8 | 假期 |
5 | 2023 年 1 月 7 日 | 病假 | 8:00 AM | 12:00 PM | 4 | 生病的 |
6 | 2023 年 2 月 6 日 | 项目 1:任务 1 | 8:00 AM | 4:00 PM | 8 | 工作 |
COUNTIFS
然后,使用和就变得很简单了SUMIFS
。例如,在其他地方(例如 A8:B10),在 A8:A10 中输入类型,输入
=SUMIFS($E$2:$E$6, $A$2:$A$6,">"&DATE(2022,12,25), $A$2:$A$6,"<"&DATE(2023,1,23), $F$2:$F$6,A8)
在 B8 中,向下拖动/填充:
A | 乙 | ||
---|---|---|---|
8 | 工作 | 8 | |
9 | 假期 | 8 | |
10 | 生病的 | 4 |
当然,硬编码日期可以用单元格引用代替。
请注意,我回答了一个类似但更复杂的问题,问题 八年前。