(Excel)常用函数公式及操作技巧之八：大小值或中间值

1、常用函数公式及操作技巧之八:大小值或中间值通过知识共享树立个人品牌。 求平均值如在列中有一组数字: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"怎样求

2、最大值(最小值或中间值=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$1 4=IF(A2=""

3、;,"",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去掉其中两个最大值和两个最小值的公式我要将一行数据进行处理。 要去掉其中两个最大值和两个最小值, 不知道怎 样运用公式,应

4、该是:=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/C

5、OUNT($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=AVE

6、RAGE(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 (

7、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

8、,B3>COU NT($B3:$Q3-3=SMALL(rongjun!$C4:$R4+COLUMN(rongjun!$C4:$R4/1000 0,1,2,3=LARGE(rongjun!$C4:$R4+COLUMN(rongjun!$C4:$R4/1000 0,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 !$C

9、4:$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=LARG

10、E(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:A1 0,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

11、$10*(ROW( $A$1:$A$10,SUM($A$1:$A$10=MAX($A$1:$A$10*(CO LUMN($A$1:$A$10如有多个最大值的话呢?如何一一显示其所在的单元格?=IF(ROW(1:1<=COUNTIF($A$1:$A$100,MAX($A$1:$A$100,AD DRESS(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$

12、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

13、,ROW($2:$10,65536,ROW(1:1&" "如何求多条件的平均值应如何求下表中 1月份 400g 重量的平均值月份 规格 重量1 400g 4011 400g 4032 400g 4022 400g 4041 200g 2011 200g 2032 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:

14、$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© 2011 EricHu 原创作品,转贴