英文:
Creating multiple rows and merging columns
问题
| ID | A | P | Q |
|---|---|---|---|
| 1 | PQ1 | 1 | 4 |
| 1 | PQ2 | 2 | 5 |
| 1 | PQ3 | 3 | 6 |
| 2 | PQ1 | 10 | 40 |
| 2 | PQ2 | 20 | 50 |
| 2 | PQ3 | 30 | 60 |
| 3 | PQ1 | 1 | 400 |
| 3 | PQ2 | 2 | 50 |
| 3 | PQ3 | 30 | 60 |
英文:
I have the following input data:
| ID | P1 | P2 | P3 | Q1 | Q2 | Q3 |
|---|---|---|---|---|---|---|
| 1 | 1 | 2 | 3 | 4 | 5 | 6 |
| 2 | 10 | 20 | 30 | 40 | 50 | 60 |
| 3 | 1 | 2 | 30 | 400 | 50 | 60 |
And I want to change it to:
| ID | A | P | Q |
|---|---|---|---|
| 1 | PQ1 | 1 | 4 |
| 1 | PQ2 | 2 | 5 |
| 1 | PQ3 | 3 | 6 |
| 2 | PQ1 | 10 | 40 |
| 2 | PQ2 | 20 | 50 |
| 2 | PQ3 | 30 | 60 |
| 3 | PQ1 | 1 | 400 |
| 3 | PQ2 | 2 | 50 |
| 3 | PQ3 | 30 | 60 |
So multiple rows for each ID, with one column stating the number behind P and Q columns, and then two columns with the corresponding values.
答案1
得分: 1
这可能需要根据您的Excel版本以及输入/输出的格式进行一些修改。
对于第一列：
=TOCOL(HSTACK(Table1[ID], Table1[ID], Table1[ID]))
对于第二列：
= "PQ" & MOD(SEQUENCE(ROWS(B9#), , 3), 3) + 1
其中，对于我而言，B9#指的是新ID列的第一个单元格。
对于第三列：
=IFS(C9#="PQ1", XLOOKUP(B9#, Table1[ID], Table1[P1]), C9#="PQ2", XLOOKUP(B9#, Table1[ID], Table1[P2]), C9#="PQ3", XLOOKUP(B9#, Table1[ID], Table1[P3])
其中，C9指的是“A”列的第一个单元格。
类似地，对于最后一列：
=IFS(C9#="PQ1", XLOOKUP(B9#, Table1[ID], Table1[Q1]), C9#="PQ2", XLOOKUP(B9#, Table1[ID], Table1[Q2]), C9#="PQ3", XLOOKUP(B9#, Table1[ID], Table1[Q3])
正如我所说，这可能需要一些修改，但如果需要，请告诉我。
英文:
This may need some alterations depending on your Excel version, and how your inputs/outputs are formatted.
For the first column:
=TOCOL(HSTACK(Table1[ID],Table1[ID],Table1[ID]))
For the second:
="PQ"&MOD(SEQUENCE(ROWS(B9#),,3),3)+1
, where B9 for me refers to the first cell of the new ID column.
For the third column:
=IFS(C9#="PQ1", XLOOKUP(B9#, Table1[ID], Table1[P1]), C9#="PQ2", XLOOKUP(B9#, Table1[ID], Table1[P2]), C9#="PQ3", XLOOKUP(B9#, Table1[ID], Table1[P3]))
, where C9 refers to the first cell of the "A" column.
Similarly, for the last column:
=IFS(C9#="PQ1", XLOOKUP(B9#, Table1[ID], Table1[Q1]), C9#="PQ2", XLOOKUP(B9#, Table1[ID], Table1[Q2]), C9#="PQ3", XLOOKUP(B9#, Table1[ID], Table1[Q3]))
As I said, this might need some alterations, but let me know if so.
答案2
得分: 0
只要Excel版本没有限制，您可以尝试以下的数组公式，它会将整个结果，包括标题一并显示出来：
我们定义了每个分组的长度，使用变量 gr（在我们的示例中为 3）。主要思路是使用 WRAPROWS 函数按照 gr 分组包装行。我们将其用于生成 A、P 和 Q 列。我们使用 SEQUENCE 生成 ID 列。其余部分只是使用了在我的回答中有文档记录的 REDUCE/VSTACK-HSTACK 模式，回答了以下问题：如何将Excel中的表格从垂直转换为水平，但长度不同。
为了更容易维护这个公式，只有 in 和 pqs 这两个名称取决于输入范围，其余的名称都是从它们生成的，使用 DROP 和 TAKE 函数。
英文:
Assuming no excel version constraints as per the tags listed in the question, you can try the following array formula, which spills the entire result including the header:
=LET(gr,3, in, A2:G4, pqs, B1:G1, ids, TAKE(in,,1), data, DROP(in,,1),
t, TRANSPOSE(WRAPROWS(pqs,gr)), A, LEFT(INDEX(t,,1)) & INDEX(t,,2),
REDUCE({"ID","A","P","Q"}, ids, LAMBDA(ac,i, LET(r, SEQUENCE(gr,,i,0),
VSTACK(ac, HSTACK(r, A, TRANSPOSE(WRAPROWS(INDEX(data,i),gr))))))))
Here is the output:
We define the length of each group under the variable gr (in our case is 3). The main idea is to use WRAPROWS function to wrap rows by gr. We use it for generating the A, P, and Q columns. SEQUENCE is used to generate the column ID. The rest is just to use REDUCE/VSTACK-HSTACK pattern as it is documented in my answer to the following question: how to transform a table in Excel from vertical to horizontal but with different length.
In order to easily maintain the formula, only in and pqs names depend on the input range, the rest are names generated from them, using the DROP and TAKE functions.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论