Google表格 - 查询枢轴 - 显示所有结果

发布于 2025-01-26 09:15:19 字数 8 浏览 3 评论 0原文

continue

In google sheets have a pivot table with columns with text day ranges 1-30, 31-60, 61-90, 90-120, >120 where some records fall under those day ranges.

This is sample data:

Unique	Account Doc	Amount	Day Range
1	123456	1000	1-30
2	561530	2000	>120
3	123456	1500	61-90
4	25106	3000	1-30

I can get this data to pivot using standard pivot tables but users needs it to be clean without the pivot table buttons and formatting. I am trying to convert to google query function but I am stumped. I'd could try the option of pivoting and then calling the pivot to a query to remove the formatting but that seems redundant and there is a lot of other things happening in my sheet so afraid of making updating slow.

End result would look like below where the day ranges are pivoted and amount is showing for each record.

All columns need to be preserved as if there are null values all results appear even null/zero values for Amount as with column 31-60 and 91-120 showing the columns with no results. I use a unique id to ensure that all records come back as some of the

I can get the query to pivot with:

=query(rawdata,"Select Z,B,E,D,F,H,J, Sum(N) where B="&$C$31&" Group by Z,B,E,D,F,H,J pivot AA order by F",1)

However if the filter on B only has some day ranges and not others it will only show those columns with data.

Link to google sheet with sample data:

https://docs.google.com/spreadsheets/d/1seX4T3M8Mo9eVZYteyAbUG2zmWM9VCZ2-6oDd76QMA8/edit?usp=sharing

Result Query:

Unique	Account Doc	1-30	61-90	>120
1	123456	1000
2	561530			2000
3	123456		1500
4	25106	3000

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

清眉祭 2025-02-02 09:15:19

尝试：

=ARRAYFORMULA(QUERY(QUERY({'Copy of raw data'!A:AA; IFERROR(VLOOKUP(
 SEQUENCE(COUNTA(UNIQUE('Copy of raw data'!B2:B)&TRANSPOSE(UNIQUE('Copy of raw data'!AA2:AA)))), {
 SEQUENCE(COUNTA(UNIQUE('Copy of raw data'!B2:B)&TRANSPOSE(UNIQUE('Copy of raw data'!AA2:AA)))), 
 SPLIT(FLATTEN(UNIQUE('Copy of raw data'!B2:B)&"×"&TRANSPOSE(UNIQUE('Copy of raw data'!AA2:AA))), "×"), 
 TO_TEXT(SPLIT(FLATTEN(UNIQUE('Copy of raw data'!B2:B)&"×"&TRANSPOSE(UNIQUE('Copy of raw data'!AA2:AA))), "×"))}, 
 {0, 2, SEQUENCE(1, 24, 0, 0), 5}, 0))},
 "select Col26,Col2,Col5,Col4,Col6,Col8,Col10,sum(Col14) 
  where 1=1 "&IF(B1="",, " and Col2="&B1)&"
  group by Col26,Col2,Col5,Col4,Col6,Col8,Col10 
  pivot Col27 
  order by Col6", 1), "where Col1 is not null", 1))

try:

=ARRAYFORMULA(QUERY(QUERY({'Copy of raw data'!A:AA; IFERROR(VLOOKUP(
 SEQUENCE(COUNTA(UNIQUE('Copy of raw data'!B2:B)&TRANSPOSE(UNIQUE('Copy of raw data'!AA2:AA)))), {
 SEQUENCE(COUNTA(UNIQUE('Copy of raw data'!B2:B)&TRANSPOSE(UNIQUE('Copy of raw data'!AA2:AA)))), 
 SPLIT(FLATTEN(UNIQUE('Copy of raw data'!B2:B)&"×"&TRANSPOSE(UNIQUE('Copy of raw data'!AA2:AA))), "×"), 
 TO_TEXT(SPLIT(FLATTEN(UNIQUE('Copy of raw data'!B2:B)&"×"&TRANSPOSE(UNIQUE('Copy of raw data'!AA2:AA))), "×"))}, 
 {0, 2, SEQUENCE(1, 24, 0, 0), 5}, 0))},
 "select Col26,Col2,Col5,Col4,Col6,Col8,Col10,sum(Col14) 
  where 1=1 "&IF(B1="",, " and Col2="&B1)&"
  group by Col26,Col2,Col5,Col4,Col6,Col8,Col10 
  pivot Col27 
  order by Col6", 1), "where Col1 is not null", 1))