Excel 有条件地连接文本(不使用数组公式)

Excel 有条件地连接文本(不使用数组公式)

在 Excel 365 中:

表 1 和表 2 中列出了名称列表。表 1 和表 2 中的每一行都只有一个唯一的名称。

表 3 中有三列,分别为(列 A)表 1 和表 2 中的名称列表、(列 B)别名类型(a/k/a、d/b/a、f/d/b/a、f/k/a、n/k/a、o/b/o、pseud.)和(​​列 C)不同人的不同别名。表 3 中的每一行只有一个别名,而表 3 中可能有多行包含表 1 或表 2 中一个人的多个别名。

表 1 和表 2 中的某些名称有别名,而表 1 和表 2 中的其他名称则没有别名。表 1 和表 2 中的某些名称有多个别名,有时表 1 和表 2 中的名称没有别名。

我正在寻找一个不使用数组公式的公式,该公式将在表 1 和表 2 中的一列中添加表 3 中针对给定名称标识的所有别名以及缩写。

例如:

表 1 中的 Jon Doe 在表 3 中的别名类型可能是 a/k/a Ted 和 f/k/a Robert Smith。我希望在表 1 中有一列,其中对于 Jon Doe 的行,公式将得出结果“Jon Doe a/k/a Ted f/k/a Robert Smith”。

我该如何写出这样的公式?

=textjoin 是逻辑公式,但我需要像 =textjoinif 这样的有条件的东西,而不需要数组公式,即,不需要使用 control、shift、enter 输入公式,因为这些公式非常慢。

感谢您的帮助。

表格1

姓名 带别名的名称
乔恩·多伊 Jon Doe 又名 Ted,又名 Robert Smith

表 2(数据不同,但数据格式与表 1 相同)

表3

姓名 类型 别名
乔恩·多伊 又名 特德
乔恩·多伊 又名 罗伯特·史密斯

我正在寻找一个公式来生成表 1 中的 NameWithAlias,而不使用数组公式(即,不使用控制、移位、输入)。

谢谢

答案1

使用表 1 和表 2 中第 1 列的值作为以下公式的输入。用它们的引用替换我输入的“E1”:

=E1&" "&TEXTJOIN(" ",TRUE,SUBSTITUTE(FILTER(B1:B4&"|"&C1:C4,A1:A4=E1),"|"," "))

如果您想使用ARRAYTOTEXT(),它会在“a/k/a”类型信息之间放置一个逗号,因此您需要将其包装在另一个逗号中SUBSTITUTE()以用空格替换逗号:

=E1&" "&SUBSTITUTE(ARRAYTOTEXT(SUBSTITUTE(FILTER(B1:B4&"|"&C1:C4,A1:A4=E1),"|"," ")),",","")

基本上,FILTER()找到与表 1 或表 2 中的每个项目匹配的所有表 3 项目(用表 3 中的适当引用替换“B1:B4&”|“&C1:C4”部分)(“A1:A4 = E1”部分:用表 3 的列引用替换范围引用)。

添加的“|”位旨在防止奇怪的巧合发生。SUBSTITUITE()将它们从结果中删除,并在其位置放置所需的空间。

然后TEXTJOIN(),最好地,或者ARRAYTOTEXT()如果您不介意添加额外的SUBSTITUTE(),将把FILTER()(公式内部的数组,即使在旧版本中)的输出连接到所需的形式,并将结果附加到第 1 列的值。

让表格功能将其复制到列下方。

由于所有这些都发生在没有DYNAMIC数组的情况下,因此它在表中有效。只有SPILL(动态)方面不会。表将公式向下传递,因此不需要符合您的条件的 {CSE} 条目。

尽管 {CSE} 和SPILL不在公式之外使用,但所有内部数组都贯穿始终,让FILTER()TEXTJOIN()完成其工作。如果您实际上在辅助列或命名范围中分两步或多步输出结果,那么就会出现问题,所以这是可行的方法。

此外,没有必要折磨自己去到达FILTER()那里SPILL,因为无论如何它都不会在表中。

相关内容