Excel2007函數(shù)公式應(yīng)用實例

Excel2007函數(shù)公式應(yīng)用實例,

"涉及到137個函數(shù)、7個行業(yè)、41類用途，為大家提供一個參考，拓展思路的機會。

公式由{}包括的為數(shù)組公式，在復(fù)制粘貼到單元后先去掉{}然后按住Shift鍵+Ctrl鍵再按Enter鍵，自動生成數(shù)組公式。",

例1,"對三組生產(chǎn)數(shù)據(jù)求和:=SUM(B2:B7,D2:D7,F2:F7)"

例2,對生產(chǎn)表中大于100的產(chǎn)量進行求和:{=SUM((B2:B11>100)*B2:B11)}

例3,對生產(chǎn)表大于110或者小于100的數(shù)據(jù)求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)}

例4,"對一車間男性職工的工資求和:{=SUM((B2:B10=""一車間"")*(C2:C10=""男"")*D2:D10)}"

例5,"對姓趙的女職工工資求和:{=SUM((LEFT(A2:A10)=""趙"")*(C2:C10=""女"")*D2:D10)}"

例6,"求前三名產(chǎn)量之和:=SUM(LARGE(B2:B10,{1,2,3}))"

例7,求所有工作表相同區(qū)域數(shù)據(jù)之和:=SUM(A組:E組!B2:B9)

例8,求圖書訂購價格總和:{=SUM((B2:E2=參考價格!A$2:A$7)*參考價格!B$2:B$7)}

例9,求當(dāng)前表以外的所有工作表相同區(qū)域的總和:=SUM(一月:五月!B2)

例10,"用SUM函數(shù)計數(shù):{=SUM((B2:B9=""男"")*1)}"

例11,求1累加到100之和:{=SUM(ROW(1:100))}

例12,"多個工作表不同區(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)))}"

例13,"計算倉庫進庫數(shù)量之和:=SUMIF(B2:B10,""=進庫"",C2:C10)"

例14,"計算倉庫大額進庫數(shù)量之和:=SUMIF(B2:B8,"">1000"")"

例15,"對1400到1600之間的工資求和:{=SUM(SUMIF(B2:B10,""<=""&{1400,1600})*{-1,1})}"

例16,"求前三名和后三名的數(shù)據(jù)之和:=SUMIF(B2:B10,"">""&LARGE(B2:B10,4))+SUMIF(B2:B10,""<""&SMALL(B2:B10,4))"

例17,"對所有車間人員的工資求和:=SUMIF(A2:A10,""?車間"",C2)"

例18,"對多個車間人員的工資求和:=SUMIF(A2:A10,""??車間*"",C2)"

例19,"匯總姓趙、劉、李的業(yè)務(wù)員提成金額:=SUM(SUMIF(A2:A10,{""趙"",""劉"",""李""}&""*"",C2:C10))"

例20,"匯總鼠標(biāo)所在列中大于600的數(shù)據(jù):=SUMIF(INDIRECT(""R2C""&CELL(""col"")&"":R8C""&CELL(""col""),FALSE),"">600"")"

例21,"只匯總60～80分的成績:=SUMIFS(B2:B10,B2:B10,"">=60"",B2:B10,""<=80"")"

例22,"匯總?cè)昙壎嗳藛T遲到次數(shù):=SUMIFS(D2:D10,B2:B10,""三年級"",C2:C10,""二班"")"

例23,"匯總車間女性人數(shù):=SUMIFS(C2:C11,A2:A11,""*車間"",B2:B11,""女"")"

例24,"計算車間男性與女性人員的差:=SUM(SUMIFS(C2:C11,B2:B11,{""女"",""男""},A2:A11,""*車間"")*{-1,1})"

例25,"計算參保人數(shù):=SUMPRODUCT((C2:C11=""是"")*1)"

例26,"求25歲以上男性人數(shù):=SUMPRODUCT((B2:B10=""男"")*1,(C2:C10>25)*1)"

例27,"匯總一班人員獲獎次數(shù):=SUMPRODUCT((B2:B11=""一班"")*C2:C11)"

例28,"匯總一車間男性參保人數(shù):=SUMPRODUCT((A2:A10&B2:B10&C2:C10=""一車間男是"")*1)"

例29,"匯總所有車間人員工資:=SUMPRODUCT(--NOT(ISERROR(FIND(""車間"",A2:A10))),C2:C10)"

例30,"匯總業(yè)務(wù)員業(yè)績:=SUMPRODUCT((B2:B11={""江西"",""廣東""})*(C2:C11=""男"")*D2:D11)"

例31,"根據(jù)直角三角形之勾、股求其弦長:=POWER(SUMSQ(B1,B2),1/2)"

例32,"計算A1：A10區(qū)域正數(shù)的平方和:{=SUMSQ(IF(A1:A10>0,A1:A10))}"

例33,"根據(jù)二邊長判斷三角形是否為直角三角形:=CHOOSE((SUMSQ(MAX(B1:B3))=SUMSQ(LARGE(B1:B3,{2,3})))+1,""非直角"",""直角"")"

例34,計算1到10的自然數(shù)的積:=FACT(10)

例35,計算50到60之間的整數(shù)相乘的結(jié)果:=FACT(60)/FACT(49)

例36,計算1到15之間奇數(shù)相乘的結(jié)果:=FACTDOUBLE(15)

例37,計算每小時生產(chǎn)產(chǎn)值:=PRODUCT(C2:E2)

例38,"根據(jù)三邊求普通三角形面積:=(PRODUCT(SUM(B1:B3)/2,SUM(B1:B3)/2-LARGE(B1:B3,{1,2,3})))^0.5"

例39,"根據(jù)直角三角形三邊求三角形面積:=PRODUCT(LARGE(B1:B3,{2,3}))/2"

例40,跨表求積:=PRODUCT(產(chǎn)量表:單價表!B2)

例41,"求不同單價下的利潤:{=MMULT(B2:B10,G2:H2)*25%}"

例42,"制作中文九九乘法表:=COLUMN()&""*""&ROW()&""=""&MMULT(ROW(),COLUMN())"

例43,"計算車間盈虧:=SUM(MMULT((B3:E5>0)*B3:E5,{1;1;1;1}),MMULT((B3:E5<0)*B3:E5,{1;1;1;1}))"

例44,"計算各組別第三名產(chǎn)量是多少:{=MAX(MMULT(COLUMN(A:E)^0,B2:G6))}"

例45,"計算C產(chǎn)品最大入庫量:{=MAX(MMULT(N(A2:A11=""C""),TRANSPOSE((B2:B11)*(A2:A11=""C""))))}"

例46,"求入庫最多的產(chǎn)品數(shù)量:{=MAX(MMULT(TRANSPOSE((B2:B11)*(A2:A11={""A"",""B"",""C"",""D""})),(A2:A11={""A"",""B"",""C"",""D""})*1))}"

例47,"計算累計入庫數(shù):{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11)}"

例48,"計算每日庫存數(shù):{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11-C2:C11)}"

例49,"計算A產(chǎn)品每日庫存數(shù):{=MMULT(N(ROW(2:17)>=TRANSPOSE(ROW(2:17))),(B2:B17=""A"")*(C2:C17-D2:D17))}"

例50,"求第一名人員最多有幾次:{=MAX(MMULT(N(B2:B7=TRANSPOSE(B2:B7)),ROW(2:7)^0))}"

例51,"求幾號選手選票最多:{=RIGHT(MAX(MMULT(N(B2:B10=TRANSPOSE(B2:B10)),ROW(2:10)^0)*100+B2:B10))}"

例52,"總共有幾個選手參選:{=SUM(1/(MMULT(N(B2:B10=TRANSPOSE(B2:B10)),ROW(2:10)^0)))}"

例53,"在不同班級有同名前提下計算學(xué)生人數(shù):{=SUM(1/MMULT(N(A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17)),ROW(2:17)^0))}"

例54,"計算前進中學(xué)參賽人數(shù):{=SUM(IFERROR(1/MMULT(N((A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17))*(A2:A17=""前進中學(xué)"")),ROW(2:17)^0),0))}"

例55,"串聯(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))"

例56,"計算達標(biāo)率:{=MMULT(TRANSPOSE(N(A2:A11<=(B2:B11))),ROW(2:11)^0)/ROWS(2:11)}"

例57,"計算成績在60-80分之間合計數(shù)與個數(shù):求和{=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11<80)*B2:B11),ROW(2:11)^0)}，求個數(shù){=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11<80)),ROW(2:11)^0)}"

例58,"匯總A組男職工的工資:{=MMULT(TRANSPOSE(N(B2:B11&C2:C11=""男A組"")*D2:D11),ROW(2:11)^0)}"

例59,"計算象棋比賽對局次數(shù)l:=COMBIN(B1,B2)"

例60,"計算五項比賽對局總次數(shù):{=SUM(COMBIN(B2:B5,2))}"

例61,"預(yù)計所有賽事完成的時間:=COMBIN(B1,B2)*B3/B4/60"

例62,"計算英文字母區(qū)分大小寫做密碼的組數(shù):=PERMUT(B1*2,B2)"

例63,"計算中獎率:=TEXT(1/PERMUT(B1,B2),""0.00%"")"

例64,計算最大公約數(shù):=GCD(B1:B5)

例65,計算最小公倍數(shù):=LCM(B1:B5)

例66,"計算余數(shù):=MOD(A2,B2)"

例67,"匯總奇數(shù)行數(shù)據(jù):=SUMPRODUCT(MOD(ROW(2:13),2)*C2:C13)"

例68,"根據(jù)單價數(shù)量匯總金額:=SUMPRODUCT(MOD(COLUMN(A:I),2)*A2:I2,(MOD(COLUMN(B:J),2)=0)*B2:J2)"

例69,"設(shè)計工資條:=IF(MOD(ROW(),3)=1,單行表頭工資明細(xì)!A$1,IF(MOD(ROW(),3)=2,OFFSET(單行表頭工資明細(xì)!A$1,ROW()/3+1,0),""""))"

例70,"根據(jù)身份證號計算性別:=IF(MOD(MID(B2,15,3),2),""男"",""女"")"

例71,"每隔4行合計產(chǎn)值:=IF(MOD(ROW(),5)=1,SUM(OFFSET(F2,-4,,4,)),D2*E2)"

例72,"工資截尾取整:=B2+MOD(一月!B2,10)-MOD(B2+MOD(一月!B2,10),10)"

例73,"匯總3的倍數(shù)列的數(shù)據(jù):{=SUM(IF(MOD(COLUMN(A:I),3)=0,A2:I10))}"

例74,"將數(shù)值逐位相加成一位數(shù):=IF(A2=0,0,MOD(A2-1,9)+1)"

例75,"計算零鈔: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"

例76,"秒與小時、分鐘的換算:=QUOTIENT(MOD($A2,IF(COLUMN()=2,A2+1,60^(3-COLUMN(A:A)+1))),60^(3-COLUMN(A:A)))"

例77,"生成隔行累加的序列:=QUOTIENT(ROW()+1,2)"

例78,"根據(jù)業(yè)績計算業(yè)務(wù)員獎金:=CHOOSE(MIN(QUOTIENT(B2,10000)+1,6),0,3%,5%,7%,9%,11%)*B2"

例79,計算預(yù)報溫度與實際溫度的最大誤差值:{=MAX(ABS(C2:C8-B2:B8))}

例80,"計算個人所得稅:=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)"

例81,產(chǎn)生100到200之間帶小數(shù)的隨機數(shù):=RAND()*(200-100)+100

例82,"產(chǎn)生ll到20之間的不重復(fù)隨機整數(shù):{=RANK(A2:A11,A2:A11)+10}"

例83,"將20個學(xué)生的考位隨機排列:{=INDEX(A$2:A$11,RANK(H2:H11,H2:H11))}"

例84,"將三個學(xué)校植樹人員隨機分組:=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),)"

例85,"產(chǎn)生-50到100之間的隨機整數(shù):=RANDBETWEEN(-50,100)"

例86,"產(chǎn)生1到100之問的奇數(shù)隨機數(shù):{=INDEX(IF(MOD(ROW(1:100),2),ROW(1:100),ROW(1:100)-1),RANDBETWEEN(1,100))}"

例87,"產(chǎn)生1到10之間隨機不重復(fù)數(shù):{=LARGE(IF(COUNTIF(A$1:A1,ROW($1:$10))=0,ROW($1:$10)),RANDBETWEEN(1,12-ROW()))}"

例88,"根據(jù)三角形三邊長求證三角形是直角三角形:=IF(POWER(MAX(B1:B3),2)=SUM(POWER(LARGE(B1:B3,{2,3}),2)),""是"",""不是"")"

例89,"計算Al:A10區(qū)域開三次方之平均值:{=AVERAGE(POWER(A1:A10,1/30))}"

例90,"計算Al:A10區(qū)域倒數(shù)之積:{=PRODUCT(POWER(A1:A10,-1))}"

例91,"根據(jù)等邊三角形周長計算面積:=SQRT(B1/2*POWER(B1/2-B1/3,3))"

例92,"抽取奇數(shù)行姓名:=INDEX(B:B,ODD(RANDBETWEEN(1,ROWS(1:12)-1)))"

例93,統(tǒng)計A1:B10區(qū)域中奇數(shù)個數(shù):=SUMPRODUCT(N(ODD(A1:B10)=(A1:B10)))

例94,"統(tǒng)計參考人數(shù):=SUMPRODUCT((EVEN(COLUMN(A1:J12))=COLUMN(A1:J12))*(MOD(ROW(A1:J12),3)=1)*(A1:J12<>""""))"

例95,計算A1:B10區(qū)域中偶數(shù)個數(shù):=SUMPRODUCT(N(EVEN(A1:B10)=(A1:B10)))

例96,"合計購物金額、保留一位小數(shù):=TRUNC(SUMPRODUCT(B2:B10,C2:C10),1)"

例97,"將每項購物金額保留一位小數(shù)再合計:=SUMPRODUCT(TRUNC(B2:B10*C2:C10,1))"

例98,"將金額進行四舍六入五單雙:=IF((A2-TRUNC(A2,1))<=0.04,TRUNC(A2,1),IF((A2-TRUNC(A2,1))>=0.06,TRUNC(A2,1)+0.1,TRUNC((TRUNC(A2,1)+0.1)/2,1)*2))"

例99,"根據(jù)重量單價計算金額，結(jié)果以萬為單位:=TRUNC(SUMPRODUCT(B2:B10,C2:C10),-4)/10000"

例100,計算年假天數(shù):=TRUNC((TODAY()-B2)*((TODAY()-B2)>=365)/365*5)

例101,"根據(jù)上機時間計算上網(wǎng)費用:=(TRUNC(B2)+(B2-TRUNC(B2)>=0.5))*1.5+(MOD(B2,1)<0.5)"

例102,"將金額見角進元與見分進元:見分進元=CEILING(TRUNC(A2,2),1);見角進元=CEILING(TRUNC(A2,1),1)"

例103,分別統(tǒng)計收支金額并忽略小數(shù):收入合計=SUMPRODUCT(INT(B2:B8));支出合計=SUMPRODUCT(TRUNC(C2:C8))

例104,"成績表的格式轉(zhuǎn)換:姓名=INDEX(A:A,INT((ROW(A6))/3));科目=INDEX(B$1:D$1,1,MOD((ROW(A1)-1),3)+1);成績=INDEX($B$2:$D$7,INT((ROW(A1)-1)/3)+1,MOD((ROW(A1)-1),3)+1)"

例105,"隔兩行進行編號:=IF(MOD(ROW(),3)=1,INT(ROW(A3)/3),"""")"

例106,INT函數(shù)在序列中的復(fù)雜運用:=INT(SQRT(2*ROW(A1))+0.5);=10^INT((ROW()-1)/2);=INT(10^(ROW())/9);=INT((ROW(A2))*2/3)

例107,"統(tǒng)計交易損失金額:=SUMPRODUCT(B2:B11-CEILING(B2:B11,0.1))"

例108,"根據(jù)員工工齡計算年資:=C2+CEILING(B2*30,30)*(INT(B2)>0)"

例109,"成績表轉(zhuǎn)換:=INDEX($A:$E,CEILING(ROW()*3/5,3)-(COLUMN()=7),MOD(ROW(B2)-1,5)+1)"

例110,"計算機上網(wǎng)費用:=CEILING(B2,30)/30*2"

例111,"統(tǒng)計可組建的球隊總數(shù):=SUMPRODUCT(FLOOR(B2:B10,5)/5)"

例112,"統(tǒng)計業(yè)務(wù)員提成金額，不足20000元忽略:=FLOOR(B2,20000)/20000*500"

例113,"FLOOR函數(shù)處理正負(fù)數(shù)混合區(qū)域:=FLOOR(A1*100,10*(IF(A1>0,1,-10)))"

例114,"將數(shù)據(jù)轉(zhuǎn)換成接近6的倍數(shù):=MROUND(A1,6)"

例115,"以超產(chǎn)80為單位計算超產(chǎn)獎:{=SUM(MROUND(B2:B11-700,80*IF(B2:B11>=700,1,-1)))/80*50}"

例116,"將統(tǒng)計金額保留到分位:=ROUND(SUMPRODUCT(B2:B10,C2:C10),2)"

例117,"將統(tǒng)計金額轉(zhuǎn)換成以萬元為單位:=ROUND(SUMPRODUCT(B2:B10,C2:C10)%%,)"

例118,"對單價計量單位不同的品名匯總金額:{=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10=""G"",1000,1),(D2:D10=""G"")*2))}"

例119,"將金額保留“角”位，忽略“分”位:{=SUM(ROUNDDOWN(B2:B10*C2:C10,1))}"

例120,"計算需要多少零鈔:{=SUM(ROUNDDOWN(B2:B10*C2:C10,{0,-1})*{1,-1})}"

例121,"計算值為l萬的整數(shù)倍數(shù)的數(shù)據(jù)個數(shù):{=SUM(N((B2:B10*C2:C10)=ROUNDDOWN(B2:B10*C2:C10,-4)))}"

例122,"計算完成工程需求人數(shù):{=SUM(ROUNDUP(B2:B11/C2:C11,))}"

例123,"按需求對成績進行分類匯總:=SUBTOTAL(HLOOKUP(G$1,{""平均成績"",""科目數(shù)量"",""最高成績"",""最低成績"",""成績合計"";1,2,4,5,9},2,0),B2:D2)"

例124,"不間斷的序號:=SUBTOTAL(103,$B$2:B2)"

例125,"僅對篩選出的人員排名次:{=CONCATENATE(""第"",SUM(N(IF((SUBTOTAL(103,OFFSET(優(yōu)等生!A$1,ROW($2:$31)-2,)))=1,$C$2:$C$31,)>C2))+1,""名"")}"

例126,判斷兩列數(shù)據(jù)是否相等:

例127,計算兩列數(shù)據(jù)同行相等的個數(shù):{=SUM(N(A1:A10=B1:B10))}

例128,計算同行相等且長度為3的個數(shù):{=SUM((A1:A10=B1:B10)*(LEN(A1:A10)=3))}

例129,"提取A產(chǎn)品最后單價:{=INDEX(C:C,MAX((B2:B10=""A"")*ROW(2:10)))}"

例130,"判斷學(xué)生是否符合獎學(xué)金發(fā)放條件:=AND(B2>90,C2<>""漢族"")"

例131,"所有裁判都給“通過”就進入決賽:{=AND(B2:E2=""通過"")}"

例132,"判斷身份證長度是否正確:=OR(LEN(B2)={15,18})"

例133,"判斷歌手是否被淘汰:{=OR(B2:E2=""不通過"")}"

例134,"根據(jù)年齡判斷職工是否退休:=OR(AND(B2=""男"",C2>60),AND(B2=""女"",C2>55))"

例135,"根據(jù)年齡與職務(wù)判斷職工是否退休:=OR(AND(B2=""男"",D2>60+(C2=""干部"")*3),AND(B2=""女"",D2>55+(C2=""干部"")*3))"

例136,"沒有任何裁判給“不通過”就進行決賽:{=NOT(OR(B2:E2=""不通過""))}"

例137,計彝成績區(qū)域數(shù)字個數(shù):{=SUM(NOT(ISERROR(NOT(B2:B11)))*1)}

例138,"評定學(xué)生成績是否及格:=IF(AVERAGE(B2:D2)>=60,""及格"",""不及格"")"

例139,"根據(jù)學(xué)生成績自動產(chǎn)生評語:=IF(AVERAGE(B2:D2)<60,""不及格"",IF(AVERAGE(B2:D2)<90,""良好"",IF(AVERAGE(B2:D2)<100,""優(yōu)秀"",""滿分"")))"

例140,"根據(jù)業(yè)績計算需要發(fā)放多少獎金:{=SUM(IF(B2:B11>80000,1000,500))}"

例141,"根據(jù)工作時間計算12月工資:=C2+SUM(IF(B2>{0,1,3,5,10},{300,500,500,500,500}))"

例142,"合計區(qū)域的值并忽略錯誤值:{=SUM(IF(ISERROR(A1:C10),0,A1:C10))}"

例143,"既求積也求和:=IF(D2<>"""",PRODUCT(C2:D2),SUM(OFFSET(E2,-3,,3)))"

例144,"分別統(tǒng)計收入和支出:收入{=SUM(IF(B2:B13>0,B2:B13))};支出{=SUM(IF(SUBSTITUTE(IF(B2:B13<>"""",B2:B13,0),""負(fù)"",""-"")*1<0,SUBSTITUTE(B2:B13,""負(fù)"",""-"")*1))}"

例145,"將成績從大到小排列:{=IF(ROW(A1)>COUNT(B$2:B$11),"""",LARGE(B$2:B$11,ROW(A1)))}"

例146,"排除空值:{=INDEX($A:$B,SMALL(IF($B$1:$B$11<>"""",ROW($1:$11),ROWS($1:$11)+1),ROW()),COLUMN(B2))&""""}"

例147,"有選擇地匯總數(shù)據(jù):{=SUM(IF(A2:A11={""A組"",""C組""},C2:C11))}"

例148,"混合單價求金額合計:{=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10=""K"",1000,1),2))}"

例149,"計算異常停機時間:{=SUM(SUBSTITUTE(SUBSTITUTE(IF(C2:C11<>"""",C2:C11,0),""修機"",""""),""換原料"","""")*1)}"

例150,"計算最大數(shù)字行與文本行:{=MAX(IF(B:B<>"""",ROW(A:A)))}"

例151,"找出誰奪冠次數(shù)最多:{=INDEX(B:B,MIN(IF(MAX(COUNTIF(B2:B12,B2:B12))=COUNTIF(B2:B12,B2:B12),ROW(2:12))))}"

例152,將全角字符轉(zhuǎn)換為半角:=ASC(A2)

例153,計算漢字全角半角混合字符串中的字母個數(shù):=LEN(ASC(A2))*2-LENB(ASC(A2))

例154,將半角字符轉(zhuǎn)換成全角顯示:=WIDECHAR(A2)

例155,計算混合字符串中漢字個數(shù):=LEN(A2)-(LENB(WIDECHAR(A2))-LENB(ASC(A2)))

例156,"判斷單元格首字符是否為字母:=OR(AND(CODE(A2)>64,CODE(A2)<91),AND(CODE(A2)>96,CODE(A2)<123))"

例157,"計算單元格中數(shù)字個數(shù):{=SUM((CODE(MID(A2,ROW(INDIRECT(""1:""&LEN(A2))),1))>47)*(CODE(MID(A2,ROW(INDIRECT(""1:""&LEN(A2))),1))<58))}"

例158,"計算單元格中大寫加小寫字母個數(shù):{=SUM((CODE(UPPER(MID(A2,ROW(INDIRECT(""1:""&LEN(A2))),1)))>64)*(CODE(UPPER(MID(A2,ROW(INDIRECT(""1:""&LEN(A2))),1)))<91))}"

例159,產(chǎn)生大、小寫字母A到Z的序列:大寫字母=CHAR(ROW(A65))，小寫字母=CHAR(ROW(A65)+32)

例160,"產(chǎn)生大寫字母A到ZZ的字母序列:=IF(ROW()<27,CHAR(MOD(ROW()-1,26)+65),CHAR(65+(ROW()-1)/26-1))&IF(ROW()>26,CHAR(MOD(ROW()-1,26)+65),"""")"

例161,"產(chǎn)生三個字母組成的隨機字符串:=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))"

例162,用公式產(chǎn)生換行符:=A2&CHAR(10)&B2

例163,"將數(shù)字轉(zhuǎn)換成英文字符:字符碼=RANDBETWEEN(1,100)，升序位置=CHAR(MOD(A1-1,26)+65)"

例164,"將字母升序排序:{=CHAR(SMALL(CODE(A$2:A$13),ROW(A1)))}"

例165,"返回自動換行單元格的第二行數(shù)據(jù):=RIGHT(A2,LEN(A2)-FIND(CHAR(10),A2))"

例166,"根據(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),""日"")"

例167,"計算平均成績及評判是否及格:=CONCATENATE(INT(AVERAGE(B2:D2)),"":"",IF(AVERAGE(B2:D2)>=60,"""",""不""),""及格"")"

例168,"提取前三名人員姓名:=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)))"

例169,將單詞轉(zhuǎn)換成首字母大寫:=PROPER(A2)

例170,將所有單詞轉(zhuǎn)換成小寫形式:=LOWER(A2)

例171,"將所有句子轉(zhuǎn)換成首字母大寫其余小寫:=CONCATENATE(PROPER(LEFT(A2)),LOWER(RIGHT(A2,LEN(A2)-1)))"

例172,將所有字母轉(zhuǎn)換成大寫形式:=UPPER(A2)

例173,"計算字符串中英文字母個數(shù):{=SUM(N(NOT(EXACT(UPPER(MID(A2,ROW(INDIRECT(""1:""&LEN(A2))),1)),LOWER(MID(A2,ROW(INDIRECT(""1:""&LEN(A2))),1))))))}"

例174,"計算字符串中單詞個數(shù):{=SUM(N(EXACT(TRIM(MID(UPPER(A2),ROW(INDIRECT(""1:""&LEN(A2))),1)),MID(PROPER(A2),ROW(INDIRECT(""1:""&LEN(A2))),1))))}"

例175,將文本型數(shù)字轉(zhuǎn)換成數(shù)值:{=SUM(VALUE(B2:B10))}

例176,"計算字符串中的數(shù)字個數(shù):=SUMPRODUCT(N(ISNUMBER(VALUE(MID(A2,ROW($1:$100),1)*1))))"

例177,"提取混合字符串中的數(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))}"

例178,"串聯(lián)區(qū)域中的文本:=CONCATENATE(T(A2),T(B2),T(C2))"

例179,"給公式添加運算說明:=CONCATENATE(""你好"",B2,""2008"")&T(N(""公式含義:連接“你好”和單元格B2、“2008”""))"

例180,"根據(jù)身份證號碼判斷性別:=TEXT(MOD(MID(B2,15,3),2),""[=1]男;[=0]女"")"

例181,"將所有數(shù)據(jù)轉(zhuǎn)換成保留兩位小數(shù)再求和:{=SUM(--TEXT(B2:B11*C2:C11,""0.00""))}"

例182,"將貨款顯示為“萬元”為單位:=TEXT(B2,""￥#""&""""""""&"".""&""""""""&""#,萬元"")"

例183,"根據(jù)身份證號碼計算出生日期:=IF(LEN(B2)=15,19,"""")&TEXT(MID(B2,7,8-(LEN(B2)=15)*2),""#年00月00日"")"

例184,"顯示今天的英文日期及星期:=""資料日期：""&TEXT(TODAY(),""dddd,mmmmdd,yyyy"")"

例185,"顯示今天每項工程的預(yù)計完成時間:=TEXT(SUM(""08:00"",B$2:B2),""h:mm:ss上午/下午"")"

例186,"統(tǒng)計A列有多少個星期日:{=SUM(N(TEXT(A1:A11,""aaa"")=""日""))}"

例187,"將數(shù)據(jù)顯示為小數(shù)點對齊:=TEXT(B2,""#.0????"")"

例188,"計算A列的日期有幾個屬于第二季度:{=SUM((--(TEXT(A1:A11,""m""))>{3,6})*{1,-1})}"

例189,"在A列產(chǎn)生1到12月的英文月份名:=TEXT((ROW())&""-1"",""mmmm"")"

例190,"將日期顯示為中文大寫:=TEXT(""2008-8-10"",""[DBNum2]yyyy年m月d日"")"

例191,"將數(shù)字金額顯示為人民幣大寫:=IF(MOD(B2,1)=0,TEXT(INT(B2),""[dbnum2]G/通用格式元整;負(fù)[dbnum2]G/通用格式元整;零元整;""),IF(B2>0,,""負(fù)"")&TEXT(INT(ABS(B2)),""[dbnum2]G/通用格式元;;"")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(FIXED(B2),2),""[dbnum2]0角0分;;""),""零角"",IF(ABS(B2)<>0,,""零"")),""零分"",""""))"

例192,"判斷單元格的數(shù)據(jù)類型:=TEXT(A2,""大于○;小于○;○;文本"")"

例193,"計算達成率，以不同格式顯示:=TEXT(B2/800,""[>=1]0.0倍;[>0]0.00%;"")"

例194,"計算字母“A”的首次出現(xiàn)位置，忽略大小寫:=TEXT(SEARCH(""a"",A2&""a""),""[>""&LEN(A2)&""]沒找到;第""&SEARCH(""a"",A2&""a"")&""個"")"

例195,"從身份證號碼中提取表示性別的數(shù)字:=MID(B2,TEXT(LEN(B2),""[=15]15;17""),1)"

例196,"將三列數(shù)據(jù)交換位置:{=TEXT({1,-1,0},C1:C5&"";""&""!""&B1:B5&"";""&A1:A5)}"

例197,"計算年終獎:=TEXT(B2,""[>3]15!0!0;[>1]1!0!0!0;5!0!0;"")"

例198,"計算星期日完工的工程個數(shù):{=COUNT((TEXT(B2:B10+C2:C10-1,""AAA"")=""日"")^0)}"

例199,"計算本月星期日的個數(shù):{=SUM(N(TEXT(TODAY()-TEXT(TODAY(),""d"")+ROW(INDIRECT(""1:""&DAY(DATE(,TEXT(TODAY(),""m"")+1,)))),""AAA"")=""日""))}"

例200,"檢驗日期是否升序排列:=TEXT(N(A3>=A2),"";;日期有誤;"")"

例201,"判斷單元格中首字符的類型:=TEXT(IF(AND(CODE(UPPER(A3))>64,CODE(UPPER(A3))<91),CODE(A3),A3),""[=""&CODE(A3)&""]字母;;數(shù)字;漢字"")"

例202,"計算每個季度的天數(shù):{=SUM(--TEXT(DATE(2008,3*ROW(A1)-ROW($1:$3)+2,),""d""))}"

例203,"將數(shù)據(jù)重復(fù)顯示5次:=SUBSTITUTE(TEXT(A2&""？"",""@@@@@""),""？"","""")"

例204,"將表示起止時間的數(shù)字格式化為時間格式:=TEXT(B2,""#!:00-00!:00"")"

例205,"根據(jù)起止時間計算經(jīng)過時間:=TEXT(INT(((TEXT(RIGHT(B4,4),""#!:00"")-TEXT(LEFT(B4,3+(LEN(B4)=8)),""#!:00""))*24*60)/60)+MOD(((TEXT(RIGHT(B4,4),""#!:00"")-TEXT(LEFT(B4,3+(LEN(B4)=8)),""#!:00""))*24*60),60.1)%,""0小時.00分鐘"")"

例206,"將數(shù)字轉(zhuǎn)化成電話格式:=TEXT(A2,""(0000)0000-0000"")"

例207,"在A1:A7區(qū)域產(chǎn)生星期一到星期日的英文全稱:{=TEXT(ROW(1:7)+1,""DDDD"")}"

例208,"將匯總金額保留一位小數(shù)并顯示千分位分隔符:{=FIXED(SUM(--FIXED(B2:B11*C2:C11,1)),1,FALSE)}"

例209,"計算訂單金額并以“百萬”為單位顯示:=FIXED(SUMPRODUCT(B2:B10,C2:C10),-6)/1000000"

例210,"將數(shù)據(jù)對齊顯示，將空白以“．”占位:=WIDECHAR(REPT(""."",10-LEN(B2))&B2)"

例211,"利用公式制作簡易圖表:=IF(B2>0,REPT("""",5)&""｜""&REPT(""■"",ABS(B2))&B2&REPT("""",5-ABS(B2)),REPT("""",5-ABS(B2)-LEN(B2)/2)&B2&REPT(""■"",ABS(B2))&""｜""&REPT("""",5))"

例212,"利用公式制作帶軸的圖表且標(biāo)示升降:{=IF(A2<>"""",A2&""┫"","""")&IF(A2="""",REPT(""〓"",(MAX(ABS(B$2:B$8))+6)*2),IF(B2>0,REPT("""",4+MAX(ABS(B$2:B$8)))&IF(ROW()=2,"""",IF(B2=OFFSET(B2,-1,0),""→"",IF(B2>OFFSET(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(B2>OFFSET(B2,-1,0),""↑"",""↓""))&REPT("""",4+MAX(ABS(B$2:B$8))))))}"

例213,計算單元格中數(shù)字個數(shù):=LEN(A2)*2-LENB(A2)

例214,"將數(shù)字倒序排列:{=TEXT(SUM(MID(A2,ROW(INDIRECT(""1:""&LEN(A2))),1)*10^(ROW(INDIRECT(""1:""&LEN(A2)))-1)),REPT(0,LEN(A2)))}"

例215,計算購物金額中小數(shù)位數(shù)最多是幾:{=MAX(LEN(B2:B10*C2:C10)-LEN(INT(B2:B10*C2:C10)))-1}

例216,"計算英文句子中有幾個單詞:=LEN(A2)-LEN(SUBSTITUTE(SUBSTITUTE(A2,""'"",""""),"""",""""))+1"

例217,"將英文句子規(guī)范化:=PROPER(LEFT(A2))&TRIM(RIGHT(A2,LEN(A2)-1))"

例218,"分別提取省市縣名:=TRIM(MID(SUBSTITUTE($A2,""/"",REPT("""",100)),COLUMN(A2)*100-99,100))"

例219,"提取英文名字:=LEFT(A2,FIND("""",A2)-1)"

例220,"將分?jǐn)?shù)轉(zhuǎn)換成小數(shù):=(LEFT(A2,FIND(""/"",A2)-1)+RIGHT(A2,LEN(A2)-FIND(""/"",A2)))/2"

例221,"從英文短句中提取每一個單詞:=IFERROR(MID($A2,FIND(""~"",SUBSTITUTE(""""&$A2&"""","""",""~"",COLUMN(A2))),FIND(""~"",SUBSTITUTE(""""&$A2&"""","""",""~"",COLUMN(B2)))-FIND(""~"",SUBSTITUTE(""""&$A2&"""","""",""~"",COLUMN(A2)))),"""")"

例222,"將單位為“雙”與“片”混合的數(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)))}"

例223,"提取工作表名:=RIGHT(CELL(""filename""),LEN(CELL(""filename""))-FIND(""]"",CELL(""filename"")))"

例224,"根據(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)))"

例225,"提取括號中的字符串:=IFERROR(MID(A2,FIND(""("",A2)+1,FIND("")"",A2)-FIND(""("",A2)-1),"""")"

例226,"分別提取長、寬、高:=MID($B2,FIND(""@"",SUBSTITUTE($B2,""（"",""@"",COLUMN(A1)))+1,FIND(""@"",SUBSTITUTE($B2,""）"",""@"",COLUMN(A1)))-FIND(""@"",SUBSTITUTE($B2,""（"",""@"",COLUMN(A1)))-1)"

例227,"提取學(xué)校與醫(yī)院地址:{=IF(OR(IFERROR(FIND({""學(xué)校"",""醫(yī)院""},A2),FALSE)),A2,"""")}"

例228,"計算密碼字符串中字符個數(shù):{=COUNT(FIND(CHAR(ROW(65:90)),A2),FIND(CHAR(ROW(97:122)),A2),FIND(ROW(1:10)-1,A2))}"

例229,"通訊錄單列轉(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)}"

例230,"將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))}"

例231,"將產(chǎn)品型號規(guī)范化:=IF(MID(A2,5,2)=""00"",A2,REPLACE(A2,5,,""00""))"

例232,"求最大時間:{=TEXT(MAX(--TEXT(REPLACE(LEFT(A2:A7,7),5,1,RIGHT(A2:A7,2)),""00!:0000-00"")),""hmm/dd/mm"")}"

例233,"分別提取小時、分鐘、秒:=REPLACE(REPLACE($A$1&$A2,FIND(B$1,$A$1&$A2),100,),1,FIND(A$1,$A$1&$A2)+1,)"

例234,"將年級或者專業(yè)與班級名稱分開:{=REPLACE(A2,MAX(IFERROR(SEARCH(CHAR(ROW($65:$90)),A2),0)),10,)}"

例235,"提取各軟件的版本號:=REPLACE(REPLACE(A2,1,SEARCH(""("",A2),),LEN(REPLACE(A2,1,SEARCH(""("",A2),)),1,)"

例236,"店名分類:=IF(COUNT(SEARCH({""小吃"",""酒吧"",""茶"",""咖啡"",""電影"",""休閑"",""網(wǎng)吧""},A2))=1,""餐飲娛樂"",IF(COUNT(SEARCH({""干洗"",""醫(yī)院"",""藥"",""茶"",""蛋糕"",""面包"",""物流"",""駕校"",""開鎖"",""家政"",""裝飾"",""搬家"",""維修"",""中介"",""衛(wèi)生"",""旅館""},A2))=1,""便民服務(wù)"",IF(COUNT(SEARCH({""游樂場"",""旅行社"",""旅游""},A2))=1,""旅游"")))"

例237,"查找編號中重復(fù)出現(xiàn)的數(shù)字:重復(fù)數(shù)字個數(shù){=COUNT(SEARCH((ROW($1:$10)-1)&""*""&(ROW($1:$10)-1),A2))};重復(fù)字符=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,)"

例238,"統(tǒng)計名為“劉星”者人數(shù):{=COUNT(SEARCH(""?劉星"",A2:A9))}"

例239,"剔除多余的省名:=SUBSTITUTE(A2,IF(ISERROR(SEARCH(""重慶市"",A2)),"""",""四川省""),"""")"

例240,"將日期規(guī)范化再求差:=SUBSTITUTE(C2,""."",""-"")-SUBSTITUTE(B2,""."",""-"")"

例241,"提取兩個符號之間的字符串:=TRIM(MID(SUBSTITUTE(B2,""*"",REPT("""",50)),FIND(""*"",B2),100))"

例242,"產(chǎn)品規(guī)格格式轉(zhuǎn)換:=SUBSTITUTE(SUBSTITUTE(A2,""："",""(""),""*"","")*"")&"")"""

例243,"判斷調(diào)色配方中是否包含色粉“B”:=LEN(SUBSTITUTE(B2,""B"",""""))<>LEN(B2)"

例244,"提取姓名與省份:=TRIM(MID(A2,1,FIND(""|"",A2)-1)&MID(SUBSTITUTE(A2,""|"",REPT("""",100)),500,100))"

例245,"將IP地址規(guī)范化:=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("".""&A2,"".0"","".""),"".0"","".""),""."","""",1)"

例246,"提取最后一次短跑成績:=REPLACE(A2,1,FIND(""々"",SUBSTITUTE(A2,""|"",""々"",LEN(A2)-LEN(SUBSTITUTE(A2,""|"",)))),)"

例247,"從地址中提取省名:=LEFT(A2,FIND(""省"",A2))"

例248,"計算小學(xué)參賽者人數(shù):{=COUNT(0/(LEFT(B2:B11)=""小""))}"

例249,"計算四川方向飛機票總價:=SUMPRODUCT(N(LEFT(A2:A11,2)=""四川""),N(B2:B11=""飛機""),C2:C11)"

例250,"通過身份證號碼計算年齡:=TEXT(TODAY(),""YYYY"")-(IF(LEN(B2)=18,"""",19)&LEFT(REPLACE(B2,1,6,""""),2+(LEN(B2)=18)*2))"

例251,"從混合字符串中取重量:=LOOKUP(9E+307,--LEFT(B2,ROW($1:$10)))*C2"

例252,"將金額分散填充:=LEFT(RIGHT(""￥""&$A2*100,13-COLUMN()))"

例253,"提取成績并計算平均:{=AVERAGE(MID(A2:A7,4,LEN(A2:A7)-3)*1)}"

例254,"提取參賽選手姓名:=MID(A2,FIND(""："",A2)+1,LEN(A2))"

例255,"從混合字符串中提取金額:=LOOKUP(307,--MID(B2,MIN(FIND({1;2;3;4;5;6;7;8;9},B2&123456789)),ROW($1:$99)))"

例256,"從卡機數(shù)據(jù)提取打卡時間:=730>--MID(A2,14,4)"

例257,"根據(jù)卡機數(shù)據(jù)判斷員工部門:=CHOOSE(MATCH(--RIGHT(A2,3),{1,38,14,11,8,21,43,9,28},0),""生產(chǎn)部"",""業(yè)務(wù)部"",""總務(wù)部"",""人事部"",""食堂"",""保衛(wèi)部"",""采購部"",""送貨部"",""財務(wù)部"")"

例258,"根據(jù)身份證號碼統(tǒng)計男性人數(shù):{=SUM(MOD(LEFT(RIGHT(B2:B11,1+(LEN(B2:B11)=18))),2))}"

例259,"從漢字與數(shù)字混合字串中提取溫度數(shù)據(jù):{=MAX(IFERROR(--RIGHT(LEFT(B2,LEN(B2)-1),ROW($1:$10)),0))}"

例260,"將字符串位數(shù)統(tǒng)一:{=TEXT(RIGHT(A2,LEN(A2)-1),""!""&LEFT(A2)&REPT(0,MAX(LEN(A$2:A$10))-1))}"

例261,"對所有人員按平均分排序:{=INDEX(A:A,RIGHT(LARGE(B$2:B$11*1000+ROW($2:$11),ROW()-1),3))}"

例262,"取金額的角位與分位叫:=--RIGHT(ROUND(A2*100,),2)"

例263,"從格式不規(guī)范的日期中取出日:=TRIM(RIGHT(SUBSTITUTE(A2,""."",""

"",2),3))"

例264,"計算平均成績(忽略缺考人員):=ROUND(AVERAGE(B2:B10),2)"

例265,"計算90分以上的平均成績:{=ROUND(AVERAGE(IF(ISNUMBER(B2:B10)*(B2:B10>90),B2:B10)),2)}"

例266,計算當(dāng)前表以外的所有工作表平均值2:=AVERAGE(一班:五班!B:B)

例267,"計算二車間女職工的平均工資:{=AVERAGE(IF((B2:B10=""二車間"")*(C2:C10=""女""),D2:D10))}"

例268,"計算一車間和三車間女職工的平均工資:{=AVERAGE(IF((B2:B10=""一車間"")+(B2:B10=""三車間"")*(C2:C10=""女""),D2:D10))}"

例269,計算各業(yè)務(wù)員的平均獎金:{=AVERAGE(1500+300*(INT((C2:C11-80000)/10000)))}

例270,"計算平均工資(不忽略無薪人員):=ROUND(AVERAGEA(B2:B10),2)"

例271,"計算每人平均出口量:{=AVERAGEA((C2:C11=""A"")*D2:D11)}"

例272,計算平均成績，成績空白也計算:{=AVERAGEA(B2:B11*1)}

例273,"計算二年級所有人員的平均獲獎率:{=TEXT(AVERAGEA(IF(LEFT(A2:A10,3)=""二年級"",B2:B10/C2:C10)),""0.00%"")}"

例274,"統(tǒng)計前三名人員的平均成績:=AVERAGEA(LARGE(B2:B11,{1,2,3}))"

例275,"求每季度平均支出金額:=AVERAGEIF(B2:B9,""支出"",C2)"

例276,"計算每個車間大于250的平均產(chǎn)量:=AVERAGEIF(B2:C11,"">250"")"

例277,"去掉首尾求平均:=AVERAGEIFS(B2:B11,B2:B11,"">""&MIN(B2:B11),B2:B11,""<""&MAX(B2:B11))"

例278,"生產(chǎn)A產(chǎn)品且無異常的機臺平均產(chǎn)量:=AVERAGEIFS(C2:C11,B2:B11,""A"",D2:D11,"""")"

例279,計算生產(chǎn)車間異常機臺個數(shù):=COUNT(C2:C11)

例280,"計算及格率:{=TEXT(COUNT(0/(B2:B11>=60))/COUNT(B2:B11),""0.00%"")}"

例281,"統(tǒng)計屬于餐飲娛樂業(yè)的店名個數(shù):{=COUNT(SEARCH({""小吃"",""酒吧"",""茶"",""咖啡"",""電影"",""休閑"",""網(wǎng)吧""},A2:A11))}"

例282,統(tǒng)計各分?jǐn)?shù)段人數(shù):{=COUNT(0/((B$2:B$11>ROW(A6)*10)*(B$2:B$11<=ROW(A7)*10)))}

例283,"統(tǒng)計有多少個選手:{=COUNT(0/(MATCH(B2:B11,B2:B11,)=(ROW(2:11)-1)))}"

例284,統(tǒng)計出勤異常人數(shù):=COUNTA(B2:B11)

例285,"判斷是否有人缺考:=IF(COUNTA(B2:E10)=ROWS(B2:E10)*COLUMNS(B2:E10),""沒有"",""有"")"

例286,統(tǒng)計未檢驗完成的產(chǎn)品數(shù):=COUNTBLANK(B2:B11)

例287,"統(tǒng)計產(chǎn)量達標(biāo)率:=TEXT(COUNTIF(B2:B11,"">=800"")/COUNT(B2:B11),""0.00"")"

例288,"根據(jù)畢業(yè)學(xué)校統(tǒng)計中學(xué)學(xué)歷人數(shù):=COUNTIF(B2:B11,""*中學(xué)"")"

例289,"計算兩列數(shù)據(jù)相同個數(shù):{=SUM(COUNTIF(A2:A11,B2:B11))}"

例290,"統(tǒng)計連續(xù)三次進入前十名的人數(shù):{=SUM(COUNTIF(C2:C11,IF(COUNTIF(A2:A11,B2:B11),B2:B11)))}"

例291,"統(tǒng)計淘汰者人數(shù):{=SUM(N(COUNTIF(A2:C11,A2:C11)=1))}"

例292,"統(tǒng)計區(qū)域中不重復(fù)數(shù)據(jù)個數(shù):{=SUM(1/COUNTIF(B2:B8,B2:B8))}"

例293,"統(tǒng)計諾基亞、摩托羅拉和聯(lián)想已隹出手機個數(shù):=SUM(COUNTIF(B2:B11,""*""&{""諾基亞"",""摩托羅拉"",""聯(lián)想""}&""*""))"

例294,"統(tǒng)計聯(lián)想比摩托羅拉手機的銷量高多少:{=SUM(COUNTIF(B2:B11,{""諾基亞*"",""*聯(lián)想*""})*{1,-1})}"

例295,"統(tǒng)計冠軍榜前三名:{=INDEX(B:B,SMALL(IF(COUNTIF(B$2:B$12,B$2:B$12)*((MATCH(B$2:B$12,B$2:B$12,)=ROW($2:$12)-1))>=LARGE(COUNTIF(B$2:B$12,B$2:B$12)*((MATCH(B$2:B$12,B$2:B$12,)=ROW($2:$12)-1)),3),ROW($2:$12)),ROW(A1)))}"

例296,"統(tǒng)計真空、假空單元格個數(shù):=COUNTIF(成績!C2:C11,""="")"

例297,"對名冊表進行混合編號:=IF(RIGHT(B1)<>""班"",ROW()-COUNTIF($B$1:B1,""??班""),TEXT(COUNTIF($B$1:B1,""??班""),""[DBNum2]0""))"

例298,"提取不重復(fù)數(shù)據(jù)5:{=INDEX(B:B,MATCH(0,COUNTIF($D$1:D1,B$2:B$11),0)+1)}"

例299,"中國式排名:{=SUM(IF(B$2:B$11>B2,1/COUNTIF(B$2:B$11,B$2:B$11)))+1}"

例300,"統(tǒng)計大于80分的三好學(xué)生個數(shù):{=COUNTIFS(B2:B11,""三好學(xué)生"",C2:C11,"">80"")}"

例301,"統(tǒng)計業(yè)績在6萬到8萬之間的女業(yè)務(wù)員個數(shù):=COUNTIFS(B2:B11,""女"",C2:C11,"">60000"",C2:C11,""<=800000"")"

例302,"統(tǒng)計二班和三班數(shù)學(xué)競賽獲獎人數(shù):=SUM(COUNTIFS(B2:B11,{""二班"",""三班""},C2:C11,""數(shù)學(xué)*""))"

例303,"根據(jù)身高計算各班淘汰人數(shù):=SUM(COUNTIFS(B$2:B$11,E1,C$2:C$11,{""<160"","">180""}))"

例304,"計算A列最后一個非空單元格行號:{=MAX((A:A<>"""")*ROW(A:A))}"

例305,"計算女職工的最大年齡:{=MAX((B2:B11=""女"")*C2:C11)}"

例306,"消除單位提取數(shù)據(jù):{=MAX(IFERROR(ABS(LEFT(A2,ROW($1:$100))),))*IF(LEFT(A2)=""-"",-1,1)}"

例307,"計算單日最高銷售金額:{=MAX(SUMIF(A2:A11,A2:A11,C2:C11))}"

例308,"查找第一名學(xué)生姓名:=INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10,))"

例309,"統(tǒng)計季度最高產(chǎn)值合計:{=MAX(SUBTOTAL(9,OFFSET(B2,,COLUMN(B:E)-2,ROWS(2:10),1)))}"

例310,"根據(jù)達標(biāo)率計算員工獎金:=MAX((B2>{0,0.8,0.9,1,1.05})*{200,250,300,450,550})"

例311,"提取產(chǎn)品最后報價和最高報價:{=INDEX(C:C,MAX((A2:A11=""B"")*ROW(2:11)))}"

例312,"計算衛(wèi)冕失敗最多的次數(shù):{=MAX(FREQUENCY(ROW(2:11),((B2:B10=""第一名"")<>(B3:B11=""第一名""))*ROW(2:10)))}"

例313,"低于平均成績中的最優(yōu)成績:{=MAX(IF(B2:B11<AVERAGE(B2:B11),B2:B11,))}"

例314,"計算語文成績大于90分者的最高總成績:=DMAX(A1:E11,5,G1:G2)"

例315,"計算數(shù)學(xué)成績等于100分的男生最高總成績:=DMAX(A1:E11,""總分"",B1:B2)"

例316,"根據(jù)下拉列表計算不同項目的最大值:=DMAX(A1:E11,G4,G1:G2)"

例317,計算中間成績:=MEDIAN(B2:B11)

例318,"顯示動態(tài)日期，但不能超過9月30日:=MIN(""2008-9-30"",TODAY())"

例319,"根據(jù)工作時間計算可休假天數(shù):=MIN(SUM((B2={""A"",""B"",""C""})*{5,4,3})+(C2-1),10)"

例320,"確定最佳成績:=MATCH(MIN(B2:B11),B2:B11,)"

例321,"計算文具類產(chǎn)品和家具類產(chǎn)品最小利率:{=TEXT(MIN(IF(ISNUMBER(SEARCH(""（?具類"",A2:A11)),B2:B11)),""0.00%"")}"

例322,"計算得票最少者有幾票:{=MIN(COUNTIF(B2:C11,B2:C11))}"

例323,"根據(jù)工程的難度系數(shù)計算獎金:=MIN(A2,1+(A2>1.3)*0.3)*500"

例324,"將科目與成績分開:{=MID(A2,MIN(IF(ISNUMBER(FIND(ROW($1:$9),A2)),FIND(ROW($1:$9),A2))),100)}"

例325,"計算五個班的第一名人員的最低成績:=MIN(SUBTOTAL(4,INDIRECT({""一"",""二"",""三"",""四"",""五""}&""班!B2:b11"")))"

例326,"根據(jù)員工生產(chǎn)產(chǎn)品的廢品率記分:=MAX(MIN(6-(B2*100-5),10),0)"

例327,"統(tǒng)計售價850元以上的產(chǎn)品最低利率是多少:=DMIN(A1:D11,F4,F1:F2)"

例328,"統(tǒng)計文具類和廚具類產(chǎn)品的最低單價:=DMIN(A1:B11,2,D1:D2)"

例329,"第三個最小的成績:=SMALL(B2:B11,3)"

例330,"計算最后三名成績的平均值:=AVERAGE(SMALL(B2:B11,{1,2,3}))"

例331,"將成績按升序排列:{=SMALL(B$2:B$11,ROW(A1))}"

例332,"羅列三個班第一名成績:{=SMALL(IF(C$2:C$11=""第一名"",D$2:D$11),ROW(A1))}"

例333,"將英文月份名稱升序排列:{=INDEX(A$2:A$13,SMALL(IF(CODE($A$2:$A$13)=SMALL(CODE(A$2:A$13),ROW(A1)),ROW($1:$12)),COUNTIF(C$1:C1,CHAR(SMALL(CODE(A$2:A$13),ROW(A1)))&""*"")+1))}"

例334,"查看產(chǎn)品曾經(jīng)銷售的所有價位:{=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,""<=""&E1)))}"

例335,"羅列三個工作表B列最后三名成績:=SMALL(一班:三班!B:B,ROW(A1))"

例336,"第3個最小成績到第6個最小成績之間的人數(shù):{=SUM((((SMALL(B2:D11,ROW(INDIRECT(""1:""&COUNT(B2:D11))))>SMALL(B2:D11,{3,6}))*{1,-1})))}"

例337,"計算與第3個最大值并列的個數(shù):{=SUM(--(B2:B11=LARGE(B2:B11,3)))}"

例338,"計算大于等于前10個最大產(chǎn)量之和:=SUMPRODUCT((B2:C11>LARGE(B2:C11,11))*B2:C11)"

例339,"按成績列出學(xué)生排行榜:{=INDEX(A$2:A$11,MATCH(LARGE(10-ROW($2:$11)+B$2:B$11*1000,ROW(A1)),10-ROW($2:$11)+B$2:B$11*1000,0))}"

例340,"最后一次獲得第一名是第幾屆:{=INDEX(A:A,LARGE((B2:B11=""第一名"")*ROW(2:11),1))}"

例341,"提取銷量的前三名的外銷產(chǎn)品名稱:{=LOOKUP(0,0/($B$2:$B$10*100+ROW($2:$10)=(LARGE(IF(RIGHT(A$2:A$10,3)=""外銷）"",B$2:B$10*100+ROW($2:$10)),ROW(A1)))),A$2:A$10)}"

例342,"哪種產(chǎn)品生產(chǎn)次數(shù)最多:{=TEXT(MODE(B2:B9*1),""00"")}"

例343,"羅列出被投訴多次的工作人員編號:{=IFERROR(TEXT(MODE(IF(COUNTIF($D$1:D1,$B$2:$B$11)=0,$B$2:$B$11*1)),""00""),"""")}"

例344,"對學(xué)生成績排名:=RANK(B2,B$2:B$11,0)"

例345,"計算兩列數(shù)值相同個數(shù):=COUNT(RANK(B2:B11,C2:C11))"

例346,"查詢某人成績在三個班中的排名:成績{=LOOKUP(0,0/(E2:E11=H2),F2:F11)};名次=RANK(I2,(B2:B11,D2:D11,F2:F11),0)"

例347,"分別統(tǒng)計每個分?jǐn)?shù)段的人員個數(shù):{=FREQUENCY(B2:B11,D2:D5)}"

例348,"蟬聯(lián)冠軍最多的次數(shù):{=MAX(FREQUENCY(ROW(B$2:B$11),(B$2:B$10<>B$3:B$11)*ROW(B$2:B$10)))}"

例349,"計算最多經(jīng)過幾次測試才成功:{=MAX(FREQUENCY(ROW(2:11),(B2:B11=""成功"")*ROW(2:11)))}"

例350,"計算三個不連續(xù)區(qū)間的頻率分布:{=SUM(LOOKUP({1,3,5},ROW(1:5),FREQUENCY(B2:B11,{500,550,600,650})))}"

例351,"計算因密碼錯誤被鎖定幾次:{=COUNT(0/((FREQUENCY(ROW(2:12),(B2:B12<>""錯誤"")*ROW(B2:B12))-1)>=3))}"

例352,"計算小學(xué)加初中人數(shù)及中專加大學(xué)人數(shù):{=FREQUENCY((B2:B11<>""小學(xué)"")*(B2:B11<>""初中""),0)}"

例353,"計算文本的頻率分布:{=FREQUENCY(CODE(B2:B11),CODE(D2:D5))}"

例354,"奪冠排行榜:{=IF(ROW(A1)>S