答:编写了个自定义函数。按ALT+F11,打开VB编辑器,选择"插入""模块",复制下面代码进去。回到工作表,像使用普通公式一样使用它:
Function JOINIF(Rng1 As Range, Str, Rng2 As Range)
Dim arr, brr
Dim i As Long
Dim j As Long
Dim MyStr As String
If Rng1.Rows.Count > 65536 Then
arr = Rng1.Resize(65536, Rng1.Columns)
brr = Rng2.Resize(65536, Rng1.Columns)
Else
arr = Rng1
brr = Rng2
End If
For i = 1 To UBound(arr)
For j = 1 To UBound(arr, 2)
If arr(i, j) <> "" Then
If arr(i, j) = Str Then
MyStr = MyStr & brr(i, j) & ","
End If
Else
Exit For
End If
Next j
Next i
JOINIF = Left(MyStr, Len(MyStr) - 1)
End Function
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/91529822720e0cf3c06f65140146f21fbf09aa40?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)