• 主页

# excel中有没有什么办法把一个人的多项数据处理成一行多列？

...全文
58 点赞 收藏 7

7 条回复
milaoshu1020 2020年04月16日

Option Explicit

Sub 多项数据处理成一行多列()
Dim dict As Object
Set dict = CreateObject("scripting.dictionary")

Dim i As Long
i = 3

Do While Sheet1.Cells(i, 1) <> ""
Dim j As Long
For j = 3 To 16
If Sheet1.Cells(i, j) <> "" Then
Dim strKey As String
strKey = Sheet1.Cells(i, j) & "," & Sheet1.Cells(i, 1) & "," & Sheet1.Cells(1, j)

Dim strValue As String
strValue = Sheet1.Cells(i, 2)

If dict.exists(strKey) Then
dict.Item(strKey) = dict.Item(strKey) & "," & strValue
Else
End If
End If
Next
i = i + 1
Loop

i = 2
Dim varKey As Variant
For Each varKey In dict
Dim arrKey() As String
arrKey = Split(varKey, ",")

Sheet2.Cells(i, 1) = arrKey(0)
Sheet2.Cells(i, 2) = arrKey(1)
Sheet2.Cells(i, 3) = dict(varKey)
Sheet2.Cells(i, 4) = arrKey(2)

i = i + 1
Next

MsgBox "done!"
End Sub

milaoshu1020 2020年04月16日

milaoshu1020 2020年04月16日

Option Explicit

Sub 多项数据处理成一行多列()
Dim dict As Object
Set dict = CreateObject("scripting.dictionary")

Dim i As Long
i = 3

Do While Sheet1.Cells(i, 1) <> ""
Dim j As Long
For j = 3 To 16
If Sheet1.Cells(i, j) <> "" Then
Dim strKey As String
strKey = Sheet1.Cells(i, j) & "," & Sheet1.Cells(i, 1) & "," & Sheet1.Cells(1, j)

Dim strValue As String
strValue = Sheet1.Cells(i, 2)

If dict.exists(strKey) Then
dict.Item(strKey) = dict.Item(strKey) & "," & strValue
Else
End If
End If
Next
i = i + 1
Loop

i = 2
Dim varKey As Variant
For Each varKey In dict
Dim arrKey() As String
arrKey = Split(varKey, ",")

Sheet2.Cells(i, 1) = arrKey(0)
Sheet2.Cells(i, 2) = arrKey(1)
Sheet2.Cells(i, 3) = dict(varKey)
Sheet2.Cells(i, 4) = arrKey(2)

i = i + 1
Next

MsgBox "done!"
End Sub

vansoft 2020年04月15日

VBA

1576

1.0w+

VBA（Visual Basic for Applications）是Visual Basic的一种宏语言，是在其桌面应用程序中执行通用的自动化(OLE)任务的编程语言。