根据另一列将多个列堆叠在一个列中 [英] Stack multiple columns in a single column based on another column
问题描述
In sheet1, I have something like this (A to D are headers - sample sheet here):
| A | B | C | D |
|---|---|---|---|
| X | X1 | X2 | |
| 是 | Y1 | Y2 | Y3 |
| Z | Z1 |
在sheet2中，我想要这样的东西:
And in sheet2, I want something like this:
| A | B |
|---|---|
| X | X1 |
| X2 | |
| 是 | Y1 |
| Y1 | |
| Y1 | |
| Z | Z1 |
* A列中的值仅在第一个实例上存在(它不与它下面的单元格合并)
Sheet1数据来自google表单提交，但我们要针对sheet2中的表示例构建它们，其中sheet1列B到D堆叠在sheet2列B中.
Sheet1 data comes from google form submissions, but we want to structure them as to the table sample in sheet2, where sheet1 columns B to D are stacked in sheet2 column B.
现在，我们使用以下内容将B到D列合并到与A列中的值对齐的单个单元格中:
For now, we're using the following to merge columns B to D in a single cell aligned with the values in column A:
=ArrayFormula(Sheet1!A:A&CHAR(10)&Sheet1!B:B&CHAR(10)&Sheet1!C:C&CHAR(10)&Sheet1!C:C&CHAR(10)Sheet1!D:D)
但是，这带来了很多问题，因为即使没有所谓的第二行，换行符仍然会存在，并且我们必须手动更新这些项目的状态(因为它们已用于监视).
However, this presents a lot of problems since line breaks would still be there even when there's no supposed second line, and that we have to manually update status of these items (since they're used for monitoring).
如果我们可以按预期逐行进行操作，则可以使某些任务自动化.我们尝试过使用QUERY，但无济于事(尽管我认为可以通过该功能...不确定).
If we can have it line by line as what is expected, we'd be able to automate some of the tasks. We tried playing with QUERY, but to no avail (although I think it's possible via that function... not sure).
希望从社区中获得想法.谢谢！
Hoping to get ideas from the community. Thanks!
推荐答案
我在A1中添加了具有以下公式的新工作表("Erik帮助"):
I've added a new sheet ("Erik Help") with the following formula in A1:
= ArrayFormula({"Header 1"，"Header 2"; QUERY(SPLIT(FLATTEN({FILTER(INDIRECT("Sheet1！A2:A"))，INDIRECT("Sheet1！A2:: A")"")" |"过滤器(INDIRECT("Sheet1！B2:B")，INDIRECT("Sheet1！A2:A")<；"")，IF(过滤器(ROW(INDIRECT("Sheet1！A2:A")))，INDIRECT("Sheet1！A2:A"))，";)&"|"& FILTER(INDIRECT("Sheet1！C2:D")，INDIRECT("Sheet1！A2:A")")})，"|，1,0)，"选择*哪里Col2不为空")})
此公式首先创建两个标题.
This formula creates the two headers first.
您会注意到 INDIRECT 大量用于引用范围.这是因为您将在该工作表中输入表单数据.如果该公式没有办法锁定"，则范围，则每当将新行添加到表单接收表时，这些范围就会向下移动一个范围.在大多数其他应用中，您可以锁定"密码.通过使用全列引用(例如A:A而不是A2:A)来确定这些范围.但是鉴于您要在此处执行的操作的具体细节， INDIRECT 是最简单的方法.请记住，由于使用了INDIRECT，因此如果将Sheet1重命名为其他名称(例如"Form Responses 1")，引用将不会自动更改.您将需要手动更改每个参考.或使用查找/替换，选择特定范围"设置为公式单元格，然后选中也在公式内搜索"框.
You'll notice the heavy use of INDIRECT to reference ranges. This is because you'll have form data coming into that sheet; and if the formula doesn't have a way to "lock" ranges, those ranges will shift down one every time a new row is added onto the form-intake sheet. In most other applications, you can "lock" those ranges by using full-column references (e.g., A:A instead of A2:A). But given the specifics of what you're trying to do here, INDIRECT was the least complex approach. Keep in mind that, because INDIRECT is used, the references will not automatically change if you rename Sheet1 to something else (like "Form Responses 1"). You will need to change each reference manually. Or use FIND/REPLACE, select "Specific Range" set to the formula cell, and check the "Also search within formulas" box.
这里的想法是将A2:A中的每个值连接到B2:D同一行中的每个值，并在其之间使用竖线符号(作为以后的 SPLIT 标记).由于您只想查看Col-B值旁边的Col-A值，因此首先单独处理Col-A" ||& Col-B;然后在其他所有内容后都添加一个空格，而不是Col-A.
The idea here is that every value from A2:A is concatenated to every value in the same row of B2:D, with a pipe symbol between (as a SPLIT marker for later). Since you only want to see the Col-A values beside Col-B values, Col-A&"|"&Col-B are first processed alone; then a blank is appended instead of Col-A for everything else.
FILTER 仅用于处理Col-A中有数据的行.
FILTER is used to only process rows for which there is data in Col-A.
SPLIT 将在管道符号处进行的组合(如上所述)进行拆分，形成两列.
SPLIT splits the combinations made (as described above) at the pipe symbol, forming two columns.
QUERY 仅保留第二列中包含某些内容的 SPLIT 的结果.
QUERY keeps only those results of the SPLIT that have something in the second column.
这篇关于根据另一列将多个列堆叠在一个列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
