Google Spreadsheets QUERY()可以合并多个工作表，同一个工作簿 [英] Google Spreadsheets QUERY() to combine multiple sheets, same workbook
问题描述
目标:我正在gSheets中使用QUERY()合并同一工作簿中多个工作表的数据.数据是从GA中提取的数据，分为几小段以防止采样.这意味着它具有所有相同的字段，我只是将它们拼凑在一起进行分析.
Goal: I'm using QUERY() in gSheets to combine data from multiple sheets in the same workbook. The data is an extract from GA broken down into small segments to prevent sampling. This means it has all the same fields, I'm just piecing it back together for analysis.
我想使用QUERY()进行此操作，因为使用Google Analytics(分析)表格插件将数据连接到自动更新.这个想法是，当它在将来进行更新时，它将重新组合在一起，并且分析将在我们方面进行而无需任何额外的工作(或人为错误的风险).
I would like to use QUERY() to do this because the data is hooked up to auto-update using the Google Analytics Sheets addon. The idea is that when it updates in the future it will piece itself together again and the analysis will run without any additional effort (or risk of human error) on our part.
我遵循了Google支持语法，其中有一项内容适合多张纸.但是，在其他帖子上也提到了这一点，并且似乎与其他人一起使用时(与IMPORTRANGE结合使用引用外部工作簿时).在同一工作簿中必须有类似的方法.
I followed the Google Support syntax, with one accomodation to fit the multiple sheets. This was mentioned on other posts and seems to work for other people--when combined with IMPORTRANGE referencing outside workbooks, though. There has to be a similar way to do it within the same workbook.
我尝试过的事情:
=QUERY({'LandingPages-Oct1'!A16:F,'LandingPages-Oct2'!A16:F},"select *",0)
^所有逗号分隔导致工作表并排导入
^ All comma separation causes the sheets to import side by side
=QUERY({'LandingPages-Oct1'!A16:F;'LandingPages-Oct2'!A16:F},"select *",0)
^分号分隔的表格会导致第一张数据出现，但第二张数据不会出现
^ Semicolon separating sheets causes the first sheet of data to appear, but not the second
=QUERY({'LandingPages-Oct1'!A16:F;'LandingPages-Oct2'!A16:F};"select *",0)
^分号分隔的查询参数不返回结果
^ Semicolon separating query parameters returns no results
我想念什么?
推荐答案
用分号分隔工作表会导致出现第一行数据，但不会出现第二行
Semicolon separating sheets causes the first sheet of data to appear, but not the second
这通常意味着您没有滚动到工作表的底部来查看数据的第二部分，位于第二个工作表的第一行的所有空行下.
This usually means you didn't scroll to the bottom of the sheet to see the second part of data there, under all of the empty rows from the first sheet.
由于您可能不想导入空行，因此将其过滤掉(按预期为非空的某些列):
Since you probably don't want to import the empty rows, filter them out (by some column that is expected to be nonempty):
={filter('LandingPages-Oct1'!A16:F, len('LandingPages-Oct1'!A16:A));
filter('LandingPages-Oct2'!A16:F, len(LandingPages-Oct2'!A16:A))}
完全不需要查询；查询字符串"select *"表示您不是真正的查询.
No need for query at all; query string "select *" means you are not really querying.
这篇关于Google Spreadsheets QUERY()可以合并多个工作表，同一个工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
