英文:
Group by WEEKNUM in google spreadsheet
问题
我使用查询来从列日期（J）中获取周数，并在这里汇总成本的公式如下：```excel=QUERY(J4:K, "SELECT WEEKNUM(J), SUM(K) WHERE J IS NOT NULL GROUP BY WEEKNUM(J) LABEL WEEKNUM(J) 'Week', SUM(K) 'Total'")
我尝试使用MONTH而不是WEEKNUM，它完全正常运行，我真的不知道错误可能在哪里。
示例文件：https://docs.google.com/spreadsheets/d/1GO-BwaEWOSf1AnR7rKOMYcpZ_12crehiSJTkFVOOqx8/edit?usp=sharing
更新：
[2]: https://i.stack.imgur.com/w6Lhl.png
为什么会有间隙？
<details><summary>英文:</summary>I'm using query for getting the weeknum from column Date (J) and sum the cost here is the formula
=QUERY(J4:K, "SELECT WEEKNUM(J), SUM(K) WHERE J IS NOT NULL GROUP BY WEEKNUM(J) LABEL WEEKNUM(J) 'Week', SUM(K) 'Total'")
I tried using MONTH instead of WEEKNUM and it run perfectly, I really don't know where the error might besample file : https://docs.google.com/spreadsheets/d/1GO-BwaEWOSf1AnR7rKOMYcpZ_12crehiSJTkFVOOqx8/edit?usp=sharing[![\[1\]: https://i.stack.imgur.com/0uYlr.png][1]][1]________________________________________________________________update :[![enter image description here][2]][2]why there's a gap?[1]: https://i.stack.imgur.com/HfEcB.png[2]: https://i.stack.imgur.com/w6Lhl.png</details># 答案1**得分**: 3`WEEKNUM` 在 `QUERY` 中**不存在**您可以尝试以下操作
=QUERY(INDEX(IF(A72:A86="",, {WEEKNUM(A72:A86),B72:C86})), "在此处使用 Col1，sum(Col3) 等编写您的查询")
## 编辑刚刚检查了您的表格。请在您的公式中尝试这个查询
=QUERY(INDEX(IF(A2:A22="",, {WEEKNUM(A2:A22),B2:C22})),
"选择 Col1，sum(Col2) where Col1 不为空
按 Col1 分组 标签 Col1 '周'，sum(Col2) '周汇总' ")
*(根据您的范围和地区* **自行调整** *公式)*<details><summary>英文:</summary>`WEEKNUM` does **not exist in `QUERY`**Instead you can try the following=QUERY(INDEX(IF(A72:A86="",, {WEEKNUM(A72:A86),B72:C86})), "your query here using Col1, sum(Col3) etc")## EDITJust checked your sheet.Try this query in your formula=QUERY(INDEX(IF(A2:A22="",, {WEEKNUM(A2:A22),B2:C22})),"Select Col1, sum(Col2) where Col1 is not nullgroup by Col1 label Col1 'week', sum(Col2) 'sum/week' ")*(**Do** adjust the formulae according to your ranges and locale)*</details># 答案2**得分**: 1`WEEKNUM()` 不被 `QUERY()` 识别为 SQL 函数。您可以尝试以下方式：```excel=QUERY({INDEX(WEEKNUM(A2:INDEX(A2:A,COUNTA(A2:A))), B2:INDEX(B2:B,COUNTA(A2:A))}, "select Col1, sum(Col2) group by Col1 label Col1 'Week', sum(Col2) 'Sum'")
英文:
WEEKNUM() is not recognized by QUERY() as SQL function. You may try-
=QUERY({INDEX(WEEKNUM(A2:INDEX(A2:A,COUNTA(A2:A)))),B2:INDEX(B2:B,COUNTA(A2:A))},"select Col1, sum(Col2) group by Col1 label Col1 'Week', sum(Col2) 'Sum'")
答案3
得分: 1
这里有另一种方法 非查询型
={ "Week", "Sum" ; index( map( unique( weeknum( filter( A2:A, A2:A<>"" ) ) ), lambda( z, { z, sumif( weeknum( A:A ), z, B:B ) } ) ) ) }
英文:
Here's another approach non-query based
={"Week","Sum";index(map(unique(weeknum(filter(A2:A,A2:A<>""))),lambda(z,{z,sumif(weeknum(A:A),z,B:B)})))}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论