按周排序时间表记录的小时数

debugcn 发表于 Dev

乔纳·格林

因此，我有一个正在处理的项目的时间表，我想看看我每周工作多少小时。

我从这篇文章中找到了答案：在Google表格中创建每周/每月/每年折线图，以汇总同一周/月/年范围内的金额，这真让我感到很遥远。

多亏了玩家player0，每月和每年的查询都非常有效。但是，每周排序对我来说很烦。我现在使用的代码来自该帖子：

=ARRAY_CONSTRAIN(ARRAYFORMULA( 
IF(J2="Weekly", QUERY({"week "&WEEKNUM(A2:A)&" "&YEAR(A2:A), E2:E, YEAR(A2:A)+WEEKNUM(A2:A)*0.083}, 
"select Col1,sum(Col2),Col3 where Col2 is not null group by Col1,Col3 order by Col3 label sum(Col2)''", 0), 
IF(J2="Monthly", QUERY({TEXT(A2:A, "mmmm")&" "&YEAR(A2:A), E2:E, YEAR(A2:A)+MONTH(A2:A)*0.083}, 
"select Col1,sum(Col2),Col3 where Col2 is not null group by Col1,Col3 order by Col3 label sum(Col2)''", 0), 
IF(J2="Yearly", QUERY({YEAR(A2:A), E2:E}, 
"select Col1,sum(Col2) where Col2 is not null group by Col1 label sum(Col2)''", 0), )))), 999^99, 2)

这是床单的样子

我对Google表格的了解很少，因此您必须对我保持简单性XD

玩家0

尝试：

=ARRAY_CONSTRAIN(ARRAYFORMULA( 
 IF(J2="Weekly",  QUERY(QUERY({"week "&WEEKNUM(A2:A)&" "&YEAR(A2:A), E2:E, "w"&YEAR(A2:A)&TEXT(WEEKNUM(A2:A), "00")}, 
 "select Col1,sum(Col2),Col3 where Col2 is not null group by Col1,Col3 order by Col3 label sum(Col2)''", 0), "offset 1", 0), 
 IF(J2="Monthly", QUERY({TEXT(A2:A, "mmmm")&" "&YEAR(A2:A), E2:E, YEAR(A2:A)+MONTH(A2:A)*0.083}, 
 "select Col1,sum(Col2),Col3 where Col2 is not null group by Col1,Col3 order by Col3 label sum(Col2)''", 0), 
 IF(J2="Yearly",  QUERY({YEAR(A2:A), E2:E}, 
 "select Col1,sum(Col2) where Col2 is not null group by Col1 label sum(Col2)''", 0), )))), 999^99, 2)