office excel最常用函數公式技巧搜集大全(13.12.09更新)19

06-05

office excel最常用函數公式技巧搜集大全(13.12.09更新)

導讀：不知道怎樣運用公式，可用下面的公式，公式為：=(SUM(A5:E5)-MAX(A5:E5)-MIN(A5:E5))/，但另用TRIMMEAN()函數較好，用活了TRIMMEAN函數，=MIN(B16:B25)（得到最小的數的公式），如何用公式求出最大值所在的行？，語文成績有多個最高分,如何用公式的方法把他們抽出來(動態)?，數組公式，數組公式：{=AVERAGE(IF(B2:B8="

我要將一行數據進行處理。要去掉其中兩個最大值和兩個最小值，不知道怎樣運用公式，應該是：

=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)

㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜

【查詢和查找引用】

查找順序公式

=LOOKUP(2,1/(A1:A20<>0),A1:A20)

=MATCH(7,A1:A20)

=VLOOKUP(7,A1:B11,2)

怎樣實現精確查詢

用VLOOKUP

=VLOOKUP(B11,B3:F7,4,FALSE)

用LOOKUP

=LOOKUP(B11,B3:B7,E3:E7)

用MATCH+INDEX

=INDEX(E3:E7,MATCH(B11,B3:B7,0))

用INDIRECT+MATCH

=INDIRECT("E"&MATCH(B11,B3:B7,0)+2)

用OFFSET+MATCH

=OFFSET(E3,MATCH(B11,B3:B7,0)-1,0)

用INDIRECT+ADDRESS+MATCH

=INDIRECT(ADDRESS(MATCH(B11,B4:B7,0)+3,5))

用數組公式

=INDEX(E1:E7,MAX(IF((B4:B7=B11),ROW(B4:B7),0)))

查找及引用

如何查找並引用B2單元格中所顯示日期當日的相應代碼的值。

B3=IF(COUNTIF($E$3:$E$20,A3),VLOOKUP($A3,$E$2:$M$20,MATCH(B$2,$F$2:$M$2,)+1,),"")

查找函數的應用

我想在A5輸入表的名稱，B5自動跳出該表中B列的最後一個有效數值，請問B5的公式該如何設定？

=LOOKUP(9E+307,INDIRECT(A5&"!"&"B:B"))

B2 =IF(A2="","",LOOKUP(9E+307,INDIRECT(A2&"!B:B")))

怎麼能方便的判斷某個單元格中包含多少個指定的字元？

例：A1 中是―ASAFAG‖，我希望計算出A1裡面有多少個―A‖......

=LEN(A1)-LEN(SUBSTITUTE(A1,"A",""))

如何用查找函數

一、要求: 利用公式從左表中查詢相應的地區，結果放在H14單元格

=VLOOKUP(G14,IF({1,0},D14:D18,C14:C18),2,)

h14=OFFSET(C14,MATCH(G14,D14:D18,0)-1,,,)

H14 =INDIRECT("c"&MATCH(G14,D:D,))

二、要求: 根據C25單元格的商品名稱，查找該商品的最新單價，即該商品最後一條記錄的單價（結果放在D25單元格）。用數組公式：

=INDIRECT("G"&MAX((D14:D22=C25)*ROW(D14:D22)))

D25 =LOOKUP(2,1/(D14:D22=C25),G14:G22)

日期查找的問題

我有一個日期比如:2007/02/12,我想知道它減去一個固定天數比如6後,最接近它的一個星期四(只能提前)是多少號

2007/02/12的答案應該是2007/02/01而不是2007/02/08

日期在A1處，B1處輸入：

=MAX((WEEKDAY(A1-6-{1,2,3,4,5,6,7},2)=4)*(A1-6-{1,2,3,4,5,6,7}))

A1 =2007/02/12

B1, 輸入公式 :

=A1-6-MOD(WEEKDAY(A1-6,2)+3,7)

如何自動查找相同單元格內容

=SUMPRODUCT(($D$2:$D$15=A21)*($E$2:$E$15))

=IF(ISERROR(VLOOKUP(A6,$D$2:$E$15,2,0)),0,VLOOKUP(A6,$D$2:$E$15,2,0)) 查找函數

D3 =LOOKUP(2,1/(($G$3:$G$14=B3)*($H$3:$H$14=C3)),$I$3:$I$14)

=IF(ISERROR(VLOOKUP(A14,A:B:D:F,2,FALSE)),"",VLOOKUP(A14,A:B:D:F,2,FALSE)