excel：我如何在单元格内进行排序？ [英] excel: how do i sort within a cell?

问题描述

我有一个用逗号分隔的字符串，可以使用excel公式在单元格内的值中排序吗？

解决方案

这是一个解决方案（快速回车代码从此处）。您只需将一个按钮连接到 SortVals 宏，您只需点击该按钮即可对活动单元格中的逗号分隔值进行排序。

  Option Explicit 
 
公共Sub SortVals（）
 Dim i As Integer 
 Dim arr As Variant 
 arr = Split（ActiveCell.Text，，）
 
'修剪值，所以排序将正常工作
对于i = LBound（arr）到UBound（arr）
 arr（i）= Trim（arr（i））
 Next i 
 
'sort 
 QuickSort arr，LBound（arr），UBound（arr）
 
'加载排序值返回单元格
 Dim逗号As String 
逗号=
 ActiveCell =
对于i = LBound（arr）到UBound（arr ）
 ActiveCell = ActiveCell&逗号& CStr（arr（i））
 comma =，
 Next i 
 End Sub 
 
 Public Sub QuickSort（vArray As Variant，inLow As Long，inHi As Long）
 
 Dim pivot As Variant 
 Dim tmpSwap As Variant 
 Dim tmpLow As Long 
 Dim tmpHi As Long 
 
 tmpLow = inLow $ （$）
 
 
 $ b while（vArray（tmpLow）< pivot和tmpLow< inHi）
 tmpLow = tmpLow + 1 
 Wend 
 
 While（pivot< vArray（tmpHi）和tmpHi& ; inLow）
 tmpHi = tmpHi  -  1 
 Wend 
 
如果（tmpLow< = tmpHi）然后
 tmpSwap = vArray（tmpLow）
 vArray tmpLow）= vArray（tmpHi）
 vArray（tmpHi）= tmpSwap 
 tmpLow = tmpLow + 1 
 tmpHi = tmpHi  -  1 
 End If 
 
 Wend 
 
 If（inLow< tmpHi）Then QuickSort vArray，inLow，tmpHi 
 If（tmpLow< inHi）然后QuickSort vArray，tmpLow，inHi 
 
 End Sub 
  

i have a string separated by commas, is it possible to use an excel formula to sort within the values within the cell?

解决方案

Here's a solution (quicksort code stolen from here). You would just wire up a button to the SortVals macro and you could just click the button and it'll sort the comma separated values in the active cell.

Option Explicit

Public Sub SortVals()
    Dim i As Integer
    Dim arr As Variant
    arr = Split(ActiveCell.Text, ",")

    ' trim values so sort will work properly
    For i = LBound(arr) To UBound(arr)
        arr(i) = Trim(arr(i))
    Next i

    ' sort
    QuickSort arr, LBound(arr), UBound(arr)

    ' load sorted values back to cell
    Dim comma As String
    comma = ""
    ActiveCell = ""
    For i = LBound(arr) To UBound(arr)
        ActiveCell = ActiveCell & comma & CStr(arr(i))
        comma = ","
    Next i
End Sub

Public Sub QuickSort(vArray As Variant, inLow As Long, inHi As Long)

  Dim pivot   As Variant
  Dim tmpSwap As Variant
  Dim tmpLow  As Long
  Dim tmpHi   As Long

  tmpLow = inLow
  tmpHi = inHi

  pivot = vArray((inLow + inHi) \ 2)

  While (tmpLow <= tmpHi)

     While (vArray(tmpLow) < pivot And tmpLow < inHi)
        tmpLow = tmpLow + 1
     Wend

     While (pivot < vArray(tmpHi) And tmpHi > inLow)
        tmpHi = tmpHi - 1
     Wend

     If (tmpLow <= tmpHi) Then
        tmpSwap = vArray(tmpLow)
        vArray(tmpLow) = vArray(tmpHi)
        vArray(tmpHi) = tmpSwap
        tmpLow = tmpLow + 1
        tmpHi = tmpHi - 1
     End If

  Wend

  If (inLow < tmpHi) Then QuickSort vArray, inLow, tmpHi
  If (tmpLow < inHi) Then QuickSort vArray, tmpLow, inHi

End Sub

这篇关于excel：我如何在单元格内进行排序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！