google-sheets - 谷歌工作表，选择命名范围内的列

Question

我试图在谷歌表格上的命名范围内选择一系列数据，但搜索后我仍然不知道该怎么做。

在这种情况下，我想计算一个俱乐部在一个比赛周内出现的时间。

在这里，我可以为我拥有的所有游戏周表做到这一点，没有问题。

全部的

=QUERY({B:C;D:E;F:G;H:I;J:K}; "SELECT Col1, SUM(Col2) WHERE Col1 <> '' AND Col1 <> 'GK' AND Col1 <> 'DEF' AND Col1 <> 'MID' AND Col1 <> 'FW' AND Col1 <> 'EXTRA' AND Col1 <> 'Club' AND Col1 <> 'count' AND Col1 MATCHES '^.{0,5}$' GROUP BY Col1 ORDER BY SUM(Col2) DESC LABEL Col1 'Club', SUM(Col2) 'count'")

但我想对每个游戏周表做同样的事情。到目前为止我有这个

游戏周 1

=QUERY({(B6:B15):(C6:C15);(D6:D15):(E6:E15);(F6:F15):(G6:G15);(H6:H15):(I6:I15);(J6:J15):(K6:K15)}; "SELECT Col1, SUM(Col2) WHERE Col1 <> '' GROUP BY Col1 ORDER BY SUM(Col2) DESC LABEL Col1 'Club', SUM(Col2) 'count'")

但它不是很有用，因为我必须为每个下一个表更改每个范围。

因此，我尝试将“Gameweek 1”表设置为命名范围gw1_clubcount，并且只选择与总数类似的足够列，这样我只需要为每个游戏周更改它，但显然它不起作用，但这就是我想要的抵达。

具有命名范围的游戏周 1

=QUERY(gw1_clubcount {B:C;D:E;F:G;H:I;J:K}; "SELECT Col1, SUM(Col2) WHERE Col1 <> '' GROUP BY Col1 ORDER BY SUM(Col2) DESC LABEL Col1 'Club', SUM(Col2) 'count'")

我找到了 INDEX 函数，您可以在其中选择命名范围的所需行和列。这将在下面给出类似的内容，但这不是您使用它的方式。

=QUERY({INDEX(gw1_clubcount,,1):INDEX(gw1_clubcount,,2);INDEX(gw1_clubcount,,3):INDEX(gw1_clubcount,,4);INDEX(gw1_clubcount,,5):INDEX(gw1_clubcount,,6);INDEX(gw1_clubcount,,7):INDEX(gw1_clubcount,,8);INDEX(gw1_clubcount,,9):INDEX(gw1_clubcount,,10)} ; "SELECT Col1, SUM(Col2) WHERE Col1 <> '' GROUP BY Col1 ORDER BY SUM(Col2) DESC LABEL Col1 'Club', SUM(Col2) 'count'")

我对床单不是很有经验，非常感谢您的帮助！

标签： google-sheetsgoogle-sheets-formula

Answer 1

在这里，我创建了一个需要自定义功能的解决方案：

编写自定义函数：

1. 在 Google 表格中创建或打开电子表格。
2. 选择菜单项工具 > 脚本编辑器。
3. 删除脚本编辑器中的任何代码。
4. 对于这个问题，只需将下面的代码（堆栈函数）复制并粘贴到您的脚本编辑器中。
5. 在顶部，单击保存保存。

使用自定义函数：

1. 单击要使用该功能的单元格。
2. 键入等号 (=)，后跟函数名称和任何输入值（例如 =DOUBLE(A1)），然后按 Enter。
3. 单元格将暂时显示 Loading...，然后返回结果。

代码：

function stack(arr) {
  var flatArr = arr.flat();
  var filtered = flatArr.filter(function (el) {return el != "";});
  const newArr = [];
  while(filtered.length) newArr.push(filtered.splice(0,2));
  return(newArr);
}

命名范围：

示例用法：

游戏周1：

=QUERY(stack(FLATTEN(gw1_clubcount)), "SELECT Col1, SUM(Col2) WHERE Col1 <> '' GROUP BY Col1 ORDER BY SUM(Col2) DESC LABEL Col1 'Club', SUM(Col2) 'count'")

游戏周2：

=QUERY(stack(FLATTEN(gw2_clubcount)), "SELECT Col1, SUM(Col2) WHERE Col1 <> '' GROUP BY Col1 ORDER BY SUM(Col2) DESC LABEL Col1 'Club', SUM(Col2) 'count'")

参考：

• 自定义函数
• 展平