在 Excel 中,如果某一列中的值与另一个表中的数据匹配且在特定日期范围内,我该如何过滤、提取和求和?

在 Excel 中,如果某一列中的值与另一个表中的数据匹配且在特定日期范围内,我该如何过滤、提取和求和?

好的,就上下文而言,这个链接让我开始使用,但我现在试图将它应用到我的用例中: 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

当然,硬编码日期可以用单元格引用代替。


请注意,我回答了一个类似但更复杂的问题,问题 八年前。

相关内容