excel查找显示多个文本

用EXCEL自带的函数很难解决，主要是要用到把数组的各元素连接到一起，EXCEL没这样的函数，自定义一个文本连接函数CONTXT,进入VBA,新建一个模块，
Option Explicit
Function ConTxt(ParamArray args() As Variant) As Variant
Dim tmptext As Variant, i As Variant, cellv As Variant
Dim cell As Range
tmptext = ""

For i = 0 To UBound(args)
If Not IsMissing(args(i)) Then
Select Case TypeName(args(i))
Case "Range"
For Each cell In args(i)
tmptext = tmptext & cell
Next cell
Case "Variant()"
For Each cellv In args(i)
tmptext = tmptext & cellv
Next cellv
Case Else
tmptext = tmptext & args(i)
End Select
End If
Next i

ConTxt = tmptext
End Function
然后在C1用公式=LEFT(contxt(IF(A1:A7="甲",B1:B7&CHAR(10),"")),LEN(contxt(IF(A1:A7="甲",B1:B7&CHAR(10),"")))-1)，按CTRL+SHIFT+ENTER结束，把单元格格式设为自动换行

选定BC列,点"数据"->"筛选"->"自动筛选&quo