Question

求教专家，如何用excel表格自动计算一组数据前2/3的平均值

在表格中一列60个单元格中输入一组数据（1、随机输入，不按大小顺序；2、可能还有空白单元格，空白单元格不计入统计个数。）
问题：如何用excel表格自动计算一组数据，按从大到小排列后，排在前2/3的数据的个数和平均值，以及排在后1/3的数据的个数和平均值？
说明：主要是要制作一个固定格式的表格，用于统计计算，后期只要输入数据后就可自动得出结果，所以不能对数据进行排序等操作，只能用公式/函数计算，头疼了。。。
补充一下，有几个要求的：
1、输入数据大小是随机，输入完成后不能再变动，也就是说不能对数据进行排序操作
2、输入时可能会有空白单元格，这些空白单元格是不纳入个数计算以及平均值计算的
3、求前2/3的数据的平均值，是这组数据按从大到小排序后的，排在前面的2/3个数的平均值
4、要求整个表格，完成后只要在固定区域（如A1:A60）中输入数据，即可自动计算出结果，其公式等设置好后不能变动
公式/函数小弟只懂一点皮毛，面对这些变态要求，崩溃了，求教高手 orz
另外，答案中最好能带上一点解说，小白们万分感激！

Answer 1

难点在于空白单元格不计入统计个数
因此建议使用一个辅助列进行个数统计
假设数据在A1:A60
则使用B1:B60作为辅助
其中B1公式为：=IF(A1="","",COUNT(A$1:A1))
向下复制填充到B60

统计前2/3数据的个数为：=ROUND(COUNT($A$1:$A$60)*2/3,)
平均值为：=SUMIF($B$1:$B$60,"<="&ROUND(COUNT($A$1:$A$60)*2/3,),$A$1:$A$60)/ROUND(COUNT($A$1:$A$60)*2/3,)
后1/3数据的个数为：=ROUND(COUNT($A$1:$A$60)/3,)
平均值为：=SUMIF($B$1:$B$60,">"&ROUND(COUNT($A$1:$A$60)*2/3,),$A$1:$A$60)/ROUND(COUNT($A$1:$A$60)/3,)

追问

首先，非常感谢您的回答，但是这个我在表中试了一下，计算的是直接输入完成后，位置排在前面2/3（或后1/3）的数据的平均值，而非大小排序，问题我又补充详述了一下要求，请了解！

追答

我的解答并非是大小排序
B列是对每个数据进行编号
然后根据编号判断是否是前2/3，并进行求和
应该是符合要求的
请仔细理解公式的含义

追问

了解B列编号的用意是排除空格，这个ideal非常不错！
但是计算的时候，要求是数据按从大到小排列后，取前2/3的数据计算平均值。
比如说我在A1:A45中输入了数据，按上面的公式计算前2/3的数据的平均值，是算的A1：A30的平均值，而我想要的是A1：A45中数据按从大到小排列后，排在前面2/3的数据的平均值，也就是说，取最大的2/3部分的数据求平均值。
这个可以参考网友 骨头81 给的公式，只是他的公式没有考虑空格，望解惑！

追答

哦，不好意思，我理解错了，原来还需要按大小统计
那么有一个问题
如果有相同的数据，该如何取值？
比如只有9个数据，假设就是1，2，3，4，5，6，6，6，7
取前2/3的数据应该是1，2，3，4，5，6
那么另外的两个6是否不需要计算？

追问

另外两个6不需要计算，这种算法我也想过，就是设置出在2/3位置的临界数，然后用大于等于临界数的总和除以个数，得出平均值。但是这种计算方法在出现临界数有多个重复的时候，结果会有偏差，不符合要求

追答

明白了
请将B列的辅助编号公式改为：
=IF(A1="","",RANK(A1,$A$1:$A$60,1)+COUNTIF(A$1:A1,A1)-1)

计数和统计公式不变

Answer 2

假设数据在A1：A60
则B1输入
=AVERAGE(IF(ISERROR(LARGE(A1:A60,ROW(A1:A40))),"",LARGE(A1:A60,ROW(A1:A40))))
前2/3的数据的平均值
后1/3的数据的个数和平均值：
B1输入
=AVERAGE(IF(ISERROR(SMALL(A1:A60,ROW(A1:A20))),"",SMALL(A1:A60,ROW(A1:A20))))
各公式shift+ctrl+enter 三键结束。

追问

够简洁！
不过，大哥，前面说的空白单元格貌似也算进来了，比如说，60个单元格，只输入了45个数据，那么前2/3的数据的个数应该只有30个，计算平均值应该也是只有这30个数据来算
另外，SMALL(A1:A60,ROW(A1:A40）），高段的组合计算？小白没看懂，可以解说一下吗，真心求教

追答

要将空白单元格排除在外：
B1输入=ROUND(COUNTA(A1:A60)*(2/3),0) shift+ctrl+enter 三键结束，求的是A1:A60中非真空单元格的个数的2/3，经四舍五入后的值。
C1输入 =AVERAGE(LARGE(A1:A60,ROW(INDIRECT("A1:A"&B1)))) shift+ctrl+enter 三键结束
求得是A1:A60中从大到小排序后，前2/3的数的平均值。
D1输入=ROUND(COUNTA(A1:A60)*(1/3),0) shift+ctrl+enter 三键结束，求的是A1:A60中非真空单元格的个数的1/3，经四舍五入后的值。
E1输入=AVERAGE(SMALL(A1:A60,ROW(INDIRECT("A1:A"&D1)))) shift+ctrl+enter 三键结束
求得是A1:A60中从大到小排序后，后1/3的数的平均值。
shift+ctrl+enter 是数组公式，你还是看看书吧,这里说不明白。

Answer 3

我来给个简单的，昨晚下班的时候看到的，可是没时间了，现在补上。
此方法不需理会空单元格, 巧妙之处就在于rank函数计算排名上面
插入辅助列B B1=rank(b1,A$1:A$60),得出每个数据在此列中的排名（空单元格显示#N/A，不用理会），C1=countif(A1:160,"<>"),得出非空单元格个数，
D1=round(C1*2/3) 得出前2/3的个数（实际上不管是前2/3还是后2/3，个数而已，就是占总体的比例）
那么下面前2/3的平均值为 =averageif(B1:B60,"<=D1",A1:A60) 即算排名前2/3的平均值
这样的话，后1/3的平均值也很好算了 =averageif(B1:B60,">D1",A1:A60)

追问

如果数据中有较多重复项，特别在2/3临界的地方有多个数据相等的话，计算结果会有偏差，可以参考网友 一百℃猪 回答中最后一组9个数的例子，所以这个问题是比较麻烦的

追答

是啊，不好意思，忘了这茬，如果在2/3之前有重复值是无所谓的，关键就是在2/3前后值相同的话就不好弄了，同求高人指点
我想到了，为了能够解释清楚和发现其中的错误，我们一步一步来，还是需要插入辅助列（排名列），不过要用到VLOOKUP，所以这个辅助列查到前面去，A列为排名，B列为数据
假设数据为
数据 3 4 4 4 4 5
排序 1 2 2 2 2 6
（不想排版，实际为竖排）
C1和D1任然是上面的意义，
讨论在2/3处出现重复值，则必然count(A1:A60,"D1，那么要取得小于排名为D1的最大一个数和前面的那些数
=MAX(IF(A1:A60<D1,A1:A60))
按ctrl+shift+enter三键结束.

得出小于D1的那个最大排名，我们把这个公式放在E1中。
得到了E1，那么E1对应的那个避免被重复计算的最大值为F1=VLOOKUP(E1,A1:B60,2,0)
实际要计算的次数为 D1-(E1-1)
下面开始来做这个最终的公式

=if(count(A1:A60,"<=D1")<=D1,averageif(A1:160,"<=D1",B1:B60),(sumif(A1:A60,"<E1",B1:B60)+(D1-E1+1)*F1)/D1)

Answer 4

“排在前2/3的数据的个数和平均值”比较好搞

Answer 5

数据排序方法：排在B列，从大到小排列，在B1输入=LARGE(A$1:A$60,ROW())，向下填充至B60；从小到大排列就把LARGE改为SMALL！

追问

这种方法可行，公式还可以调整一下，这样如果有空格会好看一点，=IF(ISERROR(LARGE(A$1:A$60,ROW())),"",LARGE(A$1:A$60,ROW()))
但也还有一个问题，就是如果有空的单元格，如何计算有效数据的个数，后续求平均值需要用到的
补充要求：数据的值未确定范围