使用 Excel 正确排序语义版本列表

Question

我们使用 Excel 从我们的错误跟踪器导出摘要/票证列表。我想要排序的列是“版本”列。我们使用语义版本控制（https://semver.org) 因此排序后的列表如下所示：

 V1
 V10 
 V10.1 
 V10.2 
 V10.3 
 V10.3.1 
 V10.3.2 
 V10.4 
 V10.5 
 V10.6 
 V10.6.1 
 V10.6.2 
 V10.6.3 
 V10.6.4 
 V4 
 V5 
 V6 
 V7 
 V7.1 
 V7.2 
 V7.3 
 V8 
 V8.1 
 V8.2 
 V9 
 V9.1 
 V9.2 
 V9.3 

我曾考虑过创建新列并按 V 和“.”分隔符进行拆分，但我的方法不够完善。在新的 Excel 表（N 列中的数据）中，我一直尝试使用 =LEFT(N9;FIND("V";N9))“V”来 =RIGHT(N9;LEN(N9)-FIND(".";N9))获取第二个数字。我很难得到中间的数字，而且我怀疑这个解决方案是否适用于具有主要、次要和补丁号的版本。我如何按版本对列表进行排序？它应该看起来像

V1
V4
V5
V6
V7
V7.1 
V7.2
V7.3
V8
V8.1
V8.2
V9
V9.1
V9.2
V9.3
V10
V10.1
V10.2
V10.3
V10.3.1
V10.3.2 
V10.4 
V10.5 
V10.6 
V10.6.1 
V10.6.2 
V10.6.3 
V10.6.4

Answer 1

公式

• 公式利用SORTBY函数使用TEXTAFTER和TEXTBEFORE识别每个级别（深度）的字符串。
• 最大深度在公式中设定——参见示例公式（深度 3）和公式（深度 4）——但过度配置并没有真正的惩罚。
• 句号.用于划分级别，并添加额外的句号以避免公式错误。
• 排序之前，字符串会被强制转换为数字，因此句点处会添加零值，因为 NULL 值不会被强制转换为数字。 V1变成V1.0.0.当检查深度为 3 时。同样V1.2.3会成为V1.2.3.0.0.。

公式（深度 3）

有效的：V1; V1.2;V1.2.3

=LAMBDA(y, FILTER(y, y<>""))(
    LAMBDA(x, 
       SORTBY(x,
         TEXTAFTER(TEXTBEFORE(x&".", "."),"V")*1,1,
         TEXTAFTER(TEXTBEFORE(x&".0.", ".",2),".")*1,1,
         TEXTAFTER(TEXTBEFORE(x&".0.0.", ".",3),".",2)*1,1
         )
    )(A1:A10000)
 )

要增加最大深度，只需按照相同模式添加额外的行。

公式（深度 4）

有效的：V1; V1.2; V1.2.3;v1.2.3.4

=LAMBDA(y, FILTER(y, y<>""))(
    LAMBDA(x,
       SORTBY(x,
         TEXTAFTER(TEXTBEFORE(x&".", "."),"V")*1,1,
         TEXTAFTER(TEXTBEFORE(x&".0.", ".",2),".")*1,1,
         TEXTAFTER(TEXTBEFORE(x&".0.0.", ".",3),".",2)*1,1,
         TEXTAFTER(TEXTBEFORE(x&".0.0.0.", ".",4),".",3)*1,1
         )
    )(A1:A10000)
 )

原来的	已排序
V10.4	V1
版本9.01	V2
V9.3	V4
V10.2	V5
V10.5	V6
V8.2	V7.2
V9.2	V7.3
V10.65.1	版本 7.10
V8	V8
V10.6.4	V8.2
V10.1	版本 8.11
V7.3	版本9.01
V5	V9.1
V1	V9.2
版本 8.11	V9.3
V10	V10
V4	V10.1
V6	V10.2
V10.3	V10.3
版本 7.10	V10.3.1
V7.2	V10.4
V10.3.1	V10.5
V9.1	V10.6.4
V2	V10.65.1

Answer 2

Text to columns这可以通过在 Excel 中设置分隔符来实现，.并由此生成新列。按照以下步骤操作即可获得所需的结果。

首先，取第一列的参考数据：

然后选择数据并转到Data>>Text to Columns，选择Delimited并按下一步。然后执行以下操作：

此处的分隔符是.

然后单击Next并按下进入。

结果：

现在我们必须制作两个辅助列，以便将字母保存V在一列中，将后续数字保存在另一列中。

选择B 列然后右键单击以插入一个空列。重复此过程以添加另一列，您将获得如下内容：

选择B1单元格并输入=LEFT(A2, 1)。向下拖动以用字母填充该列中的其他单元格五。

现在，选择C1单元格并输入=RIGHT(A1, (LEN(A1)-SEARCH("V", A1, 1)))+0并向下拖动以填充其他单元格。您将获得如下表格：

现在选择C 列， 去家选项卡，单击Sort and Filter，选择Smallest to Largest，然后展开选择，并单击种类：

您将获得如下表格：

现在，最后，选择F1单元格并输入此公式：=IF(ISBLANK(D1), CONCATENATE(B1, C1), IF(ISBLANK(E1), CONCATENATE(B1, C1, ".", D1), CONCATENATE(B1, C1, ".", D1, ".", E1)))；向下拖动以填充其他单元格，您将获得所需的结果：

就这样吧。希望这对你有帮助。我找不到更不无聊的方法来做这件事。如果这对你有用，请告诉我 :) 晚安！