[已解决]如何将一区域内的数据按出现次数从少到多排序

lijian8003

下列代码是将a1:d3区域内的数据按出现次数从多到少排序
欲按出现次数从少到多排序，次数相同的，从小到大排，如何修改？
=MOD(SMALL(ROW($1:$10)-COUNTIF($A1:$D3,ROW($1:$10)-1)/1%-1,COLUMN(A1)),10)

最佳答案

月排行榜 / 总排行榜

dsmch

2015-2-15 11:05

借用2楼附件

1. Sub Macro1()
   
2. Dim arr, d, i&
   
3. Set d = CreateObject("scripting.dictionary")
   
4. arr = [a1:d3]
   
5. For i = 1 To UBound(arr)
   
6.     For j = 1 To UBound(arr, 2)
   
7.         d(arr(i, j)) = d(arr(i, j)) + 1
   
8.     Next
   
9. Next
   
10. With Range("f1").Resize(d.Count, 2)
    
11.     .Value = Application.Transpose(Array(d.keys, d.items))
    
12.     .Sort Key1:=[g1], Key2:=[f1], Header:=xlGuess
    
13. End With
    
14. [g:g] = ""
    
15. End Sub

复制代码

跳转到最佳答案楼层

baksy

附件

dsmch

借用2楼附件

1. Sub Macro1()
   
2. Dim arr, d, i&
   
3. Set d = CreateObject("scripting.dictionary")
   
4. arr = [a1:d3]
   
5. For i = 1 To UBound(arr)
   
6.     For j = 1 To UBound(arr, 2)
   
7.         d(arr(i, j)) = d(arr(i, j)) + 1
   
8.     Next
   
9. Next
   
10. With Range("f1").Resize(d.Count, 2)
    
11.     .Value = Application.Transpose(Array(d.keys, d.items))
    
12.     .Sort Key1:=[g1], Key2:=[f1], Header:=xlGuess
    
13. End With
    
14. [g:g] = ""
    
15. End Sub

复制代码

lijian8003

dsmch 发表于 2015-2-15 11:05
借用2楼附件

排序后的数据横向排列，代码如何修改？

lijian8003

baksy 发表于 2015-2-15 08:25
附件

排序后的数据横向排列，代码如何修改？

love_liwu

lijian8003 发表于 2015-2-16 23:41
排序后的数据横向排列，代码如何修改？

直接复制数据，然后选择性粘贴，转置即可。

dsmch

Sub Macro1()
Dim arr, brr, d, i&
Set d = CreateObject("scripting.dictionary")
arr = [a1:d3]
For i = 1 To UBound(arr)
    For j = 1 To UBound(arr, 2)
        d(arr(i, j)) = d(arr(i, j)) + 1
    Next
Next
With Range("f1").Resize(d.Count, 2)
    .Value = Application.Transpose(Array(d.keys, d.items))
    .Sort Key1:=[g1], Key2:=[f1], Header:=xlGuess
    brr = .Value
    .Clear
End With
Range("f1").Resize(1, UBound(brr)) = Application.Transpose(Application.Index(brr, 0, 1))
End Sub

lijian8003

dsmch 发表于 2015-2-17 08:45
Sub Macro1()
Dim arr, brr, d, i&
Set d = CreateObject("scripting.dictionary")

感谢帮助。
如果用函数公式的话，代码如何写？恳望帮助。