问题

我使用查询来从列日期（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&#39;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 be

sample 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=&quot;&quot;,, {WEEKNUM(A72:A86),B72:C86})), &quot;your query here using Col1, sum(Col3) etc&quot;)


## EDIT
Just checked your sheet.  
Try this query in your formula

    =QUERY(INDEX(IF(A2:A22=&quot;&quot;,, {WEEKNUM(A2:A22),B2:C22})), 
             &quot;Select Col1, sum(Col2) where Col1 is not null 
              group by Col1 label Col1 &#39;week&#39;, sum(Col2) &#39;sum/week&#39; &quot;)

*(**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'")

=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 ) } ) ) ) }

={"Week","Sum";index(map(unique(weeknum(filter(A2:A,A2:A<>""))),lambda(z,{z,sumif(weeknum(A:A),z,B:B)})))}