如何对Excel中的混合数字和文本，多级数字进行排序

该教程解释了在Excel中订购字母数字字符串的常见问题，并提供了将数字和文本作为数字的数字排序的工作解决方案。

在Excel中分别对文本和数字进行排序与​​ABC或123一样容易:)，但是重新将产品ID，SKU和其他具有字母和数字的值重新排序可能会导致问题。 Excel的字母顺序无法区分字符串的数字部分以分别处理它。如果您想更多地控制字母数字刺的方式，请查看以下示例。

• 将数字排序为文本
• 排序混合数字和文字
• 通过文本和数字对字符串进行排序
• 对多级 /层次结构进行排序
• 对带有文本前缀的多级数字进行排序

将数字排序为excel中的文本

在Excel中从最小到最大的数字排序时，而不是这样做：

1、2、3、11、12、13、110、120、130

有时您可能会得到这样的东西：

1、11、110、12、120、13、130、2、3

当数字实际上是文本并且它们以文本为单位时，就会发生这种情况 - 就像根据字母安排的单词，“文本数”是根据数字而不是其值对其进行排序的。

如果目标列包含格式为文本的数字，则数字正常排序所需的只是将文本转换为数字。结果是在下面的屏幕截图中列为C列。

相反，如果您想将数字排序为文本，请先将数字转换为文本，然后单击“主”选项卡>编辑组> “ sort＆filter”>“＆filter” > “分类a -z” 。

结果将就像在上一个屏幕截图中的A列中一样。

如何在Excel中对混合数字和文字进行排序

借助内置的Excel排序功能，字母数字字符串（组合文本和数字）始终被分类为文本，即按字母数字，数字数字。要对包含字母前缀或后缀数字的数字列进行排序，请执行以下步骤。这就是我们试图实现的结果。

为了防止Excel将字母字符串中的数字作为文本处理，我们将在辅助列中提取这些数字，然后按该列进行排序。这将使您保持原始数据不变，但会按照自己喜欢的方式重新排列。

1. 在原始值旁边的列中，输入一个从字符串中提取数字的公式。

   在此示例中，我们可以简单地在连字符之后提取所有字符（“ - ”）。在Excel 365中，可以使用TextAfter函数完成此操作。与任何其他文本功能一样，它的输出始终是文本，无论您是检索字母还是数字，因此我们将结果乘以1将文本字符串转换为数字。

   =TEXTAFTER(A2, "-")*1

   在较旧的Excel版本中，可以使用三种不同函数的组合来实现相同的结果，这些函数在特定字符之后提取文本。此外，执行 *1操作以将文本输出转换为数字：

   =RIGHT(A2, LEN(A2) - SEARCH("-", A2)) *1

   

   提示。要快速从单元格中的任何位置提取数字，您可以使用我们的Excel终极套件中包含的提取工具。

   

2. 按提取的数字对原始数据进行排序。选择的数字，转到“主页”选项卡> sort＆filter > “排序最小到最大” 。在弹出的对话框中，选择“展开选择”选项，然后单击“排序” 。有关更多详细信息，请查看如何对行进行排序和保持行在一起。

   

   现在，包含字母和数字的字符串被分类为数字，您可以在必要时删除或隐藏助手列。

   

一次按文本和数字对字符串进行排序

如果您的源字符串具有几个不同的元素，则可以在单独的列中提取每个元素，然后按多个列进行排序。结果，字符串将像下面的屏幕截图中的D一样排列：

由于我们数据集中的所有字符串都具有相同的模式，因此将文本和数字分为不同列的最简单方法是使用正则表达式。这可以在名为RegexPextract的自定义功能的帮助下完成。首先，如本教程所述，您将其代码添加到Excel中。之后，使用以下公式。

要从单元A2提取文本的第一次出现，B2中的公式为：

=RegExpExtract(A2, "[^\d] ", 1)

要提取第一个数字，C2中的公式为：

=RegExpExtract(A2, "\d ", 1)*1

要提取第二个数字，D2中的公式为：

=RegExpExtract(A2, "\d ", 2)*1

在2 ^nd和3 ^rd公式中，我们将RegexPexTract输出乘以1，以将提取的子字符串变成一个数字。

我们最终套件的用户可以利用Regex工具来达到相同的结果。他们只需要执行一个更快速的操作即可将提取的数字字符串（C列C和D）转换为数字：

一旦文本和数字被拆分，请通过多列对数据集进行排序：

1. 选择所有列（在我们的情况下为A2：D16）。
2. 在“数据”选项卡上，在“排序和过滤器组”中，单击“排序”按钮。
3. 根据需要添加尽可能多的排序级别，然后选择所需的排序顺序。
4. 完成后，单击确定。

在我们的情况下，选定的范围首先按A到Z进行文本排序，然后按数字为1 ，然后从最小到最大的数字排名第2 。

结果正是我们要寻找的：

提示。要以不同的方式重新安排字符串，请在排序对话框中更改级别的顺序。

如何在Excel中对多级 /层次结构进行排序

在Microsoft Excel中，多级数字，例如1.1、1.1.1、1.1.2是字符串，它们排序为文本，而不是数字：

要将多级字符串排序为数字，您可以使用具有以下语法的自定义用户定义函数：

层次数（range，del，max_level）

在哪里：

• 范围是要排序的范围。
• DEL是用于分离级别的定界符，通常是一个点（。）。
• max_level是层次结构中的最大级别数量。

这是函数的代码：

定制函数以排序层次结构编号

函数eRARCHYNUMBER（范围为range，del as string ，max_level as integer ）作为double as as as as as as as aS variant Part = split（range.value，del，del） for index = 0 to to ubound （part）hierarchynumber = hierarchynumber = hierarchynumber = hierarchynumber part （ index）

该函数的作用是生成允许对多级数字字符串进行排序的数字。更确切地说，它将层次结构字符串分为单个数字（部分），根据以下算法处理每个部分，并将结果加起来：

part(Index) * (10 ^ ((level - Index) * 2))

例如，对于1.1，该功能会产生此结果：

1*（10^（（2-0）*2））1*（10^（（2-1）*2））= 10100

对于1.2，是：

1*（10^（（2-0）*2））2*（10^（（2-1）*2））= 10200

如何使用自定义功能对层次结构编号进行排序：

1. 将层次函数的代码插入工作簿的标准代码模块中，并将其保存为宏观启用的工作簿（.XLSM）。详细说明在这里。
2. 在第一个层次字符串旁边的空白单元格中，输入层次结构公式，然后按Enter键。
3. 根据需要将公式拖到尽可能多的行上。
4. 根据公式列对数据集进行排序。

假设您的多级数字字符串在A列A中最多3级。B2的公式为：

=HierarchyNumber(A2, ".", 3)

复制公式后，您将获得此结果：

之后，您可以按公式列从最小到最大的公式列对整个数据集（A2：B19）进行排序：

并将多级数字字符串排序为数字：

对带有文本前缀的多级数字进行排序

此示例讨论了一个略有不同的情况，其中多级数字带有一些文本，例如“级别1.1.1”。乍一看，任务听起来更复杂，但实际上解决方案更简单:)

首先，将多级数字子字符串提取到单独的列中。为此，您可以使用我们的Ultimate Suite包含的提取工具：

或者，您可以编写一个公式以在空间之后拉动所有字符：

=RIGHT(A2, LEN(A2) - SEARCH(" ", A2))

然后，进行自定义排序。在Excel排序对话框中，添加两个级别 - 首先由提取的层次结构编号，然后由原始字符串 - 然后单击“确定：

以下警告将弹出，您选择“将存储为单独存储为文本的数字和数字”。

结果，包含多级数字的字符串按数字排序：

这就是如何对Excel中的数字字符串和多级数字进行排序。感谢您阅读并在下周在我们的博客上与您见面！

可用下载

在Excel中对混合数字和文本进行排序 - 示例（.xlsm File）Ultimate Suite完全功能的试用版（.EXE文件）

以上是如何对Excel中的混合数字和文本，多级数字进行排序的详细内容。更多信息请关注PHP中文网其他相关文章！