|
发的帖子跟贴那么少，心里好是惭愧呀。我的例子很多是论坛里收集的，解决办法有些是参照高手的，有些是自己摸索的。看排序我有一个体会就是公式比较庞大，比数据有效性，单元格要复杂的多，尤其涉及数组公式。但看到后来我发觉排序基本上都可以套用一个公式来解决（该公式是我看一位仁兄的公式一个下午再经消化吸收改编形成的）。一种排序往往实现的方式有多种，但从解决问题的角度来说一个人只要熟练自己习惯的一些函数组合就行了，遇到特殊的排序可以降低要求作个辅助列来解决，本来么写函数的话一个辅助列应该就相当于定义一个数组，没有什么丢人的（本人就因为对一个区域排序想通过不加辅助列的方式一步实现，结果白白浪费了两天，头都涨死了，最后还是添加了辅助列，把它变成了一个很熟悉的问题，我们的首要任务是解决问题，心态很重要。当然你是高手那除外，毕竟我们要不断完美自己。） 排序，一般是对某列数字排序，当排序的特性不是数据的时候我们就要把他转化为数据（比如根据文本的长度我们可以先通过一个公式LEN（）来转化成数据，其他雷同），然后套用INDEX（REF，SMALL（IF（large(f(REF),row())=f(REF),row(REF),""),row()-sum(if(large(f(REF),row())<f(REF),1,"")))). 解释：上面的公式我已经作了某些假定：我们是从第一行开始排序。其他简要说明如下：f(REF),在这里不是一个具体的函数，他是不确定的，他的功能是用来把那些排序特性转化为数值型的，就如上边的LEN（），如果排序的对象就是数值型的那么可以省去这个函数；INDEX（）函数大家应该懂吧，不懂自己查帮助（总的逻辑如下：排序么从稍微高一点的角度讲就是把一列数（源数）更改次序后变成另一列数（目标数）。这里有两点，一，目标数和源数是同一堆数，所以目标数1的生成过程可以看成是把源数中满足条件的数选过来（降序排列时就是从源数据中找到第row()大的数据(这里row()是用来标注目标数据的序号，可以根据实际进行修改，如果目标数据是一行的，那么可以用column()取代）在源数据中的位置，然后把源数据中这个位置的数据取出来生成目标数据。对于纯数据排序这可以了，但是如果是这样我们不是可以通过excel自己的降序来完成了吗？对呀，但实际情况可能是这样源数据有多列，其中有一列是数据，呵呵，这样好象也能用excel的自带降序来完成...恩，这样用公式后就是自动实现了，如果数据多的话还是省心很多，加上如果要对文本长度之类特殊的排序还是懂点公式好，呵呵，必要性就讲这里吧，呵呵。 上边的通用公式有个地方可能看上去有点晦涩，row()-sum(if(large(f(REF),row())<f(REF),1,"")))可能括号层次有错误，先不考虑这些。这部分主要是针对下面这种情况，看了你就明白了。先有必要讲一下large()函数，定义名称1，5，6，8，5为data，那么large(data,1)是8，没有疑问，large(data,2)=6,也没有疑问，但是large(data,3)=large(data,4)=5,如果他们分别对应事物A,B,C,D,E（定义名称为ABC）的某个数据属性，我们要根据他们的这个数据属性对事物进行排序，如果我们使用INDEX（REF，SMALL（IF（large(f(REF),row())=f(REF),row(REF),""),1))).那么降序的结果是D，C，B，B，A，你可以发现B出现了2次，而E没有出现，原因就是因为B，E的数据属性大小相等，而B在源数据中的位置在前面。在公式IF（large(f(REF),row())=f(REF),row(REF),"")中我们把第row()大的源数据项在源数据中的位置都记录下来了，保存在这样的一个数组中{"","",row(ref1),"","",row(ref2)...}。对应我们上面的情况就是IF（large(data,row())=data,row(data),""),当row()=3是，就是把第三大的数5在源数据中的位置登记到这个数组了{"",2,"","","",5}.如果我们使用公式=INDEX（ABC，SMALL（IF（large(data,3)=data,row(data),""),1)).括号层次可能不对，不理会这个。这样就是取了ABC中的第2个数，就是B。当row()=4时，large(data,4)=5,此时产生的数组也是{"",2,"","","",5},通过公式得到的数据依然是B。而我们期望这个时候得到的是5，就是ABCDE中的E。这种情况什么时候会发生呢，就是当数据相同的时候发生这样的错误（上面的例子中就是同时出现5的情况）。从信息的角度讲就是我们没有充分利用源数据中的次序这个量，就是对中间生成的数组没有充分利用。当产生的数组中非""的量有多个时为了能让他一个一个吐（应该第一声吧，呵呵）出来，而不是单单就知道吐第一个，怎么办呢，我于是想到了row()的渐大性。最大的产生就是row(),第二大的产生就是row()-第一大的数的个数，第三大的产生就是row()-第一大的数的个数-第二大的数的个数...于是就是row()-所有比large(data,row())大的个数。这个个数就是sum(if(large(f(REF),row())<f(REF),1,"")）。这样对于我们的列子，B的产生，row()-2=1,取数组{"",2,"","","",5},中的第一小的数据就是2，对应ABC中的事物就是B，E的产生，row()-2=4-2=2,取数组{"",2,"","","",5},中第二小的数据就是5，对应ABC中的事物就是E。于是排序就是D，C，B，E，A。 个人觉得排序核心的就是这个公式。如果消化了就好呀，呵呵。如果喜欢请回个贴，我最近对这个虚无的东西还是很感兴趣，谢谢配合。
hl3eLkHd.rar
(1.93 KB, 下载次数: 20)
个人感觉排序的功力主要体现在那个数组公式里,如果一时看起来有点累,建议先去看我的主题贴中那个数组公式解读,然后我想你会更容易读这个公式,然后排序的时候真的就当他公式套好了，,呵呵
[此贴子已经被作者于2006-12-10 17:13:14编辑过] |
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2006-11-9 22:45
发表于 2006-11-10 00:24
楼主
。我觉得写公式比较简单，但读公式是比较难的，有时需要加上点猜想，现在我在主攻透视性表，希望不日和大家见面，呵呵。那个东西我发觉对统计分析的人很有帮助。