EXCEL公式大全操作應(yīng)用實(shí)例(史上最全)

1、EXCEL公式大全操作應(yīng)用實(shí)例（史上最全）excel常用函數(shù)公式及技巧搜集從身份證號碼中提取出生年月口從身份證號碼中提取出性別從身份證號碼中進(jìn)行年齡判斷按身份證號號碼計(jì)算至今天年齡以2006年10月31 口為基準(zhǔn)口,按按身份證計(jì)算年齡（周歲）的公式按身份證號分男女年齡段【年齡和工齡計(jì)算】根據(jù)出生年月計(jì)算年齡根據(jù)出生年月推算生肖如何求出一個(gè)人到某指定口期的周歲？計(jì)算距離退休年齡的公式求工齡計(jì)算工齡年齡及工齡計(jì)算H動算出工齡口期格式為（yyyy.mm.dd）【時(shí)間和日期應(yīng)用】1'1動顯示當(dāng)前口期公式如何在單元格中fl動填入當(dāng)前口期如何判斷某口是否星期天某個(gè)口期足星期幾什么函數(shù)可以顯示當(dāng)前星

2、期求本月天數(shù)顯示昨天的口期關(guān)于取口期如何對口期進(jìn)行上、中、下旬區(qū)分如何獲収一個(gè)月的最大天數(shù)日期格式轉(zhuǎn)換公式【排名及排序篩選】一個(gè)具有11項(xiàng)匯總方式的函數(shù)SUBTOTAL自動排序按奇偶數(shù)排序自動生成序號如何1*1動標(biāo)示A欄中的數(shù)字大小排序？如何設(shè)建自動排序重復(fù)數(shù)據(jù)得到唯一的排位序列按字符數(shù)呈排序排序字母與數(shù)字的混合內(nèi)容隨機(jī)排序排序的問題怎樣才能讓數(shù)列白動加數(shù)一個(gè)排序問題數(shù)字的|'1動排序，插入后不變？根據(jù)規(guī)律的重復(fù)的姓名列產(chǎn)生I' I動序號排名的函數(shù)白動排名公式百分比排名的公式寫法為：平均分及總分排名求名次排名排名次根據(jù)分?jǐn)?shù)進(jìn)行普通排名對于普通排名分?jǐn)?shù)相同時(shí)，按順序進(jìn)行不重復(fù)排名

3、依分?jǐn)?shù)比高低名次成績排名美國式排名中國式排名求最結(jié)簡的H動排名公式排序后排名位次排名根據(jù)雙列成績進(jìn)行共同排名在雙列間排名等次排名不等次排名（行小排先）不等次排名（行大排先）順次排名有并列排名無并列排名有并列分段排名無并列分段排名成績排名如何排名數(shù)據(jù)排名（隔幾行排名）根據(jù)分?jǐn)?shù)進(jìn)行倒排名倒數(shù)排名函數(shù)足什么如何實(shí)現(xiàn)每口各車間產(chǎn)呈的排名分?jǐn)?shù)相同時(shí)按照一科的分?jǐn)?shù)進(jìn)行排名篩選后fl動產(chǎn)生序列號并匯總?cè)绾魏Y選奇數(shù)行函數(shù)篩選姓名名次篩選如何實(shí)現(xiàn)快速定位（篩選出不重復(fù)值）如何請?jiān)贜列中列出A1:L9中每列都存在的數(shù)值 自動為性別編號的問題【文木與頁而設(shè)置】EXCEL中如何刪除*號將字符串中的星號'、*&

4、quot;替換為其它字符去空格函數(shù)如何去掉字符和單元格里的空格怎樣快速去除表中不同行和列的空格如何禁止輸入空格代替單元格中字符串把單元格中的數(shù)字轉(zhuǎn)變成為特定的字符格式把有六百多個(gè)單元格的一列，變成一頁的多列將N列變M列公式歸納為一列變四列四列變一列重復(fù)四次填充多行數(shù)據(jù)排成一列將單元格一列分為多列首寫字母大寫把單元格編號中的小寫字母變成大寫字母讓姓名左右對齊數(shù)字居中而小數(shù)點(diǎn)又對齊計(jì)算指定單元格編號組中非空單元格的數(shù)雖比較兩個(gè)單元格內(nèi)容繪否一致怎么樣設(shè)置才能讓這一列的每個(gè)單元格只能輸入12位如何讓工作表奇數(shù)行背衆(zhòng)是紅色偶數(shù)行背衆(zhòng)是藍(lán)色計(jì)算特定的一組單元格中，滿足條件的單元格的個(gè)數(shù)把文本格式的數(shù)字轉(zhuǎn)

5、換成真正的數(shù)字設(shè)世頁碼Excel表格里如何插入頁碼的？如何設(shè)置頁腳首頁為第5頁表格的頁腳問題無拘無束的頁眉打印表頭Excel打印中如何不顯示錯(cuò)誤值符號對于一些不可打印的字符的處理用那個(gè)函數(shù)可將個(gè)位數(shù)前而的寥值顯示出來？如果你要在A3的前而插入100行請問如何每隔30行粘貼一新行在工作表里有連續(xù)10行數(shù)據(jù)，現(xiàn)在要每行間格2行一個(gè)大表每一行下面需要加一行空行，怎么加最方便Excel中插入空白行快速刪除工作表中的空行快速刪除空行一次刪完Excel里面多出很多的空白行每30行為一頁并加上一個(gè)標(biāo)題如何實(shí)現(xiàn)如何實(shí)現(xiàn)隔行都加上標(biāo)題項(xiàng)如何把標(biāo)簽頁去掉的？去掉默認(rèn)的表格線（網(wǎng)線）表格的框線列標(biāo)的標(biāo)識變了符號的意

6、義雙擊格式刷競也能COPY文木（不是文本格式）查找+格式刷的妙用光標(biāo)移動最后一行為文木最后一行為數(shù)字如何在EXCEL中快速定位最后一行數(shù)據(jù)用SUN函數(shù)快速求和在Excel中快速查看所有工作表公式在Excel中設(shè)呂行間距怎樣同時(shí)改變多行行高快速換行讓文本換行在Excel中行列快速轉(zhuǎn)換將原有列中的內(nèi)容倒置過來快速回到A1單元格復(fù)制粘貼中回車鍵的妙用一次選中批注單元格一次在所有單位格中插入批注在公式中插入批注不連續(xù)單元格填充同一數(shù)據(jù)空白行的填充怎樣用函數(shù)向下實(shí)現(xiàn)|'|動填充怎么設(shè)置H動保存避免輸入網(wǎng)址和電子郵件地址時(shí)的超鏈接單元格前面自動加了等號加蓋公章査找+格式刷的妙用Excel中鼠標(biāo)雙擊

7、妙用Excel中快速定位的技巧實(shí)例在Excel中插入Flash時(shí)鐘的步驟小寫數(shù)字轉(zhuǎn)換成人民幣大寫輕輕松松制作超復(fù)雜Excel表頭【字符截取與增減】截取單元格里某個(gè)字符后的字符截取字符的公式如何確定*號后的數(shù)字如何提取數(shù)字中間的數(shù)三個(gè)數(shù)中，如何取出中間那個(gè)取數(shù)值后三位公式取數(shù)函數(shù)如何把單元格中的數(shù)字提取出來（字符串中不連續(xù)） 數(shù)字在字符串中不連續(xù)如何提取數(shù)字用如何提取前后的字符怎樣刪去：后的文字怎樣只取之后的文字？獲取單元格內(nèi)容中字符串如何提取一串?dāng)?shù)字中的幾位數(shù)字（字符）如何把一個(gè)單元格中的數(shù)字挑出來 分割文本按照給定的位數(shù)，截?cái)嘈?shù)點(diǎn)后的數(shù)字單元格數(shù)字提取問題以關(guān)鍵字提取名稱如何把文木中的前幾

8、個(gè)字符去除對一列中的文字統(tǒng)一去掉最后一個(gè)字討如何去掉單元格中的第一個(gè)數(shù)字？論一下取最后一個(gè)單詞的方法如何去掉單元格最后一位數(shù)字如何在一列已經(jīng)輸入的數(shù)據(jù)前添加、'p"什么函數(shù)可以插入字符如何在數(shù)據(jù)前添加號數(shù)字前而加上數(shù)字【數(shù)據(jù)拆分與合并】數(shù)字如何拆分單元格中的數(shù)據(jù)拆分單元格的拆分如何拆分字組用連字符來合并文木怎樣把不同格式不同位呂的內(nèi)容合并到一個(gè)單元格內(nèi) 把不同單元格的內(nèi)容合到一個(gè)單元格里合并與拆分合并不同單元格的內(nèi)容關(guān)于文字在表格中的組合求拆解合并公式如何把字母和數(shù)字分開？把分散在各單元格的內(nèi)容合在一個(gè)單元格中多個(gè)工作表的單元格合并計(jì)算【條件自定義格式】通過條件格式將小計(jì)和總

9、計(jì)的行設(shè)為不同的顏色如何實(shí)現(xiàn)這樣的條件格式隔行不同字體顏色怎么設(shè)置讓不同類型數(shù)據(jù)用不同顏色顯示如何做這樣的EXCEL表（顏色交叉）條件格式使用條件格式設(shè)港顏色條紋相同數(shù)據(jù)的提示？如何做到小于10顯示二位小數(shù)，大于10顯示一位小數(shù)如何根據(jù)數(shù)值的正負(fù)加上符號120, 000 顯示為 12.0121. 999 顯示為 12.2自定義單元格格式將單元格中的數(shù)全部變成萬元表示有何辦法實(shí)現(xiàn)將一張表中的數(shù)據(jù)山元的單位轉(zhuǎn)換為萬元常用的自定義格式自定義格式怎樣定義格式在工具按鈕之間設(shè)置分隔線白定義區(qū)域?yàn)槊恳豁摰臉?biāo)題一個(gè)單元格內(nèi)格式問題 定制單元格數(shù)字顯示格式 巧用定位選條件單元格 工作表的標(biāo)簽的字體和大小可以更

10、改嗎 隔行用不同顏色顯示，請問如何做 將單元格設(shè)置為有''凸出"的效果或''凹進(jìn)去"的效果 在Excel中設(shè)計(jì)彩色數(shù)字定義名稱的妙處零值問題【匯總計(jì)算與統(tǒng)計(jì)】個(gè)調(diào)稅公式算物價(jià)的函數(shù)H動計(jì)算應(yīng)收款滯納金淘汰率應(yīng)用公積金的一個(gè)函數(shù)如何利用公式將數(shù)值轉(zhuǎn)為百分比格式比高得分公式門動評定獎級對帶有單位的數(shù)據(jù)如何進(jìn)行求和對a列動態(tài)求和動態(tài)求和公式 列的跳躍求和 有規(guī)律的隔行求和如何實(shí)現(xiàn)奇數(shù)行或偶數(shù)行求和單數(shù)行求和統(tǒng)計(jì)偶數(shù)單元格合計(jì)數(shù)值隔行求和公式設(shè)垃隔列將相同項(xiàng)目進(jìn)行求和隔行或隔列加總請問如何在一百行內(nèi)做隔行相加如何將間隔一定的列的數(shù)據(jù)相加呢隔列求和（A

11、、B列隔列求和的公式隔列求和關(guān)于隔行、隔列求和的問題EXCEL中求兩列的對應(yīng)元素乘積之和計(jì)算900-1000之間的數(shù)值之和雙條件求和如何實(shí)現(xiàn)這樣的條件求和Al:A10數(shù)字顯為文本格式時(shí)，如何求和如何分班統(tǒng)計(jì)男女人數(shù)統(tǒng)計(jì)數(shù)值大于等于80的單元格數(shù)0計(jì)算出A1里有幾個(gè)abc有條件統(tǒng)計(jì)如何統(tǒng)計(jì)各年齡段的數(shù)雖如何計(jì)算20-50歲的人數(shù)？如何統(tǒng)計(jì)40-50歲的人的個(gè)數(shù)要統(tǒng)計(jì)出7歲的女生人數(shù)統(tǒng)計(jì)人數(shù)如何統(tǒng)計(jì)Al:A10. Dl:D10中的人數(shù)？如何讓EXCEL白動從頭統(tǒng)計(jì)到當(dāng)前單元格統(tǒng)計(jì)人數(shù)統(tǒng)計(jì)人數(shù)求各分?jǐn)?shù)段人數(shù)有什么方法統(tǒng)計(jì)偶數(shù)如何顯示工資統(tǒng)計(jì)中的問題統(tǒng)計(jì)數(shù)據(jù)問題一例根據(jù)給定的條件，對數(shù)據(jù)進(jìn)行合計(jì)十列數(shù)

12、據(jù)合計(jì)成一列統(tǒng)計(jì)漢字字符個(gè)數(shù)關(guān)于取數(shù)統(tǒng)計(jì)單元格內(nèi)不為空的格數(shù)白動將銷雖前十名的產(chǎn)品代號及銷呈填入表中統(tǒng)計(jì)最大的連續(xù)次數(shù)3個(gè)''不重復(fù)"個(gè)數(shù)統(tǒng)計(jì)在一列有重復(fù)的姓名中，如何統(tǒng)計(jì)出具體有幾人 計(jì)數(shù)的問題不相同如何分班統(tǒng)計(jì)男女人數(shù)在幾百幾個(gè)數(shù)據(jù)中發(fā)現(xiàn)重復(fù)項(xiàng)統(tǒng)計(jì)互不相同的數(shù)據(jù)個(gè)數(shù)多個(gè)工作表的單元格合并計(jì)算單個(gè)單元格中字符統(tǒng)計(jì)數(shù)據(jù)區(qū)包介某一字符的項(xiàng)的總和,該用什么公式函數(shù)如何實(shí)現(xiàn)分組編碼【數(shù)值取整及進(jìn)位】取整數(shù)函數(shù)數(shù)值取整求余數(shù)的函數(shù)四舍五入公式對數(shù)字進(jìn)行四舍五入如何實(shí)現(xiàn)''見分進(jìn)元"四舍五入如何四舍五入取兩位小數(shù)根據(jù)給定的位數(shù)，四舍五入指定的數(shù)值四舍六

13、入如何實(shí)現(xiàn)2舍3入怎么設(shè)世單元格以T元四舍五入ROUND函數(shù)的四舍五入不進(jìn)位的解決方法？7保留一位小數(shù)如何三舍四入另類四舍五入想把小數(shù)點(diǎn)和后而的數(shù)字都去掉，不要四舍五入求真正的四舍五入后的數(shù)小數(shù)點(diǎn)進(jìn)位個(gè)位數(shù)歸0或者歸5【大小值或中間值】求平均值如何實(shí)現(xiàn)求平均值時(shí)只對不等于零的數(shù)求均值？平均分的問題怎樣求最大值（最小值或中間值）平均數(shù)怎么弄去掉其中兩個(gè)最大值和兩個(gè)最小值的公式去一行最高分最低分求平均值在9個(gè)數(shù)值中去掉最高與最低然后求平均值求最大值（n列）如何實(shí)現(xiàn)求平均值時(shí)只對不等于零的數(shù)求均值？得到單元格編號組中最大的數(shù)或最小的數(shù)標(biāo)記出3個(gè)最大最小值取前五名，后五名的方法如何用公式求出最大值所在

14、的行？求多個(gè)最高分如何求多條件的平均值想求出第三大之?dāng)?shù)值【査詢和查找引用】査找順序公式怎樣實(shí)現(xiàn)結(jié)確査詢査找及引用査找函數(shù)的應(yīng)用怎么能方便的判斷某個(gè)單元格中包倉多少個(gè)指定的字符？如何用査找函數(shù)日期查找的問題如何門動查找相同單元格內(nèi)容査找函數(shù)怎樣對號入座（査找）一個(gè)文本查找的問題査找一列中最后一個(gè)數(shù)值查找重復(fù)字符請教查找普換問題IF函數(shù)替換法總結(jié)査找的函數(shù)（査找末位詞組）怎樣從原始數(shù)據(jù)中白動獲取最后一個(gè)數(shù)據(jù)兩列數(shù)據(jù)查找相同值對應(yīng)的位置査找數(shù)據(jù)公式兩個(gè)（基木査找函數(shù)為VLOOKUP,MATCH）【輸入數(shù)據(jù)的技巧】談?wù)凟xcel輸入的技巧一列中不輸入重復(fù)數(shù)字如果要査找重復(fù)輸入的數(shù)字 單元格輸入大呈0值

15、輸入超級技巧如何在C列中輸入工號在D列顯示姓名 輸入提示如何做在信息輸入前就給予提示 數(shù)據(jù)有效性【身份證信息？提取】從身份證號碼中提取出生年月日=TEXT(MID(Al,7,6+(LEN(Al)=18)*2),#-00-00,)+0 =TEXT(MID(Al,7,6+(LEN(Al)=18)*2),#-00-00,)*l =IF(A2on,TEXT(LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),n#-00-00M)+0,)顯示格式均為yvyy-m-d.(最簡單的公式，把單元格設(shè)置為日期格式)=IF(LEN(A2)=15,19, &MID(A

16、2,7,2)&,-,&MID(A2,9,2)&''&MID(A2,11,2),MID(A2,7,4)& ,-,&MID(A2,ll,2)&M-"&MID(A2,13,2)顯示格式為yvyy-mni-ddo (如果要求為“ 1995/03/29”格式的話，將”換成S即可)=IF(D4=M,nJF(LEN(D4)=15,TEXT(n19n&MID(D4,7,6), 0000 年 00 月 00 日 ,),IF(LEN(D4)=18,TEXT(MID(D4,7,8), 0000 年 00 月 00 日”

17、)顯示格式為yyyy年mm月dd日。(如果將公式中“oooo年oo月oo日”改成“oooo()o-oo”，則顯示格式 為 yyyy-mm-dd )=IF(LEN(A1:A2)=1& MID(A1:A2,7,8),M19M&MID(A1:A2,7,6)顯示格式為yyvymmddo=TEXT(LEN(A1)=15)*19&MID(Al,7,6+(LEN(Al)=18)*2),n#-00-00H)+0 =IF(LEN(A2)=18,MID(A2,7,4)&-MID(A2,11,2),19&MID(A2,7,2)&-MID(A2,9,2) =MID(A1

18、,7,4)& '年”&MID(A1,11,2)&”月 ”&MID(A1,13,2)&“ 日”=IF(AloH,TEXT(LEN(Al)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2)；#-OO-OOM)從身份證號碼中提取出性別=IF(MOD(MID(A1，15,3),2)畀男”畀女”)(最簡單公式)=IF(MOD(RIGHT(LEFT(Al,17),2),n男畀女')=IF(A2v>” ”JF(MOD(RIGHT(LEFT(A2，17),2),”男”齊女”),)=IF(VALUE(LEN(ROUND(RI

19、GHT(A1,1)/2,2)=1,M 男蔦”女“)從身份證號碼中進(jìn)行年齡判斷=IF(A3o,w,DATEDIF(TEXT(LEN(A3)=15*19&MID(A3,7,6+(LEN(A3)=18*2),”#0000”),TODAY(),”Y”),)=DATEDIF (Al, TODAY () , “Y”)(以上公式會判斷是否已過生日而自動增減一歲)=YEAR(NOW()-MID(E2JF(LEN(E2)=18,9,7),2)-1900=YEAR(TODAY()-IF(LEN(A1)=15,19,&MID(A1,7,2),MID(A1,7,4)=YEAR(TODAY()-VALU

20、E(MID(B1,7,4)&“歲“=YEAR(TODAY()-IF(MID(B1,18,1)='" ,CONCATENATE(M19'MID(Bl,7,2)AnD(Bl,7,4)按身份證號號碼計(jì)算至今天年齡=DATEDIF(TEXT(LEN(A1)=15)*19&MID(Al,7,6+(LEN(Al)=18)*2),lt#-00-00lt),TODAY0；,ylt) 以2006年10月31日為基準(zhǔn)日,按按身份證計(jì)算年齡(周歲)的公式 =DATEDIF(TEXT(MID(Al,7,6+(LEN(Al)=18)*2),H#-00-00',)*l,H

21、2006-10-3r,Hyn) 按身份證號分男女年齡段按身份證號分男女年齡段，身份證號在K列，年齡段在J列(身份證號為18位)男性16周歲以下為1男性16周歲(含16周歲)以上至50周歲為2 男性50周歲(含50周歲)以上至60周歲為3 男性60周歲(含60周歲)以上為4女性16周歲以下為1女性16周歲(含16周歲)以上至45周歲為2 女性45周歲(含45周歲)以上至55周歲為3 女性55周歲(含55周歲)以上為4=MATCH(DATEDIF(DATE(MID(Kl,7,4),MID(Kl,ll,2)JMID(Kl,13,2),TODAY(),HyH),0,16,50,600,0,5,5*IS

22、EVEN(MID(Kl,17,l)=SUM(-(DATEDIF(MID(K1,7,4)&*7n&MID(Kl,ll,2)&,7,&MID(Kl,13,2),TODAY(),ny,)>=0,16,45,55+0,0,5,5*MOD(MID(K1,17,1),2)【年齡和工齡計(jì)算】根據(jù)出生年月計(jì)算年齡= DATEDIF(Al,TODAY(),nyn)=DATEDIF(A1,TODAY(), y1)&”周歲“=DATEDIF(Al,NOW(),y n)根據(jù)出生年月推算生肖中國人有12生肖，屬什么可以推算出來。即用誕生年份除以12,再用除不盡的余數(shù)對 照如

23、下：0猴，1雞，2狗，3豬，4鼠，5牛，6虎，7兔，8龍，9-*蛇，10 -馬，11-羊例如：XXX出生于1921年，即用1921年除以12,商得數(shù)為160,余數(shù)為1,對 照上面得知余數(shù)1對應(yīng)生肖是雞，XXX就屬雞。二MID猴雞狗豬鼠牛虎兔龍蛇馬羊“,MOD(YEAR(A2),+1,1)(2007)如何求出一個(gè)人到某指定日期的周歲？=DATEDIF(起始日期，結(jié)束日期,“ Y”)計(jì)算距離退休年齡的公式=IF(E2=* * ； , ,IF(E2>=V2,M已經(jīng)退休営'距離退休還有"&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(

24、U2),DAY(U2), Y,)&”年"&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2), YM, )&”個(gè)月 "&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2),Md, )&M天”) 其中E2為年齡(可用身份證號碼的公式生成)；V2 為法定退休年齡(男 60,女 50)公式為：=IF(D2=”,”,IF(D2=”,60,50)D2為男或女(可用身份證號碼的公式生成)；U2為出生年月日(可用身份證號碼的公式生成)。求工齡

25、=DATEDIF(B2,TODAY(),y,)=DATEDIF(B2,TODAY(),=DATEDIF(B2,TODAY(),md,)=DATEDIF(B2,TODAY(),y,)&”年“&DATEDIF(B2,TODAY(),”ynT)&”月”&DATEDIF(B2,TODAY(),nmd,)&n 日“計(jì)算工齡二DATEDIF(C6,C8,”y“)求兩日期間的年數(shù)二DATEDIF(C6,C8,”ym“)求兩日期間除去整年數(shù)剩余的月數(shù)=DATEDIF(C6,C8,nm,)求兩日期間的總月數(shù)如果只需要算出周年的話，可以用二datedif(H1978-8nz

26、H2006-5M；'Yn)年齡及工齡計(jì)算有出生年月如何求年齡？有工作時(shí)間如何求工齡？(求出的結(jié)果為多少年另幾個(gè)月，如：0303的形式，即3年零 3個(gè)月)。al是出生年月或工作時(shí)間：=datedif(al,today(),ny")=text(datedif(al,today(),HyM),H00n)&text(datedif(al,today(),nmM),n00n)如B2=1964-9-l 則:=TEXT(DATEDIF(B2,TODAY(),y,),MOOn)&TEXT(MOD(DATEDIF(B2,TODAY。, ”m”),12) 昇0(T)顯示4009=

27、TEXT(DATEDIF(B2,TODAY(),y, ), 00 年“)&TEXT(MOD(DATEDIF(B2,TODAY()Jm”),12),“00 月”)顯示 40 年 09 月如果你找不到DATEDIF函數(shù)，也可以不用DATEDIF函數(shù)，如B2=1964-9-l 則:=TEXT(RIGHT(YEAR(NOW()-B2),2),00,)&TEXT(MOD(MONTH(NOW()-B2)-1,12),OOh) 顯示4009=TEXT(RIGHT(YEAR(NOW()-B2),2)&”年”&MOD(MONTH(NOW()-B2)-1,12)&”個(gè)月,M

28、n) 顯示40年09個(gè)月自動算出工齡日期格式為(yyyy.mm.dd)能否用：(yyyy.nmi.dd)這種格式來計(jì)算出工齡有多長呢？以前用這樣一段(二TEXT(RIGHT(YEAR(NOW()A1),2)&"年'&MOD(MONTH(NOW()A1)1,12)&”個(gè)月)。但這種方法只能用：(yyyv-nmi-dd)這樣的日期格式才能實(shí)現(xiàn)！你不妨把替換成''，不就行了嗎，再說后者是日期的一種標(biāo)準(zhǔn)格式，=TEXT(RIGHT(YEAR(NOW()-SUBSTITUTE(Al,.H,n-n),2)&"年& MOD(

29、MONTH(NOW()-SUBSTITUTE(Al,M.H,n-n)-l,12)&H 個(gè)月I 川"I 川 | 山)|川|汕|汕|"|川|)|川|)|川|川|川|)|"汕|汕|汕|汕|"|)|川|)|)|川|)|"|)【時(shí)間和日期應(yīng)用】自動顯示當(dāng)前日期公式=YEAR(NOW() =MONTH(NOW() =DAY(NOVV()當(dāng)前年 當(dāng)前月 當(dāng)前日如何在單元格中自動填入當(dāng)前日期Ctrl+;如何判斷某日是否星期天=WEEKDAY(A2,2)二 TEXT(Al,”aaaa“)=MOD(A1,7)<2某個(gè)日期是星期幾比如2007年2月9日

30、，在一單元格內(nèi)顯示星期兒。=TEXT(Al；aaau) 二 TEXT(Al,”aaaa“) 二 TEXT(AlJddcT) =TEXT(Al；ddddu)(五)(星期五)(Fri) (Friday)什么函數(shù)可以顯示當(dāng)前星期如：星期二 10:41:56二TEXT(NOW(),"aaaa hh:mm:ssn)求本月天數(shù)設(shè)A1為2006-8-4求本月天數(shù)A1=DAY(DATE(YEAR(A1),MONTH(A1)+1,0)也有更簡便的公式：二DAY(EOMONTH(NOW(),0)需加載分析工具箱。當(dāng)前月天數(shù)：=DATE(YEAR(TODAY(),MONTH(TODAY()+1,1)-DA

31、TE(YEAR(TODAY(),MONTH(TOD AY(),1)用公式算出除去當(dāng)月星期六、星期日以外的天數(shù) =SUMPRODUCT(-(MOD(ROW(INDIRECT(DATE(YEAR(NOyV(),MONTH(NOW(),l)&” :DATE(YEAR(NOW(),MONTH(NOW()+1,0),7)>1)顯示昨天的日期每天需要單元格內(nèi)顯示昨天的日期，但雙休日除外。 例如，今天是7月3號的話，就顯示7月2號，如果是7月9號，就顯示7月6號。=IF(TEXT(TODAY(),“AAA”)二'一”,TODAY()3,IF(TEXT(TODAY(),'AAA&

32、#39;)二”日11 ,TODAY()-2,TODAY()-1)=IF(TEXT(TODAY(),“AAA hi,TODAY()3,TODAY()l)關(guān)于取日期怎么設(shè)個(gè)公式使A1在年月日向后推5年，變成2011-7-15=DATE(YEAR(A 1 )+5,MONTH( A1 ),DAY(A 1) =EDATE(A1,12*5)如何對日期進(jìn)行上、中、下旬區(qū)分=LOOKUP(DAY(A1),0,11,2131,"± 旬”,”中旬下旬下旬")如何獲取一個(gè)月的最大天數(shù)二DAY(DATE(2002,3,l)l)或二DAY(B11)，Bl 為20010301日期格式轉(zhuǎn)換公式

33、將 “01/12/2005” 轉(zhuǎn)換成“20050112”格式=RIGHT(A 1,4)&MID(A 1,4,2)&LEFT(A 1,2)= YEAR($A2)&TEXT(MONTH($A2)JOO“)&TEXT(DAY($A2),"0(T) 該公式不用設(shè)置數(shù)據(jù) 有效性，但要設(shè)置儲存格格式。也可以用下列兩方法：1、先轉(zhuǎn)換成文本，然后再用字符處理函數(shù)。2、數(shù)據(jù)卜分列日期卜MDY將“2005年9月”轉(zhuǎn)換成“200509”格式先用公式：二text(al,”yyyymm”)+O然后將單元格格式為常規(guī)。將“200586”格式轉(zhuǎn)換為“20050806”格式用公式：=

34、TEXT(A1,YYYYMMDD,)反之，將20050806轉(zhuǎn)為日期2005-8-6格式，可用公式：=DATE(LEFT(A 1,4),MID( A1,5,2),RIGHT( A1,2)15另四種公式：=text(al,MOOOO-OO-OOM)顯示：2005-08-06=-TEXT(Al,n#-00-00n),把單元格設(shè)置為日期格式顯示：2005-8-6=TEXT(20050806,n0000-00-00n)*l，單元格設(shè)置日期型顯示：2005-8-6=VALUE(LEFT(A1,4)& ''&MID(A1,5,2)& r'&RIGHT

35、SlR) 顯示：2005-8-6將“20060501”轉(zhuǎn)換為“20060501”格式=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)將“199306”轉(zhuǎn)換為“19936”公式 1： =LEFT(A3,4)&M-n&RIGHT(A3,2)*l公式2： =-TEXT( A3* 100+1, * '#-00-00* *)公式2需要設(shè)置單元格格式，自定義：e-m公式 3： =TEXT(TEXT(A3&n01n,0000-00-00u),e-mn)把 198405 轉(zhuǎn)換成 1984.05一、査找一1984,替換一1984.二、如果全部是年月

36、的話，我個(gè)人建議，1、采取輔助=mid (xxxxxx, 1, 4) &" & right (xxxxxx, 2)2、選中這列，用數(shù)據(jù)中的分列。然后三、單元格格式/數(shù)字/自定義，類型下面輸入：#”"#將文本“2004.01.02”轉(zhuǎn)換為日期格式：2004-1-2=DATE(MID(A 1,1,4),MID( A1,6,2),MID( A 1,9,2)將2005-8-6轉(zhuǎn)換為2005年8月6日格式=TEXT(A1； yyyy,n年月“”d”“日象22怎樣轉(zhuǎn)換成22日?轉(zhuǎn)成當(dāng)年當(dāng)月的日子公式為：=date(year(now(),month(now(),22)將“

37、2006年5月"轉(zhuǎn)換成“2006年05月”公式為:=TEXT(A 8,MyyyyMn月也可以這樣處理：選中單元格，設(shè)置單元格公式一數(shù)字一自定義，將yyyy “年” m “月” 改為：yyyy “年” mm “月”，即可。但這方法打印出來顯示為：2006/5/將“1968年6月12日”轉(zhuǎn)換為“1968/6/12"格式=YEAR(A1)&“/”&MONTH(Al)&“/“&DAY(Al)顯示：1968/6/12=TEXT(Al,nyyyy/mni/ddM)顯示：1968/06/12將“1968年6月12日”轉(zhuǎn)換為“1968612”格式=¥

38、;EAR(Al)&,-n&MONTH(Al)&,-n&DAY(Al)顯示：1968-6-12二 TEXT(Al,”yyyymmd(r')顯示：1968-06-12將1993-12-28的日期格式轉(zhuǎn)換成1993年12月=CONCATENATE(YEAR(A1),H 年“,MONTH(Al),” 月”)=YEAR(A1)&“年”&MONTH(A1)&“月”也可以自定義格式$404捫年“m”月“將“197852”包含年月日的日期轉(zhuǎn)換成“197805”只有年月的格式=year(Al)&text(month(Al),"0(

39、F')要將“99.08.15”格式轉(zhuǎn)換成“1999.08.15”如何做選中列，數(shù)據(jù)菜單中選分列，分列過程中''格式"選''日期YMD",結(jié)束。要保持2005/8/6格式當(dāng)輸入2005/8/6后系統(tǒng)自動變成2005-8-6,要保持2005/8/6格式，可以使用強(qiáng)制文本(前 面加，號)或使用公式=TEXT( A1, * * Y Y Y Y/MM/DD *) o也可以用另一種公式：=IF(ISERROR(TEXT(Al,yyyy/mm/dd,),TEXT(Al,0000!/00!/00M),TEXT(Al,Myyyy/mm/d d“)將“二

40、OO三年十二月二十五日”轉(zhuǎn)為“20031225”格式，1、可以用數(shù)組公式將中文日期轉(zhuǎn)化為日期系列數(shù)=14610+MATCH(SUBSTITUTE(A3,” 元,-*,),TEXT(ROW($14611:$55153),DBNumlyyyy 年 m 月 d 日 ”),0)該公式速度較慢。2、改進(jìn)后的公式，速度要快的多：=DATE(1899+MATCH(LEFT(A7,4),TEXT(ROW($1900:$2100),DBNunil0000,),0),MO NTH(MATCH(SUBSTITUTE(MID(A7,6,7),U 元,畀一”),TEXT(ROW($l:$366),”DBNumlm 月

41、d 日 ”),0),DAY(MATCH(SUBSTITUTE(MID(A7,6,7),”元”，“一 ,),TEXT(ROW($l:$366),nDBNumlm 月 d 日”),0)要設(shè)置為1900年的日期格式。日期格式轉(zhuǎn)換如A列是月份數(shù)為8, B列是日期數(shù)為18,如何在C列顯示“8月18日”二 A1&”月"&B1&”日”反之，要將C列的“8月i 8日”直接分別到D、E列，顯示月份和日期，月數(shù)份二 LEFT(C5,FIND月 U,C5)-1)日期數(shù)=MID(C5,HNDC，月 M,C5)+1,FIND(H 日 M,C5)-FIND(H 月 M,C5)-1)也可分

42、別用公式：=month(c5)=day(-c5)日期格式轉(zhuǎn)換問題輸入的日期是94-07-26.與另一格的”001”合并，合并出來是:040726001.二 TEXT(A1,”YYMMDD”) & “001”要想自動取得“編制日期：XXXX年X月X日”可在該單元格輸入編制日期'&TEXT(TODAY(),“yyyy年m月d日”)|"|川|)|山)| 山)|川|)|汕|"|)|"山 |)|山|)|川|)|"|)|計(jì)| 川 |)|)|川|)|)|汕|汕|)|【排名及排序篩選】一個(gè)具有11項(xiàng)匯總方式的函數(shù)SUBTOTAL=SUBTOTAL

43、(9,$B$2:B2)在數(shù)據(jù)篩選求和上有意想不到的功能，11項(xiàng)功能為：1、求平均數(shù)，2、求計(jì)數(shù)，3、求 計(jì)數(shù)值(自動篩選序列)4、求最大值，5、求最小值，6、求乘積，7、求總體標(biāo)準(zhǔn)偏差，8、 求標(biāo)準(zhǔn)偏差、9、求和，10、求方差，11、求總體方差。自動排序=SUBTOTAL(3,$B$2:B2)* 1 =IF(A2oAl,l,N(Cl)+l) 按奇偶數(shù)排序我想請教怎樣按奇數(shù)順序然后再按偶數(shù)順序排序=IF(MOD(A1,2),0,1)=IF(ROW()>50,(ROW()*2)-100,(ROW()*2)-1) =ROW()*2-1-(ROW()>50)*99自動生成序號比如在第二列中

44、輸入內(nèi)容回車后第一列的下一行自動生成序列號。=IF(B2o,H,A2+l,nn)如何自動標(biāo)示A欄中的數(shù)字大小排序？=RANK(A1,$A$1:$A$5)=RANK(A1,A：A)如何設(shè)置自動排序A列自動變成從小到大排列B=SNL4LL(A$2: A$28,RO W(1:1)A列自動變成從大到小排列B=LARGE(A$2:A$2&ROW(1:1)重復(fù)數(shù)據(jù)得到唯一的排位序列想得到數(shù)據(jù)的出現(xiàn)總數(shù)嗎(1,2, 2,3,4, 4, 5數(shù)據(jù)的出現(xiàn)總數(shù)為5) ? 解答：不需要插列,不需要很多的函數(shù)就行了.二 RANK(B3,B$3:B$12)+COUNT1F(B$3:B3,B3)1按字符數(shù)量排序制作

45、歌曲清單時(shí)，習(xí)慣按字符數(shù)量來排列分類，但是EXCEL并不能直接按字?jǐn)?shù)排序。 需要先計(jì)算出每首歌曲的字?jǐn)?shù)，然后再進(jìn)行排序。如A、B列分別為“歌手”和“歌名”，在C1輸入“字?jǐn)?shù)”，在C2輸入公式：17= LEN(B2) 下拖，單擊C2,單擊工具欄上的“升序排列”即可，刪除C列。排序字母與數(shù)字的混合內(nèi)容日常使用中，表格經(jīng)常會有包含字母和數(shù)字混合的數(shù)據(jù)，對此類數(shù)據(jù)排序時(shí)，通常是先比較字母的大小，再比較數(shù)字的大小，但EXCEL是按照對字符進(jìn)行逐位比較來排序的，如A1A1222A293A3174A435A76B207B38C1449C510C33下表：A7排在第5位,AB1A7A0072A29A0293A

46、43A0434A122A1225A317A3176B3B0037B20B0208C5C0059C33C03310C144C144而不是第1位。排序結(jié)果無法令人滿意。如果希望EXCEL改變排序的規(guī)則，需要將數(shù)據(jù)做一些改變。在 B1 中輸入公式：LEFT(A1,1)&RIGHT(“OO(T& RIGHT(A1,LEN(A1)1),3)下拖 單擊B2,單擊工具欄上的“升序排列”即可。隨機(jī)排序如A、B列分別為“歌手”和“歌名”，在C1輸入“次序”,在C2輸入公式：=RAND (),下拖，單擊C2,單擊工具欄上的“降序排列”即可對歌曲清單進(jìn)行隨機(jī)排序。排序的問題我想要這樣的排序:2001

47、-20032004-20062007-20092010-2012;其實(shí)不是數(shù)據(jù)排序，應(yīng)該是數(shù)據(jù)填充。輸入公式二LEFT(E3,4)+3&”&RIGHT(E3,4)+3 即可。怎樣才能讓數(shù)列自動加數(shù)怎樣做才能讓數(shù)列自動加數(shù)AA0001BB0001AA0002CC0001AA0003BB0002CC0002公式為二A1&,000,f&COUNTIF(A$1:A1,A1)向下拖二丁忙乂丁(：01；1丁皿$1:人1人1)昇!”&人1&<,0000”)否則數(shù)字超過9就錯(cuò)誤了。 一個(gè)排序問題一個(gè)電子表格,格式是 101、102. 999,10101、1

48、0102. 99901,1010101,1020201. 9990101,1# 問如何將它排列成 101,10101,1010101,102,10201,1020101,. 999,99901,9990101 的形式。我在數(shù)字前加了個(gè)字母,比如”d“&”數(shù)字役然后用排序就可以把它們按你的需求排列了.最 后再把字母W去掉。數(shù)字的自動排序,插入后不變?趙趙趙趙趙趙二人 二人總經(jīng)理 副經(jīng)理 副經(jīng)理 技術(shù)員 員工如上的一個(gè)表，如何實(shí)現(xiàn)當(dāng)我把趙六這一整行(第6行)插入到上面的表中時(shí),A列的序列 號不變?最后的效果如下：1趙一總經(jīng)理2趙二副經(jīng)理3趙六員工4趙三副經(jīng)理5趙四技術(shù)員6趙五A1單元格輸入

49、公式二nm()，往下拉，然后再插入。=SUBTOTAL(3,$B$2:$B2)在Al中輸入公式：“二if(bl二",",coimta($b$l:bl)”后下拉復(fù)制至A列各行即可(“” 不必輸入)根據(jù)規(guī)律的重復(fù)的姓名列產(chǎn)生自動序號姓名 序號 張三 1 張三 1 李四 2 李四 2 趙五 3 趙五 3 趙五 3 王六 4 王六 4=(AloA2)+N(Bl) =IF(A3=A2,B2,B2+1) 姓名己排序：B2=SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2)姓名未排序：B2=IF(COUNTIF(A$2:A2,A2)>1,VLOOKUP(A2,A

50、：B,2,0),SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2)排名的函數(shù)用排名函數(shù)來對成績進(jìn)行排名，用起來非常地方便。=IF(ISERR(RANK(M3,M:M),n,RANK(M3,M:M)A列是成績B列是排名=SUMPRODUCT(A$1:A$9>A1)/COUNTIF(A$1:A$9,A$1：A$9)+1自動排名公式=RANK(C3,$C$3:$C$12)=RANK(A2,$A$2：$A$ 11,0)=RANK(C2,$C$2:$C$65)+COUNTIF($C$2:C2,C2)-1百分比排名的公式寫法為：=PERCENTRANK($C$3:$C$12,C3

51、)平均分及總分排名=AVERAGE(B2:E2)=RANK(F2,$F$2:$F$65536)求名次排名統(tǒng)計(jì)成績時(shí)遇到一個(gè)分別求班級和年級總分名次排名的問題，不曉得應(yīng)該運(yùn)用什么公式 來實(shí)現(xiàn)。班級名次：=SUMPRODUCT(BJ=A2)*(ZF>E2)+1年級名次：二 RANK(E2,ZF)公式下拖。排名次根據(jù)總分值大小，只將姓名排序后，降序結(jié)果=INDEX(A$2:A$6,RANK(D2,D$2:D$6)根據(jù)總分值大小，只將姓名排序后，升序=INDEX(A$2:A$6,RANK(D2,D$2:D$6,1)根據(jù)分?jǐn)?shù)進(jìn)行普通排名=RANK(A2,$A$2：$A$12)二 RANK(A2,A

52、$2:A$12)+COUNTIF(A$2:A2,A2)1=SUMPRODUCT(1*($E$3:$E$12>=E3)二 RANK(K3,$K$3:$K$26)二RANK(A2,A$2:A$12)=SUM(A$2:A$12>=A2)/COUNTIF(A$2:A$12,A$2:A$12)=COUNTIF($K$3:$K$26； >M&K3)+1 =INDEX($A$2:$A$7,MATCH(LARGE($C$2:$C$7,ROW(A1),$C$2:$C$7,0),1)=SUMPRODUCT($A$2:$A$12>A2)/COUNTIF($A$2:$A$12,$A$2

53、：$A$12&mm)+1=IL4NK(D2,OFFSET($A$1,MATCH($A2,$A:$A,0)-13,COUNTIF($A:$A,$A2),1)對于普通排名分?jǐn)?shù)相同時(shí)，按順序進(jìn)行不重復(fù)排名二 RANK(K32,$K$32:$K$55)+COUNTIF($K$32:$K32,K32)1二 COUNTIF($K$32:K32,K32)1+COUNTIF($K$3:$K$26,”>'&K32)+1=SUMPRODUCT(1*($E$3:$E$12+ROW($E$3:$E$12)/100>=($E3+ROW(E3)/100)=RANK(E3,$E$3:$E

54、$12)+COUNTIF($E$3:E3,E3)-1=SUMPRODUCT(1*($E$3:$E$12+$B$3:$B$12/100)>=(E3+B3/100)依分?jǐn)?shù)比高低名次成績排名二RANK($E3,$E$3:$E$22)內(nèi)建方式排名=SUMPRODUCT(1*($E$3:$E$12>=E3) 一般方式排名=RANK(E3,$E$3:$E$22)+SUM(IF($E$3:$E$22>E3,1/COUNTIF($E$3:$E$22,$E$3:$E$22)一般方式排名=RANK(E3,$E$3:$E$12)+COUNTIF($E$3:E3,E3)-1 不重復(fù)排名 =SUMPR

55、ODUCT(1*($E$3:$E$12+ROW($E$3:$E$12)/100>=($E3+ROW(E3)/100) =SUMPRODUCT(1*($E$3:$E$12+$B$3:$B$12/100)>=(E3+B3/100)不重復(fù)排名 =SUMPRODUCT(1*($E$3:$E$12+$B$3:$B$12/100+$C$3:$C$12/10000)>=(E3+B3/100+C3/1 0000)不重復(fù)排名二RANK($E3,$E$3:$E$22,1)倒排序美國式排名二 RANK(K247,$K$247:$K$270)二RANK(B1,$B1:$H1)中國式排名=RANK(B

56、2,$B$2:$B$21,0)二 RANK(B1,$B1:$H1)+COUNTIF($B$1:B1,B1)1=SUM(IF($A$1:$E$1>=A1,1/COUNTIF($A$1:$E$1,$A$1:$E$1),M,)=SUMPRODUCT($B$2:$B$21>=B2)/COUNTIF($B$2:B$21,B$2:B$21)=SUMPRODUCT(B$3:B$21>B3)*(1/COUNTIF($B$3:$B$21,$B$3:$B$21)+1(升序)=SUMPRODUCT(B$3:B$21<B3)*(VCOUNTIF($B$3:$B$21,$B$3:$B$21)+1(降序)=SUM(-(IF(FREQUENCY(B$2:B$21,B$2：B$21),B$2：B$21>B2)