[原创]【公式解析系列】之多关键字排序解法

楼主 gouweicao78	【声明】请尊重原创版权！未经允许，不得转载！数据排序技术是Excel的一项自带功能，Excel2003单个排序关键字仅3个，Excel2007达到64个，功能强大。但有些时候，我们并不希望源数据被改变顺序，而希望用公式来得到满足条件的筛选记录并排序，该怎么实现呢。下面本人就函数与公式的多关键字排序解法进行解析：【原理】加权排序法，即：按关键字配“权”，再用SMALL（或LARGE、MIN等）函数排序，最后用MOD除权。【实例】如图成绩表中C、D列是智育、德育成绩，折算比例：智育占70%，德育占30%，要得到按折算分排名后的姓名。（具体见附件）【解析】过程一、E2输入=C20.7+D20.3向下复制，可得到折算后的总分过程二、F2输入=RANK(E2,E$2:E$7)向下复制，可得到按折算分排序的名次。过程三、要得到按折算分排序姓名，如果利用了过程一、二的成果，则很简单：有E、F列的成果，直接用：=INDEX(B:B,MATCH(ROW(1:1),F:F,0))，向下复制如果只用过程一的成果，稍微复杂点：有E列成果，用：=INDEX(B:B,MIN(IF(E$2:E$7=LARGE($E$2:$E$7,ROW(1:1)),ROW($2:$7))))，数组公式，按CTRL+SHFIT+ENTER结束，向下复制【问题】什么是“权” 如果用基础功能的排序，智育就第一关键字，德育就是第二关键字（Excel称主要关键字、次要关键字）。而通过上面这2个方法直接或间接利用了E列的折算分，“权”其实很简单，就是一个折算比例而已，智育成绩的“权”是70%，德育是30%。那么如果没有E、F列，要直接得出这个结果，用下面数组公式：公式1、=INDEX(B:B,MOD(LARGE((C$2:C$70.7+D$2:D$70.3)10^6+ROW($2:$7),ROW(1:1)),10^5)) 其中，C列成绩0.7+D列成绩0.3——已经很容易理解了，“权”的分配嘛。拓展思维：把成绩与行号也加上“权”来分配，即折算分的“权”是10^6（即100万倍），行号的权是10^0（即1倍）。这样，放大后的成绩加上一个“权”不足以影响成绩排名的“行号”，排名仍然一样，LARGE排序后再用MOD去除10^5以上部分，得到的就是按成绩排序后的“行号”的值。其实，从公式1看，我们并不关心折算的成绩是多少分，只管按智育、德育排序。因而，还可以再拓展一下，即：智育成绩10^7，德育成绩10^5，再加行号来排序，如：公式2、=INDEX(B:B,MOD(LARGE(C$2:C$710^8+D$2:D$710^5+ROW($2:$7),ROW(1:1)),10^5)) 【配权】从过程一的折算分中0.7、0.3，演变成10^n，这些都可以看做“权”的一种体现方式。那么本例中，10^n这个n怎么定的呢？ Excel2003的总行数为65536行，不足10万，因而一个正整数字10^5+行号，肯定不会受到行号的影响。如果有1位小数，则配权为10^6（比如公式1），如果公式引用区域的行号根本就不会到100行，那么10^3也就够用了。同理：第一关键字要不受第二关键字的影响（比如公式2），因为德育是3位数以内的整数，因而关键字110^8+关键字210^5再加行号。也就是说，“权”可以根据具体的情况自己调节，本例只是给一个思路，Excel2007的行数已超过此10^5，但如果数据不会超过此行数，都可以用这个解法（超过的话，大数据量的数组公式运算速度也就够呛了，该用基础功能就用）。【除权】除了MOD(加权行号，10^n)方法除权外，我们还可以用RIGHT(加权行号,n)来实现除权。【注意事项】Excel的计算精度是15位数，因而，这个“权”从10^5~10^15之间，要注意合理分配，避免超出计算精度把行号都忽略成0了。【友情提示】如读起来感觉吃力，可先阅读：多关键字排序解析.rar*
2楼 gouweicao78	1楼阐述了多关键字通过加权、排序、除权后取得结果的方法，下面再进行拓展：【加条件】提取智育及格（60分）及以上的姓名，并按智育为主要关键字、德育为次要关键字排序。 =INDEX(B:B,MOD(LARGE(IF(C$2:C$7>=60,C$2:C$710^8+D$2:D$710^5+ROW($2:$7),4^8),ROW(1:1)),10^5))&"" 通过对
3楼 BIN_YANG168	好帖！不得不顶啊，
4楼 xpm130	高人尽出精品!慢慢学哎~~
5楼江南	看来要学习，GOU版的东西，还得安装07
6楼 yukexin	草兄：你好！十分感谢你的分享！你对函数的理解太透彻了！
7楼灵鹤	突然发现你发的关于函数的说明很仔细呀，对于我们新手来就很好学习。真是辛苦你了
8楼 jackycheng001	挺有收获的，还得支持下
9楼 herenqing	不明白“10^2”是什么意思？求解答！感激
10楼谢唐	谢谢老师
11楼真实的我	感谢！
12楼 herenqing	尚需慢慢学习！
13楼 wangqilong1980	非常好用，谢谢版主。
14楼 wangqilong1980	谢谢分享,收藏学习
15楼 china_yilong	谢谢！我学习不懂的地方我会问到底！
16楼 laowuwyx	公式=INDEX(B:B,MOD(LARGE((C$2:C$70.7+D$2:D$70.3)10^6+ROW($2:$7),ROW(1:1)),10^5)) 中的mod好像换成right也可以，如： =INDEX(B:B,RIGHT(LARGE((C$2:C$70.7+D$2:D$70.3)10^6+ROW($2:$7),ROW(1:1)))) 然数据量大时right的第二个参数可以设的大一些。
17楼 zhoulxi	谢谢老师讲解及点评
18楼我一定会回来的	版版你好，能否传一份2003的，因为我们公司都是用03的，我想用07或10的都不成，先谢谢了。
19楼 angel928	草版讲解的好详细喔，收藏起来。
20楼 larkzh	这么好的帖子，才看到，收藏了。
21楼 larkzh	这么好的帖子，才看到，收藏了。
22楼好大一棵树王玉	这次终于能看懂了,很是高兴!