关于数值范围排序？

adidas315073682

数值范围排序 :  AAA-BBB
                         BBB-CCC
                         ccc-ddd
还有200-100怎么转换成100-200

ykcbf1100

借题练习，VBA，排序

ykcbf1100

1. Sub ykcbf()   '//2025.3.28   按A列中的第一组数字排序
   
2.     Application.ScreenUpdating = False
   
3.     Application.DisplayAlerts = False
   
4.     r = Cells(Rows.Count, "a").End(3).Row
   
5.     arr = Range("a1:b" & r)
   
6.     u = UBound(arr, 2)
   
7.     For i = 1 To UBound(arr)
   
8.         st = Split(arr(i, 1), "-")
   
9.         arr(i, u) = Val(st(0))
   
10.     Next
    
11.     Set sht = ThisWorkbook.Worksheets.Add
    
12.     With sht
    
13.         Set Rng = .[a1].Resize(UBound(arr), UBound(arr, 2))
    
14.         Rng.Value = arr
    
15.         Rng.Sort Rng.Columns(2), 1, Orientation:=1
    
16.         arr = [a1].Resize(r, u - 1).Value
    
17.     End With
    
18.     sht.Delete
    
19.     Range("a1:a" & r) = arr
    
20.     Application.ScreenUpdating = True
    
21.     MsgBox "OK！"
    
22. End Sub
    

复制代码

leepoison

=SORTBY(MAP(A1:A5,LAMBDA(y,MIN(1*TEXTSPLIT(y,"-"))&"-"&MAX(1*TEXTSPLIT(y,"-")))),MAP(A1:A5,LAMBDA(x,MAX(1*TEXTSPLIT(x,"-")))))

无关岁月

1. =LET(a,MAP(A1:A5,LAMBDA(x,TEXTJOIN("-",,SORT(--REGEXP(x,"[^-]+"),,,1)))),SORTBY(a,--REGEXP(a,"[\d.]+")))

复制代码

mjm04

高版本\WPS 可用

不去重：
=LET(x,TOCOL(A1:B20,3),y,SORT(HSTACK(x,MAP(x,LAMBDA(a,MAX(REGEXP(a,"\d+\.?")*1)))),2,1),CHOOSECOLS(y,1))


去重

=LET(x,UNIQUE(TOCOL(A1:B20,3)),y,SORT(HSTACK(x,MAP(x,LAMBDA(a,MAX(REGEXP(a,"\d+\.?")*1)))),2,1),CHOOSECOLS(y,1))

sandays

=LET(a,BYROW(A1:A5,LAMBDA(x,TEXTJOIN("-",,SORT(--TEXTSPLIT(x,,"-"))))),SORTBY(a,--TEXTBEFORE(a,"-")))