提问人：loveli 提问时间：8/26/2023 最后编辑：loveli 更新时间：8/26/2023 访问量：64
Google 表格查询 - 按类别将文本合并到单个单元格中
Google Sheet Query - combine text into single cell by category
问：
我遇到了这个公式，但我不知道如何改变它。 按类别在单个单元格中显示查询值。有 5 个类别，第 1 个单元格显示 1 个类别，第 2 个单元格显示第 2 个类别，依此类推。
如果没有第一类，则从第二类开始，依此类推。
我在这里附上了链接进行编辑。.谢谢
更改查询公式以根据需要显示，但失败，请帮忙
答：
0赞
Chris Strickland
8/26/2023
#1
看看 outcomeCS。公式如下：
=let(
categories, {
"Incomplete with Reason";
"Failed with Reason";
"Incomplete";
"Failed";
"Offline"
},
results, map(
categories,
lambda(
x, {
x,
iferror(
textjoin(
char(10) & char(10),
true,
map(
filter(Data!$B:$B, Data!$C:$C=x, Data!$A:$A=$A3),
filter(Data!$D:$D, Data!$C:$C=x, Data!$A:$A=$A3),
lambda(y, z, y & char(10) & z)
)
),
"err"
)
}
)
),
filteredResults, filter(results, index(results,0,2)<>"err"),
buffer, reduce(, filteredResults, lambda(a, x, {a,x})),
filter(buffer, buffer<>"")
)
我会提供一些解释。
从本质上讲，我们需要获取类别列表，然后获取与这些类别匹配的值。我们想按类别和日期从 B 列和 D 列中获取值，所以我们想使用类似 FILTER 的东西，假设我们有一个类别和一个日期，那么我们这样做是这样的：
filter(Data!$B:$B, Data!$C:$C=category, Data!$A:$A=date)
和
filter(Data!$D:$D, Data!$C:$C=category, Data!$A:$A=date)
若要获取所需的输出，请执行以下操作：
03:00
text 1
我们想取每一对值并像这样组合主题：ColB 换行符 ColD。为此，让我们使用 MAP。MAP 采用一个或多个数组，然后将 lambda 公式应用于每个值或多个值。MAP 语法为：MAP（input， lambda（value， formula）），它将输入数组分解为每个值，然后将该值传递给 lambda，其中公式应用于每个值。可以使用多个输入。在本例中，我将把两个过滤好的列作为前两个参数传入映射，然后在 lambda 中，我将使用 y 和 z 作为值占位符，最后只需用换行符连接 y 和 z （char（10））：
map(
filter(Data!$B:$B, Data!$C:$C=category, Data!$A:$A=date),
filter(Data!$D:$D, Data!$C:$C=category, Data!$A:$A=date),
lambda(y, z, y & char(10) & z)
)
lambda 是实际返回的内容，它将是一个与输入数组大小相同的数组。在本例中，我们只是将 B 和 D 的过滤值连接起来。由于您想像这样组合所有这些单独的值：
03:00
text 1
13:00
text 3
我们可以将该数组传递给 TEXTJOIN，后者接受一个值数组，然后使用提供的分隔符将它们连接在一起。
TEXTJOIN(
char(10),
TRUE,
map(
filter(Data!$B:$B, Data!$C:$C=category, Data!$A:$A=date),
filter(Data!$D:$D, Data!$C:$C=category, Data!$A:$A=date),
lambda(y, z, y & char(10) & z)
)
)
如果过滤后的集合中没有匹配项，FILTER 将抛出错误，因此让我们将整个内容包装在 IFERROR 中：
IFERROR(
TEXTJOIN(
char(10),
TRUE,
map(
filter(Data!$B:$B, Data!$C:$C=category, Data!$A:$A=date),
filter(Data!$D:$D, Data!$C:$C=category, Data!$A:$A=date),
lambda(y, z, y & char(10) & z)
)
),
"err"
)
这将为您提供特定类别或日期的值数组。唯一的诀窍是获取要传递到过滤器中的类别和日期。现在，如果您不需要对它们进行排序，或者您只需要按字母顺序排列它们，那么我们可以在 C 列上使用 UNIQUE，但由于您需要特定的顺序，我们将使用文字表示法手动创建数组。{} 是 googleSheets 中数组的文字表示法，分隔符（either 或 ;)确定数组是水平的还是垂直的。
categories, {
"Incomplete with Reason";
"Failed with Reason";
"Incomplete";
"Failed";
"Offline"
}
为了节省一些打字时间，我们将使用 LET。LET 允许您定义一些可重用的值，它采用以下格式的名称/值对：
LET（name1， value1， name2， value2， ...）expr）
其中 expr 可以是任何有效的公式，通常可以放在 = 之后的任何公式。我们要做的第一件事是获取该类别数组并为其命名。
=let(
categories, {
"Incomplete with Reason";
"Failed with Reason";
"Incomplete";
"Failed";
"Offline"
},
其次，我们将使用这些类别来映射一个新的输出数组，我们将执行我们之前在类别上讨论的相同操作，我们将这个新数组分配给名称“results”（我们只是在这里继续 LET 语句）：
results, map(
categories,
lambda(
x, {
x,
iferror(
textjoin(
char(10) & char(10),
true,
map(
filter(Data!$B:$B, Data!$C:$C=x, Data!$A:$A=$A3),
filter(Data!$D:$D, Data!$C:$C=x, Data!$A:$A=$A3),
lambda(y, z, y & char(10) & z)
)
),
"err"
)
}
)
),
这看起来有点复杂，但让我们给出我们之前讨论的操作，整个 IFERROR/TEXTJOIN/FILTER 块，一个名称：joinedRemarks，并将其用作占位符以使函数更具可读性：
results, map(
categories,
lambda(
x, {
x,
joinedRemarks
}
)
),
然后让我们去掉一些空格：
results, map(
categories,
lambda(x, {x, joinedRemarks})
),
因此，我们所做的只是给出一个名称（结果）并将该 MAP 公式的输出分配给它。在此映射中，我传入了我们之前创建的数组类别，并定义了一个 lambda：lambda（x， {x， joinedRemarks}）。MAP 采用类别数组，然后将每个值从中传递到 lambda，其中 x 是传递值的占位符，要应用的公式是 {x， joinedRemarks}，其中 joinedRemarks 是之前的 TEXTJOIN/FILTER 操作。请记住，{} 是文字表示法数组，因此我们将 x（从输入数组传入的值）与该值的 joinedRemarks 组合成一个新数组。
我们必须做的一件事是在 FILTER 公式中定义实际的类别和日期。请记住，我是这样定义的：
filter(Data!$B:$B, Data!$C:$C=category, Data!$A:$A=date),
但我们从未真正提供过真正的类别和日期，我只是说“如果你有类别和日期，你可以用它们进行过滤”。现在在这个 MAP 公式中，输入数组是 categories，lambda 的占位符是 x，所以我们想用 x 替换 category，因为日期在 Col A 中，我们也可以替换它。因此，我们使用的不是原始解释中的“category”和“date”，而是 x（lambda 中的占位符值）和 A 列中的值：
filter(Data!$B:$B, Data!$C:$C=x, Data!$A:$A=$A3),
所以到目前为止，整个事情是：
=let(
categories, {
"Incomplete with Reason";
"Failed with Reason";
"Incomplete";
"Failed";
"Offline"
},
results, map(
categories,
lambda(
x, {
x,
iferror(
textjoin(
char(10) & char(10),
true,
map(
filter(Data!$B:$B, Data!$C:$C=x, Data!$A:$A=$A3),
filter(Data!$D:$D, Data!$C:$C=x, Data!$A:$A=$A3),
lambda(y, z, y & char(10) & z)
)
),
"err"
)
}
)
),
我们想筛选出数组中产生错误的任何元素（请记住，如果没有匹配项，FILTER 将返回错误），因此我们将对刚刚创建的结果数组使用 filter 来删除这些元素并将其分配给名称 filteredResults：
filteredResults, filter(results, index(results,0,2)<>"err"),
然后，由于您需要这些水平，我们将将它们减少到一个新的数组 buffer 中：
buffer, reduce(, filteredResults, lambda(a, x, {a,x})),
我们这样做是因为 filteredResults 将包含如下内容：
categoryA, resultsA
categoryB, resultsB
categoryC, resultsC
categoryD, resultsD
在一个二维数组中，所以我们不能只是转置它，或者我们最终会得到一个二维数组，只是侧向转动，你想要一行。因此，我们要做的是将filteredResults传递给REDUCE，这几乎类似于MAP，只是它不是接受一个数组并生成一个新数组，而是接受一个数组并将其简化为单个值：REDUCE（initial， input， lambda（accumulator， value， formula））。累加器是我们要添加新值的变量，initial 是累加器的起始值。
因此，在这里REDUCE将获取数组的每个元素（本身是一个数组：categoryA，resultsA），然后使用文字表示法{a，x}将它们组合成一个水平数组。因此，首先我们将得到这个：categoryA，resultsA。然后在下一次迭代中，我们将有：categoryA、resultsA、categoryB、resultsB 等。
最后，由于我们从初始空值开始，然后不断扩展数组，因此我们只想从数组中删除该空值：
filter(buffer, buffer<>"")
)
这是整个事情：
=let(
categories, {
"Incomplete with Reason";
"Failed with Reason";
"Incomplete";
"Failed";
"Offline"
},
results, map(
categories,
lambda(
x, {
x,
iferror(
textjoin(
char(10) & char(10),
true,
map(
filter(Data!$B:$B, Data!$C:$C=x, Data!$A:$A=$A3),
filter(Data!$D:$D, Data!$C:$C=x, Data!$A:$A=$A3),
lambda(y, z, y & char(10) & z)
)
),
"err"
)
}
)
),
filteredResults, filter(results, index(results,0,2)<>"err"),
buffer, reduce(, filteredResults, lambda(a, x, {a,x})),
filter(buffer, buffer<>"")
)
我知道这是漫长而复杂的。一旦你掌握了 LET、MAP 和 REDUCE 的窍门，它就比看起来要简单得多。希望这一切都是有道理的。
评论
0赞
loveli
8/26/2023
嗨，谢谢..如何使它水平？它应按日期跟随 A 列，从当月的 1 月 30 日至 31 日。
0赞
loveli
8/26/2023
所以答案是这样的..水平和按天列 A 列 D 列 E 列 F 列 G 第 1 天 ..第 1 类（为第 1 类合并文本） 第 2 类（为第 1 类合并文本） 第 2 天 第 3 天 第 4 天 ...第31天
0赞
Chris Strickland
8/26/2023
看一看 outcomeCS
0赞
loveli
8/26/2023
它有效！ ..两个公式都有效。也许可以给我一些解释，因为使用的公式对我来说是很新的，而且它有效。谢谢
0赞
Chris Strickland
8/26/2023
我编辑了答案并添加了一些解释，但有点复杂。看看它，希望它有意义。
0赞
rockinfreakshow
8/26/2023
#2
=reduce(tocol(,1),unique(tocol(A2:A,1)),lambda(a,c,ifna(vstack(a,hstack(c, let(Λ,torow(,1), Γ,char(10),
reduce(Λ,tocol(F2:F,1),lambda(f,q,let(Σ,filter(B:B&Γ&D:D,C:C=q,A:A=c),hstack(f,if(iserror(Σ),Λ,q),if(iserror(Σ),Λ,textjoin(Γ&Γ,1,Σ))))))))))))
评论