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

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

时间：2022-06-29 06:18:50

(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)))&""