excel中怎么让同类型的多个数据合并到一个单元格？

发布网友 发布时间：2022-02-21 04:40

我来回答

共3个回答

热心网友 时间：2022-02-21 06:10

运行一小段代码可以一步实现，代码自动将结果生成一个新工作表。

假设数据在Sheet1中，按ALT+F11组合键，打开VB窗口，执行"插入"-"模块"，复制下面代码进去，按F5运行程序即得到结果。

Sub demo()

    Dim Orng As Range

    Dim Drng As Range

    Dim N As String, M As String, T As String, D As String, A As String

Set Orng = Sheets("Sheet1").Range("A1")

    With Sheets.Add

        .Range("A1:E1") = Array("姓名", "群众", "类型", "日期", "地址")

        Set Drng = .Range("A2")

    End With

    Do Until IsEmpty(Orng)

        N = Orng

        M = M & vbCrLf & Orng.Offset(0, 2)

        T = T & vbCrLf & Orng.Offset(0, 3)

        D = D & vbCrLf & Orng.Offset(0, 4)

        A = A & vbCrLf & Orng.Offset(0, 5)

        If Orng <> Orng.Offset(1, 0) Then

            With Drng

                .Offset(0, 0) = N

                .Offset(0, 1) = Right(M, Len(M) - 2)

                .Offset(0, 2) = Right(T, Len(T) - 2)

                .Offset(0, 3) = Right(D, Len(D) - 2)

                .Offset(0, 4) = Right(A, Len(A) - 2)

            End With

            N = "": M = "": T = "": D = "": A = ""

            Set Drng = Drng.Offset(1, 0)

        End If

        Set Orng = Orng.Offset(1, 0)

    Loop

    With Drng.Parent.UsedRange

        .ColumnWidth = 200

        .EntireRow.AutoFit

        .EntireColumn.AutoFit

    End With

End Sub

运行结果图：

热心网友 时间：2022-02-21 07:28

热心网友 时间：2022-02-21 09:02