我在 Excel 中有一列“数字”,我想按如下方式排序:
1.1
1.2
1.2.1
1.2.6
1.2.9
1.2.10
1.2.11
1.3
但是,无论单元格的类型是数字还是文本,Excel 都会按如下方式对其进行排序:
1.1
1.2
1.2.1
1.2.10
1.2.11
1.2.6
1.2.9
1.3
这也可以称为“自然排序”——在 PHP 这样的编程语言中,这将通过以下方式实现:natsort
功能。但我似乎无法在 Excel 中找到以这种方式排序的方法。
为了进一步提供一些背景信息:这些数字代表章节和小节 - 不是小数,而是列表项:
1. Section
1. Sub-section
2. Sub-section
1. Item
6. Item
9. Item
10. Item
11. Item
3. Sub-section
答案1
您可以制作一个表格来拆分值:
对于每一列,使用标题(第 1 行)和公式(第 2 行及以上):
A1: text A2: (your section numbers)
B1: dot1 B2: =FIND(".",A2,1)
C1: dot2 C2: =IFERROR(FIND(".",A2,B2+1),LEN(A2)+1)
D1: num1 D2: =VALUE(MID(A2,1,B2-1))
E1: num2 E2: =IFERROR(VALUE(MID(A2,B2+1,C2-B2-1)),0)
F1: num3 F2: =IFERROR(VALUE(MID(A2,C2+1,LEN(A2)-C2)),0)
显示为:
A B C D E F
1 text dot1 dot2 num1 num2 num3
2 1.1 2 4 1 1 0
3 1.1.3 2 4 1 1 3
4 2.10.7 2 5 2 10 7
然后您可以按列 num1、num2 和 num3 进行自定义排序。
答案2
通过插入用户定义函数,我获得了良好的结果。UDF 返回一个值,该值表示以百万为单位的部分数量、以千为单位的子部分数量、以个为单位的项目数量、以千为单位的子项目(如果有)、以百万为单位的子子项目等等的总和。例如,
1.2.3
将会1,002,003
返回
1.51.5.5
返回1,051,005.005
- 然后可以使用这些数字进行排序。
我发现这比使用工作表内的字符串函数或对多列进行排序更不麻烦。
UDF如下:
Function LList(stInVal As String) As Double
Dim iPower As Integer
Dim vSplit As Variant
Dim i As Long
iPower = 6
vSplit = Split(stInVal, ".", -1)
For i = 0 To UBound(vSplit)
LList = LList + CInt(vSplit(i)) * 10 ^ (iPower - 3 * i)
Next i
End Function
答案3
Doug 详细解释了这一点。希望您觉得有道理!
答案4
刚刚遇到同样的问题...
如果您想要一个快速的解决方案(并且不介意使用“。”那么只需找到所有“。”并替换为“_” - 这将允许 Excel 以层次结构方式排序。
排序几乎可以正常工作..现在您需要做的就是在数据中添加一个尾随的“下划线”...因此带有一个字符“1”的数据将变为“1_”(数据中的字符数)