excel2007函數(shù)技巧大全

1、Excel2007函數(shù)公式收集了688個(gè)實(shí)例，涉及到137個(gè)函數(shù)、7個(gè)行業(yè)、41類用途，為大家提供一個(gè)參考，拓展思路的機(jī)會(huì)。公式由包括的為數(shù)組公式，在復(fù)制粘貼到單元后先去掉然后按住Shift鍵Ctrl鍵再按Enter鍵，自動(dòng)生成數(shù)組公式。對(duì)三組生產(chǎn)數(shù)據(jù)求和:=SUM(B2:B7D2:D7F2:F7)對(duì)生產(chǎn)表中大于100的產(chǎn)量進(jìn)行求和:=SUM((B2:B11100)B2:B11)對(duì)生產(chǎn)表大于110或者小于100的數(shù)據(jù)求和:=SUM(((

2、B2:B11110))B2:B11)對(duì)一車間男性職工的工資求和:=SUM((B2:B10=“一車間“)(C2:C10=“男“)D2:D10)對(duì)姓趙的女職工工資求和:=SUM((LEFT(A2:A10)=“趙“)(C2:C10=“女“)D2:D10)求前三名產(chǎn)量之和:=SUM(LARGE(B2:B10123))求所有工作表相同區(qū)域數(shù)據(jù)之和:=SUM(A組:E組!B2:B9)求圖書訂購(gòu)價(jià)格總和:=SUM((B2:E2=參考價(jià)格!A$2:A$

3、7)參考價(jià)格!B$2:B$7)求當(dāng)前表以外的所有工作表相同區(qū)域的總和:=SUM(一月:五月!B2)用SUM函數(shù)計(jì)數(shù):=SUM((B2:B9=“男“)1)求1累加到100之和:=SUM(ROW(1:100))多個(gè)工作表不同區(qū)域求前三名產(chǎn)量和:=SUM(LARGE(CHOOSE(12345A組!B2:B9B組!B2:B9C組!B2:B9D組!B2:B9E組!B2:B9)ROW(1:3)))計(jì)算倉(cāng)庫(kù)進(jìn)庫(kù)數(shù)量之和:=SUMIF(B2:B10“=

4、進(jìn)庫(kù)“C2:C10)計(jì)算倉(cāng)庫(kù)大額進(jìn)庫(kù)數(shù)量之和:=SUMIF(B2:B8“1000“)對(duì)1400到1600之間的工資求和:=SUM(SUMIF(B2:B10““&LARGE(B2:B104))SUMIF(B2:B10“600“)只匯總60～80分的成績(jī):=SUMIFS(B2:B10B2:B10“=60“B2:B10“=TRANSPOSE(ROW(2:11)))B2:B11C2:C11)計(jì)算A產(chǎn)品每日庫(kù)存數(shù):=MMULT(N(ROW(2:1

5、7)=TRANSPOSE(ROW(2:17)))(B2:B17=“A“)(C2:C17D2:D17))求第一名人員最多有幾次:=MAX(MMULT(N(B2:B7=TRANSPOSE(B2:B7))ROW(2:7)^0))求幾號(hào)選手選票最多:=RIGHT(MAX(MMULT(N(B2:B10=TRANSPOSE(B2:B10))ROW(2:10)^0)100B2:B10))總共有幾個(gè)選手參選:=SUM(1(MMULT(N(B2:B10=

6、TRANSPOSE(B2:B10))ROW(2:10)^0)))在不同班級(jí)有同名前提下計(jì)算學(xué)生人數(shù):=SUM(1MMULT(N(A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17))ROW(2:17)^0))計(jì)算前進(jìn)中學(xué)參賽人數(shù):=SUM(IFERR(1MMULT(N((A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17))(A2:A

7、17=“前進(jìn)中學(xué)“))ROW(2:17)^0)0))串聯(lián)單元格中的數(shù)字:=MMULT(10^(COLUMNS(B:K)COLUMN(C:L))TRANSPOSE(B2:K2))或=SUMPRODUCT(B2:K210^(COLUMNS(B:K)COLUMN(B:K)1))計(jì)算達(dá)標(biāo)率:=MMULT(TRANSPOSE(N(A2:A1160)(B2:B1160)(B2:B1180))ROW(2:11)^0)匯總A組男職工的工資:=MMULT

8、(TRANSPOSE(N(B2:B11&C2:C11=“男A組“)D2:D11)ROW(2:11)^0)計(jì)算象棋比賽對(duì)局次數(shù)l:=COMBIN(B1B2)計(jì)算五項(xiàng)比賽對(duì)局總次數(shù):=SUM(COMBIN(B2:B52))預(yù)計(jì)所有賽事完成的時(shí)間:=COMBIN(B1B2)B3B460計(jì)算英文字母區(qū)分大小寫做密碼的組數(shù):=PERMUT(B12B2)計(jì)算中獎(jiǎng)率:=TEXT(1PERMUT(B1B2)“0.00%“)計(jì)算最大公約數(shù):=GCD(B1

9、:B5)計(jì)算最小公倍數(shù):=LCM(B1:B5)計(jì)算余數(shù):=MOD(A2B2)匯總奇數(shù)行數(shù)據(jù):=SUMPRODUCT(MOD(ROW(2:13)2)C2:C13)根據(jù)單價(jià)數(shù)量匯總金額:=SUMPRODUCT(MOD(COLUMN(A:I)2)A2:I2(MOD(COLUMN(B:J)2)=0)B2:J2)設(shè)計(jì)工資條:=IF(MOD(ROW()3)=1單行表頭工資明細(xì)!A$1IF(MOD(ROW()3)=2OFFSET(單行表頭工資明細(xì)!A