如何在Google Sheets中添加Hrs./Day与Total Hrs./Week列

我是Google Sheets新手，现有表格数据如下（注：原始数据中"Tast"应为"Task"）：

原始CSV数据：

Date,Project name,Minutes spent,Time Start,Time End,Day
09/09/2022,Tast I,120,11:30 AM,1:30 PM,Friday
09/09/2022,Tast II,120,1:30 PM,3:30 PM,Friday
09/09/2022,Tast II,90,3:30 PM,5:00 PM,Friday
09/09/2022,Tast II,120,6:30 PM,8:30 PM,Friday
10/09/2022,Tast III,120,8:30 AM,10:30 AM,Saturday
10/09/2022,Tast III,120,10:30 AM,12:30 PM,Saturday
10/09/2022,Tast III,120,12:30 PM,2:30 PM,Saturday
10/09/2022,Tast III,150,2:30 PM,5:00 PM,Saturday
10/09/2022,Tast III,210,6:30 PM,10:00 PM,Saturday
11/09/2022,,,,,Sunday
12/09/2022,Tast IV,120,8:30 AM,10:30 AM,Monday
12/09/2022,Tast IV,90,10:30 AM,12:00 PM,Monday
12/09/2022,Tast V,120,12:00 PM,2:00 PM,Monday
12/09/2022,Tast V,180,2:00 PM,5:00 PM,Monday

需求：添加**Hrs./Day（每日时长）和Total Hrs./Week（每周总时长，包含周日）**两列，最终效果如下：

目标CSV数据：

Date,Project name,Minutes spent,Time Start,Time End,Day,Hrs./Day,Total Hrs./ Week
09/09/2022,Tast I,120,11:30 AM,1:30 PM,Friday,7.5,19.5
09/09/2022,Tast II,120,1:30 PM,3:30 PM,Friday,7.5,19.5
09/09/2022,Tast II,90,3:30 PM,5:00 PM,Friday,7.5,19.5
09/09/2022,Tast II,120,6:30 PM,8:30 PM,Friday,7.5,19.5
10/09/2022,Tast III,120,8:30 AM,10:30 AM,Saturday,12,19.5
10/09/2022,Tast III,120,10:30 AM,12:30 PM,Saturday,12,19.5
10/09/2022,Tast III,120,12:30 PM,2:30 PM,Saturday,12,19.5
10/09/2022,Tast III,150,2:30 PM,5:00 PM,Saturday,12,19.5
10/09/2022,Tast III,210,6:30 PM,10:00 PM,Saturday,12,19.5
11/09/2022,,,,,Sunday,0,8.5
12/09/2022,Tast IV,120,8:30 AM,10:30 AM,Monday,8.5,8.5
12/09/2022,Tast IV,90,10:30 AM,12:00 PM,Monday,8.5,8.5
12/09/2022,Tast V,120,12:00 PM,2:00 PM,Monday,8.5,8.5
12/09/2022,Tast V,180,2:00 PM,5:00 PM,Monday,8.5,8.5

解决方案 #

假设数据从A1单元格开始，新增的Hrs./Day列对应G列，Total Hrs./Week对应H列，按以下步骤操作：

1. 计算每日时长（Hrs./Day） #

在G2单元格输入公式，然后下拉填充至所有行：

=SUMIF($A:$A, $A2, $C:$C)/60

• 逻辑：用SUMIF按日期（A列）汇总当天所有的Minutes spent（C列），除以60转换为小时数；周日无数据时自动显示0，符合需求。

2. 计算每周总时长（Total Hrs./Week） #

根据目标数据的周划分（周五至周四为一周），在H2单元格输入公式，然后下拉填充至所有行：

=SUMIFS($C:$C, ARRAYFORMULA(WEEKNUM($A:$A, 17)), WEEKNUM($A2, 17))/60

• 逻辑：
  • WEEKNUM($A2, 17)：将周五设为一周的起始日，返回当前日期所属的周数
  • ARRAYFORMULA(WEEKNUM($A:$A, 17))：批量计算A列所有日期的周数
  • SUMIFS汇总同一周内所有的Minutes spent，除以60转换为小时数，得到每周总时长

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