Excel2007函數(shù)公式實例匯總.docx

Excel 2007函數(shù)公式實例匯總 Excel2007函數(shù)公式收集了688個實例，涉及到137個函數(shù)、7個行業(yè)、41類用途，為大家提供一個參考，拓展思路的機會。公式由包括的為數(shù)組公式，在復制粘貼到單元后先去掉然后按住Shift鍵+Ctrl鍵再按Enter鍵，自動生成數(shù)組公式。對三組生產(chǎn)數(shù)據(jù)求和:=SUM(B2:B7,D2:D7,F2:F7) 對生產(chǎn)表中大于100的產(chǎ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)求圖書訂購價格總和:=SUM(B2:E2=參考價格!A$2:A$7)*參考價格!B$2:B$7)求當前表以外的所有工作表相同區(qū)域的總和:=SUM(一月:五月!B2)用SUM函數(shù)計數(shù):=SUM(B2:B9=男)*1)求1累加到100之和:=SUM(ROW(1:100)多個工作表不同區(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)計算倉庫進庫數(shù)量之和:=SUMIF(B2:B10,=進庫,C2:C10)計算倉庫大額進庫數(shù)量之和:=SUMIF(B2:B8,1000)對1400到1600之間的工資求和:=SUM(SUMIF(B2:B10,&LARGE(B2:B10,4)+SUMIF(B2:B10,600)只匯總6080分的成績:=SUMIFS(B2:B10,B2:B10,=60,B2:B10,25)*1)匯總一班人員獲獎次數(shù):=SUMPRODUCT(B2:B11=一班)*C2:C11)匯總一車間男性參保人數(shù):=SUMPRODUCT(A2:A10&B2:B10&C2:C10=一車間男是)*1)匯總所有車間人員工資:=SUMPRODUCT(-NOT(ISERROR(FIND(車間,A2:A10),C2:C10)匯總業(yè)務員業(yè)績:=SUMPRODUCT(B2:B11=江西,廣東)*(C2:C11=男)*D2:D11)根據(jù)直角三角形之勾、股求其弦長:=POWER(SUMSQ(B1,B2),1/2)計算A1：A10區(qū)域正數(shù)的平方和:=SUMSQ(IF(A1:A100,A1:A10)根據(jù)二邊長判斷三角形是否為直角三角形:=CHOOSE(SUMSQ(MAX(B1:B3)=SUMSQ(LARGE(B1:B3,2,3)+1,非直角,直角)計算1到10的自然數(shù)的積:=FACT(10)計算50到60之間的整數(shù)相乘的結(jié)果:=FACT(60)/FACT(49)計算1到15之間奇數(shù)相乘的結(jié)果:=FACTDOUBLE(15)計算每小時生產(chǎn)產(chǎn)值:=PRODUCT(C2:E2)根據(jù)三邊求普通三角形面積:=(PRODUCT(SUM(B1:B3)/2,SUM(B1:B3)/2-LARGE(B1:B3,1,2,3)0.5根據(jù)直角三角形三邊求三角形面積:=PRODUCT(LARGE(B1:B3,2,3)/2跨表求積:=PRODUCT(產(chǎn)量表:單價表!B2)求不同單價下的利潤:=MMULT(B2:B10,G2:H2)*25%制作中文九九乘法表:=COLUMN()&*&ROW()&=&MMULT(ROW(),COLUMN()計算車間盈虧:=SUM(MMULT(B3:E50)*B3:E5,1;1;1;1),MMULT(B3:E5=TRANSPOSE(ROW(2:11),B2:B11)計算每日庫存數(shù):=MMULT(N(ROW(2:11)=TRANSPOSE(ROW(2:11),B2:B11-C2:C11)計算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)求幾號選手選票最多:=RIGHT(MAX(MMULT(N(B2:B10=TRANSPOSE(B2:B10),ROW(2:10)0)*100+B2:B10)總共有幾個選手參選:=SUM(1/(MMULT(N(B2:B10=TRANSPOSE(B2:B10),ROW(2:10)0)在不同班級有同名前提下計算學生人數(shù):=SUM(1/MMULT(N(A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17),ROW(2:17)0)計 算前進中學參賽人數(shù):=SUM(IFERROR(1/MMULT(N(A2:A17&B2:B17& C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17)*(A2:A17=前進中 學),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)-1)計算達標率:=MMULT(TRANSPOSE(N(A2:A1160)*(B2:B1160)*(B2:B11& lt;80),ROW(2:11)0)匯總A組男職工的工資:=MMULT(TRANSPOSE(N(B2:B11&C2:C11=男A組)*D2:D11),ROW(2:11)0)計算象棋比賽對局次數(shù)l:=COMBIN(B1,B2)計算五項比賽對局總次數(shù):=SUM(COMBIN(B2:B5,2)預計所有賽事完成的時間:=COMBIN(B1,B2)*B3/B4/60計算英文字母區(qū)分大小寫做密碼的組數(shù):=PERMUT(B1*2,B2)計算中獎率:=TEXT(1/PERMUT(B1,B2),0.00%)計算最大公約數(shù):=GCD(B1:B5)計算最小公倍數(shù):=LCM(B1:B5)計算余數(shù):=MOD(A2,B2)匯總奇數(shù)行數(shù)據(jù):=SUMPRODUCT(MOD(ROW(2:13),2)*C2:C13)根據(jù)單價數(shù)量匯總金額:=SUMPRODUCT(MOD(COLUMN(A:I),2)*A2:I2,(MOD(COLUMN(B:J),2)=0)*B2:J2)設計工資條:=IF(MOD(ROW(),3)=1,單行表頭工資明細!A$1,IF(MOD(ROW(),3)=2,OFFSET(單行表頭工資明細!A$1,ROW()/3+1,0),)根據(jù)身份證號計算性別:=IF(MOD(MID(B2,15,3),2),男,女)每隔4行合計產(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)計算零鈔: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),0.2)/0.1秒與小時、分鐘的換算:=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è)績計算業(yè)務員獎金:=CHOOSE(MIN(QUOTIENT(B2,10000)+1,6),0,3%,5%,7%,9%,11%)*B2計算預報溫度與實際溫度的最大誤差值:=MAX(ABS(C2:C8-B2:B8)計 算個人所得 稅:=ROUND(0.05*SUM(H2-1600-0,500,2000,5000,20000,40000,60000,80000,100000+ABS(H2-1600-0,500,2000,5000,20000,40000,60000,80000,100000)/2,0)產(chǎn)生100到200之間帶小數(shù)的隨機數(shù):=RAND()*(200-100)+100產(chǎn)生ll到20之間的不重復隨機整數(shù):=RANK(A2:A11,A2:A11)+10將20個學生的考位隨機排列:=INDEX(A$2:A$11,RANK(H2:H11,H2:H11)將 三個學校植樹人員隨機分組:=OFFSET(A$1,RANK(G2,G$2:G$11),)&:& OFFSET(B$1,RANK(G2,G$2:G$11),)&:&OFFSET(C$1,RANK(G2,G$2:G$11),)產(chǎn)生-50到100之間的隨機整數(shù):=RANDBETWEEN(-50,100)產(chǎn)生1到100之問的奇數(shù)隨機數(shù):=INDEX(IF(MOD(ROW(1:100),2),ROW(1:100),ROW(1:100)-1),RANDBETWEEN(1,100)產(chǎn)生1到10之間隨機不重復數(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),2),是,不是)計算Al:A10區(qū)域開三次方之平均值:=AVERAGE(POWER(A1:A10,1/30)計算Al:A10區(qū)域倒數(shù)之積:=PRODUCT(POWER(A1:A10,-1)根據(jù)等邊三角形周長計算面積:=SQRT(B1/2*POWER(B1/2-B1/3,3)抽取奇數(shù)行姓名:=INDEX(B:B,ODD(RANDBETWEEN(1,ROWS(1:12)-1)統(tǒng)計A1:B10區(qū)域中奇數(shù)個數(shù):=SUMPRODUCT(N(ODD(A1:B10)=(A1:B10)統(tǒng)計參考人數(shù):=SUMPRODUCT(EVEN(COLUMN(A1:J12)=COLUMN(A1:J12)*(MOD(ROW(A1:J12),3)=1)*(A1:J12)計算A1:B10區(qū)域中偶數(shù)個數(shù):=SUMPRODUCT(N(EVEN(A1:B10)=(A1:B10)合計購物金額、保留一位小數(shù):=TRUNC(SUMPRODUCT(B2:B10,C2:C10),1)將每項購物金額保留一位小數(shù)再合計:=SUMPRODUCT(TRUNC(B2:B10*C2:C10,1)將 金額進行四舍六入五單雙:=IF(A2-TRUNC(A2,1)=0.06,TRUNC(A2,1)+0.1,TRUNC(TRUNC(A2,1)+0.1)/2,1)*2)根據(jù)重量單價計算金額，結(jié)果以萬為單位:=TRUNC(SUMPRODUCT(B2:B10,C2:C10),-4)/10000計算年假天數(shù):=TRUNC(TODAY()-B2)*(TODAY()-B2)=365)/365*5)根據(jù)上機時間計算上網(wǎng)費用:=(TRUNC(B2)+(B2-TRUNC(B2)=0.5)*1.5+(MOD(B2,1)0)成績表轉(zhuǎn)換:=INDEX($A:$E,CEILING(ROW()*3/5,3)-(COLUMN()=7),MOD(ROW(B2)-1,5)+1)計算機上網(wǎng)費用:=CEILING(B2,30)/30*2統(tǒng)計可組建的球隊總數(shù):=SUMPRODUCT(FLOOR(B2:B10,5)/5)統(tǒng)計業(yè)務員提成金額，不足20000元忽略:=FLOOR(B2,20000)/20000*500FLOOR函數(shù)處理正負數(shù)混合區(qū)域:=FLOOR(A1*100,10*(IF(A10,1,-10)將數(shù)據(jù)轉(zhuǎn)換成接近6的倍數(shù):=MROUND(A1,6)以超產(chǎn)80為單位計算超產(chǎn)獎:=SUM(MROUND(B2:B11-700,80*IF(B2:B11=700,1,-1)/80*50將統(tǒng)計金額保留到分位:=ROUND(SUMPRODUCT(B2:B10,C2:C10),2)將統(tǒng)計金額轉(zhuǎn)換成以萬元為單位:=ROUND(SUMPRODUCT(B2:B10,C2:C10)%,)對單價計量單位不同的品名匯總金額:=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10=G,1000,1),(D2:D10=G)*2)將金額保留“角”位，忽略“分”位:=SUM(ROUNDDOWN(B2:B10*C2:C10,1)計算需要多少零鈔:=SUM(ROUNDDOWN(B2:B10*C2:C10,0,-1)*1,-1)計算值為l萬的整數(shù)倍數(shù)的數(shù)據(jù)個數(shù):=SUM(N(B2:B10*C2:C10)=ROUNDDOWN(B2:B10*C2:C10,-4)計算完成工程需求人數(shù):=SUM(ROUNDUP(B2:B11/C2:C11,)按需求對成績進行分類匯總:=SUBTOTAL(HLOOKUP(G$1,平均成績,科目數(shù)量,最高成績,最低成績,成績合計;1,2,4,5,9,2,0),B2:D2)不間斷的序號:=SUBTOTAL(103,$B$2:B2)僅對篩選出的人員排名次:=CONCATENATE(第,SUM(N(IF(SUBTOTAL(103,OFFSET(優(yōu)等生!A$1,ROW($2:$31)-2,)=1,$C$2:$C$31,)C2)+1,名)判斷兩列數(shù)據(jù)是否相等:計算兩列數(shù)據(jù)同行相等的個數(shù):=SUM(N(A1:A10=B1:B10)計算同行相等且長度為3的個數(shù):=SUM(A1:A10=B1:B10)*(LEN(A1:A10)=3)提取A產(chǎn)品最后單價:=INDEX(C:C,MAX(B2:B10=A)*ROW(2:10)判斷學生是否符合獎學金發(fā)放條件:=AND(B290,C2漢族)所有裁判都給“通過”就進入決賽:=AND(B2:E2=通過)判斷身份證長度是否正確:=OR(LEN(B2)=15,18)判斷歌手是否被淘汰:=OR(B2:E2=不通過)根據(jù)年齡判斷職工是否退休:=OR(AND(B2=男,C260),AND(B2=女,C255)根據(jù)年齡與職務判斷職工是否退休:=OR(AND(B2=男,D260+(C2=干部)*3),AND(B2=女,D255+(C2=干部)*3)沒有任何裁判給“不通過”就進行決賽:=NOT(OR(B2:E2=不通過)計彝成績區(qū)域數(shù)字個數(shù):=SUM(NOT(ISERROR(NOT(B2:B11)*1)評定學生成績是否及格:=IF(AVERAGE(B2:D2)=60,及格,不及格)根據(jù)學生成績自動產(chǎn)生評語:=IF(AVERAGE(B2:D2)60,不及格,IF(AVERAGE(B2:D2)90,良好,IF(AVERAGE(B2:D2)80000,1000,500)根據(jù)工作時間計算12月工資:=C2+SUM(IF(B20,1,3,5,10,300,500,500,500,500)合計區(qū)域的值并忽略錯誤值:=SUM(IF(ISERROR(A1:C10),0,A1:C10)既求積也求和:=IF(D2,PRODUCT(C2:D2),SUM(OFFSET(E2,-3,3)分 別統(tǒng)計收入和支出:收入=SUM(IF(B2:B130,B2:B13);支出 =SUM(IF(SUBSTITUTE(IF(B2:B13,B2:B13,0),負,-)*1COUNT(B$2:B$11),LARGE(B$2:B$11,ROW(A1)排除空值:=INDEX($A:$B,SMALL(IF($B$1:$B$11,ROW($1:$11),ROWS($1:$11)+1),ROW(),COLUMN(B2)&有選擇地匯總數(shù)據(jù):=SUM(IF(A2:A11=A組,C組,C2:C11)混合單價求金額合計:=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10=K,1000,1),2)計算異常停機時間:=SUM(SUBSTITUTE(SUBSTITUTE(IF(C2:C11,C2:C11,0),修機,),換原料,)*1)計算最大數(shù)字行與文本行:=MAX(IF(B:B,ROW(A:A)找出誰奪冠次數(shù)最多:=INDEX(B:B,MIN(IF(MAX(COUNTIF(B2:B12,B2:B12)=COUNTIF(B2:B12,B2:B12),ROW(2:12)將全角字符轉(zhuǎn)換為半角:=ASC(A2)計算漢字全角半角混合字符串中的字母個數(shù):=LEN(ASC(A2)*2-LENB(ASC(A2)將半角字符轉(zhuǎn)換成全角顯示:=WIDECHAR(A2)計算混合字符串中漢字個數(shù):=LEN(A2)-(LENB(WIDECHAR(A2)-LENB(ASC(A2)判斷單元格首字符是否為字母:=OR(AND(CODE(A2)64,CODE(A2)96,CODE(A2)47)* (CODE(MID(A2,ROW(INDIRECT(1:&LEN(A2),1)58)計算單元格中大寫 加小寫字母個數(shù):=SUM(CODE(UPPER(MID(A2,ROW(INDIRECT(1:&LEN(A2),1)& gt;64)*(CODE(UPPER(MID(A2,ROW(INDIRECT(1:&LEN(A2),1)91)產(chǎn)生大、小寫字母A到Z的序列:大寫字母=CHAR(ROW(A65)，小寫字母=CHAR(ROW(A65)+32)產(chǎn) 生大寫字母A到ZZ的字母序列:=IF(ROW()26,CHAR(MOD(ROW()-1,26)+65),)產(chǎn)生三個字母組成的隨機字符串:=CHAR(RANDBETWEEN(65,90)&CHAR(RANDBETWEEN(65,90)&CHAR(RANDBETWEEN(65,90)用公式產(chǎn)生換行符:=A2&CHAR(10)&B2將數(shù)字轉(zhuǎn)換成英文字符:字符碼=RANDBETWEEN(1,100)，升序位置=CHAR(MOD(A1-1,26)+65)將字母升序排序:=CHAR(SMALL(CODE(A$2:A$13),ROW(A1)返回自動換行單元格的第二行數(shù)據(jù):=RIGHT(A2,LEN(A2)-FIND(CHAR(10),A2)根據(jù)身份證號碼提取出生年月日:=CONCATENATE(MID(B2,7,4-2*(LEN(B2)=15),年,MID(B2,11-2*(LEN(B2)=15),2),月,MID(B2,13-2*(LEN(B2)=15),2),日 )計算平均成績及評判是否及格:=CONCATENATE(INT(AVERAGE(B2:D2),: ,IF(AVERAGE(B2:D2)=60,不),及格)提 取前三名人員姓 名:=CONCATENATE(LOOKUP(0,0/(B2:B11=LARGE(B2:B11,1),A2:A11),|,LOOKUP(0,0/(B2:B11=LARGE(B2:B11,2),A2:A11),|,(LOOKUP(0,0/(B2:B11=LARGE(B2:B11,3),A2:A11)將單詞轉(zhuǎn)換成首字母大寫:=PROPER(A2)將所有單詞轉(zhuǎn)換成小寫形式:=LOWER(A2)將所有句子轉(zhuǎn)換成首字母大寫其余小寫:=CONCATENATE(PROPER(LEFT(A2),LOWER(RIGHT(A2,LEN(A2)-1)將所有字母轉(zhuǎn)換成大寫形式:=UPPER(A2)計 算字符串中英文字母個數(shù):=SUM(N(NOT(EXACT(UPPER(MID(A2,ROW(INDIRECT(1:& LEN(A2),1),LOWER(MID(A2,ROW(INDIRECT(1:&LEN(A2),1)計 算字符串中單詞個數(shù):=SUM(N(EXACT(TRIM(MID(UPPER(A2),ROW(INDIRECT(1:& LEN(A2),1),MID(PROPER(A2),ROW(INDIRECT(1:&LEN(A2),1)將文本型數(shù)字轉(zhuǎn)換成數(shù)值:=SUM(VALUE(B2:B10)計算字符串中的數(shù)字個數(shù):=SUMPRODUCT(N(ISNUMBER(VALUE(MID(A2,ROW($1:$100),1)*1)提取混合字符串中的數(shù)字:=MAX(IFERROR(VALUE(MID(A2,MIN(FIND(0;1;2;3;4;5;6;7;8;9,A2&1234567890),ROW(INDIRECT(1:&LEN(A2),0)串聯(lián)區(qū)域中的文本:=CONCATENATE(T(A2),T(B2),T(C2)給公式添加運算說明:=CONCATENATE(你好,B2,2008)&T(N(公式含義:連接“你好”和單元格B2、“2008”)根據(jù)身份證號碼判斷性別:=TEXT(MOD(MID(B2,15,3),2),=1男;=0女)將所有數(shù)據(jù)轉(zhuǎn)換成保留兩位小數(shù)再求和:=SUM(-TEXT(B2:B11*C2:C11,0.00)將貨款顯示為“萬元”為單位:=TEXT(B2,￥#&.&#,萬元)根據(jù)身份證號碼計算出生日期:=IF(LEN(B2)=15,19,)&TEXT(MID(B2,7,8-(LEN(B2)=15)*2),#年00月00日)顯示今天的英文日期及星期:=資料日期：&TEXT(TODAY(),dddd, mmmm dd, yyyy)顯示今天每項工程的預計完成時間:=TEXT(SUM(08:00,B$2:B2),h:mm:ss 上午/下午)統(tǒng)計A列有多少個星期日:=SUM(N(TEXT(A1:A11,aaa)=日)將數(shù)據(jù)顯示為小數(shù)點對齊:=TEXT(B2,#.0?)計算A列的日期有幾個屬于第二季度:=SUM(-(TEXT(A1:A11,m)3,6)*1,-1)在A列產(chǎn)生1到12月的英文月份名:=TEXT(ROW()&-1,mmmm)將日期顯示為中文大寫:=TEXT(2008-8-10,DBNum2yyyy年m月d日)將 數(shù)字金額顯示為人民幣大寫:=IF(MOD(B2,1)=0,TEXT(INT(B2),dbnum2G/通用格式元整;負dbnum2G/通 用格式元整;零元整;),IF(B20,負)&TEXT(INT(ABS(B2),dbnum2G/通用格式 元;)&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(FIXED(B2),2),dbnum20角0 分;),零角,IF(ABS(B2)0,零),零分,)判斷單元格的數(shù)據(jù)類型:=TEXT(A2,大于;小于;文本)計算達成率，以不同格式顯示:=TEXT(B2/800,=10.0倍;00.00%;)計算字母“A”的首次出現(xiàn)位置，忽略大小寫:=TEXT(SEARCH(a,A2&a),&LEN(A2)&沒找到;第&SEARCH(a,A2&a)&個)從身份證號碼中提取表示性別的數(shù)字:=MID(B2,TEXT(LEN(B2),=1515;17),1)將三列數(shù)據(jù)交換位置:=TEXT(1,-1,0,C1:C5&;&!&B1:B5&;&A1:A5)計算年終獎:=TEXT(B2,315!0!0;11!0!0!0;5!0!0;)計算星期日完工的工程個數(shù):=COUNT(TEXT(B2:B10+C2:C10-1,AAA)=日)0)計算本月星期日的個數(shù):=SUM(N(TEXT(TODAY()-TEXT(TODAY(),d)+ROW(INDIRECT(1:&DAY(DATE(,TEXT(TODAY(),m)+1,),AAA)=日)檢驗日期是否升序排列:=TEXT(N(A3=A2),;日期有誤;)判斷單元格中首字符的類型:=TEXT(IF(AND(CODE(UPPER(A3)64,CODE(UPPER(A3)0,REPT(,5)&REPT(,ABS(B2)&B2& amp;REPT(,5-ABS(B2),REPT(,5- ABS(B2)-LEN(B2)/2)&B2&REPT(,ABS(B2)&REPT(,5)利 用公式制作帶軸的圖表且標示升降:=IF(A2,A2&,)&IF(A2=,REPT(, (MAX(ABS(B$2:B$8)+6)*2),IF(B20,REPT( ,4+MAX(ABS(B$2:B$8)&IF(ROW()=2, ,IF(B2=OFFSET(B2,-1,0),IF(B2OFFSET(B2,-1,0),)& REPT(,ABS(B2)&B2&REPT( ,4+MAX(ABS(B$2:B$8)-ABS(B2),REPT( ,4+MAX(ABS(B$2:B$8)-ABS(B2)-LEN(B2)/2)&B2&REPT(,ABS(B2)&IF(ROW()=1, ,IF(B2=OFFSET(B2,-1,0),IF(B2OFFSET(B2,-1,0),)& REPT(,4+MAX(ABS(B$2:B$8)計算單元格中數(shù)字個數(shù):=LEN(A2)*2-LENB(A2)將數(shù)字倒序排列:=TEXT(SUM(MID(A2,ROW(INDIRECT(1:&LEN(A2),1)*10(ROW(INDIRECT(1:&LEN(A2)-1),REPT(0,LEN(A2)計算購物金額中小數(shù)位數(shù)最多是幾:=MAX(LEN(B2:B10*C2:C10)-LEN(INT(B2:B10*C2:C10)-1計算英文句子中有幾個單詞:=LEN(A2)-LEN(SUBSTITUTE(SUBSTITUTE(A2, ), ,)+1將英文句子規(guī)范化:=PROPER(LEFT(A2)&TRIM(RIGHT(A2,LEN(A2)-1)分別提取省市縣名:=TRIM(MID(SUBSTITUTE($A2,/,REPT( ,100),COLUMN(A2)*100-99,100)提取英文名字:=LEFT(A2,FIND( ,A2)-1)將分數(shù)轉(zhuǎn)換成小數(shù):=(LEFT(A2,FIND(/,A2)-1)+RIGHT(A2,LEN(A2)-FIND(/,A2)/2從 英文短句中提取每一個單詞:=IFERROR(MID($A2,FIND(,SUBSTITUTE( &$A2& , ,COLUMN(A2),FIND(,SUBSTITUTE( &$A2& , ,COLUMN(B2)-FIND(,SUBSTITUTE( &$A2& , ,COLUMN(A2),)將單位為“雙”與“片”混合的數(shù)量匯總: =SUM(IF(ISNUMBER(FIND(/,C2:C9),(LEFT(C2:C9,FIND( /,C2:C9)-1)+RIGHT(C2:C9,LEN(C2:C9)-FIND(/,C2:C9)/2,C2:C9*IF(B2:B9= 片,0.5,1)提取工作表名:=RIGHT(CELL(filename),LEN(CELL(filename)-FIND(,CELL(filename)根 據(jù)產(chǎn)品規(guī)格計算產(chǎn)品體 積:=PRODUCT(LEFT(B2,FIND(*,B2)-1),MID(B2,FIND(*,B2)+1,FIND(*,B2,FIND(*,B2)+1)-1-FIND(*,B2),RIGHT(B2,LEN(B2)-FIND(*,B2,FIND(*,B2)+1)提取括號中的字符串:=IFERROR(MID(A2,FIND(,A2)+1,FIND(),A2)-FIND(,A2)-1),)分 別提取長、寬、高:=MID($B2,FIND(,SUBSTITUTE($B2, （,COLUMN(A1)+1,FIND(,SUBSTITUTE($B2,）,COLUMN(A1)-FIND(,SUBSTITUTE($B2, （,COLUMN(A1)-1)提取學校與醫(yī)院地址:=IF(OR(IFERROR(FIND(學校,醫(yī)院,A2),FALSE),A2,)計算密碼字符串中字符個數(shù):=COUNT(FIND(CHAR(ROW(65:90),A2),FIND(CHAR(ROW(97:122),A2),FIND(ROW(1:10)-1,A2)通訊錄單列轉(zhuǎn)三列:=MID(INDEX($A:$A,SMALL(IF(IFERROR(FIND(C$1,$A$1:$A$15),FALSE),ROW($1:$15),100000),ROW(A1),LEN(C$1)+1,100)將 15位身份證號碼升級為18位:=IF(LEN(B2)=18,B2,LEFT(REPLACE(B2,7,19),17)& MID(10X98765432,MOD(SUM(MID(REPLACE(B2,7,19),ROW(INDIRECT(1:17),1)*2(18-ROW(INDIRECT(1:17),11)+1,1)將產(chǎn)品型號規(guī)范化:=IF(MID(A2,5,2)=00,A2,REPLACE(A2,5,00)求最大時間:=TEXT(MAX(-TEXT(REPLACE(LEFT(A2:A7,7),5,1,RIGHT(A2:A7,2),00!:00 00-00),hmm/dd/mm)分別提取小時、分鐘、秒:=REPLACE(REPLACE($A$1&$A2,FIND(B$1,$A$1&$A2),100,),1,FIND(A$1,$A$1&$A2)+1,)將年級或者專業(yè)與班級名稱分開:=REPLACE(A2,MAX(IFERROR(SEARCH(CHAR(ROW($65:$90),A2),0),10,)提取各軟件的版本號:=REPLACE(REPLACE(A2,1,SEARCH(,A2),),LEN(REPLACE(A2,1,SEARCH(,A2),),1,)店 名分類:=IF(COUNT(SEARCH(小吃,酒吧,茶,咖啡,電影,休閑,網(wǎng)吧,A2)=1,餐飲娛 樂,IF(COUNT(SEARCH(干洗,醫(yī)院,藥,茶,蛋糕,面包,物流,駕校,開鎖,家政,裝 飾,搬家,維修,中介,衛(wèi)生,旅館,A2)=1,便民服務,IF(COUNT(SEARCH(游樂場,旅行 社,旅游,A2)=1,旅游)查找編號中重復出現(xiàn)的數(shù)字:重復數(shù)字個數(shù) =COUNT(SEARCH(ROW($1:$10)-1)&*&(ROW($1:$10)-1),A2);重復字 符=IF(COUNT(SEARCH(0*0,A2),0,)&SUBSTITUTE(SUMPRODUCT(ISNUMBER(SEARCH(ROW($1:$9)&*&ROW($1:$9),A2)*ROW($1:$9)*10(9-ROW($1:$9),0,)統(tǒng)計名為“劉星”者人數(shù):=COUNT(SEARCH(?劉星,A2:A9)剔除多余的省名:=SUBSTITUTE(A2,IF(ISERROR(SEARCH(重慶市,A2),四川省),)將日期規(guī)范化再求差:=SUBSTITUTE(C2,.,-)-SUBSTITUTE(B2,.,-)提取兩個符號之間的字符串:=TRIM(MID(SUBSTITUTE(B2,*,REPT( ,50),FIND(*,B2),100)產(chǎn)品規(guī)格格式轉(zhuǎn)換:=SUBSTITUTE(SUBSTITUTE(A2,：,(),*,)*)&)判斷調(diào)色配方中是否包含色粉“B”:=LEN(SUBSTITUTE(B2,B,)LEN(B2)提取姓名與省份:=TRIM(MID(A2,1,FIND(|,A2)-1)&MID(SUBSTITUTE(A2,|,REPT( ,100),500,100)將IP地址規(guī)范化:=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(.&A2,.0,.),.0,.),.,1)提取最后一次短跑成績:=REPLACE(A2,1,FIND(,SUBSTITUTE(A2,|,LEN(A2)-LEN(SUBSTITUTE(A2,|,),)從地址中提取省名:=LEFT(A2,FIND(省,A2)計算小學參賽者人數(shù):=COUNT(0/(LEFT(B2:B11)=小)計算四川方向飛機票總價:=SUMPRODUCT(N(LEFT(A2:A11,2)=四川),N(B2:B11=飛機),C2:C11)通過身份證號碼計算年齡:=TEXT(TODAY(),YYYY)-(IF(LEN(B2)=18,19)&LEFT(REPLACE(B2,1,6,),2+(LEN(B2)=18)*2)從混合字符串中取重量:=LOOKUP(9E+307,-LEFT(B2,ROW($1:$10)*C2將金額分散填充:=LEFT(RIGHT( ￥&$A2*100,13-COLUMN()提取成績并計算平均:=AVERAGE(MID(A2:A7,4,LEN(