EXCEL表格数字排序?

发布网友发布时间：2022-04-29 14:55

我来回答

共4个回答

热心网友时间：2022-05-23 00:18

Sub PPOP()

Dim a As Range, ARR(0 To 9), BRR(0 To 9), i%, k%, b As Range

Range("C1:f9999").Clear

For Each a In Range(Range("A1"), Range("A65535").End(3))

For i = 0 To 9

ARR(i) = 0

BRR(i) = i

Next i

For i = 1 To Len(a)

ARR(Mid(a, i, 1)) = ARR(Mid(a, i, 1)) + 1

Next i

'对数组个数进行排序

For k = 0 To 8

For i = k + 1 To 9

If ARR(BRR(k)) <= ARR(BRR(i)) Then

X = BRR(k)

BRR(k) = BRR(i)

BRR(i) = X

If BRR(k) < BRR(i) And ARR(BRR(k)) = ARR(BRR(i)) Then

X = BRR(k)

BRR(k) = BRR(i)

BRR(i) = X

End If

Next i

Next k

Set b = a.Offset(0, 2)

For j = 0 To 9

For k = 1 To ARR(BRR(j))

b = b & " " & BRR(j)

Next k

Next j

Next a

End Sub

热心网友时间：2022-05-23 01:36

=CONCAT(MOD(LARGE((LEN(A1)-LEN(SUBSTITUTE(A1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"")))*10+MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW(INDIRECT("1:"&LEN(A1)))),10))
ctrl+shift+回车追问

你好，如果数字是分开的，想要的排序也是分开的，原公式应该怎样修改才可以啊？

追答P5:
=IFERROR(MOD(LARGE(COUNTIF($G5:$N5,$G5:$N5)*10+$G5:$N5,COLUMN(A1)),10),"")
ctrl+shift+回车，右拉。

热心网友时间：2022-05-23 03:11

本方法适合07及以上版本
加10个辅助列可以解决，数据在a2，c1：L1依次输入0-9.c2=IF(COUNT(FIND(C$1,$A2)),--(TEXT(LEN($A2)-LEN(SUBSTITUTE($A2,C$1,"")),"#;;")&C$1),"")右拉到L2
然后b2结果=REPT(RIGHT(LARGE(C2:L2,1)),--LEFT(LARGE(C2:L2,1)))&IFERROR(REPT(RIGHT(LARGE(C2:L2,2)),--LEFT(LARGE(C2:L2,2))),"")&IFERROR(REPT(RIGHT(LARGE(C2:L2,3)),--LEFT(LARGE(C2:L2,3))),"")&IFERROR(REPT(RIGHT(LARGE(C2:L2,4)),--LEFT(LARGE(C2:L2,4))),"")&IFERROR(REPT(RIGHT(LARGE(C2:L2,5)),--LEFT(LARGE(C2:L2,5))),"")&IFERROR(REPT(RIGHT(LARGE(C2:L2,6)),--LEFT(LARGE(C2:L2,6))),"")&IFERROR(REPT(RIGHT(LARGE(C2:L2,7)),--LEFT(LARGE(C2:L2,7))),"")&IFERROR(REPT(RIGHT(LARGE(C2:L2,8)),--LEFT(LARGE(C2:L2,8))),"")&IFERROR(REPT(RIGHT(LARGE(C2:L2,9)),--LEFT(LARGE(C2:L2,9))),"")&IFERROR(REPT(RIGHT(LARGE(C2:L2,10)),--LEFT(LARGE(C2:L2,10))),"")

热心网友时间：2022-05-23 05:02

如果版本支持的话

=CONCAT(LET(x,FILTER(ROW($2:$11)-1,ISNUMBER(FIND(ROW($2:$11)-1,A2))),y,LEN(A2)-LEN(SUBSTITUTE(A2,x,)),SORTBY(REPT(x,y),y,-1,x,-1)))