Google表格，将2张纸合并为一张的数组公式，并附加一个新列以指定数据来自哪个表
我正在尝试合并Sheet1＆amp; Sheet2到Sheet3。在Sheep3上，我希望A列A显示数据来自哪个表。
Sheet1（源＃1）
| A | B |
|---|---|
| John Doe | 123 Street |
Sheet2（源＃2）
| A | B |
|---|---|
| Jane Smith | 999 Street |
Sheet3-使用以下公式进行预期结果。
| A | B | C |
|---|---|---|
| Sheet1 | John Doe | 123 Street |
| Sheet2 | Jane Smith | 999 Street |
我尝试了以下配方。
=ARRAYFORMULA(
{
{"Sheet1",FILTER('Sheet1'!A1:B,'Sheet1'!A1:A<>"")};
{"Sheet2",FILTER('Sheet2'!A1:B,'Sheet1'!A1:A<>"")}
}
)
=ARRAYFORMULA(
{
{"Sheet1",QUERY('Sheet1'!A1:B,"SELECT * WHERE A is not null")};
{"Sheet2",QUERY('Sheet2'!A1:B,"SELECT * WHERE A is not null")}
}
)
两者都会给出错误：在Array_literal中，一个或多个行的数组是缺少的值。
我在做什么错？谢谢！
I am trying to merge Sheet1 & Sheet2 into Sheet3. On Sheet3 I would like Column A to display which Sheet the data came from.
Sheet1 (Source #1)
| A | B |
|---|---|
| John Doe | 123 Street |
Sheet2 (Source #2)
| A | B |
|---|---|
| Jane Smith | 999 Street |
Sheet3 - Expected results using the formulas below.
| A | B | C |
|---|---|---|
| Sheet1 | John Doe | 123 Street |
| Sheet2 | Jane Smith | 999 Street |
I have tried the following formulas.
=ARRAYFORMULA(
{
{"Sheet1",FILTER('Sheet1'!A1:B,'Sheet1'!A1:A<>"")};
{"Sheet2",FILTER('Sheet2'!A1:B,'Sheet1'!A1:A<>"")}
}
)
=ARRAYFORMULA(
{
{"Sheet1",QUERY('Sheet1'!A1:B,"SELECT * WHERE A is not null")};
{"Sheet2",QUERY('Sheet2'!A1:B,"SELECT * WHERE A is not null")}
}
)
Both give the error: In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.
What am I doing wrong? Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
尝试：
Try:
由于您无法将表名称附加到多行，因此您需要获得创意。
这样做的是将列组合成一个，并将每行的表格与查询中使用的相同定界符一起添加。然后附加两个结果，然后使用上述定界符将它们分开。
公式：
输出：
Since you can't append the sheet name to multiple rows, you need to get creative.
What this does is combine the columns into one and append the sheet name per row together with the same delimiter used inside the query. Then append both results then proceed to split them using the said delimiter.
Formula:
Output: