相同字段分在不同列怎么做透视

dashe5023

款号	数量	款号	数量	款号	数量
A6330060336	0	A6330060337	0	A6330060301	3
A6330060636	0	A6330060637	0	A6330060601	3
A6330061136	0	A6330061137	0	A6330061101	3
A6340010336	2	A6340010337	0	A6340010301	1
A6340010636	1	A6340010637	0	A6340010601	2
A6340011136	1	A6340011137	0	A6340011101	1
A6340040336	0	A6340040337	0	A6340040301	1
A6340040636	0	A6340040637	0	A6340040601	1
A6340110336	0	A6340110337	0	A6340110301	0

汗了，数据像这个样子 为什么一样的字段Excel要自己编个123号来

dashe5023

或者用个VBA解决掉他？怎么弄呢

zhaogang1960

导入数据
select * from [Sheet1$A:B] union all select * from [Sheet1$C:D] union all select * from [Sheet1$E:F] union all select * from [Sheet1$G:H] union all select * from [Sheet1$I:J] union all select * from [Sheet1$K:L] union all select * from [Sheet1$M:N] union all select * from [Sheet1$O:P] union all select * from [Sheet1$Q:R] union all select * from [Sheet1$S:T] union all select * from [Sheet1$U:V] union all select * from [Sheet1$W:X] union all select * from [Sheet1$Y:Z]
再做透视表

zhaogang1960

vba:
Sub Macro1()
    Dim cnn As Object, sql$, lr&, i%
    lr = Sheets("Sheet1").[a1].CurrentRegion.Rows.Count
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=excel 8.0;Data Source=" & ThisWorkbook.FullName
    For i = 1 To 26 Step 2
        If sql <> "" Then sql = sql & " union all "
        sql = sql & "select * from [Sheet1$" & Cells(1, i).Resize(lr, 2).Address(0, 0) & "]"
    Next
'    sql进一步处理
    [a1].CurrentRegion.Offset(1, 0).ClearContents
    [a2].CopyFromRecordset cnn.Execute(sql)
    cnn.Close
    Set cnn = Nothing
End Sub

dashe5023

Thank you .My friend!!!!!