excel2007函數(shù)公式實(shí)例匯總

1、ExcelExcel20072007函數(shù)公式實(shí)例匯總函數(shù)公式實(shí)例匯總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

2、:B11)對(duì)生產(chǎn)表大于110或者小于100的數(shù)據(jù)求和:=SUM(((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

3、)求圖書訂購價(jià)格總和:=SUM((B2:E2=參考價(jià)格!A$2:A$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)RO

4、W(1:3)))計(jì)算倉庫進(jìn)庫數(shù)量之和:=SUMIF(B2:B10“=進(jìn)庫“C2:C10)計(jì)算倉庫大額進(jìn)庫數(shù)量之和:=SUMIF(B2:B8“1000“)對(duì)1400到1600之間的工資求和:=SUM(SUMIF(B2:B10“0)B3:E51111)MMULT((B3:E5=TRANSPOSE(ROW(2:11)))B2:B11)計(jì)算每日庫存數(shù):=MMULT(N(ROW(2:11)=TRANSPOSE(ROW(2:11)))B2:B11C

5、2:C11)計(jì)算A產(chǎn)品每日庫存數(shù):=MMULT(N(ROW(2:17)=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)