利用字典或数组排序

china047

各位老师:

        如何利用字典或数组对A列与B列的数据进行排序,将结果入在D列与E列,效果如D列与E列的效果! 利用字典与数组排序.rar (7.85 KB, 下载次数: 28)

2020-7-16 12:18 上传

点击文件名下载附件

duquancai

看描述，Excel工作表排序不就行了吗？

一把小刀闯天下

'录制个宏或者手工排序要快得多，，，

Option Explicit

Sub test()
  Dim arr, i, p
  arr = [a1].CurrentRegion.Offset(1).Value
  Call bsort(arr, 1, UBound(arr, 1) - 1, 1, UBound(arr, 2), 1)
  For i = 1 To UBound(arr, 1) - 1
    If arr(i, 1) <> arr(i + 1, 1) Then
      Call bsort(arr, p + 1, i, 1, UBound(arr, 2), 2)
      p = i
    End If
  Next
  [a1].CurrentRegion.Offset(1) = arr
End Sub

Function bsort(arr, first, last, left, right, key)
  Dim i, j, k, t
  For i = first To last - 1
    For j = first To last + first - 1 - i
      If arr(j, key) > arr(j + 1, key) Then
        For k = left To right
          t = arr(j, k): arr(j, k) = arr(j + 1, k): arr(j + 1, k) = t
        Next
      End If
    Next
  Next
End Function

liulang0808

这个排序就能解决的问题
楼主如果是为了练习字典跟数组，就自己尝试下，遇到具体问题再问

cui26896

试了一下这样就行了
Option Explicit
Sub test()
With Sheets(1)
.Activate
.Range("a2:b" & Cells(Rows.Count, "a").End(xlUp).Row).Sort key1:=.Range("a2"), order1:=xlAscending, key2:=.Range("b2"), order2:=xlAscending, Header:=xlNo
End With
End Sub

duquancai

python 语言，仅供参考！

1. # -*- coding: utf-8 -*-
   
2. # version: Python 3.7.0
   
3. import pandas as pd
   
4. 
   
5. df = pd.read_excel('./利用字典与数组排序.xlsm',usecols=['类别','数值'])
   
6. df.groupby('类别',as_index=False).apply(lambda df:df.sort_values('数值')).to_excel('./排序后.xlsx',index=False)

复制代码

tigertc

字典加数组排序,数组内排序可以用ArrayList,供参考.

1. Sub 排序()
   
2. Dim arr, d As Object
   
3. arr = [a1].CurrentRegion.Value
   
4. Set d = CreateObject("scripting.dictionary")
   
5. For i = 2 To UBound(arr)
   
6.     d(arr(i, 1)) = d(arr(i, 1)) & "|" & arr(i, 2)
   
7. Next i
   
8. k = getSort(d.keys)
   
9. ReDim arr(1 To 2, 1 To 1000): n = 1
   
10. For i = 0 To UBound(k)
    
11.     x = getSort(Split(d(k(i)), "|"))
    
12.     For j = 1 To UBound(x)
    
13.         arr(1, n) = k(i)
    
14.         arr(2, n) = x(j)
    
15.         n = n + 1
    
16.         If n = UBound(arr, 2) Then ReDim Preserve arr(1 To 2, 1 To n + 1000)
    
17.     Next j
    
18. Next i
    
19. Columns(7).Resize(, 2).Clear
    
20. [g1].Resize(1, 2) = Array("类别", "数值")
    
21. [g2].Resize(n - 1, 2) = Application.WorksheetFunction.Transpose(arr)
    
22. End Sub
    
23. Function getSort(x)
    
24. Dim objsca As Object
    
25. Set objsca = CreateObject("system.collections.arraylist")
    
26. For i = 0 To UBound(x)
    
27.     If x(i) = "" Or IsNumeric(x(i)) Then
    
28.         objsca.Add (Val(x(i)))
    
29.     Else
    
30.         objsca.Add (x(i))
    
31.     End If
    
32. Next i
    
33. objsca.Sort
    
34. For i = 0 To objsca.Count - 1
    
35.     x(i) = objsca(i)
    
36. Next i
    
37. getSort = x
    
38. End Function

复制代码

tigertc

附件供参考

chxw68

EXCEL自带排序方法比自己写的数组或字典排序方法速度要快好多！

china047

tigertc 发表于 2020-7-16 16:32
附件供参考