时间戳

首页 > 解决方案 > 查找哪些用户正在使用相同的 JobID

google-sheets - 查找哪些用户正在使用相同的 JobID

问题描述

我只从 Google 电子表格中的每个用户表中提取 JobID 列。我想查询哪些用户正在使用同一个 JobID。我还需要将用户名列为查询的列，因为该列不存在于每个用户表中，尽管工作表名称是用户名。有没有办法使用谷歌查询来做到这一点？例如

JobID |  Count | Users using it                    |
----------------------------------------------------
1001  |   3    | alex, hubert, mark                |
1002  |   1    | mark                              |
1003  |   2    | sally, chaz, maha                 |
1004  |   1    | hubert                            |
1005  |   2    | maha, mark                        |

PS：此外，表格可以重命名为其他用户。所以需要一些动态的东西。

这是示例文件的链接。

标签： google-sheetsgoogle-query-language

解决方案

根据您的评论，我有一个公式可以返回 JobID 和用户。但要包括用户数量，我必须添加另一列和公式。见下文：

在此处输入图像描述

D1中的关键公式如下：

={"Job ID","Users (hidden)";
  ArrayFormula(trim(split(regexreplace(REGEXREPLACE(transpose(query(query({
     query({ Users!A$1 & "♦️" & row(indirect(Users!A$1 & "!A$2:A")), indirect(Users!A$1 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$2 & "♦️" & row(indirect(Users!A$2 & "!A$2:A")), indirect(Users!A$2 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$3 & "♦️" & row(indirect(Users!A$3 & "!A$2:A")), indirect(Users!A$3 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$4 & "♦️" & row(indirect(Users!A$4 & "!A$2:A")), indirect(Users!A$4 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$5 & "♦️" & row(indirect(Users!A$5 & "!A$2:A")), indirect(Users!A$5 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$6 & "♦️" & row(indirect(Users!A$6 & "!A$2:A")), indirect(Users!A$6 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0) },
     "select max(Col1) where Col2 <>'' group by Col1 pivot Col2",0)
     ,,99^99)),
     "♦️[0-9 ]+" , ", "),
     "(.*),", "$1"),
     "♥️",0)))}

这将返回一个 JobID 和用户数组，以逗号分隔。它还包括标题标签要添加用户计数，以您显示的格式，我隐藏了用户列，添加了我计​​算用户数量的列，然后添加了复制隐藏用户列的以下列。如果您愿意，还有很多其他方法可以做到这一点。

下一个关键问题是，如果公式包含一个没有选项卡的用户（Users 中的一行），并且其中包含 JobID 作为数据，则该公式将失败。有几种方法可以解决这个问题。
一种是额外的选项卡（二十个或您的最大用户数），给它们命名，如 TEMP1、TEMP2 等，并在每个选项卡中使用一个 JobID 作为填充符，其值为 999999 或类似值。然后主公式查询可以排除具有该 JobID 的记录。这意味着每个用户查询将始终返回至少一个数据行，对于 JobID 999999，并且不会失败。
当您获得一个新用户时，您只需将“用户”选项卡中的 TEMP1 替换为他们的真实姓名，并将 TEMP1 选项卡重命名为他们的姓名。

可能还有其他错误检查可以防止这种情况发生，但我发现如果一个查询没有返回任何行，堆叠多个查询会给我一个错误。例如。：

{ query(indirect(Users!A$1 & "!A$2:A").........);
  query(indirect(Users!A$2 & "!A$2:A").........);
  query(indirect(Users!A$3 & "!A$2:A").........);
  query(indirect(Users!A$4 & "!A$2:A").........)   }

User!A2如果其中一个为空白，或者没有与单元格中的名称匹配的选项卡User!A2，或者具有该名称的选项卡没有数据，则给我一个错误。

如果您采取这些措施，则可以预设主要公式以处理二十个（或更多）用户。我上面显示的公式是针对当前六个用户的，但我已经测试了更多。我将在底部提供二十个用户的公式。

让我知道这是否有帮助，或者如果您有任何疑问。很高兴回答他们。

二十个用户的公式。每个“用户”都有一个名称，或者一个虚拟名称占位符，在用户中，必须有一个具有该名称的选项卡，并且每个选项卡中必须至少有一个 JobID 值，或者一个虚拟 JobID，例如 999999。

={"Job ID","Users (hidden)";
  ArrayFormula(trim(split(regexreplace(REGEXREPLACE(transpose(query(query({
     query({ Users!A$1 & "♦️" & row(indirect(Users!A$1 & "!A$2:A")), indirect(Users!A$1 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$2 & "♦️" & row(indirect(Users!A$2 & "!A$2:A")), indirect(Users!A$2 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$3 & "♦️" & row(indirect(Users!A$3 & "!A$2:A")), indirect(Users!A$3 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$4 & "♦️" & row(indirect(Users!A$4 & "!A$2:A")), indirect(Users!A$4 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$5 & "♦️" & row(indirect(Users!A$5 & "!A$2:A")), indirect(Users!A$5 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$6 & "♦️" & row(indirect(Users!A$6 & "!A$2:A")), indirect(Users!A$6 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$7 & "♦️" & row(indirect(Users!A$7 & "!A$2:A")), indirect(Users!A$7 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$8 & "♦️" & row(indirect(Users!A$8 & "!A$2:A")), indirect(Users!A$8 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$9 & "♦️" & row(indirect(Users!A$9 & "!A$2:A")), indirect(Users!A$9 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$10 & "♦️" & row(indirect(Users!A$10 & "!A$2:A")), indirect(Users!A$10 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$11 & "♦️" & row(indirect(Users!A$11 & "!A$2:A")), indirect(Users!A$11 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$12 & "♦️" & row(indirect(Users!A$12 & "!A$2:A")), indirect(Users!A$12 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$13 & "♦️" & row(indirect(Users!A$13 & "!A$2:A")), indirect(Users!A$13 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$14 & "♦️" & row(indirect(Users!A$14 & "!A$2:A")), indirect(Users!A$14 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$15 & "♦️" & row(indirect(Users!A$15 & "!A$2:A")), indirect(Users!A$15 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$16 & "♦️" & row(indirect(Users!A$16 & "!A$2:A")), indirect(Users!A$16 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$17 & "♦️" & row(indirect(Users!A$17 & "!A$2:A")), indirect(Users!A$17 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$18 & "♦️" & row(indirect(Users!A$18 & "!A$2:A")), indirect(Users!A$18 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$19 & "♦️" & row(indirect(Users!A$19 & "!A$2:A")), indirect(Users!A$19 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$20 & "♦️" & row(indirect(Users!A$20 & "!A$2:A")), indirect(Users!A$20 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0) },
     "select max(Col1) where Col2 <>'' group by Col1 pivot Col2",0)
     ,,99^99)),
     "♦️[0-9 ]+" , ", "),
     "(.*),", "$1"),
     "♥️",0)))}

推荐阅读