[练习题]函数排序（已总结）

gvntw

<p>用函数对表一排序（主要关键字：列1降序，次要关键字：列2升序）</p><p></p><p>为了解大部分会员的承受能力，以决定题目难度，请大家积极投票，也可以谈谈看法，如：如果只按某列排序，才能做出来；添加辅助列才可以做出来。<br/></p><p>&nbsp;</p><font color="#0000ff" size="3"><strong><div class="msgheader">QUOTE:</div><div class="msgborder"><p><font color="#0000ff" size="3"><strong>总结见32楼。—gvntw</strong></font></p></div></strong></font>

[此贴子已经被作者于2007-6-8 13:44:35编辑过]

knifefox

<p>好题目，另外表一表二这个做的比较漂亮哦。</p><p>过来支持一下，先让大家参与！</p>

detifo

不是很容易。。要想想。

Luckyguy2008

<p>公式可一次成形。但我有个不明白的地方，Mod()部分用F9一步步运算没问题，和起来算会出现小数位，导致结果有点问题。我加了个Int()就好了，这是为什么，请各位大师指点。<br/><br/>=LOOKUP(INT(MOD(SMALL(COUNTIF($A$2:$A$25,"&gt;"&amp;$A$2:$A$25)+COUNTIF($B$2:$B$25,"&lt;"&amp;$B$2:$B$25)/100+ROW(INDIRECT("1:"&amp;ROWS($A$2:$A$25)))/10000,ROW(INDIRECT("1:"&amp;ROWS($A$2:$A$25))))*10000,100)),ROW(INDIRECT("1:"&amp;ROWS($A$2:$A$25))),OFFSET($A$2:$A$25,,COLUMN($A:$E)-1))</p><font color="#0000ff"><div class="msgheader">QUOTE:</div><div class="msgborder"><p><font color="#0000ff">思路很好，这是个多单元格数组公式，有几点意见供参考：</font></p><p><font color="#0000ff">1、为解决浮点计算的误差，应使用Round()进行修约，不能用Int()。</font></p><p><font color="#0000ff">2、<u>为避免浮点计算的误差，应尽量使用整数计算，把数字扩大，不要把数字缩小为小数（这点很重要）。</u></font></p><p><font color="#0000ff">3、使用太多的ROW(INDIRECT("1:"&amp;ROWS($A$2:$A$25)))，没有必要把数组元素都从1开始，只有SMALL要用到，但直接引用单元格，也不必用INDIRECT，直接用ROW(A2:A25)-ROW(A2)+1，即减少了公式长度，又减少了函数嵌套层数。</font></p><p><font color="#0000ff">4、多单元格数组公式，可直接使用相对引用（此点只为减少公式长度，与计算结果无关）。</font></p><p><font color="#0000ff">简化如下：</font></p><p><font color="#0000ff">=LOOKUP(--RIGHT(SMALL(COUNTIF(A2:A25,"&gt;"&amp;A2:A25)*10000+COUNTIF(B2:B25,"&lt;"&amp;B2:B25)*100+ROW(A2:A25),ROW(A2:A25)-ROW(A2)+1),2),ROW(A2:A25),INDEX(A2:E25,,COLUMN(A:E)))</font></p><p><font color="#0000ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;by&nbsp;&nbsp; gvntw</font></p></div></font>

[此贴子已经被gvntw于2007-5-22 9:19:38编辑过]

封州大少

<p>指高手指正</p><p>=INDEX(A:A,RIGHT(SMALL((COUNTIF($A$2:$A$25,"&gt;="&amp;$A$2:$A$25)*10^8)+(COUNTIF($B$2:$B$25,"&lt;"&amp;$B$2:$B$25)*10^5)+ROW($2:$25),ROW(1:1)),5))</p>

knifefox

<div class="msgheader">QUOTE:</div><div class="msgborder"><b>以下是引用<i>封州大少</i>在2007-5-21 17:57:37的发言：</b><br/><p>指高手指正</p><p>=INDEX(A:A,RIGHT(SMALL((COUNTIF($A$2:$A$25,"&gt;="&amp;$A$2:$A$25)*10^8)+(COUNTIF($B$2:$B$25,"&lt;"&amp;$B$2:$B$25)*10^5)+ROW($2:$25),ROW(1:1)),5))</p></div><p>呵呵 封兄不要这么快出手么，给大家留点机会哦。</p>

封州大少

呵呵，抛砖引玉啊

laoyebin

先占个楼，琢磨琢磨

Luckyguy2008

还有一个问题，我的公式中最后一部分是OFFSET($A$2:$A$25,,COLUMN($A:$E)-1)。上次听刀狐老师说过这样会产生3维引用，不能得到正确结果，需要用T()或N()来转换。但我的公式没用T()可以得到正确结果。如果在Offset外面加上T()，反而会不对。请一下这是怎么回事啊。<br/><br/>虽然是凑出了答案，但还是有很多地方不太明白。[em06]

封州大少

MOD有时会产生浮点运算的问题，气死你，呵呵