将数组公式的文本结果转换为可用格式

Question

将数组公式的文本结果转换为可用格式

11

当数组公式的结果是数字时，我通常很容易找到将数组折叠成单个结果的适当方法。但是，当数组公式的结果是文本时，我发现很难以一种提供单个所需结果的方式来操作公式。简而言之，是否有一种我忽视的处理文本结果数组的方法？请参见本问题底部的最终所需公式，并请求解决方案。

*编辑 - 再次阅读后，我可以用另一种方式总结我的问题：是否有一种方法可以从“Formula Array result”中访问多个文本元素，而无需单独选择（例如：使用INDEX）？

以下是数组公式适用的示例，其中结果数组是数字值：

(1) 示例1: 假设A列行1-500是以xyz123格式列出的产品ID列表，B列行1-500显示该产品的总销售额。如果我想要查找销售额最高的产品的销售额，其中ID的最后3位数大于400，则可以使用如下的数组公式（使用CTRL + SHIFT + ENTER进行确认，而不是只使用ENTER）：

=MAX(IF(VALUE(RIGHT(A1:A500,3))>400,B1:B500,""))

（2）示例2现在假设B列包含产品名称，而不是销售额。我现在想要返回符合产品ID的最后3位数大于400的第一个名称。可以按以下方式完成：

=INDEX(B1:B500,MIN(IF(VALUE(RIGHT(A1:A500,3))>400,ROW(A1:A500),"")))

我进行了一些操作，使得公式的实际数组部分 [IF(RIGHT(A1:A500,3...] 返回一个值结果 [单元格A1:A500中最后3位数字大于400的行号]；因此我可以使用MIN只显示第一个匹配的行号，然后在常规INDEX函数中使用该结果。

(3) 示例3 最后一个示例，请参见这里的类似问题讨论 [比我的下面摘要的例子更深入地讨论了这个问题，但与此问题不直接相关]: https://stackoverflow.com/a/31325935/5090027

现在假设您想要所有产品名称的列表，其中产品ID的最后3位 > 400。据我所知，这不能在单个单元格中完成，必须通过将每个单独的结果放置在后续单元格上来完成。例如，下面的公式可以放置在C1中并向下拖动10行，然后将显示具有最后3位数字 > 400的产品ID的前10个产品名称。

=INDEX($B$1:$B$500,SMALL(IF(VALUE(RIGHT($A$1:$A$500,3))>400,ROW($A$1:$A$500),""),ROW()))

数组公式无法使用的示例，其中结果数组是文本值

现在假设我想对示例3中的结果进行一些文本操作。例如，假设我想将它们全部连接成单个文本字符串。下面的方法不起作用，因为concatenate不会将这样的结果数组作为可接受的参数。

=CONCATENATE((IF(VALUE(RIGHT($A$1:$A$500,3))>400,ROW($B$1:$B$500),"")))

所以问题是：有没有人知道如何使这个最后的公式起作用？或者，如何让一个接受文本结果数组的公式工作，并将其转换为“可用范围”[以便可以插入上面的连接]，或者可以立即使用文本参数进行操作[如mid、search、substitute等]? 目前我能看到的唯一方法是使用上面的示例3，然后进一步说，例如，Concatenate(C1,C2,C3...C10)。

- Grade 'Eh' Bacon

3

如果要连接的位数事先不知道，就没有原生的数组公式解决方案可用。简单的用户自定义函数可以替换受限的原生CONCATENATE()函数，并产生您需要的结果，但明显的缺点是需要使用宏。 - Excel Hero

2

几年前，我很惊讶 CONCATENATE 在数组上不起作用。我认为没有任何基于公式的解决方法（尝试过使用 N 等等，但都不起作用）。 - brettdj

你是否同意，例如要列出示例3的结果，则必须使用“工作”列，如果是这样，则可以使用一个公式获得示例3结果的连接，无论其数量如何，这样也是可接受的。这就是您正在寻找的吗？ - EEM

所以，回答你的问题是数组可以生成（或者像你所说的可用范围），但要选择完整的连接，它必须在物理上存在。 - EEM

当我提到“可用范围”时，我的意思是将文本值数组转换为易于操作的格式，例如CONCATENATE / SUBSTITUTE / SEARCH等。我可以看到如何在将数组转换为单独拉出的值之后执行此操作-请查看我的帖子的最后一句话；那是我目前的做法。现在，我正在寻找除了逐个拉取它们（例如使用INDEX）之外访问文本值数组元素的替代方法的想法。 - Grade 'Eh' Bacon

显示剩余3条评论

5个回答

2

重申其他回答，我没有找到一种在数组上使用连接函数的方法。但是，我找到了一种只使用一个数组函数和没有所谓的“辅助列”来连接“产品名称”的方法。虽然它相当冗长而繁琐，但我认为这可能会增加讨论的内容。首先，如果您实际上要使用这样的公式来解决某个有效目的或克服特定障碍，可以通过复制和粘贴公式轻松使用（也就是说，它实际上是相对适应的）。另一方面，如果您的兴趣更多是好奇心，我的答案可能比您想要的更平凡。

在模拟您的问题时，我也有两列，但将行数缩短到了40。最左边的列（“C”）包含三个字母和三个数字的序列，而右列（“D”）包含模拟您的“产品名称”的字母和数字的随机序列。

我使用了嵌套的替换和连接函数的组合。以下函数被切成了聚集函数的“基本单元”。

REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),1)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),1))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),1)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),2)))=TRUE,””,

上述公式基本上是查找第一个产品名称和相应的产品ID，其数字序列> 400，然后将其替换为连接（给定存在满足相同产品ID条件的另一个产品）。可以将其视为“累积”连接，从最内部的括号开始。这个公式的“基本单位”可以重复任意次。也就是说，如果您认为列表中有200到280个符合您设置的产品ID标准的产品，您可以将这个基本代码重复280次。如果公式尝试连接不存在的产品名称（您有280个公式基本单位，但只有275个产品符合条件），则该公式会自我终止......在某种程度上。它实际上开始一遍又一遍地连接空白，直到所有基本单位都被执行。结果将是所有所需产品名称连接在一个单元格中，并用句点分隔每个名称。

从基本块到基本块只有一个数字变化，那就是SMALL数组的第k个元素。这些变量显然会在每个基本单位中递增一次。对于我的测试，我使用了14个基本单位。

带有14个基本单位的完整公式

=REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),1)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),1))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),1)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),2)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),2)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),2))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),2)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),3)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),3)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),3))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),3)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),4)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),4)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),4))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),4)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),5)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),5)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),5))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),5)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),6)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),6)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),6))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),6)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),7)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),7)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),7))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),7)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),8)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),8)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),8))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),8)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),9)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),9)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),9))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),9)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),10)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),10)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),10))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),10)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),11)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),11)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),11))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),11)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),12)))=TRUE,””,**REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),12)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),12))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),12)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),13)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),13)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),13))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),13)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),14)))=TRUE,””,INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),14)))))))))))))))))))))))))))))))))))))))))

显然，如果你看整个公式，它非常难以理解。但是，从基本单位的角度来看，你可以看到它如何轻松构建，然后复制和粘贴（在编写初始基本单位后，花费约2分钟将其全部组合在一起）。

- bidout

好的，这有点有趣 - 我喜欢你总体上避免错误的方法，如果结果数量少于可能的最大数量。然而，正如你所说，这是一件有点乏味的事情，并且仍然使用INDEX来提取结果，这意味着涉及到很多重复。这是值得思考的事情，当然。 - Grade 'Eh' Bacon

请注意，经反思我认为使用类似于你的方法的最简单方式是以下公式——其中一个好处是使用&而不是Concatenate可以消除需要拥有数十个结束括号的必要，因为每个项都是自己的术语。只需添加一个新的“基本”公式部分，并每次将k值迭代1即可。 =INDEX($B$1:$B$500,SMALL(IFERROR(IF(VALUE(RIGHT($A$1:$A$500,3))>400,ROW($A$1:$A$500),""),""),1))&INDEX($B$1:$B$500,SMALL(IFERROR(IF(VALUE(RIGHT($A$1:$A$500,3))>400,ROW($A$1:$A$500),""),""),2)) - Grade 'Eh' Bacon

我理解你的观点。对我的方法来说确实是简化了。我认为这是一个很重要、很成熟的问题。值得深思......“为什么 Excel 处理公式数组结果似乎存在如此的限制？”显然，正如其他人所指出的那样，使用 VBA 解决方案非常容易。因此，我认为微软应该加入。比如修复 CONCATENATE()。 - bidout

2

这是一种不需要使用VBA的解决方案，可以在Excel 2016中使用Get&Transform或在之前版本中使用Power Query Add-In：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ExtractLast3Digits = Table.AddColumn(Source, "Value", each Text.End([ProductID],3)),
    ChangeToNumber = Table.TransformColumnTypes(ExtractLast3Digits,{{"Value", type number}}),
    FilterAbove400 = Table.SelectRows(ChangeToNumber, each [Value] > 400),
    Concatenate = Text.Combine(FilterAbove400[ProductName])
in
    Concatenate

在“FilterAbove400”步骤中，您可以对“array-output”执行各种文本操作。在此示例中，我只是按您的请求连接了字符串而没有使用分隔符。

它需要以表格形式命名为“Table1”的输入数据作为第一步（源）。

解决方案文件链接：https://www.dropbox.com/s/utsraj0bec5ewqk/SE_ConvertArrayFormulasTextResult.xlsx?dl=0

- ImkeF

有趣的是，我喜欢Excel 2016中的一些新特性。不幸的是，对于我的工作场所来说，无论是Excel 2016还是Power Query Add-In都可能不适用于访问工作簿的所有用户。 - Grade 'Eh' Bacon

1

你可以创建自己的聚合函数来处理公式数组的结果。这需要一些VBA，但并不难。这将允许您对值数组进行各种字符串操作或数字分析。

要执行连接函数，请打开VBA代码窗口，并通过右键单击项目->插入->新模块来创建一个新模块。双击新模块并插入以下代码以创建将数组串联成一个大字符串的函数：

Function ConcatenateArray(ParamArray Nums() As Variant) As Variant
Dim BigString As String
Dim N As Long
Dim A() As Variant
Let A = Nums(0)

BigString = ""
For N = LBound(A) To UBound(A)
    BigString = BigString & A(N, 1)
Next
ConcatenateArray = BigString

End Function

然后将单元格中的数组公式更改为：

=ConcatenateArray(IF(VALUE(RIGHT($A$1:$A$500,3))>400,$A$1:$A$500,""))

当然，你需要按下CTRL + SHIFT + ENTER键来确认单元格为数组公式，而不是仅仅按下ENTER键。

- Brian Pressler

我很感激提供UDF解决方案的详细代码。不幸的是，根据工作簿的情况，VBA可能无法使用（例如：当文件被分发给执行严格“无宏”政策的群体时）。 - Grade 'Eh' Bacon

明白了...但希望这能帮助那些没有这种限制的人。它是Excel非常强大的扩展。 - Brian Pressler

我同意 - 这是大多数问题的一个很好的解决方案。 - Grade 'Eh' Bacon

0

我会尝试回答此帖子中提出的几个问题：

如何使一个接受文本结果数组的公式正常工作，并将其转换为“可用范围”[以便可以插入上面的连接函数]，

即使第一个问题是可行的，但最后一个部分（即“[以便可以插入上面的连接函数]”）是不可能的，因为CONCATENATE函数不接受范围作为参数。

还是能够立即使用文本参数进行操作[如mid、search、substitute等]?现在我唯一看到的方法是使用上面的示例3，然后进一步说，例如，Concatenate(C1,C2,C3...C10)。

这当然是一种方法，但请尝试以下方法：

让我们从这里开始：

现在假设我想取出示例3中的结果，并对它们执行一些文本操作。例如，假设我想将它们全部连接成一个文本字符串。

但首先让我们假设以下内容：

-. 数据范围位于D10:F510，包括字段：产品，产品，销售额和产品名称（选择）*

*用于列出示例3中公式的结果

.- 数据包含符合示例1定义的条件的23条记录（见图1）

.- 值400输入到单元格E4中，以便在公式中轻松修改条件，而不是硬编码（见图3）。

图1

现在，为了生成一个带有连接结果的数组并将其发布到可用范围内，让我们对示例3中的公式进行微小修改。在G11中输入此FormulaArray，并复制到最后一条记录（不仅仅是10行）

=TRIM(CONCATENATE(
IF(ROW(G11)-ROW(G$11)+1=1,"",G10)," ",
IFERROR(INDEX($E$11:$E$510,
SMALL(IF(VALUE(RIGHT($D$11:$D$510,3))>$E$4,ROW($D$11:$D$510)-ROW($D$11)+1,""),
ROW(G11)-ROW(G$11)+1)),"")))

图2

在摘要部分的D4:E8中，我们有来自示例1和2的结果以及所选产品列表的连接结果（见图3）。在E8中输入此公式（建议将行高增加到最大值409并将文本换行设置为true）

=INDEX($M$11:$M$510,1+MAX(ROW($M$11:$M$510))-ROW($D$11))

图3

关于这个问题：

有没有一种方法可以从“公式数组结果”中访问多个文本元素，而不必逐个选择（例如：使用INDEX）？

在这种特定情况下（即数组元素的串联），我会采用不同的思路生成串联结果的数组，然后挑选所需的元素，即使需要使用INDEX。

最后，我想对这些公式做一个小注释：

示例2：

=INDEX(B1:B500,MIN(IF(VALUE(RIGHT(A1:A500,3))>400,ROW(A1:A500),"")))

如果数据范围不是从第1行开始，请使用以下公式：

=INDEX($E$11:$E$510,MIN(IF(VALUE(RIGHT($D$11:$D$510,3))>400,
1+ROW($D$11:$D$510)-ROW($D$11),"")))

示例3：

=INDEX($B$1:$B$500,SMALL(IF(VALUE(RIGHT($A$1:$A$500,3))>400,ROW($A$1:$A$500),""),ROW()))

如果数据范围不是从第1行开始，请使用以下公式：

=IFERROR(INDEX($E$11:$E$510,
SMALL(IF(VALUE(RIGHT($D$11:$D$510,3))>$E$4,
1+ROW($D$11:$D$510)-ROW($D$11),""),
1+ROW()-ROW($K$11))),"")

- EEM

1

我可以看到如何使用辅助列来完成所有这些操作 - 我理解你所说的“先连接再检索单个结果”的意思。然而，这仍然不能在单个单元格中提供解决方案，这可能是不可能的（除非像@bidout上面的公式一样重复多次使用相同的公式）。请注意，对于您的拖动公式，我只需将公式设置为以下内容，即可完全消除数组公式的需要（从G11开始并向下拖动[G10为空白]）：= G10＆IF（RIGHT（D11，3）> 400，E11，“”），这实际上就是TigerAvatar提供的答案。 - Grade 'Eh' Bacon

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- tigeravatar · Accepted Answer

如先前所述，在单个单元格中没有本地功能可以实现您想要的操作。如果您绝对不能使用VBA，则可以使用辅助列（如果需要，可以隐藏该列），然后使要显示结果的单元格仅显示辅助列的最后一个单元格。

示例：

Produce Name   Type
Apple          Fruit
Broccoli       Vegetable
Carrot         Vegetable
Orange         Fruit

假设您希望单个单元格显示所有水果结果。您可以使用另一列来承载此公式。稍后您将隐藏该列，因此让我们使用远离视线的一列，例如Z列。我们还希望能够轻松更改要查找的内容，因此我们将条件放在D2单元格中。在Z2单元格和下方复制，您将使用以下公式：

=IF(B2=$D$2,IF(Z1="",A2,Z1&", "&A2),IF(Z1="","",Z1))

这将导致以下结果：

Produce Name   Type              Search For   (other columns until you get to Z)      
Apple          Fruit             Fruit                                             Apple
Broccoli       Vegetable                                                           Apple
Carrot         Vegetable                                                           Apple
Orange         Fruit                                                               Apple, Orange

然后，在你想要放置结果的单元格（比如D3），只需使用以下公式即可获取来自辅助列的最后一个结果，然后隐藏该辅助列。

=Z5

这将导致以下结果：

Produce Name   Type              Search For
Apple          Fruit             Fruit
Broccoli       Vegetable         Apple, Orange
Carrot         Vegetable
Orange         Fruit

你可以使用一个动态命名范围来代替简单的=Z5，这样你就能确保获取到帮助列中的最后一个单元格，以便在数据增长或缩小时仍能得出正确的结果。但现在你可以更改D2单元格的内容，将Fruit更改为Vegetable，此时结果单元格将显示Broccoli, Carrot。希望类似这样的方法能够满足你的需求。