Office中国论坛/Access中国论坛

标题: 如何才能判断单元格的数量及取得单列单元格的2个以上的最大值 [打印本页]

作者: 乱笔 时间: 2008-7-21 10:07
标题: 如何才能判断单元格的数量及取得单列单元格的2个以上的最大值
如何才能判断单元格的数量及取得单列单元格的2个以上的最大值。

假设： A列单元格有效数据在1-100个左右，使用什么函数判断单元格有多少个有效数据，然后使用什么函数取得有效果数据的N个最大值和N个最小值。

小弟看了下，MAX，MIN函数都只能取到一个最大值，而不能指定取几个最大值？原题目是：

有效投标报价投标人在5-10人的，去掉1个最高值和1个最低值后进行平均；在11-30人的，去掉2个最高值和2个最低值后进行平均；在31-50人的，去掉5个最高值和5个最低值后进行平均；在51-100人的，去掉10个最高值和10个最低值后进行平均；在100人以上的，去掉20个最高值和20个最低值后进行平均。

而且要求在一个单元格里完成

作者: liwen 时间: 2008-7-21 11:46
=AVERAGE(LARGE(A1:A100,ROW(OFFSET($A$1,IF(COUNTA(A1:A100)<=10,1,IF(COUNTA(A1:A100)<=30,2,IF(COUNTA(A1:A100)<=50,5,10))),0):OFFSET($A$1,COUNTA(A1:A100)-IF(COUNTA(A1:A100)<=10,1,IF(COUNTA(A1:A100)<=30,2,IF(COUNTA(A1:A100)<=50,5,10)))-1,0))))
ctrl+shift+Enter组合键结束

作者: 乱笔 时间: 2008-7-21 12:41
经过测试，很灵，谢谢。
不过，这个好像只写到100人的那条，那100人以前，要加哪一句，看了N长时间，一下子没理解

作者: liwen 时间: 2008-7-21 15:25
开始看你问题以为就在1-100之间呢

将IF语句中最后的10改为IF(COUNTA(A1:A100)<=100,10,20)

作者: 乱笔 时间: 2008-7-21 15:53

原帖由 liwen 于 2008-7-21 15:25 发表
开始看你问题以为就在1-100之间呢

将最后的10改为IF(COUNTA(A1:A100)

改成这样报错：=AVERAGE(LARGE(A1:A100,ROW(OFFSET($A$1,IF(COUNTA(A1:A100)<=10,1,IF(COUNTA(A1:A100)<=30,2,IF(COUNTA(A1:A100)<=50,5,10))),0):OFFSET($A$1,COUNTA(A1:A100)-IF(COUNTA(A1:A100)<=10,1,IF(COUNTA(A1:A100)<=30,2,IF(COUNTA(A1:A100)<=50,5,IF(COUNTA(A1:A100)<=100,10,20))))-1,0))))

这样也报错：=AVERAGE(LARGE(A1:A100,ROW(OFFSET($A$1,IF(COUNTA(A1:A100)<=10,1,IF(COUNTA(A1:A100)<=30,2,IF(COUNTA(A1:A100)<=50,5,IF(COUNTA(A1:A100)))),0):OFFSET($A$1,COUNTA(A1:A100)-IF(COUNTA(A1:A100)<=10,1,IF(COUNTA(A1:A100)<=30,2,IF(COUNTA(A1:A100)<=50,5,IF(COUNTA(A1:A100))))-1,0))))

再次请教。。。。。。

作者: liwen 时间: 2008-7-21 16:09
=AVERAGE(LARGE(A1:A100,ROW(OFFSET($A$1,IF(COUNTA(A1:A100)<=30,IF(COUNTA(A1:A100)<=10,1,2),IF(COUNTA(A1:A100)<=50,5,IF(COUNTA(A1:A100)<=100,5,10))),0):OFFSET($A$1,COUNTA(A1:A100)-IF(COUNTA(A1:A100)<=30,IF(COUNTA(A1:A100)<=10,1,2),IF(COUNTA(A1:A100)<=50,5,IF(COUNTA(A1:A100)<=100,5,10)))-1,0))))

没注意,可能数组公式里if语句只能嵌套三层吧!

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)