客服熱線
186-8811-5347、186-7086-0265
官方郵箱
contactus@mingting.cn
excel函數(shù)公式大全
2022-06-01 來(lái)源：金山毒霸文檔/文件服務(wù)作者：辦公技巧
??函數(shù)公式我們?cè)诠ぷ髦薪?jīng)常用到，那excel如何使用函數(shù)公式呢?下面就是excel使用函數(shù)公式教程，一起來(lái)看看吧。
??excel函數(shù)公式實(shí)例大全
??excel函數(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:B7,D2:D7,F2:F7)
??對(duì)生產(chǎn)表中大于100的產(chǎn)量進(jìn)行求和:{=SUM((B2:B11>100)*B2:B11)}
??對(duì)生產(chǎn)表大于110或者小于100的數(shù)據(jù)求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)}
??對(duì)一車間男性職工的工資求和:{=SUM((B2:B10="一車間")*(C2:C10="男")*D2:D10)}
??對(duì)姓趙的女職工工資求和:{=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)
??求圖書訂購(gòu)價(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({1,2,3,4,5},A組!B2:B9,B組!B2:B9,C組!B2:B9,D組!B2:B9,E組!B2:B9),ROW(1:3)))}
??計(jì)算倉(cāng)庫(kù)進(jìn)庫(kù)數(shù)量之和:=SUMIF(B2:B10,"=進(jìn)庫(kù)",C2:C10)
??計(jì)算倉(cāng)庫(kù)大額進(jìn)庫(kù)數(shù)量之和:=SUMIF(B2:B8,">1000")
??對(duì)1400到1600之間的工資求和:{=SUM(SUMIF(B2:B10,"<="&{1400,1600})*{-1,1})}
??求前三名和后三名的數(shù)據(jù)之和:=SUMIF(B2:B10,">"&LARGE(B2:B10,4))+SUMIF(B2:B10,"<"&SMALL(B2:B10,4))
??對(duì)所有車間人員的工資求和:=SUMIF(A2:A10,"?車間",C2)
??對(duì)多個(gè)車間人員的工資求和:=SUMIF(A2:A10,"??車間*",C2)
??匯總姓趙、劉、李的業(yè)務(wù)員提成金額:=SUM(SUMIF(A2:A10,{"趙","劉","李"}&"*",C2:C10))
??匯總鼠標(biāo)所在列中大于600的數(shù)據(jù):=SUMIF(INDIRECT("R2C"&CELL("col")&":R8C"&CELL("col"),FALSE),">600")
??只匯總60～80分的成績(jī):=SUMIFS(B2:B10,B2:B10,">=60",B2:B10,"<=80")
??匯總?cè)昙?jí)二班人員遲到次數(shù):=SUMIFS(D2:D10,B2:B10,"三年級(jí)",C2:C10,"二班")
??匯總車間女性人數(shù):=SUMIFS(C2:C11,A2:A11,"*車間",B2:B11,"女")
??計(jì)算車間男性與女性人員的差:=SUM(SUMIFS(C2:C11,B2:B11,{"女","男"},A2:A11,"*車間")*{-1,1})
??計(jì)算參保人數(shù):=SUMPRODUCT((C2:C11="是")*1)
??求25歲以上男性人數(shù):=SUMPRODUCT((B2:B10="男")*1,(C2:C10>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è)績(jī):=SUMPRODUCT((B2:B11={"江西","廣東"})*(C2:C11="男")*D2:D11)
??根據(jù)直角三角形之勾、股求其弦長(zhǎng):=POWER(SUMSQ(B1,B2),1/2)
??計(jì)算A1：A10區(qū)域正數(shù)的平方和:{=SUMSQ(IF(A1:A10>0,A1:A10))}
??根據(jù)二邊長(zhǎng)判斷三角形是否為直角三角形:=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(B1:B3,{1,2,3})))^0.5
??根據(jù)直角三角形三邊求三角形面積:=PRODUCT(LARGE(B1:B3,{2,3}))/2
??跨表求積:=PRODUCT(產(chǎn)量表:單價(jià)表!B2)
??求不同單價(jià)下的利潤(rùn):{=MMULT(B2:B10,G2:H2)*25%}
??制作中文九九乘法表:=COLUMN()&"*"&ROW()&"="&MMULT(ROW(),COLUMN())
??計(jì)算車間盈虧:=SUM(MMULT((B3:E5>0)*B3:E5,{1;1;1;1}),MMULT((B3:E5<0)*B3:E5,{1;1;1;1}))
??計(jì)算各組別第三名產(chǎn)量是多少:{=MAX(MMULT(COLUMN(A:E)^0,B2:G6))}
??計(jì)算C產(chǎn)品最大入庫(kù)量:{=MAX(MMULT(N(A2:A11="C"),TRANSPOSE((B2:B11)*(A2:A11="C"))))}
??求入庫(kù)最多的產(chǎn)品數(shù)量:{=MAX(MMULT(TRANSPOSE((B2:B11)*(A2:A11={"A","B","C","D"})),(A2:A11={"A","B","C","D"})*1))}
??計(jì)算累計(jì)入庫(kù)數(shù):{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11)}
??計(jì)算每日庫(kù)存數(shù):{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11-C2:C11)}
??計(jì)算A產(chǎn)品每日庫(kù)存數(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ù):{=SUM(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)-1))
??計(jì)算達(dá)標(biāo)率:{=MMULT(TRANSPOSE(N(A2:A11<=(B2:B11))),ROW(2:11)^0)/ROWS(2:11)}
??計(jì)算成績(jī)?cè)?0-80分之間合計(jì)數(shù)與個(gè)數(shù):求和{=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11<80)*B2:B11),ROW(2:11)^0)}，求個(gè)數(shù){=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11<80)),ROW(2:11)^0)}
??匯總A組男職工的工資:{=MMULT(TRANSPOSE(N(B2:B11&C2:C11="男A組")*D2:D11),ROW(2:11)^0)}
??計(jì)算象棋比賽對(duì)局次數(shù)l:=COMBIN(B1,B2)
??計(jì)算五項(xiàng)比賽對(duì)局總次數(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ì)算最大公約數(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),"男","女")
??每隔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),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ī)計(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,80000,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é)校植樹(shù)人員隨機(jī)分組:=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之間的隨機(jī)整數(shù):=RANDBETWEEN(-50,100)
??產(chǎn)生1到100之問(wèn)的奇數(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ù)三角形三邊長(zhǎng)求證三角形是直角三角形:=IF(POWER(MAX(B1:B3),2)=SUM(POWER(LARGE(B1:B3,{2,3}),2)),"是","不是")
??計(jì)算Al:A10區(qū)域開(kāi)三次方之平均值:{=AVERAGE(POWER(A1:A10,1/30))}
??計(jì)算Al:A10區(qū)域倒數(shù)之積:{=PRODUCT(POWER(A1:A10,-1))}
??根據(jù)等邊三角形周長(zhǎng)計(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ì)購(gòu)物金額、保留一位小數(shù):=TRUNC(SUMPRODUCT(B2:B10,C2:C10),1)
??將每項(xiàng)購(gòu)物金額保留一位小數(shù)再合計(jì):=SUMPRODUCT(TRUNC(B2:B10*C2:C10,1))
??將金額進(jìn)行四舍六入五單雙:=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))
??根據(jù)重量單價(jià)計(jì)算金額，結(jié)果以萬(wàn)為單位:=TRUNC(SUMPRODUCT(B2:B10,C2:C10),-4)/10000
??計(jì)算年假天數(shù):=TRUNC((TODAY()-B2)*((TODAY()-B2)>=365)/365*5)
??根據(jù)上機(jī)時(shí)間計(jì)算上網(wǎng)費(fèi)用:=(TRUNC(B2)+(B2-TRUNC(B2)>=0.5))*1.5+(MOD(B2,1)<0.5)
??將金額見(jiàn)角進(jìn)元與見(jiàn)分進(jìn)元:見(jiàn)分進(jìn)元=CEILING(TRUNC(A2,2),1);見(jiàn)角進(jìn)元=CEILING(TRUNC(A2,1),1)
??分別統(tǒng)計(jì)收支金額并忽略小數(shù):收入合計(jì)=SUMPRODUCT(INT(B2:B8));支出合計(jì)=SUMPRODUCT(TRUNC(C2:C8))
??成績(jī)表的格式轉(zhuǎn)換:姓名=INDEX(A:A,INT((ROW(A6))/3));科目=INDEX(B$1:D$1,1,MOD((ROW(A1)-1),3)+1);成績(jī)=INDEX($B$2:$D$7,INT((ROW(A1)-1)/3)+1,MOD((ROW(A1)-1),3)+1)
??隔兩行進(jìn)行編號(hào):=IF(MOD(ROW(),3)=1,INT(ROW(A3)/3),"")
??INT函數(shù)在序列中的復(fù)雜運(yùn)用:=INT(SQRT(2*ROW(A1))+0.5);=10^INT((ROW()-1)/2);=INT(10^(ROW())/9);=INT((ROW(A2))*2/3)
??統(tǒng)計(jì)交易損失金額:=SUMPRODUCT(B2:B11-CEILING(B2:B11,0.1))
??根據(jù)員工工齡計(jì)算年資:=C2+CEILING(B2*30,30)*(INT(B2)>0)
??成績(jī)表轉(zhuǎn)換:=INDEX($A:$E,CEILING(ROW()*3/5,3)-(COLUMN()=7),MOD(ROW(B2)-1,5)+1)
??計(jì)算機(jī)上網(wǎng)費(fèi)用:=CEILING(B2,30)/30*2
??統(tǒng)計(jì)可組建的球隊(duì)總數(shù):=SUMPRODUCT(FLOOR(B2:B10,5)/5)
??統(tǒng)計(jì)業(yè)務(wù)員提成金額，不足20000元忽略:=FLOOR(B2,20000)/20000*500
??FLOOR函數(shù)處理正負(fù)數(shù)混合區(qū)域:=FLOOR(A1*100,10*(IF(A1>0,1,-10)))
??將數(shù)據(jù)轉(zhuǎn)換成接近6的倍數(shù):=MROUND(A1,6)
??以超產(chǎn)80為單位計(jì)算超產(chǎn)獎(jiǎng):{=SUM(MROUND(B2:B11-700,80*IF(B2:B11>=700,1,-1)))/80*50}
??將統(tǒng)計(jì)金額保留到分位:=ROUND(SUMPRODUCT(B2:B10,C2:C10),2)
??將統(tǒng)計(jì)金額轉(zhuǎn)換成以萬(wàn)元為單位:=ROUND(SUMPRODUCT(B2:B10,C2:C10)%%,)
??對(duì)單價(jià)計(jì)量單位不同的品名匯總金額:{=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10="G",1000,1),(D2:D10="G")*2))}
??將金額保留“角”位，忽略“分”位:{=SUM(ROUNDDOWN(B2:B10*C2:C10,1))}
??計(jì)算需要多少零鈔:{=SUM(ROUNDDOWN(B2:B10*C2:C10,{0,-1})*{1,-1})}
??計(jì)算值為l萬(wàn)的整數(shù)倍數(shù)的數(shù)據(jù)個(gè)數(shù):{=SUM(N((B2:B10*C2:C10)=ROUNDDOWN(B2:B10*C2:C10,-4)))}
??計(jì)算完成工程需求人數(shù):{=SUM(ROUNDUP(B2:B11/C2:C11,))}
??按需求對(duì)成績(jī)進(jìn)行分類匯總:=SUBTOTAL(HLOOKUP(G$1,{"平均成績(jī)","科目數(shù)量","最高成績(jī)","最低成績(jī)","成績(jī)合計(jì)";1,2,4,5,9},2,0),B2:D2)
??不間斷的序號(hào):=SUBTOTAL(103,$B$2:B2)
??僅對(duì)篩選出的人員排名次:{=CONCATENATE("第",SUM(N(IF((SUBTOTAL(103,OFFSET(優(yōu)等生!A$1,ROW($2:$31)-2,)))=1,$C$2:$C$31,)>C2))+1,"名")}
??判斷兩列數(shù)據(jù)是否相等:
??計(jì)算兩列數(shù)據(jù)同行相等的個(gè)數(shù):{=SUM(N(A1:A10=B1:B10))}
??計(jì)算同行相等且長(zhǎng)度為3的個(gè)數(shù):{=SUM((A1:A10=B1:B10)*(LEN(A1:A10)=3))}
??提取A產(chǎn)品最后單價(jià):{=INDEX(C:C,MAX((B2:B10="A")*ROW(2:10)))}
??判斷學(xué)生是否符合獎(jiǎng)學(xué)金發(fā)放條件:=AND(B2>90,C2<>"漢族")
??所有裁判都給“通過(guò)”就進(jìn)入決賽:{=AND(B2:E2="通過(guò)")}
??判斷身份證長(zhǎng)度是否正確:=OR(LEN(B2)={15,18})
??判斷歌手是否被淘汰:{=OR(B2:E2="不通過(guò)")}
??根據(jù)年齡判斷職工是否退休:=OR(AND(B2="男",C2>60),AND(B2="女",C2>55))
??根據(jù)年齡與職務(wù)判斷職工是否退休:=OR(AND(B2="男",D2>60+(C2="干部")*3),AND(B2="女",D2>55+(C2="干部")*3))
??沒(méi)有任何裁判給“不通過(guò)”就進(jìn)行決賽:{=NOT(OR(B2:E2="不通過(guò)"))}
??計(jì)彝成績(jī)區(qū)域數(shù)字個(gè)數(shù):{=SUM(NOT(ISERROR(NOT(B2:B11)))*1)}
??評(píng)定學(xué)生成績(jī)是否及格:=IF(AVERAGE(B2:D2)>=60,"及格","不及格")
??根據(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)秀","滿分")))
??根據(jù)業(yè)績(jī)計(jì)算需要發(fā)放多少獎(jiǎng)金:{=SUM(IF(B2:B11>80000,1000,500))}
??根據(jù)工作時(shí)間計(jì)算12月工資:=C2+SUM(IF(B2>{0,1,3,5,10},{300,500,500,500,500}))
??合計(jì)區(qū)域的值并忽略錯(cuò)誤值:{=SUM(IF(ISERROR(A1:C10),0,A1:C10))}
??既求積也求和:=IF(D2<>"",PRODUCT(C2:D2),SUM(OFFSET(E2,-3,,3)))
??分別統(tǒng)計(jì)收入和支出:收入{=SUM(IF(B2:B13>0,B2:B13))};支出{=SUM(IF(SUBSTITUTE(IF(B2:B13<>"",B2:B13,0),"負(fù)","-")*1<0,SUBSTITUTE(B2:B13,"負(fù)","-")*1))}
??將成績(jī)從大到小排列:{=IF(ROW(A1)>COUNT(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))}
??混合單價(jià)求金額合計(jì):{=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10="K",1000,1),2))}
??計(jì)算異常停機(jī)時(shí)間:{=SUM(SUBSTITUTE(SUBSTITUTE(IF(C2:C11<>"",C2:C11,0),"修機(jī)",""),"換原料","")*1)}
??計(jì)算最大數(shù)字行與文本行:{=MAX(IF(B:B<>"",ROW(A:A)))}
??找出誰(shuí)奪冠次數(shù)最多:{=INDEX(B:B,MIN(IF(MAX(COUNTIF(B2:B12,B2:B12))=COUNTIF(B2:B12,B2:B12),ROW(2:12))))}
??將全角字符轉(zhuǎn)換為半角:=ASC(A2)
??計(jì)算漢字全角半角混合字符串中的字母?jìng)€(gè)數(shù):=LEN(ASC(A2))*2-LENB(ASC(A2))
??將半角字符轉(zhuǎn)換成全角顯示:=WIDECHAR(A2)
??計(jì)算混合字符串中漢字個(gè)數(shù):=LEN(A2)-(LENB(WIDECHAR(A2))-LENB(ASC(A2)))
??判斷單元格首字符是否為字母:=OR(AND(CODE(A2)>64,CODE(A2)<91),AND(CODE(A2)>96,CODE(A2)<123))
??計(jì)算單元格中數(shù)字個(gè)數(shù):{=SUM((CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))>47)*(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<58))}
??計(jì)算單元格中大寫加小寫字母?jìng)€(gè)數(shù):{=SUM((CODE(UPPER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))>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()<27,CHAR(MOD(ROW()-1,26)+65),CHAR(65+(ROW()-1)/26-1))&IF(ROW()>26,CHAR(MOD(ROW()-1,26)+65),"")
??產(chǎn)生三個(gè)字母組成的隨機(jī)字符串:=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)))}
??返回自動(dòng)換行單元格的第二行數(shù)據(jù):=RIGHT(A2,LEN(A2)-FIND(CHAR(10),A2))
??根據(jù)身份證號(hào)碼提取出生年月日:=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),"日?")
??計(jì)算平均成績(jī)及評(píng)判是否及格:=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)
??計(jì)算字符串中英文字母?jìng)€(gè)數(shù):{=SUM(N(NOT(EXACT(UPPER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),LOWER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))))))}
??計(jì)算字符串中單詞個(gè)數(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))}
??計(jì)算字符串中的數(shù)字個(gè)數(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(INDI
??串聯(lián)區(qū)域中的文本:=CONCATENATE(T(A2),T(B2),T(C2))
??給公式添加運(yùn)算說(shuō)明:=CONCATENATE("你好",B2,"2008")&T(N("公式含義:連接“你好”和單元格B2、“2008”"))
??根據(jù)身份證號(hào)碼判斷性別:=TEXT(MOD(MID(B2,15,3),2),"[=1]男;[=0]女")
??將所有數(shù)據(jù)轉(zhuǎn)換成保留兩位小數(shù)再求和:{=SUM(--TEXT(B2:B11*C2:C11,"0.00"))}
??將貨款顯示為“萬(wàn)元”為單位:=TEXT(B2,"￥#"&""""&"."&""""&"#,萬(wàn)元")
??根據(jù)身份證號(hào)碼計(jì)算出生日期:=IF(LEN(B2)=15,19,"")&TEXT(MID(B2,7,8-(LEN(B2)=15)*2),"#年00月00日")
??顯示今天的英文日期及星期:="資料日期："&TEXT(TODAY(),"dddd,?mmmm?dd,?yyyy")
??顯示今天每項(xiàng)工程的預(yù)計(jì)完成時(shí)間:=TEXT(SUM("08:00",B$2:B2),"h:mm:ss?上午/下午")
??統(tǒng)計(jì)A列有多少個(gè)星期日:{=SUM(N(TEXT(A1:A11,"aaa")="日"))}
??將數(shù)據(jù)顯示為小數(shù)點(diǎn)對(duì)齊:=TEXT(B2,"#.0????")
??計(jì)算A列的日期有幾個(gè)屬于第二季度:{=SUM((--(TEXT(A1:A11,"m"))>{3,6})*{1,-1})}
??在A列產(chǎn)生1到12月的英文月份名:=TEXT((ROW())&"-1","mmmm")
??將日期顯示為中文大寫:=TEXT("2008-8-10","[DBNum2]yyyy年m月d日")
??將數(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,,"零")),"零分",""))
??判斷單元格的數(shù)據(jù)類型:=TEXT(A2,"大于○;小于○;○;文本")
??計(jì)算達(dá)成率，以不同格式顯示:=TEXT(B2/800,"[>=1]0.0倍;[>0]0.00%;")
??計(jì)算字母“A”的首次出現(xiàn)位置，忽略大小寫:=TEXT(SEARCH("a",A2&"a"),"[>"&LEN(A2)&"]沒(méi)找到;第"&SEARCH("a",A2&"a")&"個(gè)")
??從身份證號(hào)碼中提取表示性別的數(shù)字:=MID(B2,TEXT(LEN(B2),"[=15]15;17"),1)
??將三列數(shù)據(jù)交換位置:{=TEXT({1,-1,0},C1:C5&";"&"!"&B1:B5&";"&A1:A5)}
??計(jì)算年終獎(jiǎng):=TEXT(B2,"[>3]15!0!0;[>1]1!0!0!0;5!0!0;")
??計(jì)算星期日完工的工程個(gè)數(shù):{=COUNT((TEXT(B2:B10+C2:C10-1,"AAA")="日")^0)}
??計(jì)算本月星期日的個(gè)數(shù):{=SUM(N(TEXT(TODAY()-TEXT(TODAY(),"d")+ROW(INDIRECT("1:"&DAY(DATE(,TEXT(TODAY(),"m")+1,)))),"AAA")="日"))}
??檢驗(yàn)日期是否升序排列:=TEXT(N(A3>=A2),";;日期有誤;")
??判斷單元格中首字符的類型:=TEXT(IF(AND(CODE(UPPER(A3))>64,CODE(UPPER(A3))<91),CODE(A3),A3),"[="&CODE(A3)&"]字母;;數(shù)字;漢字")
??計(jì)算每個(gè)季度的天數(shù):{=SUM(--TEXT(DATE(2008,3*ROW(A1)-ROW($1:$3)+2,),"d"))}
??將數(shù)據(jù)重復(fù)顯示5次:=SUBSTITUTE(TEXT(A2&"?","@@@@@"),"?","")
??將表示起止時(shí)間的數(shù)字格式化為時(shí)間格式:=TEXT(B2,"#!:00-00!:00")
??根據(jù)起止時(shí)間計(jì)算經(jīng)過(guò)時(shí)間:=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小時(shí).00分鐘")
??將數(shù)字轉(zhuǎn)化成電話格式:=TEXT(A2,"(0000)0000-0000")
??在A1:A7區(qū)域產(chǎn)生星期一到星期日的英文全稱:{=TEXT(ROW(1:7)+1,"DDDD")}
??將匯總金額保留一位小數(shù)并顯示千分位分隔符:{=FIXED(SUM(--FIXED(B2:B11*C2:C11,1)),1,FALSE)}
??計(jì)算訂單金額并以“百萬(wàn)”為單位顯示:=FIXED(SUMPRODUCT(B2:B10,C2:C10),-6)/1000000
??將數(shù)據(jù)對(duì)齊顯示，將空白以“.”占位:=WIDECHAR(REPT(".",10-LEN(B2))&B2)
??利用公式制作簡(jiǎn)易圖表:=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))
??利用公式制作帶軸的圖表且標(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))))))}
??計(jì)算單元格中數(shù)字個(gè)數(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)))}
??計(jì)算購(gòu)物金額中小數(shù)位數(shù)最多是幾:{=MAX(LEN(B2:B10*C2:C10)-LEN(INT(B2:B10*C2:C10)))-1}
??計(jì)算英文句子中有幾個(gè)單詞:=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)
??將分?jǐn)?shù)轉(zhuǎn)換成小數(shù):=(LEFT(A2,FIND("/",A2)-1)+RIGHT(A2,LEN(A2)-FIND("/",A2)))/2
??從英文短句中提取每一個(gè)單詞:=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ī)格計(jì)算產(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)))
??提取括號(hào)中的字符串:=IFERROR(MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1),"")
??分別提取長(zhǎng)、寬、高:=MID($B2,FIND("@",SUBSTITUTE($B2,"(","@",COLUMN(A1)))+1,FIND("@",SUBSTITUTE($B2,")","@",COLUMN(A1)))-FIND("@",SUBSTITUTE($B2,"(","@",COLUMN(A1)))-1)
??提取學(xué)校與醫(yī)院地址:{=IF(OR(IFERROR(FIND({"學(xué)校","醫(yī)院"},A2),FALSE)),A2,"")}
??計(jì)算密碼字符串中字符個(gè)數(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位身份證號(hào)碼升級(jí)為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)品型號(hào)規(guī)范化:=IF(MID(A2,5,2)="00",A2,REPLACE(A2,5,,"00"))
??求最大時(shí)間:{=TEXT(MAX(--TEXT(REPLACE(LEFT(A2:A7,7),5,1,RIGHT(A2:A7,2)),"00!:00?00-00")),"hmm/dd/mm")}
??分別提取小時(shí)、分鐘、秒:=REPLACE(REPLACE($A$1&$A2,FIND(B$1,$A$1&$A2),100,),1,FIND(A$1,$A$1&$A2)+1,)
??將年級(jí)或者專業(yè)與班級(jí)名稱分開(kāi):{=REPLACE(A2,MAX(IFERROR(SEARCH(CHAR(ROW($65:$90)),A
??提取各
??店名分類:=IF(COUNT(SEARCH({"小吃","酒吧","茶","咖啡","電影","休閑","網(wǎng)吧"},A2))=1,"餐飲娛樂(lè)",IF(COUNT(SEARCH({"干洗","醫(yī)院","藥","茶","蛋糕","面包","物流","駕校","開(kāi)鎖","家政","裝飾","搬家","維修","中介","衛(wèi)生","旅館"},A2))=1,"便民服務(wù)",IF(COUNT(SEARCH({"游樂(lè)場(chǎng)","旅行社","旅游"},A2))=1,"旅游")))
??查找編號(hào)中重復(fù)出現(xiàn)的數(shù)字:重復(fù)數(shù)字個(gè)數(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,)
??統(tǒng)計(jì)名為“劉星”者人數(shù):{=COUNT(SEARCH("?劉星",A2:A9))}
??剔除多余的省名:=SUBSTITUTE(A2,IF(ISERROR(SEARCH("重慶市",A2)),"","四川省"),"")
??將日期規(guī)范化再求差:=SUBSTITUTE(C2,".","-")-SUBSTITUTE(B2,".","-")
??提取兩個(gè)符號(hào)之間的字符串:=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)
??提取最后一次短跑成績(jī):=REPLACE(A2,1,FIND("々",SUBSTITUTE(A2,"|","々",LEN(A2)-LEN(SUBSTITUTE(A2,"|",)))),)
??從地址中提取省名:=LEFT(A2,FIND("省",A2))
??計(jì)算小學(xué)參賽者人數(shù):{=COUNT(0/(LEFT(B2:B11)="小"))}
??計(jì)算四川方向飛機(jī)票總價(jià):=SUMPRODUCT(N(LEFT(A2:A11,2)="四川"),N(B2:B11="飛機(jī)"),C2:C11)
??通過(guò)身份證號(hào)碼計(jì)算年齡:=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()))
??提取成績(jī)并計(jì)算平均:{=AVERAGE(MID(A2:A7,4,LEN(A2:A7)-3)*1)}
??提取參賽選手姓名:=MID(A2,FIND("：",A2)+1,LEN(A2))
??從混合字符串中提取金額:=LOOKUP(307,--MID(B2,MIN(FIND({1;2;3;4;5;6;7;8;9},B2&123456789)),ROW($1:$99)))
??從卡機(jī)數(shù)據(jù)提取打卡時(shí)間:=730>--MID(A2,14,4)
??根據(jù)卡機(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)部","采購(gòu)部","送貨部","財(cái)務(wù)部")
??根據(jù)身份證號(hào)碼統(tǒng)計(jì)男性人數(shù):{=SUM(MOD(LEFT(RIGHT(B2:B11,1+(LEN(B2:B11)=18))),2))}
??從漢字與數(shù)字混合字串中提取溫度數(shù)據(jù):{=MAX(IFERROR(--RIGHT(LEFT(B2,LEN(B2)-1),ROW($1:$10)),0))}
??將字符串位數(shù)統(tǒng)一:{=TEXT(RIGHT(A2,LEN(A2)-1),"!"&LEFT(A2)&REPT(0,MAX(LEN(A$2:A$10))-1))}
??對(duì)所有人員按平均分排序:{=INDEX(A:A,RIGHT(LARGE(B$2:B$11*1000+ROW($2:$11),ROW()-1),3))}
??取金額的角位與分位叫:=--RIGHT(ROUND(A2*100,),2)
??從格式不規(guī)范的日期中取出日:=TRIM(RIGHT(SUBSTITUTE(A2,".","?",2),3))
??計(jì)算平均成績(jī)(忽略缺考人員):=ROUND(AVERAGE(B2:B10),2)
??計(jì)算90分以上的平均成績(jī):{=ROUND(AVERAGE(IF(ISNUMBER(B2:B10)*(B2:B10>90),B2:B10)),2)}
??計(jì)算當(dāng)前表以外的所有工作表平均值2:=AVERAGE(一班:五班!B:B)
??計(jì)算二車間女職工的平均工資:{=AVERAGE(IF((B2:B10="二車間")*(C2:C10="女"),D2:D10))}
??計(jì)算一車間和三車間女職工的平均工資:{=AVERAGE(IF((B2:B10="一車間")+(B2:B10="三車間")*(C2:C10="女"),D2:D10))}
??計(jì)算各業(yè)務(wù)員的平均獎(jiǎng)金:{=AVERAGE(1500+300*(INT((C2:C11-80000)/10000)))}
??計(jì)算平均工資(不忽略無(wú)薪人員):=ROUND(AVERAGEA(B2:B10),2)
??計(jì)算每人平均出口量:{=AVERAGEA((C2:C11="A")*D2:D11)}
??計(jì)算平均成績(jī)，成績(jī)空白也計(jì)算:{=AVERAGEA(B2:B11*1)}
??計(jì)算二年級(jí)所有人員的平均獲獎(jiǎng)率:{=TEXT(AVERAGEA(IF(LEFT(A2:A10,3)="二年級(jí)",B2:B10/C2:C10)),"0.00%")}
??統(tǒng)計(jì)前三名人員的平均成績(jī):=AVERAGEA(LARGE(B2:B11,{1,2,3}))
??求每季度平均支出金額:=AVERAGEIF(B2:B9,"支出",C2)
??計(jì)算每個(gè)車間大于250的平均產(chǎn)量:=AVERAGEIF(B2:C11,">250")
??去掉首尾求平均:=AVERAGEIFS(B2:B11,B2:B11,">"&MIN(B2:B11),B2:B11,"<"&MAX(B2:B11))
??生產(chǎn)A產(chǎn)品且無(wú)異常的機(jī)臺(tái)平均產(chǎn)量:=AVERAGEIFS(C2:C11,B2:B11,"A",D2:D11,"")
??計(jì)算生產(chǎn)車間異常機(jī)臺(tái)個(gè)數(shù):=COUNT(C2:C11)
??計(jì)算及格率:{=TEXT(COUNT(0/(B2:B11>=60))/COUNT(B2:B11),"0.00%")}
??統(tǒng)計(jì)屬于餐飲娛樂(lè)業(yè)的店名個(gè)數(shù):{=COUNT(SEARCH({"小吃","酒吧","茶","咖啡","電影","休閑","網(wǎng)吧"},A2:A11))}
??統(tǒng)計(jì)各分?jǐn)?shù)段人數(shù):{=COUNT(0/((B$2:B$11>ROW(A6)*10)*(B$2:B$11<=ROW(A7)*10)))}
??統(tǒng)計(jì)有多少個(gè)選手:{=COUNT(0/(MATCH(B2:B11,B2:B11,)=(ROW(2:11)-1)))}
??統(tǒng)計(jì)出勤異常人數(shù):=COUNTA(B2:B11)
??判斷是否有人缺考:=IF(COUNTA(B2:E10)=ROWS(B2:E10)*COLUMNS(B2:E10),"沒(méi)有","有")
??統(tǒng)計(jì)未檢驗(yàn)完成的產(chǎn)品數(shù):=COUNTBLANK(B2:B11)
??統(tǒng)計(jì)產(chǎn)量達(dá)標(biāo)率:=TEXT(COUNTIF(B2:B11,">=800")/COUNT(B2:B11),"0.00")
??根據(jù)畢業(yè)學(xué)校統(tǒng)計(jì)中學(xué)學(xué)歷人數(shù):=COUNTIF(B2:B11,"*中學(xué)")
??計(jì)算兩列數(shù)據(jù)相同個(gè)數(shù):{=SUM(COUNTIF(A2:A11,B2:B11))}
??統(tǒng)計(jì)連續(xù)三次進(jìn)入前十名的人數(shù):{=SUM(COUNTIF(C2:C11,IF(COUNTIF(A2:A11,B2:B11),B2:B11)))}
??統(tǒng)計(jì)淘汰者人數(shù):{=SUM(N(COUNTIF(A2:C11,A2:C11)=1))}
??統(tǒng)計(jì)區(qū)域中不重復(fù)數(shù)據(jù)個(gè)數(shù):{=SUM(1/COUNTIF(B2:B8,B2:B8))}
??統(tǒng)計(jì)諾基亞、摩托羅拉和聯(lián)想已隹出手機(jī)個(gè)數(shù):=SUM(COUNTIF(B2:B11,"*"&{"諾基亞","摩托羅拉","聯(lián)想"}&"*"))
??統(tǒng)計(jì)聯(lián)想比摩托羅拉手機(jī)的銷量高多少:{=SUM(COUNTIF(B2:B11,{"諾基亞*","*聯(lián)想*"})*{1,-1})}
??統(tǒng)計(jì)冠軍榜前三名:{=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)))}
??統(tǒng)計(jì)真空、假空單元格個(gè)數(shù):=COUNTIF(成績(jī)!C2:C11,"=")
??對(duì)名冊(cè)表進(jìn)行混合編號(hào):=IF(RIGHT(B1)<>"班",ROW()-COUNTIF($B$1:B1,"??班"),TEXT(COUNTIF($B$1:B1,"??班"),"[DBNum2]0"))
??提取不重復(fù)數(shù)據(jù)5:{=INDEX(B:B,MATCH(0,COUNTIF($D$1:D1,B$2:B$11),0)+1)}
??中國(guó)式排名:{=SUM(IF(B$2:B$11>B2,1/COUNTIF(B$2:B$11,B$2:B$11)))+1}
??統(tǒng)計(jì)大于80分的三好學(xué)生個(gè)數(shù):{=COUNTIFS(B2:B11,"三好學(xué)生",C2:C11,">80")}
??統(tǒng)計(jì)業(yè)績(jī)?cè)?萬(wàn)到8萬(wàn)之間的女業(yè)務(wù)員個(gè)數(shù):=COUNTIFS(B2:B11,"女",C2:C11,">60000",C2:C11,"<=800000")
??統(tǒng)計(jì)二班和三班數(shù)學(xué)競(jìng)賽獲獎(jiǎng)人數(shù):=SUM(COUNTIFS(B2:B11,{"二班","三班"},C2:C11,"數(shù)學(xué)*"))
??根據(jù)身高計(jì)算各班淘汰人數(shù):=SUM(COUNTIFS(B$2:B$11,E1,C$2:C$11,{"<160",">180"}))
??計(jì)算A列最后一個(gè)非空單元格行號(hào):{=MAX((A:A<>"")*ROW(A:A))}
??計(jì)算女職工的最大年齡:{=MAX((B2:B11="女")*C2:C11)}
??消除單位提取數(shù)據(jù):{=MAX(IFERROR(ABS(LEFT(A2,ROW($1:$100))),))*IF(LEFT(A2)="-",-1,1)}
??計(jì)算單日最高銷售金額:{=MAX(SUMIF(A2:A11,A2:A11,C2:C11))}
??查找第一名學(xué)生姓名:=INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10,))
??統(tǒng)計(jì)季度最高產(chǎn)值合計(jì):{=MAX(SUBTOTAL(9,OFFSET(B2,,COLUMN(B:E)-2,ROWS(2:10),1)))}
??根據(jù)達(dá)標(biāo)率計(jì)算員工獎(jiǎng)金:=MAX((B2>{0,0.8,0.9,1,1.05})*{200,250,300,450,550})
??提取產(chǎn)品最后報(bào)價(jià)和最高報(bào)價(jià):{=INDEX(C:C,MAX((A2:A11="B")*ROW(2:11)))}
??計(jì)算衛(wèi)冕失敗最多的次數(shù):{=MAX(FREQUENCY(ROW(2:11),((B2:B10="第一名")<>(B3:B11="第一名"))*ROW(2:10)))}
??低于平均成績(jī)中的最優(yōu)成績(jī):{=MAX(IF(B2:B11
??計(jì)算語(yǔ)文成績(jī)大于90分者的最高總成績(jī):=DMAX(A1:E11,5,G1:G2)
??計(jì)算數(shù)學(xué)成績(jī)等于100分的男生最高總成績(jī):=DMAX(A1:E11,"總分",B1:B2)
??根據(jù)下拉列表計(jì)算不同項(xiàng)目的最大值:=DMAX(A1:E11,G4,G1:G2)
??計(jì)算中間成績(jī):=MEDIAN(B2:B11)
??顯示動(dòng)態(tài)日期，但不能超過(guò)9月30日:=MIN("2008-9-30",TODAY())
??根據(jù)工作時(shí)間計(jì)算可休假天數(shù):=MIN(SUM((B2={"A","B","C"})*{5,4,3})+(C2-1),10)
??確定最佳成績(jī):=MATCH(MIN(B2:B11),B2:B11,)
??計(jì)算文具類產(chǎn)品和家具類產(chǎn)品最小利率:{=TEXT(MIN(IF(ISNUMBER(SEARCH("(?具類",A2:A11)),B2:B11)),"0.00%")}
??計(jì)算得票最少者有幾票:{=MIN(COUNTIF(B2:C11,B2:C11))}
??根據(jù)工程的難度系數(shù)計(jì)算獎(jiǎng)金:=MIN(A2,1+(A2>1.3)*0.3)*500
??將科目與成績(jī)分開(kāi):{=MID(A2,MIN(IF(ISNUMBER(FIND(ROW($1:$9),A2)),FIND(ROW($1:$9),A2))),100)}
??計(jì)算五個(gè)班的第一名人員的最低成績(jī):=MIN(SUBTOTAL(4,INDIRECT({"一","二","三","四","五"}&"班!B2:b11")))
??根據(jù)員工生產(chǎn)產(chǎn)品的廢品率記分:=MAX(MIN(6-(B2*100-5),10),0)
??統(tǒng)計(jì)售價(jià)850元以上的產(chǎn)品最低利率是多少:=DMIN(A1:D11,F4,F1:F2)
??統(tǒng)計(jì)文具類和廚具類產(chǎn)品的最低單價(jià):=DMIN(A1:B11,2,D1:D2)
??第三個(gè)最小的成績(jī):=SMALL(B2:B11,3)
??計(jì)算最后三名成績(jī)的平均值:=AVERAGE(SMALL(B2:B11,{1,2,3}))
??將成績(jī)按升序排列:{=SMALL(B$2:B$11,ROW(A1))}
??羅列三個(gè)班第一名成績(jī):{=SMALL(IF(C$2:C$11="第一名",D$2:D$11),ROW(A1))}
??將英文月份名稱升序排列:{=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))}
??查看產(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,"<="&E1)))}
??羅列三個(gè)工作表B列最后三名成績(jī):=SMALL(一班:三班!B:B,ROW(A1))
??第3個(gè)最小成績(jī)到第6個(gè)最小成績(jī)之間的人數(shù):{=SUM((((SMALL(B2:D11,ROW(INDIRECT("1:"&COUNT(B2:D11))))>SMALL(B2:D11,{3,6}))*{1,-1})))}
??計(jì)算與第3個(gè)最大值并列的個(gè)數(shù):{=SUM(--(B2:B11=LARGE(B2:B11,3)))}
??計(jì)算大于等于前10個(gè)最大產(chǎn)量之和:=SUMPRODUCT((B2:C11>LARGE(B2:C11,11))*B2:C11)
??按成績(jī)列出學(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))}
??最后一次獲得第一名是第幾屆:{=INDEX(A:A,LARGE((B2:B11="第一名")*ROW(2:11),1))}
??提取銷量的前三名的外銷產(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)}
??哪種產(chǎn)品生產(chǎn)次數(shù)最多:{=TEXT(MODE(B2:B9*1),"00")}
??羅列出被投訴多次的工作人員編號(hào):{=IFERROR(TEXT(MODE(IF(COUNTIF($D$1:D1,$B$2:$B$11)=0,$B$2:$B$11*1)),"00"),"")}
??對(duì)學(xué)生成績(jī)排名:=RANK(B2,B$2:B$11,0)
??計(jì)算兩列數(shù)值相同個(gè)數(shù):=COUNT(RANK(B2:B11,C2:C11))
??查詢某人成績(jī)?cè)谌齻€(gè)班中的排名:成績(jī){=LOOKUP(0,0/(E2:E11=H2),F2:F11)};名次=RANK(I2,(B2:B11,D2:D11,F2:F11),0)
??分別統(tǒng)計(jì)每個(gè)分?jǐn)?shù)段的人員個(gè)數(shù):{=FREQUENCY(B2:B11,D2:D5)}
??蟬聯(lián)冠軍最多的次數(shù):{=MAX(FREQUENCY(ROW(B$2:B$11),(B$2:B$10<>B$3:B$11)*ROW(B$2:B$10)))}
??計(jì)算最多經(jīng)過(guò)幾次測(cè)試才成功:{=MAX(FREQUENCY(ROW(2:11),(B2:B11="成功")*ROW(2:11)))}
??計(jì)算三個(gè)不連續(xù)區(qū)間的頻率分布:{=SUM(LOOKUP({1,3,5},ROW(1:5),FREQUENCY(B2:B11,{500,550,600,650})))}
??計(jì)算因密碼錯(cuò)誤被鎖定幾次:{=COUNT(0/((FREQUENCY(ROW(2:12),(B2:B12<>"錯(cuò)誤")*ROW(B2:B12))-1)>=3))}
??計(jì)算小學(xué)加初中人數(shù)及中專加大學(xué)人數(shù):{=FREQUENCY((B2:B11<>"小學(xué)")*(B2:B11<>"初中"),0)}
??計(jì)算文本的頻率分布:{=FREQUENCY(CODE(B2:B11),CODE(D2:D5))}
??奪冠排行榜:{=IF(ROW(A1)>SUM(1/COUNTIF($B$2:$B$11,$B$2:$B$11)),"",INDEX($B$2:$B$11,MATCH(LARGE(FREQUENCY(MATCH($B$2:$B$11,$B$2:$B$11,),ROW($1:$9))-ROW($1:$10)%,ROW(A1)),FREQUENCY(MATCH($B$2:$B$11,$B$2:$B$11,),ROW($1:$9))-ROW($1:$10)%,)))}
??誰(shuí)蟬聯(lián)冠軍次數(shù)最多:=INDEX(B2:B11,MATCH(MAX(FREQUENCY(ROW(2:11),(B2:B10<>B3:B11)*ROW(2:10))),FREQUENCY(ROW(2:11),(B2:B10<>B3:B11)*ROW(2:10)),))
??中國(guó)式排名:{=SUM(--(IF(FREQUENCY(B$2:B$11,B$2:B$11),B$2:B$11>B2)))+1}
??誰(shuí)獲得第二名:{=INDEX(A:A,SMALL(IF(B$2:B$11=SMALL(IF(FREQUENCY($B$2:$B$11,$B$2:$B$11),$B$2:$B$11),2),ROW($2:$11),1048576),ROW(A1)))&""}
??記錄當(dāng)前日期與時(shí)間:=TEXT(NOW(),"m月d日?h:m:s")
??確定是否已到加油時(shí)間:=TEXT(NOW()-B2,"H:m")>"5:30"
??國(guó)慶倒計(jì)時(shí):=TEXT("10-1"-TEXT(NOW(),"mm-dd"),"00")
??統(tǒng)計(jì)發(fā)貨到收款天數(shù):=ROUNDUP(IF(B2<>"",B2-A2,NOW()-A2),0)
??統(tǒng)計(jì)已到達(dá)收款時(shí)間的貨品數(shù)量:=COUNTIF(B2:B10,"<"&(TODAY()-30))
??本月需要完成幾批貨物生產(chǎn):{=SUM(N(B2:B11=TEXT(TODAY(),"MMMM")))}
??計(jì)算本季度收款的合計(jì):{=SUM(IF(ROUNDUP(B2:B11/3,0)=ROUNDUP(TEXT(TODAY(),"M")/3,0),C2:C11))}
??判斷今年是否閏年:=OR((MOD(TEXT(TODAY(),"yyyy"),4)=0)*(MOD(TEXT(TODAY(),"yyyy"),100)<>0),AND(MOD(TEXT(TODAY(),"yyyy"),{100,400})=0))
??計(jì)算2008年有多少個(gè)星期日:{=SUM(N(TEXT(DATE(2008,1,ROW(INDIRECT("1:"&("2008-12-31"-"2008-1-1")))),"AAA")="日"))}
??計(jì)算本月有多少天:=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0),"D")
??確定今年母親節(jié)的日期:=DATE(YEAR(TODAY()),5,14-WEEKDAY(DATE(YEAR(TODAY()),4,30),2))
??今年包含多少個(gè)星期:{=SUM(N(WEEKDAY(DATE(YEAR(TODAY()),1,ROW(INDIRECT("1:"&(365+(DAY(DATE(YEAR(TODAY()),2,29))=29))))),2)=7))+(WEEKDAY(DATE(YEAR(TODAY()),1,365+(DAY(DATE(YEAR(TODAY()),2,29))=29)))<7)}
??將身份證號(hào)碼轉(zhuǎn)換成出生日期序列:=DATE(MID(B2,7,2+(LEN(B2)=18)*2),MID(B2,9+(LEN(B2)=18)*2,2),MID(B2,11+(LEN(B2)=18)*2,2))
??計(jì)算建國(guó)多少周年:=YEAR(TODAY())-1949
??計(jì)算2000年前電腦培訓(xùn)平均收費(fèi):{=AVERAGE(IF(YEAR(A2:A11)<2000,B2:B11))}
??計(jì)算今天離本年度最后一天的天數(shù):=(YEAR(TODAY())&"-12-31")-TODAY()
??計(jì)算本月需要交貨的數(shù)量:{=SUM((MONTH(B2:B11)=MONTH(TODAY()))*C2:C11)}
??計(jì)算8月份筆筒和毛筆的進(jìn)貨數(shù)量:{=SUM(IF(MONTH(A2:A11)=8,IF((B1:H1="筆筒")+(B1:H1="毛筆"),B2:H11)))}
??計(jì)算交貨起止月:{=MIN(MONTH(B2:B11))&"月-"&MAX(MONTH(B2:B11))&"月"}
??有幾個(gè)月要交貨:{=COUNT(0/FREQUENCY(MONTH(B2:B11),MONTH(B2:B11)))}
??哪幾個(gè)月要交貨:{=IFERROR(SMALL(IF(FREQUENCY(MONTH(B$2:B$11),MONTH(B$2:B$11)),MONTH(B$2:B$11)),ROW(A1))&"月","")}
??統(tǒng)計(jì)家具類和文具類產(chǎn)品在1月份的出庫(kù)次數(shù):{=SUM((B2:B11={"文具類","家具類"})*(IF(C2:C11>0,MONTH(C2:C11)=1)))}
??計(jì)算今年平均每月天數(shù):{=AVERAGE(DAY(DATE(YEAR(TODAY()),ROW(2:13),0)))}
??計(jì)算員工轉(zhuǎn)正時(shí)間:=DATE(YEAR(B2),MONTH(B2)+3+(DAY(B2)>15),16)
??統(tǒng)計(jì)本月下旬出庫(kù)數(shù)量:{=SUM(C2:C11*(DAY(B2:B11)>20))}
??計(jì)算生產(chǎn)速度是否達(dá)標(biāo):=YEARFRAC(C2,D2)<=(E2/B2)
??計(jì)算截至今天的利息:=B2*D2*YEARFRAC(C2,NOW())
??計(jì)算還款日期:=TEXT(EDATE(B2,C2),"yy-mm-dd")
??計(jì)算2008年到2010年共有多少天:{=SUM(DAY(EDATE("2008-1-31",ROW(1:36)-1)))}
??提示合同續(xù)約:=TEXT(EDATE(B2,C2*12)-TODAY(),"[<0]合同過(guò)期;[<=10]即將到期;;")
??計(jì)算借款日期到本月底的天數(shù):=EOMONTH(B2,MONTH(TODAY())-MONTH(B2))-B2
??計(jì)算本季度天數(shù):=SUM(DAY(EOMONTH(NOW(),{0,1,2}-MOD(MONTH(NOW())-1,3))))
??生成工資結(jié)算日期:=TEXT(EOMONTH(B2,0)+1,"e年M月D日")
??統(tǒng)計(jì)兩倍工資的加班小時(shí)數(shù):=SUMPRODUCT(--(TEXT(ROW(INDIRECT(B2&":"&EOMONTH(B2,0))),"AAA")="六"))*8
??計(jì)算員工工作天數(shù)和月數(shù):=DATEDIF(B2,C2,"M")
??根據(jù)進(jìn)廠日期計(jì)算員工可假休天數(shù):=MIN(IF(DATEDIF(B2,TODAY(),"M")<6,0,IF(DATEDIF(B2,TODA
??根據(jù)身份證號(hào)碼計(jì)算年齡(包括年月天):=CONCATENATE(DATEDIF(TEXT(MID(B2,7,LEN(B2)/2-1),"#-00-00"),TODAY(),"Y"),"年",DATEDIF(TEXT(MID(B2,7,LEN(B2)/2-1),"#-00-00"),TODAY(),"YM"),"月",DATEDIF(TEXT(MID(B2,7,LEN(B2)/2-1),"#-00-00"),TODAY(),"MD"),"天")
??計(jì)算年資:=10*MIN(DATEDIF(B2,TODAY(),"y"),15)+MAX(DATEDIF(B2,TODAY(),"y")-15,0)*5
??計(jì)算臨時(shí)工的工資:=ROUND(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(B2,"分",""),"小時(shí)",":"))/(8/24)*50,)
??計(jì)算本日工時(shí)工資:=(HOUR(C2-TIMEVALUE("8:00"))-1-ROUNDUP(B2-TIMEVALUE("8:00"),0))*6
??計(jì)算8：00一16：00的平均電壓:{=AVERAGE(IF((DAY(A2:A11)=8)*(HOUR(A2:A11)>=8)*(HOUR(A2:A11)>=16),B2:B11))}
??計(jì)算工作時(shí)間，精確到分鐘:=HOUR(C2)+MINUTE(C2)/60-HOUR(B2)-MINUTE(B2)/60-D2+24*(C2
??根據(jù)完工時(shí)間計(jì)算獎(jiǎng)金:=IF(HOUR(B2)>=18,-(ROUNDUP((HOUR(B2-"18:00")*60+MINUTE(B2))/30,0))*3,(ROUNDDOWN((HOUR("18:00"-B2)*60+60-MINUTE(B2))/30,0))*3)
??計(jì)算工程時(shí)間:=SUMPRODUCT(MINUTE(B2:B11)+(SECOND(B2:B11)>0))
??計(jì)算今天是星期幾:=WEEKDAY(NOW(),2)
??匯總星期日的支出金額:{=SUM((WEEKDAY(A2:A11,2)=7)*(B2:B11="支出")*C2:C11)}
??匯總第一個(gè)星期的出庫(kù)數(shù)量:{=SUM(OFFSET(A2,,MIN(IF(WEEKDAY(B1:P1,2)=1,COLUMN(B:P))),,7))}
??計(jì)算每日工時(shí)工資:=8*5*IF(WEEKDAY(A2,2)<6,1,1.5)+(B2-8)*5*1.5
??計(jì)算指定日期所在月份有幾個(gè)星期日:{=SUM(N(WEEKDAY(DATE(YEAR(A2),MONTH(A2),ROW(INDIRECT("1:"&DAY(EOMONTH(A2,0))))))=1))}
??按周匯總產(chǎn)量:{=SUM(((WEEKDAY($B1,2)-WEEKDAY($B1:$AF1,2))+(COLUMN($B1:$AF1)-1)=(1+(COLUMN(A1)-1)*7))*$B2:$AF2)}
??按周匯總進(jìn)倉(cāng)與出倉(cāng)數(shù)量:{=SUM(((WEEKDAY($B1,2)-WEEKDAY($B1:$BK1,2))+INT((COLUMN($B1:$BK1))/2)=(1+(INT((COLUMN(A1)+1)/2)-1)*7))*$B3:$BK3*($B2:$BK2=B7))}
??羅列本月休息日:{=IFERROR(SMALL(IF(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),ROW(INDIRECT("1:"&DAY(EOMONTH(NOW(),0))))),2)=2,DATE(YEAR(NOW()),MONTH(NOW()),ROW(INDIRECT("1:"&DAY(EOMONTH(NOW(),0)))))),ROW()),"")}
??計(jì)算周末獎(jiǎng)金補(bǔ)貼:=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(B2&":"&C2))-1,2)>5))*10
??羅列值班日期:{=MIN(IF(WEEKDAY(DATE(2008,ROW(),ROW($1:$31)),2)=7,DATE(2008,ROW(),ROW($1:$31))))}
??計(jì)算本月加班時(shí)間:{=SUM((MOD(MOD(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),ROW(INDIRECT("1:"&DAY(EOMONTH(NOW(),0))))),2),7),2)={1,0})*{3,2})}
??今天是本年度第幾周:=WEEKNUM(TODAY())
??本月包括多少周:=WEEKNUM(EOMONTH(NOW(),0),2)-WEEKNUM((EOMONTH(NOW(),-1)+1),2)+1
??羅列第30周日期:{=TEXT(SMALL(IF(WEEKNUM(DATE(YEAR(NOW()),1,ROW($1:$366)),2)=30,DATE(YEAR(NOW()),1,ROW($1:$366))),ROW(A1)),"YYYY-MM-DD")}
??統(tǒng)計(jì)某月第四周的支出金額:{=SUM((WEEKNUM(A2:A11*1,1)-WEEKNUM(YEAR(A2:A11)&"-"&MONTH(A2:A11)&"-1")+1=4)*B2:B11)}
??判斷本月休息日:{=(SUM(N(WEEKNUM(ROW((INDIRECT((EOMONTH(NOW(),-1)+1)&":"&EOMONTH(NOW(),0)))),2)-WEEKNUM(EOMONTH(NOW(),-1)+1,2)+1=5))>3)+4}
??計(jì)算離職日期:=WORKDAY(A2,5,{"2008-10-1","2008-10-2","2008-1-3"})
??計(jì)算工程完工日期:{=WORKDAY(A2,B2,EOMONTH(A2,ROW(INDIRECT("1:"&INT(B2/30*2)))))}
??計(jì)算2008年第一季度有多少個(gè)工作日:=NETWORKDAYS(EOMONTH(NOW(),-MONTH(NOW()))+1,EOMONTH(NOW(),3-MONTH(NOW())),{"2008-2-7","2008-2-8","2008-2-9"})
??計(jì)算2008年第一季度有多少個(gè)非工作日:=EOMONTH(NOW(),3-MONTH(NOW()))-EOMONTH(NOW(),-MONTH(NOW()))-NETWORKDAYS(EOMONTH(NOW(),-MONTH(NOW()))+1,EOMONTH(NOW(),3-MONTH(NOW())),{"2008-2-7","2008-2-8","2008-2-9"})
??計(jì)算今天離國(guó)慶節(jié)還有多少個(gè)工作日:=NETWORKDAYS(TODAY(),DATE(YEAR(TODAY())+(TODAY()>DATE(YEAR(TODAY()),10,1)),10,1))
??填充12個(gè)月的月份名:=CONCATENATE("第",TEXT(ROW(A1),"[DBNum1]"),"月")
??產(chǎn)生“坐標(biāo)”:=CHAR(64+COLUMN(A1))
??檢查日倉(cāng)庫(kù)報(bào)表日期是否正確:{=IF(SUM(N((11-RANK(A2:A11,A2:A11))=(ROW(2:11)-1)=FALSE)),"非遞增","遞增")}
??檢查字符串中哪一個(gè)字符出現(xiàn)次數(shù)最多:{=CHAR(MODE(IFERROR(CODE(MID(A2,ROW(1:16),1)),"")))}
??產(chǎn)生每?jī)尚欣奂?的編號(hào):=IF(ROW()=1,1,IF(MOD(ROW(),3),COUNT(OFFSET(A$1,,,ROW()-1))+1,""))
??最后一次不及格是哪次測(cè)試:{=INDEX(A:A,MAX((B2:B11<60)*ROW(2:11)))}
??計(jì)算第11名到第30名學(xué)員的平均成績(jī):{=AVERAGE(IF(RANK(B2:B101,B2:B101)=TRANSPOSE(ROW(11:30)),B2:B101))}
??計(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)))+1
??計(jì)算第一次收入金額大于30元時(shí)的金額是多少:=INDEX(B:B,MIN(IF((A2:A11=A2)*(B2:B11>30),ROW(2:11))))
??計(jì)算扣除所有扣款后的最高薪資:{=MAX(B2:B10-MMULT(C2:G10*1,ROW(1:5)^0))}
??對(duì)班級(jí)和成績(jī)升序排列:{=1*MID(SMALL(1*($A2:$A12&TEXT($B2:$B12,"000")),ROW($A$2:$A$12)-1),{1,2},{1,3})}
??羅列今日銷售的諾基亞手機(jī)型號(hào):{=T(INDEX(B:B,SMALL(IF(ISERROR(FIND("諾基亞",B$2:B$11)),10^6,ROW($2:$11)),ROW(1:1))))}
??統(tǒng)計(jì)圖書數(shù)量:{=IF(B2="","",MIN(IF(B3:B$13<>"",ROW(3:$13),13))-ROW())}
??羅列第一名學(xué)生姓名:{=T(INDEX(A:A,SMALL(IF($B$2:$B$11=MAX(B$2:B$11),ROW($2:$11),12),ROW(A1))))}
??羅列1到1000之間的質(zhì)數(shù):{=INDEX(A:A,SMALL(IF(A$2:A$1000<>"",ROW($2:$1000),1001),ROW(A1)))&""}
??判斷某數(shù)是否為質(zhì)數(shù):{=IF(A2<2,"非質(zhì)非合",IF(SMALL(IF(MOD(A2,ROW(INDIRECT("1:"&A2)))=0,ROW(INDIRECT("1:"&A2))),2)=A2,"質(zhì)數(shù)","合數(shù)"))}
??計(jì)算某個(gè)數(shù)的約數(shù)個(gè)數(shù)及羅列所有約數(shù):約數(shù)個(gè)數(shù){=COUNT(0/(MOD(A2,ROW(INDIRECT("1:"&A2)))=0))};羅列約數(shù){=IFERROR(SMALL(IF(MOD(A$2,ROW(INDIRECT("1:"&A$2)))=0,ROW(INDIRECT("1:"&A$2))),ROW(A1)),"")}
??將六個(gè)號(hào)碼組合成一個(gè):{=SUM(B1:B6*10^(2*(ROWS(B1:B6)-ROW(1:6))))}
??將每個(gè)人的貸款重新分組:{=INDEX($C:$C,SMALL(IF($A$2:$A$11=$E2,ROW($2:$11),ROWS($1:$12)),COLUMN(A1)))}
??檢測(cè)每個(gè)志愿是否與之前的重復(fù):=MATCH(B2,$B$2:$B$10,)<>ROWS($2:2)
??將列標(biāo)轉(zhuǎn)換成數(shù)字:=COLUMN(INDIRECT(A2&1))
??重組人事資料表:=REPLACE(INDIRECT("B"&1+(ROW(A1)-1)*4+COLUMN(A:A)),1,LEN(D$1)+1,"")
??班級(jí)成績(jī)查詢:{=INDEX($B:$E,SMALL(IF($A$2:$A$12=$H$2,ROW($2:$12),ROWS($1:$12)+1),ROW(A1)),COLUMN(A1))&""}
??羅列每日缺席名單:{=INDEX(全體成員!$1:$1,SMALL(IF(COUNTIF($B2:$K2,全體成員!$A$1:$M$1)=0,COLUMN($A:$M),16384),COLUMN(A1)))&""}
??計(jì)算所有人的一周產(chǎn)量并排名:{=INDEX(1:1,RIGHT(LARGE(SUBTOTAL(9,OFFSET($A2:$A8,,COLUMN($B:$J)-1,,))*10+COLUMN($B:$J)-1,COLUMN(A1)))+1)}
??將金額分散填充，空位以“-”占位:=MID(TEXT(INT($A2*100),REPT("-",9-LEN(INT($A2)))&REPT(0,LEN(INT($A2))+1)),COLUMNS($A:A),1)
??提取引用區(qū)域右下角的數(shù)據(jù):=INDIRECT(ADDRESS(ROW(B3:D7)+ROWS(B3:D7)-1,COLUMN(B3:D7)+COLUMNS(B3:D7)-1))
??整理成績(jī)單:=INDIRECT(CHAR(ROWS($1:22)*3)&COLUMN())
??合并三個(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))
??多區(qū)域計(jì)數(shù):=SUM(COUNTIF(INDIRECT({"C2:C11","F2:F11","I2:I11"}),"<60"))
??求積、求和兩相宜:=SUM(IF(C2="",INDIRECT("E"&LOOKUP(1,0/ISERROR((0/$C$1:C1="")),ROW($C$2:C2))&":E"&(ROW()-1)),C2*D2))
??計(jì)算五個(gè)工作表最大平均值:{=MAX(SUBTOTAL(1,INDIRECT({"一","二","三","四","五"}&"班!B2:b11")))}
??按卡號(hào)中的英文及數(shù)值排序:{=INDIRECT("A"&MOD(SMALL(CODE(B$2:B$11)*10000+MID(B$2:B$11,2,9)*100+ROW($2:$11),ROW(B1)),100))}
??多行多列取唯一值:{=IF(OR((B$2:D$5<>"")*(COUNTIF(F$1:F1,B$2:D$5)=0)),INDIRECT(TEXT(MIN(IF((B$2:D$5<>"")*(COUNTIF(F$1:F1,B$2:D$5)=0),ROW(B$2:D$5)*1000+COLUMN(B:D))),"r0c???"),),"")}
??羅列三個(gè)表中的最大值:{=SUBTOTAL(4,INDIRECT({"A組";"B組";"C組"}&"!B2:B11"))}
??將三列課程轉(zhuǎn)換成單列且忽略空值:{=INDIRECT(TEXT(SMALL(IF($B$2:$D$7<>"",ROW($2:$7)*1000+1,1048576001),ROW(A1)),"r#c000"),)&""}
??羅列兩個(gè)正整數(shù)的所有公約數(shù):{=IFERROR(SMALL(IF((MOD(A$2,ROW(INDIRECT("1:"&GCD(A$2:B$2))))=0)*(MOD(B$2,ROW(INDIRECT("1:"&GCD(A$2:B$2))))=0),ROW(INDIRECT("1:"&GCD(A$2:B$2)))),ROW()-1),"")}
??B列最大值的地址:{=ADDRESS(MAX(IF(B2:B11=MAX(B2:B11),ROW(2:11))),2)}
??記錄最后一次銷量大于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)*1000)/1000))}
??根據(jù)下拉列表引用不同工作表的產(chǎn)量:=INDIRECT(ADDRESS(11,2,1,1,D1))
??根據(jù)下拉列表羅列班級(jí)成績(jī)第一名姓名:{=IFERROR(INDIRECT(ADDRESS(LARGE(((INDIRECT(D$1&"!B2:B10")=MAX(INDIRECT(D$1&"!B2:B10")))*ROW($2:$10)),ROW(A1)),1,1,1,D$1)),"")}
??查詢成績(jī):=OFFSET(A1,MATCH(F1,A2:A11,0),MATCH(G1,B1:D1,0))
??在具有合并單元格的A列產(chǎn)生自然數(shù)編號(hào):=1+COUNT(OFFSET($A$2,,,ROW()-2,))
??引用合并區(qū)域時(shí)防止產(chǎn)生0值:=IF(A1<>"",A1,OFFSET(B1,-1,))
??計(jì)算10屆運(yùn)動(dòng)會(huì)中有幾次破紀(jì)錄:=SUMPRODUCT(N(SUBTOTAL(5,OFFSET(B2,,,ROW(2:10)))
??計(jì)第奎續(xù)三天之總產(chǎn)量大于等于25萬(wàn)元的次數(shù):=SUMPRODUCT(N(SUBTOTAL(9,OFFSET($B$1,ROW(1:10)-1,,3))>=25))
??進(jìn)、出庫(kù)合計(jì)查詢:=SUM(OFFSET(A1,E2,MATCH(G2&"總計(jì)",B1:C1,0),F2-E2+1))
??根據(jù)人數(shù)自動(dòng)調(diào)整表格大小:{=IFERROR(OFFSET($E$1,SMALL(IF(F$2:F$5>=TRANSPOSE(ROW(INDIRECT("1:"&MAX(F$2:F$5)))),ROW($2:$5)-1),ROW(1:1)),),"")}
??累計(jì)數(shù)據(jù):{=SUM(OFFSET(B$2,,,ROW()-1))}
??計(jì)算至少兩科不及格的學(xué)生人數(shù):{=SUM(--(COUNTIF(OFFSET($B$1,ROW(2:11)-1,,,4),"<60")>=2))}
??列出成績(jī)最好的科目:{=OFFSET(A2,,SUM((MAX(SUBTOTAL(9,OFFSET(A2,1,ROW(1:4),4)))=SUBTOTAL(9,OFFSET(A2,1,COLUMN(A:D),4)))*COLUMN(B:E))-1)}
??計(jì)算及格率不超過(guò)50%的科目數(shù):{=SUM(N(COUNTIF(OFFSET(A1,1,COLUMN(A:D),10,1),"<60")>=ROWS(2:11)/2))}
??羅列兩次未打卡人員:{=IFERROR(OFFSET(A$1,LARGE((COUNTIF(OFFSET(A$1,ROW($2:$11)-1,1,,4),"×")>=2)*ROW($2:$11),ROW(A1))-1,),"")}
??計(jì)算語(yǔ)文、英語(yǔ)、化學(xué)、政治哪科總分最高:=CHOOSE(MATCH(MAX(SUBTOTAL(9,OFFSET(A1,1,MATCH({"語(yǔ)文","英語(yǔ)","化學(xué)","政治"},$B$1:$G$1,0),10,))),SUBTOTAL(9,OFFSET(A1,1,MATCH({"語(yǔ)文","英語(yǔ)","化學(xué)","政治"},$B$1:$G$1,0),10,)),0),"語(yǔ)文","英語(yǔ)","化學(xué)","政治")
??連續(xù)三屆達(dá)到100的次數(shù):=SUMPRODUCT(N(COUNTIF(OFFSET(B1,ROW(2:9)-1,,3,1),">=100")=3))
??羅列及格率最高的學(xué)生姓名:{=INDEX(A:A,SMALL(IF(MAX(COUNTIF(OFFSET(A$1,ROW($2:$11)-1,1,1,COLUMNS(B:G)),">=60"))=COUNTIF(OFFSET(A1,ROW($2:$11)-1,1,1,COLUMNS(B:G)),">=60"),ROW($2:$11),12),ROW(A1)))&""}
??計(jì)算Excel類圖書最多進(jìn)貨量及書名:{=MAX(SUMIF(OFFSET(B1,ROW(2:11)-1,1,1,6),">=100")*(B2:B11="excel"))}
??計(jì)算Excel類圖書進(jìn)貨最多的是哪一個(gè)月:{=INDEX(C1:H1,MATCH(MAX(SUMIF(B2:B11,"excel",OFFSET(C2,,COLUMN(C:H)-3,ROWS(2:11),1))),SUMIF(B2:B11,"excel",OFFSET(C2,,COLUMN(C:H)-3,ROWS(2:11),1)),0))}
??根據(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))}
??根據(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),"")}
??計(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),"[DBNum1]0季度")
??分欄打印:=IF(ROW()=1,CHOOSE(MOD(COLUMN()-1,3)+1,資料!$A$1,資料!$B$1,""),IF(MOD(COLUMN(),3)=0,"",OFFSET(資料!$A$1,INT(COLUMN()/3)*9+ROW()-1,MOD(COLUMN(),3)-1,)))
??分類匯總:=IF(SUMIF(B$2:B$11,E2,C$2:C$11)=0,"",SUMIF(B$2:B$11,E2,C$2:C$11))
??分類匯總并排序:{=OFFSET(B$1,RIGHT(LARGE(IF(MATCH(B$2:B$11,B$2:B$11,)=ROW($2:$11)-1,SUMIF(B$2:B$11,B$2:B$11,C$2:C$11)*1000+ROW($2:$11),ROWS($1:$11)+1),ROW(1:1)),3)-1,)&""}
??工資查詢:{=IFERROR(OFFSET(D1,MATCH(F2&G2&H2,A2:A11&B2:B11&C2:C11,0),),G2&"無(wú)此人")}
??多表成績(jī)查詢:{=SUBTOTAL(9,OFFSET(INDIRECT(ADDRESS(1,MATCH(H1,1:1,0),1,1,{"一班";"二班";"三班"})),1,,ROWS(2:11),))}
??計(jì)算每個(gè)學(xué)生總分是否高于本班平均成績(jī):{=SUM(C2:E2)>AVERAGE(IF((A2=A$2:A$11),SUBTOTAL(9,OFFSET(B$1,ROW($2:$11)-1,1,,COLUMNS(C:E)))))}
??計(jì)算每個(gè)學(xué)生進(jìn)入前三名的科目總數(shù):{=SUM(N((RANK(N(OFFSET($B$2,ROW()-2,COLUMN(B:F)-2,1,1)),OFFSET($B$2,0,COLUMN(B:F)-2,ROWS($2:$11),1)))<=3))}
??計(jì)算高于單科平均值的科目總數(shù):{=SUM(N(N(OFFSET($B$2,ROW()-2,COLUMN(B:F)-2,1,1))>SUBTOTAL(1,OFFSET($B$2,0,COLUMN(B:F)-2,ROWS($2:$11),1))))}
??羅列平均成績(jī)倒數(shù)三名的班級(jí):{=OFFSET(A1,MATCH(SMALL(SUBTOTAL(1,OFFSET(A1,ROW($2:$9)-1,1,1,COLUMNS(B:F)))*1000+ROW(2:9),ROW(1:3)),SUBTOTAL(1,OFFSET(A1,ROW($2:$9)-1,1,1,COLUMNS(B:F)))*1000+ROW(2:9),),)}
??將姓名重復(fù)三次:{=T(OFFSET(A$1,ROUNDUP(ROW(INDIRECT("1:"&ROWS(A$2:A$5)*3))/3,0),))}
??多表匯總金額:{=SUM(SUBTOTAL(6,OFFSET(INDIRECT({"華南區(qū)","華東區(qū)","華北區(qū)"}&"!B1:C1"),ROW(2:10)-1,)))}
??從單價(jià)表引用單價(jià)并匯總金額:{=SUM((N(OFFSET(G1,MATCH(A2:A7,F2:F13,),)))*B2:B7)}
??從單價(jià)表引用最新單價(jià)并匯總金額:{=SUM((N(OFFSET(F1,MATCH(A2:A7,D2:D13,)+(COUNTIF(D2:D13,A2:A7)-1),)))*B2:B7)}
??根據(jù)完工狀況匯總工程款:{=SUM(SUBTOTAL(9,OFFSET(C1,ROW(2:11)-1,,1,2))*(E2:E11=G2))}
??統(tǒng)計(jì)最后三天的平均銷量:{=SUBTOTAL(1,OFFSET(INDIRECT("B"&MAX((A:A<>"")*ROW(1:1048576))),,,-3,1))}
??重組培訓(xùn)科目表:姓名=LOOKUP(ROW()-1,COUNTIF(OFFSET(B$1:G$1,,,ROW($1:$7)),"<>"),A$2:A$8)&"";科目=IFERROR(OFFSET(B$2,MATCH(H2,$A$2:$A$7,)-1,COUNTIF($H$2:H2,H2)-1),"")
??從多個(gè)產(chǎn)品相同單價(jià)的單價(jià)表中引用單價(jià):=SUMPRODUCT(COUNTIF(OFFSET(A$2,ROW($2:$4)-2,0,1,4),G2)*E$2:E$4)*H2
??統(tǒng)計(jì)所有業(yè)務(wù)員銷售利潤(rùn)并羅列排列榜:{=OFFSET(A1,MOD(LARGE(INT(SUBTOTAL(6,OFFSET(C2,ROW(C2:C11)-2,,,3)))*1000+ROW(2:11),ROW(2:11)-1),1000)-1,)}
??按季度引用不同價(jià)格并統(tǒng)計(jì)金額與累計(jì):{=IF(A2<>"累計(jì)",LOOKUP(COUNTIF(OFFSET(A$1,1,0,ROWS($2:2),),"合計(jì)")+1,ROW($2:$5)-1,F$2:F$5)*B2,SUM(C1:C$2*(A1:A$2<>"累計(jì)")))}
??計(jì)算10個(gè)月中的銷售利潤(rùn)并排名:{=OFFSET(A1,MOD(LARGE(INT(MMULT(SUBTOTAL(6,OFFSET(INDIRECT({"華東區(qū)","華南區(qū)","華北區(qū)","華中區(qū)","西南區(qū)"}&"!A1"),ROW(2:11)-1,1,1,3)),{1;1;1;1;1}))*1000+ROW(2:11),ROW(1:10)),1000)-1,)}
??計(jì)算五個(gè)地區(qū)銷售利潤(rùn):{=TRANSPOSE(MMULT({1,1,1,1,1,1,1,1,1,1},SUBTOTAL(6,OFFSET(INDIRECT({"華東區(qū)","華南區(qū)","華北區(qū)","華中區(qū)","西南區(qū)"}&"!A1"),ROW(西南區(qū)!$2:$11)-1,1,1,3)))*1000+ROW(2:11))}
??計(jì)算第幾輪銷量最高以及售貨員姓名:{=OFFSET(A1,RIGHT(MAX(SUBTOTAL(9,OFFSET(D1,5*(ROW(INDIRECT("1:"&CEILING(COUNTA(C:C)/5,1)))-1),,5))*10+ROW(INDIRECT("1:"&CEILING(COUNTA(C:C)/5,1)))))*5-1,)}
??提取組名及計(jì)算每組平均達(dá)標(biāo)率:{=TEXT(SUBTOTAL(1,OFFSET(B1,((ROW(1:4))*2-1),,,8)),"0.00%")}
??判斷是否超過(guò)一半人達(dá)標(biāo)率在90%以上:{=COUNTIF(OFFSET(B1,((ROW(1:4))*2-1),,,8),">=0.9")>COLUMNS(B:I)/2}
??分別計(jì)算每個(gè)班第一名的成績(jī)和姓名:名次{=MAX(SUBTOTAL(9,OFFSET(B$1,ROW($2:$31)-1,1,,COLUMNS(C:I)))*(B$2:B$31=K2))};名{=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,)}
??計(jì)算哪一個(gè)月完成目標(biāo):=OFFSET(A1,LOOKUP(,1*(SUBTOTAL(9,OFFSET(B1,1,0,ROW(2:12)-1))>=200),ROW(2:12)),)
??有幾次連續(xù)三個(gè)月的平均值低于整體平均值:{=SUM(N((SUBTOTAL(9,OFFSET(B4,ROW(2:11)-2,,3,2
??計(jì)算10個(gè)月中的銷售利潤(rùn)并排名:{=OFFSET(A1,MOD(LARGE(INT(MMULT(SUBTOTAL(6,OFFSET(INDIRECT({"華東區(qū)","華南區(qū)","華北區(qū)","華中區(qū)","西南區(qū)"}&"!A1"),ROW(2:11)-1,1,1,3)),{1;1;1;1;1}))*1000+ROW(2:11),ROW(1:10)),1000)-1,)}
??將表格轉(zhuǎn)置方向:{=TRANSPOSE(A1:E5)}
??對(duì)組數(shù)進(jìn)行排名:{=MMULT(N(B2:B11*(IF(LEFT(C2:C11)="萬(wàn)",10000,1))
??區(qū)分大小寫提取產(chǎn)品單價(jià):{=MMULT((EXACT(B2:B11,TRANSPOSE(單價(jià)表!A2:A5)))*TRANSPOSE(單價(jià)表!B2:B5),{1;1;1;1})}
??區(qū)分大小寫查單價(jià)且統(tǒng)計(jì)三組總金額:{=MMULT(TRANSPOSE(SUBTOTAL(9,OFFSET(B1,ROW(2:11)-1,1,,5))*MMULT((EXACT(B2:B11,TRANSPOSE(單價(jià)表!A2:A5)))*TRANSPOSE(單價(jià)表!B2:B5),{1;1;1;1})),1*(A2:A11={"A組","B組","C組"}))}
??引用銷售金額高于200次數(shù)最多者:{=INDEX(A:A,RIGHT(MAX(MMULT((B2:H9>200)*1,TRANSPOSE(COLUMN(B:H)^0))*10+ROW(2:9))))}
??根據(jù)評(píng)委評(píng)分和權(quán)重分配統(tǒng)計(jì)最后得分:{=SUM(B2:F8*(A2:A8=B10)*TRANSPOSE(I2:I6))}
??羅列選手得分前三名的姓名:{=OFFSET($A1,RIGHT(LARGE(MMULT($B2:$F8*TRANSPOSE($I2:$I6),TRANSPOSE(COLUMN($B:$F)^0))*10^6+ROW(2:8),COLUMN(A1)),2)-1,,)}
??根據(jù)字母評(píng)語(yǔ)轉(zhuǎn)換得分:{=MMULT(TRANSPOSE(評(píng)語(yǔ)換算得分!A$2:A$11=TRANSPOSE(E2:E11))*1,評(píng)語(yǔ)換算得分!B$2:B$11)+SUBTOTAL(9,OFFSET(B2,ROW(2:11)-2,,,COLUMNS(B:D)))}
??多列、隔行數(shù)據(jù)匯總:{=SUM(MMULT(D2:G11,TRANSPOSE(COLUMN(D:G)^0))*(A2:A11="趙還珠"))}
??計(jì)算犯規(guī)低于3次的人數(shù):{=SUM(N(MMULT(--(B2:B21=TRANSPOSE(B2:B21)),ROW(2:21)^0)={1,2})/{1,2})}
??提取姓名:=INDEX(B:B,ROW()*2)&""
??從電話簿中選擇性引用數(shù)據(jù):=INDEX($A:$B,ROW(A1)*3-2,COLUMN(A:A))
??消除廠牌打印資料照片行:{=INDEX(A:A,SMALL(IF(MOD(ROW($1:$12),3)>0,ROW($1:$12),1048576),ROW(A1)))&""}
??羅列優(yōu)秀員工:{=INDEX(A:A,MOD(SMALL(B$2:B$11*100+ROW($2:$11),ROW(8:8)),100))}
??插入空行分割數(shù)據(jù):=IF(MOD(ROW(),3)>0,INDEX(A:A,ROW(A2)*2/3),"")
??僅僅提取通訊錄中四分之三信息:=INDEX(A:B,ROW(A2)*2/3,(MOD(ROW(A3),3)+1)/3+1)
??羅列12月中產(chǎn)量倒數(shù)第一名次數(shù)最多者名單:{=INDEX(B:B,SMALL(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)))&""}
??按投訴次數(shù)升序排列客服姓名:{=INDEX(B:B,MOD(SMALL(IF(MATCH(B$2:B$12,B$2:B$12,)=ROW($2:$12)-1,COUNTIF(B$2:B$12,B$2:B$12)*10^5+IF(MATCH(B$2:B$12,B$2:B$12,)=ROW($2:$12)-1,ROW($2:$12),9999999),9999999),ROW(A1)),10^5))&""}
??計(jì)算60分到95分之間的人員個(gè)數(shù):=INDEX(FREQUENCY(B2:B11,{60,95}),2)
??羅列導(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)))),"")}
??按身高對(duì)學(xué)生排列座次表:{=INDEX($A:$A,MOD(SMALL($C$2:$C$49*1000+ROW($2:$49),(ROW(A1)-1)*6+MOD(COLUMN(A1)-1,6)+1),1000))}
??重組教師授課表:{=INDEX(班級(jí)!$A:$A,SMALL(IF(班級(jí)!$B$2:$D$11=$A3,ROW($2:$11),1048576),COLUMN(C$1)))&""}
??提取三個(gè)不規(guī)則區(qū)域的交集:{=INDEX($B:$B,SMALL(IF(COUNTIF(C組!$B$2:$I$2,$B$2:$B$9)*COUNTIF(B組!$C$2:$D$4,$B$2:$B$9),ROW($B$2:$B$9),10),ROW(A4)))&""}
??不區(qū)分大小寫查找單價(jià):=VLOOKUP(B2,單價(jià)表!A$2:C$11,3,0)*C2
??亂序資料表中查找多個(gè)項(xiàng)目:=VLOOKUP($B2,單價(jià)表!$A$2:$E$11,MATCH(C$1,單價(jià)表!$A$1:$E$1,0),0)
??將得分轉(zhuǎn)換成等級(jí):=VLOOKUP(B2,{0,"D";60,"C";80,"B";90,"A"},2)
??查找美元與人民幣報(bào)價(jià):=VLOOKUP(B2,INDIRECT(E2&"報(bào)價(jià)!A2:B9"),2,0)
??多條件查找:{=VLOOKUP(A2&B2&C2,IF({1,0},資料表!A2:A11&資料表!B2:B11&資料表!D2:D11,資料表!C2:C11),2,0)}
??查找最后更新單價(jià):{=VLOOKUP(10^16,--LEFT(VLOOKUP(B2,單價(jià)表!A:Z,COUNTA(INDIRECT("單價(jià)表!A"&MATCH(B2,單價(jià)表!A:A,0)&":Z"&MATCH(B2,單價(jià)表!A:A,0))),0),ROW($1:$16)),1)}
??查找雙列信息:{=VLOOKUP(A9,CHOOSE({3,2,1},A1:A6&B1:B6,C1:C6&D1:D6,E1:E6&F1:F6),{2,3},)}
??提取姓名拼音的首字母:=VLOOKUP(LEFT(A2),拼音,2)&VLOOKUP(MID(A2,2,1),拼音,2)&VLOOKUP(MID(A2,3,1),拼音,2)
??用不確定條件查找:{=VLOOKUP(A2&"",IF({1,0},IF(COUNTIF(資料表!A2:A10,A2)=0,資料表!B2:B10,資料表!A2:A10),資料表!E2:E10),2,0)}
??按學(xué)歷對(duì)姓名排序:{=VLOOKUP(MOD(SMALL(MATCH(B$2:B$10,{"大學(xué)";"高中";"初中";"小學(xué)"},0)*1000+ROW($2:$10),ROW(A1)),1000),IF({1,0},ROW($2:$10),A$2:A$10),2,0)}
??使用通配符進(jìn)行查找:{=VLOOKUP("*"&A2&"*",IF({1,0},資料表!B$2:B$9,資料表!A$2:A$9),2,0)}
??多工作表查找最大值:{=TEXT(VLOOKUP(MAX(SUBTOTAL(9,INDIRECT(TEXT(ROW(1:6),"[DBNum1]")&"年級(jí)!B"&MATCH(D2,A:A,0)))),IF({1,0},SUBTOTAL(9,INDIRECT(TEXT(ROW(1:6),"[DBNum1]")&"年級(jí)!B"&MATCH(D2,A:A,0))),ROW(1:6)),2,0),"[DBNum1]")}
??對(duì)帶有合并單元格的區(qū)域查找年假天數(shù):=VLOOKUP(F2,OFFSET(B2,MATCH(E2,A2:A13,0)-1,,4,2),2)
??查找某業(yè)務(wù)員在某季度的銷量:=HLOOKUP(G2,A1:E9,MATCH(H2,A:A,0),0)
??在同一行查找數(shù)據(jù):{=HLOOKUP(MAX(A2:H2),IF({1;0},B2:H2,A2:G2),2,FALSE)}
??計(jì)算兩個(gè)產(chǎn)品不同時(shí)期的單價(jià):=HLOOKUP(MONTH(A2),IF(B2="塑膠機(jī)",{0,3,8;25,19,18},{0,5,10;12.5,10,11}),2)
??多條件計(jì)算加班費(fèi):=TEXT(HOUR(B2)+HLOOKUP(MINUTE(B2),{0,20.0001,50.0001;0,0.5,1},2),"[>2]6;5")*HOUR(B2)+HLOOKUP(MINUTE(B2),{0,20.0001,50.0001;0,0.5,1},2)
??根據(jù)進(jìn)廠日期計(jì)算有薪假天數(shù):=HLOOKUP(DATEDIF(B2,TODAY(),"y"),{0,1,3,5,7,10;0,2,3,5,7,10},2)
??制作準(zhǔn)考證:=HLOOKUP(B2,學(xué)生檔案庫(kù)!$1:$11,ROUNDUP(COLUMN()/5,0)+1+INT(ROW()/7)*2,FALSE)
??不區(qū)分大小寫判斷兩列相同數(shù)據(jù)個(gè)數(shù):{=COUNT(MATCH(A2:A11,B2:B11,0))}
??按漢字評(píng)語(yǔ)進(jìn)行排序:{=INDEX(A:B,MOD(SMALL(MATCH($B$2:$B$12,排名標(biāo)準(zhǔn)!$A$2:$A$9,)*100+ROW($B$2:$B$12),ROW(2:12)-1),100),{1,2})}
??提取A列最后一個(gè)數(shù)據(jù):{=INDIRECT("A"&(MATCH(1,0/(A:A<>""))))}
??提取字符串中的漢字:{=MID(A2,MATCH(1,1/(MID(A2,ROW($1:$99),1)>="啊"),),SUM(MATCH({1,2},1/(MID(A2,ROW($1:$99),1)>="啊"),{0,1})*{-1,1})+1)}
??將文件號(hào)中的中文大寫轉(zhuǎn)小寫:{="第"&TEXT(SUM((MATCH(MID(A2,{2,3,4},1),TEXT(ROW($1:$10)-1,"[DBNum2]"),0)-1)*{100,10,1}),"000")&"號(hào)文件"}
??計(jì)算補(bǔ)課科目總數(shù):{=COUNT(0/(MATCH(B2:B8,B2:B8,0)=ROW(2:8)-1))}
??產(chǎn)生混合編號(hào):=TEXT(COUNTIF(C$1:C1,"*"),"[DBNum2]")&TEXT(ROW()-MATCH("々",C$1:C1),"(000);;")
??提取遲到次數(shù)最多者姓名:=INDEX(B2:B11,MODE(MATCH(B$2:B$11,B$2:B$11,0)))
??羅列多次遲到者姓名:{=IFERROR(INDEX(B$2:B$11,MODE(IF(COUNTIF(D$1:D1,B$2:B$11)=0,MATCH(B$2:B$11,B$2:B$11,0)))),"")}
??區(qū)分、不區(qū)分大小寫統(tǒng)計(jì)字符個(gè)數(shù):{=COUNT(0/(MATCH(MID(A2,ROW($1:$100),1),MID(A2,ROW($1:$100),1),0)=ROW($1:$100)))-1}
??按金、銀、銅牌排名次:{=MATCH(B2:B11+C2:C11%+D2:D11%%,LARGE(B2:B11+C2:C11%+D2:D11%%,ROW(2:11)-1),0)}
??按班級(jí)插入分隔行:{=INDEX(A:B,MOD(SMALL(IF({1,0},ROW(2:11)*1001,IF(ROW(2:11)-1=MATCH(A2:A11,A2:A11,0),((MATCH(A2:A11,A2:A11,)+COUNTIF(A2:A11,A2:A11))*1000+100),1048576)),ROW(1:100)),1000),{1,2})&""}
??統(tǒng)計(jì)一、二班舉重參賽人員數(shù):{=COUNT(MATCH(B2:B11&C2:C11,{"一班","二班"}&"舉重",))}
??累計(jì)銷量并列出排行榜:{=OFFSET($B$1,MATCH(1,N(MAX(IF(COUNTIF($D$1:D1,B$2:B$12)=0,SUMIF(B$2:B$12,B$2:B$12,C$2:C$12)))=IF(COUNTIF($D$1:D1,B$2:B$12)=0,SUMIF(B$2:B$12,B$2:B$12,C$2:C$12))),),)&""}
??利用公式對(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)))&""}
??羅列每個(gè)地區(qū)的獲獎(jiǎng)人員姓名:{=IFERROR(INDEX($A:$A,MATCH(1,(COUNTIF(E$1:E1,$A$2:$A$10)=0)*($B$2:$B$10=E$1),)+1),"")}
??對(duì)合并區(qū)域進(jìn)行數(shù)據(jù)查詢:=OFFSET(B1,MATCH(G2,A2:A13,0)-1+MATCH(H2,{"冰箱","空調(diào)","洗衣機(jī)"},0),MATCH(I2,C1:E1,0))
??將一維人事資料表轉(zhuǎn)二維:{=REPLACE(IFERROR(OFFSET($A$1,MATCH(C$1:F$1&"：*",IF(COUNTIF(OFFSET(A$1,,,ROW($1:$42)),"")=ROW()-2,A$1:A$42),0)-1,),""),1,LEN(C$1:F$1)+1,"")}
??區(qū)分大小寫查找單價(jià):{=INDEX(B:B,MATCH(0,0/EXACT(E1,A1:A8),0))}
??根據(jù)姓名查找左邊的身份證號(hào):=LOOKUP(E2,B2:B9,A2:A9)
??將中文大寫編號(hào)轉(zhuǎn)換成阿位伯?dāng)?shù)字小寫:=TEXT(LOOKUP(1,0/(B2=TEXT(ROW($1:$1000),"[DBNum2]000")),ROW($1:$1000)),"000")
??將姓名按拼音升序排列:{=LOOKUP(0,0/(ROW(A1)=MMULT(N($A$2:$A$11>=TRANSPOSE($A$2:$A$11)),ROW($2:$11)^0)),A$2:A$11)}
??將酒店按星級(jí)降序排列:{=LOOKUP(ROUND(1/MOD(LARGE(LEN(B$2:B$10)+1/ROW($2:$10),ROW(A1)),1),0),ROW($2:$10),A$2:A$10)}
??計(jì)算某班六年中誰(shuí)獲第一名次數(shù)最多:{=MAX(COUNTIF(B2:B7,B2:B7))}
??羅列每個(gè)名次的所有姓名:{=IFERROR(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(A2)))),"")}
??提取新書的印刷批次:=LOOKUP(9E+307,--RIGHT(LEFT(A2,FIND("[",A2)-1),ROW($1:$99)))
??羅列2008年每月第一個(gè)及最后一個(gè)星期日:{=MIN(IF(WEEKDAY(DATE(2008,ROW(A1),ROW(INDIRECT("1:"&DAY(DATE(2008,ROW(A1)+1,0))))),2)=7,DATE(2008,ROW(A1),ROW(INDIRECT("1:"&DAY(DATE(2008,ROW(A1)+1,0)))))))}
??填補(bǔ)空白區(qū):=LOOKUP(1,0/($A$2:A2<>""),A$2:A2)
??將字母轉(zhuǎn)換成評(píng)分:{=AVERAGE(LOOKUP(B2:I2,{"A","B","C","D","E"},{10,9.5,8,7,5}))}
??將字母轉(zhuǎn)換成評(píng)分并對(duì)選手排名:{=LOOKUP(MOD(LARGE(MMULT(LOOKUP($B$2:$I$7,{"A","B","C","D","E"},{10,9.5,8,7,5}),ROW($1:$8)^0)*10000+ROW($2:$7),ROW(A1)),10),ROW($2:$7),A$2:A$7)}
??標(biāo)識(shí)各選手應(yīng)得的獎(jiǎng)牌:{=LOOKUP(SUM(N(IF(FREQUENCY(B$2:B$11,B$2:B$11),B$2:B$11,0)>B2))+1,ROW($1:$4),{"冠軍","亞軍","季軍",""})}
??計(jì)算各廠商參賽人數(shù):{=IFERROR(LOOKUP(SMALL(IF(A$2:A$21<>"",ROW($2:$21)),ROW(A1)),ROW($2:$21),A$2:A$21)&":?"&MMULT(SMALL(IF(A$2:A$21<>"",ROW($1:$20),21),ROW(A1)+{0,1}),{-1;1}),"")}
??從品名信息中分別提取多段數(shù)值:=IFERROR(-LOOKUP(0,-MID($A2,FIND(B$1,$A2)+LEN(B$1),ROW($1:$100))),"--")
??反向查找數(shù)據(jù):=LEN(A2)-LOOKUP(100,SEARCH(B2,A2,ROW($1:$99)))-LEN(B2)+2
??一級(jí)、二級(jí)分組編號(hào):=TEXT(COUNTIF(B$1:B2,"第*"),"00")&TEXT(ROW()-LOOKUP(1,0/(LEFT(B$1:B2)="第"),ROW($1:2)),"[=0]?;000")
??計(jì)算購(gòu)貨金額:{=LOOKUP(9E+307,--MID($A2,MATCH(0,0*MID($A2,ROW($1:$1000),1),0),ROW($1:$15)))*(LOOKUP(9E+307,--LEFT(REPLACE(A2,1,FIND("*",A2),""),ROW($1:$1000))))}
??誰(shuí)是百米冠軍:=LOOKUP(0,0/(B2:B11=MIN(B2:B11)),A2:A11)
??從銷售記錄中提取銷量與單價(jià)并計(jì)算金額:{=LOOKUP(10^16,--RIGHT(REPLACE(A2,FIND("公斤",A2),100,""),ROW($1:$100)))*LOOKUP(10^16,--RIGHT(REPLACE(A2,FIND("元",A2),100,""),ROW($1:$100)))}
??根據(jù)比賽結(jié)果降序排列選手且標(biāo)識(shí)名次:{=LOOKUP(SUM(N(COUNTIF(B$2:B$21,E2)<--IF(FREQUENCY(COUNTIF($B$2:$B$21,B$2:B$21),COUNTIF($B$2:$B$21,B$2:B$21)),COUNTIF($B$2:$B$21,B$2:B$21))))+1,ROW($1:$4),{"冠軍","亞軍","季軍",""})}
??計(jì)算每個(gè)職工的得分:=LOOKUP(,-FIND(B2,{"A**","A*","A","B**","B*","B","C**","C*","C","D"}),11-ROW($1:$10))
??查詢業(yè)務(wù)員的負(fù)責(zé)地區(qū):{=T(INDEX(B:B,SMALL(IF(LOOKUP(ROW(A$2:A$11),IF(A$2:A$11<>"",ROW(A$2:A$11)),A$2:A$11)=$D$2,ROW(A$2:A$11),1048576),ROW(1:1))))}
??根據(jù)產(chǎn)量計(jì)算員工產(chǎn)量得分:{=LOOKUP(B2,{3,0.5}*(ROW($1:$11)-1))}
??根據(jù)員工得分轉(zhuǎn)換為相應(yīng)的等級(jí):=LOOKUP(B2,--REPLACE(等級(jí)與分值!B$2:B$6,FIND("-",等級(jí)與分值!B$2:B$6),10,""),等級(jí)與分值!A$2:A$6)
??提取產(chǎn)量冠軍的組別:=IF(COUNTA(B2:E2),LOOKUP(1,0/ISTEXT(B2:E2),B$1:E$1),"")
??區(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,0),,,10))
??使用通配符查找所有符合條件的數(shù)據(jù):{=IFERROR(LOOKUP(1,0/SEARCH("*醫(yī)院*",IF(COUNTIF($C$1:C3,A$2:A$12)=0,A$2:A$12,)),A$2:A$12),"")}
??分別提取身份證號(hào)碼中的年月日:=TEXT(TEXT(MID($A2,7,8),"0000-00-00"),"[DBNum1]"&CHOOSE(MATCH(B$1,{"年","月","日"},0),"YYYY年","M月","D日"))
??根據(jù)不良率判斷送貨品處理辦法:=CHOOSE((SUM(N(C2/B2>={0,0.005,0.01}))),"合格","允收","退貨")
??讓VLOOKUP函數(shù)在多區(qū)域查找:=VLOOKUP(A11,CHOOSE(MATCH(B11,{"一年級(jí)","二年級(jí)","三年級(jí)"},0),A1:B9,D1:E9,G2:H9),2,0)
??將區(qū)域互換位置:=VLOOKUP(E2&"",CHOOSE({2,1},A2:A9,C2:C9),2,0)
??跨表統(tǒng)計(jì)最大值:{=CHOOSE(MOD(MAX(SUBTOTAL(9,INDIRECT({"A組";"B組";"C組"}&"!B2:B10"))*100+{1;2;3}),100),"A組","B組","C組")}
??羅列所有參加田徑的人員:{=IFERROR(VLOOKUP(1,CHOOSE({1,2},--(COUNTIF(OFFSET(C$2,,,ROW($2:$11)-1),"田徑")=ROW(1:1)),A$2:A$11),2,),"")}
??計(jì)算今天是本月的上旬、中旬還是下旬:=CHOOSE(MIN(CEILING(DAY(TODAY())/10,1),3),?"上旬","中旬","下旬")
??建立文件目錄:=HYPERLINK("[E:\\產(chǎn)量表\\"&TEXT(ROW(1:1),"[DBNum1]")&"月產(chǎn)量表.xlsx]sheet1!A1",TEXT(ROW(1:1),"[DBNum1]")&"月產(chǎn)量表")
??鏈接“總表”中B列最大值單元格:{=HYPERLINK("#總表!B"&MAX((MAX(總表!B:B)=總表!B:B)*ROW(B:B)),"至總表B列最大值")}
??鏈接至B列最末的非空單元格:{=HYPERLINK("#B"&MAX(((B:B)<>"")*ROW(B:B)),"B列最后非空值")}
??選擇冠軍姓名:{=HYPERLINK("#"&TEXT(SUM(SMALL(IF(B2:B13=MAX(B2:B13),ROW(2:13)),ROW(INDIRECT("1:"&COUNTIF(B2:B13,MAX(B2:B13)))))*10^((ROW(INDIRECT("1:"&COUNTIF(B2:B13,MAX(B2:B13))))-1)*2)),REPT("A00!,",COUNTIF(B2:B13,MAX(B2:B13))-1)&"A00"),"得票冠軍")}
??選擇二年級(jí)曠課人員名單:{=HYPERLINK("#A"&MIN(IF(A2:A12="二年級(jí)",ROW(2:12)))&":B"&MAX(IF(A2:A12="二年級(jí)",ROW(2:12))),"二年級(jí)名單")}
??選擇產(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)量組")}
??選擇打印區(qū)域:=HYPERLINK("#Print_Area",IF(ISERR(INDEX(Print_Area,1,1)),"未設(shè)置打印區(qū)","跳至打印區(qū)域"))
??計(jì)算期末平均成績(jī):{=AVERAGE(IF(ISEVEN(COLUMN(B:I)-1),B3:I3))}
??提取期末成績(jī)明細(xì):{=INDEX(成績(jī)表!1:1,SMALL(IF(ISEVEN(COLUMN($B:$I)-(ROW()<>1)),COLUMN($B:$I)),COLUMN(A1)))}
??提取每日累計(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})}
??根據(jù)身份證號(hào)碼匯總男、女職工總數(shù):男{=SUM(--ISODD(MID(B2:B10,15,3)))};女{=SUM(--ISEVEN(MID(B2:B10,15,3)))}
??提取當(dāng)前表打印區(qū)域地址:=IF(ISNA(VLOOKUP("蘋果",A2:B5,2,0)),10,LOOKUP(10^16,--LEFT(VLOOKUP("蘋果",A2:B5,2,0),ROW(1:100))))
??計(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)}
??提取A、B列相同項(xiàng)與不同項(xiàng):{=T(INDEX(A:A,SMALL(IF(NOT(ISERROR(MATCH(A$2:A$11,B$2:B$11,0))),ROW($2:$11),1048576),ROW(A1))))}
??計(jì)算產(chǎn)品體積:=IF(ISERROR(FIND("/",B2)),B2^3,PRODUCT(1*TRIM(MID(SUBSTITUTE(B2,"/",REPT("?",100)),{1,100,200},100))))
??引用單價(jià)并去除干擾符:=IF(ISNA(MATCH(B2,單價(jià)表!B$1:E$1,0)),"請(qǐng)更新單價(jià)",C2*LOOKUP(10^16,--LEFT(HLOOKUP(B2,單價(jià)表!B$1:E$2,2,0),ROW($1:$100))))
??查詢書籍在七年中的最高單價(jià):{=IF(ISNA(MATCH(A10,A2:A8,0)),"書名錯(cuò)誤",MAX(VLOOKUP(A10,A1:H8,COLUMN(B:H),0)))}
??根據(jù)計(jì)價(jià)單位查詢單價(jià):=IF(ISNA(MATCH(B2,F$1:H$1,0)),"未設(shè)定匯率",C2*HLOOKUP(B2,F$1:H$2,2,0))
??數(shù)字、字母與漢字個(gè)數(shù)計(jì)算:數(shù)字個(gè)數(shù){=SUM(--(ERROR.TYPE(INDIRECT("XFD"&MID(A2,ROW(INDI
??判斷錯(cuò)誤類型:=LOOKUP(ERROR.TYPE(A2),ROW(1:7),{"空值錯(cuò)誤";"被零除錯(cuò)誤";"值錯(cuò)誤";"無(wú)效的單元格引用";"無(wú)效的名稱";"數(shù)字錯(cuò)誤";"值不可用"})
??羅列某運(yùn)動(dòng)員九次參賽成績(jī):{=INDEX($1:$1,MAX(ISTEXT(B2:E2)*COLUMN(B:E)))}
??提取每年級(jí)第一名名單:=LOOKUP(1,0/ISTEXT(B2:E2),B$1:E$1)&":"&LOOKUP(1,0/ISTEXT(B2:E2),B2:E2)
??將按日期排列的銷售表轉(zhuǎn)換成按品名排列:{=IFERROR(VLOOKUP($A2,IF(MATCH(ROW($1:$15),IF(ISTEXT(日期!$A$1:$A$15),MATCH(日期!$A$1:$A$15,日期!$A$1:$A$15,0)))=MATCH(B$1,日期!$A$1:$A$15,0),日期!$B$1:$C$15),2,0),"")}
??按月份統(tǒng)計(jì)每個(gè)產(chǎn)品的機(jī)器返修數(shù)量:=SUMPRODUCT(ISNUMBER(FIND(F$2,$A$2:$A$11))*(TEXT($B$2:$B$11,"YM")=TEXT($E3,"YM"))*$C$2:$C$11)
??按文字描述求和:{=SUM(ISNUMBER(FIND(A$2:A$8,D2))*B$2:B$8)}
??按編碼計(jì)算庫(kù)存總數(shù):{=SUM(ISNUMBER(FIND("/"&A$2:A$11*1&"/","/"&D2&"/"))*B$2:B$11)}
??從產(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)
??累計(jì)每日得分:=(N(C1)=0)*5+N(C1)+IF(B2>0,-B2,0.1)
??統(tǒng)計(jì)各班所有科目成績(jī)大于60分者人數(shù):{=MMULT(N(TRANSPOSE(A2:A21)=H3:H6),N(COUNTIF(OFFSET(C2:F2,ROW(2:21)-2,),">=60")=4))}
??區(qū)分大小寫統(tǒng)計(jì)不重復(fù)值個(gè)數(shù):{=SUM(N(MMULT(N(EXACT(A2:A11,TRANSPOSE(A2:A11))),ROW(2:11)^0)=TRANSPOSE(ROW(2:11)-1))/TRANSPOSE(ROW(2:11)-1))}
??累計(jì)每日庫(kù)存數(shù):=N(G1)+SUM(OFFSET(C$1,ROW(A1)*2-1,,2))-SUM(OFFSET(D$1,ROW(A1)*2-1,,2))
??提取當(dāng)前工作表名、工作簿名及存放目錄:工作表=REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"");工作簿=SUBSTITUTE(REPLACE(CELL("filename"),1,FIND("[",CELL("filename")),""),REPLACE(CELL("filename"),1,FIND("]",CELL("filename"))-1,""),"");存放目錄=REPLACE(CELL("filename"),FIND("[",CELL("filename")),100,"")
??提取第一次參賽取得最佳成績(jī)者姓名與成績(jī):參賽者{=INDEX(A:A,MOD(MAX((IF(NOT(ISBLANK(C2:C11)),MATCH(A2:A11,A:A,0))=ROW(2:11))*C2:C11*100+ROW(2:11)),100))};成績(jī){=MAX((IF(NOT(ISBLANK(C2:C11)),MATCH(A2:A11,A:A,0))=ROW(2:11))*C2:C11)}
??計(jì)算哪一個(gè)項(xiàng)目得票最多:{=INDEX({"A","B","C"},RIGHT(MAX(MMULT(TRANSPOSE(ROW(2:11)^0),N(IF(ISBLANK(B2:B11),"A",B2:B11)={"A","B","C"}))*10+{1,2,3})))}
??根據(jù)利率、存款與時(shí)間計(jì)算存款加利息數(shù):=FV(B2,D2,-C2,0)
??計(jì)算七個(gè)投資項(xiàng)目相同收益條件下誰(shuí)投資更少:{=MAX(PV(B2:B8,C2:C8,0,100000))}
??根據(jù)利息和存款數(shù)計(jì)算存款達(dá)到1萬(wàn)元需要幾個(gè)月:=NPER(A2,0,-B2,C2)*12
??根據(jù)投資金額、時(shí)間和目標(biāo)收益計(jì)算增長(zhǎng)率:=RATE(B2,0,-A2,C2)
??根據(jù)貸款、利率和時(shí)間計(jì)算某段時(shí)間的利息:=CUMIPMT(B2/12,C2*12,A2,1,24,0)
??根據(jù)貸款、利率和時(shí)間計(jì)算需償還的本金:=CUMPRINC(B2/12,C2*12,A2,1,24,0)
??以固定余額遞減法計(jì)算資產(chǎn)折舊值:=DB(A$2,B$2,C$2,ROW(A1),12)
??以雙倍余額遞減法計(jì)算資產(chǎn)折舊值:=DDB(A$2,B$2,C$2,1,2)
??以年限總和折舊法計(jì)算折舊值:=SYD(A$2,B$2,C$2,ROW(A1))
??使用雙倍余額遞減法計(jì)算任何期間的資產(chǎn)折舊值:=VDB(A$2,B$2,C$2*12,7,12,2)
??獲取當(dāng)前工作簿中工作表數(shù)量:=COLUMNS(sheets)&T(NOW())
??建立工作表目錄與超級(jí)鏈接:=IFERROR(HYPERLINK(INDEX(sheets,ROW(A1))&"!a1",REPLACE(INDEX(sheets,ROW(A1))&T(NOW()),1,FIND("]",INDEX(sheets,ROW(A1))),"")),"")
??選擇最后工作表的最后非空單元格:=HYPERLINK(INDEX(sheets,COLUMNS(sheets))&"!A"&LOOKUP(1,0/(INDIRECT(INDEX(sheets,COLUMNS(sheets))&"!A:A")<>""),ROW(1:1048576)))
??引用單元格數(shù)據(jù)同時(shí)引用格式:=IF(TODAY()>A2,"",TEXT(A2,格式))
??分別匯總當(dāng)前表以外的所有工作表數(shù)據(jù):AcSht=GET.CELL(62);sheets=GET.WORKBOOK(1);WorkBook=GET.CELL(66);{=IFERROR(REPLACE(INDEX(sheets,SMALL(IF(TRANSPOSE(sheets)<>AcSht,ROW(INDIRECT("1:"&COLUMNS(sheets)))),ROW(A2))),1,LEN(WorkBook)+2,""),"")}
??提取單元格的公式:名稱=GET.CELL(6,Sheet1!$B1)&T(NOW())
??羅列工作簿中所有名稱:{=IFERROR(INDEX(名稱,SMALL(IF(名稱<>"名稱",TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(名稱))))),ROW(A1))),"")}
??在任意單元格顯示當(dāng)前頁(yè)數(shù)及總頁(yè)數(shù):無(wú)拘無(wú)束的頁(yè)眉="第"&IF(橫向當(dāng)前頁(yè)=1,縱向當(dāng)前頁(yè),橫向當(dāng)前頁(yè)+縱向當(dāng)前頁(yè))&"頁(yè)/共"&總頁(yè)&"頁(yè)";縱向當(dāng)前頁(yè)=IFERROR(MATCH(ROW(),GET.DOCUMENT(64))+1,1)
??提取單元格中的批注:批注=GET.OBJECT(12,?"備注?1")
??利用列表框篩選數(shù)據(jù):篩選=IF(GET.OBJECT(78,"列表框?1"),GET.OBJECT(78,"列表框?1")*TRANSPOSE(ROW(sheet1!$A$2:$A$8)))
??判斷單元格是否被圖形對(duì)象覆蓋:=ADDRESS(ROW(INDIRECT(左上,0)),COLUMN(INDIRECT(左上,0)))&":"&ADDRESS(ROW(INDIRECT(右下,0)),COLUMN(INDIRECT(右下,0)))
??將單元格的公式轉(zhuǎn)換成數(shù)值:計(jì)算=EVALUATE(Sheet1!A3)
??將IP地址補(bǔ)足三位:IP地址=TEXT(EVALUATE("{"&SUBSTITUTE(Sheet1!A4,".",",")&"}"),"000.");
??按分隔符取數(shù)并求平均:成績(jī)=EVALUATE(SUBSTITUTE("{"&SUBSTITUTE(Sheet1!$B2,"http://","/FALSE/")&"}","/",";"))
??根據(jù)產(chǎn)品規(guī)格計(jì)算體積:體積=EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!XFD4,"(L)","*"),"(W)","*"),"(H)",""))
??計(jì)算減肥前后的三國(guó)差異:后=EVALUATE("{"&SUBSTITUTE(Sheet1!C3,":",",")&"}");?前=EVALUATE("{"&SUBSTITUTE(Sheet1!B3,":",",")&"}")
??計(jì)算各樓層空佘面積:面積=EVALUATE(SUBSTITUTE(SUBSTITUTE(Sheet1!XFD1,"[","*istext("""),"]",""")"))
??將數(shù)據(jù)分列，提取省市縣:分列=EVALUATE("{"""&SUBSTITUTE(SUBSTITUTE(Sheet1!$A5,"省","省"","""),"市","市"",""")&"""}")
??按圖書編號(hào)匯總價(jià)格:圖書=EVALUATE("{"""&SUBSTITUTE(Sheet1!B2,"/",""",""")&"""}")
??標(biāo)識(shí)B列中的重復(fù)值:條件格式:=COUNTIF($B:$B,B1)>1
??將數(shù)據(jù)間隔著色:條件格式:=MOD(SUM(N($B$2:$B2<>$B$1:$B1)),2)=0
??隱藏錯(cuò)誤值:單元格的公式:=VLOOKUP(A2,單價(jià)表!$A$2:$B$10,2,0)，條件格式:=ISERROR(B1)
??突顯前三個(gè)最大值:條件格式:=B2>LARGE($B$2:$F$10,4)
??將成績(jī)高于平均值的姓名標(biāo)示“優(yōu)等”:條件格式:=(B2>AVERAGE($B$2:$F$10))*MOD(COLUMN(),2)
??突顯奇數(shù)行:條件格式:=ISODD(ROW())
??突顯非數(shù)值:條件格式:=NOT(ISNUMBER(A2))*ISEVEN(COLUMN())
??B列中禁止輸入重復(fù)數(shù)據(jù):數(shù)據(jù)有效性設(shè)置-自定義:=COUNTIF(B:B,B8)=1
??僅允許錄入英文姓名:數(shù)據(jù)有效性設(shè)置-自定義:=SUM(--(ERROR.TYPE(INDIRECT(MID(SUBSTITUTE(A2,"?",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2,"?","")))),1)&1))=3))=LEN(SUBSTITUTE(A2,"?",""))
??強(qiáng)制錄入規(guī)范化的日期:數(shù)據(jù)有效性設(shè)置-自定義:=(LEN(A2)=8)*TEXT(A2,"#-00-00")
??建立動(dòng)態(tài)下拉選單:定義名稱:水果=OFFSET(單價(jià)表!$A$1,,,COUNTA(單價(jià)表!$A:$A))
??建立二級(jí)下拉選單:定義名稱:省=OFFSET(參考區(qū)!$A$1,,,,COUNTA(參考區(qū)!$1:$1));市=OFFSET(參考區(qū)!$A$1,1,MATCH(Sheet1!$A$2,參考區(qū)!$1:$1,0)-1,COUNTA(OFFSET(參考區(qū)!$A$1,1,MATCH(Sheet1!$A$2,參考區(qū)!$1:$1,0)-1,1048575)))
??建立不重復(fù)的下拉選單:{=INDEX(A:A,SMALL(IF(COUNTIF(Sheet1!A$1:A$8,A$1:A$8)=0,ROW($1:$8),1048576),ROW(A2)))&""}?(生成不重復(fù)單位);定義名稱:=OFFSET(名單!$B$1,,,8-COUNTBLANK(名單!$B$1:$B$8))
??讓A列只能輸入質(zhì)數(shù):數(shù)據(jù)有效性設(shè)置-自定義:=OR(A2=2,A2=3,PRODUCT(MOD(A2,ROW(INDIRECT("2:"&?INT(A2^0.5))))))
??設(shè)置D列只能錄入男職工的姓名:數(shù)據(jù)有效性設(shè)置-自定義:=VLOOKUP(D2,A:B,2,0)="男"
??禁止錄入不完整的產(chǎn)品規(guī)格:數(shù)據(jù)有
??效性設(shè)置-自定義:=ISNUMBER(SEARCH("長(zhǎng)?*寬?*高?*",B2))
??自動(dòng)記錄進(jìn)庫(kù)時(shí)間:=IF(ISBLANK(B2),"",IF(C2="",NOW(),C2))
??記錄歷史最高值:=MAX(B:B,D2)
??解一元二次方程:X+100=X^2+10
??以上就是excel使用函數(shù)公式教程，希望可以幫助到大家。
??解二元一次方程:X=10X=(100-5Y)/25,Y=Y/5=(200?+4X)/4
最后，小編給您推薦，金山毒霸“PDF轉(zhuǎn)化”，支持如下格式互轉(zhuǎn)：· Word和PDF格式互轉(zhuǎn)· Excel和PDF格式互轉(zhuǎn)· PPT和PDF格式互轉(zhuǎn)· 圖片和PDF格式互轉(zhuǎn)· TXT和PDF格式互轉(zhuǎn)· CAD和PDF格式互轉(zhuǎn)