自定义函数--多单元格文本合并

lailyb 2012-07-20

展开全文

多单元格文本合并/条件合并

经常遇到网友问如何将多个单元格中的文本合并到一个单元格中，或者根据某个条件将多个单元格中的文本合并到一个单元格中。

模仿sumif函数，自定义了一个HB函数供参考。
使用方法：在excel中按住ALT，依次按F11,I,M
将下面的代码粘贴在刚刚插入的模块中就可以在工作表中调用函数HB了。

Function HB(if_range, Optional criteria, Optional hb_range, Optional separator)
    If IsMissing(separator) Then separator = " "
    If IsMissing(hb_range) Then Set hb_range = if_range
    If IsMissing(criteria) Then
        For Each c In hb_range.Cells
            t = t & separator & c
        Next
    Else
        If Left(criteria, 1) = "F" Then
            For i = 1 To if_range.Cells.Count
                If InStr(if_range.Cells(i), Mid(criteria, 2)) Then t = t & separator & hb_range.Cells(i)
            Next i
        Else
            For i = 1 To if_range.Cells.Count
                If Application.Evaluate(if_range.Cells(i) & criteria) Then t = t & separator & hb_range.Cells(i)
            Next i
        End If
    End If
    HB = Mid(t, 2)
End Function

函数参数说明
HB(参数1,参数2,参数3,参数4)
参数1：查找区域。用来查找或与参数2进行比较的区域
参数2：条件。如果该参数省略，则直接合并参数1中的全部内容。如果该参数以F开头，则在参数1中查找F后所跟字符串。
参数3：当参数1符合参数2的条件时，要返回的内容。该区域大小必须与参数1一致。如果省略则等于参数1。
参数4：返回结果中的分隔符，如果省略则为空格。

用一个具体的实例来说明一下函数的用法。
假设A1:B11中有如下数据

姓名成绩
曹操 99
孙权 96
刘备 96
关羽 90
张飞 59
赵云 89
诸葛亮 100
周瑜 99
大乔 82
小乔 85

公式：=hb(A2:A11)
说明：将A2:A11区域的文本合并，使用默认分隔符空格
结果：曹操孙权刘备关羽张飞赵云诸葛亮周瑜大乔小乔

公式：=hb(A2:A11,,,"/")
说明：将A2:A1区域的文本合并，使用分隔符“/”
结果：曹操/孙权/刘备/关羽/张飞/赵云/诸葛亮/周瑜/大乔/小乔

公式：=hb(B2:B11,"=99",A2:A11)
说明：将B2:B11区域等于99的对应A2:A11区域合并，使用默认分隔符空格
结果：曹操周瑜

公式：=hb(B2:B11,">90",A2:A11,",")
说明：将B2:B11中大于90的对应A2:A11区域合并，使用分隔符“,”
结果：曹操,孙权,刘备,诸葛亮,周瑜

公式：=hb(A2:A11,"F乔")
说明：在A2:A11查找“乔”，返回合并结果，使用默认分隔符空格。
结果：大乔小乔

=============================================================================================

附件：中文版

学写个中文版的
Function 条件合并(条件区域 As Range, Optional 条件 As Variant = "<>", Optional 合并区域 As Range, Optional 分隔符 As String = "")
    Dim 行数 As Integer, 列数 As Integer, 行 As Integer, 列 As Integer
    Dim 合并 As String

    行数 = 条件区域.Rows.Count
    列数 = 条件区域.Columns.Count
    If 合并区域 Is Nothing Then Set 合并区域 = 条件区域 Else Set 合并区域 = 合并区域(1).Resize(行数, 列数)
    With Application.WorksheetFunction
        For 行 = 1 To 行数
        For 列 = 1 To 列数
                If .CountIf(条件区域(行, 列), 条件) Then 合并 = 合并 & 分隔符 & 合并区域(行, 列)
        Next
        Next
    条件合并 = .Substitute(合并, 分隔符, "", 1)
    End With

End Function