Excel 中的字母数字排序

Question 1

用户@fixer1234 是对的，你可能想使用字符串函数。这是实现此目的的一种方法。

步骤1

[更新]

在“数字”列中，突出显示范围，然后在连字符处拆分文本：do...

Data> Text to Columns> Delimited> Next> Other: ->Finish

注意你需要一个连字符 (-)Other:文本框中。在执行此操作之前，请确保相邻列 (右侧) 为空，以免覆盖重要数据。

您还可以使用此函数从 A 列中提取数字：

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

第2步

现在，正如你从上面的截图中看到的那样，我们下一步要做的是在每个数字的开头添加零，位数少于最大数字。这将允许您按照您想要的方式对这些数字进行排序。

但首先，我们需要做一些事实调查。如果你能很容易地知道最大的数字是多少，那么只需计算出这个最大数字中的数字——这将是你想在下一个函数中使用的零的数量。还有另一种方法可以确定最长的数字，而不必手动计算最大数字的数字。

[更新]您可以使用以下函数（尽管到目前为止我们没有很清楚为什么）确定最长的数字：

=MAX(INDEX(LEN(C2:C14),,1))

或者，您可以简单地在单元格中输入以下公式（您可以在上图中看到以橙色突出显示的单元格），但不要简单地按下ENTER按键来设置单元格，而是按下热键CTRL-SHIFT-ENTER。这将改变函数的性质，将其变成数组公式，而不必玩弄诸如的功能INDEX()。

=MAX(LEN(C2:C14))

（确保您使用的特定电子表格的范围是准确的。）

点击后CTRL-SHIFT-ENTER，单元格的内容将变为这样，但手动输入此内容不会产生任何效果：

{=MAX(LEN(C2:C14))}

你想怎么做都可以。只要确定列表中最大的数字由多少位组成：“1”当然有 1 位数字，“10”有 2 位数字，“100”有 3 位数字，等等。

步骤3

最后，在“扩展”列中，此函数会将“数字”列中的数字转换为文本，其中包含您在步骤 2 中确定应使用的前导零的数量。

=TEXT(C2,"000")

确保在零两边加上引号。

如果最大的数字有 8 位数字，那么您的函数将如下所示：

=TEXT(C2,"00000000")

Answer

用户@fixer1234 是对的，你可能想使用字符串函数。这是实现此目的的一种方法。

步骤1

[更新]

在“数字”列中，突出显示范围，然后在连字符处拆分文本：do...

Data> Text to Columns> Delimited> Next> Other: ->Finish

注意你需要一个连字符 (-)Other:文本框中。在执行此操作之前，请确保相邻列 (右侧) 为空，以免覆盖重要数据。

您还可以使用此函数从 A 列中提取数字：

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

第2步

现在，正如你从上面的截图中看到的那样，我们下一步要做的是在每个数字的开头添加零，位数少于最大数字。这将允许您按照您想要的方式对这些数字进行排序。

但首先，我们需要做一些事实调查。如果你能很容易地知道最大的数字是多少，那么只需计算出这个最大数字中的数字——这将是你想在下一个函数中使用的零的数量。还有另一种方法可以确定最长的数字，而不必手动计算最大数字的数字。

[更新]您可以使用以下函数（尽管到目前为止我们没有很清楚为什么）确定最长的数字：

=MAX(INDEX(LEN(C2:C14),,1))

或者，您可以简单地在单元格中输入以下公式（您可以在上图中看到以橙色突出显示的单元格），但不要简单地按下ENTER按键来设置单元格，而是按下热键CTRL-SHIFT-ENTER。这将改变函数的性质，将其变成数组公式，而不必玩弄诸如的功能INDEX()。

=MAX(LEN(C2:C14))

（确保您使用的特定电子表格的范围是准确的。）

点击后CTRL-SHIFT-ENTER，单元格的内容将变为这样，但手动输入此内容不会产生任何效果：

{=MAX(LEN(C2:C14))}

你想怎么做都可以。只要确定列表中最大的数字由多少位组成：“1”当然有 1 位数字，“10”有 2 位数字，“100”有 3 位数字，等等。

步骤3

最后，在“扩展”列中，此函数会将“数字”列中的数字转换为文本，其中包含您在步骤 2 中确定应使用的前导零的数量。

=TEXT(C2,"000")

确保在零两边加上引号。

如果最大的数字有 8 位数字，那么您的函数将如下所示：

=TEXT(C2,"00000000")

Question 2

一个简单的解决方案是使用字符串函数将数值作为数字放入新列中。然后对该列进行排序。您没有指定值可能如何变化或如何排列，但有一个示例：

假设“a-”前缀永远不会改变，条目位于 A 列，从第 2 行开始，B 列可用。在 B2 中，您可以输入类似以下内容：=value(mid(a2,3,len(a2)-2))，然后根据需要复制公式。要排序，请突出显示两列并在 B 上排序。

如果可以有其他前缀，比如 b-、c- 等，同样将文本部分拆分到另一列。然后以文本列作为第一排序列，以数字列作为第二排序列。

您突出显示的所有内容都将根据排序列进行排序。

要解释字符串函数，请先从 mid 函数开始，该函数从一串字符中提取一串字符。mid(a2,3,len(a2)-2) 查看 a2 中的文本，从第三个字符（假设所有前缀都是“a-”的第一个数字）开始，然后取比原始文本中的数字少两个的字符数（原始文本的长度减去“a-”个字符）。然后，value 函数将该结果转换为数字，而不是数字字符串。

Answer

一个简单的解决方案是使用字符串函数将数值作为数字放入新列中。然后对该列进行排序。您没有指定值可能如何变化或如何排列，但有一个示例：

假设“a-”前缀永远不会改变，条目位于 A 列，从第 2 行开始，B 列可用。在 B2 中，您可以输入类似以下内容：=value(mid(a2,3,len(a2)-2))，然后根据需要复制公式。要排序，请突出显示两列并在 B 上排序。

如果可以有其他前缀，比如 b-、c- 等，同样将文本部分拆分到另一列。然后以文本列作为第一排序列，以数字列作为第二排序列。

您突出显示的所有内容都将根据排序列进行排序。

要解释字符串函数，请先从 mid 函数开始，该函数从一串字符中提取一串字符。mid(a2,3,len(a2)-2) 查看 a2 中的文本，从第三个字符（假设所有前缀都是“a-”的第一个数字）开始，然后取比原始文本中的数字少两个的字符数（原始文本的长度减去“a-”个字符）。然后，value 函数将该结果转换为数字，而不是数字字符串。

Question 3

我有以下数据：

[Student Name]  [Regn. No (AlphaNum)]    [Test Score]

Lisa            DD38                      90

Aletta          BC36                      85

Ava             AB40                      95

Sunny           BB34                      91

Sofia           CD36                      89

Johnny          CM07                      80

初始数据按照字母数字字符串排序

我希望学生和成绩按照他们的注册号排列，注册号是字母数字字符串。最终输出应该是这样的：

[Student Name]  [Regn. No (AlphaNum)]    [Test Score]

Ava             AB40                      95

Sunny           BB34                      91

Aletta          BC36                      85

Sofia           CD36                      89

Johnny          CM07                      80

Lisa            DD38                      90

最终输出应如下所示

如何：

将字母数字字符串转换为纯数字的步骤

尽管上面的快照是不言而喻的，但步骤如下：

使用公式提取列中的字母部分=LEFT(B4, 2)，其中“2”表示字符串中的字母数，我的字符串从开始B4。
使用以下方式为该字母部分分配一个数字=COLUMN(INDIRECT(E4&1))
使用公式提取注册号的数字部分=RIGHT(B4,2)。这里“2”是字符串中的数字数量。
将 2 和 3 中的两部分连接起来
使用以下方法将这些转换为“纯”数字=VALUE(H4)
将最后一列复制到原始表并使用选项粘贴为“值”
现在，根据最后一列排序并选择“扩大选择”
完毕！！！

重要提示：专业提示 -> 使用以下方法将步骤 1 至 5 合并：=VALUE(CONCATENATE(COLUMN(INDIRECT(LEFT(B4,2)&1)),RIGHT(B4,2)))

Answer