(Excel)常用函數公式及操作技巧之八:大小值或中間值

（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類的對應姓名的最?分

?份 規格 重量

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)

這個也可以