对混合字母和数字的数据排序.doc

对混合字母和数字的数据排序

作者：Excel123 文章来源： 点击数：1370 更新时间：2010-7-25 6:34:31

    Excel对混合字母和数字的数据进行排序时，有时可能不会得到我们所需的结果，因为Excel是对单元格值从左到右逐字符地进行比较并排序的。例如下图左侧为排序前的数据，右侧图是单击工具栏中的“A→Z”按钮按升序排序后的结果，可以看到“B3”排到了“B100”的后面。

    如果要对这种类型的数据按照先字母后数值的顺序排序，如上例中将“B3”排到“B100”的前面，该怎样操作呢？
    方法一：分列法
    对于类似上例字母和数字排列有规律的数据，可以用分列的方法将字母和数字分别放到相邻的列中再进行排序。例如下图中的数据中用“-”分隔的情况。

    下图是直接进行排序的结果，不符合要求：

    以Excel 2003为例介绍如下：
    1.假如数据在A1:A17区域中，先复制该列数据到B1:B17中。
    2.选择B1:B17区域，单击菜单“数据→分列”，弹出“文本分列向导”对话框，默认已选择“分隔符号”，单击“下一步”。
    3.在对话框中“其他”右侧的文本框中输入分隔符号“-”，单击“完成”。

    4.B列中的数据被拆分到B、C、D列。选择A1:D17区域，单击菜单“数据→排序”，在“排序”对话框中将“主要关键字”、“次要关键字”和“第三关键字”分别设置为“列B”、“列C”、“列D”。

    单击“确定”完成排序。最后删除B、C、D列。

    方法二：辅助列+公式法
    对于仅由连续字母和连续数字组成的混合数据，由于每个数据中包含的字母数量不等，也没有分隔符号，不能用分列的方法来处理。这时可以利用公式将其中的字母和数字分别提取到辅助列中，然后再进行排序。如下图中的数据。

    上面右图显示了直接排序的结果，其中“AC71”排在“AC317”后，“B100”排在“B12”前，不符合要求。
    假如数据在A1:A10区域中，在C1单元格中输入数组公式提取数值：
    =LOOKUP(9.9999999E+307,--MID(A1,MIN(IF(ISNUMBER(FIND(ROW($1:$9),A1)),FIND(ROW($1:$9),A1))),ROW(INDIRECT("1:"&LEN(A1)))))
    或：
=--MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),COUNT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))
公式输入完毕后按Ctrl+Shift+Enter结束，然后拖动填充柄将公式填充到C2:C10区域。
    在B1单元格中输入公式提取字母：
    =SUBSTITUTE(A1,C1,"")
    并拖动填充柄填充公式到B1:B10区域。

    最后，参照方法一中的排序方法对A1:C10区域以B列和C列为关键字进行排序，排序完成后删除B、C两列，即可得到所需的排序结果。