答:编写了个自定义函数来完成。按Alt+F11组合键,打开vb编辑器。然后插入“模块”,将下面代码复制进去。
Function JOINIF(Rng1 As Range, Str, Rng2 As Range, Linkstr)
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) & Linkstr
End If
Else
Exit For
End If
Next j
Next i
JOINIF = Left(MyStr, Len(MyStr) - 1)
End Function
使用方法:在C列后插入一列,假设图中数据有100行,那么在C2单元格插入公式:
=JOINIF($B$2:$B$100,B2,$C$4:$C$100,";"),往下复制公式,就得到你需要的结果。