在Excel中连接多个匹配项 [英] concatenate multiple matches in excel
问题描述
请参见下面
我想将表2中的注释"连接到表1中,如图像系列所示,而不使用TEXTJOIN()或宏.仅使用常规的Excel功能
I want to concatenate 'comments' in table 2 into table 1 as shown in the series of images without using TEXTJOIN() or macros. Only using regular excel functions
推荐答案
有一种简单的方法可以做到这一点. :)请参见此 Google工作表以了解工作原理例子.
There's a simple way to do this. :) Please see this Google sheet for a working example.
您可以使用FILTER和JOIN函数来实现此目的:
You can use the FILTER and JOIN functions to achieve this:
=iferror(join(", ", filter(E$3:E$12, D$3:D$12 = G3)))
在上面的示例中,FILTER函数将查看单元格D3:D12
,并尝试查找与G3
中的值匹配的行.对于匹配的行,FILTER函数将单元格E3:E12
中的值作为数组返回.
In the above example the FILTER function will look at cells D3:D12
and try to find rows matching the value in G3
. For the matching rows, the FILTER function returns the values from cells E3:E12
as an array.
JOIN用于将数组项与之间的逗号连接在一起.
JOIN is used to join the array items together with a comma in between.
最后,IFERROR消除了由于FILTER不匹配而导致的N/A
错误.
Finally, IFERROR gets rid of N/A
errors resulting from FILTER not matching anything.
(对此处的原始答案表示敬意 https://stackoverflow.com/a/23367059/36817 )
这篇关于在Excel中连接多个匹配项的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!