|
原帖由 大双子星 于 2010-7-14 21:27 发表
=MAX(IF(COUNTIF(AA,ROW($1:$10)-1)-(MATCH(ROW($1:$10)-1,--AA,)/100)=LARGE(COUNTIF(AA,ROW($1:$10)-1)-(MATCH(ROW($1:$10)-1,--AA,)/100),ROW(1:1)),ROW($1:$10)-1)) 能讲解下不?没弄明白
一.先把公式中部分函数、符号、“代号”做个简单介绍:
1.AA——定义了的名称,是动态取A列最后19个数据(见插入/名称/定义:AA=OFFSET(INDIRECT("$A"&LOOKUP(1,0/($A$1:$A$1000),ROW($A$1:$A$1000))),-18,,19),其中LOOKUP(1,0/(Sheet3!$A$1:$A$1000),ROW(Sheet3!$A$1:$A$1000))求出A列($A$1:$A$1000)最后有数据的单元格行号;INDIRECT("$A"&LOOKUP(1,0/($A$1:$A$1000),ROW($A$1:$A$1000)))最后单元格;OFFSET(INDIRECT("$A"&LOOKUP(1,0/($A$1:$A$1000),ROW($A$1:$A$1000))),-18,,19)最后单元格向上偏移18行起往下取19行的数据。
2.“--AA”——AA数据是文本公式,前面加上“--”,转化成数值型。
3.ROW($1:$10)-1——数字0~9。
4.COUNTIF(AA,ROW($1:$10)-1)——数字0~9依次在AA里出现的次数,本例(未在A21添加数字时,下同)为{1;3;3;1;2;2;3;1;2;1}:0一次、1三次、2三次……。
5.MATCH(ROW($1:$10)-1,--AA,)——数字0~9依次在AA里出现的位置,本例{12;11;1;4;6;14;3;15;2;9},0在12位、1在11位、2在第1位……;MATCH(ROW($1:$10)-1,--AA,)/100——把位置次序除以100,将此次序变成一个较小的加权数,与上一条结合在一起,COUNTIF(AA,ROW($1:$10)-1)-(MATCH(ROW($1:$10)-1,--AA,)/100)本例得到{0.88;2.89;2.99;0.96;1.94;1.86;2.97;0.85;1.98;0.91},就可以把出现次数一样多但先出现的变得比后出现的大一点,如1和2,都出现3次,但经过加权后,后出现的“1”3次变成2.89,而先出现的“2”3次变成2.99。这样便解决了“数字出现次数相同的话,在统计范围内谁先出现的,谁就排在上面”的可操作性。
二.整个公式:=MAX(IF(COUNTIF(AA,ROW($1:$10)-1)-(MATCH(ROW($1:$10)-1,--AA,)/100)=LARGE(COUNTIF(AA,ROW($1:$10)-1)-(MATCH(ROW($1:$10)-1,--AA,)/100),ROW(1:1)),ROW($1:$10)-1))——
1.IF(COUNTIF(AA,ROW($1:$10)-1)-(MATCH(ROW($1:$10)-1,--AA,)/100)=LARGE(COUNTIF(AA,ROW($1:$10)-1)-(MATCH(ROW($1:$10)-1,--AA,)/100),ROW(1:1)),ROW($1:$10)-1))——在第一个单元格,判断上面加权后得到的数组中哪一个数是第一大(ROW(1:1)):2.99,其对应的0~9中的数2就是原值,而其余的为false;随着下拉,依次判别哪个是第二大(ROW(2:2))2.97、第三大(ROW(3:3))2.89……对应的即为6、1……
2.MAX(……)——取出最大数,不言而喻吧。
[ 本帖最后由 wangjguo44 于 2010-7-14 23:12 编辑 ] |
|