☰

使用 XLOOKUP 或 FILTER 将数据聚合到单个单元格中

tag-icon • tag-icon microsoft-excel worksheet-function google-spreadsheets
使用 XLOOKUP 或 FILTER 将数据聚合到单个单元格中

我有一个数据集，它由不同的人填写表单来填充 Google Sheet 上的电子表格。数据集可能如下所示： 列出姓名、收集的评论、日期和呼叫者的数据集。 图片1

有一群呼叫者，他们给个人打电话收集信息，比如电影评论，并在表格中记录他们呼叫的人的姓名、他们的评论是什么、日期和呼叫者的姓名。我想汇总数据，以便按姓名将其组织成一个单独的表格，在一个单元格中列出该人给出的所有评论，并注明收集评论的呼叫者的姓名和收集日期。这就是我想要的结果表的样子。 图片2

我尝试了两种不同的选项。首先是 XLOOKUP，该函数如下所示：

=CONCATENATE(XLOOKUP(I2, $A$2:$A, $B$2:$B), " (",XLOOKUP(I2, $A$2:$A, $D$2:$D), ", ", TEXT(XLOOKUP(I2, $A$2:$A, $C$2:C),"mm/dd/yyyy"), ")")

这给了我正确的格式，但如果同一个人给出了多条评论，XLOOKUP 只会给我第一个结果。我想收集他们所有的评论。使用 XLOOKUP 生成的表，其中不包含第二组评论的单元格以红色突出显示。 图片3

然后我尝试使用 FILTER：

=TEXTJOIN(CHAR(10),TRUE,FILTER($B$2:B, $A$2:A=L2),FILTER($D$2:D,A$2:$A=L2), FILTER($C$2:C,A$2:$A=L2))

这给了我所有数据，但格式不正确。现在，如果有多条评论，则按顺序列出，然后列出呼叫者的姓名，然后列出日期

图片4

我如何返回所有匹配项，但将返回数据的顺序/格式自定义到 1 个单元格中？

答案1

欢迎！

你是对的——TEXTJOIN(CHAR(10)...这会起到作用。

你也是对的——FILTER()可以选择你想要的数据。

您只错过了一点——作为第一个参数（用于过滤的数组），您需要传递一组已经格式化的字符串：

=IFERROR(TEXTJOIN(CHAR(10),1,
    ARRAYFORMULA(FILTER(
       {B1:B&" (" & D1:D & ", " & TEXT(C1:C,"YYYY-MM-DD") & ")"},
       A1:A=L1))),"")`

答案2

对于使用 Excel 365 的用户：

在第一列中输入公式：

=SORT(UNIQUE($A$2:$A$9))

在第二列，输入下面的公式并Wrap Text为单元格启用。

=TEXTJOIN(CHAR(10),TRUE,TOROW(FILTER(HSTACK($B$2:$B$9,$D$2:$D$9,TEXT($C$2:$C$9,"dd/MM/yyyy")),A13=$A$2:$A$9),0,TRUE))

将公式向下拖动以填充下方的单元格。

相关内容