Question

excel中如何解决一对多数据合并，具体从A1:B10到D1:E4？

Answer 1

1、如果你的EXCEL支持TEXTJOIN函数，就可以直接用数组公式计算，如图E2公式

=TEXTJOIN("/",TRUE,IF($A$2:$A$10=D2,$B$2:$B$10,""))

按Ctrl+Shift+Enter组合键输入。

2、如果不支持以上函数，可以编写自定义函数完成，按ALT+F11组合键，打开VB窗口，执行"插入"-"模块"，复制下面代码进去，回到工作表，像使用普通公式一样使用它，如E2公式：

=JOINIF($A$2:$A$10,D2,$B$2:$B$10)

代码：

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