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

導讀：均為數組公式，如:a1*b1+a2*b2+b3*b3...的和=SUM(A1:A3*B1:B3，sumif函數的計算格式為:=sumif($a$1:$a$20,"&g，H啊~~~你不知道什麼是數組函數啊，請按如下操作1、把數據區域設置成可篩選2、把SEX篩選成"=女&quo，將此函數橫著使用（A1-G1）=TRIMMEAN(A1:G1,4/7)*(7-4，G4:G11公式為

隔列求和

銷售合計=SUMIF($D$3:$BS$3,"銷售",$D$4:$BT$4) =SUMIF($D$3:$BS$3,BT$3,$D4:$BS4)

=SUMPRODUCT((MOD(COLUMN($D4:$BS4),2)=0)*$D4:$BS4)

關於隔行、隔列求和的問題

隔2列加總

=SUM((MOD(ROW($A$1:$A$25),2)=0)*$A$1:$A$25)

隔2行加總

=SUM((MOD(COLUMN($B$1:$T$1),2)=0)*$B$1:$T$1)

均為數組公式。

EXCEL中求兩列的對應元素乘積之和

如:a1*b1+a2*b2+b3*b3...的和 =SUM(A1:A3*B1:B3) (數組公式) =SUMPRODUCT(A1:A10,B1:B10)

計算900~1000之間的數值之和

sumif函數的計算格式為: =sumif($a$1:$a$20,">1000")。即返回$a$1:$a$20中大於1000的數值的和,但如果想計算900~1000之間的數值之和,應該如何編寫。

請參考：{=SUM(IF((A1:A20>900)*(A1:A20<1000),A1:A20))}或{=SUM((900<A1:A20)*(A1:A20<1000)*A1:A20)}

2、=SUMIF(A1:A20,">900")-SUMIF(A1:A20,">1000")

雙條件求和

1、 求一班女生的個數 :

=SUMPRODUCT((A2:A9=1)*(B2:B9=""女""))

2、求一班成績的和 : =SUMIF(A2:A9,1,C2:C9) "

3、求一班男生成績的和 :

=SUMPRODUCT((A2:A9=1)*(B2:B9=""男""),C2:C9) "

如何實現這樣的條件求和

B$2:$B$12)

=SUMPRODUCT((ISNUMBER(FIND("BC",A2:A12))<>ISNUMBER(FIND("ABC",A2:A12)))*B2:B12)

A1:A10數字顯為文本格式時，如何求和

=SUMPRODUCT(A1:A10)

求和

。

71700 =SUM($G$7:$G$16)

簡單求和

B 求男演員工資總額

42200 =SUMIF($D$7:$D$16,"男",$G$7:$G$16) 單條件求和.1

C 求年齡在20歲以下的演員工資

22500 =SUMIF($E$7:$E$16,"<20",$G$7:$G$16) 單條件求和.2

D 求主角和配角的工資(不是普通演員)

67500 =SUMIF($F$7:$F$16,"*角",$G$7:$G$16) 單條件求和.3

E 求20歲以下女演員工資

9500 {=SUM(($D$7:$D$16="女")*($E$7:$E$16<20)*$G$7:$G$16)} 多條件求和-同時滿足條件

F 求男性或主角的工資

59200 {=SUM(IF(($D$7:$D$16="男")+($F$7:$F$16="主角"),$G$7:$G$16))} 多條件求和-只須滿足條件之一

G 求男性非主角或主角非男性的工資(即除男主角外的男性和主角)

g.1 21200 {=SUM(IF(($D$7:$D$16="男")-($F$7:$F$16="主角"),$G$7:$G$16))} g.2 21200 {=SUM(IF(($F$7:$F$16="主角")-($D$7:$D$16="男"),$G$7:$G$16))} 多條件求和-只滿足條件之一而不能同時滿足

H 啊~~~你不知道什麼是數組函數啊，可是你有時候也要用多條件求和？ 不要緊，教你用另外的方法：SUBTOTAL 求20歲以下女演員工資

71700 =SUBTOTAL(9,$G$7:$G$16) 現在你看到的還不是最後結果，請按如下操作 1、把數據區域設置成可篩選 2、把SEX篩選成"=女", 把年齡篩選成<20 3、你再看上面的公式結果?

去掉其中兩個最大值和兩個最小值，再求和

請問如何去掉兩個最高分，兩個最低分，剩餘人員的分數求和，例如A1-A7中的7個分 ，去掉兩個最高分，兩個最低分，剩餘人員的分數求和。

=SUM(A1:A50)-MAX(A1:A50)-LARGE(A1:A50,2)-MIN(A1:A50)-SMALL(A1:A50,2) =SUM(A1:A20)-SUM(LARGE(A1:A20,{1,2}))-SUM(SMALL(A1:A20,{1,2}))

=TRIMMEAN(A1:A7,4/7)*(7-4)

=SUMPRODUCT(LARGE(A1:A7,ROW(A1:A7))*(ROW(1:7)>2)*(ROW(1:7)<6))

=SUMPRODUCT((A$1:A$7<LARGE(A$1:A$7,2))*(A$1:A$7>SMALL(A$1:A$7,2))*A$1:A$7)

=SUM (A!:A7)-LARGE(A!:A7,1)-LARGE(A!:A7,2)-SMALL(A!:A7,1)-SMALL(A!:A7,2)

將此函數橫著使用（A1-G1） =TRIMMEAN(A1:G1,4/7)*(7-4)

=SUMPRODUCT(LARGE(A1:G1,COLUMN(A1:G1))*(COLUMN(A:G)>2)*(COLUMN(A:G)<6))

去掉兩個最高分、最低分，顯示出被去掉的分數

被去掉的分數：

最大兩個：=large(data,{1;2}) 最小兩個：=small(data,{1;2})

永恆的求和

1、=SUM(OFFSET(A1,,,ROW()-ROW(A1)))可以對A列數值自動求和。 2、=SUM(INDIRECT("R2C:R[-1]C",FALSE)) 3、=SUM(INDIRECT("A2:A"&ROW()-1))

=SUM(INDIRECT(ADDRESS(1,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN())))

按字體顏色求和

做法：

G3={SUM(IF(($A$2:$A$19=E3)*($B$2:$B$19=F3),$C$2:$C$19))}

G4:G11公式為G3公式下拖.

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

如何分班統計男女人數

男=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=F$1)) =SUMPRODUCT(($B$2:$B$446=E3)*($C$2:$C$446=F$1)) =SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=F$1)) ｛=SUM(($B$2:$B$446=$E2)*($C$2:$C$446=$F$1))｝

｛=SUM(($B$2:$B$446=F2)*($C$2:$C$446=$G$1)*$D$2:$D$446)｝ 女=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=G$1)) 合計=COUNTIF($B$2:$B$446,E2)

統計數值大於等於80的單元格數目

在C17單元格中輸入公式： =COUNTIF(B1:B13,">=80")

確認後，即可統計出B1至B13單元格區域中，數值大於等於80的單元格數目。

計算出A1里有幾個abc

A1: abc-ded-abc-def-abc-ded-ded-abc , 如何計算出A1里有幾個abc 公式=(LEN(A1)-LEN(SUBSTITUTE(A1,"abc","")))/LEN("abc")

有條件統計

如何統計當A1<=15時，統計B列中<=8.5的累加值和個數，而>15時不進行統計？ 個數：

=IF(A1>15,"",COUNTIF(B2:B10,"<=8.5")) 累加值（求和）：

=IF(A1>15,"",SUMIF(B2:B10,"<=8.5"))

如何統計各年齡段的數量

需分別統計20歲以下、21-30歲、31-40歲、41-50歲、50歲以上年齡段的數量。 根據「出生日期」用以下公式，得到「自動顯示年齡」。

先將F列的出生日期設置為「1976年5月」格式，在G列公式為： =DATEDIF(F2,TODAY(),"Y") （周歲，自動顯示年齡） =YEAR(TODAY())-YEAR(F2)

再根據年齡段：20歲以下、21-30歲、31-40歲、41-50歲、50歲以上，用以下公式，求出不同年齡段人數。

在J2公式為：

=SUMPRODUCT(($G$2:$G$34>$H1)*($G$2:$G$34<=$H2)*($C$2:$C$34=J$1)) ｛=SUM(($G$2:$G$34<=VALUE(MID(I2,1,2)))*1)｝

或數組公式：

｛=SUM(($G$2:$G$34<=VALUE(MID(I3,4,2)))*1)-SUM($J$2:J2)｝

如何計算20-50歲的人數?

=COUNTIF(C3:C17,">=20")-COUNTIF(C3:C17,">50") =SUMPRODUCT((C3:C17>=20)*(C3:C17<=50))

=FREQUENCY(C3:C17,50)-FREQUENCY(C3:C17,19) ｛=SUM(COUNTIF(C3:C17,">="&{20,51})*{1,-1})｝