☰

如何对包含字母和数字的值进行排序？

tag-icon • tag-icon microsoft-excel microsoft-excel-2016 sorting
如何对包含字母和数字的值进行排序？

我有一个包含如下值的列

A1
A-3 // reads: A (minus 3)
J24
J-2
A24
...

现在我想先按首字母排序，然后按后面的数字排序。但 Excel 会将减号读为破折号，并按破折号排序

A1
A2
A3
..
A-1

有特殊情况需要使用尾随字母

F2B
F-2B

我希望我的 AZ 排序看起来像什么

A-3
A-2
A-1
A0
A1
..
Z-3
Z-2
Z-1
Z0
Z1
Z2
..

有没有不用 VBA 的办法来解决这个问题？

答案1

示例操作

你可以将你的价值x分为三个部分：

  • 前缀部分=LEFT(x)
  • 其他部分=RIGHT(x, LEN(x)-1)，如下所示y，包含
    • 数字部分=IFERROR(VALUE(y), VALUE(LEFT(y, LEN(y)-1)))和
    • 后缀部分=RIGHT(y, LEN(y)-LEN(number_part)))

然后将它们排序在一起。

答案2

基于以下算法的 Excel 实现示例

您可以将字符串分解为单个组件并进行自定义排序，但如果没有列格式规范，则只能提供猜测。

首先，像下面这样设置您的电子表格（不需要列名，但已添加以与排序对话框对齐）。请注意，此示例假设您的字符串最多可以包含 6 个字符。

电子表格布局以对列进行排序

复制公式如下：

  1. =MID(A2, 1, 1)进入 B2 列。
  2. =IF(MID(A2, 2, 1) = "-", IF(MID(A2, 3, 1) = "", "", "-" & MID(A2, 3, 1)), MID(A2, 2, 1))进入 C2 列。
  3. =IF(MID(A2, 2, 1) = "-", IF(MID(A2, 4, 1) = "", "", "-" & MID(A2, 4, 1)), MID(A2, 3, 1))进入 D2 列。
  4. =IF(MID(A2, 2, 1) = "-", IF(MID(A2, 5, 1) = "", "", "-" & MID(A2, 5, 1)), MID(A2, 4, 1))进入 E2 列。
  5. =IF(MID(A2, 2, 1) = "-", IF(MID(A2, 6, 1) = "", "", "-" & MID(A2, 6, 1)), MID(A2, 5, 1))进入 F2 列。
  6. 选择 B2 至 F2 列并将公式复制到最后一行。您的结果应类似于上面的屏幕截图。
  7. 选择 A1 至 F10 列，右键单击，选择种类， 进而自定义排序.... 将会出现此弹出窗口。

排序对话框

  1. 我使用自定义列表来指定我的排序顺序，如下所示。将其复制到您的自定义列表中，您可以从命令下拉列表。现在按照图中所示对列进行排序，然后选择好的进行排序。您可能会收到“排序警告”提示，我选择了“分别对数字和以文本形式存储的数字进行排序”（不确定这是否重要）。

    -9,-8,-7,-6,-5,-4,-3,-2,-1,-0,-Z,-Y,-X,-W,-V,-U,-T,-S,-R,-Q,-P,-O,-N,-M,-L,-K,-J,-I,-H,-G,-F,-E,-D,-C,-B,-A,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,0,1,2,3,4,5,6,7,8,9

  2. 以下是我的最终结果。一些观察：

    • 负值位于非负值之前。
    • 对于负值，较大的数字位于较小的数字之前（例如 J-24 和 J-2）
    • 对于非负值，较大的数字位于较小的数字之后（例如 A1 和 A24）。
    • 请注意，下面算法中的排序列表自定义可以根据需要按任何顺序重新排序值。

最终排序结果

Excel 函数说明

为了分离第一个字符，我使用了公式

=MID(A2, 1, 1)

为了提取后续字符，我使用了以下公式：

=IF(MID(A2, 2, 1) = "-", IF(MID(A2, N, 1) = "", "", "-" & MID(A2, N, 1)), MID(A2, N-1, 1))`

Where character `N` applies to characters 3, 4, 5, and 6.  The `Else` part of the first `IF` function will take care of character 2 for a non-minus sort.

在伪代码中，该公式的作用如下：

If character 2 is a minus then prep our current character N for sorting
  If character N is blank then
    Return a blank because we have no character to sort by
  Else
    Return a "-" prepended to character N
    (e.g. `-5`, `-B`, which is understood by custom sort list)
  End If
Else character 2 is not a minus
  Return character N-1 for sorting
  (-1 because minus doesn't exist for non-minus values)
End If

带有假设的算法

我结合使用了 MID 和 IF 函数将字符串拆分成多个部分，然后应用自定义排序来获得最终结果。我做了一些改动，做出了以下假设：

  1. 第一列与字符串的其余部分分开处理，并且始终按字母升序排序（例如A, B, Y, Z）。
  2. 对于第 2 列到末尾，带减号的值-将排在没有减号的值之前（例如-24B, -24A, -2B, -2A, -1, 1, 2A, 2B, 24A, 24B，按此顺序）。此外：
    • 对于前面带有减号 的值-，排序顺序为数字降序和字母降序，但数字在字母之前。示例：
      • 降序数字： -24位于 之前-2，因此-24A位于 之前-2A。
      • 降序 alpha： B位于 之前A，B并分别A被视为-B和-A，因此-2B位于 之前-2A。
      • 数字在字母之前： 4在之前B，所以-24B在之前-2B。
    • 对于前面没有减号的值，排序顺序为数字升序和字母升序，但字母在数字之前。示例：
      • 升序数字： 2位于 之前24，因此2A位于 之前24A。
      • 升序 alpha：A位于 之前B，因此2A位于 之前2B。
      • 字母在数字之前： A在 之前4，所以2A在 之前24A。

根据列的格式，规则可能会有很大差异。

自定义排序列表

自定义排序列表中的排序按以下顺序列出。负数数字和字母字符按降序排列，数字在字母之前。正数数字和字母字符按升序排列，字母在数字之前。

-9,-8,-7,-6,-5,-4,-3,-2,-1,-0,-Z,-Y,-X,-W,-V,-U,-T,-S,-R,-Q,-P,-O,-N,-M,-L,-K,-J,-I,-H,-G,-F,-E,-D,-C,-B,-A,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,0,1,2,3,4,5,6,7,8,9

请注意，排序列表可以按任意方式排序，以获得理想的结果。以下是一些示例。

  • 重新排序，-Z to -A就像-A to -Z您想要升序减字母顺序一样（例如，A-2A在 之前A-2B）。
  • 移动到数字-9 to -0之前-Z to -A以获得减去字母（例如J-2F在之前J-24）。
  • 移至之前以获取字母之前的非负数字（例如，0在之前）。9AZF21F2A

此自定义排序列表从左列到右列单独应用于每个字符，以获得最终的排序结果。

答案3

您的问题可以通过几个辅助列来解决：

在此处输入图片描述

怎么运行的：

  • 未分类的数据范围是B2:B13。
  • 公式在C2. 中=Left(B2,1)，填入即可。
  • 公式在D2。=VALUE(RIGHT(B2,LEN(B2)-1))，填下去。
  • 选择要排序的数据。
  • 从主页标签点击种类图标并选择自定义排序。
  • 设置Column，Sort ON然后Order如屏幕截图所示，完成好的。
  • 最后从排序警告对话框选择 Second Option并点击好的完成。

注意：

  • 我已将未排序数据的原始顺序包含在 A 列（红色）中，以便与排序数据进行比较。
  • 数据排序后，您可以隐藏两个辅助列。
  • 根据需要调整公式中的单元格引用。

相关内容