Excel实现数据分类转换【！！！！紧急！！！！】

w_cun

转换.zip (6.9 KB, 下载次数: 7)

2011-11-23 18:26 上传

点击文件名下载附件
附件

如何用函数或者VBA实现从表1转换为表2格式，请高手支招，感谢！！！

1、原始表格

Name	cell
ABC	1,2,3
BCD	1,2,3,4,5
EF	1,2,3,4

2、转换成如下表格：

ABC	1
ABC	2
ABC	3
BCD	1
BCD	2
BCD	3
BCD	4
BCD	5
EF	1
EF	2
EF	3
EF	4

w_cun

已经解决，代码如下，很经典！！！！！

Sub changformat()

Dim i As Integer, j As Integer, k As Integer, AAA As String, BBB As String, CCC As String
k = 1
CCC = ""
For i = 2 To Worksheets("原始表").Cells(65536, 1).End(xlUp).Row
  AAA = Worksheets("原始表").Cells(i, 1)
  BBB = Worksheets("原始表").Cells(i, 2)
  For j = 1 To Len(BBB)
    If Mid(BBB, j, 1) <> "," And j < Len(BBB) Then
       CCC = CCC & Mid(BBB, j, 1)
    ElseIf Mid(BBB, j, 1) <> "," Or j = Len(BBB) Then
       CCC = CCC & Mid(BBB, j, 1)
       k = k + 1
       Worksheets("输出表").Cells(k, 1) = AAA
       Worksheets("输出表").Cells(k, 2) = CCC
    Else
       k = k + 1
       Worksheets("输出表").Cells(k, 1) = AAA
       Worksheets("输出表").Cells(k, 2) = CCC
       CCC = ""
    End If
  Next j
  CCC = ""
Next i

End Sub

jinlun2002

深奥，不懂，但也学习了

吴志龙

没什么公式或函数可以解决么？