各个连续区域的内部数字排序

兰色幻想

           这个题目不是要对整列进行统一排序，而是要对每一个连续的区域进行内部排序。详见下图：

          注：函数或VBA均可

            
     

  本题已设置仅作者可见，两天后解除。

yuhe0008

Sub test()
Dim i%
For i = 1 To [a65536].End(xlUp).Row + 1
If Cells(i, 1) = "" Then Cells(i - 1, 1).CurrentRegion.Sort Key1:=Cells(i - 1, 1)
Next
End Sub

放浪形骸

1. Sub SortSub()
   
2. Dim iRowMax As Integer
   
3. Dim iSta As Integer
   
4. Dim i As Integer
   
5. 
   
6. iRowMax = Range("A65536").End(xlUp).Row
   
7. iSta = 0
   
8. For i = 1 To iRowMax + 1
   
9.     If Cells(i, 1) = "" Then
   
10.         If i - 1 <> iSta Then Range("C" & iSta + 1 & ":C" & i - 1).FormulaR1C1 =  "=SMALL(R" & iSta + 1 & "C1:R" & i - 1 & "C1,ROW()-" & iSta & ")"
    
11.         iSta = i
    
12.     End If
    
13. Next
    
14. End Sub

复制代码

天马

回复 兰色幻想 的帖子

chunlin1wang

=IF(A1:A18="","",MOD(SMALL(MMULT((ROW(1:18)>=COLUMN(A:R))*1,(A1:A18="")*1)/1%%+A1:A18,ROW(1:18)),10^4))

1. Sub paix()
   
2.     For i = 1 To Range("A65536").End(xlUp).Row
   
3.         If Range("A" & i) <> "" Then
   
4.             Range("A" & i).CurrentRegion.Copy Range("d" & i)
   
5.             Range("d" & i).CurrentRegion.Sort Key1:=Range("d" & i), Order1:=xlAscending
   
6.             i = i + Range("A" & i).CurrentRegion.Count
   
7.         End If
   
8.     Next i
   
9. End Sub

复制代码

蝶·舞

=IF(A1="","",MOD(SMALL(IF(A$1:A$18<>"",COUNTIF(OFFSET(A$1,,,ROW($1:$18)),"")/1%%+A$1:A$18),COUNT(A$1:A1)),10^4))

杰

Sub sample()
Dim MyRow As Long
MyRow = 1
Do
With Sheets("Sheet1").Sort
    .SetRange Range("a" & MyRow).CurrentRegion
    .Header = xlNo
    .Orientation = xlTopToBottom
    .Apply
End With
MyRow = Range("a" & MyRow).End(xlDown).Row
MyRow = Range("a" & MyRow).End(xlDown).Row
Loop Until MyRow > Cells(Rows.Count, 1).End(xlUp).Row
End Sub

=IF(A1="","",MOD(SMALL(IFERROR(LOOKUP(ROW($1:$18),IF(A$1:A$18="",ROW($1:$18))),1)/1%+A$1:A$18,ROW(A1)),100))

yuhe0008

取巧的公式：
=IF(A1:A18="","",--RIGHT(SMALL(COUNTIF(OFFSET(A1,,,ROW(1:18)),"")+A1:A18%,ROW(1:18)),2))
老实人的公式：
=IF(A1:A18="","",ROUND(MOD(SMALL(COUNTIF(OFFSET(A1,,,ROW(1:18)),"")+A1:A18%,ROW(1:18)),1)*100,))

yuhe0008

取巧公式：=IF(A1:A18="","",--RIGHT(TEXT(SMALL(COUNTIF(OFFSET(A1,,,ROW(1:18)),"")+A1:A18%,ROW(1:18)),"0.00"),2))

开辆小富康

=IF(A1="","",--RIGHT(SMALL(COUNTIF(OFFSET($A$1,,,ROW($1:$18),),"=")*10000+$A$1:$A$18,ROW(A1)),2))

这个有点长,不会短的