嵌套在数组中的动态查询公式

Question

我在谷歌工作表中有一个查询公式，它根据我的谷歌工作表中的其他数据列进行更新，此处是=QUERY('Deals List - URL Split'!A:DZ, "select A, C where C contains 'http'",)

所以它可能会添加QUERY('Deals List - URL Split'!A:DZ, "select A, E where E contains 'http'",)，然后它最终会变成下面的代码，依此类推。

=QUERY('Deals List - URL Split'!A:DZ, "select A, C where C contains 'http'",);QUERY('Deals List - URL Split'!A:DZ, "select A, E where E contains 'http'",) 

我想要做的就是让结果查询公式在单元格‘List’中！在数组公式中使用A1作为QUERY('Deals List - URL Split'!A:DZ, "select A, C where C contains 'http'",);QUERY('Deals List - URL Split'!A:DZ, "select A, E where E contains 'http'",)作为引用，这样我就不必在每次添加新的查询公式时都更新公式。

静态查询公式为

=SORT(ARRAYFORMULA({QUERY('Deals List - URL Split'!A:DZ, "select A, C where C contains 'http'",);QUERY('Deals List - URL Split'!A:DZ, "select A, E where E contains 'http'",)}),1,TRUE,2,TRUE)

实际上，得到动态公式的公式如下所示，但我总是得到一个错误，只得到上面的文字静态公式。

=SORT(ARRAYFORMULA({'List'!A1}),1,TRUE,2,TRUE)

Answer 1

我想我有一个(或两个)答案给你。看过你的工作表后，我不得不说，我确信你的工作表可以有一个更简单的设计，这将简化一切。无论如何，我构建了一个公式，仅使用工作表'2 URL SPLIT'！中的数据和'4 URL FILTER'!A1:1中的所需列。请参阅我的示例选项卡，GK-6项和URL，添加到工作表中。

简化为基本形式的公式为：

={
IFERROR({'2 URL SPLIT'!$A$2:$A, INDIRECT(INDEX(
  {ARRAYFORMULA(IFERROR("'2 URL SPLIT'!" 
   & TRANSPOSE('4 URL FILTER'!1:1) 
   & TRANSPOSE(SPLIT(
      {"2:" 
       & TEXTJOIN("~2:",1,TRANSPOSE('4 URL FILTER'!1:1))},"~",0,0)) 
       & ROWS('2 URL SPLIT'!A:A)))},1,0))},{"",""})
}

该公式并不是真正动态的，但它忽略了空白列。所以我使用的骗局是扩展公式的容量，以包括额外的空列，如果它们填充了数据，就会使用这些数据。我将它设置为包含50列数据，您当前使用的是39列，但是您可以将其扩展到处理大约200列，然后才能达到一个单元格的50,000个字符的限制。

如上所示的公式处理一列。对于处理50列的那个，就像我的样例工作表一样，我简单地复制了内部公式，即外括号"{....}“中的所有内容，并递增其中的数字。你只需要这样做一次，或者从我的工作表中复制我的。如果/当数据列展开时，不需要进行更新。

如果你认为这个公式对你有效，我很乐意补充更多的解释。但该公式的基础是动态构建要查询的单元格范围。公式内部的结果如下所示。请注意，每个范围中的2是硬编码的，如果您的结构发生变化，则可以更改，但范围的限制是根据您的数据计算的。

​

​

公式的其余部分使用索引到这个“表”中，按1递增以选择每个连续的数据范围，这将添加要查询的新数据列。这些数据的范围从'2 URL SPLIT!‘包括列A和一个后续数据列，如'4 URL FILTER'!A1:A中所指定，并通过使用";“分隔符堆叠在另一个列之上。

然后对这个垂直的两列堆栈运行查询，选择列2包含"http“的所有行。

最终结果如下所示：

​

​