根据列表从另一张图纸上的单元格中提取值

Question

我在一张表上有一个数据列表。第二张表用于显示谁做了什么，就像仪表板一样。我得到了一个数组公式来生成数据，该数据可以查找员工的姓名，并在他们出现在已完成课程的列表中时显示。如果是；是"；在仪表板上相应的课程编号下列出。我不知道我需要用什么来更改REGEXREPLACE，以显示数据列表中的日期列；是的"；。我知道REGEXREPLACE只适用于文本值，而日期则不然——即使将日期列更改为文本似乎也无关紧要。

下面是当前数组公式的一个工作示例：

https://docs.google.com/spreadsheets/d/1jkG515zyl4UxRHxhtFTjgWsjG0aBE4vsOxbpvqgjogU/edit#gid=536376041

以下是使用的公式：

=ARRAYFORMULA(IF(A5:A="",,REGEXREPLACE(IFNA(VLOOKUP(A5:A, QUERY({TRIM('Form Responses 1'!B2:G)}, "select Col1,count(Col1) group by Col1 pivot Col6"), MATCH(F2:P2, QUERY(QUERY({TRIM('Form Responses 1'!B2:G)}, "select Col1,count(Col1) group by Col1 pivot Col6"), "limit 0", 1), 0), 0))&"", "d+", "yes")))

在上面的例子中，我需要表单响应表中D列的"完成日期"。

Answer 1

这里是可能对您有所帮助的第一次尝试。

这是一个简单的查询，它提取日期，并将员工姓名放在左侧，课程名称放在顶部。

但它并不试图将数据与现有的员工列表进行匹配，它只列出了所有提交了表格的员工。因此，如果你想查看所有员工，而没有提交表格的员工则为空白行，这对你来说是行不通的。

A2中的公式为：

=ARRAYFORMULA(QUERY({'Form Responses 1'!B2:G}, "select Col1,max(Col3) where Col1<>'' group by Col1 pivot Col6 order by Col1"))

请参阅选项卡Sheet1 GK，该选项卡已添加到您的样本表中。

如果这有帮助，或者你需要一些不同的东西，请告诉我。

更新：要将结果限制在特定的课程列表中，请使用以下修改：

=ARRAYFORMULA(QUERY({'Form Responses 1'!B2:G}, 
"select Col1,max(Col3) where Col1<>'' 
and Col6 matches '" & TEXTJOIN("|",1,F2:2) & "' 
group by Col1 pivot Col6 order by Col1 "))

在这里，需要报告的课程列表在F2:2中，这是你最初拥有它们的地方，但这个列表可以放在任何地方，甚至可以放在另一个选项卡上。如果你命名你放置它的范围，这可以稍微简化这个公式。现在，您可以隐藏第2行。我已将其分组在左侧以隐藏它。使用[+]可再次显示它。