用公式对含有数值、字母、数字与字母混合、空格等的一列数据进行排序（具有挑战性）

lhx120824

A列是原始数据，包括数值、字母、数字与字母混合、空格等，对A列进行排序，要求不含空格，去除0值，排序成C列的结果。通过研究分析，先增加一个辅助列转换成非数值的内容，定义了4个名称。解题过程是通过几个辅助列逐渐完成的，最后再把辅助列定义成名称使用。此方法速度不快，看那位大师有更好的比较简洁的方法能解决。与大家共享。

所定义名称：
bj  =COUNTIF(Sheet1!$B$1:$B$258,"<"&Sheet1!$B$1:$B$258)
ys  =Sheet1!$A$1:$A$258
排序  =INDEX(Sheet1!$B$1:$B$258,MATCH(SMALL(bj,行数),bj,0))
行数  =ROW(INDIRECT(1+COUNTIF(Sheet1!$B:$B,"0(")&":"&COUNTA(Sheet1!$B:$B)))

数字、字母、空格、0值混合排序20100526.rar (7.48 KB, 下载次数: 142)

2010-5-27 10:55 上传

点击文件名下载附件

[ 本帖最后由 lhx120824 于 2010-5-27 11:11 编辑 ]

HadesBoss

直接点排序，按升序排列就可以
0值会排在一起（在前面），可以选中删除单位格
空格会排在最后，不用理会

lilyqiu

1.通常这类型的排序，我会用到数据-数据透视表和数据透视图
2.在A1单元格给A列给个标题
3.在对A列用数据透视表进行计数

lhx120824

原帖由 lilyqiu 于 2010-5-27 11:18 发表
1.通常这类型的排序，我会用到数据-数据透视表和数据透视图
2.在A1单元格给A列给个标题
3.在对A列用数据透视表进行计数

只要求排序，不能进行筛选。

wangg913

最后显示的时候，数字一律显示的是文本
=INDEX(A:A,RIGHT(SMALL(--TEXT((1+COUNTIF(A:A,"<="&TEXT(A1:A258,"0!.00(;;;@")))*(A1:A258>0)*10^4+ROW(1:258),"[<1e4]9999999"),ROW(1:258)),4))&""
数组公式。

lhx120824

原帖由 wangg913 于 2010-5-27 11:47 发表
最后显示的时候，数字一律显示的是文本
=INDEX(A:A,RIGHT(SMALL(--TEXT((1+COUNTIF(A:A,"

很好，你构造数组的功力很深，公式简练，数据类型格式的转化很有技巧，值得我等好好学习。在解决这个问题的过程中，我学了很多东西，最后的结果虽然不是最好的。但自己能敢承担问题。

hkgoodwill

原帖由 wangg913 于 2010-5-27 11:47 发表
最后显示的时候，数字一律显示的是文本
=INDEX(A:A,RIGHT(SMALL(--TEXT((1+COUNTIF(A:A,"

先受小弟一拜，可以解析否，在下實在愚昧。。。

hkgoodwill

原帖由 wangg913 于 2010-5-27 11:47 发表
最后显示的时候，数字一律显示的是文本
=INDEX(A:A,RIGHT(SMALL(--TEXT((1+COUNTIF(A:A,"

我嘗試將你的程式套入LOOKUP(如下)，但不成功，為什麼？？？  應怎麼改？？？

{=lookup(A6,INDEX(A:A,RIGHT(SMALL(--TEXT((1+COUNTIF(A:A,"<="&TEXT(A1:A258,"0!.00(;;;@")))*(A1:A258>0)*10^4+ROW(1:258),"[<1e4]9999999"),ROW(1:258)),4)))}

wangg913

原帖由 hkgoodwill 于 2010-5-29 17:13 发表
我嘗試將你的程式套入LOOKUP(如下)，但不成功，為什麼？？？  應怎麼改？？？
{=lookup(A6,INDEX(A:A,RIGHT(SMALL(--TEXT((1+COUNTIF(A:A,"

说话不用太客气，这个题目我也是根据楼主的要求，改编了一下，算不得什么。
我甚至不知楼主要干什么，哈哈。
=========================================================
LOOKUP(xx,INDEX(,))  这两个函数不能一起使用，即便使用也不能得到你预期的效果。

LOOKUP(xx,{})
    lookup第二个参数应该是引用（区域）或者是单行、单列向量，通常使用A1:A8这样的区域，或者是offset生成的区域引用，也可以是常量数组比如{1,2,3,4,5}，也可以是区域或者引用的运算形式，比如A1:A8+6,{1,2,3,4,5}*5+6+k8  等等。
也可以是内存数组，比如由函数或运算生成的，frequency、match、if函数、len  等等，随便，只要是内存数组就可以。
但是index恰恰不能生成内存数组，index数组只能在多单元格里表现，也就是所谓的多单元格数组公式。
所以lookup（XX，index（，））lookup只找到index生成的第一个数据，其他的都找不到，因为这个空间里没有。
关于index的这种属性，本站有专门的精华帖。方便的话，很容易找到。

hkgoodwill

原帖由 wangg913 于 2010-5-29 19:15 发表

说话不用太客气，这个题目我也是根据楼主的要求，改编了一下，算不得什么。
我甚至不知楼主要干什么，哈哈。
=========================================================
LOOKUP(xx,INDEX(,))  这两个函数不能 ...

大頭兄，我不是特意說這些客氣說話，而是我真的很感動，看到有一簡單程式去將文字及數字排序，又能去除。

我仍希望找到方法能將你的程式放進另一數組的方法，使如LOOKUP函數可以用。