英文:
How to use Query() to generate subtotal group by a column under the Query() result?
问题
我在想是否有智能和优雅的方法可以在使用Google Sheets的query()函数时，在查询结果下面创建小计。
参考下面的图片，如果我有包含货币值的DATA，是否可以使用Query()或某种组合Query()来生成基于货币的小计表格？
挑战在于DATA中的行数和货币种类数是动态的。要使用查询生成Report A并不难，但我无法想出一种智能的方式（而不是使用App Scripts或数据透视表）在货币数量是动态的情况下创建Report B。
请帮忙。
英文:
I am wondering if there are smart and elegant ways to create subtotal below a query result in using Google Sheets' query() function.
Referring to the image below, if I have DATA with Currency values, can I generate a table with subtotal based on currency using Query() or some forms of combined Query()?
The challenge is that the number of rows and number of currencies are dynamic in DATA. To use query to generate Report A is not hard as shown but I just cannot think of a smart way using formulas (instead of App Scripts or pivot table) to create Report B when number of currencies is dynamic.
Please help.
答案1
得分: 1
<!-- 语言：js -->
*这是一个你可以尝试的方法：*
```js
=reduce(A3:C3, unique(tocol(B4:B, 1)), lambda(a, c, vstack(a, filter(A:C, B:B=c), hstack("Subtotal", c, sumif(B:B, c, C:C)))))
- 你可以使用条件格式设置来突出显示“Subtotal”行，以防万一
[1]: https://i.stack.imgur.com/byBIC.png
英文:
<!-- language-all: js -->
Here's one approach you may test out:
=reduce(A3:C3,unique(tocol(B4:B,1)),lambda(a,c,vstack(a,filter(A:C,B:B=c),hstack("Subtotal",c,sumif(B:B,c,C:C)))))
- you can use conditional formatting to highlight the
Subtotalrows just in case
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论