您的位置: 首页 > EXCEL技巧 > Excel函数 >

用数组公式和自定义函数对单元格内的数字排序

时间:2013-12-14 整理:docExcel.net

假如Excel工作表的A1单元格中包含某个数字,如“987650056789”,要将各位数字按从小到大顺序排列,即得到“005566778899”,通常可以用数组公式或自定义函数:

如果单元格内的数字不超过15为,可使用下面的数组公式将数字按从小到大的顺序排列,在B1单元格中输入:

=TEXT(SUM(SMALL(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW(INDIRECT("1:"&LEN(A1))))*10^(LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))))),REPT("0",LEN(A1)))

注意需按Ctrl+Shift+Enter结束输入。如果要将数字按从大到小的顺序排列,可将“SMALL”改为“LARGE”函数。受数字精度的限制,如果单元格内的数字超过15位,可能得不到正确的结果。而使用自定义函数则可用于较长的文本形式的数字。

按Alt+F11,打开VBA编辑器,单击菜单“插入→模块”,在代码窗口粘贴下列代码:

Function SortNumsInCell(num As String, Optional order As Boolean) As String
Dim Str As String
For i = 0 To 9
  For j = 1 To UBound(Split(num, i))
Str = IIf(order, i & Str, Str & i)
  Next
Next
SortNumsInCell = Str
End Function

上述代码定义了一个自定义函数,它有两个参数,第一个为单元格内的数字或直接输入的数字,第二个参数为“0”或省略时指定按从小到大的顺序排列,为“1”时则相反。关闭VBA编辑器,返回Excel界面,在B1中输入公式:

=sortnumsincell(A1)

即可得到按从小到大排列的数字。如果输入:

=sortnumsincell(A1,1)

则返回从大到小顺序排列的数字。

用数组公式和自定义函数对单元格内的数字排序 问题:用数组公式和自定义函数对单元格内的数字排序
回答:...6778899”,通常可以用数组公式或自定义函数: 如果单元格内的数字不超过15为,可使用下面的数组公式将数字按从小到大的顺序排列,在B1单元格中输入: =TEXT(SUM(SMALL(--MID(A1,ROW(INDIRECT( 1: &LEN(A1))),1),ROW(INDIRECT( 1: &LEN(A1))))*...
另类方保护Excel工作表结构 问题:另类方保护Excel工作表结构
回答:...某个区域中输入数组公式后,如果要更改该区域某个单元格内容或删除该区域中的某个单元格,Excel将给出“不能更改数组的某一部分”的提示对话框。如图: 利用这一点,我们可以保护工作表的结构,而不让使用者更改工...
分离Excel单元格内的数值和单位 问题:分离Excel单元格内的数值和单位
回答:通常在设计Excel表格时会将数值及其单位放在不同的单元格内,以便数值参与运算。但有时一些用户会将单位的数值放在同一单元格内,如输入为“30克”、“200.25平方厘米”等,这就无法对其进行计算了。要分离这样的数值和...
用数组公式从一列中提取非空单元格值 问题:用数组公式从一列中提取非空单元格值
回答:...中包含一些空单元格,要去掉这些空单元格,将非空单元格内容放到另一列中,最快的方法是通过筛选隐藏空单元格,再复制到其他列中。如果不使用筛选,还可以用下面的数组公式: 假如数据在A2:A20区域中,将提取后的数...
用自定义函数提取单元格内字符串中的数字 问题:用自定义函数提取单元格内字符串中的数字
回答:如果Excel单元格中包含一个混合文本和数字的字符串,要提取其中的数字,通常可以用下面的公式,例如字符串“隆平高科000998”在A1单元格中,在B1中输入数组公式: =MID(A1,MATCH(1,--ISNUMBER(--MID(A1,ROW(INDIRECT( 1: &LEN(A1))),1)),0),COUN...
Excel中类似分列功能的公式 问题:Excel中类似分列功能的公式
回答:在Excel中会经常用到分列功能,可以将同一单元格内的数据分割到多列中,非常方便。实际上,用公式也能实现类似分列的功能,如下面的一些公式。 一、固定宽度分列 在分列时的“文本分列向导”对话框中有两个选项,分别...
相关推荐: