excel：我如何在一个单元格内排序？

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

我有一个用逗号分隔的字符串,是否可以使用excel公式在单元格内的值内进行排序?

3 个解决方案

#1

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.

这是一个解决方案(从这里窃取快速排序代码)。您只需将一个按钮连接到SortVals宏,您只需单击该按钮即可对活动单元格中的逗号分隔值进行排序。

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

#2

You'd have to split the string, sort the values and create a new string out of the sorted values.

您必须拆分字符串,对值进行排序并从排序值中创建新字符串。

#3

At the time this question was asked my LAselect framework add-in wasn't available to the general public. But now it is and it solves just these kind of problems in an elegant way.

在提出这个问题的时候,我的LAselect框架插件并没有向公众开放。但现在它是,它以优雅的方式解决了这些问题。

You only have to provide a few lines of 'solution' code in VBA to sort or order anything you want.

您只需在VBA中提供几行“解决方案”代码即可对您想要的任何内容进行排序或排序。

To get at the comma-delimited values use VBA's Split() function. You'll then have as many separate values as your cell had; if splitvalues(1) is firstname and splitvalues(0) is lastname, the Add-in could sort your single cell something like this:

要获得以逗号分隔的值,请使用VBA的Split()函数。然后,您将拥有与您的单元格一样多的单独值;如果splitvalues(1)是firstname而splitvalues(0)是lastname,则Add-in可以对您的单个单元格进行排序,如下所示:

Dim splitvalues1 as Variant, splitvalues2 as Variant

While LAselect(ControlSignal, BreakOutBox)
      valcol = BreakOutBox.Pins.ColData

      splitvalues1 = Split(BreakOutBox.Data.VBArray(BreakOutBox.Pins.RowSource, valcol), ",")

      splitvalues2 = Split(BreakOutBox.Data.VBArray(BreakOutBox.Pins.RowDestin, valcol), ",")


      If splitvalues1(0) > splitvalues2(0) Then
         ControlSignal = LA_ISLARGER
      ElseIf splitvalues1(0) < splitvalues2(0) Then
         ControlSignal = LA_ISSMALLER
      Else
         ControlSignal = LA_ISEQUAL
      End If
Wend

At LA_ISEQUAL you would have to test for splitvalues1(1) etc. to further sort duplicate lastnames on first names.

在LA_ISEQUAL,您必须测试splitvalues1(1)等,以进一步对名字上的重复姓氏进行排序。

Did it become clear to you that it really does NOT matter for my add-in how many delimter-separated fields you squeeze into a cell or even what other cells you to include into the decision? Both complete records are passed to your 'solution' code and you'll have full freedom what ever property you wish to sort on.

你是否清楚地知道,对于我的加载项,你挤入一个单元格中的分隔符分隔的字段,甚至是你要包含在决策中的其他单元格,这真的无关紧要?这两个完整的记录都会传递给您的“解决方案”代码,您可以完全*地找到您希望排序的财产。

My demo is available from www.liquorice-allsorts.com so you could try this out.

我的演示可以从www.liquorice-allsorts.com获得,所以你可以尝试一下。

#1