EXCEL常用計(jì)算公式大全

1、電子商務(wù)主講老師：李靜qq：695078981日常辦公中少不了EXCEL 表格的運(yùn)用，方便、快速、高效。EXCEL表格是基于計(jì)算公式而運(yùn)行的，只有掌握了基本公式，才能完成各項(xiàng)運(yùn)算。經(jīng)過(guò)長(zhǎng)時(shí)間收集，整理了一些常用工具，基本能夠滿足日常運(yùn)用。EXCEL常用計(jì)算公式大全EXCEL常用計(jì)算公式大全整理說(shuō)明 EXCEL 是日常辦公中少不了的運(yùn)算工具，方便、快速、高效。因?yàn)镋XCEL表格是基于計(jì)算公式而運(yùn)行的，只有掌握了基本公式，才能完成各項(xiàng)運(yùn)算。經(jīng)過(guò)長(zhǎng)時(shí)間收集，整理了一些常用工具，基本能夠滿足日常使用。公式由包括的為數(shù)組公式，在復(fù)制粘貼到單元后先去掉然后按住Shift鍵+Ctrl鍵再按Enter鍵，自動(dòng)

2、生成數(shù)組公式。目 錄常用求和運(yùn)算.31、 單組數(shù)據(jù)加減乘除運(yùn)算.32、 多組數(shù)據(jù)加減乘除運(yùn)算.33、 其它應(yīng)用函數(shù)代表.5和字篇.51、 求和.52、 與和.5產(chǎn)字篇.71、 生產(chǎn).72、 產(chǎn)量.73、 產(chǎn)品.8四、產(chǎn)值.105、 產(chǎn)生.10大小于篇.10一、大于.112、 小于.12數(shù)據(jù)篇.12工資篇.14價(jià)格篇.15計(jì)算篇.16庫(kù)房篇.27一、進(jìn)庫(kù).28二、出庫(kù).28三、庫(kù)存.28成字篇.28 一、成績(jī).28 二、與成.31金字篇.33一、金額.33二、獎(jiǎng)金.35時(shí)間篇.351、 時(shí)間.352、 年.373、 月.394、 日.41銷售篇 .44EXCEL常用計(jì)算公式大全常用求和運(yùn)算一、單

3、組數(shù)據(jù)加減乘除運(yùn)算：1 單組數(shù)據(jù)求加和公式：=（A1+B1） 舉例： 單元格A1：B1區(qū)域依次輸入了數(shù)據(jù)10和5， 計(jì)算： 在C1中輸入 =A1+B1 后點(diǎn)擊鍵盤(pán)“Enter（確定）”鍵后，該單元格就自動(dòng)顯示10與5的和15。2 單組數(shù)據(jù)求減差公式：=（A1-B1） 舉例：在C1中輸入 =A1-B1 即求10與5的差值5，電腦操作方法同上；3 單組數(shù)據(jù)求乘法公式：=（A1*B1） 舉例：在C1中輸入 =A1*B1 即求10與5的積值50，電腦操作方法同上； 4 單組數(shù)據(jù)求乘法公式：=（A1/B1） 舉例：在C1中輸入 =A1/B1 即求10與5的商值2，電腦操作方法同上；5 其它應(yīng)用： 在D1

4、中輸入 =A13 即求5的立方（三次方）； 在E1中輸入 =B1（1/3） 即求10的立方根小結(jié)： 在單元格輸入的含等號(hào)的運(yùn)算式，Excel中稱之為公式，都是數(shù)學(xué)里面的基本運(yùn)算，只不過(guò)在計(jì)算機(jī)上有的運(yùn)算符號(hào)發(fā)生了改變 “”與“*”同、“”與“/”同、“”與“乘方”相同，開(kāi)方作為乘方的逆運(yùn)算，把乘方中和指數(shù)使用成分?jǐn)?shù)就成了數(shù)的開(kāi)方運(yùn)算。這些符號(hào)是按住電腦鍵盤(pán)“Shift”鍵同時(shí)按住鍵盤(pán)第二排相對(duì)應(yīng)的數(shù)字符號(hào)即可顯示。如果同一列的其它單元格都需利用剛才的公式計(jì)算，只需要先用鼠標(biāo)左鍵點(diǎn)擊一下剛才已做好公式的單元格，將鼠標(biāo)移至該單元格的右下角，帶出現(xiàn)十字符號(hào)提示時(shí)，開(kāi)始按住鼠標(biāo)左鍵不動(dòng)一直沿著該單元格

5、依次往下拉到你需要的某行同一列的單元格下即可，即可完成公司自動(dòng)復(fù)制，自動(dòng)計(jì)算。二、多組數(shù)據(jù)加減乘除運(yùn)算：1 多組數(shù)據(jù)求加和公式：(常用) 舉例說(shuō)明：=SUM（A1:A10）,表示同一列縱向從A1到A10的所有數(shù)據(jù)相加； =SUM（A1:J1）,表示不同列橫向從A1到J1的所有第一行數(shù)據(jù)相加； 2 多組數(shù)據(jù)求乘積公式：（較常用） 舉例說(shuō)明：=PRODUCT（A1:J1）表示不同列從A1到J1的所有第一行數(shù)據(jù)相乘； =PRODUCT（A1:A10）表示同列從A1到A10的所有的該列數(shù)據(jù)相乘；3 多組數(shù)據(jù)求相減公式：(很少用) 舉例說(shuō)明：=A1-SUM（A2:A10）表示同一列縱向從A1到A10的所

6、有該列數(shù)據(jù)相減； =A1-SUM（B1:J1）表示不同列橫向從A1到J1的所有第一行數(shù)據(jù)相減；4 多組數(shù)據(jù)求除商公式：(極少用) 舉例說(shuō)明：=A1/PRODUCT（B1:J1）表示不同列從A1到J1的所有第一行數(shù)據(jù)相除； =A1/PRODUCT（A2:A10）表示同列從A1到A10的所有的該列數(shù)據(jù)相除；三、其它應(yīng)用函數(shù)代表： 1 平均函數(shù) =AVERAGE（:）； 2 最大值函數(shù) =MAX （:）； 3 最小值函數(shù) =MIN （:）； 4 統(tǒng)計(jì)函數(shù) =COUNTIF（:）：B5，”60”) 說(shuō)明：統(tǒng)計(jì)分?jǐn)?shù)大于60分的人數(shù)，注意，條件要加雙引號(hào)，在英文狀態(tài)下輸入。 和字篇一、求和 1、對(duì)三組生產(chǎn)

7、數(shù)據(jù)求和:=SUM(B2:B7,D2:D7,F2:F7)2、對(duì)生產(chǎn)表中大于100的產(chǎn)量進(jìn)行求和:=SUM(B2:B11100)*B2:B11)3、對(duì)生產(chǎn)表大于110或者小于100的數(shù)據(jù)求和:=SUM(B2:B11110)*B2:B11)4、對(duì)一車間男性職工的工資求和:=SUM(B2:B10=一車間)*(C2:C10=男)*D2:D10)5、 對(duì)姓趙的女職工工資求和:=SUM(LEFT(A2:A10)=趙)*(C2:C10=女)*D2:D10)6、 LEFT(left)：解釋，就是最左邊的 （最開(kāi)始的）如：石啟洪 最左邊就是“石”對(duì)應(yīng)RIGHT(right)最右邊的 （最后的）。7、 對(duì)1400

8、到1600之間的工資求和:=SUM(SUMIF(B2:B10,60)*(B2:B1160)*(B2:B1180),ROW(2:11)0)11、 既求積也求和:=IF(D2,PRODUCT(C2:D2),SUM(OFFSET(E2,-3,3)12、 按文字描述求和:=SUM(ISNUMBER(FIND(A$2:A$8,D2)*B$2:B$8)13、 求積、求和兩相宜:=SUM(IF(C2=,INDIRECT(E&LOOKUP(1,0/ISERROR(0/$C$1:C1=),ROW($C$2:C2)&:E&(ROW()-1),C2*D2)14、 將所有數(shù)據(jù)轉(zhuǎn)換成保留兩位小數(shù)再求和:=SUM(-TE

9、XT(B2:B11*C2:C11,0.00)15、 將所有數(shù)據(jù)轉(zhuǎn)換成保留兩位小數(shù)再求和:=SUM(-TEXT(B2:B11*C2:C11,0.00)2、 與和1、 求前三名產(chǎn)量之和:=SUM(LARGE(B2:B10,1,2,3)2、 求數(shù)組中最大值:=LARGE(B2:B10,1)3、 求所有工作表相同區(qū)域數(shù)據(jù)之和:=SUM(A組:E組!B2:B9)4、 求圖書(shū)訂購(gòu)價(jià)格總和:=SUM(B2:E2=參考價(jià)格!A$2:A$7)*參考價(jià)格!B$2:B$7)5、 求當(dāng)前表以外的所有工作表相同區(qū)域的總和:=SUM(一月:五月!B2)6、 用SUM函數(shù)計(jì)數(shù):=SUM(B2:B9=男)*1)7、 求1累加

10、到100之和:=SUM(ROW(1:100)8、 多個(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)9、 計(jì)算倉(cāng)庫(kù)進(jìn)庫(kù)數(shù)量之和:=SUMIF(B2:B10,=進(jìn)庫(kù),C2:C10)10、 計(jì)算倉(cāng)庫(kù)大額進(jìn)庫(kù)數(shù)量之和:=SUMIF(B2:B8,1000)11、 求前三名和后三名的數(shù)據(jù)之和:=SUMIF(B2:B10,&LARGE(B2:B10,4)+SUMIF(B2:B10,0,A1:A10)13、 分別統(tǒng)計(jì)收入和支出:收入=SUM(IF(B2:B130

11、,B2:B13);支出=SUM(IF(SUBSTITUTE(IF(B2:B13,B2:B13,0),負(fù),-)*1LARGE(B2:C11,11)*B2:C11)22、 計(jì)算8月份筆筒和毛筆的進(jìn)貨數(shù)量:=SUM(IF(MONTH(A2:A11)=8,IF(B1:H1=筆筒)+(B1:H1=毛筆),B2:H11)23、 統(tǒng)計(jì)家具類和文具類產(chǎn)品在1月份的出庫(kù)次數(shù):=SUM(B2:B11=文具類,家具類)*(IF(C2:C110,MONTH(C2:C11)=1)24、 計(jì)算員工工作天數(shù)和月數(shù):=DATEDIF(B2,C2,M)25、 對(duì)班級(jí)和成績(jī)升序排列:=1*MID(SMALL(1*($A2:$A1

12、2&TEXT($B2:$B12,000),ROW($A$2:$A$12)-1),1,2,1,3)26、 根據(jù)下拉列表中的時(shí)間和產(chǎn)品名計(jì)算銷量冠軍:=INDEX(A2:A11,MATCH(MAX(OFFSET(C2,MATCH(J2,C1:H1,0)-1,ROWS(2:11),)*(B2:B11=K2),OFFSET(C2,MATCH(J2,C1:H1,0)-1,ROWS(2:11),)*(B2:B11=K2),0)27、 分別計(jì)算每個(gè)班第一名的成績(jī)和姓名:名次=MAX(SUBTOTAL(9,OFFSET(B$1,ROW($2:$31)-1,1,COLUMNS(C:I)*(B$2:B$31=K2

13、);名=OFFSET(A$1,MOD(MAX(SUBTOTAL(9,OFFSET(B$1,ROW($2:$31)-1,1,COLUMNS(C:I)*1000+ROW($2:$31)*(B$2:B$31=K2),1000)-1,)28、 根據(jù)評(píng)委評(píng)分和權(quán)重分配統(tǒng)計(jì)最后得分:=SUM(B2:F8*(A2:A8=B10)*TRANSPOSE(I2:I6)29、 區(qū)分工種和達(dá)標(biāo)率計(jì)算獎(jiǎng)金:=LOOKUP(C2*100,1*LEFT(達(dá)標(biāo)與獎(jiǎng)金標(biāo)準(zhǔn)!B$1:K$1,FIND(%,達(dá)標(biāo)與獎(jiǎng)金標(biāo)準(zhǔn)!B$1:K$1)-1),OFFSET(達(dá)標(biāo)與獎(jiǎng)金標(biāo)準(zhǔn)!B$1,MATCH(B2,達(dá)標(biāo)與獎(jiǎng)金標(biāo)準(zhǔn)!A$2:A$4

14、,0),10)30、 提取每日累計(jì)出庫(kù)數(shù)和每日庫(kù)存數(shù):日期=INDEX(A:A,ROW(A1)*2);累計(jì)出庫(kù)數(shù)=SUM(ISODD(ROW(INDIRECT(2:&(ROW(A1)*2)+1)*OFFSET(C$1,1,ROWS($1:1)*2);每日庫(kù)存數(shù)=SUM(SUMIF(OFFSET(B$1,1,ROW(A1)*2),進(jìn)庫(kù),出庫(kù),C$2)*1,-1)31、 計(jì)算生產(chǎn)部人數(shù)和非生產(chǎn)部人數(shù):生產(chǎn)部人數(shù)=SUM(NOT(ISERR(FIND(車間,A2:A11)*B2:C11);非生產(chǎn)部人數(shù)=SUM(ISERR(FIND(車間,A2:A11)*B2:C11)32、 根據(jù)利息和存款數(shù)計(jì)算存款

15、達(dá)到1萬(wàn)元需要幾個(gè)月:=NPER(A2,0,-B2,C2)*1233、 根據(jù)投資金額、時(shí)間和目標(biāo)收益計(jì)算增長(zhǎng)率:=RATE(B2,0,-A2,C2)34、 根據(jù)貸款、利率和時(shí)間計(jì)算某段時(shí)間的利息:=CUMIPMT(B2/12,C2*12,A2,1,24,0)35、 根據(jù)貸款、利率和時(shí)間計(jì)算需償還的本金:=CUMPRINC(B2/12,C2*12,A2,1,24,0)36、 以年限總和折舊法計(jì)算折舊值:=SYD(A$2,B$2,C$2,ROW(A1)產(chǎn)字篇一、生產(chǎn)1、 對(duì)三組生產(chǎn)數(shù)據(jù)求和:=SUM(B2:B7,D2:D7,F2:F7)2、 對(duì)生產(chǎn)表中大于100的產(chǎn)量進(jìn)行求和:=SUM(B2:B1

16、1100)*B2:B11)3、 對(duì)生產(chǎn)表大于110或者小于100的數(shù)據(jù)求和:=SUM(B2:B11110)*B2:B11)4、 計(jì)算每小時(shí)生產(chǎn)產(chǎn)值:=PRODUCT(C2:E2)5、 生產(chǎn)A產(chǎn)品且無(wú)異常的機(jī)臺(tái)平均產(chǎn)量:=AVERAGEIFS(C2:C11,B2:B11,A,D2:D11,)6、 根據(jù)員工生產(chǎn)產(chǎn)品的廢品率記分:=MAX(MIN(6-(B2*100-5),10),0)7、 哪種產(chǎn)品生產(chǎn)次數(shù)最多:=TEXT(MODE(B2:B9*1),00)8、 本月需要完成幾批貨物生產(chǎn):=SUM(N(B2:B11=TEXT(TODAY(),MMMM)9、 計(jì)算生產(chǎn)車間異常機(jī)臺(tái)個(gè)數(shù):=COUNT(C

17、2:C11)10、 計(jì)算生產(chǎn)速度是否達(dá)標(biāo):=YEARFRAC(C2,D2)250)6、 統(tǒng)計(jì)產(chǎn)量達(dá)標(biāo)率:=TEXT(COUNTIF(B2:B11,=800)/COUNT(B2:B11),0.00)7、 計(jì)算大于等于前10個(gè)最大產(chǎn)量之和:=SUMPRODUCT(B2:C11LARGE(B2:C11,11)*B2:C11)8、 按周匯總產(chǎn)量:=SUM(WEEKDAY($B1,2)-WEEKDAY($B1:$AF1,2)+(COLUMN($B1:$AF1)-1)=(1+(COLUMN(A1)-1)*7)*$B2:$AF2)9、 計(jì)算所有人的一周產(chǎn)量并排名:=INDEX(1:1,RIGHT(LARGE

18、(SUBTOTAL(9,OFFSET($A2:$A8,COLUMN($B:$J)-1,)*10+COLUMN($B:$J)-1,COLUMN(A1)+1)10、 根據(jù)下拉列表引用不同工作表的產(chǎn)量:=INDIRECT(ADDRESS(11,2,1,1,D1)11、 計(jì)算連續(xù)三天之總產(chǎn)量大于等于25萬(wàn)元的次13、 計(jì)算產(chǎn)量最高的季度:=TEXT(MATCH(MAX(SUBTOTAL(9,OFFSET(A1,0,3,6,9,1,3),SUBTOTAL(9,OFFSET(A1,0,3,6,9,1,3),0),DBNum10季度)14、 羅列12月中產(chǎn)量倒數(shù)第一名次數(shù)最多者名單:=INDEX(B:B,S

19、MALL(IF(COUNTIF(B$2:B$13,B$2:B$13)=MAX(COUNTIF($B$2:$B$13,$B$2:$B$13)*(MATCH($B$2:$B$13,$B$2:$B$13,0)=ROW($2:$13)-1),ROW($2:$13),1048576),ROW(A1)&15、 根據(jù)產(chǎn)量計(jì)算員工產(chǎn)量得分:=LOOKUP(B2,3,0.5*(ROW($1:$11)-1)16、 提取產(chǎn)量冠軍的組別:=IF(COUNTA(B2:E2),LOOKUP(1,0/ISTEXT(B2:E2),B$1:E$1),)17、 建立文件目錄:=HYPERLINK(E:產(chǎn)量表&TEXT(ROW(1

20、:1),DBNum1)&月產(chǎn)量表.xlsxsheet1!A1,TEXT(ROW(1:1),DBNum1)&月產(chǎn)量表)18、 選擇產(chǎn)量最高工作表:=HYPERLINK(#&CHAR(64+MOD(MAX(SUBTOTAL(9,INDIRECT(CHAR(64+ROW(1:8)&組!B2:B11)*100+ROW(1:8),100)&組!A1,跳至最大產(chǎn)量組)19、 計(jì)算連續(xù)三天之總產(chǎn)量大于等于25萬(wàn)元的次數(shù):=SUMPRODUCT(N(SUBTOTAL(9,OFFSET($B$1,ROW(1:10)-1,3)=25)三、產(chǎn)品1、 求入庫(kù)最多的產(chǎn)品數(shù)量:=MAX(MMULT(TRANSPOSE(B

21、2:B11)*(A2:A11=A,B,C,D),(A2:A11=A,B,C,D)*1)2、 計(jì)算A產(chǎn)品每日庫(kù)存數(shù):=MMULT(N(ROW(2:17)=TRANSPOSE(ROW(2:17),(B2:B17=A)*(C2:C17-D2:D17)3、 計(jì)算C產(chǎn)品最大入庫(kù)量:=MAX(MMULT(N(A2:A11=C),TRANSPOSE(B2:B11)*(A2:A11=C)4、 根據(jù)產(chǎn)品規(guī)格計(jì)算產(chǎn)品體積:=PRODUCT(LEFT(B2,FIND(*,B2)-1),MID(B2,FIND(*,B2)+1,FIND(*,B2,FIND(*,B2)+1)-1-FIND(*,B2),RIGHT(B2,

22、LEN(B2)-FIND(*,B2,FIND(*,B2)+1)5、 將產(chǎn)品型號(hào)規(guī)范化:=IF(MID(A2,5,2)=00,A2,REPLACE(A2,5,00)6、 產(chǎn)品規(guī)格格式轉(zhuǎn)換:=SUBSTITUTE(SUBSTITUTE(A2,：,(),*,)*)&)7、 統(tǒng)計(jì)未檢驗(yàn)完成的產(chǎn)品數(shù):=COUNTBLANK(B2:B11)8、 統(tǒng)計(jì)季度最高產(chǎn)值合計(jì):=MAX(SUBTOTAL(9,OFFSET(B2,COLUMN(B:E)-2,ROWS(2:10),1)9、 提取產(chǎn)品最后報(bào)價(jià)和最高報(bào)價(jià):=INDEX(C:C,MAX(A2:A11=B)*ROW(2:11)10、 計(jì)算文具類產(chǎn)品和家具類產(chǎn)品

23、最小利率:=TEXT(MIN(IF(ISNUMBER(SEARCH(?具類,A2:A11),B2:B11),0.00%)11、 統(tǒng)計(jì)售價(jià)850元以上的產(chǎn)品最低利率是多少:=DMIN(A1:D11,F4,F1:F2)12、 統(tǒng)計(jì)文具類和廚具類產(chǎn)品的最低單價(jià):=DMIN(A1:B11,2,D1:D2)13、 查看產(chǎn)品曾經(jīng)銷售的所有價(jià)位:=IF(ROW(A1)SUM(1/COUNTIF(B$2:C$11,B$2:C$11),SMALL(B$2:C$11,1+COUNTIF(B$2:C$11,0,MONTH(C2:C11)=1)16、 根據(jù)下拉列表中的時(shí)間和產(chǎn)品名計(jì)算銷量冠軍:=INDEX(A2:A1

24、1,MATCH(MAX(OFFSET(C2,MATCH(J2,C1:H1,0)-1,ROWS(2:11),)*(B2:B11=K2),OFFSET(C2,MATCH(J2,C1:H1,0)-1,ROWS(2:11),)*(B2:B11=K2),0)17、 根據(jù)下拉列表中的產(chǎn)品提取姓名與銷量:=IFERROR(1/MOD(SMALL(IF(B2:B11=K1,1/SUBTOTAL(9,OFFSET(C2,ROW(2:11)-2,0,1,COLUMNS(C:H)+ROW(2:11),ROW(1:10),1),)18、 從多個(gè)產(chǎn)品相同單價(jià)的單價(jià)表中引用單價(jià):=SUMPRODUCT(COUNTIF(O

25、FFSET(A$2,ROW($2:$4)-2,0,1,4),G2)*E$2:E$4)*H220、 區(qū)分大小寫(xiě)提取產(chǎn)品單價(jià):=MMULT(EXACT(B2:B11,TRANSPOSE(單價(jià)表!A2:A5)*TRANSPOSE(單價(jià)表!B2:B5),1;1;1;1)21、 羅列導(dǎo)致產(chǎn)品不良的主因:=IFERROR(T(INDEX($A:$A,SMALL(IF($B$2:$B$11=LARGE(IF(FREQUENCY($B$2:$B$11,$B$2:$B$11),$B$2:$B$11),ROW(A1),ROW($2:$11),COLUMN(A1),)22、 計(jì)算兩個(gè)產(chǎn)品不同時(shí)期的單價(jià):=HLOOK

26、UP(MONTH(A2),IF(B2=塑膠機(jī),0,3,8;25,19,18,0,5,10;12.5,10,11),2)23、 產(chǎn)生混合編號(hào):=TEXT(COUNTIF(C$1:C1,*),DBNum2)&TEXT(ROW()-MATCH(,C$1:C1),(000);)24、 計(jì)算產(chǎn)品體積:=IF(ISERROR(FIND(/,B2),B23,PRODUCT(1*TRIM(MID(SUBSTITUTE(B2,/,REPT( ,100),1,100,200,100)25、 按月份統(tǒng)計(jì)每個(gè)產(chǎn)品的機(jī)器返修數(shù)量:=SUMPRODUCT(ISNUMBER(FIND(F$2,$A$2:$A$11)*(TE

27、XT($B$2:$B$11,YM)=TEXT($E3,YM)*$C$2:$C$11)26、 從產(chǎn)品規(guī)格中提取直徑、長(zhǎng)、寬:長(zhǎng)(直徑)=LOOKUP(9.9E+307,-RIGHT(IF(ISNUMBER(FIND(,A2),REPLACE(A2,FIND(,A2),100,),A2),ROW($1:$100);寬=IF(ISNUMBER(FIND(,A2),-RIGHT(A2,LEN(A2)-FIND(,A2),0)27、 根據(jù)產(chǎn)品規(guī)格計(jì)算體積:體積=EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!XFD4,(L),*),(W),*),(H),

28、)禁止錄入不完整的產(chǎn)品規(guī)格:數(shù)據(jù)有效性設(shè)置-自定義:=ISNUMBER(SEARCH(長(zhǎng)?*寬?*高?*,B2)四、產(chǎn)值1、 計(jì)算每小時(shí)生產(chǎn)產(chǎn)值:=PRODUCT(C2:E2)2、 每隔4行合計(jì)產(chǎn)值:=IF(MOD(ROW(),5)=1,SUM(OFFSET(F2,-4,4,),D2*E2)五、產(chǎn)生1、 產(chǎn)生100到200之間帶小數(shù)的隨機(jī)數(shù):=RAND()*(200-100)+1002、 產(chǎn)生ll到20之間的不重復(fù)隨機(jī)整數(shù):=RANK(A2:A11,A2:A11)+103、 產(chǎn)生-50到100之間的隨機(jī)整數(shù):=RANDBETWEEN(-50,100)4、 產(chǎn)生1到100之問(wèn)的奇數(shù)隨機(jī)數(shù):=IN

29、DEX(IF(MOD(ROW(1:100),2),ROW(1:100),ROW(1:100)-1),RANDBETWEEN(1,100)5、 產(chǎn)生1到10之間隨機(jī)不重復(fù)數(shù):=LARGE(IF(COUNTIF(A$1:A1,ROW($1:$10)=0,ROW($1:$10),RANDBETWEEN(1,12-ROW()6、 根據(jù)學(xué)生成績(jī)自動(dòng)產(chǎn)生評(píng)語(yǔ):=IF(AVERAGE(B2:D2)60,不及格,IF(AVERAGE(B2:D2)90,良好,IF(AVERAGE(B2:D2)100,優(yōu)秀,滿分)7、 產(chǎn)生大、小寫(xiě)字母A到Z的序列:大寫(xiě)字母=CHAR(ROW(A65)，小寫(xiě)字母=CHAR(ROW

30、(A65)+32)8、 產(chǎn)生大寫(xiě)字母A到ZZ的字母序列:=IF(ROW()26,CHAR(MOD(ROW()-1,26)+65),)9、 產(chǎn)生三個(gè)字母組成的隨機(jī)字符串:=CHAR(RANDBETWEEN(65,90)&CHAR(RANDBETWEEN(65,90)&CHAR(RANDBETWEEN(65,90)10、 用公式產(chǎn)生換行符:=A2&CHAR(10)&B211、 在A列產(chǎn)生1到12月的英文月份名:=TEXT(ROW()&-1,mmmm)12、 在A1:A7區(qū)域產(chǎn)生星期一到星期日的英文全稱:=TEXT(ROW(1:7)+1,DDDD)13、 產(chǎn)生“坐標(biāo)”:=CHAR(64+COLUMN

31、(A1)14、 產(chǎn)生每?jī)尚欣奂?的編號(hào):=IF(ROW()=1,1,IF(MOD(ROW(),3),COUNT(OFFSET(A$1,ROW()-1)+1,)15、 計(jì)算成績(jī)排名，不能產(chǎn)生并列名次:=SUMPRODUCT(-(A$2:A$15=A2)*($C$2:$C$15)+1/ROW($C$2:$C$15)C2+1/ROW(2:2)+116、 在具有合并單元格的A列產(chǎn)生自然數(shù)編號(hào):=1+COUNT(OFFSET($A$2,ROW()-2,)17、 引用合并區(qū)域時(shí)防止產(chǎn)生0值:=IF(A1,A1,OFFSET(B1,-1,)18、 以固定余額遞減法計(jì)算資產(chǎn)折舊值:=DB(A$2,B$2,C$

32、2,ROW(A1),12)19、 以雙倍余額遞減法計(jì)算資產(chǎn)折舊值:=DDB(A$2,B$2,C$2,1,2)20、 使用雙倍余額遞減法計(jì)算任何期間的資產(chǎn)折舊值:=VDB(A$2,B$2,C$2*12,7,12,2)21、 以超產(chǎn)80為單位計(jì)算超產(chǎn)獎(jiǎng):=SUM(MROUND(B2:B11-700,80*IF(B2:B11=700,1,-1)/80*50大小于篇一、大于1、 對(duì)生產(chǎn)表中大于100的產(chǎn)量進(jìn)行求和:=SUM(B2:B11100)*B2:B11)2、 對(duì)生產(chǎn)表大于110或者小于100的數(shù)據(jù)求和:=SUM(B2:B11110)*B2:B11)3、 匯總鼠標(biāo)所在列中大于600的數(shù)據(jù):=SUM

33、IF(INDIRECT(R2C&CELL(col)&:R8C&CELL(col),FALSE),600)4、 判斷單元格的數(shù)據(jù)類型:=TEXT(A2,大于;小于;文本)5、 計(jì)算每個(gè)車間大于250的平均產(chǎn)量:=AVERAGEIF(B2:C11,250)6、 統(tǒng)計(jì)大于80分的三好學(xué)生個(gè)數(shù):=COUNTIFS(B2:B11,三好學(xué)生,C2:C11,80)7、 計(jì)算語(yǔ)文成績(jī)大于90分者的最高總成績(jī):=DMAX(A1:E11,5,G1:G2)8、 計(jì)算大于等于前10個(gè)最大產(chǎn)量之和:=SUMPRODUCT(B2:C11LARGE(B2:C11,11)*B2:C11)9、 計(jì)算第一次收入金額大于30元時(shí)的

34、金額是多少:=INDEX(B:B,MIN(IF(A2:A11=A2)*(B2:B1130),ROW(2:11)10、 記錄最后一次銷量大于3000的地址:=ADDRESS(MOD(MAX(IF(ISNUMBER(B2:D7),B2:D7,0)3000)*ROW(B2:D7)+(IF(ISNUMBER(B2:D7),B2:D7,0)3000)*COLUMN(B2:D7)*1000),1000),INT(MAX(IF(ISNUMBER(B2:D7),B2:D7,0)3000)*ROW(B2:D7)+(IF(ISNUMBER(B2:D7),B2:D7,0)3000)*COLUMN(B2:D7)*10

35、00)/1000)11、 計(jì)第奎續(xù)三天之總產(chǎn)量大于等于25萬(wàn)元的次數(shù):=SUMPRODUCT(N(SUBTOTAL(9,OFFSET($B$1,ROW(1:10)-1,3)=25)12、 統(tǒng)計(jì)各班所有科目成績(jī)大于60分者人數(shù):=MMULT(N(TRANSPOSE(A2:A21)=H3:H6),N(COUNTIF(OFFSET(C2:F2,ROW(2:21)-2,),=60)=4)13、 對(duì)生產(chǎn)表大于110或者小于100的數(shù)據(jù)求和:=SUM(B2:B11110)*B2:B11)3、 小于1、 判斷單元格的數(shù)據(jù)類型:=TEXT(A2,大于;小于;文本)2、 對(duì)生產(chǎn)表大于110或者小于100的數(shù)據(jù)求

36、和:=SUM(B2:B11110)*B2:B11)數(shù)據(jù)篇1、 對(duì)三組生產(chǎn)數(shù)據(jù)求和:=SUM(B2:B7,D2:D7,F2:F7)2、 對(duì)生產(chǎn)表大于110或者小于100的數(shù)據(jù)求和:=SUM(B2:B11110)*B2:B11)37、 求所有工作表相同區(qū)域數(shù)據(jù)求和:=SUM(A組:E組!B2:B9)3、 求前三名和后三名的數(shù)據(jù)求和:=SUMIF(B2:B10,&LARGE(B2:B10,4)+SUMIF(B2:B10,600)5、 匯總奇數(shù)行數(shù)據(jù):=SUMPRODUCT(MOD(ROW(2:13),2)*C2:C13)6、 匯總3的倍數(shù)列的數(shù)據(jù):=SUM(IF(MOD(COLUMN(A:I),3)

37、=0,A2:I10)7、 將數(shù)據(jù)轉(zhuǎn)換成接近6的倍數(shù):=MROUND(A1,6)8、 計(jì)算值為l萬(wàn)的整數(shù)倍數(shù)的數(shù)據(jù)個(gè)數(shù):=SUM(N(B2:B10*C2:C10)=ROUNDDOWN(B2:B10*C2:C10,-4)9、 判斷兩列數(shù)據(jù)是否相等:=IF(A1=B1,相等,)10、 計(jì)算兩列數(shù)據(jù)同行相等的個(gè)數(shù):=SUM(N(A1:A10=B1:B111、 有選擇地匯總數(shù)據(jù):=SUM(IF(A2:A11=A組,C組,C2:C11)12、 返回自動(dòng)換行單元格的第二行數(shù)據(jù):=RIGHT(A2,LEN(A2)-FIND(CHAR(10),A2)13、 將所有數(shù)據(jù)轉(zhuǎn)換成保留兩位小數(shù)再求和:=SUM(-TEX

38、T(B2:B11*C2:C11,0.00)14、 將數(shù)據(jù)顯示為小數(shù)點(diǎn)對(duì)齊:=TEXT(B2,#.0?)15、 判斷單元格的數(shù)據(jù)類型:=TEXT(A2,大于;小于;文本)16、 將三列數(shù)據(jù)交換位置:=TEXT(1,-1,0,C1:C5&;&!&B1:B5&;&A1:A5)17、 將數(shù)據(jù)重復(fù)顯示5次:=SUBSTITUTE(TEXT(A2&?,),?,)18、 將數(shù)據(jù)對(duì)齊顯示，將空白以“.”占位:=WIDECHAR(REPT(.,10-LEN(B2)&B2)19、 從卡機(jī)數(shù)據(jù)提取打卡時(shí)間:=730-MID(A2,14,4)20、 根據(jù)卡機(jī)數(shù)據(jù)判斷員工部門(mén):=CHOOSE(MATCH(-RIGHT(

39、A2,3),1,38,14,11,8,21,43,9,28,0),生產(chǎn)部,業(yè)務(wù)部,總務(wù)部,人事部,食堂,保衛(wèi)部,采購(gòu)部,送貨部,財(cái)務(wù)部)21、 從漢字與數(shù)字混合字串中提取溫度數(shù)據(jù):=MAX(IFERROR(-RIGHT(LEFT(B2,LEN(B2)-1),ROW($1:$10),0)22、 計(jì)算兩列數(shù)據(jù)相同個(gè)數(shù):=SUM(COUNTIF(A2:A11,B2:B11)23、 統(tǒng)計(jì)區(qū)域中不重復(fù)數(shù)據(jù)個(gè)數(shù):=SUM(1/COUNTIF(B2:B8,B2:B8)24、 提取不重復(fù)數(shù)據(jù):=INDEX(B:B,MATCH(0,COUNTIF($D$1:D1,B$2:B$11),0)+1)25、 消除單位提

40、取數(shù)據(jù):=MAX(IFERROR(ABS(LEFT(A2,ROW($1:$100),)*IF(LEFT(A2)=-,-1,1)26、 提取引用區(qū)域右下角的數(shù)據(jù):=INDIRECT(ADDRESS(ROW(B3:D7)+ROWS(B3:D7)-1,COLUMN(B3:D7)+COLUMNS(B3:D7)-1)27、 合并三個(gè)工作表的數(shù)據(jù):=INDIRECT(CHOOSE(MOD(ROW(A2)-1,3)+1,一年級(jí)!A&INT(ROW(A3)/3)+1,二年級(jí)!A&INT(ROW(A3)/3)+1,三年級(jí)!A&INT(ROW(A3)/3)+1)28、 累計(jì)數(shù)據(jù):=SUM(OFFSET(B$2,R

41、OW()-1)29、 多列、隔行數(shù)據(jù)匯總:=SUM(MMULT(D2:G11,TRANSPOSE(COLUMN(D:G)0)*(A2:A11=趙還珠)30、 從電話簿中選擇性引用數(shù)據(jù):=INDEX($A:$B,ROW(A1)*3-2,COLUMN(A:A)31、 插入空行分割數(shù)據(jù):=IF(MOD(ROW(),3)0,INDEX(A:A,ROW(A2)*2/3),)32、 在同一行查找數(shù)據(jù):=HLOOKUP(MAX(A2:H2),IF(1;0,B2:H2,A2:G2),2,FALSE)33、 不區(qū)分大小寫(xiě)判斷兩列相同數(shù)據(jù)個(gè)數(shù):=COUNT(MATCH(A2:A11,B2:B11,0)34、 提取A列最后一個(gè)數(shù)據(jù):=INDIRECT(A&(MATCH(1,0/(A:A)35、 利用公式對(duì)入庫(kù)表進(jìn)行數(shù)據(jù)分析:=INDEX(B:B,SMALL(IF(MATCH(B$2:B$200,B$2:B$200,0)=ROW($2:$200)-1,ROW($2:$200),65536),ROW(A1)&36