excel - 当数字包含破折号 '-'时，在excel中对数字进行排序

Question

我在excel的列中有一些数字，如下所示:

201
202
208-1
210

当我对该列进行排序时，排序的列如下所示:

201
202
210
208-1

我如何对该列进行排序？我希望排好序的列变成这样:

201
202
208-1
210

或者

210
208-1
202
201

Answer 1

一个选项是隐藏列，假设上面列出的值在 A2:A5 中，在右侧插入一列，在 B2 中输入下面的公式并将其复制到其他 B 单元格:

=IFERROR(VALUE(LEFT(A2,FIND("-",A2)-1)),VALUE(A2))

或@Gary'sStudent 建议的替代方法，它也通过转换为小数来处理连字符后的值:

=IFERROR(VALUE(SUBSTITUTE(A2,"-",".")),VALUE(A2))

这会去除第一个连字符之前的数字。选择两列中的所有值，选择排序，然后按列B排序。然后，您可以右键单击 B 列并选择隐藏。

如果您不想使用隐藏列，那么我认为您唯一的选择是编写一些 VBA 来执行自定义排序过程。然后，您还需要一种触发方式，例如电子表格中的控件或键盘快捷键。

更新

我已经尝试过 VBA 程序，它并不像我预期的那么简单，所以可能有一种更简单的方法可以做到这一点。

我经历的基本步骤是提示用户输入单元格范围(您只需在提示时选择单元格)，将值存储到字符串数组，创建一个等效的数字数组，其中连字符被小数点替换，对数值数组进行排序，然后循环遍历按顺序粘贴值的初始范围。

我惊讶地发现 VBA 没有内置的数组排序方法，但是发现了一些可以使用的代码 here .这将创建一个临时工作表并使用工作表函数，那里还有纯 VBA 解决方案的代码，但它很长。

要创建 VBA 程序，您需要使用 alt F11 打开 VBA 编辑器并创建一个新模块，然后将下面的代码粘贴到一个模块中(创建一个新模块 - 右键单击​​右侧的模块并插入)然后粘贴下面的代码.

您需要调用的程序是 sort_with-hyphens .

您将需要创建一个控件或创建一个键盘快捷键来触发它。对于任何一个，您都需要通过文件>选项启用开发人员功能区选项卡。对于控件，请执行开发人员>控件>按钮并右键单击以分配宏。对于键盘快捷键 developer>Macros，从宏列表中选择 VBA 过程名称并选择选项。

Sub sort_with_hyphens()
On Error GoTo sort_with_hyphens_err
   Dim vRange As Range
   Dim vCell As Variant
   Dim vStrArray(), vNumArray()
   Dim i As Long, vStart As Long, vEnd As Long
   Dim vStep As String: vStep = "Initialising values"

   ' prompt user to specify range
   Set vRange = Application.InputBox("Select a range to be sorted", _
                                     "Obtain Range Object", _
                                     Type:=8)
   vStrArray = vRange.Value
   vStart = LBound(vStrArray)
   vEnd = UBound(vStrArray)
   ReDim vNumArray(vStart To vEnd)
   vStep = "Populating Numeric Array"

   ' loop through array copying strings with hyphen to decimal equivalent
   For i = vStart To vEnd
       vNumArray(i) = Val(Replace(vStrArray(i, 1), "-", "."))
       Debug.Print i, vNumArray(i)
   Next i

   ' sort numeric array
   vStep = "Sorting Numeric Array"
   SortViaWorksheet vNumArray

   ' write out sorted values
   vStep = "Writing out Sorted Values"
   For i = vStart To vEnd
       ' convert back to string and switch periods back to hyphens
       vRange.Cells(i, 1).Value = Replace(CStr(vNumArray(i)), ".", "-")
   Next

sort_with_hyphens_exit:
    Exit Sub

sort_with_hyphens_err:
    If vStep = "Writing out Sorted Values" Then
        MsgBox ("An error has occurred, the original values will " & _
                "be restored. Error in Step: " & vStep & vbCrLf & _
                "Error Details:" & vbCrLf & err.Number & " - " & _
                err.Description)
        For i = vStart To vEnd
            ' replace with original value incase of error
            vRange.Cells(i, 1).Value = vStrArray(i)
        Next
    Else
        MsgBox ("An error has occurred in Step: " & vStep & vbCrLf & _
                "Aborting sort procedure." & vbCrLf & _
                "Error Details:" & vbCrLf & err.Number & " - " & _
                err.Description)
   End If
End Sub

Sub SortViaWorksheet(pArray)
    Dim WS As Worksheet ' temporary worksheet
    Dim R As Range
    Dim N As Long

    Application.ScreenUpdating = False

    ' create a new sheet
    Set WS = ThisWorkbook.Worksheets.Add

    ' put the array values on the worksheet
    Set R = WS.Range("A1").Resize(UBound(pArray) - LBound(pArray) + 1, 1)
    R = Application.Transpose(pArray)

    ' sort the range
    R.Sort key1:=R, order1:=xlAscending, MatchCase:=False

    ' load the worksheet values back into the array
    For N = 1 To R.Rows.Count
        pArray(N) = R(N, 1)
    Next N

    ' delete the temporary sheet
    Application.DisplayAlerts = False
    WS.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    ' test/debug/confirmation
    Debug.Print vbCrLf & "Sorted Array:" & vbCrLf & "------------"
    For N = LBound(pArray) To UBound(pArray)
        Debug.Print N, pArray(N)
    Next N
End Sub

如果您有任何问题，请告诉我。