求大神帮助将一行多列数据转换成多行一列数据

chinasun0

求大神帮助将将表1中的城市、数量1、数量2、数量3，生成表2中的城市、数量。万分感谢!!!

liulang0808

供参考。。。。

ykcbf1100

附件供参考。。。

swl4561255

1. =INDEX(A$2:A$5,INT((ROW(A1)-1)/3)+1)

复制代码

1. =INDEX(E$2:G$5,INT((ROW(A1)-1)/3)+1,MOD(ROW(A1)-1,3)+1)

复制代码

ykcbf1100

参与一下。。。

1. Sub ykcbf()  '//2024.4.16
   
2.     r = Me.Cells(Rows.Count, 1).End(3).Row
   
3.     arr = Me.[a1].Resize(r, 7)
   
4.     ReDim brr(1 To UBound(arr) * 5, 1 To 2)
   
5.     For i = 2 To UBound(arr)
   
6.         For j = 5 To UBound(arr, 2)
   
7.             m = m + 1
   
8.             brr(m, 1) = arr(i, 1)
   
9.             brr(m, 2) = arr(i, j)
   
10.         Next
    
11.     Next
    
12.     Me.[i2:j1000] = ""
    
13.     Me.[i2].Resize(m, 2) = brr
    
14. End Sub
    
15. 
    

复制代码

excel小白白2

转置嘛，我习惯用application.transpose

ooluckydog

baofa2

1. Sub test1()
   
2.   Dim ar, i As Long, Conn As Object, rs As Object, SQL As String, strConn As String, tb As String
   
3.   Set Conn = CreateObject("ADODB.Connection")
   
4.   strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source="
   
5.   Conn.Open strConn & ThisWorkbook.FullName
   
6.   With Range("A1").CurrentRegion
   
7.     tb = .Parent.Name & "$" & .Address(0, 0)
   
8.     ar = .Rows(1).Value
   
9.   End With
   
10.   For i = 5 To UBound(ar, 2)
    
11.     SQL = SQL & " UNION ALL SELECT " & ar(1, 1) & "," & ar(1, i) & " AS 数量 FROM [" & tb & "]"
    
12.   Next
    
13.   SQL = "SELECT * FROM (" & Mid(SQL, 12) & ") ORDER BY " & ar(1, 1) & " DESC,数量"
    
14.   Set rs = Conn.Execute(SQL)
    
15.   With Range("P1")
    
16.     .CurrentRegion.ClearContents
    
17.     For i = 0 To rs.Fields.Count - 1
    
18.       .Offset(, i) = rs.Fields(i).Name
    
19.     Next
    
20.     .Offset(1).CopyFromRecordset rs
    
21.   End With
    
22.   rs.Close: Set rs = Nothing: Conn.Close: Set Conn = Nothing
    
23.   Beep
    
24. End Sub

复制代码

x-xx-xx