1、Good is good, but better carries it.精益求精，善益求善。Excel2007函數(shù)公式實(shí)例集-Excel2007函數(shù)公式實(shí)例集.txt我是天使，回不去天堂是因?yàn)轶w重的原因。別人裝處，我只好裝經(jīng)驗(yàn)豐富。泡妞就像掛QQ，每天哄她2個(gè)小時(shí)，很快就可以太陽了。Excel2007函數(shù)公式收集了688個(gè)實(shí)例，涉及到137個(gè)函數(shù)、7個(gè)行業(yè)、41類用途，為大家提供一個(gè)參考，拓展思路的機(jī)會(huì)。公式由包括的為數(shù)組公式，在復(fù)制粘貼到單元后先去掉然后按住Shift鍵+Ctrl鍵再按Enter鍵，自動(dòng)生成數(shù)組公式。對三組生產(chǎn)數(shù)據(jù)求和:=SUM(B2:B7,D2:D7,F2:F7)對生產(chǎn)表中

2、大于100的產(chǎn)量進(jìn)行求和:=SUM(B2:B11100)*B2:B11)對生產(chǎn)表大于110或者小于100的數(shù)據(jù)求和:=SUM(B2:B11110)*B2:B11)對一車間男性職工的工資求和:=SUM(B2:B10=一車間)*(C2:C10=男)*D2:D10)對姓趙的女職工工資求和:=SUM(LEFT(A2:A10)=趙)*(C2:C10=女)*D2:D10)求前三名產(chǎn)量之和:=SUM(LARGE(B2:B10,1,2,3)求所有工作表相同區(qū)域數(shù)據(jù)之和:=SUM(A組:E組!B2:B9)求圖書訂購價(jià)格總和:=SUM(B2:E2=參考價(jià)格!A$2:A$7)*參考價(jià)格!B$2:B$7)求當(dāng)前表以外

3、的所有工作表相同區(qū)域的總和:=SUM(一月:五月!B2)用SUM函數(shù)計(jì)數(shù):=SUM(B2:B9=男)*1)求1累加到100之和:=SUM(ROW(1:100)多個(gè)工作表不同區(qū)域求前三名產(chǎn)量和:=SUM(LARGE(CHOOSE(1,2,3,4,5,A組!B2:B9,B組!B2:B9,C組!B2:B9,D組!B2:B9,E組!B2:B9),ROW(1:3)計(jì)算倉庫進(jìn)庫數(shù)量之和:=SUMIF(B2:B10,=進(jìn)庫,C2:C10)計(jì)算倉庫大額進(jìn)庫數(shù)量之和:=SUMIF(B2:B8,1000)對1400到1600之間的工資求和:=SUM(SUMIF(B2:B10,&LARGE(B2:B10,4)+SU

4、MIF(B2:B10,600)只匯總6080分的成績:=SUMIFS(B2:B10,B2:B10,=60,B2:B10,25)*1)匯總一班人員獲獎(jiǎng)次數(shù):=SUMPRODUCT(B2:B11=一班)*C2:C11)匯總一車間男性參保人數(shù):=SUMPRODUCT(A2:A10&B2:B10&C2:C10=一車間男是)*1)匯總所有車間人員工資:=SUMPRODUCT(-NOT(ISERROR(FIND(車間,A2:A10),C2:C10)匯總業(yè)務(wù)員業(yè)績:=SUMPRODUCT(B2:B11=江西,廣東)*(C2:C11=男)*D2:D11)根據(jù)直角三角形之勾、股求其弦長:=POWER(SUMSQ

5、(B1,B2),1/2)計(jì)算A1：A10區(qū)域正數(shù)的平方和:=SUMSQ(IF(A1:A100,A1:A10)根據(jù)二邊長判斷三角形是否為直角三角形:=CHOOSE(SUMSQ(MAX(B1:B3)=SUMSQ(LARGE(B1:B3,2,3)+1,非直角,直角)計(jì)算1到10的自然數(shù)的積:=FACT(10)計(jì)算50到60之間的整數(shù)相乘的結(jié)果:=FACT(60)/FACT(49)計(jì)算1到15之間奇數(shù)相乘的結(jié)果:=FACTDOUBLE(15)計(jì)算每小時(shí)生產(chǎn)產(chǎn)值:=PRODUCT(C2:E2)根據(jù)三邊求普通三角形面積:=(PRODUCT(SUM(B1:B3)/2,SUM(B1:B3)/2-LARGE(B

6、1:B3,1,2,3)0.5根據(jù)直角三角形三邊求三角形面積:=PRODUCT(LARGE(B1:B3,2,3)/2跨表求積:=PRODUCT(產(chǎn)量表:單價(jià)表!B2)求不同單價(jià)下的利潤:=MMULT(B2:B10,G2:H2)*25%制作中文九九乘法表:=COLUMN()&*&ROW()&=&MMULT(ROW(),COLUMN()計(jì)算車間盈虧:=SUM(MMULT(B3:E50)*B3:E5,1;1;1;1),MMULT(B3:E5=TRANSPOSE(ROW(2:11),B2:B11)計(jì)算每日庫存數(shù):=MMULT(N(ROW(2:11)=TRANSPOSE(ROW(2:11),B2:B11-

7、C2:C11)計(jì)算A產(chǎn)品每日庫存數(shù):=MMULT(N(ROW(2:17)=TRANSPOSE(ROW(2:17),(B2:B17=A)*(C2:C17-D2: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)*100+B2:B10)總共有幾個(gè)選手參選:=SUM(1/(MMULT(N(B2:B10=TRANSPOSE(B2:B10),ROW(2:10)0)在不同班級(jí)有同名前提下計(jì)算學(xué)生人數(shù):=S

8、UM(1/MMULT(N(A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17),ROW(2:17)0)計(jì)算前進(jìn)中學(xué)參賽人數(shù):=SUM(IFERROR(1/MMULT(N(A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17)*(A2:A17=前進(jìn)中學(xué)),ROW(2:17)0),0)串聯(lián)單元格中的數(shù)字:=MMULT(10(COLUMNS(B:K)-COLUMN(C:L),TRANSPOSE(B2:K2)或=SUMPRODUCT(B2:K2,10(COLUMNS(B:K)-COLUMN(B:K)-

9、1)計(jì)算達(dá)標(biāo)率:=MMULT(TRANSPOSE(N(A2:A1160)*(B2:B1160)*(B2:B1180),ROW(2:11)0)匯總A組男職工的工資:=MMULT(TRANSPOSE(N(B2:B11&C2:C11=男A組)*D2:D11),ROW(2:11)0)計(jì)算象棋比賽對局次數(shù)l:=COMBIN(B1,B2)計(jì)算五項(xiàng)比賽對局總次數(shù):=SUM(COMBIN(B2:B5,2)預(yù)計(jì)所有賽事完成的時(shí)間:=COMBIN(B1,B2)*B3/B4/60計(jì)算英文字母區(qū)分大小寫做密碼的組數(shù):=PERMUT(B1*2,B2)計(jì)算中獎(jiǎng)率:=TEXT(1/PERMUT(B1,B2),0.00%)計(jì)

10、算最大公約數(shù):=GCD(B1:B5)計(jì)算最小公倍數(shù):=LCM(B1:B5)計(jì)算余數(shù):=MOD(A2,B2)匯總奇數(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$1,IF(MOD(ROW(),3)=2,OFFSET(單行表頭工資明細(xì)!A$1,ROW()/3+1,0),)根據(jù)身份證號(hào)計(jì)算性別:=IF(MOD(MID(B2,15,3),2),男

11、,女)每隔4行合計(jì)產(chǎn)值:=IF(MOD(ROW(),5)=1,SUM(OFFSET(F2,-4,4,),D2*E2)工資截尾取整:=B2+MOD(一月!B2,10)-MOD(B2+MOD(一月!B2,10),10)匯總3的倍數(shù)列的數(shù)據(jù):=SUM(IF(MOD(COLUMN(A:I),3)=0,A2:I10)將數(shù)值逐位相加成一位數(shù):=IF(A2=0,0,MOD(A2-1,9)+1)計(jì)算零鈔:5角=INT(MOD(SUM(B2:B10),1)/0.5);2角=INT(MOD(MOD(SUM(B2:B10),1),0.5)/0.2);1角=MOD(MOD(MOD(SUM(B2:B10),1),0.5

12、),0.2)/0.1秒與小時(shí)、分鐘的換算:=QUOTIENT(MOD($A2,IF(COLUMN()=2,A2+1,60(3-COLUMN(A:A)+1),60(3-COLUMN(A:A)生成隔行累加的序列:=QUOTIENT(ROW()+1,2)根據(jù)業(yè)績計(jì)算業(yè)務(wù)員獎(jiǎng)金:=CHOOSE(MIN(QUOTIENT(B2,10000)+1,6),0,3%,5%,7%,9%,11%)*B2計(jì)算預(yù)報(bào)溫度與實(shí)際溫度的最大誤差值:=MAX(ABS(C2:C8-B2:B8)計(jì)算個(gè)人所得稅:=ROUND(0.05*SUM(H2-1600-0,500,2000,5000,20000,40000,60000,80

13、000,100000+ABS(H2-1600-0,500,2000,5000,20000,40000,60000,80000,100000)/2,0)產(chǎn)生100到200之間帶小數(shù)的隨機(jī)數(shù):=RAND()*(200-100)+100產(chǎn)生ll到20之間的不重復(fù)隨機(jī)整數(shù):=RANK(A2:A11,A2:A11)+10將20個(gè)學(xué)生的考位隨機(jī)排列:=INDEX(A$2:A$11,RANK(H2:H11,H2:H11)將三個(gè)學(xué)校植樹人員隨機(jī)分組:=OFFSET(A$1,RANK(G2,G$2:G$11),)&:&OFFSET(B$1,RANK(G2,G$2:G$11),)&:&OFFSET(C$1,RAN

14、K(G2,G$2:G$11),)產(chǎn)生-50到100之間的隨機(jī)整數(shù):=RANDBETWEEN(-50,100)產(chǎn)生1到100之問的奇數(shù)隨機(jī)數(shù):=INDEX(IF(MOD(ROW(1:100),2),ROW(1:100),ROW(1:100)-1),RANDBETWEEN(1,100)產(chǎn)生1到10之間隨機(jī)不重復(fù)數(shù):=LARGE(IF(COUNTIF(A$1:A1,ROW($1:$10)=0,ROW($1:$10),RANDBETWEEN(1,12-ROW()根據(jù)三角形三邊長求證三角形是直角三角形:=IF(POWER(MAX(B1:B3),2)=SUM(POWER(LARGE(B1:B3,2,3),

15、2),是,不是)計(jì)算Al:A10區(qū)域開三次方之平均值:=AVERAGE(POWER(A1:A10,1/30)計(jì)算Al:A10區(qū)域倒數(shù)之積:=PRODUCT(POWER(A1:A10,-1)根據(jù)等邊三角形周長計(jì)算面積:=SQRT(B1/2*POWER(B1/2-B1/3,3)抽取奇數(shù)行姓名:=INDEX(B:B,ODD(RANDBETWEEN(1,ROWS(1:12)-1)統(tǒng)計(jì)A1:B10區(qū)域中奇數(shù)個(gè)數(shù):=SUMPRODUCT(N(ODD(A1:B10)=(A1:B10)統(tǒng)計(jì)參考人數(shù):=SUMPRODUCT(EVEN(COLUMN(A1:J12)=COLUMN(A1:J12)*(MOD(ROW(A1:J12),3)=1)*(A1:J12)計(jì)算A1:B10區(qū)域中偶數(shù)個(gè)數(shù):=SUMPRODUCT(N(EVEN(A1:B10)=(A1:B10)合計(jì)購物金額、保留一位小數(shù):=TRUNC(SUMPRODUCT(B2:B10,C2:C10),1)將每項(xiàng)購物金額保留一位小數(shù)再合計(jì):=SUMPRODUCT(TRUNC(B2:B10*C2:C10,1)將金額進(jìn)行四舍六入五