Google Sheets：排除查询中使用间接函数提取唯一数据的隐藏行

发布于 2025-01-11 14:47:14 字数 808 浏览 0 评论 0原文

我有一个 Apps 脚本，将 Google 表格中的每个选项卡名称拉入列表中。然后，我使用查询通过间接函数从每个选项卡中提取所有唯一名称（从名称列）。

当前每个选项卡中都有隐藏行（总共 15 个选项卡）。我的查询当前正在提取这些隐藏选项卡中的名称。

如何告诉查询提取除隐藏行之外的所有唯一数据？这是我现有的查询公式，其中包含隐藏行数据（这里仅包含 2 个数据选项卡以缩短此示例的公式）：

=UNIQUE(QUERY({IF(B2="", {"","","",""}, INDIRECT(B2));IF(B3="", {"","","",""}, INDIRECT(B3))},"Select Col4 where Col4 is not null order by Col4 label Col4 'Names'",1))

{"","","",""} 是因为我有用于未来预期选项卡的间接公式，这些选项卡尚不存在于选项卡列表中，因此每次添加新选项卡时我不必更新公式。为了排除隐藏数据，我研究了 SUBTOTAL 公式，但在将其应用于这种情况时遇到了困难。

这是表格的一个较小比例的模型示例我正在与. 选项卡 选项卡上名称列表中以“隐藏”开头的所有名称都位于从中提取查询的选项卡之一的隐藏行中。我想从查询输出中排除那些隐藏的名称/行。

提前致谢！

原文

I have an Apps Script pulling every tab name in my Google Sheet into a list. I'm then using a Query to pull all unique Names (from a Name column) from each of those tabs, with the Indirect function.

There are currently hidden rows in each of the tabs (there are 15 tabs total). The Query I have is currently pulling the Names in those hidden tabs.

How do I tell the Query to pull all unique data EXCEPT the hidden rows? Here is the existing Query formula I have which is including hidden row data (I'm only including 2 tabs of data here to shorten the formula for this example):

=UNIQUE(QUERY({IF(B2="", {"","","",""}, INDIRECT(B2));IF(B3="", {"","","",""}, INDIRECT(B3))},"Select Col4 where Col4 is not null order by Col4 label Col4 'Names'",1))

The {"","","",""} is because I have INDIRECT formulas for future anticipated tabs which don't yet exist in the list of tabs, so I won't have to update the formula every time a new tab is added. For excluding the hidden data, I've looked into the SUBTOTAL formula but I'm having trouble applying it to this situation.

Here's a mock-up, smaller scale example of the sheet I'm working with. All of the names in the Names list on the Tabs tab that start with "Hidden" are in hidden rows on one of the tabs the query is pulling from. I want to exclude those Hidden names/rows from the query output.

Thanks in advance!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

靑春怀旧 2025-01-18 14:47:14

SUBTOTAL 是一种方法，但它不适用于 arrayformula，因此您需要将其向下拖动到每一行中。看你的表。

=UNIQUE(QUERY({
 IF(B2="",  {"","","","",""}, INDIRECT(B2));
 IF(B3="",  {"","","","",""}, INDIRECT(B3));
 IF(B42="", {"","","","",""}, INDIRECT(B4))},
 "Select Col4 
  where Col4 is not null 
    and Col5 = 1 
  order by Col4 
  label Col4 'Name'", 1))

另请注意从 A:D 到 A:E 的范围变化

SUBTOTAL is the way but it does not work with arrayformula so you will need to drag it down into each row. see your sheet.

=UNIQUE(QUERY({
 IF(B2="",  {"","","","",""}, INDIRECT(B2));
 IF(B3="",  {"","","","",""}, INDIRECT(B3));
 IF(B42="", {"","","","",""}, INDIRECT(B4))},
 "Select Col4 
  where Col4 is not null 
    and Col5 = 1 
  order by Col4 
  label Col4 'Name'", 1))

also note the range change from A:D to A:E