excel中 如何将一组数据中的三个最大值和三个最小值剔除后求平均值？求公式

无重复数值情况下

=(if(or(rank(a1,a1:a10,0)<=3,rank(a1,A1:A10,0)>=(COUNT(A1:A10))-3),0,a1)+if(or(rank(a2,a1:a10,0)<=3,rank(a2,A1:A10,0)>=(COUNT(A1:A10))-3),0,a2)+if(or(rank(a3,a1:a10,0)<=3,rank(a3,A1:A10,0)>=(COUNT(A1:A10))-3),0,a3)+if(or(rank(a4,a1:a10,0)<=3,rank(a4,A1:A10,0)>=(COUNT(A1:A10))-3),0,a4)+if(or(rank(a5,a1:a10,0)<=3,rank(a5,A1:A10,0)>=(COUNT(A1:A10))-3),0,a5)+if(or(rank(a6,a1:a10,0)<=3,rank(a6,A1:A10,0)>=(COUNT(A1:A10))-3),0,a6)+if(or(rank(a7,a1:a10,0)<=3,rank(a7,A1:A10,0)>=(COUNT(A1:A10))-3),0,a7)+if(or(rank(a8,a1:a10,0)<=3,rank(a8,A1:A10,0)>=(COUNT(A1:A10))-3),0,a8)+if(or(rank(a9,a1:a10,0)<=3,rank(a9,A1:A10,0)>=(COUNT(A1:A10))-3),0,a9)+if(or(rank(a10,a1:a10,0)<=3,rank(a10,A1:A10,0)>=(COUNT(A1:A10))-3),0,a10))/(count(a1:a10)-6)

=TRIMMEAN(A1:A20,6/COUNT(A1:A20))
引用范围根据实际情况修改。

=trimmean(a:a,6/count(a:a))