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

(Excel)常用函數(shù)公式及操作技巧之八：大小值或中間值求平均值

——通過知識(shí)共享樹立個(gè)人品牌。如在列中有一組數(shù)字：10、7、9、27、2=AVERAGE(A2:A6)上面數(shù)字的平均值為11行公式=AVERAGE(B2:D2)如何實(shí)現(xiàn)求平均值時(shí)只對(duì)不等于零的數(shù)求均值？＝AVERAGE(IF(A1:A5>0,A1:A5))平均分的問題605050{=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)))平均數(shù)怎么弄如在列中有一組數(shù)字：10、7、9、27、2公式為：=AVERAGE(A2:A6)上面數(shù)字的平均值為11=AVERAGE(A2:A65)上面數(shù)字與510去掉其中兩個(gè)最大值和兩個(gè)最小值的公式樣運(yùn)用公式，應(yīng)該是：=SUM(A1:A50)-MAX(A1:A50)-LARGE(A1:A50,2)-MIN(A1:A50)-SMALL(A1:A50,2)這個(gè)只能減去1個(gè)最大和1個(gè)最小值，不符合題意。可用下面的公式。=SUM(A1:A20)-SUM(LARGE(A1:A20,{1,2}))-SUM(SMALL(A1:A20,{1,2}))去一行最高分最低分求平均值去一行中一個(gè)最高分和一個(gè)最低分求平均值公式為：=(SUM(A5:E5)-MAX(A5:E5)-MIN(A5:E5))/(COUNTIF(A5:E5,">0")-2)但另用TRIMMEAN()函數(shù)較好。=TRIMMEAN($A$5:$E$5,2/COUNT($A$5:$E$5))為需要進(jìn)行整理并求平均值的數(shù)組或數(shù)值區(qū)域。TRIMMEAN(array,percent)為計(jì)算時(shí)所要除去的數(shù)據(jù)點(diǎn)的比例，例如，如果percent0.2，在20數(shù)據(jù)點(diǎn)的集合中，就要除去4個(gè)數(shù)據(jù)點(diǎn)(20x0.2)：頭部除去2個(gè)，尾部除去2個(gè)。用活了TRIMMEAN函數(shù)，這個(gè)問題易如反掌。9個(gè)數(shù)值中去掉最高與最低然后求平均值9A1=(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))｝如何實(shí)現(xiàn)求平均值時(shí)只對(duì)不等于零的數(shù)求均值？=TRIMMEAN(IF(A1:A5>0,A1:A5))得到單元格編號(hào)組中最大的數(shù)或最小的數(shù)對(duì)字符格式的數(shù)字不起作用。=MAX(B16:B25)=MIN(B16:B25)（得到最小的數(shù)的公式）3個(gè)最大最小值=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)-30,{1,2,3})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!$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個(gè)數(shù)，怎么求出最大的數(shù)在哪個(gè)單元格？=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))))}如有多個(gè)最大值的話呢？如何一一顯示其所在的單元格？{=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),"")}求多個(gè)最高分語文成績有多個(gè)最高分,如何用公式的方法把他們抽出來(動(dòng)態(tài))?B15=INDEX(A:A,SMALL(IF(B$2:B$10=MAX(B$2:B$10),ROW($2:$10),65536),ROW(1:1)))&""數(shù)組公式，按下Ctrl+Shift+Enter結(jié)束。如果增加一個(gè)條件,就是在姓名前加一個(gè)類別,例如前5個(gè)人是A類的,后4個(gè)是B類的,請(qǐng)分類找出A類和B類的對(duì)應(yīng)姓名的最高分=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$"如何求多條件的平均值1400g月份規(guī)格重量1400g4011400g4032400g4022400g4041200g2011200g2032200g202試試這個(gè)行不行=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))}數(shù)組公式：{=AVERAGE(IF(B2:B8="400g")*(A2:A8=1),(C2:C8),""))}另一個(gè)數(shù)組公式試試：=Average(if((a1:a10=1)*(b1:b10="400g"),c1:c10))=SUMIF(B1:B7,B1,C1:C7)/COUNTIF(B1:B7,B1)這個(gè)也可以想求出第三大之?dāng)?shù)值如A1：A4分別為1,2,2,3.想求出第三大之?dāng)?shù)值"1",應(yīng)如何設(shè)公式。=large(if(frequency(a1:a4,a1:a4),a1:a4),3)數(shù)組公式的解法=LARGE((MATCH(A1:A10,A1:A10,)=ROW(1:10))*A1:A10,3)?2011EricHu原創(chuàng)作品，轉(zhuǎn)貼請(qǐng)注明作者和出處，留此信息。------------------------------------------------cnBlobs:/huyong/CSDN：