在 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
,因为无论如何它都不会在表中。