英文:
Is there a way in Google Sheets to order cells in a range in the way depicted in the attached file and with one single formula?
问题
在Google Sheets中，我有如下情况：
在A列中，我有一些键的列表，带有关联的顺序（B列）。然后，在D列中，我有一些与先前的键关联的值，这些键分散在D列，并且有一个子顺序（F列）。
在H列中，我有所需的结果；所有值按照它们的子顺序列出，并分组在关联的键下，并且按照来自B列的键顺序进行排序。此外，这些键在前缀和后缀之间带有多个连字符。
我想要在单元格H2中使用一个单一的公式来实现这些结果，或者在单元格H1中包括标题（在示例中，我使用了多个sort和filter公式）。
以下是电子表格的链接，以防您需要它：
https://docs.google.com/spreadsheets/d/1XDaJFE_BYJpkhdq32iPHzucjf78mP2WCrDN76U5KwZM/edit?usp=sharing
谢谢。
英文:
<br/>
In Google Sheets I have a situacion like this:<br/>
<br/>
https://i.stack.imgur.com/P2GZI.png
<br/>
In column A:A I have some keys list, with an associated order (column B:B). Then I have some values in column E:E associated with the previous keys spread in column D:D and with a suborder (column F:F).<br/>
In column H:H I have the desired results; all the values listed, following their suborder, and grouped under the associated key, and everything ordered by the keys order takeb from column B:B. Besides that, the keys are prefixed and suffixed with several hypens.<br/>
I would like to achieve these results with one single formula located in cell H2, or cell H1 including the header (in the example I am using several sort & filter formulas).<br/>
Down below is the link to the spreadsheet in case you need it. <br/>
The formula should be able to accept as many keys, orders, values and suborders needed.<br/>
https://docs.google.com/spreadsheets/d/1XDaJFE_BYJpkhdq32iPHzucjf78mP2WCrDN76U5KwZM/edit?usp=sharing
<br/>
Thank you.
答案1
得分: 1
*你可以尝试：*
=reduce("Header",tocol(sort(A2:A,B2:B,1),1),lambda(a,c,{a;"--- "&c&" ---";choosecols(sort(filter(E:F,D:D=c),2,1),1);if(,,}))
<details>
<summary>英文:</summary>
<!-- language-all: js -->
*You may try:*
=reduce("Header",tocol(sort(A2:A,B2:B,1),1),lambda(a,c,{a;"--- "&c&" ---";choosecols(sort(filter(E:F,D:D=c),2,1),1);if(,,)}))
[![enter image description here][1]][1]
[1]: https://i.stack.imgur.com/l2Whq.png
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论