(Excel)常用函数公式及操作技巧之八:大小值或中间值
(Excel)常用函数公式及操作技巧之八:
大小值或中间值
——通过知识共享树立个人品牌。
求平均值
如在列中有一组数字:10、7、9、27、2
=AVERAGE(A2:A6) 上面数字的平均值为11 行公式=AVERAGE(B2:D2)
如何实现求平均值时只对不等于零的数求均值?
=AVERAGE (IF(A1:A5>0,A1:A5))
平均分的问题
假设一个班有60人,要统计出各个学科排名前50的学生的平均分,用公式应该如何写?如果用排序再来算的话很麻烦,能不能直接用公式找出前50名进行计算?
{=AVERAGE(LARGE(A1:A60,ROW(INDIRECT("1:50"))))}
怎样求最大值(最小值或中间值)
=IF(A2="","",MAX(OFFSET(C2,,,MIN(IF(A3:$A$15<>"",ROW(3:$15),15))-MAX(($A$2:A2<>"")*ROW($2:2))))) =IF(A2="","",MAX((LOOKUP(ROW($A$2:$A$14),IF($A$2:$A$14<>"",ROW($A$2:$A$14)),$A$2:$A$14)=A2)*$C$2:$C$14)) =IF(A2="","",LOOKUP(2,1/FIND(A2,$B$2:$B$1000),$C$2:$C$1000)) =IF(A2="","",MAX(IF(ISNUMBER(FIND(A2,$B$2:$B$1000)),$C$2:$C$1000)))
平均数怎么弄
如在列中有一组数字:10、7、9、27、2
公式为:
=AVERAGE(A2:A6) 上面数字的平均值为11 =AVERAGE(A2:A6, 5) 上面数字与 5 的平均值为10
去掉其中两个最大值和两个最小值的公式
我要将一行数据进行处理。要去掉其中两个最大值和两个最小值,不知道怎样运用公式,应该是:
=SUM(A1:A50)-MAX(A1:A50)-LARGE(A1:A50,2)-MIN(A1:A50)-SMALL(A1:A50,2)
这个只能减去1个最大和1个最小值,不符合题意。可用下面的公式。
=SUM(A1:A20)-SUM(LARGE(A1:A20,{1,2}))-SUM(SMALL(A1:A20,{1,2}))
去一行最高分最低分求平均值
去一行中一个最高分和一个最低分求平均值
公式为:=(SUM(A5:E5)-MAX(A5:E5)-MIN(A5:E5))/(COUNTIF(A5:E5,">0")-2)
但另用TRIMMEAN ()函数较好。=TRIMMEAN($A$5:$E$5,2/COUNT($A$5:$E$5))
为需要进行整理并求平均值的数组或数值区域。TRIMMEAN(array,percent)
为计算时所要除去的数据点的比例,例如,如果 percent = 0.2,在 20 个数据点的集合中,就要除去 4 个数据点 (20 x 0.2):头部除去 2 个,尾部除去 2 个。
用活了TRIMMEAN函数,这个问题易如反掌。
在9个数值中去掉最高与最低然后求平均值
假设9个数值所在的区域为A1:A9
=(SUM(A1:A9)-MAX(A1:A9)-MIN(A1:A9))/7 =TRIMMEAN(A1:A9,2/COUNTA(A1:A9)) =TRIMMEAN(A1:A9,2/9) {=AVERAGE(SMALL(A1:A9,ROW(2:8)))} =ROUND((SUM(A1:A9)-MAX(A1:A9)-MIN(A1:A9))/(COUNT(A1:A9)-2),3) =TRIMMEAN(A1:A9,0.286)
求最大值(n列)
{=MAX(($A$2:$A$16=$D$2)*($B$2:$B$16))} {=LARGE(IF(FREQUENCY(N3:AT3,N3:AT3),TRANSPOSE(N3:AT3)),ROW(A1))} {=LARGE(IF(FREQUENCY(TRANSPOSE(N3:AT3),TRANSPOSE(N3:AT3)),(N3:AT3)),ROW(A1))}
如何实现求平均值时只对不等于零的数求均值?
= TRIMMEAN (IF(A1:A5>0,A1:A5))
得到单元格编号组中最大的数或最小的数
对字符格式的数字不起作用。
=MAX(B16:B25) =MIN(B16:B25) (得到最小的数的公式)
标记出3个最大最小值
=RANK(B4,$B4:$Q4)+COUNTIF($B4:B4,B4)<=4 =RANK(B4,$B4:$Q4,2)+COUNTIF(B4:$Q4,B4)<=4 =(COUNTIF($B3:$Q3,">"&B3)+COUNTIF($B3:B3,B3))<=3 =(COUNTIF($B3:$Q3,">"&B3)+COUNTIF(B3:$B3,B3))>COUNT($B3:$Q3)-3 =SMALL(rongjun!$C4:$R4+COLUMN(rongjun!$C4:$R4)/10000,{1,2,3}) =LARGE(rongjun!$C4:$R4+COLUMN(rongjun!$C4:$R4)/10000,{1,2,3}) =RANK(B8,$B8:$Q8)+COUNTIF($B8:B8,B8)-1<=3 =RANK(B8,$B8:$Q8)+COUNTIF($B8:B8,B8)-1>COUNT($B8:$Q8)-3 =C4+COLUMN(C4)/10000>LARGE(rongjun!$C4:$R4+COLUMN(rongjun!$C4:$R4)/10000,4)
取前五名,后五名的方法
{=LARGE(IF(ISERROR($D$2:$D$57),0,$D$2:$D$57),ROW())} {=SMALL(IF(ISERROR($D$2:$D$57),0,$D$2:$D$57),ROW())} {=LARGE(IF(ISERROR(D$2:D$57),"",D$2:D$57),ROW(1:5))} {=SMALL(IF(ISERROR(D$2:D$57),"",D$2:D$57),ROW(1:5))} =LARGE(B$2:B$57,ROW(A1)) =SMALL(B$2:B$57,ROW(A1)+COUNTIF(B$2:B$57,0)) =LARGE(D$2:D$57,ROW(A1)) =SMALL($D$2:$D$57,5-MOD(ROW(A5),5))
如何用公式求出最大值所在的行?
如A1:A10中有10个数,怎么求出最大的数在哪个单元格?
=MATCH(LARGE(A1:A10,1),A1:A10,0) =ADDRESS(MATCH(SMALL(A1:A10,COUNTA(A1:A10)),A1:A10,0),1) =ADDRESS(MATCH(MAX(A1:A10,1),A1:A10,0),1) {=ADDRESS(MATCH(MAX(LEN(A1:A10)),LEN(A1:A10),FALSE),1)} {=ADDRESS(SUM(($A$1:$A$10=MAX($A$1:$A$10))*(ROW($A$1:$A$10))),SUM(($A$1:$A$10=MAX($A$1:$A$10))*(COLUMN($A$1:$A$10))))}
如有多个最大值的话呢?如何一一显示其所在的单元格?
{=IF(ROW(1:1)<=COUNTIF($A$1:$A$100,MAX($A$1:$A$100)),ADDRESS(LARGE(IF($A$1:$A$100=MAX($A$1:$A$100),ROW($A$1:$A$100)),ROW(1:1)),1),"")}
求多个最高分
语文成绩有多个最高分,如何用公式的方法把他们抽出来(动态)?
B15=INDEX(A:A,SMALL(IF(B$2:B$10=MAX(B$2:B$10),ROW($2:$10),65536),ROW(1:1)))&""
数组公式,按下Ctrl+Shift+Enter结束。
如果增加一个条件,就是在姓名前加一个类别,例如前5个人是A类的,后4个是B类的,请分类找出A类和B类的对应姓名的最高分
=INDEX(B:B,SMALL(IF(C$2:C$10=MAX(IF($A$2:$A$10="A",$C$2:C$10)),ROW($2:$10),IF(C$2:C$10=MAX(IF($A$2:$A$10="B",$C$2:$C$10)),ROW($2:$10),65536)),ROW(1:1)))&""
如何求多条件的平均值
应如何求下表中1月份400g重量的平均值
月份 规格 重量
1 400g 401
1 400g 403
2 400g 402
2 400g 404
1 200g 201
1 200g 203
2 200g 202
試試這個行不行
=SUMPRODUCT(($A$4:$A$10=1)*($B$4:$B$10="400g"),($C$4:$C$10))/SUMPRODUCT(($A$4:$A$10=1)*($B$4:$B$10="400g"))
比较土的办法
{=SUM(IF(($A$1:$A$7=1)*($B$1:$B$7="400g"),C1:C7,0))/SUM(IF(($A$1:$A$7=1)*($B$1:$B$7="400g"),1,0))
数组公式:
{=AVERAGE(IF(B2:B8="400g")*(A2:A8=1),(C2:C8),""))}
另一个数组公式试试:=Average(if((a1:a10=1)*(b1:b10="400g"),c1:c10))
=SUMIF(B1:B7,B1,C1:C7)/COUNTIF(B1:B7,B1)
这个也可以
想求出第三大之数值
如A1:A4分别为1,2,2,3.
想求出第三大之数值"1",应如何设公式。
=large(if(frequency(a1:a4,a1:a4),a1:a4),3)
数组公式的解法
=LARGE((MATCH(A1:A10,A1:A10,)=ROW(1:10))*A1:A10,3)
作者:
RDIF
出处:
http://www.cnblogs.com/huyong/
Email:
406590790@qq.com
QQ:
406590790
微信:
13005007127(同手机号)
框架官网:
http://www.guosisoft.com/
http://www.rdiframework.net/
框架其他博客:
http://blog.csdn.net/chinahuyong
http://www.cnblogs.com/huyong
国思RDIF开发框架
,
给用户和开发者最佳的.Net框架平台方案,为企业快速构建跨平台、企业级的应用提供强大支持。
关于作者:系统架构师、信息系统项目管理师、DBA。专注于微软平台项目架构、管理和企业解决方案,多年项目开发与管理经验,曾多次组织并开发多个大型项目,在面向对象、面向服务以及数据库领域有一定的造诣。现主要从事基于
RDIF
框架的技术开发、咨询工作,主要服务于金融、医疗卫生、铁路、电信、物流、物联网、制造、零售等行业。
如有问题或建议,请多多赐教!
本文版权归作者和CNBLOGS博客共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,如有问题,可以通过微信、邮箱、QQ等联系我,非常感谢。