[已解决]横向取数归类和统计

张雄友

横向取数。

最佳答案

月排行榜 / 总排行榜

dsmch

2014-12-8 06:46

1. Sub Macro2()
   
2. Dim arr, i&, n%, j%
   
3. [b1:h1].ClearContents
   
4. arr = Range("a1").CurrentRegion
   
5. ReDim w(1 To 2, 1 To UBound(arr, 2) - 1)
   
6. w(1, 1) = 0: w(2, 1) = 2
   
7. For j = 3 To UBound(arr, 2)
   
8.     w(1, j - 1) = Val(arr(2, j)) - 0.01
   
9.     w(2, j - 1) = j
   
10. Next
    
11. For i = 3 To UBound(arr)
    
12.     n = Application.Lookup(arr(i, 1), w)
    
13.     arr(i, n) = arr(i, 1)
    
14.     arr(1, n) = arr(1, n) + 1
    
15. Next
    
16. Range("a1").CurrentRegion = arr
    
17. End Sub

复制代码

跳转到最佳答案楼层

dsmch

1. Sub Macro1()
   
2. Dim arr, i&, n%
   
3. w = [{0,60.99,80.99,100.99,110.99,130.99,150.99;0,1,2,3,4,5,6}]
   
4. [b1:h1].ClearContents
   
5. arr = Range("a1").CurrentRegion
   
6. For i = 3 To UBound(arr)
   
7.     n = Application.Lookup(arr(i, 1), w) + 2
   
8.     arr(i, n) = arr(i, 1)
   
9.     arr(1, n) = arr(1, n) + 1
   
10. Next
    
11. Range("a1").CurrentRegion = arr
    
12. End Sub

复制代码

dsmch

1. Sub Macro2()
   
2. Dim arr, i&, n%, j%
   
3. [b1:h1].ClearContents
   
4. arr = Range("a1").CurrentRegion
   
5. ReDim w(1 To 2, 1 To UBound(arr, 2) - 1)
   
6. w(1, 1) = 0: w(2, 1) = 2
   
7. For j = 3 To UBound(arr, 2)
   
8.     w(1, j - 1) = Val(arr(2, j)) - 0.01
   
9.     w(2, j - 1) = j
   
10. Next
    
11. For i = 3 To UBound(arr)
    
12.     n = Application.Lookup(arr(i, 1), w)
    
13.     arr(i, n) = arr(i, 1)
    
14.     arr(1, n) = arr(1, n) + 1
    
15. Next
    
16. Range("a1").CurrentRegion = arr
    
17. End Sub

复制代码