谷歌电子表格 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 Sheets 插件将数据连接到自动更新.这个想法是，当它在未来更新时，它会再次拼凑起来，分析将在我们没有任何额外工作(或人为错误的风险)的情况下运行.

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

我错过了什么?

示例数据: https://docs.google.com/spreadsheets/d/1STuBdXPCY-mtJdmKZVblR8WlvLaRPa3tl4Kme10sQBQ/edit?usp=sharing

推荐答案

分号分隔表导致出现第一张数据，但不出现第二张数据

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.

这篇关于谷歌电子表格 QUERY() 合并多个工作表，同一个工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！